官术网_书友最值得收藏!

  • MySQL for Python
  • Albert Lukaszewski, PhD
  • 353字
  • 2021-04-13 17:12:28

Using user-defined variables

What if you want to specify a different price floor every time you run the search? What if you didn't want to use a floor but specify the price exactly? What if you wanted to reuse part of the statement and automate queries by fish name instead of retrieving all of them at once? Under such circumstances, you need to be able to handle variables in your SELECT statements.

MySQL for Python passes variables to MySQL in the same way that Python formats other kinds of output. If we wanted to specify just the floor of the search, we would assign the variable as any other and pass it to the execute() method as a string. Consider the following snippet from a Python terminal session:

>>> value = "7.50"
>>> command = cur.execute("""SELECT * FROM menu WHERE price = %s""" %(value))
>>> results = cur.fetchall()
>>> for record in results:
... print record[0], ". ", record[1], "(%s)" %record[2]
... 
1 . tuna (7.50)

If we wanted the user to have the option of specifying the price precisely or using comparative expressions, we can add in that option along with making the previous variable user-defined.

>>> operation = input("operation: ")
operation: '='
>>> value = input("value: ")
value: 7.50
>>> command = cur.execute("""SELECT * FROM menu WHERE price %s %s""" %(operation, value))
>>> results = cur.fetchall()
>>> for record in results:
... print record[0], ". ", record[1], "(%s)" %record[2]
... 
1 . tuna (7.50)

As you may have surmised by now, the execute() method is simply passing the MySQL statement as a string to _mysql, which in turn passes it to the C database API, which in turn passes it to MySQL. This being the case, we can define the statement separately and pass it to execute() as a variable. Consider the following replacement for the latter half of the preceding code.

>>> statement = """SELECT * FROM menu WHERE price %s %s""" %(operation, value)
>>> command = cur.execute(statement)
>>> results = cur.fetchall()
>>> for record in results:
... print record[0], ". ", record[1], "(%s)" %record[2]
... 
1 . tuna (7.50)
主站蜘蛛池模板: 荃湾区| 新余市| 横山县| 连城县| 亳州市| 鄂温| 略阳县| 新化县| 来凤县| 武宣县| 婺源县| 朝阳县| 沙河市| 达日县| 铜梁县| 通榆县| 肥乡县| 略阳县| 松溪县| 柏乡县| 彭阳县| 逊克县| 合水县| 启东市| 应城市| 伊宁市| 集安市| 台前县| 榆中县| 建德市| 平江县| 普格县| 包头市| 长丰县| 东兰县| 平潭县| 云安县| 镇远县| 会泽县| 红河县| 普格县|