sqlalchemy(数据库炼金术)

sqlalchemy ORM

orm英文全称object relational mapping,就是对象映射关系程序,简单来说就是将数据库映射成编程语言可编辑的对象。在python下，就是sqlalchemy orm。

（一）新增

1.新增表

 1 import sqlalchemy
 2 from sqlalchemy import create_engine
 3 from sqlalchemy.ext.declarative import declarative_base
 4 from sqlalchemy import Column, Integer, String
 5 
 6 #调用数据库API，连接数据库。echo为True时，显示过程
 7 engine = create_engine("mysql+pymysql://root:11111111@localhost/testdb",encoding='utf-8', echo=False)
 8 
 9 Base = declarative_base()  # 生成orm基类
10 
11 #映射数据库中的表
12 class User(Base):
13     __tablename__ = 'user'  # 表名
14     id = Column(Integer, primary_key=True)
15     name = Column(String(32))
16     telp= Column(Integer)
17      def __repr__(self):
18         return "<User(name='%s',  password='%s')>" % (
19             self.name, self.telp)
20 
21  
22 
23 # 创建表结构
24 Base.metadata.create_all(engine)  

 

 

2.插入数据

 1 import sqlalchemy
 2 from sqlalchemy import create_engine
 3 from sqlalchemy.ext.declarative import declarative_base
 4 from sqlalchemy import Column, Integer, String
 5 from sqlalchemy.orm import sessionmaker #构建数据
 6 
 7 engine = create_engine("mysql+pymysql://root:11111111@localhost/testdb",encoding='utf-8', echo=False)
 8 Base = declarative_base() 
 9 class User(Base):
10     __tablename__ = 'user' 
11     id = Column(Integer, primary_key=True)
12     name = Column(String(32))
13     telp = Column(Integer)
14 
15 
16 
17     def __repr__(self):
18 
19         return "<User(name='%s',  password='%s')>" % (
20 
21             self.name, self.telp)
22 
23 #Base.metadata.create_all(engine)  这句话这里可以不要，但前面要有。
24 
25 #创建与数据库的会话session ,注意,这里返回给session的是个class,加（）成为实例
26 
27 Session = sessionmaker(bind=engine)() 
28 
29 
30 user_obj = User(name="zl", telp="4")  # 生成你要创建的数据对象
31 user_obj1 = User(name="wen", telp="2")
32 user_obj2 = User(name="bo",telp="3")
33 
34 Session.add(user_obj)  # 把要创建的数据对象添加到这个session里， 一会统一创建
35 Session.add(user_obj1)
36 Session.add(user_obj2)
37 
38 
39 Session.commit()  # 现此才统一提交，创建数据

 

 

（二）删除数据

（三）   修改数据

 

 1 import sqlalchemy
 2 from sqlalchemy import create_engine
 3 from sqlalchemy.ext.declarative import declarative_base
 4 from sqlalchemy import Column, Integer, String
 5 from sqlalchemy.orm import sessionmaker #构建数据
 6 
 7 engine = create_engine("mysql+pymysql://root:11111111@localhost/testdb",encoding='utf-8', echo=False)
 8 Base = declarative_base() 
 9 class User(Base):
10     __tablename__ = 'user' 
11     id = Column(Integer, primary_key=True)
12     name = Column(String(32))
13     telp = Column(Integer)
14     def __repr__(self):
15 
16         return "<User(name='%s',  password='%s')>" % (
17 
18             self.name, self.telp)
19 Session = sessionmaker(bind=engine)()
20 
21 my_user = Session.query(User).filter_by(name="bo").first()
22 my_user.name = "wangwu"
23 Session.commit()

 

 

 

（四）   查询数据

(1)按条件查询filter

 1 import sqlalchemy
 2 from sqlalchemy import create_engine
 3 from sqlalchemy.ext.declarative import declarative_base
 4 from sqlalchemy import Column, Integer, String
 5 from sqlalchemy.orm import sessionmaker #构建数据
 6 
 7 engine = create_engine("mysql+pymysql://root:11111111@localhost/testdb",encoding='utf-8', echo=False)
 8 Base = declarative_base()
 9 class User(Base):
10     __tablename__ = 'user'
11     id = Column(Integer, primary_key=True)
12     name = Column(String(32))
13     telp = Column(Integer)
14 
15     def __repr__(self):
16         return "<User(name='%s',  password='%s')>" % (
17             self.name, self.telp)
18 
19 Session = sessionmaker(bind=engine)()
20 
21 my_user = Session.query(User).filter_by(name="lishi").first()
22 print(my_user)

 

(2)查询所有数据

my_user = Session.query(User).all()

 

(3)多条件查询 filter().filter()….

Print(Session.query(User).filter(User.id>0).filter(User.id<7).all())

（五）   统计 count

print(Session.query(User).filter(User.name.like("z%")).count())

（六）   分组

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

（七）   外键

 1 from sqlalchemy import ForeignKey
 2 from sqlalchemy.orm import relationship
 3 
 4 
 5 class Address(Base):
 6     __tablename__ = 'addresses'
 7     id = Column(Integer, primary_key=True)
 8     email_address = Column(String(32), nullable=False)
 9     user_id = Column(Integer, ForeignKey('user.id'))
10 
11     user = relationship("User", backref="addresses")  # 可在user表里通过backref字段反向查出所有它在addresses表里的关联项
12 
13     def __repr__(self):
14         return "<Address(email_address='%s')>" % self.email_address
15 
16 
17 obj = Session.query(User).first()
18 for i in obj.addresses:  # 通过user对象反查关联的addresses记录
19     print(i)
20 
21 addr_obj = Session.query(Address).first()
22 print(addr_obj.user.name)  # 在addr_obj里直接查关联的user表

转载于:https://www.cnblogs.com/L736/p/8466302.html