How to update SQL server table for every 1 minute using python

Question

I have a SQL Server database table which contains stock market OHLC data loading from CSV file. I am live data into CSV so every specific interval, one row gets added in CSV file, same I need to add in database table. This database needs to be updated at regular interval, say 1 minute.

def printit():
    threading.Timer(60, printit).start()
    data_1_min = pd.DataFrame(mt.copy_rates_range("EURUSD.", mt.TIMEFRAME_M1, datetime.datetime(2022, 1, 1), dt.now()))
    data_1_min.drop(['tick_volume', 'spread'], axis = 1, inplace = True)
    data_1_min['time'] = [dt.fromtimestamp(x).strftime("%m-%d-%Y %H:%M:%S") for x in data_1_min['time']]
    data_1_min['TF'] = '1M'
    data_1_min.rename(columns = {'time':'Time','open':'O', 'high':'H','low':'L', 'close':'C', 'real_volume':'V'}, inplace = True)
    data_file = data_1_min.to_csv('data1.csv', index = False)
    with open ('data1.csv', 'r') as f:
        reader = csv.reader(f)
        columns = next(reader) 
        query = 'insert into mytable({0}) values ({1})'
        query = query.format(','.join(columns), ','.join('?' * len(columns)))
        cursor = cnxn.cursor()
        for data in reader:
            cursor.execute(query, data)
            cursor.commit()
        
printit()

Whenever I refresh database table, it loads all rows from CSV file, not only new rows. Tried with if not exists and merge but it taking long time. Want solution to save these live data to database. Using Microsoft SQL server management Studio 18. Doing all code in Python.

For `INSERT ... SELECT ... WHERE NOT EXISTS ...` you will need a supporting index for it to perform well. Do you have one? — Charlieface, Mar 07 '22 at 11:45
No. I am new to work with SQL server even so unable to get that flow how to work on it... — varsha patil, Mar 08 '22 at 03:10
You will have to show your full table schema, along with current indexes, and what exactly you are trying to achieve in terms of merging data — Charlieface, Mar 08 '22 at 03:34
Image added in question. All fields are of nvarchar(50) data type in SQL server. — varsha patil, Mar 08 '22 at 03:46
Please do not post images of code or data, only use images for something that cannot be shown as text such as GUI. Instead paste your data in as text (preferably `CREATE TABLE` and `INSERT` statements). Either way this doesn't help us: we need the full schema *and indexes*, and we need to know how you want to "merge the data", what criteria to use to match old and new rows — Charlieface, Mar 08 '22 at 03:54
I want to merge data according to time, every minute will get 1 new frame so I need to add that in existing table. — varsha patil, Mar 08 '22 at 07:13
You say *"Tried with `if not exists` and `merge` but it taking long time."* but it's still not clear how you do that merge. How about you give us that code for `not exists` and `merge` — Charlieface, Mar 08 '22 at 11:56
Tried with simple scenario just to calculate time of execution so unable to share that code. Not tried with these, small size takes much time than expected so — varsha patil, Mar 09 '22 at 06:15
That does not have `IF NOT EXISTS` or `MERGE`, it's just a straight `INSERT`. Youi still have not explained how you propose to match these rows, which columns are you using to decide if a row already exists? — Charlieface, Mar 09 '22 at 14:35
Want to do that according to time. Said in previous comments — varsha patil, Mar 09 '22 at 14:41
So why not just `insert into mytable (Time, O, H, L, C, V, TF) select ?, ?, ?, ?, ?, ?, ? where not exists (select 1 from mytable where Time = ?);` and pass `Time` again as another parameter — Charlieface, Mar 09 '22 at 14:44

How to update SQL server table for every 1 minute using python

0 Answers0