Comprehensive Study Notes on Python-MySQL Connectivity

Overview of Python – MySQL Connectivity

• In real-life applications, programs frequently encounter conditions requiring interaction with databases stored in various Relational Database Management Systems (RDBMS).

• Just like other programming languages, Python allows users to establish connections to different databases to access, manipulate, and manage the information stored within them.

• Connectivity allows Python to perform operations such as data retrieval, addition, and modification within a MySQL environment.

• To establish this connection, a separate library called mysql.connector must be installed in CPython.

• Once mysql.connector is installed, its presence can be verified using the Python IDLE script mode by attempting to import the module.

Built-In Functions of mysql.connector

The mysql.connector library module offers several functions for database programming, categorized by their specific tasks:

• Establishment & Verification Functions:

  • .connect(): Establishes the link between Python and MySQL.

  • .is_connected(): Verifies if the connection is active.

• Execution & Navigation Functions:

  • .cursor(): Creates a cursor object to execute queries.

  • .execute(): Executes a specific SQL query string.

• Recording Accessing Functions (Retrieving Data):

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

  • .fetchone(): Retrieves a single record from the result set.

  • .fetchmany(): Retrieves a specific number of records from the result set.

  • .rowcount: A property that indicates the number of rows retrieved or affected.

• Transaction Control Functions:

  • .commit(): Saves changes permanently to the database.

  • .rollback(): Reverts changes made during the current transaction.

• Termination Function:

  • .close(): Terminates the connection and releases resources.

The .connect() Function and Connection Parameters

• The .connect() function is the primary tool for establishing a connection between a MySQL database and Python.

• It successfully connects to a database and stores the connection information in a variable known as a connection object. This object acts as the gateway for all subsequent transactions.

• Parameters of .connect():

  • host: Refers to the computer name or the IP address where the MySQL database is located. For local databases, this is often "localhost".

  • user: Refers to the MySQL username. The default username is often "root".

  • passwd: Refers to the password required to access the MySQL database.

  • database: Refers to the name of the specific valid MySQL database being accessed.

• Import Syntax Example:

  • import mysql.connector as MYCON

  • MYCON.connect(host, user, passwd, database)

  • In this instance, MYCON is used as an alias for mysql.connector to simplify code.

Connection Validation and Error Handling

• Python uses try...except: blocks to handle connectivity errors. If any parameter provided to the .connect() function is incorrect, Python raises an error.

• Validating connectivity ensures that the program does not crash and can provide customized error messages or "guard code" for recovery.

• Common Error Scenarios involving a target database named "PythonDB" with password "12345":

  • Invalid Host: Providing host='local host' (with a space) will raise an error because the host name does not exist.

  • Invalid User: Providing user='roo' instead of user='root' will fail as it is not a valid username.

  • Invalid Password: Providing passwd='1245' instead of passwd='12345' will result in a connection failure.

  • Invalid Database Name: Providing database='PythoDB' instead of database='PythonDB' will fail as the database does not exist.

• Use of the .is_connected() function: This function accepts no arguments and returns a boolean value. It returns True if the connection is successful and currently active, otherwise it returns False.

The Database Cursor (.cursor())

• Definition: In computer science, a database cursor is a control structure used to enable traversal over records in a database.

• Functionality: Cursors facilitate processing tasks such as retrieval, addition, and removal of database records.

• Metaphor: A cursor is akin to an "iterator" or a loop in programming languages, allowing for individual row processing from a result set.

• In Python: The .cursor() function is invoked through the connection object. Its details are stored in a cursor variable (object) used to execute DDL (Data Definition Language) and DML (Data Manipulation Language) queries.

• Example Syntax: MYCurObj = MYCON.cursor()

  • MYCurObj is the cursor variable.

  • MYCON is the existing connection object.

  • This specific cursor will only execute commands on the database specified in the MYCON connection (e.g., "PythonDB").

Executing Queries via .execute()

• Once a cursor is initialized, the .execute() function is used to run SQL commands like CREATE, ALTER, DROP, INSERT, UPDATE, SELECT, and DELETE.

• It accepts a string argument representing the SQL query.

• Transaction Logic: Changes made by commands like INSERT, UPDATE, and DELETE are temporary. They are not saved to the physical database until the .commit() function is called through the connection object.

• Reverting Changes: Changes can be undone using the .rollback() function of the connection object. This applies strictly to DML transactions.

• Note on DDL: Transactions like CREATE, ALTER, and DROP are generally non-reversible via rollback() in standard MySQL transactions; however, they require careful execution as they modify the database schema permanently.

Static vs. Dynamic Queries

Queries can be specified in two distinct ways:

• Static Queries (Fixed/Constant): The query is hardcoded.

  • Example: "DELETE FROM Members WHERE Mno='102'" will always delete record $102$.

• Dynamic Queries (Parameterized): These allow users to supply different values at runtime. There are two primary methods in Python to create these:

1. Using the % Operator (Old Method)

• Uses %s as a placeholder within the query string.

• Syntax: StringFormat % (ValuesList)

• The order of values in the list must exactly match the order of %s placeholders in the string.

• Limitation: Cannot reuse values from the list. If a value is needed twice, it must be provided twice in the list.

• Example:

  • MNo=103, MName="RONAK", MAge=19

  • S = "Insert Into Member Values ('%s','%s','%s')" % (Mno, Mname, MAge)

  • Final String: "Insert Into Member Values ('103','RONAK','19')"

2. Using the .format() Function (New Method)

• Uses {} or {Index} as placeholders.

• Syntax: String.format(ValuesList)

• Sequence Based: "Values ('{}','{}')".format(val1, val2) maps values in order.

• Index Based: "Values ('{0}','{1}','{0}')".format(val1, val2) allows reusing val1 multiple times by referencing index $0$.

• Variable Based: "Values ('{X}','{Y}')".format(X=val1, Y=val2) uses named placeholders.

• Example of Reuse:

  • S = "Update Members Set MNo='{0}', Mname='{1}' Where Mno='{0}'".format(Mno, Mname)

Accessing Records (The Result Set)

When a SELECT command is executed, Python retrieves a "result set" as a list of tuples. This set can be accessed using specific cursor methods:

• .fetchone():

  • Fetches exactly one record/tuple.

  • Useful for gradual, one-by-one access.

  • Returns None if no more records exist.

• .fetchall():

  • Fetches all records from the result set at once as a list of tuples.

  • If called again after all records have been fetched, it will raise an error or return an empty list depending on the state.

  • Often processed using a for loop to traverse the list.

• .fetchmany(Size):

  • Fetches the number of records specified by the Size integer argument.

  • If fewer records remain than the requested Size, it fetches the remaining records.

  • Returns an empty list if called after all records are already fetched.

Sequential Record Retrieval Logic

Retrieval methods can be combined, and they always fetch from the current "active" record position:

• fetchone() followed by fetchmany(2): First $1$ record is fetched, then the next $2$ are fetched, totaling $3$ records.

• fetchmany(2) followed by fetchone(): First $2$ records are fetched, then the $3^{rd}$ record is fetched.

• fetchone() followed by fetchall(): First $1$ record is fetched, then all remaining records are fetched.

• fetchall() followed by fetchone(): fetchall() retrieves everything; a subsequent fetchone() will return None as no records are left.

• Accessing by Index: Because results are returned as tuples or lists of tuples, individual fields can be accessed using forward or backward indices (e.g., X[0]).

The .rowcount Property

• Definition: An additional property that returns an integer indicating the total number of records fetched from the cursor object thus far.

• Initial State: If the .execute() function has run but no records have been fetched yet, .rowcount returns $-1$.

• Dynamic Counting Examples:

  • After CUR.fetchone(): .rowcount returns $1$.

  • After CUR.fetchmany(2): .rowcount returns $2$.

  • After CUR.fetchall() (assuming $4$ total records): .rowcount returns $4$.

  • After combining fetchmany(2) and fetchone(): .rowcount returns $3$.

Terminating the Session

• .close(): This is the final function to be invoked. It closes the connection opened by .connect().

• Purpose: To ensure all resources occupied by Python and MySQL are released.

• Prerequisite: Ensure .commit() has been called if any DML changes need to be saved before closing.

• Syntax: ConnObject.close() where ConnObject is the variable storing the connection.

• Example: CN.close() terminates the connection established in connection variable CN.

Summary Diagram of interaction

• Python Program interacts with a CURSOR.

• CURSOR performs operations (execute, fetchone, fetchall, close, insert, delete, update) on the Connection.

• Connection links to the Database, which houses Tables, Members, Courses, and Users.