Using pandas dictionary for creating sql tables-CodePudding

I would like to use my pandas dictionary in order to create sql tables. My dictionary looks like this:

my_dict -> {'ETL_A': ['TABLE_001', 'TABLE_002', 'TABLE_003'],
            'TEST': ['TABLE_TEST', 'TEST001', 'SAMPLE_TEST'],
            'MY_PRIVATE_SHEMA': ['WWW_001', 'LTT_222']}

In this dictionary my shema names are keys...ETL_A,TEST,MY_PRIVATE_SHEMA with list of tables. I would need to create sql inserts:

string = 'CREATE TABLE {SCHEMA}.TABLE_NAME SELECT * FROM {SCHEMA}.TABLE_NAME at(timestamp => {TIMESTAMP}::timestamp);'.format(SCHEMA=SCHEMA, TIMESTAMP=TIMESTAMP)

I'm using variables SCHEMA and TIMESTAMP. Example SCHEMA = 'TEST' and TIMESTAMP = '10-06-26 02:31:29'. I would like to base don my key in the dictionary so in my case shema name , create sql ddl commands. So if I need to create tables in the TEST shema my code would give me an output:

CREATE TABLE TEST.TABLE_TEST SELECT * FROM TEST.TABLE_TEST at(timestamp => '10-06-26 02:31:29'::timestamp);
CREATE TABLE TEST.TEST001 SELECT * FROM TEST.TEST001 at(timestamp => '10-06-26 02:31:29'::timestamp);
CREATE TABLE TEST.SAMPLE_TEST SELECT * FROM TEST.SAMPLE_TEST at(timestamp => '10-06-26 02:31:29'::timestamp);

So based on my key(schema name) I would get all tables from that shema.

CodePudding user response：

If I understand what you want correctly, you almost have a solution -- you just need to iterate through your dictionary, and plug the values in the string.

from datetime import datetime

for schema, tables in my_dict.items():
    for table in tables:
        timestamp = datetime.now().strftime('%Y-%m-%d %H:%M:%S')
        print(f"CREATE TABLE {schema}.{table} SELECT * FROM {schema}.{table} at(timestamp => '{timestamp}'::timestamp);")

Which for my_dict from the question outputs the following:

CREATE TABLE ETL_A.TABLE_001 SELECT * FROM ETL_A.TABLE_001 at(timestamp => '2022-10-04 19:31:25'::timestamp);
CREATE TABLE ETL_A.TABLE_002 SELECT * FROM ETL_A.TABLE_002 at(timestamp => '2022-10-04 19:31:25'::timestamp);
CREATE TABLE ETL_A.TABLE_003 SELECT * FROM ETL_A.TABLE_003 at(timestamp => '2022-10-04 19:31:25'::timestamp);
CREATE TABLE TEST.TABLE_TEST SELECT * FROM TEST.TABLE_TEST at(timestamp => '2022-10-04 19:31:25'::timestamp);
CREATE TABLE TEST.TEST001 SELECT * FROM TEST.TEST001 at(timestamp => '2022-10-04 19:31:25'::timestamp);
CREATE TABLE TEST.SAMPLE_TEST SELECT * FROM TEST.SAMPLE_TEST at(timestamp => '2022-10-04 19:31:25'::timestamp);
CREATE TABLE MY_PRIVATE_SHEMA.WWW_001 SELECT * FROM MY_PRIVATE_SHEMA.WWW_001 at(timestamp => '2022-10-04 19:31:25'::timestamp);
CREATE TABLE MY_PRIVATE_SHEMA.LTT_222 SELECT * FROM MY_PRIVATE_SHEMA.LTT_222 at(timestamp => '2022-10-04 19:31:25'::timestamp);

I use an f-string rather than the .format(...) formatting, but it is very similar: instead the f in the front, you could equivalently also write .format(schema=schema, table=table, timestamp=timestamp) at the end.