Support write operations to SQL databases

[GoogleCodeExporter]

sqlgraph has been extended to support write operations.  By passing the
writeable=True flag to the SQLTable() constructor (or similar class), write
operations are enabled:

db.new(**kwargs) # create a new object with attr values given by kwargs

obj.attr = val # change an attribute value of a row object

del db[rowID] # delete a row from the database

db[newID] = obj # change obj's ID, overwriting any existing row with that ID

Tests of write operations were added to sqltable_test.py

Original issue reported on code.google.com by cjlee...@gmail.com on 28 Feb 2009 at 3:55

[GoogleCodeExporter]

Hi Jenny,
write support has been added to SQLTable and its ilk; you just pass the argument
writeable=True to make the SQLTable interface read-write.  I will try to add
documentation on the basic write operations:
- SQLTable.new(**kwargs): creates a new row object, initialized with the 
arguments
specified in kwargs.
- SQLTable[new_id] = obj: changes the ID of an existing obj from the database
- obj.attr = new_value: changes an attribute of an existing obj from the 
database
- del SQLTable[obj_id]: delete the specified object from the database

Once I send you the updated documentation, perhaps you could test the new write 
support?

-- Chris

Original comment by cjlee...@gmail.com on 5 Mar 2009 at 12:41

[GoogleCodeExporter]

Sure.  I will see what I can do.  I will try not to promise more than I can
deliver...  That's my biggest weakness at the moment...

Original comment by jqian....@gmail.com on 5 Mar 2009 at 1:15

[GoogleCodeExporter]

Original comment by mare...@gmail.com on 13 Mar 2009 at 1:04

• Added labels: reviewby-jqian.ubc

[GoogleCodeExporter]

Hi Chris,

I tested the four basic write operations (see below).  It works great.

Jenny

*original table

mysql> select * from run_data_pe;
+----+---------+---------+---------+
| id | total   | U0      | UM      |
+----+---------+---------+---------+
|  1 | 5995633 | 1684314 | 1132511 | 
|  2 | 6276157 | 2033901 | 1151109 | 
|  3 | 6147752 | 1687606 | 1282933 | 
+----+---------+---------+---------+

*test code

>>> conn = sqlgraph.DBServerInfo(user='root')
>>> testTB = sqlgraph.SQLTable('test.run_data_pe', serverInfo=conn, 
writeable=True)

# create a new row
>>> testTB.new(id=4, total=6073153, U0=1924043, UM=1137506)
<pygr.classutil.TupleORW_test.run_data_pe object at 0x1016af6d0>

mysql> select * from run_data_pe;
+----+---------+---------+---------+
| id | total   | U0      | UM      |
+----+---------+---------+---------+
|  1 | 5995633 | 1684314 | 1132511 | 
|  2 | 6276157 | 2033901 | 1151109 | 
|  3 | 6147752 | 1687606 | 1282933 | 
|  4 | 6073153 | 1924043 | 1137506 | 
+----+---------+---------+---------+

# change the ID of an existing row
>>> run = testTB[1]
>>> testTB[5] = run

mysql> select * from run_data_pe;
+----+---------+---------+---------+
| id | total   | U0      | UM      |
+----+---------+---------+---------+
|  5 | 5995633 | 1684314 | 1132511 | 
|  2 | 6276157 | 2033901 | 1151109 | 
|  3 | 6147752 | 1687606 | 1282933 | 
|  4 | 6073153 | 1924043 | 1137506 | 
+----+---------+---------+---------+

# change the value of an existing row
>>> run.UM = 1

mysql> select * from run_data_pe;
+----+---------+---------+---------+
| id | total   | U0      | UM      |
+----+---------+---------+---------+
|  5 | 5995633 | 1684314 |       1 | 
|  2 | 6276157 | 2033901 | 1151109 | 
|  3 | 6147752 | 1687606 | 1282933 | 
|  4 | 6073153 | 1924043 | 1137506 | 
+----+---------+---------+---------+

# delete an existing row
>>> del testTB[5]

mysql> select * from run_data_pe;
+----+---------+---------+---------+
| id | total   | U0      | UM      |
+----+---------+---------+---------+
|  2 | 6276157 | 2033901 | 1151109 | 
|  3 | 6147752 | 1687606 | 1282933 | 
|  4 | 6073153 | 1924043 | 1137506 | 
+----+---------+---------+---------+

Original comment by jqian....@gmail.com on 7 May 2009 at 7:09

[GoogleCodeExporter]

As seen above, the review has been successful and the issue can now be closed.

Original comment by mare...@gmail.com on 13 May 2009 at 2:05

• Changed state: Closed