一、安装pymysql
py -m pip install pymysql;
二、pymysql数据库操作
1.简单示例
#coding=utf-8 import pymysql # 打开数据库连接 conn = pymysql.connect( host = "127.0.0.1", port = 3306, user = "lgb", passwd = "Lgb@1234", db = "test", charset = "utf8") # 使用cursor()方法获取数据库的操作游标 cursor = conn.cursor() print(cursor) print(type(cursor))
2.创建数据库
#coding=utf-8 import pymysql try: conn = pymysql.connect( host = "127.0.0.1", port = 3306, user = "lgb", passwd = "Lgb@1234" ) cur = conn.cursor() cur.execute("CREATE DATABASE IF NOT EXISTS grdb DEFAULT CHARSET utf8 COLLATE utf8_general_ci;") cur.close() conn.close() print("创建数据库pythonDB成功! ") except pymysql.Error as e: print("Mysql Error %d: %s" %(e.args[0],e.args[1]))
#COLLATE utf8_general_ci:大小写不敏感
3.创建表
#coding=utf-8 import pymysql try: conn = pymysql.connect( host = "127.0.0.1", port = 3306, user = "lgb", passwd = "Lgb@1234") conn.select_db('grdb') ## 选择pythonDB数据库 cur = conn.cursor() ## 获取游标 ## 如果所建表已存在,删除重建 cur.execute("drop table if exists User;") ## 执行建表sql语句 cur.execute('''CREATE TABLE User (id int(11) DEFAULT NULL,name varchar(255) DEFAULT NULL,password varchar(255) DEFAULT NULL,birthday date DEFAULT NULL)ENGINE=innodb DEFAULT CHARSET=utf8;''') cur.close() conn.close() print(u"创建数据表成功") except pymysql.Error as e: print("Mysql Error %d: %s" %(e.args[0],e.args[1]))
4.插入表数据
#coding=utf-8 import pymysql conn = pymysql.connect( host = "127.0.0.1", port = 3306, user = "lgb", passwd = "Lgb@1234", db = "grdb", charset = "utf8") ## 使用cursor()方法获取数据库的操作游标 cursor = conn.cursor() ## 方式一:直接执行insert语句,插入一条数据 insert = cursor.execute("insert into user values(1,'Tom','123','1990-01-01')") print(u"添加语句受影响的行数:",insert) ## 方式二:通过格式字符串传入值,此方式可以防止SQL注入 sql = "insert into user values(%s,%s,%s,%s)" insert = cursor.execute(sql,(3,'lucy','efg','1993-02-01')) print(u"添加语句受影响的行数:",insert) ## 关闭游标 cursor.close() ## 提交事务 conn.commit() ## 关闭数据库连接 conn.close() print(u"sql语句执行成功!")
5.查询表数据语句
(1)逐条获取fetchone()
#coding=utf-8 import pymysql conn = pymysql.connect( host = "127.0.0.1", port = 3306, user = "lgb", passwd = "Lgb@1234", db = "grdb", charset = "utf8") ## 使用cursor()方法获取数据库的操作游标 cursor = conn.cursor() cursor.execute("select * from user") while 1: res = cursor.fetchone() if res is None: ## 表示已经取完结果集 break print(res) ## 将读取到的时间格式化 print(res[-1].strftime("%Y-%m-%d")) ## 关闭游标 cursor.close() ## 提交事务 conn.commit() ## 关闭数据库连接 conn.close() print("sql语句执行成功!")
#cursor.fetchone 一条一条获取数据,每条数据是元组
(2)获取n条数据fetchmany(n)
#coding=utf-8 import pymysql conn = pymysql.connect( host = "127.0.0.1", port = 3306, user = "lgb", passwd = "Lgb@1234", db = "grdb", charset = "utf8") ## 使用cursor()方法获取数据库的操作游标 cursor = conn.cursor() cursor.execute("select * from user") ## 获取游标处两条数据 resTuple = cursor.fetchmany(2) print("结果集类型:",type(resTuple)) for i in resTuple: print(i) ## 关闭游标 cursor.close() ## 提交事务 conn.commit() ## 关闭数据库连接 conn.close() print("sql语句执行成功!")
#cursor.fetchmany 获取前两行数据,每条数据是元组
(3)获取所有数据fetchall()
#coding=utf-8 import pymysql conn = pymysql.connect( host = "127.0.0.1", port = 3306, user = "lgb", passwd = "Lgb@1234", db = "grdb", charset = "utf8") ## 使用cursor()方法获取数据库的操作游标 cursor = conn.cursor() cursor.execute("select * from user") ## 获取所有的数据 resTuple = cursor.fetchall() print("结果集类型:",type(resTuple)) for i in resTuple: print(i) ## 关闭游标 cursor.close() ## 提交事务 conn.commit() ## 关闭数据库连接 conn.close() print("sql语句执行成功!")
#cursor.fetchall 获取数据,每条数据是元组
6.更新数据
#coding=utf-8 import pymysql conn = pymysql.connect( host = "127.0.0.1", port = 3306, user = "lgb", passwd = "Lgb@1234", db = "grdb", charset = "utf8") ##使用cursor()方法获取数据库的操作游标 cursor = conn.cursor() ## 查询一条数据 query = cursor.execute("select * from user") print("表中所有数据:") for i in cursor.fetchall(): print(i) ## 批量更新数据 cursor.executemany("update user set password = %s where name=%s",[('tomx2x', 'tom'), ('amy2x', 'amy')]) ## 查看更新后的结果 query = cursor.execute("select * from user") print("表中所有数据:") for i in cursor.fetchall(): print(i) ## 关闭游标 cursor.close() ## 提交事务 conn.commit() ## 关闭数据库连接 conn.close() print("sql语句执行成功!")
7.删除数据
#coding=utf-8 import pymysql conn = pymysql.connect( host = "127.0.0.1", port = 3306, user = "lgb", passwd = "Lgb@1234", db = "grdb", charset = "utf8")标签:Python,pymysql,cursor,##,user,MySQL,print,conn From: https://www.cnblogs.com/yeyuzhuanjia/p/16776346.html
## 使用cursor()方法获取数据库的操作游标 cursor = conn.cursor() cursor.execute("select * from user") print("表中所有数据:") for i in cursor.fetchall(): print(i) ## 删除数据 delete = cursor.execute("delete from user where name='tom'") print("删除语句影响的行数:",delete) print("删除一条数据后,表中数据:") cursor.execute("select * from user") for i in cursor.fetchall(): print(i) ## 提交事务 conn.commit() ## 关闭游标 cursor.close()