文檔: 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:[email protected]: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"])