DATABASE CONECTIVITY USING PYTHON CREATE UPDATE RETRIVE DELETE

Ex. No. :2(b)

DATABASE CONECTIVITY USING PYTHON

create.py

#!/usr/bin/python

# -*- coding: utf-8 -*-

import MySQLdb as mdb

import sys

con = mdb.connect('localhost', '2910007', 'cse007', 'db1');

with con:

cur = con.cursor()

cur.execute("CREATE TABLE Writers(Id INT PRIMARY KEY AUTO_INCREMENT, Name VARCHAR  (25))")

cur.execute("INSERT INTO Writers(Name) VALUES('aaa')")

 cur.execute("INSERT INTO Writers(Name) VALUES('bbb')")

cur.execute("INSERT INTO Writers(Name) VALUES('ccc')")

 cur.execute("INSERT INTO Writers(Name) VALUES('ddd')")

cur.execute("INSERT INTO Writers(Name) VALUES('eee')")

[root@localhost student]# /etc/init.d/mysqld start

Starting mysqld:                                           [  OK  ]

[root@localhost student]# mysql

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 2

Server version: 5.1.51 Source distribution

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.

This software comes with ABSOLUTELY NO WARRANTY. This is free software,

and you are welcome to modify and redistribute it under the GPL v2 license

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;

+--------------------+

| Database           |

+--------------------+

| information_schema |

| kan                |

| mysql              |

| test               |

| www                |

+--------------------+

5 rows in set (0.05 sec)

mysql> create database db1;

Query OK, 1 row affected (0.01 sec)

mysql> CREATE USER '2910007'@'localhost' IDENTIFIED BY 'cse007';

Query OK, 0 rows affected (0.00 sec)

mysql> GRANT ALL ON db1.* TO '2910007'@'localhost';

Query OK, 0 rows affected (0.00 sec)

mysql> use db1;

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

Database changed

retrieve.py

#!/usr/bin/python

# -*- coding: utf-8 -*-

import MySQLdb as mdb

import sys

con = mdb.connect('localhost', '2910007','cse007', 'db1');

with con:

cur = con.cursor()

            cur.execute("SELECT * FROM Writers")

numrows = int(cur.rowcount)

for i in range(numrows):

row = cur.fetchone()

 print row[0], row[1]

OUTPUT

[root@localhost student]# vi retrive.py

[root@localhost student]# python retrive.py

1 aaa

2 bbb

3 ccc

4 ddd

5 eee

[root@localhost student]#

update.py

#!/usr/bin/python

# -*- coding: utf-8 -*-

import MySQLdb as mdb

import sys

aid=raw_input('enter the  id u want to change')

aname=raw_input('enter the new name')

con = mdb.connect('localhost', '2910007', 'cse007', 'db1')

with con:

cur = con.cursor()

cur.execute("UPDATE Writers SET Name = %s WHERE Id = %s",(aname,aid))

print "Number of rows updated: %d" % cur.rowcount

OUTPUT

[root@localhost student]# vi update.py

[root@localhost student]# python update.py

enter the  id u want to change 5

enter the new name efg

Number of rows updated: 1

[root@localhost student]#

delete.py

#!/usr/bin/python

# -*- coding: utf-8 -*-

import MySQLdb as mdb

import sys

aid=raw_input('enter the  id u want to delete')

con = mdb.connect('localhost', '2910007', 'cse007', 'db1')

with con:

cur = con.cursor()

cur.execute("DELETE from  Writers where ID=%s",(aid))

OUTPUT

[root@localhost student]# vi delete.py

[root@localhost student]# python delete.py

enter the  id u want to delete 5

RESULT

            Thus database connectivity in python program was implemented.

--

Don't ever give up.

Even when it seems impossible,

Something will always

pull you through.

The hardest times get even

worse when you lose hope.

As long as you believe you can do it, You can.

But When you give up,

You lose !

I DONT GIVE UP.....!!!

with regards

prem sasi kumar arivukalanjiam