cannot insert into database with python

Question

I get error using python 3.8.5

( cursor.execute("INSERT INTO dbo.sftpserverlist(FileName,FileSize) VALUES ("+files[0]+","+str(sizes[0])+")")

pyodbc.ProgrammingError: ('42000', '[42000] [Microsoft][ODBC SQL Server Driver][SQL Server]The multi-part identifier "DidosSupply.zip" could not be bound. (4104) (SQLExecDirectW)')

while I am trying call following function and insert into table dbo.sftpserverlist.

def getfile(sftp):
    FileNames=[]
    FileName = sftp.listdir_attr()   
    for i in FileName:
        FileNames.append(i.filename)

    FileSizes=[]
    FileSize = sftp.listdir_attr()
    for i in FileSize:
        FileSizes.append(i.st_size)

    return FileNames,FileSizes

-----------------------------------------------------------


cursor.execute("INSERT INTO dbo.sftpserverlist(FileName,FileSize) VALUES ("+files[0]+","+str(sizes[0])+")")
conn.commit()

Answer 1

I'm not an expert with Python, by any means, but I think this is the old-school way of doing it (INSERT INTO). I recently stumbled upon a super-easy, scalable, and controllable, way of pushing data from Python to SQL Server. Try the sample code and post back if you have additional questions.

import pyodbc
import pandas as pd

engine = "mssql+pyodbc://your_server_name/your_database_name?driver=SQL Server Native Client 11.0?trusted_connection=yes"

... dataframe here...

dataframe.to_sql(x, engine, if_exists='append', index=True)

dataframe is pretty self explanatory.

x = the name yo uwant your table to be in SQL Server.