mssql_to_postgresql

pyhelpers.dbms.utils.mssql_to_postgresql(mssql, postgres, mssql_schema=None, postgres_schema=None, chunk_size=None, excluded_tables=None, file_tables=False, memory_threshold=2.0, update=False, confirmation_required=True, verbose=True)

Copy tables of a database from a Microsoft SQL server to a PostgreSQL server.

Parameters:

• mssql (pyhelpers.dbms.MSSQL) – Name of the Microsoft SQL (source) database.

• postgres (pyhelpers.dbms.PostgreSQL) – Name of the PostgreSQL (destination) database.

• mssql_schema (str | None) – Name of the schema to be migrated from the SQL Server.

• postgres_schema (str | None) – Name of the schema to store the migrated data in the PostgreSQL server.

• chunk_size (int | None) – Number of rows in each batch to be read/written at a time; defaults to None.

• excluded_tables (list | None) – Names of tables excluded from data migration.

• file_tables (bool) – Whether to include FileTables; defaults to False.

• memory_threshold (float | int) – Threshold (in GiB) beyond which data is migrated by partitions; defaults to 2..

• update (bool) – Whether to redo the transfer between database servers; defaults to False.

• confirmation_required (bool) – Whether to request confirmation before proceeding; defaults to True.

• verbose (bool | int) – Whether to print relevant information; defaults to True.

Examples:

>>> from pyhelpers.dbms.utils import mssql_to_postgresql
>>> from pyhelpers.dbms import PostgreSQL, MSSQL
>>> from pyhelpers._cache import example_dataframe
>>> # Connect/create a PostgreSQL database, which is named [testdb]
>>> mssql_testdb = MSSQL(database_name='testdb', verbose=True)
Creating a database: [testdb] ... Done.
Connecting <server_name>@localhost:1433/testdb ... Successfully.
>>> mssql_testdb.database_name
'testdb'
>>> example_df = example_dataframe()
>>> example_df
            Longitude   Latitude
City
London      -0.127647  51.507322
Birmingham  -1.902691  52.479699
Manchester  -2.245115  53.479489
Leeds       -1.543794  53.797418
>>> test_table_name = 'example_df'
>>> # Import the example dataframe into a table named [example_df]
>>> mssql_testdb.import_data(example_df, table_name=test_table_name, index=True, verbose=2)
To import data into [dbo].[example_df] at <server_name>@localhost:1433/testdb
? [No]|Yes: yes
Importing the data into the table [dbo].[example_df] ... Done.
>>> mssql_testdb.get_column_names(table_name=test_table_name)
['City', 'Longitude', 'Latitude']

Figure 24 The table [dbo].[example_df] in the Microsoft SQL Server database [testdb].

>>> # Create an instance for a PostgreSQL database, which is also named "testdb"
>>> postgres_testdb = PostgreSQL(database_name='testdb', verbose=True)
Password (postgres@localhost:5432): ***
Creating a database: "testdb" ... Done.
Connecting postgres:***@localhost:5432/testdb ... Successfully.
>>> # For now, the newly-created database doesn't contain any tables
>>> postgres_testdb.get_table_names()
{'public': []}
>>> # Copy the example data from the SQL Server to the PostgreSQL "testdb" (under "public")
>>> mssql_to_postgresql(mssql=mssql_testdb, postgres=postgres_testdb)
To copy tables from [testdb] (MSSQL) to "testdb" (PostgreSQL)
? [No]|Yes: yes
Processing tables ...
    (1/1) Copying [dbo].[example_df] to "public"."example_df" ... Done.
Completed.
>>> postgres_testdb.get_table_names()
{'public': ['example_df']}

Figure 25 The table “dbo”.”example_df” in the PostgreSQL database “testdb”.

>>> # Drop/delete the created databases
>>> mssql_testdb.drop_database(verbose=True)
To drop the database [testdb] from <server_name>@localhost:1433
? [No]|Yes: yes
Dropping [testdb] ... Done.
>>> postgres_testdb.drop_database(verbose=True)
To drop the database "testdb" from postgres:***@localhost:5432
? [No]|Yes: yes
Dropping "testdb" ... Done.