python operates on the mysql database

1. Basic operation of database

1. To allow you to write Chinese in the database, use the following command when creating the database

create database zcl charset utf8;

2. View the students table structure

desc students;

3. View the statements that created the students table structure

show create table students;

4. Delete the database

drop database zcl;

5. Create a new field

alter table students add column nal char(64);

PS: I hate this "simple explanation + code" blog. In fact, I wrote a lot of examples on the mysql terminal at that time, but I could not Ctrl+C/V because the computer was running a software to watch videos at that time. Now I am lazy

2. python connection to database

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

1. Install the pymysql module

pip3 install pymysql

2. Connect to the database and insert data instances

import pymysql
# Generate the instance and connect to the database zcl
conn = pymysql.connect(host='127.0.0.1', user='root', passwd='root', db='zcl')
# Generates the cursor, the current state of the instance 
cur = conn.cursor()
# Insert data 
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() # Instance commit command  
cur.close()
conn.close()
print(reCount)

View results:

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

3. Get the 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() # To obtain 1 The data 
res2 = cur.fetchmany(3) # To obtain 3 The data 
res3 = cur.fetchall() # Get all the ( A tuple format )
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

The transaction rollback is performed before the data is written to the database, so the transaction rollback conn.rollback () precedes the instance commit command conn.commit (). As long as the data is not committed, it can be rolled back, but after the rollback, ID is self-incrementing. Here's an example:

Insert 3 pieces of data (note transaction rollback):

import pymysql
# Connect to database zcl
conn=pymysql.connect(host='127.0.0.1', user='root', passwd='root', db='zcl')
# Generates the cursor, the current state of the instance 
cur=conn.cursor()
# Insert data 
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() # Transaction rollback 
conn.commit() # Instance commit command  
cur.close()
conn.close()
print(reCount)

Before and after the command is not executed (including the rollback operation)(note ID number): the result of not executing the above code and executing the above code is the same!! Since the transaction has been rolled back, the students table does 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 |
+----+------+--------+-----+-------------+------+
rows in set

After the command is executed (no rollback included): simply comment the code on line 11 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 |
+----+-------+--------+-----+-------------+------+
rows in set

Conclusion: although the transaction is rolled back, ID is still self-added, which will not be cancelled due to the rollback, but this does not affect the 1 uniqueness of the data (I am not clear about the underlying principle ~).

4. Batch insert data

import pymysql
# Connect to database zcl
conn = pymysql.connect(host='127.0.0.1', user='root', passwd='root', db='zcl')
# Generates the cursor, the current state of the instance 
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"),
]
# Insert data 
reCount = cur.executemany('insert into students(name,sex,age,tel,nal) values(%s,%s,%s,%s,%s)', li)
#conn.rollback() # Transaction rollback 
conn.commit() # Instance commit command 
cur.close()
conn.close()
print(reCount)

Output: 5 under pycharm

mysql terminal display:

mysql> select* from students; 　 # Before inserting data 
+----+-------+--------+-----+-------------+------+
| 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 |
+----+-------+--------+-----+-------------+------+
rows in set


mysql> 
mysql> select* from students;　　　# After inserting the data 
+----+-------+--------+-----+-------------+------+
| 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 |
+----+-------+--------+-----+-------------+------+
rows in set