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.