pyodbc in Python to run an existing access query that asks for user input

Question

I have an existing access query for example called *RunExampleQuery:

select name from table_x where date = [start];

But I can't seem to find the sql code that will run this query i.e

sql = """SELECT * FROM *RunExampleQuery WHERE [start] = ?"""

params = (datetime.date(2016,11,25))

cursor.execute(sql,params)

Thanks for your help in advance.

Parfait · Accepted Answer · 2016-11-29 22:44:24Z

1

Couple of items are the issue:

[start] field must exist in RunExampleQuery in order to use it in WHERE clause
To bind parameters to prepared SQL statements, you must pass values in tuple or list. This requires converting scalar strings to these type:

RunExampleQuery

select name, [start] from table_x;

Tuple Parameterization

sql = """SELECT * FROM [RunExampleQuery] WHERE [start] = ?"""
params = datetime.date(2016,11,25)
cursor.execute(sql, (params,))

List Parameterization

sql = """SELECT * FROM [RunExampleQuery] WHERE [start] = ?"""
params = datetime.date(2016,11,25)
cursor.execute(sql, [params])

answered Nov 29, 2016 at 22:44

Parfait

108k19 gold badges102 silver badges138 bronze badges

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

3 Comments

pjunited Over a year ago

So [start] comes from a column in table_x called date. It's asking for user input in the MS Access Query to enter a date. So when I'm calling it in from the sql portion would it be sql = """SELECT * FROM [RunExampleQuery] WHERE date.[start] = ?"""

Parfait Over a year ago

You cannot run a user prompt query like in MSAccess.exe. Have user enter through Python using input() and pass that as query parameter.

pjunited Over a year ago

Thanks Parfiat. I didn't want to do that because the query is used by different groups whom will not be using my python script.

Collectives™ on Stack Overflow

pyodbc in Python to run an existing access query that asks for user input

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