cant resolve cryptic error in Python/sqlite program

Question

Im running into this error that I can't work out Im writing some code in Python using tkinter interface to transfer data from a text file to sqlite.

first, here is the relevant code:

def submit_data(self):
    self.new_filename = self.file_entry.get()
    self.new_tablename = self.table_entry.get()
    self.new_fieldname = self.field_entry.get().split(',')

    #print(self.new_fieldname)
    self.create_new.destroy()

    from sqlite3 import dbapi2 as sqlite

    con = sqlite.connect(self.new_filename)    
    cur = con.cursor()
    cur.execute('CREATE TABLE ' + self.new_tablename + '(id INTEGER PRIMARY KEY)')
    for field in self.new_fieldname:
        cur.execute('ALTER TABLE ' + self.new_tablename + ' ADD ' + field)

    with open(self.filename, 'r', encoding='latin-1') as self.the_file:    
        status = True
        #_keynumber=1
        while status:
            _row = self._next_line()

            if _row:
                _entry_list = _row.split(',')
                # add space after text line comma for formatting
                _entry_list = ', '.join(_entry_list)
                #print(_entry_list)

                #entries = {'row': _keynumber, 'entry': _entry_list} 
                #row_entry = "INSERT INTO " + self.new_tablename + " VALUES(" + _entry_list + ")"
                cur.execute("INSERT INTO " + self.new_tablename + " VALUES(" + _entry_list + ")")

                #_colrange = range(_colamount)                    

                #_keynumber+=1

            else:
                status = False   

        con.commit()

At the cur.execute("INSERT INTO " ... line (about 6 lines up) I get this error: ** cur.execute("INSERT INTO " + self.new_tablename + " VALUES(" + _entry_list + ")") sqlite3.OperationalError: near ".": syntax error**

I have changed this around in many different ways. At one time I had the whole "INSERT INTO ... VALUES ...." string as a variable and used

cur.execute(*variable*)

when I did it this way the error was the same except "OperationalError: near "." was "OperationalError: near "of" ... and there was no 'of' anywhere.

Im really confused and frustrated. Someone break this down for my please??

Thanks F

the text file lines its reading are set up like this: A Big Star In Hollywood,Sandra Dickinson

so I had figured that if I use .join() to put a space after the comma then the string would be the equivalent of two VALUES for the INSERT INTO statement.

Kien Truong · Accepted Answer · 2012-03-08 11:03:43Z

5

Remove

_entry_list = ', '.join(_entry_list)

and use

cur.execute("INSERT INTO " + self.new_tablename + "(" + ",".join(self.new_fieldname) +") VALUES(" + ",".join(("?" for i in xrange(len(_entry_list)))) + ")", _entry_list)

This will parameterize your query and automatically quote all value in _entry_list.

You still have to manually quote self.new_tablename and self.new_fieldname. This should be before you use them in any sql statements.

edited Mar 8, 2012 at 11:03

answered Mar 8, 2012 at 8:33

Kien Truong

11.4k2 gold badges34 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Li-aung Yip Over a year ago

This is the Correct Way. Note that using parametrized queries will also protect you against SQL injection attacks. (What happens if someone types "Robert'); DROP TABLE Students;" into your GUI?)

Icsilk Over a year ago

Thank you, that seems like it will work, but im running into a sort of cyclical problem now. because when I created the table I started with an id column, this code is telling me my table has three columns but I only supplied 2 values. when I add a binding, like " VALUES(?," for example it says theres too many bindings. so theres either too many bindings for the values supplied or too many columns. I've tinkered with different things, like the above example and get the same kinds of errors.

Icsilk Over a year ago

yeah, I had removed that line a while ago. I can't seem to get the too many bindings/too many columns issue resolved still though.

Icsilk Over a year ago

all seems to be right now except because on the CREATE TABLE I defaulted it with the id INTEGER PRIMARY KEY and then added two columns. so as is I get: sqlite3.OperationalError: table mememel has 3 columns but 2 values were supplied and if I add a binding I get: sqlite3.ProgrammingError: Incorrect number of bindings supplied. The current statement uses 3, and there are 2 supplied. still giving me hardcore problems.

Burhan Khalid · Accepted Answer · 2012-03-08 08:09:56Z

0

You need to quote your strings.

As written, your SQL statement is:

INSERT INTO foo VALUES(Hello there, world, I am, unquoted, string, not good)

You should use:

INSERT INTO foo VALUES("Hello there","world","I am","quoted","string","hooray")

answered Mar 8, 2012 at 8:09

Burhan Khalid

175k20 gold badges254 silver badges291 bronze badges

2 Comments

Icsilk Over a year ago

ahhh, so both the substrings from the text file string need quoting too? any advise on the best way to do that programmatically? there are literally thousands, can't do it by hand, lol

Burhan Khalid Over a year ago

Replace _entry_list = ', '.join(_entry_list) with _entry_list = '", "'.join(_entry_list), then deal with the edge case by doing _entry_list = '"'+_entry_list[:-1]+_entry_list[len(_entry_list)-1]+'"'

codeape · Accepted Answer · 2012-03-08 08:14:29Z

0

I suggest you do the following:

a) Instead of executing the statement, print it to the console. Change the line:

cur.execute("INSERT INTO " + self.new_tablename + ...)

to:

print "INSERT INTO " + self.new_tablename + ...

b) Run your program after making this change. Take a look at the SQL statements that you print to the console. Are they valid SQL statements? Start a SQLite command-line, and copy/paste the statements produced by your program. Does SQLite give any errors when you try to execute the pasted statements?

answered Mar 8, 2012 at 8:14

codeape

102k26 gold badges180 silver badges201 bronze badges

1 Comment

Icsilk Over a year ago

Yeah, I have done this. and just did it again. I do need to quote those substrings, Im just having trouble with it. Im not sure how I can split that list into substrings and quote them - well I can but that would be code that is specific to just this text file. I want to do it so it is abstracted enough to work with any text file regardless if it has 2 or 10 entries in the list.

Collectives™ on Stack Overflow

cant resolve cryptic error in Python/sqlite program

3 Answers 3

4 Comments

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related