Macroの杜(Python編)
[ Python for LibreOffice( Apache OpenOffice ) ]

【 Base( データベース ) 】

**********************【 Index 】**********************

File

BF-)[Base]新規Base file起動
BF-)[Base]指定Base file起動
BF-)[Base]指定Base file保存(上書き保存)

DataSource

BDS-)[Base]登録DataSourceの有無Check[ .hasElements() ]
BDS-)[Base]登録されているDataSource数の取得[ .getElementNames() / for ]
BDS-)[Base]登録されているDataSource名の取得[ getElementNames() ]
BDS-)[Base][ ]
BDS-)[Base]

Connect with DataSource

BCD-)[Base]登録されたData SourceへのConnect[ com.sun.star.sdb.DatabaseContext ]
BCD-)[Base][ ]

Table

BTb-)[Base]Table Name一覧取得[ getTables() / createEnumeration() / hasMoreElements() / nextElement() / Name ]
BTb-)[Base]同名Tbaleの有無Check[ getTables() / hasByName ]
BTb-)[Base][ ]

[ RowSet Service ]

BTRw-)[Base]RowSet Serviceを用いたParent Name取得[ .ActiveConnection / .Parent / .Name ]
BTRw-)[Base]Current Row No取得[ .RowCount ]
BTRw-)[Base][ ]

SQL / Query

BSQL-)[Base]新規Tableの作成[ createStatement() / CREATE TABLE / execute() ]
BSQL-)[Base]新規TableへData入力[ INSERT INTO / VALUES ]
BSQL-)[Base]Tableの削除[ DROP TABLE ]
BSQL-)[Base][ ]

File

# -*- coding: utf-8 -*-

import uno

def load():
    desktop = XSCRIPTCONTEXT.getDesktop()
    doc =desktop.loadComponentFromURL( "private:factory/sdatabase","_blank", 0, () )

# -*- coding: utf-8 -*-

import uno
import unohelper
import os.path

def oMacroTest():
    try:
        ofile= os.path.abspath('c:\\temp\\oBaseMacro2.odb')
        oURL = unohelper.systemPathToFileUrl(ofile)
    #
        desktop = XSCRIPTCONTEXT.getDesktop()
        desktop.loadComponentFromURL( oURL,"_blank", 0, () )
    except:
        pass

# -*- coding: utf-8 -*-

import uno
import unohelper
import os.path

def oMacroTest():
    try:
        ofile= os.path.abspath('c:\\temp\\oBaseMacro2.odb')
        oURL = unohelper.systemPathToFileUrl(ofile)
    #
        desktop = XSCRIPTCONTEXT.getDesktop()
        oDoc = desktop.loadComponentFromURL( oURL,"_blank", 0, () )
    #
        p = PropertyValue()
        p.Name = 'Overwrite'
        p.Value = True
        properties = (p,)
        oDoc.storeAsURL( oURL, properties)
        oDoc.dispose()
	#
    except:
        pass

DataSource

#!
#coding: UTF-8
# python Marco
import uno
import sys
import traceback
from com.sun.star.awt import Rectangle
def omsgbox(oMessage='',oBtnType=1,oTitle='Title',oMsgType='messbox'):
#	"""shows message."""
		desktop = XSCRIPTCONTEXT.getDesktop()
		frame = desktop.getCurrentFrame()
		window = frame.getContainerWindow()
		toolkit = window.getToolkit()
		msgbox = toolkit.createMessageBox(window, Rectangle(), oMsgType, oBtnType, oTitle, oMessage)
		return msgbox.execute()
def createUnoService(service_name):
	"""Create a UNO Service By this name"""
	ctx = XSCRIPTCONTEXT.getComponentContext()
	try:
		service = ctx.ServiceManager.createInstanceWithContext(service_name, ctx)
	except:
		service = NONE
	return service
def oTest():
	try:
		context = createUnoService('com.sun.star.sdb.DatabaseContext')
		if not context.hasElements():
			oDisp = unicode('登録されているDataSourceはありません','utf-8')
		else:
			oDisp = unicode('登録されているDataSourceがあります','utf-8')
	except:
		oDisp = traceback.format_exc(sys.exc_info()[2])
	finally:
		omsgbox(oDisp,1)

#!
#coding: UTF-8
# python Marco
import uno
import sys
import traceback
from com.sun.star.awt import Rectangle
def omsgbox(oMessage='',oBtnType=1,oTitle='Title',oMsgType='messbox'):
#	"""shows message."""
		desktop = XSCRIPTCONTEXT.getDesktop()
		frame = desktop.getCurrentFrame()
		window = frame.getContainerWindow()
		toolkit = window.getToolkit()
		msgbox = toolkit.createMessageBox(window, Rectangle(), oMsgType, oBtnType, oTitle, oMessage)
		return msgbox.execute()
def createUnoService(service_name):
	"""Create a UNO Service By this name"""
	ctx = XSCRIPTCONTEXT.getComponentContext()
	try:
		service = ctx.ServiceManager.createInstanceWithContext(service_name, ctx)
	except:
		service = NONE
	return service
def oTest():
	try:
		context = createUnoService('com.sun.star.sdb.DatabaseContext')
		if not context.hasElements():
			oDisp = unicode('登録されているDataSourceはありません','utf-8')
			sys.exit()
		oRstDataSources = context.getElementNames() 
		oCount = 0
		for i in oRstDataSources:
			oCount = oCount + 1
		oDisp = unicode('登録されているDataSource \n => ','utf-8') + str(oCount)
	except:
		oDisp = traceback.format_exc(sys.exc_info()[2])
	finally:
		omsgbox(oDisp,1)

#!
#coding: UTF-8
# python Marco
import uno
import sys
import traceback
from com.sun.star.awt import Rectangle
def omsgbox(oMessage='',oBtnType=1,oTitle='Title',oMsgType='messbox'):
#	"""shows message."""
		desktop = XSCRIPTCONTEXT.getDesktop()
		frame = desktop.getCurrentFrame()
		window = frame.getContainerWindow()
		toolkit = window.getToolkit()
		msgbox = toolkit.createMessageBox(window, Rectangle(), oMsgType, oBtnType, oTitle, oMessage)
		return msgbox.execute()
def createUnoService(service_name):
	"""Create a UNO Service By this name"""
	ctx = XSCRIPTCONTEXT.getComponentContext()
	try:
		service = ctx.ServiceManager.createInstanceWithContext(service_name, ctx)
	except:
		service = NONE
	return service
def oTest():
	try:
		context = createUnoService('com.sun.star.sdb.DatabaseContext')
		if not context.hasElements():
			oDisp = unicode('登録されているDataSourceはありません','utf-8')
			sys.exit()
		oRstDataSources = context.getElementNames() 
		oDisp = unicode('[ 登録されているDataSource名 ]\n ','utf-8') + str(oRstDataSources)
	except:
		oDisp = traceback.format_exc(sys.exc_info()[2])
	finally:
		omsgbox(oDisp,1)
#
# ReturnはTuple

Connect with DataSource

#!
#coding: UTF-8
# python Marco
import uno
import sys
import traceback
from com.sun.star.awt import Rectangle
def omsgbox(oMessage='',oBtnType=1,oTitle='Title',oMsgType='messbox'):
#	"""shows message."""
		desktop = XSCRIPTCONTEXT.getDesktop()
		frame = desktop.getCurrentFrame()
		window = frame.getContainerWindow()
		toolkit = window.getToolkit()
		msgbox = toolkit.createMessageBox(window, Rectangle(), oMsgType, oBtnType, oTitle, oMessage)
		return msgbox.execute()
def createUnoService(service_name):
	"""Create a UNO Service By this name"""
	ctx = XSCRIPTCONTEXT.getComponentContext()
	try:
		service = ctx.ServiceManager.createInstanceWithContext(service_name, ctx)
	except:
		service = NONE
	return service
def oTest():
	try:
		context = createUnoService('com.sun.star.sdb.DatabaseContext')
		oRst_BaseFile = 'Test'		# Macroを実行するBase File
		db = context.getByName(oRst_BaseFile)
		oCon = db.getConnection('','')
		oCon.close()
		oDisp = 'Success'
	except:
		oDisp = traceback.format_exc(sys.exc_info()[2])
	finally:
		omsgbox(oDisp,1)

Table

#!
#coding: UTF-8
# python Marco
import uno
import sys
import traceback
from com.sun.star.awt import Rectangle
def omsgbox(oMessage='',oBtnType=1,oTitle='Title',oMsgType='messbox'):
#	"""shows message."""
		desktop = XSCRIPTCONTEXT.getDesktop()
		frame = desktop.getCurrentFrame()
		window = frame.getContainerWindow()
		toolkit = window.getToolkit()
		msgbox = toolkit.createMessageBox(window, Rectangle(), oMsgType, oBtnType, oTitle, oMessage)
		return msgbox.execute()
def createUnoService(service_name):
	"""Create a UNO Service By this name"""
	ctx = XSCRIPTCONTEXT.getComponentContext()
	try:
		service = ctx.ServiceManager.createInstanceWithContext(service_name, ctx)
	except:
		service = NONE
	return service
def oTest():
	try:
		context = createUnoService('com.sun.star.sdb.DatabaseContext')
		oRst_BaseFile = 'Test'		# Macroを実行するBase File
		db = context.getByName(oRst_BaseFile)
		oCon = db.getConnection('','')
		oDBTables = oCon.getTables().createEnumeration()
		oLmt = 1
		oDisp = ''
		while oDBTables.hasMoreElements() and oLmt < 500:
			oTable = oDBTables.nextElement()
			oTableName = oTable.Name
			oDisp = oDisp + str(oTableName) + '\n'
			oLmt = oLmt + 1
		oCon.close()
	except:
		oDisp = traceback.format_exc(sys.exc_info()[2])
	finally:
		omsgbox(oDisp,1)

#!
#coding: UTF-8
# python Marco
import uno
import sys
import traceback
from com.sun.star.awt import Rectangle
def omsgbox(oMessage='',oBtnType=1,oTitle='Title',oMsgType='messbox'):
#	"""shows message."""
		desktop = XSCRIPTCONTEXT.getDesktop()
		frame = desktop.getCurrentFrame()
		window = frame.getContainerWindow()
		toolkit = window.getToolkit()
		msgbox = toolkit.createMessageBox(window, Rectangle(), oMsgType, oBtnType, oTitle, oMessage)
		return msgbox.execute()
def createUnoService(service_name):
	"""Create a UNO Service By this name"""
	ctx = XSCRIPTCONTEXT.getComponentContext()
	try:
		service = ctx.ServiceManager.createInstanceWithContext(service_name, ctx)
	except:
		service = NONE
	return service
def oTest():
	try:
		context = createUnoService('com.sun.star.sdb.DatabaseContext')
		oRst_BaseFile = 'Test'		# Macroを実行するBase File
		db = context.getByName(oRst_BaseFile)
		oCon = db.getConnection('','')
		oDBTables = oCon.getTables()
		oTbName = 'OPYTHONTB'
		oCheck = oDBTables.hasByName(oTbName)
		oCon.close()
		oDisp = unicode('[ 同名Tbaleの有無Check ]\n ','utf-8')\
		+ oTbName + ' => ' + str(oCheck)
	except:
		oDisp = traceback.format_exc(sys.exc_info()[2])
	finally:
		omsgbox(oDisp,1)

[ RowSet Service ]

#!
#coding: UTF-8
# python Marco
import unohelper
import uno
import sys
import traceback
from com.sun.star.awt import Rectangle
def omsgbox(oMessage='',oBtnType=1,oTitle='Title',oMsgType='messbox'):
#	"""shows message."""
		desktop = XSCRIPTCONTEXT.getDesktop()
		frame = desktop.getCurrentFrame()
		window = frame.getContainerWindow()
		toolkit = window.getToolkit()
		msgbox = toolkit.createMessageBox(window, Rectangle(), oMsgType, oBtnType, oTitle, oMessage)
		return msgbox.execute()
def createUnoService(service_name):
	"""Create a UNO Service By this name"""
	ctx = XSCRIPTCONTEXT.getComponentContext()
	try:
		service = ctx.ServiceManager.createInstanceWithContext(service_name, ctx)
	except:
		service = NONE
	return service
def oTest():
	try:
		oDoc = XSCRIPTCONTEXT.getDocument()
		oDBSource = oDoc.getURL()
		#
		oRowSet = createUnoService('com.sun.star.sdb.RowSet')
		oTableName = 'CITY_LIST'
		#
		oRowSet.DataSourceName = oDBSource
		oRowSet.CommandType = 2
		oRowSet.Command = 'SELECT * FROM ' + oTableName
		oRowSet.execute()
		#
		oParentURL = oRowSet.ActiveConnection.Parent.Name
		oParentName = unohelper.fileUrlToSystemPath(oParentURL)
		#
		# Close Rowset
		oRowSet.close
		#
		oDisp= str(oParentName)
	except:
		oDisp = traceback.format_exc(sys.exc_info()[2])
	finally:
		omsgbox(oDisp,1)
#
# [ CommandType ]
# 0 : com.sun.star.sdb.CommandType.TABLE
# 1 : com.sun.star.sdb.CommandType.QUERY
# 2 : com.sun.star.sdb.CommandType.COMMAND

#!
#coding: UTF-8
# python Marco
import uno
import sys
import traceback
from com.sun.star.awt import Rectangle
def omsgbox(oMessage='',oBtnType=1,oTitle='Title',oMsgType='messbox'):
#	"""shows message."""
		desktop = XSCRIPTCONTEXT.getDesktop()
		frame = desktop.getCurrentFrame()
		window = frame.getContainerWindow()
		toolkit = window.getToolkit()
		msgbox = toolkit.createMessageBox(window, Rectangle(), oMsgType, oBtnType, oTitle, oMessage)
		return msgbox.execute()
def createUnoService(service_name):
	"""Create a UNO Service By this name"""
	ctx = XSCRIPTCONTEXT.getComponentContext()
	try:
		service = ctx.ServiceManager.createInstanceWithContext(service_name, ctx)
	except:
		service = NONE
	return service
def oTest():
	try:
		oDoc = XSCRIPTCONTEXT.getDocument()
		oDBSource = oDoc.getURL()
		# ResultSet
		dbContext = createUnoService('com.sun.star.sdb.DatabaseContext')
		oDataSource = dbContext.getByName(oDBSource)
		oCon = oDataSource.getConnection('','')
		# RowSet
		oRowSet = createUnoService('com.sun.star.sdb.RowSet')
		oTableName = 'ADDRESS'
		oSQL = 'SELECT * FROM ' + oTableName
		#
		oRowSet.ActiveConnection = oCon
		oRowSet.Command = oSQL
		oRowSet.execute()
		# Count Row
		dbTableRow = oRowSet.RowCount
		# Close Rowset
		oRowSet.close()
		#
		# Close ResultSet
		oCon.close()
		#
		oDisp= u'接続しているBase Table情報' + '\n' + u'Table名 　：　' + unicode(oTableName,'utf-8') + '\n' + u'Data数(行数)　：　' + str(dbTableRow)
	except:
		oDisp = traceback.format_exc(sys.exc_info()[2])
	finally:
		omsgbox(oDisp,1)

SQL / Query

#!
#coding: UTF-8
# python Marco
import uno
import sys
import traceback
from com.sun.star.awt import Rectangle
def omsgbox(oMessage='',oBtnType=1,oTitle='Title',oMsgType='messbox'):
#	"""shows message."""
		desktop = XSCRIPTCONTEXT.getDesktop()
		frame = desktop.getCurrentFrame()
		window = frame.getContainerWindow()
		toolkit = window.getToolkit()
		msgbox = toolkit.createMessageBox(window, Rectangle(), oMsgType, oBtnType, oTitle, oMessage)
		return msgbox.execute()
def createUnoService(service_name):
	"""Create a UNO Service By this name"""
	ctx = XSCRIPTCONTEXT.getComponentContext()
	try:
		service = ctx.ServiceManager.createInstanceWithContext(service_name, ctx)
	except:
		service = NONE
	return service
def oTest():
	try:
		context = createUnoService('com.sun.star.sdb.DatabaseContext')
		oRst_BaseFile = 'Test'		# Macroを実行するBase File
		db = context.getByName(oRst_BaseFile)
		oCon = db.getConnection('','')
		oStm = oCon.createStatement()
		oTableName = 'oPythonTb'
		oSQL = 'CREATE TABLE ' + oTableName + '(ID INTEGER IDENTITY, NAME VARCHAR(32), ADDRESS VARCHAR(100), PRIMARY KEY(''ID''));'
		oStm.execute(oSQL)
		oCon.close()
		oDisp = 'Success'
	except:
		oDisp = traceback.format_exc(sys.exc_info()[2])
	finally:
		omsgbox(oDisp,1)

#!
#coding: UTF-8
# python Marco
import uno
import sys
import traceback
from com.sun.star.awt import Rectangle
def omsgbox(oMessage='',oBtnType=1,oTitle='Title',oMsgType='messbox'):
#	"""shows message."""
		desktop = XSCRIPTCONTEXT.getDesktop()
		frame = desktop.getCurrentFrame()
		window = frame.getContainerWindow()
		toolkit = window.getToolkit()
		msgbox = toolkit.createMessageBox(window, Rectangle(), oMsgType, oBtnType, oTitle, oMessage)
		return msgbox.execute()
def createUnoService(service_name):
	"""Create a UNO Service By this name"""
	ctx = XSCRIPTCONTEXT.getComponentContext()
	try:
		service = ctx.ServiceManager.createInstanceWithContext(service_name, ctx)
	except:
		service = NONE
	return service
def oTest():
	try:
		context = createUnoService('com.sun.star.sdb.DatabaseContext')
		oRst_BaseFile = 'Test'		# Macroを実行するBase File
		db = context.getByName(oRst_BaseFile)
		oCon = db.getConnection('','')
		oStm = oCon.createStatement()
		#
		# CREATE TABLE句
		oTableName = 'oPythonTb'
		oSQL1 = 'CREATE TABLE ' + oTableName + '(ID INTEGER IDENTITY, NAME VARCHAR(32), TEL VARCHAR(15), PRIMARY KEY(''ID''));'
		oStm.execute(oSQL1)
		#
		# INSERT句
		for i in range(10,21):
			oVal = "VALUES(" + str(i) + "," + "'new_OOo3-" + str(i) + "','090-1234-56" + str(i) + "');"
			oSQL2 = "INSERT INTO " + oTableName + "(ID,NAME,TEL) " + oVal
			oStm.executeUpdate(oSQL2)
			#
		oCon.close()
		oDisp = 'Success'
	except:
		oDisp = traceback.format_exc(sys.exc_info()[2])
	finally:
		omsgbox(oDisp,1)

#!
#coding: UTF-8
# python Marco
import uno
import sys
import traceback
from com.sun.star.awt import Rectangle
def omsgbox(oMessage='',oBtnType=1,oTitle='Title',oMsgType='messbox'):
#	"""shows message."""
		desktop = XSCRIPTCONTEXT.getDesktop()
		frame = desktop.getCurrentFrame()
		window = frame.getContainerWindow()
		toolkit = window.getToolkit()
		msgbox = toolkit.createMessageBox(window, Rectangle(), oMsgType, oBtnType, oTitle, oMessage)
		return msgbox.execute()
def createUnoService(service_name):
	"""Create a UNO Service By this name"""
	ctx = XSCRIPTCONTEXT.getComponentContext()
	try:
		service = ctx.ServiceManager.createInstanceWithContext(service_name, ctx)
	except:
		service = NONE
	return service
def oDropTb(cStm,cTName):
	if cStm != None and cTName != None:
		cSQL = 'DROP TABLE ' + cTName + ';'
		cStm.execute(cSQL)
def oTest():
	try:
		context = createUnoService('com.sun.star.sdb.DatabaseContext')
		oRst_BaseFile = 'Test'		# Macroを実行するBase File
		db = context.getByName(oRst_BaseFile)
		oCon = db.getConnection('','')
		oDBTables = oCon.getTables()
		oTbName = 'OPYTHONTB'
		if oDBTables.hasByName(oTbName):
			oStm = oCon.createStatement()
			oDropTb(oStm,oTbName)
		oCon.close()
		oDisp = 'Success'
	except:
		oDisp = traceback.format_exc(sys.exc_info()[2])
	finally:
		omsgbox(oDisp,1)