SqlAlchemy ORM

from sqlalchemy import Integer,ForeignKey,String,Column 2 from sqlalchemy.ext.declarative import declarative_base 3 from sqlalchemy.orm import relationship 4 from sqlalchemy import create_engine 5 6 engine = create_engine("mysql+pymysql://root:123456@localhost/oldboy?charset=utf8") 7 8 Base = declarative_base() 9 10 11 class Customer(Base): 12 __tablename__ = "customer" 13 id = Column(Integer,primary_key=True) 14 name = Column(String(32)) 15 16 billing_address_id = Column(Integer,ForeignKey("address.id")) 17 shopping_address_id = Column(Integer,ForeignKey("address.id")) 18 19 bill_address = relationship("Address",foregin_keys=[billing_address_id]) 20 shopping_address = relationship("Address",foregin_keys=[shopping_address_id]) 21 22 23 class Address(Base): 24 __tablename__ = "address" 25 id = Column(Integer,primary_key=True) 26 street = Column(String(32)) 27 city = Column(String(32)) 28 state = Column(String(32)) 29 30 31 Base.metadata.create_all(engine) 多外键关联

多对多关系：

 1 from sqlalchemy import Table,Column,Integer,String,DATE,ForeignKey
 2 from sqlalchemy.orm import relationship
 3 from sqlalchemy.ext.declarative import declarative_base
 4 from sqlalchemy import create_engine
 5 from sqlalchemy.orm import sessionmaker
 6 
 7 engine = create_engine("mysql+pymysql://root:123456@localhost/oldboy?charset=utf8",
 8                        encoding="utf8")
 9 
10 Base = declarative_base()
11 
12 # 外键关联表,ORM自动维护,不需要手动维护的一般采用Table这种方式创建
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(32))
22     pub_date = Column(DATE)
23     authors = relationship("Author",secondary=book_m2m_author,backref="books")
24 
25     def __repr__(self):
26         return self.name
27 
28 
29 class Author(Base):
30     __tablename__ = "authors"
31     id = Column(Integer,primary_key=True)
32     name = Column(String(32))
33 
34     def __repr__(self):
35         return self.name
36 
37 Base.metadata.create_all(engine)
38 
39 
40 session_class = sessionmaker(bind=engine)
41 s = session_class()
42 
43 b1 = Book(name="Python自动化")
44 b2 = Book(name="人工智能")
45 b3 = Book(name="心灵鸡汤")
46 b4 = Book(name="读者")
47 
48 a1 = Author(name="Alex")
49 a2 = Author(name="Jack")
50 a3 = Author(name="Rain")
51 
52 b1.authors = [a1,a2]
53 b2.authors = [a1,a2,a3]
54 
55 s.add_all([b1,b2,b3,a1,a2,a3])
56 
57 s.commit()
58 
59 # ------------ 书名查作者 ----------------
60 obj = s.query(Book).filter(Book.name.like("%ython%")).first()
61 print(obj.name,obj.authors)
62 
63 # ------------ 作者查书名 ----------------
64 author_obj = s.query(Author).filter_by(name="Alex").first()
65 print(author_obj.name,author_obj.books)

ORM多对多

多对多删除

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

通过书删除作者

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

通过书删除作者

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

s.commit()

直接删除作者　

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

author_obj =s.query(Author).filter_by(name="Alex").first()

# print(author_obj.name , author_obj.books)

s.delete(author_obj)

s.commit()　

 

处理中文

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

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

 

补充知识点：

• #  排序 
• users = session.query(User).order_by(User.name) 
• #  降序（需要导入desc方法） 
• from sqlalchemy import desc 
• users = session.query(User).order_by(desc(User.name))
  

　

　　

SqlAlchemy ORM

标签：first   latest   htm   reg   close   数据库   排序   转换   remove