Python with MySQL Connectivity: Database & Table [Examples]

By : Anna Blake Anna Blake
Hours Updated

⚡ Smart Summary

Python connects to a MySQL database using the mysql-connector library. Here you will learn to install the connector, connect to MySQL, create databases and tables, add primary keys, alter tables, and insert records with examples.

  • 🔌 Connect: mysql.connector.connect() links Python to MySQL with host and credentials.
  • 🗄️ Create: Run CREATE DATABASE and CREATE TABLE through a cursor.
  • ➕ Insert: execute() an INSERT query, then commit() to save records.
  • 🤖 AI help: AI tools like Copilot generate MySQL connector code instantly.
Read More

Python with MySQL Connectivity

In order to use MySQL connectivity with Python, you must have some knowledge of SQL

Before diving deep, let’s understand

What is MySQL?

MySQL is an Open-Source database and one of the best type of RDBMS (Relational Database Management System). Co-founder of MySQLdb is Michael Widenius’s, and also MySQL name derives from the daughter of Michael.

How to Install MySQL Connector Python on Windows, Linux/Unix

Install MySQL in Linux/Unix:

Download RPM package for Linux/Unix from Official site: https://www.mysql.com/downloads/

In terminal use following command

rpm  -i <Package_name>
Example   rpm -i MySQL-5.0.9.0.i386.rpm

To check in Linux

mysql --version

Install MySQL in Windows

Download MySQL database exe from official site and install as usual normal installation of software in Windows. Refer this tutorial, for a step by step guide

How to Install MySQL Connector Library for Python

Here is how to connect MySQL with Python:

For Python 2.7 or lower install using pip as:

pip install mysql-connector

For Python 3 or higher version install using pip3 as:

pip3 install mysql-connector

Test the MySQL Database connection with Python

To test MySQL database connectivity in Python here, we will use pre-installed MySQL connector and pass credentials into connect() function like host, username and password as shown in the below Python MySQL connector example.

Syntax to access MySQL with Python:

import mysql.connector
	db_connection = mysql.connector.connect(
  	host="hostname",
  	user="username",
  	passwd="password"
    )

Example:

import mysql.connector
db_connection = mysql.connector.connect(
  host="localhost",
  user="root",
  passwd="root"
)
print(db_connection)

Output:

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

Here output shows the connection created successfully.

RELATED ARTICLES

Creating Database in MySQL using Python

Syntax to Create new database in SQL is

CREATE DATABASE "database_name"

Now we create database using database programming in Python

import mysql.connector
  db_connection = mysql.connector.connect(
  host= "localhost",
  user= "root",
  passwd= "root"
  )
# creating database_cursor to perform SQL operation
db_cursor = db_connection.cursor()
# executing cursor with execute method and pass SQL query
db_cursor.execute("CREATE DATABASE my_first_db")
# get list of all databases
db_cursor.execute("SHOW DATABASES")
#print all databases
for db in db_cursor:
	print(db)

Output:

Creating Database in MySQL

Here above image shows the my_first_db database is created

Create a Table in MySQL with Python

Let’s create a simple table “student” which has two columns as shown in the below MySQL connector Python example.

SQL Syntax:

CREATE  TABLE student (id INT, name VARCHAR(255))

Example:

import mysql.connector
  db_connection = mysql.connector.connect(
  host="localhost",
  user="root",
  passwd="root",
  database="my_first_db"
  )
db_cursor = db_connection.cursor()
#Here creating database table as student'
db_cursor.execute("CREATE TABLE student (id INT, name VARCHAR(255))")
#Get database table'
db_cursor.execute("SHOW TABLES")
for table in db_cursor:
	print(table)

Output:

 ('student',)

Create a Table with Primary Key

Let’s create an Employee table with three different columns. We will add a primary key in id column with AUTO_INCREMENT constraint as shown in the below Python project with database connectivity.

SQL Syntax:

CREATE TABLE employee(id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255), salary INT(6))

Example:

import mysql.connector
  db_connection = mysql.connector.connect(
  host="localhost",
  user="root",
  passwd="root",
  database="my_first_db"
  )
db_cursor = db_connection.cursor()
#Here creating database table as employee with primary key
db_cursor.execute("CREATE TABLE employee(id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255), salary INT(6))")
#Get database table
db_cursor.execute("SHOW TABLES")
for table in db_cursor:
	print(table)

Output:

('employee',) ('student',)

ALTER table in MySQL with Python

Alter command is used for modification of Table structure in SQL. Here we will alter Student table and add a primary key to the id field as shown in the below Python MySQL connector project.

SQL Syntax:

ALTER TABLE student MODIFY id INT PRIMARY KEY

Example:

import mysql.connector
  db_connection = mysql.connector.connect(
  host="localhost",
  user="root",
  passwd="root",
  database="my_first_db"
  )
db_cursor = db_connection.cursor()
#Here we modify existing column id
db_cursor.execute("ALTER TABLE student MODIFY id INT PRIMARY KEY")

Output:

Here below you can see the id column is modified.

ALTER table in MySQL

Insert Operation with MySQL in Python:

Let’s perform insertion operation in MySQL Database table which we already create. We will insert data oi STUDENT table and EMPLOYEE table.

SQL Syntax:

INSERT INTO student (id, name) VALUES (01, "John")
INSERT INTO employee (id, name, salary) VALUES(01, "John", 10000)

Example:

import mysql.connector
  db_connection = mysql.connector.connect(
  host="localhost",
  user="root",
  passwd="root",
  database="my_first_db"
  )
db_cursor = db_connection.cursor()
student_sql_query = "INSERT INTO student(id,name) VALUES(01, 'John')"
employee_sql_query = " INSERT INTO employee (id, name, salary) VALUES (01, 'John', 10000)"
#Execute cursor and pass query as well as student data
db_cursor.execute(student_sql_query)
#Execute cursor and pass query of employee and data of employee
	db_cursor.execute(employee_sql_query)
db_connection.commit()
print(db_cursor.rowcount, "Record Inserted")

Output:

 2 Record Inserted

FAQs

Install mysql-connector with pip, then call mysql.connector.connect() with your host, user, and password to get a connection object.

Create a cursor, then run cursor.execute(‘CREATE DATABASE name’). Use SHOW DATABASES to confirm it was created.

Connect, create a cursor, and execute a CREATE TABLE statement. Add AUTO_INCREMENT PRIMARY KEY to auto-number the id column.

Execute a SELECT query with the cursor, then call fetchall() for all rows or fetchone() for a single row.

Run an UPDATE or DELETE statement with cursor.execute(), then commit(). Always add a WHERE clause to target specific rows.

AI assistants like GitHub Copilot autocomplete connector code, generate CRUD queries, and suggest parameterized statements that prevent SQL injection.

Yes. AI-powered tools write connection and CRUD functions, map tables to classes, and add error handling for failed queries.

commit() saves changes permanently. Without it, INSERT, UPDATE, and DELETE operations roll back when the connection closes, losing data.

Summarize this post with: