A little background, I am building up a database of stock prices for analysis (if this is not the best method, let me know). I am using MySql and running python scripts though mysql.connector I have a table prices that looks a bit like this.
CREATE TABLE `Prices` (
`id` int PRIMARY KEY AUTO_INCREMENT,
`date_id` int,
`ticker_id` int,
`Open` decimal(6,2),
`Close` decimal(6,2),
`High` decimal(6,2),
`Low` decimal(6,2),
`Volume` int,
`Adj_Open` decimal(6,2),
`Adj_Close` decimal(6,2),
`Adj_High` decimal(6,2),
`Adj_Low` decimal(6,2),
`Adj_Volume` decimal(6,2)
);
So far I have only stored the prices for Apple in it and it has a little over 10000 entries. I tried to select/show the table from Mysql Workbench but kept getting Error Code: 2013. Lost connection to MySQL server during query. The same thing happens when I tried to truncate the table. I even increased the timeout from the 30s to 600 as suggested in one StackOverflow post, but I still get the same error.
When I run the following in python
def print_table(name):
mycursor = mydb.cursor()
text ="SELECT * FROM " + name
mycursor.execute(text)
myresult = mycursor.fetchall()
for i in myresult:
print(i)
mycursor.close()
#print table prices
print_table("prices")
It doesn't take 2 seconds and I have a printout out of the entire table content. Is there other settings that I have to change to allow me to run these queries in workbench without getting errors.
Second question:
I would like to have a unique key based on 2 columns in the table date_id and ticker_id how can I go about setting this up. So that the following code works without entering the same date and price twice when I update the table in the future.
sql = "INSERT IGNORE INTO prices (date_id,ticker_id,open,close,high,low,volume) VALUES (%s,%s,%s,%s,%s,%s,%s)"
mycursor.execute(sql,(date_id,ticker_id,p_open, p_close,p_high,p_low,p_volume))