Python interface with MySQL

Contents:

• Introduction
• Connecting SQL with Python
• Performing Insert, Update, Delete queries
• Display data by using fetchone(),fetchall(),rowcount

Introduction:

To interface i.e. to execute query from a python program and then get data back to python program, we need to install and import MySQL Connector which is a python libarary.

SYNTAX TO INSTALL:
C:\Users\Your Name\AppData\Local\Programs\Python\Python36-32\Scripts>python -m pip install mysql-connector-python

Once the install is complete, then it must be imported in the program to attain access to its functionality . Example:

import mysql.connector

Connecting Mysql Database with python:</br> Steps involved in database connection:

1. Import mysql.connector in the program
2. open a connection with the database using connect() function by providing host name, user name and password

Example:

In [6]:

import mysql.connector as con
connection=con.connect(host="localhost", user="root", password="root")
if connection:
    print("Connection Established")
else:
    print("Connection failed")
    
print(connection)

Connection Established
<mysql.connector.connection.MySQLConnection object at 0x00F36580>

Performing Insert, Update, Delete queries:
Insert:

In [11]:

import mysql.connector as con
connection=con.connect(host="localhost", user="root", password="root", database="test")

mycursor = connection.cursor()

mycursor.execute("INSERT INTO stu1 values(47, 'James')")
mycursor.execute("commit")
connection.close()

Update:

In [14]:

import mysql.connector as con
connection=con.connect(host="localhost", user="root", password="root", database="test")

mycursor = connection.cursor()

mycursor.execute("UPDATE  stu1 SET name='John' WHERE roll=47")
mycursor.execute("commit")
connection.close()

Delete:

In [16]:

import mysql.connector as con
connection=con.connect(host="localhost", user="root", password="root", database="test")

mycursor = connection.cursor()

mycursor.execute("DELETE FROM stu1 WHERE roll=47")
mycursor.execute("commit")
connection.close()

Display data from an SQL table

fetchone():

In [17]:

import mysql.connector as con
connection=con.connect(host="localhost", user="root", password="root", database="test")

mycursor = connection.cursor()

mycursor.execute("SELECT * from stu1")
result=mycursor.fetchone()
print(result)
connection.close()

(26, 'tom')

fetchall():

In [19]:

import mysql.connector as con
connection=con.connect(host="localhost", user="root", password="root", database="test")

mycursor = connection.cursor()

mycursor.execute("SELECT * from stu1")
result=mycursor.fetchall()
print(result)
connection.close()

[(26, 'tom'), (45, 'david')]

rowcount():

In [26]:

import mysql.connector as con
connection=con.connect(host="localhost", user="root", password="root", database="test")

mycursor = connection.cursor(buffered=True)

mycursor.execute("SELECT * FROM stu1")
result=mycursor.rowcount
print(result)
connection.close()

2

In [ ]: