retrieving data from table based on a value from a python variable

Question

I am writing a function that will retrieve data from sqlite table based on the parameters user provide. This is the function so far

def database_retrieve(db_file, id):
    try:
        conn = sqlite3.connect(db_file)

        with conn:
            sql_command = "SELECT * FROM my_table WHERE id = "+id

            cur = conn.cursor()
            cur.execute(sql_command)
            result = cur.fetchall()

            return result

    except Exception as e:
        print(e)

db_file = 'testdb.db'
print(database_retrieve(db_file, 'subject1'))

This gives me the following error

no such column: subject1
None

When I add subject1, which is an entry under the id column in my_table, directly to the sql command like this

sql_command = "SELECT * FROM my_table WHERE id = 'subject1'"

it works fine and prints all the data.

I am new to sqlite3. Please help. Thanks in advance

These are the links I used to come this far Python sqlite3 string variable in execute

https://www.dummies.com/programming/databases/how-to-retrieve-data-from-specific-rows-in-mysql-databases/

Shiva · Accepted Answer · 2018-09-19 05:05:42Z

0

When you do this

sql_command = "SELECT * FROM my_table WHERE id = "+id

The value of sql_command is

"SELECT * FROM my_table WHERE id = subject1"

As you can see, subject1 is not in quotes. sqlite thinks it is a column, that's why you see that error.

Instead, do this

sql_command = "SELECT * FROM my_table WHERE id = ?"
cur.execute(sql_command, [id])

? acts as a placeholder for the variable id.

The official sqlite3 documentation mentions few others methods https://docs.python.org/2/library/sqlite3.html

answered Sep 19, 2018 at 5:05

Shiva

2,87826 silver badges40 bronze badges

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

Comments

Vishnudev Krishnadas · Accepted Answer · 2018-09-19 05:04:20Z

0

The sql_command string being generated should be something like this (Formatted string):

sql_command = "SELECT * FROM my_table WHERE id = %s AND name = %s" % (212212, 'shashank')

answered Sep 19, 2018 at 5:04

Vishnudev Krishnadas

11k2 gold badges29 silver badges58 bronze badges

1 Comment

Vishnudev Krishnadas Over a year ago

could you post the schema for my_table

Collectives™ on Stack Overflow

retrieving data from table based on a value from a python variable

2 Answers 2

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related