Python Tutorial: Connecting to MySQL Database and Creating Tables

![[Python 강좌] Python Flask 블로그 만들기, 지금부터 함께 시작합니다.](/uploads/editor/Python-%EA%B0%95%EC%A2%8C-Python-Flask-%EB%B8%94%EB%A1%9C%EA%B7%B8-%EB%A7%8C%EB%93%A4%EA%B8%B0-%EC%A7%80%EA%B8%88%EB%B6%80%ED%84%B0-%ED%95%A8%EA%BB%98-%EC%8B%9C%EC%9E%91%ED%95%A9%EB%8B%88%EB%8B%A4-20260706-614807.webp)
Why Do We Need a Database?
So far, the blog we've built only displays content on screen but doesn't actually store any data.
For example, let's say you write a post. After refreshing the page, the post should remain visible as expected.
However, in our current setup, there's no storage space, so all content disappears after refresh.
This is where a database (Database) comes in handy.
In simple terms, a database is a secure storage system for information that can be retrieved when needed.
Blogs, e-commerce sites, community platforms, banking systems, and hospital appointment systems all rely on databases.
Why Use MySQL?
In Python, you can work with various databases.
For this tutorial, we'll be using MySQL.
MySQL is one of the most widely used open-source databases globally, known for its speed and reliability.
It also integrates seamlessly with many programming languages including PHP, Python, Java, and Node.js, making it a popular choice in real-world applications.
We'll use MySQL in this project to create an environment similar to actual production systems.
Preparing Flask for MySQL Connection
To use MySQL with Flask, you first need to install a library that allows communication between Python and MySQL.
Run the following command in your terminal:
pip install pymysql
Once installed, you'll be ready to connect to a MySQL database from Python.
Creating the Database
First, let's create a dedicated database for our blog in MySQL.
CREATE DATABASE flask_blog;
You can name your database anything you like, but for this tutorial, we'll use flask_blog.
Creating the Posts Table
Now let's create a table to store blog posts.
CREATE TABLE posts (
id INT AUTO_INCREMENT PRIMARY KEY,
title VARCHAR(200) NOT NULL,
content TEXT NOT NULL,
created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);
We're starting with a basic structure. In future lessons, we'll add more columns like author, view count, and thumbnail images as needed.
Connecting Flask to MySQL
Now let's connect Python to the MySQL database.
You can establish a connection like this:
import pymysql
conn = pymysql.connect(
host="localhost",
user="root",
password="password",
database="flask_blog",
charset="utf8mb4"
)
Here's what each parameter means:
host: MySQL server addressuser: MySQL account usernamepassword: Account passworddatabase: Name of the database to usecharset: Ensures proper display of Korean characters (prevents encoding issues)
Simply enter your account details that match your environment.
Understanding Cursors and Commits
To execute database commands, you first need to create a Cursor.
cursor = conn.cursor()
Now you can run SQL queries:
cursor.execute("SELECT * FROM posts")
If you're adding or modifying data, you must save the changes.
conn.commit()
Calling commit() ensures your changes are reflected in the actual database.
This is one of the most common mistakes beginners make.
It's Important to Close Connections
Once you're done with your work, it's good practice to close the database connection.
conn.close()
Leaving connections open unnecessarily consumes server resources.
Developing the habit of closing connections after use is recommended.
How We'll Use the Database Going Forward
This lesson only covered connecting to the database, but future lessons will involve more complex operations:
Writing posts
Viewing post lists
Viewing individual posts
Editing posts
Deleting posts
Implementing search functionality
All these features will rely on MySQL for their implementation.
In other words, this lesson forms the foundation for nearly all future functionality in our blog application.
The Database's Role in Our Blog
Here's how the database functions within our blog:
Functionality | Database Role |
|---|---|
Writing posts | Saving posts |
Viewing lists | Retrieving stored posts |
Viewing details | Loading specific posts |
Editing | Updating existing data |
Deleting | Removing posts |
Searching | Finding specific posts |
While users simply see the interface, all these features operate through database interactions behind the scenes.
Key Takeaways from This Lesson
The main points to remember are:
Databases provide a storage space for information.
We're using MySQL in this project.
Flask connects to MySQL using the
pymysqllibrary.We've created a table structure for blog posts.
After executing SQL commands, you must call
commit(). <Develop the habit of closing connections with
close()when done.
Once you understand these concepts, your blog will be ready to store and retrieve real data.
In Closing
This lesson covered connecting Flask with MySQL and creating the database and table structure for our blog.
Previously, we only had a static blog interface. Now, starting from this point, our application can actually store and retrieve data - becoming a true web application.
In the next lesson, we'll implement functionality to create posts and display them in lists, bringing us closer to building a fully functional blog.
FAQ
Why is a database necessary for a blog?
A database stores data persistently so that content remains after page refreshes, unlike temporary memory storage.
What does the commit() method do in MySQL connections?
The commit() method saves changes made to the database; without it, modifications won't be reflected in the actual database.
Korean Source
This article is an English localization of a Korean-language report from BlogScore. Original: [Python 강좌] 6강. MySQL 데이터베이스 연동과 테이블 만들기