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

Groups > comp.lang.python > #62735

Re: Insert NULL into mySQL datetime

Show all headers | View raw

On 12/24/2013 05:33 PM, 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:
> 
[...]
>>>> 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)

[...sql syntax error...]

>>>> a = ""
>>>> cur.execute("Insert Into mytable(datefield) VALUES(STR_TO_DATE(%s))", a)

[..."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,))

Back to comp.lang.python | Previous | Next — Previous in thread | 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