CBSE 12th Unit 3-Chapter -4 DATABASE MANAGEMENT AND DATABASE CONCEPTS

 CHAPTER – 4

1. Connecting to an SQL Database

• Import the pyodbc module: This module provides an interface for connecting to various database systems like SQL Server, MySQL, Oracle, etc.

Python

import pyodbc

• Establish a connection: Use the connect() method to create a connection to the database. You'll need to provide the connection string with details like server name, database name, username, and password.

Python

conn = pyodbc.connect('Driver={SQL Server};'

                      'Server=your_server_name;'

                      'Database=your_database_name;'

                      'UID=your_username;'

                      'PWD=your_password;')

2. Creating a Cursor

• Create a cursor object using the cursor() method. The cursor is used to execute SQL statements.

Python

cursor = conn.cursor()

3. Performing SQL Queries

• Insert:

Python

sql = "INSERT INTO Students (StudentID, FirstName, LastName) VALUES (?, ?, ?)"

cursor.execute(sql, (101, 'Alice', 'Wonderland'))

conn.commit()  # Commit the changes to the database

• Update:

Python

sql = "UPDATE Students SET Age = 20 WHERE StudentID = 101"

cursor.execute(sql)

conn.commit()

• Delete:

Python

sql = "DELETE FROM Students WHERE StudentID = 101"

cursor.execute(sql)

conn.commit()

4. Fetching Data

• fetchone(): Retrieves the next row from the result set.

Python

cursor.execute("SELECT * FROM Students")

row = cursor.fetchone()

print(row)

• fetchall(): Retrieves all rows from the result set.

Python

cursor.execute("SELECT * FROM Students")

rows = cursor.fetchall()

for row in rows:

    print(row)

• rowcount: Returns the number of rows affected by the last executed statement.

Python

cursor.execute("DELETE FROM Students WHERE StudentID = 101")

print(cursor.rowcount, "row(s) deleted.")

5. Using %s Format Specifier or format()

• %s Format Specifier:

Python

student_id = 101

name = 'Bob'

sql = "INSERT INTO Students (StudentID, FirstName) VALUES (%s, %s)"

cursor.execute(sql, (student_id, name))

• format() Method:

Python

student_id = 101

name = 'Bob'

sql = "INSERT INTO Students (StudentID, FirstName) VALUES ({}, {})"

cursor.execute(sql.format(student_id, name))

6. Closing the Connection

• Close the cursor and the database connection to release resources.

Python

cursor.close()

conn.close()

Diagram: Python-SQL Interaction

 

diagram showing the flow of data between a Python script and an SQL database, including connection, cursor, SQL queries, and data retrieval

Key Points for Students

• Python provides robust libraries like pyodbc to interact with various databases.
• Understanding how to connect, execute queries, and fetch data is crucial for database programming.

Using parameterized queries (like with %s or format()) helps prevent SQL injection vulnerabilities.