无法使用executemany执行ST_GEOMFROMTEXT
问题描述:
在Ubuntu 16.04上使用python 3.5和mysql 5.7.18,我无法在executemany语句中使用ST_GEOMFROMTEXT函数。无法使用executemany执行ST_GEOMFROMTEXT
使用此代码:
print(query)
print(add_vals[-1:])
zip_cursor.executemany(query, add_vals[-1:])
我得到以下输出:
INSERT INTO zipcodes (zipcode, name, area, pop, emp, emp_pay, households, location) VALUES (%s,%s,%s,%s,%s,%s,%s,%s);
[["'54517'", "'CLAM LAKE, WI'", 201.441702, 112, 10, 600, 59, "ST_GEOMFROMTEXT('POINT(46.145917 -90.930676)')"]]
Traceback (most recent call last):
File "zip_parse.py", line 319, in <module>
main()
File "zip_parse.py", line 32, in main
insert_data(zip_db, zip_cursor, data)
File "zip_parse.py", line 160, in insert_data
zip_cursor.executemany(query, add_vals[-1:])
File "/usr/local/lib/python3.5/dist-packages/mysql/connector/cursor.py", line 618, in executemany
return self.execute(stmt)
File "/usr/local/lib/python3.5/dist-packages/mysql/connector/cursor.py", line 515, in execute
self._handle_result(self._connection.cmd_query(stmt))
File "/usr/local/lib/python3.5/dist-packages/mysql/connector/connection.py", line 488, in cmd_query
result = self._handle_result(self._send_cmd(ServerCmd.QUERY, query))
File "/usr/local/lib/python3.5/dist-packages/mysql/connector/connection.py", line 395, in _handle_result
raise errors.get_exception(packet)
mysql.connector.errors.DataError: 1416 (22003): Cannot get geometry object from data you send to the GEOMETRY field
然而,通过声明似乎在复制到mysql命令行就好了去:
mysql> INSERT INTO zipcodes (zipcode, name, area, pop, emp, emp_pay, households, location) VALUES ('54517','CLAM LAKE, WI',201.441702,112,10,600,59,ST_GEOMFROMTEXT('POINT(46.145917 -90.930676)'));
Query OK, 1 row affected (0.05 sec)
过去我广泛使用了ST_GEOMFROMTEXT函数,而且我非常自信这是将空间数据添加到几何字段(字段'位置'具有类型'POINT')的正确方法。正如所证明的那样,它不需要引用它来在命令行上工作(这经常是我的经验中的焦点)。
任何想法为什么这不与执行许多?
答
我有同样的问题:
import pymysql
point = 'POINT(18.16 -66.72)'
geojson = '{ "type": "Point", "coordinates": [102.0, 0.0]}'
下面的代码片段不起作用:
"INSERT INTO `zip`(`id`, `zip`, `location`, `geometry`) VALUES (NULL,100, ST_PointFromText('%s'), ST_GeomFromGeoJSON('%s'))"
qparams = (point, geojson)
cursor.execute(sql, qparams)
所以答案就是以这种方式来使用它:
"INSERT INTO `zip`(`id`, `zip`, `location`, `geometry`) VALUES (NULL,100, ST_PointFromText('"+ point +"'), ST_GeomFromGeoJSON('"+ geojson +"'))"