Base, Calc, Python: INSERT, UPDATE, DELETE from spreadsheet

Creating Extension - Shared Libraries
Forum rules
For sharing working examples of macros / scripts. These can be in any script language supported by OpenOffice.org [Basic, Python, Netbean] or as source code files in Java or C# even - but requires the actual source code listing. This forum is not for asking questions about writing your own macros.

Base, Calc, Python: INSERT, UPDATE, DELETE from spreadsheet

Postby Villeroy » 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.
Attachments
ParamsFromSheet.py.odt
Text document with Python code and trivial Basic installer.
(26.96 KiB) Downloaded 18 times
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, OpenOffice 4.x & LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 25839
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Base, Calc, Python: INSERT, UPDATE, DELETE from spreadsh

Postby Villeroy » Fri Oct 19, 2018 7:13 pm

My test documents. A spreadsheet source and database target (embedded HSQL).
Your own setup requires a spreadsheet with 2 named cells, a data range with 2 header rows and a database document connected to some kind of updateable database (HSQL, MySQL, PostgreSQL, Access or whatever). The statement in named cell "SQL_Statement" needs to comply with the syntax of your database engine. Your database engine needs to support positional parameters.
Attachments
Test_HSQLDB.odb
Target database with one table "tblTypes" having an auto-ID column and various fields of different data types.
(8 KiB) Downloaded 18 times
Calc2HSQLDB.ods
Spreadsheet document with documentation and 3 example sheets to INSERT, UPDATE and DELETE.
(27.69 KiB) Downloaded 19 times
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, OpenOffice 4.x & LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 25839
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Base, Calc, Python: INSERT, UPDATE, DELETE from spreadsh

Postby Villeroy » Tue Oct 23, 2018 1:41 pm

I managed to use this code for a csv import system.
I connected a registered Base document to csv files in my downloads folder. A prepared spreadsheet has the required named cells to connect with the target database and an Import range below the row of type declarations. The type declarations and named cells are locked.

I drag the csv file to import from the data source browser onto the sheet, click a single cell, call the macro and the import is well done with all required type conversions and concatenations and without duplicates.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, OpenOffice 4.x & LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 25839
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany


Return to Code Snippets

Who is online

Users browsing this forum: No registered users and 1 guest