尝试在 SQL Server 2016 中从 csv 文件创建新数据库表时出错，同时将 python 3.5 与 pandas 和 sqlalchemy 一起使用

2021-12-30 00:00:00 python-3.x pandas sqlalchemy csv sql-server

问题

我正在尝试使用 python 读取我的 csv 文件并将其作为新表放入 Microsoft SQL Server 2016.简单地说，我不想在SQL上创建表并导入csv，我想用python编写一个脚本，可以读取csv并为我在SQL中创建一个新表.

更新

我可能不得不重新考虑我的方法.我更正了驱动程序，但从 to_sql 收到以下错误.我认为我的身份验证方案有问题.遗憾的是，to_sql 文档和 sql_alchemy 并没有透露太多信息.开始考虑替代方案.

sqlalchemy.exc.DBAPIError 未被用户代码处理消息: (pyodbc.Error) ('08001', '[08001] [Microsoft][SQL Server Native Client 11.0]Named Pipes Provider: 无法打开到 SQL Server [53] 的连接.(53) (SQLDriverConnect)')

代码

将pandas导入为pd导入 sqlalchemy#读取文件数据 = pd.read_csv(file.csv)#连接数据库并写表服务器 = "DERPSERVER"数据库 = "HERPDB"用户名 = "DBUser"密码 = "密码"表名 = "HerpDerpTable"driver = "SQL+Server+Native+Client+11.0"#使用SQL Server驱动连接SQL打印(连接到 SQL Server")cnxn = sqlalchemy.create_engine("mssql+pyodbc://"+username+":"+password+"@"+server +"/"+database + "?driver="+driver)

更新

我将字符串改写如下，但不起作用:

sqlalchemy.create_engine('mssql+pymssql://'+用户名+':'+密码+'@'+服务器+'/'+数据库+'/?charset=utf8')data.to_sql(表名，cnxn)；

尝试

这些是我的方法中需要注意的一些重要事项.请特别注意我在下面分享的第二个要点.我认为我的 create_engine 连接字符串在某种程度上或可能是错误的，但不知道出了什么问题，因为我遵循了文档.

  • 我相信我处于没有 DSN 的情况.因此，试图通过文档中描述的其他方式进行连接.
  • 我正在使用这个链接 帮助我在 create_engine 中创建连接字符串部分.
  • 我尝试了 to_sql 将数据写入数据库，但认为我的连接字符串可能仍然混乱?我咨询了这个问题 在 stackoverflow 上.
  • 更新 我将驱动程序规范添加为 MaxU，并指定了 sqlalchemy 的文档.但是，我收到一条错误消息，说找不到我的数据源名称，并且没有使用 to_sql 指定默认驱动程序.我是否还需要提供 to_sql 驱动程序?如果是这样，显示我哪里出错的文档或示例代码在哪里?

由于未来的目标和需求，我正在努力学习 Python 并将其用作脚本语言.我将不胜感激所提供的任何帮助、帮助和指导.

解决方案

您应该明确指定 MS SQL Server 驱动程序 如果您使用 SQLAlchemy 1.0.0+ 版:

示例:

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

<块引用>

在 1.0.0 版中更改:基于主机名的 PyODBC 连接现在需要明确指定的 SQL Server 驱动程序名称.SQLAlchemy无法在此处选择最佳默认值，因为它因平台而异和已安装的驱动程序.

本机 SQL Server 客户端名称列表

  • SQL Server 2005:
<块引用>

SQL Server 本地客户端

  • SQL Server 2008:
<块引用>

SQL Server 本机客户端 10.0

  • SQL Server 2016:
<块引用>

SQL Server 本机客户端 11.0

<小时>

或者你可以使用 pymssql:

engine = create_engine('mssql+pymssql://:@/?charset=utf8')

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.

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')

相关文章