SQLAlchemy是python操作數(shù)據(jù)庫(kù)的一個(gè)庫(kù)。能夠進(jìn)行orm映射，SQLAlchemy“采用簡(jiǎn)單的Python語(yǔ)言，為高效和高性能的數(shù)據(jù)庫(kù)訪問(wèn)設(shè)計(jì)，實(shí)現(xiàn)了完整的企業(yè)級(jí)持久模型”
SQLAlchemy的理念是，SQL數(shù)據(jù)庫(kù)的量級(jí)和性能重要于對(duì)象集合；而對(duì)象集合的抽象又重要于表和行。
一安裝SQLAlchemy
代碼如下:
pipinstallsqlalchemy
導(dǎo)入如果沒(méi)有報(bào)錯(cuò)則安裝成功
代碼如下:>>>importsqlalchemy
>>>sqlalchemy.__version__
'0.9.1'
>>>
二使用sqlalchemy對(duì)數(shù)據(jù)庫(kù)操作
1.定義元信息，綁定到引擎
代碼如下:
(env)ghost@ghost-H61M-S2V-B3:~/project/flask/fsql$python
Python2.7.3(default,Apr102013,05:13:16)
[GCC4.7.2]onlinux2
Type"help","copyright","credits"or"license"formoreinformation.
>>>fromsqlalchemyimport*
>>>fromsqlalchemy.ormimport*
>>>engine=create_engine('sqlite:///./sqlalchemy.db',echo=True)#定義引擎
>>>metadata=MetaData(engine)#綁定元信息
>>>
2.創(chuàng)建表格，初始化數(shù)據(jù)庫(kù)
代碼如下:
>>>users_table=Table('users',metadata,
...Column('id',Integer,primary_key=True),
...Column('name',String(40)),
...Column('email',String(120)))
>>>
>>>users_table.create()
2014-01-0910:03:32,436INFOsqlalchemy.engine.base.Engine
CREATETABLEusers(
idINTEGERNOTNULL,
nameVARCHAR(40),
emailVARCHAR(120),
PRIMARYKEY(id)
)
2014-01-0910:03:32,436INFOsqlalchemy.engine.base.Engine()
2014-01-0910:03:32,575INFOsqlalchemy.engine.base.EngineCOMMIT
>>>
執(zhí)行上述代碼，我們就創(chuàng)建一個(gè)users表，有id，name，email三個(gè)字段
代碼如下:
(env)ghost@ghost-H61M-S2V-B3:~/project/flask/fsql$sqlite3sqlalchemy.db
SQLiteversion3.7.132012-06-1102:05:22
Enter".help"forinstructions
EnterSQLstatementsterminatedwitha";"
sqlite>.tables
users
sqlite>
3.基本操作，插入
如果已經(jīng)table表已經(jīng)存在，第二次運(yùn)行就不許要create了，使用autoload設(shè)置
代碼如下:
>>>fromsqlalchemyimport*
>>>fromsqlalchemy.ormimport*
>>>engine=create_engine('sqlite:///./sqlalchemy.db',echo=True)
>>>metadata=MetaData(engine)
>>>users_table=Table('users',metadata,autoload=True)
2014-01-0910:20:01,580INFOsqlalchemy.engine.base.EnginePRAGMAtable_info("users")
2014-01-0910:20:01,581INFOsqlalchemy.engine.base.Engine()
2014-01-0910:20:01,582INFOsqlalchemy.engine.base.EnginePRAGMAforeign_key_list("users")
2014-01-0910:20:01,583INFOsqlalchemy.engine.base.Engine()
2014-01-0910:20:01,583INFOsqlalchemy.engine.base.EnginePRAGMAindex_list("users")
2014-01-0910:20:01,583INFOsqlalchemy.engine.base.Engine()
>>>users_table
Table('users',MetaData(bind=Engine(sqlite:///./sqlalchemy.db)),Column('id',INTEGER(),table=<users>,primary_key=True,nullable=False),Column('name',VARCHAR(length=40),table=<users>),Column('email',VARCHAR(length=120),table=<users>),schema=None)
>>>
實(shí)例化一個(gè)插入句柄
代碼如下:
>>>i=users_table.insert()
>>>i
<sqlalchemy.sql.dml.Insertobjectat0x31bc850>
>>>printi
INSERTINTOusers(id,name,email)VALUES(?,?,?)
>>>i.execute(name='rsj217',email='rsj21@gmail.com')
2014-01-0910:24:02,250INFOsqlalchemy.engine.base.EngineINSERTINTOusers(name,email)VALUES(?,?)
2014-01-0910:24:02,250INFOsqlalchemy.engine.base.Engine('rsj217','rsj21@gmail.com')
2014-01-0910:24:02,251INFOsqlalchemy.engine.base.EngineCOMMIT
<sqlalchemy.engine.result.ResultProxyobjectat0x31bce10>
>>>i.execute({'name':'ghost'},{'name':'test'})
2014-01-0910:24:57,537INFOsqlalchemy.engine.base.EngineINSERTINTOusers(name)VALUES(?)
2014-01-0910:24:57,537INFOsqlalchemy.engine.base.Engine(('ghost',),('test',))
2014-01-0910:24:57,537INFOsqlalchemy.engine.base.EngineCOMMIT
<sqlalchemy.engine.result.ResultProxyobjectat0x31bcd50>
>>>
數(shù)據(jù)庫(kù)內(nèi)容為
代碼如下:
sqlite>select*fromusers;
1|rsj217|rsj21@gmail.com
2|ghost|
3|test|
sqlite>
查詢刪除和插入類似都需要先實(shí)例一個(gè)sqlalchemy.sql.dml對(duì)象
三使用ORM
使用orm就是將pythonclass與數(shù)據(jù)庫(kù)的table映射，免去直接寫sql語(yǔ)句
創(chuàng)建映射
代碼如下:
>>>classUser(object):
...def__repr__(self):
...return'%s(%r,%r)'%(self.__class__.__name__,self.name,self.email)
...
>>>mapper(User,users_table)#創(chuàng)建映射
<Mapperat0x31bcfd0;User>
>>>ul=User()
>>>ul.name
>>>printul
User(None,None)
>>>printul.name
None
>>>
建立會(huì)話
查詢
代碼如下:
>>>session=create_session()
>>>session
<sqlalchemy.orm.session.Sessionobjectat0x31bef10>
>>>query=session.query(User)
>>>query
<sqlalchemy.orm.query.Queryobjectat0x31bee50>
>>>u=query.filter_by(name='rsj217').first()
2014-01-0910:44:23,809INFOsqlalchemy.engine.base.EngineSELECTusers.idASusers_id,users.nameASusers_name,users.emailASusers_email
FROMusers
WHEREusers.name=?
LIMIT?OFFSET?
2014-01-0910:44:23,809INFOsqlalchemy.engine.base.Engine('rsj217',1,0)
>>>u.name
u'rsj217'
>>>
插入
代碼如下:
>>>fromsqlalchemyimport*
>>>fromsqlalchemy.ormimport*
>>>engine=create_engine('sqlite:///./sqlalchemy.db')
>>>metadata=MetaData(engine)
>>>users_table=Table('users',metadata,autoload=True)
>>>classUser(object):pass
...
>>>mapper(User,users_table)
<Mapperat0x18185d0;User>
>>>Session=sessionmaker(bind=engine)
>>>session=Session()
>>>u=User()
>>>u.name='new'
>>>session.add(u)
>>>session.flush()
>>>session.commit()
>>>
注意建立會(huì)話的方式，sqlalchemy的版本不同sessionmaker的方式更好
剩下刪除關(guān)系事物等高級(jí)操作就參考官方文檔了。