1.创建表格

import sqlalchemyfrom sqlalchemy import create_enginefrom sqlalchemy.ext.declarative import declarative_basefrom sqlalchemy import Column, Integer, String engine = create_engine("mysql+pymysql://root:alex3714@localhost/testdb",                                    encoding='utf-8', echo=True)  Base = declarative_base() #生成orm基类 class User(Base):    __tablename__ = 'user' #表名    id = Column(Integer, primary_key=True)    name = Column(String(32))    password = Column(String(64)) Base.metadata.create_all(engine) #创建表结构

　　除上面的创建之外，还有一种创建表的方式，虽不常用，但还是看看吧

from sqlalchemy import Table, MetaData, Column, Integer, String, ForeignKeyfrom sqlalchemy.orm import mapper metadata = MetaData() user = Table('user', metadata,            Column('id', Integer, primary_key=True),            Column('name', String(50)),            Column('fullname', String(50)),            Column('password', String(12))        ) class User(object):    def __init__(self, name, fullname, password):        self.name = name        self.fullname = fullname        self.password = password mapper(User, user) #the table metadata is created separately with the Table construct, then associated with the User class via the mapper() function

2.创建一条数据

1 Session_class = sessionmaker(bind=engine) #创建与数据库的会话session class ,注意,这里返回给session的是个class,不是实例 2 Session = Session_class() #生成session实例 3   4   5 user_obj = User(name="alex",password="alex3714") #生成你要创建的数据对象 6 print(user_obj.name,user_obj.id)  #此时还没创建对象呢，不信你打印一下id发现还是None 7   8 Session.add(user_obj) #把要创建的数据对象添加到这个session里， 一会统一创建 9 print(user_obj.name,user_obj.id) #此时也依然还没创建10  11 Session.commit() #现此才统一提交，创建数据

3.查询

1 my_user = Session.query(User).filter_by(name="alex").first()2 print(my_user)

此时你看到的输出是这样的应该

1 <__main__.User object at 0x105b4ba90>

调用每个字段就可以跟调用对象属性一样

1 print(my_user.id,my_user.name,my_user.password)2  3 输出4 1 alex alex3714

不过刚才上面的显示的内存对象对址你是没办法分清返回的是什么数据的，除非打印具体字段看一下，如果想让它变的可读，只需在定义表的类下面加上这样的代码

1 def __repr__(self):2     return "
     
      " % (3         self.name, self.password)
     

4.修改

1 my_user = Session.query(User).filter_by(name="alex").first()2  3 my_user.name = "Alex Li"4  5 Session.commit()

5.回滚

1 my_user = Session.query(User).filter_by(id=1).first() 2 my_user.name = "Jack" 3   4   5 fake_user = User(name='Rain', password='12345') 6 Session.add(fake_user) 7   8 print(Session.query(User).filter(User.name.in_(['Jack','rain'])).all() )  #这时看session里有你刚添加和修改的数据 9  10 Session.rollback() #此时你rollback一下11  12 print(Session.query(User).filter(User.name.in_(['Jack','rain'])).all() ) #再查就发现刚才添加的数据没有了。13  14 # Session15 # Session.commit()

6.获取所有数据

1 print(Session.query(User.name,User.id).all() )

7.多条件查询

1 objs = Session.query(User).filter(User.id>0).filter(User.id<7).all()

上面2个filter的关系相当于 user.id >1 AND user.id <7 的效果

8.统计和分组

1 Session.query(User).filter(User.name.like("Ra%")).count()

分组

1 from sqlalchemy import func2 print(Session.query(func.count(User.name),User.name).group_by(User.name).all() )

相当于原生sql为

1 SELECT count(user.name) AS count_1, user.name AS user_name2 FROM user GROUP BY user.name

输出为

[(1, 'Jack'), (2, 'Rain')]

9.外键关联

我们创建一个addresses表，跟user表关联

1 from sqlalchemy import ForeignKey 2 from sqlalchemy.orm import relationship 3   4 class Address(Base): 5     __tablename__ = 'addresses' 6     id = Column(Integer, primary_key=True) 7     email_address = Column(String(32), nullable=False) 8     user_id = Column(Integer, ForeignKey('user.id')) 9  10     user = relationship("User", backref="addresses") #这个nb，允许你在user表里通过backref字段反向查出所有它在addresses表里的关联项11  12     def __repr__(self):13         return "
     
      " % self.email_address
     

表创建好后，我们可以这样反查试试

1 obj = Session.query(User).first()2 for i in obj.addresses: #通过user对象反查关联的addresses记录3     print(i)4  5 addr_obj = Session.query(Address).first()6 print(addr_obj.user.name)  #在addr_obj里直接查关联的user表

10.创建关联对象

1 obj = Session.query(User).filter(User.name=='rain').all()[0]2 print(obj.addresses)3  4 obj.addresses = [Address(email_address="r1@126.com"), #添加关联对象5                  Address(email_address="r2@126.com")]6  7  8 Session.commit()

11.filter()常用查询语法

• equals:
  1 query.filter(User.name == 'ed')
• not equals:
  1 query.filter(User.name != 'ed')
• LIKE:
  1query.filter(User.name.like('%ed%'))
• IN:
  1 query.filter(User.name.in_(['ed', 'wendy', 'jack']))2 # works with query objects too:3 query.filter(User.name.in_( session.query(User.name).filter(User.name.like('%ed%'))))
• NOT IN:
  1 query.filter(~User.name.in_(['ed', 'wendy', 'jack']))
• IS NULL:
  1 query.filter(User.name.is_(None))
• IS NOT NULL:
  1 query.filter(User.name.isnot(None))
• AND:
  1 query.filter(and_(User.name == 'ed', User.fullname == 'Ed Jones'))2 3 # or send multiple expressions to .filter()4 query.filter(User.name == 'ed', User.fullname == 'Ed Jones')5 6 # or chain multiple filter()/filter_by() calls7 query.filter(User.name == 'ed').filter(User.fullname == 'Ed Jones')MATCH
• MATCH:
  1 query.filter(User.name.match('wendy'))

 12.多外键关联

下表中，Customer表有2个字段都关联了Address表 

1 from sqlalchemy import Integer, ForeignKey, String, Column 2 from sqlalchemy.ext.declarative import declarative_base 3 from sqlalchemy.orm import relationship 4   5 Base = declarative_base() 6   7 class Customer(Base): 8     __tablename__ = 'customer' 9     id = Column(Integer, primary_key=True)10     name = Column(String)11  12     billing_address_id = Column(Integer, ForeignKey("address.id"))13     shipping_address_id = Column(Integer, ForeignKey("address.id"))14  15     billing_address = relationship("Address") 16     shipping_address = relationship("Address")17  18 class Address(Base):19     __tablename__ = 'address'20     id = Column(Integer, primary_key=True)21     street = Column(String)22     city = Column(String)23     state = Column(String)

创建表结构是没有问题的，但你Address表中插入数据时会报下面的错

1 sqlalchemy.exc.AmbiguousForeignKeysError: Could not determine join2 condition between parent/child tables on relationship3 Customer.billing_address - there are multiple foreign key4 paths linking the tables.  Specify the 'foreign_keys' argument,5 providing a list of those columns which should be6 counted as containing a foreign key reference to the parent table.

解决办法如下

1 class Customer(Base): 2     __tablename__ = 'customer' 3     id = Column(Integer, primary_key=True) 4     name = Column(String) 5   6     billing_address_id = Column(Integer, ForeignKey("address.id")) 7     shipping_address_id = Column(Integer, ForeignKey("address.id")) 8   9     billing_address = relationship("Address", foreign_keys=[billing_address_id])10     shipping_address = relationship("Address", foreign_keys=[shipping_address_id])

这样sqlachemy就能分清哪个外键是对应哪个字段了

13.多对多关系

现在来设计一个能描述“图书”与“作者”的关系的表结构，需求是

1. 一本书可以有好几个作者一起出版
2. 一个作者可以写好几本书

此时你会发现，用之前学的外键好像没办法实现上面的需求了，因为

当然你更不可以像下面这样干，因为这样就你就相当于有多条书的记录了，太low b了，改书名还得都改。。。

 

那怎么办呢？ 此时，我们可以再搞出一张中间表，就可以了

这样就相当于通过book_m2m_author表完成了book表和author表之前的多对多关联

用orm如何表示呢？

1 #一本书可以有多个作者，一个作者又可以出版多本书 2  3  4 from sqlalchemy import Table, Column, Integer,String,DATE, ForeignKey 5 from sqlalchemy.orm import relationship 6 from sqlalchemy.ext.declarative import declarative_base 7 from sqlalchemy import create_engine 8 from sqlalchemy.orm import sessionmaker 9 10 11 Base = declarative_base()12 13 book_m2m_author = Table('book_m2m_author', Base.metadata,14                         Column('book_id',Integer,ForeignKey('books.id')),15                         Column('author_id',Integer,ForeignKey('authors.id')),16                         )17 18 class Book(Base):19     __tablename__ = 'books'20     id = Column(Integer,primary_key=True)21     name = Column(String(64))22     pub_date = Column(DATE)23     authors = relationship('Author',secondary=book_m2m_author,backref='books')24 25     def __repr__(self):26         return self.name27 28 class Author(Base):29     __tablename__ = 'authors'30     id = Column(Integer, primary_key=True)31     name = Column(String(32))32 33     def __repr__(self):34         return self.name

接下来创建几本书和作者

1 Session_class = sessionmaker(bind=engine) #创建与数据库的会话session class ,注意,这里返回给session的是个class,不是实例 2 s = Session_class() #生成session实例 3   4 b1 = Book(name="跟Alex学Python") 5 b2 = Book(name="跟Alex学把妹") 6 b3 = Book(name="跟Alex学装逼") 7 b4 = Book(name="跟Alex学开车") 8   9 a1 = Author(name="Alex")10 a2 = Author(name="Jack")11 a3 = Author(name="Rain")12  13 b1.authors = [a1,a2]14 b2.authors = [a1,a2,a3]15  16 s.add_all([b1,b2,b3,b4,a1,a2,a3])17  18 s.commit()

此时，手动连上mysql，分别查看这3张表，你会发现，book_m2m_author中自动创建了多条纪录用来连接book和author表

1 mysql> select * from books; 2 +----+------------------+----------+ 3 | id | name             | pub_date | 4 +----+------------------+----------+ 5 |  1 | 跟Alex学Python   | NULL     | 6 |  2 | 跟Alex学把妹     | NULL     | 7 |  3 | 跟Alex学装逼     | NULL     | 8 |  4 | 跟Alex学开车     | NULL     | 9 +----+------------------+----------+10 4 rows in set (0.00 sec)11  12 mysql> select * from authors;13 +----+------+14 | id | name |15 +----+------+16 | 10 | Alex |17 | 11 | Jack |18 | 12 | Rain |19 +----+------+20 3 rows in set (0.00 sec)21  22 mysql> select * from book_m2m_author;23 +---------+-----------+24 | book_id | author_id |25 +---------+-----------+26 |       2 |        10 |27 |       2 |        11 |28 |       2 |        12 |29 |       1 |        10 |30 |       1 |        11 |31 +---------+-----------+32 5 rows in set (0.00 sec)

此时，我们去用orm查一下数据

1 print('--------通过书表查关联的作者---------')2  3 book_obj = s.query(Book).filter_by(name="跟Alex学Python").first()4 print(book_obj.name, book_obj.authors)5  6 print('--------通过作者表查关联的书---------')7 author_obj =s.query(Author).filter_by(name="Alex").first()8 print(author_obj.name , author_obj.books)9 s.commit()

输出如下

1 --------通过书表查关联的作者---------2 跟Alex学Python [Alex, Jack]3 --------通过作者表查关联的书---------4 Alex [跟Alex学把妹, 跟Alex学Python]

多对多删除

删除数据时不用管boo_m2m_authors　， sqlalchemy会自动帮你把对应的数据删除

通过书删除作者

1 author_obj =s.query(Author).filter_by(name="Jack").first()2  3 book_obj = s.query(Book).filter_by(name="跟Alex学把妹").first()4  5 book_obj.authors.remove(author_obj) #从一本书里删除一个作者6 s.commit()

直接删除作者

删除作者时，会把这个作者跟所有书的关联关系数据也自动删除

1 author_obj =s.query(Author).filter_by(name="Alex").first()2 # print(author_obj.name , author_obj.books)3 s.delete(author_obj)4 s.commit()

14.处理中文

sqlalchemy设置编码字符集一定要在数据库访问的URL上增加charset=utf8，否则数据库的连接就不是utf8的编码格式

1 eng = create_engine('mysql://root:root@localhost:3306/test2?charset=utf8',echo=True)