[sqlalchemy] getting error with column name end using geoalchemy2
Hi list,
I am encountering a very strange error and I am scratching my head and got
no idea what is going on.
class Line(Base):
__tablename__ = 'lines'
id = Column(Integer, Sequence('line_id_seq'), primary_key=True)
start = Column(Geometry('POINT'), nullable=False, *index=False*)
*end* = Column(Geometry('POINT'), nullable=False, *index=False*)
On creating this table, it threw a strange error:
*sqlalchemy.exc.ProgrammingError: (ProgrammingError) syntax error at or
near end*
*LINE 1: ...INDEX idx_lines_end ON public.lines USING GIST (end)*
* ^*
* 'CREATE INDEX idx_lines_end ON public.lines USING GIST (end)' {}*
The created table is :
CREATE TABLE lines
(
id integer NOT NULL,
start geometry(Point) NOT NULL,
*end* geometry(Point) NOT NULL,
CONSTRAINT lines_pkey PRIMARY KEY (id)
)
WITH (
OIDS=FALSE
);
ALTER TABLE lines
OWNER TO postgres;
CREATE INDEX idx_lines_start
ON lines
USING gist
(start);
It is weird there are quotes surrounding the word *end* , and although I
have specified *index=False*, for some reason indexs are still created
automatically.
Anything other than *end*, such as *end_, end1 *worked perfectly.
Is end a keyword for *postgis* or *geoalchemy2*?
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] getting error with column name end using geoalchemy2
I once had a very strange error with a table called audit on an oracle
database.
It turned out, that exspecially on oracle audit was a reserved word -
while it worked out fine on any other database.
I would recommend trying to use other column names.
As end is surrounded by double quites your database postgres also know
it as a reserved word. (see
https://stackoverflow.com/questions/5570783/using-end-as-column-name-in-ruby-on-rails-mysql)
Change the column name.
Kind regards
Cornelius
Am 21.06.2014 14:34, schrieb Chung WONG:
Hi list,
I am encountering a very strange error and I am scratching my head and
got no idea what is going on.
class Line(Base):
__tablename__ = 'lines'
id = Column(Integer, Sequence('line_id_seq'), primary_key=True)
start = Column(Geometry('POINT'), nullable=False, *index=False*)
*end* = Column(Geometry('POINT'), nullable=False, *index=False*)
On creating this table, it threw a strange error:
/sqlalchemy.exc.ProgrammingError: (ProgrammingError) syntax error at
or near end/
/LINE 1: ...INDEX idx_lines_end ON public.lines USING GIST (end)/
/ ^/
/ 'CREATE INDEX idx_lines_end ON public.lines USING GIST (end)' {}/
The created table is :
CREATE TABLE lines
(
id integer NOT NULL,
start geometry(Point) NOT NULL,
*end* geometry(Point) NOT NULL,
CONSTRAINT lines_pkey PRIMARY KEY (id)
)
WITH (
OIDS=FALSE
);
ALTER TABLE lines
OWNER TO postgres;
CREATE INDEX idx_lines_start
ON lines
USING gist
(start);
It is weird there are quotes surrounding the word *end* , and although
I have specified *index=False*, for some reason indexs are still
created automatically.
Anything other than *end*, such as *end_, end1 *worked perfectly.
Is end a keyword for *postgis* or *geoalchemy2*?
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
mailto:sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com
mailto:sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] getting error with column name end using geoalchemy2
On 6/21/14, 8:34 AM, Chung WONG wrote:
Hi list,
I am encountering a very strange error and I am scratching my head and
got no idea what is going on.
class Line(Base):
__tablename__ = 'lines'
id = Column(Integer, Sequence('line_id_seq'), primary_key=True)
start = Column(Geometry('POINT'), nullable=False, *index=False*)
*end* = Column(Geometry('POINT'), nullable=False, *index=False*)
On creating this table, it threw a strange error:
/sqlalchemy.exc.ProgrammingError: (ProgrammingError) syntax error at
or near end/
/LINE 1: ...INDEX idx_lines_end ON public.lines USING GIST (end)/
/ ^/
/ 'CREATE INDEX idx_lines_end ON public.lines USING GIST (end)' {}/
The created table is :
CREATE TABLE lines
(
id integer NOT NULL,
start geometry(Point) NOT NULL,
*end* geometry(Point) NOT NULL,
CONSTRAINT lines_pkey PRIMARY KEY (id)
)
WITH (
OIDS=FALSE
);
ALTER TABLE lines
OWNER TO postgres;
CREATE INDEX idx_lines_start
ON lines
USING gist
(start);
It is weird there are quotes surrounding the word *end* , and although
I have specified *index=False*, for some reason indexs are still
created automatically.
Anything other than *end*, such as *end_, end1 *worked perfectly.
Is end a keyword for *postgis* or *geoalchemy2*?
end is likely a reserved word, there's nothing wrong with using it as
a column name as SQLAlchemy will quote it, though the other poster has a
point that it's always better to avoid reserved words if for no other
reason than reducing verbosity.
as far as the Index i don't have an insight on the index being created
or not, if perhaps geoalchemy is involved there, not really sure.See
if changing the Geometry type to something else temporarily changes things.
--
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] getting error with column name end using geoalchemy2
`END` is a reserved keyword in most SQL dialects, including the ANSI sql standards. I think mysql is the only major database that allows it. Here's a link to a collection of all known reserved keywords across databases, with links to relevant documentation for each particular standard. https://www.drupal.org/node/141051 -- 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] Is using column_property preferred over hybrid property in some circumstances?
The documentation regarding column_property and hybrid property says both methods are intended for linking a sql expression to an attribute, which is highly useful. But it is not obvious to junior users which one is preferred. Generally speaking under what circumstances I should use one or the other? I have read this part several times but still haven't understood the overall differences between the two. I started by reading the ORM tutorial. The tutorial often references links to other pages, which makes it impossible for a newbie to learn something just by reading the tutorial itself. Sometimes I feel overwhelmed by the links to pages that is so technically detailed that after reading it I forgot where I left off in the tutorial. By the way, in my opinion a primer for newbie users on such advanced features is in high demand. -- 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] Is using column_property preferred over hybrid property in some circumstances?
On 6/21/14, 4:00 PM, Bao Niu wrote: The documentation regarding column_property and hybrid property says both methods are intended for linking a sql expression to an attribute, which is highly useful. But it is not obvious to junior users which one is preferred. Heh.Well, one is much older than the other, and truthfully it is also not obvious to *me* which one is preferred :).Hybrid property is useful for more complex expressions where you'd like to have access to an in-Python version when you already have an object loaded. The way I used hybrids to a great extent, where i would not have used a column_property(), was on an object that has dozens of methods used in a complex report, and many of the methods built on each other (this is a mock version of that): class MyReportThing(Base): # ... @hybrid_property def x(self): # ... @hybrid_property def y(self): # ... @hybrid_property def x_y_variance(self): return variance(self.x, self.y, ...) @hybrid_property def average_foo_x(self): # .. # many more @hybrid_property objects So when we'd generate a full report, we'd load all the MyReportThing objects in fully. The table had perhaps 20 columns. But the report had about 100. This is because from those 20 columns, the hybrids produced 80 other variants of the data within.This processing was much better accomplished in Python than on the relational database side; we only had to load 20 columns in and then use those 20 cols to generate many other answers about that data. if it was all on the DB side there would have been an enormous SQL statement with 100 columns. But we still wanted to make these hybrids, because in some cases the functions we had on MyReportThing were also useful in a query now and then to use in query.filter(), like query.filter(MyReportThing.average_foo_x 25). In fact most of the hybrids were part of an abstract interface that applied to many types of objects, not just MyReportThing, and in this way the hybrids were very flexible in-Python and sometimes in-SQL methods that didn't otherwise have any complex interactions within the mapping process.Some classes would override some of the hybrids to do something differently, and some of them would even present in-Python only for some of them; the @expression form would be overridden to raise a NotImplementedError for some objects where calculating the values relied on things that couldn't be acquired in a simple SQL expression. I think for general use, business-level methods, hybrids are a lot more flexible and work very well with heavily object oriented techniques. The column_property() on the other hand is more about data that you'd like to load every time on your object. When there's some view of data that you really want at the SQL level, every time and up front when you query(), column_property() can work better for that.But with column_property() you really can't fall back onto any kind of Python-side replacement of the feature. It's more rigidly tied to the structure of how the database is queried. column_property() is also used for rudimental mapping tasks, like if two columns in a table or a JOIN should have the same value. I started by reading the ORM tutorial. The tutorial often references links to other pages, which makes it impossible for a newbie to learn something just by reading the tutorial itself. well the tutorial is supposed to give you the general idea of how to interact between a set of database tables and objects using SQLAlchemy. However, it isn't by itself going to teach you much about object oriented programming or about relational database programming techniques. I'm not sure of your background, but if it is the case that you're learning a little bit about all three at the same time, that is a much harder hill to climb. I think this is somewhat the case for most people that I interact with and is entirely normal - for example, I'm now learning lots about Openstack, but as it turns out, in order to use Openstack I really have to learn a whole lot more about networking as well, and this is hard. But I can't blame Openstack's docs for the fact that I'm not super clear on routers/subnetworks/etc. and all that, there's multiple concepts to learn at once.I watch very closely what people say about SQLAlchemy and at the same time I try to get indicators for where they are coming from; the people that really love SQLAlchemy the most are those who already have a database background and are also not hostile to object oriented programming. The people who are the most grumpy seem to be those who are learning database programming from the ground up at the same time. The product by itself, and its documentation, is only trying to solve the problem of integrating these two worlds; it can't go too far as it stands into filling in details on either side of that bridge except to the degree that the work people put
Re: [sqlalchemy] Is using column_property preferred over hybrid property in some circumstances?
Agreed, very funny comment Mike, and a good one:) Thanks. On Sat, Jun 21, 2014 at 2:52 PM, Mike Bayer mike...@zzzcomputing.com wrote: On 6/21/14, 4:00 PM, Bao Niu wrote: The documentation regarding column_property and hybrid property says both methods are intended for linking a sql expression to an attribute, which is highly useful. But it is not obvious to junior users which one is preferred. Heh.Well, one is much older than the other, and truthfully it is also not obvious to *me* which one is preferred :).Hybrid property is useful for more complex expressions where you'd like to have access to an in-Python version when you already have an object loaded. The way I used hybrids to a great extent, where i would not have used a column_property(), was on an object that has dozens of methods used in a complex report, and many of the methods built on each other (this is a mock version of that): class MyReportThing(Base): # ... @hybrid_property def x(self): # ... @hybrid_property def y(self): # ... @hybrid_property def x_y_variance(self): return variance(self.x, self.y, ...) @hybrid_property def average_foo_x(self): # .. # many more @hybrid_property objects So when we'd generate a full report, we'd load all the MyReportThing objects in fully. The table had perhaps 20 columns. But the report had about 100. This is because from those 20 columns, the hybrids produced 80 other variants of the data within.This processing was much better accomplished in Python than on the relational database side; we only had to load 20 columns in and then use those 20 cols to generate many other answers about that data. if it was all on the DB side there would have been an enormous SQL statement with 100 columns. But we still wanted to make these hybrids, because in some cases the functions we had on MyReportThing were also useful in a query now and then to use in query.filter(), like query.filter(MyReportThing.average_foo_x 25). In fact most of the hybrids were part of an abstract interface that applied to many types of objects, not just MyReportThing, and in this way the hybrids were very flexible in-Python and sometimes in-SQL methods that didn't otherwise have any complex interactions within the mapping process.Some classes would override some of the hybrids to do something differently, and some of them would even present in-Python only for some of them; the @expression form would be overridden to raise a NotImplementedError for some objects where calculating the values relied on things that couldn't be acquired in a simple SQL expression. I think for general use, business-level methods, hybrids are a lot more flexible and work very well with heavily object oriented techniques. The column_property() on the other hand is more about data that you'd like to load every time on your object. When there's some view of data that you really want at the SQL level, every time and up front when you query(), column_property() can work better for that.But with column_property() you really can't fall back onto any kind of Python-side replacement of the feature. It's more rigidly tied to the structure of how the database is queried. column_property() is also used for rudimental mapping tasks, like if two columns in a table or a JOIN should have the same value. I started by reading the ORM tutorial. The tutorial often references links to other pages, which makes it impossible for a newbie to learn something just by reading the tutorial itself. well the tutorial is supposed to give you the general idea of how to interact between a set of database tables and objects using SQLAlchemy. However, it isn't by itself going to teach you much about object oriented programming or about relational database programming techniques. I'm not sure of your background, but if it is the case that you're learning a little bit about all three at the same time, that is a much harder hill to climb. I think this is somewhat the case for most people that I interact with and is entirely normal - for example, I'm now learning lots about Openstack, but as it turns out, in order to use Openstack I really have to learn a whole lot more about networking as well, and this is hard. But I can't blame Openstack's docs for the fact that I'm not super clear on routers/subnetworks/etc. and all that, there's multiple concepts to learn at once.I watch very closely what people say about SQLAlchemy and at the same time I try to get indicators for where they are coming from; the people that really love SQLAlchemy the most are those who already have a database background and are also not hostile to object oriented programming. The people who are the most grumpy seem to be those who are learning database programming from the ground up at the same time. The product by itself, and its
