CODEWITHSUNDEEP
python-3.xsqlite
puppet
puppet

Reputation: 727

Insert in column names with blank space in sqlite3

I'm trying to fill a sqlite3 database with Python3 with data stored in a dict. The problem in my code seems to be in this snippet.

matchlist=['id','handicap','goal line','corner line']
heads='"'+'","'.join([a for a in list(match.keys()) if a in matchlist])+'"'
llaves=':'+',:'.join([a for a in list(match.keys()) if a in matchlist])

cur.execute('''INSERT or IGNORE INTO preodds ({}) VALUES ({});'''.format(heads,llaves),
            match)

This gives me an operational error next to line.

Apparently, when you try to insert in columns which name has blank spaces youneed to"escape" them. To do so, I modified as well my llaves string, doing the following:

llaves='":'+'",":'.join([a for a in list(match.keys()) if a in matchlist])+'"'

Doing so fix my issue, but instead of filling the table with the value provided my the dict, it is filled with the literal value in llaves string.

Why is this? and more importantly, if i have a table with columns with blank spaces, how do you fill rows using dict data?

Upvotes: 1

Views: 1750

Answers (1)

C Perkins
C Perkins

Reputation: 3886

Your original code is likely producing this

INSERT or IGNORE INTO preodds ("id","handicap","goal line","corner line")
 VALUES (:id,:handicap,:goal line,:corner line)

Notice the spaces in the parameters names... the names starting with colon (:). That's the problem.

Your attempted solution is just submitting string literals as values, so that is exactly what is inserted. 

INSERT or IGNORE INTO preodds ("id","handicap","goal line","corner line")
 VALUES (":id",":handicap",":goal line",":corner line")

Quotes can be used to delimit object names, but in other contexts quotes are interpreted as literal string value delimiters. Precise rules for determining how it interprets quoted values are found here.

As far as I can tell, parameters cannot be escaped and so cannot contain spaces or other special characters, at least nothing that is documented. See sqlite docs.

If you are going to build the parameter list dynamically, you should strip out all spaces from the parameter names. Or alternatively, you could just use unnamed parameters using the ? character. Either way the parameters are assigned values in the order they appear, so there would be no difference.

Something like:

INSERT or IGNORE INTO preodds ("id","handicap","goal line","corner line")
 VALUES (:id,:handicap,:goalline,:cornerline)

or 

INSERT or IGNORE INTO preodds ("id","handicap","goal line","corner line")
 VALUES (?, ?, ?, ?)

Upvotes: 1

Related Questions