Обсуждение: Switching python app from sqlite to postgres
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
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
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
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
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
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