Solved

phpMyAdmin and column addition

Posted on 2009-07-07
4
562 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
  • 2
4 Comments
 
LVL 7

Accepted Solution

by:
Fairlight2cx earned 500 total points
Comment Utility
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
Comment Utility
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
Comment Utility
Yup.  that'd give you a 20char max field.
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

As a database administrator, you may need to audit your table(s) to determine whether the data types are optimal for your real-world data needs.  This Article is intended to be a resource for such a task. Preface The other day, I was involved …
Nothing in an HTTP request can be trusted, including HTTP headers and form data.  A form token is a tool that can be used to guard against request forgeries (CSRF).  This article shows an improved approach to form tokens, making it more difficult to…
The viewer will learn how to count occurrences of each item in an array.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.

763 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

Need Help in Real-Time?

Connect with top rated Experts

6 Experts available now in Live!

Get 1:1 Help Now