Python——操作mysql数据库

最新推荐文章于 2024-08-15 14:00:00 发布

蚂蚁蚂蚁大蚂蚁

最新推荐文章于 2024-08-15 14:00:00 发布

阅读量241

点赞数

分类专栏： Python

本文链接：https://blog.csdn.net/CedarSia/article/details/105933781

版权

Python 专栏收录该内容

12 篇文章 0 订阅

订阅专栏

Python3和Python2操作mysql数据库用的三方库分别是pymysql和mysqldb。二者用法相同。本文以Python3为例。

1、批量插入以及查询数据

import pymysql
import uuid
db = pymysql.connect("192.168.1.143","root","123456","pytest")
cur = db.cursor()
print(type(cur))
i= 0
for i in range(1):
    list_info = [("stu" + str(num), str(uuid.uuid1()), str(15612340000+num), "school", "parents"+str(num), str(15602340000+num),)
             for num in range(i, i+2)]
    print(list_info)

    try:
        insert_sqli = "insert into info_stu values (%s,%s,%s,%s,%s,%s)"
        select_sqli = "select code1 from info_stu where class = 'school'"
        cur.executemany(insert_sqli,list_info)# 插入数据，executemany是批量插入，性能好于execute
        cur.execute(select_sqli)# 查询数据
        res = cur.fetchone() # 取查询数据的第一条
        print(res)# ('09d3641e-8ea5-11ea-bb16-a0b3cc17c467',),
        res = cur.fetchall()  # 取所有的查询结果
        print(res) # (('09d3641e-8ea5-11ea-bb16-a0b3cc17c467',), ('09d3b240-8ea5-11ea-b6f5-a0b3cc17c467',))
    except Exception as Error:
         print('sql exrcutemany failed:', Error)
    else:
        print('sql executeamny success!')
# 4. 提交sql语句， 作用于数据库;
    db.commit()
# 5. 先关闭游标
cur.close()
# 6. 关闭数据库连接
db.close()

2、读取一张表格的某个数据，插入到另一个表格

import pymysql
db = pymysql.connect("192.168.1.143","root","123456","pytest")
cur = db.cursor()
select_sql = "select stu_name,code from info_stu"
cur.execute(select_sql)
res = cur.fetchall()
print(res)
# print(len(res))
insert_sql = "insert into stu_score values (%s,%s,%s,%s)"
list_info = []
for i in range(len(res)):
    tmp = res[i]
    for j in range(100):
        list_info.append((tmp[0], tmp[1], '80', '68'))
cur.executemany(insert_sql,list_info)
# print(list_info)
db.commit()
# 5. 先关闭游标
cur.close()
# 6. 关闭数据库连接
db.close()