python处理数据库工具sqlalchemy
发布于 2021年 07月 02日 17:06
有价值的参考文档: http://www.jianshu.com/p/e6bba189fcbd
官方参考网址: http://docs.sqlalchemy.org/en/rel_0_7/orm/relationships.html
安装sqlalchemy
pip install sqlalchemy
数据库基本操作
数据库连接
使用create_engine
创建数据库连接; 例子:
from sqlalchemy import create_engine engine = create_engine('mysql+mysqlconnector://root:password@localhost:3306/test') connect = engine.connect()
创建表
方法一:
from sqlalchemy import Table, MetaData, create_engine engine = create_engine("mysql+mysqlconnector://root:password@localhost:3306/test") metadata = MetaData(bind=engine) t1 = Table('users', metadata, Column('id',INT, primary_key=True), Column('name', String(20)), Column('fullname', String(50)), Column('password', String(20)) ) t2 = Table('address', metadata, Column('id',INT, primary_key = True), Column('email_address',String(50), nullable=False), Column('user_id', INT, ForeignKey('users.id')) ) t1.create() t2.create()
方法二
from sqlalchemy import Table, MetaData, create_engine engine = create_engine("mysql+mysqlconnector://root:password@localhost:3306/test") metadata = MetaData() t1 = Table('users', metadata, Column('id',INT, primary_key=True), Column('name', String(20)), Column('fullname', String(50)), Column('password', String(20)) ) t2 = Table('address', metadata, Column('id',INT, primary_key = True), Column('email_address',String(50), nullable=False), Column('user_id', INT, ForeignKey('users.id')) ) metadata.create_all(engine)
String
对象使用时,++必须声明大小++,对应的是mysql数据库的varchar
。
==区别==:方法一是直接在连接的基础上定义了模式【直接将数据库和模式对应】,而方法二先定义模式,侯江模式设置到特定的数据库中。
==重定义表结构==:在表定义的语句末尾添加
extend_existing=True
。
表和类
from sqlalchemy.ext.declarative import declarative_base from sqlalchemy import Column, INT, String, ForeignKey from sqlalchemy.orm import backref,relationship Base = declarative_base() class User(Base): __tablename__ = "users" id = Column(INT, primary_key=True) name = Column(String) fullname = Column(String) password = Column(String) class Address(Base): __tablename__ = "addresses" id = Column(INT, primary_key = True) email_address = Column(String, nullable = False) user_id = Column(INT, ForeignKey('users.id')) user = relationship('User', backref=backref('addresses',order_by=id))
Column
函数- 第一个参数是元组的数据类型
primary_key
指定主键,nullable
指定元组是否可以为空(nullable = False
表示元组不许为空)ForeignKey
指定外键约束,例如ForeignKey('users.id')
,表示addresses表外键约束users表的主键id
relatioship
函数[^2x]:将会告知ORM
通过Address.user,Address类自身必须链接到User类。relationship()使用两个表的外键约束来判定这种链接的性质。比如说判定Address.user将会是多对一(many-to-one)关系。backref
函数[^2x]:它将提供一种用于反向查询的细节,比如说在对象User上的Address对象集是通过User.addresses属性引用,那么多对一的关系(many-to-one)反向总会是一对多关系(one-to-many)。还有对于Address.user和User.addresses的关系来说总是双向的。(一般与relationship函数结合使用。)
会话
使用sessionmaker
来绑定数据库连接,并建立会话。
例子:
from sqlalchemy import Column, String, create_engine from sqlalchemy.orm import sessionmaker engine = create_engine('mysql+mysqlconnector://root:password@localhost:3306/test') Session = sessionmaker(bind = engine) session = Session()
==连接信息格式==:
数据库类型+数据库驱动名称://用户名:口令@机器地址:端口号/数据库名
,如果碰到中文乱码问题,可在后面加上?charset=utf8
。
==注意==:此处的session相当于java中的statement一样,具有操作数据库的句柄,可以执行sql语句
session.execute("sql语句")
。
添加数据
方法一:使用session执行sql语句方式
session.execute('insert into users values(2,"Bob","Bob hgf", "hgf")') session.commit()
方法二:使用映射类成员变量的数据
user = User(, name="alice", fullname="alice hgf", password="hgf") session.add(user) session.commit()
查询操作
- 查询并获取所有结果
users = session.query(User).all()
==说明==:上述语句返回所有的users表中的数据并以User对象的形式储存在列表中。
for x in query: print x 结果: <__main__.User object at 0x36aa990> <__main__.User object at 0x36aaad0>
- 查询并排序 根据表中的某一个元组排序,使用
order_by
语句
q = session.query(Address).order_by(desc(Address.user_id)) result = q.all() for x in result: print x.email_address
==说明==:执行查询函数后,再使用
all()
函数,才能将结果映射成类的列表,类中储存从数据库中获取的一行数据。
- 过滤查询
q = session.query(Address).filter(Address.user_id == 1) result = q.all() for x in result: print x.email_address
- 连接查询
q = session.query(Address).join(Address.user).group_by(Address.id) result = q.all() for x in result: print x.email_address
- 常见的内联函数,聚合函数
- 求平均值:
q = session.query(func.avg(Address.id))
- Count:
session.query(Address).filter(Address.user_id ==1).count()
- distinct:
session.query(Address).disdinct().count()
删除数据
方法一:使用session执行sql语句方式
方法二:使用映射类成员变量的数据
session.query(Address).filter(Address.id ==4).delete() session.commit()
级联删除:在relationship关联时要加上passive_deletes=True
外键要加上ondelete='CASCADE'
,否则sqlalchemy
不能级联删除。例如:
class MyClass(Base): __tablename__ = 'mytable' id = Column(Integer, primary_key=True) children = relationship("MyOtherClass", cascade="all, delete-orphan", passive_deletes=True) class MyOtherClass(Base): __tablename__ = 'myothertable' id = Column(Integer, primary_key=True) parent_id = Column(Integer, ForeignKey('mytable.id', ondelete='CASCADE') )
修改数据
方法一:使用session执行sql语句方式
session.execute('update addresses set user_id = 1 where id = 2') session.commit()
方法二:使用映射类成员变量的数据
session.query(Address).filter(Address.id == 2).update({"user_id": 1})
==注意==:
update
函数中的参数必须是字典类型
删除表
t1.drop() #t1是sqlalchemy.Table 对象,为users表的定义 --- **{贺广福}(heguangfu)**(tm) @2015-9-18