Thursday, April 1, 2010

[Level 2] MySQLdb Python API -- Connection Objects

Connection have such methods:
1. close() => close database connection.
2. commit() => commit transaction.
3. rollback() => rollback transaction.
4. cursor() => create curosr object and return.
5. begin() => start transaction. (deprecated, and will be removed from 1.3)

The sample code for connection/cursor testing:
Ex1:
#/usr/bin/python
import MySQLdb
cxn = MySQLdb.connect(host='localhost', db='test', user='root', passwd='admin123' )


## create cursor
cur = cxn.cursor()


## query from database
#cur.query("
select * from test.t limit 5;") ## no query attribute for cursor
cur.execute("select * from test.t limit 5;")
for data in cur.fetchall():
  #print(data[0],data[1],data[2])
  print(data[0],data[1])

cur.close()
cxn.close()

Ex2:
#/usr/bin/python
import MySQLdb
cxn = MySQLdb.connect(host='localhost', db='test', user='root', passwd='admin123' )
cur = cxn.cursor()

## test cursor attributes
cur.arraysize=1
print "select * from test.t limit 5;"
cur.execute("select * from test.t limit 5;")
data = cur.fetchmany(2)
## fetch 2 rows
print data
while True:
  #data = cur.fetchmany() ## use default value => cur.arraysize
  #data = cur.fetchmany(cur.arraysize) ## cur.arraysize
  data = cur.fetchmany(3) ## fetch 3 rows
  if data:
    for row in data:
      print(row)
  else:
    break
print "cur.rowcount=" + str(cur.rowcount)
## get row count from cursor

cur.close()
cxn.close()

Ex3:
#/usr/bin/python
import MySQLdb
cxn = MySQLdb.connect(host='localhost', db='test', user='root', passwd='admin123' )
cur=cxn.cursor()

print "test cur.next(): select * from test.t limit 10;"
cur.execute("select * from test.t limit 10;")
iter = cur.__iter__();
while True:
  try:
    data = iter.next()
    print data
  except StopIteration:
    print "Catch StopIteration Exception..."
    break
cur.close()
cxn.close()



Ex4:
#/usr/bin/python
## test transaction
## deprecated, and will remove from 1.3

import MySQLdb
cxn = MySQLdb.connect(host='localhost', db='test', user='root', passwd='admin123' )
#cxn.autocommit = True


cur = cxn.cursor()
#cur = cxn.begin()
## no begin attribute for cursor
#cur.begin()

print "insert into test.t values(1),(2),(3);"
cur.execute("insert into test.t values(1),(2),(3);")
print "cur.rowcount=" + str(cur.rowcount)
#cxn.rollback()
cxn.commit() ## default auto-commit off

cur.close()
cxn.close()



Ex5:
#/usr/bin/python
import MySQLdb
cxn = MySQLdb.connect(host='localhost', db='test', user='root', passwd='admin123' )

cur=cxn.cursor()

## test exception
try:
  cur.execute("select * from test.t wheree 1=1;")
except Exception:
  print "Exception..."

cxn.close()



Wish this helps.
regards,
Stanley Huang