
Python MySQL Tutorial – How to Using Create a Table Python
Last updated 2 weeks, 1 day ago | 53 views 75 5

Creating tables is a fundamental step when working with relational databases like MySQL. In this tutorial, you'll learn how to create MySQL tables using Python and the mysql-connector-python
package. We'll walk through each step with code snippets, best practices, and a complete example.
Table of Contents
-
Prerequisites
-
Installing MySQL Connector
-
Connecting to MySQL Database
-
Creating a Table with SQL
-
Verifying Table Creation
-
Full Working Example
-
Common Pitfalls
-
Tips and Best Practices
✅ 1. Prerequisites
Before starting, ensure you have:
-
Python installed on your machine
-
MySQL server running locally or remotely
-
MySQL credentials (username and password)
-
A database already created (see Python MySQL Create Database Tutorial)
2. Installing MySQL Connector
Install the MySQL Connector package using pip:
pip install mysql-connector-python
This library allows Python to communicate with your MySQL database.
3. Connecting to a MySQL Database
You'll first need to connect to an existing database where your table will reside.
import mysql.connector
db = mysql.connector.connect(
host="localhost",
user="your_username",
password="your_password",
database="mydatabase"
)
cursor = db.cursor()
print("Connected to database!")
✅ Replace
"your_username"
,"your_password"
, and"mydatabase"
with your actual MySQL credentials and target database name.
4. Creating a Table with SQL
Use the CREATE TABLE
SQL command to define your table schema. Here's a basic example:
cursor.execute("""
CREATE TABLE users (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100),
email VARCHAR(100),
age INT
)
""")
print("Table 'users' created successfully.")
This table has:
-
id
: a unique, auto-incrementing integer -
name
: a string up to 100 characters -
email
: a string up to 100 characters -
age
: an integer
5. Verifying Table Creation
You can check all existing tables in the current database with:
cursor.execute("SHOW TABLES")
for table in cursor:
print(table)
This will list all tables, including the one you just created.
6. Full Working Example
Here's a complete Python script that connects to a MySQL database and creates a table:
import mysql.connector
from mysql.connector import Error
def create_table():
try:
# Connect to the database
db = mysql.connector.connect(
host="localhost",
user="root",
password="your_password",
database="mydatabase"
)
cursor = db.cursor()
# Create table
cursor.execute("""
CREATE TABLE users (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100),
email VARCHAR(100),
age INT
)
""")
print("Table 'users' created successfully.")
except Error as e:
print("Error:", e)
finally:
if db.is_connected():
db.close()
print("Connection closed.")
# Run the function
create_table()
⚠️ 7. Common Pitfalls
Issue | Solution |
---|---|
1049 (42000): Unknown database |
Ensure the database exists before connecting |
1050 (42S01): Table already exists |
Use CREATE TABLE IF NOT EXISTS |
Wrong column types | Validate your SQL syntax and data types |
Not closing connection | Always close connection in a finally block |
8. Tips and Best Practices
-
✅ Always use
CREATE TABLE IF NOT EXISTS
to avoid errors if the table already exists. -
✅ Use consistent naming conventions (
snake_case
orcamelCase
). -
✅ Keep column names descriptive and meaningful.
-
✅ Define
PRIMARY KEY
and useAUTO_INCREMENT
for IDs. -
✅ Separate database logic into functions for better structure and testing.
Example with IF NOT EXISTS
:
cursor.execute("""
CREATE TABLE IF NOT EXISTS users (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100),
email VARCHAR(100),
age INT
)
""")
Summary Table
Task | Code |
---|---|
Install connector | pip install mysql-connector-python |
Connect to MySQL | mysql.connector.connect() |
Create table | cursor.execute("CREATE TABLE ...") |
Show tables | cursor.execute("SHOW TABLES") |
Handle errors | Use try-except block with finally |
Final Thoughts
Creating tables in MySQL using Python is an essential skill for any backend or full-stack developer. With mysql-connector-python
, you can programmatically manage your databases and tables with ease. Follow best practices to ensure clean, scalable, and error-free database integration.
Tips and Tricks
What is pass in Python?
Python | Pass Statement
The pass statement is used as a placeholder for future code. It represents a null operation in Python. It is generally used for the purpose of filling up empty blocks of code which may execute during runtime but has yet to be written.
def myfunction():
pass
How can you generate random numbers?
Python | Generate random numbers
Python provides a module called random using which we can generate random numbers. e.g: print(random.random())
We have to import a random module and call the random() method as shown below:
import random
print(random.random())
The random() method generates float values lying between 0 and 1 randomly.
To generate customized random numbers between specified ranges, we can use the randrange() method
Syntax: randrange(beginning, end, step)
import random
print(random.randrange(5,100,2))
What is lambda in Python?
Python | Lambda function
A lambda function is a small anonymous function. This function can have any number of parameters but, can have just one statement.
Syntex:
lambda arguments : expression
a = lambda x,y : x+y
print(a(5, 6))
It also provides a nice way to write closures. With that power, you can do things like this.
def adder(x):
return lambda y: x + y
add5 = adder(5)
add5(1) #6
As you can see from the snippet of Python, the function adder takes in an argument x and returns an anonymous function, or lambda, that takes another argument y. That anonymous function allows you to create functions from functions. This is a simple example, but it should convey the power lambdas and closures have.
What is swapcase() function in the Python?
Python | swapcase() Function
It is a string's function that converts all uppercase characters into lowercase and vice versa. It automatically ignores all the non-alphabetic characters.
string = "IT IS IN LOWERCASE."
print(string.swapcase())
How to remove whitespaces from a string in Python?
Python | strip() Function | Remove whitespaces from a string
To remove the whitespaces and trailing spaces from the string, Python provides a strip([str]) built-in function. This function returns a copy of the string after removing whitespaces if present. Otherwise returns the original string.
string = " Python "
print(string.strip())
What is the usage of enumerate() function in Python?
Python | enumerate() Function
The enumerate() function is used to iterate through the sequence and retrieve the index position and its corresponding value at the same time.
lst = ["A","B","C"]
print (list(enumerate(lst)))
#[(0, 'A'), (1, 'B'), (2, 'C')]
Can you explain the filter(), map(), and reduce() functions?
Python | filter(), map(), and reduce() Functions
- filter() function accepts two arguments, a function and an iterable, where each element of the iterable is filtered through the function to test if the item is accepted or not.
>>> set(filter(lambda x:x>4, range(7))) # {5, 6}
-
map() function calls the specified function for each item of an iterable and returns a list of result
>>> set(map(lambda x:x**3, range(7))) # {0, 1, 64, 8, 216, 27, 125}
-
reduce() function reduces a sequence pair-wise, repeatedly until we arrive at a single value..
>>> reduce(lambda x,y:y-x, [1,2,3,4,5]) # 3
Let’s understand this:
2-1=1
3-1=2
4-2=2
5-2=3Hence, 3.
What is a namedtuple?
Python | namedtuple
A namedtuple will let us access a tuple’s elements using a name/label. We use the function namedtuple() for this, and import it from collections.
>>> from collections import namedtuple
#format
>>> result=namedtuple('result','Physics Chemistry Maths')
#declaring the tuple
>>> Chris=result(Physics=86,Chemistry=92,Maths=80)
>>> Chris.Chemistry
# 92
Write a code to add the values of same keys in two different dictionaries and return a new dictionary.
We can use the Counter method from the collections module
from collections import Counter
dict1 = {'a': 5, 'b': 3, 'c': 2}
dict2 = {'a': 2, 'b': 4, 'c': 3}
new_dict = Counter(dict1) + Counter(dict2)
print(new_dict)
# Print: Counter({'a': 7, 'b': 7, 'c': 5})
Python In-place swapping of two numbers
Python | In-place swapping of two numbers
>>> a, b = 10, 20
>>> print(a, b)
10 20
>>> a, b = b, a
>>> print(a, b)
20 10
Reversing a String in Python
Python | Reversing a String
>>> x = 'PythonWorld'
>>> print(x[: : -1])
dlroWnohtyP
Python join all items of a list to convert into a single string
Python | Join all items of a list to convert into a single string
>>> x = ["Python", "Online", "Training"]
>>> print(" ".join(x))
Python Online Training
python return multiple values from functions
Python | Return multiple values from functions
>>> def A():
return 2, 3, 4
>>> a, b, c = A()
>>> print(a, b, c)
2 3 4
Python Print String N times
Python | Print String N times
>>> s = 'Python'
>>> n = 5
>>> print(s * n)
PythonPythonPythonPythonPython
Python check the memory usage of an object
Python | Check the memory usage of an object
>>> import sys
>>> x = 100
>>> print(sys.getsizeof(x))
28