Python- Connect to a MySQL Database with examples
Today in this article, we will see an example with Python-Connect to MySQL Database.
We will see different techniques for connecting to MySQL using Python.
- Using pymysql – discussed here
- Using MySQL connectors – Please see this article for more details.
However, this article will focus on how to use PyMySQL which is MySQL client library.
Installing PyMySQL using CLI
python3 -m pip install PyMySQL
----- Installing 'pip install pymysql' -----
Collecting pip
Using cached pip-22.3-py3-none-any.whl (2.1 MB)
Collecting install
Using cached install-1.3.5-py3-none-any.whl (3.2 kB)
Collecting pymysql
Using cached PyMySQL-1.0.2-py3-none-any.whl (43 kB)
Installing collected packages: pip, install, pymysql
Attempting uninstall: pip
Found existing installation: pip 20.1.1
Uninstalling pip-20.1.1:
Successfully uninstalled pip-20.1.1
Successfully installed install-1.3.5 pip-22.3 pymysql-1.0.2
----- Successfully installed 'pip install pymysql' -----
To use “sha256_password” or “caching_sha2_password” for authenticate,
python3 -m pip install PyMySQL[rsa]
Installing PyMySQL using Requirments.txt
Please visit below article,
Prerequisites requirements
- Python – one of the following:
- CPython >= 3.6
- Latest PyPy 3
- MySQL Server – one of the following:
- MySQL >= 5.6
- MariaDB >= 10.0
Step 1: Adding import statements
Adding import statement to Python,
import pymysql.cursors
Step 2: Connect to the database
Connect to the database,
connection = pymysql.connect(host='localhost',
user='user',
password='passwd',
database='db',
cursorclass=pymysql.cursors.DictCursor)
If you are using RSA based connect then kindly check refer article for more details, Python RSA Key pair Encryption and Decryption with examples
Step 3: Read from the database
Let’s perform read operations on the MySQL Database,
with connection.cursor() as cursor:
# Read a single record
sql = "SELECT `College`, `Name` FROM `Employee` WHERE `ID`=%s"
cursor.execute(sql)
result = cursor.fetchone()
print(result)
Step 4: Read all tables from MySQL database using Python
Read All tables from MYSQL
try:
with connection.cursor() as cursor:
# Read tables
sql = "SHOW TABLES"
cursor.execute(sql)
result = cursor.fetchone()
print(result)
finally:
connection.close()
Example:
Do you have any comments or ideas or any better suggestions to share?
Please sound off your comments below.
Happy Coding !!
Please bookmark this page and share it with your friends. Please Subscribe to the blog to receive notifications on freshly published(2024) best practices and guidelines for software design and development.
This works in command line, but not in requirements.txt. Suggestions?
Hello Val Huber- Thanks for your queries. Are you using using PyMySQL with RSA ? To install PyMySQL with RSA support using requirements.txt, you’ll need to include both the PyMySQL package and the cryptography package.
Please visit How to install Python PyMySQL with RSA Keys for more details.