I have a Python script to list PostgreSQL schemas using psycopg2.
#!/usr/bin/env python
import yaml
import psycopg2
def load_config(file_name):
with open(file_name, 'r') as stream:
config = yaml.load(stream)
return config
config = load_config('config.yml')['database']
conn = psycopg2.connect(host=config['host'], port=config['port'], dbname=config['name'], user=config['user'], password=config['password'])
cursor = conn.cursor()
print('conn = %s' % conn)
print('cursor = %s' % cursor)
sql_list_schemas = """
SELECT *
FROM information_schema.schemata
WHERE schema_name <> 'information_schema'
AND schema_name !~ E'^pg_';
"""
list_schemas = cursor.execute(sql_list_schemas)
print('list_schemas = %s' % list_schemas)
print('list_schemas.fetchall() = %s' % list_schemas.fetchall())
When I run the script, I got:
conn = <connection object at 0x7f0e12eef050; dsn: 'user=test password=xxxxxxxxxxxxx host=127.0.0.1 port=5432 dbname=test', closed: 0>
cursor = <cursor object at 0x7f0e1326c148; closed: 0>
list_schemas = None
Traceback (most recent call last):
File "./postgres_db_schema.py", line 26, in <module>
print('list_schemas.fetchall() = %s' % list_schemas.fetchall())
AttributeError: 'NoneType' object has no attribute 'fetchall'
I thought the SQL query is OK. When I execute the query using another PostgreSQL client, it returned some rows. Why did the cursor.execute
return None
? Is there something wrong in my script?
.execute()
just executes the query and does not return anything. It is up to you how you are going to fetch the results (ex: iterator, fetchall(), fetchone() etc.)
>>> cursor.execute(sql_list_schemas)
>>> list_schemas = cursor.fetchall()
--
Similarly,
>>> cursor.execute(sql_list_schemas)
>>> first_row = cursor.fetchone()
>>> second_row = cursor.fetchone()
>>> remaining_rows = cursor.fetchall()
--
PEP-2049
states that return values are not defined for .execute()
method so database connectors may return something different than None
. (ex: a boolean flag indicating whether sql command has been successfully executed or not)
However, one thing for sure, cursor.execute()
never returns the result set.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With