how to ingest a table specification file in .txt form and create a table in sqlite?-CodePudding

I tried a few different ways, below but having trouble a) removing the width and b) removing the \n with a comma. I have a txt file like the below and I want to take that information and create a table in sqlite (all using python)

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

Current python code - trying to read in the file, and get the values to pass in the sql statement below

import os import pandas as 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 I want to pass in

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

CodePudding user response：

This produces something that will work.

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 )