Import data from db to calc - translate basic to python

Hello

I would appreciate any help to translate this to python:


sub DoStuff()
	oDBRange = thisComponent.DataBaseRanges.getByName("something")
	oDesc() = oDBRange.getImportDescriptor()
	For i = 0 to ubound(oDesc())
		oPrp = oDesc(i)
		If oPrp.Name = "DatabaseName" then
			oPrp.Value = "checking_inserts"
		elseIf oPrp.Name = "SourceType" then
			oPrp.Value = com.sun.star.sheet.DataImportMode.SQL
		elseIf oPrp.Name = "SourceObject" then
			oPrp.Value = "SELECT * FROM WELLBORES"
		elseif oPrp.Name = "IsNative" then
			oPrp.Value = True
		Endif
		oDesc(i) = oPrp
	Next
	oDBRange.getReferredCells.doImport(oDesc())
end sub
def DoStuff():
	this= XSCRIPTCONTEXT.getDocument()
	print(this)
	oDBRange = this.DataBaseRanges.getByName("something")
from com.sun.star.sheet.DataImportMode import SQL

def db_import(*_):
    doc = XSCRIPTCONTEXT.getDocument()
    db_range = doc.DataBaseRanges["something"]
    description = db_range.getImportDescriptor()
    new = []
    for prop in description:
        if prop.Name == "DatabaseName":
            prop.Value = "checking_inserts"
        elif prop.Name == "SourceType":
            prop.Value = SQL
        elif prop.Name == "SourceObject":
            prop.Value = "SELECT * FROM WELLBORES"
        elif prop.Name == "IsNative":
            prop.Value = True
        new.append(prop)
        
    db_range.getReferredCells.doImport(tuple(new))

no Warranty!!

1 Like

Typo, but as you wrote: no warranty.

2 Likes

Here is an updated:

ef DoStuff():
	doc = XSCRIPTCONTEXT.getDocument()
	db_range = doc.DatabaseRanges["something"]
	description = db_range.getImportDescriptor()
	new = []
	for prop in description:
		if prop.Name == "DatabaseName":
			prop.Value = "checking_inserts"
		elif prop.Name == "SoureType":
			prop.Value = SQL
		elif prop.Name == "SourceObject":
			prop.Value = "SELECT * FROM WELLBORES"
		elif prop.Name == "IsNative":
			prop.Value = True
		new.append(prop)
	db_range.getReferredCells.doImport(tuple(new))

I get the followring:

Error during invoking function DoStuff in module vnd.sun.star.tdoc:/1/Scripts/python/Module.py (<class 'AttributeError'>: 'PyUNO_callable' object has no attribute 'doImport'
  File "D:\Program Files\LibreOffice\App\libreoffice\program\pythonscript.py", line 915, in invoke
    ret = self.func( *args )
  File "vnd.sun.star.tdoc:/1/Scripts/python/Module.py", line 55, in DoStuff
)

Here is my version:

import uno
from com.sun.star.sheet.DataImportMode import SQL
def DoStuff1():
	model = XSCRIPTCONTEXT.getDesktop().getCurrentComponent()	
	oDBRange = model.DatabaseRanges.getByName("something")
	oDesc= oDBRange.getImportDescriptor()
	for oPrp in oDesc:
		if oPrp.Name == "DatabaseName":
			oPrp.Value = "checking_inserts"
		if oPrp.Name == "SourceType":
			oPrp.Value = SQL
		if oPrp.Name == "SourceObject":
			oPrp.Value = "SELECT * FROM WELLBORES"
		if oPrp.Name == "IsNative":
			oPrp.Value = True
	oDBRange.getReferredCells.doImport(oDesc)

And this is my error, which is the same as before:

Error during invoking function DoStuff1 in module vnd.sun.star.tdoc:/1/Scripts/python/Module.py (<class 'AttributeError'>: 'PyUNO_callable' object has no attribute 'doImport'
  File "D:\Program Files\LibreOffice\App\libreoffice\program\pythonscript.py", line 915, in invoke
    ret = self.func( *args )
  File "vnd.sun.star.tdoc:/1/Scripts/python/Module.py", line 37, in DoStuff1
)

python is casesensitive, maybe you should try with ……doimport
and please double-check my Typo SoureType→→ SourceType

@Wanderer: thanks!!

Hello,

I used doimport, all lowercase and still get the error:

Error during invoking function DoStuff1 in module vnd.sun.star.tdoc:/1/Scripts/python/Module.py (<class 'AttributeError'>: 'PyUNO_callable' object has no attribute 'doimport' File "D:\Program Files\LibreOffice\App\libreoffice\program\pythonscript.py", line 915, in invoke ret = self.func( *args ) File "vnd.sun.star.tdoc:/1/Scripts/python/Module.py", line 37, in DoStuff1 )

    oDBRange.ReferredCells.doImport(oDesc) # Attribut-access
    #or:
    oDBRange.getReferredCells().doImport(oDesc) # Method-accress needs to called()
1 Like

and the two winners are:

def DoStuff1():
	model = XSCRIPTCONTEXT.getDesktop().getCurrentComponent()	
	oDBRange = model.DatabaseRanges.getByName("something")
	oDesc= oDBRange.getImportDescriptor()
	for oPrp in oDesc:
		if oPrp.Name == "DatabaseName":
			oPrp.Value = "checking_inserts"
		if oPrp.Name == "SourceType":
			oPrp.Value = SQL
		if oPrp.Name == "SourceObject":
			oPrp.Value = "SELECT * FROM WELLBORES"
		if oPrp.Name == "IsNative":
			oPrp.Value = True
	oDBRange.getReferredCells().doImport(oDesc)			

and:

def DoStuff():
	doc = XSCRIPTCONTEXT.getDocument()
	db_range = doc.DatabaseRanges["something"]
	description = db_range.getImportDescriptor()
	new = []
	for prop in description:
		if prop.Name == "DatabaseName":
			prop.Value = "checking_inserts"
		elif prop.Name == "SourceType":
			prop.Value = SQL
		elif prop.Name == "SourceObject":
			prop.Value = "SELECT * FROM WELLBORES"
		elif prop.Name == "IsNative":
			prop.Value = True
		new.append(prop)
	db_range.ReferredCells.doImport(tuple(new)) # Attribut-access

Thank you all for your help.

please do a favour to all “pythonistas” give it (the function) a sensefull Name, lowercase maybe with_underscore… why not for Example do_import ?

1 Like

As you so wish:

def importFromDb():
	model = XSCRIPTCONTEXT.getDesktop().getCurrentComponent()	
	oDBRange = model.DatabaseRanges.getByName("something")
	oDesc= oDBRange.getImportDescriptor()
	for oPrp in oDesc:
		if oPrp.Name == "DatabaseName":
			oPrp.Value = "checking_inserts"
		if oPrp.Name == "SourceType":
			oPrp.Value = SQL
		if oPrp.Name == "SourceObject":
			oPrp.Value = "SELECT * FROM WELLBORES"
		if oPrp.Name == "IsNative":
			oPrp.Value = True
	oDBRange.getReferredCells().doImport(oDesc)			

and:

def import_from_db():
	doc = XSCRIPTCONTEXT.getDocument()
	db_range = doc.DatabaseRanges["something"]
	description = db_range.getImportDescriptor()
	new = []
	for prop in description:
		if prop.Name == "DatabaseName":
			prop.Value = "checking_inserts"
		elif prop.Name == "SourceType":
			prop.Value = SQL
		elif prop.Name == "SourceObject":
			prop.Value = "SELECT * FROM WELLBORES"
		elif prop.Name == "IsNative":
			prop.Value = True
		new.append(prop)
	db_range.ReferredCells.doImport(tuple(new)) 
1 Like

the Last would be perfect with lowercase f & d

:blush:

1 Like

lowercase f & d at your service

Its not just me, its the style-guide for python

1 Like