Lesson menuPython TutorialHideShow

Python Tutorial

Python MySQL - Create Table

Define a table's columns and types, and add a primary key.

← MySQL Create DatabaseMySQL Insert →

CREATE TABLE

A table is defined by named columns, each with a data type. Connect to your database, then run CREATE TABLE.

import mysql.connector

conn = mysql.connector.connect(
    host="localhost", user="root", password="secret", database="mydatabase")
cursor = conn.cursor()

cursor.execute(
    "CREATE TABLE customers ("
    "  name VARCHAR(255),"
    "  address VARCHAR(255)"
    ")"
)

Add a Primary Key

Almost every table needs a unique identifier. An INT AUTO_INCREMENT PRIMARY KEY gives each row an automatic, unique id.

cursor.execute(
    "CREATE TABLE IF NOT EXISTS customers ("
    "  id INT AUTO_INCREMENT PRIMARY KEY,"
    "  name VARCHAR(255) NOT NULL,"
    "  address VARCHAR(255),"
    "  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP"
    ")"
)

Use IF NOT EXISTS so re-running your setup script does not error when the table is already there.

Common Column Types

TypeUse for
INT / BIGINTWhole numbers, ids
DECIMAL(10,2)Money — exact, no float rounding
VARCHAR(n)Short, variable-length text
TEXTLong text
DATE / DATETIME / TIMESTAMPDates and times
BOOLEANTrue/false (stored as TINYINT)

Never store money in FLOAT — binary floating point cannot represent 0.10 exactly. Use DECIMAL.

Check That the Table Exists

cursor.execute("SHOW TABLES")
for (table_name,) in cursor.fetchall():
    print(table_name)

Constraints Worth Knowing

  • NOT NULL — the column must always have a value.
  • UNIQUE — no two rows may share the value (e.g. email).
  • DEFAULT — value used when none is supplied.
  • FOREIGN KEY — links a column to another table's primary key.
cursor.execute(
    "CREATE TABLE orders ("
    "  id INT AUTO_INCREMENT PRIMARY KEY,"
    "  customer_id INT,"
    "  amount DECIMAL(10,2) NOT NULL,"
    "  FOREIGN KEY (customer_id) REFERENCES customers(id)"
    ")"
)

Best Practices

  • Give every table an auto-increment primary key.
  • Choose the narrowest type that fits the data.
  • Add NOT NULL and UNIQUE constraints to enforce data integrity at the database level.
  • Use foreign keys to keep related tables consistent.

Try It Yourself

Exercise 1: Write a CREATE TABLE for products with an auto-increment id, a name, and a price stored exactly.

Show solution
cursor.execute(
    "CREATE TABLE products ("
    "  id INT AUTO_INCREMENT PRIMARY KEY,"
    "  name VARCHAR(255) NOT NULL,"
    "  price DECIMAL(10,2) NOT NULL)")

Exercise 2: Why store money in DECIMAL rather than FLOAT?

Show solution

FLOAT is binary and cannot represent values like 0.10 exactly, causing rounding errors. DECIMAL stores exact decimal values.

📘 Real-World Deep Dive

Knowing <strong>MySQL Create Table (MySQL)</strong> well is what turns MySQL from a curiosity into a daily tool — you'll reach for it in nearly every real project.

Real-Life Scenario

An end-to-end usage of MySQL Create Table that you'd actually see in a data pipeline or analytics notebook.

Real-Life Example

import mysql.connector
conn = mysql.connector.connect(database="shop")
cur = conn.cursor()
cur.execute("""
CREATE TABLE IF NOT EXISTS users (
    id   INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(120) NOT NULL,
    email VARCHAR(120) UNIQUE
) CHARACTER SET utf8mb4
""")

Expected Output

(no output)

Common mistakes

  • Reading with the default cursor returns tuples — switch to DictCursor for named access.
  • Strings without parameter binding produce SQL-injection holes — never use f-strings in queries.
  • Connecting through a long-lived daemon pool: make sure pool_reset_connection is on to avoid session state leakage.
  • Treating MySQL Create Table as a black box without reading the docs — the API has subtle defaults that bite when you scale.

🚀 Performance & Best Practices

  • Use prepared statements (cursor.executemany) for bulk inserts.
  • Fetch with cursor.fetchmany(1000) in streams instead of fetchall().
  • Use pymysql's server-side cursors for very large result sets.
  • When working with MySQL, prefer vectorised / batched operations over Python loops.

🧪 Try It Yourself

  1. Reproduce the snippet on a representative slice of your own data.
  2. Profile the snippet with cProfile or timeit and find the single biggest improvement.
  3. Generalise the snippet into a small, reusable function you can drop into future projects.

FAQ: Python MySQL - Create Table

Common questions about this page.

What is Python MySQL - Create Table?

Python MySQL - Create Table is a MySQL lesson that explains python mysql - create table in MySQL. Define a table's columns and types, and add a primary key. Copy the samples and run them in the MySQL editor. It is written for beginners who want a clear definition and working examples.

Should I run python mysql - create table examples locally for better learning?

Yes. Use the browser editor on StudyGrid for a quick check, then Download the example and run it on your computer. Local runs show real errors and the real toolchain, which is one of the fastest ways to learn python mysql - create table in this MySQL MySQL lesson (Python MySQL - Create Table).

How do I use python mysql - create table in MySQL?

To use python mysql - create table in MySQL, follow the examples on this StudyGrid page. Copy a snippet, run it in the browser, then Download and run it locally for better learning. Change the values and compare the output.

What is the syntax of python mysql - create table?

This Python MySQL - Create Table tutorial shows python mysql - create table syntax with short MySQL examples. Use the code blocks in this lesson for the exact statements, then try them in your editor.

Python MySQL - Create Table example for beginners

Yes. This page includes a beginner python mysql - create table example you can copy and run. It is designed for searches such as "python mysql - create table for beginners", "python mysql - create table example", and "how to use python mysql - create table".

What are common mistakes with python mysql - create table?

Common python mysql - create table mistakes include wrong syntax, mixing types, and skipping practice. Work through this MySQL chapter in order, run every example, and check the output before moving on.

Why should I learn python mysql - create table?

Python MySQL - Create Table is used in real MySQL work. Learning python mysql - create table helps you write clearer programs and continue the MySQL tutorial on StudyGrid.

Is Python MySQL - Create Table free to learn online?

Yes. You can learn python mysql - create table free on StudyGrid (studygrid.in). This chapter is part of the MySQL path and includes examples, syntax, and next-step links.