Home of site


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

【 Base( データベース ) 】

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

File

DataSource

Connect with DataSource

Table

[ RowSet Service ]

SQL / Query

**********[ Macro Code ]**********


File

BF-)[Base]新規Base file起動

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

import uno

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

BF-)[Base]指定Base file起動

# -*- 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

BF-)[Base]指定Base file保存(上書き保存)

# -*- 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

BF-)[Base]



DataSource

BDS-)[Base]登録DataSourceの有無Check


#!
#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)

BDS-)[Base]登録されている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')
			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)

BDS-)[Base]登録されている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')
			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

BDS-)[Base]




BDS-)[Base]











Connect with DataSource

BCD-)[Base]登録されたData SourceへのConnect


#!
#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)

BCD-)[Base]











Table

BTb-)[Base]Table Name一覧取得


#!
#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)

BTb-)[Base]同名Tbaleの有無Check


#!
#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)

BTb-)[Base]











[ RowSet Service ]

BTRw-)[Base]RowSet Serviceを用いたParent Name取得


#!
#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

BTRw-)[Base]Current Row No取得


#!
#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)

BTRw-)[Base]











SQL / Query

BSQL-)[Base]新規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('','')
		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)

BSQL-)[Base]新規TableへData入力


#!
#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)

BSQL-)[Base]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 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)

BSQL-)[Base]





Top of Page

inserted by FC2 system