We will be migrating from Ask to Discourse on the first week of August, read the details here

Ask Your Question
0

How can I execute SQL in a Python script?

asked 2020-04-30 17:04:03 +0200

madtom1999 gravatar image

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

edit retag flag offensive close merge delete

Comments

What DB you want use?

mauricio gravatar imagemauricio ( 2020-04-30 18:43:16 +0200 )edit

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.

madtom1999 gravatar imagemadtom1999 ( 2020-04-30 19:44:59 +0200 )edit

1 Answer

Sort by » oldest newest most voted
1

answered 2020-04-30 20:40:01 +0200

Jim K gravatar image

updated 2020-05-01 21:06:44 +0200

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))')
edit flag offensive delete link more

Comments

1

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 gravatar imagemadtom1999 ( 2020-04-30 20:54:04 +0200 )edit

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

madtom1999 gravatar imagemadtom1999 ( 2020-04-30 20:58:03 +0200 )edit

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

Ratslinger gravatar imageRatslinger ( 2020-04-30 21:32:26 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2020-04-30 17:04:03 +0200

Seen: 149 times

Last updated: May 01 '20