0
Submit

Python Core


Python Tutorial


Admission Enquiry Form

Database Connectivity in Python

In Database Connectivity, we will learn the steps to connect the python application to the database.
There are the following steps to connect a python application to the database.
  1. Import mysql.connector module
  2. Create the connection object.
  3. Create the cursor object
  4. Execute the query.

Some important points to remember before creating connection using mysql.They are as follows:

    
    # check mysql module is installed or not

    # Install mysql Database Management System in your machine

    # or install xampp in your machine.

    # and keep mysql dbms start before connecting to the database  using python

    #to make connectivity to mysql first we need to install  mysql-connector-python

    #by using command : pip install mysql-connector-python

    # or if you are using pycharm : use  File->settings->project-python project

    #python Interpretor then press + available packages->find  the following packages amd install packages

    # 1 mysql-connector

    # 2 mysql-connector-python

    # 3 mysql-connector-python-rf

    # then restart pycharm again

    # the pycharm is ready to connect to the mysql or maria db

Example of Database Using mysql

import mysql.connector
mydb=mysql.connector.connect(
    host="localhost",
    database="test",
    user="root",
    password=""
)
print("connection ok")
mycursor=mydb.cursor()
#mycursor.execute("show tables")
#mycursor.execute("desc student")
mycursor.execute("insert into student values('sunil',234,6700,500)")
print("data saved :",mycursor.rowcount)
mydb.commit()

mycursor.execute("select * from student")
print(mycursor.rowcount,"rows retrieved")
for x in mycursor:
print(x)

MySql Connectivity with Python

Following points are needed before creating connection with mysql.

  1. You need a web servers installed in your device(Eg.xampp)
  2. Start the xampp server on your machine and start apache and mysql.
  3. Open pycharm and go to file>settings>project interpretor
  4. Then press + to add packages
  5. Add the following packages:

    6. mysql-connector

    mysql-connector-python

    mysql-connector-python-rf
  6. Restart pycharm after installing packages

Now the program is ready for sql connectivity

To import the package to your program:

Syntax:import.mysql.connector




Creating table in database

import mysql.connector
mydb=mysql.connector.connect(
host="localhost",
database="test",
user="root",
password=""
)
print("connection ok")
mycursor=mydb.cursor()
mycursor.execute("Create table table1(name varchar(10),rollno int(10),age int(5))")
print("table created")



Output



Inserting data into the table

import mysql.connector
mydb=mysql.connector.connect(
host="localhost",
database="test",
user="root",
password=""
)
print("connection ok")
mycursor=mydb.cursor()
#mycursor.execute("Create table table1(name varchar(10),rollno int(10),age int(5))")
mycursor.execute("insert into table1 values('Ritik',1802623,22)")
mydb.commit()
print("Data inserted")



Output



Retrieving data from the table

import mysql.connector
mydb=mysql.connector.connect(
host="localhost",
database="test",
user="root",
password=""
)
print("connection ok")
mycursor=mydb.cursor()
mycursor.execute("select * from table1")
for x in mycursor:
print(x)



Output



Printing table column names using cursor

#Printing table column names using cursor
import mysql.connector as sql
con=sql.connect(host="localhost",user="root",password="",database="compuhelp")
mycur=con.cursor()
mycur.execute("select * from student")

mycols=mycur.column_names
#the following code to print column names
print("----------------------")
for cols in mycols:
print(cols,end=" | ")
print("\n----------------------")
for row in mycur:
print(row[0]," | ",row[1],"|",row[2])
print("------------------------")




Taking input from user and entering into database

import mysql.connector
mydb=mysql.connector.connect(
host="localhost",
database="test",
user="root",
password=""
)
print("connection ok")
mycursor=mydb.cursor()
name=input("Enter name of user")
rollno=input("Enter rollno of user")
age=input("Enter age of student")
mycursor.execute("delete from table1")
mycursor.execute("insert into table1 values(%s,%s,%s)",(name,rollno,age))
mydb.commit()
print("Data inserted")
mycursor.execute("select * from table1")
for i in mycursor:
print(i)



Output



Deleting all content from table

import mysql.connector
mydb=mysql.connector.connect(
host="localhost",
database="test",
user="root",
password=""
)
print("connection ok")
mycursor=mydb.cursor()
name=input("Enter name of user")
rollno=input("Enter rollno of user")
age=input("Enter age of student")
mycursor.execute("delete from table1")
mycursor.execute("insert into table1 values(%s,%s,%s)",(name,rollno,age))
mydb.commit()
print("Data inserted")
mycursor.execute("select * from table1")
for i in mycursor:
print(i)



Output



Deleting specific content using where clause

import mysql.connector
mydb=mysql.connector.connect(
host="localhost",
database="test",
user="root",
password=""
)
print("connection ok")
mycursor=mydb.cursor()
mycursor.execute("delete from table1 where rollno=15802621")
mydb.commit()
print("Data inserted")
mycursor.execute("select * from table1")
for i in mycursor:
print(i)



Output

Before Deleting

After Deleting



Updating data in the table using where clause

import mysql.connector
mydb=mysql.connector.connect(
host="localhost",
database="test",
user="root",
password=""
)
print("connection ok")
mycursor=mydb.cursor()
mycursor.execute("UPDATE table1 SET name = 'Sahil' WHERE rollno = 15802624;")
mydb.commit()
print("Data Updated")
mycursor.execute("select * from table1")
for i in mycursor:
print(i)



Output

Before Updating

After Updating



Alter table add column

import mysql.connector
mydb=mysql.connector.connect(
host="localhost",
database="test",
user="root",
password=""
)
print("connection ok")
mycursor=mydb.cursor()
mycursor.execute("ALTER TABLE table1 ADD Email varchar(255)")
mydb.commit()
print("Table Altered")
mycursor.execute("select * from table1")
for i in mycursor:
print(i)



Output



Alter table delete column

import mysql.connector
mydb=mysql.connector.connect(
host="localhost",
database="test",
user="root",
password=""
)
print("connection ok")
mycursor=mydb.cursor()
mycursor.execute("ALTER TABLE table1 DROP COLUMN age")
mydb.commit()
print("Table Altered")
mycursor.execute("select * from table1")
for i in mycursor:
print(i)



Output



Taking multiple user inputs to enter into table

import mysql.connector
mydb=mysql.connector.connect(
host="localhost",
database="test",
user="root",
password=""
)
print("connection ok")
i=1
while i==1:
mycursor = mydb.cursor()
name = input("Enter name of user")
rollno = input("Enter rollno of user")
age = input("Enter age of student")
mycursor.execute("insert into table1 values(%s,%s,%s)", (name, rollno, age))
mydb.commit()
print("Data inserted")
i = int(input("Enter 1 if want to enter more data otherwise enter 0"))
mycursor = mydb.cursor()
mycursor.execute("select * from table1")
for i in mycursor:
print(i)



Output



Checking changes in Database server through CLI(command-line interface):

  1. Open cmd and change the working directory to the path of Xampp folder by

    usingcd command
  2. Run mysql server using the command:

    3. Syntax: mysql -u root

  3. The connection with the server will begin
  4. Use show database; command to check all databases in the server.
  5. Now to use a particular database execute the use command

    6. Syntax: use database_name

  6. Execute show table commands to show all tables in the database
  7. Now you can run sql queries to perform operations on data and check

    changes in database through CLI