Autarchy of the Private Cave

Tiny bits of bioinformatics, [web-]programming etc

    MySQL: INSERT IF NOT EXISTS syntax

    18th October 2007

    To start: as of the latest MySQL, syntax presented in the title is not possible. But there are several very easy ways to accomplish what is expected using existing functionality.

    There are 3 possible solutions: using INSERT IGNORE, REPLACE, or INSERT … ON DUPLICATE KEY UPDATE.

    Imagine we have a table:

    1. CREATE TABLE `transcripts` (
    2.  `ensembl_transcript_id` varchar(20) NOT NULL,
    3.  `transcript_chrom_start` int(10) unsigned NOT NULL,
    4.  `transcript_chrom_end` int(10) unsigned NOT NULL,
    5.  PRIMARY KEY  (`ensembl_transcript_id`)
    6. ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

    Now imagine that we have an automatic pipeline importing transcripts meta-data from Ensembl, and that due to various reasons the pipeline might be broken at any step of execution. Thus, we need to ensure two things: 1) repeated executions of the pipeline will not destroy our database, and 2) repeated executions will not die due to ‘duplicate primary key’ errors.

    Method 1: using REPLACE
    Read the rest of this entry »

    Share

    Posted in Programming | 46 Comments »

    MySQL – Python: good MySQLdb tutorial (examples)

    7th September 2007

    Andy Dustman (used to blog at dustman.net) gave a presentation on Python and MySQL at the MySQL Users Conference 2005, Santa Clara, CA. The presentation is an excellent collection of examples for those who use the MySQLdb Python module.
    Read the rest of this entry »

    Share

    Posted in Links, Notepad, Programming, Python | No Comments »