Interface Python with an SQL Database

Python is an easy-to-learn language and connectivity of python with any SQL database is a much-desired option to have the persistence feature. Python is an object-oriented programming language and it is open source. Newcomers to the software industry including school children too can learn Python easily. Python can be downloaded easily according to the operating system and all novice users also can do it easily.

SQL Server is a database that is helpful for persistent data. Whenever we want to store a lot of information, we are dependent on a database. It can be any database like MySQL, SQL Server, and even NoSQL database like MongoDB. Information is stored across multiple tables in a database like MySQL or SQLServer. Under a database, multiple tables can be created. Each and every table can have multiple columns. A table can have numerous rows where each and every row represents the information of either student/employee/exam etc., That means each and every row must be uniquely identified by means of a Primary Key. Apart from this, we have different keys like Unique Key, Foreign Key, etc. to access data.

The necessity of interfacing Python with SQL database

All database (RDBMS) is required to store the data, and manage the data (in the way of inserting/deleting/updating) the data. The way of handling these sets of operations is called CRUD operations (CREATE/READ/UPDATE/DELETE). Python is a user-friendly language and it can able to get connected to any database easily via the above ways. Once proper installation is done, Python and that database can be connected. Any CRUD operations can able to get handled easily.

1. In Python, we will be receiving the inputs from various sources.
2. Python will work on the inputs and produce the desired output upon certain computations.
3. The final output can be stored in the database if we interface with Python and SQL databases.

For interfacing Python and the database, we need to install the desired installations. For each and every SQL database (RDBMS), we need to install it differently. That differs with the operating system as well

For sqlite3 Module:

pip install pysqlite3 

In earlier versions of python, it will be 

pip install pysqlite

For anaconda environment

conda install sqlite3

Similarly for MySQL, it will be

pip install mysqlclient
--older versions  (1.2.x versions (legacy Python only))
pip install MySQL-python

SQL Server:

pip install pymssql

Connecting MySQL with Python

The following program will show how to connect MySQL with Python

# Python3 Program to connect MySQL to Python
# As we are connecting mysql, this is needed
import mysql.connector
db_connection = mysql.connector.connect(
  host="localhost",
  
  # Change your username here
  user="root",  
  
  # Change your password here
  passwd="admin",  
  
  # Need to point for the correct db
  database="geeksforgeeks" 
)

# Database is in scope now
my_database = db_connection.cursor()  

Once we get the connection, we need to see how to insert the data. Let us assume that in MySQL, there is a table named 'GEEKPORTALLOGIN' available. Providing the scripts to create a table in MySQL as well

drop table GEEKSFORGEEKS.GEEKPORTALLOGIN;  # If we want to drop the table if already exists

-- Create the table GEEKPORTALLOGIN 

CREATE TABLE GEEKSFORGEEKS.GEEKPORTALLOGIN (id INT NOT NULL,

   loginName VARCHAR(20) default NULL,

   password VARCHAR(45) default NULL,

   PRIMARY KEY (id)

);

Inserting Data using Python

We can insert data into the database using Python. The following methods are used to insert data:

1. execute(): This function is used to prepare a database operation and run it. It takes parameters in the form of a series or mapping. 

Syntax:

execute(operation[, parameters])

2. executemany(): This function is used to build a database action and run it against all of the parameter tuples in the series of parameters. It is especially used for database update instructions. 

Syntax:

executemany(operation, sequence_of_parameters)

The following examples will show how to insert data into the database using Python.

Example 1:

# Python Program to insert data into database 
import mysql.connector

db_connection = mysql.connector.connect(
  host="localhost",
  
  # Change your username here
  user="root",  
  
  # Change your password here
  passwd="admin",  
  
  # Need to point for the correct db
  database="geeksforgeeks" 
)
my_database = db_connection.cursor()

# Get the inputs. Usually from frontend we may 
# get inputs and they will be passed to Python
# Here let us get from Python itself
loginName = input("Enter a login Name : ");

# For sample only password is getting shown here
password = input("Enter a password : "); 
idx = "1"
sql_statement = "INSERT INTO geekportallogin (id,loginName,password) values(%s,%s,%s)"
values = (idx,loginName,password)

# Execute will help to insert a single row of data
my_database.execute(sql_statement,values)

# To save the data into the database under the particular table
db_connection.commit()  

Output:

Example 2: 

Similarly, we can insert many rows of data together. This will be the ideal scenario when we are doing bulk inserts

# Python3 program to insert multiple rows
import mysql.connector

sample_connection = mysql.connector.connect(
  host="localhost",
  
  # Change your username here
  user="root",  
  
  # Change your password here
  passwd="admin",  
  
  # Need to point for the correct db
  database="geeksforgeeks" 
)
sample_database = sample_connection.cursor()

# Inserting data
values = [ ("2","geekb","geekb"),

           ("3","geekc","geekc"),

           ("4","geekd","geekd")  
          ]
sql_statement = "INSERT INTO geekportallogin (id,loginName,password)  values(%s,%s,%s)"

# As we are going to insert multiple values, we need to use executemany
sample_database.executemany(sql_statement,values)

# To save the data into the database under the particular table
sample_connection.commit()  

Output:

Fetching Data using Python

We can also fetch data using Python from the MySql database. To fetch the data from the MySql database we can use the following methods:

1. fetchall(): This function will fetch all the tuples from the last executed statement from the table. If there are no more rows available, then it will return an empty list.

Syntax: 

data = cursor.fetchall()

2. fetchone(): This function is used to fetch one row from the specified table.

Syntax: 

data = cursor.fetchone()

2. fetchmany(): This function is used to fetch the next set of rows from a query result and If there are no more rows available, then it will return a blank list. The default size of fetchmany() function is one only. It takes only one argument which represents the number of rows to fetch. 

Syntax:

data = cursor.fetchmant([size = cursor.arraySize])

The following examples will show how to fetch the MySQL data from Python. 

Example 1:

# Python program to fetch data from MySql database
import mysql.connector
sample_connection = mysql.connector.connect(
  host="localhost",
  
  # Change your username here
  user="root",  
  
  # Change your password here
  passwd="admin", 
  
  # Need to point for the correct db
  database="geeksforgeeks" 
)
sample_database = sample_connection.cursor()

# Specify your correct table name here
sql_statement = "SELECT * FROM GEEKPORTALLOGIN" 
sample_database.execute(sql_statement)

# This step is used to get all the records as a list of tuples
sample_output = sample_database.fetchall()  

# Iterate over the output
for value in sample_output:
  print(value)

Output:

Example 2: 

# Python program to fetch data from MySql database
import mysql.connector
sample_connection = mysql.connector.connect(
  host="localhost",
  
  # Change your username here
  user="root",  
  
  # Change your password here
  passwd="admin",  
  
  # Need to point for the correct db
  database="geeksforgeeks" 
)
sample_database = sample_connection.cursor()

# Specify your correct table name here
sql_statement = "SELECT * FROM GEEKPORTALLOGIN" 
sample_database.execute(sql_statement)

# fetchmany(size=n) will tell n records where
# n can be more than 1
# This step is used to get only 2 records
sample_output = sample_database.fetchmany(size=2)  

# Iterate over the output
# Hence we can see only first 2 rows of data
# as we have given size = 2
for value in sample_output:
  print(value)
  

Output:

Updating the MySQL data using Python

We can also update the data stored in the MySql database using python. The following example will show how to update the data with the new data. 

Example: 

# Python program to update data in database
import mysql.connector

sample_connection = mysql.connector.connect(
  host="localhost",
  
  # Change your username here
  user="root",  
  
  # Change your password here
  passwd="admin",  
  
  # Need to point for the correct db
  database="geeksforgeeks" 
)
sample_database = sample_connection.cursor()

# Specify the update statement
sql_statement = "UPDATE  GEEKPORTALLOGIN SET loginName = %s WHERE id = %s" 

# Provide the values now for loginName and id

# First one will indicate for loginName and second one for id
values = ("geeka123","1") 

# This will take care of updating values
sample_database.execute(sql_statement,values) 
sample_connection.commit()

Output:

Deleting the MySQL data using Python

We can also delete the specific row from the MySql database using python. The following example will show how to delete the specified row from the record.

Example:

# Python program to delete the row
import mysql.connector

sample_connection = mysql.connector.connect(
  host="localhost",
  
  # Change your username here
  user="root", 
  
  # Change your password here
  passwd="admin",  
  
  # Need to point for the correct db
  database="geeksforgeeks" 
)
sample_database = sample_connection.cursor()

# Specify the delete statement
sql_statement = "DELETE FROM  GEEKPORTALLOGIN  WHERE id = %s" 

# Provide the values now for id
# We are going to delete for id = 1. 
# This is the syntax we have to follow
values = ("1",) 

# This will take care of deleting values
sample_database.execute(sql_statement,values)  
sample_connection.commit()

Output: