Re: Special SELECT and UPDATE for a field

On Thu, 31 Jan 2013 23:17:57 +0200, Yarden Sachs <yrdnsa@gmail.com>
declaimed the following in gmane.comp.python.django.user:

> You can see in my paste that i already created a custom field for geometry
> field.
>
<ugh -- top-posted with massive quote... as a result my comments
will be rather unordered>

I believe the implication is that, if you define a custom field, YOU
must define the type translations for storing and retrieving values in
that field definition. You do not hope that some internal DBMS function
can do translations for you.

https://docs.djangoproject.com/en/1.4/howto/custom-model-fields/#converting-database-values-to-python-objects
... and the next two or three subheadings after that

>
> On Thu, Jan 31, 2013 at 7:52 PM, Tom Evans <tevans.uk@googlemail.com> wrote:
>
> > On Wed, Jan 30, 2013 at 8:14 PM, Yarden Sachs <yarden@tapingo.com> wrote:
> > > Hello,
> > > i want to create a field class for geometry (not using geodjango).
> > > here's the field code: http://pastebin.com/yADpZykJ
> > >
> > > i want 2 things i can't seem to do:
> > > 1. I want the select to wrap the column with MySql AsText() Function
> > > e.x. SELECT AsText(location) FROM addresses
> > >
> > > 2. I want to be able to save the value wrapped with a function e.x.
> > > GeomFromText('POINT(0.0 0.0)')
> > > but is seems that django will wrap that function with quotes, and so
> > mysql
> > > thinks its a string.
> > >

Any proper DB-API usage will escape data values explicitly to
prevent them being treated as executable SQL statements -- not just
MySQL.

Furthermore -- EVERYTHING supplied to MySQL comes in as a string
(MySQLdb works with MySQL versions down to 3.x. Prior to MySQL v5.x,
MySQL did not support "prepared" statements -- .executemany() generates
many individual SQL statements with the placeholders replaced by escaped
values.

Using raw DB-API SQL form, you are attempting the equivalent of:

cur.execute("""insert into TABLE (LOCATION, other, stuff)
values (%s, %s, %s)""",
("GeomFromText('POINT(0.0 0.0)')", Other, Stuff) )

but since parameterized values get escaped/safed to prevent SQL
injection attacks, you really need the SQL to look like:

cur.execute("""insert into TABLE (LOCATION, other, stuff)
values (GeomFromText(%s), %s, %s)""",
('POINT(0.0 0.0)', Other, Stuff) )

That is, the GeomFromText() call needs to be /in/ the parameterized SQL
before the parameters go in.

Django, on its own, is not capable of generating this (it is
generating the equivalent of the first example).


<snip>

> > Sounds like you want to define a custom field rather than fiddle with
> > a Model's manager.
> >
> > https://docs.djangoproject.com/en/1.4/howto/custom-model-fields/
> >
> > Cheers
> >
> > Tom
> >

One problem I see is that you are NOT really using a custom type.
Django's "custom types" are a way to convert a non-native Python object
into common Django DBMS types -- which for plain Django do not include
the geometry extensions.

If you are insisting on using DBMS geometry extensions but NOT
geodjango, you will have to implement everything that geodjango does to
handle those extensions. Note this clause from
https://docs.djangoproject.com/en/1.4/ref/contrib/gis/tutorial/#introduction
"""
* Extensions to Django's ORM for the querying and manipulation of
spatial data.
"""
--
Wulfraed Dennis Lee Bieber AF6VN
wlfraed@ix.netcom.com HTTP://wlfraed.home.netcom.com/

--
You received this message because you are subscribed to the Google Groups "Django users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to django-users+unsubscribe@googlegroups.com.
To post to this group, send email to django-users@googlegroups.com.
Visit this group at http://groups.google.com/group/django-users?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.