Python数据库编程

2019年1月4日09:18:23 发表评论 604 views

第1章 Pymysql模块

1.1 Pymysql模块介绍

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

1.2 安装Pymysql

1.2.1 命令行安装

pip3 install pymysql

1.2.2 PyCharm安装

Python数据库编程

1.3 使用方法

1.3.1 连接数据库

【语法】：

import pymysql

db = pymysql.connect("数据库ip","用户","密码","数据库" )    # 打开数据库连接
cursor.execute("SELECT VERSION()")                          # 使用 execute() 方法执行 SQL 查询
data = cursor.fetchone()                                    # 使用 fetchone() 方法获取单条数据
print ("Database version : %s " % data)
db.close()                                                  # 关闭数据库连接

【更多参数】：
import pymysql

conn = pymysql.connect(
        host='localhost', user='root', password="root",
        database='db', port=3306, charset='utf-8',
)

cur = conn.cursor(cursor=pymysql.cursors.DictCursor)        # 获取字典类型的查询结果

1.3.2 数据库操作

【示例】：创建表

import pymysql

# 打开数据库连接
db = pymysql.connect("localhost","testuser","test123","TESTDB" )

# 使用 cursor() 方法创建一个游标对象 cursor
cursor = db.cursor()

# 使用 execute() 方法执行 SQL，如果表存在则删除
cursor.execute("DROP TABLE IF EXISTS EMPLOYEE")

# 使用预处理语句创建表
sql = """CREATE TABLE EMPLOYEE (
         FIRST_NAME  CHAR(20) NOT NULL,
         LAST_NAME  CHAR(20),
         AGE INT, 
         SEX CHAR(1),
         INCOME FLOAT )"""

cursor.execute(sql)

# 关闭数据库连接
db.close()

【示例】：插入操作
import pymysql

# 打开数据库连接
db = pymysql.connect("localhost","testuser","test123","TESTDB" )

# 使用cursor()方法获取操作游标
cursor = db.cursor()

# SQL 插入语句：单条插入
sql = """INSERT INTO EMPLOYEE(FIRST_NAME,
         LAST_NAME, AGE, SEX, INCOME)
         VALUES ('Mac', 'Mohan', 20, 'M', 2000)"""
# SQL 插入语句：多条插入
sql = "INSERT INTO EMPLOYEE(FIRST_NAME, \
       LAST_NAME, AGE, SEX, INCOME) \
       VALUES (%s, %s,  %s,  %s,  %s )" % \
       ('Mac', 'Mohan', 20, 'M', 2000)
try:
   cursor.execute(sql) # 执行sql语句
   db.commit()         # 提交到数据库执行
except:
   db.rollback()       # 如果发生错误则回滚

# 关闭数据库连接
db.close()

【示例】：查询操作
import pymysql

# 打开数据库连接
db = pymysql.connect("localhost","testuser","test123","TESTDB" )

# 使用cursor()方法获取操作游标
cursor = db.cursor()

# SQL 查询语句
sql = "SELECT * FROM EMPLOYEE \
       WHERE INCOME > %s" % (1000)
try:
   cursor.execute(sql)          # 执行SQL语句
   results = cursor.fetchall()  # 获取所有记录列表
   for row in results:
      fname = row[0]
      lname = row[1]
      age = row[2]
      sex = row[3]
      income = row[4]
       # 打印结果
      print ("fname=%s,lname=%s,age=%s,sex=%s,income=%s" % \
             (fname, lname, age, sex, income ))
except:
   print ("Error: unable to fetch data")

# 关闭数据库连接
db.close()

【解析】：

Python查询Mysql使用 fetchone() 方法获取单条数据, 使用fetchall() 方法获取多条数据。

  • fetchone(): 该方法获取下一个查询结果集。结果集是一个对象
  • fetchall(): 接收全部的返回结果行.
  • rowcount: 这是一个只读属性，并返回执行execute()方法后影响的行数

【示例】：更新操作

import pymysql

# 打开数据库连接
db = pymysql.connect("localhost","testuser","test123","TESTDB" )

# 使用cursor()方法获取操作游标
cursor = db.cursor()

# SQL 更新语句
sql = "UPDATE EMPLOYEE SET AGE = AGE + 1 WHERE SEX = '%c'" % ('M')
try:
   cursor.execute(sql)  # 执行SQL语句
   db.commit()          # 提交到数据库执行
except
   db.rollback()        # 发生错误时回滚

# 关闭数据库连接
db.close()

【示例】：删除操作
import pymysql

# 打开数据库连接
db = pymysql.connect("localhost","testuser","test123","TESTDB" )

# 使用cursor()方法获取操作游标
cursor = db.cursor()

# SQL 删除语句
sql = "DELETE FROM EMPLOYEE WHERE AGE > %s" % (20)
try
   cursor.execute(sql)  # 执行SQL语句
   db.commit()          # 提交修改
except
   db.rollback()        # 发生错误时回滚

# 关闭连接
db.close()

1.3.3 执行SQL语句示例

【示例】：

#!/usr/bin/env python
# !-*- coding:utf-8 -*-

import pymysql

# 创建连接
conn = pymysql.connect(host='localhost', port=3306, user='root', passwd='root', db='test')

# 创建游标
cursor = conn.cursor()

# 执行SQL，并返回收影响行数
effect_row_insert = cursor.executemany("insert into in1(nid,name,email,extra)VALUES(%s,%s,%s,%s)",[(1,"老大","[email protected]","abc"),(2,"老二","[email protected]","def"),(3,"老三","[email protected]","ghi")])
effect_row_select = cursor.execute("select * from in1")

# 提交，不然无法保存新建或者修改的数据
conn.commit()

# 关闭游标
cursor.close()
# 关闭连接
conn.close()

# 获取最新自增ID
new_id = cursor.lastrowid

print(effect_row_insert,effect_row_select)
print(new_id)

【运行结果】：
# PyChram返回结果
3 3
3
# 数据库查询结果
mysql> select * from in1;
+-----+--------+-----------------+-------+
| nid | name   | email           | extra |
+-----+--------+-----------------+-------+
|   1 | 老大   | [email protected]  | abc   |
|   2 | 老二   | [email protected]  | def   |
|   3 | 老三   | [email protected] | ghi   |
+-----+--------+-----------------+-------+
3 rows in set (0.00 sec)

1.3.4 获取查询结果示例

【示例】：获取元组类型结果

#!/usr/bin/env python
# !-*- coding:utf-8 -*-

import pymysql

conn = pymysql.connect(host='localhost', port=3306, user='root', passwd='root', db='test')
cursor = conn.cursor()
cursor.execute("select * from in1")

# 获取第一行数据
row_1 = cursor.fetchone()
# 获取前n行数据
row_2 = cursor.fetchmany(2)
# 获取所有数据
row_3 = cursor.fetchall()

conn.commit()
cursor.close()
conn.close()

print(row_1)
print(row_2)
print(row_3)

【运行结果】：
(1, '老大', '[email protected]', 'abc')
((2, '老二', '[email protected]', 'def'), (3, '老三', '[email protected]', 'ghi'))
((4, '老四', '[email protected]', 'jkl'), (5, '老五', '[email protected]', 'mno'))

Tips：在fetch数据时按照顺序进行，可以使用cursor.scroll(num,mode)来移动游标位置，如：
cursor.scroll(1,mode='relative') # 相对当前位置移动
cursor.scroll(2,mode='absolute') # 相对绝对位置移动

【示例】：获取字典类型结果

import pymysql

conn = pymysql.connect(host='localhost', port=3306, user='root', passwd='root', db='test')
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
cursor.execute("select * from in1")

row_1 = cursor.fetchall()

conn.commit()
cursor.close()
conn.close()

print(row_1)

【运行结果】：
[{'nid': 1, 'name': '老大', 'email': '[email protected]', 'extra': 'abc'}, {'nid': 2, 'name': '老二', 'email': '[email protected]', 'extra': 'def'}, {'nid': 3, 'name': '老三', 'email': '[email protected]', 'extra': 'ghi'}, {'nid': 4, 'name': '老四', 'email': '[email protected]', 'extra': 'jkl'}, {'nid': 5, 'name': '老五', 'email': '[email protected]', 'extra': 'mno'}]

Tips：字典类型的查询结果同时会显示表的列名，方便后面程序的调用。

第2章 ORM框架之SQLAlchemy

2.1 SQLAlchemy介绍

SQLAlchemy是Python编程语言下的一款ORM框架，该框架建立在数据库API之上，使用关系对象映射进行数据库操作，简言之便是：将对象转换成SQL，然后使用数据API执行SQL并获取执行结果。

Python数据库编程

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:[email protected]:port/dbname[ key=value&key=value...]

Tips：更多详见：http://docs.sqlalchemy.org/en/latest/dialects/index.html

2.2 安装SQLAchemy

2.2.1 命令行安装

pip3 install SQLAlchemy

2.2.2 PyCharm安装

Python数据库编程

2.3 内部处理使用方法

使用 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/test", max_overflow=5)

# 执行SQL
cur = engine.execute("insert into in1 (nid,name,email,extra) VALUES (6,'老六','[email protected]','pqr')")
cur = engine.execute("insert into in1(nid,name,email,extra)VALUES(%s,%s,%s,%s)",
                     [(7,"老七","[email protected]","stu"),
                      (8,"老八","[email protected]","vwx")])
cur = engine.execute("insert into in1(nid,name,email,extra)VALUES(%(nid)s,%(name)s,%(email)s,%(extra)s)",
                     nid='9',name='老九',email='[email protected]',extra='yz')

# 新插入行自增ID
new_id = cur.lastrowid

cur = engine.execute('select * from in1')

# 获取数据
row_1 = cur.fetchone()
row_2 = cur.fetchmany(3)
row_3 = cur.fetchall()

print(new_id)
print(row_1)
print(row_2)
print(row_3)

【执行结果】：
9
(1, '老大', '[email protected]', 'abc')
[(2, '老二', '[email protected]', 'def'), (3, '老三', '[email protected]', 'ghi'), (4, '老四', '[email protected]', 'jkl')]
[(5, '老五', '[email protected]', 'mno'), (6, '老六', '[email protected]', 'pqr'), (7, '老七', '[email protected]', 'stu'), (8, '老八', '[email protected]', 'vwx'), (9, '老九', '[email protected]', 'yz')]

2.4 ORM功能使用

使用 ORM/Schema Type/SQL Expression Language/Engine/ConnectionPooling/Dialect 所有组件对数据进行操作。根据类创建对象，对象转换成SQL，执行SQL。

2.4.1 创建表结构

【示例】：

#!/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

# 固定格式
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)

Tips：在面向对象编程中，类相当于数据库的“表”；对象相当于“表中的行”。

2.4.2 数据库操作

【示例】：

################ 创建连接数据库 ###############
# echo=True打印输出执行原始SQL的语句
engine = create_engine("mysql+pymysql://root:[email protected]:3306/test", max_overflow=5 ,echo=True)
Base.metadata.create_all(engine) # 执行此句后创建表结构

Session = sessionmaker(bind=engine)
session = Session()

################ 数据库操作-增 ###############
obj = Users(name="leonshadow", extra='abc')
session.add(obj)
session.add_all([
    Users(name="leon", extra='def'),
    Users(name="shadow", extra='ghi'),
])
session.commit()

################ 数据库操作-查 ###############
ret_1 = session.query(Users).all()
ret_2 = session.query(Users.name, Users.extra).all()
ret_3 = session.query(Users).filter_by(name='leon').all()
ret_4 = session.query(Users).filter_by(name='leon').first()
# ret_5 = session.query(Users).filter(text("id<:value and name=:name")).params(value=2, name='leon').order_by(User.id).all()
# ret_6 = session.query(Users).from_statement(text("SELECT * FROM users where name=:name")).params(name='leon').all()
print(ret_1)
print(ret_2)
print(ret_3)
print(ret_4)

【运行结果】：
[2-leon, 1-leonshadow, 3-shadow]
[('leon', 'def'), ('leonshadow', 'abc'), ('shadow', 'ghi')]
[2-leon]
2-leon

【示例】：
################ 数据库操作-改 ###############
session.query(Users).filter(Users.id > 2).update({"name" : "666"})
# UPDATE users SET name=%(name)s WHERE users.id > %(id_1)s
# {'name': '666', 'id_1': 2}

session.query(Users).filter(Users.id > 2).update({Users.name: Users.name + "888"}, synchronize_session=False)
# UPDATE users SET name=(concat(users.name, %(name_1)s)) WHERE users.id > %(id_1)s
# {'name_1': '888', 'id_1': 2}

# session.query(Users).filter(Users.id > 2).update({"extra": Users.extra + 1}, synchronize_session="evaluate")  # 以数字方式相加
# UPDATE users SET extra=(users.extra + %(extra_1)s) WHERE users.id > %(id_1)s
# {'extra_1': 1, 'id_1': 2}
session.commit()

################ 数据库操作-查 ###############
ret_1 = session.query(Users).all()
# SELECT users.id AS users_id, users.name AS users_name, users.extra AS users_extra
# FROM users
# {}

ret_2 = session.query(Users.name, Users.extra).all()
# SELECT users.name AS users_name, users.extra AS users_extra
# FROM users
# {}

ret_3 = session.query(Users).filter_by(name='leon').all()
# SELECT users.id AS users_id, users.name AS users_name, users.extra AS users_extra
# FROM users
# WHERE users.name = %(name_1)s
# {'name_1': 'leon'}

ret_4 = session.query(Users).filter_by(name='leon').first()
# SELECT users.id AS users_id, users.name AS users_name, users.extra AS users_extra
# FROM users
# WHERE users.name = %(name_1)s
#  LIMIT %(param_1)s
# {'name_1': 'leon', 'param_1': 1}

print(ret_1)
print(ret_2)
print(ret_3)
print(ret_4)

【运行结果】：
[3-666888, 2-leon, 1-leonshadow]
[('666888', 'ghi'), ('leon', 'def'), ('leonshadow', 'abc')]
[2-leon]
2-leon

【示例】：
################ 数据库操作-删 ###############
session.query(Users).filter(Users.id > 2).delete()
# DELETE FROM users WHERE users.id > %(id_1)s
# {'id_1': 2}
session.commit()

ret_1 = session.query(Users).all()
print(ret_1)

【运行结果】：
[2-leon, 1-leonshadow]

2.4.3 其他查询操作

【示例】：条件查询

# 条件查询，filter内部默认为and条件
ret = session.query(Users).filter_by(name='leon').all()
print(session.query(Users).filter_by(name='leon'))
print(ret)
# SELECT users.id AS users_id, users.name AS users_name, users.extra AS users_extra
# FROM users
# WHERE users.name = %(name_1)s
# [2-leon, 5-leon]

ret = session.query(Users).filter(Users.id > 1, Users.name == 'leon').all()
print(session.query(Users).filter(Users.id > 1, Users.name == 'leon'))
print(ret)
# SELECT users.id AS users_id, users.name AS users_name, users.extra AS users_extra
# FROM users
# WHERE users.id > %(id_1)s AND users.name = %(name_1)s
# [2-leon, 5-leon]

ret = session.query(Users).filter(Users.id.between(1, 3), Users.name == 'leon').all()
print(session.query(Users).filter(Users.id.between(1, 3), Users.name == 'leon'))
print(ret)
# SELECT users.id AS users_id, users.name AS users_name, users.extra AS users_extra
# FROM users
# WHERE users.id BETWEEN %(id_1)s AND %(id_2)s AND users.name = %(name_1)s
# [2-leon]

ret = session.query(Users).filter(Users.id.in_([1,3,4])).all()
print(session.query(Users).filter(Users.id.in_([1,3,4])))
print(ret)
# SELECT users.id AS users_id, users.name AS users_name, users.extra AS users_extra
# FROM users
# WHERE users.id IN (%(id_1)s, %(id_2)s, %(id_3)s)
# [1-leonshadow, 4-leonshadow]

ret = session.query(Users).filter(~Users.id.in_([1,3,4])).all()
print(session.query(Users).filter(~Users.id.in_([1,3,4])))
print(ret)
# SELECT users.id AS users_id, users.name AS users_name, users.extra AS users_extra
# FROM users
# WHERE users.id NOT IN (%(id_1)s, %(id_2)s, %(id_3)s)
# [2-leon, 5-leon, 6-shadow]

ret = session.query(Users).filter(Users.id.in_(session.query(Users.id).filter_by(name='leon'))).all()
print(session.query(Users).filter(Users.id.in_(session.query(Users.id).filter_by(name='leon'))))
print(ret)
# SELECT users.id AS users_id, users.name AS users_name, users.extra AS users_extra
# FROM users
# WHERE users.id IN (SELECT users.id AS users_id
# FROM users
# WHERE users.name = %(name_1)s)
# [2-leon, 5-leon]

# 使用and或or查询
from sqlalchemy import and_, or_
ret = session.query(Users).filter(and_(Users.id > 2, Users.name == 'leon')).all()
print(session.query(Users).filter(and_(Users.id > 2, Users.name == 'leon')))
print(ret)
# SELECT users.id AS users_id, users.name AS users_name, users.extra AS users_extra
# FROM users
# WHERE users.id > %(id_1)s AND users.name = %(name_1)s
# [5-leon]

from sqlalchemy import and_, or_
ret = session.query(Users).filter(or_(Users.id < 4, Users.name == 'leon')).all()
print(session.query(Users).filter(or_(Users.id < 4, Users.name == 'leon')))
print(ret)
# SELECT users.id AS users_id, users.name AS users_name, users.extra AS users_extra
# FROM users
# WHERE users.id < %(id_1)s OR users.name = %(name_1)s
# [2-leon, 5-leon, 1-leonshadow]

from sqlalchemy import and_, or_
ret = session.query(Users).filter(
    or_(
        Users.id < 2,
        and_(Users.name == 'leon', Users.id > 3),
        Users.extra != ""
    )).all()
print(session.query(Users).filter(
    or_(
        Users.id < 2,
        and_(Users.name == 'leon', Users.id > 3),
        Users.extra != ""
    )))
print(ret)
# SELECT users.id AS users_id, users.name AS users_name, users.extra AS users_extra
# FROM users
# WHERE users.id < %(id_1)s OR users.name = %(name_1)s AND users.id > %(id_2)s OR users.extra != %(extra_1)s
# [2-leon, 5-leon, 1-leonshadow, 4-leonshadow, 6-shadow]

【示例】：通配符查询
ret = session.query(Users).filter(Users.name.like('l%')).all()
print(session.query(Users).filter(Users.name.like('l%')))
print(ret)
# SELECT users.id AS users_id, users.name AS users_name, users.extra AS users_extra
# FROM users
# WHERE users.name LIKE %(name_1)s
# [2-leon, 5-leon, 1-leonshadow, 4-leonshadow]

ret = session.query(Users).filter(~Users.name.like('l%')).all()
print(session.query(Users).filter(~Users.name.like('l%')))
print(ret)
# SELECT users.id AS users_id, users.name AS users_name, users.extra AS users_extra
# FROM users
# WHERE users.name NOT LIKE %(name_1)s
# [6-shadow]

【示例】：限制查询
ret = session.query(Users)[1:2]
print(ret)
# SELECT users.id AS users_id, users.name AS users_name, users.extra AS users_extra
# FROM users
#  LIMIT %(param_1)s, %(param_2)s
# {'param_1': 1, 'param_2': 1}
# [5-leon]

【示例】：排序查询
ret = session.query(Users).order_by(Users.name.desc()).all()
print(session.query(Users).order_by(Users.name.desc()))
print(ret)
# SELECT users.id AS users_id, users.name AS users_name, users.extra AS users_extra
# FROM users ORDER BY users.name DESC
# [6-shadow, 4-leonshadow, 1-leonshadow, 5-leon, 2-leon]

ret = session.query(Users).order_by(Users.name.desc(), Users.id.asc()).all()
print(session.query(Users).order_by(Users.name.desc(), Users.id.asc()))
print(ret)
# SELECT users.id AS users_id, users.name AS users_name, users.extra AS users_extra
# FROM users ORDER BY users.name DESC, users.id ASC
# [6-shadow, 1-leonshadow, 4-leonshadow, 2-leon, 5-leon]

【示例】：分组查询
ret = session.query(Users).group_by(Users.extra).all()
print(session.query(Users).group_by(Users.extra))
print(ret)
# SELECT users.id AS users_id, users.name AS users_name, users.extra AS users_extra
# FROM users GROUP BY users.extra
# [1-leonshadow, 2-leon, 6-shadow]

from sqlalchemy.sql import func
ret = session.query(
    func.max(Users.id),
    func.sum(Users.id),
    func.min(Users.id)).group_by(Users.name).all()
print(session.query(
    func.max(Users.id),
    func.sum(Users.id),
    func.min(Users.id)).group_by(Users.name))
print(ret)
# SELECT max(users.id) AS max_1, sum(users.id) AS sum_1, min(users.id) AS min_1
# FROM users GROUP BY users.name
# [(5, Decimal('7'), 2), (4, Decimal('5'), 1), (6, Decimal('6'), 6)]

from sqlalchemy.sql import func
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()
print(session.query(
    func.max(Users.id),
    func.sum(Users.id),
    func.min(Users.id)).group_by(Users.name).having(func.min(Users.id) >2))
print(ret)
# SELECT max(users.id) AS max_1, sum(users.id) AS sum_1, min(users.id) AS min_1
# FROM users GROUP BY users.name
# HAVING min(users.id) > %(min_2)s
# [(6, Decimal('6'), 6)]

【示例】：连表操作
ret = session.query(Users, Favor).filter(Users.id == Favor.nid).all()
print(session.query(Users, Favor).filter(Users.id == Favor.nid))
print(ret)
# SELECT users.id AS users_id, users.name AS users_name, users.extra AS users_extra, favor.nid AS favor_nid, favor.caption AS favor_caption
# FROM users, favor
# WHERE users.id = favor.nid
# []

ret = session.query(Person).join(Favor).all()
print(session.query(Person).join(Favor))
print(ret)
# SELECT person.nid AS person_nid, person.name AS person_name, person.favor_id AS person_favor_id
# FROM person INNER JOIN favor ON favor.nid = person.favor_id
# []

ret = session.query(Person).join(Favor, isouter=True).all()
print(session.query(Person).join(Favor, isouter=True))
print(ret)
# SELECT person.nid AS person_nid, person.name AS person_name, person.favor_id AS person_favor_id
# FROM person LEFT OUTER JOIN favor ON favor.nid = person.favor_id
# []

【示例】：组合查询
q1 = session.query(Users.name).filter(Users.id > 2)
q2 = session.query(Favor.caption).filter(Favor.nid < 2)
ret = q1.union(q2).all()
print(q1.union(q2))
print(ret)
# SELECT anon_1.users_name AS anon_1_users_name
# FROM (SELECT users.name AS users_name
# FROM users
# WHERE users.id > %(id_1)s UNION SELECT favor.caption AS favor_caption
# FROM favor
# WHERE favor.nid < %(nid_1)s) AS anon_1
# [('leonshadow',), ('leon',), ('shadow',)]

q1 = session.query(Users.name).filter(Users.id > 2)
q2 = session.query(Favor.caption).filter(Favor.nid < 2)
ret = q1.union_all(q2).all()
print(q1.union_all(q2))
print(ret)
# SELECT anon_1.users_name AS anon_1_users_name
# FROM (SELECT users.name AS users_name
# FROM users
# WHERE users.id > %(id_1)s UNION ALL SELECT favor.caption AS favor_caption
# FROM favor
# WHERE favor.nid < %(nid_1)s) AS anon_1
# [('leonshadow',), ('leon',), ('shadow',)]

weinxin
我的微信
如果有技术上的问题可以扫一扫我的微信
版权声明
1. 本网站名称：Leon的博客
2. 本站永久网址：https://blog.leonshadow.cn
3. 本网站的文章部分内容可能来源于网络，仅供大家学习与参考，如有侵权，请联系站长QQ632113590进行删除处理。
4. 本站一切资源不代表本站立场，并不代表本站赞同其观点和对其真实性负责。
5. 本站一律禁止以任何方式发布或转载任何违法的相关信息，访客发现请向站长举报
6. 本站资源大多存储在云盘，如发现链接失效，请联系我们我们会第一时间更新。
liyang