Insert NULL into mySQL datetime

I

Igor Korot

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.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")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.
 
R

rurpy

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:
[...]

[...sql syntax error...]

[..."Incorrect parameter count...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.

I don't use MySql but a quick peak at the docs for
str_to_date() at:

http://docs.oracle.com/cd/E17952_01/refman-5.6-en/date-and-time-functions.html#function_str-to-date

seems to say that it takes two arguments, the datetime
string and a format string. Perhaps that's part of
your problem? (And as Peter said, the values argument
to cursor.execute need to be a tuple, not a single value).

And are you sure that NULL (aka None in Python) is an
acceptable value for the 'str' argument of str_to_date()?

If not perhaps you need to do something like:

if a is None:
cur.execute("Insert Into mytable(datefield) VALUES(NULL))", (,))
else:
cur.execute("Insert Into mytable(datefield) VALUES(STR_TO_DATE(%s, '%M %d,%Y'))", (a,))
 
D

Dennis Lee Bieber

if a is None:
cur.execute("Insert Into mytable(datefield) VALUES(NULL))", (,))

I'm pretty sure that MySQLdb, at least, does not require the
, (,)
when there are no placeholders in the SQL statement. Might even cause an
error if the adapter doesn't detect that the tuple is empty as the last
stage of .execute() would be performing

"insert into mytable(datefield) values (NULL)" % (,)

-=-=-=-Traceback ( File "<interactive input>", line 1
"insert into mytable(datefield) values ()" % (,)
^
SyntaxError: invalid syntaxTraceback ( File "<interactive input>", line 1
"insert into mytable(datefield) values (%s)" % (,)
^
SyntaxError: invalid syntaxTraceback (most recent call last):
-=-=-=-

Actually, it is more likely to break on the step that escapes the
parameters, which (simplified) looks something like
Traceback ( File "<interactive input>", line 1
tuple('"%s"'%x for x in (,))
^
SyntaxError: invalid syntax
 
J

Jussi Piitulainen

Dennis said:
I'm pretty sure that MySQLdb, at least, does not require the
, (,)
when there are no placeholders in the SQL statement. Might even

Isn't (,) invalid syntax in itself? At least I get the exception from
writing just (,) to Python 3.

The empty tuple is denoted ().
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

Forum statistics

Threads
473,744
Messages
2,569,482
Members
44,900
Latest member
Nell636132

Latest Threads

Top