Creating
Database Table
import MySQLdb
# Open database connection
db =
MySQLdb.connect("localhost",“root",“root","TESTDB"
)
# 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 EMPLOYEE")
# Create table as per requirement
sql = """CREATE TABLE EMPLOYEE (
FIRST_NAME CHAR(20) NOT NULL,
LAST_NAME CHAR(20),
AGE INT,
GENDER
CHAR(1),
INCOME FLOAT
)"""
cursor.execute(sql)
# disconnect from server
db.close()
******************************************************************************************************************************************************************
INSERT
Operation
import MySQLdb
# Open database connection
db =
MySQLdb.connect("localhost","root","root","TESTDB"
)
# prepare a cursor object using cursor() method
cursor = db.cursor()
# Prepare SQL query to INSERT a record into the database.
sql = """INSERT INTO EMPLOYEE(FIRST_NAME,
LAST_NAME,
AGE, GENDER, INCOME)
VALUES
('Mac', 'Mohan', 20, 'M', 2000)"""
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()
******************************************************************************************************************************************************************
Above
example can be written as follows to create SQL queries dynamically −
import MySQLdb
# Open database connection
db =
MySQLdb.connect("localhost","root","root","TESTDB"
)
# prepare a cursor object using cursor() method
cursor = db.cursor()
# Prepare SQL query to INSERT a record into the database.
sql = "INSERT INTO EMPLOYEE(FIRST_NAME, \
LAST_NAME, AGE,
GENDER, INCOME) \
VALUES ('%s',
'%s', '%d', '%c', '%d' )" % \
('Mac',
'Mohan', 20, 'M', 2000)
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()
******************************************************************************************************************************************************************
select
operation
import MySQLdb
# Open database connection
db =
MySQLdb.connect("localhost","root","root","TESTDB"
)
# prepare a cursor object using cursor() method
cursor = db.cursor()
sql = "SELECT * FROM EMPLOYEE \
WHERE INCOME
> '%d'" % (1000)
try:
#
Execute the SQL command
cursor.execute(sql)
# Fetch all the
rows in a list of lists.
results = cursor.fetchall()
for row in results:
fname = row[0]
lname = row[1]
age = row[2]
Gender= row[3]
income = row[4]
# Now print
fetched result
print
"fname=%s,lname=%s,age=%d,gender=%s,income=%d" % \
(fname,
lname, age, gender, income )
except:
print "Error:
unable to fecth data"
# disconnect from server
db.close()
******************************************************************************************************************************************************************
update
Operation
import MySQLdb
# Open database connection
db =
MySQLdb.connect("localhost","root","root","TESTDB"
)
# prepare a cursor object using cursor() method
cursor = db.cursor()
# Prepare SQL query to UPDATE required records
sql = "UPDATE EMPLOYEE SET AGE = AGE + 1
WHERE gender= '%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
import MySQLdb
# Open database connection
db = MySQLdb.connect("localhost",“root",“root","TESTDB"
)
# prepare a cursor object using cursor() method
cursor = db.cursor()
# Prepare SQL query to DELETE required records
sql = "DELETE FROM EMPLOYEE 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()
No comments:
Post a Comment