Dennis Núñez-Fernández

BSc in Electronic Engineering, Universidad Nacional de Ingeniería (UNI)


Using a Database with MySQL and Python


1. Make a Backup

Move to the Database location with cd command:

$ cd /home/<location>

Then, make the Backup into a .sql file:

$ mysqldump -u root -p mysql_database > mysql_database_1.sql


2. Install MySQL dependencies and mysqlclient

Install the following dependencies:

$ sudo apt-get install mysql-server mysql-client mysql-common $ sudo apt-get install libmysqlclient-dev

Install "mysqlclient" (you can install into an environment):

$ pip install mysqlclient


3. Using the backup Database

Move to the backup Database location with cd command:

$ cd /home/<location>

Create an empty database and use it:

$ mysql -u root -p mysql> SHOW DATABASES; mysql> CREATE DATABASE mysql_database_1; mysql> USE mysql_database_1; mysql> SHOW DATABASES; mysql> SELECT DATABASE(); #see the database that is being used mysql> EXIT

Fill the database created with the .sql file:

$ mysql -u root -p mysql_database_1 < mysql_database_1.sql

See the database filled with:

$ mysql -u root -p mysql> SHOW DATABASES; mysql> USE mysql_database_1; mysql> SHOW TABLES; mysql> SHOW COLUMNS FROM sample; mysql> SHOW COLUMNS FROM user; mysql> DESCRIBE sample; mysql> DESCRIBE user; mysql> EXIT


4. Using the backup Database with Python

Move to the backup Database location with cd command:

$ cd /home/<location>

Open Jupyter Notebook (or Spyder) and work with the backup Database:

$ jupyter notebook

In [ ]: import MySQLdb In [ ]: db = MySQLdb.connect(host="localhost", user="root", passwd="<my_root_password>", db="mysql_database_1") In [ ]: cur = db.cursor() In [ ]: cur.execute("SELECT * FROM user") for row in cur.fetchall(): print(row[0]) In [ ]: db.close()