orm 框架

在python中可以通過寫類生成資料庫對應的表結構, 生成物件來為表加入資料, 這就是orm框架的本質思想. 其中sqlalchemy就是實現這種框架的一個python模組

---

使用sqlalchemy對單表進行操作 :

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

# 傳入資料庫名, 使用者名稱, 密碼以及ip, 建立連線資料庫的引擎engine(負責與資料庫驅動程式進行互動), 加入echo=True則會列印建立過程
engine = create_engine("mysql+pymysql://root:@127.0.0.1:3306/blog_db?charset=utf8")

# 通過繼承sqlalchemy生成的基類來建立與資料庫表對應的類(一個類在資料庫中表現為一張表)
Base = declarative_base()


# 自定義類, 本質就是設定表中欄位格式
class User(Base):
    # 設定表名, 固定格式
    __tablename__ = "users"

    # 預設就是自增長, 不加autoincrement=True引數也可
    id = Column(Integer, primary_key=True, autoincrement=True)
    # 建立name欄位的同時建立普通索引, 該欄位不能為空, String型別對應資料庫中的varchar欄位
    name = Column(String(32), nullable=True, index=True)
    pwd = Column(String(16))
    address = Column(String(16))

    # 建立聯合索引
    __table_args__ = (
        UniqueConstraint(name, pwd, name="n_unique_p"),
    )


# 根據已有的所有類來建立對應的表
Base.metadata.create_all(engine)

# 根據引擎建立mysql與程式之間的會話
MySession = sessionmaker(engine)
session = MySession()

# 設定插入的一條資料
u1 = User(name="test-01", pwd="111")
# 插入資料並提交
session.add(u1)
session.commit()

# 插入多條資料
session.add_all([
    User(name="test-02", pwd="222"),
    User(name="test-03", pwd="333")
])
session.commit()

# 查詢指定表的第一條資料
# 根據要查詢的表對應的類生成sql語句Query物件
sql_obj = session.query(User)
print(type(sql_obj), sql_obj)

# 執行查詢結果, 返回的是查詢的表對應的物件
result_first = sql_obj.first()
print("result_first ---> \n", result_first.name)

# 加入過濾條件查詢, filter中只能加入條件, filter_by只能及誒如鍵值對過濾, 返回表物件
result_filter = sql_obj.filter(User.name == "test-02").first()
print("result_filter --->\n", result_filter.name)

# 獲得所有查詢結果, 返回的是列表, 即使只有一個物件也不例外
result_all = sql_obj.all()
print("result_all --->\n", result_all)
for obj in result_all:
    print(obj.id, obj.name)

# 將查詢結果排序顯示
result_order = sql_obj.order_by(User.id)
print("result_order --->\n", result_order)
for obj in result_order:
    print(obj.id, obj.name)

# 統計查詢個數
result_num = sql_obj.count()
print("result_num --->\n", result_num)
print(result_num)

# 取反查詢
result_oth = sql_obj.filter(~User.name == "test-02").all()
print("result_oth --->\n", result_oth)
for obj in result_oth:
    print(obj.id, obj.name)

# 與或查詢
from sqlalchemy import or_

result_or = sql_obj.filter(or_(User.id == 1, User.id == 2)).all()
print("result_or --->\n", result_or)
for obj in result_or:
    print(obj.id, obj.name)

# 刪除指定條件的資料
session.query(User).filter(User.id > 2).delete()
session.commit()

# 修改指定條件的資料
session.query(User).filter(User.id == 1).update({"name": "test-up"})
session.commit()

# 關閉會話
session.close()複製程式碼

---

使用sqlalchemy關於一對多型別的表的操作:

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

engine = create_engine("mysql+pymysql://root:@127.0.0.1:3306/blog_db?charset=utf8")
Base = declarative_base()


class Father(Base):
    __tablename__ = "father"

    id = Column(Integer, primary_key=True, autoincrement=True)
    name = Column(String(16), nullable=True)


class Son(Base):
    __tablename__ = "son"

    id = Column(Integer, primary_key=True, autoincrement=True)
    name = Column(String(20))
    # 建立外來鍵
    son_id_father = Column(Integer, ForeignKey("father.id"))

    # 設定Son與Father之間的雙向關係, 第一個引數為類名, backref表示反向的名稱為son_name
    father_name = relationship("Father", backref="son_name")


# Base.metadata.create_all(engine)

MySession = sessionmaker(engine)
session = MySession()

# 為father設定資料
f1 = Father(name="peter")
f2 = Father(name="nick")

# 為son設定資料
s1 = Son(name="peter-son-01")
s2 = Son(name="peter-son-02")
s3 = Son(name="peter-son-03")
s4 = Son(name="nickr-son-01")

# 利用relationship設定f1, f2的對應關係
f1.son_name = [s1, s2, s3]
f2.son_name = [s4]

session.add_all([f1, f2])
session.commit()

# 聯表查詢
sql_obj = session.query(Father.name.label("father_name"), Son.name.label("son_name")).join(Son)
print(sql_obj)
result = sql_obj.all()
for obj in result:
    print(obj)
session.commit()複製程式碼

---

使用sqlalchemy關於多對多型別的表的操作:

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

engine = create_engine("mysql+pymysql://root:@127.0.0.1:3306/blog_db?charset=utf8")
Base = declarative_base()


class ManToBook(Base):
    __tablename__ = "mantobook"

    id = Column(Integer, primary_key=True, autoincrement=True)
    book_id = Column(Integer, ForeignKey("book.id"))
    man_id = Column(Integer, ForeignKey("man.id"))


class Book(Base):
    __tablename__ = "book"

    id = Column(Integer, primary_key=True, autoincrement=True)
    name = Column(String(16), nullable=True)

    # 建立Book與Man之間雙向的關係, secondary表示根據ManToBook確定其雙向的具體關係, backref表示反向的名稱為book_name
    man_name = relationship("Man", secondary=ManToBook.__table__, backref="book_name")


class Man(Base):
    __tablename__ = "man"

    id = Column(Integer, primary_key=True, autoincrement=True)
    name = Column(String(20))


Base.metadata.create_all(engine)

MySession = sessionmaker(engine)
session = MySession()

# 設定資料
b1 = Book(name="python")
b2 = Book(name="java")
b3 = Book(name="c#")

m1 = Man(name="lisa")
m2 = Man(name="peter")
m3 = Man(name="nick")

# 利用relationship設定對應關係
# python這本書同時有lisa, peter和nick在讀
b1.man_name = [m1, m2, m3]
# lisa 同時擁有python和java
m1.book_name = [b1, b2]

session.add_all([b1, m1])
session.commit()複製程式碼