Copying data from third party db to calc

Good Morning,

This is a follow up to my previous question - Import data from db to calc - translate basic to python - English - Ask LibreOffice. Now using the same method (whether in Basic or Python), is it possible to copy from the a third party DB to Calc.

I have done this before, using XResultSet, while loop, and cycling through each field and row. I would like to avoid that. The method in the link above is a huge time saver and if there is a way to do this by connecting directly to the database (no need to make a datasource) that would be greatly appreciated.

So what i mean is this. With my original code:

sub DoStuff()
	oDBRange = thisComponent.DataBaseRanges.getByName("something")
	oDesc() = oDBRange.getImportDescriptor()
	For i = 0 to ubound(oDesc())
		oPrp = oDesc(i)
REM - WOULD LIKE TO CONNECT TO THIRD PARTY DATABASE DIRECTLY
REM - I WOULD LIKE TO AVOID GOING THROUGH A BASE INSTANCE THAT IS 
REM - CONNECTING TO THAT THIRD PARTY DATABASE USING THIS 
REM - METHODOLOGY AS DESCRIBED IN THE ABOVE-MENTIONED LINK
		Endif
		oDesc(i) = oPrp
	Next
	oDBRange.getReferredCells.doImport(oDesc())
end sub

May I suggest you to have a look at what the ScriptForge libraries have to propose in these matters:

None of them require a Base instance to be open.
Both are available in Basic and Python in the same way.

What 3rd-party DB are we talking about?

As mentioned by @JPLED you can use the DataBase ScriptForge service to connect to a database either via its ODB file or using its registration name. Then you can run GetRows or RunSql methods to extract data as a Array (to be processed in Python as a tuple of tuples).

If you simply wish to dump the DB data into a Calc sheet, use the ImportFromDatabase method from the Calc service. The code would look something like:

myDoc = CreateScriptService("Calc")
myDoc.ImportFromDatabase(r"C:\Temp\myDbFile.odb", , "SheetY.C5", "SELECT * FROM [Employees] ORDER BY [LastName]")

The code above will place the result of the SQL query in cell SheetY.C5 of the current Calc document.

disclaimer: I’m a complete newbie in DataBases, but for now I has done the sqlite3 tuturial and leave with the last lines:

    #………
    res = cur.execute("SELECT year, title, score FROM movie ORDER BY score DESC")
    data = res.fetchall()
    calc_cursor.collapseToSize(len(data), len(data[0])) #count of rows , count of columns!!
    
    calc_cursor.DataArray = data

But not using a Base file was part of the original request:

While you can do this in a macro (have not seen in ScriptForge - Another reason to run away from it), that process does not appear to be valid to fit into the method state in the question. That method requires a Base file - registered I believe.

While you can do this in a macro

Can you please develop the statement that you don’t need a Base file or provide pointers ? Thanks.

Have posted a few already. Here are a couple of links:

Also with python you can install the appropriate connector such as:

1 Like

Thanks for this great and detailed info, Ratslinger.

However, the original question is about how to use

doImport

which is a “huge time saver” vs. a loop row by row/field by field and does not require to load the full data in memory first, without a Base file ?
This seems not to be possible.

IMO the necessity to have a Base file somewhere (it does NOT need to be registered: put simply a file:// URL in the DatabaseName parameter …) is not a high price to pay.

BTW do not run too fast away from ScriptForge, it uses doImport internally.

Just to remember you: The question was not about: “how to use SF…” , it was “how to use python …”

1 Like

The point is that is not what the OP requested. Also, if the DB are various, this may become a hassle.
As for ScriptForge - I’ve stated, my opinion, multiple times it is a waste of time. Learn the API instead. Too much wrong - documentation is poor, have seen code not working, and not everything is there so you still need to go to the API. Waste of time.
.
Edit:
.
Almost forgot an important item of information. If you have this necessary Base file, what is the purpose of changing ANY of the existing code? Especially adding in unnecessary ScriptForge code. It works as is already if a Base file is used.

1 Like

@ztminhas: to rephrase your question, is it basically: “process DB-related stuff in native python and dump it into Calc?” …is it?
so IHMO if you already not exactly a experienced python-coder you should start with the python tutorial in general
If you need to introduce yourself for the concepts of DataBase-handling you may start with the tutorial about the python builtin-module sqlite3

back to your code-example: at the end of the day you may have something like:
edit: complete working example…of course a db need to be create before we can do querys:

def create_sqlite3_db(*_):
    con = sqlite3.connect("tutorial.db")
    cur = con.cursor()
    cur.execute("CREATE TABLE movie(title, year, score)")
    cur.execute("""
        INSERT INTO movie VALUES
            ('Monty Python and the Holy Grail', 1975, 8.2),
            ('And Now for Something Completely Different', 1971, 7.5)
            """)
    con.commit()

    data = [
        ("Monty Python Live at the Hollywood Bowl", 1982, 7.9),
        ("Monty Python's The Meaning of Life", 1983, 7.5),
        ("Monty Python's Life of Brian", 1979, 8.0),
    ]
    cur.executemany("INSERT INTO movie VALUES(?, ?, ?)", data)
    con.commit()  # Remember to commit the transaction after executing INSERT.

    con.close()
## above only for creating!!
def db_import(*_):    
    con = sqlite3.connect("tutorial.db")
    cursor = con.cursor()
    result = cursor.execute("SELECT year,title,score FROM movie ORDER BY score DESC")
    data = result.fetchall()
    con.close()
    
    doc = XSCRIPTCONTEXT.getDocument()
    sheet = doc.Sheets["Tabelle1"]
    target = sheet["A1"]
    calc_cursor = sheet.createCursorByRange(target)
    calc_cursor.collapseToSize(len(data[0]), len(data))     
    calc_cursor.DataArray = data  

fortunatly that all does not require any SF-service!

1 Like

Okay. Thus is fine. I have done this kind if code before. You will see a post where I was trying to dump something like 90k rows of data from Oracle to Calc in python. I thought there were some native libraries which would be similar to my previous post.

There is a more efficient method (Oracle or PostgreSQL).

Sub TestImport
 Dim oDoc, oRange
 Dim props(2) As New com.sun.star.beans.PropertyValue
 oDoc = StarDesktop.LoadComponentFromUrl("private:factory/scalc", "_blank", 0, Array())
 props(0).Name="DatabaseName" : props(0).Value="YourDataBase"
 props(1).Name="SourceType"   : props(1).Value=1
 props(2).Name="SourceObject" : props(2).Value="Select * from tmp_site"
 oRange=oDoc.Sheets(0).getCellRangeByName("A1")
 oRange.doImport props
End Sub

or dBase, Spreadsheet, csv, MySQL, SQLite, … anything connectable. However, people hate Base documents.

At the same time, there is no problem creating a temporary DocumentDataSource object on the fly and fully working with it.