Python SQL Database

Definition of Python SQL Database

Python sql database of MySQL is one of the most widely used DBMSs today. Because almost all software programs require data, programming languages such as python provide storage facilities and access to these data sources. We will be able to integrate a MySQL database with a Python program using multiple approaches. Using SQL databases in python, we can do DDL and DML operations.

What is a python SQL database?

• For database programming, the language of python will include a lot of useful capabilities. For example, MySQL, PostgreSQL, and other databases are all supported by python.
• Python also supports DDL, DML, and other Query Statements. The Python DB-API is the industry standard for database interfaces. This is the standard for most Python database interfaces.
• We have the option of selecting the most appropriate database for our needs. The Python Database API supports a wide number of database servers.
• Using python sql databases, we can easily interact with the database and execute DDL and DML operations per users’ needs.

How to create a python SQL database?

Below are the steps that show how to create a python SQL database. To create a database, we are using execute method.

1) The connect method of MySQL. The connector module establishes a link between the python and MySQL databases. In the method, we are giving the info of hostname, username, and password. If we use the same system for MySQL and python, we need to use the hostname as localhost.

2) To create the database, first, we need to install the MySQL connector in our system. This library will add a MySQL connector module to our python code. The example below shows how to install the MySQL connector package in our system.

pip install mysql-connector

3) After installing the mysql connector module in this step, we create the database using MySQL. We are creating the database name db_server database into the MySQL database server using python code. We use the “create database sql_db” statement to create a database.

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_db")
print ("Database created.")

• In the above example first line, we have imported the MySQL connector module. After importing the module in the second line, we use the connect method to connect the MySQL database to create the database.
• After calling the connect method in the next step, we provide the hostname, username, and password for connecting to the database server.
• After providing connection details in the next step, we create the cursor and its object to execute the command of creating a database on the MySQL DB server through python.
• After creating the object, we execute the create database command using the execute method.

How to use a python SQL database?

• After connecting to the database and creating it into the database server, it is easy to connect to it and execute the DDL and DML commands.
• We can connect the database by giving the database details when creating the connection.
• The example below shows the database details when creating the connection.

Code:

import mysql.connector
py_my = mysql.connector.connect (
host = "localhost",
user = "root",
password = "Mysql@123",
database = "sql_db"
)
print ("Using database name as sql_db")

• In the above example first line, we have imported the mysql connector module. After importing the module in the second line, we use the connect method to connect the MySQL database using the sql_db database.
• After calling the connect method in the next step, we provide the hostname, username, password, and database name for connecting to the database server and using the specified database.
• After specifying all the detail, we have printed that we are using the sql_db database in our python code.
• We can also use the database when executing any SQL statement through python code.
• The example below shows we are using the database when executing the statement by using python code.

Code:

import mysql.connector
py_my = mysql.connector.connect (
host = "localhost",
user = "root",
password = "Mysql@123"
)
py_cur = py_my.cursor ()
py_cur.execute ("select * from db_server.db_table")
print ("Using db_server database.")

Python SQL database functions

• Once we’ve established a database connection, we are ready to run a query against this database. To get a single record or multiple values from a database table, use the fetchone or fetchall functions.
• Below, the functions of python sql are as follows.

1) Fetchone – It gets the next result set of queries using fetchone. So, for example, when we use a cursor object to query a table, we will get a result set as an object.

2) Fetchall – It gets all of the rows with fetchall. It will retrieve the result set remaining row if some rows from the result set have already been extracted.

3) Rowcount – It was an attribute of read-only, which returns the number of rows affected by the execute method.

Programming python SQL database

• In the below example, we are creating the table in the sql_db database. We are creating the table name as db_table.
• In the first line, we have imported the mysql connector module. Also, we are using the connect method to connect the MySQL database server.

Code:

import mysql.connector
py_my = mysql.connector.connect (
host = "localhost",
user = "root",
password = "Mysql@123",
database = "sql_db"
)
py_cur = py_my.cursor ()
py_cur.execute ("CREATE TABLE db_table (stud_name VARCHAR(10), stud_std VARCHAR(10));")
print ("table created.")

• We can also insert records into the MySQL database table using python code. The example below shows that inserting the record into the MySQL database table using python code is as follows.

Code:

import mysql.connector
py_my = mysql.connector.connect (
host = "localhost",
user = "root",
password = "Mysql@123",
database = "sql_db"
)
py_cur = py_my.cursor ()
py_cur.execute ("insert into db_table values ('ABC', '1st');")
print ("Record created.")

Conclusion

We can easily interact with the database using python SQL databases and execute DDL and DML operations per users’ needs. For database programming language of python will include a lot of useful capabilities. MySQL, PostgreSQL, and other databases are all supported by python. Python also supports DDL DML commands.