Getting 'TypeError: not all arguments converted during string formatting' in Python Postgr-CodePudding

I have read many answers to this same question, but haven't seen anything that works for me.

I have a very basic app just to test things (in app.py and database.py). I'm using ElephantSQL, psycopg2-binary and have checked - the table on ElephantSQL is made from this code, but cannot insert from here. I can obviously insert from ElephantSQL.

I am just testing with the headline string, but headline will ultimately be the result of a web scrape using bs4.

This is my first time using a database with Python, but I'm absolutely blocked on what should be quite simple code. Please help.

This is database.py:

from psycopg2.extras import execute_values

CREATE_HEADLINES = """CREATE TABLE IF NOT EXISTS headlines 
(headline TEXT);"""
INSERT_HEADLINE = "INSERT INTO headlines (headline) VALUES %s;"
SELECT_ALL_HEADLINES = "SELECT * FROM headlines;"

def create_tables(connection):
    with connection:
        with connection.cursor() as cursor:
            cursor.execute(CREATE_HEADLINES)

def create_headlines(connection):
    with connection:
        with connection.cursor() as cursor:
            cursor.execute(CREATE_HEADLINES)

def add_headline(connection, headline):
    with connection:
        with connection.cursor() as cursor:
            cursor.execute(INSERT_HEADLINE, (headline))        

def get_headlines(connection):
    with connection:
        with connection.cursor() as cursor:
            cursor.execute(SELECT_ALL_HEADLINES)
            return cursor.fetchall()

this is the main part of my app.py (I've hidden sensitive stuff):

import psycopg2
from bs4 import BeautifulSoup
import database

connection = psycopg2.connect(db_url)
headline = 'Hello, world!'
print(headline)
print(type(headline))

database.create_tables(connection)
database.create_headlines(connection)
database.add_headline(connection, headline)

Returns this error:

Hello, world!
<class 'str'>
Traceback (most recent call last):
  File "/Users/jamesdanielmalvern/femicide/scraper/app.py", line 33, in <module>
    database.add_headline(connection, headline)
  File "/Users/jamesdanielmalvern/femicide/scraper/database.py", line 21, in add_headline
    cursor.execute(INSERT_HEADLINE, (headline))        
TypeError: not all arguments converted during string formatting

CodePudding user response：

An INSERT needs around the values parenthesis, that is also true for placejholders

INSERT_HEADLINE = "INSERT INTO headlines (headline) VALUES (%s);

And You need also two elents in a list when you insert, if you have only 1 you need to add and empty one

cursor.execute(INSERT_HEADLINE, (headline,))