Python Forum
SQLite DB integration duplicate columns
Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
SQLite DB integration duplicate columns
#1
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)
Reply
#2
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
Reply


Possibly Related Threads…
Thread Author Replies Views Last Post
  Create SQLite columns from a list or tuple? snakes 6 1,847 May-04-2021, 12:06 PM
Last Post: snakes
Photo Integration of apache spark and Kafka on eclipse pyspark aupres 1 1,387 Feb-27-2021, 08:38 AM
Last Post: Serafim
  Tableau Time Series Prediction using Python Integration tobimarsh43 0 787 Jul-24-2020, 10:38 AM
Last Post: tobimarsh43
  R-PYTHON INTEGRATION RELATED PROBLEM arnab93 0 728 Jun-05-2020, 02:07 PM
Last Post: arnab93
  STATA/Python Integration jprender 0 812 May-03-2020, 09:38 PM
Last Post: jprender
  Sqlite CONCAT columns issac_n 4 2,342 Mar-22-2020, 09:31 AM
Last Post: buran
  Integration of a complex function having singularities using quad amjad26 0 2,379 May-17-2019, 09:16 PM
Last Post: amjad26
  phython language java integration jammytcs123123 1 1,335 Jul-04-2018, 03:13 AM
Last Post: Skaperen
  Trapezoidal integration method in python. Getting wrong results auting82 5 3,995 Oct-14-2017, 07:38 PM
Last Post: buran
  Sympy Integration Flexico 5 5,125 Dec-07-2016, 07:24 AM
Last Post: micseydel

Forum Jump:

User Panel Messages

Announcements
Announcement #1 8/1/2020
Announcement #2 8/2/2020
Announcement #3 8/6/2020