Updated March 30, 2023
Definition of Python SQL Library
Python SQL library data is interacted with by all software applications, most typically via a DBMS. Some programming languages have modules for interacting with a database management system, whilst others need the usage of third-party software. Python SQL library is used to connect the database. For connecting to MySQL database we need to use MySQL-connector dependency, also for connecting to the SQLite database we need to use sqlite3 dependency.
Python SQL Library
- In python, we need to use the python SQL library as per the database which was we are using to connect. Below are the databases we are using to connect the python.
1) SQLite
- While using the SQLite database we have no need to install any python SQL modules, for connecting to the SQLite database.
- We can connect with a SQLite database using the sqlite3 Python SQL package that comes with our Python installation.
- Furthermore, because they read and write data to a file, SQLite databases are serverless and self-contained. This implies that, unlike MySQL, we don’t need to install or run an SQLite server in order to conduct database operations.
Code:
import sqlite3
py_con = sqlite3.connect("py_sql.db")
py_csr = py_con.cursor()
print ("Connected SQLite database using python")
- Below is the working of the above code is as follows.
- The module’s Error class and sqlite3 are imported on lines 1 and 2. A function is defined on line 3 of the program. Create connection takes the SQLite database path as an argument.
- Line 2 calls the sqlite3 modules. connect function, which accepts the SQLite DB path as an argument.
- The database connection is made if the database is located at the supplied location. Otherwise, a connection is established in the DB specified location.
- The database connection status is printed on line 4. If.connect fails to create a connection, line 4 captures any exceptions that might be issued.
- Create connection is returning the connection object from sqlite3. connect (path). An SQLite database can be queried using this connection object.
2) MySQL
- Unlike SQLite, there is no built-in Python SQL library for connecting to MySQL databases. In order to connect with a MySQL database from within a Python program, we will need to install a SQL driver of Python for MySQL. Mysql-connector-python is one of these drivers. The module of python SQL is available for download via pip.
- Below examples shown to install the MySQL connection by using the pip command are as follows.
- MySQL is a DBMS that is hosted on a server. Multiple databases can exist on a MySQL server. In contrast to SQLite, where making a connection is the same as building a database of MySQL.
- To connect to a MySQL server, first, create a connection to it. To construct the database, run a separate query.
- Create a function that connects to MySQL and returns the connection object. To create a connection we need to give a hostname, username, and password.
Code:
import mysql.connector
py_my = mysql.connector.connect (
host = "localhost",
user = "root",
password = "Mysql@123"
)
print ("Connected to MySQL DB")
- The python connector in the python SQL module will contain the method of connecting, which was we have used in our code. After establishing a connection to the database server, the connection object will return the calling function.
- The connection object will represent the connection to the DB server with which we want to interact. The query that produces the database is called a query.
- The cursor object is used to run queries. Cursor receives the query to be executed. Execute is a function that returns a string.
- In the below example we are creating the database in MySQL database by using execute the method are as follows.
Code:
import mysql.connector
py_my = mysql.connector.connect(
host = "localhost",
user = "root",
password = "Mysql@123"
)
py_cur = py_my.cursor ()
py_cur.execute ("CREATE DATABASE sql_lib ")
print ("Database created.")
- In the above example, we have created the database name as sql_lib on the MySQL database server.
3) PostgreSQL –
- After importing the module into the python code then, we are using the connect method, to make the connection with the database at the time of making a connection with the database we need a username and password and also need the database name which was we need to access.
- After calling connect method and specifying the name of the database. For executing any query into the database server we need to create a cursor object. A cursor object is used to execute a command on the database server.
- PostgreSQL is the most popular RDBMS database used to interact with the code of python.
- Using PostgreSQL we can execute any query like DML and DDL by connecting the database using connect method.
- There is no default library of python for interacting with a PostgreSQL database, just as there is no default MySQL library.
- In order to interface with PostgreSQL, we must install a driver of a third party. psycopg2 is a PostgreSQL Python SQL driver. For installing the module of psycopg2, type the following command into our terminal.
pip install psycopg2
- For connecting to the MySQL database we are using the same connection method for connecting to the PostgreSQL database server.
- For connecting to the PostgreSQL database server python program uses the psycopg2. connect method.
- After that, we can use create a connection to connect to a database of PostgreSQL. To begin, we will connect to the default database, Postgres.
- In the below example, we have created the database name as py_sql into the PostgreSQL database.
Code:
import psycopg2
py_my = psycopg2.connect(
host = "localhost",
user = "postgres",
password = "postgres"
)
print ("Connected to PostgreSQL DB")
py_cur = py_my.cursor ()
cr_db = "CREATE DATABASE py_sql"
create_database (py_my, cr_db)
Conclusion
We can connect with a SQLite database using the sqlite3 Python SQL package. Python SQL library data is interacted with by all software applications, most typically via a DBMS. Some programming languages have modules for interacting with a database management system, whilst others need the usage of third-party software.
Recommended Articles
This is a guide to Python SQL Library. Here we discuss the Definition, various Python SQL Library, examples with implementation. You may also have a look at the following articles to learn more –
