如何获取.txt格式的表规范文件并在sqlite中创建表

Question

我尝试了以下几种不同的方法，但遇到了问题a(删除宽度和b(删除带逗号的。我有一个如下所示的txt文件，我想获取这些信息并在sqlite中创建一个表(全部使用python(

"field",width, type
name, 15, string
revenue, 10, decimal
invoice_date, 10, string
amount, 2, integer

当前python代码-尝试读取文件，并获取要在下面的sql语句中传递的值

导入操作系统进口熊猫作为pd

dir_path = os.path.dirname(os.path.realpath(__file__))
file = open(str(dir_path) + '/revenue/revenue_table_specifications.txt','r')
lines = file.readlines()
table = lines[2::]
s = ''.join(str(table).split(','))
x = s.replace("n", ",").strip()
print(x)

我想在中传递的sql

c = sqlite3.connect('rev.db') #connnect to DB
try: 
    c.execute('''CREATE TABLE
                    revenue_table (information from txt file,
                             information from txt file,
                             ....)''')
except sqlite3.OperationalError: #i.e. table exists already
    pass

Answer 1

这会产生一些有用的东西。

def makesql(filename):
    s = []
    for row in open(filename):
        if row[0] == '"':
            continue
        parts = row.strip().split(", ")
        s.append( f"{parts[0]} {parts[2]}" )
    return "CREATE TABLE revenue_table (n" + ",n".join(s) + ");"
sql = makesql( 'x.csv' )
print(sql)
c.execute( sql )