Problem connecting to MySQL database using Python through ODBC

Question

I'm having a problem establishing a connection to the MySQL database No matter what I try or change I seem to be getting the same error. I want to be able to use python to modify MySQL database using ODBC but being new to this I'm not sure how to go about it. This is the error message:

Traceback (most recent call last): File "C:\Users\Lutho\PycharmProjects\pyODBC\main.py", line 17, in conx = pyodbc.connect(f'''DRIVER={driver}; pyodbc.OperationalError: ('08001', '[08001] [Microsoft][ODBC Driver 17 for SQL Server]Named Pipes Provider: Could not open a connection to SQL Server [53]. (53) (SQLDriverConnect); [08001] [Microsoft][ODBC Driver 17 for SQL Server]Login timeout expired (0); [08001] [Microsoft][ODBC Driver 17 for SQL Server]A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online. (53)')

Code I used:

import pyodbc

# define the server and database name
driver = '{ODBC Driver 17 for SQL Server}'
server = 'Local instance MySQL80'
database = 'tarsdb'
username = 'root'
password = '1234'

# define connection string
conx = pyodbc.connect(f'''DRIVER={driver}; 
                          SERVER={server}; 
                          DATABASE={database};
                          Uid={username};
                          Pwd={password};''')
# create connection cursor
cursor = conx.cursor()

I also tried using Trusted_Connection=yes and moved my MySQL file into the same folder as my python file but nothing has worked. Is there something I'm missing that I don't know about? If you can solve my problem that would be much appreciated. (If the question looks messy, just know I had a problem formating it.)

Why are you using a driver meant for SQL Server, i.e. {ODBC Driver 17 for SQL Server}, to try to connect to a MySQL database? — norie
– norie, Commented May 22, 2021 at 15:36
You are trying to connect to a MySQL server using an ODBC driver for Microsoft SQL Server. To connect to MySQL from Python consider using mysqlclient or pymysql — Gord Thompson
– Gord Thompson, Commented May 22, 2021 at 15:38
@norie I thought it was the same thing and that when you try to connect to the MySQL database it's treated as a server. — Pluto
– Pluto, Commented May 23, 2021 at 16:19
@GordThompson I will try those out and get back to you on that. — Pluto
– Pluto, Commented May 23, 2021 at 16:20
Thank you @GordThompson my program seems to be working now. I decided to go with the pymysql. — Pluto
– Pluto, Commented May 24, 2021 at 16:07

Gord Thompson · Accepted Answer · 2021-05-24 16:15:17Z

1

The issue was trying to use the ODBC driver for Microsoft SQL Server when the target database was MySQL. ODBC drivers are not interchangeable between different database products.

Connecting to MySQL from Python is possible using pyodbc and "MySQL Connector/ODBC" but there are better alternatives, specifically mysqlclient or pymysql. Both are solid choices, although in many circumstances mysqlclient is significantly faster than pymysql.

answered May 24, 2021 at 16:15

Gord Thompson

125k38 gold badges251 silver badges458 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Problem connecting to MySQL database using Python through ODBC

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related