Python MySQL - Database Connection

This blog post on Python MySQL - Database Connection shows how to establish a connection with Python applications with a MySQL database server.

Python's MySQL Connector has the following benefits: 

• It is written in the Python programming language, and it can do database queries on its own.
• It works with MySQL and a Python driver that is officially supported by Oracle.
• It works with Python 3 and is actively maintained.

Establishing connection with MySQL using python                                   

Before connecting to the MySQL database 

• We have built a database with the name mydb.
• We have created a table EMPLOYEE, and the following columns have been added: FIRST NAME, LAST NAME, AGE, SEX, and INCOME.
• username: root, and password: password is the credentials we are using to connect with MySQL.

The connect() function is used to create a connection. This accepts a MySQLConnection class object as input and returns it together with the username, passwords, hostname, and name of the database you need to connect to.

Example

The following shows how to connect with the MySQL database "mydb".

import mysql.connector

#establishing the connection

conn = mysql.connector.connect(user='root', password='password', host='127.0.0.1',   

database='mydb')

#Creating a cursor object using the cursor() method

cursor = conn.cursor()

#Executing an MYSQL function using the execute() method

cursor.execute("SELECT DATABASE()")

# Fetch a single row using fetchone() method.

data = cursor.fetchone()

print("Connection established to: ",data)

#Closing the connection

conn.close()

Insert Data

A query can be passed as a parameter and then executed by the execute() function (invoked on the cursor object). The MySQL INSERT statement must have a parameter passed to it in order to insert data.

• Import MySQL.connector package.
• Create a connection object using the mysql.connector.connect()method, bypassing the user name, password, host (optional default: localhost), and, database (optional) as parameters to it.
• Create a cursor object using the cursor() function on the connection object created above
• Execute the INSERT statement as a parameter to execute the function.

Example

The following example executes a SQL INSERT statement to insert a record/value into the EMPLOYEE table

import mysql.connector

#establishing the connection

conn=mysql.connector.connect(user='root',password='password', host='127.0.0.1',  

             database='mydb')

#Creating a cursor object using the cursor() method

cursor = conn.cursor()

# Preparing SQL query to INSERT a record into the database.

sql = """INSERT INTO EMPLOYEE(

   FIRST_NAME, LAST_NAME, AGE, SEX, INCOME)

    VALUES ('Mac', 'Mohan', 20, 'M', 2000)"""

try:

   # Executing the SQL command

   cursor.execute(sql)

    # Commit your changes in the database

    conn.commit()

except:

    # Rolling back in case of error

   conn.rollback()

# Closing the connection

conn.close()

Python PostgreSQL

Establishing connection using python

The psycopg2 connection class represents and manages a connection object. The connect() function allows you to establish new connections. This takes dbname, user, password, host, and port as input parameters for the connection and returns a connection object. With the help of this function, you can connect to PostgreSQL.

Example

The Python code provided below demonstrates how to connect to an existing database. If the database does not exist, it will be created, and finally, a database object will be returned. PostgreSQL's default database name is postgre. We are providing it as the database name.

import psycopg2

#establishing the connection

conn=psycopg2.connect(database="postgres",user='postgres', password='password', 

host='127.0.0.1', port= '5432')

#Creating a cursor object using the cursor() method

cursor = conn.cursor()

#Executing an MYSQL function using the execute() method

cursor.execute("select version()")

# Fetch a single row using fetchone() method.

data = cursor.fetchone()

print("Connection established to: ",data)

#Closing the connection

conn.close()

Connection established to:('PostgreSQL 11.5, compiled by Visual C++ build 1914, 

64-bit',)

Inserting data using python

The cursor class of psycopg2 provides a method with the name execute().  The query is executed by this method after being accepted as a parameter.

• Import psycopg2 package.
• Create a connection object using the connect() method, bypassing the user name, password, host (optional default: localhost), and, database (optional) as parameters to it.
• You can turn off the auto-commit option by setting false as a value to the attribute auto-commit.
• A cursor object is returned by the cursor() function of the Connection class of the psycopg2 library. With this technique, a cursor object is created.
• The INSERT statement(s) should then be executed by sending them as a parameter to the execute() method.

Example

The PostgreSQL database is created with the name Employee table, and records are added to it using the Python program that follows.

import psycopg2

#Establishing the connection

conn=psycopg2.connect(database="mydb",user='postgres', password='password',

host='127.0.0.1', port= '5432')

#Setting auto commit false

conn.autocommit = True

#Creating a cursor object using the cursor() method

cursor = conn.cursor()

# Preparing SQL queries to INSERT a record into the database.

cursor.execute('''INSERT INTO EMPLOYEE(FIRST_NAME, LAST_NAME, AGE, SEX,

    INCOME) VALUES ('Ramya', 'Rama priya', 27, 'F', 9000)''')

# Commit your changes in the database

conn.commit()

print("Records inserted........")

# Closing the connection

conn.close()

Python SQLite

Establishing connection using python

The SQLite3 Python module can be used to interact with the SQLite2 database. To do so, you must first establish a connection (create a connection object).

To connect to an SQLite3 database using Python,

• Use import statement import splite3 module.
• The connection object is returned by the connect() method, which takes the name of the database you want to connect to as a parameter.

Example

import sqlite3

conn = sqlite3.connect('example.db')

Inserting data using Python

To add records to a table that already exists in an SQLite database

• Import the sqlite3 package.
• Create a connection object with the connect() function, passing the name of the database as a parameter.
• To create a connection object, the database name should be passed as a parameter to the connect() method.  
• The cursor() function returns a cursor object you can use to interact with SQLite3. By using the cursor() on the Connection object (which is above created), you can build a cursor object.
• Then, execute the cursor object's with the execute() method by providing an INSERT statement to it as an input.

Example

The following Python example updates record the EMPLOYEE table.

import sqlite3

#Connecting to sqlite

conn = sqlite3.connect('example.db')

#Creating a cursor object using the cursor() method

cursor = conn.cursor()

# Preparing SQL queries to INSERT a record into the database.

cursor.execute('''INSERT INTO EMPLOYEE(FIRST_NAME, LAST_NAME, AGE, SEX, 

INCOME) VALUES 

    ('Ramya', 'Rama Priya', 27, 'F', 9000)''')

# Commit your changes in the database

conn.commit()

print("Records inserted........")

# Closing the connection

conn.close()

Conclusion

In this blog post, you learned how to connect a MySQL database to your Python application with examples. One of the best and most feature-rich database management systems is MySQL.  You can efficiently connect a MySQL database to your Python application using the methods covered in this blog.