Python Forum

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.

Error:
Traceback (most recent call last):
  File "C:\Users\Rachit-PC\AppData\Local\Continuum\Anaconda3\lib\site-packages\IPython\core\interactiveshell.py", line 2881, in run_code
    exec(code_obj, self.user_global_ns, self.user_ns)
  File "<ipython-input-40-f4d415e15688>", line 31, in <module>
    cursor.execute(sql_query)
sqlite3.OperationalError: duplicate column name: Measure

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)

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