文档:https://docs.sqlalchemy.org/en/13/core/engines.html
链接字符串
# default engine = create_engine('mysql://scott:tiger@localhost/foo') # mysqlclient (a maintained fork of MySQL-Python) engine = create_engine('mysql+mysqldb://scott:tiger@localhost/foo') # PyMySQL engine = create_engine('mysql+pymysql://scott:tiger@localhost/foo')
管理数据库连接
# -*- coding: utf-8 -*- from sqlalchemy import create_engine, text db_url = "mysql://root:123456@127.0.0.1:3306/demo" engine = create_engine(db_url, echo=True) db = engine.connect() db.close()
备注:echo 参数可以开启SQL语句日志打印
1、插入数据
insert = "insert into student(name)values(:name)" # 插入数据 单条 dct = {"name": "Tom"} result = db.execute(text(insert), dct) print(result.rowcount) # 1 # 插入数据 多条 # 注意: # peewee的特殊字符: % # pymysql的特殊字符: ? # 都能正常写入,不需要特殊转义 data = [ {"name": "Tom"}, {"name": "Tom?"}, {"name": "Tom%"} ] result = db.execute(text(insert), data) print(result.rowcount) # 3
2、删除数据
delete = "delete from student where id=1" result = db.execute(text(delete)) print(result.rowcount) # 1
3、修改数据
update = "update student set sex='2' where id=1" result = db.execute(text(update)) print(result.rowcount) # 1
4、查询数据
select = "select id, name from student where id=1" result = db.execute(text(select)) print(result.keys()) # ['id', 'name'] print(result.fetchall()) # [(50, 'Tom')] print(result.rowcount) # 1 for row in cursor: print(row["name"])