我是資深的電腦資訊從業員,曾於 Motorola 及 Philips 等跨國大型公司管理層工作十多年,具各類ERP資源管理系統及其它應用系統經驗,如QAD之MFG/PRO、SAP、Ufida(用友)、Kingdee(金蝶)、Microsoft's Dynamic、Wonderware's In-Track (SFC)、Webplan (SCM)、Hyperion (business intelligence)、Informatics (Data Warehouse)...等等。另外,我精於廠房車間之電腦資訊運作,擁有 CISSP 及 ITIL 認證,能提供日常資訊運作之檢測及審查,以提高操作效率。 本人誠意為各類大中小型廠房提供資訊審計、支援及意見,歡迎聯絡,電郵為




我想很多人會對"香港股票價位的歷史數據"有興趣,我已下載成Microsoft Access database version 2000 的文檔,資料由2008/1/1至2009/12/2,zip壓縮後也有11M,若索取請留你的PM我 。




How to setup Python 3.4 to access MS SQL

Getting started

Generally, you will want to install pymssql with:
pip install pymssql
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 pip install pymssql 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.:
  • Ubuntu/Debian:
    sudo apt-get install freetds-dev
  • Mac OS X with Homebrew:
    brew install freetds


Another possible way to get started quickly with pymssql is to use a Docker image.
See the Docker docs for installation instructions for a number of platforms; you can try this link:
There is a pymssql docker image on the Docker Registry at:
It is a Docker image with:
  • Ubuntu 14.04 LTS (trusty)
  • Python 2.7.6
  • pymssql
  • FreeTDS 0.91
  • SQLAlchemy 0.9.8
  • Alembic 0.7.4
  • Pandas 0.15.2
  • Numpy 1.9.1
  • IPython 2.3.1
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'

_mssql examples

Example scripts using _mssql module.

Quickstart usage of various features

import _mssql
conn = _mssql.connect(server='SQL01', user='user', password='password', \
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 table
conn.execute_query('SELECT * FROM persons WHERE salesrep=%s', 'John Doe')
for row in conn:
    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 functions
numemployees = 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 here
employeedata = conn.execute_row("SELECT * FROM employees WHERE id=%d", 13)
# how to fetch rows from a stored procedure
conn.execute_query('sp_spaceused')   # sp_spaceused without arguments returns 2 result sets
res1 = [ row for row in conn ]       # 1st result
res2 = [ row for row in conn ]       # 2nd result
# how to get an output parameter from a stored procedure
sqlcmd = """
EXEC usp_mystoredproc @res OUT
res = conn.execute_scalar(sqlcmd)
# how to get more output parameters from a stored procedure
sqlcmd = """
DECLARE @res1 INT, @res2 TEXT, @res3 DATETIME
EXEC usp_getEmpData %d, %s, @res1 OUT, @res2 OUT, @res3 OUT
SELECT @res1, @res2, @res3
res = conn.execute_row(sqlcmd, (13, 'John Doe'))
# examples of queries with parameters
conn.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]),))
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.

An example of exception handling

import _mssql

conn = _mssql.connect(server='SQL01', user='user', password='password',
    conn.execute_non_query('CREATE TABLE t1(id INT, name VARCHAR(50))')
except _mssql.MssqlDatabaseException as e:
    if e.number == 2714 and e.severity == 16:
        # table already existed, so quieten the error
        raise # re-raise real error

Custom message handlers

New in version 2.1.1.
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 _mssql connection basis by using the _mssql.MSSQLConnection.set_msghandler() method:
import _mssql

def my_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')
    conn.set_msghandler(my_msg_handler)  # Install our custom handler
    cnx.execute_non_query("USE mydatabase")  # It gets called at this point