How to create sqlite3 table columns from a list? (in python)

Question

I'm trying to build a table columns according to a user request (the size of the list may change, that's why I can't determine an exact number of question marks):

userInput=[a,b,c,d,e,f,g]
ct.execute('CREATE TABLE IF NOT EXISTS chars(?)',(userInput))

I get the following error:

sqlite3.OperationalError: near "?": syntax error

what SQL query do you expect to get ? where do you have column names and columns types. I'm not sure but ? expects only one element but you have many elements. — furas
– furas, Commented Oct 18, 2016 at 22:50
I don't query yet, tried to add types and it didn't help. The size of the list may change, that's why I can't determine an exact number of question marks. — Yotam Hochman
– Yotam Hochman, Commented Oct 18, 2016 at 22:52
maybe first generate one string with column names and column types and then use it as single argument in query. — furas
– furas, Commented Oct 18, 2016 at 22:55
Is there any smart way to do so besides creating a loop that concatenates the question marks string (?,?,?,?,?...) with the argument parts altogether? — Yotam Hochman
– Yotam Hochman, Commented Oct 18, 2016 at 23:02
You cannot pass structural components like column names as parameters only data values usually in WHERE, ON, VALUES(), and IN() clauses. You will need to concatenate list values to SQL string. — Parfait
– Parfait, Commented Oct 19, 2016 at 0:05

fralau · Accepted Answer · 2016-10-21 17:01:37Z

2

To answer your question, we will ignore security issues. I assume you want to create (untyped) columns according to your python list.

CREATE TABLE chars (a,b,c,d,e,f,g)

Here is my proposal:

userInput=['a','b','c','d','e','f','g']
q = "CREATE TABLE IF NOT EXISTS chars(%s)" % ", ".join(userInput)

(Note that the column names must be quoted, per the Python syntax of string literals).

It may be argued that %s as a syntax is "de-emphasized", but in my humble opinion it is nicely terse in that case.

edited Oct 21, 2016 at 17:01

answered Oct 21, 2016 at 16:20

fralau

3,9693 gold badges31 silver badges47 bronze badges

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

Collectives™ on Stack Overflow

How to create sqlite3 table columns from a list? (in python)

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related