Interface Python with an SQL Database
Last Updated :
22 Aug, 2022
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.
- In Python, we will be receiving the inputs from various sources.
- Python will work on the inputs and produce the desired output upon certain computations.
- 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
import mysql.connector
db_connection = mysql.connector.connect(
host="localhost",
user="root",
passwd="admin",
database="geeksforgeeks"
)
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:
Python3
import mysql.connector
db_connection = mysql.connector.connect(
host="localhost",
user="root",
passwd="admin",
database="geeksforgeeks"
)
my_database = db_connection.cursor()
loginName = input("Enter a login Name : ");
password = input("Enter a password : ");
idx = "1"
sql_statement = "INSERT INTO geekportallogin (id,loginName,password) values(%s,%s,%s)"
values = (idx,loginName,password)
my_database.execute(sql_statement,values)
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
import mysql.connector
sample_connection = mysql.connector.connect(
host="localhost",
user="root",
passwd="admin",
database="geeksforgeeks"
)
sample_database = sample_connection.cursor()
values = [ ("2","geekb","geekb"),
("3","geekc","geekc"),
("4","geekd","geekd")
]
sql_statement = "INSERT INTO geekportallogin (id,loginName,password) values(%s,%s,%s)"
sample_database.executemany(sql_statement,values)
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:
Python3
import mysql.connector
sample_connection = mysql.connector.connect(
host="localhost",
user="root",
passwd="admin",
database="geeksforgeeks"
)
sample_database = sample_connection.cursor()
sql_statement = "SELECT * FROM GEEKPORTALLOGIN"
sample_database.execute(sql_statement)
sample_output = sample_database.fetchall()
for value in sample_output:
print(value)
|
Output:
Example 2:
Python3
import mysql.connector
sample_connection = mysql.connector.connect(
host="localhost",
user="root",
passwd="admin",
database="geeksforgeeks"
)
sample_database = sample_connection.cursor()
sql_statement = "SELECT * FROM GEEKPORTALLOGIN"
sample_database.execute(sql_statement)
sample_output = sample_database.fetchmany(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:
Python3
import mysql.connector
sample_connection = mysql.connector.connect(
host="localhost",
user="root",
passwd="admin",
database="geeksforgeeks"
)
sample_database = sample_connection.cursor()
sql_statement = "UPDATE GEEKPORTALLOGIN SET loginName = %s WHERE id = %s"
values = ("geeka123","1")
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:
Python3
import mysql.connector
sample_connection = mysql.connector.connect(
host="localhost",
user="root",
passwd="admin",
database="geeksforgeeks"
)
sample_database = sample_connection.cursor()
sql_statement = "DELETE FROM GEEKPORTALLOGIN WHERE id = %s"
values = ("1",)
sample_database.execute(sql_statement,values)
sample_connection.commit()
|
Output: