Connect Oracle/MySQL to python

The database is a collection of organized information that can easily be used, managed, updated, and they are classified according to their organizational approach.

so let's get started

Steps to perform to connect with the database

1. Import the package - Database Specific
2. Establish the connection between Python and Oracle (Database)
3. Create Cursor object
4. Execute the SQL Queries
    cursor.execute(sqlquery) -> Single query execution
    cursor.executescript(sqlqueries) -> Queries seperated by ;
    cursor.executemany() -> Execute the parameterized queries
5. Fetch the data
    cursor.fetchone() -> Fetch only one record
    cursor.fetchall() -> Fetch all the records
    cursor.fetchmany(n) -> Fetch n rows
6. commit()
    rollback()
7. cursor.close()
8. con.close()

import the package:-

#!pip install cx_Oracle
import cx_Oracle

Establish the connection between python and oracle (database):-

parameter = your database hostname 
con = cx_Oracle.connect(parameter)
con.version

Create cursor object:-

cursor = con.cursor()

Execute the SQL Queries:-

## Created student table
query = "create table student_details (sid number, stuname varchar2(20), stumarks number, suaddr varchar2(20))"
cursor.execute(query)
cursor.close()
## insertion into the student table
query = "insert into student_details values (100,'Rahul', NULL, 'Gurgaon')"
cursor.execute(query)
con.commit()
cursor.close()
## Delete from student table
query = "delete from student_details"
cursor.execute(query)
con.commit()
cursor.close()

Insert many queries at a time:-

## many record at a time (list of tuple)
records = [(101,'A',97,'Delhi'),(102, 'B', 92, 'Hyd'),(103, 'C',98,'Chennai'),(104, 'D',100, 'Blore')]
query = "insert into student_details values (:sid, :stuname, :stumarks, :stuaddr)"
cursor = con.cursor()
## many record at a time with executemany 
cursor.executemany(query, records)
con.commit()
cursor.close()

Take user input and fill the table:-

This is how you can connect and execute the query from python to your database table.

Thank you for reading !!!

If you enjoy this article and would like to Buy Me a Coffee, please click here.

you can connect with me on Linkedin.