Using Python to manipulate MySQL databases

These are some of the libraries and tools that I used to use MySQL, which are documented here, and are easily accessible to me.

Python version: 2.7.13

MySQL version: 5.5.36

Several Python libraries

1, Mysql-connector-python

Is the official MySQL python drive

https://dev.mysql.com/doc/connector-python/en/

Installation:

Pip Install Mysql-connector

Example code:

https://github.com/mike-zhang/pyExamples/blob/master/databaseRelate/mysqlOpt/mysql-connector_Opt/test1.py

2, Mysql-python

is a python driver that encapsulates the MySQL C drive.

Installation:

Pip Install MySQL

Under CentOS: Yum install Mysql-python

Example code:

https://github.com/mike-zhang/pyExamples/blob/master/databaseRelate/mysqlOpt/MySQLdb_Opt/test1.py

3, Pymysql

A pure Python-implemented MySQL library

Installation:

Pip Install Pymysql

Example code:

https://github.com/mike-zhang/pyExamples/blob/master/databaseRelate/mysqlOpt/pymysql_Opt/test1.py

Several tools

The following tools are developed using the Mysql-python library and need to be installed in advance.

1. Back up data from MySQL table to CSV file

Mysqldump can back up data, but it is a backup of SQL statements, and sometimes it is necessary to back up a single or multiple table as a CSV file.

Principle:

Paging gets data and writes data to a CSV file

Source Address:

Https://github.com/mike-zhang/pyExamples/blob/master/databaseRelate/mysqlOpt/MySQLdb_Opt/csvBakAndRestore/ backtable2csv_test1.py

2. Import data from CSV file to MySQL table

corresponding to data export, the tool applies when a CSV file with a header needs to be imported into the database.

Principle:

Read the CSV file and generate the SQL statement to bulk commit the statements into the library.

Source Address:

Https://github.com/mike-zhang/pyExamples/blob/master/databaseRelate/mysqlOpt/MySQLdb_Opt/csvBakAndRestore/ restoretablefromcsv_test1.py

3. Import data from SQL file to MySQL table

When the exported SQL file needs to be restored, if the file is too large, there will be a long wait time problem, during which the data cannot be viewed, and if this problem is resolved, the tool is applicable.

Principle:

Read SQL statements, commit in batches (default 10,000 commits once)

Source Address:

Https://github.com/mike-zhang/pyExamples/tree/master/databaseRelate/mysqlOpt/MySQLdb_Opt/importFromSqlString

4. Get the Build Table statement

There is a need to get a MySQL build table statement in bulk, and the tool is applicable.

Principle:

Get a list of table names in the database through show tables, and then get the build table statements through show create table.

Source Address:

https://github.com/mike-zhang/pyExamples/blob/master/databaseRelate/mysqlOpt/MySQLdb_Opt/getTableCreateSql.py

5. Get table field name

There is a need to get the name of the table field, which is appropriate for the tool.

Principle:

Get table field information with the DESC command

Source Address:

https://github.com/mike-zhang/pyExamples/blob/master/databaseRelate/mysqlOpt/MySQLdb_Opt/getTableFields.py

6. Paging Test

This code applies when there is too much data to be paged out.

Principle:

Implemented by limit

Source Address:

https://github.com/mike-zhang/pyExamples/blob/master/databaseRelate/mysqlOpt/MySQLdb_Opt/pagingTest1.py

7. Batch clean up the contents of the table

This code applies when you need to clean up the contents of a table in bulk.

Principle:

Executes multiple DELETE statements through a script.

Source Address:

https://github.com/mike-zhang/pyExamples/blob/master/databaseRelate/mysqlOpt/MySQLdb_Opt/clearTables.py

All right, that's it, I hope it helps you.

This article GitHub address:

https://github.com/mike-zhang/mikeBlogEssays/blob/master/2017/20170703_ uses Python to manipulate the MySQL database. RST

Welcome to Supplement

Using Python to manipulate MySQL databases