Python_pymysql+游标(cursor())的应用
pymysql的应用
一、装包(前提)
py -m pip install pymysql;
D:\Python36\python.exe -m pip install pymysql
二、创建连接+游标的应用
1、简单示例
#coding=utf-8
import pymysql
# 打开数据库连接
conn = pymysql.connect(
host = "127.0.0.1",
port = 3306,
user = "root",
passwd = "1234qwer",
db = "gloryroad",
charset = "utf8")
# 使用cursor()方法获取数据库的操作游标
cursor = conn.cursor()
print(cursor)
print(type(cursor))
2、创建数据库
try: conn = pymysql.connect( host = "127.0.0.1", port = 3306, user = "root", passwd = "1234qwer" ) 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 = "root", passwd = "1234qwer") 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 = "root", passwd = "1234qwer", db = "grdb", charset = "utf8") #使用cursor()方法获取数据库的操作游标 cursor = conn.cursor() #插入一条数据 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、插入数据(含字符串拼接实例)
#coding=utf-8 import pymysql conn = pymysql.connect( host = "127.0.0.1", port = 3306, user = "root", passwd = "1234qwer", db = "grdb", charset = "utf8") # 使用cursor()方法获取数据库的操作游标 cursor = conn.cursor() # 插入一条数据 #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)" j=0 for i in range(100,201): insert = cursor.execute(sql,(i,'lucy'+str(i),'efg'+str(i),str(1900+j)+'-02-01')) j+=1 # 关闭游标 cursor.close() # 提交事务 conn.commit()#提交事务后,不能回滚了 # 关闭数据库连接 conn.close() print(u"sql语句执行成功!")
6、查询表数据语句
6.1逐条获取cursor.fetchone
#coding=utf-8 import pymysql import pymysql conn = pymysql.connect( host = "127.0.0.1", port = 3306, user = "root", passwd = "1234qwer", 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 一条一条取数据,每条数据是元祖
6.2获取n条数据
import pymysql conn = pymysql.connect( host = "127.0.0.1", port = 3306, user = "root", passwd = "1234qwer", 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语句执行成功!")
7、更新数据
批量更新数据 #批量更新的代码 import pymysql conn = pymysql.connect( host = "127.0.0.1", port = 3306, user = "root", passwd = "1234qwer", 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'), ('Tomx2x', 'amy')]) # 查看更新后的结果 query = cursor.execute("select * from user where name='tom' or name='amy'") print("表中所有数据:") for i in cursor.fetchall(): print(i) # 关闭游标 cursor.close() # 提交事务 conn.commit() # 关闭数据库连接 conn.close() print("sql语句执行成功!")
8、删除数据
import pymysql conn = pymysql.connect( host = "127.0.0.1", port = 3306, user = "root", passwd = "1234qwer", db = "grdb", charset = "utf8") # 使用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) # 关闭游标 cursor.close() # 提交事务 conn.commit() # 关闭数据库连接 conn.close() print("sql语句执行成功!") cursor.close()
9、回滚事务(roll back)
回滚事务