worldCup/formatSQL.py at master · qualityassurance21/worldCup · GitHub

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
#!/usr/bin/env python
# -*- coding: utf-8 -*-
# @Author : Torre Yang Edit with Python3.6
# @Email  : klyweiwei@163.com
# @Time   : 2018/6/19 10:22
# 适用于所有 json相关爬虫的数据采集 任务
import connect_dataBase

# db连接
connectDB = connect_dataBase.ConnectDatabase()
get_conf = connectDB.get_conf('databases_conf.json')
conn, cur = connectDB.connect_db(get_conf["worldCup"]["host"], get_conf["worldCup"]["user"],
                     get_conf["worldCup"]["password"], get_conf["worldCup"]["database"], get_conf["worldCup"]["port"])


# def getTabs():
#     sql = 'show tables'
#     res = connectDB.get_res(cur, sql)
#     saveTabs = []
#     for tab in res:
#         tab = tab[0]
#         saveTabs.append(tab)
#     # print(saveTabs)
#     return saveTabs
# print(connectDB.getTabs(cur))

# 一键 获取表, 并为数据的存储做铺垫
# tabs = connectDB.getTabs(cur)
# for tab in tabs:
#     sqll = 'insert into '+ tab
#     print(sqll)

# 数据的格式化 很重要 , ajax请求json爬取数据自动生产插入SQL语句 "'+str(dt)+'"
def formatSQL(table, cur):
    # table名字
    resCon = connectDB.get_cols(table, cur)
    saveConls = []
    saveVals = []
    for row in resCon:
        col = row[0]
        # print(col)
        # 保存值列表
        val = '"\'+str(' + str(col) + ')+\'"'
        saveVals.append(val)
        saveValss = ','.join(saveVals)
        # 保存字段列表
        saveConls.append(col)
        saveConlss = ','.join(saveConls)
    # return saveConls, saveVals

        # col = '"\'+str('+ str(col) +')+\'"'
        # saveConls.append(col)
        # saveConlss = ','.join(saveConls)
    sql = 'insert into '+table+''+'('+saveConlss+')'+ 'values('+saveValss+')'
    return sql


# 函数 的调用
sql = formatSQL('teamtechavg', cur)
print(sql)