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.
解决方案
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.
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://:@/?charset=utf8')