数据对象操作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))