每日10行代码114:用python根据excel的明细数据自动创建建表语句

176 篇文章 4 订阅
15 篇文章 2 订阅

背景:
如果想把excel类型的明细数据导入数据库的话,第一步一般都是先建表,但是建表其实是一个挺繁琐的事,尤其是在数据列非常多的情况下,需要为每一列选择一个合适的名称,再选择一个合适的数据类型。我的工作中经常遇到类似的问题,在网上找了下,也没有合适的解决方案,pandas里的to_sql倒是可以自动建表并插入数据,但有时选择的数据类型也不太合适,会有bug。
另外看到一个兄弟写的文章https://blog.csdn.net/ltg01/article/details/48422145 但这个里面用的是c#, 这个我不会。
找不到好方法,那就只有自己造轮子了,于是我写了一个小程序来自动生成建表语句。

from ExcelColumn import ExcelColumn
import openpyxl
import pinyin
from os.path import basename


path = r'e:\投标记录.xlsx'

wb = openpyxl.load_workbook(path)
ws = wb.active


def get_table_name(path):
	t = basename(path)
	fn = t.split('.')[0]
	return pinyin.get_initial(fn, delimiter="").upper()

def make_col_sql(col_index):
	col = ExcelColumn(path,column_index=col_index)
	col_dbname = col.get_column_dbname()
	if col.is_num_col():
		col_sql_sfx = 'Number'
	else:
		if col.is_equal_len():
			col_len = col.get_column_max_len()
			col_sql_sfx = 'char(' + str(col_len) +')'  
		else:
			col_len = int(col.get_column_max_len()*1.2)
			col_sql_sfx = 'Varchar2(' + str(col_len) +')'  
	col_sql = f'{col_dbname} {col_sql_sfx}'
	return col_sql

def make_col_comment_sql(col_index):
	col = ExcelColumn(path,column_index=col_index)
	col_name = col.get_column_name()
	col_dbname = col.get_column_dbname()
	sql = f"comment on column {col_dbname} is '{col_name}';\n"
	return sql
	


def make_create_table_sql():
	table_name = get_table_name(path)
	sql = 'create table '+ table_name + '\n(\n'
	for col_index in range(1, ws.max_column+1):
		col_sql = make_col_sql(col_index)
		sql += '\t' + col_sql + ',\n'
	create_table_sql = sql[:-2] + '\n);'
	return create_table_sql


def make_comment_sql():
	comment_sql = ''
	for col_index in range(1, ws.max_column+1):
		comment_sql += make_col_comment_sql(col_index)
	return comment_sql

	
sql1 = make_create_table_sql()
sql2 = make_comment_sql()
print(sql1 + '\n' +sql2)	

输出结果:

create table TBJL
(
	BH Number,
	TBRQ char(10),
	JKBT Varchar2(52),
	TBJE Varchar2(21),
	HKJD Varchar2(6),
	ZT char(6),
	TBLX char(9),
	HKJH char(12)
);
comment on column BH is '编号';
comment on column TBRQ is '投标日期';
comment on column JKBT is '借款标题';
comment on column TBJE is '投标金额';
comment on column HKJD is '还款进度';
comment on column ZT is '状态';
comment on column TBLX is '投标类型';
comment on column HKJH is '还款计划';

文中用到的类是我另一篇文章里的:
https://blog.csdn.net/weixin_44981444/article/details/113532205

最后要说明的:

  1. 我的建表语句是基于oracle的,没有在mysql和其他数据库里测试过。
  2. 由于日期格式的复杂性,excel里可能出现‘20190303’,‘2019-03-03’ , ‘2019、03、03 11:32:30’ 等各种类型的日期格式,导入数据库中需要针对不同格式进行转换,工作量较大,为了方便,我统一使用varchar2格式来导入日期数据,兼容性高一些。至于各种日期格式的统一处理,可以放到数据库里进行。当然如果日期格式是统一的话,也是很方便改下程序来插入日期的。
  3. 对于长度可变的字符列,我取了最大长度*1.2来设置合理的长度范围,以应对后期插入更长的数据。
  4. 取文件名的首字母缩写作为数据库表名,取列的首字母缩写作为数据库列名。
  5. 默认excel没有复杂的表头,第一行为列名,以下为数据。

目前程序的不足之处:

  • 由于写的匆忙,里面函数的调用可能会有些混乱,有些表达式有些重复。
  • 没考虑列名重复的情况,没考虑列名为空的情况。
  • 在函数内调用了函数外的变量。

后记:
随着代码能力的慢慢提高,每日10行代码已经有些不太适合我目前状况了,因为我想写的是有实际意义 的代码,有一定的应用背景的,但是这样的代码还需要一些思路,一些场景,再加上现在随便写一点小功能,一般的代码量都已经不止10行了。我也不想为了学习而学习,硬是去写一些代码。所以后期我可能会降低些更新频率,写一些复杂点的项目,等项目完成再一起发布。

  • 0
    点赞
  • 3
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值