Detailed python database MySQL operation

Preface: recently began to learn Django, learned the web framework, immediately feel a lot of knowledge. happy~~ this blog to organize and write down the basic operation of the database, the content is very small. Write SQLAlchemy tomorrow.

first, the basic operation of the database

1. To allow the database to be written in Chinese, use the following command when creating the database

Create Database zcl charset UTF8;

2. View the students table structure

DESC students;

3. View the statement that created the students table structure

Show create table students;

4. Deleting a database

Drop database zcl;

5. Create a new field

ALTER TABLE students add column nal char (64);

PS: I am very disgusted with the above "simple Explanation + code" blog. I actually wrote a lot of examples at the MySQL terminal, but I couldn't ctrl+c/v because the computer was running a video-watching software. Now lazy haha ~ ~

Second, Python connection database

Python3 no longer supports MYSQLDB. Its replacement module is pymysql. examples of this article are in the python3.4 environment.

1. Installing the Pymysql module

PIP3 Install Pymysql

2. Connect to the database and insert the data instance

Import pymysql# Build instance, connect database Zclconn = Pymysql.connect (host= ' 127.0.0.1 ', user= ' root ', passwd= ' root ', db= ' zcl ') #生成游标, Current instance state 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 to 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 results:

Mysql> select* from students;+----+------+-----+-----+-------------+------+| ID | name | sex | Age | Tel         | nal  |+----+------+-----+-----+-------------+------+|  1 | ZCL  |  22 | 15622341234 | NULL | |  2 | Alex | Mans |  30 | 15622341235 | NULL |+----+------+-----+-----+-------------+------+2 rows in Set

3. Get Data

Import Pymysqlconn = 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 ()     #获取所有 (tuple format) print (res) print (res2) print (RES3) conn.commit () Cur.close () Conn.close ()

Output:

(1, ' zcl ', ' man ', ' 15622341234 ', None) (2, ' Alex ', ' man ', +, ' 15622341235 ', None), (5, ' Jack ', ' man ', ' 1351234 ', ' CN '), (6, ' Mary ', ' female ', 18, ' 1341234 ') , ' USA ')) ()

Third, transaction rollback

a transaction rollback is performed before the data is written to the database , so the transaction is rolled back Conn.rollback () before the instance submits the command Conn.commit (). as long as the data is not committed, it can be rolled back, but the ID is self-increment after the rollback. Take a look at the following example:

Insert 3 data (Note transaction rollback):

Import pymysql# Connect database Zclconn=pymysql.connect (host= ' 127.0.0.1 ', user= ' root ', passwd= ' root ', db= ' zcl ') #生成游标, The current instance is in a state of Cur=conn.cursor () #插入数据reCount =cur.execute (' INSERT into students (name, sex, age, tel, nal) values (%s,%s,%s,%s, %s) ', (' Jack ', ' man ', +, 1351234, ' CN ') ' Recount=cur.execute (' insert into students (name, sex, age, tel, nal) VALUES (%s,%s, %s,%s,%s) ', (' Jack2 ', ' man ', +, 1351234, "CN")) Recount=cur.execute (' INSERT into students (name, sex, age, tel, nal) values (%s,%s,%s,%s,%s) ', (' Mary ', ' female ', ' 1341234, ' USA ') ' Conn.rollback ()      #事务回滚conn. Commit ()        # Instance submission Command Cur.close () conn.close () print (recount)

Do not execute the command before and after executing the command (including rollback operation) (note ID number): The above code is not executed and the result of executing the above code is the same!! Because 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  |  22 | 15622341234 | NULL | |  2 | Alex | Mans    |  30 | 15622341235 | NULL | |  5 | Jack | Mans    |  25 | 1351234     | CN   | |  6 | Mary | Female |  18 | 1341234     | USA  |+----+------+--------+-----+-------------+------+4 rows in Set

After executing the command (does not include a rollback operation): Simply comment the 11th line of code above.

Mysql> select* from students;+----+-------+--------+-----+-------------+------+| ID | Name  | sex    | age | Tel         | nal  |+----+-------+--------+-----+-------------+------+|  1 | ZCL   |  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 | Mans    |  25 | 1351234     | CN   | | 12 | Mary  | female |  18 | 1341234     | USA  |+----+-------+--------+-----+-------------+------+7 rows in Set

Summary: Although the transaction is rolled back, the ID is self-increment and will not be canceled due to rollback, but this does not affect the consistency of the data (the underlying principle I do not know ~)

Iv. inserting data in bulk

Import pymysql# Connect Database Zclconn = Pymysql.connect (host= ' 127.0.0.1 ', user= ' root ', passwd= ' root ', db= ' zcl ') #生成游标, The current instance is in the state cur = conn.cursor () li = [    ("Cjy", "Man", 18,1562234, "USA"),    ("Cjy2", "Man", 18,1562235, "USA"),    (" Cjy3 "," Man ", 18,1562235," USA "),    (" Cjy4 "," Mans ", 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 ()  #事务回滚conn. Commit ()  #实例提交命令cur. Close () Conn.close () print (recount)

Pycharm Output: 5

MySQL Terminal display:

Mysql> select* from students; #插入数据前 +----+-------+--------+-----+-------------+------+| ID | name | sex | Age | Tel |  NAL |+----+-------+--------+-----+-------------+------+| 1 | ZCL |  Mans | 22 | 15622341234 |  NULL | | 2 | Alex |  Mans | 30 | 15622341235 |  NULL | | 5 | Jack |  Mans | 25 | 1351234 |  CN | | 6 | Mary |  Female | 18 | 1341234 | USA | | 10 | Jack |  Mans | 25 | 1351234 | CN | | 11 | Jack2 |  Mans | 25 | 1351234 | CN | | 12 | Mary |  Female | 18 | 1341234 |　　　USA |+----+-------+--------+-----+-------------+------+7 rows in setmysql> mysql> select* from students; #插入数据后 +----+-------+--------+-----+-------------+------+| ID | name | sex | Age | Tel |  NAL |+----+-------+--------+-----+-------------+------+| 1 | ZCL |  Mans | 22 | 15622341234 |  NULL | | 2 | Alex |  Mans | 30 | 15622341235 |  NULL | | 5 | Jack |  Mans | 25 | 1351234 |  CN | | 6 | Mary |  Female | 18 |     1341234| USA | | 10 | Jack |  Mans | 25 | 1351234 | CN | | 11 | Jack2 |  Mans | 25 | 1351234 | CN | | 12 | Mary |  Female | 18 | 1341234 | USA | | 13 | Cjy |  Mans | 18 | 1562234 | USA | | 14 | Cjy2 |  Mans | 18 | 1562235 | USA | | 15 | Cjy3 |  Mans | 18 | 1562235 | USA | | 16 | Cjy4 |  Mans | 18 | 1562235 | USA | | 17 | Cjy5 |  Mans | 18 | 1562235 | USA |+----+-------+--------+-----+-------------+------+12 rows in Set

Finish the finished things to summarize in time, some things have forgotten ah ~_~

Read more about Python's database MySQL operations related articles please follow topic.alibabacloud.com!

