how to substitute string in SQL connection in Python

Question

i am have a variable (exchanges) and I loop thru the values in my code to change the path for the output files for each exchange. When I also try to use this string substitution in the SQL block, it doesnt understand it. can someone suggest what is wrong? Here is the error I am getting

pandas.io.sql.DatabaseError: Execution failed on sql '
    f'SELECT [Ticker], [Date], [Open], [High], [Low], [Close], [Volume] FROM olaptraderv3.dbo.{ex} order by Ticker'
    ': ('42000', "[42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Could not find stored procedure 'f'. (2812) (SQLExecDirectW)")

Here is the relevant part of the code:

import pandas as pd

pd.options.mode.chained_assignment = None  # default='warn'
import pyodbc
import talib
import os
from talib import  (AD,ADOSC,WILLR)

exchanges = ["BATS","US","V"]

for ex in exchanges:
path = f'H:\\EOD_DATA_RECENT\\INDICATORS\\FROM-SQL-SOURCE\\{ex}\\'

DB_READ = {'servername': 'XYZ\XYZ',
  'database': 'olaptraderv3'}

conn = pyodbc.connect('DRIVER={SQL Server};SERVER=' + DB_READ['servername'] + ';DATABASE=' +  DB_READ['database'] + ';Trusted_Connection=yes')

sql = """
f'SELECT [Ticker], [Date], [Open], [High], [Low], [Close], [Volume] FROM olaptraderv3.dbo.{ex} order by Ticker'
"""
df = pd.read_sql(sql, conn)

im_baby · Accepted Answer · 2021-02-12 05:11:59Z

1

You need to have the format 'f' outside of the string.

sql = f"""
'SELECT [Ticker], [Date], [Open], [High], [Low], [Close], [Volume] FROM olaptraderv3.dbo.{ex} order by Ticker'
"""

I highly discourage doing string formatting/substitution with SQL as it is not sanitized and can be abused.

answered Feb 12, 2021 at 5:11

im_baby

9991 gold badge9 silver badges14 bronze badges

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

Collectives™ on Stack Overflow

how to substitute string in SQL connection in Python

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related