Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Python sqlite3 string variable in execute

Tags:

I try to execute this sqlite3 query in Python. I reduced the code to the minimum, sqlite.connect, etc works.

column = 'Pron_1_Pers_Sg' goal = 'gender'  constrain = 'Mann'   with con:     cur = con.cursor()      cur.execute("SELECT ? FROM Data where ?=?", (column, goal, constrain))     con.commit()      rows = cur.fetchall()      for element in rows:         values.append(element) 

This returns an empty list. If I hardcode the strings, it works and returns values.

like image 545
Steffen Avatar asked Dec 14 '12 14:12

Steffen


People also ask

How fetch data from sqlite3 in Python?

SQLite Python: Querying Data First, establish a connection to the SQLite database by creating a Connection object. Next, create a Cursor object using the cursor method of the Connection object. Then, execute a SELECT statement. After that, call the fetchall() method of the cursor object to fetch the data.

What is Fetchall in sqlite3?

You can fetch data from MYSQL using the fetch() method provided by the sqlite python module. The sqlite3. Cursor class provides three methods namely fetchall(), fetchmany() and, fetchone() where, The fetchall() method retrieves all the rows in the result set of a query and returns them as list of tuples.

What is pysqlite3?

This library takes the SQLite module from Python 3 and packages it as a separately-installable module. This may be useful for creating SQLite modules capable of working with other versions of SQLite (via the amalgamation option). Additional features: User-defined window functions (requires SQLite >= 3.25)


2 Answers

Parameter markers can be used only for expressions, i.e., values. You cannot use them for identifiers like table and column names.

Use this:

cur.execute("SELECT "+column+" FROM Data where "+goal+"=?", (constrain,)) 

or this:

cur.execute("SELECT %s FROM Data where %s=?" % (column, goal), (constrain,)) 

(And don't commit before you have actually finished accessing the data.)

like image 93
CL. Avatar answered Nov 10 '22 08:11

CL.


Try this: c.execute("SELECT {idf} FROM Data WHERE {goal}".\ format(idf=column, goal=constrain))

like image 27
Rajendra Avatar answered Nov 10 '22 08:11

Rajendra