Interface Python with MySQL
Now we will access and manipulate our database using a python program.
To connect our python script to a MySQL database, we need some special functions,
for which we have a library mysql connector.
Step 1 : Import mysql.connector as sqlcon
we can send our queries from script to the database
Connecting DB to object
Step 2 :
To establish the connection to MySQL Database we’ll use connect( ) function.
<connection-object> = sqlcon.connect(host=<host-name>, user=<username>,
passwd=<password> [, database=<database>])
mycon=sqlcon.connect(host='localhost', user='rahul',passwd='ILoveNeha123',database = 'test')
if mycon.is_connected( ):
print('Successfully Connected to MySQL database')
Creating cursor instance
Step 3 :
When we send our query to the server where it gets executed, the result(what we
asked in query) is sent back to us in one go. But if we want to access the retrieved
data one row at a time, we can’t do it directly. A control structure called database
cursor can be created that gets access of all the records retrieved as per query and
allows us to traverse the result row by row.
cur = mycon.cursor( )
cursor object connector object cursor function
Executing SQL query
Step 4 :
After making the cursor, we can execute SQL queries using execute( ) function as
per following syntax :
<cursor-object>.execute(<sql query string>)
e.g. cur.execute('select * from student')
*This will store the retrieved records in the cursor object.
Extracting data from cursor
Step 5 :
Now that records have been retrieved from the database using our Query, we
need to extract the records from cursor or we can say, we need to fetch the data
using fetch( ) functions.
1. <data> = <cursor>.fetchall( ) returns all the records retrieved in a tuple form.
2. <data> = <cursor>.fetchone( ) returns one record as a tuple or list, next time
next record and so on. Returns None if no records.
3. <data> = <cursor>.fetchmany(n) returns ‘n’ records as a tuple, empty tuple if no
records.
4. <var> = <cursor>.rowcount( ) returns number of rows retrieved from the
cursor so far.
Cleaning up the Environment
Step 6 :
At last, we close the connection established.
<connector object>.close( )
e.g. mycon.close( )
Parameterized Queries
n=33
cur.execute( 'SELECT * FROM student WHERE marks>n' )
cur.execute( 'SELECT * FROM student WHERE marks>%s' %(33,) )
cur.execute( '---- WHERE marks>%s AND sec='%s' ' %(33,'A') )
Parameterized Queries
pass_marks = 33
section = 'A'
query = ' SELECT * FROM student WHERE marks>%s AND sec='%s' '
values = (pass_marks, section)
values2 = (27,'B')
cur.execute(query, values)
cur.execute(query, values2)
Parameterized Queries
'I will score { } out of { }'.format(27,70)
'I will score {0} out of {1}'.format(33,100)
'I will score {marks} out of {total}'.format(total=100, marks=33)
s='SELECT * FROM stu WHERE mrk>{ } AND sec='{}''.format(60, ‘A’)
Inserting Queries
By similarly using the execute( ) function, we can insert data in tables.
cur.execute('INSERT INTO student(rollno, name, marks)
VALUES ({}, '{}', {})'.format(141,'Harsh', 99))
mycon.commit( )
cur.execute('UPDATE student SET marks ={ }
WHERE marks={ }'.format(94,96))
mycon.commit( )
