Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.lang.python > #62692

Insert NULL into mySQL datetime

Show key headers only | View raw

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.

Back to comp.lang.python | Previous | Next — Next in thread | Find similar | Unroll thread

Thread

Insert NULL into mySQL datetime Igor Korot <ikorot01@gmail.com> - 2013-12-24 16:33 -0800
  Re: Insert NULL into mySQL datetime rurpy@yahoo.com - 2013-12-25 17:20 -0800
    Re: Insert NULL into mySQL datetime Dennis Lee Bieber <wlfraed@ix.netcom.com> - 2013-12-26 12:13 -0500
      Re: Insert NULL into mySQL datetime Jussi Piitulainen <jpiitula@ling.helsinki.fi> - 2013-12-26 19:59 +0200

csiph-web