List:MySQL and Perl« Previous MessageNext Message »
From:francesca casalino Date:March 30 2011 3:08pm
Subject:Re: MySQL queries in a relational database
View as plain text  
Hi and thank you for your replies and suggestions.

I realise I was not clear at all, sorry!

The problem I have with both of my questions is actually that I don't really
know how to do it and whether I am approaching the problems correctly (well,
just the first problem, for the second I don't even have ideas...)

I can get the counts of the alleles for each variation, and the counts of
the minor allele, but I do not know how to store and retrieve the values for
both of these select statements, in order to calculate the frequency (for
each variation_id) and insert this frequency in another MySQL table...

Here is what I can do so far:

#count number of alleles entered for each variation: This gives me 2
columns: one with the variation_id and one with the count
my $sth =$dbh->prepare("SELECT allele.variation_id,
count(genotype.allele_id)
from allele, genotype
where allele.allele_id = genotype.allele_id
group by allele.variation_id");
$sth->execute();
my (@allele_count);
while (@allele_count = $sth->fetchrow_array()) {
print join (",", @allele_count), "\n";
}
$sth->finish;

#Count the number of samples that have alternate (minor) allele: This gives
me two columns: one with the variation_id and one with the minor allele
counts

my $sth =$dbh->prepare("SELECT allele.variation_id,
count(genotype.allele_id)
from allele, genotype where allele.allele_id = genotype.allele_id and
allele.reference=1

group by allele.variation_id");
$sth->execute();
my (@min_allele_count);
while (@min_allele_count = $sth->fetchrow_array()) {
print join (",", @min_allele_count), "\n";
}
$sth->finish;


I then need to divide min_allele_count by allele_count to get the frequency,
but as it is I cannot get these values because they are not stored...I hope
this helps explaining 1) ....

Thank you for any suggestions..
-francy


2011/3/30 Michael R Boudreau <mboudreau@stripped>

>  Hi Francesca,
>
> Given the sample tables below, your query should return a single row
> (‘1’,
> ‘1’). You’ll need to describe in more detail what you mean by
> “it does not
> work”. In particular, I’d recommend adding some code to collect error
> messages, e.g.,
>
> my $sth = $dbh->prepare(“...”)
>     or print “Cannot prepare query: $DBI::errstr\n”;
>
> $sth->execute
>    or print “Cannot execute query: $DBI::errstr\n”;
>
>
> Here are some books that I have found helpful for learning MySQL and the
> Perl DBI:
>
> Alligator Descartes and Tim Bunce, *Programming the Perl DBI*. O’Reilly,
> 2000.
>
> Paul DuBois, *MySQL*. 4th edition. Addison-Wesley, 2009.
>
> Paul DuBois, *MySQL Cookbook*. 2nd edition. O’Reilly, 2007.
>
>
>
>
>
>
> On 3/30/11 5:58 AM, "francesca casalino" <francy.casalino@stripped>
> wrote:
>
> Dear Perl and MySQL gurus,
>
>
>
> I have been learning a lot from you for the past month, and, learning from
> your comments and with your help, I was able to build a database on MySQL
> through Perl DBI.
>
>
>
> I am now having trouble with generating a query in Perl DBI to take data
> from this database and insert it into another table in MySQL; I am still
> just at the start in learning both MySQL and Perl, and sorry if this is a
> simple question again, but I have been stuck on this for a while now…if you
> have any advice on how to tackle this please let me know.
>
>
>
> My database is constructed with different tables connected to each other
> through foreign keys. This is an example of the data that I am having
> trouble with (primary and foreign keys are specified in parenthesis, and
> foreign keys refer to another table in the database). I used ENGINE=INNODB
> for constructing the database, and MySQL version 5.1.
>
>
>
>
>
> *Allele*
>
> Allele_id (Primary key)            Variation_id (Foreign key)        Allele
>             Reference
>
> 12                                            1
> A                     0
>
> 13                                            1
> G                     1
>
>
>
> *Genotype*
>
> Genotype_id (Primary key)       Sample_id (Foreign key)           Allele_id
> (Foreign key)
>
> 1                                              Sample1
>             12
>
> 2                                              Sample1
> 13
>
>
>
>
>
>
>
> I am trying to:
>
> 1) Fill in another table which contains frequencies for each of these
> alleles, for each variation, and
>
> 2) Find a way to select and count the samples that have allele.reference =
> 0
> and 0 for each variation and store them in one group, and the ones that
> have
> allele.reference = 0 and 1 in another group, and the ones that have
> allele.reference = 1 and 1 ina athird group.
>
>
>
> For 1) I have solved the counts that I will need for the frequency
> calculations on MySQL, but for some reason it does not work when I try the
> first of these queries on Perl. The Mysql is this:
>
>
>
> #count number of alleles entered for each variation:
>
> select allele.variation_id, count(genotype.allele_id)
>
> from allele, genotype
>
> where allele.allele_id = genotype.allele_id
>
> group by allele.variation_id;
>
>
>
> #Count the number of samples that have reference=0 for each variation:
>
> select allele.variation_id, count(genotype.allele_id)
>
> from allele, genotype
>
> where allele.allele_id = genotype.allele_id and allele.reference=0
>
> group by allele.variation_id;
>
>
>
> When I try this in Perl it does not work…
>
> #count number of alleles entered for each variation:
>
> my $sth =$dbh->prepare("SELECT allele.variation_id,
> count(genotype.allele_id)
>
> from allele, genotype
>
> where allele.allele_id = genotype.allele_id
>
> group by allele.variation_id");
>
> $sth->execute();
>
> my (@allele);
>
> while (@allele = $sth->fetchrow_array()) {
>
> print @allele."\n";
>
> }
>
>
>
> 2) I really have no idea how to record the values of the successive entries
> of “reference” grouped by variation and sample_id… I have been
> looking
> through MySQL tutorials, but I really need to understand this better to
> know
> how to approach this…
>
>
>
> #Count the number of samples that have reference =0 for both entries for
> each variation
>
> select allele.variation_id, count(genotype.sample_id)
>
> from allele JOIN genotype ON allele.allele_id = genotype.allele_id
>
> group by allele.variation_id, genotype.sample_id;
>
>
>
>
>
> Thank you VERY VERY much for any help/suggestions, or any books/tutorials
> that I could look at to understand how to solve these problems…
>
>
>
> -francesca
>
>
> --
> Michael R. Boudreau
> Senior Publishing Technology Analyst
> The University of Chicago Press
> 1427 E. 60th Street
> Chicago, IL 60637
> (773) 753-3298  fax: (773) 753-3383
>
>

Thread
MySQL queries in a relational databasefrancesca casalino30 Mar
  • Re: MySQL queries in a relational databaseMichael R Boudreau30 Mar
    • Re: MySQL queries in a relational databasefrancesca casalino30 Mar
  • Re: MySQL queries in a relational databaseDarren Duncan30 Mar
    • Re: MySQL queries in a relational databasefrancesca casalino31 Mar
      • Re: MySQL queries in a relational databaseDarren Duncan31 Mar
        • Re: MySQL queries in a relational databasefrancesca casalino31 Mar