0

How could I insert into a DF multiple values, as such:

INSERT INTO points_data (name, address, email, phone, profession)
VALUES ("bob", "street", any@any, 1234, dev),("bob", "street", any@any, 1234, dev),("bob", "street", any@any, 1234, dev);

When the VALUES comes from a pandas database? I'm not finding a way to convert a pd database to tuples, I'm always getting something like nested lists...

Maybe using psygopg?

EDIT:

Ok, I've managed to "extract" the values from the DB with comprehension:

df = pd.DataFrame({'id1': [0, 1, 2, 3, 4],
                   'id2': [5, 6, 7, 8, 9],
                   'id3': [5, 6, 7, 8, 9],
                   'id4': [5, 6, 7, 8, 9],
                   'id5': [5, 6, 7, 8, 9],
                   'id6': [5, 6, 7, 8, 9],
                   'id7': [5, 6, 7, 8, 9],})

# Iterating over multiple columns - differing data type
result = [(row[0], row[1],row[2],row[3],row[4],row[5], row[6]) for row in zip(df['id1'], df['id2'], df['id3'], df['id4'], df['id5'], df['id6'], df['id7'])]
result = tuple(result)
print(result)

((0, 5, 5, 5, 5, 5, 5), (1, 6, 6, 6, 6, 6, 6), (2, 7, 7, 7, 7, 7, 7), (3, 8, 8, 8, 8, 8, 8), (4, 9, 9, 9, 9, 9, 9))

Now, problem is the number of rows is not fixed, so this works on this exemple. Is there a way to get the rows "automatically", according to the number of rows provided?

Paulo Feresin
  • 39
  • 6
  • 2
    Look at pd.DataFrame.to_sql? – Scott Boston Jul 20 '22 at 13:48
  • 1
    Does this answer your question? [How to insert a pandas dataframe to an already existing table in a database?](https://stackoverflow.com/questions/38610723/how-to-insert-a-pandas-dataframe-to-an-already-existing-table-in-a-database) – Umut TEKİN Jul 20 '22 at 14:31
  • I think this answer it, but I'm not sure how to implement it. Could you give an example? – Paulo Feresin Jul 20 '22 at 14:38

1 Answers1

0

df.values will convert your dataframe to a numpy array that you can convert to tuples:

tuple(map(tuple, df.values))

Output:

((0, 5, 5, 5, 5, 5, 5), (1, 6, 6, 6, 6, 6, 6), (2, 7, 7, 7, 7, 7, 7), (3, 8, 8, 8, 8, 8, 8), (4, 9, 9, 9, 9, 9, 9))
Tranbi
  • 11,407
  • 6
  • 16
  • 33