【MySQL数据库原理】Python3连接 MySQL 数据库并批量创建表、批量插入sql语句数据库

数据库原理】Python3连接 MySQL 数据库并批量创建表、批量插入sql语句数据库"/>

【MySQL数据库原理】Python3连接 MySQL 数据库并批量创建表、批量插入sql语句数据库

目录

• 1、实现步骤说明
• 2、完整代码实现：批量创建表、sql语句批量插入数据数据库
• 3、封装上述代码并注释每一行代码

1、实现步骤说明

Python 3 可以使用 “mysql-connector-python” 包来连接 MySQL 数据库，使用以下步骤可以创建并批量创建表以及使用 SQL 语句批量插入数据到数据库中：

安装 “mysql-connector-python” 包：
在命令行中输入以下命令进行安装：pip install mysql-connector-python

连接 MySQL 数据库：
可以使用 mysql.connector.connect() 函数连接到 MySQL 数据库，需要提供主机名、用户名、密码、数据库名等信息。例如：

import mysql.connectormydb = mysql.connector.connect(host="localhost",user="yourusername",password="yourpassword",database="mydatabase"
)

创建表：
可以使用 execute() 方法执行 CREATE TABLE 语句来创建表。例如：

mycursor = mydb.cursor()
mycursor.execute("CREATE TABLE customers (name VARCHAR(255), address VARCHAR(255))")

批量创建表：
可以使用 execute() 方法多次执行 CREATE TABLE 语句来批量创建表。

插入数据：
可以使用 execute() 方法执行 INSERT INTO 语句来插入数据。例如：

mycursor = mydb.cursor()
sql = "INSERT INTO customers (name, address) VALUES (%s, %s)"
val = ("John", "Highway 21")
mycursor.execute(sql, val)
mydbmit()

批量插入数据：
可以使用 executemany() 方法批量插入数据。例如：

mycursor = mydb.cursor()
sql = "INSERT INTO customers (name, address) VALUES (%s, %s)"
val = [  ('Peter', 'Lowstreet 4'),  ('Amy', 'Apple st 652'),  ('Hannah', 'Mountain 21'),  ('Michael', 'Valley 345'),  ('Sandy', 'Ocean blvd 2'),  ('Betty', 'Green Grass 1'),  ('Richard', 'Sky st 331'),  ('Susan', 'One way 98'),  ('Vicky', 'Yellow Garden 2'),  ('Ben', 'Park Lane 38'),  ('William', 'Central st 954'),  ('Chuck', 'Main Road 989'),  ('Viola', 'Sideway 1633')]
mycursor.executemany(sql, val)
mydbmit()

此外，在执行 SQL 语句之后，需要调用 commit() 方法才能确保修改已经生效。
在执行 INSERT INTO 语句时，可以使用 %s 占位符来表示参数，然后使用元组或列表传递参数。

2、完整代码实现：批量创建表、sql语句批量插入数据数据库

根据上述介绍，python使用类似的方法实现在mysql中操作执行

import mysql.connector    # 导入mysql数据库的连接器
import pandas as pd    # 导入pandas库，用于读取csv文件
import math    # 导入math库，用于进行数学运算
#
# # 读取dev.csv文件
# data = pd.read_csv('dev.csv')
#
# # 将数据转换为列表
# data_list = data.values.tolist()
#
# # 输出列表
# print(data_list[0])# 创建连接
mydb = mysql.connector.connect(host="localhost",    # 数据库主机地址user="root",    # 数据库用户名password="password"    # 数据库密码
)# 初始化删除与创建数据库
mycursor = mydb.cursor()
mycursor.execute("DROP DATABASE mynlpdatabase;")    # 删除已有的数据库
mycursor.execute("CREATE DATABASE mynlpdatabase")    # 创建新的数据库# 连接数据库
mydb = mysql.connector.connect(host="localhost",user="root",password="password",database="mynlpdatabase"
)# 创建表
mycursor = mydb.cursor()# 拼接TABLE
result = ""
for i in range(3):texts = "column"+str(i+1)+" VARCHAR(255)"result += texts + ","
print(result[:-1])mycursor.execute("CREATE TABLE mycsv_dev (id INT AUTO_INCREMENT PRIMARY KEY, "+result[:-1]+")")    # 创建一个新的表，表名为mycsv_dev# 将数据插入表中
x_list,y_list,z_list = [],[],[]
for i in range(3):i = 0.5*i*(math.pi)+4x = math.sin(i)y = math.cos(i)z = math.tan(i)x_list.append(x)y_list.append(y)z_list.append(z)data1 = x_list    # 第一行数据
data2 = y_list    # 第二行数据
data3 = z_list    # 第三行数据# 拼接column，VALUES
resultmycsv_dev = ""
s_dev = ""
for i in range(3):texts2 = "column"+str(i+1)resultmycsv_dev += texts2 + ","s_dev += "%s"+ ","print(resultmycsv_dev[:-1],s_dev[:-1])sql = "INSERT INTO mycsv_dev ("+resultmycsv_dev[:-1]+") VALUES ("+s_dev[:-1]+")"lists1 = [x_list[0], x_list[1], x_list[2]]
lists2 = [y_list[0], y_list[1], y_list[2]]
lists3 = [z_list[0], z_list[1], z_list[2]]alllist = [lists1,lists2,lists3]
for listsi in alllist:vals = tuple([lst for lst in listsi])mycursor.execute(sql, vals)  # 向表中插入第一行数据mydbmit()    # 提交更改
print(mycursor.rowcount, "记录插入成功。")    # 输出插入的记录数

运作结果：

3、封装上述代码并注释每一行代码

import mysql.connector # 导入mysql连接器模块
import pandas as pd # 导入pandas模块
import math # 导入math模块# 定义函数，读取csv文件并转换为列表
def read_csv(filepath):data = pd.read_csv(filepath) # 读取csv文件data_list = data.values.tolist() # 将读取的数据转换为列表return data_list# 定义函数，创建数据库
def create_database(dbname, host, user, password):mydb = mysql.connector.connect(host=host, # 数据库主机地址user=user, # 数据库用户名password=password # 数据库密码)mycursor = mydb.cursor() # 创建游标对象mycursor.execute(f"DROP DATABASE IF EXISTS {dbname};") # 如果数据库已经存在则删除mycursor.execute(f"CREATE DATABASE {dbname}") # 创建数据库mydb.close() # 关闭数据库连接# 定义函数，连接数据库
def connect_database(dbname, host, user, password):mydb = mysql.connector.connect(host=host, # 数据库主机地址user=user, # 数据库用户名password=password, # 数据库密码database=dbname # 数据库名称)return mydb # 返回数据库连接对象# 定义函数，创建表
def create_table(mydb, tablename, columns):mycursor = mydb.cursor() # 创建游标对象cols = ', '.join([f"{col} VARCHAR(255)" for col in columns]) # 创建表的列信息mycursor.execute(f"CREATE TABLE IF NOT EXISTS {tablename} (id INT AUTO_INCREMENT PRIMARY KEY, {cols})") # 创建表# id为主键列，自动递增# 定义函数，插入数据
def insert_data(mydb, tablename, data):mycursor = mydb.cursor() # 创建游标对象columns = [f"column{i+1}" for i in range(len(data))] # 创建表的列名cols = ', '.join(columns) # 将列名转换为字符串vals = ', '.join(['%s' for i in range(len(data[0]))]) # 创建占位符sql = f"INSERT INTO {tablename} ({cols}) VALUES ({vals})" # 创建插入数据的SQL语句for row in data:mycursor.execute(sql, row) # 执行SQL语句，插入数据mydbmit() # 提交事务# 定义函数，生成数据
def generate_data():x_list,y_list,z_list = [],[],[]for i in range(3):i = 0.5*i*(math.pi)+4 # 生成数据x = math.sin(i)y = math.cos(i)z = math.tan(i)x_list.append(x)y_list.append(y)z_list.append(z)data1 = x_listdata2 = y_listdata3 = z_listdata = [data1, data2, data3]return dataif __name__ == '__main__':# 读取csv文件data = read_csv('dev.csv')# 创建数据库create_database('mynlpdatabase', 'localhost', 'root', '密码')# 连接数据库mydb = connect_database('mynlpdatabase', 'localhost', 'root', '密码')# 创建表create_table(mydb, 'mycsv_dev', ['column1', 'column2', 'column3'])# 插入数据data = generate_data()insert_data(mydb, 'mycsv_dev', data)print("记录插入成功.")

实现结果同上。