Python 操作 MySQL 之 pysql 與 ORM(轉載)

来源:http://www.cnblogs.com/gispathfinder/archive/2016/08/19/5787313.html
-Advertisement-
Play Games

本文針對 Python 操作 MySQL 主要使用的兩種方式講解： 原生模塊 pymsql ORM框架 SQLAchemy 本章內容： pymsql 執行 sql 增\刪\改\查 語句 pymsql 獲取查詢內容、獲取自增 ID pymsql 游標 pymsql 更改 fetch 數據類型 pyms... ...


本文針對 Python 操作 MySQL 主要使用的兩種方式講解：

  • 原生模塊 pymsql
  • ORM框架 SQLAchemy

本章內容：

  • pymsql 執行 sql 增\刪\改\查 語句
  • pymsql 獲取查詢內容、獲取自增 ID
  • pymsql 游標
  • pymsql 更改 fetch 數據類型
  • pymsql 利用 with 簡化操作
  • ORM 下載安裝
  • ORM 史上最全操作

一、pymsql

pymsql 是 Python 中操作 MySQL 的原生模塊，其使用方法和 MySQL 的SQL語句幾乎相同

1、下載安裝
pip3 install pymysql

 

2、執行SQL

執行 SQL 語句的基本語法：

需要註意的是：創建鏈接後，都由游標來進行與資料庫的操作，當然，拿到數據也靠游標

 

#!/usr/bin/env python
# -*- coding:utf-8 -*-
import pymysql
  
# 創建連接
conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='123', db='t1')
# 創建游標
cursor = conn.cursor()
  
# 執行SQL，並返回收影響行數
effect_row = cursor.execute("update hosts set host = '1.1.1.2'")
  
# 執行SQL，並返回受影響行數
#effect_row = cursor.execute("update hosts set host = '1.1.1.2' where nid > %s", (1,))
  
# 執行SQL，並返回受影響行數
#effect_row = 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()

 

3、獲取新創建數據自增ID

可以獲取到最新自增的ID，也就是最後插入的一條數據ID 

#!/usr/bin/env python
# -*- coding:utf-8 -*-
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
new_id = cursor.lastrowid
4、獲取查詢數據

獲取查詢數據的三種方式： 

#!/usr/bin/env python
# -*- coding:utf-8 -*-
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")
  
# 獲取第一行數據
row_1 = cursor.fetchone()
  
# 獲取前n行數據
# row_2 = cursor.fetchmany(3)

# 獲取所有數據
# row_3 = cursor.fetchall()
  
conn.commit()
cursor.close()
conn.close()

 

5、移動游標

操作都是靠游標，那對游標的控制也是必須的 

註：在fetch數據時按照順序進行，可以使用cursor.scroll(num,mode)來移動游標位置，如：

cursor.scroll(1,mode='relative')  # 相對當前位置移動
cursor.scroll(2,mode='absolute')  # 相對絕對位置移動

 

6、fetch數據類型

預設拿到的數據是小括弧，元祖類型，如果是字典的話會更方便操作，那方法來了： 

# 關於預設獲取的數據是元祖類型，如果想要或者字典類型的數據，即：

#!/usr/bin/env python
# -*- coding:utf-8 -*-
import pymysql
  
conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='123', db='t1')
  
# 游標設置為字典類型
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
r = cursor.execute("call p1()")
  
result = cursor.fetchone()
  
conn.commit()
cursor.close()
conn.close()
7、利用 with 自動關閉

每次連接資料庫都需要連接和關閉，啊，好多代碼，那麼方法又來了：

是不是很屌啊？ 

# 利用with定義函數

    @contextlib.contextmanager
    def mysql(self, host='127.0.0.1', port=3306, user='nick', passwd='', db='db1', charset='utf8'):
        self.conn = pymysql.connect(host=host, port=port, user=user, passwd=passwd, db=db, charset=charset)
        self.cuersor = self.conn.cursor(cursor=pymysql.cursors.DictCursor)

        try:
            yield self.cuersor
        finally:
            self.conn.commit()
            self.cuersor.close()
            self.conn.close()

# 執行
with mysql() as cuersor:
   print(cuersor)
   # 操作MySQL代碼塊

二、SQLAlchemy

SQLAlchemy 簡稱 ORM 框架，該框架建立在資料庫的 API 之上，使用關係對象映射來進行資料庫操作；

簡言之便是：將類對象轉換成 SQL 語句，然後使用數據 API 執行 SQL 語句並獲取執行結果。

1、下載安裝
pip3 install SQLAlchemy

 

image

需要註意了：SQLAlchemy 自己無法操作資料庫，必須結合 pymsql 等第三方插件，Dialect 用於和數據 API 進行交互，根據配置文件的不同調用不同的資料庫 API，從而實現對資料庫的操作，如：

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

 

2、內部處理

使用 Engine/ConnectionPooling/Dialect 進行資料庫操作，Engine使用ConnectionPooling連接資料庫，然後再通過Dialect執行SQL語句。 

#!/usr/bin/env python
# -*- coding:utf-8 -*-
from sqlalchemy import create_engine
  
  
engine = create_engine("mysql+pymysql://root:[email protected]:3306/t1", max_overflow=5)
  
# 執行SQL
# cur = engine.execute(
#     "INSERT INTO hosts (host, color_id) VALUES ('1.1.1.22', 3)"
# )
  
# 新插入行自增ID
# cur.lastrowid
  
# 執行SQL
# cur = engine.execute(
#     "INSERT INTO hosts (host, color_id) VALUES(%s, %s)",[('1.1.1.22', 3),('1.1.1.221', 3),]
# )
  
  
# 執行SQL
# cur = engine.execute(
#     "INSERT INTO hosts (host, color_id) VALUES (%(host)s, %(color_id)s)",
#     host='1.1.1.99', color_id=3
# )
  
# 執行SQL
# cur = engine.execute('select * from hosts')
# 獲取第一行數據
# cur.fetchone()
# 獲取第n行數據
# cur.fetchmany(3)
# 獲取所有數據
# cur.fetchall()
3、ORM功能使用

使用 ORM/Schema Type/SQL Expression Language/Engine/ConnectionPooling/Dialect 所有組件對數據進行操作。

根據類創建對象，對象轉換成SQL，執行SQL。

a、創建表
#!/usr/bin/env python
# -*- coding:utf-8 -*-
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String, ForeignKey, UniqueConstraint, Index
from sqlalchemy.orm import sessionmaker, relationship
from sqlalchemy import create_engine

# 指定字元集、最大連接池數
engine = create_engine("mysql+pymysql://root:[email protected]:3306/suoning4?charset=utf8", max_overflow=5)

Base = declarative_base()

# 創建單表
class Users(Base):
    # 表名
    __tablename__ = 'users'
    # 表欄位
    id = Column(Integer, primary_key=True)  # 主鍵、預設自增
    name = Column(String(32))
    extra = Column(String(16))

    __table_args__ = (
    UniqueConstraint('id', 'name', name='uix_id_name'), # 唯一索引
    Index('ix_id_name', 'name', 'extra'),   # 普通索引
    )

    def __repr__(self):
        # 查是輸出的內容格式，本質還是對象
        return "%s-%s" %(self.id, self.name)

# 一對多
class Favor(Base):
    __tablename__ = 'favor'
    nid = Column(Integer, primary_key=True)
    caption = Column(String(50), default='red', unique=True) # 預設值、唯一索引

    def __repr__(self):
        return "%s-%s" %(self.nid, self.caption)

class Person(Base):
    __tablename__ = 'person'
    nid = Column(Integer, primary_key=True)
    name = Column(String(32), index=True, nullable=True)
    favor_id = Column(Integer, ForeignKey("favor.nid"))
    # 與生成表結構無關，僅用於查詢方便
    favor = relationship("Favor", backref='pers')

# 多對多
class ServerToGroup(Base):
    # 關係表要放對應表上面，否則找不到
    __tablename__ = 'servertogroup'
    nid = Column(Integer, primary_key=True, autoincrement=True)
    server_id = Column(Integer, ForeignKey('server.id'))    # 外鍵
    group_id = Column(Integer, ForeignKey('group.id'))

    group = relationship("Group", backref='s2g')
    server = relationship("Server", backref='s2g')

class Group(Base):
    __tablename__ = 'group'
    id = Column(Integer, primary_key=True)
    name = Column(String(64), unique=True, nullable=False) # 不能為空
    port = Column(Integer, default=22)
    # group = relationship('Group',secondary=ServerToGroup,backref='host_list')


class Server(Base):
    __tablename__ = 'server'

    id = Column(Integer, primary_key=True, autoincrement=True) # 自增
    hostname = Column(String(64), unique=True, nullable=False)


def init_db():
    # 創建表
    Base.metadata.create_all(engine)

def drop_db():
    # 刪除表
    Base.metadata.drop_all(engine)

創建表

註：設置外檢的另一種方式 ForeignKeyConstraint(['other_id'], ['othertable.other_id'])

b、操作表

操作表那必須導入模塊，創建相應類，相應增\刪\改\查的語法，詳細見下code吧^^： 

#!/usr/bin/env python
# -*- coding:utf-8 -*-
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String, ForeignKey, UniqueConstraint, Index
from sqlalchemy.orm import sessionmaker, relationship
from sqlalchemy import create_engine

# 指定字元集、最大連接池數
engine = create_engine("mysql+pymysql://root:[email protected]:3306/suoning4?charset=utf8", max_overflow=5)

Base = declarative_base()

# 創建單表
class Users(Base):
    # 表名
    __tablename__ = 'users'
    # 表欄位
    id = Column(Integer, primary_key=True)  # 主鍵、預設自增
    name = Column(String(32))
    extra = Column(String(16))

    __table_args__ = (
    UniqueConstraint('id', 'name', name='uix_id_name'), # 唯一索引
    Index('ix_id_name', 'name', 'extra'),   # 普通索引
    )

    def __repr__(self):
        # 查是輸出的內容格式，本質還是對象
        return "%s-%s" %(self.id, self.name)

# 一對多
class Favor(Base):
    __tablename__ = 'favor'
    nid = Column(Integer, primary_key=True)
    caption = Column(String(50), default='red', unique=True) # 預設值、唯一索引

    def __repr__(self):
        return "%s-%s" %(self.nid, self.caption)

class Person(Base):
    __tablename__ = 'person'
    nid = Column(Integer, primary_key=True)
    name = Column(String(32), index=True, nullable=True)
    favor_id = Column(Integer, ForeignKey("favor.nid"))
    # 與生成表結構無關，僅用於查詢方便
    favor = relationship("Favor", backref='pers')

# 多對多
class ServerToGroup(Base):
    # 關係表要放對應表上面，否則找不到
    __tablename__ = 'servertogroup'
    nid = Column(Integer, primary_key=True, autoincrement=True)
    server_id = Column(Integer, ForeignKey('server.id'))    # 外鍵
    group_id = Column(Integer, ForeignKey('group.id'))

    group = relationship("Group", backref='s2g')
    server = relationship("Server", backref='s2g')

class Group(Base):
    __tablename__ = 'group'
    id = Column(Integer, primary_key=True)
    name = Column(String(64), unique=True, nullable=False) # 不能為空
    port = Column(Integer, default=22)
    # group = relationship('Group',secondary=ServerToGroup,backref='host_list')


class Server(Base):
    __tablename__ = 'server'

    id = Column(Integer, primary_key=True, autoincrement=True) # 自增
    hostname = Column(String(64), unique=True, nullable=False)


def init_db():
    # 創建表
    Base.metadata.create_all(engine)

def drop_db():
    # 刪除表
    Base.metadata.drop_all(engine)


# 先實例化sessionmaker類，Session對象加括弧執行類下的__call__方法，
# 得到session對象，所以session可以調用Session類下的add，add_all等方法
Session = sessionmaker(bind=engine) # 指定引擎
session = Session()
# 增

# 添加一條
obj = Users(name="張三", extra='三兒')
session.add(obj)
# 添加多條
session.add_all([
    Users(name="李四", extra='四兒'),
    Users(name="汪五", extra='五兒'),
])
# 提交
session.commit()
# 刪

session.query(Users).filter(Users.id > 2).delete()
session.query(Users).filter_by(id = 1).delete()
session.commit()
# 改

session.query(Users).filter(Users.id > 2).update({"name" : "nick"})
session.query(Users).filter(Users.id > 2).update({"name" : "nick", "extra":"niubily"})
session.query(Users).filter(Users.id > 2).update({Users.name: Users.name + "Suo"}, synchronize_session=False)
session.query(Users).filter(Users.id > 2).update({"num": Users.num + 1}, synchronize_session="evaluate")
session.commit()
# 查
# all()結果為對象列表，first()為具體對象

ret = session.query(Users).all()
ret = session.query(Users.name, Users.extra).all()
ret = session.query(Users).filter_by(name='nick').all()
ret = session.query(Users).filter_by(name='nick').first()
print(ret)

 

那如何加限制條件等，我要更靈活使用，好吧，還是見下 code：

#　條件
ret = session.query(Users).filter_by(name='nick').all()
ret = session.query(Users).filter(Users.id > 1, Users.name == 'nick').all()
ret = session.query(Users).filter(Users.id.between(1, 3), Users.name == 'nick').all()
ret = session.query(Users).filter(Users.id.in_([1,3,4])).all()
ret = session.query(Users).filter(~Users.id.in_([1,3,4])).all()
ret = session.query(Users).filter(Users.id.in_(session.query(Users.id).filter_by(name='nick'))).all()

from sqlalchemy import and_, or_        # 導入模塊
ret = session.query(Users).filter(and_(Users.id > 3, Users.name == 'nick')).all()
ret = session.query(Users).filter(or_(Users.id < 2, Users.name == 'nick')).all()
ret = session.query(Users).filter(
    or_(
        Users.id < 2,
        and_(Users.name == 'nick', Users.id > 3),
        Users.extra != ""
    )).all()


# 通配符
ret = session.query(Users).filter(Users.name.like('n%')).all()
ret = session.query(Users).filter(~Users.name.like('n%')).all()

# 限制
ret = session.query(Users)[1:2]

# 排序
ret = session.query(Users).order_by(Users.name.desc()).all()
ret = session.query(Users).order_by(Users.name.desc(), Users.id.asc()).all()

# 分組
from sqlalchemy.sql import func        # 導入模塊

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()

# 連表

ret = session.query(Users, Favor).filter(Users.id == Favor.nid).all()

ret = session.query(Person).join(Favor).all()

ret = session.query(Person).join(Favor, isouter=True).all()

# isouter=True 理解為 left join ，如果不寫為 inner join

# 組合
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()

您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • Android與單片機通信常用數據轉換方法（彙總）
    下麵直接貼代碼 1. 將GB2312轉化為中文,如BAFAC2DCB2B7→胡蘿蔔,兩個位元組合成一個文字 2.將中文轉化為GB2312,並且結果以byte[]形式返回,如胡蘿蔔→new byte[]{BA FA C2 DC B2 B7}，一個字被分為兩個位元組 3.將十六進位的byte[]原封不動的轉 ...
  • 整理sql server數據類型
    我們在平常開發過程中，在設計數據的時候，經常碰到數據類型選擇的問題，為了更快，更合適地選擇正確的數據類型，所以在這裡做個總結。 轉自：http://www.cnblogs.com/mcgrady/p/5776255.html ...
  • SQL優化 1
    fvdwtfv18yy0m 士大夫士大夫 撒發順豐 select name,DATATYPE_STRING,VALUE_STRING from v$sql_bind_capture where sql_id='fvdwtfv18yy0m'; SELECT TABLE_NAME,COLUMN_NAME ...
  • 如何解讀SQL Server日誌(2/3)
    接下來說說返回的RowLogo Content列，例子中返回了三個列。這些列包含了數據操作的“有效工作負載(Playload)”記錄。根據不同操作類型有效負載的內容也是不同的，但是它必須包含足夠的信息，能讓相應的數據操作在恢復時能被REDO和UNDO。對於每一個INSERT而言，它包含了插入行的所有 ...
  • get_locked_objects_rpt.sql
    在metalink上看到一個腳本（get_locked_objects_rpt.sql），非常不錯，如下所示 /*----------------------------------------------------------------------------+ | MODULE: get_l... ...
  • 如何在刪除ibdata1和ib_logfile的情況下恢復MySQL資料庫
    昨天，有個朋友對公司內部使用的一個MySQL實例開啟binlog，但是在啟動的過程中失敗了（他也沒提，為何會失敗），在啟動失敗後，他刪除了ibdata1和ib_logfile，後來，能正常啟動了，但所有的表通過show tables能看到，但是select的過程中卻報“Table doesn't e ...
  • 高級SQL運用
    一:什麼是資料庫設計? 資料庫設計就是將資料庫中的數據實體以及這些數據實體之間的關係,進行規範和結構化的過程. 二:為什麼要實施資料庫設計? 1:良好的資料庫設計可以有效的解決數據冗餘的問題 2:效率高 3:便於進一步擴展 4:使得應用程式開發變得容易 三:設計資料庫的步驟 第一步 需求分析: 分析 ...
  • 【騰訊Bugly乾貨分享】移動客戶端中高效使用SQLite
    這篇文章主要從 SQLite 資料庫的使用入手，介紹如何合理、高效、便捷的將這個桌面資料庫和 App 全面結合。避免 App 開發過程中可能遇到的坑，也提供一些在開發過程中通過大量實踐和數據對比後總結出的一些參數設置。整篇文章將以一個個具體的技術點作為講解單元，從 SQLite 資料庫生命周期起始講... ...
一周排行
    -Advertisement-
    Play Games
  • 移動開發（一）：使用.NET MAUI開發第一個安卓APP
    移動開發（一）：使用.NET MAUI開發第一個安卓APP 對於工作多年的C#程式員來說，近來想嘗試開發一款安卓APP，考慮了很久最終選擇使用.NET MAUI這個微軟官方的框架來嘗試體驗開發安卓APP，畢竟是使用Visual Studio開發工具，使用起來也比較的順手，結合微軟官方的教程進行了安卓 ...
  • wpf ToggleButton選中效果和一個登錄界面
    前言 QuestPDF 是一個開源 .NET 庫，用於生成 PDF 文檔。使用了C# Fluent API方式可簡化開發、減少錯誤並提高工作效率。利用它可以輕鬆生成 PDF 報告、發票、導出文件等。 項目介紹 QuestPDF 是一個革命性的開源 .NET 庫，它徹底改變了我們生成 PDF 文檔的方 ...
  • 彈幕樹洞項目功能新增篇
    項目地址 項目後端地址： https://github.com/ZyPLJ/ZYTteeHole 項目前端頁面地址： ZyPLJ/TreeHoleVue (github.com) https://github.com/ZyPLJ/TreeHoleVue 目前項目測試訪問地址： http://tree ...
  • 第27篇 sqlserver2022詳細安裝步驟
    話不多說，直接開乾 一.下載 1.官方鏈接下載： https://www.microsoft.com/zh-cn/sql-server/sql-server-downloads 2.在下載目錄中找到下麵這個小的安裝包 SQL2022-SSEI-Dev.exe，運行開始下載SQL server； 二. ...
  • .NET 開源高性能 MQTT 類庫
    前言 隨著物聯網（IoT）技術的迅猛發展，MQTT（消息隊列遙測傳輸）協議憑藉其輕量級和高效性，已成為眾多物聯網應用的首選通信標準。 MQTTnet 作為一個高性能的 .NET 開源庫，為 .NET 平臺上的 MQTT 客戶端與伺服器開發提供了強大的支持。 本文將全面介紹 MQTTnet 的核心功能 ...
  • Serilog文檔翻譯系列（六） - 可用的接收器、增強器、格式化輸出
    Serilog支持多種接收器用於日誌存儲，增強器用於添加屬性，LogContext管理動態屬性，支持多種輸出格式包括純文本、JSON及ExpressionTemplate。還提供了自定義格式化選項，適用於不同需求。 ...
  • 警惕 Visual Studio 屬性求值副作用導致邏輯不符合預期
    目錄簡介獲取 HTML 文檔解析 HTML 文檔測試參考文章 簡介 動態內容網站使用 JavaScript 腳本動態檢索和渲染數據，爬取信息時需要模擬瀏覽器行為，否則獲取到的源碼基本是空的。 本文使用的爬取步驟如下： 使用 Selenium 獲取渲染後的 HTML 文檔 使用 HtmlAgility ...
  • [使用目前最新版]HybridCLR6.9.0+YooAsset2.2.4實現純C# Unity熱更新方案 (一)
    1.前言 什麼是熱更新 游戲或者軟體更新時，無需重新下載客戶端進行安裝，而是在應用程式啟動的情況下，在內部進行資源或者代碼更新 Unity目前常用熱更新解決方案 HybridCLR，Xlua，ILRuntime等 Unity目前常用資源管理解決方案 AssetBundles，Addressable， ...
  • 在 ASP.NET Core Web API 中使用操作篩選器統一處理通用操作
    本文章主要是在C# ASP.NET Core Web API框架實現向手機發送驗證碼簡訊功能。這裡我選擇是一個互億無線簡訊驗證碼平臺，其實像阿裡雲，騰訊雲上面也可以。 首先我們先去 互億無線 https://www.ihuyi.com/api/sms.html 去註冊一個賬號 註冊完成賬號後，它會送 ...
  • 第28篇 如何.net中實現高效可靠數據同步api
    通過以下方式可以高效，並保證數據同步的可靠性 1.API設計 使用RESTful設計，確保API端點明確，並使用適當的HTTP方法（如POST用於創建，PUT用於更新）。 設計清晰的請求和響應模型，以確保客戶端能夠理解預期格式。 2.數據驗證 在伺服器端進行嚴格的數據驗證，確保接收到的數據符合預期格 ...
所有分類