本篇對于Python操作MySQL主要使用兩種方式：
原生模塊 pymsql
ORM框架 SQLAchemy
pymsql是Python中操作MySQL的模塊，其使用方法和MySQLdb幾乎相同。
下載安裝
1 | pip3 install pymysql |
使用操作
1、執(zhí)行SQL
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | # 創(chuàng)建連接conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='123456', db='db1',charset='utf8')# 創(chuàng)建游標(biāo)cursor = conn.cursor()# 執(zhí)行SQL，并返回受影響行數(shù)effect_row = cursor.execute("update hosts set host = '1.1.1.2' where nid > %s", (1,)) # 執(zhí)行SQL，并返回受影響行數(shù)effect_row = cursor.executemany("insert into hosts(host,color_id)values(%s,%s)", [("1.1.1.11",1),("1.1.1.11",2)])# 提交，不然無法保存新建或者修改的數(shù)據(jù)conn.commit() # 關(guān)閉游標(biāo)cursor.close()# 關(guān)閉連接conn.close() |
增,刪,改需要執(zhí)行 conn.commit()
2、獲取新創(chuàng)建數(shù)據(jù)自增ID
1 2 3 4 5 6 7 8 9 10 11 | import pymysql conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='123', db='t1')cursor = conn.cursor()cursor.executemany("insert into hosts(host,color_id)values(%s,%s)", [("1.1.1.11",1),("1.1.1.11",2)])conn.commit()cursor.close()conn.close() # 獲取最新自增ID => 如果插入多條,只能拿到最后一條idnew_id = cursor.lastrowid |
3、獲取查詢數(shù)據(jù)
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 | import pymysql conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='123', db='t1')cursor = conn.cursor()cursor.execute("select * from hosts") # 獲取第一行數(shù)據(jù)row_1 = cursor.fetchone()# => 再次執(zhí)行:cursor.fetchone() 獲得下一條數(shù)據(jù),沒有時為None# 獲取前n行數(shù)據(jù)# row_2 = cursor.fetchmany(n)# ==> 執(zhí)行了n次fetchone()# 獲取所有數(shù)據(jù)# row_3 = cursor.fetchall() conn.commit()cursor.close()conn.close() |
注：在fetch數(shù)據(jù)時按照順序進(jìn)行，可以使用cursor.scroll(num,mode)來移動游標(biāo)位置，如：
cursor.scroll(-1,mode='relative') # 相對當(dāng)前位置移動
cursor.scroll(2,mode='absolute') # 相對絕對位置移動
4、fetch數(shù)據(jù)類型
關(guān)于默認(rèn)獲取的數(shù)據(jù)是元祖類型，如果想要或者字典類型的數(shù)據(jù)，即：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | import pymysql conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='123456', db='t1') # 游標(biāo)設(shè)置為字典類型cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)row = cursor.execute("select * from user") result = cursor.fetchone()print(result)conn.commit()cursor.close()conn.close() |
補(bǔ)充:
1.SQL注入
"select name from user where name='%s' and password ='%s' " %(username,password)
"select name from user where name=%s and password =%s ",( username,password )
SQLAlchemy是Python編程語言下的一款ORM框架，該框架建立在數(shù)據(jù)庫API之上，使用關(guān)系對象映射進(jìn)行數(shù)據(jù)庫操作，
簡言之便是：將對象轉(zhuǎn)換成SQL，然后使用數(shù)據(jù)API執(zhí)行SQL并獲取執(zhí)行結(jié)果。
ORM:
ORM框架的作用就是把數(shù)據(jù)庫表的一行記錄與一個對象互相做自動轉(zhuǎn)換。 正確使用ORM的前提是了解關(guān)系數(shù)據(jù)庫的原理。
ORM就是把數(shù)據(jù)庫表的行與相應(yīng)的對象建立關(guān)聯(lián)，互相轉(zhuǎn)換。 由于關(guān)系數(shù)據(jù)庫的多個表還可以用外鍵實現(xiàn)一對多、多對多等關(guān)聯(lián)，
相應(yīng)地， ORM框架也可以提供兩個對象之間的一對多、多對多等功能。
SQLAlchemy:
本身無法操作數(shù)據(jù)庫，其必須以pymsql等第三方插件，
Dialect用于和數(shù)據(jù)API進(jìn)行交流，根據(jù)配置文件的不同調(diào)用不同的數(shù)據(jù)庫API，從而實現(xiàn)對數(shù)據(jù)庫的操作，如：
1 2 3 4 5 6 7 8 9 10 11 12 13 | MySQL-Python mysql+mysqldb://<user>:<password>@<host>[:<port>]/<dbname> pymysql mysql+pymysql://<username>:<password>@<host>/<dbname>[?<options>] MySQL-Connector mysql+mysqlconnector://<user>:<password>@<host>[:<port>]/<dbname> cx_Oracle oracle+cx_oracle://user:pass@host:port/dbname[?key=value&key=value...] 更多詳見：http://docs.sqlalchemy.org/en/latest/dialects/index.html |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | #!/usr/bin/env python# -*-coding:utf-8 -*-from sqlalchemy import create_engine,and_,or_,func,Tablefrom sqlalchemy.ext.declarative import declarative_basefrom sqlalchemy import Column, Integer, String,ForeignKeyfrom sqlalchemy.orm import sessionmaker,relationshipengine = create_engine("mysql+pymysql://root:123456@127.0.0.1:3306/t1?charset=utf8", max_overflow=5)Base = declarative_base()def init_db(): Base.metadata.create_all(engine) def drop_db(): Base.metadata.drop_all(engine) |
一、底層處理
使用 Engine/ConnectionPooling/Dialect 進(jìn)行數(shù)據(jù)庫操作，Engine使用ConnectionPooling連接數(shù)據(jù)庫，然后再通過Dialect執(zhí)行SQL語句。
二、ORM功能使用
使用 ORM/Schema Type/SQL Expression Language/Engine/ConnectionPooling/Dialect 所有組件對數(shù)據(jù)進(jìn)行操作。
根據(jù)類創(chuàng)建對象，對象轉(zhuǎn)換成SQL，執(zhí)行SQL。處理中文數(shù)據(jù)時,在連接數(shù)據(jù)庫時要加上 ?charset=utf8
1.創(chuàng)建表
一對多
多對多
2.操作表
.增
.刪
.改
.查
3.更多查詢方法:
1.filter_by( ... ) 填寫鍵值對方式
ret = session.query(Man).filter_by(name='alex').first()print(ret.nid,ret.name)
2.filter 填寫條件判斷
ret = session.query(Man).filter(Man.name=='eric').first()ret = session.query(Man).filter(Man.name=='eric' , Man.nid > 0).first()row = session.query(Man).filter(Man.nid.between(1,4)).all()ret = session.query(Users).filter(Users.id.in_(session.query(Users.id).filter_by(name='eric'))).all()
3.and_ or_ 條件判斷
from sqlalchemy import and_, or_ret = session.query(Man).filter(and_(Man.name == 'eric',Man.nid == 2)).first()ret = session.query(Man).filter(or_(Man.name == 'eric',Man.nid == 2)).first()
4.~ 取反
ret = session.query(Man).filter(Man.nid.in_([2,3])).first()ret = session.query(Man).filter(~Man.nid.in_([2,3])).first()
5.like + % 通配符
ret = session.query(Man).filter(Man.name.like('%x')).first()ret = session.query(Man).filter(~Man.name.like('%x')).first()
6.切片 限制 ( 序號,前閉后開 )
row = session.query(Man)[1:3]for ret in row: print(ret.nid, ret.name)row = session.query(Man).limit(3).offset(1)
7.order_by 排序
row = session.query(Man).order_by(Man.nid.desc()).all()row = session.query(Man).order_by(Man.nid.asc()).all()
8.group_by 分組
row = session.query(func.count('*')).select_from(Man).all()row = session.query(func.count('*')).filter(Man_To_Woman.nid > 1).all()row = session.query(func.count('*')).select_from(Man_To_Woman).group_by(Man_To_Woman.man_id).all()row = session.query(func.count('*')).select_from(Man_To_Woman).group_by(Man_To_Woman.man_id).limit(1).all()row = session.query(Man_To_Woman).group_by(Man_To_Woman.man_id).all()ret = session.query(Users).group_by(Users.extra).all()ret = session.query( func.max(Users.id), func.sum(Users.id), func.min(Users.id)).group_by(Users.name).all()ret = session.query( func.max(Users.id), func.sum(Users.id), func.min(Users.id)).group_by(Users.name).having(func.min(Users.id) >2).all()
9.join 連表
row = session.query(Users, Favor).filter(Users.id == Favor.nid).all()ret = session.query(Son).join(Father).all()ret = session.query(Son).join(Father, isouter=True).all()
10.union 組合
q1 = session.query(Users.name).filter(Users.id > 2)q2 = session.query(Favor.caption).filter(Favor.nid < 2)ret = q1.union(q2).all()q1 = session.query(Users.name).filter(Users.id > 2)q2 = session.query(Favor.caption).filter(Favor.nid < 2)ret = q1.union_all(q2).all()
更多功能參見文檔，猛擊這里下載PDF
補(bǔ)充 Relationship:
改變數(shù)據(jù)輸出的方式:可以在表的類中定義一個特殊成員:__repr__, return一個自定義的由字符串拼接的數(shù)據(jù)連接方式.
數(shù)據(jù)庫中表關(guān)系之間除了MySQL中標(biāo)準(zhǔn)的外鍵(ForeignKey)之外,還可以創(chuàng)建一個虛擬的關(guān)系,比如 group = relationship("Group",backref='uuu'),一般此虛擬關(guān)系與foreignkey一起使用.
relationship : 通過relatioinship 找到綁定關(guān)系的數(shù)據(jù) !!!
一對多,連表操作:
正向查詢:
需求:查詢Son表中所有數(shù)據(jù),并且顯示對應(yīng)的Father表中的數(shù)據(jù).
ret = session.query(Son).all()for obj in ret: print(obj.nid,obj.name,obj.father_id,obj.father.name)
反向查詢:
需求:查詢Father表中, 屬于 alvin 的所有兒子Son.
obj = session.query(Father).filter(Father.name=='alvin').first()row = obj.sonfor ret in row: print(ret.nid,ret.name,ret.father.name)
多對多,連表操作:
正,反向操作:
1.alex的所有女人
2.鳳姐的所有男人
man1 = session.query(Man).filter(Man.name=='alex').first()print(man1)for ret in man1.woman: print(ret.nid,ret.name)woman1 = session.query(Woman).filter(Woman.name=='fengjie').first()print(woman1)for ret in woman1.man: print(ret.nid,ret.name)
relatioinship 語句的簡寫: ,我添加到Man表中
woman = relationship("Woman", secondary=Man_To_Woman.__table__,backref='man')
1 關(guān)于 session.add session.query session.commit的順序問題？
就是說在同一個會話中， insert into table (xxxx)后，可以select * from xxx;可以查詢到插入的數(shù)據(jù)，只是不能在其他會話，比如我另開一個客戶端去連接數(shù)據(jù)庫不能查詢到剛剛插入的數(shù)據(jù)。
這個數(shù)據(jù)已經(jīng)到數(shù)據(jù)庫。值是數(shù)據(jù)庫吧這個數(shù)據(jù)給鎖了。只有插入數(shù)據(jù)的那個session可以查看到，其他的session不能查看到，可以理解提交并解鎖吧。
