Database Lesson 1

We are going to give a basic overview of accessing and using a database in this step. Our C# application will use the Microsoft SQL Server database system. With Python, we'll be using MySQL, a fast and secure freely available database system, widely used in production environments.

• Python
• C#

• MySQL with Python

  Our Python application uses MySQL as the database backend. This is a freely available, production capable, database server system. In order to use the database system, you will need to download and install the MySQL Connector/Python module. The Connector/Python module is fully functional and freely available at https://dev.mysql.com/downloads/connector/python, and we will be using this module for application development.

  Running MySQL command line

  MySQL comes with a command line utility. Rather than issue database commands via a programming language, the command line utility is a quick way of test queries and generating tables before use in a program.

  On Windows, the command line program is located in the MySQL section of your Start menu. In Linux, you can start the program by issuing:

  mysql -u root -p

• SQL Server and C#

  Our C# application uses Microsoft SQL Server as the database backend. This software is available in a free Express edition from the Microsoft website.

  The SQL Server comes supplied with a command line utility for quickly checking test queries before writing them in a program. To start the utility, open a new terminal window and enter:

  sqlcmd

  The above command will assume the database is stored on the local machine, and will not use any usernames or passwords.

  NB: For all SQL Server commands to follow in the next section, you must enter "go" followed by enter after typing in the SQL command. The database server will not execute any commands unless the go command is given.

Creating a database

Our first task is to create a database which we can later access. To do this, enter:

create database test;

This creates a database called test. Each database can have a number of tables within it, all of which are associated with a name. Each table has a number of different named columns.

Using a database

Now that we've created a database, we need to explicitly tell the utility to use the set to create further tables. We can do this by executing the following command:

use test;

Creating a table

To create a table called users, execute the following command:

create table users (pin int, age int);

This command created a new table called users, with two names columns, pin and age. Both of these columns are of type int, which is a integer type.

Adding some data

We're going to add some data to this table now. An entry in the table is called a row. We are going to add a row with both a PIN number and age value, which will then be used in further steps.

insert into users values (1234, 30);

This has added a row into the users table with values 1234 and 30 into the columns in the same order as when the table was created. In this case, 1234 will go into the pin column, and 30 will go into the age column.

However, we don't have to use this style to insert data: we can explicitly name which columns the data should be inserted into using a slightly different syntax:

insert into users (age, pin) values (30, 5678);

The above code inserted a row with pin of 5678 and age of 30.

Querying the database

So, we can now add entries into our own database. The next important lesson is returning data in the form of a query. We can make a query on any table, returning any columns, under any search criteria we specify.

select * from users;

The above command will return all columns from the table users. This command will produce something like the following:

+------+------+
| pin  | age  |
+------+------+
| 1234 |   30 |
| 5678 |   30 |
+------+------+

In a telephony application, suppose we wanted the user to enter a four digit pin number, and then we query the database to find the age value. We can make these type of queries by adding the "where" clause onto the end of a statement:

select * from users where pin=1234;

The above will return both the pin and age column values for any entries where the pin value is equal to 30.

To return a specific column, we can list the names of the columns seperated by a comma, rather than using the '*' keyword. For example, we can get the exact same query result as above by issuing the follow command:

select pin, age from users where pin=1234;

To return just the age value of the same query:

select age from users where pin=1234;

Next time...

Now that we know how to use the database to do some simple operations and queries, our next step will implement a simple telephony application to make use of our PIN/age user table.

Lesson 2