0

i have a issue while import yfinance data to mysql database. Here's the codes:

ystock_list = ['ZSL','ZROZ']
df = yf.download(ystock_list, group_by='Ticker',start='2021-12-01',end='2021-12-31',threads=True)
df = df.stack(level=0).rename_axis(['Date', 'Ticker']).reset_index(level=1).sort_values(['Ticker','Date'])

result = df.to_string(header=None, index_names=None) # Data: (Date,Ticker,Adj_close,Close,High,Low,Open,Volume)

list_raw = result.split()
splitter = [list_raw[i:i + 8] for i in range(0, len(list_raw), 8)] # split into 8 data into 1 list inside list
convert = list(tuple(x) for x in splitter) # convert the list inside list to tuple
print(type(convert[0][0])) # yfinance data type for 1st data which is date is str (2022-01-04)

code = "test"
z=0
sql_add = "insert into `{table}` (Date) values ({a});".format(table=code,a=str(convert[z][0]))
cursor.execute(sql_add)
conn.commit()

| test  | CREATE TABLE `test` (
  `Date` date NOT NULL,
  `High` decimal(7,2) DEFAULT NULL,
  `Low` decimal(7,2) DEFAULT NULL,
  `Open` decimal(7,2) DEFAULT NULL,
  `Close` decimal(7,2) DEFAULT NULL,
  `Volume` bigint DEFAULT NULL,
  `Adj_Close` decimal(7,2) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 |

i try to change the mysql Date into Varchar(30) and import the yfinance data into mysql but i get the 2017 instead of 2022-01-04 which is shown in python with str type, and i try change to Date to Date(type) in mysql and the error shows pymysql.err.OperationalError: (1292, "Incorrect date value: '2017' for column 'Date' at row 1") while execute above code. Please help.

Brian Tung
  • 29
  • 5
  • --Don't use string formatting or concatenation to inject _values_ into a query statement (you can use them for column and table names, as long as they are not from untrusted input. See the answers to the linked Q&A – snakecharmerb Jan 12 '22 at 14:01
  • thanks for the remind regarding the query statement, but my issue still remain as the date format from yfinance seems different, when i try inject values into query statement, the sql output is 2017 instead 2022-01-04.. – Brian Tung Jan 12 '22 at 14:54
  • Your aren't quoting the values properly _because_ you are using string formatting. [This answer](https://stackoverflow.com/a/68153087/5320906) explains why your date ends up as 2017. – snakecharmerb Jan 12 '22 at 15:42

0 Answers0