Write a quick pandas data file for postgres

Question

I am wondering about the fastest way to write data from a pandas DataFrame to a table in mail messages DB.

1) I tried pandas.to_sql , but for some reason an entity is required to copy data,

2) In addition, I tried the following:

 import io f = io.StringIO() pd.DataFrame({'a':[1,2], 'b':[3,4]}).to_csv(f) cursor = conn.cursor() cursor.execute('create table bbbb (a int, b int);COMMIT; ') cursor.copy_from(f, 'bbbb', columns=('a', 'b'), sep=',') cursor.execute("select * from bbbb;") a = cursor.fetchall() print(a) cursor.close() 

but returns an empty list [] .

So, I have two questions: what is the fastest way to copy data from python code (dataframe) to postgres DB? and what was wrong with the second approach I tried?

Answer 1

Your second approach should be very quick.

There are two problems in the code:

• After writing csv to f you are at the end of the file. Before reading, you need to return your position to the beginning.
• When writing csv you need to omit the header and index

Here's what your latest code looks like:

 import io f = io.StringIO() pd.DataFrame({'a':[1,2], 'b':[3,4]}).to_csv(f, index=False, header=False) # removed header f.seek(0) # move position to beginning of file before reading cursor = conn.cursor() cursor.execute('create table bbbb (a int, b int);COMMIT; ') cursor.copy_from(f, 'bbbb', columns=('a', 'b'), sep=',') cursor.execute("select * from bbbb;") a = cursor.fetchall() print(a) cursor.close()