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'
2019-08-03 22:51:00,647 INFO sqlalchemy.engin