# -*- coding: utf-8 -*-
# @Time : 2017/4/10 10:08
# @Author : xiaojingjing
# @File : sqlalchemy_tutorial.py
# @Software: PyCharm
import sqlalchemy
#引入此包,方便告诉程序使用什么数据库引擎
from sqlalchemy import create_engine
#根据不同数据库引擎创建一个基类,然后通过集成创建这个表对应的类
from sqlalchemy.ext.declarative import declarative_base
#导入创建类时对应类型的定义
from sqlalchemy import Column,Integer,String
#导入事务处理函数
from sqlalchemy.orm import sessionmaker
#关系操作查询模块
from sqlalchemy import and_,or_
#查看SQLAlchemy当前版本
print(sqlalchemy.__version__)
#创建数据库引擎,这里拿的小型数据库sqlite"'sqlite:///foo.db'"sqlite数据库引擎在create_engine中的参数配置 ; "echo=True"表示数据库操作的原始语句将会被显示出来
engine=create_engine('sqlite:///foo.db',echo=True)
Base=declarative_base()
class User(Base):
__tablename__='user_login'
id=Column(Integer,primary_key=True)
username=Column(String(30))
password=Column(String(30))
def __repr__(self):
return "<User(name='%s',password='%s')>"%(self.username,self.password)
#创建表
# Base.metadata.create_all(engine)
# #插入数据
# ad_user=User(username='one',password='onlyone')
# print(ad_user)
#创建事务,并实例化
Session=sessionmaker(bind=engine)
session=Session()
# session.add(ad_user)
#添加一条查询语句
our_users=session.query(User).filter_by(username='one').first()
#批量添加
# session.add_all(
# [
# User(username='two',password='onlytwo'),
# User(username='three',password='onlythree'),
# User(username='four',password='onlyfour'),
# User(username='five',password='onlyfive')
#
# ])
#
# #提交事务
# session.commit()
#查询一条数据,指定username=two,查询第一条,first()默认为第一条,first(10)为前10条
# print(session.query(User).filter_by(username='two').first())
#查询所有,User为对象,对象里面指定了表名"user_login"
# print(session.query(User).all())
#根据id查询循环打印
# for row in session.query(User).order_by(User.id):
# print(row)
#查找username为('one','two','three')的数据~in
# for row in session.query(User).filter(User.username.in_(['one','two','three'])):
# print(row)
#查找username不为('one','two','three')的数据~not in
# for row in session.query(User).filter(~User.username.in_(['one','two','three'])):
# print(row)
#统计数量,username为“one”的数量
# count=session.query(User).filter(User.username=='one').count()
# print(count)
#And条件判断
# for row in session.query(User).filter(and_(User.username=='one',User.password=='onlyone')):
# print(row)
#Or条件判断
# for row in session.query(User).filter(or_(User.username=='one',User.username=='two')):
# print(row)
#创建带有外键的表
from sqlalchemy import ForeignKey
from sqlalchemy.orm import relationship,backref
class Address(Base):
__tablename__='addresses'
id=Column(Integer,primary_key=True)
email_address=Column(String,nullable=False)
user_id=Column(Integer,ForeignKey('user_login.id'))
user=relationship("User",backref=backref('addresses',order_by=id))
def __repr__(self):
return "<Address(email_address='%s')>"%self.email_address
#执行创建操作
# Base.metadata.create_all(engine)
#对两张表添加数据,并根据外键查询结果
jack=User(username='jack',password='admin')
jack.addresses=[
Address(email_address='jack@yeah.net'),
Address(email_address='towjd@sina.com')]
session.add(jack)
session.commit()
for u,a in session.query(User,Address).\
filter(User.id==Address.user_id).\
filter(Address.email_address=='jack@yeah.net').\
all():
print(u,a)