Python SQL Database
Posted by Superadmin on April 30 2023 05:14:40

Python SQL Database

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?

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

How to use a python SQL database?

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")

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

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

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

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.