python MySQLdb迭代查询表

Question

我尝试使用Python和MySQLdb查询一个表和另一个表。到目前为止我得到的是：

db = MySQLdb.connect( host = 'localhost', user = 'user', passwd= 
'password', db = 'vacants')

cursor = db.cursor()

numrows = cursor.rowcount

query = "SELECT address, ((20903520) * acos (cos ( radians(38.67054) )* cos( 
radians( lat ) ) * cos( radians( `long` ) - radians(-90.22942) ) + sin ( 
radians(38.67054) ) * sin( radians( lat ) ))) AS distance FROM vacants HAVING 
distance < 100;"

cursor.execute(query)

我有一张桌子，cfs，还有一张，vacants。我想看看cfs的每一排是否有100英尺以内的空置房产。所以对于（弧度（38.67054）和弧度（-90.22942），我需要遍历cfs表，以便每个cfs纬度和经度替换这两个数字。（这只是我们使用的经纬度测试）

最后，我希望（在.csv中）有空的属性地址、与服务调用的距离和调用类型（这是服务调用数据库中两个独立的字段）。类似于上面的查询：

下面是示例数据-服务坐标调用：

38.595767638008056,-90.2316138251402
38.57283495467307,-90.24649031378685
38.67497061776659,-90.28415976525395
38.67650431524285,-90.25623757427952
38.591971519414784,-90.27782710145746
38.61272746420862,-90.23292862245287
38.67312983860098,-90.23591869583113
38.625956494342674,-90.18853950906939
38.69044465638584,-90.24339061920696
38.67745024638241,-90.20657832034047

以及逃学者：

38.67054,-90.22942
38.642956,-90.21466
38.671535,-90.27293
38.666367,-90.23749
38.65339,-90.23141
38.645996,-90.20334
38.60214,-90.224815
38.67265,-90.214134
38.665504,-90.274414
38.668354,-90.269966

Answer 1

这不是最终的解决方案，因为没有足够的信息（地址字段？和20903520？）但通过演示如何迭代这两个表，并将CFS表中的lat、lon替换为应用于vacants表的查询，它可能会帮助您步入正轨：

import mysql.connector
cnx1 = mysql.connector.connect(user='root',password='xxxx',host='127.0.0.1',database=db)
cursor1 = cnx1.cursor()
cnx2 = mysql.connector.connect(user='root',password='xxxx',host='127.0.0.1',database=db)
cursor2 = cnx2.cursor()

sql_cfs = ('select lat,lon from cfs')

cursor1.execute(sql_cfs)
for cfs in cursor1:
    [cfs_lat,cfs_lon] = cfs
    print (cfs_lat,cfs_lon)
    query = ("SELECT address, ((20903520) * " \
        "acos (cos(radians(lon)) *" \
        "cos(radians({})) * " \
        "cos(radians({})-radians(lat)) + sin(radians(lon)) * " \
        "sin( radians({})))) AS distance " \
        "FROM vacants HAVING distance < 100;".format(cfs_lat,cfs_lon,cfs_lat))

    print (query)

    cursor2.execute(query)
    for vacants in cursor2:
        print (vacants)