Python to MySQL Connection

MySQL connector Python

This is MySQL driver written in Python. Install ( pip ) install MySQL connector from your Command Prompt like this.

C:\Users\user>pip install mysql-connector-python

Without this module you will get this error message
ModuleNotFoundError: No module named 'mysql'

import mysql.connector
my_conn=mysql.connector.connect(
        host='localhost',
        user='root', 
        password = 'your_password',
        db='my_database',
        )
      
my_cursor = my_conn.cursor()
my_cursor.execute("SELECT * FROM student")
my_result = my_cursor.fetchall() # we get a tuple
print(my_result)

py mysql

This driver is written in Python and contains a pure-Python MySQL client library. We can install (pip) from our command prompt.

C:\Users\user>pip install PyMySQL

Here is the sample code to connect and show data from our student table using pymysql connect .

import pymysql  
my_conn=pymysql.connect(
        host='localhost',
        user='root', 
        password = 'password',
        db='my_tutorial',
        )
      
my_cursor = my_conn.cursor()
my_cursor.execute("SELECT * FROM student")
my_result = my_cursor.fetchall() # we get a tuple
print(my_result)

Note : Don't give the file name as pymysql.py and use the import. Give a different file name. You may get error like this.

AttributeError: partially initialized module 'pymysql' has no attribute 'connect' (most likely due to a circular import)

MySQLdb

MySQLdb, is a C extension module. After MySQLDB2 now it is evolved as mysqlclient. Here is how to install mysqlclient.

C:\Users\user>pip install mysqlclient

Connection to MySQL using MySQLdb

import MySQLdb
my_conn=MySQLdb.connect(
        host='localhost',
        user='root', 
        password = "your_password",
        db='db_name',
        )
      
my_cursor = my_conn.cursor()
my_cursor.execute("SELECT * FROM student")
my_result = my_cursor.fetchall() # we get a tuple
print(my_result)

Note the difference in import MySQLdb but we have installed mysqlclient.

pyodbc

pip install pyodbc

In your system check for the driver name

Administrative tools -> ODBC Data Sources -> Driver tab then copy the river name to the first parameter
odbc drivers for using pyodbc to connect to database

odbc drivers for using pyodbc to connect to database

import pyodbc
my_conn = pyodbc.connect("DRIVER={MySQL ODBC 8.0 ANSI Driver}; \
 SERVER=localhost;DATABASE=my_tutorial; UID=root; PASSWORD=your_password;")
my_cursor = my_conn.cursor()
my_cursor.execute("SELECT * FROM student LIMIT 0,10")
my_result = my_cursor.fetchall() # we get a tuple
print(my_result)

Download & Install MySQL with Workbench and creating database with sample student table with records

Database Management using Python

BLOB	Managing MySQL Blob Data type
connection	Python MySQL connection string
create	Create table by Query and showing structure
rowcount	Number of rows affected due to Query
error	Capturing Error in MySQL Query
Collect records	Select Query to get records from database table
Add record	Using sqlalchemy to Insert record to database table
Add record	Insert Query to add record to database table
Delete Table	Drop table Query to remove table from MySQL database
Update record	Update Query to update records of a database table
Delete record	Delete records of a database table
read_sql	Using MySql records to create Pandas DataFrame
mysqlclient	Using sqlalchemy and create_engine to manage MySQL
pickle	How to Pickle MySQL table and restore records.

Python code for Installing sample student table

from sqlalchemy import create_engine
my_conn = create_engine("mysql+mysqldb://root:pwt@localhost/my_db")

#my_conn.execute("DROP table IF EXISTS student")

query1="CREATE TABLE IF NOT EXISTS `student` (\
  `id` int(2) NOT NULL DEFAULT '0',\
  `name` varchar(50) CHARACTER SET utf8 NOT NULL DEFAULT '',\
  `class` varchar(10) CHARACTER SET utf8 NOT NULL DEFAULT '',\
  `mark` int(3) NOT NULL DEFAULT '0',\
  `gender` varchar(6) CHARACTER SET utf8 NOT NULL DEFAULT 'male'\
) ENGINE=InnoDB DEFAULT CHARSET=latin1;"

query2="INSERT INTO `student` (`id`, `name`, `class`, `mark`, `gender`) VALUES\
(1, 'John Deo', 'Four', 75, 'female'),\
(2, 'Max Ruin', 'Three', 85, 'male'),\
(3, 'Arnold', 'Three', 55, 'male'),\
(4, 'Krish Star', 'Four', 60, 'female'),\
(5, 'John Mike', 'Four', 60, 'female'),\
(6, 'Alex John', 'Four', 55, 'male'),\
(7, 'My John Rob', 'Five', 78, 'male'),\
(8, 'Asruid', 'Five', 85, 'male'),\
(9, 'Tes Qry', 'Six', 78, 'male'),\
(10, 'Big John', 'Four', 55, 'female'),\
(11, 'Ronald', 'Six', 89, 'female'),\
(12, 'Recky', 'Six', 94, 'female'),\
(13, 'Kty', 'Seven', 88, 'female'),\
(14, 'Bigy', 'Seven', 88, 'female'),\
(15, 'Tade Row', 'Four', 88, 'male'),\
(16, 'Gimmy', 'Four', 88, 'male'),\
(17, 'Tumyu', 'Six', 54, 'male'),\
(18, 'Honny', 'Five', 75, 'male'),\
(19, 'Tinny', 'Nine', 18, 'male'),\
(20, 'Jackly', 'Nine', 65, 'female'),\
(21, 'Babby John', 'Four', 69, 'female'),\
(22, 'Reggid', 'Seven', 55, 'female'),\
(23, 'Herod', 'Eight', 79, 'male'),\
(24, 'Tiddy Now', 'Seven', 78, 'male'),\
(25, 'Giff Tow', 'Seven', 88, 'male'),\
(26, 'Crelea', 'Seven', 79, 'male'),\
(27, 'Big Nose', 'Three', 81, 'female'),\
(28, 'Rojj Base', 'Seven', 86, 'female'),\
(29, 'Tess Played', 'Seven', 55, 'male'),\
(30, 'Reppy Red', 'Six', 79, 'female'),\
(31, 'Marry Toeey', 'Four', 88, 'male'),\
(32, 'Binn Rott', 'Seven', 90, 'female'),\
(33, 'Kenn Rein', 'Six', 96, 'female'),\
(34, 'Gain Toe', 'Seven', 69, 'male'),\
(35, 'Rows Noump', 'Six', 88, 'female');"
my_conn.execute(query1)
my_conn.execute(query2)

#my_cursor=my_conn.execute("SELECT * FROM student")
#my_result = my_cursor.fetchall() # we get a tuple
#print(my_result)

Installing MySQL connection from Anaconda

Download & install Anaconda with Python by using mysqlclient to connect to MySQL database server

Before this you must have MySQL database running and keep your connection userid and password with your database name ready.

We need to install MySQL connector to connect from Python to MySQL database.

Inside Anaconda goto Environment at your left side navigational menu
MySQL installation from Anaconda

Middle you can see Base root
From Base root select Open Terminal
MySQL installation from Anaconda

You will reach the Anaconda terminal where you can install any package . Just enter this line at the prompt to install MySQL connector.

conda install -c anaconda mysql-connector-python

This will install your MySQL connector by downloading packages from the internet. Now it is time to check the connection.

MySQL Connecting string
MySQL connection using SQLAlchemy

Using SQLAlchemy

For updating the database you need to use sqlalchemy and its create_engine
Here is the code to get the connection by using sqlalchemy

from sqlalchemy import create_engine
engine = create_engine("mysql+mysqldb://userid_here:password_here@localhost/db_name")
my_conect = engine.connect()

If you are getting error message like this.
No module named 'MySQLdb'
then you need to install mysqlclient.
Open your Anaconda terminal as explained above and then enter

pip install mysqlclient

To check the installation

pip show mysqlclient

Checking mysqlclient installation for python

SQLAlchemy

pip install sqlalchemy

To check the installation

pip show sqlalchemy

MySQL & SQLAlchemy

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

MariaDB

This is a open source community developed relational database. To connect to MariaDB from Python installa this .

pip install mariadb

Using SQLALchemy we can create connection string like this.

my_conn = create_engine("mariadb+mariadbconnector://root:pw@localhost/db_name")

Google Cloud & MySQL

Using Python we can manage MySQL database of google Cloud.
Managing MySQL database at Google cloud

Data Transfer from Google sheets to MySQL and vice versa

Data Transfer from googel sheet to MySQL and vice versa

By using Pandas DataFrame we can transfer data from MySQL database table to Google sheets and from Google sheets to MySQL.
Data Transfer between Google sheet and MySQL

Exercise
Here is a list of Queries you can execute using the above learning and display the outcome.

List of Queries

Download .zip file with SQL dump and .ipynb files
of Video Tutorials

MySQL Connection Collect records Python- Tutorials

Subscribe to our YouTube Channel here