Configuration for Connecting to MSSQL Server 2008 on Virtualbox GuestOS from Ubuntu 12.04 HostOS using PyODBC 3.0.8

1. VirtualBox Configuration

– Adding a host-only network adapter
If you don’t have a host-only adapter setup then goto topmost menu:
File->Preferences->Network->Host-only network->Add ‘vboxnet0’ by clicking on the ‘+’ sign at
the right-hand corner. Go to edit and take note of the IP address assigned (lets assume its 192.168.56.1, so that is the virtual network adapter that has been created at host OS)
Now go to:
Settings->Network->Adapter 2 Tab (considering you’ve NAT set on Adapter 1 for internet) ->Attach to host-only adapter> Name select ‘vboxnet0’
[Note, if you already have a network adapter with NAT selected for accessing internet from
guest os, leave it and just add another one.]
-Add GuestOS to the network
Go to Stat->Settings->Network Connections->Select ‘Local Area Connection 2’ (‘Local Area Connection 1’ should be allocated to NAT Adapter 1 , which is used for Internet)
Right click to get properties ->Internet Protocol (TCP/IP)->Use the following IP address->Assign an IP in the same network as host-only adapter (In this example, that can be 192.168.56.2/255.255.255.0)

2. MSSQL Server 2008 R2 Configuration (GuestOS)

– Enable TCP/IP and Piping

Open SQL Server Configuration Manager (SSCM) go to ‘SQL Server Network Configuration’->Double Click on ‘TCP/IP’->Select Protocol Tab -> Select Enabled as ‘yes’
Goto IP Address Tab -> Change IP1 (or anyother) IP address to ‘192.168.56.1’ (In same network as the host OS ip)->Select Active to yes, Enabled to yes
Now go back to main menu, change ‘Named Pipes’ to ‘Enabled’

– Set SQL Server Authentication

If you’re using ‘Windows Authentication’ to login to your Database. You have to change it to ‘SQL Server Authentication’ and set username and password for it , so that it can be accessed using PyODBC.

To change security authentication mode

In SQL Server Management Studio Object Explorer, right-click the server, and then click Properties.
On the Security page, under Server authentication, select the new server authentication mode, and then click OK.
In the SQL Server Management Studio dialog box, click OK to acknowledge the requirement to restart SQL Server.
In Object Explorer, right-click your server, and then click Restart. If SQL Server Agent is running, it must also be restarted.

To enable the sa login, (‘sa’ will be your username)

In Object Explorer, expand Security, expand Logins, right-click sa, and then click Properties.
On the General page, you might have to create and confirm a password for the login.
On the Status page, in the Login section, click Enabled, and then click OK.

3. Disble Windows Firewall serivce
Simply turning off the firewall won’t do, you’ve to stop the service, do:
Run->Services.msc->Windows Firewall->Stop

4. Test connection
Using telnet:
From hostOS (192.168.56.1) do: telnet 192.168.56.2 1433
You can also use a DB client such as, dbeaver.

5. PyODBC configurations

– freetds.conf

# local sql server
[localsqlserver]
    host = 192.168.56.2
    port = 1433  
    tds version = 7.0

– odbcinst.ini

[sqlserverdatasource1]
Driver = /usr/lib/i386-linux-gnu/odbc/libtdsodbc.so
Description = ODBC connection via FreeTDS
Server = 192.168.56.2
Port = 1433 
Database = Test
TDS_Version = 8.0

– pyodbc-test.py code

import pyodbc

dsn = 'sqlserverdatasource1'
user = 'sa'
password = '******'
database = 'TestDB' 

con_string = 'DSN=%s;UID=%s;PWD=%s;DATABASE=%s;' % (dsn, user, password, database)
cnxn = pyodbc.connect(con_string)
cursor = cnxn.cursor()
print cursor
Advertisements

Increase your VirtualBox Hard Disk Size

Steps to Increase your VirtualBox Hard Disk Size

cd into the directory where your virtual drive is found (usually ~/VirtualBox VMs/{Your VirtualBox PC name})

    cd ~/VirtualBox VMs/WinXP_SP3

run the modify hd function with the new size (in MB) I choose 25GB (25 * 1024mb = 25600MB)

    VBoxManage modifyhd WinXP_SP3.vdi --resize 25600

VirtualBox will resize your hard drive and you will see the following output from the system indicating that the process is complete

    0%...10%...20%...30%...40%...50%...60%...70%...80%...90%...100%

Now start up your Virtual Windows Machine. Right Click on ‘My Computer’ (‘Computer’ in newer versions) and choose Manage.

Click on the Disk Management option on the left panel. You will notice some black space next to your C drive (this means the space addition was successful.)

Right Click on the C drive and choose Extend Volume. Follow the wizard and you have successfully increased the size of your VirtualBox disk. Or, if that option is not available use a third party partition making software like Partition Magic or create a second primary partition.

Ubuntu tip: How to safely kill hanged processes.

List the process using ps and find desired application, e.g. idle

naved@neo:~$ ps aux|grep idle
naved     7884  0.5  0.2  33556 15316 pts/3    Tl   20:56   0:30 /usr/bin/python /usr/bin/idle -e ens_repo_pyodbc.py
naved     7929  0.6  0.2  34156 16000 pts/3    Tl   21:06   0:30 /usr/bin/python /usr/bin/idle -e dbmethods.py
naved     7933  0.5  0.2  33416 15280 pts/3    Tl   21:08   0:24 /usr/bin/python /usr/bin/idle -e dbtester.py
naved     7998  0.3  0.2  33408 15308 pts/3    Tl   21:45   0:08 /usr/bin/python /usr/bin/idle -e /home/naved/Coding/pyodbc-test.py
naved     8243  0.0  0.0   4384   832 pts/4    S+   22:27   0:00 grep --color=auto idle

Use kill -9 to force kill the process

naved@neo:~$ kill -9 7884

To kill all proceses of an specific application, e.g. idle:

killall -9 idle

MSSQL Server connection using pyodbc 3.0.7 on Ubuntu 12.04

1. Install pyodbc from your Virtualenv pip

python_2.7.5_venv/bin/pip install pyodbc

2. Install mssql odbc drivers and other dependencies

sudo apt-get install unixodbc unixodbc-dev freetds-dev freetds-bin tdsodbc

3. open /etc/freetds/freetds.conf and add the following at the end:

# mssql server
[sqlserver]
    host = <host-adddress>
    port = <port-number>
    tds version = 7.0

4. Test server connection using the following command

tsql -S sqlserver -U <user-name> -P <password>

5. Check configuration files using –

odbcinst -j

6. Configure /etc/odbcinst.ini and insert the following:

[FreeTDS]
Description = TDS driver (Sybase/MS SQL)
# Some installations may differ in the paths
Driver = /usr/lib/i386-linux-gnu/odbc/libtdsodbc.so
Setup = /usr/lib/i386-linux-gnu/odbc/libtdsS.so
CPTimeout =
CPReuse =
FileUsage = 1

7. Open /etc/odbc.ini and insert the following:

[sqlserverdatasource]
Driver = FreeTDS
Description = ODBC connection via FreeTDS
Trace = No
Servername = sqlserver
Database = <database-name>
TDS_Version = 8.0

8. Check connection using the following command:

isql -v sqlserverdtasource <username> <password>

9. Use the following code to check pyodbc :

import pyodbc
 
dsn = 'sqlserverdatasource'
user = '<username>'
password = '<password>'
database = '<database-name>'
 
con_string = 'DSN=%s;UID=%s;PWD=%s;DATABASE=%s;' % (dsn, user, password, database)
cnxn = pyodbc.connect(con_string)

IPython Notebook : Keyboard Shortcuts

Ipython Notebook Keyboard Shortcuts
Shif­t-E­nter: run cell
Ctrl­-En­ter: run cell in-place
Ctrl-m x: cut cell
Ctrl-m c: copy cell
Ctrl-m v: paste cell
Ctrl-m d: delete cell
Ctrl-m a: insert cell above
Ctrl-m b: insert cell below
Ctrl-m o: toggle output
Ctrl-m 0: toggle output scroll
Ctrl-m l: toggle line numbers
Ctrl-m s: save notebook
Ctrl-m j: move cell down
Ctrl-m k: move cell up
Ctrl-m y: code cell
Ctrl-m m: markdown cell
Ctrl-m t: raw cell
Ctrl-m 1-6: heading 1-6 cell
Ctrl-m p: select previous
Ctrl-m n: select next
Ctrl-m i: interrupt kernel
Ctrl-m .: restart kernel
Ctrl-m h: show keyboard shortcuts

IPython Console Cheatsheet

Ref : http://object22.blogspot.com/2011/12/ipython-cheat-sheet.html
Part I – basic commands
var – tab completion of a variable that starts with prefix ‘var’
who [int, str, ..]- list available variables, or just those with the give type
whos – a table of all the variables, their type and values
psearch var* [type] – search for variables using the pattern and possibly type
reset – clear all variables
logstate – has the logging been started or not
logstart – start logging into (ipython_log.py by default)
logon – turn logging on
logoff – turn logging off
lsmagic – list all built in commands, called magic commands. These are prefixed with % to differentiate between variables if they have the same name.
magic – scrolling help about the magic commands.
cmd? – overview of the command, e.g lsmagic?
cmd?? – more information about the command including source code
var? – information about the variable
page obj – pretty print the object
func arg1, arg2 – automatically adds parentheses and calls func(arg1, arg2)
/func – same as func()
, func arg1, arg2 – same as func(“arg1”, “arg2”)
help(keyword) – manual pages on keyword (module, function, etc)
help() -> topics – lists all the help topics
pdef func – shows the function signature (list of arguments it takes)
pdoc func – prints doc string for the function
pinfo func – same as func?
psource func – shows the source code for the function
pfile func – opens the source file with the func
edit -x func – opens the source with default editor on the line with the function

Part II – navigation and the shell

Analogous to standard shell commands there are pwd, cd, pushd, popd, dirs Tab completion also works with these.
bookmark name [path] – bookmarks the current directory or the given path
bookmark -l – lists all saved bookmarks
bookmark -d name – removes bookmark name
dhist – prints the directory navigation history. cd can take numbers from that list as argument, e.g cd -2
!cmd – runs cmd as a shell command, e.g !ls
alias – lists defined aliases for shell commands we can use
alias newname cmd args – creates a new alias with newname as name for the cmd with args
unalias name – removes alias name
var = !cmd – store the output of cmd into variable
pycat name – prints the highlighted contents of the file
run script.py – runs a python file called script.py and prints the output. Also the functions, variables and modules from the script REMAIN in our namespace!
run -p script.py – run the profiling on the script

Part III – input manipulation

ctrl+p, ctrl+n, arrow keys – previous next command from history
_i, _ii, _iii – prints 1,2 or 3 previous command
_i[X] – prints command number X, e.g _i1
In[X] – prints X-th input commands
hist [N1-N2] – prints a list of previously typed commands, or N1 through N2 previous commands
macro name cmdA-cmdB cmdX – defines a mactor that can execute commands from the history
print macro – prints a macro
edit file – opens up the default editor to edit a file
edit [N1-N2] – edit the history N1 through N2
save file N1-N2 – saves the historey N1 through N2 into file.py
cpaste – paste in source code without, IPython will not format it.
Out[X] – prints the X-th output if it exists
func; – putting does not echo the return of the function and does not put it into Out dictionary

Note! The following commands do not work in IPython 0.11:
store var – save the variable in profile for future use
p var – shorthand for print
exec cmd – execute a command
runlog – executes the IPython log

Install IPython 1.0 and Notebook for Virtualenv Python 2.7.5

Use the pip inside virtual environment to install ipython

(py2.7.5)naved@neo:~/Coding/azureprecomplier$ py2.7.5/bin/pip install ipython

Install ipython dependencies:

sudo apt-get install libncurses5-dev
sudo ~/Coding/py2.7.5/bin/easy_install readline

Install notebook dependencies:

(py2.7.5)naved@neo:~/Coding$ py2.7.5/bin/pip install pyzmq
(py2.7.5)naved@neo:~/Coding$ py2.7.5/bin/pip install jinja2
(py2.7.5)naved@neo:~/Coding$ py2.7.5/bin/pip install tornado

Run ipython inside virtual environment

(py2.7.5)naved@neo:~/Coding/azureprecomplier$ ipython
/home/naved/Coding/azureprecomplier/py2.7.5/lib/python2.7/site-packages/IPython/frontend.py:30: UserWarning: The top-level `frontend` package has been deprecated. All its subpackages have been moved to the top `IPython` level.
  warn("The top-level `frontend` package has been deprecated. "
Python 2.7.5 (default, Aug 30 2013, 22:14:16) 
Type "copyright", "credits" or "license" for more information.

IPython 1.0.0 -- An enhanced Interactive Python.
?         -> Introduction and overview of IPython's features.
%quickref -> Quick reference.
help      -> Python's own help system.
object?   -> Details about 'object', use 'object??' for extra details.

In [1]:

Run ipython notebook:

(py2.7.5)naved@neo:~/Coding/azureprecomplier$ ipython notebook