Breaking News: Grepper is joining You.com. Read the official announcement!

python copy table from one database to another

Xabos answered on November 16, 2023 Popularity 1/10 Helpfulness 3/10

answer python copy table from one database to another

python copy table from one database to another

Comment

To copy a table from one database to another using Python,
you can use the sqlite3 module for SQLite databases or psycopg2 for PostgreSQL databases.
Below are examples for both SQLite and PostgreSQL databases.

import sqlite3

# Connect to the source database
source_conn = sqlite3.connect('source.db')
source_cursor = source_conn.cursor()

# Connect to the destination database
dest_conn = sqlite3.connect('destination.db')
dest_cursor = dest_conn.cursor()

# Specify the table name to copy
table_name = 'your_table_name'

# Fetch all rows from the source table
source_cursor.execute(f'SELECT * FROM {table_name}')
rows = source_cursor.fetchall()

# Create the same table in the destination database
dest_cursor.execute(f'CREATE TABLE IF NOT EXISTS {table_name} (column1, column2, ...)')

# Insert rows into the destination table
dest_cursor.executemany(f'INSERT INTO {table_name} VALUES (?, ?, ...)', rows)

# Commit the changes and close the connections
dest_conn.commit()
source_conn.close()
dest_conn.close()

Replace 'source.db', 'destination.db', and 'your_table_name' with the appropriate values for your databases and table.

PostgreSQL Example:
import psycopg2

# Connect to the source database
source_conn = psycopg2.connect(
    host="source_host",
    database="source_db",
    user="source_user",
    password="source_password"
)
source_cursor = source_conn.cursor()

# Connect to the destination database
dest_conn = psycopg2.connect(
    host="destination_host",
    database="destination_db",
    user="destination_user",
    password="destination_password"
)
dest_cursor = dest_conn.cursor()

# Specify the table name to copy
table_name = 'your_table_name'

# Fetch all rows from the source table
source_cursor.execute(f'SELECT * FROM {table_name}')
rows = source_cursor.fetchall()

# Create the same table in the destination database
dest_cursor.execute(f'CREATE TABLE IF NOT EXISTS {table_name} AS SELECT * FROM {table_name} WHERE 1=0')

# Insert rows into the destination table
dest_cursor.executemany(f'INSERT INTO {table_name} VALUES (%s, %s, ...)', rows)

# Commit the changes and close the connections
dest_conn.commit()
source_conn.close()
dest_conn.close()

Replace the connection parameters (source_host, source_db, source_user, source_password,
destination_host, destination_db, destination_user, destination_password),
and 'your_table_name' with the appropriate values for your databases and table.

Note: Ensure that you have the necessary permissions to read from the source table and 
write to the destination table in both databases. Also, handle sensitive information such as database credentials securely.

Popularity 1/10 Helpfulness 3/10 Language python

Source: Grepper

Tags: copy database python

Link to this answer
Share Copy Link

Contributed on Nov 16 2023

Xabos

0 Answers Avg Quality 2/10

python copy table from one database to another

Contents

More Related Answers

python copy table from one database to another

Grepper

Documentation

Social

Legal

Contact

Oops, You will need to install Grepper and log-in to perform this action.