sqlalchemy练习（二）

# -*- coding: utf-8 -*-
# @Time    : 2017/4/10 14:33
# @Author  : xiaojingjing
# @File    : sqlalchemy_core.py
# @Software: PyCharm
import sqlalchemy
print(sqlalchemy.__version__)

from sqlalchemy import create_engine
from sqlalchemy import Table,Column,String,Integer,MetaData,ForeignKey

engine=create_engine('sqlite:///fooo.db',echo=True)

#实例化metadata类
mentadata=MetaData()

users=Table(
    'users',mentadata,
    Column('id',Integer,primary_key=True),
    Column('name',String),
    Column('fullname',String)
)

addresses=Table(
    'addresses',mentadata,
    Column('id',Integer,primary_key=True),
    Column('user_id',None,ForeignKey('users.id')),
    Column('email_Address',String,nullable=False)
)
#执行创建表操作
mentadata.create_all(engine)

#建立数据库引擎连接
conn=engine.connect()

#执行插入操作
# conn.execute(users.insert(),[dict(name='peter',fullname='peter Jones'),dict(name='dack',fullname='dack Willer')])

# conn.execute(addresses.insert(),[
#     {'user_id':1,'email_Address':'[e-mail]'},
#     {'user_id':1,'email_Address':'[e-mail]'},
#     {'user_id':2,'email_Address':'[e-mail]'},
#     {'user_id':2,'email_Address':'[e-mail]'}
# ])

#查询操作

#引用select模块
from sqlalchemy.sql import select

# s=select([users])
# result=conn.execute(s)
# for row in result:
#     print(row)

#外键关联查询
# s=select([users,addresses]).where(users.c.id==addresses.c.user_id)
# for row in conn.execute(s):
#     print(row)

from sqlalchemy.sql import text

s=text(
    """
    select users.fullname||','||addresses.email_Address As title
    FROM users,addresses
    WHERE users.id=addresses.user_id
    AND users.name BETWEEN :x AND :y
    AND (addresses.email_Address LIKE :e1)
    OR (addresses.email_Address LIKE :e2)
    """
)


print(conn.execute(s,x='m',y='z',e1='.com',e2='sina.com').fetchall())