Python MySQL Connection: How to Connect Python to MySQL Database

We need to download and install mysql connector for python.

import mysql.connector

my_connect = mysql.connector.connect(
  host="localhost",
  user="userid",
  passwd="password",
  database="database_name"
)
####### end of connection ####
my_conn = my_connect.cursor()

Python to MySQL database connection by SQLAlchemy with error handling using try except blocks

MySQLCursor class instantiates objects to execute SQL statement. Such Cursor objects interact with MySQL database through MySQL connector.

In above code we have declared my_conn as cursor object
We will use my_conn having connection string in our applications.

Python to MySQL connector

With error message using try except

import mysql.connector
try:
    my_connect = mysql.connector.connect(
      host="localhost",
      user="root",
      passwd="test",
      database="my_tutorial"
    )
except mysql.connector.Error as my_error:
    print(my_error.msg)  # Error message
    print(my_error)      # With error number
####### end of connection ####
my_conn = my_connect.cursor()

####### Showing records #####
my_conn.execute("SELECT * FROM student") # SQL to execute
my_result = my_conn.fetchall()
for row in my_result:
    print(row)

Using sqlalchemy

Read on how to install sqlalchemy
Connection string is here

from sqlalchemy import create_engine
my_conn = create_engine("mysql+mysqldb://userid:password@localhost/database_name")

If you are getting this error. ( for recent drivers )
AttributeError: 'Engine' object has no attribute 'execute'
Use this code

Transitioning to SQLAlchemy’s New Connection Method with connect() and text()

In recent versions of SQLAlchemy, you are required to explicitly call the connect() method when working with the engine to ensure that the database connection is established properly.

Previously, you could execute queries directly using my_conn.execute(). Now, after creating the engine with create_engine(), you must call my_conn.connect() to get a connection object and use text() to wrap your SQL queries. This change enhances query execution and improves connection handling, making the process more reliable and secure. Here's an updated example:

from sqlalchemy import create_engine, text
my_conn = create_engine("mysql+mysqldb://id:pw@localhost/db_name").connect()
r_set = my_conn.execute(text("SELECT * FROM student"))
for row in r_set:
    print(row)

We will be using the connection variable my_conn in our scripts here.
Learn how to use XML file as configuration file to connect to MySQL

Listing all tables of the database

r_set=my_conn.execute("SHOW TABLES");
for row in r_set:
    print(row)

Listing all row of a table

r_set=my_conn.execute(text("SELECT * FROM student"));
for row in r_set:
    print(row)

Using SQLite database with SQLAlchemy

We can use SQLAlchemy to manage SQLite. The script part remain same and only the connection string changes. Here we have created the connection engine ( the variable ) my_conn. We used a common varaible my_conn as output of both connection engines so the script part remain same throughout.

from sqlalchemy import create_engine
my_conn = create_engine("sqlite:///my_db.db")

SQLAlchemy with error handling

u_id:User id of MySQL database
pw: Password for user id
loclhost: Address of MySQL server
database: Database of MySQL to connect

from sqlalchemy import create_engine 
from sqlalchemy.exc import SQLAlchemyError

try:
  my_conn =create_engine("mysql+mysqldb://u_id:pw@localhost/my_tutorial")
  my_conn.connect() # error is generated at this stage     
except SQLAlchemyError as e:
  #print(e)
  error = str(e.__dict__['orig'])
  print(error)
## connection is over ####

try:
  query="SELECT * FROM student1 LIMIT 0,2"
  r_set=my_conn.execute(query)
  for row in r_set:
    print(row)
  print('Data displayed ..')
except SQLAlchemyError as e:
  #print(e)
  error = str(e.__dict__['orig'])
  print(error)

Advantage of SQLAlchemy

If you want to change to other database ( say SQLite ) then only connection string needs to be changed as by using SQLAlchemy can be used to connect different databases. Here the script part mostly remain same. Easy portability is the main advantages here.

There is a small change at Parameterized query where for the placeholders we have to use %s for MySQL and ? for SQLite.
Inserting record using sqlalchemy
More on Error handling

Collecting Records

Python functions for MySQL Collect records Python- Tutorials

Subscribe to our YouTube Channel here

Python MySQL connection