1、初识
1.1 简介
1、使用Python编写的
2、一个SQL工具包
3、一个ORM框架
4、支持常见的数据库：Microsoft SQL Server,MySQL,Oracle,PostgreSQL,SQLite,Sybase
1.2 安装
使用pip进行快速安装
pip install sqlalchemy
1.3 连接数据库
连接sqlite
from sqlalchemy import create_engine
engine = create_engine('sqlite:///test.db', echo=True)
connection = engine.connect()
连接Mysql
from sqlalchemy import create_engine
engine = create_engine('mysql://user:pwd@localhost/test.db', echo=True)
connection = engine.connect()
pip install mysqlclient
1.4 查询数据库
查询数据库
from sqlalchemy import text
query = text("SELECT * FROM students") # 原始的查询语句
result_set = connection.execute(query)
print(result_set.all())
conn.close()
# engine.dispose()
2、创建表与增加记录
2.1 创建表
使用MetaData，Table，Column以及字段类型在代码中来创建表
from sqlalchemy import create_engine, MetaData, Table, Column, Integer, String
engine = create_engine('sqlite:///test.db', echo=True)
meta = MetaData()
students = Table(
'student', meta,
Column('id', Integer, primary_key=True),
Column('first_name', String),
Column('last_name', String),
)
meta.create_all(engine)
2.2 增加一条记录
INSERT查询
student_insert = students.insert()
insert_tome = students.insert().values(first_name='tom', last_name='Smith')
with engine.connect() as conn:
result = conn.execute(insert_tome)
print(result.inserted_primary_key)
conn.commit()
插入多条数据
student_insert = students.insert()
with engine.connect() as conn:
conn.execute(student_insert, [
{"first_name": "jack", "last_name": "hjack"},
{"first_name": "alice", "last_name": "halice"},
{"first_name": "ecro", "last_name": "hecro"},
])
conn.commit()
3、查询记录
3.1 查询所有记录
SQL查询
SELECT * FROM person
函数查询
person.select()
3.2 提取结果集
循环提取
result = conn.exec(query)
for row in result:
print(row[0])
print(row.id)
使用提取函数
result = conn.exec(query)
result.fetchone()
result.fetchall()
3.3 条件查询
SQL查询
select * from person WHERE birthday > '2000-10-1'
函数表达式
person.select().where(person.c.birthday > '2000-10-1')
db_init.py
from sqlalchemy import create_engine, MetaData, Table, Column, Integer, String
engine = create_engine('sqlite:///test.db', echo=True)
meta = MetaData()
students_table = Table(
'student', meta,
Column('id', Integer, primary_key=True),
Column('first_name', String),
Column('last_name', String),
)
meta.create_all(engine)
select_by_name.py
from db_init import engine, students_table
with engine.connect() as conn:
query = students_table.select().where(students_table.c.id > '2')
result_set = conn.execute(query)
result = result_set.fetchall()
print(result)
复杂查询
from db_init import engine, students_table
from sqlalchemy.sql import and_, or_
with engine.connect() as conn:
# query = students_table.select().where(students_table.c.id > '2').where(students_table.c.first_name == 'ecro')
query = students_table.select().where(
or_(
students_table.c.first_name == 'tom',
and_(
students_table.c.id > '3'
)
)
)
result_set = conn.execute(query)
result = result_set.fetchall()
print(result)
4、更新与删除
4.1 更新记录
SQL语句
UPDATE person SET name = 'Maria' WHERE id = 5
函数表达式
person.update().where(person.c.id == 5).values(name='Maria')
update(person).where(person.c.id == 5).values(name='Maria')
from db_init import engine, students_table
with engine.connect() as conn:
# update_query = students_table.update().values(age="22") # 这个字段下全部更新
update_query = students_table.update().values(age="100").where(students_table.c.id == 4) # 制定更新
conn.execute(update_query)
conn.commit()
4.2 删除所有记录
SQL语句
DELETE FROM person
函数表达式
query = person.delete()
from db_init import engine, students_table
with engine.connect() as conn:
del_query = students_table.delete().where(students_table.c.id == 6) # 制定更新
conn.execute(del_query)
conn.commit()
4.3 删除符合条件的记录
SQL语句
DELETE FROM person WHERE id >6
函数表达式
query = person.delete().where(person.c.id > 6)
5、关联表定义
5.1 一对多的关联表
5.2 一对多关联表的定义
import sqlalchemy
engine = sqlalchemy.create_engine('sqlite:///test.db', echo=True)
meta_data = sqlalchemy.MetaData()
department = sqlalchemy.Table(
"department", meta_data,
sqlalchemy.Column("id", sqlalchemy.Integer, primary_key=True),
sqlalchemy.Column("name", sqlalchemy.String(255), nullable=False, unique=True),
)
employee = sqlalchemy.Table(
"employee", meta_data,
sqlalchemy.Column("id", sqlalchemy.Integer, primary_key=True),
sqlalchemy.Column("department_id", sqlalchemy.Integer, sqlalchemy.ForeignKey("department.id"), nullable=False),
sqlalchemy.Column("name", sqlalchemy.String(255), nullable=False),
)
meta_data.create_all(engine)
插入数据
from db_init import engine, department, employee
with engine.connect() as conn:
conn.execute(department.insert(), [
{"name": "hr"}, {"name": "it "}
])
conn.execute(employee.insert(), [ {"department_id": 1, "name": "Jack"}, {"department_id": 1, "name": "Tom"}, {"department_id": 1, "name": "Mary"}, {"department_id": 2, "name": "alex"}, {"department_id": 2, "name": "zero"}, {"department_id": 2, "name": "alice"}, ])
conn.commit()
6、关联表查询
6.1 查询实例1
查询hr部门的所有员工及部门信息
import sqlalchemy
from db_init import engine, department, employee
with engine.connect() as conn:
join = employee.join(department, department.c.id == employee.c.department_id)
query = sqlalchemy.select(join).where(department.c.name == 'hr')
print(conn.execute(query).fetchall())
6.2 查询实例2
查询hr部门的所有员工信息
import sqlalchemy
from db_init import engine, department, employee
with engine.connect() as conn:
join = employee.join(department, department.c.id == employee.c.department_id)
# query = sqlalchemy.select(join).where(department.c.name == 'hr')
query = sqlalchemy.select(employee).select_from(join).where(department.c.name == 'hr')
print(conn.execute(query).fetchall())
6.3 查询示例3
查询员工Mary所在的部门信息
import sqlalchemy
from db_init import engine, department, employee
with engine.connect() as conn:
join = employee.join(department, department.c.id == employee.c.department_id)
# query = sqlalchemy.select(join).where(department.c.name == 'hr')
# query = sqlalchemy.select(employee).select_from(join).where(department.c.name == 'hr')\
query = sqlalchemy.select(department).select_from(join).where(employee.c.name == 'alice')
print(conn.execute(query).fetchall())
7、映射类定义与添加记录
7.1 映射类的基础
使用declarative_base
from sqlalchemy import create_engine, Column, Integer, String, Date
from sqlalchemy.ext.declarative import declarative_base
engine = create_engine('sqlite:///test.db', echo=True)
Base = declarative_base()
class Person(Base):
__tablename__ = "person"
id = Column(Integer, primary_key=True)
name = Column(String(128), unique=True, nullable=False)
birthday = Column(Date, nullable=False)
address = Column(String(255), nullable=False)
Base.metadata.create_all(engine)
7.2 基于映射类添加记录
使用sessions代替connection
from sqlalchemy.orm import sessionmaker
Session = sessionmaker(bind=engine)
添加单条记录
session = Session()
p = Person(name = "Jack",birthday = "2000-1-20",address="somewhere")
session.add(p)
from sqlalchemy import create_engine, Column, Integer, String, Date
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
engine = create_engine('sqlite:///test.db', echo=True)
Base = declarative_base()
class Person(Base):
__tablename__ = "person"
id = Column(Integer, primary_key=True)
name = Column(String(128), unique=True, nullable=False)
birthday = Column(Date, nullable=False)
address = Column(String(255), nullable=False)
Base.metadata.create_all(engine)
Session = sessionmaker(bind=engine)
from db_init import Session, Person
session = Session()
p = Person(name="amy", birthday="2000-10-11", address="unknown")
session.add(p)
session.commit()
7.3 批量添加记录
添加多条记录
session = Session()
p = [
Person(name="amy", birthday="2000-10-11", address="unknown"),
Person(name="amy", birthday="2000-10-11", address="unknown")
]
session.add_all(p) ＃添加数组
8、映射类查询与修改
8.1 基于映射类的查询
查询所有记录
result = session.query(Person).all()
条件查询
result = session.query(Person).filter(Person.address == 'aaa')
eg：
from db_init import Session, Person
session = Session()
# result = session.query(Person).all()
result = session.query(Person).filter(Person.address == '11')
for person in result:
print(f'name:{person.name},birthday:{person.birthday}')
8.2 单条记录查询的返回
使用first（） 结果集第一条记录（结果集多条记录）
result = session.filter(Person.id < 12).first()
使用one（） 结果集只有一条记录
result = session.filter(Person.id == 12).one()
使用scalar（）
result = session.filter(Person.id == 12).scalar()
8.3 修改单条记录
方法一
person = session.query(Person).filter(Person.id == 1).one()
person.address = 'ccc'
方法二
session.query(Person).filter(Person.id == 1).update({Person.address:"bbb"})
