{"id":2952,"date":"2022-01-25T20:14:26","date_gmt":"2022-01-25T14:44:26","guid":{"rendered":"https:\/\/cbsepython.in\/?p=2952"},"modified":"2024-03-16T12:55:28","modified_gmt":"2024-03-16T07:25:28","slug":"python-mysql-connectivity-notes-class-12","status":"publish","type":"post","link":"https:\/\/cbsepython.in\/python-mysql-connectivity-notes-class-12\/","title":{"rendered":"Python MySQL Connectivity Notes Class 12"},"content":{"rendered":"

Python MySQL Connectivity Notes Class 12<\/span><\/h1>\n

 <\/p>\n

Interface Python with MySQL database<\/span><\/h3>\n

Contents:<\/span><\/h3>\n

\u2666\u00a0 Connecting SQL with Python<\/span><\/p>\n

\u2666\u00a0 Creating database connectivity application<\/span><\/p>\n

\u2666\u00a0 Performing insert, delete, update, queries<\/span><\/p>\n

\u2666\u00a0 Display data by using fetchone(), fetchall(),fetchmany(), rowcount()<\/span><\/p>\n

 <\/p>\n

Database connectivity<\/span><\/h2>\n

Database connectivity refers to connection and communication between an application and a database system.<\/span><\/p>\n

The term “front-end” refers to the user interface, while “back-end” means the server application and database that work behind the scenes to deliver information to the user.<\/span><\/p>\n

<\/h2>\n

mysql.connector-Library or package to connect from python to MySQL.<\/span><\/h2>\n

Before we connect the program with mysql, we need to install connectivity package named mysql-connector-python.<\/span><\/p>\n

<\/h2>\n

Command to install connectivity package:<\/span><\/h2>\n

pip install mysql-connector-python<\/span><\/p>\n

 <\/p>\n

Command to import connector:-<\/span><\/h2>\n

import mysql.connector<\/span><\/p>\n

 <\/p>\n

Steps for python MySQL connectivity<\/span><\/h2>\n

Step 1: Install Python<\/span><\/p>\n

Step 2: Install MySQL<\/span><\/p>\n

Step 3: Open Command prompt & Switch on internet connection<\/span><\/p>\n

Step 4: Type pip install mysql-connector-python and execute<\/span><\/p>\n

Step 5: Open python IDLE<\/span><\/p>\n

Step 6: Import mysql.connector<\/span><\/p>\n

 <\/p>\n

Steps for creating database connectivity applications<\/span><\/h2>\n

Step 1: Start Python: Start python editor to create your python script<\/span><\/p>\n

Step 2: mysql.connector package<\/span><\/p>\n

Step 3: Establishing connection to MySQL DATABASE<\/span><\/p>\n

We need to establish a connection to a mysql database using connect() function of mysql.connector package.<\/span><\/p>\n

The connect statement creates a connection to the mysql server and returns a MySQL connection object.<\/span><\/p>\n

Syntax:<\/span><\/p>\n

<Connection object>=mysql.connector.connect (host=<hostname>, user=<username>, passwd <password>, database=<dbname>)<\/span><\/p>\n

 <\/p>\n

import mysql.connector<\/span><\/p>\n

con=mysql.connector.connect(host=”localhost”, user=”root”, \u00a0passwd=” “)<\/span><\/p>\n

 <\/p>\n

Creating a cursor Object:<\/span><\/h2>\n

It is a useful control structure of database connectivity. It will let us execute all the queries we need. Cursor stores all the data as a temporary container of returned data and allows traversal so that we can fetch data one row at a time from cursor. Cursors are created by the connection.cursor() method.<\/span><\/p>\n

 <\/p>\n

Syntax:<\/span><\/p>\n

<cursor object>=<connectionobject> .cursor()<\/span><\/p>\n

Eg: cursor=con.cursor()<\/span><\/p>\n

 <\/p>\n

Execute SQL query:<\/span><\/h2>\n

We can execute SQL query using execute() function<\/span><\/p>\n

Syntax:<\/span><\/p>\n

<cursor object>.execute(SQL QUERY)<\/span><\/p>\n

Eg: cursor.execute(“select* from data”)<\/span><\/p>\n

 <\/p>\n

The above code will execute the sql query and store the retrieved records (resultset) in the cursor object(cursor).<\/span><\/p>\n

Result set refers to a logical set of records that are fetched from the database by executing an sql query and made available in the program.<\/span><\/p>\n

 <\/p>\n

Extract data from Result set:<\/span><\/h2>\n

The records retrieved from the database using SQL select query has to be extracted as record from the result set. We can extract data from the result set using the following fetch() function.<\/span><\/p>\n

fetchall()<\/span><\/p>\n

fetchone()<\/span><\/p>\n

fetchmany()<\/span><\/p>\n

 <\/p>\n

Ways to retrieve data<\/span><\/h2>\n