【Python之路】第十九篇

highoo 2019-03-20

展開全文

本篇對(duì)于Python操作MySQL主要使用兩種方式：

原生模塊 pymsql
ORM框架 SQLAchemy

pymsql

pymsql是Python中操作MySQL的模塊，其使用方法和MySQLdb幾乎相同。

下載安裝

1	`pip3 install pymysql`

使用操作

1、執(zhí)行SQL

# 創(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

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 => 如果插入多條,只能拿到最后一條id

new_id = cursor.lastrowid

3、獲取查詢數(shù)據(jù)

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ù),沒有時(shí)為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ù)時(shí)按照順序進(jìn)行，可以使用cursor.scroll(num,mode)來移動(dòng)游標(biāo)位置，如：

cursor.scroll(-1,mode='relative') # 相對(duì)當(dāng)前位置移動(dòng)
cursor.scroll(2,mode='absolute') # 相對(duì)絕對(duì)位置移動(dòng)

4、fetch數(shù)據(jù)類型

　　關(guān)于默認(rèn)獲取的數(shù)據(jù)是元祖類型，如果想要或者字典類型的數(shù)據(jù)，即：

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注入

由于字符串拼接出現(xiàn)注入

"select name from user where name='%s' and password ='%s' " %(username,password)

pymysql 提供了轉(zhuǎn)義功能:

"select name from user where name=%s and password =%s ",( username,password )

SQLAchemy

SQLAlchemy是Python編程語言下的一款ORM框架，該框架建立在數(shù)據(jù)庫API之上，使用關(guān)系對(duì)象映射進(jìn)行數(shù)據(jù)庫操作，

簡言之便是：將對(duì)象轉(zhuǎn)換成SQL，然后使用數(shù)據(jù)API執(zhí)行SQL并獲取執(zhí)行結(jié)果。

ORM:

ORM框架的作用就是把數(shù)據(jù)庫表的一行記錄與一個(gè)對(duì)象互相做自動(dòng)轉(zhuǎn)換。正確使用ORM的前提是了解關(guān)系數(shù)據(jù)庫的原理。

ORM就是把數(shù)據(jù)庫表的行與相應(yīng)的對(duì)象建立關(guān)聯(lián)，互相轉(zhuǎn)換。由于關(guān)系數(shù)據(jù)庫的多個(gè)表還可以用外鍵實(shí)現(xiàn)一對(duì)多、多對(duì)多等關(guān)聯(lián)，

相應(yīng)地， ORM框架也可以提供兩個(gè)對(duì)象之間的一對(duì)多、多對(duì)多等功能。

SQLAlchemy:

本身無法操作數(shù)據(jù)庫，其必須以pymsql等第三方插件，

Dialect用于和數(shù)據(jù)API進(jìn)行交流，根據(jù)配置文件的不同調(diào)用不同的數(shù)據(jù)庫API，從而實(shí)現(xiàn)對(duì)數(shù)據(jù)庫的操作，如：

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

+ View Code ?

#!/usr/bin/env python

# -*-coding:utf-8 -*-

from sqlalchemy import create_engine,and_,or_,func,Table

from sqlalchemy.ext.declarative import declarative_base

from sqlalchemy import Column, Integer, String,ForeignKey

from sqlalchemy.orm import sessionmaker,relationship

engine = 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語句。

View Code

二、ORM功能使用

使用 ORM/Schema Type/SQL Expression Language/Engine/ConnectionPooling/Dialect 所有組件對(duì)數(shù)據(jù)進(jìn)行操作。

根據(jù)類創(chuàng)建對(duì)象，對(duì)象轉(zhuǎn)換成SQL，執(zhí)行SQL。處理中文數(shù)據(jù)時(shí),在連接數(shù)據(jù)庫時(shí)要加上 ?charset=utf8

1.創(chuàng)建表

View Code

一對(duì)多

View Code

多對(duì)多

View Code

2.操作表

View Code

.增

View Code

.刪

View Code

.改

View Code

.查

View Code

3.更多查詢方法:

事例: 表結(jié)構(gòu)

1.filter_by( ... ) 填寫鍵值對(duì)方式

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.切片限制 ( 序號(hào),前閉后開 )

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ù)輸出的方式:可以在表的類中定義一個(gè)特殊成員:__repr__, return一個(gè)自定義的由字符串拼接的數(shù)據(jù)連接方式.
數(shù)據(jù)庫中表關(guān)系之間除了MySQL中標(biāo)準(zhǔn)的外鍵(ForeignKey)之外,還可以創(chuàng)建一個(gè)虛擬的關(guān)系,比如 group = relationship("Group",backref='uuu'),一般此虛擬關(guān)系與foreignkey一起使用.

relationship : 通過relatioinship 找到綁定關(guān)系的數(shù)據(jù) !!!

一對(duì)多,連表操作:

表結(jié)構(gòu)

正向查詢:

需求:查詢Son表中所有數(shù)據(jù),并且顯示對(duì)應(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.son
for ret in row:
    print(ret.nid,ret.name,ret.father.name)

多對(duì)多,連表操作:

表結(jié)構(gòu)

正,反向操作:

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的順序問題？

就是說在同一個(gè)會(huì)話中， insert into table (xxxx)后，可以select * from xxx;可以查詢到插入的數(shù)據(jù)，只是不能在其他會(huì)話，比如我另開一個(gè)客戶端去連接數(shù)據(jù)庫不能查詢到剛剛插入的數(shù)據(jù)。

這個(gè)數(shù)據(jù)已經(jīng)到數(shù)據(jù)庫。值是數(shù)據(jù)庫吧這個(gè)數(shù)據(jù)給鎖了。只有插入數(shù)據(jù)的那個(gè)session可以查看到，其他的session不能查看到，可以理解提交并解鎖吧。

本站是提供個(gè)人知識(shí)管理的網(wǎng)絡(luò)存儲(chǔ)空間，所有內(nèi)容均由用戶發(fā)布，不代表本站觀點(diǎn)。請(qǐng)注意甄別內(nèi)容中的聯(lián)系方式、誘導(dǎo)購買等信息，謹(jǐn)防詐騙。如發(fā)現(xiàn)有害或侵權(quán)內(nèi)容，請(qǐng)點(diǎn)擊一鍵舉報(bào)。

轉(zhuǎn)藏 分享

QQ空間 QQ好友新浪微博微信

獻(xiàn)花（0） +1

來自： highoo > 《數(shù)據(jù)分析》

舉報(bào)/認(rèn)領(lǐng)