Use variable in pd.read.sql [duplicate]

Question

I have data in SQL and I want to read some data out from it with variable instead of using number like "953381". Having no idea why I just can't use var = 9553381, directly.

df = pd.read_sql('select name, data, from sheet where name = "953381" ', conn,
                index_col=['date'], parse_dates=['date'])

Should be not, since I can get correct data if I using numbers, 953381. — 林佳聖
– 林佳聖, Commented Jun 13, 2018 at 13:19

koPytok · Accepted Answer · 2018-06-13 13:46:41Z

0

SQL doesn't understand double quotes. Use single quotes:

x = "953381"
query = "SELECT name, data FROM sheet WHERE name = '%s'" % x # x inside single quotes
df = pd.read_sql(query, conn, index_col=['date'], parse_dates=['date'])

edited Jun 13, 2018 at 13:46

answered Jun 13, 2018 at 13:40

koPytok

3,7731 gold badge16 silver badges29 bronze badges

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

2 Comments

林佳聖 Over a year ago

Thank you for the help!!! It works !!!!

Martijn Pieters Over a year ago

Do not use string formatting to put SQL values into a query! This is never a good idea, it opens up the code for SQL injection attacks, and robs the database of an opportunity to reuse optimised queries. Use ? or %s placeholders (depends on the database driver) and pass in the value to read_sql() via params=(x,).

PhilB · Accepted Answer · 2018-06-13 12:51:48Z

0

I guess that is what you are looking for:

var =  "953381" 
df = pd.read_sql('select name, data, from sheet where name =' + var, conn,
                index_col=['date'], parse_dates=['date'])

answered Jun 13, 2018 at 12:51

PhilB

994 bronze badges

Comments

Louis · Accepted Answer · 2018-06-13 12:53:52Z

-1

Is this what you are looking for ?

var = "953381"
df = pd.read_sql('select name, data, from sheet where name =' + var , conn,
            index_col=['date'], parse_dates=['date'])

answered Jun 13, 2018 at 12:53

Louis

971 silver badge8 bronze badges

2 Comments

林佳聖 Over a year ago

Thank you for the suggestion. but it still can't find the data.

Martijn Pieters Over a year ago

Do not use string formatting to put SQL values into a query! This is never a good idea, it opens up the code for SQL injection attacks, and robs the database of an opportunity to reuse optimised queries. Use ? or %s placeholders (depends on the database driver) and pass in the value to read_sql() via params=(x,).

Evgeny · Accepted Answer · 2018-06-13 13:08:04Z

-1

Is this a case for .format()?

x = "953381"
sql_str = 'select name, data, from sheet where name = "{}"'.format(x)

answered Jun 13, 2018 at 13:08

Evgeny

4,5814 gold badges22 silver badges42 bronze badges

2 Comments

林佳聖 Over a year ago

I tried, not this case, but thank you the suggestion!

Martijn Pieters Over a year ago

Do not use string formatting to put SQL values into a query! This is never a good idea, it opens up the code for SQL injection attacks, and robs the database of an opportunity to reuse optimised queries. Use ? or %s placeholders (depends on the database driver) and pass in the value to read_sql() via params=(x,).

Collectives™ on Stack Overflow

Use variable in pd.read.sql [duplicate]

4 Answers 4

2 Comments

Comments

2 Comments

2 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

Comments

2 Comments

2 Comments

Linked

Related