Getting Started with Python and MySQL: A Complete Beginner’s Guide

Last updated 2 weeks, 1 day ago | 36 views 75     5

Tags:- MySQL Python

Python is one of the most popular programming languages, and MySQL is one of the most widely used relational databases. When combined, they allow you to build robust, data-driven applications.

In this tutorial, you'll learn:

  • How to connect Python to a MySQL database

  • How to install the required connector

  • How to create databases and tables

  • How to insert, retrieve, and manage data

  • A complete working example

  • Tips and common pitfalls


Prerequisites

Before you start, make sure you have:

✅ Python installed
✅ MySQL server installed and running
✅ Basic understanding of SQL (not mandatory)


Step 1: Install MySQL Connector for Python

To connect to MySQL, we use the mysql-connector-python package.

Install with PIP:

pip install mysql-connector-python

If you're using a virtual environment, activate it first.


Step 2: Connect to MySQL Database

import mysql.connector

# Establish connection
db = mysql.connector.connect(
    host="localhost",
    user="your_username",
    password="your_password"
)

# Print success message
print("Connected to MySQL!")

✅ Replace your_username and your_password with your MySQL credentials.


Step 3: Create a Database

cursor = db.cursor()
cursor.execute("CREATE DATABASE mydatabase")
print("Database created!")

Step 4: Connect to Your Database

db = mysql.connector.connect(
    host="localhost",
    user="your_username",
    password="your_password",
    database="mydatabase"
)

Step 5: Create a Table

cursor = db.cursor()

cursor.execute("""
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255),
    email VARCHAR(255)
)
""")

print("Table created successfully.")

Step 6: Insert Data into Table

sql = "INSERT INTO users (name, email) VALUES (%s, %s)"
values = ("Alice", "[email protected]")

cursor.execute(sql, values)
db.commit()

print(cursor.rowcount, "record inserted.")

Step 7: Retrieve Data from Table

cursor.execute("SELECT * FROM users")
results = cursor.fetchall()

for row in results:
    print(row)

Step 8: Update & Delete Records

Update:

sql = "UPDATE users SET email = %s WHERE name = %s"
values = ("[email protected]", "Alice")
cursor.execute(sql, values)
db.commit()

Delete:

sql = "DELETE FROM users WHERE name = %s"
values = ("Alice",)
cursor.execute(sql, values)
db.commit()

✅ Complete Working Example

import mysql.connector

# Connect to MySQL and database
db = mysql.connector.connect(
    host="localhost",
    user="your_username",
    password="your_password",
    database="mydatabase"
)

cursor = db.cursor()

# Create table if it doesn't exist
cursor.execute("""
CREATE TABLE IF NOT EXISTS users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255),
    email VARCHAR(255)
)
""")

# Insert data
sql = "INSERT INTO users (name, email) VALUES (%s, %s)"
users = [("John", "[email protected]"), ("Jane", "[email protected]")]
cursor.executemany(sql, users)
db.commit()

# Read data
cursor.execute("SELECT * FROM users")
for row in cursor.fetchall():
    print(row)

# Update data
cursor.execute("UPDATE users SET email = '[email protected]' WHERE name = 'Jane'")
db.commit()

# Delete data
cursor.execute("DELETE FROM users WHERE name = 'John'")
db.commit()

⚠️ Common Pitfalls

Mistake Problem Solution
Wrong credentials Access denied Double-check username/password
Forgetting .commit() Data not saved Always call db.commit() after insert/update/delete
Using %s incorrectly Syntax errors Use tuple with comma: ("value",)
Missing database param Can't find tables Add database="your_db" in connect()

Tips for Using Python with MySQL

  • ✅ Always use prepared statements (%s) to prevent SQL injection.

  • ✅ Use cursor.fetchall() or cursor.fetchone() to read results.

  • ✅ Close the connection with db.close() when done.

  • ✅ Use try-except blocks for better error handling.

Example:

try:
    db = mysql.connector.connect(...)
    cursor = db.cursor()
    cursor.execute("SELECT * FROM users")
    for row in cursor.fetchall():
        print(row)
except mysql.connector.Error as err:
    print("Error:", err)
finally:
    db.close()

Summary Table

Task Function
Install connector pip install mysql-connector-python
Connect to DB mysql.connector.connect()
Create table cursor.execute("CREATE TABLE ...")
Insert data cursor.execute("INSERT ...") + db.commit()
Fetch data cursor.fetchall()
Update/Delete cursor.execute(...) + db.commit()

Final Thoughts

Integrating MySQL with Python is a powerful skill that unlocks endless possibilities — from web apps to data analytics. This tutorial gave you a hands-on introduction to connecting, querying, and manipulating MySQL databases using Python.

 

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=3

    Hence, 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