how to use pg_trgm operators e.g (<-> ) in python

222 views Asked by At

I'm using the pg_trgm for similarity search on PostgreSQL DB and I need to return the results to the PostGIS table, but I'm getting this programmer error, I learned that this error is related to syntax of the sql query I tried same query in PostgreSQL and it worked, but couldn't get it to working with python. what I use (Windows 10, Python 3.8, PostgreSQL 12.6)

class OSMGeocoder(object):
    """ A class to provide geocoding features using an OSM dataset in PostGIS."""
    def __init__(self, db_connectionstring):
        #db initialize db connection parameters
        self.db_connectionstring = db_connectionstring
        
    def geocode(self, placename):
        """ Geocode a given place name."""
        # here we crete the connection object
        conn = psycopg2.connect(self.db_connectionstring)
        cur = conn.cursor()
        #this is the core sql query, using triagrams to detect streets similar to a given placename
        #here we execute the sql and return all of the results
        cur.execute("SELECT name, name <-> '%s' AS weight, ST_AsText(ST_Centroid(wkb_geometry)) AS point FROM public.osm_roads ORDER BY weight LIMIT 10;" % placename)
        rows = cur.fetchall()
        conn.commit()
        cur.close()
        conn.close()
        return rows
if __name__ =='__main__':
    # the user must provide at least two parameters, the place name and the connection string to PostGIS
    if len(sys.argv) < 3 or len(sys.argv) > 3:
        print("usage: <placename> <connection string>")
        raise SystemExit
    placename = sys.argv[1]
    db_connectionstring = sys.argv[2]
    #here we instantiate the geocoder, providing the needed PostGIS connection parameters
    geocoder = OSMGeocoder(db_connectionstring)
    #here we query the geocode methiod, for getting the geocoded points for the given placenames
    results = geocoder.geocode(placename)
    print(results)

ProgrammingError: invalid dsn: missing "=" after "C:\Users\Lenovo\AppData\Roaming\jupyter\runtime\kernel-ee3068bc-0b95-4bba-a373-752c8196980f.json" in connection info string

0

There are 0 answers