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?
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.