Scribd
Upload
14 views9 pages

Python SQL Database Integration Guide

Uploaded by

Manish Kumar
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
14 views9 pages

Python SQL Database Integration Guide

Uploaded by

Manish Kumar
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd

Interface of Python with an SQL database

A database is nothing but an organized collection of data. Data is


organized into rows, columns and tables and it is indexed to make it
easier to find relevant information. It becomes necessary to provide an
interface between Python and Database through SQL.

SQL is just a query language, it is not a database. To perform SQL


queries, we need to install any database for example Oracle, MySQL,
MongoDB, PostGres SQL, SQL Server, DB2 etc.

Python Database API supports a wide range of database servers, like


msql, postgressql, Informix, oracle, Sybase etc.

Connecting SQL with Python

Before we connect python program with any database like MySQL we


need to build a bridge to connect Python and MySQL.

Steps to use mysql-connector


1. Download Mysql API ,exe file and install it.(click here to download)
Or
2. Install Mysql-Python Connector
(Open command prompt and execute command)
>pip install mysql-connector
3. Write python statement in python shell import [Link]

If no error message is shown means mysql connector is properly installed

To provide interface between database and programming language:


1. Connection must be established.
2. mysql must be installed on the system
3. Database and Table also must be already created.

import [Link] Or
import [Link] as ms

Here “ms” is an alias, so every time we can use “ms” in place of


“[Link]”
To create connection, connect() function is used
Its syntax is:

connect(host=<server_name>,user=<user_name>,passwd=<password>[,datab
ase=<database>])

 Here server_name means database servername, generally it is


given as “localhost”
 User_name means user by which we connect with mysql generally it
is given as “root”
 Password is the password of user “root”
 Database is the name of database whose data(table) we want to
use

Example:

import [Link]
mydb=[Link](host="localhost",user="root",passwd="roo
t“, database=“school”)
print(mydb)

After successful execution of above statements in python following out


will be displayed
<[Link] object at 0x022624F0>

Example:

 is_connected() function returns true if connection is established


otherwise false
 “mys” is an alias of package “[Link]”
 “mycon” is connection object which stores connection established
with MySQL
 Connect() functions is used to establish connection with given
parameters.

Cursor object :
The MySQLCursor class instantiates objects that can execute operations
such as SQL statements. Cursor objects interact with the MySQL
server using a MySQLConnection object.

Cursor stores all the data as a temporary container of returned data


and we can fetch data one row at a time from Cursor.

TO CREATE CURSOR
Cursor_name = [Link]()
For e.g.
mycursor = [Link]()
TO EXECUTE QUERY

We use execute() function to send query to connection


Cursor_name.execute(query)
For e.g.
[Link](„select * from emp‟)

Example:

Output shows cursor is created and query is fired and stored, but no
data is coming.
To fetch data we have to use functions like fetchall(), fetchone(),
fetchmany() etc.

Example: (creating database)

import [Link]
mydb=[Link](host="localhost",user="root",passwd="")
mycursor=[Link]()
[Link]("create database if not exists school")
[Link]("show databases")
for x in mycursor:
print(x)

Through line 4 we are creating a database named school


(if it is already not created with the help of cursor object.)

Line 5 executes the sql query show databases and store result in
mycursor as collection, whose values are being fetched in x variable one
by one.

On execution of above program school database is created and a list of


available databases is shown.

Example: (creating table)

import [Link]
mydb=[Link](host="localhost",user="root",passwd="",database=
"student")
mycursor=[Link]()
[Link]("create table student(rollno int(3) primary key,name
varchar(20),age int(2))")
Example: change table structure (add, edit, remove column of a table)

import [Link]
mydb=[Link](host="localhost",user="root",passwd="",database=
"student")
mycursor=[Link]()
[Link]("alter table emp add (bonus int(3))")
[Link]("desc emp")

for x in mycursor:
print(x)

Example: (insert record in a table)

import [Link]
mydb=[Link](host="localhost",user="root",passwd="",database=
"student")
mycursor=[Link]()
while 1==1:
ch=int(input("enter -1 to exit any other no to insert record into student table"))
if ch==-1:
break
eno=int(input("Enter eno"))
ename=input("Enter ename")
edept=input("Enter dept")
sal=int(input("Enter salary"))

[Link]("insert into emp


values('"+str(eno)+"','"+ename+"','"+edept+"','"+str(sal)+"')")

[Link]()

Example:(search a record)

import [Link]
mydb=[Link](host="localhost",user="root",passwd="",database=
"student")
mycursor=[Link]()
nm=input("enter name")
[Link]("select * from emp where ename='"+nm+"'")
for x in mycursor:
print (x)
Example: (delete record of a table)

import [Link]
mydb=[Link](host="localhost",user="root",passwd="",database=
"student")
mycursor=[Link]()
[Link]("delete from emp where eno=100")
[Link]()

In above program delete query will delete a record with rollno=[Link]()


method is necessary to call for database transaction.

Example: (Update record)

import [Link]
mydb=[Link](host="localhost",user="root",passwd="",da
tabase="student")
mycursor=[Link]()
[Link]("update emp set sal=1000 where eno=101")
[Link]()

In above program update query update the marks with 99 of rollno=2

To extract data from cursor following functions are used:

fetchall() : it will return all the record in the form of tuple.


fetchone() : it return one record from the result set. i.e. first time it
will return first record, next time it will return second record and so on.
If no more record it will return None
fetchmany(n) : it will return n number of records. if no more record it will
return an empty tuple.
rowcount : it will return number of rows retrieved from the cursor so
far.

Example:
import [Link] as mys
mycon=[Link](host="localhost",user="root",passwd="",database="student")
mycursor=[Link]()
[Link]('select * from emp')
mydata=[Link]()
nrec=[Link]
print('Total records fetch:',nrec)
for row in mydata:
print(row)

Example:

import [Link] as mys


mycon=[Link](host="localhost",user="root",passwd="",database="student")
mycursor=[Link]()
[Link]('select * from emp')
mydata=[Link]()
nrec=[Link]
print('Total records fetch:',nrec)
for e,n,d,s in mydata:
print (e,n,d,s)

Example:

import [Link] as mys


mycon=[Link](host="localhost",user="root",passwd="",database="student")
mycursor=[Link]()
[Link]('select * from emp')
mydata=[Link]()
nrec=[Link]
print('Total records fetch:',nrec)
for row in mydata:
print(row[0],':',row[1],':',row[2],':',row[3])

Example: (fetch all recrods)


import [Link]
mydb=[Link](host="localhost",user="root",passwd="",database=
"student")
mycursor=[Link]()
[Link]("select * from emp")
myrecords=[Link]()
for x in myrecords:
print (x)

Example:

import [Link] as mys


mycon=[Link](host="localhost",user="root",passwd="",database="student")
mycursor=[Link]()
[Link]('select * from emp')
mydata=[Link]()
nrec=[Link]
print('Total records fetch:',nrec)
print(mydata)
mydata=[Link]()
nrec=[Link]
print('Total records fetch:',nrec)
print(mydata)

Example:

import [Link]
mydb=[Link](host="localhost",user="root",passwd="",database=
"student")
mycursor=[Link]()
[Link]("select * from emp")
row=[Link]()
while row is not None:
print(row)
row = [Link]()

Example:
import [Link] as mys
mycon=[Link](host="localhost",user="root",passwd="",database="student")
mycursor=[Link]()
[Link]('select * from emp')
mydata=[Link](3)
nrec=[Link]
print('Total records fetch:',nrec)
for row in mydata:
print(row)

Example:

import [Link] as mys


mycon=[Link](host="localhost",user="root",passwd="",database="student")
mycursor=[Link]()
e=int(input('Enter emp number to search'))
query='select * from emp where eno='+str(e)
[Link](query)
data=[Link]()
if data!=None:
print(data)
else:
print('No such employee')

Example: (rowcount()):

Rows affected by Query. We can get number of rows affected by the


query by

import [Link]
mydb=[Link](host="localhost",user="root",passwd="",database=
"student")
mycursor=[Link]()
mycursor = [Link](buffered=True)
[Link]("select * from emp")
noofrows=[Link]
print("No of rows in student table are",noofrows)

Common questions

Powered by AI

Insertion is done using 'mycursor.execute("insert into emp values(...)")' followed by 'mydb.commit()' to save changes. Searching involves executing 'mycursor.execute("select * from emp where condition")' and iterating over the results. Updating uses 'mycursor.execute("update emp set column=value where condition")', also followed by 'mydb.commit()'. Deleting is performed with 'mycursor.execute("delete from emp where condition")' and again followed by 'mydb.commit()'. Each operation requires explicit execution of SQL commands via a cursor object and calls to 'commit()' for data persistence .

Using Python's mysql-connector, databases and tables can be created by first establishing a connection with the database server. A cursor object is then acquired using the 'cursor()' method. To create a database, the 'execute()' method of the cursor can be used, e.g., 'mycursor.execute("create database if not exists school")'. Similarly, tables are created using SQL commands passed to the cursor, such as 'mycursor.execute("create table student(rollno int(3) primary key,name varchar(20),age int(2))")'. To display available databases or table structures, commands like 'mycursor.execute("show databases")' or 'mycursor.execute("desc emp")' are executed, with results fetched for display .

The data retrieval methods fetchall(), fetchone(), and fetchmany() offer different mechanisms to handle data efficiently. fetchall() is suitable when all records need to be processed or returned at once, while fetchone() allows for processing one record at a time, ideal for row-by-row processing. fetchmany(n) helps in batch processing by retrieving a fixed number of rows, balancing between memory efficiency and retrieval speed. These functions can be combined creatively to tailor data processing loops according to application requirements and resource constraints .

The MySQLCursor class in Python manages the execution of SQL statements and interacts with the MySQL server using a MySQLConnection object. It acts as a temporary container for returned data, allowing data to be fetched one row at a time from the cursor. SQL queries are executed using the cursor's 'execute()' function, and data can be retrieved using methods like 'fetchall()', 'fetchone()', and 'fetchmany()' .

The 'rowcount' attribute in a MySQL cursor indicates the number of rows affected by the last execute() call, which can be crucial for understanding the impact of DML operations like DELETE, UPDATE, or SELECT. It provides insight into how many records have been manipulated or returned, thereby allowing developers to assess the query impact and debug or optimize SQL commands effectively .

The mysql-connector provides several methods for data retrieval: 'fetchall()' returns all records as a tuple, 'fetchone()' retrieves the next row of a query result, returning None if no data is available, and 'fetchmany(n)' retrieves a specified number of rows, returning an empty tuple if no more records are available. These methods allow for flexible control over data retrieval based on the application's needs, with 'fetchone()' typically used in iterative data processing scenarios .

In Python, a new column can be dynamically added to an existing MySQL table using the 'ALTER TABLE' SQL command. This is executed through the cursor with 'mycursor.execute("alter table emp add (bonus int(3))")'. To view the modified table structure, 'mycursor.execute("desc emp")' is used, which retrieves the description of the table structure, allowing the updated schema to be printed and verified .

Python interfaces with SQL databases using the mysql-connector library. To establish a connection, one must install the mysql-connector using the command 'pip install mysql-connector'. After installation, it can be imported in Python using 'import mysql.connector'. Establishing a connection requires using the 'connect()' function, which includes parameters such as 'host', 'user', 'passwd', and 'database' for connecting to the database server, typically on 'localhost', with user 'root' and the respective password and database name .

The 'execute()' function on a cursor object is crucial for executing SQL queries in Python. It takes a SQL query string as its parameter and sends it to the MySQL server via the cursor. This function is used for a variety of SQL operations, including SELECT, INSERT, UPDATE, DELETE, and others, effectively bridging Python commands with SQL operations, enabling database manipulations directly from the Python application .

The 'commit()' method in mysql-connector's connection object is crucial for managing database transactions. It finalizes changes made to the database by executing SQL commands like INSERT, UPDATE, or DELETE. Without calling 'commit()', no changes will be saved to the database, as MySQL operates in a transactional context in Python, maintaining data integrity by allowing explicit control over when data is permanently stored .

You might also like