SQLite DB integration duplicate columns - Printable Version +- Python Forum (https://python-forum.io) +-- Forum: Python Coding (https://python-forum.io/forum-7.html) +--- Forum: General Coding Help (https://python-forum.io/forum-8.html) +--- Thread: SQLite DB integration duplicate columns (/thread-5899.html) |
SQLite DB integration duplicate columns - rachitmishra25 - Oct-27-2017 I am trying to populate an SQLite DB within a python script via a CSV file. I am getting an error whenever my code iterates through a csv that has multiple attributes with same partial names. For instance, I have a column Measure Date and another column Measure City. I am trying to replace the blank spaces with underscores so as to make it MEASURE_DATE and MEASURE_CITY and eliminate any chance of error altogether but really not sure as to at which part of the code should I include that. I have attached my code snippet below.import string import codecs #from sqlite3 import pragma for file in glob.glob(os.path.join(staging, "*.csv")): tablename = os.path.splitext(os.path.basename(file).lower())[0] #print(tablename) if tablename.find(" ")>0: tablename = tablename.replace(" ", "_") tablename.lower() if tablename.find("-")>0 : tablename = tablename.replace("-", "_") if tablename.find("%")>0: tablemame = tablename.replace("%", "pct ") if tablename.find("/") > 0: tablemame = tablename.replace("/", "_") with codecs.open(file, "rU", 'cp1252') as f: reader = csv.reader(x.replace('\0', '') for x in f) header = True for row in reader: if header: header = False sql_query = "DROP TABLE IF EXISTS %s" % tablename cursor.execute(sql_query) sql_query = "CREATE TABLE %s (%s)" % (tablename, ", ".join(["%s TEXT" % col for col in row ])) cursor.execute(sql_query) #print(col) RE: SQLite DB integration duplicate columns - buran - Oct-27-2017 you need to change this line sql_query = "CREATE TABLE %s (%s)" % (tablename, ", ".join(["%s TEXT" % col for col in row ]))with sql_query = "CREATE TABLE %s (%s)" % (tablename, ", ".join(["%s_TEXT" % col.replace(' ', '_') for col in row ]))note that you have space also in %s TEXT
|