Connect to MS SQL Server using Python

Steps to take to connect to a MS SQL Server using Python. In this case using the python ODBC module pyodbc.
1. Download pyodbc from http://code.google.com/p/pyodbc/downloads/list, get the newest 32-bit version (even if you have 64-bit OS), since you need to have a paid license to use the 64-bit version. The 32-bit version is free.
2. Check what ODBC client you have for MS SQL Server. Go to Control Panel -> Administrative Tools -> Data Sources (ODBC) -> Drivers tab (on Windows 7). You will hopefully see a list of drivers to choose from. I had SQL Server and SQL Server Native Client 10.0. You will need the whole name in the next step.
3. Here is a sample program to fetch data from a table:
# -*- coding: utf-8 -*-
import pyodbc

CONNECTION_STRING="""
Driver={SQL Server Native Client 10.0};
Server=localhost\sqlexpress;
Database=MYDB_DB;
Trusted_Connection=yes;
"""

db = pyodbc.connect(CONNECTION_STRING)
c = db.cursor()
c.execute ('SELECT * FROM users')
rs = c.fetchall()
for r in rs:
    print r[0]
Notice how we put the driver name from step 2. into the Driver parameter, the Server parameter should point to your server and the Database should be the name of your database. The triple quotations is just to let us have the connection string on multiple lines in the code, you could use a single quotation mark and have it all on the same line.

Sources: http://community.activestate.com/forum/how-instal-pyodbc-module, http://stackoverflow.com/questions/289978/whats-the-simplest-way-to-access-mssql-with-python-or-ironpython, http://code.google.com/p/pyodbc/wiki/GettingStarted, http://stackoverflow.com/questions/10558354/using-microsoft-access-database-mdb-with-python-on-ubuntu

Python writing UTF-8 files

Some steps to take to create UTF-8 files with Python:
1. use codecs module to read and write files:
import codecs
f = codecs.open('file.txt', mode="w", encoding="utf-8-sig")
2. Don't mix strings and unicode. (Always prefix "strings" with u (e.g. u"hello world"))
3. Start your python script with # -*- coding: utf-8 -*- in case you might have any unicode characters in your code or in hardcoded "strings" in your script.

4. Also read http://lobstertech.com/python_unicode.html and http://www.evanjones.ca/python-utf8.html for more info on using UTF-8/unicode with Python.

Other sources:
http://docs.python.org/howto/unicode.html
http://www.carlosble.com/2010/12/understanding-python-and-unicode/

Transact SQL Arithmetic overflow error

Getting
Arithmetic overflow error converting numeric to data type numeric.
when trying to insert into your MS SQL (Transact SQL) table?

Most probably this is caused by the datatype of your field. If the type is for example numeric(18,18) this does not mean that you can have 18 digits before the decimal point and 18 after. It actually means that you can have a total of 18 digits (specified by the first value) and 18 digits after the decimal point.

As you might realize this means you can't have any values larger or equal than 1.0 in your field.

So, if that's what your're trying to do, change the field type to for example numeric(18,5) which will give you 5 digits in front of the decimal point and 13 after.

Source: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=35602