从Python写入SQL Server

Question

这是我能给出的最小的例子，是MRE

我正在尝试执行以下操作：

• 使用pyodbc，从SQL Server实例读取（完成）
• 然后，打印该数据以进行验证（完成）
• 然后，获取该数据，并将其插入到新表中（如果存在，则覆盖该表）<；-失败

代码如下：

import pyodbc
import pandas as pd
import sqlalchemy as sa

sqlConn = pyodbc.connect(
    "DRIVER={SQL Server};"
    "SERVER=servername;"
    "DATABASE=dbname;"
    "Trusted_Connection=yes;"
)

sql = """
SELECT TOP (1000) [PART]
      ,[STEP]
      ,[COMPLETIONTIME]
  FROM [dbname].[dbo].[STEPS]
"""

engine = sa.create_engine('mssql+pyodbc://servername/dbname')


df = pd.read_sql(sql, sqlConn)

df.to_sql(name = 'Test', con = engine, if_exists = 'replace', index = False)
sqlConn.commit()
sqlConn.close()

对于一个错误，我得到以下信息：

  File "WiP.py", line 26, in <module>
    df.to_sql(name = 'Test', con = engine, if_exists = 'replace', index = False)
  File "C:\Python367-64\lib\site-packages\pandas\core\generic.py", line 2712, in to_sql
    method=method,
  File "C:\Python367-64\lib\site-packages\pandas\io\sql.py", line 518, in to_sql
    method=method,
  File "C:\Python367-64\lib\site-packages\pandas\io\sql.py", line 1319, in to_sql
    table.create()
  File "C:\Python367-64\lib\site-packages\pandas\io\sql.py", line 641, in create
    if self.exists():
  File "C:\Python367-64\lib\site-packages\pandas\io\sql.py", line 628, in exists
    return self.pd_sql.has_table(self.name, self.schema)
  File "C:\Python367-64\lib\site-packages\pandas\io\sql.py", line 1344, in has_table
    self.connectable.dialect.has_table, name, schema or self.meta.schema
  File "C:\Python367-64\lib\site-packages\sqlalchemy\engine\base.py", line 2162, in run_callable
    with self._contextual_connect() as conn:
  File "C:\Python367-64\lib\site-packages\sqlalchemy\engine\base.py", line 2242, in _contextual_connect
    self._wrap_pool_connect(self.pool.connect, None),
  File "C:\Python367-64\lib\site-packages\sqlalchemy\engine\base.py", line 2280, in _wrap_pool_connect
    e, dialect, self
  File "C:\Python367-64\lib\site-packages\sqlalchemy\engine\base.py", line 1547, in _handle_dbapi_exception_noconnection
    util.raise_from_cause(sqlalchemy_exception, exc_info)
  File "C:\Python367-64\lib\site-packages\sqlalchemy\util\compat.py", line 398, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb, cause=cause)
  File "C:\Python367-64\lib\site-packages\sqlalchemy\util\compat.py", line 152, in reraise
    raise value.with_traceback(tb)
  File "C:\Python367-64\lib\site-packages\sqlalchemy\engine\base.py", line 2276, in _wrap_pool_connect
    return fn()
  File "C:\Python367-64\lib\site-packages\sqlalchemy\pool\base.py", line 363, in connect
    return _ConnectionFairy._checkout(self)
  File "C:\Python367-64\lib\site-packages\sqlalchemy\pool\base.py", line 760, in _checkout
    fairy = _ConnectionRecord.checkout(pool)
  File "C:\Python367-64\lib\site-packages\sqlalchemy\pool\base.py", line 492, in checkout
    rec = pool._do_get()
  File "C:\Python367-64\lib\site-packages\sqlalchemy\pool\impl.py", line 139, in _do_get
    self._dec_overflow()
  File "C:\Python367-64\lib\site-packages\sqlalchemy\util\langhelpers.py", line 68, in __exit__
    compat.reraise(exc_type, exc_value, exc_tb)
  File "C:\Python367-64\lib\site-packages\sqlalchemy\util\compat.py", line 153, in reraise
    raise value
  File "C:\Python367-64\lib\site-packages\sqlalchemy\pool\impl.py", line 136, in _do_get
    return self._create_connection()
  File "C:\Python367-64\lib\site-packages\sqlalchemy\pool\base.py", line 308, in _create_connection
    return _ConnectionRecord(self)
  File "C:\Python367-64\lib\site-packages\sqlalchemy\pool\base.py", line 437, in __init__
    self.__connect(first_connect_check=True)
  File "C:\Python367-64\lib\site-packages\sqlalchemy\pool\base.py", line 639, in __connect
    connection = pool._invoke_creator(self)
  File "C:\Python367-64\lib\site-packages\sqlalchemy\engine\strategies.py", line 114, in connect
    return dialect.connect(*cargs, **cparams)
  File "C:\Python367-64\lib\site-packages\sqlalchemy\engine\default.py", line 482, in connect
    return self.dbapi.connect(*cargs, **cparams)
sqlalchemy.exc.InterfaceError: (pyodbc.InterfaceError) ('IM002', '[IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified (0) (SQLDriverConnect)')
(Background on this error at: http://sqlalche.me/e/rvf5)

我查了一下on this site，得到了以下结果：

Exception raised for errors that are related to the database’s operation and not necessarily under the control of the programmer, e.g. an unexpected disconnect occurs, the data source name is not found, a transaction could not be processed, a memory allocation error occurred during processing, etc.

我还咨询了：

• Connecting to Microsoft SQL server using Python
• List sql tables in pandas.read_sql
• https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.read_sql_table.html
• https://github.com/mkleehammer/pyodbc/issues/300
• Connecting to SQL Server 2012 using sqlalchemy and pyodbc

我也尝试过将df.to_sql(name = 'Test', con = engine, if_exists = 'replace', index = False)切换到df.to_sql(name = 'Test', con = sqlConn, if_exists = 'replace', index = False)，但得到了完全相同的错误。你知道吗

我做错了什么？如何从数据帧写入新表（或覆盖现有表）？你知道吗

更新

似乎连接失败了。以下内容：

import pyodbc
import pandas as pd
import sqlalchemy as sa
from sqlalchemy import create_engine

sqlConn = pyodbc.connect(
    "DRIVER={SQL Server};"
    "SERVER=servername;"
    "DATABASE=dbname;"
    "Trusted_Connection=yes;"
)

sql = """
SELECT TOP (1000) [ORDR_PART_NO]
      ,[OROP_ID]
      ,[COMPLETIONTIME]
  FROM [dbname].[dbo].[OpsLookup]
"""
engine = sa.create_engine('mssql+pyodbc://servername/dbname')

cnxn = engine.connect()
result = cnxn.execute("SELECT TOP (1000) * FROM [dbname].[dbo].[STEPS]")
for row in result:
    print(row)
cnxn.close()

收益率：

C:\Python367-64\lib\site-packages\sqlalchemy\connectors\pyodbc.py:79: SAWarning: No driver name specified; this is expected by PyODBC when using DSN-less connections
  "No driver name specified; "
Traceback (most recent call last):
  File "C:\Python367-64\lib\site-packages\sqlalchemy\engine\base.py", line 2276, in _wrap_pool_connect
    return fn()
  File "C:\Python367-64\lib\site-packages\sqlalchemy\pool\base.py", line 303, in unique_connection
    return _ConnectionFairy._checkout(self)
  File "C:\Python367-64\lib\site-packages\sqlalchemy\pool\base.py", line 760, in _checkout
    fairy = _ConnectionRecord.checkout(pool)
  File "C:\Python367-64\lib\site-packages\sqlalchemy\pool\base.py", line 492, in checkout
    rec = pool._do_get()
  File "C:\Python367-64\lib\site-packages\sqlalchemy\pool\impl.py", line 139, in _do_get
    self._dec_overflow()
  File "C:\Python367-64\lib\site-packages\sqlalchemy\util\langhelpers.py", line 68, in __exit__
    compat.reraise(exc_type, exc_value, exc_tb)
  File "C:\Python367-64\lib\site-packages\sqlalchemy\util\compat.py", line 153, in reraise
    raise value
  File "C:\Python367-64\lib\site-packages\sqlalchemy\pool\impl.py", line 136, in _do_get
    return self._create_connection()
  File "C:\Python367-64\lib\site-packages\sqlalchemy\pool\base.py", line 308, in _create_connection
    return _ConnectionRecord(self)
  File "C:\Python367-64\lib\site-packages\sqlalchemy\pool\base.py", line 437, in __init__
    self.__connect(first_connect_check=True)
  File "C:\Python367-64\lib\site-packages\sqlalchemy\pool\base.py", line 639, in __connect
    connection = pool._invoke_creator(self)
  File "C:\Python367-64\lib\site-packages\sqlalchemy\engine\strategies.py", line 114, in connect
    return dialect.connect(*cargs, **cparams)
  File "C:\Python367-64\lib\site-packages\sqlalchemy\engine\default.py", line 482, in connect
    return self.dbapi.connect(*cargs, **cparams)
pyodbc.InterfaceError: ('IM002', '[IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified (0) (SQLDriverConnect)')

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "WiP.py", line 21, in <module>
    cnxn = engine.connect()
  File "C:\Python367-64\lib\site-packages\sqlalchemy\engine\base.py", line 2209, in connect
    return self._connection_cls(self, **kwargs)
  File "C:\Python367-64\lib\site-packages\sqlalchemy\engine\base.py", line 103, in __init__
    else engine.raw_connection()
  File "C:\Python367-64\lib\site-packages\sqlalchemy\engine\base.py", line 2307, in raw_connection
    self.pool.unique_connection, _connection
  File "C:\Python367-64\lib\site-packages\sqlalchemy\engine\base.py", line 2280, in _wrap_pool_connect
    e, dialect, self
  File "C:\Python367-64\lib\site-packages\sqlalchemy\engine\base.py", line 1547, in _handle_dbapi_exception_noconnection
    util.raise_from_cause(sqlalchemy_exception, exc_info)
  File "C:\Python367-64\lib\site-packages\sqlalchemy\util\compat.py", line 398, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb, cause=cause)
  File "C:\Python367-64\lib\site-packages\sqlalchemy\util\compat.py", line 152, in reraise
    raise value.with_traceback(tb)
  File "C:\Python367-64\lib\site-packages\sqlalchemy\engine\base.py", line 2276, in _wrap_pool_connect
    return fn()
  File "C:\Python367-64\lib\site-packages\sqlalchemy\pool\base.py", line 303, in unique_connection
    return _ConnectionFairy._checkout(self)
  File "C:\Python367-64\lib\site-packages\sqlalchemy\pool\base.py", line 760, in _checkout
    fairy = _ConnectionRecord.checkout(pool)
  File "C:\Python367-64\lib\site-packages\sqlalchemy\pool\base.py", line 492, in checkout
    rec = pool._do_get()
  File "C:\Python367-64\lib\site-packages\sqlalchemy\pool\impl.py", line 139, in _do_get
    self._dec_overflow()
  File "C:\Python367-64\lib\site-packages\sqlalchemy\util\langhelpers.py", line 68, in __exit__
    compat.reraise(exc_type, exc_value, exc_tb)
  File "C:\Python367-64\lib\site-packages\sqlalchemy\util\compat.py", line 153, in reraise
    raise value
  File "C:\Python367-64\lib\site-packages\sqlalchemy\pool\impl.py", line 136, in _do_get
    return self._create_connection()
  File "C:\Python367-64\lib\site-packages\sqlalchemy\pool\base.py", line 308, in _create_connection
    return _ConnectionRecord(self)
  File "C:\Python367-64\lib\site-packages\sqlalchemy\pool\base.py", line 437, in __init__
    self.__connect(first_connect_check=True)
  File "C:\Python367-64\lib\site-packages\sqlalchemy\pool\base.py", line 639, in __connect
    connection = pool._invoke_creator(self)
  File "C:\Python367-64\lib\site-packages\sqlalchemy\engine\strategies.py", line 114, in connect
    return dialect.connect(*cargs, **cparams)
  File "C:\Python367-64\lib\site-packages\sqlalchemy\engine\default.py", line 482, in connect
    return self.dbapi.connect(*cargs, **cparams)
sqlalchemy.exc.InterfaceError: (pyodbc.InterfaceError) ('IM002', '[IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified (0) (SQLDriverConnect)')
(Background on this error at: http://sqlalche.me/e/rvf5)

Answer 1

engine = sa.create_engine('mssql+pyodbc://servername/dbname')

需要更改为

engine = sa.create_engine('mssql+pyodbc://servername/dbname?trusted_connection=yes&driver=ODBC Driver 13 for SQL Server')

每：https://docs.sqlalchemy.org/en/13/dialects/mssql.html#hostname-connections