MySQL help

[supercomputer]

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.rowcount)
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(db.insert_id()))


Thanks,

 

[deelan]

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.

(...)

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

 

[supercomputer]

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

 

[Dennis Lee Bieber]

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+"'"

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

result = cursor.execute(check)

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

numrows = int(cursor.rowcount)
print "numrows:", numrows
cursor = db.cursor()
if numrows == 0:
output="INSERT INTO "+a+" SET nodeid='"+name+"',
lid='"+b+space+", ".join(v)

Pardon? Does SQL insert accept "set" notation?

output = 'insert into %s (nodeid, lid) values (%s, %s)'

cursor.execute(output)

cursor.execute(output, (name, b))

db.commit()
print "Insert"
result=''
else:
output="UPDATE "+a+" SET nodeid='"+name+"',
lid='"+b+space+", ".join(v)

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



--

 

[supercomputer]

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]

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.

The DB-API spec is that the
cursor.execute(parameterized_statement, (parameters,))
will properly quote arguments -- strings get quote marks, numerics are
left alone, etc.

--