MySQL help

[supercomputer@gmail.com]

So i'm writing this program to check if a row exists in a table. If it
doesn't it inserts it if it does it will update that row with the
current info.

Well it sorta works but not fully. It goes through and executes the
correct querries but when it comes to determining if the row exists it
doesn't get back a result. Yet if I mannually enter it into the mysql
console I get a result. This only happens when my if statement to
determine if there was a result returned is not commented out. If it
is commented out it returns that the row exists. Any help would be
great.

Thanks.

here is a snippet of the code assume a cursor has been defined, import
MySQL has occured and the connection have all taken place.

"a" is the table the query is taking place on.
name is nodeXXX and b is either a 0-3 number.


check="SELECT * FROM "+a+" WHERE nodeid='"+name+ "' AND
lid='"+b+"'"
result = cursor.execute( check)
numrows = int(cursor.rowc ount)
print "numrows:", numrows
cursor = db.cursor()
if numrows == 0:
output="INSERT INTO "+a+" SET nodeid='"+name+ "',
lid='"+b+space+ ", ".join(v)
cursor.execute( output)
db.commit()
print "Insert"
result=''
else:
output="UPDATE "+a+" SET nodeid='"+name+ "',
lid='"+b+space+ ", ".join(v)
cursor.execute( output)
result=''
db.commit()
print "Updating"

id.append(int(d b.insert_id()))


Thanks,

[deelan]

Re: MySQL help

supercomputer@g mail.com wrote:[color=blue]
> So i'm writing this program to check if a row exists in a table. If it
> doesn't it inserts it if it does it will update that row with the
> current info.[/color]

(...)

quick tip: are you aware of the mysql's REPLACE command?
<http://dev.mysql.com/doc/mysql/en/replace.html>


--
deelan, #1 fan of adriana lima!
<http://www.deelan.com/>

[supercomputer@gmail.com]

Re: MySQL help

I wasn't aware of the replace command I'll take a look at that tomorrow
and see if it helps. Thanks

[color=blue]
>quick tip: are you aware of the mysql's REPLACE command?
><http://dev.mysql.com/doc/mysql /en/replace.html>[/color]

[Dennis Lee Bieber]

Re: MySQL help

On 1 Aug 2005 11:58:37 -0700, "supercomputer@ gmail.com"
<supercomputer@ gmail.com> declaimed the following in comp.lang.pytho n:
[color=blue]
>
> here is a snippet of the code assume a cursor has been defined, import
> MySQL has occured and the connection have all taken place.
>[/color]
Hopefully in the order of import, connection, cursor <G>
[color=blue]
> "a" is the table the query is taking place on.
> name is nodeXXX and b is either a 0-3 number.
>
>
> check="SELECT * FROM "+a+" WHERE nodeid='"+name+ "' AND
> lid='"+b+"'"[/color]

"b" had better be a STRING or this is nonsense...

Better would be:

check = 'select * from %s where nodeid="%s" and lid="%s"' % (a,
name, b)
[color=blue]
> result = cursor.execute( check)[/color]

I don't have experience to know if select and update can use the
notation used for insert... If it does, the above would be

check = 'select * from %s where nodeid=%%s and lid=%%s" % (a,)
result = cursor.execute( check, (name, b))
[color=blue]
> numrows = int(cursor.rowc ount)
> print "numrows:", numrows
> cursor = db.cursor()
> if numrows == 0:
> output="INSERT INTO "+a+" SET nodeid='"+name+ "',
> lid='"+b+space+ ", ".join(v)[/color]

Pardon? Does SQL insert accept "set" notation?

output = 'insert into %s (nodeid, lid) values (%s, %s)'
[color=blue]
> cursor.execute( output)[/color]

cursor.execute( output, (name, b))
[color=blue]
> db.commit()
> print "Insert"
> result=''
> else:
> output="UPDATE "+a+" SET nodeid='"+name+ "',
> lid='"+b+space+ ", ".join(v)[/color]

That's going to set EVERY record's nodeid and lid to the same
values. Normally update is used with a where clause to identify which
records are to be changed. Of course, you also have this strange case
where you are trying to set the fields to the SAME values you used in
the select -- there is no apparent "update" here if you are trying to
ONLY affect the record found by select; IE

output = 'update %s set nodeid=%%s,lid= %%s where nodeid=%%s and
lid=%%s' % (a,)
cursor.execute( output, (name, b, name, b))



--[color=blue]
> =============== =============== =============== =============== == <
> wlfraed@ix.netc om.com | Wulfraed Dennis Lee Bieber KD6MOG <
> wulfraed@dm.net | Bestiaria Support Staff <
> =============== =============== =============== =============== == <
> Home Page: <http://www.dm.net/~wulfraed/> <
> Overflow Page: <http://wlfraed.home.ne tcom.com/> <[/color]

[supercomputer@gmail.com]

Re: MySQL help

Hey Dennis thanks for the tips I haven't had a chance to take another
stab at that code yet but I think I may try some of your suggestions.
The SQL statements are valid but something doesn't appear to work right
I may try and switch them to what you're suggesting and see if that
helps with my problem.

Thanks,

[Dennis Lee Bieber]

Re: MySQL help

On 3 Aug 2005 08:37:38 -0700, "supercomputer@ gmail.com"
<supercomputer@ gmail.com> declaimed the following in comp.lang.pytho n:
[color=blue]
> Hey Dennis thanks for the tips I haven't had a chance to take another
> stab at that code yet but I think I may try some of your suggestions.
> The SQL statements are valid but something doesn't appear to work right
> I may try and switch them to what you're suggesting and see if that
> helps with my problem.
>[/color]
The DB-API spec is that the
cursor.execute( parameterized_s tatement, (parameters,))
will properly quote arguments -- strings get quote marks, numerics are
left alone, etc.

--[color=blue]
> =============== =============== =============== =============== == <
> wlfraed@ix.netc om.com | Wulfraed Dennis Lee Bieber KD6MOG <
> wulfraed@dm.net | Bestiaria Support Staff <
> =============== =============== =============== =============== == <
> Home Page: <http://www.dm.net/~wulfraed/> <
> Overflow Page: <http://wlfraed.home.ne tcom.com/> <[/color]