实用：python中sqlalchemy数据库框架+pymysql操作mysql数据库

import sqlalchemy
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column,Integer,String

#实体基类
Base = declarative_base()

#实体类
class Student(Base):
    __tablename__ = 'student'
    id = Column(Integer,primary_key=True,autoincrement=True)
    name = Column(String(64),nullable=False)
    age = Column(Integer)

    def __repr__(self):
        return '{} id:{} name:{} age:{}'.format(self.__class__.__name__,self.id,self.name,self.age)

    __str__ =__repr__

host = '172.20.10.11'
port = '3306'
user = 'root'
passwd = '123456'
database = 'test'

conn_str = 'mysql+pymysql://{}:{}@{}:{}/{}'.format(user,passwd,host,port,database)
engine = sqlalchemy.create_engine(conn_str,echo=True)

Base.metadata.create_all(engine)
#Base.metadata.dropo_all(engine)

运行结果：

2019-08-03 14:42:37,760 INFO sqlalchemy.engine.base.Engine SHOW VARIABLES LIKE 'sql_mode'
2019-08-03 14:42:37,760 INFO sqlalchemy.engine.base.Engine {}
2019-08-03 14:42:37,762 INFO sqlalchemy.engine.base.Engine SHOW VARIABLES LIKE 'lower_case_table_names'
2019-08-03 14:42:37,762 INFO sqlalchemy.engine.base.Engine {}
2019-08-03 14:42:37,764 INFO sqlalchemy.engine.base.Engine SELECT DATABASE()
2019-08-03 14:42:37,764 INFO sqlalchemy.engine.base.Engine {}
2019-08-03 14:42:37,765 INFO sqlalchemy.engine.base.Engine show collation where `Charset` = 'utf8mb4' and `Collation` = 'utf8mb4_bin'
2019-08-03 14:42:37,765 INFO sqlalchemy.engine.base.Engine {}
2019-08-03 14:42:37,767 INFO sqlalchemy.engine.base.Engine SELECT CAST('test plain returns' AS CHAR(60)) AS anon_1
2019-08-03 14:42:37,767 INFO sqlalchemy.engine.base.Engine {}
2019-08-03 14:42:37,769 INFO sqlalchemy.engine.base.Engine SELECT CAST('test unicode returns' AS CHAR(60)) AS anon_1
2019-08-03 14:42:37,769 INFO sqlalchemy.engine.base.Engine {}
2019-08-03 14:42:37,770 INFO sqlalchemy.engine.base.Engine SELECT CAST('test collated returns' AS CHAR CHARACTER SET utf8mb4) COLLATE utf8mb4_bin AS anon_1
2019-08-03 14:42:37,770 INFO sqlalchemy.engine.base.Engine {}
2019-08-03 14:42:37,772 INFO sqlalchemy.engine.base.Engine DESCRIBE `student`
2019-08-03 14:42:37,772 INFO sqlalchemy.engine.base.Engine {}
2019-08-03 14:42:37,773 INFO sqlalchemy.engine.base.Engine ROLLBACK
2019-08-03 14:42:37,775 INFO sqlalchemy.engine.base.Engine 
CREATE TABLE student (
	id INTEGER NOT NULL AUTO_INCREMENT, 
	name VARCHAR(64) NOT NULL, 
	age INTEGER, 
	PRIMARY KEY (id)
)


2019-08-03 14:42:37,775 INFO sqlalchemy.engine.base.Engine {}

查看数据库变化：

改进：

import sqlalchemy
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column,Integer,String
from sqlalchemy.orm import sessionmaker


#实体基类
Base = declarative_base()

#实体类
class Student(Base):
    __tablename__ = 'student'
    id = Column(Integer,primary_key=True,autoincrement=True)
    name = Column(String(64),nullable=False)
    age = Column(Integer)

    def __repr__(self):
        return '{} id:{} name:{} age:{}'.format(self.__class__.__name__,self.id,self.name,self.age)

    __str__ =__repr__

host = '172.20.10.11'
port = '3306'
user = 'root'
passwd = '123456'
database = 'test'

conn_str = 'mysql+pymysql://{}:{}@{}:{}/{}'.format(user,passwd,host,port,database)
engine = sqlalchemy.create_engine(conn_str,echo=True)

Base.metadata.drop_all(engine)
Base.metadata.create_all(engine)

Session = sessionmaker() #class
session = Session(bind=engine) #instance

try:
    lst = []
    for i in range(5):
        student = Student()
        student.name = 'tom' + str(i)
        student.age = 20 + i
        lst.append(student)
    session.add_all(lst)
    session.commit()
except Exception as e:
    print('-----------------------')
    session.rollback()
finally:
    pass

运行结果：

2019-08-03 22:51:00,640 INFO sqlalchemy.engine.base.Engine SHOW VARIABLES LIKE 'sql_mode'
2019-08-03 22:51:00,640 INFO sqlalchemy.engine.base.Engine {}
2019-08-03 22:51:00,642 INFO sqlalchemy.engine.base.Engine SHOW VARIABLES LIKE 'lower_case_table_names'
2019-08-03 22:51:00,643 INFO sqlalchemy.engine.base.Engine {}
2019-08-03 22:51:00,645 INFO sqlalchemy.engine.base.Engine SELECT DATABASE()
2019-08-03 22:51:00,645 INFO sqlalchemy.engine.base.Engine {}
2019-08-03 22:51:00,646 INFO sqlalchemy.engine.base.Engine show collation where `Charset` = 'utf8mb4' and `Collation` = 'utf8mb4_bin&#