Tag Archives: hair loss

Two Important SQLite and Python Lessons

When using SQLite3 and Python (2.x), there are two important lessons that are not obvious (at least not to me).

1. Dictionaries and TimeStamps

Ideally, I would like to do two things.  First, access data from a dictionary and not a list.  It is far more intuitive to access by column name (or query name substitution) than by list index.  Second, the datetime values should be correctly coerced even though SQLite has no implicit timestamp type.  I believe these two simple requirements are expected by most people, especially those family with Microsoft SQL and ADO.NET.

Good news, Python supports this!  However, there are some switches to set so to speak.

After importing sqlite3, the following connect statement will suffice for both needs:

conn = sqlite3.connect(dbname, detect_types=sqlite3.PARSE_DECLTYPES|sqlite3.PARSE_COLNAMES)

The PARSE_COLNAMES returns a dictionary for each row fetched instead of a list.

The PARSE_DECLTYPES empowers python to do a bit more type conversion based on the queries provided.  For example, if you do this:

cur.execute('select mytime from sometable')

You will get mytime as a string.  However, if you do this:

cur.execute('select mytime as "[timestamp]" from sometable')

You will get mytime as a datetime type in python.  This is very useful.

One step further; let’s say you want a timestamp but substitute the column name in the query.  Do this:

cur.execute('select mytime as "mytime [timestamp]" from sometable')

Not only with the data be returned as a datetime object, the dictionary will contain column name substitution provided in the query.  Beware, if you don’t do this on aggregate functions, the python sqlite library will attempt to add to the dictionary with an empty string key.  (Not sure why this is but beware.)

The adapters and converters live in the file dpapi2.py in your python library installation directory for sqlite3.

Refer to the documentation here.

2. Execute and Tuples

This is really a lesson on declaring implicit tuples.

The execute method on a cursor is great because it will safely convert strings for use in the database (reducing the possibility of SQL injection).  It takes a query string and a substitution tuple.

However, I got caught again on the example below as will many who follow me.

This works:

cur.execute('insert into mytable values(?, ?, ?, ?)', (1, 2, 3, 4))

This doesn’t work and throws an exception:

cur.execute('insert into mytable values(?)', (1))

This works:

cur.execute('insert into mytable values(?)', (1,))

The comma after the one declares a tuple of one element.  Argh!!!

I hope this helps.


Using crosstool-ng on MinGW

Hello sadistic friends.

I have given up.

I am leaving the instructions below but this is plan too difficult and I am moving on to Cygwin.  This is will remain here for simply…  I don’t know…  warnings to others that hair loss is not a good trade-off for getting this to work under MinGW.

We are going to try to build a compiler on Winderz now.  This is a follow up to installing crosstool-ng on MinGW on Winderz.  You should have MinGW already installed.

  1. Open up your msys bash shell the batch file – C:\MinGW\msys\1.0\msys.bat as administrator.  We will assume that you installed MinGW on C:\MinGW.  We will be working from the instructions here.  I installed mine at /home/maks/crosstool.  Always, I repeat, always run the terminal as administrator or some of the decompression of packages will not occur due to permission errors.
  2. Export your tool location by typing
    export PATH="${PATH}:/home/maks/crosstool/bin"

    Substitute your own path because I don’t think your name is Maks.  Now you should be able to run ct-ng now.  In fact, if you create a .profile file in your home directory that includes this line, you will never need to type it again.

  3. Make a directory for your cross compiler.  I called mine linux64 and I placed it in my home directory.  Remember your name is not Maks.  I decided that I am going to build an x86_64_unknown-linux-gnu because I want it to run on an Ubuntu built server 13.10. So in the linux64, type
    ct-ng x86_64-unknown-linux-gnu

    You can read about using ct-ng from the link above.  Then through

    ct-ng menuconfig

    I moved to all the latest versions of everything, eglibc, the 3.10 kernel, binutils, etc.  Also, make sure that your compiler is not linking statically.  This doesn’t work on Windows (and I haven’t tracked down why).

  4. Ok, now run
    ct-ng build

    You will get errors.  We will work through them.

  5. The first error will be that the filesystem is not case sensitive.  We will remedy this by adding the key to the registry.
    HKLM\SYSTEM\CurrentControlSet\Control\Session Manager\kernel\obcaseinsensitive

    Set this value to zero.

  6. The second will be that the OS  MINGW32_NT-6.2 is not supported.  We will add that.  Edit ~/crosstool/lib/ct-ng.1.19.0/scripts/functions and look for a function called CT_DoForceRmdir.  Find the case statement and add MINGW32* to the line.  It should look like this.
  7. You may need to create a source directory where all of the tarballs are cached.  Simply
    mkdir ~/src
  8. The next error relates to downloading the tarballs and the use of certificates.  I determined this by looking at build.log.  The utility wget is attempting to validate certificates.  For now, we will simply remote the check.   Edit ~/crosstool/lib/ct-ng.1.19.0/scripts/functions and look for CT_DoGetFile.  On the line that starts with “if CT_DoExecLog ALL wget”, add –no-check-certificate to the command line.  Be mindful for split lines in script using the \.
  9. Oh geez, there is way too much.  Headers aren’t there… ugh.  Too much.  I have given up.  I am going to now try my hand at Cygwin.