Python to MySQL Connection

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_cursor = 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_cursor as cursor object
We will use my_cursor 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="password",
      database="my_tutorial1"
    )
except mysql.connector.Error as my_error:
    print(my_error.msg)  #  Error message
    print(my_error)      #  With error number 
####### end of connection ####
my_cursor = my_connect.cursor()

Each time we run the script we will add one record. We will get confirm ( of adding record ) message by reading the rowcount value.

Here is the code to capture error while inserting the record into the table. Here we have added one wrong field ( column ) name for class.

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

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

We will be using the connection variable my_conn in our scripts here.

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("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