Pandas and SQL; How to select specific row in database?

Question

I use Pandas to read this SQL using Python (works using SELECT * FROM table):

   ID                      Timestamp   
1  AA34234234234234234234  B534  
2  AA34234234234234234234  B864 
3  cv0Qc0PJFDAP5S4T6pn69Y  B435  
4  cv0Qc0PJFDAP5S4T6pn69Y  B978

All fields are "TEXT".

Now want to access the last row identical with ID = AA34234234234234234234 and enter this line:

status = pd.read_sql('SELECT * FROM table WHERE ID = AA34234234234234234234', database).tail(1)

I expect to get:

   ID                      Timestamp  
2  AA34234234234234234234  B864

But I get an error instead:

(sqlite3.OperationalError) unrecognized token: "AA34234234234234234234" [SQL: 'SELECT * FROM table WHERE ID = AA34234234234234234234'] (Background on this error at: https://sqlalche.me/e/14/e3q8)

I am sure I do a silly syntax mistake but I can't seem to narrow down the issue! The link is not helpful for me at least.

Suggestions?

nstvnsn · Accepted Answer · 2022-08-16 03:45:06Z

2

If ID column is of string type, you will need to wrap the ID in quotes:

status = pd.read_sql('SELECT * FROM table WHERE ID = "4234234234234234234234"', database).tail(1)

That is my suspicion of what is happening. I cannot be sure without knowing the table schema.

answered Aug 16, 2022 at 3:45

nstvnsn

1238 bronze badges

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

3 Comments

Dalalama231123 Over a year ago

Thanks. It worked. I used fstrings and placed '' before and end the curlies! f"SELECT * FROM table WHERE ID = '{order}'"

nstvnsn Over a year ago

While that does work, I would not recommend it unless using prepared statements. In raw SQL, if someone could change the value of order before it is inserted into that string, you could have your database messed with from SQL injection attacks, such as dropping tables or getting informaton that should be private. Definitely recommend reading about SQL injections and prepared sql statements. Also, glad I was able to help!

nstvnsn Over a year ago

It looks like Pandas read_sql is capable of doing so safely, but in general it is a risky practice. Double check the Pandas docs to ensure you don't expose your database to unnecessary risk. Not as important for small projects while learning, but do keep in mind as you progress :)

Collectives™ on Stack Overflow

Pandas and SQL; How to select specific row in database?

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related