Python database (mysql) operation, pythonmysql

Python database (mysql) operation, pythonmysql

Preface:Recently, I started to learn django and learned the web framework. Now I feel that I have a lot of knowledge. Happy ~~ This blog sorts and writes down the basic database operations, with little content. Write SQLAlchemy tomorrow.

 

I. Basic Database Operations

1. To allow Chinese writing in the database, run the following command when creating the database:

create database zcl charset utf8;

2. view the students table structure

desc students;

3. view the statement for creating the students table structure

show create table students;

4. delete a database

drop database zcl;

5. Create a new field

alter table students add column nal char(64);　

 

PS: I hate the above blog "simple explanation + code. In fact, I wrote a lot of examples on the mysql terminal at the time, but I was unable to Ctrl + C/V because the computer was running a Video watching software. Now, I am lazy ~~

 

2. Connect python to the 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 data instances

Import pymysql # generate an instance and connect to the database zclconn = pymysql. connect (host = '2017. 0.0.1 ', user = 'root', passwd = 'root', db = 'zcl') # generate a cursor. The current instance status is cur = conn. cursor () # insert data reCount = cur.exe cute ('insert into students (name, sex, age, tel, nal) values (% s, % s) ', ('jack', 'Man', 25, 1351234, "CN") reCount = cur.exe cute ('insert into students (name, sex, age, tel, nal) values (% s, % s) ', ('Mary', 'female ', 18, 1341234, "USA") conn. commit () # 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 |+----+------+-----+-----+-------------+------+2 rows in set

View Code

 

3. Get Data

Import pymysqlconn = pymysql. connect (host = '2017. 0.0.1 ', user = 'root', passwd = 'root', db = 'zcl') cur = conn. cursor () reCount = cur.exe cute ('select * from students ') res = cur. fetchone () # obtain a data res2 = cur. fetchiterator (3) # Get 3 data records res3 = cur. fetchall () # obtain all (tuples) 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'))()

View Code

 

Iii. Transaction rollback

Transaction rollback is performed before data is written to the database. Therefore, the transaction rollback conn. rollback () must be performed before the instance commit command conn. commit.You can roll back the data as long as the data is not submitted, but the ID is auto-incrementing after the rollback.. See the following example:

Insert 3 data records (note transaction rollback ):

Import pymysql # connect to the database zclconn = pymysql. connect (host = '2017. 0.0.1 ', user = 'root', passwd = 'root', cipher cute ('insert into students (name, sex, age, tel, nal) values (% s, % s, % s) ', ('jack', 'man', 25,135 1234, "CN" using repeated recount‑cur.exe cute ('insert into students (name, sex, age, tel, nal) values (% s, % s) ', ('jack2', 'man', 25,135 1234, "CN" has been recountw.cur.exe cute ('insert into students (name, sex, age, tel, nal) values (% s, % s )', ('Mary ', 'female', 18,134 1234, "USA") conn. rollback () # transaction rollback conn. commit () # cur. close () conn. close () print (reCount)

 

Before and after the command is not executed (including the rollback operation) (note the ID number): the result of not executing the above Code is the same as that of executing the above Code !! 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

View Code

After executing the command (excluding the rollback operation): You only need to comment out the above 11th lines of code.

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

View Code

Conclusion: although the transaction is rolled back, the ID is still auto-incrementing and will not be canceled due to rollback, but this does not affect data consistency (I am not sure about the underlying principle ~)

 

Iv. Batch insert data

Import pymysql # connect to the database zclconn = pymysql. connect (host = '2017. 0.0.1 ', user = 'root', passwd = 'root', db = 'zcl') # generate a cursor. The current instance status is cur = conn. cursor () li = [("cjy", "man", "USA"), ("cjy2", "man", "USA "), ("cjy3", "man", 18, 1562235, "USA"), ("cjy4", "man", 18, 1562235, "USA"), ("cjy5 ", "man", 18,1562235, "USA"),] # insert data reCount = cur.exe cute.pdf ('insert into students (name, sex, age, tel, nal) values (% s, % s, % s) ', li) # conn. rollback () # transaction rollback conn. commit () # cur. close () conn. close () print (reCount)

Output in pycharm: 5

Mysql terminal display:

Mysql> select * from students; # Before data insertion + ---- + ------- + -------- + ----- + ----------- + ------ + | 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 setmysql> mysql> select * from students; # After data is inserted, + ---- + ------- + -------- + ----- + ----------- + ------ + | 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

View Code

 

 

Make a summary of the learned things in time, and forget some things ~ _~