# -*- 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':'xiaohui@sina.com'},
# {'user_id':1,'email_Address':'bighui@sina.com'},
# {'user_id':2,'email_Address':'xiaohui@yeah.net'},
# {'user_id':2,'email_Address':'bighui@yeah.net'}
# ])
#查询操作
#引用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())