Created
May 21, 2020 02:53
-
-
Save taojy123/53c57c5ecf80ad9cbf08c8871ce32f72 to your computer and use it in GitHub Desktop.
pymysql + sqlalchemy
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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