Scribd
Upload
18 views4 pages

Python SQL Database Integration Guide

The document explains how to connect Python with SQL databases like MySQL and SQLite using connector modules. It outlines the steps for establishing a connection, executing SQL commands, and fetching data, while emphasizing the importance of using parameterized queries with %s placeholders for security against SQL injection. Additionally, it compares the use of %s placeholders and the format() method for inserting dynamic data into SQL queries, highlighting the advantages and disadvantages of each approach.

Uploaded by

subhamsahu770032
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
18 views4 pages

Python SQL Database Integration Guide

The document explains how to connect Python with SQL databases like MySQL and SQLite using connector modules. It outlines the steps for establishing a connection, executing SQL commands, and fetching data, while emphasizing the importance of using parameterized queries with %s placeholders for security against SQL injection. Additionally, it compares the use of %s placeholders and the format() method for inserting dynamic data into SQL queries, highlighting the advantages and disadvantages of each approach.

Uploaded by

subhamsahu770032
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd

Interface of Python with SQL Database

INTRODUCTION
Python can connect to SQL databases such as MySQL, Oracle, SQLite, PostgreSQL etc.
Using Python, we can:
 Insert data
 Update data
 Delete data
 Display (fetch) data
Create database connectivity applications
To connect SQL with Python, we use database connector modules like:
 [Link] (MySQL)
 sqlite3 (SQLite)
STEPS TO CONNECT PYTHON WITH SQL
[Link] the connector module
import [Link]
[Link] connection
con = [Link](
host="localhost",
user="root",
password="",
database=""
)
[Link] Cursor
cur = [Link]()
[Link] SQL commands
[Link]("SQL QUERY")
[Link] ()
[Link]()
[Link] connection
[Link]()
Function Meaning
connect() Connects Python to SQL database
cursor() Creates cursor object to execute
queries
execute() Runs SQL commands
commit() Saves changes permanently in database
fetchone() Fetches only one row
fetchall() Fetches all rows
rowcount Number of rows affected
DISPLAYING DATA
Fetch one row
[Link]("SELECT * FROM students")
row = [Link]()
print(row)
Fetch all rows
[Link]("SELECT * FROM students")
rows = [Link]()
for r in rows:
print(r)
Rowcount
print("Rows found:", [Link])
USING %s PLACEHOLDER (Recommended & Safe)
 %s is used to insert dynamic data in SQL query
 Prevents SQL injection (more secure)
 Values are passed separately → safer for databases.
DIFFERENCE BETWEEN %s AND format()
Point %s Placeholder format()
Safety More secure Less secure
Type SQL parameterized query String formatting
Syntax VALUES(%s, %s) with tuple "VALUES('{}', {})".format()
Best Use Real applications, security Small projects, basic learning

Using format() in Python SQL


 When we connect Python to an SQL database (such as MySQL or SQLite), SQL
commands are written as strings.

To insert values inside those SQL strings, we can use:


 %s placeholder (parameterized – safest)
 .format() method (string formatting)
format()= format() is a Python string method used to insert values inside a string at {}
placeholders.
Example
name = "Soumya"
print("Hello, {}".format(name))
Using format() in SQL Queries
format() inserts values inside SQL query strings.
Example 1: INSERT using format()
import [Link]

con = [Link](
host = "localhost",
user = "root",
password = "",
database = "school"
)

cur = [Link]()

name = "Rahul"
age = 18

query = "INSERT INTO students(name, age) VALUES ('{}', {})".format(name, age)

[Link](query)
[Link]()

print("Record Inserted Successfully")


{} will be replaced by values of name and age.
Example 2: DELETE using format()
roll = 5
query = "DELETE FROM students WHERE roll_no = {}".format(roll)
[Link](query)
[Link]()
print("Record Deleted")
Example 3: UPDATE using format()
new_age = 19
roll = 2

query = "UPDATE students SET age = {} WHERE roll_no = {}".format(new_age, roll)


[Link](query)
[Link]()
print("Record Updated")
Example 4: SELECT using format()
roll = 3
query = "SELECT * FROM students WHERE roll_no = {}".format(roll)
[Link](query)
data = [Link]()
print("Student Details:", data)
Advantages
 Easy way to insert values in SQL strings
 Simple to understand for beginners
Disadvantage
Not as safe as using %s placeholders because it can allow SQL injection if user input is used.

You might also like