3

I would like to clone a schema of PostGIS into the same database using PyQGIS. At the moment, I'm able to do it using SQL using pgAdmin with this code:

CREATE OR REPLACE FUNCTION clone_schema(source_schema text, dest_schema text) RETURNS void AS
$$

DECLARE
  object text;
  buffer text;
  default_ text;
  column_ text;
BEGIN
  EXECUTE 'CREATE SCHEMA ' || dest_schema ;
 
  -- TODO: Find a way to make this sequence's owner is the correct table.
  FOR object IN
    SELECT sequence_name::text FROM information_schema.SEQUENCES WHERE sequence_schema = source_schema
  LOOP
    EXECUTE 'CREATE SEQUENCE ' || dest_schema || '.' || object;
  END LOOP;
 
  FOR object IN
    SELECT table_name::text FROM information_schema.TABLES WHERE table_schema = source_schema
  LOOP
    buffer := dest_schema || '.' || object;
    EXECUTE 'CREATE TABLE ' || buffer || ' (LIKE ' || source_schema || '.' || object || ' INCLUDING CONSTRAINTS INCLUDING INDEXES INCLUDING DEFAULTS)';
   
    FOR column_, default_ IN
      SELECT column_name::text, replace(column_default::text, source_schema, dest_schema) FROM information_schema.COLUMNS where table_schema = dest_schema AND table_name = object AND column_default LIKE 'nextval(%' || source_schema || '%::regclass)'
    LOOP
      EXECUTE 'ALTER TABLE ' || buffer || ' ALTER COLUMN ' || column_ || ' SET DEFAULT ' || default_;
    END LOOP;
  END LOOP;
 
END;

$$ LANGUAGE plpgsql VOLATILE;

SELECT clone_schema('old_schema','new_schema');

I would like to launch this code directly into QGIS using PyQGIS. I suppose first step would be to be :

  • connect to the DB
  • then to launch the SQL code
  • and then to refresh the PostGIS UI windows in QGIS in order to display the new schema, but how? I begin in PostGIS/PyQGIS.

I try the code below but it seems it doesn't work. There is no error message, and I do not know if it is a syntax error or an authorization error to do this action from PyQGIS.

import psycopg2
conn = psycopg2.connect("dbname='DB' host='localhost' user='postgres' password='Pass'")
c = conn.cursor()

sql = """
CREATE OR REPLACE FUNCTION clone_schema(source_schema text, dest_schema text) RETURNS void AS
$$

DECLARE
  object text;
  buffer text;
  default_ text;
  column_ text;
BEGIN
  EXECUTE 'CREATE SCHEMA ' || dest_schema ;
 
  -- TODO: Find a way to make this sequence's owner is the correct table.
  FOR object IN
    SELECT sequence_name::text FROM information_schema.SEQUENCES WHERE sequence_schema = source_schema
  LOOP
    EXECUTE 'CREATE SEQUENCE ' || dest_schema || '.' || object;
  END LOOP;
 
  FOR object IN
    SELECT table_name::text FROM information_schema.TABLES WHERE table_schema = source_schema
  LOOP
    buffer := dest_schema || '.' || object;
    EXECUTE 'CREATE TABLE ' || buffer || ' (LIKE ' || source_schema || '.' || object || ' INCLUDING CONSTRAINTS INCLUDING INDEXES INCLUDING DEFAULTS)';
   
    FOR column_, default_ IN
      SELECT column_name::text, replace(column_default::text, source_schema, dest_schema) FROM information_schema.COLUMNS where table_schema = dest_schema AND table_name = object AND column_default LIKE 'nextval(%' || source_schema || '%::regclass)'
    LOOP
      EXECUTE 'ALTER TABLE ' || buffer || ' ALTER COLUMN ' || column_ || ' SET DEFAULT ' || default_;
    END LOOP;
  END LOOP;
 
END;

$$ LANGUAGE plpgsql VOLATILE;
SELECT clone_schema('test2','astk4');
"""
c.execute(sql)

In pgAdmin I can see the SQL is received from PyQGIS but the action doesn't happen. On the screenshoot we can see that not all the code is written.

enter image description here

6
+100

If you want any change made to the database, you should use

conn.commit()

You should also use with statement to avoid forgetting to close the connection e.g

import psycopg2

with psycopg2.connect("") as conn:
    with conn.cursor("dbname='DB' host='localhost' user='postgres' password='Pass'") as cur:
        cur.execute(sql)

Another approach without psycopg2 can be

sql = """
CREATE OR REPLACE FUNCTION clone_schema(source_schema text, dest_schema text) RETURNS void AS
$$

DECLARE
  object text;
  buffer text;
  default_ text;
  column_ text;
BEGIN
  EXECUTE 'CREATE SCHEMA ' || dest_schema ;
 
  -- TODO: Find a way to make this sequence's owner is the correct table.
  FOR object IN
    SELECT sequence_name::text FROM information_schema.SEQUENCES WHERE sequence_schema = source_schema
  LOOP
    EXECUTE 'CREATE SEQUENCE ' || dest_schema || '.' || object;
  END LOOP;
 
  FOR object IN
    SELECT table_name::text FROM information_schema.TABLES WHERE table_schema = source_schema
  LOOP
    buffer := dest_schema || '.' || object;
    EXECUTE 'CREATE TABLE ' || buffer || ' (LIKE ' || source_schema || '.' || object || ' INCLUDING CONSTRAINTS INCLUDING INDEXES INCLUDING DEFAULTS)';
   
    FOR column_, default_ IN
      SELECT column_name::text, replace(column_default::text, source_schema, dest_schema) FROM information_schema.COLUMNS where table_schema = dest_schema AND table_name = object AND column_default LIKE 'nextval(%' || source_schema || '%::regclass)'
    LOOP
      EXECUTE 'ALTER TABLE ' || buffer || ' ALTER COLUMN ' || column_ || ' SET DEFAULT ' || default_;
    END LOOP;
  END LOOP;
 
END;

$$ LANGUAGE plpgsql VOLATILE;
SELECT clone_schema('old_schema','new_schema');
"""

md = QgsProviderRegistry.instance().providerMetadata('postgres')

# Approach 1 without using existing connection
uri = QgsDataSourceUri()
uri.setConnection("localhost", "5432", "DB-1", "postgres", "postgres")
conn1 = md.createConnection(uri.uri(), {})

# Part you need
conn1.executeSql(sql)


# Approach 2 using existing connection
# (declared in "Data Source manager | PostgreSQL")
conn2 = md.createConnection('DB-1')

# Part you need
conn2.executeSql(sql)

# Refresh in the GUI for browserModel
iface.browserModel().reload()

# If you got a crash you may try to execute refresh via the GUI button using PyQGIS
panelNameInYourLang = 'Browser'
browser = iface.mainWindow().findChild(QDockWidget, panelNameInYourLang)
browser.findChild(QAction, 'mActionRefresh').trigger()

FYI, if you use this other approach, you can create a schema using conn2.CreateSchema('new_empty_schema'), you can rename it conn2.renameSchema('old_schema','new_schema') but you will remain stuck to copy tables and associated constraints from one schema to another.

Copying table from one schema to another is quite easy e.g conn2.executeSql("CREATE TABLE {old_schema}.table_name AS TABLE {new_schema}.table_name;") but creating all indexes and constraints would be more painly to do e.g https://stackoverflow.com/questions/6601978/completely-copying-a-postgres-table-with-sql.

4
  • With the second approach I do not need to use "with" in order to close the connection, right?
    – zlikotp
    Oct 30 at 17:47
  • with syntax does not work for 2nd approach. In this approach, I don't see where you could close connection
    – ThomasG77
    Oct 30 at 18:09
  • the code works very well expect the last function : iface.browserModel().reload() I use last QGIS 3.22 and it seems QGIS always crash when I launch this part of the code. The workaround is to do it manually using the UI windows.. but if you have another workaround it would be great. Does it work on your computeR?
    – zlikotp
    Oct 31 at 8:55
  • The latest function works although I got a crash once. Edited my answer to refresh via the GUI but with programming
    – ThomasG77
    Oct 31 at 12:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.