Adding SQL for loop in python

Question

i am a newbees for programming, i have an db file with some date, open, high, low , close data in it, and name with 0001.HK; 0002.HK; 0003.HK then i try to build a loop to take out some data in the database.

conn = sqlite3.connect(os.path.join('data', "hkprice.db"))


def read_price(stock_id):
    
    connect = 'select Date, Open, High, Low, Close, Volume from ' + stock_id
    df = pd.read_sql(connect, conn,index_col=['Date'], parse_dates=['Date'])

for y in range(1 ,2):
    read_price(str(y).zfill(4) + '.HK')

when it output it show: Execution failed on sql 'select Date, Open, High, Low, Close, Volume from 0001.HK': unrecognized token: "0001.HK"

but i should have the 0001.HK table in the database what should i do?

try adding a `'`around the value you pass, `read_price("'" + str(y).zfill(4) + ".HK'")` — Shijith, Jun 23 '20 at 03:55
it works thank you so much!! then and other question...how can i skip the no such table while looping? — y2kmarkham, Jun 23 '20 at 04:20
What kind of error are you getting? You can just simply add a `try-except` statement specifying error type and let it simply pass in case table doesn't exist. — NotAName, Jun 23 '20 at 22:47
@pavel hello sir! thanks for answering me again I saw someone use `except: pass`(its didn't work), but i find out that i shouldn't loop the file name with number range... in my sql db, some of the number are not exist, such as 0012.HK(exist); 0013.HK(not exist); 0014.HK(exist), so i think i shouldn't loop with the file name, but i am not sure about how to read the sql one by one — y2kmarkham, Jun 24 '20 at 02:20
@y2kmarkham, it's strange, because `except: pass` should work, although it's a very bad habit to use it like this. Instead do `except %ExceptionClass%:` where you replace %ExceptionClass% with the actual class of exception you are getting. Make sure `pass` is on the next line and properly indented. — NotAName, Jun 24 '20 at 04:00
it's said `Execution failed on sql 'select Date, Open, High, Low, Close, Volume from '0013.HK'': no such table: 0013.HK`. i think i should change the way to read the sql.. — y2kmarkham, Jun 24 '20 at 04:04

NotAName · Accepted Answer · 2020-06-23T04:08:56.847

If you want to use variables with a query, you need to put a placeholder ?. So in your particular case:

connect = 'select Date, Open, High, Low, Close, Volume from ?'

After that in read_sql you can provide a list of your variables to the params kwarg like so:

df = pd.read_sql(connect, conn, params=[stock_id], index_col=['Date'], parse_dates=['Date'])

If you have multiple parameters and, hence, multiple ? placeholders then when you supply the list of variables to params they need to be in exactly the same order as your ?.

EDIT: For example if I had a query where I wanted to get data between some dates, this is how I would do it:

start = ['list of dates']
end = ['another list of dates']

query = """select * 
           from table
           where start_date >= ? and
                 end_date < ?
        """

df = pd.read_sql_query(query, conn, params=[start, end])

Here interpreter will see the first ? and grab the first item from the first list, then when it gets to the second ? it will grab the first item from the second list. If there's a mismatch between the number of ? and the number of supplied params then it will throw an error.

thank you so much, but i am a bit confused about the placeholder, am i right to putting 'select ... from ' + stock_id ? — y2kmarkham, Jun 23 '20 at 04:02
No, you put `?` as a placeholder into your query. Once interpreter reaches the `?` it will look whether anything has been provided to `params` kwarg and if all matches up, it will automatically populate data, otherwise it will throw an error. — NotAName, Jun 23 '20 at 04:04

Adding SQL for loop in python

1 Answers1