Python3 pymysql 操作笔记

来源：互联网发布：指南针手机炒股软件编辑：程序博客网时间：2024/05/22 15:13

创建数据库（官方示例）

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_binAUTO_INCREMENT=1 ;

插入与查询数据库（官方示例）

import pymysql.cursors# Connect to the databaseconnection = 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# 传入的变量参数必须要用列表的形式

个人学习，仅供参考

0 0