MySQL Databases with Python
Python has powerful database programming features. Python supports a wide range of databases, including SQLite, MySQL, Oracle, Sybase, and PostgreSQL. Python also supports Data Definition Language (DDL), Data Manipulation Language (DML), and Data Query Statements. Python is an ideal companion for MySQL, a popular relational database management system. MySQL is famous for its speed and ease of use.
Note: You must install two components: a MySQL server and a MySQL connector. The MySQL server will provide all of the services required to manage your database. Once the server is operational, you can use MySQL Connector/Python to connect your Python program to it.
Connecting to MySQL Database in Python
Download and install “MySQL Connector”:
-m pip install mysql-connector-python
# To test if the installation was successful, create a Python page with the following content:
import mysql.connector
If this page is executed with no errors, you have the “mysql.connector” module installed.
Start by creating a connection to the database.
Use the username and password from your MySQL database:
import mysql.connector
# Create a connection to the database
mydb = mysql.connector.connect(
host=”localhost”,
user=”myusername”,
password=”mypassword”
)
print(mydb)
Output:
<mysql.connector.connection.MySQLConnection object ar 0x016645F0>
Now you can start querying the database using SQL statements.
Creating a Database
To create a database in MySQL, use the “CREATE DATABASE” statement:
Example:
# Create a database named “mydb2”
import mysql.connector
# Connect to MySQL
mydb = mysql.connector.connect(
host=”localhost”,
user=”myusername”,
password=”mypassword”
)
# Create a cursor
mycursor = mydb.cursor()
# Execute SQL to create a database named “mydb2”
mycursor.execute(“CREATE DATABASE mydb2”)
If this page is executed with no error, you have successfully created a database.
Try connecting to the database “mydb2”:
import mysql.connector
# Connect to the newly created database “mydb2”
mydb = mysql.connector.connect(
host=”localhost”,
user=”myusername”,
password=”mypassword”,
database=”mydb2″
)
If this page is executed with no error, the database “mydb2” exists in your system
Create Table
To create a table in MySQL, use the “CREATE TABLE” statement.
Example:
# Create a table named “customers”:
import mysql.connector
# Connect to the database “mydb2”
mydb = mysql.connector.connect(
host=”localhost”,
user=”myusername”,
password=”mypassword”,
database=”mydb2″
)
# Create a cursor
mycursor = mydb.cursor()
# Execute SQL to create a table “customers”
mycursor.execute(“CREATE TABLE customers (name VARCHAR(255), address VARCHAR(255))”)
If this page is executed with no error, you have successfully created a table named “customers”.
Note: When creating a table, you should also create a column with a unique key for each record. This can be done by defining a PRIMARY KEY.
import mysql.connector
# Connect to the database “mydb2”
mydb = mysql.connector.connect(
host=”localhost”,
user=”yourusername”,
password=”yourpassword”,
database=”mydb2″
)
# Create a cursor
mycursor = mydb.cursor()
# Execute SQL to create a table “customers” with a primary key
mycursor.execute(“CREATE TABLE customers (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255), address VARCHAR(255))”)
The table “customers” now has a primary key
Insert Into Table
Use the “INSERT INTO” statement
import mysql.connector
# Connect to the database “mydb2”
mydb = mysql.connector.connect(
host=”localhost”,
user=”myusername”,
password=”mypassword”,
database=”mydb2″
)
# Create a cursor
mycursor = mydb.cursor()
# SQL statement for inserting data
sql = “INSERT INTO customers (name, address) VALUES (%s, %s)”
values = (“John”, “Highway 21”)
# Execute the SQL statement
mycursor.execute(sql, values)
# Commit the changes
mydb.commit()
# Output the number of records inserted
print(mycursor.rowcount, “record inserted.”)
Conclusion:
Python and MySQL make a great team for handling data. Python, a friendly programming language, works well with MySQL, a powerful database system. Together, they allow developers to easily connect, create databases and tables, and add data. Python’s flexibility and MySQL’s user-friendly features provide a strong base for building applications that deal with information. This quick overview covers the basics, showing how to connect to MySQL, create databases and tables, and insert data. As developers explore more, they’ll find even more ways to use Python and MySQL for managing data effectively.