import psycopg2
from osgeo import ogr


# connect to the database
connection = psycopg2.connect(user="postgres",
                              password="password",
                              host="localhost",
                              database="Datamall")
# create cursor
cursor = connection.cursor()
cursor.execute("DROP TABLE IF EXISTS trial1")
cursor.execute("CREATE TABLE trial1 (id SERIAL PRIMARY KEY, geom Geometry)")
cursor.execute("CREATE INDEX trial1_index ON trial1 USING GIST(geom)")
print("Successfully created ")
connection.commit()

# define OSMfile path
osm = ogr.Open(r"C:UsersPoonam PatelInternshipDataOSMOSMpedestrian.osm")
layer = osm.GetLayer(1)

#  delete the existing contents of the table
cursor.execute("DELETE FROM trial1")
print(str(layer.GetFeatureCount()))
for i in range(layer.GetFeatureCount()):
    feature = layer.GetFeature(i)
    # Get feature geometry
    geometry = feature.GetGeometryRef()
    wkt = geometry.ExportToWkt()
    # Insert data into database,
    cursor.execute("INSERT INTO trial1 (geom) VALUES (ST_GeomFromText(" + "'" + wkt + "', 4326))")
    print("Data inserted successfully")
connection.commit()