[sqlalchemy] Postgrsql Array of CIDR and return type as String

[Peter Hudec]

Hi,

I have found, that sqlalcheny should convert poctgresql ARRAY fri LIST.
This works fine with the TEXT.  See example in some other post 

http://stackoverflow.com/questions/20699196/python-list-to-postgresql-array

(, "['new', 'updated', 'values']")


If I change the column type to CIDR ARRAY I get


(, "'{127.0.0.1/32,::1/128}'")

postgresl version: 9.4 /debian jessie/
python: 2.7.9
bottle (0.12.9)
bottle-pgsql (0.2)
bottle-sqlalchemy (0.4.3)
psycopg2 (2.6.1)
SQLAlchemy (1.0.11)


Please could anybody help me why the ARRAY of CIDR is not converted to the LIST?


  best regards
Peter Hudec

-- 
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 https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] Modifying records across multiple modules?

[Simon King]

OK, so you’re not actually getting Customer objects back from your query. What 
does your call to session.query() look like? For this to work, it really ought 
to be something like “session.query(Customer)”. I suspect you are doing 
something like “session.query(Customer.id, Customer.name, …)” instead.

Simon

> On 3 Feb 2016, at 17:43, Alex Hall  wrote:
> 
> I'm on the Gmail site, so am not sure I can reply in-line. Sorry.
> 
> This is a basic table class, like
> class Customer(base):
> __tablename__ = "customers"
> name = Column(String(50)),
> ...
> 
> When I print the type:
> 
> And repr():
> (2, u'Powersports Etc', 5554443210L, u'ahall+dbte...@autodist.com', True)
> 
> 
> On 2/3/16, Simon King  wrote:
>> On Wed, Feb 3, 2016 at 3:54 PM, Alex Hall  wrote:
>> 
>>> Hello list,
>>> I'm new to SQLAlchemy, but not to Python. I have an application that's
>>> coming together, and relies on SQLAlchemy to talk to a database for
>>> many of the app's functions. Listing tables, listing records, updating
>>> records, pulling records for internal use, and so on.
>>> 
>>> My app is working, but so far I've been writing the framework and GUI
>>> with a bit of SQLite just to check that things are working how I want.
>>> Now, though, I'm getting into my first "real" user-facing database
>>> task: taking the values from a dialog and updating a record according
>>> to those values. Thus far, I'm having no luck.
>>> 
>>> My organization for now is DBInterface.py, which holds all my table
>>> definitions, database details, and my base, session, and engine
>>> objects. I can hear the groans from here; I do plan to move the table
>>> definitions into a module of their own at some point, there simply
>>> hasn't been a need yet. GUIManager.py imports DBInterface, and handles
>>> all the GUI stuff, as the name suggests. It's where, eventually, I'll
>>> take user input and use it to update records by calling functions from
>>> DBInterface. That's the problem, though. In GUIManager, I have a
>>> simple test:
>>> 
>>> self.records[self.selectedRecordIndex].name="test name" #records is
>>> the list of objects returned by querying the current table
>>> 
>>> Which errors out every time:
>>> AttributeError: can't set attribute
>>> 
>>> (Yes, "name" is an attribute name of my Customer class.) From what
>>> I've read thus far, updating records is as easy as modifying their
>>> properties and calling session.commit(). That isn't working, though. I
>>> imagine the problem is that the records in a query aren't the same as
>>> the records originally created, and modify/commit only works on those
>>> originals. I'm not sure if that's right, though. If it is, how could I
>>> modify the originals, given that I run a new query each time the user
>>> selects a table name in my GUI's list of names? If I'm wrong, how
>>> would I update the record attributes and save the changes back to the
>>> database? I think I'm picturing this whole thing wrong, to be honest.
>>> Thanks for any help, and please let me know if I need to provide more
>>> code or context.
>>> 
>>> 
>> Is your query against a single mapped class, or is it against some set of
>> columns? What do you get if you write:
>> 
>>  print type(self.records[self.selectedRecordIndex])
>>  print repr(self.records[self.selectedRecordIndex])
>> 
>> ...at the point where you are trying to set the name?
>> 
>> Simon
>> 
>> --
>> 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 https://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 https://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 https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] Postgrsql Array of CIDR and return type as String

[Mike Bayer]

On 02/03/2016 05:36 AM, Peter Hudec wrote:

Hi,

I have found, that sqlalcheny should convert poctgresql ARRAY fri LIST.
This works fine with the TEXT.  See example in some other post

http://stackoverflow.com/questions/20699196/python-list-to-postgresql-array

|(,"['new', 'updated', 'values']")


If I change the column type to CIDR ARRAY I get


(, "'{127.0.0.1/32,::1/128}'")

postgresl version: 9.4 /debian jessie/
python: 2.7.9
bottle (0.12.9)
bottle-pgsql (0.2)
bottle-sqlalchemy (0.4.3)
psycopg2 (2.6.1)
SQLAlchemy (1.0.11)


Please could anybody help me why the ARRAY of CIDR is not converted to
the LIST?


typically the psycopg2 driver is responsible for recognizing the string 
form of datatypes coming back from the server and converting them. 
While SQLAlchemy's type objects are capable of doing the same thing, in 
the case of Postgresql we tend to not get into it, because psycopg2 
almost always has their own converter, or worse, they add one in at some 
version which then conflicts with ours.


So in this case I'd gather psycopg2 isn't handling the convert, because 
CIDR ARRAY is a little unusual.


you should be able to adapt the recipe at 
http://docs.sqlalchemy.org/en/rel_1_0/dialects/postgresql.html#using-enum-with-array, 
which is a similar workaround for ARRAY of ENUM.   Here it is, works great:


import sqlalchemy as sa
from sqlalchemy.dialects.postgresql import ARRAY, CIDR
import re

class ArrayOfCIDR(ARRAY):

def bind_expression(self, bindvalue):
return sa.cast(bindvalue, self)

def result_processor(self, dialect, coltype):
super_rp = super(ArrayOfCIDR, self).result_processor(
dialect, coltype)

def handle_raw_string(value):
inner = re.match(r"^{(.*)}$", value).group(1)
return inner.split(",") if inner else []

def process(value):
if value is None:
return None
return super_rp(handle_raw_string(value))
return process

from sqlalchemy import Table, Column, Integer, create_engine, MetaData, 
select


metadata = MetaData()
t = Table(
'mydata', metadata,
Column('id', Integer, primary_key=True),
Column('data', ArrayOfCIDR(CIDR))

)

e = create_engine("postgresql://scott:tiger@localhost/test", echo=True)
metadata.drop_all(e)
metadata.create_all(e)

e.execute(t.insert(), data=['127.0.0.1/32', '::1/128'])

value = e.scalar(select([t.c.data]))
print value[0]
print value[1]









best regards
Peter Hudec
|

--
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 https://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 https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] SQL connections rising into the hundreds / thousands

[Daniel Cochran]

I recently switched to SQL Alchemy to take advantage of connection pooling, 
but I notice that my SQL server will have anywhere from 30-60 open 
connections throughout the day, ("threads_connected"), and sometimes it 
randomly climbs up to the max of 2000 and just completely falls over. This 
climb has nothing to do with queries per second, though, sometimes it will 
happen at 2am when traffic is very low.

The obvious culprit to the high # of connections is that one of my API 
endpoints that connects to the SQL instance is not closing connections, 
though I'm not sure where that would be happening. Here is my configuration:

class SqlAlchemy(object):
  """A light abstraction for SQL Alchemy."""


  def __init__(self, connection):
base = automap_base()
self.engine = sql.create_engine(connection,
max_overflow=2,
pool_size=10,
pool_recycle=120,
pool_timeout=15)


base.prepare(self.engine, reflect=True)


# These models reflect the tables in your database.
self.models = base.classes
self.Session = scoped_session(sessionmaker(bind=self.engine))


# Ping the connection to make sure it's valid. Per:
# http://docs.sqlalchemy.org/en/latest/core/pooling.html
@sql.event.listens_for(self.engine, 'engine_connect')
def ping_connection(connection, branch):  # pylint: 
disable=unused-variable
  if branch:
# "branch" refers to a sub-connection of a connection,
# we don't want to bother pinging on these.
return


  try:
# run a SELECT 1.   use a core select() so that
# the SELECT of a scalar value without a table is
# appropriately formatted for the backend
connection.scalar(sql.select([1]))
  except sql.exc.DBAPIError as err:
# catch SQLAlchemy's DBAPIError, which is a wrapper
# for the DBAPI's exception.  It includes a .connection_invalidated
# attribute which specifies if this connection is a "disconnect"
# condition, which is based on inspection of the original exception
# by the dialect in use.
if err.connection_invalidated:
  # run the same SELECT again - the connection will re-validate
  # itself and establish a new connection.  The disconnect detection
  # here also causes the whole connection pool to be invalidated
  # so that all stale connections are discarded.
  connection.scalar(sql.select([1]))
else:
  raise


  def insert(self, table, data):
"""Insert one record into a SQL table.


Args:
  table: A string representing a table name that exists in self.engine.
  data: A dict of key/value pairs representing the inserted record.


Returns:
  A dict of the newly entered log.
"""
session = self.get_session()
record_as_dict = {}

try:
  model = getattr(self.models, table)
  record = model(**data)
  session.add(record)
  session.commit()
  record_as_dict = to_dict(record)
except Exception, err:
  raise Exception(err)
finally:
  self.close_session()


return record_as_dict

  def close_session(self):
 """Close a SQL Alchemy Session."""
return self.Session.remove()

  def get_session(self):
"""Returns a SQL Alchemy Session."""
return self.Session()


In my API, I then would do something like this on POST, where 
get_sqlAlchemy returns a singleton instance of the class above via the 
webapp2 registry:

  def post(self):
record = get_sqlAlchemy().insert('MyTable', self.request.body)
self.response.set_status(201)
self.response.out.write(json.EncodeJSON(record))

I may just be wildly misunderstanding how connection pooling works, but my 
desire would be that no more than 12 connections are ever open with the 
server, as I'm using Google App Engine and I believe the max concurrent 
connections is 12:

https://cloud.google.com/appengine/docs/python/cloud-sql/#Python_Connect_to_your_database

How does my configuration above fail to limit the connections?

-- 
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 https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] Session.flush() before Session.begin()

[Mike Bayer]

On 02/03/2016 09:39 PM, jemer...@spiresystems.com wrote:

We're using SQLAlchemy in a GUI application and have been experiencing
some challenges integrating the ORM Session with our usage model. (This
is difficult to change as we have ported from a legacy system to
SQLAlchemy.)

We're currently using autocommit=True. When the user edits a record we
create a new session, load the record from the database, and allow them
to interact with the data. When they save the record we validate and
flush the changes. Since we don't want to hold database transactions
open for a long period of time, we start a transaction once the user has
clicked save. Unfortunately, as soon as we call Session.begin() the
session will call flush() outside the context of the transaction.


ah, that's some old behavior but yes, that will happen *if you have 
dirty state* in the session.   The Session can't keep it's contract of 
being able to roll back state to a point that matches what's in the 
database if it doesn't flush out dirty state beforehand; because when a 
rollback happens, it wants to just expire all the state, which means 
your pre-transaction changes will be lost.


You can disable this by passing the flag 
_enable_transaction_accounting=False, but you then the state of the 
session will be out of sync after a rollback.





We have tried a couple approaches to work around this, to very limited
success:

First, we set _enable_transaction_accounting=False, but this causes
Session._expunge_states() to fail with an AttributeError accessing
SessionTransaction._deleted.


well that's just a bug.  Feel free to file a test case for that and it's 
likely a simple fix.




Subsequently we tried using Connection.begin() to "hide" the transaction
from the Session, but this causes problems when there is an error
causing the transaction to rollback as the Session and the database are
now inconsistent with each other.


that would be my next idea, so if you do need to have rollbacks cause 
the object state to reset itself and remain consistent with the 
beginning of the transaction, this indicates 
_enable_transaction_accounting won't work for you.


If you need the Session to expire its state such that it matches what 
was present at the beginning of the transaction, then I'm not sure how 
you intend to have dirty state from before the transaction lying around 
unflushed. Syncing up with a rollback necessarily means that we 
expire all in-memory state and we have to load it again.   Your 
pre-transaction changes however would be lost.


Only the approach of doing a full in-memory rollback to the previous 
state of the objects would get around this, but there is no such ORM in 
the Python world I'm familiar with which performs such a task, while 
it's "simple" for things like scalar attributes, it quickly becomes 
massively complicated for collections and object graphs.






Most recently, I put on my mad science gear and have attempted to pickle
the __dict__ and InstanceState to restore these on rollback, but this
doesn't seem to work. (My understanding of session internals is still
fairly weak, so perhaps I'm simply missing something or perhaps this is
an unworkable solution.)

It's my sincere hope that we can find a solution that does not involve
replacing SQLAlchemy with another ORM.


im not sure why you need to use begin() at all?  in autocommit mode, the 
user presses save, you just call flush().  flush() always uses a 
transaction internally.  That's originally how the Session was meant to 
be used, having an explicitly open transaction was an optional feature.


Since you're looking for an ORM that can rollback a transaction and 
simultaneously reset the state of the objects to exactly what they were 
at the beginning of that transaction, there is no ORM I'm aware of that 
can do this any differently than SQLAlchemy does.   More simplistic ORMs 
like Django / Peewee etc. have no concept of expiration-on-rollback, and 
after a transaction rollback your objects have exactly the same 
in-memory state as before.   SQLAlchemy offers this mode of operation 
both through the _enable_transaction_accounting=False (with small 
bugfixes apparently) as well as the use of an external transaction.








Thanks,
   James Emerton

--
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 https://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 

Re: [sqlalchemy] Modifying records across multiple modules?

[Simon King]

On Wed, Feb 3, 2016 at 3:54 PM, Alex Hall  wrote:

> Hello list,
> I'm new to SQLAlchemy, but not to Python. I have an application that's
> coming together, and relies on SQLAlchemy to talk to a database for
> many of the app's functions. Listing tables, listing records, updating
> records, pulling records for internal use, and so on.
>
> My app is working, but so far I've been writing the framework and GUI
> with a bit of SQLite just to check that things are working how I want.
> Now, though, I'm getting into my first "real" user-facing database
> task: taking the values from a dialog and updating a record according
> to those values. Thus far, I'm having no luck.
>
> My organization for now is DBInterface.py, which holds all my table
> definitions, database details, and my base, session, and engine
> objects. I can hear the groans from here; I do plan to move the table
> definitions into a module of their own at some point, there simply
> hasn't been a need yet. GUIManager.py imports DBInterface, and handles
> all the GUI stuff, as the name suggests. It's where, eventually, I'll
> take user input and use it to update records by calling functions from
> DBInterface. That's the problem, though. In GUIManager, I have a
> simple test:
>
> self.records[self.selectedRecordIndex].name="test name" #records is
> the list of objects returned by querying the current table
>
> Which errors out every time:
> AttributeError: can't set attribute
>
> (Yes, "name" is an attribute name of my Customer class.) From what
> I've read thus far, updating records is as easy as modifying their
> properties and calling session.commit(). That isn't working, though. I
> imagine the problem is that the records in a query aren't the same as
> the records originally created, and modify/commit only works on those
> originals. I'm not sure if that's right, though. If it is, how could I
> modify the originals, given that I run a new query each time the user
> selects a table name in my GUI's list of names? If I'm wrong, how
> would I update the record attributes and save the changes back to the
> database? I think I'm picturing this whole thing wrong, to be honest.
> Thanks for any help, and please let me know if I need to provide more
> code or context.
>
>
Is your query against a single mapped class, or is it against some set of
columns? What do you get if you write:

  print type(self.records[self.selectedRecordIndex])
  print repr(self.records[self.selectedRecordIndex])

...at the point where you are trying to set the name?

Simon

-- 
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 https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] Session.flush() before Session.begin()

[James Emerton]

> On Feb 3, 2016, at 7:07 PM, Mike Bayer  wrote:
> 
> On 02/03/2016 09:39 PM, jemer...@spiresystems.com wrote:
>> We're using SQLAlchemy in a GUI application and have been experiencing
>> some challenges integrating the ORM Session with our usage model. (This
>> is difficult to change as we have ported from a legacy system to
>> SQLAlchemy.)
>> 
>> We're currently using autocommit=True. When the user edits a record we
>> create a new session, load the record from the database, and allow them
>> to interact with the data. When they save the record we validate and
>> flush the changes. Since we don't want to hold database transactions
>> open for a long period of time, we start a transaction once the user has
>> clicked save. Unfortunately, as soon as we call Session.begin() the
>> session will call flush() outside the context of the transaction.
> 
> ah, that's some old behavior but yes, that will happen *if you have dirty 
> state* in the session.   The Session can't keep it's contract of being able 
> to roll back state to a point that matches what's in the database if it 
> doesn't flush out dirty state beforehand; because when a rollback happens, it 
> wants to just expire all the state, which means your pre-transaction changes 
> will be lost.
> 
> You can disable this by passing the flag 
> _enable_transaction_accounting=False, but you then the state of the session 
> will be out of sync after a rollback.

Since the record objects are directly edited by the user, we will be 
accumulating dirty state outside the context of the transaction. (We also 
experimented with holding a transaction open while the record was being edited, 
but this wasn’t workable for several reasons.)

>> We have tried a couple approaches to work around this, to very limited
>> success:
>> 
>> First, we set _enable_transaction_accounting=False, but this causes
>> Session._expunge_states() to fail with an AttributeError accessing
>> SessionTransaction._deleted.
> 
> well that's just a bug.  Feel free to file a test case for that and it's 
> likely a simple fix.

Will do.

> 
>> 
>> Subsequently we tried using Connection.begin() to "hide" the transaction
>> from the Session, but this causes problems when there is an error
>> causing the transaction to rollback as the Session and the database are
>> now inconsistent with each other.
> 
> that would be my next idea, so if you do need to have rollbacks cause the 
> object state to reset itself and remain consistent with the beginning of the 
> transaction, this indicates _enable_transaction_accounting won't work for you.
> 
> If you need the Session to expire its state such that it matches what was 
> present at the beginning of the transaction, then I'm not sure how you intend 
> to have dirty state from before the transaction lying around unflushed. 
> Syncing up with a rollback necessarily means that we expire all in-memory 
> state and we have to load it again.   Your pre-transaction changes however 
> would be lost.
> 
> Only the approach of doing a full in-memory rollback to the previous state of 
> the objects would get around this, but there is no such ORM in the Python 
> world I'm familiar with which performs such a task, while it's "simple" for 
> things like scalar attributes, it quickly becomes massively complicated for 
> collections and object graphs.
> 
> 
> 
>> 
>> Most recently, I put on my mad science gear and have attempted to pickle
>> the __dict__ and InstanceState to restore these on rollback, but this
>> doesn't seem to work. (My understanding of session internals is still
>> fairly weak, so perhaps I'm simply missing something or perhaps this is
>> an unworkable solution.)
>> 
>> It's my sincere hope that we can find a solution that does not involve
>> replacing SQLAlchemy with another ORM.
> 
> im not sure why you need to use begin() at all?  in autocommit mode, the user 
> presses save, you just call flush().  flush() always uses a transaction 
> internally.  That's originally how the Session was meant to be used, having 
> an explicitly open transaction was an optional feature.

Unfortunately, some processes in our application execute SQL directly, often 
operating on records which were very recently inserted into the database. Our 
models have save methods that validate the record and then issue a flush. This 
means that flush will be called several times during the course of a 
transaction. (Yay legacy code!)

> Since you're looking for an ORM that can rollback a transaction and 
> simultaneously reset the state of the objects to exactly what they were at 
> the beginning of that transaction, there is no ORM I'm aware of that can do 
> this any differently than SQLAlchemy does.   More simplistic ORMs like Django 
> / Peewee etc. have no concept of expiration-on-rollback, and after a 
> transaction rollback your objects have exactly the same in-memory state as 
> before.   SQLAlchemy offers this mode of operation both