forked from Boris-code/feapder
-
Notifications
You must be signed in to change notification settings - Fork 0
Expand file tree
/
Copy pathcreate_table.py
More file actions
135 lines (114 loc) · 3.72 KB
/
create_table.py
File metadata and controls
135 lines (114 loc) · 3.72 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
# -*- coding: utf-8 -*-
"""
Created on 2018-08-28 17:38:43
---------
@summary: 根据json生成表
---------
@author: Boris
@email: boris_liu@foxmail.com
"""
import sys
import time
import feapder.setting as setting
import feapder.utils.tools as tools
from feapder.db.mysqldb import MysqlDB
from feapder.utils.tools import key2underline
class CreateTable:
def __init__(self):
self._db = MysqlDB()
def is_vaild_date(self, date):
try:
if ":" in date:
time.strptime(date, "%Y-%m-%d %H:%M:%S")
else:
time.strptime(date, "%Y-%m-%d")
return True
except:
return False
def get_key_type(self, value):
try:
value = eval(value)
except:
value = value
key_type = "varchar(255)"
if isinstance(value, int):
key_type = "int"
elif isinstance(value, float):
key_type = "double"
elif isinstance(value, str):
if self.is_vaild_date(value):
if ":" in value:
key_type = "datetime"
else:
key_type = "date"
elif len(value) > 255:
key_type = "text"
else:
key_type = "varchar(255)"
return key_type
def get_data(self):
"""
@summary: 从控制台读取多行
---------
---------
@result:
"""
data = ""
while True:
line = sys.stdin.readline().strip()
if not line:
break
data += line
return tools.get_json(data)
def create(self, table_name):
# 输入表字段
print('请输入表数据 json格式 如 {"name":"张三"}\n等待输入：\n')
data = self.get_data()
if not isinstance(data, dict):
raise Exception("表数据格式不正确")
# 拼接表结构
sql = """
CREATE TABLE `{db}`.`{table_name}` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT 'id 自动递增',
{other_key}
`gtime` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '抓取时间',
PRIMARY KEY (`id`),
{unique}
) COMMENT='';
"""
print("请设置注释 回车跳过")
other_key = ""
for key, value in data.items():
key = key2underline(key)
key_type = self.get_key_type(value)
comment = input("%s : %s -> comment：" % (key, key_type))
other_key += "`{key}` {key_type} COMMENT '{comment}',\n ".format(
key=key, key_type=key_type, comment=comment
)
print("\n")
while True:
is_need_batch_date = input("是否添加batch_date 字段 （y/n）:")
if is_need_batch_date == "y":
other_key += "`{key}` {key_type} COMMENT '{comment}',\n ".format(
key="batch_date", key_type="date", comment="批次时间"
)
break
elif is_need_batch_date == "n":
break
print("\n")
while True:
unique = input("请设置唯一索引, 多个逗号间隔\n等待输入：\n").replace("，", ",")
if unique:
break
unique = "UNIQUE `idx` USING BTREE (`%s`) comment ''" % "`,`".join(
unique.split(",")
)
sql = sql.format(
db=setting.MYSQL_DB,
table_name=table_name,
other_key=other_key,
unique=unique,
)
print(sql)
self._db.execute(sql)
print("\n%s 创建成功" % table_name)