CODEWITHSUNDEEP
pythonsqlsql-serverkdb+pyq
michaelg
michaelg

Reputation: 243

Updating Table from KDB+ to SQL Server using Python

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()

Upvotes: 1

Views: 714

Answers (2)

Alexander Belopolsky
Alexander Belopolsky

Reputation: 2268

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)

Upvotes: 0

Simon Major
Simon Major

Reputation: 291

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

Upvotes: 1

Related Questions