Re: [UrbanSim-Users] mysql / escape sequence

Hi,

I test your suggestion that give me way to invetigate.

Yes, the Sql INSERT (or UPDATE) statement want the string values with
characters ' inside must be doubled (So in MySql we can add the character \
before ').

Finaly, It works modifying the line 129 as following to replace quote only
on string values but not full INSERT query (line 138):
128: if table_data[column_name].dtype.char == 'S':
129: value = "'%s'" % value.replace('\'', '\'\'') # Modif PZU Add
replace ' by '' as suggest by Travis

Can you take in charge Opus_core modification for with subject ?.

Regards,
Philippe.

-----Message d'origine-----
De : users-bounces_at_urbansim.org [mailto:users-bounces_at_urbansim.org]De la
part de Travis Kriplean
Envoye : jeudi 29 novembre 2007 17:26
A : Fabrice Marchal
Cc : users_at_urbansim.org
Objet : Re: [UrbanSim-Users] mysql / escape sequence

Fabrice,

In the mysql_storage object, right before line 138, try doing this on
the string representing the query:

query = query.replace('\'', '\\\'')

Travis

Fabrice Marchal wrote:
> Hi,
>
> we had a small problem moving data from mysql -> cache and back to mysql
> with some string sequences that contain the " ' " character (i.e. French
> zone names)
>
> the data were modified in mysql. flushing them to the cache works but
> we had the following problem with a subsequent export_from_cache_to_mysql:
>
> File "opus_core/tools/do_export_cache_to_mysql_database.py", line 99,
> in <module>
> ExportStorage().export(in_storage=input_storage,
> out_storage=output_storage)
> File "/home/fmarchal/opus-4.1/opus_core/export_storage.py", line 32,
> in export
> File "/home/fmarchal/opus-4.1/opus_core/export_storage.py", line 41,
> in export_dataset
> File "/home/fmarchal/opus-4.1/opus_core/store/mysql_storage.py", line
> 138, in write_table
> _mysql_exceptions.ProgrammingError: (1064, "You have an error in your
> SQL syntax; check the manual that corresponds to your MySQL server
> version for the right syntax to use near 'Abeau (L'
> )',1.0,51064,2,3,10861,381930101,2,1,2,1,1,1,0)' at line 1")
>
>
> Regards,
>
> Fabrice
>
>
> =====================================================
> Fabrice Marchal
> http://marchal.let.fr
> CNRS Associate researcher +33 472
> 72 79 44
> Lab of Transportation Economics (LET), Berthelot 14, F-69363 Lyon
> =====================================================
>
>
>
>
> ------------------------------------------------------------------------
>
> _______________________________________________
> Users mailing list
> Users_at_urbansim.org
> http://www.urbansim.org/mailman/listinfo/users
_______________________________________________
Users mailing list
Users_at_urbansim.org
http://www.urbansim.org/mailman/listinfo/users
Received on Fri Nov 30 2007 - 03:27:20 PST