Sequences, autoincrement and company

[Former user]

Consider MySQL table like this:

  CREATE TABLE `campaigns` (
    `id` varchar(36) NOT NULL default '',
    `tracker_key` int(11) NOT NULL auto_increment,
    `name` varchar(50) default NULL,
    ...
    PRIMARY KEY  (`id`),
    KEY `auto_tracker_key` (`tracker_key`),
  );

Imagine also that you have Campaign class connected to this table (id column has primary_key = True).

If you create new instance of this object cam = Campaign(name='test') and commit, you will get the following::

  cam.id # 59L
  cam.tracker_key # None

Which is clearly wrong. It should be:

  cam.id # ''
  cam.tracker_key # 59L

[Mike Bayer]

it seems like your mapper is not configured correctly. from an engine perspective it works fine.

heres one version of a test with your table:

t = Table('campaigns', x,
        Column('id', String(36), primary_key=True),
        Column('tracker_key', Integer, nullable=False),
        Column('name', String(50))
        )

t.insert().execute(id='id2', name='row2')

produces the query:

INSERT INTO campaigns (id, name) VALUES (%s, %s)
['row2']('id2',)

here it is using reflection:

t = Table('campaigns', x, autoload=True)

t.insert().execute(id='id3', name='row3')




SHOW CREATE TABLE campaigns
{}
describe campaigns
{}
INSERT INTO campaigns (id, name) VALUES (%s, %s)
['row3']('id3',)

[Former user]

Yes, it generates correct queries, the problem is refreshing the python instance using autogenerated columns. Try this:

  CREATE TABLE `camps` (
    `id` varchar(36) NOT NULL default '',
    `tracker_key` int(11) NOT NULL auto_increment,
    `name` varchar(50) default NULL,
    PRIMARY KEY  (`id`),
    KEY `auto_tracker_key` (`tracker_key`)
  );

And then this:

t = Table('camps', dbcrm,
        Column('id', String(36), primary_key=True),
        Column('tracker_key', Integer, nullable=False),
        Column('name', String(50))
        )

class Camps(object):
    pass

assign_mapper(Camps, t)
camp = Camps(id='id2', name='row2')
objectstore.commit()

All looks fine. There is correct row inside a database, but:

camp.id # "id2" - OK
camp.name # "row2" - OK
camp.tracker_key # None - NOT OK

If you try:

camp3 = Camps(name='row3')
objectstore.commit()

camp.id # 3L - NOT OK
camp.name # "row3" - OK
camp.tracker_key # None - NOT OK

It took autogenerated tracker_key and stuffed into id (because it is the PK i guess).

[Mike Bayer]

there are some confusing elements to your test. namely that on the second round, you are not specifying a primary key value. SQLAlchemy then fetches "cursor.lastrowid" which MySQL reports as AUTOINCREMENT that was placed in the non-primary key column, screwing up everything.

since you have no default specified for your primary key column, you must put an explicit value for this.

Also, there is an as-yet undocumented (except on the mailing list) feature for post-fetching db-generated default values.

the working version of the program is as follows:

from sqlalchemy import *

x = create_engine('mysql://db=test&host=127.0.0.1&user=scott&passwd=tiger', echo=True)

# specify PassiveDefault on "tracker_key", indicating that
# SQLAlchemy should post-fetch the row to see what was created by the DB
t = Table('campaigns', x,
         Column('id', String(36), primary_key=True),
         Column('tracker_key', Integer, PassiveDefault("tracker"), nullable=False ),
         Column('name', String(50))
         )

t.delete().execute()

class Camps(object):
        pass

assign_mapper(Camps, t)
camp = Camps(id='id2', name='row2')
objectstore.commit()

# all is good
print camp.id
print camp.name
print camp.tracker_key


# explicit id is *required*, because you have no default set up
# for your "id" column (i.e. no AUTOINCREMENT or anything)
# if you dont put id, MySQL is returning the number from the tracker_key
# as the cursor "lastrowid", giving SQLAlchemy the wrong value to post-select
camp3 = Camps(id="id3",  name='row3')

objectstore.commit()

# print camp3
print camp3.id
print camp3.name
print camp3.tracker_key