SQLAlchemy最新权威详细教程

SQLAlchemy最新权威详细教程前言:最近开始学习SQLAlchemy,本教程是其官方文档以及在读英文版EssentialSQLAlchemy的翻译加一些自己的理解和总结1什么是SQLAlchemy?它是给mysql,oracle,sqlite等关系型数据库的python接口,不需要大幅修改原有的python代码,它已经包含了SQL表达式语言和ORM,看一些例子:sql=”INSERTINTOuser(user_name,password)VALUES(%s,%s)”cursor=conn.cursor()cursor.execute(sql,(‘dongwm’,‘testpass’))以上是一个常用的mysql的SQL语句,但是冗长也容易出错,并且可能导致安全问题(因为是字符串的语句,会存在SQL注入),并且代码不跨平台,在不同数据库软件的语句不同(以下是一个Oracle例子),不具备客移植性:sql=”INSERTINTOuser(user_name,password)VALUES(:1,:2)”cursor=conn.cursor()cursor.execute(sql,‘dongwm’,‘testpass’)而在SQLAlchemy里只需要这样写:statement=user_table.insert(user_name=’rick’,password=’parrot’)statement.execute()#护略是什么数据库环境SQLAlchemy还能让你写出很pythonic的语句:statement=user_table.select(and_(user_table.c.created=date(2007,1,1),user_table.c.createddate(2008,1,1))result=statement.execute()#检索所有在2007年创建的用户metadata=MetaData(‘sqlite://’)#告诉它你设置的数据库类型是基于内存的sqliteuser_table=Table(#创建一个表‘tf_user’,metadata,Column(‘id’,Integer,primary_key=True),#一些字段,假设你懂SQL,那么以下的字段很好理解Column(‘user_name’,Unicode(16),unique=True,nullable=False),Column(‘email_address’,Unicode(255),unique=True,nullable=False),Column(‘password’,Unicode(40),nullable=False),Column(‘first_name’,Unicode(255),default=”),Column(‘last_name’,Unicode(255),default=”),Column(‘created’,DateTime,default=datetime.now))users_table=Table(‘users’,metadata,autoload=True)#假设table已经存在.就不需要指定字段,只是加个autoload=TrueclassUser(object):pass#虽然SQLAlchemy强大,但是插入更新还是需要手动指定,可以使用ORM,方法就是:设定一个类,定义一个表,把表映射到类里面mapper(User,user_table)下面是一个完整ORM的例子:Sourcecodefromsqlalchemy.ormimportmapper,sessionmaker#sessionmaker()函数是最常使用的创建最顶层可用于整个应用Session的方法,Session管理着所有与数据库之间的会话fromdatetimeimportdatetimefromsqlalchemyimportTable,MetaData,Column,ForeignKey,Integer,String,Unicode,DateTime#会SQL的人能理解这些函数吧?engine=create_engine(sqlite:///tutorial.db,echo=True)#创建到数据库的连接,echo=True表示用logging输出调试结果metadata=MetaData()#跟踪表属性user_table=Table(#创建一个表所需的信息:字段,表名等'tf_user',metadata,Column('id',Integer,primary_key=True),Column('user_name',Unicode(16),unique=True,nullable=False),Column('email_address',Unicode(255),unique=True,nullable=False),Column('password',Unicode(40),nullable=False),Column('first_name',Unicode(255),default=''),Column('last_name',Unicode(255),default=''),Column('created',DateTime,default=datetime.now))metadata.create_all(engine)#在数据库中生成表classUser(object):pass#创建一个映射类mapper(User,user_table)#把表映射到类Session=sessionmaker()#创建了一个自定义了的Session类Session.configure(bind=engine)#将创建的数据库连接关联到这个sessionsession=Session()u=User()u.user_name='dongwm'u.email_address='[e-mail]'u.password='testpass'#给映射类添加以下必要的属性,因为上面创建表指定这几个字段不能为空session.add(u)#在session中添加内容session.flush()#保存数据session.commit()#数据库事务的提交,sisson自动过期而不需要关闭query=session.query(User)#query()简单的理解就是select()的支持ORM的替代方法,可以接受任意组合的class/column表达式printlist(query)#列出所有userprintquery.get(1)#根据主键显示printquery.filter_by(user_name='dongwm').first()#类似于SQL的where,打印其中的第一个u=query.filter_by(user_name='dongwm').first()u.password='newpass'#修改其密码字段session.commit()#提交事务printquery.get(1).password#打印会出现新密码forinstanceinsession.query(User).order_by(User.id):#根据id字段排序,打印其中的用户名和邮箱地址printinstance.user_name,instance.email_address既然是ORM框架,我们来一个更复杂的包含关系的例子,先看sql语句:CREATETABLEtf_user(idINTEGERNOTNULL,user_nameVARCHAR(16)NOTNULL,email_addressVARCHAR(255)NOTNULL,passwordVARCHAR(40)NOTNULL,first_nameVARCHAR(255),last_nameVARCHAR(255),createdTIMESTAMP,PRIMARYKEY(id),UNIQUE(user_name),UNIQUE(email_address));CREATETABLEtf_group(idINTEGERNOTNULL,group_nameVARCHAR(16)NOTNULL,PRIMARYKEY(id),UNIQUE(group_name));CREATETABLEtf_permission(idINTEGERNOTNULL,permission_nameVARCHAR(16)NOTNULL,PRIMARYKEY(id),UNIQUE(permission_name));CREATETABLEuser_group(user_idINTEGER,group_idINTEGER,PRIMARYKEY(user_id,group_id),FOREIGNKEY(user_id)REFERENCEStf_user(id),#user_group的user_id关联了tf_user的id字段FOREIGNKEY(group_id)REFERENCEStf_group(id));#group_id关联了tf_group的id字段CREATETABLEgroup_permission(group_idINTEGER,permission_idINTEGER,PRIMARYKEY(group_id,permission_id),FOREIGNKEY(group_id)REFERENCEStf_group(id),#group_permission的id关联tf_group的id字段FOREIGNKEY(permission_id)REFERENCEStf_permission(id));#permission_id关联了tf_permission的id字段这是一个复杂的多对多的关系,比如检查用户是否有admin权限,sql需要这样:SELECTCOUNT(*)FROMtf_user,tf_group,tf_permissionWHEREtf_user.user_name=’dongwm’ANDtf_user.id=user_group.user_idANDuser_group.group_id=group_permission.group_idANDgroup_permission.permission_id=tf_permission.idANDpermission_name=’admin’;看起来太复杂并且繁长了在面向对象的世界里,是这样的:classUser(object):groups=[]classGroup(object):users=[]permissions=[]classPermission(object):groups=[]Sourcecodeprint'Summaryfor%s'%user.user_nameforginuser.groups:print'Memberofgroup%s'%g.group_nameforping.permissions:print'...whichhaspermission%s'%p.permission_nameSourcecodedefuser_has_permission(user,permission_name):#检查用户是否有permission_name的权限的函数forginuser.groups:forping.permissions:#可以看出来使用了for循环ifp.permission_name=='admin':returnTruereturnFalse而在SQLAlchemy中,这样做:mapper(User,user_table,properties=dict(groups=relation(Group,secondary=user_group,backref=’users’)))#properties是一个字典值。增加了一个groups值，它又是一