Shantanu's Blog

import sqlite3
conn = sqlite3.connect('/root/accounts.db', timeout=10)
c = conn.cursor()

c.execute('''CREATE TABLE stocks (date text, trans text, symbol text, qty real, price real)''')
c.execute("INSERT INTO stocks VALUES ('2006-01-05','BUY','RHAT',100,35.14)")

# Do this instead
t = ('2013-01-05','BUY','RHAT',100,35.14)

c.execute('INSERT INTO stocks VALUES (?,?,?,?,?)', t)

# Larger example that inserts many records at a time
purchases = [('2014-03-28', 'BUY', 'IBM', 1000, 45.00),
             ('2015-04-05', 'BUY', 'MSFT', 1000, 72.00),
             ('2016-04-06', 'SELL', 'IBM', 500, 53.00),
            ]
c.executemany('INSERT INTO stocks VALUES (?,?,?,?,?)', purchases)

# print all rows
for row in c.execute('SELECT * FROM stocks ORDER BY price'):
        print row

conn.commit()
conn.close()

_____

conn = sqlite3.connect('/Users/test/Desktop/my-accounts.db')
currentAccount = None

for row in conn.execute('SELECT email FROM accounts WHERE active=0'):
    currentAccount = row[0]
    print "Checking out: ",currentAccount
    break

if currentAccount is None:
    print "No available accounts"
else:
    conn.execute('UPDATE accounts SET active=1 WHERE email=?', [currentAccount,])
conn.close()

conn.commit()

_____

# A minimal SQLite shell for experiments

import sqlite3

con = sqlite3.connect(":memory:")
con.isolation_level = None
cur = con.cursor()

buffer = ""

print "Enter your SQL commands to execute in sqlite3."
print "Enter a blank line to exit."

while True:
    line = raw_input()
    if line == "":
        break
    buffer += line
    if sqlite3.complete_statement(buffer):
        try:
            buffer = buffer.strip()
            cur.execute(buffer)

            if buffer.lstrip().upper().startswith("SELECT"):
                print cur.fetchall()
        except sqlite3.Error as e:
            print "An error occurred:", e.args[0]
        buffer = ""

con.close()

_____


sqlite3 is the obvious alternative for a persistent store in python.
But its powerful SQL interface can be too complex when you just want a dict.

import sqlite3dbm
db = sqlite3dbm.sshelve.open('mydb.sqlite3')
db['foo'] = {'count': 100, 'ctr': .3}
db['bar'] = {'count': 314, 'ctr': .168}
db.items()
[('foo', {'count': 100, 'ctr': 0.29999999999999999}), ('bar', {'count': 314, 'ctr': 0.16800000000000001})]

db['foo']['count']
100

// connect
import boto.dynamodb
conn = boto.connect_dynamodb (aws_access_key_id='', aws_secret_access_key='')

// list tables
conn.list_tables()
[u'MTA', u'reply']

// select table
table = conn.get_table('reply')

// select a record
item = table.get_item(hash_key='Amazon DynamoDB#DynamoDB Thread 2', range_key='2012-01-31 23:28:40')
print item

// update record
item['PostedBy'] = 'User X'
item.put()

// delete record
item.delete()

// add record
item_data = {
        'Message': 'DynamoDB Thread 3 Reply 4 text',
        'SentBy': 'User A',
        'PostedBy': 'User oksoft',
        'ReceivedTime': '12/9/2011 11:36:03 PM',
    }

item = table.new_item(
        # Our hash key is 'forum'
        hash_key='Amazon DynamoDB#DynamoDB Thread 3',
        # Our range key is 'subject'
        range_key='2013-04-24 23:28:40',
        # This has the
        attrs=item_data
    )

item.put()

It is possible to recover a deleted file if S3 versioning is enabled.
We simply need to find the version ID of the deleted file and restore it back on S3.

import boto
c = boto.connect_s3('access-key','secret-key')
bucket = c.get_bucket('todelapr15')

rs = bucket.list_versions()
for key in rs:
  print key, key.version_id

The above code will list all the files and their version ID as shown below.

74eHPzvO1HpNvyujXn1.hs.UG5yObfh_
p7uMSJjrNLwJbclPPbYo56TPyb1L4asu
gjhBuYB4RgpwGJt6DCbIFHVBX7Od_hP.
IoBQ7FB1h4kDJrvzLlDmhPtBRts.14RF
WN4oSvCvoOmfmnAPT5pZS.YFCohyrvjh
Im3ci98wSsng8vaAZNDBsw8hoaTYcR2l

The deleted file needs to be copied first to a new location before it can be downloaded.

bucket.copy_key('new_ptty.exe', 'todelapr15/testing', 'ptty.exe', metadata=None, src_version_id='WN4oSvCvoOmfmnAPT5pZS.YFCohyrvjh')

YAML is a human friendly data serialization standard for all programming languages including python.

>>> import yaml
>>> document = """
  a: 1
  b:
    c: 3
    d: 4
"""

>>>print yaml.dump(yaml.load(document))
a: 1
b: {c: 3, d: 4}

>>> print yaml.dump(yaml.load(document), default_flow_style=False)
a: 1
b:
  c: 3
  d: 4

https://pypi.python.org/pypi/PyYAML

Here is how to run a command in python and get the standard out and error out responses saved as variables.

>>> import envoy
>>> r = envoy.run('git config', data='data ', timeout=2)

>>> r.status_code
129
>>> r.std_out
'usage: git config [options]'
>>> r.std_err
''

Here is how to take the backup to Glacier from the S3 folder
http://docs.pythonboto.org/en/latest/s3_tut.html#transitioning-objects-to-glacier

// connect to the parent folder that will be archived to glacier
import boto
c = boto.connect_s3('acess-key','secret-key')
bucket = c.get_bucket('todelapr09')

// all objects under logs/* to Glacier after 1 day
from boto.s3.lifecycle import Lifecycle, Transition, Rule
to_glacier = Transition(days=1, storage_class='GLACIER')
rule = Rule('ruleid', 'logs/', 'Enabled', transition=to_glacier)
lifecycle = Lifecycle()
lifecycle.append(rule)
bucket.configure_lifecycle(lifecycle)

// confirm if the policy is set
current = bucket.get_lifecycle_config()
print current[0].transition

// confirm the storate engine has changed:
>>> for key in bucket.list():
...   print key, key.storage_class

// Or use prefix argument to the bucket.list method:
>>> print list(b.list(prefix='logs/testlog1.log'))[0].storage_class

// Restore from Glacier to S3
>>> key = bucket.get_key('logs1/Config.py')
>>> key.restore(days=5)

// Download from S3
>>> key.get_contents_to_filename('testlog1.log')

Here are 3 python scripts those will help to keep track of Glacier activity.
_____

Archive File Upload

from boto.glacier.layer1 import Layer1
from boto.glacier.vault import Vault
from boto.glacier.concurrent import ConcurrentUploader
import sys
import os.path

access_key = "..."
secret_key = "..."
target_vault_name = '...'

fname = sys.argv[1]

if(os.path.isfile(fname) == False):
    print("Can't find the file to upload!");
    sys.exit(-1);

glacier_layer1 = Layer1(aws_access_key_id=access_key, aws_secret_access_key=secret_key)

uploader = ConcurrentUploader(glacier_layer1, target_vault_name, 32*1024*1024)

print("operation starting...");

archive_id = uploader.upload(fname, fname)

print("Success! archive id: '%s'"%(archive_id))
_____

Initiate Job to Get Vault Inventory

from boto.glacier.layer1 import Layer1
from boto.glacier.vault import Vault
from boto.glacier.job import Job
import sys
import os.path
import json

access_key = "..."
secret_key = "..."
target_vault_name = '...'

glacier_layer1 = Layer1(aws_access_key_id=access_key, aws_secret_access_key=secret_key)

print("operation starting...");

job_id = glacier_layer1.initiate_job(target_vault_name, {"Description":"inventory-job", "Type":"inventory-retrieval", "Format":"JSON"})

print("inventory job id: %s"%(job_id,));

print("Operation complete.")
_____

Read Vault Inventory Job Result

from boto.glacier.layer1 import Layer1
from boto.glacier.vault import Vault
from boto.glacier.job import Job
import sys
import os.path
import json

access_key = "..."
secret_key = "..."
target_vault_name = '...'

if(len(sys.argv) < 2):
    jobid = None
else:
    jobid = sys.argv[1]

glacier_layer1 = Layer1(aws_access_key_id=access_key, aws_secret_access_key=secret_key)

print("operation starting...");

if(jobid != None):
    print glacier_layer1.get_job_output(target_vault_name, jobid)
else:
    print glacier_layer1.list_jobs(target_vault_name, completed=False)

print("Operation complete.")

This shell script can be set in a cron that will check if any redshift cluster is active at the end of the day and then drop the cluster after taking the snapshot.

59 23 * * * /root/aws.sh > /root/aws_succ.txt 2> /root/aws_err.txt

#!/bin/sh

# make sure amazon command line interface is installed
# http://aws.amazon.com/cli/

export AWS_ACCESS_KEY_ID=ABC
export AWS_SECRET_ACCESS_KEY=PQR+XYZ
export AWS_DEFAULT_REGION=us-east-1

identifier=`aws redshift describe-clusters | grep ClusterIdentifier | awk -F ':' '{print $2}' | sed 's/"//g' | sed 's/,//g'`

aws redshift delete-cluster --cluster-identifier $identifier --final-cluster-snapshot-identifier $identifier.`date '+%b%d'`

if [$? -eq 0 ];then
echo "successfully delete $identifier"
else
echo "error"
fi


_____

python is recommended to upload files to glacier.

cat import.py

# python can be used if boto is installed
# https://boto.readthedocs.org/en/latest/getting_started.html

# Import boto's layer2
import boto.glacier.layer2

# Various variables for AWS creds, vault name, local file name
awsAccess = "ABC"
awsSecret = "PQR+XYZ"
vaultName = "company_backup"

fileName = "backup.sql"

# Create a Layer2 object to connect to Glacier
l = boto.glacier.layer2.Layer2(aws_access_key_id=awsAccess, aws_secret_access_key=awsSecret)

# Get a vault based on vault name (assuming you created it already)
v = l.get_vault(vaultName)

# Create an archive from a local file on the vault
archiveID = v.create_archive_from_file(fileName)

# If you ever want, you can delete the archive on the vault
# with the archive ID.
# v.delete_archive(archiveID)

### perl or php can be used to upload a file to glacier

# perl glacier upload utility is here
# http://mt-aws.com/
# ./mtglacier upload-file --config=glacier.cfg --vault=Viva_Test --journal=journal.log --dir /root/ --filename=/root/test.pl

# php can be used if php SDK is installed
# http://aws.amazon.com/sdkforphp/