Khóa học miễn phí MySQL – Python Syntax nhận dự án làm có lương

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()

Khóa học lập trình tại Toidayhoc vừa học vừa làm dự án vừa nhận lương: Khóa học lập trình nhận lương tại trung tâm Toidayhoc