Python 学习笔记 - SQLAlchemy(上)

最新推荐文章于 2021-03-01 00:38:30 发布

weixin_33725239

最新推荐文章于 2021-03-01 00:38:30 发布

阅读量116

点赞数

文章标签：数据库 python

原文链接：https://my.oschina.net/learnbo/blog/870732

版权

2019独角兽企业重金招聘Python工程师标准>>>

上一篇简单的介绍了MySQL的原生命令，以及如何使用pymysql模块在Python里面使用。这一节简单的介绍一下sqlAlchemy和他的ORM框架。ORM框架允许开发人员通过类的方法来操作数据库，而无需在使用原生的SQL语句。

例1. 单表的操作

#导入模块

from sqlalchemy import create_engine

from sqlalchemy.ext.declarative import declarative_base

from sqlalchemy import Column, Integer, String, ForeignKey, UniqueConstraint, Index

from sqlalchemy.orm import sessionmaker, relationship

#创建一个engine，基本格式是数据库类型名字+驱动：//用户名：密码@主机：端口/数据库

engine = create_engine("mysql+pymysql://yli:yli@sydnagios:3306/mydb", max_overflow=5)

#声明一个基类

Base = declarative_base()

# 定义一个类，继承基类

class Test(Base):

#表名

__tablename__ = 'test'

#列名nid，整型，主键，自增

nid = Column(Integer, primary_key=True,autoincrement=True)

#列名name，字符串型

name = Column(String(32))

#通过类创建表，本质其实是在通过pymysql执行sql语句

def init_db():

Base.metadata.create_all(engine)

#删除表

def drop_db():

Base.metadata.drop_all(engine)

init_db()

#绑定数据库

Session = sessionmaker(bind=engine)

session = Session()

#添加一条数据并提交

session.add(Test(name='apple'))

session.commit()

#查询Test表

ret=session.query(Test)

#自动转换成sql语句

print(ret)

#sql语句的结果，all()返回所有的对象的结果，以列表结果显示，first()只显示第一个结果

print(ret.first())

#显示这个对象的属性值

print(ret.first().nid,ret.first().name)

--------------

SELECT test.nid AS test_nid, test.name AS test_name

FROM test

<__main__.Test object at 0x0000021D709F9438>

1 apple

修改

1 2	`session.query(Test).filter(Test.nid==1).update({"name":"pear"})` `session.commit()`

删除

1 2	`session.query(Test).filter(Test.nid==1).delete()` `session.commit()`

例2. 1对多联表的查询

group表和user表之间通过group_id形成外键约束

#导入模块

from sqlalchemy import create_engine

from sqlalchemy.ext.declarative import declarative_base

from sqlalchemy import Column, Integer, String, ForeignKey, UniqueConstraint, Index

from sqlalchemy.orm import sessionmaker, relationship

engine = create_engine("mysql+pymysql://yli:yli@sydnagios:3306/mydb", max_overflow=5)

Base = declarative_base()

# 一对多

class Group(Base):

#表名

__tablename__ = 'group'

#列名

nid = Column(Integer, primary_key=True,autoincrement=True)

caption = Column(String(32))

class User(Base):

#表名

__tablename__ = 'user'

#列名

nid = Column(Integer, primary_key=True,autoincrement=True)

username = Column(String(32))

#外键约束

group_id = Column(Integer, ForeignKey('group.nid'))

#显示这个类的对象的时候不再显示内存地址，而是执行这个方法

def __repr__(self):

temp = "%s - %s: %s" %(self.nid, self.username, self.group_id)

return temp

def init_db():

Base.metadata.create_all(engine)

def drop_db():

Base.metadata.drop_all(engine)

init_db()

Session = sessionmaker(bind=engine)

session = Session()

#添加

session.add(Group(caption='dba'))

session.add(Group(caption='ddd'))

session.commit()

#批量添加

session.add_all([

User(username='alex1',group_id=1),

User(username='alex2',group_id=2)

])

session.commit()

# 只是获取用户表的内容，通过filter进行过滤

ret = session.query(User).filter(User.username == 'alex1')

#显示对应的sql语句

print(ret)

#显示对象，这里自动调用_repr_方法

print(ret.all())

#不过滤

ret = session.query(User).all()

#获取列表里面的第一个对象

obj = ret[0]

print(ret)

print(obj)

#获取这个对象属性值

print(obj.nid)

print(obj.username)

print(obj.group_id)

--------

SELECT user.nid AS user_nid, user.username AS user_username, user.group_id AS user_group_id

FROM user

WHERE user.username = %(username_1)s

[1 - alex1: 1, 3 - alex1: 1]

[1 - alex1: 1, 2 - alex2: 2, 3 - alex1: 1, 4 - alex2: 2]

1 - alex1: 1

1

alex1

1

在上面的基础上，如果我们希望执行联表查询，可以有两种方式。

第一种，通过join实现。修改一下上面的例子

例如

from sqlalchemy import create_engine

from sqlalchemy.ext.declarative import declarative_base

from sqlalchemy import Column, Integer, String, ForeignKey, UniqueConstraint, Index

from sqlalchemy.orm import sessionmaker, relationship

engine = create_engine("mysql+pymysql://yli:yli@sydnagios:3306/mydb", max_overflow=5)

Base = declarative_base()

# 一对多

class Group(Base):

__tablename__ = 'group'

nid = Column(Integer, primary_key=True,autoincrement=True)

caption = Column(String(32))

class User(Base):

__tablename__ = 'user'

nid = Column(Integer, primary_key=True,autoincrement=True)

username = Column(String(32))

group_id = Column(Integer, ForeignKey('group.nid'))

def __repr__(self):

temp = "%s - %s: %s" %(self.nid, self.username, self.group_id)

return temp

def init_db():

Base.metadata.create_all(engine)

def drop_db():

Base.metadata.drop_all(engine)

# init_db()

Session = sessionmaker(bind=engine)

session = Session()

#查询user表的username字段的所有信息

ret = session.query(User.username).all()

print(ret)

#left join的效果

sql = session.query(User,Group).join(Group, isouter=True)

print(sql)

ret = session.query(User,Group).join(Group, isouter=True).all()

print(ret)

-----------

"C:\Program Files\Python3\python.exe" C:/Users/yli/Downloads/a39dab3773523eacb8c8568b446bbcec580842/day13/s1.py

[('alex1',), ('alex2',), ('alex1',), ('alex2',), ('alex1',), ('alex2',), ('alex1',), ('alex2',)]

SELECT user.nid AS user_nid, user.username AS user_username, user.group_id AS user_group_id, `group`.nid AS group_nid, `group`.caption AS group_caption

FROM user LEFT OUTER JOIN `group` ON `group`.nid = user.group_id

[(1 - alex1: 1, <__main__.Group object at 0x0000027295723630>), (2 - alex2: 2, <__main__.Group object at 0x0000027295723710>), (3 - alex1: 1, <__main__.Group object at 0x0000027295723630>), (4 - alex2: 2, <__main__.Group object at 0x0000027295723710>), (5 - alex1: 1, <__main__.Group object at 0x0000027295723630>), (6 - alex2: 2, <__main__.Group object at 0x0000027295723710>), (7 - alex1: 1, <__main__.Group object at 0x0000027295723630>), (8 - alex2: 2, <__main__.Group object at 0x0000027295723710>)]

第二种，通过relation实现

from sqlalchemy import create_engine

from sqlalchemy.ext.declarative import declarative_base

from sqlalchemy import Column, Integer, String, ForeignKey, UniqueConstraint, Index

from sqlalchemy.orm import sessionmaker, relationship

engine = create_engine("mysql+pymysql://yli:yli@sydnagios:3306/mydb", max_overflow=5)

Base = declarative_base()

# 一对多

class Group(Base):

__tablename__ = 'group'

nid = Column(Integer, primary_key=True,autoincrement=True)

caption = Column(String(32))

class User(Base):

__tablename__ = 'user'

nid = Column(Integer, primary_key=True,autoincrement=True)

username = Column(String(32))

group_id = Column(Integer, ForeignKey('group.nid'))

#指定relationship，通过User找Group正向关系是‘Group’，通过Group找user是u，这个是个虚拟的关系，不影响表的实际结构

group=relationship('Group',backref='u')

def __repr__(self):

temp = "%s - %s: %s" %(self.nid, self.username, self.group_id)

return temp

def init_db():

Base.metadata.create_all(engine)

def drop_db():

Base.metadata.drop_all(engine)

# init_db()

Session = sessionmaker(bind=engine)

session = Session()

#正向的查询

ret=session.query(User).all()

print(ret)

for obj in ret:

print(obj.username,obj.group.caption)

#逆向的查询

ret=session.query(Group).filter(Group.caption=='dba').first()

print(ret.caption,ret.u)

for j in ret.u:

print(j.username)

--------------

[1 - alex1: 1, 2 - alex2: 2, 3 - alex1: 1, 4 - alex2: 2, 5 - alex1: 1, 6 - alex2: 2, 7 - alex1: 1, 8 - alex2: 2]