Using Python as a database (in-memory and on disk) package

Post your FreeBASIC tips and tricks here. Please don’t post your code without including an explanation.
AGS
Posts: 1284
Joined: Sep 25, 2007 0:26
Location: the Netherlands

Using Python as a database (in-memory and on disk) package

Postby AGS » Feb 03, 2014 17:43

Python (=an interpreted language) comes with some handy container data structures
that can be written to disk and read back again later. This allows for the creation of an in-memory
database that can be saved to disk (and read back from disk to memory).

Python comes with a C interface that a FreeBASIC programmer can use to create, read and write
container type data structures.

Avaiable container types are:
--> list
--> tuple
--> set
--> dictionary

A container can contain other containers or strings or numbers
(either integer or floating point) or etc....
Containers and non - containers are all of the same type: any ptr.
Or rather PyObject ptr. To make it easier to work with PyObjects
the example defines a PyObject as an opaque type (any).

Everything you put in a Python container has to be of type PyObject ptr.
Meaning that every piece of data you want to put in a container has to be
wrapped into a PyObject. This wrapping is done by functions like PyLong_FromLong
(see example).

To read more on the subject of Python objects go to
http://docs.python.org/2/c-api/concrete.html

More on the subject of using Python as a library
http://docs.python.org/2/c-api/intro.ht ... ing-python

Be aware that the documentation assumes the reader is programming in C.
The content of the documentation is, however, applicable to FreeBASIC code(rs)
as well.

Windows users: you need a couple of dynamic link libraries to compile/run the demo.
I have put a package on my website that contains the necessary libraries.
You can find the package at
http://www.sarmardar.nl/FreeBASIC/python_demo.zip
The package contains the following libraries:
--> python27.dll
--> msvcr90.dll

If you want to download Python yourself go to http://www.python.org/download/
and download/install the appropriate package (python-2.7.6.msi).

A full python installation can take up as much as 85+ MB (python27.dll is only
a couple of MB). msvcr90.dll is not part of the python installer.
According to microsoft (and me) msvcr90.dll can be found in a subdirectory of
C:\Windows\winsxs\
The name of the subdirectory where you can find msvcr90.dll starts with
x86_microsoft.vc90.crt_
There can and most likely will be multiple subdirectories that have a name that
starts with x86_microsoft.vc90.crt_. Just pick one (the most recent one would
be a good idea?).

Linux users: Python is part of most linux distributions/package repositories.

Code: Select all

#include "crt/stdio.bi"

namespace import_python
  ''keep PyObject opaque
  type PyObject as any
  extern "C"
    dim PyMarshal_WriteObjectToString as function(byval value as PyObject ptr, byval version as integer) as PyObject ptr
    dim PyMarshal_ReadObjectFromString as function(byval content as zstring ptr, byval size as integer) as PyObject ptr
    dim PyMarshal_ReadObjectFromFile as function(byval f as FILE ptr) as PyObject ptr
    dim PyDict_New as function() as PyObject ptr
    dim PyDict_SetItemString as function (byval dp as PyObject ptr, byval key as zstring ptr, _
                                          byval item as PyObject ptr) as integer
    dim PyDict_GetItemString as function(byval dp as PyObject ptr,_
                                         byval key as zstring ptr) as PyObject ptr
    dim PyDict_Clear as sub (byval mp as PyObject ptr)
    dim PyLong_FromLong as function (byval l as long) as PyObject ptr
    dim PyLong_AsLong as function(byval obj as PyObject ptr) as long
    dim PyString_AsString as function(byval obj as PyObject ptr) as zstring ptr
    dim PyString_Size as function(byval obj as PyObject ptr) as integer
    dim PyString_AsStringAndSize as function(byval obj as PyObject ptr,_
                                             byval buffer as ubyte ptr ptr, _
                                             byval length as integer ptr) as integer
    dim Py_DecRef as sub (byval obj as PyObject ptr) 
    dim Py_Initialize as sub() 
    dim Py_Finalize as sub()
  end extern
end namespace

function python_database(byref filename as string) as integer

  ''access to function/sub prototypes
  using import_python
  if (filename = "") then
    return -1
  end if
  var lh = dylibload("python27.dll")
  if (lh = 0) then
    return -1
  end if
  PyMarshal_ReadObjectFromFile = dylibsymbol(lh,"PyMarshal_ReadObjectFromFile")
  PyDict_New  = dylibsymbol(lh,"PyDict_New")
  PyDict_SetItemString = dylibsymbol(lh,"PyDict_SetItemString")
  PyDict_GetItemString = dylibsymbol(lh,"PyDict_GetItemString")
  PyLong_FromLong= dylibsymbol(lh,"PyLong_FromLong")
  PyLong_AsLong=dylibsymbol(lh,"PyLong_AsLong")
  Py_DecRef=dylibsymbol(lh,"Py_DecRef")
  PyDict_Clear=dylibsymbol(lh,"PyDict_Clear")
  PyMarshal_WriteObjectToString=dylibsymbol(lh,"PyMarshal_WriteObjectToString")
  PyString_AsString=dylibsymbol(lh,"PyString_AsString")
  PyString_Size=dylibsymbol(lh,"PyString_Size")
  PyString_AsStringAndSize=dylibsymbol(lh,"PyString_AsStringAndSize")
  PyMarshal_ReadObjectFromString=dylibsymbol(lh,"PyMarshal_ReadObjectFromString")
  Py_Initialize=dylibsymbol(lh,"Py_Initialize")
  Py_Finalize=dylibsymbol(lh,"Py_Finalize")

  dim table(0 to 100) as zstring ptr = {_
@"ABS",@"ABSTRACT",@"ACCESS",@"ACOS",@"ALIAS",@"AND",@"ANDALSO",@"ANY",@"APPEND",_
@"AS",@"ASC",@"ASIN",@"ASM",@"ATAN2",@"ATN",@"BASE",@"BINARY",@"BYREF",@"BYTE",_
@"BYVAL",@"CALL",@"CASE",@"CAST",@"CBYTE",@"CDBL",@"CDECL",@"CHR",@"CINT",@"CIRCLE",_
@"CLASS",@"CLNG",@"CLNGINT",@"CLOSE",@"COLOR",@"COMMON",@"CONST",@"CONSTRUCTOR",@"CONTINUE",_
@"COS",@"CPTR",@"CSHORT",@"CSIGN",@"CSNG",@"CUBYTE",@"CUINT",@"CULNG",@"CULNGINT",@"CUNSG",_
@"CUSHORT",@"CVD",@"CVI",@"CVL",@"CVLONGINT",@"CVS",@"CVSHORT",@"DATA",@"DECLARE",@"DEFBYTE",_
@"DEFDBL",@"DEFINED",@"DEFINT",@"DEFLNG",@"DEFLONGINT",@"DEFSHORT",@"DEFSNG",@"DEFSTR",@"DEFUBYTE",_
@"DEFUINT",@"DEFULNG",@"DEFULONGINT",@"DEFUSHORT",@"DELETE",@"DESTRUCTOR",@"DIM",@"DO",@"DOUBLE",_
@"DRAW",@"DYNAMIC",@"ELSE",@"ELSEIF",@"ENCODING",@"END",@"ENDIF",@"ENUM",@"EQV",@"ERASE",_
@"ERR",@"ERROR",@"EXIT",@"EXP",@"EXPLICIT",@"EXPORT",@"EXTENDS",@"EXTERN",@"FIELD",@"FIX",_
@"FOR",@"FRAC",@"FUNCTION",@"GET",@"GOSUB" }

  Py_Initialize()
  var obj = PyDict_New()
  ''add keywords to dictionary
  for i as integer = 0 to 100
    PyDict_SetItemString(obj,table(i),PyLong_FromLong(cast(long,i)))
  next i
  Print "Content after adding strings:"
  for i as integer = 0 to 100
    var obj1 = PyDict_GetItemString(obj,table(i))
    var mylong = PyLong_AsLong(obj1)
    print "(";*table(i);"),(";mylong;")"
  next i
  print "--------------------------------"
  var obj1 = PyMarshal_WriteObjectToString(obj,0)
  dim content as ubyte ptr
  dim string_size as integer = PyString_Size(obj1)
  if string_size > 20 then
    content = PyString_AsString(obj1)
    if content = 0 then
      print "Output of PyMarshal_WriteObjectToString is not a valid string"
      Py_Finalize()
      DyLibFree(lh)
      return -1
    end if
  else
    print "Output of PyMarshal_WriteObjectToString is not a valid string"
    Py_Finalize()
    DyLibFree(lh)   
    return -1
  end if
  var fh = freefile()
  open filename for binary access write as #fh
  var err_ = err()
  if err_ then
    print "Could not open ";filename;" for writing"
    deallocate(content)
    Py_Finalize()
    DyLibFree(lh)
    return -1   
  end if
  put #fh,,*content,string_size
  close #fh
  PyDict_Clear(obj)
  Py_Decref(obj)
  fh = freefile()
  content = callocate(string_size,sizeof(ubyte))
  open filename for binary access read as #fh
  err_ = err()
  if err_ then
    print "Could not open file ";filename;" for reading"
    deallocate(content)
    Py_Finalize()
    DyLibFree(lh)
    return -1   
  end if
  get #fh,,*content,string_size
  close #fh
  obj = 0
  obj = PyMarshal_ReadObjectFromString(content, string_size)
  if (obj = 0) then
    print "Could not read object from string"
    deallocate(content)
    Py_Finalize()
    DyLibFree(lh)
    return -1   
  end if
  deallocate(content)
  content = 0
  Print "Content after saving to disk and reading back from disk:"
  for i as integer = 0 to 100
    var obj1 = PyDict_GetItemString(obj,table(i))
    var mylong = PyLong_AsLong(obj1)
    print "(";*table(i);"),(";mylong;")"
  next i
  print "--------------------------------"
  Py_Finalize()
  DyLibFree(lh)
  return 0
end function

var error_ = python_database("dummy")
if (error_) then
  print "something went wrong"
end if
RockTheSchock
Posts: 226
Joined: Mar 12, 2006 16:25

Re: Using Python as a database (in-memory and on disk) packa

Postby RockTheSchock » Feb 04, 2014 14:21

If you need something lighter and perhaps faster you could also use lua which has a nice and easy syntax. A blueprint file(lua syntax) for a unit could look like this one for the game forged alliance. You embed a lua interpreter with only 130kb of overhead.

https://bitbucket.org/thepilot/forged-a ... 01_unit.bp

The basic data structure used by lua is a table (assoziative nested arrays).
VANYA
Posts: 1362
Joined: Oct 24, 2010 15:16
Location: Ярославль
Contact:

Re: Using Python as a database (in-memory and on disk) packa

Postby VANYA » Feb 05, 2014 4:16

Hi AGS!

Don't work your example. Program enters the procedure Py_Initialize , and ends in it.
AGS
Posts: 1284
Joined: Sep 25, 2007 0:26
Location: the Netherlands

Re: Using Python as a database (in-memory and on disk) packa

Postby AGS » Feb 25, 2014 6:31

VANYA wrote:Hi AGS!

Don't work your example. Program enters the procedure Py_Initialize , and ends in it.


Thank you, VANYA, for basically destroying my hopes of using Python as an in - memory
database :) And of course thank you for finding the bug.

What I had in mind was this: an fb programmer can use python27.dll as an implementation
of abstract data types that could be used to create an in - memory database that can
be saved to disk (would it not have been nice to be able to write/read a hash table, a list etc....
to/from disk without having to write the actual code that does the actual reading/writing)?

Python comes with implementations of enough abstract data types to be usable
(though a tree implementation is sadly missed), python27.dll is a couple of mb in size
so everything looked okay (a smaller python27.dll would have been nicer).

First up: I am guessing you used python27.dll and either did not install Python (=downloaded Python from
python.org and did a full install using an official installer) or perhaps you did install Python and there is
some kind of other problem.

Either way Py_Initialize seems to be where the problem is at.

I tried to remove the call to Py_Initialize from the example to see what happens.
And removing Py_Initialize breaks the example (it cannot be removed from the example code).

So I went on to find out what it is that Py_Initialize does (Python is open source and written in C.
And me can read/understand code written in C).

Py_Initialize calls lots of functions. My guess is that some of those need a bit more than python27.dll
to work. Meaning that in order to use Python as an in - memory database package you need to do
a full Python install.

Which is where the idea of using Python as an in - memory database finds it's end.
Having to do a full python install in order to use it as an in - memory database is a bit too
much. Using python as a dll (python27.dll) to me is the same as using any other external
library. For databases that could be sqlite.dll, gdbm.dll, cdb.dll, psql.dll or etc....

But having to do a full Python install is not quite the same as using one dll.

The reason why I did not catch the error with Py_Initialize is that Python
is installed on my PC. Py_Initialize is going to work on my PC as I already have Python
installed.

Testing the example properly would have involved removing everything Python related
from my PC. Which is a good lesson for the future: remove any dependencies that
could aid in making an example work before testing the example.

You catching the error instead of me means I did not properly test my example.
My example would also have failed on Linux (there are no .dll files on Linux, Linux uses .so).

As far as lua is concerned: the lua interface is not quite as easy to understand/use
as the Python interface. Lua uses an explicit stack to move values from-and-to
lua. When using Lua the interpreter state is passed around explicitly as
a first parameter (L). And many operations on L involve indexing the stack (which
makes using Lua as an embedded language kinda hard).

When using Python the interpreter is not used as a parameter. And there is no stack
indexing involved.

Lua has one thing going for it: all you need are a couple of dll files to install it
(a full Python installaction contains lots and lots of files).

If Lua would have the same kind of interface as Python it would be great as a table can
contain other tables, strings, numbers etc.... (a lua table is a bit like a Python dictionary).
And even with the somewhat more awkward interface it would have been great to use.

But how to write a table to disk? What is the Lua equivalent of PyMarshal_WriteObjectToString(obj,0)?
And what is the Lua equivalent of PyMarshal_ReadObjectFromString(content, string_size)?

I've looked at the Lua interface and did not find these equivalents. What I did find (on the net)
are lots of implementations that can be used like PyMarshal_*. Meaning that at least some
Lua users would like to have marshalling support in Lua.

This lack of marshalling support and the tedious stack interface (though Python reference counting
is no picknick either) makes me think Lua will not be that easy to use as an in-memory database.
Lua being small (copying a couple of libraries is all that it takes to install lua) makes me
wish I could use it like I tried to use python in this example.
TJF
Posts: 3503
Joined: Dec 06, 2009 22:27
Location: N47°, E15°
Contact:

Re: Using Python as a database (in-memory and on disk) packa

Postby TJF » Feb 25, 2014 10:08

AGS wrote:But how to write a table to disk? What is the Lua equivalent of PyMarshal_WriteObjectToString(obj,0)?
And what is the Lua equivalent of PyMarshal_ReadObjectFromString(content, string_size)?

For this kind of stuff I use GLib Data types and libjson-glib to translate them to strings. It needs customized handlers for extended data types, which can be easily implemented.

IMHO it's too much bloat to implement an interpreter just to handle data structures. And GIO (IO extension for GLib) offers additional features to handle the data types. Ie you can read them from a compressed resource file. You can store them in a (compressed) INI file, on request for global usage on the system, ... A lot of useful features.
RockTheSchock
Posts: 226
Joined: Mar 12, 2006 16:25

Re: Using Python as a database (in-memory and on disk) packa

Postby RockTheSchock » Feb 26, 2014 22:13

a little example

Code: Select all

#Include once "Lua/lua.bi"
#include once "Lua/lauxlib.bi"
#include once "Lua/lualib.bi"

Sub iterateLuaTable(L As Lua_State Ptr, index As Integer=-1,depth As Integer=0)
   Dim As String key,value
   sleep
       
   lua_pushvalue(L, index)
   'stack now contains: -1 => table
   lua_pushnil(L)
   'stack now contains: -1 => nil; -2 => table
   Do While lua_next(L, -2)
      'stack now contains: -1 => value; -2 => key; -3 => table
      'copy the key so that lua_tostring does not modify the original
      lua_pushvalue(L, -2)
      'stack now contains: -1 => key; -2 => value; -3 => key; -4 => table
      key = *(lua_tostring(L, -1))
      value = *(lua_tostring(L, -2))
     
      Print Space(2*depth);key;" => "; value

      if lua_istable(L, -2) Then
            iterateLuaTable(L,-2,depth+1)
      EndIf
      'pop value + copy of key, leaving original key
      lua_pop(L, 2)
      'stack now contains: -1 => key; -2 => table
   Loop
   'stack now contains: -1 => table (when lua_next returns 0 it pops the key
   'but does not push anything.)
   'Pop table
   lua_pop(L, 1)
   'Stack is now the same as it was on entry to this function
End Sub

DIM Lua AS lua_State PTR
DIM AS INTEGER m = 27, n = 36
Dim As String text,table

Screen 19

Lua = luaL_newstate()
luaL_openlibs(lua)
luaopen_base(Lua)


' Skript laden
If luaL_loadfile(Lua, "Leagues.lua") ORELSE lua_pcall(Lua, 0, 0, 0) THEN
  PRINT "Fehler: " & *lua_tostring(Lua, -1)
  Sleep
  END
END IF

lua_getglobal(Lua, "Leagues")
iterateLuaTable lua

Sleep


Leagues.lua

Code: Select all

Leagues = {
  code = "GER",
  country ="Germany",
  ["Bundesliga"] = {
    level = 1,
    teams = {
      "FC Augsburg",
      "Bayer 04 Leverkusen",
      "FC Bayern München",
      "Borussia Dortmund",
      "Borussia Mönchengladbach",
      "Eintracht Braunschweig",
      "Eintracht Frankfurt",
      "SC Freiburg",
      "Hamburger SV",
      "Hannover 96",
      "Hertha BSC",
      "TSG Hoffenheim",
      "1. FSV Mainz 05",
      "1. FC Nürnberg",
      "FC Schalke 04",
      "VfB Stuttgart",
      "SV Werder Bremen",
      "VfL Wolfsburg",            
    }
  },
  ["2nd Bundesliga"] = {
    level = 2,
    teams = {
      "VfR Aalen",
      "Arminia Bielefeld",
      "VfL Bochum",
      "Dynamo Dresden",
      "Energie Cottbus",
      "FC Erzgebirge Aue",
      "Fortuna Düsseldorf",
      "FSV Frankfurt",
      "SpVgg Greuther Fürth",
      "FC Ingolstadt 04",
      "1. FC Kaiserslautern",
      "Karlsruher SC",
      "1. FC Köln",
      "1860 Munich",
      "SC Paderborn 07",
      "SV Sandhausen",
      "FC St. Pauli",
      "1. FC Union Berlin",
    }
  },
  ["3rd Liga"] = {
    level = 3,
    teams = {
        "SC Paderborn 07",
        "SpVgg Unterhaching",
        "Eintracht Braunschweig",
        "Wacker Burghausen",
        "Bayern München II",
        "VfR Aalen",
        "Dynamo Dresden",
        "Kickers Emden",
        "FC Carl Zeiss Jena",
        "Jahn Regensburg",
        "Kickers Offenbach",
        "VfB Stuttgart II",
        "1. FC Union Berlin",
        "SV Sandhausen",
        "Rot-Weiß Erfurt",
        "Wuppertaler SV",
        "Erzgebirge Aue",
        "Fortuna Düsseldorf",
        "Werder Bremen II",
        "Stuttgarter Kickers",
    }
  },   
}

Return to “Tips and Tricks”

Who is online

Users browsing this forum: No registered users and 2 guests