• kdb+

  ODBC setup for KDB+ to query other databases

  Posted by darrenwsun on March 15, 2023 at 12:00 am

  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.

  darrenwsun replied 8 months, 2 weeks ago 2 Members · 1 Reply
• 1 Reply

• 

  rocuinneagain

  Member

  March 15, 2023 at 12:00 am

  ODBC:

  • kdb/odbc.pdf at master johnanthonyludlow/kdb GitHub
  • kdb/odbc.txt at master KxSystems/kdb GitHub
  • kdb/odbc.k at master KxSystems/kdb GitHub

  JDBC:

  • GitHub – CharlesSkelton/babel: Babel for kdb+ / Query any database from kdb+ via 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];

   

   

Log In to Reply