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

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

Using user-defined variables

Just as in data retrieval, it is inevitable that you will want to utilize user input when inserting data into MySQL. MySQL for Python provides a consistent, Pythonic interface for this.

We use the same string conversion specifier as we did when incorporating user input into our SELECT statements in the previous chapter. Using the fish database, if we assume that the user gives us the name of the fish and the cost, we can code a user-defined INSERT statement as follows:

import MySQLdb, sys

mydb = MySQLdb.connect(host = 'localhost', 
                       user = 'skipper', 
                       passwd = 'secret', 
                       db = 'fish')
cur = mydb.cursor()

fish = sys.argv[1]
price = sys.argv[2]

statement = """INSERT INTO menu(name, price) VALUES(%s, %s)""" %(fish, price)
cur.execute(statement)

An alternative way of rendering the last two lines is to leave the value insertion to the execute() function. Instead of using %(fish, price) at the end of the first of the two lines, we can include the fish and price values as a second argument to execute():

statement = "INSERT INTO menu(name, price) VALUES (%s, %s)"
cur.execute(statement, (fish, price))

To make this program executable, you can preface this code with a shebang line, make the file executable (by changing the permissions on the file), and then call it as you would any other local executable that is not in your execution path. Alternatively, you can call it from the command-line by prefacing it with a call to your local Python interpreter. In either case, don't forget to supply the arguments for sys.argv[]. Here I have run it using the latter method:

 python ./user-defined-data.py angel 7.00

This then appends the data to the database in real time.

mysql> SELECT * FROM menu;
+----+----------------+-------+
| id | name | price |
+----+----------------+-------+
| 1 | tuna | 7.50 | 
| 2 | bass | 6.75 | 
| 3 | salmon | 9.50 | 
| 4 | catfish | 5.00 | 
| 5 | trout | 6.00 | 
| 6 | haddock | 6.50 | 
| 7 | yellowfin tuna | 12.00 | 
| 8 | sole | 7.75 | 
| 9 | angel | 7.00 | 
+----+----------------+-------+
9 rows in set (0.01 sec)

As this is all within the Python API, you are not limited merely to %s, but can use the same string formatting techniques as you would anywhere else in Python.

主站蜘蛛池模板: 诸暨市| 安阳县| 措美县| 花莲县| 伊吾县| 邓州市| 喜德县| 闽侯县| 鄂温| 石城县| 垫江县| 灵璧县| 铜鼓县| 东乌珠穆沁旗| 江安县| 万宁市| 廊坊市| 喀喇| 紫金县| 定日县| 杨浦区| 上林县| 家居| 玉门市| 曲阜市| 锦州市| 张家港市| 临颍县| 吉水县| 南雄市| 临桂县| 建宁县| 密云县| 页游| 三明市| 韶山市| 鹿泉市| 丹阳市| 太仓市| 自贡市| 灵寿县|