SQLAlchemy是python操作數(shù)據(jù)庫的一個庫。能夠進行orm映射，SQLAlchemy“采用簡單的Python語言，為高效和高性能的數(shù)據(jù)庫訪問設計，實現(xiàn)了完整的企業(yè)級持久模型”

SQLAlchemy的理念是，SQL數(shù)據(jù)庫的量級和性能重要于對象集合；而對象集合的抽象又重要于表和行。

一安裝SQLAlchemy

代碼如下:

pipinstallsqlalchemy

導入如果沒有報錯則安裝成功

代碼如下:>>>importsqlalchemy

>>>sqlalchemy.__version__

'0.9.1'

>>>

二使用sqlalchemy對數(shù)據(jù)庫操作

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ù)庫

代碼如下:

>>>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)建一個users表，有id，name，email三個字段

代碼如下:

(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.基本操作，插入

如果已經table表已經存在，第二次運行就不許要create了，使用autoload設置

代碼如下:

>>>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)

>>>

實例化一個插入句柄

代碼如下:

>>>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ù)庫內容為

代碼如下:

sqlite>select*fromusers;

1|rsj217|rsj21@gmail.com

2|ghost|

3|test|

sqlite>

查詢刪除和插入類似都需要先實例一個sqlalchemy.sql.dml對象

三使用ORM

使用orm就是將pythonclass與數(shù)據(jù)庫的table映射，免去直接寫sql語句

創(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

>>>

建立會話

查詢

代碼如下:

>>>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()

>>>

注意建立會話的方式，sqlalchemy的版本不同sessionmaker的方式更好

剩下刪除關系事物等高級操作就參考官方文檔了。

更多信息請查看IT技術專欄