Database and programming languages PHP,Python,Perl,Java

1
Database and programming languages
(PHP,Python,Perl,Java)
Support and Service, Bioinformatics Lab.,
CRS4 December 2006
Joel Masciocchi
2
Properties of the different languages

• Perl (Practical Extraction and Report Language)
• Used by Web programmers to create scripts for Web
  servers
• Used by programmers to parse a text file (
  reading and searching using regular expression)
• Syntax simliar to C/C
  
  

3

• PHP (Hypertext Preprocessor)
• Create dynamic Web pages
• Similar syntax to Perl or C
• Python
• Object-oriented programming language
• Language similar to Perl
• 
  

4

• Java
• Object-oriented programming language
• Create complete applications on a single
  computer or distributed among servers and clients
  in a network
• build a small application module or applet for
  use as part of a Web page
• Syntax similar to C but simplified

5
SQL request

• select
• term.name,dbxref.xref_dbname,dbxref.xref_key
• from term ,term_dbxref,dbxref
• where dbxref.idterm_dbxref.dbxref_id and
• term_dbxref.term_idterm.id and
• dbxref.xref_dbname'PROSITE' and
• term.name'rhodopsin-like receptor activity'and
• term.acc'GO0001584'

6
SQL Result

• name xref_dbname
  xref_key
• -----------------------------------------------
  ------------------------------
• rhodopsin-like receptor activity PROSITE
  PS00237 G_PROTEIN_RECEP_F1_1
• rhodopsin-like receptor activity PROSITE
  PS00238 OPSIN
• rhodopsin-like receptor activity PROSITE
  PS50262 G_PROTEIN_RECEP_F1_2

7
Architecture of a script that interacts with a
database
Connection
Build the query
Print the result using a loop
8
Access at GO database with Python

• !/usr/bin/python
• import MySQLdb
• dbMySQLdb.connect(host"localhost",
• userroot", passwd"", dbgo")
• cursor db.cursor()

Path of Python Programm
Import MySQL module
Connect to go
Create a cursor
9
Execute SQL statement

• cursor.execute("select term.name,
  dbxref.xref_dbname, dbxref.xref_key from term ,
  term_dbxref, dbxref where dbxref.idterm_dbxref.d
  bxref_id and term_dbxref.term_idterm.id and
  dbxref.xref_dbname'PROSITE' and term.name'
  rhodopsin-like receptor activity and
  term.acc'GO0001584'")
• result cursor.fetchall()
• For record in result
• print record

Get the resultset as a tuple
Iterate through resultset
10
Python result

• ('rhodopsin-like receptor activity', 'PROSITE',
  'PS00237 G_PROTEIN_RECEP_F1_1')
• ('rhodopsin-like receptor activity', 'PROSITE',
  'PS00238 OPSIN')
• ('rhodopsin-like receptor activity', 'PROSITE',
  'PS50262 G_PROTEIN_RECEP_F1_2')

11
Access at GO database with the others languages
Perl
Connection

• !/usr/bin/env perl
• use strict
• use DBI
• my dbh DBI-gtconnect( 'dbimysqlgo', 'root',
  '',
• RaiseError gt 1,
• AutoCommit gt 0
• ) die "Database connection not made
  DBIerrstr"

12
my sth dbh-gtprepare("select
term.name,dbxref.xref_key,dbxref.xref_dbname from
term ,term_dbxref,dbxref where
dbxref.idterm_dbxref.dbxref_id and
term_dbxref.term_idterm.id and
dbxref.xref_dbname'PROSITE' and term.name
'rhodopsin-like receptor activity' and term.acc
'GO0001584'")
Build the query
13
sth-gtexecute()my( term_name, acc_number,
dbname)sth-gtbind_columns( undef, \term_name,
\acc_number, \dbname )while( sth-gtfetch() )
print " term_name, acc_number,
dbname\n"sth-gtfinish() dbh-gtdisconnect()

Print the result using a loop
14
PHP
Connection

• lt?phpif (!link mysql_connect('localhost',
  'root', '' ,go))    echo 'Could not connect to
  mysql'   exitif (!mysql_select_db('go'))
     echo 'Could not select database'  
  exit

15
query    "select term.name,dbxref.xref_key,dbxr
ef.xref_dbname from term ,term_dbxref,dbxref
where dbxref.idterm_dbxref.dbxref_id and
term_dbxref.term_idterm.id and
dbxref.xref_dbname'PROSITE' and term.name
'rhodopsin-like receptor activity' and term.acc
'GO0001584'"result mysql_query(query)
Build the query
16
Print the result using a loop

• if (!result)    echo "DB Error, could not
  query the database\n"   echo 'MySQL Error ' .
  mysql_error()   exitwhile (row
  mysql_fetch_assoc(result))    echo
  rowname' echo rowxref_dbname'
• echo rowxref_key'
• ?gt

17
JAVA
Connection

• import java.sql.
• class testsql
•     public static void main(String args)
          
•   String pilote "com.mysql.jdbc.Driver"      
   
•         try
•             Class.forName(pilote)
•            Connection connexion
  DriverManager.getConnection("jdbcmysql//localhos
  t/go","root","")
•          Statement instruction
  connexion.createStatement()     
•                       

18
ResultSet resultat instruction.executeQuery("sel
ect term.name, dbxref.xref_dbname,
dbxref.xref_key from term , term_dbxref, dbxref
where dbxref.idterm_dbxref.dbxref_id and
term_dbxref.term_idterm.id and
dbxref.xref_dbname'PROSITE' and
term.name'rhodopsin-like receptor activity' and
term.acc'GO0001584'")
Build the query
19
Print the result using a loop

• while(resultat.next())                 
•                 System.out.println(GOterm"resu
  ltat.getString(term.name"))
•                 System.out.println(Prosite
  Accession number "resultat.getString(dbxref.xre
  f_key"))
•                 System.out.println(Dbname
  "resultat.getString(dbxref.xref_dbname"))
•                                         
•         

20
Interest to using programming languages

• Create a Web interface for users using HTML and
  PHP for example
• Create a script that parses a entire file and
  return the result on the screen or in a File
  (txt, csv, ..)

21
Web Interface developed in PHP

• You need mysql
• You need to compile php with mysql support
• Create a php file script

22
(No Transcript)
23
rhodopsin-like receptor activity_POSTname
GO0001584 _POSTacc
24
(No Transcript)
25
Portion of the flat file Pfam2go

• PfamPF00001 7tm_1 gt GOrhodopsin-like receptor
• activity GO0001584
• PfamPF00001 7tm_1 gt GOG-protein coupled
• Receptor protein signaling pathway GO0007186
• PfamPF00001 7tm_1 gt GOintegral to membrane
• GO0016021
• PfamPF00002 7tm_2 gt GOG-protein coupled
• receptor activity GO0004930
• PfamPF00002 7tm_2 gt GOmembrane
• GO0016020

26
Python script

• !/usr/bin/python
• import MySQL module
• import MySQLdb
• connect
• db MySQLdb.connect(host"localhost",
  user"root", passwd"", db "go")
• create a cursor
• cursor db.cursor()

27

• open the file to read it
• input_file open("./Workshop_DB/pfam2go.txt","r")
  
• open the file to write in
• output_file open("./Workshop_DB/results.txt","w"
  )
• function readlines() read the file line after
  line
• fileList input_file.readlines()

28

• for lineStr in fileList
• recuperate the GO name
• namelineStr.split('gt GO')1.split(' ')0
• recuperate the GO id
• acclineStr.split('')11-1
• cursor.execute("select term.name,dbxref.xref_dbna
  me,dbxref.xref_key from term ,term_dbxref,dbxref
  where dbxref.idterm_dbxref.dbxref_id and
  term_dbxref.term_idterm.id and
  dbxref.xref_dbname'PROSITE' and
  term.name'"name"' and term.acc'"acc"'")

29

• result cursor.fetchall()
• print the result into a file txt
• for record in result
• output_file.write( "\t".join(record)"\n" )
• close the file
• input_file.close()
• output_file.close()

30
View of the file result.txt

• rhodopsin-like receptor activity PROSITE
• PS00237 G_PROTEIN_RECEP_F1_1
• rhodopsin-like receptor activity PROSITE
• PS00238 OPSIN
• rhodopsin-like receptor activity PROSITE
• PS50262 G_PROTEIN_RECEP_F1_2
• G-protein coupled receptor protein signaling
• Pathway PROSITE PS00237
• G_PROTEIN_RECEP_F1_1
• G-protein coupled receptor protein signaling
• Pathway PROSITE PS50058 G_PROTEIN_GAMMA