0

I have django-pyodbc installed for Python 2.7 (Ubuntu 14.04, unixODBC, FreeTDS) and I am attempting to update a SQL Server database that I created as a test.

Everything works well except one particular UPDATE statement that does not affect any row. The same query executed via isql affects 258 rows (same result as if I run the query from Windows). BTW, a simple UPDATE statement works (ex: UPDATE mytable SET x=1). The query in question is based on 2 JOINS (one is a LEFT JOIN). Here it is:

UPDATE E
SET in_dico = 0
FROM entites_entite E
INNER JOIN entites_entitetype T 
      ON E.entite_type_id = T.id
LEFT JOIN entites_singleton S 
      ON LOWER(E.entite_name) = LOWER(S.entite_name)
WHERE E.entite_name NOT LIKE '% %' 
      AND T.exclude_singleton = 1 
      AND S.entite_name IS NULL

Does anyone know how such a thing possible? Regards, Patrick

EDIT: my DATABASE settings already include the autocommit parameter. Anyway, here it is:

DATABASES = {
'default': {
   'ENGINE': "django_pyodbc",
   'HOST': "myservername,1433",
   'USER': "myname",
   'PASSWORD': "mypassword",
   'NAME': "mydbname",
   'OPTIONS': {
       'host_is_server': True,
       'autocommit': True,
       'driver': "FreeTDS"
       },
   'COMMAND_TIMEOUT': 7200,
}

}

Patrick
  • 2,577
  • 6
  • 30
  • 53
  • It is certainly possible but we need some table structures here to understand what you are trying to do. By the way this query will be a problem with large data sets it will not scale well. Biggest problem is that you are using the LOWER function on the join statement. This means SQL will not be able to JOIN using indexes. – Namphibian Jan 20 '15 at 01:06
  • Essentially the query sets a flag to 0 (in_dico) for the names in a dictionary of entities that have no spaces in them (those names that I call "singletons", example John, Frank, Obama) except those contained in a table of exceptions (table named entites_singletons, ex: Madonna, Bono, U2, etc). The JOIN with entite_type is not important here. You're right about the LOWER on the JOIN but I run this task only once at the beginning of my process, so it's not a big deal. – Patrick Jan 20 '15 at 03:57
  • Could you include your DATABASES section from settings.py? I think you may need to add an option for 'autocommit': 'true', for it to work properly on updates. – FlipperPA Jan 20 '15 at 22:37

0 Answers0