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()