sqiserver2016与python,尝试在将python 3.5与pandas和sqlalchemy结合使用时尝试从csv文件在SQL Server 2016中创建新的数据库表时出错...-CSDN博客

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: