FreeTDS is required. On some platforms, we provide a pre-compiled FreeTDS to make installing easier, but you may want to install FreeTDS before doing pipinstallpymssql if you run into problems or need features or bug fixes in a newer version of FreeTDS. You can build FreeTDS from source if you want the latest. If you’re okay with the latest version that your package manager provides, then you can use your package manager of choice to install FreeTDS. E.g.:
To try it, first download the image (this requires Internet access and could take a while):
docker pull pymssql/pymssql
Then run a Docker container using the image with:
docker run -it --rm pymssql/pymssql
By default, if no command is specified, an IPython shell is invoked. You can override the command if you wish – e.g.:
docker run -it --rm pymssql/pymssql bin/bash
Here’s how using the Docker container looks in practice:
$ docker pull pymssql/pymssql
...
$ docker run -it --rm pymssql/pymssql
Python 2.7.6 (default, Mar 22 2014, 22:59:56)
Type "copyright", "credits" or "license"for more information.
IPython 2.1.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]: import pymssql; pymssql.__version__
Out[1]: u'2.1.1'
In [2]: import sqlalchemy; sqlalchemy.__version__
Out[2]: '0.9.7'
In [3]: import pandas; pandas.__version__
Out[3]: '0.14.1'
import_mssqlconn=_mssql.connect(server='SQL01',user='user',password='password', \
database='mydatabase')conn.execute_non_query('CREATE TABLE persons(id INT, name VARCHAR(100))')conn.execute_non_query("INSERT INTO persons VALUES(1, 'John Doe')")conn.execute_non_query("INSERT INTO persons VALUES(2, 'Jane Doe')")
# how to fetch rows from a tableconn.execute_query('SELECT * FROM persons WHERE salesrep=%s','John Doe')forrowinconn:print"ID=%d, Name=%s"%(row['id'],row['name'])
New in version 2.1.0: Iterating over query results by iterating over the connection object just like it’s already possible with pymssql connections is new in 2.1.0.
# examples of other query functionsnumemployees=conn.execute_scalar("SELECT COUNT(*) FROM employees")numemployees=conn.execute_scalar("SELECT COUNT(*) FROM employees WHERE name LIKE 'J%'")# note that '%' is not a special character hereemployeedata=conn.execute_row("SELECT * FROM employees WHERE id=%d",13)
# how to fetch rows from a stored procedureconn.execute_query('sp_spaceused')# sp_spaceused without arguments returns 2 result setsres1=[rowforrowinconn]# 1st resultres2=[rowforrowinconn]# 2nd result
# how to get an output parameter from a stored proceduresqlcmd="""DECLARE @res INTEXEC usp_mystoredproc @res OUTSELECT @res"""res=conn.execute_scalar(sqlcmd)
# how to get more output parameters from a stored proceduresqlcmd="""DECLARE @res1 INT, @res2 TEXT, @res3 DATETIMEEXEC usp_getEmpData %d, %s, @res1 OUT, @res2 OUT, @res3 OUTSELECT @res1, @res2, @res3"""res=conn.execute_row(sqlcmd,(13,'John Doe'))
# examples of queries with parametersconn.execute_query('SELECT * FROM empl WHERE id=%d',13)conn.execute_query('SELECT * FROM empl WHERE name=%s','John Doe')conn.execute_query('SELECT * FROM empl WHERE id IN (%s)',((5,6),))conn.execute_query('SELECT * FROM empl WHERE name LIKE %s','J%')conn.execute_query('SELECT * FROM empl WHERE name=%(name)s AND city=%(city)s', \
{'name':'John Doe','city':'Nowhere'})conn.execute_query('SELECT * FROM cust WHERE salesrep=%s AND id IN (%s)', \
('John Doe',(1,2,3)))conn.execute_query('SELECT * FROM empl WHERE id IN (%s)',(tuple(xrange(4)),))conn.execute_query('SELECT * FROM empl WHERE id IN (%s)', \
(tuple([3,5,7,11]),))
conn.close()
Please note the usage of iterators and ability to access results by column name. Also please note that parameters to connect method have different names than in pymssql module.
import_mssqlconn=_mssql.connect(server='SQL01',user='user',password='password',database='mydatabase')try:conn.execute_non_query('CREATE TABLE t1(id INT, name VARCHAR(50))')except_mssql.MssqlDatabaseExceptionase:ife.number==2714ande.severity==16:# table already existed, so quieten the errorelse:raise# re-raise real errorfinally:conn.close()
You can provide your own message handler callback function that will be invoked by the stack with informative messages sent by the server. Set it on a per _mssqlconnection basis by using the _mssql.MSSQLConnection.set_msghandler() method:
import_mssqldefmy_msg_handler(msgstate,severity,srvname,procname,line,msgtext):""" Our custom handler -- It simpy prints a string to stdout assembled from the pieces of information sent by the server. """print("my_msg_handler: msgstate = %d, severity = %d, procname = '%s', ""line = %d, msgtext = '%s'"%(msgstate,severity,procname,line,msgtext))conn=_mssql.connect(server='SQL01',user='user',password='password')try:conn.set_msghandler(my_msg_handler)# Install our custom handlercnx.execute_non_query("USE mydatabase")# It gets called at this pointfinally:conn.close()
Error to install : from PIL import Image, ImageTk
Error message: ImportError:No module named 'PIL' Solution: pip install pillow More info about PIL at http://effbot.org/imagingbook/introduction.htm
Advanced Matplotlib Series (videos and ending source only)
Once you have a basic understanding of how Matplotlib works, you might have an interest in taking your knowledge a bit further. Some of the most complex graphing needs come in the form of stock analysis and charting, or Forex. In this tutorial series, we're going to cover where and how to automatically grab, sort, and organize some free stock and forex pricing data. Next, we're going to chart it using some of the more popular indicators as an example. Here, we'll do MACD (Moving Average Convergence Divergence) and the RSI (Relative Strength Index). To help us calculate these, we will use NumPy, but otherwise we will calculate these all on our own.
To acquire the data, we're going to use the Yahoo finance API. This API returns historical price data for the ticker symbol we specify and for the time length we ask for. The larger the time frame, the lower the resolution of data we get. So, if you ask for a 1-day time frame for AAPL, you will get 3-minute OHLC (open high low close) data. If you ask for 10 years worth, you will get daily data, or even 3 day time frames. Keep this in mind and choose a time frame that fits your goals. Also, if you choose a low enough time frame and get high enough granularity, the API will return the time in a unix time stamp, as compared to a date stamp.
Once we have the data, we will want to graph it. To start, we'll just plot the lines, but most people will want to plot a candlestick instead. We will use Matplotlib's candlestick function, and make a simple edit to it to improve it slightly. On this same chart, we'll also overlay a few moving average calculations.
After this, we're going to create a subplot, and graph the volume. We cannot plot volume on the same subplot immediately, because the scale is different. To start, we will plot the volume underneath in another sub plot, but eventually we'll actually overlay volume on the same figure and make it somewhat transparent.
Then, we're going to add 2 sub plots and plot an RSI indicator on top and the MACD indicator on the bottom. For all of these, we're going to share the X axis, so we can zoom in and out in 1 plot and they will all match the same time frame.
We're going to plot in date format for the X axis, and customize just about all of the things we can for aesthetics. This includes changing tick label colors, edge / spine colors, line colors, OHLC candlestick colors, learn how to create a filled graph (for volume), histograms, draw specific lines (hline for RSI), and a whole lot more.
Here's the end-result (I have both a Python 3 and a Python 2 version for this. Python 3 first, then Python 2. Make sure you're using the one that matches your Python Version!):
# THIS VERSION IS FOR PYTHON 3 #import urllib.request, urllib.error, urllib.parse
import time
import datetime
import numpy as np
import matplotlib.pyplot as plt
import matplotlib.ticker as mticker
import matplotlib.dates as mdates
from matplotlib.finance import candlestick_ohlc
import matplotlib
import pylab
matplotlib.rcParams.update({'font.size':9})def rsiFunc(prices, n=14):
deltas = np.diff(prices)
seed = deltas[:n+1]
up = seed[seed>=0].sum()/n
down =-seed[seed<0].sum()/n
rs = up/down
rsi = np.zeros_like(prices)
rsi[:n]=100.-100./(1.+rs)for i in range(n, len(prices)):
delta = deltas[i-1]# cause the diff is 1 shorterif delta>0:
upval = delta
downval =0.else:
upval =0.
downval =-delta
up =(up*(n-1)+ upval)/n
down =(down*(n-1)+ downval)/n
rs = up/down
rsi[i]=100.-100./(1.+rs)return rsi
def movingaverage(values,window):
weigths = np.repeat(1.0, window)/window
smas = np.convolve(values, weigths,'valid')return smas # as a numpy arraydefExpMovingAverage(values, window):
weights = np.exp(np.linspace(-1.,0., window))
weights /= weights.sum()
a = np.convolve(values, weights, mode='full')[:len(values)]
a[:window]= a[window]return a
def computeMACD(x, slow=26, fast=12):"""
compute the MACD (Moving Average Convergence/Divergence) using a fast and slow exponential moving avg'
return value is emaslow, emafast, macd which are len(x) arrays
"""
emaslow =ExpMovingAverage(x, slow)
emafast =ExpMovingAverage(x, fast)return emaslow, emafast, emafast - emaslow
def bytespdate2num(fmt, encoding='utf-8'):
strconverter = mdates.strpdate2num(fmt)def bytesconverter(b):
s = b.decode(encoding)return strconverter(s)return bytesconverter
def graphData(stock,MA1,MA2):'''
Use this to dynamically pull a stock:
'''try:print('Currently Pulling',stock)
urlToVisit ='http://chartapi.finance.yahoo.com/instrument/1.0/'+stock+'/chartdata;type=quote;range=10y/csv'
stockFile =[]try:
sourceCode = urllib.request.urlopen(urlToVisit).read().decode()
splitSource = sourceCode.split('\n')for eachLine in splitSource:
splitLine = eachLine.split(',')if len(splitLine)==6:if'values'notin eachLine:
stockFile.append(eachLine)exceptExceptionas e:print(str(e),'failed to organize pulled data.')exceptExceptionas e:print(str(e),'failed to pull pricing data')try:
date, closep, highp, lowp, openp, volume = np.loadtxt(stockFile,delimiter=',', unpack=True,
converters={0: bytespdate2num('%Y%m%d')})
x =0
y = len(date)
newAr =[]while x < y:
appendLine = date[x],openp[x],highp[x],lowp[x],closep[x],volume[x]
newAr.append(appendLine)
x+=1Av1= movingaverage(closep, MA1)Av2= movingaverage(closep, MA2)
SP = len(date[MA2-1:])
fig = plt.figure(facecolor='#07000d')
ax1 = plt.subplot2grid((6,4),(1,0), rowspan=4, colspan=4, axisbg='#07000d')
candlestick_ohlc(ax1, newAr[-SP:], width=.6, colorup='#53c156', colordown='#ff1717')Label1= str(MA1)+' SMA'Label2= str(MA2)+' SMA'
ax1.plot(date[-SP:],Av1[-SP:],'#e1edf9',label=Label1, linewidth=1.5)
ax1.plot(date[-SP:],Av2[-SP:],'#4ee6fd',label=Label2, linewidth=1.5)
ax1.grid(True, color='w')
ax1.xaxis.set_major_locator(mticker.MaxNLocator(10))
ax1.xaxis.set_major_formatter(mdates.DateFormatter('%Y-%m-%d'))
ax1.yaxis.label.set_color("w")
ax1.spines['bottom'].set_color("#5998ff")
ax1.spines['top'].set_color("#5998ff")
ax1.spines['left'].set_color("#5998ff")
ax1.spines['right'].set_color("#5998ff")
ax1.tick_params(axis='y', colors='w')
plt.gca().yaxis.set_major_locator(mticker.MaxNLocator(prune='upper'))
ax1.tick_params(axis='x', colors='w')
plt.ylabel('Stock price and Volume')
maLeg = plt.legend(loc=9, ncol=2, prop={'size':7},
fancybox=True, borderaxespad=0.)
maLeg.get_frame().set_alpha(0.4)
textEd = pylab.gca().get_legend().get_texts()
pylab.setp(textEd[0:5], color ='w')
volumeMin =0
ax0 = plt.subplot2grid((6,4),(0,0), sharex=ax1, rowspan=1, colspan=4, axisbg='#07000d')
rsi = rsiFunc(closep)
rsiCol ='#c1f9f7'
posCol ='#386d13'
negCol ='#8f2020'
ax0.plot(date[-SP:], rsi[-SP:], rsiCol, linewidth=1.5)
ax0.axhline(70, color=negCol)
ax0.axhline(30, color=posCol)
ax0.fill_between(date[-SP:], rsi[-SP:],70,where=(rsi[-SP:]>=70), facecolor=negCol, edgecolor=negCol, alpha=0.5)
ax0.fill_between(date[-SP:], rsi[-SP:],30,where=(rsi[-SP:]<=30), facecolor=posCol, edgecolor=posCol, alpha=0.5)
ax0.set_yticks([30,70])
ax0.yaxis.label.set_color("w")
ax0.spines['bottom'].set_color("#5998ff")
ax0.spines['top'].set_color("#5998ff")
ax0.spines['left'].set_color("#5998ff")
ax0.spines['right'].set_color("#5998ff")
ax0.tick_params(axis='y', colors='w')
ax0.tick_params(axis='x', colors='w')
plt.ylabel('RSI')
ax1v = ax1.twinx()
ax1v.fill_between(date[-SP:],volumeMin, volume[-SP:], facecolor='#00ffe8', alpha=.4)
ax1v.axes.yaxis.set_ticklabels([])
ax1v.grid(False)###Edit this to 3, so it's a bit larger
ax1v.set_ylim(0,3*volume.max())
ax1v.spines['bottom'].set_color("#5998ff")
ax1v.spines['top'].set_color("#5998ff")
ax1v.spines['left'].set_color("#5998ff")
ax1v.spines['right'].set_color("#5998ff")
ax1v.tick_params(axis='x', colors='w')
ax1v.tick_params(axis='y', colors='w')
ax2 = plt.subplot2grid((6,4),(5,0), sharex=ax1, rowspan=1, colspan=4, axisbg='#07000d')
fillcolor ='#00ffe8'
nslow =26
nfast =12
nema =9
emaslow, emafast, macd = computeMACD(closep)
ema9 =ExpMovingAverage(macd, nema)
ax2.plot(date[-SP:], macd[-SP:], color='#4ee6fd', lw=2)
ax2.plot(date[-SP:], ema9[-SP:], color='#e1edf9', lw=1)
ax2.fill_between(date[-SP:], macd[-SP:]-ema9[-SP:],0, alpha=0.5, facecolor=fillcolor, edgecolor=fillcolor)
plt.gca().yaxis.set_major_locator(mticker.MaxNLocator(prune='upper'))
ax2.spines['bottom'].set_color("#5998ff")
ax2.spines['top'].set_color("#5998ff")
ax2.spines['left'].set_color("#5998ff")
ax2.spines['right'].set_color("#5998ff")
ax2.tick_params(axis='x', colors='w')
ax2.tick_params(axis='y', colors='w')
plt.ylabel('MACD', color='w')
ax2.yaxis.set_major_locator(mticker.MaxNLocator(nbins=5, prune='upper'))for label in ax2.xaxis.get_ticklabels():
label.set_rotation(45)
plt.suptitle(stock.upper(),color='w')
plt.setp(ax0.get_xticklabels(), visible=False)
plt.setp(ax1.get_xticklabels(), visible=False)
ax1.annotate('Big news!',(date[510],Av1[510]),
xytext=(0.8,0.9), textcoords='axes fraction',
arrowprops=dict(facecolor='white', shrink=0.05),
fontsize=14, color ='w',
horizontalalignment='right', verticalalignment='bottom')
plt.subplots_adjust(left=.09, bottom=.14, right=.94, top=.95, wspace=.20, hspace=0)
plt.show()
fig.savefig('example.png',facecolor=fig.get_facecolor())exceptExceptionas e:print('main loop',str(e))whileTrue:
stock = input('Stock to plot: ')
graphData(stock,10,50)
# THIS VERSION IS FOR PYTHON 2 #import urllib2
import time
import datetime
import numpy as np
import matplotlib.pyplot as plt
import matplotlib.ticker as mticker
import matplotlib.dates as mdates
from matplotlib.finance import candlestick
import matplotlib
import pylab
matplotlib.rcParams.update({'font.size':9})
eachStock ='EBAY','TSLA','AAPL'def rsiFunc(prices, n=14):
deltas = np.diff(prices)
seed = deltas[:n+1]
up = seed[seed>=0].sum()/n
down =-seed[seed<0].sum()/n
rs = up/down
rsi = np.zeros_like(prices)
rsi[:n]=100.-100./(1.+rs)for i in range(n, len(prices)):
delta = deltas[i-1]# cause the diff is 1 shorterif delta>0:
upval = delta
downval =0.else:
upval =0.
downval =-delta
up =(up*(n-1)+ upval)/n
down =(down*(n-1)+ downval)/n
rs = up/down
rsi[i]=100.-100./(1.+rs)return rsi
def movingaverage(values,window):
weigths = np.repeat(1.0, window)/window
smas = np.convolve(values, weigths,'valid')return smas # as a numpy arraydefExpMovingAverage(values, window):
weights = np.exp(np.linspace(-1.,0., window))
weights /= weights.sum()
a = np.convolve(values, weights, mode='full')[:len(values)]
a[:window]= a[window]return a
def computeMACD(x, slow=26, fast=12):"""
compute the MACD (Moving Average Convergence/Divergence) using a fast and slow exponential moving avg'
return value is emaslow, emafast, macd which are len(x) arrays
"""
emaslow =ExpMovingAverage(x, slow)
emafast =ExpMovingAverage(x, fast)return emaslow, emafast, emafast - emaslow
def graphData(stock,MA1,MA2):'''
Use this to dynamically pull a stock:
'''try:print'Currently Pulling',stock
print str(datetime.datetime.fromtimestamp(int(time.time())).strftime('%Y-%m-%d %H:%M:%S'))
urlToVisit ='http://chartapi.finance.yahoo.com/instrument/1.0/'+stock+'/chartdata;type=quote;range=10y/csv'
stockFile =[]try:
sourceCode = urllib2.urlopen(urlToVisit).read()
splitSource = sourceCode.split('\n')for eachLine in splitSource:
splitLine = eachLine.split(',')if len(splitLine)==6:if'values'notin eachLine:
stockFile.append(eachLine)exceptException, e:print str(e),'failed to organize pulled data.'exceptException,e:print str(e),'failed to pull pricing data'try:
date, closep, highp, lowp, openp, volume = np.loadtxt(stockFile,delimiter=',', unpack=True,
converters={0: mdates.strpdate2num('%Y%m%d')})
x =0
y = len(date)
newAr =[]while x < y:
appendLine = date[x],openp[x],closep[x],highp[x],lowp[x],volume[x]
newAr.append(appendLine)
x+=1Av1= movingaverage(closep, MA1)Av2= movingaverage(closep, MA2)
SP = len(date[MA2-1:])
fig = plt.figure(facecolor='#07000d')
ax1 = plt.subplot2grid((6,4),(1,0), rowspan=4, colspan=4, axisbg='#07000d')
candlestick(ax1, newAr[-SP:], width=.6, colorup='#53c156', colordown='#ff1717')Label1= str(MA1)+' SMA'Label2= str(MA2)+' SMA'
ax1.plot(date[-SP:],Av1[-SP:],'#e1edf9',label=Label1, linewidth=1.5)
ax1.plot(date[-SP:],Av2[-SP:],'#4ee6fd',label=Label2, linewidth=1.5)
ax1.grid(True, color='w')
ax1.xaxis.set_major_locator(mticker.MaxNLocator(10))
ax1.xaxis.set_major_formatter(mdates.DateFormatter('%Y-%m-%d'))
ax1.yaxis.label.set_color("w")
ax1.spines['bottom'].set_color("#5998ff")
ax1.spines['top'].set_color("#5998ff")
ax1.spines['left'].set_color("#5998ff")
ax1.spines['right'].set_color("#5998ff")
ax1.tick_params(axis='y', colors='w')
plt.gca().yaxis.set_major_locator(mticker.MaxNLocator(prune='upper'))
ax1.tick_params(axis='x', colors='w')
plt.ylabel('Stock price and Volume')
maLeg = plt.legend(loc=9, ncol=2, prop={'size':7},
fancybox=True, borderaxespad=0.)
maLeg.get_frame().set_alpha(0.4)
textEd = pylab.gca().get_legend().get_texts()
pylab.setp(textEd[0:5], color ='w')
volumeMin =0
ax0 = plt.subplot2grid((6,4),(0,0), sharex=ax1, rowspan=1, colspan=4, axisbg='#07000d')
rsi = rsiFunc(closep)
rsiCol ='#c1f9f7'
posCol ='#386d13'
negCol ='#8f2020'
ax0.plot(date[-SP:], rsi[-SP:], rsiCol, linewidth=1.5)
ax0.axhline(70, color=negCol)
ax0.axhline(30, color=posCol)
ax0.fill_between(date[-SP:], rsi[-SP:],70,where=(rsi[-SP:]>=70), facecolor=negCol, edgecolor=negCol, alpha=0.5)
ax0.fill_between(date[-SP:], rsi[-SP:],30,where=(rsi[-SP:]<=30), facecolor=posCol, edgecolor=posCol, alpha=0.5)
ax0.set_yticks([30,70])
ax0.yaxis.label.set_color("w")
ax0.spines['bottom'].set_color("#5998ff")
ax0.spines['top'].set_color("#5998ff")
ax0.spines['left'].set_color("#5998ff")
ax0.spines['right'].set_color("#5998ff")
ax0.tick_params(axis='y', colors='w')
ax0.tick_params(axis='x', colors='w')
plt.ylabel('RSI')
ax1v = ax1.twinx()
ax1v.fill_between(date[-SP:],volumeMin, volume[-SP:], facecolor='#00ffe8', alpha=.4)
ax1v.axes.yaxis.set_ticklabels([])
ax1v.grid(False)###Edit this to 3, so it's a bit larger
ax1v.set_ylim(0,3*volume.max())
ax1v.spines['bottom'].set_color("#5998ff")
ax1v.spines['top'].set_color("#5998ff")
ax1v.spines['left'].set_color("#5998ff")
ax1v.spines['right'].set_color("#5998ff")
ax1v.tick_params(axis='x', colors='w')
ax1v.tick_params(axis='y', colors='w')
ax2 = plt.subplot2grid((6,4),(5,0), sharex=ax1, rowspan=1, colspan=4, axisbg='#07000d')
fillcolor ='#00ffe8'
nslow =26
nfast =12
nema =9
emaslow, emafast, macd = computeMACD(closep)
ema9 =ExpMovingAverage(macd, nema)
ax2.plot(date[-SP:], macd[-SP:], color='#4ee6fd', lw=2)
ax2.plot(date[-SP:], ema9[-SP:], color='#e1edf9', lw=1)
ax2.fill_between(date[-SP:], macd[-SP:]-ema9[-SP:],0, alpha=0.5, facecolor=fillcolor, edgecolor=fillcolor)
plt.gca().yaxis.set_major_locator(mticker.MaxNLocator(prune='upper'))
ax2.spines['bottom'].set_color("#5998ff")
ax2.spines['top'].set_color("#5998ff")
ax2.spines['left'].set_color("#5998ff")
ax2.spines['right'].set_color("#5998ff")
ax2.tick_params(axis='x', colors='w')
ax2.tick_params(axis='y', colors='w')
plt.ylabel('MACD', color='w')
ax2.yaxis.set_major_locator(mticker.MaxNLocator(nbins=5, prune='upper'))for label in ax2.xaxis.get_ticklabels():
label.set_rotation(45)
plt.suptitle(stock.upper(),color='w')
plt.setp(ax0.get_xticklabels(), visible=False)
plt.setp(ax1.get_xticklabels(), visible=False)
ax1.annotate('Big news!',(date[510],Av1[510]),
xytext=(0.8,0.9), textcoords='axes fraction',
arrowprops=dict(facecolor='white', shrink=0.05),
fontsize=14, color ='w',
horizontalalignment='right', verticalalignment='bottom')
plt.subplots_adjust(left=.09, bottom=.14, right=.94, top=.95, wspace=.20, hspace=0)
plt.show()
fig.savefig('example.png',facecolor=fig.get_facecolor())exceptException,e:print'main loop',str(e)whileTrue:
stock = raw_input('Stock to plot: ')
graphData(stock,10,50)