Python SQL

Definition of Python SQL

Python SQL is database interfaces that are currently available: Interfaces and APIs for Python databases. Every database which was we have accessed requires its own DB API module. For database programming, python includes a lot of useful capabilities. All the SQL databases are supported by Python-like MySQL, SQLite, PostgreSQL, etc. Python also supports data definition language and data manipulation language.

What is Python SQL?

• The Python DB-API is the industry standard for database interfaces. This is the standard for most Python database interfaces.
• SQL can be used in a variety of ways in Python. For this reason, a number of libraries have been built that can be used. These libraries include SQLite and MySQL.
• Python’s database interfaces employ the Python DB-API, which is the standard used for the interface of the database. The DB-API is a standard interface for accessing relational databases. In other words, regardless of the database or database module used, the Python code for connecting databases is the same.

Create a Table Python SQL

• To create a table in the SQLite database first we need to import the SQLite module in our code. In the below example, we are creating a table in python by using the SQLite database as follows.
• After importing the module then, using the connect method, make the connection with the database at the time of making a connection with the database we need to pass the database name which we need to access if a file with that name already exists, it will be opened. If the specified filename does not exist, the python library will create the file with the same name.
• After calling connect method and specifying the name of the database in the next step we are creating a cursor object, this object is used to send the commands.
• The below example shows connect to the SQLite database in python as follows.

Code:

import sqlite3
py_con = sqlite3.connect ("py_sql.db")
py_csr = py_con.cursor ()
print (" Connected SQLite database using python")

Output:

• In the above example, we can see that first, we have imported the sqlite module. After importing the module then we have created the connection object to connect the SQLite database. After creating a connection object, we are creating the cursor object for sending SQL query or commands.
• The object of the cursor establishes a connection for running SQL queries. It serves as a bridge between the SQLite database and the SQL query. It’s formed once we have established a connection to an SQLite database.
• The object of the cursor is used to establish a connection for SQL query execution. It serves as a bridge between the SQLite database and the SQL query. It’s formed once we have established a connection to an SQLite database.
• The cursor object is nothing but the structure of control for traversing and retrieving database records.
• After connecting to the SQLite database and creating the cursor object in the below example we are creating the table in the database.
• To create a table in the SQLite database first we need to create an object and need to write create table SQL query in it with it comments to execute a query in the database.
• It’s also incredibly simple to use the command. In python using the SQLite database, we can execute the command, for executing or calling the execute method.
• Execute a set of commands saved as sql commands. After we have completed all of our tasks, commit our modifications to the file and then disconnect.
• The below example shows creating a table in the SQLite database using python as follows. For creating a table in python we are using standard SQL command.

Code:

import sqlite3
py_con = sqlite3.connect("py_sql.db")
py_csr = py_con.cursor()
print ("Connected SQLite database using python")
sql_py = """CREATE TABLE stud_py (
stud_no INTEGER PRIMARY KEY,
stud_fname VARCHAR (10),
stud_lname VARCHAR (20),
stud_address CHAR (40),
joining DATE);"""
py_csr.execute (sql_py)
print ("stud_py table created")
py_con.close ()

Output:

• In the above example, we have created a table name as stud_py. We have created a connection object as py_con as well as we have created a cursor object as py_csr, sql_py is an SQL command object which was used to execute the command using the cursor object.

Python SQL Databases

Below is the SQL database from which we can connect to the python database by using programs as follows.

1) SQLite
2) MySQL
3) PostgreSQL
4) Oracle

• We don’t need to install any other Python SQL modules to connect the SQLite database, it’s probably easy to connect with the python project. We can connect with an SQLite database using the sqlite3 Python SQL package that comes with our Python installation.
• The below example shows connect to the SQLite database and create the py_test database as follows.

Code:

import sqlite3
py_con = sqlite3.connect ("py_test.db")
py_test = py_con.cursor ()
print (" Connected to py_test DB")

Output:

• In the above example, we have created a connection object as py_con as well as we have created a cursor object as py_test.
• To connect the SQLite database, we have no need to install any libraries because SQLite by default comes with python.
• To connect the MySQL database, we need to install MySQL-connector dependency are as follows.
• The below example shows the installation of MySQL-connector dependency to connect the MySQL database.

Code:

pip install mysql-connector-python

pip install pandas

Output:

• The below example shows import MySQL-connector to connect the MySQL database through python. Also, we are importing pandas with mysql-connector as follows.

Code:

import mysql.connector
from mysql.connector import Error
import pandas as py

Output:

• For connecting to the MySQL database server, we need to install the MySQL database server in our system. After installing the MySQL server, we need to run the below code in python for connecting to the MySQL database server.

Code:

import mysql.connector
py_my = mysql.connector.connect (
host = "localhost",
user = "root ",
password = "[email protected]"
)

Output:

In the above example, we have given the database hostname as localhost, the database username as root, and given a password for connecting to the MySQL database server.

Conclusion

The Python DB-API is the industry standard for database interfaces. This is the standard for most Python database interfaces. Every database which was we have accessed requires its own DB API module. Python SQL list is database interfaces that are currently available, Interfaces, and APIs for Python databases.

Recommended Articles

This is a guide to Python SQL. Here we discuss the Definition, What is python SQL, Create a table of python SQL, and examples with code implementation. You may also have a look at the following articles to learn more –

1. f String in Python
2. Python Reduce
3. Timsort Python
4. Python User Defined Exception