#relational-database and MySQL and PostgreSQL overview

#Introduction

In modern Python application development - whether it is web services, AI data preprocessing or e-commerce systems - relational-database (RDBMS) is still the most reliable and standardized structured data storage solution. As the two giants of open source RDBMS, MySQL and PostgreSQL each have a highly adaptable technology stack. This article will start from the daily perspective of Python developers, break down the core functions, performance and ecological differences between the two, and finally give a selection list with strong implementation.

#1. relational-database basics (quick review)

#1.1 What is RDBMS?

RDBMS is a database system based on the relational model:

• Data is stored in a two-dimensional table (Table), each row corresponds to a business record (Record), and each column corresponds to a fixed attribute (Field/Column)
• Establish relationships between tables through Foreign Key to avoid data redundancy
• Built-in integrity constraints (unique primary key, non-null check, value range restrictions, etc.) to ensure data quality

#1.2 ACID Features: The Core Moat of RDBMS

Regardless of whether you choose MySQL or PostgreSQL, as long as production-level scenarios are involved (especially money and user data), ACID is the guarantee that you must rely on - this is also the key to their ability to replace NoSQL in processing core business:

#2. MySQL: “national-level” database for web development

#2.1 Simple and sufficient positioning

MySQL was developed by a Swedish team. After being acquired by Oracle in 2009, it still maintains its core positioning of open source, lightweight, and high-concurrency reading. It is perfectly adapted to web frameworks such as django, Flask, and FastAPI in the Python ecosystem, and is also the default database for popular products such as WordPress and Shopify.

#2.2 Storage engine: "Arsenal" for on-demand switching

One of the most flexible designs of MySQL is that it supports multiple storage engines - different engines have different performance/functional focus:

-- 查看当前支持的所有存储引擎
SHOW ENGINES;

-- 临时切换当前会话的默认引擎
SET default_storage_engine = InnoDB;

-- 创建指定引擎的表（显式指定更安全）
CREATE TABLE products (
    id INT PRIMARY KEY AUTO_INCREMENT,  -- 主键自增是MySQL最常用的ID方案
    name VARCHAR(100) NOT NULL,
    price DECIMAL(10,2) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;  -- 必须设utf8mb4，否则存不了emoji

#2.3 Quick installation (for Python development)

The following is the simplest installation method for local development environment. For production environment, please refer to the configuration plan provided by the cloud service provider.

#Windows

Download MySQL Installer for Windows directly, select the "Developer Default" option to install Server, Workbench and Python driver connector in one click. 👉 官网下载地址

#Linux（Ubuntu/Debian）

# 一键安装 Server 和基础工具
sudo apt update && sudo apt install -y mysql-server

# 启动并设置开机自启
sudo systemctl start mysql
sudo systemctl enable mysql

# 生产环境必须做的安全配置（开发环境也建议运行）
sudo mysql_secure_installation

#macOS（Homebrew）

brew install mysql
brew services start mysql

#3. PostgreSQL: The most comprehensive “open source enterprise-level” database

#3.1 Advanced but compatible positioning

PostgreSQL (PG for short) is often labeled as "open source Oracle" and "strict enforcer of SQL standards". It has long supported complex queries, custom functions, triggers, and views, and later added JSONB, arrays, PostGIS geographic extensions, etc., and is now the first choice for AI data analysis, GIS, and financial systems.

#3.2 Several advanced-features that Python developers love to use

-- 1. JSONB：比 MySQL JSON 更强大的原生 JSON 支持
CREATE TABLE user_profiles (
    id SERIAL PRIMARY KEY,  -- PG 的自增主键用 SERIAL/BIGSERIAL
    basic_info JSONB NOT NULL  -- JSONB 是二进制存储，支持索引，查询速度快
);

-- 对 JSONB 的某个字段建立索引（秒杀级优化）
CREATE INDEX idx_user_profiles_city ON user_profiles ((basic_info->>'city'));

-- 查询上海的用户（语法比 MySQL 更直观）
SELECT * FROM user_profiles WHERE basic_info->>'city' = '上海';


-- 2. 数组类型：不用额外建关联表，直接存储标签、图片列表
CREATE TABLE blog_posts (
    id SERIAL PRIMARY KEY,
    title TEXT NOT NULL,
    tags TEXT[] NOT NULL DEFAULT '{}'::TEXT[]  -- 默认空数组
);

-- 查询同时包含「Python」「AI」标签的文章
SELECT * FROM blog_posts WHERE tags @> ARRAY['Python', 'AI'];


-- 3. 自定义函数：把复杂逻辑放在数据库层处理（比如替代部分 Pandas 预处理）
CREATE OR REPLACE FUNCTION calculate_final_price(
    original_price NUMERIC(10,2),
    discount_rate NUMERIC(3,2),
    shipping_fee NUMERIC(10,2) DEFAULT 10.00
) RETURNS NUMERIC(10,2) AS $$
BEGIN
    RETURN original_price * (1 - discount_rate) + shipping_fee;
END;
$$ LANGUAGE plpgsql;  -- plpgsql 是 PG 的内置存储过程语言

#3.3 Quick installation (for Python development)

Also listed is local development one-click solution:

#Windows

Download PostgreSQL for Windows (default port 5432, just set the superuser postgres password during installation). 👉 官网下载地址

#Linux（Ubuntu/Debian）

# 一键安装 Server 和常用扩展（如 PostGIS、pg_stat_statements）
sudo apt update && sudo apt install -y postgresql postgresql-contrib

# 启动并设置开机自启
sudo systemctl start postgresql
sudo systemctl enable postgresql

# 切换到 postgres 用户进入 PSQL 命令行
sudo -u postgres psql

#macOS（Homebrew）

brew install postgresql@16  # 建议指定最新稳定版
brew services start postgresql@16

#4. Quick comparison: How should Python developers choose?

#4.1 Core functions/performance/ecology overview

#4.2 Floor-standing selection list

#✅ When choosing MySQL

• The project is traditional web application, CMS, e-commerce platform (such as using django/Flask/FastAPI for background management and mini program interface)
• The team** focuses on web development and is not familiar with advanced SQL**
• Budget or hardware limited (MySQL memory usage is lower than PG)
• Requires quick launch and quick iteration (lower learning costs and operation and maintenance costs)

#✅ When choosing PostgreSQL

• The project involves AI data preprocessing, data analysis, and BI dashboard (need to use Python's Pandas/Dask to process before saving, or directly use PG for lightweight analysis)
• Need to process complex JSON data, arrays, geographical information (such as merchant locations on takeaway platforms, user tags on social platforms)
• The projects are financial transactions, medical records, and government systems (with extremely high requirements for data integrity and ACID compliance)
• The team** has experience in database management, or requires advanced functions for long-term development**

#5. Get started quickly: Python connects two databases

#5.1 Connect to MySQL (using pymysql)

import pymysql
from pymysql.cursors import DictCursor  # 返回字典格式，更方便Python处理

# 1. 建立连接
connection = pymysql.connect(
    host='localhost',
    user='root',
    password='你的密码',
    database='webshop',
    charset='utf8mb4',
    cursorclass=DictCursor
)

try:
    # 2. 创建游标执行查询
    with connection.cursor() as cursor:
        # 查询上海的用户（MySQL 5.7+ 支持 -> 和 ->> 操作符）
        sql = "SELECT * FROM user_profiles WHERE basic_info->>'$.city' = '上海'"
        cursor.execute(sql)
        result = cursor.fetchall()
        print(result)

    # 3. 提交事务（如果是INSERT/UPDATE/DELETE）
    # connection.commit()
finally:
    # 4. 关闭连接
    connection.close()

#5.2 Connecting to PostgreSQL (using psycopg2-binary)

import psycopg2
from psycopg2.extras import RealDictCursor  # 同样返回字典格式

# 1. 建立连接
connection = psycopg2.connect(
    host='localhost',
    user='postgres',
    password='你的密码',
    database='webshop',
    port=5432
)

try:
    # 2. 创建游标执行查询
    with connection.cursor(cursor_factory=RealDictCursor) as cursor:
        # 查询同时包含「Python」「AI」标签的文章
        sql = "SELECT * FROM blog_posts WHERE tags @> ARRAY['Python', 'AI']"
        cursor.execute(sql)
        result = cursor.fetchall()
        # psycopg2返回的是RealDictRow，转成普通字典更通用
        print([dict(row) for row in result])

    # 3. 提交事务
    # connection.commit()
finally:
    # 4. 关闭连接
    connection.close()

#Related tutorials

• SQL详解之DDL
• SQL详解之DML
• SQL详解之DQL
• Python接入MySQL与PostgreSQL数据库

#Summarize

MySQL and PostgreSQL are both top choices for production-level open source RDBMS. There is no absolute "good or bad", only "suitability":

• MySQL is the "Swiss Army Knife" of Web development, lightweight, fast, and easy to use
• PostgreSQL is an all-round "data center" with full functions, good compatibility, and strong scalability

For Python developers, both provide perfect ecological support. Just choose based on your project needs, team skills, and long-term development goals. Finally, don’t forget to follow best practices: design the table structure appropriately, add appropriate indexes, back up regularly, and monitor well!