【Python之路】第十九篇

highoo 2019-03-20

展開全文

本篇對于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ù),沒有時為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ù)，即：

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)系對象映射進(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ù)庫的操作，如：

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 所有組件對數(shù)據(jù)進(jìn)行操作。

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

1.創(chuàng)建表

View Code

一對多

View Code

多對多

View Code

2.操作表

View Code

.增

View Code

.刪

View Code

.改

View Code

.查

View Code

3.更多查詢方法:

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

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ù) !!!

一對多,連表操作:

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

正向查詢:

需求:查詢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.son
for ret in row:
    print(ret.nid,ret.name,ret.father.name)

多對多,連表操作:

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

就是說在同一個會話中， 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不能查看到，可以理解提交并解鎖吧。

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

轉(zhuǎn)藏 分享

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

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

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

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

0條評論

發(fā)表

請遵守用戶評論公約

類似文章 更多

highoo

關(guān)注對話

TA的最新館藏

千位名醫(yī)總結(jié)：最簡單的養(yǎng)生法，竟是從1數(shù)到10
巧用DeepSeek做好股市里的波段交易
[轉(zhuǎn)] 神農(nóng)本草經(jīng)自學(xué)整理簡潔版全集圖片及word格式
[轉(zhuǎn)] 《神農(nóng)本草經(jīng)》全集在線閱讀
[轉(zhuǎn)] 陶弘景《本草經(jīng)集注》征引《淮南萬畢術(shù)》考
[轉(zhuǎn)] 神農(nóng)本草經(jīng)2，每天來背誦

喜歡該文的人也喜歡更多

熱門閱讀換一換

乡下人产国偷v产偷v自拍,国产午夜片在线观看,婷婷成人亚洲综合国产麻豆,久久综合给合久久狠狠狠9

【Python之路】第十九篇

pymsql

SQLAchemy