How can I execute SQL in a Python script?

[madtom1999]

I wish to create SQL from DB info and use that to create tables and dialogs.

[mauricio]

What DB you want use?

[madtom1999]

Whatever I’m connected to. Initially the Base default HSQLDB but I would hope the code would work through DB connections so it shouldnt matter for most things.

[jimk]

Use APSO to create and run the following macro. To see the table in Base, go to View → Refresh Tables.

def createDbTable():
    oDoc = XSCRIPTCONTEXT.getDocument()
    db = oDoc.DataSource
    conn = db.getConnection("","")  #username & password pair
    stmt = conn.createStatement()
    strSQL = (
        "CREATE TABLE Persons ("
        "PersonID int, LastName varchar(255), FirstName varchar(255),"
        "Address varchar(255), City varchar(255))")
    stmt.execute(strSQL)
    conn.close()

The SQL example is from w3schools.

EDIT:

For both Firebird Embedded and HSQLDB, put names with lowercase letters in double-quotes.

strSQL = (
    'CREATE TABLE "Persons" ('
    '"PersonID" int, "LastName" varchar(255), "FirstName" varchar(255),'
    '"Address" varchar(255), "City" varchar(255))')

[madtom1999]

Brilliant - thanks very much for that! The table and column names are all upper case in Base - more a cosmetic problem but is there a way to make them as per the SQL?

[madtom1999]

Seems I’ve not got enough Karma to upvote you yet!

[Ratslinger]

@madtom1999,

Upvoting is nice but it is also important to note if an answer was accepted. Don’t need karma for this (you actually get karma for doing this).

As you have been helped, please help others to know the question has been answered by clicking on the in upper left area of answer which satisfied the question.

[ztminhas]

Hello, I am very late to the discussion, but this response really solved some issues I was having connecting to PgSQL through base as the interface, and using Python as the language. Thank you.