ODBC setup for KDB+ to query other databases

Community Support
1

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.

2

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,“=”,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];