🔌

MySQL connectivity

MySQL connectivity with Python is essential for developing database-driven

applications. It allows Python programs to interact with MySQL databases,
enabling data storage, retrieval, and manipulation. This connectivity is crucial
for web applications, data analysis, and backend systems that require
persistent data storage. Python's MySQL connectors, such as mysql-
connector-python, provide a seamless interface between Python code and
MySQL databases, facilitating efficient data management and enhancing
application functionality.

Why python ?
Programming in python is arguably more efficient and faster as compared
to other languages.

Know for its portability and platform-independent ability.

Supports SQL cursors , relational database systems.

Python APIs are compatible with various databases.

Closed and open connection of the database are taken care of by python
without the action from developer side.

Installing mysql-connector-python
pip install mysql-connector-python

OR
python -m install mysql-connector

Establishing connection

MySQL connectivity 1
 import mysql.connector as x #importing with an alias of x

#syntax for connecting to mysql

con=x.connect(host="localhost",user="root",password="<your_
password>")

This snippet initiates connection to mySQL .

Arguments required for connecting to a database:

Username : Username that you use to work with mySQL server. Default
username for mySQL database is root.

Password : the password which is given by the user at the of installing

mysql database.

Hostname : Server name or IP address on which mysql is running. If you are

on local host you can use “localhost” or its IP i.e “127.0.0.0” .

Database name: name of database to which connectivity is to be

established. [OPTIONAL]

To check if the connection is established successfully use the following code:

#con here is the connection object created in the previous

code.
if con.is_connected():
print("Connected")

Creating cursor object

Cursor object is used to execute all the queries we need. Cursor stores all the
data as a temporary container of returned data and allows traversal so that we
can fetch data one row at a time.

#con here is the connection object created in the previous

code.
cursor=con.cursor() #creates a cursor object

MySQL connectivity 2
 Cursors are created using the connection.cursor() method

They are tied to the connection that created them for their entire existence

All database commands executed through the cursor operate within the
context of the database session associated with the connection

In simpler terms, a cursor is like a tool that allows your Python program to
interact with the MySQL database. It's created from your connection to the
database and remains linked to that connection. When you use the cursor to
run commands or queries, these actions take place within the specific database
session you're connected to.

→ Use cursor.excute() to execute SQL queries.

→ Close the cursor object using cursor.close() and close the mySQL connection
using connection.close()

cursor.rowcount: This is a read-only attribute of the cursor object that returns

the number of rows that were affected by the last executed SQL statement. It's
particularly useful for INSERT, UPDATE, or DELETE operations to determine
how many rows were modified.
Here's an illustration of how cursor.rowcount works:

import mysql.connector

conn=x.connect(host="localhost",user="root",password="<your_pa

# Create cursor
cursor = conn.cursor()
# Execute an INSERT statement
cursor.execute("INSERT INTO employees (name, age) VALUES ('Jo
# Get the number of rows affected
rows_affected = cursor.rowcount
#this returns a value of 1 as only one row was inserted.
print(f"Number of rows inserted: {rows_affected}")

conn.commit()

MySQL connectivity 3
 cursor.close()
conn.close()

In this example, after executing the INSERT statement, cursor.rowcount will return
2, as two rows were inserted into the 'employees' table.

Saving changes made

commit() : Saves all changes made in the current transaction to the

database.

import mysql.connector
conn = mysql.connector.connect(user='user', password='passw
ord', host='localhost', database='test')
cursor = conn.cursor()
cursor.execute("INSERT INTO employees (name) VALUES ('Joh
n')")
conn.commit() # Commits the change

rollback() : Undoes all changes made in the current transaction.

cursor.execute("DELETE FROM employees WHERE id=1")

conn.rollback() # Reverts the deletion

autocommit() : Automatically commits every SQL statement if set to True .

conn.autocommit = True # Automatically commit each stateme

nt
cursor.execute("INSERT INTO employees (name) VALUES ('Alic
e')")
# No need for conn.commit()

Read operations in mySQL

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

MySQL connectivity 4
 cursor.execute("SELECT * FROM employees")
row = cursor.fetchone() # Fetches one row
print(row)

fetchall() : Retrieves all rows from the result set and returns a list of tuples.
If some rows have been already extracted from the result set, then it
retrieves the remaining rows from result set.

cursor.execute("SELECT * FROM employees")

rows = cursor.fetchall() # Fetches all rows
print(rows)

fetchmany(size) : Retrieves the specified number of rows from the result set
as a list of tuples. The default size is 1; if there are no rows in the result set,
[] is returned.

cursor.execute("SELECT * FROM employees")

rows = cursor.fetchmany(3) # Fetches 3 rows
print(rows)

💡 Note : A result set refers to the collection of rows (data) returned by a

SELECT query in a database. When you execute a query that retrieves
data, the result set holds the records (rows) that match the query
conditions.

MySQL connectivity 5