This Jupyter notebook is intented to be used alongside the book Python for Bioinformatics
Note: The code in this chapter requires a database servers to run (a MySQL and MongoDB), so you should provide one and then change the appropiate parameters in the connect string. The example with sqlite can run in this Jupyter Notebook.
In [1]:
!curl https://raw.githubusercontent.com/Serulab/Py4Bio/master/samples/samples.tar.bz2 -o samples.tar.bz2
!mkdir samples
!tar xvfj samples.tar.bz2 -C samples
In [2]:
!wget https://raw.githubusercontent.com/Serulab/Py4Bio/master/code/ch12/PythonU.sql
!apt-get -y install mysql-server
!/etc/init.d/mysql start
!mysql -e 'create database PythonU;'
!mysql PythonU < PythonU.sql
!mysql -e "UPDATE mysql.user SET authentication_string=password('mypassword'),host='%',plugin='mysql_native_password' WHERE user='root';flush privileges;"
In [3]:
!pip install PyMySQL
In [0]:
import pymysql
db = pymysql.connect(host="localhost", user="root", passwd="mypassword", db="PythonU")
In [0]:
cursor = db.cursor()
In [6]:
cursor.execute("SELECT * FROM Students")
Out[6]:
In [7]:
cursor.fetchone()
Out[7]:
In [8]:
cursor.fetchone()
Out[8]:
In [9]:
cursor.fetchone()
Out[9]:
In [10]:
cursor.fetchall()
Out[10]:
Listing 12.1: pymysql1.py: Reading results once at a time
In [11]:
!/etc/init.d/mysql stop
In [0]:
get_ipython().system_raw('mysqld_safe --skip-grant-tables &')
In [0]:
!mysql -e "UPDATE mysql.user SET authentication_string=password('secret'),host='%',plugin='mysql_native_password' WHERE user='root';flush privileges;"
In [14]:
import pymysql
db = pymysql.connect(host='localhost',
user='root', passwd='secret', db='PythonU')
cursor = db.cursor()
recs = cursor.execute('SELECT * FROM Students')
for x in range(recs):
print(cursor.fetchone())
Listing 12.2: pymysql2.py: Iterating directly over the DB cursor
In [15]:
import pymysql
db = pymysql.connect(host='localhost',
user='root', passwd='secret', db='PythonU')
cursor = db.cursor()
cursor.execute('SELECT * FROM Students')
for row in cursor:
print(row)
Listing 12.3: sqlite1.py: Same script as 12.2, but with SQLite
In [16]:
import sqlite3
db = sqlite3.connect('samples/PythonU.db')
cursor = db.cursor()
cursor.execute('Select * from Students')
for row in cursor:
print(row)
In [17]:
!apt install mongodb
In [18]:
!/etc/init.d/mongodb start
In [0]:
from pymongo import MongoClient
In [0]:
from pymongo import MongoClient
client = MongoClient('localhost:27017')
In [21]:
client.list_database_names()
Out[21]:
In [0]:
db = client.PythonU
In [23]:
client.list_database_names()
Out[23]:
In [0]:
client.drop_database('Employee')
In [0]:
students = db.Students
In [0]:
student_1 = {'Name':'Harry', 'LastName':'Wilkinson',
'DateJoined':'2016-02-10', 'OutstandingBalance':False,
'Courses':[('Python 101', 7, '2016/1'), ('Mathematics for CS',
8, '2016/1')]}
In [0]:
student_2 = {'Name':'Jonathan', 'LastName':'Hunt',
'DateJoined':'2014-02-16', 'OutstandingBalance':False,
'Courses':[('Python 101', 6, '2016/1'), ('Mathematics for CS',
9, '2015/2')]}
In [28]:
students.count()
Out[28]:
In [29]:
students.insert(student_1)
Out[29]:
In [30]:
students.insert(student_2)
Out[30]:
In [31]:
students.count()
Out[31]:
In [32]:
from bson.objectid import ObjectId
search_id = {'_id':ObjectId('5ed902d980378228f849a40d')}
my_student = students.find_one(search_id)
my_student['LastName']
Out[32]:
In [33]:
my_student['_id'].generation_time
Out[33]:
In [34]:
for student in students.find():
print(student['Name'], student['LastName'])
In [35]:
list(students.find())
Out[35]:
In [0]: