MySQL Insert

[maestroQC]

Hi,

Its one of those days. I cannot solve this. Any help would be greatly
appreciated!
When I execute this:

class Db(object):
def insertAccount(s elf, date, accountNumber, description,
openingBalance) :
dec = decimal.Decimal (openingBalance )
db = MySQLdb.connect (host="localhos t", user="dumb",
passwd="dumber" , db="rdc")
cursor = db.cursor()
cursor.execute( "INSERT INTO es_accounts (dateCreated,
accountNumber, description, openingBalance) VALUES (%s, %s, %s, %d)",
(date, accountNumber, description, dec))

I get this error:
Traceback (most recent call last):
File "main.py", line 59, in <module>
main()
File "main.py", line 40, in main
dbObj.insertAcc ount(dateTo, item[0], item[1], item[8])
File "C:\projects\wo rkspace\INYR_ES _0.1\src\db.py" , line 19, in
insertAccount
cursor.execute( "INSERT INTO es_accounts (dateCreated,
accountNumber, description, openingBalance) VALUES (%s, %s, %s
, %d)", (date, accountNumber, description, dec))
File "c:\python25\li b\site-packages\MySQLd b\cursors.py", line 151,
in execute
query = query % db.literal(args )
TypeError: int argument required

My table is defined as:
CREATE TABLE es_accounts (
id int(6) not null auto_increment,
dateCreated date DEFAULT '0000-00-00',
accountNumber int(6) not null,
description varchar(255) not null,
openingBalance decimal(15,8) NOT NULL DEFAULT 0.00000000,
primary key (id)
);

TIA

[Carsten Haese]

Re: MySQL Insert

maestroQC wrote:

cursor.execute( "INSERT INTO es_accounts (dateCreated,
accountNumber, description, openingBalance) VALUES (%s, %s, %s
, %d)", (date, accountNumber, description, dec))
File "c:\python25\li b\site-packages\MySQLd b\cursors.py", line 151,
in execute
query = query % db.literal(args )
TypeError: int argument required

The placeholder for query parameters in MySQLdb is always %s, regardless
of the data type the actual value might have.

HTH,

--
Carsten Haese

http://informixdb.sourceforge.net

[Ethan Furman]

Re: MySQL Insert

maestroQC wrote:

Hi,
>
Its one of those days. I cannot solve this. Any help would be greatly
appreciated!
When I execute this:
>
class Db(object):
def insertAccount(s elf, date, accountNumber, description,
openingBalance) :
dec = decimal.Decimal (openingBalance )
db = MySQLdb.connect (host="localhos t", user="dumb",
passwd="dumber" , db="rdc")
cursor = db.cursor()
cursor.execute( "INSERT INTO es_accounts (dateCreated,
accountNumber, description, openingBalance) VALUES (%s, %s, %s, %d)",
(date, accountNumber, description, dec))
>
I get this error:
Traceback (most recent call last):
File "main.py", line 59, in <module>
main()
File "main.py", line 40, in main
dbObj.insertAcc ount(dateTo, item[0], item[1], item[8])
File "C:\projects\wo rkspace\INYR_ES _0.1\src\db.py" , line 19, in
insertAccount
cursor.execute( "INSERT INTO es_accounts (dateCreated,
accountNumber, description, openingBalance) VALUES (%s, %s, %s
, %d)", (date, accountNumber, description, dec))
File "c:\python25\li b\site-packages\MySQLd b\cursors.py", line 151,
in execute
query = query % db.literal(args )
TypeError: int argument required
>
My table is defined as:
CREATE TABLE es_accounts (
id int(6) not null auto_increment,
dateCreated date DEFAULT '0000-00-00',
accountNumber int(6) not null,
description varchar(255) not null,
openingBalance decimal(15,8) NOT NULL DEFAULT 0.00000000,
primary key (id)
);
>
TIA

How embarassing. My previous post is not correct in it's explanation,
unless there is some way to tell Decimal not to allow int coercion...

--from decimal import Decimal
--n10_25 = Decimal('10.25' )
--n10_25
Decimal("10.25" )
--"%d" % n10_25
'10'

About the only thing it had even partially right is not using %d, as it
will truncate the fractional part of your opening balance. As far as
why you are getting that error, I now have no idea, and I apologize for
any confusion created by my error.

~Ethan

[Ethan Furman]

Re: MySQL Insert

maestroQC wrote:

Hi,
>
Its one of those days. I cannot solve this. Any help would be greatly
appreciated!
When I execute this:
>
class Db(object):
def insertAccount(s elf, date, accountNumber, description,
openingBalance) :
dec = decimal.Decimal (openingBalance )
db = MySQLdb.connect (host="localhos t", user="dumb",
passwd="dumber" , db="rdc")
cursor = db.cursor()
cursor.execute( "INSERT INTO es_accounts (dateCreated,
accountNumber, description, openingBalance) VALUES (%s, %s, %s, %d)",
(date, accountNumber, description, dec))
>
I get this error:
Traceback (most recent call last):
File "main.py", line 59, in <module>
main()
File "main.py", line 40, in main
dbObj.insertAcc ount(dateTo, item[0], item[1], item[8])
File "C:\projects\wo rkspace\INYR_ES _0.1\src\db.py" , line 19, in
insertAccount
cursor.execute( "INSERT INTO es_accounts (dateCreated,
accountNumber, description, openingBalance) VALUES (%s, %s, %s
, %d)", (date, accountNumber, description, dec))
File "c:\python25\li b\site-packages\MySQLd b\cursors.py", line 151,
in execute
query = query % db.literal(args )
TypeError: int argument required
>
My table is defined as:
CREATE TABLE es_accounts (
id int(6) not null auto_increment,
dateCreated date DEFAULT '0000-00-00',
accountNumber int(6) not null,
description varchar(255) not null,
openingBalance decimal(15,8) NOT NULL DEFAULT 0.00000000,
primary key (id)
);
>
TIA

The problem is your %d in Values, and then using a Decimal type to feed
it. Depending on the type of your openingBalance field, you may want to
change the %d to %s, or change dec to int(dec), or change the %d to %f
and change dec to float(dec). My experience with the Decimal type is
limited, so there may be other options as well.

~Ethan

[maestroQC]

Re: MySQL Insert

Thanks for the information.
However I must stick to decimal since I'm dealing with monetary
values.
I loose the decimals with int and float has only supports one decimal
place.

I tried as you suggested to use the %s instead of the %d to no avail.

[maestroQC]

Re: MySQL Insert

Thanks to all for your time and patience with this!
The insert is working fine based on the suggestions in this thread.

I now have another problem that should be resolved with a regular
expression to remove currency formatting before inserting into the db.