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
– Shijith, Commented Jun 23, 2020 at 3:55
it works thank you so much!! then and other question...how can i skip the no such table while looping? — y2kmarkham
– y2kmarkham, Commented Jun 23, 2020 at 4: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
– NotAName, Commented Jun 23, 2020 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
– y2kmarkham, Commented Jun 24, 2020 at 2: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
– NotAName, Commented Jun 24, 2020 at 4:00

NotAName · Accepted Answer · 2020-06-23 04:08:56Z

1

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.

edited Jun 23, 2020 at 4:08

answered Jun 23, 2020 at 3:54

NotAName

4,4743 gold badges39 silver badges59 bronze badges

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

3 Comments

y2kmarkham Over a year ago

thank you so much, but i am a bit confused about the placeholder, am i right to putting 'select ... from ' + stock_id ?

NotAName Over a year ago

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 Over a year ago

@y2kmarkham, see added EDIT section in my answer.

Collectives™ on Stack Overflow

Adding SQL for loop in python

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