In [3]:

    

import psycopg2
paramstring = "dbname='%s' user='%s' host='%s' password='%s'" % ('natural_earth2','main','localhost','main')
conn = psycopg2.connect(paramstring)


    

In [15]:

    

curs = conn.cursor()
curs.execute("""SELECT table_name FROM information_schema.tables WHERE table_schema = 'public'""")
for table in curs.fetchall():
    print(table)
conn.commit()


    

    




('geography_columns',)
('geometry_columns',)
('spatial_ref_sys',)
('raster_columns',)
('raster_overviews',)
('ne_10m_admin_0_countries',)
('ne_10m_admin_1_states_provinces_shp',)
('ne_10m_geography_marine_polys',)
('ne_10m_geography_regions_elevation_points',)
('ne_10m_geography_regions_points',)
('ne_10m_geography_regions_polys',)
('ne_10m_lakes',)
('ne_10m_land',)
('ne_10m_ocean',)
('ne_10m_populated_places',)
('ne_10m_rivers_lake_centerlines',)
('ne_10m_urban_areas',)

In [43]:

    

curs = conn.cursor()
curs.execute("Select * FROM ne_10m_urban_areas")
colnames = [desc[0] for desc in curs.description]
conn.commit()
colnames


    

    
Out[43]:





['gid', 'scalerank', 'featurecla', 'area_sqkm', 'the_geom']

In [17]:

    

curs = conn.cursor()
curs.execute("CREATE TABLE cities ( id int4 primary key, name varchar(50), the_geom geometry(POINT,4326));")
conn.commit()


    

In [18]:

    

table='cities'
col="id, the_geom, name"
x=10
y=10
name='test'


    

In [23]:

    

querytmplate="INSERT INTO %(table)s (%(col)s) VALUES (1,ST_GeomFromText('POINT(%(x)f %(y)f)',4326),'%(name)s');"


    

In [26]:

    

querytmplate = querytmplate % {'table':table, 'col':col, 'x':x, 'y':x, 'name':name}


    

In [29]:

    

curs = conn.cursor()
curs.execute(querytmplate)
conn.commit()


    

In [33]:

    

curs = conn.cursor()
curs.execute("SELECT * FROM cities")
data = curs.fetchall()
conn.commit()
print(data)


    

    




[(1, 'test', '0101000020E610000000000000000024400000000000002440')]

In [37]:

    

curs = conn.cursor()
curs.execute("SELECT name, ST_AsText(the_geom) FROM cities")
data = curs.fetchall()
conn.commit()
print(data)


    

    




[('test', 'POINT(10 10)')]

In [41]:

    

data[0][1]


    

    
Out[41]:





'POINT(10 10)'

In [ ]: