数据对象操作SQLAlchemy ----创建表

1. 创建表 from sqlalchemy import MetaData from sqlalchemy import (Table,Column,Integer,Numeric,String,ForeignKey,create_engine,connectors) from datetime import datetime from sqlalchemy import DateTime metadata=MetaData() #创建metadata对象 cookies = Table('cookies', metadata, Column('cookie_id', Integer(), primary_key=True), Column('cookie_name', String(50), index=True), Column('cookie_recipe_url', String(255)), Column('cookie_sku', String(55)), Column('quantity', Integer()), Column('unit_cost', Numeric(12, 2)) ) users = Table('users', metadata, Column('user_id', Integer(), primary_key=True), Column('customer_number', Integer(), autoincrement=True), Column('username', String(15), nullable=False, unique=True), Column('email_address', String(255), nullable=False), Column('phone', String(20), nullable=False), Column('password', String(25), nullable=False), Column('created_on', DateTime(), default=datetime.now), Column('updated_on', DateTime(), default=datetime.now, onupdate=datetime.now) ) orders=Table('orders',metadata, Column('order_id',Integer(),primary_key=True), Column('user_id',ForeignKey('users.user_id')) #外键user_id是ForeignKey(users.user_id的主键) ) line_items = Table('line_items', metadata, Column('line_items_id', Integer(), primary_key=True), Column('order_id', ForeignKey('orders.order_id')), Column('cookie_id', ForeignKey('cookies.cookie_id')), Column('quantity', Integer()), Column('extended_cost', Numeric(12, 2)) ) engine=create_engine('mysql+mysqlconnector://root:root@localhost:3306/imooc') #连接数据库 metadata.create_all(engine) #create_all创建全部的表

2. 插入/更新/删除等操作

插入数据有3种方法：insert

from sqlalchemy import MetaData from sqlalchemy import (Table,Column,Integer,Numeric,String,ForeignKey,create_engine,connectors) from datetime import datetime from sqlalchemy import DateTime metadata=MetaData() cookies = Table('cookies', metadata, Column('cookie_id', Integer(), primary_key=True), Column('cookie_name', String(50), index=True), Column('cookie_recipe_url', String(255)), Column('cookie_sku', String(55)), Column('quantity', Integer()), Column('unit_cost', Numeric(12, 2)) ) engine=create_engine('mysql://root:root@localhost/imooc') connection=engine.connect() #插入数据方法1==statement语法插入 ins=cookies.insert().values( cookie_name="chocolate chip", cookie_recipe_url="http://some.aweso.me/cookie/recipe.html", cookie_sku="CC01", quantity="12", unit_cost="0.50" ) print(str(ins)) #打印插入ins的值 print ins.compile().params #compile()是以ins objeect对象返回 #执行插入语句 result = connection.execute(ins) #插入方法2：导入insert方法插入 from sqlalchemy import insert ins = insert(cookies).values( cookie_name="dark chocolate chip", cookie_recipe_url="http://some.aweso.me/cookie/recipe_dark.html", cookie_sku="CC02", quantity="1", unit_cost="0.75" ) result = connection.execute(ins) result.inserted_primary_key #插入方法3：values在执行语句中 ins = cookies.insert() result = connection.execute( ins, cookie_name='kk chocolate chip', cookie_recipe_url='http://some.aweso.me/cookie/recipe_kk.html', cookie_sku='CC04', quantity='2', unit_cost='2.2' ) result = connection.execute(ins) result.inserted_primary_key #批量插入 ins = cookies.insert() inventory_list = [ { 'cookie_name': 'peanut butter', 'cookie_recipe_url': 'http://some.aweso.me/cookie/peanut.html', 'cookie_sku': 'PB01', 'quantity': '24', 'unit_cost': '0.25' }, { 'cookie_name': 'oatmeal raisin', 'cookie_recipe_url': 'http://some.okay.me/cookie/raisin.html', 'cookie_sku': 'EWW01', 'quantity': '100', 'unit_cost': '1.00' } ] result=connection.execute(ins,inventory_list) #ins是方法，inventory_list是批量插入的值

查询数据：select

from sqlalchemy import MetaData from sqlalchemy import (Table,Column,Integer,Numeric,String,ForeignKey,create_engine,connectors) metadata=MetaData() cookies = Table('cookies', metadata, Column('cookie_id', Integer(), primary_key=True), Column('cookie_name', String(50), index=True), Column('cookie_recipe_url', String(255)), Column('cookie_sku', String(55)), Column('quantity', Integer()), Column('unit_cost', Numeric(12, 2)) ) engine=create_engine('mysql://root:root@localhost/imooc') connection=engine.connect() #select取出所有数据 from sqlalchemy.sql import select s = cookies.select() rp = connection.execute(s) results = rp.fetchall() print results first_row=results[0] #下面3条都取出cookie_name print first_row[1] print first_row.cookie_name print first_row[cookies.c.cookie_name] print "++++++++++++++++++++++++++++++++++++++++" #select取出部分字段数据 s=select([cookies.c.cookie_name,cookies.c.cookie_sku,cookies.c.quantity]) rp=connection.execute(s) results=rp.fetchall() print results print '++++++++++++++++++++++++++++++++++++++++' #返回的结果 #[('chocolate chip', 'CC01', 12L), ('dark chocolate chip', 'CC02', 1L)] #取出每条信息 # 它可以类似字典一样取出字段值：record.cookie_name,字段名就相当于key.取出所有值中字段名为cookie_name的值 #而未导入sqlalchemy则，纯python元组是a[0]取数据，字典才会a["key"]取出值 rp=connection.execute(s) for record in rp: print record #结果是元组：('chocolate chip', 'CC01', 12L) print (record.cookie_name) print '++++++++++++++++++++++++++++++++++++++++++' #查看字段名，取出第一条记录 s=select([cookies.c.cookie_name,cookies.c.quantity]) rp=connection.execute(s) print(rp.keys()) #rp.keys()是字段名 结果：['cookie_name', 'quantity'] results=rp.first() #取出第一条记录 print results

排序：order_by

限制返回记录条数：limit()

#排序(升序) s=select([cookies.c.cookie_name,cookies.c.quantity]) s=s.order_by(cookies.c.quantity) #或者合并成下面的一条语句 # s=select([cookies.c.cookie_name,cookies.c.quantity]).order_by(cookies.c.quantity) #降序 from sqlalchemy import desc s=select([cookies.c.cookie_name,cookies.c.quantity]).order_by(desc(cookies.c.quantity)) rp=connection.execute(s) for cookie in rp: print ('{} - {}'.format(cookie.quantity,cookie.cookie_name)) #限制条数 s=select([cookies.c.cookie_name,cookies.c.quantity]) s=s.order_by(cookies.c.quantity) s=s.limit(2) rp=connection.execute(s) print ([result.cookie_name for result in rp])

内嵌入sql函数：sum/count等

from sqlalchemy.sql import select from sqlalchemy import MetaData from sqlalchemy import (Table,Column,Integer,Numeric,String,ForeignKey,create_engine,connectors) metadata=MetaData() cookies = Table('cookies', metadata, Column('cookie_id', Integer(), primary_key=True), Column('cookie_name', String(50), index=True), Column('cookie_recipe_url', String(255)), Column('cookie_sku', String(55)), Column('quantity', Integer()), Column('unit_cost', Numeric(12, 2)) ) engine=create_engine('mysql://root:root@localhost/imooc') connection=engine.connect() #内嵌入sql函数 # 如sum,count等，它打包在column(s)方法中 from sqlalchemy.sql import func s=select([func.sum(cookies.c.quantity)]) rp=connection.execute(s) print rp #rp是一个对象 print(rp.scalar()) #取rp对象里的值 #返回结果是：151 #计数 s=select([func.count(cookies.c.cookie_name)]) rp=connection.execute(s) record=rp.first() print (record.keys()) print (record.count_1) #count总计多少条记录，列名为count_1 #返回结果如下 # [u'count_1'] # 6 #count列也可以用label取个别名 s=select([func.count(cookies.c.cookie_name).label('inventory_count')]) rp=connection.execute(s) record=rp.first() print (record.keys()) print (record.inventory_count) #返回的结果： # ['inventory_count'] # 6

过滤：where

#where过滤条件 #cookies.c. c相当于column s=select([cookies]).where(cookies.c.cookie_name=='chocolate chip') rp=connection.execute(s) record=rp.first() print(record.items()) #like模糊过滤查找 s=select([cookies]).where(cookies.c.cookie_name.like('%chocolate%')) rp=connection.execute(s) for record in rp.fetchall(): print (record.cookie_name) 过滤条件中常用的方法： between(cleft,cright) concat(column_two) distinct() in_([list]) is_(None) contains(string) endswith(string) like(string) startswith(string) ilike(string)

操作：

from sqlalchemy.sql import select from sqlalchemy import MetaData from sqlalchemy import (Table,Column,Integer,Numeric,String,ForeignKey,create_engine,connectors) metadata=MetaData() cookies = Table('cookies', metadata, Column('cookie_id', Integer(), primary_key=True), Column('cookie_name', String(50), index=True), Column('cookie_recipe_url', String(255)), Column('cookie_sku', String(55)), Column('quantity', Integer()), Column('unit_cost', Numeric(12, 2)) ) engine=create_engine('mysql://root:root@localhost/imooc') connection=engine.connect() #字符串连接：'+' s=select([cookies.c.cookie_name,'SKU-'+cookies.c.cookie_sku]) for row in connection.execute(s): print(row) #cast from sqlalchemy import cast s=select([cookies.c.cookie_name,cast((cookies.c.quantity * cookies.c.unit_cost), Numeric(12,2)).label('inv_cost')]) for row in connection.execute(s): print ('{} - {}'.format(row.cookie_name,row.inv_cost)) #布尔型(&,|,and) #若想得到(A<B) & (C<D)用连接更高效 #连接conjunctions # and_ from sqlalchemy import and_,or_,not_ s=select([cookies]).where( and_( cookies.c.quantity>23, cookies.c.unit_cost<0.40 ) ) for row in connection.execute(s): print (row.cookie_name) # or_ s=select([cookies]).where( or_( cookies.c.quantity.between(10,50), cookies.c.cookie_name.contains('chip') ) ) for row in connection.execute(s): print(row.cookie_name)

更新：update

from sqlalchemy import update from sqlalchemy import select from sqlalchemy import MetaData from sqlalchemy import (Table,Column,Integer,Numeric,String,ForeignKey,create_engine,connectors) metadata=MetaData() cookies = Table('cookies', metadata, Column('cookie_id', Integer(), primary_key=True), Column('cookie_name', String(50), index=True), Column('cookie_recipe_url', String(255)), Column('cookie_sku', String(55)), Column('quantity', Integer()), Column('unit_cost', Numeric(12, 2)) ) engine=create_engine('mysql://root:root@localhost/imooc') connection=engine.connect() u=update(cookies).where(cookies.c.cookie_name=='chocolate chip') u=u.values(quantity=(cookies.c.quantity+120)) result=connection.execute(u) print (result.rowcount) s=select([cookies]).where(cookies.c.cookie_name=='chocolate chip') result=connection.execute(s).first() for key in result.keys(): print('{:>20}:{}'.format(key,result[key]))

删除：Delete

from sqlalchemy import delete from sqlalchemy import select from sqlalchemy import MetaData from sqlalchemy import (Table,Column,Integer,Numeric,String,ForeignKey,create_engine,connectors) metadata=MetaData() cookies = Table('cookies', metadata, Column('cookie_id', Integer(), primary_key=True), Column('cookie_name', String(50), index=True), Column('cookie_recipe_url', String(255)), Column('cookie_sku', String(55)), Column('quantity', Integer()), Column('unit_cost', Numeric(12, 2)) ) engine=create_engine('mysql://root:root@localhost/imooc') connection=engine.connect() u=delete(cookies).where(cookies.c.cookie_name=='dark chocolate chip') result=connection.execute(u) print (result.rowcount) s=select([cookies]).where(cookies.c.cookie_name=='dark chocolate chip') result=connection.execute(s) print (len(result))