How to connect MySQL Database in Python

In this post we will see how to connect the MySQL (PhpMyAdmin) database in Python

Install connector: Install MySQL connector module using below command, open command prompt and run below command.

pip install mysql-connector-python

Import connector : Import MySQL connector module ( import mysql.connector ) in your file.

Connect Method: Use the connect() method

Use the connect() method of the Connector class with the db parameters to connect database (server, database, username, password)

Use Cursor Method:

For creating connection object use cursor method

Use Execute Method:

For running query use execute method for return result.

User Fetchone Method:

To read query result use fetchone method with object

Close Method:

for closing connection use close method with object.

import mysql.connector
from mysql.connector import Error
try:
    connection = mysql.connector.connect(host='localhost',database='pythondb',user='root',password='')
    if connection.is_connected():
        db_Info = connection.get_server_info()
        print("Connected to MySQL Server version ", db_Info)
        cursor = connection.cursor()
        cursor.execute("select database();")
        record = cursor.fetchone()
        print("Database connected successfully: ", record)

except Error as e:
    print("Database connection error", e)
finally:
    if connection.is_connected():
        cursor.close()
        connection.close()
        print("Database connection closed")

Run file using terminal

Timeout with database connection using Python.

Sometime database connection takes time to connect for increasing timeout we can use connection_timeout methods.

import mysql.connector
from mysql.connector import Error
try:
    connection = mysql.connector.connect(host='localhost',database='pythondb',user='root',password='', connection_timeout=120)
    if connection.is_connected():
        db_Info = connection.get_server_info()
        print("Connected to database!! ", db_Info)
        cursor = connection.cursor()
        global_connect_timeout = 'SET GLOBAL connect_timeout=120'
        global_wait_timeout = 'SET GLOBAL connect_timeout=120'
        global_interactive_timeout = 'SET GLOBAL connect_timeout=120'
        cursor.execute(global_connect_timeout)
        cursor.execute(global_wait_timeout)
        cursor.execute(global_interactive_timeout)
        connection.commit()
except Error as e:
    print("DB connection error:", e)
finally:
    if connection.is_connected():
        cursor.close()
        connection.close()
        print("Close connection")

Create table using Python

After creating database, we can create table using python script.

import mysql.connector

try:
    connection = mysql.connector.connect(host='localhost',database='pythondb',user='root',password='')
    mySql_Create_Table_Query = """CREATE TABLE User ( 
                             Id int(11) NOT NULL,
                             Name varchar(250) NOT NULL,
                             Email varchar(250) NOT NULL,
                             PRIMARY KEY (Id)) """

    cursor = connection.cursor()
    result = cursor.execute(mySql_Create_Table_Query)
    print("Table created!")

except mysql.connector.Error as error:
    print("Table creation error: {}".format(error))
finally:
    if connection.is_connected():
        cursor.close()
        connection.close()
        print("connection closed")

Run the file

Keep learning 🙂