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