shell> pip install mysqlx-connector-python<\/code><\/p>\nInstalling Connector\/Python on Linux with the Help of the MySQL Yum Repository<\/h4>\n
Your system’s repository list must include the MySQL Yum repository. Use the following command to check that your Yum repository is up to date:<\/p>\n
shell> sudo yum update mysql-community-release<\/code><\/p>\nThen, set up Connector\/Python using the following command:<\/p>\n
shell> sudo yum install mysqlx-connector-python<\/code><\/p>\nInstalling Connector\/Python on Linux with the Help of an RPM Package<\/h4>\n
Use the following command in order to install a Connector\/Python RPM<\/em> package. Here it is denoted as PACKAGE.rpm<\/em>:<\/p>\nshell> rpm -i PACKAGE.rpm<\/code><\/p>\nInstalling Connector\/Python on Linux with the Help of a Debian Package<\/h4>\n
Use the following command to install a Connector\/Python Debian<\/em> package. Here it is denoted as PACKAGE.deb<\/em>:<\/p>\nshell> dpkg -i PACKAGE.deb<\/code><\/p>\nHow to install Connector\/Python from source<\/h4>\n
Requirements:<\/strong><\/p>\nAs of Connector\/Python 2.2.3, source distributions come with a C++ Extension that connects to a MySQL server with the X plugin turned on and uses Protobuf as the data interchange format.<\/p>\n
To make the Connector\/Python C++ Extension for Protobuf, you need to meet the following requirements:<\/p>\n
\n- A C\/C++ compiler, like gcc<\/em><\/li>\n
- Protobuf C++ version 5.29.4<\/li>\n
- Python development files<\/li>\n
- MySQL Connector\/C or MySQL Server already installed on your system<\/li>\n<\/ul>\n
How to Install Connector\/Python from Source on Unix and Unix-Like Systems<\/h4>\n
If you want to install Connector\/Python from a tar archive, it is necessary to download the newest version and execute the following commands. Here the newest version is denoted as <version>:<\/p>\n
shell> tar xzf mysqlx-connector-python-<version>.tar.gz
\nshell> cd mysqlx-connector-python-<version>.tar.gz
\nshell> python setup.py install --with-protobuf-include-dir=<protobuf-include-dir> --with-protobuf-lib-dir=<protobuf-lib-dir> --with-protoc=<protoc-binary><\/code><\/p>\nUse this command to see all the options and commands that setup.py supports:<\/p>\n
shell> python setup.py --help<\/code><\/p>\nSteps to connect to the MySQL database using PyMySQL in Python<\/h2>\n
If you want to connect to a MySQL database using the PyMySQL library in Python, the process is quite simple. First, install the library and import it into your script. Then, use your database credentials to open a connection, create a cursor to run your queries, and once you’re done working with the database, close the connection properly.<\/p>\n
Steps to follow:<\/p>\n
\n- \n
Install PyMySQL:<\/h4>\n<\/li>\n<\/ul>\n
Open your terminal or command prompt and install the library using the following pip command.<\/p>\n
pip install PyMySQL<\/code><\/p>\n\n- \n
Import the Library:<\/h4>\n<\/li>\n<\/ul>\n
Import the pymysql module to your Python script, using the following command:<\/p>\n
import pymysql<\/code><\/p>\n\n- \n
Set Up a Connection:<\/h4>\n<\/li>\n<\/ul>\n
In order to set up a connection, you need to use the pymysql.connect()<\/em> method along with your database details. It\u2019s best to use a try-except block to handle connection problems if they pop up.<\/p>\ntry:
\nconnection = pymysql.connect(
\nhost='localhost', # Your MySQL host (e.g., '127.0.0.1' or IP address)
\nuser='yourusername', # Your MySQL username (e.g., 'root')
\npassword='yourpassword', # Your MySQL password
\ndatabase='yourdbname', # The specific database to connect to
\nport=3306, # The MySQL port (default is 3306)
\ncursorclass=pymysql.cursors.DictCursor # Optional: returns results as dictionaries
\n)
\nprint(\"Connected to the MySQL database successfully!\")
\nexcept pymysql.Error as e:
\nprint(f\"Error connecting to MySQL database: {e}\")<\/code><\/code><\/p>\n\n- \n
Make a Cursor Object:<\/h4>\n<\/li>\n<\/ul>\n
It is required to create a cursor object so that you can run your SQL queries and interact with the database.<\/p>\n
cursor = connection.cursor()<\/code><\/p>\n\n- \n
Run SQL Commands:<\/h4>\n<\/li>\n<\/ul>\n
When you want to execute different SQL commands like SELECT, INSERT, UPDATE, or DELETE, it is recommended to use the cursor object\u2019s execute()<\/em> method.<\/p>\n# For Example: Execute a SELECT query
\nsql_query = \"SELECT * FROM your_table_name\"
\ncursor.execute(sql_query)<\/code><\/p>\n# Fetch and print results (optional)
\nresults = cursor.fetchall()
\nfor row in results:
\nprint(row)<\/code><\/p>\n\n- \n
Commit Your Changes (if needed):<\/h4>\n<\/li>\n<\/ul>\n
If you make any changes to your data, such as adding, modifying or deleting some data, it is required that you save them manually. These changes will not be saved automatically. You\u2019ll need to save these changes to the database manually, and for that you\u2019ll need to run the connection.commit()<\/em> method.<\/p>\n\n- \n
Close the Connection:<\/h4>\n<\/li>\n<\/ul>\n
After you finish running your SQL queries, it is recommended to close the connection to the MySQL server.<\/p>\n
How to Connect Python to MySQL: A Step-by-Step Guide<\/h2>\n
The connect()<\/em> constructor is used to establish a connection to a MySQL server. Once the connection is successful, it returns a MySQLConnection object that allows you to interact with the database.<\/p>\nThe following example demonstrates how to connect to a MySQL server.<\/p>\n
import mysql.connector<\/code><\/p>\ncnx = mysql.connector.connect(user='ava', password='password123',
\nhost='127.0.0.1',
\ndatabase='employees_db')
\ncnx.close()<\/code><\/p>\nYou can also create a connection object using the connection.MySQLConnection() class.<\/p>\n
from mysql.connector import (connection)<\/code><\/p>\ncnx = connection.MySQLConnection(user='ava', password='password123',
\nhost='127.0.0.1',
\ndatabase='employees_db')
\ncnx.close()<\/code><\/p>\nBoth approaches, i.e. using the <\/span>connect()<\/span><\/i> constructor or calling the class directly, work the same way.<\/span><\/p>\nYou can manage connection errors by using a try statement and catching them with the errors. Error exception.<\/span><\/p>\nimport mysql.connector
\nfrom mysql.connector import errorcode<\/code><\/p>\ntry:
\ncnx = mysql.connector.connect(user='ava',
\ndatabase='employee')
\nexcept mysql.connector.Error as err:
\nif err.errno == errorcode.ER_ACCESS_DENIED_ERROR:
\nprint(\"The login details you entered are not correct.\")
\nelif err.errno == errorcode.ER_BAD_DB_ERROR:
\nprint(\"The database could not be found.\")
\nelse:
\nprint(err)
\nelse:
\ncnx.close()<\/code><\/p>\nAnother way to do this is by storing the connection arguments in a dictionary and passing them using the ** operator.<\/p>\n
import mysql.connector<\/code><\/p>\nconfig = {
\n'user': 'ava',
\n'password': 'password123',
\n'host': '127.0.0.1',
\n'database': 'employees_db',
\n'raise_on_warnings': True
\n}<\/code><\/p>\ncnx = mysql.connector.connect(**config)<\/code><\/p>\ncnx.close()<\/code><\/p>\nSet up the logger options, create a reconnection routine, and define a connection method called connect_to_mysql.<\/p>\n
import logging
\nimport time
\nimport mysql.connector<\/code><\/p>\n# Setting up logger
\nlogger = logging.getLogger(__name__)
\nlogger.setLevel(logging.INFO)<\/code><\/p>\nformatter = logging.Formatter(\"%(asctime)s - %(name)s - %(levelname)s - %(message)s\")<\/code><\/p>\n# Logging in to console
\nhandler = logging.StreamHandler()
\nhandler.setFormatter(formatter)
\nlogger.addHandler(handler)<\/code><\/p>\n# Also logging in to a file
\nfile_handler = logging.FileHandler(\"cpy-errors.log\")
\nfile_handler.setFormatter(formatter)
\nlogger.addHandler(file_handler)<\/code><\/p>\ndef connect_to_mysql(config, attempts=3, delay=2):
\nattempt = 1
\n# Implement a reconnection routine
\nwhile attempt < attempts + 1:
\ntry:
\nreturn mysql.connector.connect(**config)
\nexcept (mysql.connector.Error, IOError) as err:
\nif (attempts is attempt):
\n# Attempts to reconnect failed; returning None
\nlogger.info(\"Failed to connect, exiting without a connection: %s\", err)
\nreturn None
\nlogger.info(
\n\"Connection failed: %s. Retrying (%d\/%d)...\",
\nerr,
\nattempt,
\nattempts-1,
\n)
\n# progressive reconnect delay
\ntime.sleep(delay ** attempt)
\nattempt += 1
\nreturn None<\/code><\/p>\nHere\u2019s how you can connect to and use the Sakila_db database using the routine above, assuming it is saved in a file called myconnection.py<\/em>.<\/span><\/p>\nfrom myconnection import connect_to_mysql<\/p>\n
config = {
\n\"host\": \"127.0.0.1\",
\n\"user\": \"user\",
\n\"password\": \"pass\",
\n\"database\": \"sakila_db\",
\n}<\/code><\/p>\ncnx = connect_to_mysql(config, attempts=3)<\/code><\/p>\nif cnx and cnx.is_connected():<\/code><\/p>\nwith cnx.cursor() as cursor:<\/code><\/p>\nresult = cursor.execute(\"SELECT * FROM actor LIMIT 5\")<\/code><\/p>\nrows = cursor.fetchall()<\/code><\/p>\nfor rows in rows:<\/code><\/p>\nprint(rows)<\/code><\/p>\ncnx.close()<\/code><\/p>\nelse:<\/code><\/p>\nprint(\"Could not connect\")<\/code><\/p>\nUsing the Connector\/Python or C Extension<\/h2>\n
Connector\/Python comes with two options: a pure Python interface and a C Extension that works with the MySQL C client library. You can configure this during runtime by using the use_pure connection argument. In MySQL 8, this option is set to False by default, which means the C Extension is used automatically. If the C Extension isn\u2019t available on the system, the use_pure option automatically defaults to True. Setting use_pure=False allows the connection to use the C Extension if it is available in your Connector\/Python installation. If the extension is not available, it falls back to the Python implementation.<\/p>\n
The example below shows how you can set use_pure to False:<\/p>\n
import mysql.connector<\/code><\/p>\ncnx = mysql.connector.connect(user='ava', password='password123',
\nhost='127.0.0.1',
\ndatabase='employees_db',
\nuse_pure=False)
\ncnx.close()<\/code><\/p>\nYou can also use the C Extension directly by importing the _mysql_connector module instead of the mysql.connector module.<\/p>\n
Python MySQL Connection Code Example<\/h2>\n
Once you\u2019ve installed the MySQL Connector, it is very easy to connect Python to a MySQL database using a simple script. We’re sharing a working script that opens a database connection, creates a cursor to run queries, and then saves the changes and finally, closes the connection securely.<\/p>\n
import mysql.connector<\/code><\/p>\n# Step 1: Set up the connection to the MySQL server
\nconnection = mysql.connector.connect(
\nhost=\"localhost\",
\nuser=\"your_username\",
\npassword=\"your_password\",
\ndatabase=\"your_database\"
\n)<\/code><\/p>\n# Step 2: Create a cursor for executing SQL queries
\ncursor = connection.cursor()<\/code><\/p>\n# Step 3: Run a sample SQL command (creating a table)
\ncursor.execute(\"\"\"
\nCREATE TABLE IF NOT EXISTS users (
\nid INT AUTO_INCREMENT PRIMARY KEY,
\nname VARCHAR(100),
\nemail VARCHAR(100)
\n)
\n\"\"\")<\/code><\/p>\n# Step 4: Save the changes to the database
\nconnection.commit()<\/code><\/p>\nprint(\"Table created successfully!\")<\/code><\/p>\n# Step 5: Properly Close your cursor and database connection
\ncursor.close()
\nconnection.close()<\/code><\/p>\nHow the Code Works<\/h3>\nConnection Object:<\/h5>\n
A function mysql.connector.connect()<\/em> is used to connect Python to the MySQL database. And for this connection, it requires you to provide some important information, like the host, username, password, and the database name.<\/p>\nCursor Object:<\/h5>\n
The cursor works as a link between Python and the database. Using it you can run SQL queries, like creating a database, inserting and retrieving data.<\/p>\n
Commit:<\/h5>\n
Using the commit()<\/em> method, you can save any changes that are made during the session. Make sure you save your changes otherwise nothing will be saved in the database.<\/p>\nClose:<\/h5>\n
Using the close() method, you can securely close the cursor and database connection. It is a good practice to close connections after completing your tasks. Because it helps to free up server resources and keeps the database running smoothly.<\/p>\n
Output Explanation<\/h5>\n
When the connection details are accurate and the query runs successfully, you\u2019ll see the following output:<\/p>\n
Table created successfully!<\/em><\/p>\nThis shows that the connection between the Python and MySQL database has been established successfully, therefore, it can carry out the SQL command without any issues.<\/p>\n
CRUD Operations in Python with MySQL<\/h2>\n\n- \n
Creating a New Database (CREATE)<\/h3>\n<\/li>\n<\/ul>\n
import mysql.connector<\/code><\/p>\n# Enter your MySQL server details
\ndb_connection = mysql.connector.connect(
\nhost=\"localhost1\",
\nuser=\"rootuser\", #user_name
\npassword=\"root123\" #password
\n)<\/code><\/p>\n# Create a cursor to execute SQL queries
\ncursor = db_connection.cursor()<\/code><\/p>\n# Replace 'your_database_name' with your desired database name
\ncreate_database_query = \"CREATE DATABASE test123_db\"
\ncursor.execute(create_database_query)<\/code><\/p>\n# Commit the changes
\ndb_connection.commit()<\/code><\/p>\n# Close the cursor and the connection
\ncursor.close()
\ndb_connection.close()<\/code><\/p>\nCreating a New Table<\/h3>\n
First of all, connect to MySQL databases, then import the necessary library and connect to your MySQL database. Enter the required information in the respective fields, like your_database name, your-Password, and your_host name.<\/p>\n
When it comes to connecting with MySQL databases with the help of Python, a cursor is the most important element that allows you to run SQL queries and retrieve data easily from the database.<\/p>\n
A cursor works as a link between your Python code and the MySQL database. It makes it possible to carry out different operations like reading, writing, updating, and deleting data.<\/p>\n
import mysql.connector<\/code><\/p>\n# Enter your MySQL database credentials
\ndb_connection = mysql.connector.connect(
\nhost=\"localhost1\",
\nuser=\"roousert\",
\npassword=\"root123\",
\ndatabase=\"test123_db\"
\n)
\n# Create a cursor to run SQL queries
\ncursor = db_connection.cursor()
\n# Example of creating a table
\ncreate_table_query = \"\"\"
\nCREATE TABLE test123_table(
\nid INT AUTO_INCREMENT PRIMARY KEY,
\nname VARCHAR(50),
\nage INT
\n)
\n\"\"\"
\ncursor.execute(create_table_query)
\ndb_connection.commit()<\/code><\/p>\nAdding Data to the Database<\/h3>\n
import mysql.connector<\/code><\/p>\n# Enter your MySQL database credentials
\ndb_connection = mysql.connector.connect(
\nhost=\"localhost1\",
\nuser=\"rootuser\",
\npassword=\"root123\",
\ndatabase=\"test123_db\"
\n)
\n# Create a cursor to execute SQL queries
\ncursor = db_connection.cursor()
\n# Example of inserting data into the table
\ninsert_data_query = \"\"\"
\nINSERT INTO test_table (name, age) VALUES (%s, %s)
\n\"\"\"
\ndata_to_insert = (\"Leo Oliver\", 35)
\ncursor.execute(insert_data_query, data_to_insert)
\ndb_connection.commit()<\/code><\/p>\nModifying the Existing Data<\/h3>\n
Use the UPDATE command\/statement to update\/modify the existing data in the table.<\/p>\n
import mysql.connector<\/code><\/p>\n# Enter your MySQL database credentials
\ndb_connection = mysql.connector.connect(
\nhost=\"localhost1\",
\nuser=\"rootuser\",
\npassword=\"root123\",
\ndatabase=\"test123_db\"
\n)
\n# Create a cursor to execute SQL queries
\ncursor = db_connection.cursor()
\n# Example of updating data in the table
\nupdate_data_query = \"\"\"
\nUPDATE test_table SET age = %s WHERE name = %s
\n\"\"\"
\nnew_age = 35
\nname_to_update = \"Leo Oliver\"
\ncursor.execute(update_data_query, (new_age, name_to_update))
\ndb_connection.commit()<\/code><\/p>\nDeleting Existing Record<\/h4>\n
Use the DELETE statement in order to delete particular data from the database.<\/p>\n
import mysql.connector<\/code><\/p>\n# Enter your MySQL database credentials
\ndb_connection = mysql.connector.connect(
\nhost=\"localhost1\",
\nuser=\"rootuser\",
\npassword=\"root123\",
\ndatabase=\"test123_db\"
\n)
\n# Create a cursor to execute SQL queries
\ncursor = db_connection.cursor()
\n# Example of deleting data from the table
\ndelete_data_query = \"\"\"
\nDELETE FROM test_table WHERE name = %s
\n\"\"\"
\nname_to_delete = \"Leo Oliver\"
\ncursor.execute(delete_data_query, (name_to_delete,))
\ndb_connection.commit()<\/code><\/p>\nPrevent SQL Injection in Python MySQL<\/h2>\nWhat is SQL Injection<\/h4>\n
SQL injection is an abbreviation for SQLi, is a security vulnerability. In this type of attack a malicious code is inserted into a database query to gain unauthorised access to a web application\u2019s database. Attackers use SQL injection techniques to alter SQL queries and run malicious commands, which allow them to access to sensitive data or compromise the database server.<\/p>\n
A successful SQL injection attack can expose sensitive information stored in the database, including intellectual property, admin credentials, and customer data. SQL injection attacks can be used to exploit different types of SQL databases, such as MySQL, SQL Server, and Oracle. In most cases, SQL injection usually attacks target web applications that are connected to a backend database.<\/p>\n
SQL injection is one of the most common ways attackers use to get into databases without any permission. Attackers commonly use this technique with the help of automated tools to carry out a large number of attacks at a time.<\/p>\n
Parameterized queries<\/h4>\n
Attacks by SQL injection can be prevented by implementing parameterised queries by segregating SQL code from the user input data. These queries use placeholders for inputs instead of inserting user inputs directly into the SQL statement in the form of strings. Which stops attackers from altering the structure of the query. At this stage, the database engine securely inserts the provided values into these placeholders, ensuring they are treated only as a data and not as executable commands.<\/p>\n
For example, think about a login system where a person gives their username and password. This is a weak way to write the query:<\/p>\n
SELECT * FROM users WHERE username = 'user_input' AND password = 'user_password';<\/code><\/p>\nIf you put user inputs directly into this string, an attacker could use something like ” OR “1”=”1 as the username, which would lead to:<\/p>\n
SELECT * FROM users WHERE username = '' OR '1'='1' AND password = 'anything';<\/code><\/p>\nThis would get around authentication. Use a parameterized query to stop this from happening. This is what the implementation looks like in many programming languages (for example, in Python using a DB-API):<\/p>\n
query = \"SELECT * FROM users WHERE username = ? AND password = ?\"
\ncursor.execute(query, (username, password))<\/code><\/p>\nIn this case, the username and password are sent as parameters instead of being added to the SQL string. The database driver makes sure they are handled safely.<\/p>\n
Here’s another example that uses named parameters, which you can do in Python with SQLite or in other places:<\/p>\n
query = \"SELECT * FROM users WHERE username = :user AND password = :pass\"
\ncursor.execute(query, {'user': username, 'pass': password})<\/code><\/p>\nMost modern database libraries and frameworks let you use parameterized queries.<\/p>\n
Most modern database libraries and frameworks let you use parameterized queries.<\/h2>\n\n- \n
Avoid Using Insecure Packages<\/h4>\n
The interpreter runs the code when you import a module into a Python app. This means you need to be careful when you import modules.<\/p>\n
The PyPi package index is a great resource, but you can’t be sure that all the code in the libraries it lists is safe. There are a lot of bad packages on PyPi, and some of them try to trick people by using names of well-known libraries with small spelling mistakes.<\/p>\n
If you’re not sure if the outer packaging is real or what it contains, do some more research. If you’re still not sure where it came from or how safe it is, don’t use it.<\/li>\n
- \n
Finding Security Gaps<\/h4>\n
Making a list of security best practices and going over it before releasing your code or moving it to a test environment is the first step in stopping security holes like SQL injections. You should follow these best practices during development and check them automatically during testing. You should use automated tools that check your code at every step of the software development lifecycle (SDLC).<\/li>\n
- \n
Use Linters and Static Analysis Tools to Catch Security Issues<\/h4>\n
Linters are tools that give you automated advice on how to write good code. They are basic static application security testing (SAST) tools that look at source code while a project is being developed. You can use linters by hand in the editor, as part of a local development process, or as part of an automated testing process.Python has a few linters, such as:Pylint: is the main linter for Python. It focuses on bad coding practices, some of which can make your code less secure. But it doesn’t give a lot of security advice.<\/p>\n
Bandit: This tool can help you find common security problems in your Python code. Bandit goes through each file, makes an AST node, and then tests it with the right plugin.<\/p>\n
Python IDEs: Python IDEs like PyCharm and Wingware come with these tools and more will be added. Text editors can also get plugins that help with security.<\/li>\n<\/ol>\n
Error Handling in Python MySQL Connection<\/h2>\n
![\"\"](\"https:\/\/www.host.co.in\/kb\/wp-content\/uploads\/2025\/02\/python-mysql-connection-flow-diagram.png\")
<\/a>