sqlalchemy整理（二）

连接数据库，以及数据库操作合集

1.page_table =schema.Table('page',metadata, autoload=True) 表和metadata绑定，因为数据库中存在了，所以直接autoload就可以了

2.数据库的插入等操作，都是最基本的api，还有一个类似 endswith startswith等函数，很好用的

3.and_ or_ 等操作可以喝& | 互换

4.添删改查都有了，其中查询非常的多

代码如下，写的自我感觉很详细，需要先运行sqlalchemy整理（一）中的例子，创建数据库

from sqlalchemy.engine import  create_engine
from sqlalchemy import schema, types,select,delete,update
#from db_init import artical_table,page_table

############table connection##############
engine=create_engine("sqlite:///hello.db",echo=False)
metadata=schema.MetaData(engine)
page_table =schema.Table('page',metadata, autoload=True)
connection = engine.connect()

###########table insert one ####################
ins=page_table.insert()
ins.execute(name=u'test1', title=u'Test Page', content=u'Some content!')
connection.execute(ins,name=u'test', title=u'Test Page', content=u'Some content!')
connection.execute(page_table.insert(),name=u'test1', title=u'Test Page', content=u'Some content!')
########## tbale insert two######################

######## table select ##########################

#fetchall
s = select([page_table])
result = connection.execute(s)
rows = result.fetchall()
print rows

print "*"*20
#use where 
s = select([page_table]).where(page_table.c.id==1) 
result = connection.execute(s)
for row in result:
    print  row


#use where 
from sqlalchemy.sql import and_
from sqlalchemy.sql import or_
from sqlalchemy.sql import not_
from sqlalchemy.sql import func
#use and_
s=select([page_table],and_(page_table.c.id>3,page_table.c.id<10)) 
print "#"*5
print s
result = connection.execute(s)
for row in result:
    print  row
print "#"*5
#use or_
s=select([page_table],or_(page_table.c.id==1,page_table.c.id==2))
print s
print "#"*5
print s
result = connection.execute(s)
for row in result:
    print  row
print "#"*5
#use not_
s=select([page_table],not_(page_table.c.id==10))
result=connection.execute(s)
for row in result:
    print row


#use startswith
print "use startswith"
s=page_table.select(page_table.c.name.startswith("te"))
result=connection.execute(s)
for row in result:
    print row

#use like
print "use like"
s=page_table.select(page_table.c.name.like("te%"))
result=connection.execute(s)
for row in result:
    print row


#use like
print "use like"
s=page_table.select(page_table.c.name.like("te%"))
result=connection.execute(s)
for row in result:
    print row

#use end with
print "use endswith"
s=select([page_table.c.name,page_table.c.id],page_table.c.name.endswith("t"))
result=connection.execute(s)
for row in result:
    print row

#use end with
print "count"
s=select([func.count(page_table.c.id)])
result=connection.execute(s)
for row in result:
    print row


#use delete
s=page_table.delete().where(page_table.c.id>10)
connection.execute(s)
print s


#fetchall
s = select([page_table])
result = connection.execute(s)
rows = result.fetchall()
print rows

#use  update
s=page_table.update().values(title='xluren').where(id>0)
connection.execute(s)
print s

#fetchall
s = select([page_table])
result = connection.execute(s)
rows = result.fetchall()
print rows
''' all ways of selecting a column
    s = users.select(users.c.name == 'John')
    s = users.select(users.c.age < 40)
    s = users.select(and_(users.c.age < 40, users.c.name != 'Mary'))
    s = users.select(or_(users.c.age < 40, users.c.name != 'Mary'))
    s = users.select(not_(users.c.name == 'Susan'))
    s = users.select((users.c.age < 40) & (users.c.name != 'Mary'))
    s = users.select((users.c.age < 40) | (users.c.name != 'Mary'))
    s = users.select(~(users.c.name == 'Susan'))
    s = users.select(users.c.name.startswith('M'))
    s = users.select(users.c.name.like('%a%'))
    s = users.select(users.c.name.endswith('n'))
    s = users.select(users.c.age.between(30,39))
    s = users.select(users.c.name.in_('Mary', 'Susan'))
    s = users.select(func.substr(users.c.name, 2, 1) == 'a')
    s = select([users], users.c.name != 'Carl')
    s = select([users.c.name, users.c.age], users.c.name != 'Carl')
    s = select([func.count(users.c.user_id)])
    s = select([func.count("*")], from_obj=[users])
'''