def execute(self, async_conn):
"""Executes a query within an asyncronous psycopg2 connection
"""
if self.status == self.STATUS_CANCELLED:
return
self.status = self.STATUS_EXECUTING
# Async variant of Connection.execute() in storm/database.py
state = State()
statement = compile(self.expr, state)
stmt = convert_param_marks(statement, "?", "%s")
self._async_cursor = async_conn.cursor()
self._async_conn = async_conn
# This is postgres specific, see storm/databases/postgres.py
self._statement = stmt.encode('utf-8')
self._parameters = tuple(Connection.to_database(state.parameters))
trace("connection_raw_execute", self._conn,
self._async_cursor, self._statement, self._parameters)
self._async_cursor.execute(self._statement,
self._parameters)
# This can happen if another thread cancelled this while the cursor was
# executing. In that case, it is not interested in the retval anymore
if self.status == self.STATUS_CANCELLED:
return
self.status = self.STATUS_FINISHED
GLib.idle_add(self._on_finish)
def execute(self, statement, params=None, noresult=False):
"""NOTE: this is being overriden completely because the
original from the base class expects to receive only a
raw_cursor from raw_execute, and we need to receive also the
rowid, as we cannot set it in the cursor object
Execute a statement with the given parameters.
@type statement: L{Expr} or C{str}
@param statement: The statement to execute. It will be
compiled if necessary.
@param noresult: If True, no result will be returned.
@raise DisconnectionError: Raised when the connection is lost.
Reconnection happens automatically on rollback.
@return: The result of C{self.result_factory}, or None if
C{noresult} is True.
"""
if self._closed:
raise ClosedError("Connection is closed")
self._ensure_connected()
if isinstance(statement, Expr):
if params is not None:
raise ValueError("Can't pass parameters with expressions")
state = State()
statement = self.compile(statement, state)
params = state.parameters
statement = convert_param_marks(statement, "?", self.param_mark)
raw_cursor, rowid = self.raw_execute(statement, params)
if noresult:
self._check_disconnect(raw_cursor.close)
return None
return self.result_factory(self, raw_cursor, rowid)
def test_array_support_with_empty(self):
try:
self.connection.execute("DROP TABLE array_test")
self.connection.commit()
except:
self.connection.rollback()
self.connection.execute("CREATE TABLE array_test "
"(id SERIAL PRIMARY KEY, a INT[])")
variable = ListVariable(IntVariable)
variable.set([])
state = State()
statement = compile(variable, state)
self.connection.execute(
"INSERT INTO array_test VALUES (1, %s)" % statement,
state.parameters)
result = self.connection.execute("SELECT a FROM array_test WHERE id=1")
array = result.get_one()[0]
self.assertTrue(isinstance(array, list))
variable = ListVariable(IntVariable)
result.set_variable(variable, array)
self.assertEquals(variable.get(), [])
def test_expressions_in_union_order_by(self):
# The following statement breaks in postgres:
# SELECT 1 AS id UNION SELECT 1 ORDER BY id+1;
# With the error:
# ORDER BY on a UNION/INTERSECT/EXCEPT result must
# be on one of the result columns
column = SQLRaw("1")
Alias.auto_counter = 0
alias = Alias(column, "id")
expr = Union(Select(alias),
Select(column),
order_by=alias + 1,
limit=1,
offset=1,
all=True)
state = State()
statement = compile(expr, state)
self.assertEquals(
statement, 'SELECT * FROM '
'((SELECT 1 AS id) UNION ALL (SELECT 1)) AS "_1" '
'ORDER BY id+? LIMIT 1 OFFSET 1')
self.assertVariablesEqual(state.parameters, [Variable(1)])
result = self.connection.execute(expr)
self.assertEquals(result.get_one(), (1, ))
def execute(self, statement, params=None, noresult=False):
"""Execute a statement with the given parameters.
@type statement: L{Expr} or C{str}
@param statement: The statement to execute. It will be
compiled if necessary.
@param noresult: If True, no result will be returned.
@return: The result of C{self.result_factory}, or None if
C{noresult} is True.
"""
if self._closed:
raise ClosedError("Connection is closed")
if isinstance(statement, Expr):
if params is not None:
raise ValueError("Can't pass parameters with expressions")
state = State()
statement = self.compile(statement, state)
params = state.parameters
statement = convert_param_marks(statement, "?", self.param_mark)
raw_cursor = self.raw_execute(statement, params)
if noresult:
raw_cursor.close()
return None
return self.result_factory(self, raw_cursor)
def execute(self, statement, params=None, noresult=False):
"""Execute a statement with the given parameters.
@type statement: L{Expr} or C{str}
@param statement: The statement to execute. It will be
compiled if necessary.
@param noresult: If True, no result will be returned.
@raise ConnectionBlockedError: Raised if access to the connection
has been blocked with L{block_access}.
@raise DisconnectionError: Raised when the connection is lost.
Reconnection happens automatically on rollback.
@return: The result of C{self.result_factory}, or None if
C{noresult} is True.
"""
if self._closed:
raise ClosedError("Connection is closed")
if self._blocked:
raise ConnectionBlockedError("Access to connection is blocked")
if self._event:
self._event.emit("register-transaction")
self._ensure_connected()
if isinstance(statement, Expr):
if params is not None:
raise ValueError("Can't pass parameters with expressions")
state = State()
statement = self.compile(statement, state)
params = state.parameters
statement = convert_param_marks(statement, "?", self.param_mark)
raw_cursor = self.raw_execute(statement, params)
if noresult:
self._check_disconnect(raw_cursor.close)
return None
return self.result_factory(self, raw_cursor)
def test_compile_case(self):
"""The Case expr is compiled in a Postgres' CASE expression."""
cases = [(Column("foo") > 3, u"big"), (Column("bar") == None, 4)]
state = State()
statement = compile(Case(cases), state)
assert statement == (
"CASE WHEN (foo > ?) THEN ? WHEN (bar IS NULL) THEN ? END")
assert [3, "big", 4] == [param.get() for param in state.parameters]
def test_compile_case_with_default(self):
"""
If a default is provided, the resulting CASE expression includes
an ELSE clause.
"""
cases = [(Column("foo") > 3, u"big")]
state = State()
statement = compile(Case(cases, default=9), state)
assert "CASE WHEN (foo > ?) THEN ? ELSE ? END" == statement
assert [3, "big", 9] == [param.get() for param in state.parameters]
def test_compile_case_with_expression(self):
"""
If an expression is provided, the resulting CASE expression uses the
simple syntax.
"""
cases = [(1, u"one"), (2, u"two")]
state = State()
statement = compile(Case(cases, expression=Column("foo")), state)
self.assertEqual("CASE foo WHEN ? THEN ? WHEN ? THEN ? END", statement)
self.assertEqual([1, "one", 2, "two"],
[param.get() for param in state.parameters])
def execute(self, async_conn):
"""Executes a query within an asyncronous psycopg2 connection
"""
# Async variant of Connection.execute() in storm/database.py
state = State()
statement = compile(self.expr, state)
stmt = convert_param_marks(statement, "?", "%s")
self._async_cursor = async_conn.cursor()
self._async_conn = async_conn
# This is postgres specific, see storm/databases/postgres.py
self._statement = stmt.encode('utf-8')
self._parameters = tuple(Connection.to_database(state.parameters))
trace("connection_raw_execute", self._conn, self._async_cursor,
self._statement, self._parameters)
self._async_cursor.execute(self._statement, self._parameters)
def test_comparable_expr_subclass(self):
prop1 = self.SubClass.prop1
prop2 = self.SubClass.prop2
prop3 = self.SubClass.prop3
expr = Select(SQLRaw("*"), (prop1 == "value1") & (prop2 == "value2") &
(prop3 == "value3"))
state = State()
statement = compile(expr, state)
self.assertEquals(
statement, "SELECT * FROM mysubtable WHERE "
"mysubtable.column1 = ? AND "
"mysubtable.prop2 = ? AND "
"mysubtable.column3 = ?")
self.assertVariablesEqual(state.parameters, [
CustomVariable("value1"),
CustomVariable("value2"),
CustomVariable("value3")
])
def test_comparable_expr(cls, table):
prop1 = cls.prop1
prop2 = cls.prop2
prop3 = cls.prop3
expr = Select(SQLRaw("*"), (prop1 == "value1") & (prop2 == "value2") &
(prop3 == "value3"))
state = State()
statement = compile(expr, state)
assert statement == ("SELECT * FROM {table} WHERE "
"{table}.column1 = ? AND "
"{table}.prop2 = ? AND "
"{table}.column3 = ?").format(table=table)
assert_variables_equal(state.parameters, [
CustomVariable("value1"),
CustomVariable("value2"),
CustomVariable("value3")
])
def convert_storm_clause_to_string(storm_clause):
"""Convert a Storm expression into a plain string.
:param storm_clause: A Storm expression
A helper function allowing to use a Storm expressions in old-style
code which builds for example WHERE expressions as plain strings.
>>> from lp.bugs.model.bug import Bug
>>> from lp.bugs.model.bugtask import BugTask
>>> from lp.bugs.interfaces.bugtask import BugTaskImportance
>>> from storm.expr import And, Or
>>> print convert_storm_clause_to_string(BugTask)
BugTask
>>> print convert_storm_clause_to_string(BugTask.id == 16)
BugTask.id = 16
>>> print convert_storm_clause_to_string(
... BugTask.importance == BugTaskImportance.UNKNOWN)
BugTask.importance = 999
>>> print convert_storm_clause_to_string(Bug.title == "foo'bar'")
Bug.title = 'foo''bar'''
>>> print convert_storm_clause_to_string(
... Or(BugTask.importance == BugTaskImportance.UNKNOWN,
... BugTask.importance == BugTaskImportance.HIGH))
BugTask.importance = 999 OR BugTask.importance = 40
>>> print convert_storm_clause_to_string(
... And(Bug.title == 'foo', BugTask.bug == Bug.id,
... Or(BugTask.importance == BugTaskImportance.UNKNOWN,
... BugTask.importance == BugTaskImportance.HIGH)))
Bug.title = 'foo' AND BugTask.bug = Bug.id AND
(BugTask.importance = 999 OR BugTask.importance = 40)
"""
state = State()
clause = storm_compile(storm_clause, state)
if len(state.parameters):
parameters = [param.get(to_db=True) for param in state.parameters]
clause = clause.replace('?', '%s') % sqlvalues(*parameters)
return clause