How To Connect MySQL Database In Python

How To Connect MySQL Database In Python

What is Python

Python is an object-oriented high-level programming language and is used for rapid application development, due to the high-level built-in data structure it can be used as a scripting language to connect components together. The language is very simple to learn and there is no cost will be spent on program maintenance, the python has an extensive free library available in source or binary form. Most of the programmers prefer python because it offers increased productivity and the edit-test-debug cycle is extremely fast. Python supports a wide range of servers such as GadFly, mSQL, MySQL, Informix, Microsoft SQL Server 2000, and much more.

What Is MySQL Database

MySQL is the open-source database famous all around the world due to its key factors such as performance, ease of use and functionality, MySQL has now become perfect for web-based applications like Facebook, Twitter, and yahoo. It is the free source Relational Database Management System (RDBMS) and it uses Structured Query Language (SQL). it was first developed and marketed by a Swedish company called MYSQL AB and it has an open-source license that can be used by all types of business. MySQL seamlessly works on many OS with many programming languages like python, JAVA, C++, etc. It can support up to 50 million rows in a table with a size limit of up to 8 million terabytes (TB).

Connect MYSQL Database In Python

In this article, you will see information about how to connect to MySQL databases in Python. Before connecting the MySQL database in python we need the following detail of MySQL Server.

  • Username
  • Password
  • Hostname
  • Database name

In order to connect  Python To Work With MySQL, connect the database and create the object then execute the SQL query.

Installing MySQL

From the official site of MySQL, download and install the MySQL server.

in order to connect Python script to the MySQL database, download the mysql.connector and install it on your computer.

Connecting And Creating

Now connect to the MySQL database using username and password.

If you forget your username and password, use forget password or create a new user.

After the creation of the account, connect to the database using a username and password.

Finally, you are connected to the database.

Create Databases

To create a database, use the CREATE DATABASE and make sure that the database does not already exist, to see all the databases use SHOW DATABASES statement.

Create Tables

To store the information create tables in the database

In order to create tables, Create a code and run the code 

The code will be executed and now you are connected to the database

Use  CREATE TABLE command to create a table and now tables will be created, if you want to check the tables use the  SHOW TABLES statement.

Primary Key

Primary Key is the unique value in the table and it helps you to find the unique value

To create a primary key, use the PRIMARY KEY statement.

Use the statement INT AUTO_INCREMENT PRIMARY KEY to identify each row uniquely with a number starting from 1 to infinity.

To drop the primary key use ALTER TABLE table_name DROP column_name statement 

To add Primary Key to the existing table, use ALTER TABLE table_name ADD PRIMARY KEY(column_name)

Insert Data

For inserting data, Use INSERT INTO table_name (column_names) VALUES (data) statement and you can insert single or multiple row as per your convenience.

Select Data

SELECT column_names FROM table_name statement is used to retrieve the data


To find a certain word use WHERE.

Order By

Use  ORDER BY, Use SELECT column_names FROM table_name ORDER BY column_name statement and SELECT column_names FROM table_name ORDER BY column_name DESC statement to sort the result in ascending and descending order.


To DELETE records from the table, use DELETE FROM table_name WHERE condition statement.


Use update table_name SET column_name = new_value WHERE condition statement to update the data.

using connect() function

Install the  mysql.connector and Error objects 

use the connect() function to connect to the MySQL Server and it will accept four parameters: host, database, user and password. 

Then it establishes a connection to the  python_mysql database and returns a MySQLConnection object.

by using the is_connected() method, you can check if the connection has been established.

In case of any error, use  try-except block 

Finally close, using the  close() method of the  MySQLConnection object 

Finally you connected  MySQL database with python and you can now work on the database.

Post Comments

Leave a reply