Re: [sqlalchemy] Duplicating primary key value into another column upon insert?
On Sep 23, 2013, at 10:04 PM, Ken Lareau klar...@tagged.com wrote: Hopefully this will make sense... I have a database which is in need of some normalization of the column naming in various tables. In an effort to minimize disruption (since this is a live database used by many applications), I'm trying to use a two-step approach: 1) Add a new column that will 'mirror' the current primary key column, then update code to utilize that column once in place 2) Once all code is converted, remove the original primary key column and make the new column the primary key instead. In an effort to minimize change to the code I have currently using my SQLAlchemy model, I'm trying to find a way to deal with this from within the declarative classes themselves. So the questions are: - Is this even possible, and if so, how can it be done? - If not possible, is there a good yet minimally intrusive external change that can be done? My searching through the docs so far hasn't turned up anything useful, I'm afraid... it depends a lot on how you are generating primary key values. how are the new values being generated? (things like database platform affect this dramatically). if using something like MySQL's autoincrement, you probably want to write a trigger that will populate this new column after an INSERT. overall a trigger is probably the best way to get this extra value in the table efficiently. OTOH if your applications know the new primary key value beforehand, then SQLAlchemy events can be used to copy the value into the new column. you also want to be mindful of indexing on this column, as applications move to use the new column, you probably want queries to still use indexes. signature.asc Description: Message signed with OpenPGP using GPGMail
Re: [sqlalchemy] Duplicating primary key value into another column upon insert?
On Tue, Sep 24, 2013 at 7:25 AM, Michael Bayer mike...@zzzcomputing.comwrote: On Sep 23, 2013, at 10:04 PM, Ken Lareau klar...@tagged.com wrote: Hopefully this will make sense... I have a database which is in need of some normalization of the column naming in various tables. In an effort to minimize disruption (since this is a live database used by many applications), I'm trying to use a two-step approach: 1) Add a new column that will 'mirror' the current primary key column, then update code to utilize that column once in place 2) Once all code is converted, remove the original primary key column and make the new column the primary key instead. In an effort to minimize change to the code I have currently using my SQLAlchemy model, I'm trying to find a way to deal with this from within the declarative classes themselves. So the questions are: - Is this even possible, and if so, how can it be done? - If not possible, is there a good yet minimally intrusive external change that can be done? My searching through the docs so far hasn't turned up anything useful, I'm afraid... it depends a lot on how you are generating primary key values. how are the new values being generated? (things like database platform affect this dramatically). if using something like MySQL's autoincrement, you probably want to write a trigger that will populate this new column after an INSERT.overall a trigger is probably the best way to get this extra value in the table efficiently. OTOH if your applications know the new primary key value beforehand, then SQLAlchemy events can be used to copy the value into the new column. you also want to be mindful of indexing on this column, as applications move to use the new column, you probably want queries to still use indexes. The primary key values are indeed using autoincrement in MySQL. The reason for doing this is the database is an existing one over which I've been building a library to be used to programmatically access it, but it's not the only library accessing the database, so to prevent potential breakage with the other libraries, we taking the safe approach. The normalization is due to lovely decisions like having all columns be in lowercase except for the primary key (though sometimes other keys also have the same problem) where we have something like 'PackageID'. The new column is meant to replace the original column, but there will be a small period of time that both columns will exist, so they must remain in sync until all of the current libraries and appli- cations are using the new column. At that point, the new column will take over and hopefully no one will be the wiser that things were wrong before. *smile* As far as I know, none of the applications know the primary key value beforehand, so that isn't a concern. Indexing might be, though the hope is the changeover will be relatively short (matter of a day or so), but I can still index the new file if the impact is great enough (the database isn't _too_ large at this point). Of course, this leads back to the original question... how exactly does one accomplish this? :) Hopefully the clarification above helps some in determining this. -- - Ken Lareau -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] Duplicating primary key value into another column upon insert?
On Sep 24, 2013, at 12:00 PM, Ken Lareau klar...@tagged.com wrote: Of course, this leads back to the original question... how exactly does one accomplish this? :) Hopefully the clarification above helps some in determining this. as I said, build a trigger: http://dev.mysql.com/doc/refman/5.0/en/triggers.html signature.asc Description: Message signed with OpenPGP using GPGMail
Re: [sqlalchemy] Duplicating primary key value into another column upon insert?
On Tue, Sep 24, 2013 at 10:32 AM, Michael Bayer mike...@zzzcomputing.comwrote: On Sep 24, 2013, at 12:00 PM, Ken Lareau klar...@tagged.com wrote: Of course, this leads back to the original question... how exactly does one accomplish this? :) Hopefully the clarification above helps some in determining this. as I said, build a trigger: http://dev.mysql.com/doc/refman/5.0/en/triggers.html Oh, you meant on the database side... d'oh. *slaps hand to forehead* Thanks, will look at this. -- - Ken Lareau -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] Duplicating primary key value into another column upon insert?
On Tue, Sep 24, 2013 at 11:10 AM, Ken Lareau klar...@tagged.com wrote: On Tue, Sep 24, 2013 at 10:32 AM, Michael Bayer mike...@zzzcomputing.comwrote: On Sep 24, 2013, at 12:00 PM, Ken Lareau klar...@tagged.com wrote: Of course, this leads back to the original question... how exactly does one accomplish this? :) Hopefully the clarification above helps some in determining this. as I said, build a trigger: http://dev.mysql.com/doc/refman/5.0/en/triggers.html Oh, you meant on the database side... d'oh. *slaps hand to forehead* Thanks, will look at this. Sadly after over an hour of fighting with MySQL triggers and being unsuccessful at getting any variation to work, I've decided that I'm just going to need to shut down key infrastructure pieces during the column renaming and avoid the temporary new column as it would just complicate my current codebase more than it already is... once again, I do appreciate the help, however. -- - Ken Lareau -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] Duplicating primary key value into another column upon insert?
On Sep 24, 2013, at 5:42 PM, Ken Lareau klar...@tagged.com wrote: On Tue, Sep 24, 2013 at 11:10 AM, Ken Lareau klar...@tagged.com wrote: On Tue, Sep 24, 2013 at 10:32 AM, Michael Bayer mike...@zzzcomputing.com wrote: On Sep 24, 2013, at 12:00 PM, Ken Lareau klar...@tagged.com wrote: Of course, this leads back to the original question... how exactly does one accomplish this? :) Hopefully the clarification above helps some in determining this. as I said, build a trigger: http://dev.mysql.com/doc/refman/5.0/en/triggers.html Oh, you meant on the database side... d'oh. *slaps hand to forehead* Thanks, will look at this. Sadly after over an hour of fighting with MySQL triggers and being unsuccessful at getting any variation to work, I've decided that I'm just going to need to shut down key infrastructure pieces during the column renaming and avoid the temporary new column as it would just complicate my current codebase more than it already is... once again, I do appreciate the help, however. I googled for mysql trigger copy autoincrement and got this: Copy auto-increment value to another column on insert: http://forums.mysql.com/read.php?99,186171,186241#msg-186241 the trigger here makes use of LAST_INSERT_ID() and is worth a shot. signature.asc Description: Message signed with OpenPGP using GPGMail
Re: [sqlalchemy] Duplicating primary key value into another column upon insert?
On Tue, Sep 24, 2013 at 2:53 PM, Michael Bayer mike...@zzzcomputing.comwrote: On Sep 24, 2013, at 5:42 PM, Ken Lareau klar...@tagged.com wrote: On Tue, Sep 24, 2013 at 11:10 AM, Ken Lareau klar...@tagged.com wrote: On Tue, Sep 24, 2013 at 10:32 AM, Michael Bayer mike...@zzzcomputing.com wrote: On Sep 24, 2013, at 12:00 PM, Ken Lareau klar...@tagged.com wrote: Of course, this leads back to the original question... how exactly does one accomplish this? :) Hopefully the clarification above helps some in determining this. as I said, build a trigger: http://dev.mysql.com/doc/refman/5.0/en/triggers.html Oh, you meant on the database side... d'oh. *slaps hand to forehead* Thanks, will look at this. Sadly after over an hour of fighting with MySQL triggers and being unsuccessful at getting any variation to work, I've decided that I'm just going to need to shut down key infrastructure pieces during the column renaming and avoid the temporary new column as it would just complicate my current codebase more than it already is... once again, I do appreciate the help, however. I googled for mysql trigger copy autoincrement and got this: Copy auto-increment value to another column on insert: http://forums.mysql.com/read.php?99,186171,186241#msg-186241 the trigger here makes use of LAST_INSERT_ID() and is worth a shot. Closer, but it requires the main tables to not already have any values in them (if I'm understanding the SQL correctly), and I'm working with existing tables already populated with plenty of data. Honestly, at this point simply renaming the columns is far less painful. :) (And thankfully only needs to be done once.) But thanks for the info, much appreciated. -- - Ken Lareau -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/groups/opt_out.
[sqlalchemy] Duplicating primary key value into another column upon insert?
Hopefully this will make sense... I have a database which is in need of some normalization of the column naming in various tables. In an effort to minimize disruption (since this is a live database used by many applications), I'm trying to use a two-step approach: 1) Add a new column that will 'mirror' the current primary key column, then update code to utilize that column once in place 2) Once all code is converted, remove the original primary key column and make the new column the primary key instead. In an effort to minimize change to the code I have currently using my SQLAlchemy model, I'm trying to find a way to deal with this from within the declarative classes themselves. So the questions are: - Is this even possible, and if so, how can it be done? - If not possible, is there a good yet minimally intrusive external change that can be done? My searching through the docs so far hasn't turned up anything useful, I'm afraid... Thanks in advance. -- - Ken Lareau -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/groups/opt_out.
