Path: csiph.com!usenet.pasdenom.info!weretis.net!feeder4.news.weretis.net!rt.uk.eu.org!newsfeed.xs4all.nl!newsfeed1.news.xs4all.nl!xs4all!post.news.xs4all.nl!not-for-mail
MIME-Version: 1.0
Date: Tue, 24 Dec 2013 16:33:20 -0800
Subject: Insert NULL into mySQL datetime
From: Igor Korot <ikorot01@gmail.com>
To: python-list@python.org
Content-Type: text/plain; charset=ISO-8859-1
Precedence: list
Newsgroups: comp.lang.python
Message-ID: <mailman.4605.1387931604.18130.python-list@python.org>
Lines: 54
NNTP-Posting-Host: 2001:888:2000:d::a6
Xref: csiph.com comp.lang.python:62692

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.