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.
- 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.
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.
- 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.
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.
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.
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)
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.
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 –