CODEWITHSUNDEEP
pythonmysqlmysql-workbenchpyodbc
Pluto
Pluto

Reputation: 1

Problem connecting to MySQL database using Python through ODBC

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

Upvotes: 0

Views: 1230

Answers (1)

Gord Thompson
Gord Thompson

Reputation: 123549

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.

Upvotes: 1

Related Questions