Data is not inserted MSSQL Python

I am doing a python script to add text files into an MSSQL table. Using pyodbc, I’m doing it. The data is not inserted in the result. There is no error showing when running.

Here is my code,

import pyodbc
import os
from pathlib import Path
cnxn = pyodbc.connect('DRIVER={SQL Server};SERVER=LAPTOP-LMBL29RS;DATABASE=mydb;UID=sa;PWD=123456')
cursor = cnxn.cursor()
path = 'testfiles'
files = os.listdir(path)
for f in files:
    filepath = path +'/'+ f
    filename = os.path.basename(path+'/'+filepath)
    with open(filepath) as fp:
        lines = fp.read().splitlines()
    with open(filepath, "r") as fp:
        count = 0;
        for line in lines:
            dataarr=[]
            if (count>0):
                dataarr = line.split(',')
                if(len(dataarr)==8):
                    print(dataarr[3])
                    query = ('INSERT INTO final_tbl("Date","Open","High","Low","Close","Volume","OpenInt","filename") '
                         'VALUES(?,?,?,?,?,?,?,?)')
                    cursor.execute(query, dataarr[0],dataarr[1],dataarr[2], dataarr[3], dataarr[4], dataarr[5],dataarr[6],dataarr[7])
            count = count+1
            #print(dataarr)

Answer

You’re missing a call to cnxn.commit() after the loops are done.

Advertisement

Answer