Saturday, 20 June 2009

Python and MSSQL

I have been playing about with django a fair bit over the last week. After written a small app with django I wanted to import some data from an existing MSSQL database. To get the data into the django model I created a seperate python script to perform the import. Below is a script to import data from a table compaines in a mssql data to a django model called Company. To connect to the mssql server I used the pymssql library.


#!/usr/bin/env python
import sys,os
os.environ['DJANGO_SETTINGS_MODULE'] ='settings'
# import the modules needed to start the import
import pymssql
from datetime import *
import unicodedata
from django.core.management import setup_environ
import settings

setup_environ(settings)

#import the django models
from apps.clients.models import *

def importCompanies(conn):
cur = conn.cursor()
cur.execute("""SELECT
liCompanyPk,
szCompanyName
FROM companies""")
row = cur.fetchone()
while row:
company = Company()
company.pk = row[0]
company.Name = row[1]
company.save()
row = cur.fetchone()

if len(sys.argv) != 5:
print 'data_import.py [server] [username] [password] [database]'
else:
print 'host=%s, user=%s, password=%s, database=%s' % (sys.argv[1],sys.argv[2],sys.argv[3],sys.argv[4])
conn = pymssql.connect(host=sys.argv[1], user=sys.argv[2], password=sys.argv[3], database=sys.argv[4])
importCompanies(conn)
conn.close()


After this I started to play around with pymssql I wanted a quick way to execute simple sql queries, not knowing of an linux alternative to the mysql console client I write another simple script to provide this basic functionality.


#!/usr/bin/env python
import sys,os
import _mssql

if len(sys.argv) != 5:
print 'data_import.py [server] [username] [password] [database]'
else:
print 'host=%s, user=%s, password=%s, database=%s' % (sys.argv[1],sys.argv[2],sys.argv[3],sys.argv[4])
conn = _mssql.connect(server=sys.argv[1], user=sys.argv[2], password=sys.argv[3], database=sys.argv[4])
print '>',
cmd = raw_input()
while cmd != 'exit':
if cmd != '':
print cmd
try:
conn.execute_query(cmd)
for row in conn:
for i in range(0,( len(row) / 2 )):
print row[i],'|',
print ''
except _mssql.MssqlDatabaseException,e:
print e
print '>',
cmd = raw_input();

conn.close()

You can download mssql_client.py here

So after only using for python for a couple of weeks It's definitely powerful and fun to code with. Now to try out for all of the libraries that provide bindings such as pysvn or pyldap.

No comments:

Post a Comment