for field in fields:
if field['primary_key'] == 'Y':
primary_key.append(field['column_name'])
table_column = field['column_name'] + ' ' + field['type'] + ' ' + 'COMMENT ' + "'" + field['column_exp'] + "'" + ',\n'
#print(table_column)
columns.append(table_column)
#print(columns)
stg_create_columns = ''.join(
columns)[:-2]
# print(stg_create_columns)
create_stg_sql = "drop table if exists {};\ncreate table {} (\n{});".format(
stg_table_name,stg_table_name, stg_create_columns)
# print(create_stg_sql)
return create_stg_sql
# print(os.getcwd())
par_path = os.getcwd()
paths = [par_path+'\\']
print('---------------paths-------------------')
print(paths)
for path in paths:
for filename in os.listdir(path):
print(filename)
if filename.endswith(".xlsx") or filename.endswith(".xls"):
result_sql = ''
print(path)
print(filename)
print(path + filename)
worksheet = xlrd.open_workbook(path + filename)
table_names = worksheet.sheet_names()
for table_name in range(len(table_names)):
sheet = worksheet.sheet_by_index(table_name)
nrows = sheet.nrows
fields = []
for i in range(1,nrows):
res = sheet.row_values(i)
desc = {
'table_name': table_names[table_name].lower(),
'column_exp': res[0],
'column_name': res[1].lower(),
'type': convert_type(res[2]).upper(),
'primary_key': res[3],
fields.append(desc)
#print(fields)
result_sql += mysql_create(fields) + '\n\n'
with open(path+'\\'+filename[:-5]+'.sql', "w", encoding='utf-8') as f:
f.write(str(result_sql))
f.close()
测试使用的表结构设计为
https://download.csdn.net/download/csdnshenjiaye/12171672
将python脚本和设计表的excel放在同一目录下即可，excel中sheet名就是表名，生成的建表语句保存在了脚本所在目录。
此脚本只是实现了基本功能，脚本还可以扩展，支持更多可配置功能，比如配置库名传参，支持不同的数据库等，欢迎大家补充！
自动生成mysql建表语句平时在设计号表结构后，还得写建表语句，其实可以让脚本来完成这样重复的工作，python代码如下# -*- coding=utf-8 -*-#coding=utf-8import xlrdimport osdef convert_type(data_type): """Normalize MySQL `data_type`""" if '...
[root@serwang ~]# pip3 install sqlalchemy
Collecting sqlalchemy
Downloading http://pypi.doubanio.com/packages/aa/cc/48eec885d81f7260b07d
961b3ec
def to_hive_type(type):
if re.match(r'varchar|char|text|tinytext|longtext|blo',type,flags=0):
return 'string'
elif re.match(r'timestamp|date|bigint',type,flags=0):
return 'bigint'
elif re.match(r'int|ti
Sub 选项按钮4_Click()
Dim sqlStr, sqlTemp, pkStr, columnName, columnType, columnLength, isPk, notExistTable, isNull, comment As String
Dim tableListIndex, columnListIndex As Integer
Dim a...
create or replace function fnc_table_to_
mysql
( i_owner in string,
i_table_name in string,
i_number_default_type in string := 'decimal',
i_auto_inc...
def
mysql
_to_postgresql(
mysql
_query):
# 将
MySQL
建表语句
转换为 PostgreSQL
建表语句
postgres_query =
mysql
_query.replace("INT", "INTEGER") \
.replace("AUTO_INCREMENT", "SERIAL") \
.replace("DEFAULT '0000-00-00'", "") \
.replace("DEFAULT '0'", "") \
.replace("ENGINE=InnoDB", "") \
.replace("VARCHAR", "VARCHAR(255)") \
.replace("PRIMARY KEY", "PRIMARY KEY") \
.replace("UNSIGNED", "") \
.replace("COMMENT '", "COMMENT '") \
.replace("`", "\"")
# 处理索引
index_start = postgres_query.find("KEY ")
while index_start != -1:
index_end = postgres_query.find(",", index_start)
if index_end == -1:
index_end = postgres_query.find(")", index_start)
index_name = postgres_query[index_start:index_end].replace("`", "").replace("KEY ", "\"").replace("(", "_").replace(")", "\"")
postgres_query = postgres_query[:index_start] + "CONSTRAINT " + index_name + " " + postgres_query[index_start:index_end] + postgres_query[index_end:]
index_start = postgres_query.find("KEY ", index_end)
return postgres_query
使用方法：
```
python
mysql
_query = "CREATE TABLE `users` (`id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, `name` VARCHAR(50) NOT NULL, `age` INT UNSIGNED NOT NULL, `email` VARCHAR(100) NOT NULL, INDEX (`name`))"
postgres_query =
mysql
_to_postgresql(
mysql
_query)
print(postgres_query)
输出结果：
CREATE TABLE "users" ("id" INTEGER SERIAL PRIMARY KEY, "name" VARCHAR(255) NOT NULL, "age" INTEGER NOT NULL, "email" VARCHAR(100) NOT NULL, CONSTRAINT "name_index" INDEX ("name"))
注意事项：
- 该
脚本
只对一些常见的
MySQL
数据类型和
建表语句
进行了转换，如果您的
MySQL
建表语句
涉及到其他数据类型或特殊语法，可能需要对
脚本
进行修改。
- 索引的处理方式是将
MySQL
的 KEY 转换为 PostgreSQL 的 CONSTRAINT INDEX，索引名称的
生成
方式是将 KEY 后面的字段名（如果有）加上下划线作为索引名称。
