Databases in MYSQL

This is a tutorial for creating and controlling databases with a MYSQL server.

You'll need the mysql server.

$ sudo apt-get install mysql-server

You'll be requested to choose a root password, be sure you remember it. Now, we'll login into mysqlserver, in a console:

$ mysql -uroot -p

Now you have a database management system running in your computer. From now on, you can do several thing in it:

• Creating an user ¹⁾:

> CREATE USER 'user'@'localhost' IDENTIFIED BY 'password';

Note: the commands must end with a semicolon

• Creating a database:
  

> CREATE DATABASE [database_name];

• Now you should select the database:
  

> USE [database_name];

• Grant privileges to a user on a database:
  

> GRANT ALL PRIVILEGES ON 'database' TO 'user'@'localhost' WITH GRANT OPTION ; 

• Creating a table:
  

> CREATE TABLE [table_name]
> ( column_name_1  type  [NOT NULL | NULL] [DEFAULT default_value] [AUTO_INCREMENT] [UNIQUE [KEY] | [PRIMARY] KEY] [COMMENT 'string'] [reference_definition] , 
>   column_name_2  type  [NOT NULL | NULL] ... ,
    ...
>   column_name_n  type  [NOT NULL | NULL] ... ) ;

Notes:

• The type in each column refers to the variable type that'll be stored in each column. In mysql there are several type of variables, that can be divided in three main types:
  
  • Numeric
    
  • String
    
  • Date and Time
    
• As you can see, you can choose several option to customize each column, like:
  • NULL or NOT NULL: defines if the values stored in the column can be null.
  • DEFAULT: you can add a default value to the data in the column.
  • AUTO_INCREMENT: you can make a number increase from the last register automatically.
  • KEY's: You can make a column a PRIMARY KEY or KEY (they are the same) which means that it is a unique identifier for a database record. When a table is created, one of the fields is typically assigned as the primary key. While the primary key is often a number, it may also be a text field or other data type. Primary Key's are automatically NOT NULL and there can not be two records with the same values in the primary key column. You can also make a column a FOREIGN KEY which means that it is a field within a database record that points to a key (or group of fields forming a key) of another record in a different table. In this arrangement, a foreign key in one table will typically refer to the primary key of another table. This enables references which can be made to link information together
    
  • COMMENT: you can add a comment describing each column, as a way of documentation only. (The comment must be a string, hence iy must be between '').
    
  • There are several other options that you can add to customize your table ²⁾
    

• You can describe a table to see it's structure and column's types and customization.
  

> DESCRIBE  [table_name] ; 

You'll get a result similar to this:³⁾

. . .

* For entering a new record in a table, you can use the command INSERT, in this way:

> INSERT INTO  [table_name] ( [column1] , [column2] , ... , [column_n] ) VALUES ( [value_]1] , [value_2] , .... , [value_n]);

• Also, you can select specific records in a table, filter them with several clauses.
  • To select (show) the whole table:

> SELECT * FROM  [table_name] ;

Note: instead of “ * ” you can specify the columns you want separeted by commas.

• You can filter these results with some clauses:
  
  • WHERE: followed by a set of conditions like: column_1 < value , column_2 = value , column_m > value. You can combine these conditions to form a composed condition with logical operators, like: AND, OR, NOT or XOR.
    
  • ORDER BY … LIMIT: this clause allows you to filter results, of any field in an ascending or descending order. Also you can add LIMIT and the maximun number of values you want in return. For this the syntax is:
    

> SELECT * FROM  [table_name] ORDER BY [column_name] [ASC | DESC] LIMIT [value];

• You can modify an existing value:

> UPDATE TABLE  [table_name]  SET [column_name] = [value]  WHERE  . . . ;

• You can also delete a database:
  

> DROP DATABASE [database_name] ;

• Or you can delete any table:
  

> DROP TABLE  [table_name] ;

• Or you can delete the records in a table:
  

>TRUNCATE TABLE  [table_name] ;

• You can save a database in a file, so you can have a back-up or use it in another computer. For this, in a console, in the directory you want to save the database:

$ mysqldump -u[user] -p  [database_name]  > [file_name].sql

• As you can imagine, you can also read a database from a file, for this, you'll need to create the desired database, use it, and later do the next command:

> SOURCE [file_path]/[file_name].sql

You can see the full syntax in: http://dev.mysql.com/doc/refman/5.7/en/index.html

In this part we'll learn how to manage the database using the python language we already know. For this, we'll need a MYSQL connector to python, named python-mysqldb:

sudo apt-get install python-mysqldb

Now we're going to start using python language. We can do this in a python enviroment like ipython or by editing a .py text file.

• First, we import the MySQLdb interface we just installed:

import MySQLdb

• We create a connection with a database using the connect method. It's neccesary to include host, user, password and, most importantly, the database. We must also assign a name to the connection:

connection_name = MySQLdb.connect(host='localhost', user='username', passwd='password', db='database_name')

• Now it's necesary to create a cursor object. This is used to manage the context of a fetch operation. We're going to use this from now on to all interactions with the database.

cursor = connection_name.cursor()

• We are ready now to start giving instructions to the database. Let's see some examples:
  
  
  • Retrieving data from a table:
    

Let's say we have a table called 'Cars' with columns named 'model' and 'price'. We can obtain the price from a certain model by doing this:

cursor.execute("SELECT price FROM Table WHERE model = 1970;")
return cursor.fetchone()

The fetchone() method fetches the first entry of the operation result. If we wanted to fetch all the results, we should use the fetchall() method.
We can define a python method for doing this operation in a shorter way:

def obtain_price(car_model):
    cursor.execute("SELECT price FROM Cars WHERE model = %s" % car_model)
    return cursor.fetchone()

The %s indicates that the data-type of the argument, and % car_model indicates which method argument it will use.

• Now let's insert some data on the database. Assume our 'Cars' table now has a new column named brand.

result = cursor.execute("INSERT INTO 'Cars' (model, price, brand) VALUES (1970 , 13000, 'Toyota';")
connection_name.commit()
return result

It's important to note that if the data is string-type, it must be between ' '. It's necessary to use the commit() method in order to make the changes permanent, we will always call it if we are inserting or updating data. The return command will indicate wether the table was modified or not.

• Again, we can create a method for the operation like this:

def insert_car(car_model,car_price,car_brand):
    result = cursor.execute("INSERT INTO 'Cars' (model, price, brand) VALUES (%s , %s, '%s');" % (car_model,car_price,car_brand))
    connection_name.commit()
    return result

The arguments in the last parenthesis correspond to the ones after the VALUES statement. Note also that only the brand column has ' ' associated, as price and model are integers.

• Updating data: For example, let's modify the price of a car:

result = cursor.execute("UPDATE 'Cars' SET price = 15000 WHERE brand = 'Toyota' AND model = 1970;")
connection_name.commit()
return result

Method version:

def update_car(car_price,car_brand,car_model):
    result = cursor.execute("UPDATE 'Cars' SET price = %s WHERE brand = '%s' AND model = %s;" % (car_price,car_brand,car_model))
    connection_name.commit()
    return result

The SET statement is used to indicate which columns we want to modify.

Back To Project
UP