Interface python with an SQL database || Notes || Sumita Arora || Class 12 || Computer science

Interface python with an SQL database || Notes || Sumita Arora || Class 12 || Computer science

Note:- PDF Download link given below of this Blog

Interface python with an SQL database

• Database connectivity: - Database connectivity refers to connection and communication between an application and a database system.
• Mysql.connector:- Library or package to connect from python to MySQL.
• Command to install connectivity package: - pip install mysql-connector-python
• Command to import connector: - import mysql.connector

• Steps for python MySQL connectivity:-
1. Install Python
2. Install MySQL
3. Open Command prompt
4. Switch on internet connection
5. Type pip install mysql-connector-python and execute
6. Open python IDLE
7. import mysql.connector

• Steps for Creating Database Connectivity Applications :-

There are mainly seven steps that must be followed in order to create a database connectivity application.

Step 1 :- Start Python.
Step 2 :- Import the packages required for database programming.
Step 3 :- Open a connection to database.
Step 4 :- Create a cursor instance.
Step 5 :- Execute a query.
Step 6 :- Extract data from result set.
Step 7 :- Clean up the environment.


2. Import mysql.connector Package :-

import mysql.connector

import mysql.connector as sqlcon

3. Open a Connection to MySQL Database :-

The connect() function of mysql.connector establishes connection to a MySQL database and requires four parameters, which are ;

<Connection-Object>= mysql.connector.connect (host = <host-name>, user = <username>,
passwd = <password> [, database = <database>])

user is the username on MySQL
password is the password of the user
host-name the database server hostname or IP address
database is optional which provides the database name of a MySQL database.

For more Details Video Link :- https://www.youtube.com/watch?v=fj7nhOMsx9M

Checking connection :-

import mysql.connector as sqltor
mycon = sqltor.connect(host="localhost", user = "root", passwd = "MyPass", database="test") 
if mycon.is_connected():
	print('Successfully Connected to MySQL database')

4. Create a Cursor Instance :-

A Database Cursor is a special control structure that facilitates the row by row processing of records in the resultset, i.e., the set of records retrieved as per query.

<cursorobject>=
  <connectionobject>.cursor()

cursor = mycon.cursor()

5. Execute SQL Query :-

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

The above code will execute the given SQL query and store the retrieved records (i.e., the resultset) in the cursor object (namely cursor) which you can then use in your programs/scripts as required.

6. Extract Data from Resultset :-

RESULT SET

The result set refers to a logical set of records that are fetched from the database by executing an SQL query and made available to the application program.

(i) <data> = <cursor>.fetchall(). It will return all the records retrieved as per query in a tuple form (i.e., now <data> will be a tuple.)

For more Details Video Link :- https://www.youtube.com/watch?v=HYnQewCcznc

(ii) <data> = <cursor>.fetchone(). It will return one record from the resultset as a tuple or a list. First time it will return the first record, next time it will fetch the next record and so on.

This method returns one record as a tuple: if there are no more records then it returns None.

(iii) <data> = <cursor>.fetchmany(<n>). This method accepts number of records to fetch and returns a tuple where each record itself is a tuple. If there are not more records then it returns an empty tuple.

(iv) <variable> = <coursor.rowcount. The rowcount is a property of cursor object that returns the number of rows retrieved from the cursor so far. Following examples will make it more clear.

7. Clean Up the Environment :-

After you are through all the processing, in this final step, you need to close the connection established.

<connection
object>.close()

mycon.close()

Connecting with MySQL Database using pymysql :-

<connection> = pymysql.connect("localhost", <username>,
<password>, <databasename>)

commit – MySQLConnection.commit() method sends a COMMIT statement to the MySQL server, committing the current transaction.

 

Functions to execute SQL queries:-

1. CREATE DATABASE

import mysql.connector as my
mydb = my.connect(host = "localhost", user = "root", password = "123456")
mycon = mydb.cursor()
mycon.execute("CREATE DATABSE Pathwalla")

2. SHOW DATABASE

import mysql.connector as my
mydb = my.connect(host = "localhost", user = "root", password = "123456")
mycon = mydb.cursor()
mycon.execute("SHOW DATABSES")
for i in mycon:
    print (i)

3. CREATE TABLE

import mysql.connector as my
mydb = my.connect(host = "localhost", user = "root", password = "123456", database = "Pathwalla")
mycon = mydb.cursor()
mycon.execute("CREATE TABLE student (Name char (25), roll int(10), class int(10))")
mydb.commit()
mydb.close()

4. SHOW TABLE

import mysql.connector as my
mydb = my.connect(host = "localhost", user = "root", password = "123456", database = "Pathwalla")
mycon = mydb.cursor()
mycon.execute("SHOW TABLES")
for i in mycon:
    print (i)

5. DESCRIBE TABLE

import mysql.connector as my
mydb = my.connect(host = "localhost", user = "root", password = "123456", database = "Pathwalla")
mycon = mydb.cursor()
mycon.execute("DESC student")
data = mycon.fetchall()
for i in data:
    print (i)

6. SELECT QUERY

import mysql.connector as my
mydb = my.connect(host = "localhost", user = "root", password = "123456", database = "Pathwalla")
mycon = mydb.cursor()
mycon.execute("SELECT * FROM student")
data = mycon.fetchall()
for i in data:
    print (i)

7. WHERE CLAUSE

import mysql.connector as my
mydb = my.connect(host = "localhost", user = "root", password = "123456", database = "Pathwalla")
mycon = mydb.cursor()
mycon.execute("SELECT * FROM student where Class = 12")
data = mycon.fetchall()
for i in data:
    print (i)

8. DYNAMIC INSERTION

Forming Query Strings :-

Old Style: String Templates with % formatting

"select * from student where marks > %s" 

New Style: String Templates with % formatting

template.format (pe, p1, ..., keve, k1= v1, ..)

"We have (0) hectares planted to (1),".format (45, "okn")

import mysql.connector as my
mydb = my.connect(host = "localhost", user = "root", password = "123456", database = "Pathwalla")
mycon = mydb.cursor()

name = input("Enter Name :-")
roll = input("Enter Roll :-")
class = input("Enter Class :-")

mycon.execute("INSERT INTO student VALUES ('{}', '{}', '{}',)".format (name, roll, class))
mydb.commit()
mydb.close()

9. UPDATE COMMAND

import mysql.connector as my
mydb = my.connect(host = "localhost", user = "root", password = "123456", database = "Pathwalla")
mycon = mydb.cursor()

mycon.execute("UPDATE student SET roll = 5 WHERE Name = 'Ram'")
mydb.commit()
mydb.close()

10. DELETE COMMAND

import mysql.connector as my
mydb = my.connect(host = "localhost", user = "root", password = "123456", database = "Pathwalla")
mycon = mydb.cursor()

mycon.execute("DELET FROM student WHERE roll = 26")
mydb.commit()
mydb.close()

11. DROP COMMAND

import mysql.connector as my
mydb = my.connect(host = "localhost", user = "root", password = "123456", database = "Pathwalla")
mycon = mydb.cursor()

mycon.execute("DROP TABLE student")
mydb.commit()
mydb.close()

12. ALTER COMMAND

import mysql.connector as my
mydb = my.connect(host = "localhost", user = "root", password = "123456", database = "Pathwalla")
mycon = mydb.cursor()

mycon.execute("ALTER TABLE student ADD Marks int (10)")
mydb.commit()
mydb.close()

Some Important Question

Q1. What is database?

Answer = The database is a collection of organized information that can easily be used, managed, update, and they are classified according to their organizational approach.

Q2. Write command to install connector.

Answer = pip install mysql-connector-python

Q3. write the steps of connectivity between SQL and Python.

Answer = import, connect, cursor, execute

Q4. What is result set? Explain with example.

Answer = Fetching rows or columns from result sets in Python. The fetch functions in the ibm_db API can iterate through the result set. If your result set includes columns that contain large data (such as BLOB or CLOB data), you can retrieve the data on a column-by-column basis to avoid large memory usage.

Q5. Write code for database connectivity.

Answer =

# importing the module
import mysql.connector

# opening a database connection
conn = mysql.connector.connect ("localhost", "root", "1234", "student")

# define a cursor object
mycursor = conn.cursor()

# drop table if exists
mycursor.execute("DROP TABLE IF EXISTS STUDENT")

# query
sql = "CREATE TABLE STUDENT (NAME char(30) NOT NULL, CLASS char(5), AGE int, GENDER char(8), MARKS int)"

# execute query 
cursor.execute(sql)

conn.commit()

# close connection
conn.close()

Q6. Use of functions in connectivity - INSERT, UPDATE, DELETE, ROLLBACK.

Answer =

INSERT: - It is an SQL statement used to create a record into a table.
UPDATE: - It is used update those available or already existing record(s).
DELETE: - It is used to delete records from the database.
ROLLBACK: - It works like "undo", which reverts all the changes that you have made.

Q7. Which method is used to retrieve all rows and single row?

Answer = Fetchall, fetchone()

Q8. Write python-mysql connectivity to retrieve all the data of table student.

Answer =

import mysql.connector

mydb = mysql.connector.connect( host="localhost", user="root", passwd="123" database="school")
mycursor = mydb.cursor()
mycursor.execute("select * from student")

for x in mycursor.fetchall() :
    print(x)

Thankyou!!!!!

PDF DOWNLOAD LINK :- https://drive.google.com/file/d/19Z8sQuL6M9Z1l-YcD1cCyp2uo72VmZ6X/view?usp=drivesdk