一、數(shù)據(jù)庫基本操作

1. 想允許在數(shù)據(jù)庫寫中文，可在創(chuàng)建數(shù)據(jù)庫時用下面命令create database zcl charset utf8;

2. 查看students表結(jié)構(gòu)desc students;

3. 查看創(chuàng)建students表結(jié)構(gòu)的語句show create table students;

4. 刪除數(shù)據(jù)庫drop database zcl;

5. 創(chuàng)建一個新的字段alter table students add column nal char(64);

PS: 本人是很討厭上面這種“簡單解釋+代碼”的博客。其實我當時在mysql終端寫了很多的實例，不過因為當時電腦運行一個看視頻的軟件，導致我無法Ctrl+C/V。現(xiàn)在懶了哈哈~~

二、python連接數(shù)據(jù)庫

python3不再支持mysqldb。其替代模塊是PyMySQL。本文的例子是在python3.4環(huán)境。

1. 安裝pymysql模塊pip3 install pymysql

2. 連接數(shù)據(jù)庫，插入數(shù)據(jù)實例import pymysql

#生成實例，連接數(shù)據(jù)庫zcl

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

#生成游標，當前實例所處狀態(tài)

cur = conn.cursor()

#插入數(shù)據(jù)

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)

查看結(jié)果: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. 獲取數(shù)據(jù)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() #獲取一條數(shù)據(jù)

res2 = cur.fetchmany(3) #獲取3條數(shù)據(jù)

res3 = cur.fetchall() #獲取所有(元組格式)

print(res)

print(res2)

print(res3)

conn.commit()

cur.close()

conn.close()

輸出:(1, 'zcl', 'man', 22, '15622341234', None)

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

()

三、事務回滾

事務回滾是在數(shù)據(jù)寫到數(shù)據(jù)庫前執(zhí)行的，因此事務回滾conn.rollback()要在實例提交命令conn.commit()之前。只要數(shù)據(jù)未提交就可以回滾，但回滾后ID卻是自增的。請看下面的例子:

插入3條數(shù)據(jù)(注意事務回滾):import pymysql

#連接數(shù)據(jù)庫zcl

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

#生成游標，當前實例所處狀態(tài)

cur=conn.cursor()

#插入數(shù)據(jù)

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)

未執(zhí)行命令前與執(zhí)行命令后(包含回滾操作)(注意ID號): 未執(zhí)行上面代碼與執(zhí)行上面代碼的結(jié)果是一樣的!!因為事務已經(jīng)回滾，故students表不會增加數(shù)據(jù)!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

執(zhí)行命令后(不包含回滾操作):只需將上面第11行代碼注釋。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

總結(jié)：雖然事務回滾了，但ID還是自增了，不會因回滾而取消，但這不影響數(shù)據(jù)的一致性(底層的原理我不清楚~)

四、批量插入數(shù)據(jù)import pymysql

#連接數(shù)據(jù)庫zcl

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

#生成游標，當前實例所處狀態(tài)

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"),

]

#插入數(shù)據(jù)

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下輸出: 5

mysql終端顯示:mysql> select* from students; 　 #插入數(shù)據(jù)前+----+-------+--------+-----+-------------+------+

| 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;　　　#插入數(shù)據(jù)后+----+-------+--------+-----+-------------+------+

| 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

學完的東西要及時總結(jié)，有些東西忘記了阿~_~

總結(jié)

以上是生活随笔為你收集整理的怎么用python写数据库_如何使用python对数据库(mysql)进行操作的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。