Writing to SQL Server from Python

Question

This is the smallest example I can give that is a MRE

I am attempting to do the following:

Using pyodbc, read from a SQL Server instance (complete)
Then, print that data to verify it (complete)
Then, take that data, and insert it into a new table (or overwrite the table if it exists) <- FAILURE

The code is below:

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

I get the following for an error:

  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)

I looked that up on this site, and got the following:

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.

I have also consulted:

I also tried switching df.to_sql(name = 'Test', con = engine, if_exists = 'replace', index = False) to df.to_sql(name = 'Test', con = sqlConn, if_exists = 'replace', index = False) but got the exact same error.

What am I doing incorrectly? How can I wrote to a new table (or overwrite an existing one) from a Pandas dataframe?

UPDATE

It appears the connection is failing. The following:

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

Yields:

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)

this connection is successful ?? engine = sa.create_engine('mssql+pyodbc://servername/dbname') — GiovaniSalazar, Dec 03 '19 at 17:43
with a execute like this - > https://docs.sqlalchemy.org/en/13/core/connections.html — GiovaniSalazar, Dec 03 '19 at 17:49
ok , then maybe this can help you - > https://communities.actian.com/s/article/Data-source-name-not-found-and-no-default-driver-specified — GiovaniSalazar, Dec 03 '19 at 17:59
"SAWarning: No driver name specified ..." - For a "hostname" connection [you need to specify the `?driver=`](https://docs.sqlalchemy.org/en/13/dialects/mssql.html#hostname-connections) in the SQLAlchemy connection URI. — Gord Thompson, Dec 03 '19 at 18:13

score -1 · Accepted Answer · answered Dec 05 '19 at 14:22

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

needed to be changed to

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

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

Writing to SQL Server from Python

1 Answers1