How to write Pandas DataFrame to PostgreSQL table?

In this article, we will be looking at some methods to write Pandas dataframes to PostgreSQL tables in the Python.

Method 1: Using to_sql() function

to_sql function is used to write the given dataframe to a SQL database.

Syntax 

df.to_sql('data', con=conn, if_exists='replace', index=False)

Parameters :

• data: name of the table.
• con: connection to the database.
• if_exists: if table exists or not. "replace" or "append".
• index: True or False.

Example:

In the example demonstrated below, we import the required packages and modules, establish a connection to the PostgreSQL database and convert the dataframe to PostgreSQL table by using the to_sql() method. Finally, all rows are fetched using fetchall() method.

To access the CSV file used click here.

Output:

Method 2: Using execute_values() function

The execute_values() function from the psycopg2 library is used to get the postgres table of the given data frame.

Syntax:

psycopg2.extras.execute_values(cur, sql, argslist, template=None, page_size=100, fetch=False)

Parameters:

• cur – the cursor that will be used to run the query.
• sql – the query that will be run. A single percent s placeholder must be present, which will be substituted by a VALUES list.
• argslist — a list of series or dictionaries containing the query's arguments. The template must be followed in terms of font and content.
• template – the snippet that will be merged into each item in the argslist to form the query
• page size – the maximum amount of argslist items that each statement can have.
• fetch - it's similar to fetchall. the values can be "True" or "False"

Example:

In this example, the same CSV file is used in this method. code begins with importing packages, then we form a custom function execute_values, where the given dataframe, connection, and table name are given as arguments. The dataframe rows and values are updated into the PostgreSQL table using the execute_values() method. The defined method contains an exception handling block, if there's no exception "execute_values() done" is printed.

Output:

execute_values() done