遇到这个的情况,有一个表格里面有这个数据表结构,需要把他转为sql语句,然后执行创建表到mysql上面
编号 | 字段名 | 数据类型 | 数据长度 | 数据对象说明 | 可否空 |
1 | account | int | 外键,学号 | 否 | |
2 | batch | nvarchar | 50 | 考试批次 | |
3 | session | nvarchar | 20 | 学年学期 | 否 |
4 | testdate | varchar | 38 | 考试时间地点 | |
5 | starttime | datetime | 开始时间 | ||
6 | endtime | datetime | 截至时间 |
用chatgpt生成了一个简单的demo,然后修改了下,就可以,下面是代码
import pandas as pd
import docx
# 读取 Excel 数据
df = pd.read_excel('E:/img/临时数据库表.xls')
# 生成 CREATE TABLE 语句
table_name = 'test_table' # 指定表名
sql_cols = []
for index, row in df.iterrows():
col_name = row['字段名']
data_type = row['数据类型']
if (data_type in "nvarchar"):
data_type = 'varchar'
data_len = row['数据长度'] if pd.notnull(row['数据长度']) else None
data_desc = row['数据对象说明']
is_nullable = 'NULL' if row['可否空'] == '否' else 'NOT NULL'
sql_col = f'{col_name} {data_type}'
print("字段名:"+str(col_name)+"类型:"+str(data_type))
if data_len is not None:
sql_col += f'({int(data_len)})'
sql_col += f' {is_nullable} COMMENT "{data_desc}"'
sql_cols.append(sql_col)
sql_cols_str = ',\n '.join(sql_cols)
sql = f'drop table if exists {table_name};CREATE TABLE {table_name} (\n {sql_cols_str}\n) ENGINE=InnoDB auto_increment=100 comment = {table_name};'
print(sql)
生成的sql还是有点问题,比如没有主键id问题,需要自己设置
drop table if exists test_table;CREATE TABLE test_table (
account int NOT NULL COMMENT "外键,学号",
batch varchar(50) NOT NULL COMMENT "考试批次",
session varchar(20) NOT NULL COMMENT "学年学期",
testdate varchar(38) NOT NULL COMMENT "考试时间地点",
starttime datetime NOT NULL COMMENT "开始时间",
endtime datetime NOT NULL COMMENT "截至时间",
subject varchar(20) NOT NULL COMMENT "考试项目",
pay varchar(1) NOT NULL COMMENT "是否缴费",
enroll varchar(1) NOT NULL COMMENT "是否报名",
lessonid int NOT NULL COMMENT "课程号",
lesson varchar(20) NOT NULL COMMENT "课程名",
cost varchar(10) NOT NULL COMMENT "费用类型",
amount decimal(10) NOT NULL COMMENT "金额",
teacher varchar(15) NOT NULL COMMENT "任课教师",
nature varchar(10) NOT NULL COMMENT "课程性质",
credit double NOT NULL COMMENT "学分",
way varchar(10) NOT NULL COMMENT "考试方式",
form varchar(4) NOT NULL COMMENT "考试形式",
stutype varchar(5) NOT NULL COMMENT "修读类型",
classtype varchar(10) NOT NULL COMMENT "课程类别",
pectype varchar(10) NOT NULL COMMENT "公选课类别",
score int NOT NULL COMMENT "成绩",
stuway varchar(10) NOT NULL COMMENT "修读方式",
check varchar(10) NOT NULL COMMENT "复查操作"
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
最后修改的sql如下
drop table if exists school_exam;CREATE TABLE school_exam (
exam_id bigint(20) not null auto_increment COMMENT "外键,学号",
batch varchar(50) NOT NULL COMMENT "考试批次",
exam_session varchar(20) NOT NULL COMMENT "学年学期",
test_date varchar(38) NOT NULL COMMENT "考试时间地点",
start_time datetime NOT NULL COMMENT "开始时间",
end_time datetime NOT NULL COMMENT "截至时间",
exam_subject varchar(20) NOT NULL COMMENT "考试项目",
pay varchar(1) NOT NULL COMMENT "是否缴费",
enroll varchar(1) NOT NULL COMMENT "是否报名",
lesson_id int NOT NULL COMMENT "课程号",
lesson varchar(20) NOT NULL COMMENT "课程名",
cost varchar(10) NOT NULL COMMENT "费用类型",
amount decimal(10) NOT NULL COMMENT "金额",
teacher varchar(15) NOT NULL COMMENT "任课教师",
nature varchar(10) NOT NULL COMMENT "课程性质",
credit double NOT NULL COMMENT "学分",
way varchar(10) NOT NULL COMMENT "考试方式",
form varchar(4) NOT NULL COMMENT "考试形式",
stu_type varchar(5) NOT NULL COMMENT "修读类型",
class_type varchar(10) NOT NULL COMMENT "课程类别",
pec_type varchar(10) NOT NULL COMMENT "公选课类别",
score int NOT NULL COMMENT "成绩",
stu_way varchar(10) NOT NULL COMMENT "修读方式",
check_action varchar(10) NOT NULL COMMENT "复查操作",
del_flag char(1) default '0' comment '删除标志(0代表存在 2代表删除)',
create_by varchar(64) default '' comment '创建者',
create_time datetime comment '创建时间',
update_by varchar(64) default '' comment '更新者',
update_time datetime comment '更新时间',
primary key (exam_id)
) ENGINE=InnoDB auto_increment=100 comment = '考试信息数据表';