KeyError while trying to connect to database using pymssql

Question

The below code tries to connect to a mssql database using pymssql. I have a CSV file and I am trying to push all the rows into a single data table in the mssql database. I am getting a 'KeyError' when I try to execute the code after opening the CSV file.

import csv
import pymssql
conn = pymssql.connect(host="host name", 
                       database="dbname", 
                       user = "username",
                       password = "password")

cursor = conn.cursor()
if(conn):
    print("True")
else:
    print("False")

with open ('path to csv file', 'r') as f:
    reader = csv.reader(f)
    columns = next(reader) 
    query = "INSERT INTO Marketing({'URL', 'Domain_name', 'Downloadables', 'Text_without_javascript', 'Downloadable_Link'}) VALUES ({%s,%s,%s,%s,%s})"
    query = query.format(','.join('[' + x + ']' for x in columns), ','.join('?' * len(columns)))
    cursor = conn.cursor()
    for data in reader:
        cursor.execute(query, tuple(data))
    cursor.commit()

The below is the error that I get:

KeyError: "'URL', 'Domain_name', 'Downloadables', 'Text_without_javascript', 'Downloadable_Link'"

Using to_sql

file_path = "path to csv"
engine = create_engine("mssql://user:password@host/database") 

df = pd.read_csv(file_path, encoding  = 'latin')
df.to_sql(name='Marketing',con=engine,if_exists='append')

Output:

InterfaceError: (pyodbc.InterfaceError) ('IM002', '[IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified (0) (SQLDriverConnect)')

is the error in the python error stack? can you update with more of the error that Python threw? Thanks. — arcee123
– arcee123, Commented Oct 21, 2019 at 11:55
my initial thought is you are trying to throw a tuple. Try a list. — arcee123
– arcee123, Commented Oct 21, 2019 at 11:57
plus we need to work on your data in reader for loop. where are you assigning the values to query. I'm not seeing it. — arcee123
– arcee123, Commented Oct 21, 2019 at 11:59
The query seems a bit unecessarily complex? Try move the query line into the for loop, with query = f"INSERT INTO TABLE ('URL', 'Domain_name', 'Downloadables', 'Text_without_javascript', 'Downloadable_Link') VALUES ({data[0]});" Also if the csv isn't big using pd.to_sql is much easier. — Merelda
– Merelda, Commented Oct 21, 2019 at 12:08
For pyodbc, you will need the driver param as described by the wiki github.com/mkleehammer/pyodbc/wiki/…. Did the query string above help? — Merelda
– Merelda, Commented Oct 21, 2019 at 12:48

developer · Accepted Answer · 2019-10-22 05:03:10Z

I tried everything, from converting the parameters which were being passed to a tuple, passing it as is, but didn't help. Below is the code that helped me fix the issue:

with open ('path to csv file', 'r') as f:
    for row in f:
        reader = csv.reader(f)
#        print(reader)
        columns = next(reader) 
#        print(columns)
        cursor = conn.cursor()
        for data in reader:
#            print(data)
            data = tuple(data)
#            print(data)
            query = ("INSERT INTO Marketing(URL, Domain_name, Downloadables, Text_without_javascript, Downloadable_Link) VALUES (%s,%s,%s,%s,%s)")
            parameters = data
#            query = query.format(','.join('?' * len(columns)))
            cursor.execute(query, parameters)
        conn.commit()

Note: The connecting to the database part remains as in the question.

Collectives™ on Stack Overflow

KeyError while trying to connect to database using pymssql

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related