Python SQL

Definition of Python SQL

Available interfaces and APIs for Python databases use the standard SQL language. Each DB API module we’ve used has been specific to the underlying database. Python’s built-in features are very helpful for database development. Python is compatible with all SQL databases, including MySQL, SQLite, PostgreSQL, etc. Python additionally allows for data manipulation and defining language.

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

Code:

• 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.

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

Indicator

• 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.

:

Product 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 ()

• 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

The SQL database we will be connecting to the python database from is shown below.

2) SQLite Database Management System 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.

Number-Code:

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

Code:

• 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.

pip install mysql-connector-python

Resulting pip install pandas:

To decode:

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

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

W01A3@@7 Number-Format:

Final Product: import mysql.connector
py_my = mysql.connector.connect (
host = "localhost",
user = "root ",
password = "MySQL@123"
)

In the preceding instance, we have specified localhost as the database hostname, root as the database user, and a password for accessing the MySQL server.