Re: [sqlalchemy] Old but Gold - SQLA + Twisted
hello all :) from the past years, i've been working on solutions to the problem described by Jean (we are co-workers, and we use twisted and sqlalchemy, A LOT), and as everybody may already know, it's a very complicated combination, since we have to do a lot of code around to have a consistent application. ok, that's not bad, but ... since we are a team of several developers, from 'grey haired pythonistas' to 'fishes in a barrel', it's hard to mantain a quality standard, so we decided to take ALL our codebase based on twisted and sqlalchemy to give it a try on creating a better integration between them, specially if you are more acquainted to asynchronous programming. *RESULTS* 1. we were able to create an asynchronous sqlalchemy, but it relies on deep object copy, so every promise (or deferred, on twisted's language) generates an overhead that is not welcome in our standards (or every programmer with a brain, lol); 2. without deep copy, furthermore we were able to have again a fully promise version of sqlalchemy, but we had to left aside all the ORM codebase and work only with low level sqlalchemy. that's a good result, but again, we'll have a problem with the fishes not using the ORM. *THE PROBLEM* SO, after reading a lot about the internals of sqlalchemy AND tons of solutions out there (sAsync, etc, etc) we always ended up with the same problem: thread safety. the orm design of sqlalchemy, specially the session states, are all designed to be thread safe (Mike, correct me if i'm wrong), so from there the work might be huge, but we are willing to work on a solution - specially if our main rdbms, postgres, have one (if not the best) of dialects implementations in sa. this solution we expect even to publish for others to use :) * **THREAD SAFETY* ok, as Jean already stated (those are actually my words) that 99,9% of the programmers who uses sqlalchemy are quite happy with the solutions it already provides, which are in fact very, very usable. we have no problem with that. but, what if we want to go further on this? i mean, we can already control a lot of things in sqlalchemy. i mean, a lot, really. it is very flexible in almost all aspects. but ... why it isn't when the subject is the session state? or it is and we are missing something? *FINAL THOUGHTS* the bottom line is not about twisted, just to be clear, but to implement a non thread safe session state system that can work with async frameworks (gevent, twisted, tornado) and so on. is that really possible? my best regards, richard. On 09/05/2014 11:23 AM, Jean Marcel Duvoisin Shmidt wrote: Hi everyone! I have a more complex and architectural question to make, it will be a bit long, but I want to make myself clear as I already have done some research ;D in our company we have some *really* cool stuff made out of SQLA, we wrote over its ORM an abstraction to allow us to build the same schema on the same database but in different 'database level schema' (from Postgresql), allowing us to use the same codebase, same database, same architecture and separate clients content easily. We also managed to build a EAV (entity, attribute, value) database in top of that, that allow us to change the database schema any time, any way, and get it running it without touching the DB... aaannnddd in top of that we got a Schematics to represent the database schema and allow us to work as a ORM over the SQLAORM. Yeh its crazy, but it works, and its really *fast*. We handle thousands of client in that way, and we are happy with it, not counting how flexible is the code and the database now. But here comes the problem. We want to scale it up (not just out), and we made some tests on the past with SQLA + Twisted using, Gevent, Twisted, Psycopg. First we managed successfully to integrate the SQLA-Core + SQLA-ORM + Our EAV-ORM with twisted using twisted.deferToThread, with works nicely but it is not exactly what we wanted. This takes out all the purpose of using twisted in the first place, as we end up with a threaded model, where queries are running in threads, and we have a main thread managing all of that. What we really wanted is to make the app *assyncronous* on top of the ORM. Than we managed to use assync features of Psycopg with twisted, and in a similar mode that is done with Alchimia. We where able to make it work with SQLA-Core. But we found out that the ORM is completely designed with the synchronous paradigm, for logical reasons of course - as 99.9% of the users will use it synchronously, and we though that the best way to make it work is overwrite the Session, Query, SessionQuery and other classes to make it work with the deferred concept (collection, CollectionAdapter, Attributes, and so on). As an app developer there is no problem to create a session and all the ambient to handle every request on SQLA. With provides isolation avoiding any concurrent problems, this
[sqlalchemy] Calling a PostgreSQL function taking %ROWTYPE
I have a PostgreSQL function discounted_price(packages%ROWTYPE), where
packages is a table. I would like to add a property on the model
corresponding
to the packages table which returns the result of the discounted_price
function.
Currently I have this which works:
@property
def discounted_price(self):
return connection.execute('select discounted_price(packages.*) from
packages '
'where package_id=' +
str(self.package_id)).first()
But I'd like to call the function with sqlalchemy.sql.func, I'm just unsure
of how to
pass the packages%ROWTYPE corresponding to the model instance. Something
like this:
@property
def discounted_price(self):
return connection.query(func.discounted_price(**I don't know what goes
here**))
Is it possible to do this?
--
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.
Re: [sqlalchemy] Old but Gold - SQLA + Twisted
so anytime you say: myobject.someattribute you return a promise? because with the ORM, any attribute can trigger a SQL query. On Sep 8, 2014, at 9:08 AM, Richard Gerd Kuesters rich...@humantech.com.br wrote: hello all :) from the past years, i've been working on solutions to the problem described by Jean (we are co-workers, and we use twisted and sqlalchemy, A LOT), and as everybody may already know, it's a very complicated combination, since we have to do a lot of code around to have a consistent application. ok, that's not bad, but ... since we are a team of several developers, from 'grey haired pythonistas' to 'fishes in a barrel', it's hard to mantain a quality standard, so we decided to take ALL our codebase based on twisted and sqlalchemy to give it a try on creating a better integration between them, specially if you are more acquainted to asynchronous programming. RESULTS we were able to create an asynchronous sqlalchemy, but it relies on deep object copy, so every promise (or deferred, on twisted's language) generates an overhead that is not welcome in our standards (or every programmer with a brain, lol); without deep copy, furthermore we were able to have again a fully promise version of sqlalchemy, but we had to left aside all the ORM codebase and work only with low level sqlalchemy. that's a good result, but again, we'll have a problem with the fishes not using the ORM. THE PROBLEM SO, after reading a lot about the internals of sqlalchemy AND tons of solutions out there (sAsync, etc, etc) we always ended up with the same problem: thread safety. the orm design of sqlalchemy, specially the session states, are all designed to be thread safe (Mike, correct me if i'm wrong), so from there the work might be huge, but we are willing to work on a solution - specially if our main rdbms, postgres, have one (if not the best) of dialects implementations in sa. this solution we expect even to publish for others to use :) THREAD SAFETY ok, as Jean already stated (those are actually my words) that 99,9% of the programmers who uses sqlalchemy are quite happy with the solutions it already provides, which are in fact very, very usable. we have no problem with that. but, what if we want to go further on this? i mean, we can already control a lot of things in sqlalchemy. i mean, a lot, really. it is very flexible in almost all aspects. but ... why it isn't when the subject is the session state? or it is and we are missing something? FINAL THOUGHTS the bottom line is not about twisted, just to be clear, but to implement a non thread safe session state system that can work with async frameworks (gevent, twisted, tornado) and so on. is that really possible? my best regards, richard. On 09/05/2014 11:23 AM, Jean Marcel Duvoisin Shmidt wrote: Hi everyone! I have a more complex and architectural question to make, it will be a bit long, but I want to make myself clear as I already have done some research ;D in our company we have some *really* cool stuff made out of SQLA, we wrote over its ORM an abstraction to allow us to build the same schema on the same database but in different 'database level schema' (from Postgresql), allowing us to use the same codebase, same database, same architecture and separate clients content easily. We also managed to build a EAV (entity, attribute, value) database in top of that, that allow us to change the database schema any time, any way, and get it running it without touching the DB... aaannnddd in top of that we got a Schematics to represent the database schema and allow us to work as a ORM over the SQLAORM. Yeh its crazy, but it works, and its really *fast*. We handle thousands of client in that way, and we are happy with it, not counting how flexible is the code and the database now. But here comes the problem. We want to scale it up (not just out), and we made some tests on the past with SQLA + Twisted using, Gevent, Twisted, Psycopg. First we managed successfully to integrate the SQLA-Core + SQLA-ORM + Our EAV-ORM with twisted using twisted.deferToThread, with works nicely but it is not exactly what we wanted. This takes out all the purpose of using twisted in the first place, as we end up with a threaded model, where queries are running in threads, and we have a main thread managing all of that. What we really wanted is to make the app *assyncronous* on top of the ORM. Than we managed to use assync features of Psycopg with twisted, and in a similar mode that is done with Alchimia. We where able to make it work with SQLA-Core. But we found out that the ORM is completely designed with the synchronous paradigm, for logical reasons of course - as 99.9% of the users will use it synchronously, and we though that the best way to make it work is overwrite the Session, Query, SessionQuery and other
Re: [sqlalchemy] Calling a PostgreSQL function taking %ROWTYPE
put text() in there:
func.discounted_price(text(packages.*))
On Sep 8, 2014, at 9:59 AM, Tyler Bondy ty.bo...@gmail.com wrote:
I have a PostgreSQL function discounted_price(packages%ROWTYPE), where
packages is a table. I would like to add a property on the model
corresponding
to the packages table which returns the result of the discounted_price
function.
Currently I have this which works:
@property
def discounted_price(self):
return connection.execute('select discounted_price(packages.*) from
packages '
'where package_id=' +
str(self.package_id)).first()
But I'd like to call the function with sqlalchemy.sql.func, I'm just unsure
of how to
pass the packages%ROWTYPE corresponding to the model instance. Something
like this:
@property
def discounted_price(self):
return connection.query(func.discounted_price(**I don't know what goes
here**))
Is it possible to do this?
--
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.
--
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.
Re: [sqlalchemy] Old but Gold - SQLA + Twisted
hello Mike! yeap, that would be the point. even though the object might already have this value somewhere, the result would be a promise, always. best regards, richard. On 09/08/2014 11:31 AM, Michael Bayer wrote: so anytime you say: myobject.someattribute you return a promise? because with the ORM, any attribute can trigger a SQL query. On Sep 8, 2014, at 9:08 AM, Richard Gerd Kuesters rich...@humantech.com.br mailto:rich...@humantech.com.br wrote: hello all :) from the past years, i've been working on solutions to the problem described by Jean (we are co-workers, and we use twisted and sqlalchemy, A LOT), and as everybody may already know, it's a very complicated combination, since we have to do a lot of code around to have a consistent application. ok, that's not bad, but ... since we are a team of several developers, from 'grey haired pythonistas' to 'fishes in a barrel', it's hard to mantain a quality standard, so we decided to take ALL our codebase based on twisted and sqlalchemy to give it a try on creating a better integration between them, specially if you are more acquainted to asynchronous programming. *RESULTS* 1. we were able to create an asynchronous sqlalchemy, but it relies on deep object copy, so every promise (or deferred, on twisted's language) generates an overhead that is not welcome in our standards (or every programmer with a brain, lol); 2. without deep copy, furthermore we were able to have again a fully promise version of sqlalchemy, but we had to left aside all the ORM codebase and work only with low level sqlalchemy. that's a good result, but again, we'll have a problem with the fishes not using the ORM. *THE PROBLEM* SO, after reading a lot about the internals of sqlalchemy AND tons of solutions out there (sAsync, etc, etc) we always ended up with the same problem: thread safety. the orm design of sqlalchemy, specially the session states, are all designed to be thread safe (Mike, correct me if i'm wrong), so from there the work might be huge, but we are willing to work on a solution - specially if our main rdbms, postgres, have one (if not the best) of dialects implementations in sa. this solution we expect even to publish for others to use :) * **THREAD SAFETY* ok, as Jean already stated (those are actually my words) that 99,9% of the programmers who uses sqlalchemy are quite happy with the solutions it already provides, which are in fact very, very usable. we have no problem with that. but, what if we want to go further on this? i mean, we can already control a lot of things in sqlalchemy. i mean, a lot, really. it is very flexible in almost all aspects. but ... why it isn't when the subject is the session state? or it is and we are missing something? *FINAL THOUGHTS* the bottom line is not about twisted, just to be clear, but to implement a non thread safe session state system that can work with async frameworks (gevent, twisted, tornado) and so on. is that really possible? my best regards, richard. On 09/05/2014 11:23 AM, Jean Marcel Duvoisin Shmidt wrote: Hi everyone! I have a more complex and architectural question to make, it will be a bit long, but I want to make myself clear as I already have done some research ;D in our company we have some *really* cool stuff made out of SQLA, we wrote over its ORM an abstraction to allow us to build the same schema on the same database but in different 'database level schema' (from Postgresql), allowing us to use the same codebase, same database, same architecture and separate clients content easily. We also managed to build a EAV (entity, attribute, value) database in top of that, that allow us to change the database schema any time, any way, and get it running it without touching the DB... aaannnddd in top of that we got a Schematics to represent the database schema and allow us to work as a ORM over the SQLAORM. Yeh its crazy, but it works, and its really *fast*. We handle thousands of client in that way, and we are happy with it, not counting how flexible is the code and the database now. But here comes the problem. We want to scale it up (not just out), and we made some tests on the past with SQLA + Twisted using, Gevent, Twisted, Psycopg. First we managed successfully to integrate the SQLA-Core + SQLA-ORM + Our EAV-ORM with twisted using twisted.deferToThread, with works nicely but it is not exactly what we wanted. This takes out all the purpose of using twisted in the first place, as we end up with a threaded model, where queries are running in threads, and we have a main thread managing all of that. What we really wanted is to make the app *assyncronous* on top of the ORM. Than we managed to use assync features of Psycopg with twisted, and in a similar mode that is done with Alchimia. We where able to make it work with SQLA-Core. But we found out that the ORM is completely designed with the
Re: [sqlalchemy] Old but Gold - SQLA + Twisted
its almost like if it could return a promise, but then some kind of syntactical magic would just handle that we already know it's there, and just hide it, and then just do some kind of deferment so that we can just write the next line of code right below it. because promises and deferreds, it is 100% pre-determined when these will happen! if only this completely predictable, repetitive, boilerplate task of receiving a deferral then waiting til the next line of code in the operation could be...somehow...*automated*. or to put it another way: why are you comfortable with the ORM's implicit SQL on attribute access, but not with gevent's implicit defer on IO ? On Sep 8, 2014, at 10:47 AM, Richard Gerd Kuesters rich...@humantech.com.br wrote: hello Mike! yeap, that would be the point. even though the object might already have this value somewhere, the result would be a promise, always. best regards, richard. On 09/08/2014 11:31 AM, Michael Bayer wrote: so anytime you say: myobject.someattribute you return a promise? because with the ORM, any attribute can trigger a SQL query. On Sep 8, 2014, at 9:08 AM, Richard Gerd Kuesters rich...@humantech.com.br wrote: hello all :) from the past years, i've been working on solutions to the problem described by Jean (we are co-workers, and we use twisted and sqlalchemy, A LOT), and as everybody may already know, it's a very complicated combination, since we have to do a lot of code around to have a consistent application. ok, that's not bad, but ... since we are a team of several developers, from 'grey haired pythonistas' to 'fishes in a barrel', it's hard to mantain a quality standard, so we decided to take ALL our codebase based on twisted and sqlalchemy to give it a try on creating a better integration between them, specially if you are more acquainted to asynchronous programming. RESULTS we were able to create an asynchronous sqlalchemy, but it relies on deep object copy, so every promise (or deferred, on twisted's language) generates an overhead that is not welcome in our standards (or every programmer with a brain, lol); without deep copy, furthermore we were able to have again a fully promise version of sqlalchemy, but we had to left aside all the ORM codebase and work only with low level sqlalchemy. that's a good result, but again, we'll have a problem with the fishes not using the ORM. THE PROBLEM SO, after reading a lot about the internals of sqlalchemy AND tons of solutions out there (sAsync, etc, etc) we always ended up with the same problem: thread safety. the orm design of sqlalchemy, specially the session states, are all designed to be thread safe (Mike, correct me if i'm wrong), so from there the work might be huge, but we are willing to work on a solution - specially if our main rdbms, postgres, have one (if not the best) of dialects implementations in sa. this solution we expect even to publish for others to use :) THREAD SAFETY ok, as Jean already stated (those are actually my words) that 99,9% of the programmers who uses sqlalchemy are quite happy with the solutions it already provides, which are in fact very, very usable. we have no problem with that. but, what if we want to go further on this? i mean, we can already control a lot of things in sqlalchemy. i mean, a lot, really. it is very flexible in almost all aspects. but ... why it isn't when the subject is the session state? or it is and we are missing something? FINAL THOUGHTS the bottom line is not about twisted, just to be clear, but to implement a non thread safe session state system that can work with async frameworks (gevent, twisted, tornado) and so on. is that really possible? my best regards, richard. On 09/05/2014 11:23 AM, Jean Marcel Duvoisin Shmidt wrote: Hi everyone! I have a more complex and architectural question to make, it will be a bit long, but I want to make myself clear as I already have done some research ;D in our company we have some *really* cool stuff made out of SQLA, we wrote over its ORM an abstraction to allow us to build the same schema on the same database but in different 'database level schema' (from Postgresql), allowing us to use the same codebase, same database, same architecture and separate clients content easily. We also managed to build a EAV (entity, attribute, value) database in top of that, that allow us to change the database schema any time, any way, and get it running it without touching the DB... aaannnddd in top of that we got a Schematics to represent the database schema and allow us to work as a ORM over the SQLAORM. Yeh its crazy, but it works, and its really *fast*. We handle thousands of client in that way, and we are happy with it, not counting how flexible is the code and the database now. But here comes the problem. We want to
Re: [sqlalchemy] Calling a PostgreSQL function taking %ROWTYPE
Hmm, I've tried that and gotten the following error:
ProgrammingError: (ProgrammingError) function discounted_price(unknown) is
not unique
LINE 1: SELECT discounted_price('packages.*') AS discounted_price_1
^
HINT: Could not choose a best candidate function. You might need to add
explicit type casts.
'SELECT discounted_price(%(discounted_price_2)s) AS discounted_price_1 \n
LIMIT %(param_1)s' {'param_1': 1, 'discounted_price_2': 'packages.*'}
--
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.
Re: [sqlalchemy] Calling a PostgreSQL function taking %ROWTYPE
Ahh I think I've got it now, thanks a bunch.
On Monday, September 8, 2014 12:13:41 PM UTC-4, Michael Bayer wrote:
SQLAlchemy will send exactly what string you want. But you have to figure
out what Postgresql wants. Maybe you don’t want those quotes in there?
func.discounted_price(text(‘packages.*’)) won’t quote like that:
from sqlalchemy import func, text
print func.discounted_price(text('packages.*'))
discounted_price(packages.*)
On Sep 8, 2014, at 12:10 PM, Tyler Bondy ty.b...@gmail.com javascript:
wrote:
Hmm, I've tried that and gotten the following error:
ProgrammingError: (ProgrammingError) function discounted_price(unknown) is
not unique
LINE 1: SELECT discounted_price('packages.*') AS discounted_price_1
^
HINT: Could not choose a best candidate function. You might need to add
explicit type casts.
'SELECT discounted_price(%(discounted_price_2)s) AS discounted_price_1 \n
LIMIT %(param_1)s' {'param_1': 1, 'discounted_price_2': 'packages.*'}
--
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+...@googlegroups.com javascript:.
To post to this group, send email to sqlal...@googlegroups.com
javascript:.
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.
[sqlalchemy] Nested transactions with _branch
Using SQLAlchemy 0.9.7 against a PostgreSQL 9.2 database.
with e as an Engine:
i = 1
with e.begin() as conn:
x = conn.execute('select * from test01')
print x.fetchall()
with conn.contextual_connect() as conn2:
with conn2.begin():
i += 1
conn2.execute('insert into marius.test01 values (%s)' % str(i))
x = conn2.execute('select * from test01')
print x.fetchall()
x = conn.execute('select * from test01')
print x.fetchall()
raise Exception(HELLO)
This will actually commit a row to the table test, rather than commit
nothing.
Should the transaction not be carried over to the branched connection?
This is not PostgreSQL specific.
Something like this solves this potential issue:
class Connection(sqlalchemy.engine.Connectable):
Override of :class:`sqlalchemy.engine.Connection` to allow allow
nested connections (non-forking).
This is achieved by using a stack increment decrement.
def __init__(self, engine, connection=None, close_with_result=False,
_branch=False, _execution_options=None,
_dispatch=None, _has_events=None, transaction=None):
...
self.__transaction = transaction
...
def _branch(self):
Return a new Connection which references this Connection's
engine and connection; but does not have close_with_result
enabled,
and also whose close() method does nothing.
This is used to execute sub statements within a single
execution,
usually an INSERT statement.
return self.engine._connection_cls(
self.engine,
self._Connection__connection,
_branch=True,
_has_events=self._has_events,
_dispatch=self.dispatch,
transaction=self.__transaction)
What is the standard way to deal with this case? Subclassing Connection to
implement this?
--
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.
Re: [sqlalchemy] Nested transactions with _branch
On Sep 8, 2014, at 12:27 PM, Marius van Niekerk marius.v.niek...@gmail.com
wrote:
Using SQLAlchemy 0.9.7 against a PostgreSQL 9.2 database.
with e as an Engine:
i = 1
with e.begin() as conn:
x = conn.execute('select * from test01')
print x.fetchall()
with conn.contextual_connect() as conn2:
with conn2.begin():
i += 1
conn2.execute('insert into marius.test01 values (%s)' % str(i))
x = conn2.execute('select * from test01')
print x.fetchall()
x = conn.execute('select * from test01')
print x.fetchall()
raise Exception(HELLO)
This will actually commit a row to the table test, rather than commit nothing.
Should the transaction not be carried over to the branched connection? This
is not PostgreSQL specific.
I observed this the other day when working on some refactoring of connection
mechanics and apparently forgot to capture it, so that is here:
https://bitbucket.org/zzzeek/sqlalchemy/issue/3190/transactional-state-isnt-carried-over-with
This isn't a use case that comes up very often; usually people are binding the
Connection to a Session within a test scenario which has an explicit check for
the Connection being present already.But I think I hit it when trying to
work up an engine-level transactional test fixture.
Something like this solves this potential issue:
class Connection(sqlalchemy.engine.Connectable):
Override of :class:`sqlalchemy.engine.Connection` to allow allow
nested connections (non-forking).
This is achieved by using a stack increment decrement.
def __init__(self, engine, connection=None, close_with_result=False,
_branch=False, _execution_options=None,
_dispatch=None, _has_events=None, transaction=None):
...
self.__transaction = transaction
...
def _branch(self):
Return a new Connection which references this Connection's
engine and connection; but does not have close_with_result
enabled,
and also whose close() method does nothing.
This is used to execute sub statements within a single
execution,
usually an INSERT statement.
return self.engine._connection_cls(
self.engine,
self._Connection__connection,
_branch=True,
_has_events=self._has_events,
_dispatch=self.dispatch,
transaction=self.__transaction)
yup that's the easy part, the tedious part is new tests in
test/engine/test_transaction.py. If you have an interest in submitting
patches...
What is the standard way to deal with this case? Subclassing Connection to
implement this?
for now you'd need to work around, probably doing isinstance(conn, Connection)
and then not doing the connect() call, if you have to be in the transaction
already. Or forcing in the __transaction attribute into the new Connection
(you'd have to hit it as _Connection__transaction).
For my engine-level fixture, I implemented a full wrapper for everything, see
https://review.openstack.org/#/c/113153/12/oslo/db/sqlalchemy/utils.py.
--
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.
Re: [sqlalchemy] Old but Gold - SQLA + Twisted
the thing is i'm comfortable with all that. what i'm trying to automate, somehow, is the need to let my fishes transit around deferreds (or threads) objects that once belongs to one session and can easily be lost if its states changes in this process, including proper session handling (open, use, close). i'm not talking about something magical here. let's say i need to integrate my app with another network apps -- that may have some latency, or want to spawn a task based on some object that needs to be found first, then just say to the user: hey, i'll work on it and that's it, i'll request a promise and close the connection to the user. On 09/08/2014 11:59 AM, Michael Bayer wrote: its almost like if it could return a promise, but then some kind of syntactical magic would just handle that we already know it's there, and just hide it, and then just do some kind of deferment so that we can just write the next line of code right below it. because promises and deferreds, it is 100% pre-determined when these will happen! if only this completely predictable, repetitive, boilerplate task of receiving a deferral then waiting til the next line of code in the operation could be...somehow...*automated*. or to put it another way: why are you comfortable with the ORM's implicit SQL on attribute access, but not with gevent's implicit defer on IO ? On Sep 8, 2014, at 10:47 AM, Richard Gerd Kuesters rich...@humantech.com.br mailto:rich...@humantech.com.br wrote: hello Mike! yeap, that would be the point. even though the object might already have this value somewhere, the result would be a promise, always. best regards, richard. On 09/08/2014 11:31 AM, Michael Bayer wrote: so anytime you say: myobject.someattribute you return a promise? because with the ORM, any attribute can trigger a SQL query. On Sep 8, 2014, at 9:08 AM, Richard Gerd Kuesters rich...@humantech.com.br mailto:rich...@humantech.com.br wrote: hello all :) from the past years, i've been working on solutions to the problem described by Jean (we are co-workers, and we use twisted and sqlalchemy, A LOT), and as everybody may already know, it's a very complicated combination, since we have to do a lot of code around to have a consistent application. ok, that's not bad, but ... since we are a team of several developers, from 'grey haired pythonistas' to 'fishes in a barrel', it's hard to mantain a quality standard, so we decided to take ALL our codebase based on twisted and sqlalchemy to give it a try on creating a better integration between them, specially if you are more acquainted to asynchronous programming. *RESULTS* 1. we were able to create an asynchronous sqlalchemy, but it relies on deep object copy, so every promise (or deferred, on twisted's language) generates an overhead that is not welcome in our standards (or every programmer with a brain, lol); 2. without deep copy, furthermore we were able to have again a fully promise version of sqlalchemy, but we had to left aside all the ORM codebase and work only with low level sqlalchemy. that's a good result, but again, we'll have a problem with the fishes not using the ORM. *THE PROBLEM* SO, after reading a lot about the internals of sqlalchemy AND tons of solutions out there (sAsync, etc, etc) we always ended up with the same problem: thread safety. the orm design of sqlalchemy, specially the session states, are all designed to be thread safe (Mike, correct me if i'm wrong), so from there the work might be huge, but we are willing to work on a solution - specially if our main rdbms, postgres, have one (if not the best) of dialects implementations in sa. this solution we expect even to publish for others to use :) * **THREAD SAFETY* ok, as Jean already stated (those are actually my words) that 99,9% of the programmers who uses sqlalchemy are quite happy with the solutions it already provides, which are in fact very, very usable. we have no problem with that. but, what if we want to go further on this? i mean, we can already control a lot of things in sqlalchemy. i mean, a lot, really. it is very flexible in almost all aspects. but ... why it isn't when the subject is the session state? or it is and we are missing something? *FINAL THOUGHTS* the bottom line is not about twisted, just to be clear, but to implement a non thread safe session state system that can work with async frameworks (gevent, twisted, tornado) and so on. is that really possible? my best regards, richard. On 09/05/2014 11:23 AM, Jean Marcel Duvoisin Shmidt wrote: Hi everyone! I have a more complex and architectural question to make, it will be a bit long, but I want to make myself clear as I already have done some research ;D in our company we have some *really* cool stuff made out of SQLA, we wrote over its ORM an abstraction to allow us to build the same schema on the same database but
Re: [sqlalchemy] Old but Gold - SQLA + Twisted
Hi! Maybe I wasn't very clear with what I was suggesting think of the code: value = object.attribute be like: do_stuff_and_reply_user(myvar): return return_to_user(process_it(myvar)) #deferreds again xD object.attribute.addCallback(do_stuff_and_reply_user) #or something like deff = session.query(MyClass).all() defrr.addCallback(...) I know, it doesn’t makes much sense when you first see it but let me explain it better. The propose of the ORM is to provide the high level abstraction for queries, allowing programmers forget about data representation and only focus on data and data relations. This can be stupid for simple queries, but when you have some N to N relation, with multiple foreign keys or some little more complex relationship it can be really handy and help a lot in the codding process. Using deferred/promises will allow use this power of the ORM abstraction in a assync environment easily. When you are programming it's only needed to know that when data is retrieved on some object instead of the data itself, you get the deferred. it could be something like: MyAssyncBase(SQLABase): def __get__(...): #some sort of wrapping. # #and when you got the value, somewere, somehow: defrr.callback(value) #or let it to the gevent, twisted, tornado or any other asynchronous framework for python. I really don't have a clue now, but I'm looking for it! That's why we are asking here because maybe you will know where is the best path to follow On Mon, Sep 8, 2014 at 2:05 PM, Richard Gerd Kuesters rich...@humantech.com.br wrote: the thing is i'm comfortable with all that. what i'm trying to automate, somehow, is the need to let my fishes transit around deferreds (or threads) objects that once belongs to one session and can easily be lost if its states changes in this process, including proper session handling (open, use, close). i'm not talking about something magical here. let's say i need to integrate my app with another network apps -- that may have some latency, or want to spawn a task based on some object that needs to be found first, then just say to the user: hey, i'll work on it and that's it, i'll request a promise and close the connection to the user. On 09/08/2014 11:59 AM, Michael Bayer wrote: its almost like if it could return a promise, but then some kind of syntactical magic would just handle that we already know it’s there, and just hide it, and then just do some kind of deferment so that we can just write the next line of code right below it. because promises and deferreds, it is 100% pre-determined when these will happen! if only this completely predictable, repetitive, boilerplate task of receiving a deferral then waiting til the next line of code in the operation could be...somehow…*automated*….. or to put it another way: why are you comfortable with the ORM’s implicit SQL on attribute access, but not with gevent’s implicit “defer on IO” ? On Sep 8, 2014, at 10:47 AM, Richard Gerd Kuesters rich...@humantech.com.br wrote: hello Mike! yeap, that would be the point. even though the object might already have this value somewhere, the result would be a promise, always. best regards, richard. On 09/08/2014 11:31 AM, Michael Bayer wrote: so anytime you say: myobject.someattribute you return a promise? because with the ORM, any attribute can trigger a SQL query. On Sep 8, 2014, at 9:08 AM, Richard Gerd Kuesters rich...@humantech.com.br wrote: hello all :) from the past years, i've been working on solutions to the problem described by Jean (we are co-workers, and we use twisted and sqlalchemy, A LOT), and as everybody may already know, it's a very complicated combination, since we have to do a lot of code around to have a consistent application. ok, that's not bad, but ... since we are a team of several developers, from 'grey haired pythonistas' to 'fishes in a barrel', it's hard to mantain a quality standard, so we decided to take ALL our codebase based on twisted and sqlalchemy to give it a try on creating a better integration between them, specially if you are more acquainted to asynchronous programming. *RESULTS* 1. we were able to create an asynchronous sqlalchemy, but it relies on deep object copy, so every promise (or deferred, on twisted's language) generates an overhead that is not welcome in our standards (or every programmer with a brain, lol); 2. without deep copy, furthermore we were able to have again a fully promise version of sqlalchemy, but we had to left aside all the ORM codebase and work only with low level sqlalchemy. that's a good result, but again, we'll have a problem with the fishes not using the ORM. *THE PROBLEM* SO, after reading a lot about the internals of sqlalchemy AND tons of solutions out there (sAsync, etc, etc) we always ended up with the same problem: thread
[sqlalchemy] Update an object without first selecting it in ORM
When using ORM, is there a way to update without first selecting an object? For example, if I am updating a User record's name in the database and I have its primary key, I'd like to just execute one query and update it straight away. But in order to do so I must run 2 queries, first to select the object with the primary key, then to update it. Is there a way around this so that I can update with 1 query and not 2? -- 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.
[sqlalchemy] Plain SQLAlchemy vs ORM
In one of the YouTube videos on SQLAlchemy Mike said that some of the large users of SQLAlchemy prefer to use plain SQLAlchemy instead of SQLAlchemy ORM. What are the reasons behind that? I'm wondering because I started off with ORM and though I like it I've been wondering whether I might be better off going with plain SQLAlchemy and foregoing the ORM. What exactly would I lose if I give up the ORM that I may not realize right now? What kinds of web applications (written in Pyramid in this case) are better suited to use or not use ORM? -- 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.
Re: [sqlalchemy] Update an object without first selecting it in ORM
use the update() method: http://docs.sqlalchemy.org/en/rel_0_9/orm/query.html?highlight=query.update#sqlalchemy.orm.query.Query.update On Sep 8, 2014, at 3:12 PM, alchemy1 veerukrish...@hotmail.com wrote: When using ORM, is there a way to update without first selecting an object? For example, if I am updating a User record's name in the database and I have its primary key, I'd like to just execute one query and update it straight away. But in order to do so I must run 2 queries, first to select the object with the primary key, then to update it. Is there a way around this so that I can update with 1 query and not 2? -- 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. -- 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.
Re: [sqlalchemy] Plain SQLAlchemy vs ORM
On Sep 8, 2014, at 3:14 PM, alchemy1 veerukrish...@hotmail.com wrote: In one of the YouTube videos on SQLAlchemy Mike said that some of the large users of SQLAlchemy prefer to use plain SQLAlchemy instead of SQLAlchemy ORM. What are the reasons behind that? the Core by itself is much much faster than the ORM in terms of fetching rows and being able to emit INSERT/UPDATE/DELETE directly with a lot less Python overhead. Using the Core also corresponds to SQL execution patterns in a 1-1 fashion. With the ORM, you can construct SELECTs as directly as you can with the Core, but for persistence, the flush() process is a lot more indirect. If your app doesn't have a data model that is very object oriented then the ORM might get in the way more than it helps. I'm wondering because I started off with ORM and though I like it I've been wondering whether I might be better off going with plain SQLAlchemy and foregoing the ORM. What exactly would I lose if I give up the ORM that I may not realize right now? What kinds of web applications (written in Pyramid in this case) are better suited to use or not use ORM? IMHO I'd always use the ORM because I always write a traditional object model around my data. I drop into Core only if I'm really trying to skip some process in the ORM that I know is expensive, however the whole direction of the ORM especially in 1.0 is to allow better ORM-level workarounds for these things. this isn't merged yet, but you can take a peek at the performance profiling stuff I'm doing right now in the branch at https://bitbucket.org/zzzeek/sqlalchemy/src/fa7c8f88113d2e769274dee4aa4247b9c9aadec8/examples/performance/__init__.py?at=ticket_3100. -- 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.
[sqlalchemy] Re: Plain SQLAlchemy vs ORM
From personal experience, others may disagree: For most functions in a webapp (or most other apps), you want to use the ORM -- which gives you a natural way to surface and manipulate the underlying data as Python objects. When it comes to admin tools, maintenance work, migrations, etc -- you often don't need the ORM, and it can actually be a bit of a hinderance in writing some complex queries. In these situations, you usually don't care to interact with Python objects, you just want to issue updates/inserts/delete every quickly -- and your selects are tuned to return raw data, not structured objects. Since you're doing a pyramid webapp... if you gave up the ORM now, you'd mostly be missing out on happiness and work velocity. You'd be doing everything the hard way, trying to optimize away problems that don't exist. If you come into a situation where the ORM is being an issue (which is very rare in the bulk of web app logic), you should know enough SqlAlchemy by then to be able to not-use the ORM for certain tasks. 95% of the SqlAlchemy code in my largest project is the ORM. The other 5% are edge cases for specific performance gains. -- 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.
[sqlalchemy] building a sqlalchemy postgresql cidr contains query
I have a model defined with:
from app import db
from sqlalchemy.dialects import postgresql
class TableIpAddress(db.Model):
__tablename__ = 'ipaddress'
idipaddress = db.Column( postgresql.UUID, primary_key=True )
ipaddress = db.Column( postgresql.CIDR, index=True, nullable=False )
I would like to do something like the following:
ip = '192.168.0.0/16'
db.session.query( TableIpAddress.ipaddress.op('')(ip) ).all()
The table's ipaddress field is CIDR. But in the first line of the error
message following, it is trying to coerce to INET. It should parameterize
as CIDR? What be the proper way of making this query function properly?
sqlalchemy.exc.DBAPIError: (ParameterError) could not pack parameter
$1::pg_catalog.inet for transfer
CODE: --PIO
LOCATION: CLIENT
DETAIL: '192.168.0.0/16'
HINT: Try casting the parameter to 'text', then to the target type.
POSITION: 0
STATEMENT: [prepared]
sql_parameter_types: ['pg_catalog.inet']
results: ('anon_1' 'BOOLEAN')
statement_id: py:0x7fc6b125e2e8
string:
SELECT ipaddress.ipaddress $1 AS anon_1
FROM ipaddress
CONNECTION: [idle in block]
client_address: 127.0.0.1/32
client_port: 45620
version:
PostgreSQL 9.3.5 on x86_64-unknown-linux-gnu, compiled by gcc (Ubuntu
4.8.2-19ubuntu1) 4.8.2, 64-bit
CONNECTOR: [IP4] pq://polestar:***@127.0.0.1:5432/polestar
category: None
DRIVER: postgresql.driver.pq3.Driver 'SELECT ipaddress.ipaddress
%(ipaddress_1)s AS anon_1 \nFROM ipaddress' {'ipaddress_1':
'192.168.0.0/16'}
--
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.
--
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.
Re: [sqlalchemy] building a sqlalchemy postgresql cidr contains query
in your stack trace is this weird string postgresql.driver.pq3, googling it
reveals this is the py-postgresql driver. This is not at all a well-known
driver and I suggest switching to psycopg2, which will probably just work in
this case.
On Sep 8, 2014, at 4:23 PM, Raymond Burkholder r...@oneunified.net wrote:
I have a model defined with:
from app import db
from sqlalchemy.dialects import postgresql
class TableIpAddress(db.Model):
__tablename__ = 'ipaddress'
idipaddress = db.Column( postgresql.UUID, primary_key=True )
ipaddress = db.Column( postgresql.CIDR, index=True, nullable=False )
I would like to do something like the following:
ip = '192.168.0.0/16'
db.session.query( TableIpAddress.ipaddress.op('')(ip) ).all()
The table's ipaddress field is CIDR. But in the first line of the error
message following, it is trying to coerce to INET. It should parameterize
as CIDR? What be the proper way of making this query function properly?
sqlalchemy.exc.DBAPIError: (ParameterError) could not pack parameter
$1::pg_catalog.inet for transfer
CODE: --PIO
LOCATION: CLIENT
DETAIL: '192.168.0.0/16'
HINT: Try casting the parameter to 'text', then to the target type.
POSITION: 0
STATEMENT: [prepared]
sql_parameter_types: ['pg_catalog.inet']
results: ('anon_1' 'BOOLEAN')
statement_id: py:0x7fc6b125e2e8
string:
SELECT ipaddress.ipaddress $1 AS anon_1
FROM ipaddress
CONNECTION: [idle in block]
client_address: 127.0.0.1/32
client_port: 45620
version:
PostgreSQL 9.3.5 on x86_64-unknown-linux-gnu, compiled by gcc (Ubuntu
4.8.2-19ubuntu1) 4.8.2, 64-bit
CONNECTOR: [IP4] pq://polestar:***@127.0.0.1:5432/polestar
category: None
DRIVER: postgresql.driver.pq3.Driver 'SELECT ipaddress.ipaddress
%(ipaddress_1)s AS anon_1 \nFROM ipaddress' {'ipaddress_1':
'192.168.0.0/16'}
--
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.
--
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.
--
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.
RE: [sqlalchemy] building a sqlalchemy postgresql cidr contains query
in your stack trace is this weird string postgresql.driver.pq3, googling it reveals this is the py-postgresql driver. This is not at all a well-known driver and I suggest switching to psycopg2, which will probably just work in this case. Does psycopg2 parameterize it's queries, and forward the binary data, or does it just build up concatenated strings and pass them to the database client? From my reading, it appeared as though py-postgresql would be a superior driver, as it did parameterization and transmitted parameters in their native binary format. -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. -- 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.
Re: [sqlalchemy] building a sqlalchemy postgresql cidr contains query
psycopg2 doesn't standardize on prepared statements so i think it does string parameterization.I don't believe postgresql offers much advantage to native parameters. On Sep 8, 2014, at 5:24 PM, Raymond Burkholder r...@oneunified.net wrote: in your stack trace is this weird string postgresql.driver.pq3, googling it reveals this is the py-postgresql driver. This is not at all a well-known driver and I suggest switching to psycopg2, which will probably just work in this case. Does psycopg2 parameterize it's queries, and forward the binary data, or does it just build up concatenated strings and pass them to the database client? From my reading, it appeared as though py-postgresql would be a superior driver, as it did parameterization and transmitted parameters in their native binary format. -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. -- 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. -- 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.
Re: [sqlalchemy] Issue with return results
Unfortunately, dumping SQL Server (in favor of Oracle) may not be an
option, due to management concerns and other factors. Still working on it.
However, I did manage to get this working with pymssql. Apparently, there
is a bug with pyodbc and 64-bit python
(see
https://community.vertica.com/vertica/topics/mac_pyodbc_string_encoding_issue).
So, short of applying the fix to the cpp file recompiling pyodbc (see
http://www.vertica-forums.com/viewtopic.php?f=35t=1863p=6174#p6174), the
easy solution is to use pymssql. I believe the upgrade I did from Lion to
Mavericks allows use of 64-bit python now, so this makes sense.
Thanks!
Greg--
On Thursday, September 4, 2014 9:20:53 PM UTC-5, Horcle wrote:
I think I am going to dump SQL Server and just go with Postgres. Much
easier, and less of a headache. Fortunately, we are not yet in production.
Thanks!
Greg--
Thanks. I forgot to mention that I had tried adding the encoding scheme to
freetds.conf. I also tried other encoding schemes, all to no avail. I may
try pymssql tomorrow to see what that does. I would have tried mxodbc, but
I am not about to pay $379 for a driver. I may also see if I can get the MS
ODBC driver for Linux to work on my Mac.
I have to say that the MS SQL stuff is a royal PITA, but unfortunately,
that is what I am stuck with at work. Uggh. (;_;)
The version of FreeTDS I have been using has always been 9.1 (although, I
noticed that the Brew formula for it changed in the last few days to 9.1_1,
for what that's worth).
Greg--
On Thursday, September 4, 2014 5:23:02 PM UTC-5, Michael Bayer wrote:
SQL Server and unix, many things can change:
- UnixODBC version
- FreeTDS version (0.82 and 0.91 have *extremely* different behaviors)
- FreeTDS configuration
The first place I’d look in this case would be your freetds.conf, you
probably need to configure the character set correctly in there.
On Sep 4, 2014, at 5:06 PM, Horcle g...@umn.edu wrote:
I had to reinstall my python dev environment from scratch due to a hd
failure, and in the process something seems to have changed.
When querying against MS SQL using the script (test_conenction.py):
import pyodbc
import sqlalchemy
from sqlalchemy.engine import reflection
from sqlalchemy.engine.reflection import Inspector
def connect():
return pyodbc.connect(
'DRIVER={FreeTDS};SERVER=server.ip.address;'
'DATABASE=STUDY_PARTICIPANT;UID=test;PWD=test;port=1433;CHARSET=utf8;'
'TDS_Version=9.1;')
engine = sqlalchemy.create_engine('mssql+pyodbc://', creator=connect,
encoding='latin1',echo='debug',supports_unicode_binds=False)
conn = engine.connect()
print conn
for row in engine.execute('select 6 * 7 as [Result];'):
print row.Result
insp = reflection.Inspector.from_engine(engine)
table_name = 'irb_desc'
table_names = insp.get_table_names()
if table_name not in table_names:
print 'A: ' + table_name
I used to get the following nice output:
python test_connect.py
2014-08-18 16:15:06,611 INFO sqlalchemy.engine.base.Engine
SELECT default_schema_name FROM
sys.database_principals
WHERE principal_id=database_principal_id()
2014-08-18 16:15:06,611 INFO sqlalchemy.engine.base.Engine ()
2014-08-18 16:15:06,613 DEBUG sqlalchemy.engine.base.Engine Col (
'default_schema_name',)
2014-08-18 16:15:06,614 DEBUG sqlalchemy.engine.base.Engine Row (u'dbo',
)
2014-08-18 16:15:06,616 INFO sqlalchemy.engine.base.Engine SELECT
CAST('test
plain returns' AS VARCHAR(60)) AS anon_1
2014-08-18 16:15:06,616 INFO sqlalchemy.engine.base.Engine ()
2014-08-18 16:15:06,619 INFO sqlalchemy.engine.base.Engine SELECT
CAST('test
unicode returns' AS NVARCHAR(60)) AS anon_1
2014-08-18 16:15:06,619 INFO sqlalchemy.engine.base.Engine ()
sqlalchemy.engine.base.Connection object at 0x101877ed0
2014-08-18 16:15:06,639 INFO sqlalchemy.engine.base.Engine select 6 * 7
as [Result];
2014-08-18 16:15:06,639 INFO sqlalchemy.engine.base.Engine ()
2014-08-18 16:15:06,641 DEBUG sqlalchemy.engine.base.Engine Col (
'Result',)
2014-08-18 16:15:06,641 DEBUG sqlalchemy.engine.base.Engine Row (42, )
42
2014-08-18 16:15:06,647 INFO sqlalchemy.engine.base.Engine SELECT [
TABLES_1].[TABLE_NAME]
FROM [INFORMATION_SCHEMA].[TABLES] AS [TABLES_1]WHERE [TABLES_1].[
TABLE_SCHEMA] = CAST(? AS NVARCHAR(max)) AND [TABLES_1].[TABLE_TYPE] = ?
ORDER BY [TABLES_1].[TABLE_NAME]
2014-08-18 16:15:06,647 INFO sqlalchemy.engine.base.Engine ('dbo', 'BASE
TABLE')
2014-08-18 16:15:06,663 DEBUG sqlalchemy.engine.base.Engine Col (
'TABLE_NAME',)
2014-08-18 16:15:06,663 DEBUG sqlalchemy.engine.base.Engine Row (u
'irb_desc', )
2014-08-18 16:15:06,663 DEBUG sqlalchemy.engine.base.Engine Row (u'irbd'
, )
2014-08-18 16:15:06,663 DEBUG sqlalchemy.engine.base.Engine Row (u
'study_desc', )
2014-08-18 16:15:06,664 DEBUG sqlalchemy.engine.base.Engine Row (u
'study_irb', )
2014-08-18 16:15:06,664 DEBUG sqlalchemy.engine.base.Engine
Re: [sqlalchemy] Issue with return results
Looking at that issue, and suggested fix... I think you're best going with that route. the stock apple Python is usually pretty bad, and it seems to be the compile settings apple selected, not python. apple's version is often VERY out of date and has some weird settings. It's screwed me and colleagues up a lot. I'd strongly suggest you do the following: 1. Install a second, custom python [ you can get one from Python.org in a click-installer https://www.python.org/download/mac ]. You can then update your bash so that your console user will use that python, and not break anything on your mac. 2. reinstall all pyodbc and everything else into that custom python's site-packages. you actually need to do all this stuff whenever you have a version bump (2.7.7 to 2.7.8), but some packages magically work even if you don't. i've found that shit just works when you use a python.org interpreter. On Monday, September 8, 2014 7:04:17 PM UTC-4, Horcle wrote: Unfortunately, dumping SQL Server (in favor of Oracle) may not be an option, due to management concerns and other factors. Still working on it. However, I did manage to get this working with pymssql. Apparently, there is a bug with pyodbc and 64-bit python (see https://community.vertica.com/vertica/topics/mac_pyodbc_string_encoding_issue). So, short of applying the fix to the cpp file recompiling pyodbc (see http://www.vertica-forums.com/viewtopic.php?f=35t=1863p=6174#p6174), the easy solution is to use pymssql. I believe the upgrade I did from Lion to Mavericks allows use of 64-bit python now, so this makes sense. Thanks! -- 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.
Re: [sqlalchemy] Issue with return results
Hi Jonathan, For the record, in my current setup, I installed python via Homebrew. Prior to that, I was using the Apple build of python, which would have been the 32-bit version that came with Lion. Greg-- On Mon, Sep 8, 2014 at 6:31 PM, Jonathan Vanasco jonat...@findmeon.com wrote: Looking at that issue, and suggested fix... I think you're best going with that route. the stock apple Python is usually pretty bad, and it seems to be the compile settings apple selected, not python. apple's version is often VERY out of date and has some weird settings. It's screwed me and colleagues up a lot. I'd strongly suggest you do the following: 1. Install a second, custom python [ you can get one from Python.org in a click-installer https://www.python.org/download/mac ]. You can then update your bash so that your console user will use that python, and not break anything on your mac. 2. reinstall all pyodbc and everything else into that custom python's site-packages. you actually need to do all this stuff whenever you have a version bump (2.7.7 to 2.7.8), but some packages magically work even if you don't. i've found that shit just works when you use a python.org interpreter. On Monday, September 8, 2014 7:04:17 PM UTC-4, Horcle wrote: Unfortunately, dumping SQL Server (in favor of Oracle) may not be an option, due to management concerns and other factors. Still working on it. However, I did manage to get this working with pymssql. Apparently, there is a bug with pyodbc and 64-bit python (see https://community. vertica.com/vertica/topics/mac_pyodbc_string_encoding_issue). So, short of applying the fix to the cpp file recompiling pyodbc (see http://www.vertica-forums.com/viewtopic.php?f=35t=1863p=6174#p6174), the easy solution is to use pymssql. I believe the upgrade I did from Lion to Mavericks allows use of 64-bit python now, so this makes sense. Thanks! -- You received this message because you are subscribed to a topic in the Google Groups sqlalchemy group. To unsubscribe from this topic, visit https://groups.google.com/d/topic/sqlalchemy/-i4-GQpXkzY/unsubscribe. To unsubscribe from this group and all its topics, 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. -- 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.
