How to use Python to play with MySQL database

1. Background

I conducted the connection experiment in Anaconda notebook, using the environment Python3.6. Of course, the operation can also be performed in the Python Shell.

The most commonly used and stable python library for connecting to MySQL database is PyMySQL.

2. Basic operations

1. Install the PyMySQL library

The simplest way:

Enter on the command linepip install pymysql

Or:

Download the whl file [1] for installation. The installation process is done by yourself.

2. Install MySQL database

There are two MySQL databases:

MySQL and MariaDB

I'm using MariaDB, which is a fork of MySQL.

The two are compatible in most aspects of performance, and you can’t feel any difference when using them.

gives the download address: MySQL[2], MariaDB[3], the installation process is very simple, just follow Next Step, but remember the password.

There is a small episode. MySQL and MariaDB are equivalent to the relationship between sisters and sisters. They were created by the same person (Widenius).

After MySQL was acquired by Oracle, Mr. Widenius felt unhappy, so he built MariaDB, which can completely replace MySQL.

Daniel is willful.

3. SQL basic syntax

Next, we will use SQL table creation, query, data insertion and other functions. Here is a brief introduction to the basic statements of SQL language.

• View database: SHOW DATABASES;

• Create database: CREATE DATEBASE database name;

• Use database: USE database name;

• View data table:SHOW TABLES;

• Create data table: CREATE TABLE table name (column name 1 (data type 1), column name 2 (data type 2));

• Insert data: INSERT INTO table name (column name 1, column name 2) VALUES (data 1, data 2);

• View data: SELECT * FROM table name;

• Update data: UPDATE table name SET column name 1 = new data 1, column name 2 = New data 2 WHERE A certain column = a certain data;

4. Connect to the database

After installing the necessary files and libraries, officially start connecting to the database Well, it’s mysterious but not difficult!

#首先导入PyMySQL库
import pymysql
#连接数据库，创建连接对象connection
#连接对象作用是：连接数据库、发送数据库信息、处理回滚操作（查询中断时，数据库回到最初状态）、创建新的光标对象
connection = pymysql.connect(host = 'localhost' #host属性
                            user = 'root' #用户名
                            password = '******'  #此处填登录数据库的密码
                            db = 'mysql' #数据库名
                            )

Execute this code and the connection will be completed!

5. Add, delete, modify and query operations

First check which databases there are:

#创建光标对象，一个连接可以有很多光标，一个光标跟踪一种数据状态。
#光标对象作用是：、创建、删除、写入、查询等等
cur = connection.cursor()
#查看有哪些数据库，通过cur.fetchall()获取查询所有结果
print(cur.fetchall())

Print out all databases:

(('information_schema',),
('law',),
('mysql',),
('performance_schema',),
('test',))

Create in the test database Table:

#使用数据库test
cur.execute('USE test')
#在test数据库里创建表student，有name列和age列
cur.execute('CREATE TABLE student(name VARCHAR(20),age TINYINT(3))')

Insert a piece of data into the data table student:

sql = 'INSERT INTO student (name,age) VALUES (%s,%s)'
cur.execute(sql,('XiaoMing',23))

View the content of the data table student:

cur.execute('SELECT * FROM student')
print(cur.fetchone())

The printout is: ('XiaoMing', 23)

Bingo! It’s a piece of data we just inserted

Finally, remember to close the cursor and connection:

#关闭连接对象，否则会导致连接泄漏，消耗数据库资源
connection.close()
#关闭光标
cur.close()

OK, the whole process is roughly like this.

Of course, these are very basic operations. More usage methods need to be found in the PyMySQL official documentation [4].

3. Import big data files

Take csv files as an example. There are generally two methods for importing csv files into the database:

1. Import one by one through the insert method of SQL , suitable for CSV files with small data volume, and will not be described in detail here.

2. Importing through the load data method is fast and suitable for big data files, which is also the focus of this article.

The sample CSV file is as follows:

The overall work is divided into 3 steps:

1. Use python to connect to the mysql database;

2、基于CSV文件表格字段创建表；

3、使用load data方法导入CSV文件内容。

sql的load data语法简介：

LOAD DATA LOCAL INFILE 'csv_file_path' INTO TABLE table_name FIELDS TERMINATED BY ',' LINES TERMINATED BY '\\r\\n' IGNORE 1 LINES

csv_file_path 指文件绝对路径 table_name指表名称 FIELDS TERMINATED BY ','指以逗号分隔 LINES TERMINATED BY '\\r\\n'指换行 IGNORE 1 LINES指跳过第一行，因为第一行是表的字段名

下面给出全部代码：

#导入pymysql方法
import pymysql


#连接数据库
config = {:'',
         :3306,
         :'username',
         :'password',
         :'utf8mb4',
         :1
         }
conn = pymysql.connect(**config)
cur = conn.cursor()


#load_csv函数，参数分别为csv文件路径，表名称，数据库名称
def load_csv(csv_file_path,table_name,database='evdata'):
   #打开csv文件
   file = open(csv_file_path, 'r',encoding='utf-8')
   #读取csv文件第一行字段名，创建表
   reader = file.readline()
   b = reader.split(',')
   colum = ''
   for a in b:
       colum = colum + a + ' varchar(255),'
   colum = colum[:-1]
   #编写sql，create_sql负责创建表，data_sql负责导入数据
   create_sql = 'create table if not exists ' + table_name + ' ' + '(' + colum + ')' + ' DEFAULT CHARSET=utf8'
   data_sql = "LOAD DATA LOCAL INFILE '%s' INTO TABLE %s FIELDS TERMINATED BY ',' LINES TERMINATED BY '\\r\\n' IGNORE 1 LINES" % (csv_filename,table_name)

   #使用数据库
   cur.execute('use %s' % database)
   #设置编码格式
   cur.execute('SET NAMES utf8;')
   cur.execute('SET character_set_connection=utf8;')
   #执行create_sql，创建表
   cur.execute(create_sql)
   #执行data_sql，导入数据
   cur.execute(data_sql)
   conn.commit()
   #关闭连接
   conn.close()
   cur.close()

The above is the detailed content of How to use Python to play with MySQL database. For more information, please follow other related articles on the PHP Chinese website!