Re: [sqlalchemy] Filtering for element in PgArray column
On 08/13/2012 10:13 PM, Michael Bayer wrote: we can keep turning the crank here, here's a full series of examples to make this happen: Thanks for the example, that's definitely something I'll need sooner or later, already implemented my own PostgreSQL POINT data type and was wondering how to use compile properly. But I was also wondering how to bind variables into a query (using the sqlalchemy.orm.query.Query object on the session) without stuffing them in the string, for a quick and dirty solution. Query.params() to the rescue: rows = session.query(Model)\ .filter(text(array_column @ ARRAY[:element_value]))\ .params(element_value='1234') I suppose this is valid usage, I mean the query works as expected. -- .oO V Oo. Work Hard, Increase Production, Prevent Accidents, and Be Happy! ;) -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Re: is there a reason why lower() isn't a column operation ?
well, that's a lot of good reasons! -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Re: is there a reason why lower() isn't a column operation ?
also, just to clarify - i didn't want a case insensitive compare but a specific sql generated just to give a quick illustration With this table structure: CREATE TABLE names ( id serial not null primary key , name varchar(255) not null ); CREATE UNIQUE INDEX names_name_uidx ON names(lower(name)); Postgres and Oracle will optimize this search against the index: SELECT * FROM names WHERE lower(name) = '%s' % search.lower() Doing an ilike or similar won't work -- while the effect is a case insensitive search, the search is actually case sensitive. The nice things about function based indexes like this, is that you can both preserve uniqueness on a string ( usernames, email addresses, etc ) and also gain a little speed on your most often used lookups. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] Filtering for element in PgArray column
On Aug 14, 2012, at 4:40 AM, Vlad K. wrote: On 08/13/2012 10:13 PM, Michael Bayer wrote: we can keep turning the crank here, here's a full series of examples to make this happen: Thanks for the example, that's definitely something I'll need sooner or later, already implemented my own PostgreSQL POINT data type and was wondering how to use compile properly. But I was also wondering how to bind variables into a query (using the sqlalchemy.orm.query.Query object on the session) without stuffing them in the string, for a quick and dirty solution. Query.params() to the rescue: rows = session.query(Model)\ .filter(text(array_column @ ARRAY[:element_value]))\ .params(element_value='1234') I suppose this is valid usage, I mean the query works as expected. yeah or do that, sure ! -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] Re: is there a reason why lower() isn't a column operation ?
On Aug 14, 2012, at 10:46 AM, Jonathan Vanasco wrote: also, just to clarify - i didn't want a case insensitive compare but a specific sql generated just to give a quick illustration With this table structure: CREATE TABLE names ( id serial not null primary key , name varchar(255) not null ); CREATE UNIQUE INDEX names_name_uidx ON names(lower(name)); Postgres and Oracle will optimize this search against the index: SELECT * FROM names WHERE lower(name) = '%s' % search.lower() Doing an ilike or similar won't work -- while the effect is a case insensitive search, the search is actually case sensitive. The nice things about function based indexes like this, is that you can both preserve uniqueness on a string ( usernames, email addresses, etc ) and also gain a little speed on your most often used lookups. in 0.8 you'll be able to add whatever functions and operators to Column and friends that you'd like, specific to types.All three email threads I dealt with yesterday regarded the ability to associate new operators with types so that feature has been added as of yesterday, just needs more docs. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Re: how to get into PG database, is the url the right way? newbie question
any ideas?? basically the idea is how to search inside a database being
outside the database, especifically through SqlAlchemy from OpenLayers?
On Monday, August 13, 2012 9:23:43 PM UTC+2, Gery wrote:
Hello, I'm new around here and I've been using SQLalchemy (SA) for a
while. I work with PostGis (PG), OpenLayers (OL), ExtJS, GeoExtJS and now
with the great SA and GeoAlchemy. I have one problem, I created a model
where I defined one table of my PG database, it has a url like this: url =
'postgresql://postgres:password@localhost:5432/pgdb'. In my HTML, I have
some maps displayed with OL and a toolbar built with ExtJS and GeoExtJS. I
put a search button there using ExtJS, and the OL protocol code to get the
data. This protocol has an url option, in this way:
var searchformPanel = new Ext.form.FormPanel(
{
width: 250,
bodyStyle: 'padding:5px',
labelAlign: 'top',
defaults:
{
anchor: '100%'
},
protocol: new OpenLayers.Protocol.HTTP(
{
url: 'http://localhost/mop/py/dbmodel.py',
format: new OpenLayers.Format.GeoJSON()
}
),
items:
etc,etc..
my problem is that in this url I wrote the whole path where my model
script is located, but after pressing the button I got nothing. I think I
need something else rather than only pointing the whole path and the python
script in the url mentioned above, is that correct? I've searched how to
connect sqlalchemy to extjs in google but didn't find any that solved this
doubt.
Any support is very welcome, thanks in advance.
Best regards,
Gery
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To view this discussion on the web visit
https://groups.google.com/d/msg/sqlalchemy/-/06H8WaCTV7cJ.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] Re: how to get into PG database, is the url the right way? newbie question
On Monday, August 13, 2012 9:23:43 PM UTC+2, Gery wrote:
Hello, I'm new around here and I've been using SQLalchemy (SA) for a
while. I work with PostGis (PG), OpenLayers (OL), ExtJS, GeoExtJS and now
with the great SA and GeoAlchemy. I have one problem, I created a model
where I defined one table of my PG database, it has a url like this: url =
'postgresql://postgres:password@localhost:5432/pgdb'. In my HTML, I have
some maps displayed with OL and a toolbar built with ExtJS and GeoExtJS. I
put a search button there using ExtJS, and the OL protocol code to get the
data. This protocol has an url option, in this way:
var searchformPanel = new Ext.form.FormPanel(
{
width: 250,
bodyStyle: 'padding:5px',
labelAlign: 'top',
defaults:
{
anchor: '100%'
},
protocol: new OpenLayers.Protocol.HTTP(
{
url: 'http://localhost/mop/py/dbmodel.py',
format: new OpenLayers.Format.GeoJSON()
}
),
items:
etc,etc..
my problem is that in this url I wrote the whole path where my model
script is located, but after pressing the button I got nothing. I think I
need something else rather than only pointing the whole path and the python
script in the url mentioned above, is that correct? I've searched how to
connect sqlalchemy to extjs in google but didn't find any that solved this
doubt.
Any support is very welcome, thanks in advance.
Best regards,
Gery
I'm probably missing something here, but are you running any kind of
web framework? What happens when you visit
http://localhost/mop/py/dbmodel.py in your web browser?
You need something running on the server which accepts HTTP requests
and responds with whatever data OpenLayers is expecting. There are
plenty of web frameworks to choose from
(http://wiki.python.org/moin/WebFrameworks/) - I happen to like
Pyramid, but you may want to start with something smaller such as
Flask.
Hope that helps,
Simon
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] Re: how to get into PG database, is the url the right way? newbie question
Hello Simon,
Thanks for your answer, acttually I don't use any web framework, I just did
a html page and put Openlayers, GeoExt, and Ext code there. To display
points/lines/polylines/rasters/etc., I use Mapserver, so I connect my
PostGis database with OpenLayers. After putting the
http://localhost/mop/py/dbmodel.py link, I get the whole model (I think
this should be my model, is it right?), so the Apache works well =):
#created: 30 July 2012
#updated: 12 August 2012
#Gery
from sqlalchemy import *
from sqlachemy import create_engine, MetaData, Table
from sqlalchemy.orm import *
from sqlalchemy.orm import mapper, relation, backref, sessionmaker
from sqlalchemy.ext.declarative import declarative_base
# Setup the url, database engine and session
url = 'postgresql://postgres:password@localhost:5432/mop'
engine = create_engine(url, echo=True)
session = sessionmaker(bind=engine)
session = Session()
# Setup the declarative extension and metadata
Base = declarative_base(metadata=metadata)
metadata = MetaData(engine)
# Define the model classes
class Boreholes(Base):
__tablename__ = 'boreholes_point_wgs84'
__table_args__ = {'autoload':True}
# DDL Extensions for geometry specific DDL
GeometryDDL(Boreholes.__table__)
Do I really need a web framework? I know some people use GeoAlchemy and
SqlAlchemy with Pylons, but I don't want to use that, well in general any
web framework. What could it be that something you mentioned that I need?
I've been reading that with PHP I can make the connection to do the HTTP
requests/responds, am I right? is this the unique way or solution to do
this?
Thanks again
I'm probably missing something here, but are you running any kind of
web framework? What happens when you visit
http://localhost/mop/py/dbmodel.py in your web browser?
You need something running on the server which accepts HTTP requests
and responds with whatever data OpenLayers is expecting. There are
plenty of web frameworks to choose from
(http://wiki.python.org/moin/WebFrameworks/) - I happen to like
Pyramid, but you may want to start with something smaller such as
Flask.
Hope that helps,
Simon
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To view this discussion on the web visit
https://groups.google.com/d/msg/sqlalchemy/-/M6kFU5TjOsAJ.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] Re: how to get into PG database, is the url the right way? newbie question
On Tue, Aug 14, 2012 at 6:39 PM, Gery geryherb...@gmail.com wrote:
Hello Simon,
Thanks for your answer, acttually I don't use any web framework, I just did
a html page and put Openlayers, GeoExt, and Ext code there. To display
points/lines/polylines/rasters/etc., I use Mapserver, so I connect my
PostGis database with OpenLayers. After putting the
http://localhost/mop/py/dbmodel.py link, I get the whole model (I think this
should be my model, is it right?), so the Apache works well =):
#created: 30 July 2012
#updated: 12 August 2012
#Gery
from sqlalchemy import *
from sqlachemy import create_engine, MetaData, Table
from sqlalchemy.orm import *
from sqlalchemy.orm import mapper, relation, backref, sessionmaker
from sqlalchemy.ext.declarative import declarative_base
# Setup the url, database engine and session
url = 'postgresql://postgres:password@localhost:5432/mop'
engine = create_engine(url, echo=True)
session = sessionmaker(bind=engine)
session = Session()
# Setup the declarative extension and metadata
Base = declarative_base(metadata=metadata)
metadata = MetaData(engine)
# Define the model classes
class Boreholes(Base):
__tablename__ = 'boreholes_point_wgs84'
__table_args__ = {'autoload':True}
# DDL Extensions for geometry specific DDL
GeometryDDL(Boreholes.__table__)
Do I really need a web framework? I know some people use GeoAlchemy and
SqlAlchemy with Pylons, but I don't want to use that, well in general any
web framework. What could it be that something you mentioned that I need?
I've been reading that with PHP I can make the connection to do the HTTP
requests/responds, am I right? is this the unique way or solution to do
this?
Thanks again
Hi Gery,
At the moment, your javascript code (running in your web browser) is
making an HTTP request to Apache. Apache is simply returning the
contents of the requested file, so your javascript is receiving a
string of Python code that it has no way of interpreting. I'm afraid
this will never work.
What you need is for Apache to *execute* the python script and return
the contents to your browser. There are lots of ways of doing this,
but you'll really need to at least have a basic understanding of web
technologies. You will have to understand how OpenLayers requests data
from the server, know how to extract query parameters from the
request, turn those into a database query, format the results as JSON
and return them back to the browser. I'm afraid all of those things
are outside the scope of this list.
Sorry I can't be more help,
Simon
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
RE: [sqlalchemy] Re: how to get into PG database, is the url the right way? newbie question
Many thanks Simon, very cool explanation, I'm grateful for your neat support. I'll look for that in google and OpenLayers, about to execute the python script and Apache, I think it has something to do with modwsgi but not sure. If you could point me to some useful links for any of the ways (perahps the easiest one) of doing this, I'd be really grateful, but you already helped me a lot, thanks man. Cheers, Gery Hi Gery, At the moment, your javascript code (running in your web browser) is making an HTTP request to Apache. Apache is simply returning the contents of the requested file, so your javascript is receiving a string of Python code that it has no way of interpreting. I'm afraid this will never work. What you need is for Apache to *execute* the python script and return the contents to your browser. There are lots of ways of doing this, but you'll really need to at least have a basic understanding of web technologies. You will have to understand how OpenLayers requests data from the server, know how to extract query parameters from the request, turn those into a database query, format the results as JSON and return them back to the browser. I'm afraid all of those things are outside the scope of this list. Sorry I can't be more help, Simon -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
