Error when trying to create new database table in SQL Server 2016 from csv file while using python 3.5 with pandas and sqlalchemy

Question

Problem

I am trying to use python to read my csv file and put it into Microsoft SQL Server 2016 as a new table. Simply put, I don't want to create a table on SQL and import the csv, I want to write a script in python that can read the csv and create a new table in SQL for me.

UPDATE

I may have to rethink my approach. I corrected the driver, but I am getting the following error from to_sql. I am thinking that there is something wrong with my authentication scheme. Sadly, the to_sql documentation and sql_alchemy is not shedding much light. Starting to consider alternatives.

sqlalchemy.exc.DBAPIError was unhandled by user code
Message: (pyodbc.Error) ('08001', '[08001] [Microsoft][SQL Server Native Client 11.0]Named Pipes Provider: Could not open a connection to SQL Server [53].  (53) (SQLDriverConnect)')

Code

import pandas as pd 
import sqlalchemy

#Read the file 
data = pd.read_csv(file.csv)

#Connect to database and write the table 
 server = "DERPSERVER"
 database = "HERPDB"
 username = "DBUser" 
 password = "password"
 tablename = "HerpDerpTable"
 driver = "SQL+Server+Native+Client+11.0" 

 #Connect to SQL using SQL Server Driver 
 print("Connect to SQL Server")
 cnxn = sqlalchemy.create_engine("mssql+pyodbc://"+username+":"+password+"@"+server +"/"+database + "?driver="+driver)

UPDATE

I rewrote the string as follows, but it doesn't work:

sqlalchemy.create_engine('mssql+pymssql://'+username+':'+ password + '@' + server + '/' + database + '/?charset=utf8')


data.to_sql(tablename, cnxn);

Attempts

These are some important things to note in my approach. Pay special attention to the second bullet point I share below. I think my connection string for create_engine is somehow or maybe wrong, but don't know what is wrong because I followed the documentation.

• I believe I am in a DSN-less situation. Thus, was attempting to connect by other means as described by the documentation.
• I was using this link to help me create the connection string part in create_engine.
• I tried to_sql to write the to the database, but think my connection string might still be messed up? I consulted this question on stackoverflow.
• Update I added the driver specification as MaxU and the documentation for sqlalchemy specified. However, I am getting an error saying my data source name was not found and no default driver is specified with to_sql. Do I need to feed to_sql the driver as well? If so, where is the documentation or a sample code that shows me where I am going wrong?

I am making good effort to pick up python and to use it as a scripting language because of future goals and needs. I would appreciate any assistance, help, mentorship rendered.

Answer 1

You should explicitly specify the MS SQL Server driver if you use SQLAlchemy version 1.0.0+:

Example:

engine = create_engine("mssql+pyodbc://scott:tiger@myhost:port/databasename?driver=SQL+Server+Native+Client+10.0")

Changed in version 1.0.0: Hostname-based PyODBC connections now require the SQL Server driver name specified explicitly. SQLAlchemy cannot choose an optimal default here as it varies based on platform and installed drivers.

List of Native SQL Server Client Names

• SQL Server 2005:

SQL Server Native Client

• SQL Server 2008:

SQL Server Native Client 10.0

• SQL Server 2016:

SQL Server Native Client 11.0

---

Alternatively you can use pymssql:

engine = create_engine('mssql+pymssql://<username>:<password>@<freetds_name>/?charset=utf8')