CODEWITHSUNDEEP
pythonpostgresqlpsycopg2
jjepsuomi
jjepsuomi

Reputation: 4373

Inserting JPEG-filenames into PostgreSQL table using Psycopg2 causes "not all arguments converted during string formatting" error

I'm trying to fill a PostgreSQL table (psycopg2, Python) with the filenames I have in a specific folder. I have created a function that should do the trick, but I get the error:

not all arguments converted during string formatting,

when I run my function. I did a test run and called the function in the following way:

insert_file_names_into_database(["filename1_without_extension", "filename2_without_extension"]),

and I had no problems and the INSERT worked fine. If I did the following:

insert_file_names_into_database(["filename1.extension", "filename2.extension"]),

Then I get the error above. So the problem seems to be the "." character (e.g. image.jpg) which causes the SQL INSERT to fail. I tried to consult the Psycopg2 docs about this, but I found no examples relating to this specific case.

How should I edit the piece of code so I can get to work even with "." characters in the filenames?

def insert_file_names_into_database(file_name_list):
    """ insert multiple filenames into the table  """
    sql = "INSERT INTO mytable(filename) VALUES(%s)"
    conn = None
    try:
        # read database configuration
        # connect to the PostgreSQL database
        conn = psycopg2.connect(
            host="localhost",
            database="mydatabase",
            user="myusername",
            password="mypassword")
        # create a new cursor
        cur = conn.cursor()
        # execute the INSERT statement
        cur.executemany(sql, file_name_list)
        # commit the changes to the database
        conn.commit()
        # close communication with the database
        cur.close()
    except (Exception, psycopg2.DatabaseError) as error:
        print(error)
    finally:
        if conn is not None:
            conn.close()

Upvotes: 0

Views: 45

Answers (1)

jjepsuomi
jjepsuomi

Reputation: 4373

Solved it myself already. I knew I should be using tuples when working with the INSERT, but my function worked fine with list of strings without the "." characters.

The solution I got working was to convert the list of strings into a list of tuples like so:

tuple_file_name = [tuple((file_name,)) for file_name in file_name_list]

So for example if:

file_name_list = ["filename1.jpg", "filename2.jpg"]

Then giving this as input to my function fails. But by making it a list of tuples:

tuple_file_name = [tuple((file_name,)) for file_name in file_name_list] 
print(tuple_file_name)
[('filename1.jpg',), ('filename2.jpg',)]

Then now the function accepts the input tuple_file_name and the filenames are saved into the SQL table.

Upvotes: 1

Related Questions