CODEWITHSUNDEEP
pythonmysqlsqlalchemyodbcpyodbc
Jihjohn
Jihjohn

Reputation: 438

Cannot connect to MySQL via Python using pyodbc and SQLAlchemy

I have been struggling to connect to a mysql db using pyodbc. The problem is, I can't find the appropriate syntax anywhere. I have seen different syntax on the internet, including stackoverflow, but they are not working for me. First of all, what should I pass to the "DRIVER" param for the connection. Is it the odbc driver available in the system? What is the difference between using UID and Pass v/s Trusted connection? Here is the code I am using now. Please help.

    def createconnection(host, port, db, uid, pwd):
        '''
        To create a mysql connection 
        '''
        try:
            odbcpath = r'C:\Windows\system32\odbcad32.exe'
            params = urllib.parse.quote_plus("DRIVER={C:\Windows\system32\odbcad32.exe};"
                                             f"SERVER={host,port};"
                                             f"DATABASE={db};"
                                             "Trusted_connection=yes")
            # db_engine = create_engine(f'mysql+pyodbc://{host}/{db}?driver={odbcpath}? 
              Trusted_Connection=yes')
            db_engine = create_engine(f"mysql+pyodbc:///?odbc_connect={params}")
            self.connection = db_engine.connect()
        except:
            print('Error while creating the db engine and connection')
            raise

This code is giving the following error

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/13/rvf5)

If possible please explain what ODBC/DSN is and how is connection using DSN different from connection using UID and PWD?

Upvotes: 1

Views: 2477

Answers (1)

Gord Thompson
Gord Thompson

Reputation: 123549

For MySQL, you first need to decide whether you really do want to use ODBC. For SQLAlchemy the preferred MySQL DBAPI layer is mysqlclient.

In order to use ODBC successfully on Windows you need an ODBC driver for your specific database. ODBC drivers are not interchangeable.

For MySQL, the ODBC driver is MySQL Connector/ODBC. You install it like any other Windows program. Once installed it appears in your ODBC Administrator

ODBC Administrator

and you would use DRIVER=MySQL ODBC 8.0 ANSI Driver or DRIVER=MySQL ODBC 8.0 Unicode Driver depending on your needs. See the Unicode notes in the pyodbc wiki for details.

Trusted_Connection=yes is a connection string parameter for Microsoft SQL Server ODBC drivers and is not applicable to MySQL ODBC.

Upvotes: 1

Related Questions