MySQL - Python Syntax

Table of content

The MySQL-Python connector specifically refers to a library in Python that enables communication between a Python program and a MySQL database. It acts as a bridge, allowing Python programs to interact with and manipulate data stored in a MySQL database. Essentially, the MySQL-Python connector simplifies the process of connecting, querying, and managing databases, enabling developers to seamlessly integrate their Python applications with MySQL databases.

Installing "python-mysql" connector

To use MySQL with Python, you typically need to install a MySQL connector or library. Here are the general steps to install it −

Step 1: Install MySQL Server

Make sure you have MySQL Server installed on your machine or have access to a remote MySQL server.

Step 2: Install MySQL Connector for Python

Open a command prompt or terminal and use the following command to install the MySQL Connector for Python using pip, which is the package installer for Python:

pip install mysql-connector-python

If you are using Python 3, you might need to use 'pip3' instead of 'pip'.

Step 3: Verify Installation

After the installation is complete, you can verify that the library is installed by opening a Python interactive shell and trying to import the connector:

import mysql.connector

Python Functions to Access MySQL

When working with MySQL in Python, the 'mysql-connector-python' library provides various functions to interact with a MySQL database. Here are some important functions commonly used −

S.No Function & Description
1

connect()

Establishes a connection to the MySQL server.
2

cursor()

Creates a cursor object to execute SQL queries.
3

execute(query, params=None)

Executes a SQL query. 'params' is an optional parameter for query parameters.
4

fetchone()

Fetches the next row from the result set.
5

fetchall()

Fetches all rows from the result set.
6

commit()

Commits the current transaction to the database.
7

rollback()

Rolls back the current transaction, undoing any changes since the last commit.
8

close()

Closes the cursor and the connection to the database.
9

executemany()

Executes a SQL command against all parameter sequences in the provided list.

Basic Example

To connect and communicate with a MySQL database using Python, you can follow these steps −

  • Use 'pip install mysql-connector-python' to install the MySQL Connector for Python.
  • Import the MySQL Connector module in your Python script: "import mysql.connector".
  • Create a connection using "mysql.connector.connect()" with your database details.
  • Create a cursor using "connection.cursor()".
  • Use the cursor's "execute()" method to run SQL queries.
  • If applicable, use "fetchone()" or "fetchall()" to retrieve query results.
  • If you modify data, commit changes using "connection.commit()".
  • Close the cursor and connection with "cursor.close()" and "connection.close()".

The following example shows a generic syntax of a Python program to call any MySQL query −

import mysql.connector
# Establish connection
connection = mysql.connector.connect(host='localhost', user='user', password='pass', database='db')
# Create cursor
cursor = connection.cursor()
# Execute query
cursor.execute("SELECT * FROM table")
# Fetch and print results
rows = cursor.fetchall()
print(rows)
# Close cursor and connection
cursor.close()
connection.close()
Advertisements