🐍Python Programming

Python MySQL (Create, Insert, Select, Update, Delete)

Updated 2026-05-15

30 min read

Python MySQL (Create, Insert, Select, Update, Delete)

Databases are essential for storing and managing structured data. In this tutorial, you'll learn how to interact with a MySQL database using the mysql-connector-python library in Python. We'll cover creating databases and tables, inserting data, selecting data with conditions, updating records, deleting records, ordering results, limiting results, and joining tables.

Introduction

MySQL is one of the most popular relational database management systems (RDBMS). The mysql-connector-python library allows you to connect your Python applications to MySQL databases. In this tutorial, we'll go through the basic operations you need to perform on a MySQL database using Python.

Connecting to MySQL

Before performing any operations, you need to establish a connection to the MySQL server. Here's how you can do it:

Install mysql-connector-python: If you haven't already installed the library, you can do so using pip.
Connect to the MySQL Server: Use the connect() method from the mysql.connector module.

connect.py

1import mysql.connector
2 
3# Establishing the connection
4mydb = mysql.connector.connect(
5host="localhost",
6user="yourusername",
7password="yourpassword"
8)
9 
10print(mydb)

Output

<mysql.connector.connection.MySQLConnection object at 0x7f8b1c1b1d30>

Creating a Database

Once connected, you can create a new database using the CREATE DATABASE SQL statement.

create_db.py

1import mysql.connector
2 
3mydb = mysql.connector.connect(
4host="localhost",
5user="yourusername",
6password="yourpassword"
7)
8 
9mycursor = mydb.cursor()
10 
11# Creating a database
12mycursor.execute("CREATE DATABASE IF NOT EXISTS testdb")

Creating a Table

After creating a database, you can create tables within it. Here's how to create a table named customers:

create_table.py

1import mysql.connector
2 
3mydb = mysql.connector.connect(
4host="localhost",
5user="yourusername",
6password="yourpassword",
7database="testdb"
8)
9 
10mycursor = mydb.cursor()
11 
12# Creating a table
13mycursor.execute("CREATE TABLE IF NOT EXISTS customers (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255), address VARCHAR(255))")

Inserting Data

You can insert data into the customers table using the INSERT INTO SQL statement.

insert_data.py

1import mysql.connector
2 
3mydb = mysql.connector.connect(
4host="localhost",
5user="yourusername",
6password="yourpassword",
7database="testdb"
8)
9 
10mycursor = mydb.cursor()
11 
12# Inserting a record
13sql = "INSERT INTO customers (name, address) VALUES (%s, %s)"
14val = ("John Doe", "123 Main St")
15mycursor.execute(sql, val)
16 
17# Committing the transaction
18mydb.commit()
19 
20print(mycursor.rowcount, "record inserted.")

Output

1 record inserted.

Selecting Data

To retrieve data from the database, use the SELECT SQL statement.

select_data.py

1import mysql.connector
2 
3mydb = mysql.connector.connect(
4host="localhost",
5user="yourusername",
6password="yourpassword",
7database="testdb"
8)
9 
10mycursor = mydb.cursor()
11 
12# Selecting all records
13mycursor.execute("SELECT * FROM customers")
14 
15# Fetching the results
16myresult = mycursor.fetchall()
17 
18for x in myresult:
19print(x)

Output

(1, 'John Doe', '123 Main St')

Selecting Data with WHERE

You can filter the data using the WHERE clause.

select_with_where.py

1import mysql.connector
2 
3mydb = mysql.connector.connect(
4host="localhost",
5user="yourusername",
6password="yourpassword",
7database="testdb"
8)
9 
10mycursor = mydb.cursor()
11 
12# Selecting records with a condition
13sql = "SELECT * FROM customers WHERE address = %s"
14adr = ("123 Main St",)
15mycursor.execute(sql, adr)
16 
17myresult = mycursor.fetchall()
18 
19for x in myresult:
20print(x)

Output

(1, 'John Doe', '123 Main St')

Updating Data

To update data in the database, use the UPDATE SQL statement.

update_data.py

1import mysql.connector
2 
3mydb = mysql.connector.connect(
4host="localhost",
5user="yourusername",
6password="yourpassword",
7database="testdb"
8)
9 
10mycursor = mydb.cursor()
11 
12# Updating a record
13sql = "UPDATE customers SET address = %s WHERE name = %s"
14val = ("456 Elm St", "John Doe")
15mycursor.execute(sql, val)
16 
17# Committing the transaction
18mydb.commit()
19 
20print(mycursor.rowcount, "record(s) affected")

Output

1 record(s) affected

Deleting Data

To delete data from the database, use the DELETE SQL statement.

delete_data.py

1import mysql.connector
2 
3mydb = mysql.connector.connect(
4host="localhost",
5user="yourusername",
6password="yourpassword",
7database="testdb"
8)
9 
10mycursor = mydb.cursor()
11 
12# Deleting a record
13sql = "DELETE FROM customers WHERE name = %s"
14adr = ("John Doe",)
15mycursor.execute(sql, adr)
16 
17# Committing the transaction
18mydb.commit()
19 
20print(mycursor.rowcount, "record(s) deleted")

Output

1 record(s) deleted

Ordering Results

You can order the results using the ORDER BY clause.

order_by.py

1import mysql.connector
2 
3mydb = mysql.connector.connect(
4host="localhost",
5user="yourusername",
6password="yourpassword",
7database="testdb"
8)
9 
10mycursor = mydb.cursor()
11 
12# Selecting and ordering records
13sql = "SELECT * FROM customers ORDER BY name"
14mycursor.execute(sql)
15 
16myresult = mycursor.fetchall()
17 
18for x in myresult:
19print(x)

Limiting Results

To limit the number of results returned, use the LIMIT clause.

limit_results.py

1import mysql.connector
2 
3mydb = mysql.connector.connect(
4host="localhost",
5user="yourusername",
6password="yourpassword",
7database="testdb"
8)
9 
10mycursor = mydb.cursor()
11 
12# Selecting and limiting records
13sql = "SELECT * FROM customers LIMIT 5"
14mycursor.execute(sql)
15 
16myresult = mycursor.fetchall()
17 
18for x in myresult:
19print(x)

Joining Tables

You can join tables using the JOIN clause.

join_tables.py

1import mysql.connector
2 
3mydb = mysql.connector.connect(
4host="localhost",
5user="yourusername",
6password="yourpassword",
7database="testdb"
8)
9 
10mycursor = mydb.cursor()
11 
12# Joining tables
13sql = "SELECT customers.name, orders.order_id FROM customers INNER JOIN orders ON customers.id = orders.customer_id"
14mycursor.execute(sql)
15 
16myresult = mycursor.fetchall()
17 
18for x in myresult:
19print(x)

Practical Example

Let's create a complete mini-program that performs all the operations we've learned so far.

practical_example.py

1import mysql.connector
2 
3def create_db_and_table():
4  mydb = mysql.connector.connect(
5      host="localhost",
6      user="yourusername",
7      password="yourpassword"
8  )
9  mycursor = mydb.cursor()
10  mycursor.execute("CREATE DATABASE IF NOT EXISTS testdb")
11  mydb.database = "testdb"
12  mycursor.execute("CREATE TABLE IF NOT EXISTS customers (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255), address VARCHAR(255))")
13 
14def insert_data():
15  mydb = mysql.connector.connect(
16      host="localhost",
17      user="yourusername",
18      password="yourpassword",
19      database="testdb"
20  )
21  mycursor = mydb.cursor()
22  sql = "INSERT INTO customers (name, address) VALUES (%s, %s)"
23  val = ("John Doe", "123 Main St")
24  mycursor.execute(sql, val)
25  mydb.commit()
26  print(mycursor.rowcount, "record inserted.")
27 
28def select_data():
29  mydb = mysql.connector.connect(
30      host="localhost",
31      user="yourusername",
32      password="yourpassword",
33      database="testdb"
34  )
35  mycursor = mydb.cursor()
36  mycursor.execute("SELECT * FROM customers")
37  myresult = mycursor.fetchall()
38  for x in myresult:
39      print(x)
40 
41def update_data():
42  mydb = mysql.connector.connect(
43      host="localhost",
44      user="yourusername",
45      password="yourpassword",
46      database="testdb"
47  )
48  mycursor = mydb.cursor()
49  sql = "UPDATE customers SET address = %s WHERE name = %s"
50  val = ("456 Elm St", "John Doe")
51  mycursor.execute(sql, val)
52  mydb.commit()
53  print(mycursor.rowcount, "record(s) affected")
54 
55def delete_data():
56  mydb = mysql.connector.connect(
57      host="localhost",
58      user="yourusername",
59      password="yourpassword",
60      database="testdb"
61  )
62  mycursor = mydb.cursor()
63  sql = "DELETE FROM customers WHERE name = %s"
64  adr = ("John Doe",)
65  mycursor.execute(sql, adr)
66  mydb.commit()
67  print(mycursor.rowcount, "record(s) deleted")
68 
69if __name__ == "__main__":
70  create_db_and_table()
71  insert_data()
72  select_data()
73  update_data()
74  delete_data()

Summary

Connecting to MySQL: Use mysql.connector.connect() with host, user, and password.
Creating a Database: Use CREATE DATABASE.
Creating a Table: Use CREATE TABLE.
Inserting Data: Use INSERT INTO.
Selecting Data: Use SELECT.
Updating Data: Use UPDATE.
Deleting Data: Use DELETE.
Ordering Results: Use ORDER BY.
Limiting Results: Use LIMIT.
Joining Tables: Use JOIN.

What's Next?

Now that you've learned how to interact with MySQL databases using Python, the next step is to explore MongoDB. In the upcoming tutorial, "Python MongoDB (Insert, Find, Update, Delete)", we'll cover similar operations but for MongoDB. This will give you a comprehensive understanding of working with two popular NoSQL and SQL databases in Python.

Stay tuned!

🐍Python Programming

Python MySQL (Create, Insert, Select, Update, Delete)

Updated 2026-05-15

30 min read

Python MySQL (Create, Insert, Select, Update, Delete)

Introduction

Connecting to MySQL

Before performing any operations, you need to establish a connection to the MySQL server. Here's how you can do it:

Install mysql-connector-python: If you haven't already installed the library, you can do so using pip.
Connect to the MySQL Server: Use the connect() method from the mysql.connector module.

connect.py

1import mysql.connector
2 
3# Establishing the connection
4mydb = mysql.connector.connect(
5host="localhost",
6user="yourusername",
7password="yourpassword"
8)
9 
10print(mydb)

Output

<mysql.connector.connection.MySQLConnection object at 0x7f8b1c1b1d30>

Creating a Database

Once connected, you can create a new database using the CREATE DATABASE SQL statement.

create_db.py

1import mysql.connector
2 
3mydb = mysql.connector.connect(
4host="localhost",
5user="yourusername",
6password="yourpassword"
7)
8 
9mycursor = mydb.cursor()
10 
11# Creating a database
12mycursor.execute("CREATE DATABASE IF NOT EXISTS testdb")

Creating a Table

After creating a database, you can create tables within it. Here's how to create a table named customers:

create_table.py

1import mysql.connector
2 
3mydb = mysql.connector.connect(
4host="localhost",
5user="yourusername",
6password="yourpassword",
7database="testdb"
8)
9 
10mycursor = mydb.cursor()
11 
12# Creating a table
13mycursor.execute("CREATE TABLE IF NOT EXISTS customers (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255), address VARCHAR(255))")

Inserting Data

You can insert data into the customers table using the INSERT INTO SQL statement.

insert_data.py

1import mysql.connector
2 
3mydb = mysql.connector.connect(
4host="localhost",
5user="yourusername",
6password="yourpassword",
7database="testdb"
8)
9 
10mycursor = mydb.cursor()
11 
12# Inserting a record
13sql = "INSERT INTO customers (name, address) VALUES (%s, %s)"
14val = ("John Doe", "123 Main St")
15mycursor.execute(sql, val)
16 
17# Committing the transaction
18mydb.commit()
19 
20print(mycursor.rowcount, "record inserted.")

Output

1 record inserted.

Selecting Data

To retrieve data from the database, use the SELECT SQL statement.

select_data.py

1import mysql.connector
2 
3mydb = mysql.connector.connect(
4host="localhost",
5user="yourusername",
6password="yourpassword",
7database="testdb"
8)
9 
10mycursor = mydb.cursor()
11 
12# Selecting all records
13mycursor.execute("SELECT * FROM customers")
14 
15# Fetching the results
16myresult = mycursor.fetchall()
17 
18for x in myresult:
19print(x)

Output

(1, 'John Doe', '123 Main St')

Selecting Data with WHERE

You can filter the data using the WHERE clause.

select_with_where.py

1import mysql.connector
2 
3mydb = mysql.connector.connect(
4host="localhost",
5user="yourusername",
6password="yourpassword",
7database="testdb"
8)
9 
10mycursor = mydb.cursor()
11 
12# Selecting records with a condition
13sql = "SELECT * FROM customers WHERE address = %s"
14adr = ("123 Main St",)
15mycursor.execute(sql, adr)
16 
17myresult = mycursor.fetchall()
18 
19for x in myresult:
20print(x)

Output

(1, 'John Doe', '123 Main St')

Updating Data

To update data in the database, use the UPDATE SQL statement.

update_data.py

1import mysql.connector
2 
3mydb = mysql.connector.connect(
4host="localhost",
5user="yourusername",
6password="yourpassword",
7database="testdb"
8)
9 
10mycursor = mydb.cursor()
11 
12# Updating a record
13sql = "UPDATE customers SET address = %s WHERE name = %s"
14val = ("456 Elm St", "John Doe")
15mycursor.execute(sql, val)
16 
17# Committing the transaction
18mydb.commit()
19 
20print(mycursor.rowcount, "record(s) affected")

Output

1 record(s) affected

Deleting Data

To delete data from the database, use the DELETE SQL statement.

delete_data.py

1import mysql.connector
2 
3mydb = mysql.connector.connect(
4host="localhost",
5user="yourusername",
6password="yourpassword",
7database="testdb"
8)
9 
10mycursor = mydb.cursor()
11 
12# Deleting a record
13sql = "DELETE FROM customers WHERE name = %s"
14adr = ("John Doe",)
15mycursor.execute(sql, adr)
16 
17# Committing the transaction
18mydb.commit()
19 
20print(mycursor.rowcount, "record(s) deleted")

Output

1 record(s) deleted

Ordering Results

You can order the results using the ORDER BY clause.

order_by.py

1import mysql.connector
2 
3mydb = mysql.connector.connect(
4host="localhost",
5user="yourusername",
6password="yourpassword",
7database="testdb"
8)
9 
10mycursor = mydb.cursor()
11 
12# Selecting and ordering records
13sql = "SELECT * FROM customers ORDER BY name"
14mycursor.execute(sql)
15 
16myresult = mycursor.fetchall()
17 
18for x in myresult:
19print(x)

Limiting Results

To limit the number of results returned, use the LIMIT clause.

limit_results.py

1import mysql.connector
2 
3mydb = mysql.connector.connect(
4host="localhost",
5user="yourusername",
6password="yourpassword",
7database="testdb"
8)
9 
10mycursor = mydb.cursor()
11 
12# Selecting and limiting records
13sql = "SELECT * FROM customers LIMIT 5"
14mycursor.execute(sql)
15 
16myresult = mycursor.fetchall()
17 
18for x in myresult:
19print(x)

Joining Tables

You can join tables using the JOIN clause.

join_tables.py

1import mysql.connector
2 
3mydb = mysql.connector.connect(
4host="localhost",
5user="yourusername",
6password="yourpassword",
7database="testdb"
8)
9 
10mycursor = mydb.cursor()
11 
12# Joining tables
13sql = "SELECT customers.name, orders.order_id FROM customers INNER JOIN orders ON customers.id = orders.customer_id"
14mycursor.execute(sql)
15 
16myresult = mycursor.fetchall()
17 
18for x in myresult:
19print(x)

Practical Example

Let's create a complete mini-program that performs all the operations we've learned so far.

practical_example.py

1import mysql.connector
2 
3def create_db_and_table():
4  mydb = mysql.connector.connect(
5      host="localhost",
6      user="yourusername",
7      password="yourpassword"
8  )
9  mycursor = mydb.cursor()
10  mycursor.execute("CREATE DATABASE IF NOT EXISTS testdb")
11  mydb.database = "testdb"
12  mycursor.execute("CREATE TABLE IF NOT EXISTS customers (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255), address VARCHAR(255))")
13 
14def insert_data():
15  mydb = mysql.connector.connect(
16      host="localhost",
17      user="yourusername",
18      password="yourpassword",
19      database="testdb"
20  )
21  mycursor = mydb.cursor()
22  sql = "INSERT INTO customers (name, address) VALUES (%s, %s)"
23  val = ("John Doe", "123 Main St")
24  mycursor.execute(sql, val)
25  mydb.commit()
26  print(mycursor.rowcount, "record inserted.")
27 
28def select_data():
29  mydb = mysql.connector.connect(
30      host="localhost",
31      user="yourusername",
32      password="yourpassword",
33      database="testdb"
34  )
35  mycursor = mydb.cursor()
36  mycursor.execute("SELECT * FROM customers")
37  myresult = mycursor.fetchall()
38  for x in myresult:
39      print(x)
40 
41def update_data():
42  mydb = mysql.connector.connect(
43      host="localhost",
44      user="yourusername",
45      password="yourpassword",
46      database="testdb"
47  )
48  mycursor = mydb.cursor()
49  sql = "UPDATE customers SET address = %s WHERE name = %s"
50  val = ("456 Elm St", "John Doe")
51  mycursor.execute(sql, val)
52  mydb.commit()
53  print(mycursor.rowcount, "record(s) affected")
54 
55def delete_data():
56  mydb = mysql.connector.connect(
57      host="localhost",
58      user="yourusername",
59      password="yourpassword",
60      database="testdb"
61  )
62  mycursor = mydb.cursor()
63  sql = "DELETE FROM customers WHERE name = %s"
64  adr = ("John Doe",)
65  mycursor.execute(sql, adr)
66  mydb.commit()
67  print(mycursor.rowcount, "record(s) deleted")
68 
69if __name__ == "__main__":
70  create_db_and_table()
71  insert_data()
72  select_data()
73  update_data()
74  delete_data()

Summary

Connecting to MySQL: Use mysql.connector.connect() with host, user, and password.
Creating a Database: Use CREATE DATABASE.
Creating a Table: Use CREATE TABLE.
Inserting Data: Use INSERT INTO.
Selecting Data: Use SELECT.
Updating Data: Use UPDATE.
Deleting Data: Use DELETE.
Ordering Results: Use ORDER BY.
Limiting Results: Use LIMIT.
Joining Tables: Use JOIN.

What's Next?

Stay tuned!