I am using postgres database with postgis extension. I have to insert into data into postgres using python as backend. I am using psycopg2 for connectivity and version of my python is 3.7 . I am not able to push my data because python keeps on escaping string literals. The following is my code.
def insert(lvalues):
lvalues = [str(i) for i in lvalues]
values = ', '.join(lvalues)
return values
if __name__ == '__main__':
cursor, conn = connect()
cord = [
[
[
67.774658203125,
25.69103802005013
],
[
68.90625,
24.367113562651262
],
[
73.970947265625,
25.958044673317843
],
[
74.87182617187499,
28.565225490654658
],
[
70.33447265624999,
28.815799886487298
],
[
67.774658203125,
25.69103802005013
]
]
]
js = insert(('GISid', 'srid', 'familyid', 'geom', 'nameofgeom'))
jst = '''{ "type": "Polygon","coordinates": ''' + str(cord) + ''',
"crs": {"type": "name", "properties": {"name": "EPSG:4326"}}
}'''
print(cursor.mogrify(
"INSERT INTO public.spatial_data(gid, srid, familyid, geom, nameofgeom) VALUES (103,4326,2,ST_GeomFromGeoJSON(%s),'polygon') ",
(jst,)))
I result i get is this as below:
b'INSERT INTO public.spatial_data(gid, srid, familyid, geom, nameofgeom) VALUES (103,4326,2,ST_GeomFromGeoJSON(\'{ "type": "Polygon","coordinates": [[[67.774658203125, 25.69103802005013], [68.90625, 24.367113562651262], [73.970947265625, 25.958044673317843], [74.87182617187499, 28.565225490654658], [70.33447265624999, 28.815799886487298], [67.774658203125, 25.69103802005013]]],\n "crs": {"type": "name", "properties": {"name": "EPSG:4326"}}\n }\'),\'polygon\') '
Can somebody suggest a way of removing \ and \n that appear in the mogrify result. cursor is psycopg2 connection cursor object. Thank you in advance.
Though mogrify gives a statement that will be similar to the one that would get executed if you use execute command, it gives us a repr version. If plan on using mogrify output directly there should be a problem, but if you use an separate execute statement it might result in error.( Not always necessarily). String replacing helped me escape the problem.
jst.replace('\n', ' ').replace("\'",' ')