cancel
Showing results for 
Search instead for 
Did you mean: 

ODBC setup for KDB+ to query other databases

darrenwsun
New Contributor III

Hi community,

There used to be this link that provides the instructions for the setup, but it's no longer valid and I cannot find out the updated link anywhere. Note that it's for the reverse direction of querying KDB+ via ODBC where the setup is easily found. 

Thanks for the help.

  • T
1 ACCEPTED SOLUTION

rocuinneagain
Valued Contributor
Valued Contributor

ODBC:

JDBC:

 

EmbedPy is also an option which has a lot of flexibility.

Some example of Pandas/PyODBC/SQLAlchemy usage below

 

// https://github.com/KxSystems/embedPy
system"l p.q";

// https://github.com/KxSystems/ml
system"l ml/ml.q";
.ml.loadfile`:init.q;
 
odbc:.p.import[`pyodbc];
pd:.p.import[`pandas];

connectString:";" sv {string[x],"=",y}(.)/:(
    (`Driver;"{ODBC Driver 17 for SQL Server}");
    (`Server;"server.domain.com\\DB01");
    (`Database;"Data");
    (`UID;"KX");
    (`PWD;"password")
    );
connSqlServer:odbc[`:connect][connectString];

data:.ml.df2tab pd[`:read_sql]["SELECT * FROM tableName";connSqlServer];

cursor:connSqlServer[`:cursor][];
cursor[`:execute]["TRUNCATE FROM tableName"];
connSqlServer[`:commit][];

sa:.p.import`sqlalchemy;
engine:sa[`:create_engine]["mssql+pyodbc://KX:password@server.domain.com\\DB01/Data?driver=ODBC+Driver+17+for+SQL+Server"];
df:.ml.tab2df[data]; //Data to publish
df[`:to_sql]["tableName";engine; `if_exists pykw `append;`index pykw 0b];

 

 

View solution in original post

1 REPLY 1

rocuinneagain
Valued Contributor
Valued Contributor

ODBC:

JDBC:

 

EmbedPy is also an option which has a lot of flexibility.

Some example of Pandas/PyODBC/SQLAlchemy usage below

 

// https://github.com/KxSystems/embedPy
system"l p.q";

// https://github.com/KxSystems/ml
system"l ml/ml.q";
.ml.loadfile`:init.q;
 
odbc:.p.import[`pyodbc];
pd:.p.import[`pandas];

connectString:";" sv {string[x],"=",y}(.)/:(
    (`Driver;"{ODBC Driver 17 for SQL Server}");
    (`Server;"server.domain.com\\DB01");
    (`Database;"Data");
    (`UID;"KX");
    (`PWD;"password")
    );
connSqlServer:odbc[`:connect][connectString];

data:.ml.df2tab pd[`:read_sql]["SELECT * FROM tableName";connSqlServer];

cursor:connSqlServer[`:cursor][];
cursor[`:execute]["TRUNCATE FROM tableName"];
connSqlServer[`:commit][];

sa:.p.import`sqlalchemy;
engine:sa[`:create_engine]["mssql+pyodbc://KX:password@server.domain.com\\DB01/Data?driver=ODBC+Driver+17+for+SQL+Server"];
df:.ml.tab2df[data]; //Data to publish
df[`:to_sql]["tableName";engine; `if_exists pykw `append;`index pykw 0b];