Base, Calc, Python: INSERT, UPDATE, DELETE from spreadsheet
Posted: Fri Oct 19, 2018 7:04 pm
How it should work if set up properly:
Select the cell range to export or click any single cell within the "current region". The "current region" is a range of non-blank cells surrounded by blank cells or sheet boundaries.
Call a macro any way you prefer. The example spreadsheet contains 3 example sheets with push buttons.
What it does:
Write cell range data to a database table by substituting the positional parameters of an SQL statement with cell values. Running statements without parameter substitution is possible as well.
INSERT, UPDATE or DELETE statements are supported.
What it takes:
1) The Writer document attached to this posting. It contains Python code with a simple install routine.
2) A sheet cell named "Reg_Name" specifying the registered name of a database or the file URL of the database document.
3) A cell named "SQL_Statement" with a (parameterized) statement to be executed.
3a) Optional: named cells "User_Name" and "User_Password". If the database requires login and the two named cells are missing, the login credentials will be promtped for.
4) A sheet having a data range with one additional header row of type declarations above the header row of column labels. Row #1 gets type declarations, row #2 gets column labels, further rows gets the actual data to substitute the positional parameters with.
Below this posting you find two more attachments. A spreadsheet and a database document with a HSQLDB embedded. These are the two documents I used for testing.
The spreadsheet includes more detailted information about the setup and limitations.
Once the spreadsheet is set up properly, you click anywhere in a data range, call a macro and a database table will be updated, inserted into or deleted from.
I developed the code so it runs with Python3 (LibreOffice) and with Python2 (OpenOffice), however I could not test it thoroughly with OpenOffice because of frequent crashes.
The statement DELETE FROM "tblTypes" deletes all data without needing any parameters from sheet cells. You click a cell in a blank area and call the macro. OpenOffice raises some "null pointer exception" I'm not able to hunt down. The other two examples (INSERT and UPDATE) do work with OpenOffic 4.1.5.
How to use my test sheets:
Hit the push button with the ellipsis (...) on sheet "SQL_INSERT" to open a file picker dialog. Point to the downloaded Test_HSQLDB.odb. You may also register the database document and enter the registered name in the "DB_Name" cell M1.
Then click any cell in the red bordered area and hit the other push button labeled "Call Macro". It will insert 4 records with every push on the button.
Alternatively, you may select the first couple of rows or a multiple selection of rows. The macro disregards hidden rows of filtered ranges. But the 2 column headers (type declarations and column names) should always be included.
You can filter the little test range and the macro will insert only from visible rows.
Select any cell in the top-left area of sheet "SQL_UPDATE" and push the button. This will update some records with the current time in the "T" field.
Select any cell on sheet "SQL_DELETE" and push the button. This will delete all records from table "tblTypes", however this does not work with OpenOffice 4.1.5 on my Ubuntu system and I don't understand the problem.
Additional note on named cells in LibreOffice and OpenOffice:
The cell named "DB_Name" gets either the path of a database document or its registration name. The name points to absolute cell address $SQL_INSERT.$M$1.
The cell named "SQL_Statement" gets the parameterized statement to be executed. The name of this cell points to address $M$2. This is an absolute cell address with no sheet name. This way the name applies to cell M2 on every sheet in the same document and I could test different SQL statements using the same cell name on separate sheets.
With LibreOffice you have a little bit more flexibility because LibreOffice lets you define the same name differently on different sheets. With LibreOffice "SQL_Statement" may refer to M2 on one sheet, some other cell on another sheet and may be undefined on a third sheet. For compatibility reasons I did it the OpenOffice way. The macro tries to get the named cell on the active sheet and if this method fails, it tries to find the cell in the global name space.
The other 2 named cells are obsolete when working with embedded HSQL which does not support user logins.
Select the cell range to export or click any single cell within the "current region". The "current region" is a range of non-blank cells surrounded by blank cells or sheet boundaries.
Call a macro any way you prefer. The example spreadsheet contains 3 example sheets with push buttons.
What it does:
Write cell range data to a database table by substituting the positional parameters of an SQL statement with cell values. Running statements without parameter substitution is possible as well.
INSERT, UPDATE or DELETE statements are supported.
What it takes:
1) The Writer document attached to this posting. It contains Python code with a simple install routine.
2) A sheet cell named "Reg_Name" specifying the registered name of a database or the file URL of the database document.
3) A cell named "SQL_Statement" with a (parameterized) statement to be executed.
3a) Optional: named cells "User_Name" and "User_Password". If the database requires login and the two named cells are missing, the login credentials will be promtped for.
4) A sheet having a data range with one additional header row of type declarations above the header row of column labels. Row #1 gets type declarations, row #2 gets column labels, further rows gets the actual data to substitute the positional parameters with.
Below this posting you find two more attachments. A spreadsheet and a database document with a HSQLDB embedded. These are the two documents I used for testing.
The spreadsheet includes more detailted information about the setup and limitations.
Once the spreadsheet is set up properly, you click anywhere in a data range, call a macro and a database table will be updated, inserted into or deleted from.
I developed the code so it runs with Python3 (LibreOffice) and with Python2 (OpenOffice), however I could not test it thoroughly with OpenOffice because of frequent crashes.
The statement DELETE FROM "tblTypes" deletes all data without needing any parameters from sheet cells. You click a cell in a blank area and call the macro. OpenOffice raises some "null pointer exception" I'm not able to hunt down. The other two examples (INSERT and UPDATE) do work with OpenOffic 4.1.5.
How to use my test sheets:
Hit the push button with the ellipsis (...) on sheet "SQL_INSERT" to open a file picker dialog. Point to the downloaded Test_HSQLDB.odb. You may also register the database document and enter the registered name in the "DB_Name" cell M1.
Then click any cell in the red bordered area and hit the other push button labeled "Call Macro". It will insert 4 records with every push on the button.
Alternatively, you may select the first couple of rows or a multiple selection of rows. The macro disregards hidden rows of filtered ranges. But the 2 column headers (type declarations and column names) should always be included.
You can filter the little test range and the macro will insert only from visible rows.
Select any cell in the top-left area of sheet "SQL_UPDATE" and push the button. This will update some records with the current time in the "T" field.
Select any cell on sheet "SQL_DELETE" and push the button. This will delete all records from table "tblTypes", however this does not work with OpenOffice 4.1.5 on my Ubuntu system and I don't understand the problem.
Additional note on named cells in LibreOffice and OpenOffice:
The cell named "DB_Name" gets either the path of a database document or its registration name. The name points to absolute cell address $SQL_INSERT.$M$1.
The cell named "SQL_Statement" gets the parameterized statement to be executed. The name of this cell points to address $M$2. This is an absolute cell address with no sheet name. This way the name applies to cell M2 on every sheet in the same document and I could test different SQL statements using the same cell name on separate sheets.
With LibreOffice you have a little bit more flexibility because LibreOffice lets you define the same name differently on different sheets. With LibreOffice "SQL_Statement" may refer to M2 on one sheet, some other cell on another sheet and may be undefined on a third sheet. For compatibility reasons I did it the OpenOffice way. The macro tries to get the named cell on the active sheet and if this method fails, it tries to find the cell in the global name space.
The other 2 named cells are obsolete when working with embedded HSQL which does not support user logins.