Insert NULL into mySQL datetime

[Peter Otten]

Igor Korot wrote:

> 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)

The second argument to execute has to be a tuple. Try

cur.execute("INSERT...", (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.