Import CSV to SQL Express using Python

Question

I'm trying to take a csv file and import it into sql server express using Python. I've googled the problem and the solution I'm seeing that seems to be working for everyone else is:

import pymssql
import csv
conn = pymssql.connect(
    server="servername",
    user='username',
    password='password',
    database='db'
)
cursor = conn.cursor()
with open('new_csv_file.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 = conn.cursor()
    for data in reader:
        values = map((lambda x: x.strip()), data)  # No need for the quote
        print(tuple(values))
        cursor.execute(query, tuple(values))
    cursor.commit()
conn.commit()
cursor.close()
print("Done")
conn.close()

I've confirmed the code works it's just the "execute()" part doesn't. query is ok, but "values" is giving me the following error:

(102, b"Incorrect syntax near 'Year'.DB-Lib error message 20018, severity 15:\nGeneral SQL Server error: Check messages from the SQL Server\n")

Where 'Year' is the column I'm trying to fit the data in.

Thanks.

can you print `query` before execute it? – Yang HG Apr 30 '19 at 03:36 — Yang HG, Apr 30 '19 at 03:36

score 0 · Answer 1 · answered Apr 30 '19 at 04:23

0

OK I found a work-around using sqlalchemy and pandas

import sqlalchemy

print('Creating Engine')
engine = sqlalchemy.create_engine('mssql+pymssql://username:password@localhost/db')
print('Reading Table')
df = pd.read_sql_table("table_name",engine)
print(df)
df_new = pd.read_csv('table_name_new.csv'%)
df_new.to_sql(name="table_name",
          con = engine,
          if_exists='replace'         
        )
df_test = pd.read_sql_table("table_name",engine)

If someone did find out why my code doesn't work I'm still interested. I don't know if it's a Python 3 issue (I'm using Python 3).

Cheers

answered Apr 30 '19 at 04:23

Brad123

877
10
10

There's a function [YEAR()](https://learn.microsoft.com/en-us/sql/t-sql/functions/year-transact-sql?view=sql-server-2017) in T-SQL. When you pass 'Year' as a literal in your query string, the server probably interprets that as an attempted function invokation. It should work if you [escape the column names](https://stackoverflow.com/questions/285775/how-to-deal-with-sql-column-names-that-look-like-sql-keywords) in the query string: `",".join(map(lambda x: "[{}]".format(x), columns))`. It's just a hunch, though; I can't test this myself. – shmee Apr 30 '19 at 07:10

Import CSV to SQL Express using Python

1 Answers1