Insert NULL into mySQL datetime

Igor Korot ikorot01 at gmail.com
Wed Dec 25 01:33:20 CET 2013


Hi, ALL,
I am working on  a script that parses CSV file and after successful
parsing insert data ino mySQL table.
One of the fields in CSV holds a date+time value.

What the script should do is check if the cell has any data, i.e. not
empty and then make sure that the data is date+time.
If the validation fails, it should insert NULL into the mySQL datetime
field, otherwise the actual datetime will be inserted.

Right now the script uses a placeholder "0000-00-00 00:00:00.000" if
the date validation fails (either cell is empty or it has wrong data)

What I tried so far is:

C:\Documents and Settings\Igor.FORDANWORK\My Documents\GitHub\image_export\Artef
acts>python
Python 2.7.5 (default, May 15 2013, 22:43:36) [MSC v.1500 32 bit
(Intel)] on win32
Type "help", "copyright", "credits" or "license" for more information.
>>> import MySQLdb as mdb
>>> conn = mdb.connect('127.0.0.1','root','pass')
>>> cur = conn.cursor()
>>> a = None
>>> cur.execute("Use mydb")
0L
>>> cur.execute("Insert Into mytable(datefield) VALUES(STR_TO_DATE(%s))", a)
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "c:\python27\lib\site-packages\MySQLdb\cursors.py", line 202, in execute
    self.errorhandler(self, exc, value)
  File "c:\python27\lib\site-packages\MySQLdb\connections.py", line
36, in defaulterrorhandler
    raise errorclass, errorvalue
_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 '%s))' at line 1")
>>> a = ""
>>> cur.execute("Insert Into mytable(datefield) VALUES(STR_TO_DATE(%s))", a)
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "c:\python27\lib\site-packages\MySQLdb\cursors.py", line 202, in execute
    self.errorhandler(self, exc, value)
  File "c:\python27\lib\site-packages\MySQLdb\connections.py", line
36, in defaulterrorhandler
    raise errorclass, errorvalue
_mysql_exceptions.OperationalError: (1582, "Incorrect parameter count
in the call to native function 'STR_TO_DATE'")
>>>

Is it possible to do what I want?
I'd like to use one query to insert the data into the table.

Thank you.



More information about the Python-list mailing list