使用python SQLAlchemy实现数据库访问，增删改查。

以前自己使用python访问mysql 数据库用的都是 pymysql，但是pymysql并不太安全，SQL语句的明文容易有SQL注入的风险。准备换用ORM框架。近期用SQLAlchemy实现了对数据库的访问，分享代码如下。

开源地址：https://github.com/guliangce/MiniPrograms

from sqlalchemy import create_engine
from sqlalchemy.orm import declarative_base
from sqlalchemy import Column, String, Integer, Boolean
from sqlalchemy.orm import sessionmaker

# 1、链接一个数据库引擎 mysql
# 连接数据库名
db_name = '你的数据库'
# 数据库用户名
db_user = '你的账号'
# 数据库密码
db_passwd = '你的密码'
# 数据库地址
db_ip = '你的IP'
# 数据库端口
db_port = 3306

engine = create_engine(f'mysql+pymysql://{db_user}:{db_passwd}@{db_ip}:{db_port}/{db_name}?charset=utf8')

# 2、给数据库创建表

Base = declarative_base()


# 创建学生表
class Student(Base):
    __tablename__ = 'Student'
    sid = Column(Integer, primary_key=True,autoincrement=True)
    sname = Column(String(64), nullable=False, index=True)
    age = Column(Integer, nullable=False)
    sex = Column(Boolean, nullable=False, index=True)

    def __repr__(self):
        return '%s(%r)' % (self.__class__.__name__, self.sname)


# 创建课程表
class Course(Base):
    __tablename__ = 'Course'
    cid = Column(Integer, primary_key=True,autoincrement=True)
    cname = Column(String(64), nullable=False, index=True)
    tid = Column(Integer, nullable=False)

    def __repr__(self):
        return '%s(%r)' % (self.__class__.__name__, self.cname)


# 创建成绩表
class Score(Base):
    __tablename__ = 'Score'
    scid = Column(Integer, primary_key=True,autoincrement=True)
    sid = Column(Integer, nullable=False)
    cid = Column(Integer, nullable=False)
    score = Column(Integer, nullable=False)

    def __repr__(self):
        return '%s(%r)' % (self.__class__.__name__, self.score)


class Teacher(Base):
    __tablename__ = 'Teacher'
    tid = Column(Integer, primary_key=True,autoincrement=True)
    tname = Column(String(64), nullable=False, index=True)

    def __repr__(self):
        return '%s(%r)' % (self.__class__.__name__, self.tname)


Base.metadata.create_all(engine)

#2、插入原始数据
data_student = [
    Student(sname='马大哈',age=36,sex=1),
    Student(sname='李小华',age=28,sex=0),
    Student(sname='张大友',age=20,sex=1)
    ]

data_course = [
    Course(cname='语文',tid=1),
    Course(cname='数学',tid=2),
    Course(cname='化学',tid=3)
    ]

data_score = [
    Score(sid=1,cid=1,score=70),
    Score(sid=1,cid=2,score=46),
    Score(sid=1,cid=3,score=12),
    Score(sid=2,cid=1,score=78),
    Score(sid=2,cid=2,score=57),
    Score(sid=2,cid=3,score=78),
    Score(sid=3,cid=1,score=86),
    Score(sid=3,cid=2,score=98),
    Score(sid=3,cid=3,score=78),
    ]

data_teacher = [
    Teacher(tname='康熙'),
    Teacher(tname='雍正'),
    Teacher(tname='乾隆')
    ]

Session = sessionmaker(bind=engine)
# 实例化
session = Session()

session.add_all(data_student)
session.add_all(data_course)
session.add_all(data_score)
session.add_all(data_teacher)

session.commit()
session.close()

#3、查询表中数据

Session = sessionmaker(bind=engine)
session = Session()
#查询所有老师
print("1、[查询所有老师]：")
for instance in session.query(Teacher).order_by(Teacher.tid):
     print(instance.tid, instance.tname)
#查询所有分数在60分以上的成绩
print("2、[查询所有分数在60分以上的成绩]：")
for instance in session.query(Score).filter(Score.score>=60).order_by(Score.score):
     print(instance.sid,instance.score)
#查询所有语文成绩比数学成绩高的学生的名字
'''
sql:
select 
	*
from 
	student 
	inner join 
    score sc on student.sid = sc.sid and sc.cid = 1 
    inner join 
    score sc2 on student.sid = sc2.sid and sc2.cid = 2
where
	sc.score > sc2.score
'''
dic = {}
for instance in session.query(Student,Score).join(Score,Student.sid == Score.sid ).all():
     if instance[1].cid != 3:
         if instance[0].sname not in dic.keys():
             dic[instance[0].sname] = []
             dic[instance[0].sname].append(instance[1].score)
         else:
             dic[instance[0].sname].append(instance[1].score)
print("3、[查询所有语文成绩比数学成绩高的学生的名字]：")
for ke in dic.keys():
    if dic[ke][0] > dic[ke][1]:
        print(ke)

#4、更新表中的数据
#sql : update score set score.score = 100 where score.sid = 1 and score.cid = 1 and score.scid > 0
#跟新学号为1，课程为1的成绩为100分
obj = session.query(Score).filter(Score.sid==1,Score.cid==1).first()
obj.score = 99
print("4、[跟新学号为1，课程为1的成绩为100分]：")
print(obj.sid,obj.score)
session.commit()

#5、插入
#插入一条名字为嘉庆的老师信息
#sql :insert into teacher (tname) value ('嘉庆')
print("5、[插入一条名字为嘉庆的老师信息]：")
session.add(Teacher(tname='嘉庆'))
obj = session.query(Teacher).filter(Teacher.tname=='嘉庆').first()
print(obj.tid,obj.tname)

#6、删除
#删除所有康熙老师的课程成绩
#sql ：delete from score where score.cid = 1 and score.scid > 0
print("6、[删除所有康熙老师的课程成绩]：")
obj = session.query(Score).filter(Score.cid==1).first()
session.delete(obj)
print("6、[删除成功！]")

#7、清除数据
print("7、[清理环境！]")
session.close()
Base.metadata.drop_all(engine)