psycopg2 Substr SQL generator bug?

[Jakob Karstens]

I have a model named `Conference` which has two fields: a DateField `start_date` and a URLField `website`.

I want to filter the rows to return all Conference's c such that: c.website contains c.start_date__year. I want to perform this query db-side, not Python side (for memory reasons), and I don't want to use a raw query (for portability reasons). I believe that Django does not support using field lookups on the right side of the equals sign in a filter clause, so my idea was to create an annotation to pull out the year (e.g. '2018') from the `start_date` field and check if the `website` field contains this annotation.

To verify that I could get the year strings correctly as an annotation, I ran this statement:

Conference.objects.annotate(year_string=Substr(Cast('start_date', CharField()), 1, length=4)).values_list('year_string', flat=True)

It successfully returned:

<PolymorphicQuerySet ['2018', '2018', '2018', '2018', '2018', '2018', '2018', '2018', '2018', '2018', '2018', '2018', '2018', '2018', '2019', '2018', '2018', '2018', '2018', '2018', '...(remaining elements truncated)...']>

However, when I tried to filter by website containing year (replacing the `values_list` call with the `filter` call):

Conference.objects.annotate(year_string=Substr(Cast('start_date', CharField()), 1, length=4)).filter(website__contains=F('year_string'))

a strange exception occurred, complaining about %1% in the generated SQL (I am using Postgres and the psycopg2 Python library for my Django project)

>>> Conference.objects.annotate(year_string=Substr(Cast('start_date', CharField()), 1, length=4)).filter(website__contains=F('year_string'))
Traceback (most recent call last):
  File "/Users/jakobkarstens/confir/virt/lib/python3.6/site-packages/django/db/backends/utils.py", line 85, in _execute
    return self.cursor.execute(sql, params)
psycopg2.DataError: invalid input syntax for integer: "%1%"
LINE 1: ...E((SUBSTRING("conferences"."start_date"::varchar, '%1%', 4))...
                                                             ^




The above exception was the direct cause of the following exception:


Traceback (most recent call last):
  File "<console>", line 1, in <module>
  File "/Users/jakobkarstens/confir/virt/lib/python3.6/site-packages/polymorphic/query.py", line 456, in __repr__
    return super(PolymorphicQuerySet, self).__repr__(*args, **kwargs)
  File "/Users/jakobkarstens/confir/virt/lib/python3.6/site-packages/django/db/models/query.py", line 248, in __repr__
    data = list(self[:REPR_OUTPUT_SIZE + 1])
  File "/Users/jakobkarstens/confir/virt/lib/python3.6/site-packages/django/db/models/query.py", line 272, in __iter__
    self._fetch_all()
  File "/Users/jakobkarstens/confir/virt/lib/python3.6/site-packages/django/db/models/query.py", line 1179, in _fetch_all
    self._result_cache = list(self._iterable_class(self))
  File "/Users/jakobkarstens/confir/virt/lib/python3.6/site-packages/polymorphic/query.py", line 56, in _polymorphic_iterator
    o = next(base_iter)
  File "/Users/jakobkarstens/confir/virt/lib/python3.6/site-packages/django/db/models/query.py", line 53, in __iter__
    results = compiler.execute_sql(chunked_fetch=self.chunked_fetch, chunk_size=self.chunk_size)
  File "/Users/jakobkarstens/confir/virt/lib/python3.6/site-packages/django/db/models/sql/compiler.py", line 1064, in execute_sql
    cursor.execute(sql, params)
  File "/Users/jakobkarstens/confir/virt/lib/python3.6/site-packages/django/db/backends/utils.py", line 100, in execute
    return super().execute(sql, params)
  File "/Users/jakobkarstens/confir/virt/lib/python3.6/site-packages/django/db/backends/utils.py", line 68, in execute
    return self._execute_with_wrappers(sql, params, many=False, executor=self._execute)
  File "/Users/jakobkarstens/confir/virt/lib/python3.6/site-packages/django/db/backends/utils.py", line 77, in _execute_with_wrappers
    return executor(sql, params, many, context)
  File "/Users/jakobkarstens/confir/virt/lib/python3.6/site-packages/django/db/backends/utils.py", line 85, in _execute
    return self.cursor.execute(sql, params)
  File "/Users/jakobkarstens/confir/virt/lib/python3.6/site-packages/django/db/utils.py", line 89, in __exit__
    raise dj_exc_value.with_traceback(traceback) from exc_value
  File "/Users/jakobkarstens/confir/virt/lib/python3.6/site-packages/django/db/backends/utils.py", line 85, in _execute
    return self.cursor.execute(sql, params)
django.db.utils.DataError: invalid input syntax for integer: "%1%"
LINE 1: ...E((SUBSTRING("conferences"."start_date"::varchar, '%1%', 4))...

What is going on? Why is the generated SQL "%1%" and not "1"? Is this a psycopg2 bug? Am I missing something?

Thanks in advance

[Matthew Pava]

I would submit a ticket for that issue.

Also, instead of using string functions to  solve your problem, I would use the ExtractYear function.

 

Conference.objects.annotate(year=ExtractYear('start_date')).filter(website__contains=F('year'))

--
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...@googlegroups.com.
To post to this group, send email to django...@googlegroups.com.
Visit this group at https://groups.google.com/group/django-users.
To view this discussion on the web visit https://groups.google.com/d/msgid/django-users/083dae62-9ad7-484c-b10b-d6d1c3c7cb52%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Jakob Karstens]

Thanks Matthew, I'll submit a ticket. 

Unfortunately, ExtractYear does not return a string, so it seems like I would still need to explicitly cast the year annotation to a string:

>>> Conference.objects.annotate(year=ExtractYear('start_date')).filter(website__contains=F('year'))

Traceback (most recent call last):
  File "/Users/jakobkarstens/confir/virt/lib/python3.6/site-packages/django/db/backends/utils.py", line 85, in _execute
    return self.cursor.execute(sql, params)

psycopg2.ProgrammingError: function replace(double precision, unknown, unknown) does not exist
LINE 1: ...nces"."website"::text LIKE '%' || REPLACE(REPLACE(REPLACE((E...
                                                             ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

To post to this group, send email to djang...@googlegroups.com.

[Matthew Pava]

Oh, I see.

Then just use Cast, or the output_field argument .

Conference.objects.annotate(year=ExtractYear('start_date', output_field=CharField())).filter(website__contains=F('year'))

To post to this group, send email to django...@googlegroups.com.

Visit this group at https://groups.google.com/group/django-users.

To view this discussion on the web visit https://groups.google.com/d/msgid/django-users/8326add2-3815-425a-bbaa-8bcdf80cd0a5%40googlegroups.com.

[Jakob Karstens]

Unfortunately, using the `output_field` keyword argument leads to the same error:

>>> Conference.objects.annotate(year=ExtractYear('start_date', output_field=CharField())).filter(website__contains=F('year'))

Traceback (most recent call last):
  File "/Users/jakobkarstens/confir/virt/lib/python3.6/site-packages/django/db/backends/utils.py", line 85, in _execute
    return self.cursor.execute(sql, params)
psycopg2.ProgrammingError: function replace(double precision, unknown, unknown) does not exist
LINE 1: ...nces"."website"::text LIKE '%' || REPLACE(REPLACE(REPLACE((E...
                                                             ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

I wonder if that is part of the same issue?

One way I got it working was using an explicit cast in another annotation:

Conference.objects.annotate(year=ExtractYear('start_date')).annotate(year_string=Cast('year', CharField())).filter(website__contains=F('year_string')).values_list('website', flat=True)

I wonder why this works and the other method don't work? It seems I don't really understand the inner workings of casting and filtering between Django and Postgres.

Thanks again for your help

[odile Lambert]

Hello
I need to use an existing SQL database with Django. The database was under MSAccess. I generated a Dump of the database. I use SQL" source" command to generate my SQL database. Then I do Python manage.py inspectDB. I incorporate the proposed Django model in models.py. Then I do manage.py makemigrations.  Then I do python manage.py migrate. And I get the following error message.

File "/home/christian/djangorecettes/monvenv/lib/python3.6/site-packages/MySQLdb/connections.py", line 276, in query
    _mysql.connection.query(self, query)
django.db.utils.OperationalError: (1050, "Table 'Categorie_de_recettes' already exists")

After searching on the web it appears that it is an SQL error and several people mentionned going around it by changing from InnoDB to MyIsam engin.

So I started the wohole process again modifying the dump with ENGINE= MYISAM.
Python manage.py inspectDNB
copy the model into models.py
Python manage.py makemigrations.
I get the following error message :
auth.Group.permissions: (fields.E340) The field's intermediary table 'auth_group_permissions' clashes with the table name of 'mesrecettes.AuthGroupPermissions'

I checked the model the following way: I used the generated models.py to generate an empty database. NO problem and the use of the admin form allows me to create elements in the database.

Any suggestion would be much welcomed as I am getting desperate.>:o

My next step could be to fill the empty database with SQL commands in batch but I would like to avoid it.
Piscvau

[Matthew Pava]

Yeah, that makes sense now that I think about it.  You can combine the Cast in the first annotate method.

Conference.objects.annotate(year_string=Cast(ExtractYear('start_date'), CharField())).filter(website__contains=F('year_string')).values_list('website', flat=True)

To post to this group, send email to django...@googlegroups.com.

Visit this group at https://groups.google.com/group/django-users.

To view this discussion on the web visit https://groups.google.com/d/msgid/django-users/391faad9-696a-437d-af80-dcd453225dff%40googlegroups.com.