Thread: Switching python app from sqlite to postgres

Switching python app from sqlite to postgres

From
Bob McConnell
Date:
I have been looking at an application I downloaded from Sourceforge for
model railroading[1]. So far it has two strikes against it. It uses
Python 3, and it uses sqlite-3. Other than that, it looks like it might
be a useful program.

I tried to build and run the software, but Slackware uses and bundles
Python 2 and although I also have 3.1 installed, the packaged
installation of the sqlite-3 drivers only runs with version 2. Py 3
still says they're not installed.

So the next step is likely to switch the code to use Postgres. The
database interface is limited to three Python source files, so I don't
think it will be too difficult, if I can find some guidelines on how to
do it. Does anyone know where I might find some information on how to
make this conversion?

Thank you,

Bob McConnell
N2SPP

[1]  Model Operations Processing System

Re: Switching python app from sqlite to postgres

From
Josh Kupershmidt
Date:
On Thu, Apr 12, 2012 at 9:51 AM, Bob McConnell <rmcconne@lightlink.com> wrote:
> I have been looking at an application I downloaded from Sourceforge for
> model railroading[1]. So far it has two strikes against it. It uses Python
> 3, and it uses sqlite-3. Other than that, it looks like it might be a useful
> program.
>
> I tried to build and run the software, but Slackware uses and bundles Python
> 2 and although I also have 3.1 installed, the packaged installation of the
> sqlite-3 drivers only runs with version 2. Py 3 still says they're not
> installed.
>
> So the next step is likely to switch the code to use Postgres. The database
> interface is limited to three Python source files, so I don't think it will
> be too difficult, if I can find some guidelines on how to do it. Does anyone
> know where I might find some information on how to make this conversion?

Well, I imagine the path of least resistance to get the application
working would be to install the sqlite library it wants (BTW,
shouldn't the "sqlite3" module be built-in for all Python versions
2.5+ ?). But as to your question, converting an application from
SQLite to Postgres shouldn't be all that hard. SQLite is quite limited
in what it can do, which means less esoteric features or "SQL
extensions" to worry about porting correctly. For instance, you don't
have to worry about converting any stored procedures, since SQLite
doesn't have them.

You might have a few datatype conversions to look at, such as
"datetime" -> "timestamp", "blob" -> "bytea", and some similar issues.
My strategy is usually to build a test suite, if it doesn't have one
already, make sure the suite passes with the SQLite backend,
substitute in the SQLite connections for psycopg2, and see what breaks
:-)

Josh

Re: Switching python app from sqlite to postgres

From
Bob McConnell
Date:
Josh Kupershmidt wrote:
> On Thu, Apr 12, 2012 at 9:51 AM, Bob McConnell <rmcconne@lightlink.com> wrote:
>> I have been looking at an application I downloaded from Sourceforge for
>> model railroading[1]. So far it has two strikes against it. It uses Python
>> 3, and it uses sqlite-3. Other than that, it looks like it might be a useful
>> program.
>>
>> I tried to build and run the software, but Slackware uses and bundles Python
>> 2 and although I also have 3.1 installed, the packaged installation of the
>> sqlite-3 drivers only runs with version 2. Py 3 still says they're not
>> installed.
>>
>> So the next step is likely to switch the code to use Postgres. The database
>> interface is limited to three Python source files, so I don't think it will
>> be too difficult, if I can find some guidelines on how to do it. Does anyone
>> know where I might find some information on how to make this conversion?
>
> Well, I imagine the path of least resistance to get the application
> working would be to install the sqlite library it wants (BTW,
> shouldn't the "sqlite3" module be built-in for all Python versions
> 2.5+ ?). But as to your question, converting an application from
> SQLite to Postgres shouldn't be all that hard. SQLite is quite limited
> in what it can do, which means less esoteric features or "SQL
> extensions" to worry about porting correctly. For instance, you don't
> have to worry about converting any stored procedures, since SQLite
> doesn't have them.
>
> You might have a few datatype conversions to look at, such as
> "datetime" -> "timestamp", "blob" -> "bytea", and some similar issues.
> My strategy is usually to build a test suite, if it doesn't have one
> already, make sure the suite passes with the SQLite backend,
> substitute in the SQLite connections for psycopg2, and see what breaks
> :-)

sqlite is not included in either version of Python I have installed
(2.5.2 and 3.1.1). I tried installing it after doing the parallel
installation of 3.1.1, but it bound to the older version of Python. I
don't know anything about either Python or sqlite, so I have no idea how
to get them to work together. I won't simply replace 2.5 with 3.1, since
Slackware uses Python scripts in a number of packages, and the language
apparently changed significantly between 2 and 3, so all of those
scripts have to be replaced simultaneously with the update. This
application won't work with 2.5, probably for the same reason.

So far, the only functions I have seen are connect, create a cursor
object, use that cursor to run several sql commands, and close. I
haven't looked too closely at the sql itself, but other than creating
some tables with primary keys, there doesn't seem to be much else beyond
adding and retrieving sets of records.

Since I already have Postgres servers running, it looks like my best
option is to install the Psycopg driver, modify the three files to use
it and see what happens. Of course, that assumes I can get that driver
to link to the correct version of Python as well. But, from your
comments it sounds a lot easier than most of the conversions I have done
while upgrading databases from MySQL.

(I just checked one of my CentOS 5 servers - based on RedHat EL 5.8. It
was updated earlier this morning, and is still running Python 2.4.3. It
looks like more than one distribution has postponed updating to 3.)

Thank you,

Bob McConnell
N2SPP

Re: Switching python app from sqlite to postgres

From
Monte Milanuk
Date:
On 04/15/2012 06:17 AM, Bob McConnell wrote:
> sqlite is not included in either version of Python I have installed
> (2.5.2 and 3.1.1). I tried installing it after doing the parallel
> installation of 3.1.1, but it bound to the older version of Python. I
> don't know anything about either Python or sqlite, so I have no idea how
> to get them to work together.

I would almost bet money that your python installs *do* have sqlite
built-in and available.  Even distributions such as Ubuntu Linux which
tends to break up the base install of python a bit so as to not require
thinks like tkinter (normally also 'built-in' to python) for server
installs, include sqlite by default.  My understanding is that it
(sqlite) is used for a lot of small data stores when something slightly
more than a pickle or shelve is needed.

Fire up the python interpreter of your choice, and type 'import sqlite3'
like so:

monte@hp-dv4-linux:~$ python
Python 2.7.2+ (default, Oct  4 2011, 20:06:09)
[GCC 4.6.1] on linux2
Type "help", "copyright", "credits" or "license" for more information.
 >>> import sqlite3
 >>> sqlite3.sqlite_version
'3.7.7'
 >>> quit()
monte@hp-dv4-linux:~$ python3
Python 3.2.2 (default, Sep  5 2011, 21:17:14)
[GCC 4.6.1] on linux2
Type "help", "copyright", "credits" or "license" for more information.
 >>> import sqlite3
 >>> sqlite3.sqlite_version
'3.7.7'
 >>> quit()
monte@hp-dv4-linux:~$

Simple enough to test on your own.  You can follow along a simple
tutorial from here if you like:

http://zetcode.com/db/sqlitepythontutorial/

BTW, that site has a lot of good tutorials... including one on Python +
PostgreSQL...

http://zetcode.com/db/postgresqlpythontutorial/


HTH,

Monte

Re: Switching python app from sqlite to postgres

From
Bob McConnell
Date:
Monte Milanuk wrote:
> On 04/15/2012 06:17 AM, Bob McConnell wrote:
>> sqlite is not included in either version of Python I have installed
>> (2.5.2 and 3.1.1). I tried installing it after doing the parallel
>> installation of 3.1.1, but it bound to the older version of Python. I
>> don't know anything about either Python or sqlite, so I have no idea how
>> to get them to work together.
>
> I would almost bet money that your python installs *do* have sqlite
> built-in and available.  Even distributions such as Ubuntu Linux which
> tends to break up the base install of python a bit so as to not require
> thinks like tkinter (normally also 'built-in' to python) for server
> installs, include sqlite by default.  My understanding is that it
> (sqlite) is used for a lot of small data stores when something slightly
> more than a pickle or shelve is needed.
>
> Fire up the python interpreter of your choice, and type 'import sqlite3'
> like so:
>
> monte@hp-dv4-linux:~$ python
> Python 2.7.2+ (default, Oct  4 2011, 20:06:09)
> [GCC 4.6.1] on linux2
> Type "help", "copyright", "credits" or "license" for more information.
>  >>> import sqlite3
>  >>> sqlite3.sqlite_version
> '3.7.7'
>  >>> quit()
> monte@hp-dv4-linux:~$ python3
> Python 3.2.2 (default, Sep  5 2011, 21:17:14)
> [GCC 4.6.1] on linux2
> Type "help", "copyright", "credits" or "license" for more information.
>  >>> import sqlite3
>  >>> sqlite3.sqlite_version
> '3.7.7'
>  >>> quit()
> monte@hp-dv4-linux:~$
>
> Simple enough to test on your own.  You can follow along a simple
> tutorial from here if you like:
>
> http://zetcode.com/db/sqlitepythontutorial/
>
> BTW, that site has a lot of good tutorials... including one on Python +
> PostgreSQL...
>
> http://zetcode.com/db/postgresqlpythontutorial/

This is on Slackware 12.2.0, after doing an explicit install of Python 3
and then sqlite3 packages using Slackware's package management.

bash-3.1$ python
Python 2.5.2 (r252:60911, Sep 11 2008, 13:43:31)
[GCC 4.2.4] on linux2
Type "help", "copyright", "credits" or "license" for more information.
 >>> import sqlite3
 >>> sqlite3.sqlite_version
'3.6.6.2'
 >>> quit()
bash-3.1$ python3.1
Python 3.1.1 (r311:74480, Apr 12 2012, 09:17:32)
[GCC 4.2.4] on linux2
Type "help", "copyright", "credits" or "license" for more information.
 >>> import sqlite3
Traceback (most recent call last):
   File "<stdin>", line 1, in <module>
   File "/usr/lib/python3.1/sqlite3/__init__.py", line 24, in <module>
     from sqlite3.dbapi2 import *
   File "/usr/lib/python3.1/sqlite3/dbapi2.py", line 27, in <module>
     from _sqlite3 import *
ImportError: No module named _sqlite3
 >>>

So I now have sqlite3 installed in Python 2, but not in Python 3.
Originally, it was not available in Python 2 either. So the standard
package installed it with Python 2.

Bob McConnell
N2SPP

Re: Switching python app from sqlite to postgres

From
Monte Milanuk
Date:
On 04/15/2012 12:28 PM, Bob McConnell wrote:
>
> So I now have sqlite3 installed in Python 2, but not in Python 3.
> Originally, it was not available in Python 2 either. So the standard
> package installed it with Python 2.
>

Sounds like you have a problem specific to Slacware then - or at least
to the source version - as sqlite3 is supposed to be included by default
as part of *all* python versions since 2.5.  I will admit that my
experience with python has all been on systems that use a package
manager such as apt or yum - python/sqlite 'just works' on vanilla
installs of Debian, Ubuntu, CentOS, Scientific, openSuSE, etc.

Good luck,

Monte