如何使用Python MySQdb检查记录是否存在 [英] How to check if record exists with Python MySQdb

问题描述

我正在创建一个连接到mysql的python程序.

Im creating a python program that connects to mysql.

我需要检查表中是否包含数字1以表明它已成功连接，这是到目前为止的代码:

i need to check if a table contains the number 1 to show that it has connected successfully, this is my code thus far:

xcnx.execute('CREATE TABLE settings(status INT(1) NOT NULL)')
  xcnx.execute('INSERT INTO settings(status) VALUES(1)')
  cnx.commit()
  sqlq = "SELECT * FROM settings WHERE status = '1'"
  xcnx.execute(sqlq)
  results = xcnx.fetchall()
  if results =='1':
    print 'yep its connected'
  else:
    print 'nope not connected'

我错过了什么?我是一个SQL新手，谢谢大家.

what have i missed? i am an sql noob, thanks guys.

推荐答案

我认为最有效的是否存在"查询只是执行count:

I believe the most efficient "does it exist" query is just to do a count:

sqlq = "SELECT COUNT(1) FROM settings WHERE status = '1'"
xcnx.execute(sqlq)
if xcnx.fetchone()[0]:
    # exists

您不是要数据库对字段或行执行任何计数操作，而是要它返回1或0(如果结果产生任何匹配项).与返回实际记录并计算客户端数量相比，此方法效率更高，因为它可以节省双方的序列化和反序列化以及数据传输.

Instead of asking the database to perform any count operations on fields or rows, you are just asking it to return a 1 or 0 if the result produces any matches. This is much more efficient that returning actual records and counting the amount client side because it saves serialization and deserialization on both sides, and the data transfer.

In [22]: c.execute("select count(1) from settings where status = 1")
Out[22]: 1L  # rows

In [23]: c.fetchone()[0]
Out[23]: 1L  # count found a match

In [24]: c.execute("select count(1) from settings where status = 2")
Out[24]: 1L  # rows

In [25]: c.fetchone()[0]
Out[25]: 0L  # count did not find a match

count(*)将与count(1)相同.在您的情况下，因为要创建一个新表，它将显示1个结果.如果您有10,000个匹配项，那么它将是10000.但是您在测试中只关心它是否不为0，因此您可以执行bool真值测试.

count(*) is going to be the same as count(1). In your case because you are creating a new table, it is going to show 1 result. If you have 10,000 matches it would be 10000. But all you care about in your test is whether it is NOT 0, so you can perform a bool truth test.

更新

实际上，仅使用行计数甚至不获取结果都更快:

Actually, it is even faster to just use the rowcount, and not even fetch results:

In [15]: if c.execute("select (1) from settings where status = 1 limit 1"): 
            print True
True

In [16]: if c.execute("select (1) from settings where status = 10 limit 1"): 
            print True

In [17]: 

这也是django的ORM执行queryObject.exists()的方式.

This is also how django's ORM does a queryObject.exists().

这篇关于如何使用Python MySQdb检查记录是否存在的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！