读取"."分隔的文件并使用 python 创建 SQL 查询

Question

输入文件:

sch1.ft1
sch1.ft2
sch2.ft3
sch2.ft4

查询:

def get_tables_columns(filename):
res_dct = {}
with open(filename) as f:
for line in f:
line = line.strip()
cols = line.split('.')
res_dct = {cols[i]: cols[i + 1] for i in range(0, len(cols), 2)}
return res_dct
tables = get_tables_columns("SchemaTable.txt")
print(tables)
def get_sql(schema, table):
statement = "select colno, colname, typename, length from syscat.columns where tabschema ='{0}' and tabname ='{1}' order by colno ".format(schema,table)
return statement

我写的函数的输出:{'sch2': 'ft4'}

我需要建立一个查询使用键和值像{sch1:fact1, sch1:fact2, sch2:fact3, sch2:fact4}。

所需的SQL:

the requirement is if the sql runs for 
`select tabschema, tabname, colno, colname, typename, length from syscat.columns where tabschema ='sch1' and tabname ='ft1' order by colno`
the output should be stored in sch_ft1.txt. like wise for all the contents in input file.

是否可以将每个sql的输出存储在diff文件中。例如，如果代码为select colno, colname, typename, length from syscat.columns where tabschema ='sch2' and tabname ='fact4' order by colno运行，我需要将内容存储在sch2_fact4.txt文件中，就像其他查询一样

Answer 1

{sch1:fact1, sch1:fact2, sch2:fact3, sch2:fact4}不是一个有效的字典，因为字典需要有唯一的键。可以在get_tables_columns函数中调用get_sql函数，为读取的每一行构造查询。

这段代码应该给出你想要的输出。它将把查询写入一个名为query.txt

的文件中

def get_sql(schema, table):
statement = "select colno, colname, typename, length from syscat.columns where tabschema ='{0}' and tabname ='{1}' order by colno ".format(schema,table)
return statement
def get_tables_columns(filename):
res_dct = {}
output = open("query1.txt", 'a')
with open(filename) as f:
for line in f:
line = line.strip()
cols = line.split('.')
query = get_sql(cols[0], cols[1]) + "n"
with open(line + ".txt", 'w+') as my_file:
my_file.write(query) 
output.close()
return res_dct
get_tables_columns("SchemaTable.txt")