Pyodbc Error when creating SQLAlchemy Engine

Question

I am trying to write a Pandas Dataframe called df into a Table in SQL Express as in the code below, but I get the error DBAPIError: (pyodbc.Error) ('IM002', '[IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified (0) (SQLDriverConnect)') in the line engine = sqlalchemy.create_engine('mssql://LENOVO-PC\SQlEXPRESS\\SQLEXPRESS/Databasewithinfo?trusted_connection=yes'). I saw the answer in this post and tried to follow that. I know that my server_name = LENOVO-PC\SQlEXPRESS and that database_name = Databasewithinfo and am thus struggling to understand where I'm going wrong.

import sqlalchemy
from sqlalchemy import create_engine
engine = sqlalchemy.create_engine('mssql://LENOVO-PC\SQlEXPRESS\\SQLEXPRESS/Databasewithinfo?trusted_connection=yes')
df.to_sql('JPY_data', engine, chunksize=1000)

Thank You

score 0 · Answer 1 · answered Jun 18 '15 at 17:52

This isn't directly an answer, but what a toolkit to test connection variants until it works.

You really want to throw as many connect strings variants as you can at it until something works. I've put in two already.

I did notice the post you refer to only has SQLEXPRESS once in the connect string, unlike you.

import sqlalchemy
from sqlalchemy import create_engine

def test_it(t_connect_string):

    #these are your connection setting, they are constant
    di = dict(server="LENOVO-PC",database="Databasewithinfo")    

    connect_string = t_connect_string % di

    try:
        engine = sqlalchemy.create_engine(connect_string)
        print "%s Success!" %  (connect_string)
        return True
    except Exception, e:
        print "%s Exception=>%s" %  (connect_string, e)
        return False

#put as many possible templates as you need until it connects, then you're good
li_test = [
    """mssql://%(server)s\SQlEXPRESS\\SQLEXPRESS/%(database)s?trusted_connection=yes""",

    #the post you refer to seems to have this format instead...
    """mssql://%(server)s\\SQLEXPRESS/%(database)s?trusted_connection=yes """,

]

#test them until something works.
for test in li_test:
    result = test_it(test)
    if result:
        break

It blows up for me, because I don't odbc installed, but you'll get more relevant errors hopefully.

mssql://LENOVO-PC\SQlEXPRESS\SQLEXPRESS/Databasewithinfo?trusted_connection=yes Exception=>No module named pyodbc
mssql://LENOVO-PC\SQLEXPRESS/Databasewithinfo?trusted_connection=yes  Exception=>No module named pyodbc

Pyodbc Error when creating SQLAlchemy Engine

1 Answers1