Избегайте дублирования первичного ключа DBI с существующим оператором

Я хочу вставить информацию из файла FASTA в таблицу в базе данных MySQL. Я использовал Ensembl_id столбец в качестве первичного ключа.

Некоторые из моих Ensembl_id не являются уникальными, поэтому я попытался использовать exists Оператор, чтобы преодолеть эту проблему. Но только 5 строк были вставлены в таблицу, и среди них только один с дубликатом Ensembl_id ценности.

#!/usr/bin/perl -w

#usage script.pl <username> <password> <database_name> <mouse_genes> <mouse_transcripts>

use DBI;
use Data::Dumper;

my $user              = shift @ARGV or die $!;
my $password          = shift @ARGV or die $!;
my $database          = shift @ARGV or die $!;
my $mouse_genes       = shift @ARGV or die $!;
my $mouse_transcripts = shift @ARGV or die $!;

my $dbh = DBI->connect( "dbi:mysql:$database:localhost", "$user", "$password",
    { RaiseError => 1 } );
my %gene;

$/ = "\n>";

open( FILE, "gzip -d -c /data.dash/class2016/student/Mus_musculus.GRCm38.cdna.all.fa.gz |" )
        or die $!;

LOOP:
while ( <FILE> ) {

    my $line = $_;
    chomp $line;

    if ( $line =~ /[a-z]/ ) {

        my @array = split( "\t", $line );

        if ( m/gene:(\w+\d+\.\w+)/ ) {

            my $Ensembl_id = $1;

            if ( !exists $gene{$Ensembl_id} ) {
                $gene{$Ensembl_id} = 1;
            }
            else {
                next;
            }

            if ( m/gene_biotype:(\w+)/ ) {

                my $gene_biotype = $1;

                if ( m/gene_symbol:(\w+\D\d+)/ ) {

                    my $gene_symbol = $1;

                    if ( m/description:(\w+\s+\w+\s+\w+\s+)/ ) {

                        my $gene_description = $1;

                        if ( m/MGI:(\d+)/ ) {

                            my $MGI_accession = $1;
                            my $sth           = $dbh->prepare(
                                qq{insert into $mouse_genes (Ensembl_id,gene_biotype,gene_symbol,gene_description,MGI_accession) values ("$Ensembl_id","$gene_biotype","$gene_symbol","$gene_description","$MGI_accession")}
                            );
                            $sth->execute();
                            $sth->finish();

                            next LOOP;
                        }
                    }
                }
            }
        }
    }
}

close FILE;

$dbh->disconnect();

Как я могу использовать exists оператор для перехода на следующую строку файла, если первичный ключ $Ensembl_id дублируется?

Источник

2 ответа

Решение

Я думал, что видел вопрос, очень похожий на этот, но я не могу найти его

Решение состоит в том, чтобы забыть о хешах и использовать IGNORE ключевое слово, чтобы избежать возникновения ошибки. Документация MySQL говорит это:

Если вы используете ключевое слово IGNORE, ошибки, возникающие при выполнении оператора INSERT, игнорируются. Например, без IGNORE строка, которая дублирует существующий индекс UNIQUE или значение PRIMARY KEY в таблице, вызывает ошибку дубликата ключа, и оператор прерывается. При IGNORE строка отбрасывается, и ошибки не возникает. Игнорируемые ошибки могут вместо этого генерировать предупреждения, а ошибки с дублирующим ключом - нет.

Вы также должны использовать заполнители в вашем SQL-выражении, поэтому оно должно выглядеть так:

Обратите внимание, что END_SQL должен появляться без пробелов до или после него. Возможно, вы захотите определить оператор SQL в верхней части вашей программы, чтобы не испортить отступ

my $sth = $dbh->prepare(<<END_SQL);
INSERT IGNORE INTO $mouse_genes (
    Ensembl_id,
    gene_biotype,
    gene_symbol,
    gene_description,
    MGI_accession
)
VALUES ( ?, ?, ?, ?, ? )
END_SQL

$sth->execute($Ensembl_id, $gene_biotype, $gene_symbol, $gene_description, $MGI_accession);


Обновить

Ваша программа может быть приведена в порядок, чтобы ее было легче читать. Вот как бы я это написал

#!/usr/bin/perl

use strict;
use warnings 'all';

# usage script.pl <username> <password> <database_name> <mouse_genes> <mouse_transcripts>

use DBI;

my $user              = shift @ARGV or die $!;
my $password          = shift @ARGV or die $!;
my $database          = shift @ARGV or die $!;
my $mouse_genes       = shift @ARGV or die $!;
my $mouse_transcripts = shift @ARGV or die $!; # Not used at present

my $dbh = DBI->connect( "dbi:mysql:$database:localhost", $user, $password,
        { RaiseError => 1, PrintError => 0 } );

my $sth = $dbh->prepare( <<END_SQL );
INSERT IGNORE INTO $mouse_genes (
    Ensembl_id,
    gene_biotype,
    gene_symbol,
    gene_description,
    MGI_accession
)
VALUES ( ?, ?, ?, ?, ? )
END_SQL

my $cmd = 'gzip -d -c /data.dash/class2016/student/Mus_musculus.GRCm38.cdna.all.fa.gz';

open my $cmd_fh, '-|', $cmd or die $!;

$/ = "\n>";

while ( <$cmd_fh> ) {

    next unless my ( $ensembl_id )       = /gene:(\w+\d+\.\w+)/;
    next unless my ( $gene_biotype )     = /gene_biotype:(\w+)/;
    next unless my ( $gene_symbol )      = /gene_symbol:(\w+\D\d+)/;
    next unless my ( $gene_description ) = /description:(\w+\s+\w+\s+\w+)\s/;
    next unless my ( $mgi_accession )    = /MGI:(\d+)/;

    $sth->execute( $ensembl_id, $gene_biotype, $gene_symbol, $gene_description, $mgi_accession );
}

$dbh->disconnect;
Источник

Я выясняю, как побороть дублированные ключи, используя хэш:

#!/usr/bin/perl -w

#this script inserts sequences from Mus_musculus.GRCm38.cdna.all.fa.gz into mouse_genes table
#usage lab5_2.pl <username> <password> <database_name> <mouse_genes> <mouse_transcripts>

use DBI;

use Data::Dumper;

my $user = shift @ARGV or die $!;
my $password = shift @ARGV or die $!;
my $database = shift @ARGV or die $!;
my $mouse_genes = shift @ARGV or die $!;

my $dbh = DBI->connect("dbi:mysql:$database:localhost",
                       "$user",
                       "$password",
                       {RaiseError => 1}
                       );
my %gene;

$/ = "\n>";

open (FILE, "gzip -d -c /data.dash/class2016/student/Mus_musculus.GRCm38.cdna.all.fa.gz |") or die $!;

 LOOP: while (<FILE>) {
     if (m/gene:(\w+\d+\.\d+)/) {
         my $Ensembl_id = $1;
         if ( !exists $gene{$Ensembl_id} ) {
             $gene{$Ensembl_id} = 1;
             if (m/gene_biotype:(\w+)/) {
                 my $gene_biotype = $1;
                 my $gene_symbol;
                 if (m/gene_symbol:(\w+\D\d+)/) {
                     $gene_symbol = $1;
                 }
                 if (! defined $gene_symbol) {
                     $gene_symbol = "NULL";
                 }
                 if (m/description:([^\[]*)/) {
                     my $gene_description = $1;
                     if (m/MGI:(\d+)/) {
                         my $MGI_accession = $1;
                         $sth = $dbh->prepare (qq{insert into mouse_genes (Ensembl_id, gene_biotype, gene_symbol, gene_description, MGI_accession) values ("$Ensembl_id","$gene_biotype","$gene_symbol","$gene_description","$MGI_accession")});
                         $sth->execute();
                         $sth->finish();
                         next LOOP;
                     }
                 }
             }
close FILE;
$dbh->disconnect ();
Источник
Другие вопросы по тегам