On 8/15/15 8:07 PM, dbbbit wrote:
we have to execute our sql in limited form in our company,
like `sqlstore.execute('select id from foos where is_foo = %s and
create_time>%', (1, datetime.now())`,
which sqlstore.execute is defined like:
defexecute(self,sql,args=None,executemany=False):
the `sql` param is string type, `args` is a tuple of values
I want to use sqlalchemy core to construct the `sql` and `args`,
is that a way to translate all sqlalchemy's insert(), select() ... to
the given form?
you want to use compile() to produce a Compiled object which then gives
you a string.
This is introduced in the FAQ at
http://docs.sqlalchemy.org/en/rel_1_0/faq/sqlexpressions.html#how-do-i-render-sql-expressions-as-strings-possibly-with-bound-parameters-inlined.
You'll want to use a dialect that has paramstyle='format' since you want
%s with positional. Supposing the MySQL dialect:
from sqlalchemy.dialects import mysql
dialect = mysql.dialect()
stmt = select([table]).where(table.c.bar == 5)
compiled = stmt.compile(dialect=dialect)
sql_string = str(compiled) # or unicode(compiled)
params = compiled.params # this is a dictionary
positional_params = [params[key] for key in compiled.positiontup] #
this is positional
... or if you have the params separate, like a list for executemany
my_params = [{dict1...}, {dict2..}, ...]
positional_params = [
dict((key, paramdict[key]) for key in compiled.positiontup)
for paramdict in my_params
]
now above, what you are *losing* is that SQLAlchemy's datatypes can also
process the values given, which some datatypes need for some DBAPIs. If
you need to get at that then you need to look in compiled.binds, which
is a dictionary of name->BindParam, and in each BindParam is a type
object, e.g. TypeEngine, and for each of those datatypes you need to get
at their processor using type.bind_processor() and then apply it:
for key in my_param_dict:
bind = compiled.binds[key]
type = bind.type
processor = type.bind_processor(dialect)
if processor is not None:
my_param_dict[key] = processor(my_param_dict[key])
again that's only if you need types to take effect for binds.
Then you have the same issue on the result side. I'm not sure what "def
execute()" returns. If it returns a DBAPI cursor, that can be
theoretically sent right to a ResultProxy with your Compiled object and
you can get typing behavior on the result side as well, but it would
need some state built up for it in the form of an ExecutionContext object.
It would also be possible, with a good amount of creativity, to build
your custom "def execute()" right into a fake DBAPI or Dialect object
and have the whole thing transparently operating underneath an ordinary
SQLAlchemy Engine. Whether you stick with the FAQ recipe or get all
the way into this is all about how ambitious you want to be (though
obviously, the best approach of all is just to convince your company
that an artificial "execute()" method is not really worth the
inconvenience of disallowing better tooling).
--
You received this message because you are subscribed to the Google
Groups "sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send
an email to sqlalchemy+unsubscr...@googlegroups.com
<mailto:sqlalchemy+unsubscr...@googlegroups.com>.
To post to this group, send email to sqlalchemy@googlegroups.com
<mailto:sqlalchemy@googlegroups.com>.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
