Python 3 – MySQL Database Access
MySQL is a widely used relational database management system that allows you to store and retrieve data in a structured manner. In this article, we’ll explore how to use Python 3 to connect to a MySQL database and perform basic database operations such as create, read, update and delete (CRUD) on its tables.
MySQL Connector/Python
Before we proceed any further, we need to install the MySQL Connector/Python package which provides an interface for Python to interact with the MySQL database. To install it, we can use the pip command:
pip install mysql-connector-python
With the MySQL Connector/Python installed, we can now proceed to connecting to the database.
Connecting to the Database
To connect to a MySQL database using Python 3, we need to provide the database credentials such as the host, username, password, and the name of the database we want to connect to. Here’s an example of connecting to a MySQL database and displaying basic server information:
import mysql.connector
mydb = mysql.connector.connect(
host="localhost",
user="root",
password="password",
database="mydatabase"
)
print(mydb)
The output of the above code will display the details of the database connection such as the MySQL server version number.
Creating a Table
Once we’re connected to the MySQL database, we can create a table to store data in. To create a table, we need to define the table name and its columns along with their data types using SQL commands. Here’s an example of creating a table called customers:
import mysql.connector
mydb = mysql.connector.connect(
host="localhost",
user="root",
password="password",
database="mydatabase"
)
mycursor = mydb.cursor()
mycursor.execute("CREATE TABLE customers (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255), address VARCHAR(255))")
print("Table created successfully!")
The above code creates a table called customers with three columns: id, name, and address.
Inserting Data
Once the table is created, we can insert data into it using the SQL INSERT statement. Here’s an example of inserting data into the customers table:
import mysql.connector
mydb = mysql.connector.connect(
host="localhost",
user="root",
password="password",
database="mydatabase"
)
mycursor = mydb.cursor()
sql = "INSERT INTO customers (name, address) VALUES (%s, %s)"
val = ("John Doe", "123 Main St, Anytown USA")
mycursor.execute(sql, val)
mydb.commit()
print("Data inserted successfully!")
The above code inserts a single row of data into the customers table.
Reading Data
Once we have data in the database, we can read it using the SQL SELECT statement. Here’s an example of selecting all the records from the customers table:
import mysql.connector
mydb = mysql.connector.connect(
host="localhost",
user="root",
password="password",
database="mydatabase"
)
mycursor = mydb.cursor()
mycursor.execute("SELECT * FROM customers")
result = mycursor.fetchall()
for row in result:
print(row)
The above code reads all the records from the customers table and prints them one row at a time.
Updating Data
Once we have data in the database, we can also update it using the SQL UPDATE statement. Here’s an example of updating a record in the customers table:
import mysql.connector
mydb = mysql.connector.connect(
host="localhost",
user="root",
password="password",
database="mydatabase"
)
mycursor = mydb.cursor()
sql = "UPDATE customers SET address = %s WHERE name = %s"
val = ("456 Oak St, Anytown USA", "John Doe")
mycursor.execute(sql, val)
mydb.commit()
print(mycursor.rowcount, "record(s) updated!")
The above code updates the address of the customer with the name “John Doe”.
Deleting Data
Once we have data in the database, we can also delete it using the SQL DELETE statement. Here’s an example of deleting a record from the customers table:
import mysql.connector
mydb = mysql.connector.connect(
host="localhost",
user="root",
password="password",
database="mydatabase"
)
mycursor = mydb.cursor()
sql = "DELETE FROM customers WHERE name = %s"
val = ("John Doe",)
mycursor.execute(sql, val)
mydb.commit()
print(mycursor.rowcount, "record(s) deleted!")
The above code deletes the customer with the name “John Doe” from the customers table.
Conclusion
In this article, we explored how to use Python 3 to interact with MySQL databases. We learned how to connect to a database, create a table, and perform basic CRUD operations on its records. We hope this article was informative and helps you in using Python 3 with MySQL databases.