Solved

phpMyAdmin and column addition

Posted on 2009-07-07
4
569 Views
Last Modified: 2012-05-07
Hi, i currently have a script (see snippet below)...

The script is currently returning the following error message:

bash-3.2# perl stub_uniprot_interpro.pl
DBD::mysql::st execute failed: Unknown column 'domain_type' in 'field list' at stub_uniprot_interpro.pl line 122, <GEN0> line 652.
could not enter data at stub_uniprot_interpro.pl line 122, <GEN0> line 652.


So i'm missing the 'domain_type' column.

I want to open up phpMyAdmin and add a new column... named the same as in the script error 'domain_type' and sync it with the script above to clear the error message.

Thanks.

Stephen.

I was wondering if anybody could help me with this as i haven't used phpMyAdmin before. I'm not even sure if i have it installed...

So i need to manually sync the column into
#!/usr/bin/perl
 
# Stephen McGowan
 
use strict;
use warnings;
 
use Bio::SeqIO;
use Data::Dumper;
use DBI;
use DBD::mysql;
use mysql;
 
# The point of this script is to grab some useful identifiers from a SwissProt .dat file, and pass
# them to a MySQL database with the BioSQL schema.
# These identifiers are UniProt name, Uniprot accession number, InterPro accession number, InterPro domain type.
# The standard script load_seqdatabase.pl does not fetch the InterPro domain name.
 
 
# connect to file and create object
 
my $file = 'load_seqdb_test_dat.txt';
my $DEBUG = 0;
# my $file = 'YEAST.dat';
my $inseq = Bio::SeqIO->new(-file   => "<$file", -format => "swiss");
my @parsed_entries = (); # storage for fields of interest
 
 
# connect to mysql db, localhost
 
my $dbh = DBI->connect('DBI:mysql:biosql:host=localhost:port=3306','root') or die "Could not connect.";
 
# get primary sequence tags
 
while ( my $seq = $inseq->next_seq ) {
        my $protein_ids = {
                uniprot_number => $seq->accession_number,
                uniprot_id         => $seq->id,
                interpro       => [],   # the InterPro refs are in the dblink annotations, which is in an array of hashes, accessed below
        };                                                      # this array will hold an anonymous reference to those hashes
 
        # Pull the dblink annotations from the file
        my @annotations_dblink = $seq->annotation->get_Annotations( 'dblink' );
 
        foreach my $annotation ( @annotations_dblink ) {
                my @dblinks = $annotation->hash_tree();
 
                foreach my $dblink ( @dblinks ) {
 
                        if ( $dblink->{database} eq 'InterPro' ) {  # Here the is the InterPro hash
                                push @{$protein_ids->{interpro}}, {             # We pass the reference to the array
                                        entry  => $dblink->{primary_id},        # using the keys of entry or domain to
                                        domain => $dblink->{optional_id},   # hold the id values
                                }
                        }
            }
        }
        push @parsed_entries, $protein_ids;     # moving the data to an array for passing below
        print Dumper $protein_ids if $DEBUG;    # Debug
        last if scalar @parsed_entries == 500;  # an bad character in the file located between 500 and 1000 lines in, needs troubleshooting
}
 
 
########## Enter data in MySQL ##############
 
my $bdb_biodatabase_id = '1';
my $bdb_name = "SwissProt";
 
$dbh->do("truncate dbxref");
$dbh->do("truncate bioentry");
$dbh->do("truncate biodatabase");
 
my $sth = $dbh->prepare("insert into biodatabase(biodatabase_id, name)
values ('$bdb_biodatabase_id', '$bdb_name')");
 
$sth->execute() or die "could not enter data";
 
 
# bioentry table
 
my $be_biodatabase_id = 1;      # must agree with DB or DBD::mysql::st execute failed: Cannot add or update a child row: a foreign key constraint fails
my $be_bioentry_id = '';        # must be unique or produces the error: DBD::mysql::st execute failed: Duplicate entry
my $be_taxon_id = '';           # must agree with DB or DBD::mysql::st execute failed: Cannot add or update a child row: a foreign key constraint fails
my $be_name = '';                       # not essential, but if empty can cause trouble in retrieval
my $be_accession = '';          # if this is duplicate, produces the error: DBD::mysql::st execute failed: Duplicate entry
# my $be_identifier = '';       # must be unique or produces the error: DBD::mysql::st execute failed: Duplicate entry, set to $be_bioentry_id below
# my $be_division = '';         # not essential, and not used here
# my $be_description = '';      # not essential, and not used here
my $be_version = '';            # not essential
 
 
# dbxref table
 
my $dbx_dbxref_id = '';
my $dbx_dbname = 'InterPro';
my $dbx_accession = '';
my $dbx_domain_type = '';  # new field addeded manual to BioSQL schema to hold the InterPro domain type
my $dbx_version = '';      # used in info.php to bind bioentry to dbxref with a common numerical entry, see below dbxref for loop
                                
# pass the extracted fields to the appropriate tables in BioSQL
                                        
for ( my $i=0; $i < @parsed_entries; $i++ ){
        $be_bioentry_id = $i + 1;
        $be_name = $parsed_entries[$i] {uniprot_id};
        print "$be_name\n" if $DEBUG;
        $be_accession = $parsed_entries[$i] {uniprot_number};
        print "$be_accession\n" if $DEBUG;
        
        $sth = $dbh->prepare("insert into bioentry(bioentry_id, biodatabase_id, name, accession, identifier, version)
        values ('$be_bioentry_id', '$be_biodatabase_id', '$be_name', '$be_accession', '$be_bioentry_id', '$be_version')");
 
        $sth->execute() or die "could not enter data";
 
        for ( my $j = 0; $j < @{$parsed_entries[$i]->{interpro}}; $j++ ) {
                $dbx_accession = $parsed_entries[$i]{interpro}->[$j]->{entry};
                print "$dbx_accession\n" if $DEBUG;
                $dbx_domain_type = $parsed_entries[$i]{interpro}->[$j]->{domain};
                print "$dbx_domain_type\n" if $DEBUG;
 
                $sth = $dbh->prepare("insert into dbxref(dbxref_id, dbname, accession, domain_type, version)
                values (NULL, '$dbx_dbname', '$dbx_accession', '$dbx_domain_type', '$be_bioentry_id')");
 
                $sth->execute() or die "could not enter data";
        }
}
exit;

Open in new window

0
Comment
Question by:StephenMcGowan
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 7

Accepted Solution

by:
Fairlight2cx earned 500 total points
ID: 24800325
Okay, I don't know what data type domain_type needs to be, or I'd give you -exact- syntax.  But forget phpMyAdmin.  I know you've got the mysql command line client handy, as you used it earlier today in another question.

Here's how you'd add a column to that table.

$ mysql
mysql> use biosql;
mysql> alter table dbxref add domain_type varchar(20) after accession;

This is going off memory that the column name in the actual table was named just "accession", from the other question this morning.  The reason we're putting in the "after accession" is so that domain_type is inserted into the right order, so your arguments in the calls against that table for the INSERT actually match up and you don't have to re-order the variables in the code.  Normally it would just be added last.  This fixes that issue.

Change varchar(20) to whatever data type you need in the above definition.
0
 

Author Comment

by:StephenMcGowan
ID: 24807776
the data type i think will be "varchar", so would the sytax stay the same?

i.e.

$ mysql
mysql> use biosql;
mysql> alter table dbxref add domain_type varchar(20) after accession;

?

Thanks again FairL

Ste.
0
 
LVL 7

Expert Comment

by:Fairlight2cx
ID: 24815371
Yup.  that'd give you a 20char max field.
0

Featured Post

Secure Your WordPress Site: 5 Essential Approaches

WordPress is the web's most popular CMS, but its dominance also makes it a target for attackers. Our eBook will show you how to:

Prevent costly exploits of core and plugin vulnerabilities
Repel automated attacks
Lock down your dashboard, secure your code, and protect your users

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
The viewer will learn how to count occurrences of each item in an array.

623 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question