PYTHON MY SQL DATA BASE
ACCESS
Gad fly
msql
my sql
postgre sql
microsoft
sql server 2000
informix
interbase
oracle
sybase
WHAT IS MY SQL?
My sql is an interface for
connecting to a my sql data base server from python. it implements the python
database API v2.0 and is built on top of the mysql C API
HOW DO I INSTALL MY
SQLDB?
#!/usr/bin/python
import MySQLdb
traceback (most recent call last):
file"test.py",line 3,in
<module>
import Mysqldb
importerror:no module named My SQLdb
for Ubuntu,use the folllowing command -
$sudo apt get install python pip pythin dev libmysqlclient
dev
for fedora , use the following command
$sudo dnf install
python python devel mysql redhat rpm config gcc
for python command prompt use the following command
pip install MySQL-python
DATABASE CONNECTION
#!/usr/bin/python
import Mysqldb
#open database connection
db=My SQLdb.connect("127.0.0.1""sample""linkme30""SAMPLEDATA")
#prepare a cursor object using cursor() method
cursor=db.cursor()
#execute SQL query using execute method.
cursor.execute("SELECT VERSION()")
#fetch a single row using fetchone() method.
data = cursor.fetchone()
print("database version : %s " %data)
#disconnect from server
db.close()
Output
database version : 5.0.45
CREATING DATABASE
TABLE
import MySQLdb
#open database connection
db=MySQLdb.db.connect("127.0.0.1","SAMPLE","linkme30","sample
data")
#prepare a cursor object using cursor() method
cursor = db.cursor()
#drop table if it already exist using execute() method.
cursor.execute("drop table if exists jkemployee")
#create table as per requirement
sql="""create table jkemployee(
empno int NOT NULL,
empname char(20),
job char(20),
age int,
sex char(1),
salary float
)"""
cursor.execute(sql)
#disconnect from server
db.close()
INSERT operation
import MySQLdb
db = MSQLdb.connect("127.0.0.1","SAMPLE',"linkMe30","
SAMPLEDATA")
cursor=db.cursor()
sql="""INSERT INTO JKEMPLOYEE(empno,empname,job,age,sex,salary) VALUES(100, 'mohan', 'manager',
25,'M' 18000)"""
try:
#Execute the SQL
command
cursor.execuet(sql)
#commit your changes
in the database
db.commit()
except:
#rollback in case
there is any error
db.rollback()
# disconnect from server
db.close()
#!/usr/bin/python
import MySQLdb
#open database connection
db= MySQLdb.connec("127.0.0.1","SAMPLE","linkme30","SAMPLEDATA”)
#prepare a cursor object using cursor()
method
cursor = db.cursor()
#prepare SQL query to INSERT a record
into the database.
sql = "INSERT INTO
jkemployee(empno,empname,job,age,sex,salary)
VALUES('%d','%s','%s','%d','%c','%f')"%\
(102,
'mohan','clerk',26,'M',12000)
try:
cursor.execute(sql) #Execute the SQL command
fb.commit() #commit
your changes in the database
except:
db.rollback() #rollback in
case there is any error
#disconnect from server
db.close
Example
following code segment is another form of ececution where you can pass
parameters directly?
...........................................
user id = "SAMPLE"
password = "LinkMe30"
con.execute('insert into login values("%s","%s")'%\
(user_id,
password))
READ Operation
import MySQLdb
#open database connection
db=MySQldb.connect("127.0.01","SAMPLE","LinkMe30","SAMPLEDATA")
#prepare a cursor object using cursor()
method
cursor=db.cursor()
sql="SELECT*FROM JKEMPLOyee
WHERE SALARY>'%d'"%10000)
try:
#execute the SQL command
cursor.execute(sql)
#fetch all the rows in a list of lists.
results=cursor.fetchall()
for row in
results:
empno=row[0]
empname= row[1]
job=
row[2]
age=
row[3]
sex=
row[4]
salary=
row[5]
#now print fetched result
print "employee
no=%d,employee name=%s, designation=%s,age=%d,sex=%c,salary=%f"% \
(empno,empname,job,sex,salary)
except:
print ("error:
unable to fetch data")
#disconnect from server
fb.close()
Output
employee no=100, employee name=ramesh, designation=manager, age=26,
sex=M,income=18000
update operation
import MySQLdb
#open database connection
db= MySQLdb.connect("127.0.0.1","SAMPLE","linkMe30","SAMPLEDATA")
#prepare cursor object using cursor()
method
cursor = db.cursor()
#prepare a cursor object using cursor()
method
cursor=db.cursor()
#prepare SQL query to UPDATE required
records
sql ="UPDATE JKEMPLOYEE SET AGE=AGE+1
WHERE
SEX='%C'"%('M')
try:
#execute the SQL command
cursor.execute(sql)
#commit your changes in the database
db.commit()
except:
#rollback in case there is any error
db.rollback()
#disconnect from server
db.close()
Delete Operation
example
#!/usr/bin/python
import MySQLdb
#open database connection
db=MySQLdb.connect("127.0.0.1","SAMPLE"
,"linkMe30","SAMPLEDATA")
#prepare a cursor object using cursor()
method
cursor=db.cursor()
#prepare a cursor object using cursor()
method
cursor=db.cursor()
#prepare SQL query to DELETE required
records
sql="DELETE FROMJKEMPLOYEE WHERE
AGE>'%d'"%(20)
try:
#execute the SQL command
cursor.execute(sql)
#commit your changes in the database
db.commit()
except:
#rollback in case there is any error
db.rollback()
#disconnect from server
db.close()
