Python is a powerful programming language that is widely used for various purposes, from web development to data analysis. One of the key features of Python is its ability to connect to various databases, including MySQL.
MySQL is a popular open-source relational database management system that is widely used for storing and managing data. In this article, we will explore how to connect to a MySQL database in Python and perform basic operations, such as creating tables and inserting data.
Before we get started, make sure you have MySQL installed on your system and have a basic understanding of Python. Let's dive in!
Connecting to a MySQL Database
To connect to a MySQL database in Python, we need to use a third-party library called "mysql-connector-python". This library provides an API for connecting to and interacting with MySQL databases.
To install the library, we can use the "pip" command in our terminal:
pip install mysql-connector-python
Once the installation is complete, we can import the library in our Python code and establish a connection to the database. Let's take a look at an example:
import mysql.connector
# Establish a connection to the database
mydb = mysql.connector.connect(
host="localhost",
user="username",
password="password",
database="database_name"
)
The code above creates a connection to a MySQL database running on our local machine. We need to provide the host, username, password, and database name to establish the connection successfully. If everything goes well, we should see a success message indicating that the connection has been established.
Creating a Table
Now that we have established a connection to the database, let's create a table to store our data. We can do this by executing a "CREATE TABLE" query through our Python code. Here's an example:
# Create a table named "users"
mycursor = mydb.cursor()
mycursor.execute("CREATE TABLE users (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255), email VARCHAR(255))")
The code above creates a table named "users" with three columns: id, name, and email. The "id" column is set as the primary key, and it will auto-increment for each new record.
Inserting Data
Now that we have a table set up, let's insert some data into it. We can do this by executing an "INSERT INTO" query through our Python code. Here's an example:
# Insert a new record into the "users" table
sql = "INSERT INTO users (name, email) VALUES (%s, %s)"
val = ("John Doe", "johndoe@email.com")
mycursor.execute(sql, val)
mydb.commit()
The code above inserts a new record into the "users" table with the name "John Doe" and email address "johndoe@email.com". We use the "commit()" method to save the changes to the database.
Fetching Data
We can also retrieve data from the database using Python. We can use the "SELECT" query to retrieve specific data from a table. Here's an example:
# Retrieve all records from the "users" table
mycursor.execute("SELECT * FROM users")
records = mycursor.fetchall()
for record in records:
print(record)
The code above fetches all the records from the "users" table and prints them out. We can also use the "WHERE" clause to retrieve specific records based on a condition.
