ArcosWiki

Working on the computer that is going to host the server (localhost), execute in bash the following:

$ sudo apt-get install mysql-server mysql-client

During the packages installation, a window will be displayed, and you will be requested to input a password for the root account of MySQL. Please use a safe and strong password yet easy to remember for you, and DON'T SHARE IT WITH ANYONE.

Now we install the python module for MySQL. With this, we can import libraries in a python program, and use them to create and modify data bases.

$ sudo apt-get install python-mysqldb

Once installed, we enter the MySQL environment with the root account:

$ mysql -u root -p

You will be asked to input the same password given during the previous installation.

Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 38
Server version: 5.5.33-1 (Debian)

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> 

Now we are in the MySQL environment. As you can see, the syntax for commands consists of writting them in uppercase, and end them with semicolons “;” (similar to java in this particular detail). When we create or modify any parameter, this must be written in lowercase, and in some cases between quotes ('parameter'). Lets display the available data bases:

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema | 
| mysql              | 
+--------------------+
2 rows in set (0.00 sec)

To create a data base and modify it we do the following (don't include symbols like “<” or “>” unless instructed):

mysql> CREATE DATABASE <nombre de base de datos>;

Lets create a data base named “prueba1”:

mysql> CREATE DATABASE prueba1;
Query OK, 1 row affected (0.02 sec)

Now we create a new user and grant it privileges for modifying “prueba1”, instead of using root:

mysql> CREATE USER 'usuario'@'localhost' IDENTIFIED BY 'contraseña';

“CREATE USER” generates the new user, “@localhost” indicates that the user will be created in the server installed in localhost (If the server has a specific IP address then write that address instead of localhost). “IDENTIFIED BY” assigns a password for the user. You can create an user without password but it is NOT recommended. Lets create an user “proyecto” with password “313InGII53m”.

mysql> CREATE USER 'proyecto'@'localhost' IDENTIFIED BY '313InGII53m';
Query OK, 0 rows affected (0.00 sec)

To tell MySQL which data base are we going to modify, write the following (in this case, we will work on “prueba1”)

mysql> USE prueba1;
Database changed

Now we grant permissions to “proyecto” to modify “prueba1”

mysql> GRANT ALL ON prueba1.* TO 'proyecto'@'localhost';
Query OK, 0 rows affected (0.00 sec)

Even though “proyecto” has permissions, it can not grant the same permissions to other users, only root can. However, it is possible to allow user “proyecto” to grant privileges to other users:

mysql> GRANT ALL ON prueba1.* TO 'proyecto'@'localhost' WITH GRANT OPTION;
Query OK, 0 rows affected (0.00 sec)

To exit MySQL environment:

mysql> quit;
Bye

Now lets login with user “proyecto”

$ mysql -u proyecto -p
Enter password:

Now we will create a new table called “user” with columns “name”, “email”, “clave”. All data will be saved with VARCHAR format.

mysql> CREATE TABLE user(name VARCHAR(20), \ email VARCHAR(20), \ clave VARCHAR(16));

To display “user”, type the following command:

mysql> DESCRIBE user;

The environment will display a table similar to this:

+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| name  | varchar(20) | NO   |     | NULL    |       |
| email | varchar(20) | NO   |     | NULL    |       |
| clave | varchar(16) | NO   |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

The numbers between parenthesis determine the max lenght of the string lines. To learn how to insert information in each column, read the next example:

mysql> INSERT INTO user(name, email, clave) VALUES('Fulano Sultano', 'fusufecha@micorreo.com', '1u2d3t4c5c6s');

For displaying all the contents of the table:

mysql> SELECT * FROM user;

The following table (or a similar one) is displayed:

+----------------+----------------------+--------------+
| name           | email                | clave        |
+----------------+----------------------+--------------+
| Fulano Sultano | fusufecha@micorreo.c | 1u2d3t4c5c6s |
+----------------+----------------------+--------------+
1 row in set (0.00 sec)

If we want to erase a table from the data base:

mysql> DROP TABLE user;

And if we want to erase the whole data base:

mysql> DROP DATABASE prueba1;

#!/usr/bin/env python
# -*- coding: utf-8 -*-

#Importamos la librería de MySQL

import MySQLdb as mdb
		
#Nos conectamos a la base de datos con la siguiente línea, tal que entre paréntesis se escribe en este orden "IP del servidor", "usuario MySQL", "contraseña", "nombre de base de datos"
		
con = mdb.connect('localhost', 'proyecto', '313InGII53m', 'prueba1');
    
#Llamamos a cursor, el cual permite ejecutar comandos de  MySQL desde afuera mediante python
cur = con.cursor()
cur.execute("CREATE TABLE user(name VARCHAR(20), \ email VARCHAR(20), \ clave VARCHAR(16))")
cur.execute("INSERT INTO user(name, email, clave) VALUES('Fulano Sultano', 'fusufecha@micorreo.com', '1u2d3t4c5c6s')")

This is one of the several methods for managing databases. In this case, the management is done with a GUI. You may manage the information in the way you feel more comfortable.

You can see the installation tutorial here

Once installed phpMyAdmin, along with all the dependencies, we open a web browser and go to the address “http://localhost/phpmyadmin/” without the quotes. We should see the following screen

We simply input the user and password for our database, assigned while creating our MySQL database. If you have worked previously with the CLI to modify the databases, then you should see the following screen

To start managing the databases, click on “Databases”, then click on the desired database to manage

In this case, we are going to work with “prueba1”. Once in that database, we can manipulate tables, erase their contents or manually fill them with information.

If you click on the table “Usuario”, you should be able to see the users that were added previously using python methods, CLI, directly using MySQL command lines or directly with the app. You may also add new tables if needed.

To view the kind of variables the table can accept, as same as its characteristics, click on the “Structure” button.

Here you can add or delete columns, assign special characteristics to columns, the type of information admited, etc.

Return to previous page