如何使用python對資料庫(mysql)進行操作
- 迷茫原創
- 2017-03-25 16:51:222033瀏覽
一、資料庫基本操作
#1. 想允許在資料庫寫中文，可在建立資料庫時用下面指令
create database zcl charset utf8;
2. 查看students表格結構
desc students;
3. 查看建立students表格結構的語句
show create table students;
4. 刪除資料庫
drop database zcl;
5. 建立一個新的欄位
alter table students add column nal char(64);
PS: 本身是很討厭上面這種“簡單解釋+代碼」的部落格。其實我當時在mysql終端寫了很多的實例，不過因為當時電腦運行一個看影片的軟體，導致我無法Ctrl+C/V。現在懶了哈哈~~
二、python連接資料庫
python3不再支援mysqldb。其替代模組為PyMySQL。 本文的例子是在python3.4環境。
1. 安裝pymysql模組
pip3 install pymysql
2. 連線資料庫，插入資料實例
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)
#查看結果:
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. 取得資料
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()
輸出:
(1, 'zcl', 'man', 22, '15622341234', None) ((2, 'alex', 'man', 30, '15622341235', None), (5, 'Jack', 'man', 25, '1351234', 'CN'), (6, 'Mary', 'female', 18, '1341234', 'USA')) ()
三、事務回滾
#交易回滾是在資料寫到資料庫前執行的，因此交易回滾conn.rollback()要在實例提交指令conn.commit()之前。 只要資料未提交就可以回滾，但回滾後ID卻是自增的。請看下面的範例:
插入3條資料(注意事務回滾):
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)
未執行指令前與執行指令後(包含回滾操作)(注意ID號): 未執行上面程式碼與執行上面程式碼的結果是一樣的!!因為交易已經回滾，故students表不會增加資料!
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
執行指令後(不包含回溯操作):只需將上面第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
總結：雖然交易回滾了，但ID還是自增了，不會因為回滾而取消，但這不影響資料的一致性 （底層的原理我不清楚~）
四、批次插入資料
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)pycharm下輸出: 5
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 | +----+-------+--------+-----+-------------+------+ 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
學完的東西要及時總結，有些東西忘了阿~_~
以上是如何使用python對資料庫(mysql)進行操作的詳細內容。更多資訊請關注PHP中文網其他相關文章！
陳述：
本文內容由網友自願投稿，版權歸原作者所有。本站不承擔相應的法律責任。如發現涉嫌抄襲或侵權的內容，請聯絡admin@php.cn