def select_row_where(schema, table, select_col, select_value):
conn = None
try:
# read connection parameters
params = pgDBConfig.config()
# connect to the PostgreSQL server
print('Connecting to the PostgreSQL database...')
conn = psycopg2.connect(**params)
# create a cursor
cur = conn.cursor()
sql_command = "SELECT * FROM {}.{} WHERE {} = {};".format(
str(schema), str(table), str(select_col), str(select_value))
print(sql_command)
# execute a statement
cur.execute(sql_command)
records = cur.fetchall()
conn.close()
return records
# close the communication with the PostgreSQL
cur.close()
except (Exception, psycopg2.DatabaseError) as error:
print(error)
finally:
if conn is not None:
conn.close()
print('Database connection closed.')
def check_db_version():
""" Connect to the PostgreSQL database server """
conn = None
try:
# read connection parameters
params = pgDBConfig.config()
# connect to the PostgreSQL server
print('Connecting to the PostgreSQL database...')
conn = psycopg2.connect(**params)
# create a cursor
cur = conn.cursor()
# execute a statement
print('PostgreSQL database version:')
cur.execute('SELECT version()')
# display the PostgreSQL database server version
db_version = cur.fetchone()
print(db_version)
# close the communication with the PostgreSQL
cur.close()
except (Exception, psycopg2.DatabaseError) as error:
print(error)
finally:
if conn is not None:
conn.close()
print('Database connection closed.')
def insert_multi_column_and_geometryPolygon(schema, table, list_col,
list_value, wkt_poylygon, srid):
conn = None
try:
# read connection parameters
params = pgDBConfig.config()
# connect to the PostgreSQL server
print('Connecting to the PostgreSQL database...')
conn = psycopg2.connect(**params)
# create a cursor
cur = conn.cursor()
# get list of columns, values
fieldList = str()
valueList = str()
for field in list_col:
fieldList = fieldList + ", " + field
for value in list_value:
valueList = valueList + ", \'" + str(value) + "\'"
# create a SQL command
sql_command = '''INSERT INTO {}.{} (the_geom, {})
VALUES ( ST_GeomFromText('{}',{}), {});'''.format(
str(schema), str(table), str(fieldList[1:]), wkt_poylygon, srid,
str(valueList[1:]))
print(sql_command)
# execute a statement
cur.execute(sql_command)
# commit the changes to the database
conn.commit()
# count successfully result
print(cur.rowcount, "Record inserted successfully into the table.")
# close the communication with the PostgreSQL
cur.close()
except (Exception, psycopg2.DatabaseError) as error:
print(error)
finally:
if conn is not None:
conn.close()