In [ ]:

    

# let's create a testing database
# CREATE DATABASE IF NOT EXISTS mod_mysqldb DEFAULT CHARACTER SET 'UTF8' DEFAULT COLLATE 'UTF8_GENERAL_CI';
# GRANT ALL PRIVILEGES ON mod_mysqldb.* TO 'user'@'localhost' IDENTIFIED BY 'user';


    

In [ ]:

    

# let's connect to our database
import MySQLdb as mysql

conn = mysql.connect('localhost', 'user', 'user', 'mod_mysqldb')
cursor = conn.cursor()


    

In [ ]:

    

cursor.execute("CREATE TABLE IF NOT EXISTS writers(id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(25), UNIQUE KEY (name));")
cursor.execute("INSERT IGNORE INTO writers(Name) VALUES('Jack London')")
cursor.execute("INSERT IGNORE INTO writers(Name) VALUES('Honore de Balzac')")
cursor.execute("INSERT IGNORE INTO writers(Name) VALUES('Lion Feuchtwanger')")
cursor.execute("INSERT IGNORE INTO writers(Name) VALUES('Emile Zola')")
cursor.execute("INSERT IGNORE INTO writers(Name) VALUES('Truman Capote')")


    

In [ ]:

    

more_writers = ['Yukio Mishima', 'Lev Tolstoi', 'Franz Kafka']
for writer in more_writers:
    cursor.execute("INSERT IGNORE INTO writers(Name) VALUES(%s)", (writer,))


    

In [ ]:

    

more_writers_using_many = [('Charles Bukowski',), ('Jorge Luis Borges',), ('Gabriel Garcia Marquez',)]
cursor.executemany("INSERT IGNORE INTO writers(Name) VALUES(%s)", more_writers_using_many)


    

In [ ]:

    

more_writers_using_dict = [{'name':'Pablo Neruda'}, {'name':'Fedor Dostoievski'}]
cursor.executemany("INSERT IGNORE INTO writers(Name) VALUES(%(name)s)", more_writers_using_dict)


    

In [ ]:

    

cursor.execute("INSERT IGNORE INTO writers(Name) VALUES(%s)" % 'Francis Scott Fitzgerald')


    

In [ ]:

    

# What has happened?
"INSERT IGNORE INTO writers(Name) VALUES(%s)" % 'Francis Scott Fitzgerald'


    

In [ ]:

    

cursor.execute('SELECT * FROM writers')
for writer in cursor.fetchall():
    print writer


    

In [ ]:

    

# query for an specific register
cursor.execute("SELECT * FROM writers WHERE name='Pablo Neruda'")
print cursor.fetchone()


    

In [ ]:

    

# querying using interpolation
cursor.execute("SELECT * FROM writers WHERE name=%(name)s", {'name': 'Charles Bukowski'})
print cursor.fetchone()


    

In [ ]:

    

# using a dict cursor to improve working with a queryset
import MySQLdb.cursors
conn.commit()
cursor.close()
conn.close()
conn = mysql.connect('localhost', 'user', 'user', 'mod_mysqldb', cursorclass=MySQLdb.cursors.DictCursor)
cursor = conn.cursor()


    

In [ ]:

    

cursor.execute('SELECT * FROM writers')
for writer in cursor.fetchall():
    print writer


    

In [ ]:

    

import pymongo
client = pymongo.MongoClient('localhost', 27017)  # localhost:27017 is the default value
dbconn = client.mod_pymongo  # also client['mod_pymongo'] getting a database is so easy ('use db' in mongo shell)
print client


    

In [ ]:

    

from pymongo import ReadPreference
from pymongo.errors import AutoReconnect, ConnectionFailure, DuplicateKeyError
replica_client = pymongo.MongoClient(
                ('localhost:27017', 'localhost:27018', 'localhost:27019'), # also you can use url format
                w=3, # globally set write_concern (wtimeout can also be set...).
                replicaset='sdrepl',
                read_preference=ReadPreference.PRIMARY, # several options available
                auto_start_request=True  # consistent reads (socket allocated by requests)
                ) # you can also use MongoReplicaSetClient
# More options in http://api.mongodb.org/python/current/api/pymongo/connection.html
print replica_client
client.close()


    

In [ ]:

    

db_replica = replica_client.mod_pymongo
db_replica.books.drop()


    

In [ ]:

    

db_replica.writers.ensure_index([("name", pymongo.ASCENDING), ("age", pymongo.DESCENDING)], unique=True, name="unique_name")


    

In [ ]:

    

more_writers = ["Yukio Mishima", "Lev Tolstoi", "Franz Kafka", "J. D. Salinger"]
for writer in more_writers:
    db_replica.writers.insert({"name": writer, "age": 90})


    

In [ ]:

    

# some  more
db_replica.books.insert({'_id': 'hobbit', 'editions': []}) # rules is pretended to be a list of complex objects
db_replica.books.insert({'_id': 'lord_rings', 'editions': None }, w=0)  # write_concern can be disabled in collection level operations


    

In [ ]:

    

more_writers_using_bulk = ["Charles Bukowski", "Jorge Luis Borges", "Gabriel Garcia Marquez"]
db_replica.writers.insert([{"name": name} for name in more_writers_using_bulk])


    

In [ ]:

    

from pymongo.errors import DuplicateKeyError, OperationFailure

# collection level operations raise OperationFailure when a problem happens
# OperationFailure is translated in some cases:

try:
    db_replica.books.insert({'_id': 'hobbit'})
except DuplicateKeyError:
    print "Already created object"
except OperationFailure:
    print "Some problem occurred"


    

In [ ]:

    

cursor = db_replica.writers.find()
for writer in cursor: # we get a pymongo Cursor not a list (ordering, skip...)
    print writer


    

In [ ]:

    

# query for an specific register
res = db_replica.writers.find_one({"name": "Pablo Neruda"})
print res # we get a dict in python


    

In [ ]:

    

# querying with several fields, just provide a dict
import re
db_replica.writers.insert({'name': 'Miguel de Unamuno', 'age': 130})
db_replica.writers.insert({'name': 'Miguel Delibes', 'age': 90})
db_replica.writers.insert({'name': 'Miguel de Cervantes', 'age': 500})
res = db_replica.writers.find({"name": re.compile("^Miguel"), "age": {'$lt': 200}}) # regex can be used in query
print list(res) # we get a dict in python


    

In [ ]:

    

# sort, skip and limit are quite similar to shell
res = db_replica.writers.find().sort('name', pymongo.DESCENDING).skip(3).limit(1)
print list(res)


    

In [ ]:

    

# you can use it as kw arguments
res = db_replica.writers.find(skip=3).sort('name', pymongo.DESCENDING).limit(1)
print list(res)


    

In [ ]:

    

# to sort by more than one parameter we use list of set not dict 
res = db_replica.writers.find().sort([('name', pymongo.DESCENDING), ('_id', pymongo.ASCENDING)]).skip(3).limit(1)
print list(res)


    

In [ ]:

    

# Explain plans
from pprint import pprint
pprint(db_replica.writers.find({"name": "Pablo Neruda"}).explain())


    

In [ ]:

    

# Change the name of a field in a document
db_replica.writers.update({"name": "J. D. Salinger"}, {"name": "Jerome David Salinger"})


    

In [ ]:

    

# if object does not exist, create new one (upsert)
db_replica.writers.update({"name": "George R. R. Martin"}, {"name": "George Raymond Richard Martin"}, upsert=True)


    

In [ ]:

    

# Add book as subdocument in collection
book = {'name': 'hobbit'}
db_replica.writers.update({"name": "Jerome David Salinger"},{'$set': {'books': book}})
db_replica.writers.update({"name": "George Raymond Richard Martin"},{'$set': {'books': {'name': 'another_book'}}})


    

In [ ]:

    

# check the documents...
print db_replica.writers.find_one({"name": "Jerome David Salinger"})
print db_replica.writers.find_one({"name": "George Raymond Richard Martin"})


    

In [ ]:

    

# Update subdocument field
db_replica.writers.update({"name": "George Raymond Richard Martin"},{'$set': {'books.name': 'lord_rings'}})
res = dbconn.writers.find_one({"name": "George Raymond Richard Martin"})
print res


    

In [ ]:

    

# add one object to an array with push

edition = {
            'year': '1997',
            'editorial': 'planet'
        }
db_replica.books.update({'_id': 'hobbit' }, {'$push': {'editions': edition}}) # quite similar to mongo shell


    

In [ ]:

    

print db_replica.books.find_one({'_id': 'hobbit'})


    

In [ ]:

    

# Dealing with Autoreconnect in replicaset
# Stop the mongo primary instance before continue
import time
try:
    db_replica.books.find_one()
except AutoReconnect:
    print "Connection lost"


    

In [ ]:

    

# We make same query again ...
print db_replica.books.find_one()


    

In [ ]: