一、基本操作语句
'''
# !/usr/bin/python3
import pymysql
# 打开数据库连接
db = pymysql.connect("localhost", "testuser", "test123", "TESTDB")
# 使用 cursor() 方法创建一个游标对象 cursor
cursor = db.cursor()
# 使用 execute() 方法执行 SQL 查询
cursor.execute("SELECT VERSION()")
# 使用 fetchone() 方法获取单条数据.
data = cursor.fetchone()
print("Database version : %s " % data)
# 关闭数据库连接
db.close()
'''
二、查询数据
查询不需要提交commit()
import pymysql
connect = pymysql.connect(host='localhost',
user = 'root',
password='lzy0621',
db='user',
charset='utf8mb4',
cursorclass = pymysql.cursors.DictCursor
)
def query(para):
cursor = connect.cursor()
sql = 'select * from t_t_s where t_id=%s'
cursor.execute(sql,(para))
result = cursor.fetchone()
print('查询到的数据: ', result)
cursor.close()
connect.close()
def query1():
cursor = connect.cursor()
sql = 'select * from t_t_s where 1=1'
cursor.execute(sql)
result = cursor.fetchall()
print('查询到的数据: ', result)
cursor.close()
connect.close()
三、插入数据
需要提交commit()
import pymysql
connect = pymysql.connect(host='localhost',
user = 'root',
password='lzy0621',
db='user',
charset='utf8mb4',
cursorclass = pymysql.cursors.DictCursor
)
def create(para1,para2):
cursor = connect.cursor()
sql = 'insert into t_t_s(t_id,s_id) values(%s,%s)'
result = cursor.execute(sql,(para1,para2))
print('受影响行数: ',result)
connect.commit()
cursor.close()
connect.close()
四、批量插入数据
import pymysql
import uuid
import random
import time
connect = pymysql.connect(host='localhost',
user = 'root',
password='lzy0621',
db='user',
charset='utf8mb4',
cursorclass = pymysql.cursors.DictCursor
)
def batch_create():
cursor = connect.cursor()
sql = 'insert into t_t_s(t_id,s_id) values(%s,%s)'
start = time.clock()
list = []
for i in range(10):
data = (random.randint(10, 99), random.randint(10, 99))
list.append(data)
result=cursor.executemany(sql,list)
print('受影响行数:{},耗费时间{} '.format(result,time.clock()-start))
connect.commit()
cursor.close()
connect.close()
def batch1_create():
cursor = connect.cursor()
sql = 'insert into t_t_s(t_id,s_id) values(%s,%s)'
start = time.clock()
for i in range(10):
cursor.execute(sql, (random.randint(10, 99), random.randint(10, 99)))
print('耗费时间:{}'.format(time.clock()-start))
connect.commit()
cursor.close()
connect.close()
def batch2_create():
cursor = connect.cursor()
sql = 'insert into t_t_s(t_id,s_id) values'
start = time.clock()
for i in range(10):
sql +='({},{}),'.format(random.randint(10, 99),random.randint(10, 99))
sql = sql.rstrip(",")
result = cursor.execute(sql)
print('受影响条数{},耗费时间:{}'.format(result,time.clock()-start))
connect.commit()
cursor.close()
connect.close()
def main():
batch2_create()
if __name__=="__main__":
main()
五、更新数据
也需要提交commit()
import pymysql
connect = pymysql.connect(host='localhost',
user = 'root',
password='lzy0621',
db='user',
charset='utf8mb4',
cursorclass = pymysql.cursors.DictCursor
)
def update(para1,para2):
cursor = connect.cursor()
sql = 'update t_t_s set t_id=%s where t_id=%s'
result = cursor.execute(sql,(para1,para2))
print('受影响行数: ',result)
connect.commit()
cursor.close()
connect.close()
六、删除数据
需要commit()
import pymysql
connect = pymysql.connect(host='localhost',
user = 'root',
password='lzy0621',
db='user',
charset='utf8mb4',
cursorclass = pymysql.cursors.DictCursor
)
def delete(para1):
cursor = connect.cursor()
sql = 'delete from t_t_s where t_id=%s'
result = cursor.execute(sql,(para1))
print('受影响行数: ',result)
connect.commit()
cursor.close()
connect.close()