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

Groups > comp.lang.python > #62692 > unrolled thread

Insert NULL into mySQL datetime

Back to article view | Back to comp.lang.python

Contents

  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

#62692 — Insert NULL into mySQL datetime

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.

[toc] | [next] | [standalone]

#62735

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

[toc] | [prev] | [next] | [standalone]

#62745

On Wed, 25 Dec 2013 17:20:35 -0800 (PST), rurpy@yahoo.com declaimed the
following:

>  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)" % (,)

-=-=-=-
>>> "insert into mytable(datefield) values (NULL)" % (,)
Traceback (  File "<interactive input>", line 1
    "insert into mytable(datefield) values ()" % (,)
                                                      ^
SyntaxError: invalid syntax
>>> "insert into mytable(datefield) values (%s)" % (,)
Traceback (  File "<interactive input>", line 1
    "insert into mytable(datefield) values (%s)" % (,)
                                                    ^
SyntaxError: invalid syntax
>>> "insert into mytable(datefield) values (NULL)" % (1,)
Traceback (most recent call last):
  File "<interactive input>", line 1, in <module>
TypeError: not all arguments converted during string formatting
>>> "insert into mytable(datefield) values (%s)" % (1,)
'insert into mytable(datefield) values (1)'
>>> 
-=-=-=-

	Actually, it is more likely to break on the step that escapes the
parameters, which (simplified) looks something like

>>> tuple('"%s"'%x for x in (,))
Traceback (  File "<interactive input>", line 1
    tuple('"%s"'%x for x in (,))
                             ^
SyntaxError: invalid syntax

-- 
	Wulfraed                 Dennis Lee Bieber         AF6VN
    wlfraed@ix.netcom.com    HTTP://wlfraed.home.netcom.com/

[toc] | [prev] | [next] | [standalone]

#62747

Dennis Lee Bieber writes:

> On Wed, 25 Dec 2013 17:20:35 -0800 (PST), rurpy@yahoo.com declaimed
> the following:
> 
> >  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

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

The empty tuple is denoted ().

[toc] | [prev] | [standalone]

Back to top | Article view | comp.lang.python

csiph-web