创建数据库(官方示例)
CREATE TABLE `users` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`email` varchar(255) COLLATE utf8_bin NOT NULL,
`password` varchar(255) COLLATE utf8_bin NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin
AUTO_INCREMENT=1 ;
插入与查询数据库(官方示例)
import pymysql.cursors
# Connect to the database
connection = pymysql.connect(host='localhost',
user='user',
password='passwd',
db='db',
charset='utf8mb4',
cursorclass=pymysql.cursors.DictCursor)
try:
with connection.cursor() as cursor:
# Create a new record
sql = "INSERT INTO `users` (`email`, `password`) VALUES (%s, %s)"
cursor.execute(sql, ('webmaster@python.org', 'very-secret'))
# connection is not autocommit by default. So you must commit to save
# your changes.
connection.commit()
with connection.cursor() as cursor:
# Read a single record
sql = "SELECT `id`, `password` FROM `users` WHERE `email`=%s"
cursor.execute(sql, ('webmaster@python.org',))
result = cursor.fetchone()
print(result)
finally:
connection.close()
引用网址 http://pymysql.readthedocs.io/en/latest/user/examples.html
个人操作记录
import pymysql
# 连接数据库
conn = pymysql.connect(host='localhost', user='root', passwd='root', db='test', port=3306, charset='utf8')
# SQL代码
# 查询 SQL代码
sql1 = "select id from test1 where id=%s"
# 插入 SQL代码
sql2 = "insert into test1(id,text,num) values (%s,%s,%s)"
# 更新 SQL代码
sql3 = "update test1 set text = %s , num = %s where id = %s"
# 开始执行
cur = conn.cursor()
# 查询
sql4 = cur.execute(sql1,变量1)
for row in cur.fetchall():
print(row)
# 写入使用
cur.execute(sql2,(变量1,变量2,变量3))
conn.commit()
# 修改
cur.execute(sql3,(变量1,变量2,变量3))
conn.commit()
# 结束执行
cur.close()
# 关闭数据库连接
conn.close()
衍生出SQL函数
# insert 生成SQL的简易函数
def insertSql(table,colname):
var1 = ""
var2 = ""
for i in range(0,len(colname)):
var1 = var1 + colname[i]+','
for i in range(0,len(colname)):
var2 = var2 + "%s,"
var = "insert into "+ table +"("+ var1[0:-1] + ") values (" + var2[0:-1] +")"
return var
# update 生成SQL的简易函数
def updateSql(table,set,where):
var1 = ""
var2 = ""
for i in range(0,len(set)):
var1 = var1 + set[i] + " = %s,"
for i in range(0,len(where)):
var2 = var2 + where[i] + " = %s and "
var = "update " + table + " set " + var1[0:-1] + " where " + var2[0:-5]
return var
# 传入的变量参数必须要用列表的形式
个人学习,仅供参考