Python – Database Connection

 Python can connect to databases like MySQL, SQLite, PostgreSQL, Oracle etc.

Database connectivity allows us to:

  • Connect to database

  • Create tables

  • Insert data

  • Update data

  • Delete data

  • Read data

  • Control transactions

  • Disconnect properly

For example, we commonly use MySQL with Python using the connector:

pip install mysql-connector-python

 1. Database Connection

To connect Python with MySQL database:

import mysql.connector

conn = mysql.connector.connect(
    host="localhost",
    user="root",
    password="yourpassword",
    database="college"
)

print("Connected Successfully")

If connection is successful, it will print message.

 2. Creating Cursor Object

Cursor is used to execute SQL queries.

cursor = conn.cursor()

Cursor acts as a bridge between Python and database.

 3. Creating Table

create_table_query = """
CREATE TABLE IF NOT EXISTS students (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50),
    marks INT
)
"""

cursor.execute(create_table_query)
print("Table Created Successfully")

 4. Insert Operation

insert_query = "INSERT INTO students (name, marks) VALUES (%s, %s)"
values = ("Arun", 85)

cursor.execute(insert_query, values)
conn.commit()   # Save changes

print("Data Inserted Successfully")

commit() is necessary to save changes permanently.

 5. Read (Select) Operation

cursor.execute("SELECT * FROM students")

records = cursor.fetchall()

for row in records:
    print(row)

fetchall() retrieves all records.

 6. Update Operation

update_query = "UPDATE students SET marks=%s WHERE name=%s"
values = (90, "Arun")

cursor.execute(update_query, values)
conn.commit()

print("Data Updated Successfully")

7. Delete Operation

delete_query = "DELETE FROM students WHERE name=%s"
values = ("Arun",)

cursor.execute(delete_query, values)
conn.commit()

print("Data Deleted Successfully")

8. Transaction Control

Transaction control ensures data integrity.

Important methods:

  • commit() → Save changes

  • rollback() → Undo changes

Example:

try:
    cursor.execute("INSERT INTO students (name, marks) VALUES ('Ravi', 75)")
    conn.commit()
except:
    conn.rollback()
    print("Transaction Failed")

If error occurs, rollback() cancels the changes.

9. Closing Connection (Disconnection)

After completing operations, we must close the connection.

cursor.close()
conn.close()

print("Database Disconnected")

Closing connection releases database resources.

10. Complete Example Program

import mysql.connector

conn = mysql.connector.connect(
    host="localhost",
    user="root",
    password="yourpassword",
    database="college"
)

cursor = conn.cursor()

# Create table
cursor.execute("""
CREATE TABLE IF NOT EXISTS students (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50),
    marks INT
)
""")

# Insert
cursor.execute("INSERT INTO students (name, marks) VALUES (%s, %s)", ("Kumar", 88))
conn.commit()

# Read
cursor.execute("SELECT * FROM students")
for row in cursor.fetchall():
    print(row)

# Update
cursor.execute("UPDATE students SET marks=95 WHERE name='Kumar'")
conn.commit()

# Delete
cursor.execute("DELETE FROM students WHERE name='Kumar'")
conn.commit()

# Close
cursor.close()
conn.close()

 Advantages of Python DB Connectivity

✔ Automates database operations
✔ Used in Web Applications
✔ Used in Banking, College, Hospital systems
✔ Secure and efficient

Conclusion

Python Database Connectivity allows performing full CRUD operations:

  • Create

  • Read

  • Update

  • Delete

It also supports transaction control and proper disconnection to maintain database integrity.

Comments

Post a Comment

Popular posts from this blog

Functions in Python

 A function is a block of reusable code that performs a specific task. Functions help to avoid repetition and make programs organized and modular. In Python, functions are created using the def keyword. Advantages of using functions: Code reusability Easy debugging Better readability Reduces code length Improves program structure Defining a Function A function is defined using the def keyword followed by function name and parentheses. Syntax: def function_name (): statements Example: def greet (): print ( "Hello World" ) greet () Function with Parameters Parameters are values passed to a function. Syntax: def function_name ( parameter ): statements Example: def greet ( name ): print ( "Hello" , name ) greet ( "Kumar" ) Function with Return Value The return statement sends a value back to the caller. Syntax: def function_name (): return value Example: def add ( a , b ): return a + b ...
Read more

Tkinter -Python

  Introduction to Tkinter (Python) Tkinter is the standard GUI (Graphical User Interface) library in Python . It is used to create desktop applications with graphical elements like windows, buttons, labels, text boxes, menus, and dialogs . Tkinter is built on top of the Tk GUI toolkit and is included with most Python installations, so it does not require separate installation. It allows programmers to easily develop interactive applications using Python. Features of Tkinter Simple and easy to learn GUI library in Python. Provides various widgets such as Button, Label, Entry, Text, Frame, Menu, Canvas, etc. Supports event-driven programming. Cross-platform (works on Windows, macOS, and Linux). Useful for creating small desktop applications and GUI tools. Basic Example Program from tkinter import * # Create main window root = Tk () root . title( "My First Tkinter Program" ) # Create label label = Label ( root , text = "Hello Welcome to Tkinte...
Read more