python链接mysql数据库

python链接mysql数据库

一、安装python链接mysql的驱动包

命令：

pip install pymysql

二、执行SQL

2.1 执行非查询性质的sql

from pymysql import Connection

# 链接数据库所需要的参数
conn = Connection(
    host="127.0.0.1",
    port=3306,
    user="root",
    password="root"
)

# 打印数据库信息版本
print(conn.get_server_info())

cursor = conn.cursor()  # 获取到游标对象
conn.select_db("python")  # 选择数据库
cursor.execute("create table test_pymysql(id int);")    # 执行SQL

# 关闭数据库链接
conn.close()

2.2 select

from pymysql import Connection

# 链接数据库所需要的参数
conn = Connection(
    host="127.0.0.1",
    port=3306,
    user="root",
    password="root"
)

cursor = conn.cursor()  # 获取游标对象
conn.select_db("python")    # 选择数据库
cursor.execute("select * from student") # 执行SQL
result = cursor.fetchall()  # 获取执行结果

for r in result:
    print(r)

# 关闭数据库
conn.close()
# 输出结果
('2023060723021500000000000000001', '张三', 23, '男')
('2023060723021500000000000000002', '李四', 24, '男')
('2023060723021500000000000000003', '王五', 26, '女')
('2023060723021500000000000000004', '赵六', 89, '女')
('2023060723021500000000000000005', '孙琪', 22, '男')

2.3 insert

注意：代码执行完毕后，并不会自动commit，需要手动的commit

或者在Connection对象中设置autocommit = True

from pymysql import Connection

# 链接数据库所需要的参数
conn = Connection(
    host="127.0.0.1",
    port=3306,
    user="root",
    password="root",
    autocommit=True
)

cursor = conn.cursor()  # 获取游标对象
conn.select_db("python")
cursor.execute("insert into student values('2023060723021500000000000000006','周杰伦',31,'男')")
# conn.commit()

conn.close()

三、案例

1、pojo类

test\readFile\data_define.py

class Record:
    def __init__(self, date, order_id, money, province):
        self.date = date
        self.order_id = order_id
        self.money = money
        self.province = province

2、文件读取类

test\readFile\file_read.py

import json

from readFile.data_define import Record

# 定义接口
class FileReader:
    def read_data(self) -> list[Record]:
        pass

# 文本文件读取
class TextFileReader(FileReader):

    def __init__(self, path):
        self.path = path

    def read_data(self) -> list[Record]:
        f = open(self.path, "r", encoding="UTF-8")
        record_list: list[Record] = []
        for line in f.readlines():
            line = line.strip()
            data_list = line.split(",")
            record = Record(data_list[0], data_list[1], data_list[2], data_list[3])
            record_list.append(record)
        f.close()
        return record_list

# JSON文件读取
class JSONFileReader(FileReader):
    def __init__(self,path):
        self.path = path

    def read_data(self) -> list[Record]:
        f = open(self.path, "r", encoding="UTF-8")
        record_list: list[Record] = []
        for line in f.readlines():
            data_dict = json.loads(line)
            record = Record(data_dict["date"],data_dict["order_id"],data_dict["money"],data_dict["province"])
            record_list.append(record)
        f.close()
        return record_list

if __name__ == '__main__':
    text_file_read = TextFileReader("F:/study/code/资料/2011年1月销售数据.txt")
    json_file_read = JSONFileReader("F:/study/code/资料/2011年2月销售数据JSON.txt")
    text_file_read.read_data()
    json_file_read.read_data()

3、再封装一层供其他模块调用

test\readFile\readData.py

from readFile.data_define import Record
from readFile.file_read import JSONFileReader, TextFileReader

def readData() -> list[Record]:
    text_file_read = TextFileReader("F:/study/code/资料/2011年1月销售数据.txt")
    json_file_read = JSONFileReader("F:/study/code/资料/2011年2月销售数据JSON.txt")

    jan_data: list[Record] = text_file_read.read_data()
    sec_data: list[Record] = json_file_read.read_data()

    all_data: list[Record] = jan_data + sec_data

    return all_data

4、链接数据库，写入数据

test\mysql\collection_example.py

from readFile import readData
from pymysql import *

# 先获取所有数据
all_data = readData.readData()
# 获取链接对象
conn = Connection(
    host="127.0.0.1",
    port=3306,
    user="root",
    password="root",
    autocommit=True
)
# 执行sql
cursor = conn.cursor()
conn.select_db("python")
for record in all_data:
    # 构造sql
    sql = f"insert into orders (order_id, money, province, order_date)" \
          f"values ('{record.order_id}',{record.money},'{record.province}','{record.date}')"
    # 插入数据
    cursor.execute(sql)

结构：

2011年2月销售数据JSON.txt

2011年1月销售数据.txt