Python连接sql
导包 from pymysql import Connection
获取连接对象 Connection
.cursor 获取游标对象
.select_db() 选择数据库
.execute(‘ ’) 执行sql语言命令
记得.close()关闭
.fetchall 得到所有的数据结果
Results是以元组的形式 ((),(),(),。。。)大元组括这小元组
r则是一个个小元组 () () 。。
数据插入
要通过.commit()确认
或者autocommint
数据集通过python写入mysql
from pymysql import Connection
from data_define import Record
from file_define import *
text_file_reader = TextReader('D:/dev/python-learn/2011年1月销售数据.txt')
json_file_reader = JsonReader('D:/dev/python-learn/2011年2月销售数据JSON.txt')
Jan_data: list[Record] = text_file_reader.read_data()
Feb_data: list[Record] = json_file_reader.read_data()
all_data: list[Record] = Jan_data + Feb_data
conn = Connection(
host='localhost', # 主机名
port=3306, # 端口
user='root', # 账户
password='123456', # 密码
autocommit=True # 自动提交确认
)
conn.select_db('py_mysql')
cursor = conn.cursor()
for record in all_data:
sql = (f"insert into orders values "
f"('{record.date}', '{record.order_id}', {record.money}, '{record.province}')")
cursor.execute(sql)
conn.close()
练习
from pymysql import Connection
conn = Connection(
host='localhost', # 主机名
port=3306, # 端口
user='root', # 账户
password='123456', # 密码
autocommit=True # 自动提交确认
)
conn.select_db('py_mysql')
cursor = conn.cursor()
cursor.execute('select * from orders')
data_tuple = cursor.fetchall()
with open('D:\\dev\\python-learn\\data.txt', 'w', encoding='UTF-8') as f:
for t in data_tuple:
data_dict = {"date": t[0], "order_id": t[1], "money": t[2], "province": t[3]}
# print(data_dict)
f.write(f"{data_dict}\n")
conn.close()