Python Basics

Python PyMySQL Establish Connection

In this first article of Python PyMySQL Tutorial, we are going to learn how to establish database connection with mysql database.

Before proceeding further, you must have following knowledge

  • Basics of Python
  • MySQL commands (create table, insert row, select row, update row, delete row etc.)

To establish connection with a database like MYSQL server, we need a library first. There are plenty library exist but the best one is PyMySQL.

What is PyMySQL?

PyMySQL is a python based library which is essential to establish connection with mysql database and perform various types of mysql operations.

With PyMySQL library we can perform following tasks

  • Create Table
  • Insert Record into table
  • Print rows from the table
  • Alter table data
  • Delete table record
  • Create copy of  a table
  • Update record in the table

To perform, any SQL operation, first we need to establish database connection.

How to establish DATABASE Connection using PyMySQL in Python?

To establish database connection using pymysql, then first we must have to install pymysql library. Run following command, it will install pymysql library into the system.

pip install pymysql

To establish database connection we must have to know the following parameters.

Server name

In most cases, the server name is localhost. Because most of the webserver has mysql server installed inside apache server. But in some cases, the website or web application is located in one server and mysql database is located in another server. In such scene the server name would be the ip address of the mysql database server.

Database Name

Inside mysql server, we may have one or more than one database. To which ever mysql table you are seeking to establish connection, you must have to know the respective database name. For this tutorial purpose in my mysql server I have ’employee_db’ database. So my database name is ’employee_db’

User Name & Password

Apart from server name, Database name, to establish connection we need user name and password as well. While creating mysql server you must have created a user name and password. I am using default user name and password of my mysql server and that is root and password is blank.

So at this point we are ready with following parameters.

user_name=’root’

pwd=”

server_name=’localhost’

db=’employee_db’

To establish connection, we must have to call the pymysql connect function which takes above parameters as argument. Therefore the complete code to establish mysql connection is as below.

user_name=’root’
pwd=”
server_name=’localhost’
db=’employee_db’

conn=pymysqli.connect(server_name,user_name,pwd,db)

#we are storing pymysql connection value in conn variable. You can take any variable name you want which should not have space or special characters.

print(conn)

If you do not see any error then pymysql connection is established.