[Level 2] MySQLdb Python API -- Cursor Objects

In myCxn.py:

import MySQLdb

cxn = MySQLdb.connect(host='localhost', db='test', user='root', passwd='admin' )
cur = cxn.cursor()

In main.py

## import object from file
from myCxn import  cur, cxn

cxn2 = cur.connection # pass the connection object from cursor
# cxn2.close() # close() will cause cur.execute() fail...

## before execute()
print "before execute():"
print "description:", cur.description
print "lastrowid:", cur.lastrowid # lastrowid => The id of last modified row.
print "rowcount:", cur.rowcount


cur.setoutputsizes(2) # seems not work, Does nothing, required by DB API.
cur.execute("select * from test.t;")
for data in cur.fetchall():
  print("cur1_fetchall.rownumber:", cur.rownumber)
  print("cur1_fetchall.rowrowcount:", cur.rowcount)
  print(data[0])
 
cur.execute("select * from test.t;")
for data in cur.fetchall():
  print("cur2_fetchall.rownumber:", cur.rownumber)
  print("cur2_fetchall.rowrowcount:", cur.rowcount)
  print(data[0]) 

cur.execute("select * from test.t;")
for data in cur.fetchone():
  print("cur3_fetchone.rownumber:", cur.rownumber)
  print("cur3_fetchone.rowrowcount:", cur.rowcount)
  print(data)

cur.execute("select * from test.t;")
cur.arraysize=3
for data in cur.fetchmany():
  print("cur4_fetchmany.arraysize:", cur.arraysize)
  print("cur4_fetchmany.rownumber:", cur.rownumber)
  print("cur4_fetchmany.rowrowcount:", cur.rowcount)
  print(data[0])
 
cur.execute("truncate table test.t;")
#cxn.query("drop procedure if exists test.proc1;")   # not show warning if not exists proc1
#cur.execute("drop procedure if exists test.proc1;") # still work, but if proc1 not exist, show warning

#cxn.execute("drop procedure if exists proc1;") # fail, no such attribute
#cur.query("drop procedure if exists proc1;")   # fail, no such attribute

## cannot query DDL for create stored procedure...
#cxn.query("delimiter $$")
#cxn.query("""
#create procedure test.proc1(in p1 int, in p2 int, in p3 int)
#begin
#insert into test.t values(p1),(p2),(p3);
#end$$
#""")
#cxn.query("delimiter ;")

results = cur.callproc("proc1",(1,2,3))
#cur.connection.commit()
print "results:", results
#results = cur.callproc("proc1",('a','b','c')) # error
#cur.connection.commit()
#print "results:", results

## after execute()
print "after execute():"
print "description:", cur.description
print "lastrowid:", cur.lastrowid # lastrowid => The id of last modified row.
print "rowcount:", cur.rowcount

## execute many
cur.setinputsizes(2) # seems not work, Does nothing, required by DB API.
cur.executemany("insert into test.t values(%s)",(10,20,30,40,50))           ## work
#cur.executemany("insert into test.t values(%s)",[(10),(20),(30),(40),(50)]) ## work
#cur.executemany("insert into test.t values(%s)",[10,20,30,40,50])           ## not work


for data in cur.fetchmany():
  #print(data[0],data[1],data[2])
  #print(data[0],data[1])
  print(data[0])

## after execute() 
print "after execute():"
print "description:", cur.description
print "lastrowid:", cur.lastrowid # lastrowid => The id of last modified row.
print "rowcount:", cur.rowcount

cur.connection.commit()
cur.close()
cxn.close()


print "End helloworld!"

Wish this helps.

regards,
Stanley Huang