Updating Table from KDB+ to SQL Server using Python

Question

I would like to have a table in SQL server update daily from a table that is already updated daily in KDB. I have connected ny SQL database to python and plan to do the same with the KDB database but I am not sure what my next step would be to have the KDB table update the SQL table.

Here is my code so far which successfully connects to the database

import pyodbc
db = pyodbc.connect(
    r'DRIVER={SQL Server Native Client 10.0};'
    r'SERVER=serverName;'
    r'DATABASE=DBName;'
    r'UID=UN;'
    r'PWD=PW') 
cur = db.cursor()
SQLCommand = ("SELECT * "
               "FROM Table1") 
cur.execute(SQLCommand)
results = cur.fetchone() 
while results:
     results = cur.fetchall()

I'm not entirely sure this is an appropriate question. Seems like you really need to read up on the KDB+ documentation? There appears to be a relevant Python library (http://code.kx.com/wiki/Contrib/PyQ#PyQ:_Python_Integration). That would solve 'accessing' the KDB+ instance. Then you have to figure out the mappings from one database to the other, and that should do it. — selllikesybok, Jun 15 '17 at 22:03

Simon Major · Answer 1 · 2017-06-16T12:04:21.933

So while you can use Python as an intermediary here, why bother? You can instead use the ODBC driver natively in kdb+ as explained here.

You'll need to write something to create your querystrings for insertion, selection etc., but this is pretty easily done.

If you really must use Python, exxeleron's qpython package is the easiest way for handling python <-> q IPC (you can use pyQ as above but if you're deadset on using Python then you probably only want a plug-in Python module).

\l odbc.k
odbcHandle: .odbc.open"Driver={SQL Server Native Client 10.0};SERVER=serverName;DATABASE=DBName;UID=UN;PWD=PW";
makeCall: .odbc.eval[odbcHandle];
data: makeCall"SELECT * FROM tableName";
countQuery:"SELECT COUNT(*) AS ct FROM otherTable";
ct: first exec ct from makeCall countQuery;
makeCall"INSERT INTO otherTable (col1, col2) VALUES ('a', 'b');"; //you can also generate this string dynamically - this is an exercise for the reader
ct2: first exec ct from makeCall countQuery;
ct - ct2; // 1
.odbc.close odbcHandle

score 0 · Answer 2 · answered Jun 17 '17 at 01:39

If you can use PyQ, then fetching the data from kdb+ tables is trivial. Suppose you have a table called t:

>>> from pyq import q
>>> q.load(':/path/to/t')  # if t is not already loaded
>>> q.t.show()
a b
----
1 10
2 20
3 30
4 40
5 50

To convert this data to a list of tuples that can be sent over PyODBC, simply iterate over the table in a list comprehension:

>>> data = [tuple(row.value) for row in q.t]
>>> data
[(1, 10), (2, 20), (3, 30), (4, 40), (5, 50)]

This list can be sent the SQL server using the executemany method:

cur.executemany("insert into t(a, b) values (?, ?)", data)

Updating Table from KDB+ to SQL Server using Python

2 Answers2