Python SQLAlchemy Program Instance

Sample1

# coding=gbkfrom SQLAlchemy Import *from sqlalchemy.orm Import *engine = Create_engine (' sqlite:///./sqlalchemy.db ', echo                    =true) metadata = metadata (engine) ' CREATE TABLE ' users_table = Table (' Users ', metadata, Column (' id ', Integer, Primary_key=true), column (' Name ', String ()), column (' Email ', String ') if not users_table.exists (): users_table.create () ' Load table ' users_table = Table (' Users ', met Adata, autoload=true) ' Insert ' Users_table.insert (). Execute (name= "SSS", email= "[email protected]") users_ Table.insert (). Execute (name= "TTT", email= "[email protected]") ' Update ' Users_table.update (users_ table.c.name== "TTT"). Execute (name= "ddd") ' Select ' Result=users_table.select (and_ (Users_table.c.name = = "DDD", Users_table.c.email = = "[email protected]"). Execute () for item in Result.fetchall (): Print item ' delete ' users _table.delete (users_table.c.name== "ddd"). Execute () ' ORM--bind '' Class User (object): Pass mapper (user, users_table) session = Sessionmaker () #创建了一个自定义了的 session class Session.configure (BI Nd=engine) #将创建的数据库连接关联到这个sessionsession = Session () u = User () u.name= ' asdf ' u.email= ' [email protected] ' Session.add (U) #在session中添加内容session. Flush () #保存数据session. Commit () #数据库事务的提交, Sisson automatically expires without needing to close

Sample2

# coding=gbkfrom Sqlalchemy.orm Import Mapper, Sessionmaker #sessionmaker () function is the most commonly used method of creating the top-most useful session for the entire application, session Manages all conversations with databases from datetime import datetimefrom sqlalchemy import Table, MetaData, Column, ForeignKey, Integer, String, Un Icode, DateTime #会SQL的人能理解这些函数吧? From sqlalchemy import *from sqlalchemy.orm Import *engine = Create_engine ("sqlite:/// Tutorial.db ", echo=true) #创建到数据库的连接, echo=true means output debug results with logging MetaData = MetaData () #跟踪表属性user_table = table (#                    Information required to create a table: field, table name, etc. ' Tf_user ', metadata, Column (' id ', Integer, Primary_key=true), Column (' User_name ', Unicode (+), Unique=true, Nullable=false), column (' Email_address ', U                    Nicode (255), Unique=true, Nullable=false), Column (' Password ', Unicode (+), nullable=false),                    Column (' First_Name ', Unicode (255), default= '), column (' Last_Name ', Unicode (255), default= '), Column (' Created ', DatetimE, Default=datetime.now)) Metadata.create_all (engine) #在数据库中生成表class User (object): Pass #创建一个映射类mapper (user, User_ Table) #把表映射到类Session = Sessionmaker () #创建了一个自定义了的 session class Session.configure (Bind=engine) # Associate the created database connection to this sessionsession = Session () u = User () u.user_name= ' dongwm ' u.email_address= ' [email protected] ' u.password= ' Testpass ' #给映射类添加以下必要的属性 because the table above specifies that these fields cannot be empty session.add (U) #在session中添加内容session. Flush () # Save Data Session.commit () #数据库事务的提交, Sisson automatically expires without needing to close the query = Session.query (User) #query () A simple understanding is an ORM-enabled alternative to select (), can accept any combination of Class/column expressions Print List (query) #列出所有userprint query.get (1) #根据主键显示print query.filter_by (user_name= ' Dongwm '). First () #类似于SQL的where, print one of which u = query.filter_by (user_name= ' Dongwm '). () U.password = ' Newpass ' # Modify its password field session.commit () #提交事务print query.get (1). Password #打印会出现新密码for instance in Session.query (User). Order_by ( User.ID): #根据id字段排序, print the user name and e-mail address printed instance.user_name, instance.email_address

Sample3:

# coding=gbkfrom SQLAlchemy Import *from sqlalchemy.orm import *from datetime import datetimefrom sqlalchemy import Table, MetaData, Column, ForeignKey, Integer, String, Unicode, datetimeengine = Create_engine ("Sqlite:///tutorial2.db", echo=    True) #创建到数据库的连接, echo=true means output debug results with logging MetaData = MetaData () #跟踪表属性class User (object): Passclass Group (object): Passclass Permission (object): passuser_table = table (' Tf_user ', metadata, Column (                   ' id ', Integer, Primary_key=true), Column (' user_name ', Unicode (+), unique=true, nullable = False), Column (' Password ', Unicode (+), nullable = False)) group_table = Table ("Tf_group", metadata , column (' ID ', Integer, Primary_key=true), column (' Group_name ', Unicode (), unique= True, nullable = False) permission_table = Table ("Tf_permission", metadata, Column (' Id ', Integer, primary_key=tRue), Column (' Permission_name ', Unicode (+), unique=true, nullable = False)) User_group = Table ("User_gr                   OUP ", metadata, Column (' user_id ', None, ForeignKey (' tf_user.id '), primary_key=true), Column (' group_id ', None, ForeignKey (' tf_group.id '), primary_key=true)) Group_permission = Table ("Group_pe Rmission ", metadata, Column (' permission_id ', None, ForeignKey (' tf_permission.id '), PR Imary_key=true), Column (' group_id ', None, ForeignKey (' tf_group.id '), primary_key=true)) mapper (user, user _table, Properties=dict (_groups=relation (Group, Secondary=user_group, backref= ' _users ')) mapper (group, group_table , Properties=dict (_permissions=relation (Permission, secondary=group_permission, backref= ' _groups ')) Mapper ( Permission, permission_table) Metadata.create_all (engine)

Python SQLAlchemy Program Instance