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

Tutorial on MySQL Connection using SQLALchemy

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="test",
      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/db_name")

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

Advantage of SQLalchem

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

Python MySQL connection