一、pymysql入门
"""
演示python mysql库的基础操作
"""
from pymysql import Connection
conn = Connection(
host="localhost",
port=3306,
user="root",
password="11111"
)
cursor = conn.cursor()
conn.select_db("db3")
cursor.execute("select * from student")
results = cursor.fetchall()
for r in results:
print(r)
conn.close()
二、案例操作
1. main文件
"""
面向对象,数据分析案例,主业务逻辑代码
实现步骤:
1.设计一个类,可以完成数据的封装
2.设计一个抽象类。定义文件读取相关功能,并使用子类实现具体功能
3.读取文件,产生数据对象
4.进行数据需求的逻辑计算(计算每一天的销售额)
5.通过pyecharts进行图形绘制
"""
from file_define import TextFileReader, JsonFileReader
from data_define import Record
from pymysql import Connection
text_file_reader = TextFileReader("D:\\03计科学习资料\学习资料\python基础+进阶\第13章资料\\2011年1月销售数据.txt")
json_file_reader = JsonFileReader("D:\\03计科学习资料\学习资料\python基础+进阶\第13章资料\\2011年2月销售数据JSON.txt")
jan_data: [Record] = text_file_reader.read_data()
feb_data: [Record] = json_file_reader.read_data()
all_data: [Record] = jan_data + feb_data
conn = Connection(
host="localhost",
port=3306,
user="root",
password="11111",
autocommit=True
)
cursor = conn.cursor()
conn.select_db("py_sql")
for record in all_data:
sql = f"insert into orders(order_date, order_id, money, province)" \
f" values('{record.date}','{record.order_id}', {record.money}, '{record.province}')"
cursor.execute(sql)
conn.close()
2. data_define 数据定义
"""
数据定义的类
"""
class Record:
def __init__(self, data, order_id, money, province):
self.date = data
self.order_id = order_id
self.money = money
self.province = province
def __str__(self):
return f"{self.date}, {self.order_id}, {self.money}, {self.province}"
3. file_define 文件定义
"""
文件相关的类定义
"""
import json
from data_define import Record
class FileReader:
def read_data(self) -> list[Record]:
"""读取文件的数据,读到的每一条数据都转换为Record对象
将她们都封装到list内返回即可"""
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], int(data_list[2]), data_list[3])
record_list.append(record)
f.close()
return record_list
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
三、插入操作(补充)
"""
演示python mysql库的基础操作
方法一:手动commit
"""
from pymysql import Connection
conn = Connection(
host="localhost",
port=3306,
user="root",
password="11111"
)
cursor = conn.cursor()
conn.select_db("db3")
cursor.execute("insert into student values(201212124, '周杰伦', '男', 20, 'IT')")
conn.commit()
conn.close()
"""
# 演示python mysql库的基础操作
# 方法二:自动commit
from pymysql import Connection
# 构建mysql数据库的链接
conn = Connection(
host="localhost", # 主机名(ip)
port=3306, # 端口
user="root", # 账户
password="11111", # 密码
autocommit=True # 设置自动提交,无需手动commit
)
# print(conn.get_server_info())
# 执行非查询性质sql
cursor = conn.cursor() # 获取到游标对象
# 选择数据库
conn.select_db("db3")
# 执行sql
cursor.execute("insert into student values(201212124, '周杰伦', '男', 20, 'IT')")
# 执行查询性质sql
# cursor.execute("select * from student")
#
# results = cursor.fetchall()
# for r in results:
#
# print(r)
# 关闭链接
conn.close()
"""