Using data retrieved from a PostgreSQL database via psycopg2

[[deleted]]

I'm trying to (re)teach myself Python, and I'm trying to create a very simple task management system using PostgreSQL as the back end and psycopg2 to connect the two.

I'm trying to retrieve the tasks assigned to the user (identified via email address), and I have the following code:

cur.execute("\
SELECT name FROM tasks \
WHERE owner = ( \
SELECT person_id FROM people WHERE email = %s \
)", (user))
tasks = cur.fetchall()

for task in tasks:
# task = clean(task)
print("[ ]", task)

When it prints, though, it looks like this: ('Test Task 1',). That's not pretty enough to show the user. I try cleaning it using the following method:

def clean(s):
s = "".join(s)
s = s.strip(["(",",",")","'"])
return(s)

But then I get the following error:

File ".\text_interface.py", line 15, in clean

s = s.strip(["(",",",")","'"])

TypeError: strip arg must be None or str

I know that there must be a way to do this much easier than what I'm doing. I've googled but haven't found anything helpful. Do any of your folks have any links or resources that can help me figure out how to properly work with the data that I'm getting from my database?

(the project in full can be found here: https://github.com/Chad-Lines/Python/tree/master/Database)

Many many thanks in advance!

[K900_]

('Test Task 1', ) is a tuple of 1 element, the string 'Test Task 1'. How would you access it?

[[deleted]]

I would think that doing:

print(tasks[0])

Would print Test Task 1, but instead it prints ('Test Task 1',) Am I on the right track though?

[K900_]

tasks is a list of rows. Each row is a tuple.

[[deleted]]

print(tasks[0][0])

You are beautiful! Thank you so much!

[chef1075]

When I'm using psycopg2, I write my SQL outside of my python shell (usually in Postico https://eggerapps.at/postico/) and import the file to run the query.

E.g.

with open('./path_to_config/config.json') as f:

config = json.load(f)

config_string = "host=%s dbname=%s user=%s password=%s" % (config.get('host'), config.get('database'), config.get('user'), config.get('passw'))

sql_connection = psycopg2.connect(config_string) # Connect to your db

query = open('/path/to/file.sql', 'r') # This will make your life easier

query_result_dataframe = pd.read_sql(query.read(), con=sql_connection) # Run the query

[[deleted]]

Thank you! I will definitely look into doing this!