X Tutup
Skip to content

Commit e40968e

Browse files
author
Greg Turnquist
committed
[SESPRINGPYTHONPY-158] Added new insert_and_return_id method to DatabaseTemplate.
1 parent eab2f8f commit e40968e

File tree

5 files changed

+83
-8
lines changed

5 files changed

+83
-8
lines changed

‎docs/sphinx/source/dao.rst‎

Lines changed: 5 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -213,6 +213,7 @@ then you will find this template has a familiar feel.
213213

214214
================================================================= ================================================================================================
215215
execute(sql_statement, args = None) execute any statement, return number of rows affected
216+
insert_and_return_id(sql_statement, args = None) insert, return id of new row inserted
216217
query(sql_query, args = None, rowhandler = None query, return list converted by rowhandler
217218
query_for_list(sql_query, args = None) query, return list of DB-API tuplesTrue
218219
query_for_int(sql_query, args = None) query for a single column of a single row, and return an integer (throws exception otherwise)
@@ -221,7 +222,9 @@ query_for_object(sql_query, args = None, required_type = None) query for a s
221222
update(sql_statement, args = None) update the database, return number of rows updated
222223
================================================================= ================================================================================================
223224

224-
*Inserts* are implemented through the execute() function, just like in JdbcTemplate.
225+
*Inserts* have classically been implemented through the **execute()** function, just like in JdbcTemplate. In Spring Python 1.2, we added **insert_and_return_id()**
226+
to give the option of returning the id of the newly created row. It has the same signature as **execute()**. This is very useful when you plan to insert one row in one table, and then insert
227+
several rows in another table that reference the first row created.
225228

226229
Notes on using SQLServerConnectionFactory
227230
+++++++++++++++++++++++++++++++++++++++++
@@ -297,4 +300,4 @@ odbc_info with a dictionary. See the following example::
297300
</property>
298301
</object>
299302

300-
</objects>
303+
</objects>

‎springpython.properties‎

Lines changed: 4 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -9,12 +9,12 @@ project.key=EXTPY
99
#release.type=snapshot
1010

1111
# This property is only un-commented and set for official tagged releases.
12-
#release.type=release
13-
#build.stamp=FINAL
12+
release.type=release
13+
build.stamp=FINAL
1414

1515
# This property is uncommented for milestone releases.
16-
release.type=milestone
17-
build.stamp=RC2
16+
#release.type=milestone
17+
#build.stamp=RC2
1818

1919
# docbook reference documentation
2020
doc.ref.dir=docs/reference

‎src/springpython/database/core.py‎

Lines changed: 12 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -50,7 +50,7 @@ def __del__(self):
5050
"When this template goes out of scope, need to close the connection it formed."
5151
if self.connection_factory is not None: self.connection_factory.close()
5252

53-
def execute(self, sql_statement, args = None):
53+
def _execute(self, sql_statement, args = None):
5454
"""Issue a single SQL execute, typically a DDL statement."""
5555
sql_statement = self.connection_factory.convert_sql_binding(sql_statement)
5656

@@ -62,9 +62,11 @@ def execute(self, sql_statement, args = None):
6262
if args:
6363
cursor.execute(sql_statement, args)
6464
rows_affected = cursor.rowcount
65+
lastrowid = cursor.lastrowid
6566
else:
6667
cursor.execute(sql_statement)
6768
rows_affected = cursor.rowcount
69+
lastrowid = cursor.lastrowid
6870
except Exception, e:
6971
self.logger.debug("execute.execute: Trapped %s while trying to execute '%s'" % (e, sql_statement))
7072
error = e
@@ -77,7 +79,15 @@ def execute(self, sql_statement, args = None):
7779
if error:
7880
raise DataAccessException(error)
7981

80-
return rows_affected
82+
return {"rows_affected":rows_affected, "lastrowid":lastrowid}
83+
84+
def execute(self, sql_statement, args = None):
85+
"""Execute a single SQL statement, and return the number of rows affected."""
86+
return self._execute(sql_statement, args)["rows_affected"]
87+
88+
def insert_and_return_id(self, sql_statement, args = None):
89+
"""Execute a single INSERT statement, and return the PK of the new row."""
90+
return self._execute(sql_statement, args)["lastrowid"]
8191

8292
def query(self, sql_query, args = None, rowhandler = None):
8393
"""Execute a query given static SQL, reading the ResultSet on a per-row basis with a RowMapper.

‎test/springpythontest/databaseCoreTestCases.py‎

Lines changed: 55 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -332,6 +332,18 @@ def testProgrammaticStaticInsert(self):
332332

333333
name = self.databaseTemplate.query_for_object("SELECT name FROM animal WHERE category = 'kill_bill_viper'", required_type=types.StringType)
334334
self.assertEquals(name, "black mamba")
335+
336+
def testProgrammaticStaticInsertWithInsertApi(self):
337+
self.mock.expects(once()).method("execute").id("#1")
338+
self.mock.expects(once()).method("execute").id("#2").after("#1")
339+
self.mock.expects(once()).method("fetchall").will(return_value([("black mamba",)])).id("#3").after("#2")
340+
self.mock.lastrowid = 42
341+
342+
id = self.databaseTemplate.insert_and_return_id("INSERT INTO animal (name, category, population) VALUES ('black mamba', 'kill_bill_viper', 1)")
343+
self.assertEquals(id, 42)
344+
345+
name = self.databaseTemplate.query_for_object("SELECT name FROM animal WHERE category = 'kill_bill_viper'", required_type=types.StringType)
346+
self.assertEquals(name, "black mamba")
335347

336348
def testProgrammaticInsertWithBoundVariables(self):
337349
self.mock.expects(once()).method("execute").id("#1")
@@ -354,6 +366,27 @@ def testProgrammaticInsertWithBoundVariables(self):
354366
name = self.databaseTemplate.query_for_object("select name from animal where name = 'cottonmouth'", required_type=types.StringType)
355367
self.assertEquals(name, "cottonmouth")
356368

369+
def testProgrammaticInsertWithBoundVariablesWithInsertApi(self):
370+
self.mock.expects(once()).method("execute").id("#1")
371+
self.mock.expects(once()).method("execute").id("#2").after("#1")
372+
self.mock.expects(once()).method("fetchall").will(return_value([("black mamba",)])).id("#3").after("#2")
373+
self.mock.expects(once()).method("execute").id("#4").after("#3")
374+
self.mock.expects(once()).method("execute").id("#5").after("#4")
375+
self.mock.expects(once()).method("fetchall").will(return_value([("cottonmouth",)])).id("#6").after("#5")
376+
self.mock.lastrowid = 42
377+
378+
id = self.databaseTemplate.insert_and_return_id ("INSERT INTO animal (name, category, population) VALUES (?, ?, ?)", ('black mamba', 'kill_bill_viper', 1))
379+
self.assertEquals(id, 42)
380+
381+
name = self.databaseTemplate.query_for_object("SELECT name FROM animal WHERE category = 'kill_bill_viper'", required_type=types.StringType)
382+
self.assertEquals(name, "black mamba")
383+
384+
id = self.databaseTemplate.insert_and_return_id("INSERT INTO animal (name, category, population) VALUES (%s, %s, %s)", ('cottonmouth', 'kill_bill_viper', 1))
385+
self.assertEquals(id, 42)
386+
387+
name = self.databaseTemplate.query_for_object("select name from animal where name = 'cottonmouth'", required_type=types.StringType)
388+
self.assertEquals(name, "cottonmouth")
389+
357390
class AbstractDatabaseTemplateTestCase(unittest.TestCase):
358391
def __init__(self, methodName='runTest'):
359392
unittest.TestCase.__init__(self, methodName)
@@ -522,6 +555,14 @@ def testProgrammaticStaticInsert(self):
522555

523556
name = self.databaseTemplate.query_for_object("SELECT name FROM animal WHERE category = 'kill_bill_viper'", required_type=types.StringType)
524557
self.assertEquals(name, "black mamba")
558+
559+
def testProgrammaticStaticInsertWithInsertApi(self):
560+
self.databaseTemplate.execute("DELETE FROM animal")
561+
id = self.databaseTemplate.insert_and_return_id("INSERT INTO animal (name, category, population) VALUES ('black mamba', 'kill_bill_viper', 1)")
562+
self.assertEquals(id, 1)
563+
564+
name = self.databaseTemplate.query_for_object("SELECT name FROM animal WHERE category = 'kill_bill_viper'", required_type=types.StringType)
565+
self.assertEquals(name, "black mamba")
525566

526567
def testProgrammaticInsertWithBoundVariables(self):
527568
self.databaseTemplate.execute("DELETE FROM animal")
@@ -537,6 +578,20 @@ def testProgrammaticInsertWithBoundVariables(self):
537578
name = self.databaseTemplate.query_for_object("select name from animal where name = 'cottonmouth'", required_type=types.StringType)
538579
self.assertEquals(name, "cottonmouth")
539580

581+
def testProgrammaticInsertWithBoundVariablesWithInsertApi(self):
582+
self.databaseTemplate.execute("DELETE FROM animal")
583+
id = self.databaseTemplate.insert_and_return_id("INSERT INTO animal (name, category, population) VALUES (?, ?, ?)", ('black mamba', 'kill_bill_viper', 1))
584+
self.assertEquals(id, 1)
585+
586+
name = self.databaseTemplate.query_for_object("SELECT name FROM animal WHERE category = 'kill_bill_viper'", required_type=types.StringType)
587+
self.assertEquals(name, "black mamba")
588+
589+
id = self.databaseTemplate.insert_and_return_id("INSERT INTO animal (name, category, population) VALUES (%s, %s, %s)", ('cottonmouth', 'kill_bill_viper', 1))
590+
self.assertEquals(id, 2)
591+
592+
name = self.databaseTemplate.query_for_object("select name from animal where name = 'cottonmouth'", required_type=types.StringType)
593+
self.assertEquals(name, "cottonmouth")
594+
540595
class MySQLDatabaseTemplateTestCase(AbstractDatabaseTemplateTestCase):
541596
def __init__(self, methodName='runTest'):
542597
AbstractDatabaseTemplateTestCase.__init__(self, methodName)

‎test/springpythontest/databaseTransactionTestCases.py‎

Lines changed: 7 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -64,6 +64,13 @@ def testInsertingRowsIntoTheDatabase(self):
6464
name = self.dt.query_for_object("SELECT name FROM animal WHERE name = 'black mamba'", required_type=types.StringType)
6565
self.assertEquals(name, "black mamba")
6666

67+
def testInsertingRowsIntoTheDatabaseWithInsertApi(self):
68+
id = self.dt.insert_and_return_id("INSERT INTO animal (name) VALUES (?)", ('black mamba',))
69+
self.assertEquals(id, 1)
70+
71+
name = self.dt.query_for_object("SELECT name FROM animal WHERE name = 'black mamba'", required_type=types.StringType)
72+
self.assertEquals(name, "black mamba")
73+
6774
def testInsertingTwoRowsWithoutaTransactionButManuallyCommitted(self):
6875
self.dt.execute("INSERT INTO animal (name) VALUES (?)", ('black mamba',))
6976
self.dt.execute("INSERT INTO animal (name) VALUES (?)", ('copperhead',))

0 commit comments

Comments
 (0)
X Tutup