python之資料庫(mysql)操作,pythonmysql

來源:互聯網
上載者:User

python之資料庫(mysql)操作,pythonmysql

前言: 最近開始學django了,學了下web架構,頓時感覺又會了好多知識。happy~~ 這篇部落格整理寫下資料庫基本操作,內容挺少。明天寫SQLAlchemy。

 

一、資料庫基本操作

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#產生執行個體,串連資料庫zclconn = 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
View Code

 

3. 擷取資料

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()     #擷取所有(元組格式)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'))()
View Code

 

三、交易回復

交易回復是在資料寫到資料庫前執行的,因此交易回復conn.rollback()要在執行個體提交命令conn.commit()之前。只要資料未提交就可以復原,但復原後ID卻是自增的。請看下面的例子:

插入3條資料(注意交易回復):

import pymysql#串連資料庫zclconn=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
View Code

執行命令後(不包含復原操作):只需將上面第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
View Code


總結:雖然交易回復了,但ID還是自增了,不會因復原而取消,但這不影響資料的一致性(底層的原理我不清楚~)

 

四、批量插入資料
import pymysql#串連資料庫zclconn = 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 setmysql> 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
View Code

 

 

學完的東西要及時總結,有些東西忘記了阿~_~

 

聯繫我們

該頁面正文內容均來源於網絡整理,並不代表阿里雲官方的觀點,該頁面所提到的產品和服務也與阿里云無關,如果該頁面內容對您造成了困擾,歡迎寫郵件給我們,收到郵件我們將在5個工作日內處理。

如果您發現本社區中有涉嫌抄襲的內容,歡迎發送郵件至: info-contact@alibabacloud.com 進行舉報並提供相關證據,工作人員會在 5 個工作天內聯絡您,一經查實,本站將立刻刪除涉嫌侵權內容。

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.