2

I am trying to do something like this in Python, 

SQLCommand = ("Delete From %s where [Date] >= %s and [Date] <= %s", (calendar_table_name, required_starting_date, required_ending_date))

cursor.execute(SQLCommand)

calendar_table_name is a string variable

required_starting_date is a datetime variable

required_ending_date is a datetime variable

Trying this gives me an error:

The first argument to execute must be a string or unicode query.

Tried this and it gives me the same error:

SQLCommand = ("Delete From " +  calendar_table_name + " where [Date] >= %s and [Date] <= %s", ( required_starting_date, required_ending_date))

cursor.execute(SQLCommand)

Edit:

type(required_ending_date)

Out[103]: pandas._libs.tslibs.timestamps.Timestamp


type(required_starting_date)

Out[103]: pandas._libs.tslibs.timestamps.Timestamp

This works in SSMS for me,

  delete from [table_test] where [Date] >= '2007-01-01' and [Date] <= '2021-01-01';

Update :- This is the code, that I am trying with 

Delete_SQLCommand =  f"Delete FROM [{calendar_table_name}] WHERE [Date]>=? And [Date]<=?"
params = (required_starting_date, required_ending_date)

required_starting_date & required_ending_date are of "TimeStamp" formats

calendar_tbl_connection = pyodbc.connect(driver=driver, server=required_server, database=database_name,
                     trusted_connection='yes')   
calendar_tbl_cursor = calendar_tbl_connection.cursor()
calendar_tbl_cursor.execute(Delete_SQLCommand,params)
calendar_tbl_connection.commit
calendar_tbl_connection.close()
Improve this question
13
  • 3
    You cannot parameterize table names. From %s where is not valid in this case. You need to use string formatting to make dynamic table names, then pass the parameters for the rest of the query Commented Aug 23, 2019 at 17:02
  • 1
    You haven't shown us how this sql command is used. Show us the call to execute(). Commented Aug 23, 2019 at 17:06
  • Right, so the edit suggests that you're passing a datetime object and not a string, and either the column is configured to take TEXT values (or similar) or your connection library won't do the conversion to ISO format Commented Aug 23, 2019 at 17:06
  • 1
    @tgikal That's how you get SQL injection attacks. Don't do it that way. Commented Aug 23, 2019 at 17:09
  • 1
    Make SQLCommand be just the string portion, i.e. SQLCommand = "Delete From ..." . Then call execute with the arguments: cursor.execute(SQLCommand, (arg1, arg2)) Commented Aug 23, 2019 at 17:13

3 Answers 3

Reset to default
1

pyodbc has no problem dealing with pandas' Timestamp values as inputs to a proper parameterized query:

# test data
calendar_table_name = "#calendar_table"
crsr.execute(f"CREATE TABLE [{calendar_table_name}] ([Date] date)")
crsr.execute(f"INSERT INTO [{calendar_table_name}] VALUES ('2019-08-22'),('2019-08-24')")
df = pd.DataFrame(
    [(datetime(2019, 8, 23, 0, 0), datetime(2019, 8, 25, 0, 0))],
    columns=['required_starting_date', 'required_ending_date'])
required_starting_date = df.iloc[0][0]
required_ending_date = df.iloc[0][1]
print(type(required_starting_date))  # <class 'pandas._libs.tslibs.timestamps.Timestamp'>

# test
sql = f"DELETE FROM [{calendar_table_name}] WHERE [Date]>=? AND [Date]<=?"
params = (required_starting_date, required_ending_date)
crsr.execute(sql, params)
cnxn.commit()

#verify
rows = crsr.execute(f"SELECT * FROM [{calendar_table_name}]").fetchall()
print(rows)  # [(datetime.date(2019, 8, 22), )]
Improve this answer
Sign up to request clarification or add additional context in comments.

8 Comments

The Select statement that you have in test works. But If I change my query to delete then it fails. Sorry couldn't get back to this sooner.
Fails how? Do you get an exception?
No, it just doesn't delete it. The Query runs but the data doesn't gets deleted.
Are you remembering to do a commit()?
If you are using SSMS to check the results of the delete then double-check that you are connecting to the same SQL Server instance that your Python app is working on.
|
1

You don't say which library you are using for SQL access, but here is a safe example using psycopg.

from psycopg2 import sql

cmd = sql.SQL("delete from {} where date >= %s and date <= %s")
table_name = sql.Identifier(calendar_table_name)
cur.execute(
    cmd.format(table_name),
    [required_starting_date, required_ending_date]
)

Note that this is not str.format being called, but SQL.format. The library ensures that calendar_table_name is a proper column name, and SQL.format ensures that it is correctly incorporated into your command template before in order to produce a valid parameterized query.

Failing proper library support, you would need to do some sort of dynamic query generation. It should be a restricted sort, though, the more restricted the better. The safest way would be to start with a lookup table of hard-coded queries:

queries = {
  'name1': 'delete from name1 where ... ',
  'name2': 'delete from name2 where ...',
}

This way, you can't construct a query for an arbitrary table name, only select a pre-constructed query.

The second would be to wrap the constructor in a function that checks for a valid table name first. For example,

def generate_query(table_name):
    if table_name not in ['name1', 'name2', ...]:
        raise ValueError("Invalid table name")

    return "delete from {} where ...".format(table_name)
Improve this answer

5 Comments

I am using pyodbc
@Siddarth: This might still work, python has a standardized database connector api. If pyodbc follows it, cursor.execute should work much the same as with pyscopg2 (postgres).
@HåkenLid The classes provided by psycopg2.sql are not part of the DBAPI.
Ok. So even if the cursor.execute method works fine, the equivalent of sql.Identifier and sql.SQL in your answer might be missing from pyodbc? It would be possible to just use string formatting, I suppose. But then you would have to be careful that the table name variable is safe. I found a relevant question, but the highest answer there looks like it could expose a sql injection vulnerability: Passing table name as a parameter in pyodbc.
@HåkenLid you can get a reference list of the schema of your database and check any variable input against that list ("does this 'table' actually exist?") before execution.
0

You have 3 (at least) different problems with this code:

  1. You are using pandas Timestamp types instead of the expected python datetime types. Roganosh answer explains that
  2. You are mixing sql identifiers (table name) with sql values (date). You can only pass values as parameters to cursor.execute. see chepner's answer.
  3. You are calling cursor.execute with incorrect arguments.

cursor.execute expects two arguments. Since your SQLCommand variable is a tuple, you can use * to unpack the query string and the variables into two arguments when calling cursor.execute.

SQLCommand = (
    "DELETE FROM table_name WHERE date >= %s", 
    (datetime.date(2019, 08, 23),) 
)

cursor.execute(*SQLCommand)

Note that you can't pass sql identfiers such as table names as parameters to the cursor.execute method. The Python Database API Specification does not specify how to construct queries with dynamic identifiers (for example column or table names).

Improve this answer

2 Comments

This only works for query parameters, not the table name. The DBAPI itself does not support full dynamic generation of SQL commands.
@chepner: You are right. There are two problems with the code in the question. My answer addresses why they get the error "The first argument to execute must be a string or unicode query." in this case. I've edited the answer to make it clearer that parameters (the second argument to cursor.execute must be values, not identifiers such as table names.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.