Python与MySQL

安装库

pip install pymysql

连接数据库

db = pymysql.connect("127.0.0.1","root","root#admin","test")
# 创建钩子
cursor = db.cursor()

使用

插入数据

# 构建sql
sql = """INSERT INTO EMPLOYEE(FIRST_NAME,
         LAST_NAME, AGE, SEX, INCOME)
         VALUES ('Mac', 'Mohan', 20, 'M', 2000)"""

try:            
    # 执行sql语句
    cursor.execute(sql)
    # 提交到数据库执行
    db.commit()    
except Exception as e:
    # 打印错误            
    print(e)            
    db.rollback()

查询数据

# 构建sql
sql = "SELECT `kgzt` FROM `snd` WHERE `id` = {id}".format(id=i)

try:
    # 执行sql语句
    cursor.execute(sql)
    # 获取单条数据 返回的数据是Tuple类型
    print(cursor.fetchone())
except Exception as e:
    print(e)            
    db.rollback()

更新：从Python3.8.7开始，在使用函数时可能需要参数名，否则会使用默认的参数模板，所以数据库的连接需要使用:

db = pymysql.connect(host="127.0.0.1", user="root", password="root#admin", database="test")

不然会报错：
TypeError __init__() takes 1 positional argument but 5 were given

示例——更新局放虚拟数据

# -*- coding: UTF-8 -*-
import random
import pymysql

# 连接数据库
db = pymysql.connect("127.0.0.1","root","root#admin","lxfc2")
# 创建钩子
cursor = db.cursor()

sqls = []

for i in range(1,15):
    # int 类型变量
    ultrasound_peak = random.randint(0,200)
    ultrasound_frequency = random.randint(0,3000)
    tev_peak = random.randint(0,200)
    tev_frequency = random.randint(0,3000)
    jfzt = random.randint(0,1)
    # 字符串型变量
    scmm = "1G"+str(i)
    sql = """INSERT INTO jgjf(scmm,
         ultrasound_peak, ultrasound_frequency, tev_peak, tev_frequency, jfzt)
         VALUES ('{scmm}', {ultrasound_peak}, {ultrasound_frequency}, {tev_peak}, {tev_frequency}, {jfzt})""".format(scmm=scmm, ultrasound_peak=ultrasound_peak, ultrasound_frequency=ultrasound_frequency, tev_peak=tev_peak, tev_frequency=tev_frequency, jfzt=jfzt)
    
    sqls.append(sql)
    


for i in range(1,14):
    ultrasound_peak = random.randint(0,200)
    ultrasound_frequency = random.randint(0,3000)
    tev_peak = random.randint(0,200)
    tev_frequency = random.randint(0,3000)
    jfzt = random.randint(0,1)
    scmm = "2G"+str(i)
    sql = """INSERT INTO jgjf(scmm,
         ultrasound_peak, ultrasound_frequency, tev_peak, tev_frequency, jfzt)
         VALUES ('{scmm}', {ultrasound_peak}, {ultrasound_frequency}, {tev_peak}, {tev_frequency}, {jfzt})""".format(scmm=scmm, ultrasound_peak=ultrasound_peak, ultrasound_frequency=ultrasound_frequency, tev_peak=tev_peak, tev_frequency=tev_frequency, jfzt=jfzt)

    sqls.append(sql)
    

for sql in sqls:
    try:
        # 执行sql语句
        cursor.execute(sql)
        # 提交到数据库执行
        db.commit()
    except Exception as e:
        print(e)            
        db.rollback()
        
db.close()

参考资料：
Python 操作 MySQL 数据库
Python 3 进阶 —— 使用 PyMySQL 操作 MySQL