Cybernetic Simpleton

Tuesday, April 30, 2013

Connecting Python to SQL Server on OSx

After several hours and many frustrating errors, I was able to query SQL Server with Python. Previously, I went through several searches suggesting to install FreeTDS or find ODBC drivers. The steps were usually overly complicated and the instructions produced errors that send me down other tangents.

So here is it simply:
1. Install pyodbc. (https://code.google.com/p/pyodbc/)
Make sure the Python version and bit count (32 or 64) match your system.
2. Buy the driver from http://www.actualtech.com/
I know, buy a driver? But it's $35 and I had it installed and running my test scripts in 5 minutes.
Given my billable rate the client was happy to pay for it.
If you're skeptical, you can install the driver for free with a few restrictions.

Here's a sample of my code:

import pyodbc

cnxn = pyodbc.connect('DRIVER={Actual SQL Server};SERVER=127.0.0.1;DATABASE=somedatabase;UID=username;PWD=password')
cursor = cnxn.cursor()

q = cursor.execute("select firstname, lastname from users")
row = cursor.fetchone()
if row:
    print(row)

Wednesday, April 10, 2013

Temp File Cleanup with Python

In the short few months I've been using Python, I've been impressed by it's versitility, performance and small footprint. Here is a quick of example of how to used Python to loop through a directory and delete old files based on the last modified date.

On Windows:

import os, time, sys
from datetime import datetime

path = r"c:\tempFiles" # Target Directory
logfile = r"c:\log\tempFileCleanup.txt"  # Log File
days = 10  # Delete files older than X days

now = time.time()

for f in os.listdir(path):
 full = path + '\\'
 full = full + f
 #print(full)
 #print(os.stat(full).st_mtime)
 if os.stat(full).st_mtime < now - days * 86400:
  if os.path.isfile(full):
   d = os.stat(full).st_mtime
   print(full)
   print(datetime.utcfromtimestamp(d))
   print(" ")
   os.remove(os.path.join(path, f))

# Create the log file if it does not exist
try:
   with open(logfile): pass
except IOError:
 target = open (logfile, 'w')
 target.write("\n")
 target.close()

# Write to the log file
with open(logfile, "r+") as f:
 old = f.read() 
 f.seek(0)
 f.write("Job Complete - " + str(datetime.utcfromtimestamp(now)) + "\n" + old)
   
print("All Done")

Sunday, April 7, 2013

GPS with jQuery Mobile

Having never developed on a mobile platform before, I was very curious about using the GPS feature within a web page. Having seen a "request to access my GPS" when I viewed certain sites, I knew it was possible. After a little research I found it was much easier than I through thanks to jQuery mobile.

You must download jQuery and jQuery mobile. I also used the jQuery mobile css.

Click here for demo

<!DOCTYPE html>
<html>
<head>
<meta name=viewport content="user-scalable=no,width=device-width" />
<link rel=stylesheet href="jquery.mobile-1.3.0/jquery.mobile-1.3.0.min.css" />
<script src="jquery-1.9.1.min.js"></script>
<script src="jquery.mobile-1.3.0/jquery.mobile-1.3.0.js"></script>
</head>
<body>

<div data-role=page id=home>
<div data-role=header><h1>Where Am I?</h1></div>

<div data-role=content>
<span> Status : </span> <span id=stat></span> <br />
<span> Time : </span> <span id=time></span> <br />
<span> Latitude : </span> <span id=lat></span> <br />
<span> Longitude : </span> <span id=lng></span> <br />
</div>

<input type="button" name="ss" value="" id="ss">

<div id="out1" style="width:96%; height:200px; overflow:scroll; border:1px solid #000; margin-left:2%;"></div>
</div>

</body>
</html>

<script>
(function($) {

$.fn.changeButtonText = function(newText) {
return this.each(function() {
$this = $(this);
if( $this.is('a') ) {
$('span.ui-btn-text',$this).text(newText);
return;
}
if( $this.is('input') ) {
$this.val(newText);
// go up the tree
var ctx = $this.closest('.ui-btn');
$('span.ui-btn-text',ctx).text(newText);
return;
}
});
};

var runGPS = 0;
$("#ss").changeButtonText('Start');
$("#stat").text('Paused');

$('#ss').click(function() {
if (runGPS == 1){
$("#stat").text('Paused');
$("#ss").changeButtonText('Start');
runGPS = 0;
} else {
$("#stat").text('Running');
$('#ss').changeButtonText("Stop");
runGPS = 1;
}
});

var timer = setInterval(function(){
if (runGPS == 1){
navigator.geolocation.getCurrentPosition (function (pos)
{
date = new Date();
$("#time").text (date);

var lat = pos.coords.latitude;
var lng = pos.coords.longitude;
$("#lat").text (lat);
$("#lng").text (lng);

var t = date + " lat:" + lat + "lng:" + lng + "<br>";

$("#out1").append(t);

});
}
},2000);

})(jQuery);
</script>

Tuesday, March 19, 2013

A Tool for the Time

For the last 35 years the world has been using relational databases to store our all kinds of data. From financial to statistical data to web sites, databases such as Oracle, MS SQL Server and MySQL have been used to store petabytes of information around the world. Developers have been conditioned to normalize our data to minimize the physical space uses, enforce consistency, and maximize performance.

Now there’s a new kid on the block gaining popularity called No-SQL (Non-Relational) databases. Industry leaders such as Google, Amazon and Twitter are using No-SQL databases such as BigTable, CouchDB and MongoDB in their production environments. But what are their differences and how did we get here?

To know where we are now, we have to start back at the beginning. The first RDBMS (Relational Database Management System) was the Multics Relational Data Store in 1978. At this time the price of hardware was very high and performance expectations were relatively low. Even a slow computer was faster that a human with a calculator. Apple Computer just introduced the Disk II, a 5.25 inch floppy disk drive linked to the Apple II by a cable for $495 providing 720 kB. Program languages were completely procedural. Pascal, C & ADA were the most popular. The demands on the industry were limited by the available technology.

Jump forward to the 2000’s when hardware prices have dropped drastically. Programming languages are in their 10th and 12th generation and utilize object-oriented models. The Internet is beginning to mature and has gained use in the general public. It was this movement that fueled the development of the No-SQL database. No longer was the physical size of our data the highest priority. Uptime, redundancy and performance were the new goals. Database structures have begun to mimic objects instead of tables. Even the rules of normalization which architects hold so dear have been cast aside as they are no longer applicable to No-SQL databases.

Relational Databases

Minimize disk space through foreign key relations.
Force consistency through table definitions and constraints.
Data is usually stored in a single database. Replication is available to maintain a hot “stand by” in case of a crash.

No-SQL Databases

Do not enforce any foreign keys but store multiple copies of the same data.
Consistency is enforced at the application level, not the database. No table definitions or constraints exist on the database. Document definitions are defined on creation of the individual document.
Database clustering is available, allowing query distributions across several servers providing increased performance. Additionally, clustering allows for better uptime and lower maintenance impacts.

As with any technology, acceptance to change is slow and relational databases still have many years ahead of them. However, is the writing on the wall and the extinction of relational databases on the horizon? Only time will tell.

Mongo DB for Developers Certification

As of Tuesday March 12, I have passed my certification. For anyone interested in No-SQL databases or MongoDB I highly recommend this 7 week course. It covers CRUD, python integration, performance tuning, sharding, replication and many more valuable topics. There's also a course for DBAs. For more information, check out http://education.10gen.com.

Monday, February 18, 2013

Synchronizing Databases

If you've ever needed to synchronize databases, you know how tedious this can be. Opening each table in design view and comparing the columns visually. I've personally done this hundreds of times. I create a simple CFM file that accepts 2 datasources, a source and target, and compares the columns. It's easy to use, free, and the installation is simple. Just unzip the download and place in a directory accessible through your web server.

Requirements:

Coldfusion 6 or higher or Railo
SQL Server 2000 or higher

Click here to view an example

Click here download

EasyCFSQL

Tired of writing SQL. Stop! Have ColdFusion write your SQL for you.

This is a small script that utilizes the system tables of a MS SQL Server database to loop through the user tables and columns to display select, insert and update statements inside a cfquery.

Click here to view an example

Click here to download

Good Coding!