Python MySQL Connector - Download, Install and Use with Examples

When you want to create a real-life application it is required to interact with Database through your program. This can be done through an interface, which provides you with the facility to connect to Database directly through your Python program. As per class 12 CBSE computer science, it is required that a student must know the python and MySQL connection for the project creation.

Prerequisite

Python and MySQL both should be installed on your computer.

• To know how to install Python: Click Here 
• To know how to install MySQL watch the following Video:

After completing the above two steps follow below-given 6 steps to connect your python program with MySQL and start working on it:

STEP-1: Start Python 

The First step is to start Python. Create a ".py" file in which you want to write the python code for MySQL connection.

STEP-2: install and import mysql-connector

• The next step is to install mysql-connector. mysql-connector is available for Python that is used for connecting the python program with the MySQL Database. 
• This connector can be installed in python using the following command (run it in cmd):
  
  pip install mysql-connector
• If you are facing any error while using this command click here to resolve your environment Path related error. After that, you will not face any problem.
• After installing the mysql-connector, import it in your program by using the following command: 
  
  import mysql.connector
  or
  import mysql.connector as sql

STEP-3: Connect Python with MySQL database using connect()

• connect() function is used to make the connection from python to MySQL and returns a connection object.
• It is available inside mysql.connector module. Hence to use this function we have to import mysql.connector.
• Prototype of connect( ) Function:
  <conn_obj> = <mysql.connector>.connect(host="localhost", user="yourusername", password="yourpassword", database ="test“)
• You can check for successful connection using is_connected( ) Function with connected Object.

  if <conn_object>.is_connected()==False:
      print("Not Conneted")
  

STEP-4: Create a Cursor instance using cursor()

A Database Cursor is a special control structure that facilitates the row by row processing of records in the result set. 

<cursor_obj> = <conn_obj>.cursor()

STEP-5: Execute SQL Query by Using execute( ) Function

Now you can execute MySQL Queries using execute( ) Function with cursor object as per following syntax:

<cursor_obj>.execute(<sql query string>)

The retrieved record will be available in cursor Object.

STEP-6: Close the Connection

Use the following command to close the connection:

<conn_obj>.close()

Full code for Python-MySQL Connection

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

##MYSQL connection with python import mysql.connector as sql con = sql.connect(host='127.0.0.1',user='root',passwd='password') if con.is_connected() == True: mycursor = con.cursor() mycursor.execute("create database student") mycursor.execute("use student") mycursor.execute("create table xiicom (name varchar(20))") for i in range(5): name = input("Enter a Name:") s3 = "insert into xiicom values ('{}')".format(name) mycursor.execute(s3) con.commit() con.close() else: print("Not Connected")