sqlalchemy 的基本使用示例
下面的例子中将利用sqlalchemy进行数据库的连接,通过orm方式利用类实例属性操作的方式对数据库进行相应操作,同时应用一些常用的函数。
完整代码如下:
from sqlalchemy import create_engine, exc, orm
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.sql.schema import Table, ForeignKey, Column
from sqlalchemy.sql.sqltypes import Integer, VARCHAR
from sqlalchemy.dialects.mysql.base import TINYINT
from sqlalchemy.orm import relationship # declarative_base function will return a class which using active record pattern
# It will combine object opeartion and data operation automatically
Base = declarative_base() dsn = 'mysql://root:root@localhost/test_db' # Create table based on Base obj
class AccountStore(Base):
__tablename__ = 'account_store'
account_id = Column(Integer, primary_key=True)
items = Column(VARCHAR(20))
account = Column(VARCHAR(50))
password = Column(VARCHAR(50))
print(type(AccountStore))
class SqlalchemyActor():
def __init__(self, dsn):
try:
engine = create_engine(dsn)
except ImportError:
raise RuntimeError
engine.connect() # sessionmaker is a factory obj, generate a Session instance, reload __call__ function
# __call__ function will return a session class each time
Session = orm.sessionmaker(bind=engine)
# use Session() to create a class, and assign it to a attribute
self.session = Session()
# Assign costom table and engine to attribute
self.account = AccountStore
self.engine = engine
# Bind engine and table
# Method one: assign manually one by one
self.account.metadata.bind = engine
# Method two: use reflect to map all/partly Table schema
#Base.metadata.reflect(engine) ses = SqlalchemyActor(dsn) def printf(items):
for i in items:
print(i)
print(30*'-') def printfx(items):
for i in items:
print(i.__dict__)
print(30*'-') # execute(SQL)
re = ses.session.execute('SELECT * FROM account_store')
printf(re)
# query(table_name)
# query function similar to Connection.query / Cursor.execute in SQL adaptor
# query(Account) equal to SELECT * FROM acount_store
re = ses.session.query(AccountStore)
print(type(re), re)
printfx(re) # query(table_name.column_name)
# SELECT items FROM account_store
re = ses.session.query(AccountStore.items)
print(re)
printf(re) sq = ses.session.query(AccountStore) # all() / one() / first():
# source code does similar to: lambda x: list(x)
print(type(sq))
print(type(sq.all())) # filter()
# SELECT * FROM account_store WHERE account_store.items = 'WeChat'
re = sq.filter(AccountStore.items=='WeChat')
print(re)
printfx(re) # filter_by()
# SELECT * FROM account_store WHERE account_store.items = 'WeChat'
re = sq.filter_by(items='WeChat')
print(re)
printfx(re) # order_by()
# SELECT * FROM account_store ORDER BY account_store.items
re = sq.order_by(AccountStore.items)
print(re)
printfx(re) # desc()
# SELECT * FROM account_store ORDER BY account_store.items DESC
re = sq.order_by(AccountStore.items.desc())
print(re)
printfx(re) # limit(): select next 3 values
# SELECT * FROM account_store LIMIT 3
re = sq.limit(3)
print(re)
printfx(re) # offset(): select from 3rd values
# SELECT * FROM table_name OFFSET 2
re = sq.offset(2)
print(re)
printfx(re) # select from 3rd and next 3 values
re = sq.limit(3).offset(2) # equal to re = sq.offset(2).limit(3)
print(re)
printfx(re)
re = sq[2:5] # equal to re = sq.offset(2).limit(3)
print(re)
printfx(re) # DELETE value
re = sq.filter(AccountStore.account_id==8).delete()
print(re) # 0 fail, 1 success
re = sq.filter(AccountStore.account_id==9).delete()
print(re) # 0 fail, 1 success
re = sq.filter(AccountStore.account_id==10).delete()
print(re) # 0 fail, 1 success # INSERT value
# Not ORM
ses.session.execute(AccountStore.__table__.insert(), {'items': 'KUBO', 'account': 'STAR', 'password': '1d2345', 'account_id': 10})
# Below code is an expression of SQL
sql = ses.account.__table__.insert([{'items': 'Ericsson', 'account': 'EKELIKE', 'password': '', 'account_id': 8}, {'items': 'MOMO', 'account': 'sad', 'password': '', 'account_id': 9}])
ses.session.execute(sql) # SELECT value
re = sq.get(9)
print(re)
# Modify value
re.password = 1293 # Count values
re = sq.count()
print(re) ses.session.commit()
ses.session.close()
分段分析:
首先对所需要的模块进行导入
from sqlalchemy import create_engine, exc, orm
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.sql.schema import Table, ForeignKey, Column
from sqlalchemy.sql.sqltypes import Integer, VARCHAR
from sqlalchemy.dialects.mysql.base import TINYINT
from sqlalchemy.orm import relationship
随后利用declarative_base()函数创建一个Base类,并且基于Base类创建AccountStore表的各项信息
# declarative_base function will return a class which using active record pattern
# It will combine object opeartion and data operation automatically
Base = declarative_base()#dsn = 'mssql+pyodbc://ItpReadOnly:@reaedonlyENC@encitp.cn.ao.ericsson.se\itp:0/ITP'
dsn = 'mysql://root:root@localhost/test_db'# Create table based on Base obj
class AccountStore(Base):
__tablename__ = 'account_store'
account_id = Column(Integer, primary_key=True)
items = Column(VARCHAR(20))
account = Column(VARCHAR(50))
password = Column(VARCHAR(50))
建立起一个sql的执行器,创建一个通信引擎,同时利用orm创建出会话对象,并将会话类与通信引擎进行绑定。通过对会话类的实例化产生一个会话实例。同时手动对表的类进行引擎指向(也可利用reflect进行自动指向)。
class SqlalchemyActor():
def __init__(self, dsn):
try:
engine = create_engine(dsn)
except ImportError:
raise RuntimeError
engine.connect() # sessionmaker is a factory obj, generate a Session instance, reload __call__ function
# __call__ function will return a session class each time
Session = orm.sessionmaker(bind=engine)
# use Session() to create a class, and assign it to a attribute
self.session = Session()
# Assign costom table and engine to attribute
self.account = AccountStore
self.engine = engine
# Bind engine and table
# Method one: assign manually one by one
self.account.metadata.bind = engine
# Method two: use reflect to map all/partly Table schema
#Base.metadata.reflect(engine)
接下来对sql执行器进行初始化,并定义两个显示函数用于输出结果。
ses = SqlalchemyActor(dsn)def printf(items):
for i in items:
print(i)
print(30*'-')def printfx(items):
for i in items:
print(i.__dict__)
print(30*'-')
完成上述定义后,首先测试session类的execute函数,该函数可以直接执行SQL语句
# execute(SQL)
re = ses.session.execute('SELECT * FROM account_store')
printf(re)
# query(table_name)
# query function similar to Connection.query / Cursor.execute in SQL adaptor
测试session类的query函数,通过query函数传入表的类,来获取表内的信息,其过程等价于SQL语句中的 SELECT * FROM table_name
# query(Account) equal to SELECT * FROM acount_store
re = ses.session.query(AccountStore)
print(type(re), re)
printfx(re)
对于query函数,若想获取表的其中一列信息,则可以用account_store.item的方式进行,其效果等价于SQL语句的SELECT item FROM account_store
# query(table_name.column_name)
# SELECT items FROM account_store
re = ses.session.query(AccountStore.items)
print(re)
printf(re)
而query函数的返回值通常是一个result类,通过对该类的操作可以获取想要的结果
sq = ses.session.query(AccountStore)
首先是all() / one() / first()函数,分别为获取结果的所有,下一条,第一条项目并返回为list。其中all()函数的源码实现便类似于lambda x: list(x)
# all() / one() / first():
# source code does similar to: lambda x: list(x)
print(type(sq))
print(type(sq.all()))
filter()函数则是一个十分常用的函数,其效果类似于SQL中的WHERE条件子句,通过对filter传入条件语句可以筛选出所需要的结果。类似的filter_by函数则可以以=进行筛选表达
# filter()
# SELECT * FROM account_store WHERE account_store.items = 'WeChat'
re = sq.filter(AccountStore.items=='WeChat')
print(re)
printfx(re)# filter_by()
# SELECT * FROM account_store WHERE account_store.items = 'WeChat'
re = sq.filter_by(items='WeChat')
print(re)
printfx(re)
order_by()函数则类似于SQL中的ORDER BY条件语句,可以对筛选的结果按需求进行顺序排列。当需要倒序排列时,可对排序项目末尾加上.desc()函数
# order_by()
# SELECT * FROM account_store ORDER BY account_store.items
re = sq.order_by(AccountStore.items)
print(re)
printfx(re)# desc()
# SELECT * FROM account_store ORDER BY account_store.items DESC
re = sq.order_by(AccountStore.items.desc())
print(re)
printfx(re)
limit()函数可以对所需要的结果数量进行获取,等价于SQL中的LIMIT条件语句。将会返回当前结果之后的一定数量结果,数量有limit()传入参数确定
# limit(): select next 3 values
# SELECT * FROM account_store LIMIT 3
re = sq.limit(3)
print(re)
printfx(re)
offset()函数将会对结果进行偏移,等价于SQL的OFFSET语句
# offset(): select from 3rd values
# SELECT * FROM table_name OFFSET 2
re = sq.offset(2)
print(re)
printfx(re)
通常会将limit()函数与offset函数组合进行使用,两个函数的顺序不产生影响,而另外一种获取特定行的方式为切片,这种方式更为简洁明了
# select from 3rd and next 3 values
re = sq.limit(3).offset(2) # equal to re = sq.offset(2).limit(3)
print(re)
printfx(re)
re = sq[2:5] # equal to re = sq.offset(2).limit(3)
print(re)
printfx(re)
delete()函数通常用于从表格中删除相应的行或数据,通过filter()函数加上条件语句将需要删除的项目筛选出来后,加上.delete()来删除该项目,该函数会返回一个结果值,0为删除失败,1为删除成功
# DELETE value
re = sq.filter(AccountStore.account_id==8).delete()
print(re) # 0 fail, 1 success
re = sq.filter(AccountStore.account_id==9).delete()
print(re) # 0 fail, 1 success
re = sq.filter(AccountStore.account_id==10).delete()
print(re) # 0 fail, 1 success
insert()函数,可以采用不基于ORM的方式,直接通过会话实例进行执行。对需要插入的表的类中__table__进行insert()函数调用,execute()函数可以接收两个参数,其中第二个参数为前面函数的传入值,通过这种方式进行值的插入
# INSERT value
# Not ORM
ses.session.execute(AccountStore.__table__.insert(), {'items': 'KUBO', 'account': 'STAR', 'password': '1d2345', 'account_id': 10})
还有一种插入的代码方式为直接对account.__table__调用insert()函数,而不通过execute,insert函数接收一个列表,列表的每个元素都是字典形式的数据
# Below code is an expression of SQL
sql = ses.account.__table__.insert([{'items': 'Ericsson', 'account': 'EKELIKE', 'password': '', 'account_id': 8}, {'items': 'MOMO', 'account': 'sad', 'password': '', 'account_id': 9}])
ses.session.execute(sql)
get()函数可以对结果值的特定指数index位置获取结果
# SELECT value
re = sq.get(9)
print(re)
而若是要对表类的属性,即对表格数据进行修改,由于ORM的存在,只需要对返回结果的属性值进行修改并执行commit即可完成
# Modify value
re.password = 1293
count()函数可以返回对结果数量的计数值
# Count values
re = sq.count()
print(re)
最后,对于存在对数据库产生修改性质的操作,均需要commit进行事务确认才能生效,且在结束操作后需要关闭数据库连接
ses.session.commit()
ses.session.close()
上面的代码运行后输出结果如下,内容较多仅供参考,建议逐个运行进行尝试。
<class 'sqlalchemy.ext.declarative.api.DeclarativeMeta'>
('WeChat', 'hereisac', '', 1)
('Qq', 're32isac', '', 2)
('Mail', 're3214ac', '', 3)
('Gmail', 're35c', '123dd5', 4)
('Google', 'rccc', '3dd5', 5)
('MySQL', 'r3c', 'cd5', 6)
('SQLSERVER', 'r3cdasd', 'd5', 7)
('Ericsson', 'EKELIKE', '', 8)
('MOMO', 'sad', '', 9)
('KUBO', 'STAR', '1d2345', 10)
------------------------------
<class 'sqlalchemy.orm.query.Query'> SELECT account_store.account_id AS account_store_account_id, account_store.items AS account_store_items, account_store.account AS account_store_account, account_store.password AS account_store_password
FROM account_store
{'items': 'WeChat', 'password': '', 'account': 'hereisac', '_sa_instance_state': <sqlalchemy.orm.state.InstanceState object at 0x00000000048398D0>, 'account_id': 1}
{'items': 'Qq', 'password': '', 'account': 're32isac', '_sa_instance_state': <sqlalchemy.orm.state.InstanceState object at 0x0000000004839940>, 'account_id': 2}
{'items': 'Mail', 'password': '', 'account': 're3214ac', '_sa_instance_state': <sqlalchemy.orm.state.InstanceState object at 0x00000000048399B0>, 'account_id': 3}
{'items': 'Gmail', 'password': '123dd5', 'account': 're35c', '_sa_instance_state': <sqlalchemy.orm.state.InstanceState object at 0x0000000004839A58>, 'account_id': 4}
{'items': 'Google', 'password': '3dd5', 'account': 'rccc', '_sa_instance_state': <sqlalchemy.orm.state.InstanceState object at 0x0000000004839B00>, 'account_id': 5}
{'items': 'MySQL', 'password': 'cd5', 'account': 'r3c', '_sa_instance_state': <sqlalchemy.orm.state.InstanceState object at 0x0000000004839BA8>, 'account_id': 6}
{'items': 'SQLSERVER', 'password': 'd5', 'account': 'r3cdasd', '_sa_instance_state': <sqlalchemy.orm.state.InstanceState object at 0x0000000004839C50>, 'account_id': 7}
{'items': 'Ericsson', 'password': '', 'account': 'EKELIKE', '_sa_instance_state': <sqlalchemy.orm.state.InstanceState object at 0x0000000004839CF8>, 'account_id': 8}
{'items': 'MOMO', 'password': '', 'account': 'sad', '_sa_instance_state': <sqlalchemy.orm.state.InstanceState object at 0x0000000004839DA0>, 'account_id': 9}
{'items': 'KUBO', 'password': '1d2345', 'account': 'STAR', '_sa_instance_state': <sqlalchemy.orm.state.InstanceState object at 0x0000000004839E48>, 'account_id': 10}
------------------------------
SELECT account_store.items AS account_store_items
FROM account_store
('WeChat',)
('Qq',)
('Mail',)
('Gmail',)
('Google',)
('MySQL',)
('SQLSERVER',)
('Ericsson',)
('MOMO',)
('KUBO',)
------------------------------
<class 'sqlalchemy.orm.query.Query'>
<class 'list'>
SELECT account_store.account_id AS account_store_account_id, account_store.items AS account_store_items, account_store.account AS account_store_account, account_store.password AS account_store_password
FROM account_store
WHERE account_store.items = %s
{'items': 'WeChat', 'password': '', 'account': 'hereisac', '_sa_instance_state': <sqlalchemy.orm.state.InstanceState object at 0x0000000004839278>, 'account_id': 1}
------------------------------
SELECT account_store.account_id AS account_store_account_id, account_store.items AS account_store_items, account_store.account AS account_store_account, account_store.password AS account_store_password
FROM account_store
WHERE account_store.items = %s
{'items': 'WeChat', 'password': '', 'account': 'hereisac', '_sa_instance_state': <sqlalchemy.orm.state.InstanceState object at 0x00000000048399B0>, 'account_id': 1}
------------------------------
SELECT account_store.account_id AS account_store_account_id, account_store.items AS account_store_items, account_store.account AS account_store_account, account_store.password AS account_store_password
FROM account_store ORDER BY account_store.items
{'items': 'Ericsson', 'password': '', 'account': 'EKELIKE', '_sa_instance_state': <sqlalchemy.orm.state.InstanceState object at 0x0000000004858438>, 'account_id': 8}
{'items': 'Gmail', 'password': '123dd5', 'account': 're35c', '_sa_instance_state': <sqlalchemy.orm.state.InstanceState object at 0x00000000048584A8>, 'account_id': 4}
{'items': 'Google', 'password': '3dd5', 'account': 'rccc', '_sa_instance_state': <sqlalchemy.orm.state.InstanceState object at 0x0000000004858518>, 'account_id': 5}
{'items': 'KUBO', 'password': '1d2345', 'account': 'STAR', '_sa_instance_state': <sqlalchemy.orm.state.InstanceState object at 0x0000000004858588>, 'account_id': 10}
{'items': 'Mail', 'password': '', 'account': 're3214ac', '_sa_instance_state': <sqlalchemy.orm.state.InstanceState object at 0x00000000048585F8>, 'account_id': 3}
{'items': 'MOMO', 'password': '', 'account': 'sad', '_sa_instance_state': <sqlalchemy.orm.state.InstanceState object at 0x0000000004858668>, 'account_id': 9}
{'items': 'MySQL', 'password': 'cd5', 'account': 'r3c', '_sa_instance_state': <sqlalchemy.orm.state.InstanceState object at 0x00000000048586D8>, 'account_id': 6}
{'items': 'Qq', 'password': '', 'account': 're32isac', '_sa_instance_state': <sqlalchemy.orm.state.InstanceState object at 0x0000000004858748>, 'account_id': 2}
{'items': 'SQLSERVER', 'password': 'd5', 'account': 'r3cdasd', '_sa_instance_state': <sqlalchemy.orm.state.InstanceState object at 0x00000000048587B8>, 'account_id': 7}
{'items': 'WeChat', 'password': '', 'account': 'hereisac', '_sa_instance_state': <sqlalchemy.orm.state.InstanceState object at 0x0000000004858828>, 'account_id': 1}
------------------------------
SELECT account_store.account_id AS account_store_account_id, account_store.items AS account_store_items, account_store.account AS account_store_account, account_store.password AS account_store_password
FROM account_store ORDER BY account_store.items DESC
{'items': 'WeChat', 'password': '', 'account': 'hereisac', '_sa_instance_state': <sqlalchemy.orm.state.InstanceState object at 0x0000000004839390>, 'account_id': 1}
{'items': 'SQLSERVER', 'password': 'd5', 'account': 'r3cdasd', '_sa_instance_state': <sqlalchemy.orm.state.InstanceState object at 0x00000000048399B0>, 'account_id': 7}
{'items': 'Qq', 'password': '', 'account': 're32isac', '_sa_instance_state': <sqlalchemy.orm.state.InstanceState object at 0x0000000004839EF0>, 'account_id': 2}
{'items': 'MySQL', 'password': 'cd5', 'account': 'r3c', '_sa_instance_state': <sqlalchemy.orm.state.InstanceState object at 0x00000000048392B0>, 'account_id': 6}
{'items': 'MOMO', 'password': '', 'account': 'sad', '_sa_instance_state': <sqlalchemy.orm.state.InstanceState object at 0x0000000004839278>, 'account_id': 9}
{'items': 'Mail', 'password': '', 'account': 're3214ac', '_sa_instance_state': <sqlalchemy.orm.state.InstanceState object at 0x0000000004839320>, 'account_id': 3}
{'items': 'KUBO', 'password': '1d2345', 'account': 'STAR', '_sa_instance_state': <sqlalchemy.orm.state.InstanceState object at 0x0000000004839470>, 'account_id': 10}
{'items': 'Google', 'password': '3dd5', 'account': 'rccc', '_sa_instance_state': <sqlalchemy.orm.state.InstanceState object at 0x0000000004839CF8>, 'account_id': 5}
{'items': 'Gmail', 'password': '123dd5', 'account': 're35c', '_sa_instance_state': <sqlalchemy.orm.state.InstanceState object at 0x0000000004839668>, 'account_id': 4}
{'items': 'Ericsson', 'password': '', 'account': 'EKELIKE', '_sa_instance_state': <sqlalchemy.orm.state.InstanceState object at 0x0000000004839C88>, 'account_id': 8}
------------------------------
SELECT account_store.account_id AS account_store_account_id, account_store.items AS account_store_items, account_store.account AS account_store_account, account_store.password AS account_store_password
FROM account_store
LIMIT %s
{'items': 'WeChat', 'password': '', 'account': 'hereisac', '_sa_instance_state': <sqlalchemy.orm.state.InstanceState object at 0x0000000004839828>, 'account_id': 1}
{'items': 'Qq', 'password': '', 'account': 're32isac', '_sa_instance_state': <sqlalchemy.orm.state.InstanceState object at 0x00000000048397B8>, 'account_id': 2}
{'items': 'Mail', 'password': '', 'account': 're3214ac', '_sa_instance_state': <sqlalchemy.orm.state.InstanceState object at 0x0000000004839F28>, 'account_id': 3}
------------------------------
SELECT account_store.account_id AS account_store_account_id, account_store.items AS account_store_items, account_store.account AS account_store_account, account_store.password AS account_store_password
FROM account_store
LIMIT %s, 18446744073709551615
{'items': 'Mail', 'password': '', 'account': 're3214ac', '_sa_instance_state': <sqlalchemy.orm.state.InstanceState object at 0x00000000048581D0>, 'account_id': 3}
{'items': 'Gmail', 'password': '123dd5', 'account': 're35c', '_sa_instance_state': <sqlalchemy.orm.state.InstanceState object at 0x0000000004858240>, 'account_id': 4}
{'items': 'Google', 'password': '3dd5', 'account': 'rccc', '_sa_instance_state': <sqlalchemy.orm.state.InstanceState object at 0x00000000048582B0>, 'account_id': 5}
{'items': 'MySQL', 'password': 'cd5', 'account': 'r3c', '_sa_instance_state': <sqlalchemy.orm.state.InstanceState object at 0x0000000004858320>, 'account_id': 6}
{'items': 'SQLSERVER', 'password': 'd5', 'account': 'r3cdasd', '_sa_instance_state': <sqlalchemy.orm.state.InstanceState object at 0x0000000004858390>, 'account_id': 7}
{'items': 'Ericsson', 'password': '', 'account': 'EKELIKE', '_sa_instance_state': <sqlalchemy.orm.state.InstanceState object at 0x0000000004858400>, 'account_id': 8}
{'items': 'MOMO', 'password': '', 'account': 'sad', '_sa_instance_state': <sqlalchemy.orm.state.InstanceState object at 0x0000000004858470>, 'account_id': 9}
{'items': 'KUBO', 'password': '1d2345', 'account': 'STAR', '_sa_instance_state': <sqlalchemy.orm.state.InstanceState object at 0x00000000048584E0>, 'account_id': 10}
------------------------------
SELECT account_store.account_id AS account_store_account_id, account_store.items AS account_store_items, account_store.account AS account_store_account, account_store.password AS account_store_password
FROM account_store
LIMIT %s, %s
{'items': 'Mail', 'password': '', 'account': 're3214ac', '_sa_instance_state': <sqlalchemy.orm.state.InstanceState object at 0x0000000004858208>, 'account_id': 3}
{'items': 'Gmail', 'password': '123dd5', 'account': 're35c', '_sa_instance_state': <sqlalchemy.orm.state.InstanceState object at 0x0000000004858278>, 'account_id': 4}
{'items': 'Google', 'password': '3dd5', 'account': 'rccc', '_sa_instance_state': <sqlalchemy.orm.state.InstanceState object at 0x00000000048582E8>, 'account_id': 5}
------------------------------
[<__main__.AccountStore object at 0x0000000004858390>, <__main__.AccountStore object at 0x0000000004858400>, <__main__.AccountStore object at 0x0000000004858470>]
{'items': 'Mail', 'password': '', 'account': 're3214ac', '_sa_instance_state': <sqlalchemy.orm.state.InstanceState object at 0x00000000048583C8>, 'account_id': 3}
{'items': 'Gmail', 'password': '123dd5', 'account': 're35c', '_sa_instance_state': <sqlalchemy.orm.state.InstanceState object at 0x0000000004858438>, 'account_id': 4}
{'items': 'Google', 'password': '3dd5', 'account': 'rccc', '_sa_instance_state': <sqlalchemy.orm.state.InstanceState object at 0x00000000048584A8>, 'account_id': 5}
------------------------------
1
1
1
<__main__.AccountStore object at 0x0000000004858B38>
10
相关阅读