Python pymysql 数据库使用

使用pymysql，直接上代码- -#

import pymysql

#链接数据库
conn = pymysql.connect('127.0.0.1',user='root',password='root',db = 'test1')

#打开数据库连接，不指定数据库
# conn=pymysql.connect('127.0.0.1','root','root')
# conn.select_db('test1')

#获取游标
cursor = conn.cursor()
#创建新的数据库
# cursor.execute("CREATE DATABASE IF NOT EXISTS pythonDB DEFAULT CHARSET utf8 COLLATE utf8_general_ci;")


#创建表
# cursor.execute("drop table if exists user") #如果表存在，则删除表
# sql="""CREATE TABLE IF NOT EXISTS `user`(`ID` int(11) NOT NULL AUTO_INCREMENT,`NAME` varchar(255) NOT NULL,`AGE` int(11) NOT NULL,PRIMARY KEY(`ID`)) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=0"""
# cursor.execute(sql)


#   executemany的使用方法
# executemany(templet,args)
# 能同时执行多条语句，执行同样多的语句可比execute()快很多，强烈建议执行多条语句时使用executemany
# templet : sql模板字符串,
# 例如     'insert into table(id,name) values(%s,%s)'
# args: 模板字符串的参数，是一个列表，列表中的每一个元素必须是元组！
# 例如：  [(1,'小明'),(2,'zeke'),(3,'琦琦'),(4,'韩梅梅')]

#插入数据
# sql1 = "insert into user values(1,'gundam1',0)"
# cursor.execute(sql1)
# #插入数据的另外一种
# sql2 = 'insert into user values(%s,%s,%s)'
# cursor.execute(sql2,(2,'gundam2',1))#添加数据为元祖形式
# #批量插入
# sql3 = 'insert into user values(%s,%s,%s)'
# cursor.executemany(sql3,[(3,'gundam3',2),(4,'gundam4',3)])
# #循环插入
# sql4 = 'insert into user values(%s,%s,%s)'
# listdata = []
# for i in range(5,10):
#     data = (i,'gundam%d'%i,i-1)
#     listdata.append(data)
# # print(listdata)
# cursor.executemany(sql4,listdata)

# cursor.fetchone():获取游标所在处的一行数据，返回元组，没有返回None
# cursor.fetchmany(size):接受size行返回结果行。如果size大于返回的结果行的数量，则会返回cursor.arraysize条数据。
# cursor. fetchall():接收全部的返回结果行。

# cursor.execute("select * from user;")

#fetchone():
# while 1:
#     res = cursor.fetchone() 
#     if res is None:#表示已经搜索完毕
#         break
#     print(res)

#fetchmany():
# resTuple = cursor.fetchmany(3)
# for res in resTuple:
#     print(res)

#fetchall():
# resTuple = cursor.fetchall()
# for res in resTuple:
#     print(res)
# print('一共%d条数据库数据'%len(resTuple))

#更新数据
# updata_line = 0 #记录返回的行数
# updata_line = cursor.execute("update user set AGE=100 where NAME='gundam3'")
# print('更新的行数为:%d'%updata_line)
# #查询下更新的数据
# cursor.execute("select * from user where NAME='gundam3'")
# print(cursor.fetchone())

#更新多条数据
# sql = "update user set AGE=%s where NAME=%s"
# cursor.executemany(sql,[(100,'gundam5'),(100,'gundam6')])

# cursor.execute("select * from user")
# resTuple = cursor.fetchall()
# for res in resTuple:
#     print(res)

#删除数据
# cursor.execute("delete from user where ID=1")
# cursor.execute("select * from user")
# resTuple = cursor.fetchall()
# for res in resTuple:
#     print(res)

#删除多条数据
# sql = "delete from user where ID=%s"
# cursor.executemany(sql,[(2),(3)])
# cursor.execute("select * from user")
# resTuple = cursor.fetchall()
# for res in resTuple:
#     print(res)

#事务回滚
# sql = "delete from user where ID=%s"
# cursor.executemany(sql,[(4),(5)])
# conn.rollback() #事务回滚
# cursor.execute("select * from user")
# resTuple = cursor.fetchall()
# for res in resTuple:
#     print(res)


conn.commit()#成功插入数据
cursor.close()#先关闭游标
conn.close()#再关闭数据库连接