Skip to content

Instantly share code, notes, and snippets.

@taojy123
Created May 21, 2020 02:53
Show Gist options
  • Save taojy123/53c57c5ecf80ad9cbf08c8871ce32f72 to your computer and use it in GitHub Desktop.
Save taojy123/53c57c5ecf80ad9cbf08c8871ce32f72 to your computer and use it in GitHub Desktop.
pymysql + sqlalchemy
import sqlalchemy
from sqlalchemy import Column, Integer, String
from sqlalchemy.ext.declarative import declarative_base
import pymysql
pymysql.install_as_MySQLdb()
#object(oop) ---> mysql(SQL)
#1). 创建数据库引擎(连接数据库的过程)
#echo=True显示翻译好的SQL语句。
from sqlalchemy.orm import Session, sessionmaker
engine = sqlalchemy.create_engine("mysql://root:westos@localhost/Blog",
encoding='utf8', echo=True)
#2). 建立缓存
session = sessionmaker(bind=engine)()
#3). 创建数据库对象需要继承的基类
Base = declarative_base()
#4). 创建类, 一个类就是一个数据库表
class Student(Base):
# 数据库表的名称
__tablename__ = 'student_info'
#数据库表的属性信息
id = Column(Integer, primary_key=True, autoincrement=True)
name = Column(String(20))
sex = Column(String(10))
#def __repr__(self):
#return 'Student<%s>' %(self.name)
if __name__ == '__main__':
# 根据设置的信息创建数据库表-(创建类:创建数据库表的过程)
Base.metadata.create_all(engine)
#添加数据信息(实例化对象: 添加一条数据/记录)
#INSERT INTO student_info (id, name, sex) VALUES (%(id)s, %(name)s, %(sex)s)
stu1 = Student(id=2, name='westos2', sex='男')
#stu2 = Student(id=3, name='westos3', sex='男')
#stu3 = Student(id=4, name='westos4', sex='女')
session.add_all([stu1, ])
session.commit()
#查询数据信息
print(session.query(Student).all())
#根据筛选条件查询信息
print(session.query(Student).filter_by(name='westos1').all())
print(session.query(Student).filter_by(name='westos1').first())
print(session.query(Student).filter_by(name='westoshdhwhdewd').first())
#删除数据
stu1 = session.query(Student).filter_by(name='westos2').first()
print("before delete: ", stu1)
session.delete(stu1)
session.commit()
stu1 = session.query(Student).filter_by(name='westos1').first()
print("after delete: ", stu1)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment