sqlalchemy学习总结

1.
   from sqlalchemy import create_engine
  engine = create_engine('sqlite:///:memory:', echo=True)
链接mysql：   mysql_engine = create_engine('mysql://root:123456@localhost:3306/test?charset=utf8',encoding = "utf-8",echo =True)
      create_engine( ‘ 数据库 : //用户名 :  密码(没有密码则为空)@主机名: 端口/数据库名 ' , echo =True)


 engine.execute("select 1")    执行连接数据库，并显示sql操作语言
2.
  SQL操作
    from sqlalchemy import *
  定义表结构
   >>> users = Table('users', metadata,
...     Column('id', Integer, primary_key=True),  #主键
...     Column('name', String(50)),
... )


>>> addresses = Table('addresses', metadata,
...   Column('id', Integer, primary_key=True),
...   Column('user_id', None, ForeignKey('users.id')),   #外键
...   Column('email_address', String(50), nullable=False)
...  )
   metadata.create_all(engine)   #建表
3.
   插入数据  注意此时并没有执行插入数据
   ins = users.insert()    可以直接按顺序写('jim', '123')
    可以用values()来限制插入数据  ins = users.insert().values(name='jim')
   ins.compile().params   返回插入的值为字典形式{'name': 'jim'},只有用values()插入数据时，才可这样查询
4.
   执行
   conn = engine.connect()
   result = conn.execute(ins)   #此时才真向数据库中插入数据
  或者可以直接这样，不用values()
 ins = users.insert()
  conn.execute(ins, name='wendy')
下面这样，可以一次性的插入多条数据
>>> conn.execute(addresses.insert(), [
...    {'user_id': 1, 'email_address' : 'jack@yahoo.com'},
...    {'user_id': 2, 'email_address' : 'wendy@aol.com'},
... ])
5.
  查询
 s = select([users])   # users为Table()对象
result = conn.execute(s)
for row in result:
    print  row
row = result.fetchone()   迭代生成元组  注意不要和上面的连用，上面已经迭代完成，这里再用会报错
result.close() 关闭迭代
局部查询
s = select( [ users.c.name ，] )
result = conn.execute(s)   结果是只包含此列值的元组
条件查询
ins = select([users1]).where(users1.c.id==1)   注意后面是'=='双等号，且可以多重where选择
s = select([users, addresses]).where(users.c.id == addresses.c.user_id)
排序
stmt = select([users.c.name]).order_by(users.c.name)
conn.execute(stmt).fetchall()  列出全部，格式为字典，每行为一个元组
6.
修改数据
>>> stmt = users.update().values(name='aa')   将整列的数据改为‘aa’
stmt = users.update().\
...             where(users.c.name == 'jack').\
...             values(name='ed')    按条件查找后修改符合条件的数据
>>> conn.execute(stmt)
当要同时插入或修改多条数据时，用bindparam()来给类名起个key名，然后再每行对应的插入字典
>>> stmt = users.update().\
...             where(users.c.name == bindparam('oldname')).\
...             values(name=bindparam('newname'))
>>> conn.execute(stmt, [
...     {'oldname':'jack', 'newname':'ed'},
...     {'oldname':'wendy', 'newname':'mary'},
...     {'oldname':'jim', 'newname':'jake'},
...     ])
7.
  删除数据
conn.execute(addresses.delete())   清空表addresses
result = conn.execute(users.delete().where(users.c.name > 'm'))  删除满足条件的数据   注意先delete后where
8.
result.rowcount  得到操作影响的行数
2.
  >>> from sqlalchemy.ext.declarative import declarative_base
>>> Base = declarative_base()   建立基类
>>> from sqlalchemy import Column, Integer, String
>>> class User(Base):
...     __tablename__ = 'users'
...
...     id = Column(Integer, primary_key=True)
...     name = Column(String)       #此处是sqlite所以不用写数，mysql要写Column(String(50))
...     fullname = Column(String)
...     password = Column(String)
...
...     def __init__(self, name, fullname, password):    #__init__可以不定义，Base会自动执行,但传值时要用参数传值
...         self.name = name
...         self.fullname = fullname
...         self.password = password
...
...     def __repr__(self):                           #写不写无所谓
...        return "<User('%s','%s', '%s')>" % (self.name, self.fullname, self.password)
 Base.metadata.create_all(engine)  #建表


>>> ed_user = User('ed', 'Ed Jones', 'edspassword')  #实例化并传入值，注意此时User不自定义__init__也可执行，但要用参数传值(name='ed', fullname='Ed Jones', pa....)，达到同样效果
>>> ed_user.name
'ed'
>>> ed_user.password
'edspassword'
>>> str(ed_user.id)    #因并没有将数据插入到数据库中，故id值为空
'None'
3.
a.
   >>> from sqlalchemy.orm import sessionmaker
>>> Session = sessionmaker(bind=engine)
创建Session并bind定engine
b.
session = Session()
c.
>>> ed_user = User('ed', 'Ed Jones', 'edspassword')
>>> session.add(ed_user)
d,
 our_user = session.query(User).filter_by(name='ed').first() #或者' .all() '在数据库中插入数据并查询出来，结果就是ed_user
4.
>>> session.add_all([
...     User('wendy', 'Wendy Williams', 'foobar'),
...     User('mary', 'Mary Contrary', 'xxg527'),
...     User('fred', 'Fred Flinstone', 'blah')])
同时加入多个值
ed_user.password = 'f8s7ccs' 改已经加如到session中的数据的值的话
session.dirty   会保存，更改的数据
session.new    会保存，新加入的数据
 session.commit()  执行所有更改
5.
  MySQL的session.rollback()貌似不管用
6.
session.query(User)可用以下三种方式访问
方式一for循环遍历，注意其本身可能是生成器但 不能用next() 等方法得到值。
        for instance in session.query(User).order_by(User.id):      当然也可以不接order_by等    order_by(desc(User.id))  倒序排列
                print instance.name
            for name, fullname in session.query(User.name, User.fullname):   也可以直接先制定列
                      print name, fullname
            for instance in session.query(User).filter_by(name='alen'):    按条件查找，
            for name, in session.query(User).filter(User.name=='Ed Jones').filter(User.fullname=='aa')  注意与filter_by区别，要加对象User.name, 是双等号'==',
            filter_by与filter都可以连着用，且可以混合使用
@@@filter很重要，有多个参数可选，查手册
        还可以直接用字符串：
              for user in session.query(User).\
...             filter("id<224").\
...             order_by("id").all():             order_by也可直接接受字符串,   order_by(desc('id'))   倒序排列
             session.query(User).filter("id<:value and name=:name").\          注意当时等于时，只有一个等号，而不是双等号，SQL语句中应该没有双等号
...     params(value=224, name='fred').order_by(User.id).one()    通过params来传入参数，动态控制筛选，之前的变量用‘ : key ’代替而不是‘%s’
         session.query(User).from_statement(             此函数可以直接接受SQL语句来查询
...                     "SELECT * FROM users where name=:name").\
...                     params(name='ed').all()
[<User('ed','Ed Jones', 'f8s7ccs')>]
方式二  session.query(User)[0].name
方式三jack = session.query(User).get(5)       get()中的值时primary_key既id的值,如果没有对象：不会报错
方式四session.query(User).all()    .one()   all得到的是个列表，one得到的是一个对象，并且如果多了或者没有会报错
7.
   qq = session.query(User).filter()      在filter里不写条件则筛选出所有的值的：一个对象
   qq.all()  返回一个列表，里面是所有删选出的对象
   qq.one() 返回对象必须是单一的，否则报错
   qq.first()  返回一个对象，且是列表的第一个值     可以用于查找是否存在，如果存在可直接用。如果不存在也不会报错
8.
    session.query(User).filter(User.name.like('%ed')).count()       count()方法返回行数
9.
    session.query(User).group_by(User.sex).all()   去除重复值，只返回无重复的第一次出现的数据对象
10.
    表间关系：
>>> from sqlalchemy import ForeignKey
>>> from sqlalchemy.orm import relationship, backref
    OneToMany和ManyToOne      :    ForeignKey在Many方，relationship在主表当中，如果加上backref=‘users’参数，可实现两表正反都有关系，一般要加。如果在从表中写relationship时要用 backref=backref('addresses', order_by=id)
    OneToOne ：在relationship中加个参数uselist=False实现
    ManyToMany  要单独建一个表，来储存两个id。本笔记最后详细记载步骤
  手册：http://docs.sqlalchemy.org/en/rel_0_8/orm/relationships.html#relationship-patterns
11.
插入数据
a.
>>> jack = User('jack', 'Jack Bean', 'gjffdd')
>>> jack.addresses
[]                       一开始为空
b.
>>> jack.addresses = [
...                 Address(email_address='jack@google.com'),
...                 Address(email_address='j25@yahoo.com')]
赋值
当主表为One时，此属性为列表，向上述一样赋值
当主表为Many是，此属性为一个对象    jack.addresses = Address(.....)  直接赋值，不是列表
c.
>>> session.add(jack)
>>> session.commit()       此时不仅users表插入数据，address表也插入了数据
12.
表间查询
>>> jack = session.query(User).\
... filter_by(name='jack').one()
jack.address为列表  可以随意查询 jack.address[0].email
jack.address.append( Address('11') )   #用append来添加对象
逆向查询时，如果address为一个数据对象，address.user 可查到user的单一数据对象
13.
session.query(User, Address).\
...                     filter(User.id==Address.user_id).\
...                     filter(Address.email_address=='jack@google.com').\
...                     all():
可以用join来简单的实现
>>> session.query(User).join(Address).\
...         filter(Address.email_address=='jack@google.com').\
...         all()
14
func.count()   查询数量的函数
15.
http://docs.sqlalchemy.org/en/rel_0_8/orm/tutorial.html#common-relationship-operators
16.
session.delete(jack)   删除数据，但此时从表的数据不会被删除
addresses = relationship("Address", backref='user', cascade="all, delete, delete-orphan")   设置表间关系时，加上cascade参数，后从表数据会随着主表数据删除而删除
del jack.addresses[1]    这样也能从主表删除从表数据了
17.
实现ManyToMany
a.
>>> from sqlalchemy import Table, Text
>>> # association table
>>> post_keywords = Table('post_keywords', Base.metadata,
...     Column('post_id', Integer, ForeignKey('posts.id')),
...     Column('keyword_id', Integer, ForeignKey('keywords.id'))
... )                    #建一张表用来储存两表关系
b.
keywords = relationship('Keyword', secondary=post_keywords, backref='posts')       relationship加个参数secondary
c.
>>> post = BlogPost("Wendy's Blog Post", "This is a test")   主表数据
>>> session.add(post)   加入session
d.
>>> post . keywords . append( Keyword('wendy') )             从主表索引然后添加从表数据
e.
session.query(BlogPost).\
...             filter(BlogPost.keywords.any(keyword='firstpost')).\
...             all()            执行插入数据，并查询
18.
   当session.query(User).get(1)时出现了异常时 TypeError: 'str' object is not callable
    很可能是__repr__函数里出错
19.
  加参数cascade实现级联删除时，如果relationship在Many一方时，还要加参数single_parent=True
   ArgumentError: On Reply.user, delete-orphan cascade is not supported on a many-to-many or many-to-one relationship when single_parent is not set.   Set single_parent=True on the relationship().
20.
    reply = relationship('Reply', backref='note', cascade='all, delete, dele    te-orphan', order_by=desc('id'))     在relationship中也有参数order_by,加上desc后可实现倒序排列。当然在 backref=backref('note',  order_by='')  中也有order_by