sqlobject-discuss

[SQLObject] syntax error exception on sqlite database?

[Dominic L. <dom...@gm...>]

Hello,

I am using SQLObject to create an object model for a legacy database
which can be found here: http://zofu.no-ip.de/tyr101/

I am not quite sure what the problem is, but I have so far ruled out
both the database and my code. When I use the same code on the same
database data in a mysql database, everything works fine. I'll paste
the stack trace along with all of SQLObject's debug output below.

      My code can be found: http://github.com/dlobue/evedbobject

This code is being run on a gentoo linux machine with sqlite versions
2.8.16-r4 and 3.7.0 both installed. I am using python 2.6.4, and
sqlobject 0.12.4.

Here's the stack trace with all sqlobject debug output turned on:
 1/QueryOne:  SELECT sql FROM sqlite_master WHERE type='table' AND
name='crpNPCCorporations'
 1/QueryR  :  SELECT sql FROM sqlite_master WHERE type='table' AND
name='crpNPCCorporations'
 1/QueryOne-> ('CREATE TABLE "crpNPCCorporations" (\n  "corporationID"
int(11) NOT NULL,\n  "size" char(1) default NULL,\n  "extent" char(1)
default NULL,\n  "solarSystemID" int(11) default NULL,\n
"investorID1" int(11) default NULL,\n  "investorShares1" tinyint(4)
default NULL,\n  "investorID2" int(11) default NULL,\n
"investorShares2" tinyint(4) default NULL,\n  "investorID3" int(11)
default NULL,\n  "investorShares3" tinyint(4) default NULL,\n
"investorID4" int(11) default NULL,\n  "investorShares4" tinyint(4)
default NULL,\n  "friendID" int(11) default NULL,\n  "enemyID" int(11)
default NULL,\n  "publicShares" bigint(20) default NULL,\n
"initialPrice" int(11) default NULL,\n  "minSecurity" double default
NULL,\n  "scattered" tinyint(1) default NULL,\n  "fringe" tinyint(4)
default NULL,\n  "corridor" tinyint(4) default NULL,\n  "hub"
tinyint(4) default NULL,\n  "border" tinyint(4) default NULL,\n
"factionID" int(11) default NULL,\n  "sizeFactor" double default
NULL,\n  "stationCount" smallint(6) default NULL,\n
"stationSystemCount" smallint(6) default NULL,\n  "description"
varchar(4000) default NULL,\n  PRIMARY KEY  ("corporationID")\n)',)
 1/COMMIT  :  auto
 1/QueryOne:  SELECT sql FROM sqlite_master WHERE type='table' AND
name='mapSolarSystems'
 1/QueryR  :  SELECT sql FROM sqlite_master WHERE type='table' AND
name='mapSolarSystems'
 1/QueryOne-> ('CREATE TABLE "mapSolarSystems" (\n  "regionID" int(11)
default NULL,\n  "constellationID" int(11) default NULL,\n
"solarSystemID" int(11) NOT NULL,\n  "solarSystemName" varchar(100)
default NULL,\n  "x" double default NULL,\n  "y" double default
NULL,\n  "z" double default NULL,\n  "xMin" double default NULL,\n
"xMax" double default NULL,\n  "yMin" double default NULL,\n  "yMax"
double default NULL,\n  "zMin" double default NULL,\n  "zMax" double
default NULL,\n  "luminosity" double default NULL,\n  "border"
tinyint(1) default NULL,\n  "fringe" tinyint(1) default NULL,\n
"corridor" tinyint(1) default NULL,\n  "hub" tinyint(1) default
NULL,\n  "international" tinyint(1) default NULL,\n  "regional"
tinyint(1) default NULL,\n  "constellation" tinyint(1) default NULL,\n
 "security" double default NULL,\n  "factionID" int(11) default
NULL,\n  "radius" double default NULL,\n  "sunTypeID" smallint(6)
default NULL,\n  "securityClass" varchar(2) default NULL,\n  PRIMARY
KEY  ("solarSystemID")\n)',)
 1/COMMIT  :  auto
 1/QueryOne:  SELECT sql FROM sqlite_master WHERE type='table' AND
name='mapConstellations'
 1/QueryR  :  SELECT sql FROM sqlite_master WHERE type='table' AND
name='mapConstellations'
 1/QueryOne-> ('CREATE TABLE "mapConstellations" (\n  "regionID"
int(11) default NULL,\n  "constellationID" int(11) NOT NULL,\n
"constellationName" varchar(100) default NULL,\n  "x" double default
NULL,\n  "y" double default NULL,\n  "z" double default NULL,\n
"xMin" double default NULL,\n  "xMax" double default NULL,\n  "yMin"
double default NULL,\n  "yMax" double default NULL,\n  "zMin" double
default NULL,\n  "zMax" double default NULL,\n  "factionID" int(11)
default NULL,\n  "radius" double default NULL,\n  PRIMARY KEY
("constellationID")\n)',)
 1/COMMIT  :  auto
 1/QueryOne:  SELECT sql FROM sqlite_master WHERE type='table' AND
name='mapRegions'
 1/QueryR  :  SELECT sql FROM sqlite_master WHERE type='table' AND
name='mapRegions'
 1/QueryOne-> ('CREATE TABLE "mapRegions" (\n  "regionID" int(11) NOT
NULL,\n  "regionName" varchar(100) default NULL,\n  "x" double default
NULL,\n  "y" double default NULL,\n  "z" double default NULL,\n
"xMin" double default NULL,\n  "xMax" double default NULL,\n  "yMin"
double default NULL,\n  "yMax" double default NULL,\n  "zMin" double
default NULL,\n  "zMax" double default NULL,\n  "factionID" int(11)
default NULL,\n  "radius" double default NULL,\n  PRIMARY KEY
("regionID")\n)',)
 1/COMMIT  :  auto
 1/QueryOne:  SELECT sql FROM sqlite_master WHERE type='table' AND
name='chrFactions'
 1/QueryR  :  SELECT sql FROM sqlite_master WHERE type='table' AND
name='chrFactions'
 1/QueryOne-> ('CREATE TABLE "chrFactions" (\n  "factionID" int(11)
NOT NULL,\n  "factionName" varchar(100) default NULL,\n  "description"
varchar(1000) default NULL,\n  "raceIDs" int(11) default NULL,\n
"solarSystemID" int(11) default NULL,\n  "corporationID" int(11)
default NULL,\n  "sizeFactor" double default NULL,\n  "stationCount"
smallint(6) default NULL,\n  "stationSystemCount" smallint(6) default
NULL,\n  "militiaCorporationID" int(11) default NULL,\n  PRIMARY KEY
("factionID")\n)',)
 1/COMMIT  :  auto
 1/QueryOne:  SELECT sql FROM sqlite_master WHERE type='table' AND
name='staStations'
 1/QueryR  :  SELECT sql FROM sqlite_master WHERE type='table' AND
name='staStations'
 1/QueryOne-> ('CREATE TABLE "staStations" (\n  "stationID" int(11)
NOT NULL,\n  "security" smallint(6) default NULL,\n
"dockingCostPerVolume" double default NULL,\n  "maxShipVolumeDockable"
double default NULL,\n  "officeRentalCost" int(11) default NULL,\n
"operationID" tinyint(3) default NULL,\n  "stationTypeID" smallint(6)
default NULL,\n  "corporationID" int(11) default NULL,\n
"solarSystemID" int(11) default NULL,\n  "constellationID" int(11)
default NULL,\n  "regionID" int(11) default NULL,\n  "stationName"
varchar(100) default NULL,\n  "x" double default NULL,\n  "y" double
default NULL,\n  "z" double default NULL,\n  "reprocessingEfficiency"
double default NULL,\n  "reprocessingStationsTake" double default
NULL,\n  "reprocessingHangarFlag" tinyint(4) default NULL,\n  PRIMARY
KEY  ("stationID")\n)',)
 1/COMMIT  :  auto
 1/QueryOne:  SELECT sql FROM sqlite_master WHERE type='table' AND
name='staOperations'
 1/QueryR  :  SELECT sql FROM sqlite_master WHERE type='table' AND
name='staOperations'
 1/QueryOne-> ('CREATE TABLE "staOperations" (\n  "activityID"
tinyint(3) default NULL,\n  "operationID" tinyint(3) NOT NULL,\n
"operationName" varchar(100) default NULL,\n  "description"
varchar(1000) default NULL,\n  "fringe" tinyint(4) default NULL,\n
"corridor" tinyint(4) default NULL,\n  "hub" tinyint(4) default
NULL,\n  "border" tinyint(4) default NULL,\n  "ratio" tinyint(4)
default NULL,\n  "caldariStationTypeID" smallint(6) default NULL,\n
"minmatarStationTypeID" smallint(6) default NULL,\n
"amarrStationTypeID" smallint(6) default NULL,\n
"gallenteStationTypeID" smallint(6) default NULL,\n
"joveStationTypeID" smallint(6) default NULL,\n  PRIMARY KEY
("operationID")\n)',)
 1/COMMIT  :  auto
 1/QueryOne:  SELECT sql FROM sqlite_master WHERE type='table' AND
name='staStationTypes'
 1/QueryR  :  SELECT sql FROM sqlite_master WHERE type='table' AND
name='staStationTypes'
 1/QueryOne-> ('CREATE TABLE "staStationTypes" (\n  "stationTypeID"
smallint(6) NOT NULL,\n  "dockingBayGraphicID" smallint(6) default
NULL,\n  "hangarGraphicID" smallint(6) default NULL,\n  "dockEntryX"
double default NULL,\n  "dockEntryY" double default NULL,\n
"dockEntryZ" double default NULL,\n  "dockOrientationX" double default
NULL,\n  "dockOrientationY" double default NULL,\n  "dockOrientationZ"
double default NULL,\n  "operationID" tinyint(3) default NULL,\n
"officeSlots" tinyint(4) default NULL,\n  "reprocessingEfficiency"
double default NULL,\n  "conquerable" tinyint(1) default NULL,\n
PRIMARY KEY  ("stationTypeID")\n)',)
 1/COMMIT  :  auto
 1/QueryOne:  SELECT FactionName, Description, solarSystemID,
corporationID, militiaCorporationID, raceIDs, sizeFactor,
stationCount, stationSystemCount, PRIMARY FROM chrFactions WHERE
((chrFactions.factionID) = (500001))
 1/QueryR  :  SELECT FactionName, Description, solarSystemID,
corporationID, militiaCorporationID, raceIDs, sizeFactor,
stationCount, stationSystemCount, PRIMARY FROM chrFactions WHERE
((chrFactions.factionID) = (500001))
 1/COMMIT  :  auto
Traceback (most recent call last):
  File "models.py", line 291, in <module>
    print(chrFactions.get(500001))
  File "/usr/lib64/python2.6/site-packages/sqlobject/main.py", line 893, in get
    val._init(id, connection, selectResults)
  File "/usr/lib64/python2.6/site-packages/sqlobject/main.py", line
933, in _init
    selectResults = self._connection._SO_selectOne(self, dbNames)
  File "/usr/lib64/python2.6/site-packages/sqlobject/dbconnection.py",
line 526, in _SO_selectOne
    return self._SO_selectOneAlt(so, columnNames, so.q.id==so.id)
  File "/usr/lib64/python2.6/site-packages/sqlobject/dbconnection.py",
line 536, in _SO_selectOneAlt
    clause=condition)))
  File "/usr/lib64/python2.6/site-packages/sqlobject/dbconnection.py",
line 392, in queryOne
    return self._runWithConnection(self._queryOne, s)
  File "/usr/lib64/python2.6/site-packages/sqlobject/dbconnection.py",
line 262, in _runWithConnection
    val = meth(conn, *args)
  File "/usr/lib64/python2.6/site-packages/sqlobject/dbconnection.py",
line 385, in _queryOne
    self._executeRetry(conn, c, s)
  File "/usr/lib64/python2.6/site-packages/sqlobject/sqlite/sqliteconnection.py",
line 186, in _executeRetry
    raise OperationalError(ErrorMessage(e))
sqlobject.dberrors.OperationalError: near "PRIMARY": syntax error



Any ideas?

--
Dominic LoBue

Re: [SQLObject] syntax error exception on sqlite database?

[Oleg B. <ph...@ph...>]

On Mon, Aug 02, 2010 at 10:24:18PM -0700, Dominic LoBue wrote:
> I am not quite sure what the problem is, but I have so far ruled out
> both the database and my code.

   I am not so sure...

> When I use the same code on the same
> database data in a mysql database, everything works fine.

   It could be that the SQL syntax you use is valid in MySQL but invalid in
SQLite.

>  1/QueryR  :  SELECT FactionName, Description, solarSystemID,
> corporationID, militiaCorporationID, raceIDs, sizeFactor,
> stationCount, stationSystemCount, PRIMARY FROM chrFactions WHERE
> ((chrFactions.factionID) = (500001))

> sqlobject.dberrors.OperationalError: near "PRIMARY": syntax error

   What does it mean - SELECT *** PRIMARY FROM? It seems you've got a
column named PRIMARY...

Oleg.
-- 
     Oleg Broytman            http://phd.pp.ru/            ph...@ph...
           Programmers don't die, they just GOSUB without RETURN.

Re: [SQLObject] syntax error exception on sqlite database?

[Dominic L. <dom...@gm...>]

On Tue, Aug 3, 2010 at 10:06 AM, Oleg Broytman <ph...@ph...> wrote:
> On Mon, Aug 02, 2010 at 10:24:18PM -0700, Dominic LoBue wrote:
>> I am not quite sure what the problem is, but I have so far ruled out
>> both the database and my code.
>
>   I am not so sure...
>
>> When I use the same code on the same
>> database data in a mysql database, everything works fine.
>
>   It could be that the SQL syntax you use is valid in MySQL but invalid in
> SQLite.
>
>>  1/QueryR  :  SELECT FactionName, Description, solarSystemID,
>> corporationID, militiaCorporationID, raceIDs, sizeFactor,
>> stationCount, stationSystemCount, PRIMARY FROM chrFactions WHERE
>> ((chrFactions.factionID) = (500001))
>
>> sqlobject.dberrors.OperationalError: near "PRIMARY": syntax error
>
>   What does it mean - SELECT *** PRIMARY FROM? It seems you've got a
> column named PRIMARY...
>
> Oleg.
>
------------------------------------------------------------------------------
> The Palm PDK Hot Apps Program offers developers who use the
> Plug-In Development Kit to bring their C/C++ apps to Palm for a share
> of $1 Million in cash or HP Products. Visit us here for more details:
> http://p.sf.net/sfu/dev2dev-palm
> _______________________________________________
> sqlobject-discuss mailing list
> sql...@li...
> https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss
>

Oleg,

I'm not writing my own SQL queries. All I'm doing in my test is calling the
get method, the query is automatically generated. Also, keep in mind I used
the following keywords when I called connectionForURI(): debug=True,
debugOutput=True

To answer your question, I do not know what it means or why it is trying to
perform a select on 'PRIMARY'. There is no PRIMARY column, neither in my
code nor in the database itself.

I just double checked to be sure, and I was right, there's no PRIMARY
column. Here's a create statement to recreate the table:
CREATE TABLE "chrFactions" (
 "factionID" int(11) NOT NULL,
 "factionName" varchar(100) default NULL,
 "description" varchar(1000) default NULL,
 "raceIDs" int(11) default NULL,
 "solarSystemID" int(11) default NULL,
 "corporationID" int(11) default NULL,
 "sizeFactor" double default NULL,
 "stationCount" smallint(6) default NULL,
 "stationSystemCount" smallint(6) default NULL,
 "militiaCorporationID" int(11) default NULL,
 PRIMARY KEY  ("factionID")
)


And here's my definition:
class chrFactions(SQLObject):
   class sqlmeta:
       style = MixedCaseStyle(longID=True)
       idName = 'factionID'
       fromDatabase = True
   factionName = StringCol()
   description = StringCol()
   solarSystem = ForeignKey('mapSolarSystems', dbName='solarSystemID')
   corporation = ForeignKey('crpNPCCorporations', dbName='corporationID')
   militiaCorporation = ForeignKey('crpNPCCorporations',
                                   dbName='militiaCorporationID')
   corporations = MultipleJoin('crpNPCCorporations', joinColumn='factionID')
   solarSystems = MultipleJoin('mapSolarSystems', joinColumn='factionID')
   constellations = MultipleJoin('mapConstellations',
joinColumn='factionID')
   regions = MultipleJoin('mapRegions', joinColumn='factionID')


However SQLObject clearly thinks there's a PRIMARY column for some reason:
>>> pprint(chrFactions.sqlmeta.columns)
{'corporationID': <SOForeignKey corporationID connected to
crpNPCCorporations>,
 'description': <SOStringCol description>,
 'factionName': <SOStringCol factionName>,
 'militiaCorporationID': <SOForeignKey militiaCorporationID connected to
crpNPCCorporations>,
 'pRIMARY': <SOCol pRIMARY default=None>,
 'raceIDs': <SOIntCol raceIDs default=None>,
 'sizeFactor': <SOCol sizeFactor default=None>,
 'solarSystemID': <SOForeignKey solarSystemID connected to mapSolarSystems>,
 'stationCount': <SOIntCol stationCount default=None>,
 'stationSystemCount': <SOIntCol stationSystemCount default=None>}


Now why would it think that?

-- 
Dominic LoBue

Re: [SQLObject] syntax error exception on sqlite database?

[Oleg B. <ph...@ph...>]

On Wed, Aug 04, 2010 at 03:50:53AM -0700, Dominic LoBue wrote:
> CREATE TABLE "chrFactions" (
[skip]
>  PRIMARY KEY  ("factionID")
> )
> 
> 
> And here's my definition:
> class chrFactions(SQLObject):
>    class sqlmeta:
>        style = MixedCaseStyle(longID=True)
>        idName = 'factionID'
>        fromDatabase = True
> 
> However SQLObject clearly thinks there's a PRIMARY column for some reason:

   SQLObject's fromDatabase machinery is quite primitive and doesn't
support all of the backend features. In this case it sees the line

>  PRIMARY KEY  ("factionID")

   and decides it is a column. To work around the problem add the following
parameter to SQLiteConnection constructor: use_table_info=True; if you use
DB URIs add '&use_table_info=1'. This switches SQLiteConnection to a
different, more clever parser. In the future I'm going to make that the
default; the switch is at the top of my TODO.

Oleg.
-- 
     Oleg Broytman            http://phd.pp.ru/            ph...@ph...
           Programmers don't die, they just GOSUB without RETURN.

Re: [SQLObject] syntax error exception on sqlite database?

[Dominic L. <dom...@gm...>]

On Wed, Aug 4, 2010 at 4:18 AM, Oleg Broytman <ph...@ph...> wrote:
> On Wed, Aug 04, 2010 at 03:50:53AM -0700, Dominic LoBue wrote:
>> CREATE TABLE "chrFactions" (
> [skip]
>>  PRIMARY KEY  ("factionID")
>> )
>>
>>
>> And here's my definition:
>> class chrFactions(SQLObject):
>>    class sqlmeta:
>>        style = MixedCaseStyle(longID=True)
>>        idName = 'factionID'
>>        fromDatabase = True
>>
>> However SQLObject clearly thinks there's a PRIMARY column for some reason:
>
>   SQLObject's fromDatabase machinery is quite primitive and doesn't
> support all of the backend features. In this case it sees the line
>
>>  PRIMARY KEY  ("factionID")
>
>   and decides it is a column. To work around the problem add the following
> parameter to SQLiteConnection constructor: use_table_info=True; if you use
> DB URIs add '&use_table_info=1'. This switches SQLiteConnection to a
> different, more clever parser. In the future I'm going to make that the
> default; the switch is at the top of my TODO.
>
> Oleg.
> ------------------------------------------------------------------------------
> The Palm PDK Hot Apps Program offers developers who use the
> Plug-In Development Kit to bring their C/C++ apps to Palm for a share
> of $1 Million in cash or HP Products. Visit us here for more details:
> http://p.sf.net/sfu/dev2dev-palm
> _______________________________________________
> sqlobject-discuss mailing list
> sql...@li...
> https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss
>

Oleg,

That fixed it, thanks!

-- 
Dominic LoBue