Detailed explanation of python database mysql operation

Foreword: I recently started learning Django. After learning about the web framework, I suddenly felt like I learned a lot more knowledge. happy~~ This blog organizes and writes down the basic operations of the database, and the content is quite small. I will write SQLAlchemy tomorrow.

1. Basic database operations

1. If you want to allow writing Chinese in the database, you can use the following command when creating the database

create database zcl charset utf8;

2. View the students table structure

desc students;

3. View the creation Statements of students table structure

show create table students;

4. Delete database

drop database zcl;

5. Create a new field

alter table students add column nal char(64);　

PS: I really hate the above "simple explanation + code" blog. In fact, I wrote a lot of examples in the mysql terminal at that time, but because the computer was running a video-watching software at the time, I couldn't Ctrl+C/V. I’m too lazy now haha~~

2. Python connection database

python3 no longer supports mysqldb. Its replacement module is PyMySQL. The example in this article is in the python3.4 environment.

1. Install the pymysql module

pip3 install pymysql

2. Connect to the database and insert the data instance

import pymysql
#生成实例，连接数据库zcl
conn = pymysql.connect(host='127.0.0.1', user='root', passwd='root', db='zcl')
#生成游标，当前实例所处状态
cur = conn.cursor()
#插入数据
reCount = cur.execute('insert into students(name, sex, age, tel, nal) values(%s, %s, %s, %s, %s)',('Jack','man',25,1351234,"CN"))
reCount = cur.execute('insert into students(name, sex, age, tel, nal) values(%s, %s, %s, %s, %s)',('Mary','female',18,1341234,"USA"))

conn.commit()  #实例提交命令

cur.close()
conn.close()
print(reCount)

##View the results:

mysql> select* from students;+----+------+-----+-----+-------------+------+
| id | name | sex | age | tel         | nal  |
+----+------+-----+-----+-------------+------+
|  1 | zcl  | man |  22 | 15622341234 | NULL |
|  2 | alex | man |  30 | 15622341235 | NULL |
+----+------+-----+-----+-------------+------+
2 rows in set

3. Get data

import pymysql

conn = pymysql.connect(host='127.0.0.1', user='root', passwd='root', db='zcl')
cur = conn.cursor()

reCount = cur.execute('select* from students')

res = cur.fetchone()       #获取一条数据
res2 = cur.fetchmany(3)   #获取3条数据
res3 = cur.fetchall()     #获取所有(元组格式)
print(res)
print(res2)
print(res3)
conn.commit()

cur.close()
conn.close()

Output:

(1, 'zcl', 'man', 22, '15622341234', None)
((2, 'alex', 'man', 30, '15622341235', None), (5, 'Jack', 'man', 25, '1351234', 'CN'), (6, 'Mary', 'female', 18, '1341234', 'USA'))
()

3. Transaction rollback

#Transaction rollback is executed before data is written to the database, so transaction rollback conn.rollback () before the instance commits the command conn.commit(). As long as the data is not submitted, it can be rolled back, but after the rollback, the ID will be incremented . Please look at the following example:

Insert 3 pieces of data (note transaction rollback):

import pymysql
#连接数据库zcl
conn=pymysql.connect(host='127.0.0.1', user='root', passwd='root', db='zcl')
#生成游标，当前实例所处状态
cur=conn.cursor()
#插入数据
reCount=cur.execute('insert into students(name, sex, age, tel, nal) values(%s, %s, %s, %s, %s)', ('Jack', 'man', 25, 1351234, "CN"))
reCount=cur.execute('insert into students(name, sex, age, tel, nal) values(%s,%s,%s,%s,%s)', ('Jack2', 'man', 25, 1351234, "CN"))
reCount=cur.execute('insert into students(name, sex, age, tel, nal) values(%s, %s, %s, %s, %s)', ('Mary', 'female', 18, 1341234, "USA"))

conn.rollback()      #事务回滚
conn.commit()        #实例提交命令

cur.close()
conn.close()
print(reCount)

Before executing the command and after executing the command (including rollback operation) (note the ID number): The results of not executing the above code and executing the above code are the same!! Because the transaction has been rolled back, the students table will not add data!

mysql> select* from students;+----+------+--------+-----+-------------+------+
| id | name | sex    | age | tel         | nal  |
+----+------+--------+-----+-------------+------+
|  1 | zcl  | man    |  22 | 15622341234 | NULL |
|  2 | alex | man    |  30 | 15622341235 | NULL |
|  5 | Jack | man    |  25 | 1351234     | CN   |
|  6 | Mary | female |  18 | 1341234     | USA  |
+----+------+--------+-----+-------------+------+
4 rows in set

After executing the command (excluding rollback operation): Just comment the 11th line of code above.

mysql> select* from students;+----+-------+--------+-----+-------------+------+
| id | name  | sex    | age | tel         | nal  |
+----+-------+--------+-----+-------------+------+
|  1 | zcl   | man    |  22 | 15622341234 | NULL |
|  2 | alex  | man    |  30 | 15622341235 | NULL |
|  5 | Jack  | man    |  25 | 1351234     | CN   |
|  6 | Mary  | female |  18 | 1341234     | USA  |
| 10 | Jack  | man    |  25 | 1351234     | CN   |
| 11 | Jack2 | man    |  25 | 1351234     | CN   |
| 12 | Mary  | female |  18 | 1341234     | USA  |
+----+-------+--------+-----+-------------+------+
7 rows in set

##Summary:

Although the transaction is rolled back, the ID is still incremented It will not be canceled due to rollback, but this does not affect the consistency of the data (I don’t know the underlying principle~)

4. Batch insert data

import pymysql
#连接数据库zcl
conn = pymysql.connect(host='127.0.0.1', user='root', passwd='root', db='zcl')
#生成游标，当前实例所处状态
cur = conn.cursor()
li = [
    ("cjy","man",18,1562234,"USA"),
    ("cjy2","man",18,1562235,"USA"),
    ("cjy3","man",18,1562235,"USA"),
    ("cjy4","man",18,1562235,"USA"),
    ("cjy5","man",18,1562235,"USA"),
]

#插入数据
reCount = cur.executemany('insert into students(name,sex,age,tel,nal) values(%s,%s,%s,%s,%s)', li)

#conn.rollback()  #事务回滚
conn.commit()  #实例提交命令

cur.close()
conn.close()
print(reCount)

Output under pycharm: 5

mysql terminal display:

mysql> select* from students;   　  #插入数据前+----+-------+--------+-----+-------------+------+
| id | name  | sex    | age | tel         | nal  |
+----+-------+--------+-----+-------------+------+
|  1 | zcl   | man    |  22 | 15622341234 | NULL |
|  2 | alex  | man    |  30 | 15622341235 | NULL |
|  5 | Jack  | man    |  25 | 1351234     | CN   |
|  6 | Mary  | female |  18 | 1341234     | USA  |
| 10 | Jack  | man    |  25 | 1351234     | CN   |
| 11 | Jack2 | man    |  25 | 1351234     | CN   |
| 12 | Mary  | female |  18 | 1341234     | USA  |
+----+-------+--------+-----+-------------+------+
7 rows in set


mysql> mysql> select* from students;　　　#插入数据后+----+-------+--------+-----+-------------+------+
| id | name  | sex    | age | tel         | nal  |
+----+-------+--------+-----+-------------+------+
|  1 | zcl   | man    |  22 | 15622341234 | NULL |
|  2 | alex  | man    |  30 | 15622341235 | NULL |
|  5 | Jack  | man    |  25 | 1351234     | CN   |
|  6 | Mary  | female |  18 | 1341234     | USA  |
| 10 | Jack  | man    |  25 | 1351234     | CN   |
| 11 | Jack2 | man    |  25 | 1351234     | CN   |
| 12 | Mary  | female |  18 | 1341234     | USA  |
| 13 | cjy   | man    |  18 | 1562234     | USA  |
| 14 | cjy2  | man    |  18 | 1562235     | USA  |
| 15 | cjy3  | man    |  18 | 1562235     | USA  |
| 16 | cjy4  | man    |  18 | 1562235     | USA  |
| 17 | cjy5  | man    |  18 | 1562235     | USA  |
+----+-------+--------+-----+-------------+------+
12 rows in set

What you have learned should be summarized in time, some things have been forgotten~_~

For more detailed explanations of python database mysql operation related articles, please pay attention to the PHP Chinese website!