3

I am trying to upload a dataframe to a database on Azure SQL Server Database using SQLAlchemy and pyobdc. I have established connection but when uploading I get an error that says

(pyodbc.Error) ('IM010', '[IM010] [Microsoft][ODBC Driver Manager] Data source name too long (0) (SQLDriverConnect)')

I'm not sure where this error is coming from since I've used sqlalchemy before without a problem. I've attached my code below, can anybody help me diagnose the problem?

username = 'bcadmin'
password = 'N@ncyR2D2'
endpoint = 'bio-powerbi-bigdata.database.windows.net'

engine = sqlalchemy.create_engine(f'mssql+pyodbc://{username}:{password}@{endpoint}')

df.to_sql("result_management_report",engine,if_exists='append',index=False)

I know of other ETL methods like Data Factory and SSMS but I'd prefer to use pandas as the ETL process.

Please help me with this error.

Gord Thompson
  • 116,920
  • 32
  • 215
  • 418
Heeje Cho
  • 81
  • 2
  • 8
  • Perhaps try explicitly adding `:1433` after the endpoint name. It looks like SQLA/pyodbc is interpreting the server name as a DSN name. – Gord Thompson Mar 18 '20 at 22:46
  • 1
    Escape your password, it contains `@`. – Ilja Everilä Mar 19 '20 at 09:44
  • @IljaEverilä I tried escaping the password, that didnt fix the error. Adding :1433 did not help either. I wonder what the problem could be. – Heeje Cho Mar 19 '20 at 15:50
  • Your URL lacks the trailing `/` required to distinguish it from a DSN connection, so the hostname is treated as DSN (see https://docs.sqlalchemy.org/en/13/dialects/mssql.html#module-sqlalchemy.dialects.mssql.pyodbc). – Ilja Everilä Mar 19 '20 at 17:30
  • @IljaEverilä Hi Ilja i tried the trailing / and it does not seem to work either. I am lost. – Heeje Cho Mar 19 '20 at 22:45
  • If using a host based connection, you'll need to provide the other details as well, such as the database to connect to on the host, the driver to use, etc. – Ilja Everilä Mar 20 '20 at 11:05

1 Answers1

4

Three issues here:

  1. If a username or password might contain an @ character then it needs to be escaped in the connection URL.
  2. For the mssql+pyodbc dialect, the database name must be included in the URL in order for SQLAlchemy to recognize a "hostname" connection (as opposed to a "DSN" connection).
  3. Also for mssql+pyodbc hostname connections, the ODBC driver name must be supplied using the driver attribute.

The easiest way to build a proper connection URL is to use the URL.create() method:

from sqlalchemy import create_engine
from sqlalchemy.engine import URL

my_uid = "bcadmin"
my_pwd = "N@ncyR2D2"
my_host = "bio-powerbi-bigdata.database.windows.net"
my_db = "master"
my_odbc_driver = "ODBC Driver 17 for SQL Server"

connection_url = URL.create(
    "mssql+pyodbc",
    username=my_uid,
    password=my_pwd,
    host=my_host,
    database=my_db,  # required; not an empty string
    query={"driver": my_odbc_driver},
)
print(connection_url)
"""console output:
mssql+pyodbc://bcadmin:N%40ncyR2D2@bio-powerbi-bigdata.database.windows.net/master?driver=ODBC+Driver+17+for+SQL+Server
"""

engine = create_engine(connection_url, fast_executemany=True)
Gord Thompson
  • 116,920
  • 32
  • 215
  • 418