0

I have a .csv file containing data of all stocks. I wanted to create a table for each stock with the table name as the stock's symbol, in a MySQL database daily_stock_recorder.

There are about 1900 items and obviously it wouldnt be feasible to write them one by one, so I created this python program, which takes the stock_symbol of each row and makes a table for it. Here's the code:

import mysql.connector as mc
import pandas as pd

mycon=mc.connect(host='localhost',user='root',passwd='1234',database='daily_stock_recorder')
cursor=mycon.cursor()

stock_df = pd.read_csv(r'C:\Users\Tirth\Desktop\pyprojects\all_stocks.csv')

for i in range(0, len(stock_df)):
    stock=stock_df['SYMBOL'][i]
    for j in range(0,len(stock)):
        if ord(stock[j])==38:
            stock_ticker=stock.replace(stock[j],'')
        else:
            stock_ticker=stock.replace(stock[j],stock[j])
    
    todo="create table %s (close_price decimal(10,8) not null, traded_value bigint not null, 
traded_quantity bigint not null, date_recorded date not null unique)"%(stock_ticker)
    cursor.execute(todo)
    mycon.commit()

As you can see I have used a 'if' statement to replace the character '&' (ASCII Value = 38) in the symbol with '' (nothing). This is because every time I run the code, it is able to create tables successfully but then gives the error when it comes to the symbol 'COX&KINGS'. I am assuming that Mysql doesnt take table names with special characters.

But even after executing with the 'if' statement for replacing the special character, I get the same name without replacement of '&' and it lands on the same error.

Can You please point out what I am doing wrong?

By the way, this is the error:

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '&KINGS (close_price decimal(10,8) not null)' at line 1

Thanks!

2 Answers2

3

If you take a small segment of your code out and run it, you'll see it doesn't do what you're expecting:

stock='A&B'
for j in range(0,len(stock)):
    if ord(stock[j])==38:
        stock_ticker=stock.replace(stock[j],'')
    else:
        stock_ticker=stock.replace(stock[j],stock[j])
    print("stock_ticker is now " + stock_ticker)

This will output:

stock_ticker is now A&B
stock_ticker is now AB
stock_ticker is now A&B

As you're using the source value with each letter in itself, so it will only have the desired impact if the ticker ends with '&`

It would be much better to replace this with:

stock='A&B'
stock_ticker=stock.replace('&','')
print(stock_ticker)

That said, I would highly recommend looking into database normalization techniques. Having lots of tables named after stock tickers is bound to cause lots of problems. What if you want to track a stock that also happens to be a reserved keyword in SQL? What happens if there's a ticker A&B and AB for different stocks. It's much better to store this sort of data in one table.

Anon Coward
  • 9,784
  • 3
  • 26
  • 37
  • Thanks for your answer! And as you said about different stocks which might end up having same name after replacing '&', so then how do I create a Table with Table name having a special character? –  Dec 06 '20 at 07:05
  • 2
    [This answers](https://stackoverflow.com/questions/925696/mysql-create-database-with-special-characters-in-the-name) covers the details, but the short version is "don't". Find some other way to store the data, you're going down a path that will cause problems eventually. – Anon Coward Dec 06 '20 at 07:35
0

The whole inner for j ... loop is unnecessary.

Replace it by

stock_ticker=stock.replace("&", "")

In the inner "j" - loop for each character stock_ticker is overwritten. If the last character isn't a & then it is overwritten by the unmodified content of stock by the

stock_ticker=stock.replace(stock[j],stock[j])
Michael Butscher
  • 10,028
  • 4
  • 24
  • 25