Venus Database Integrations (Connection string for remote Postgres?)

Hamilton (Venus)
1

Hello,

I’ve recently started playing around with the connection string in the File: Open dialog box and ultimately, I’d like to interface with a remote postgres server to log/query run information.

Previously I’ve had success connecting to a local xlsx file using command connection strings for querying data and updating information.

File Path: “”

Command String: “SELECT * FROM [Sheet1$]”

Connection String: “Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Location\example.xlsx;Extended Properties="Excel 12.0 Xml;HDR=YES";”

Source: Excel connection strings - ConnectionStrings.com

(You can even have the file in excel open during the run :wink: and watch it update rows in real time)

This got me thinking about if it was possible to connect to a remote SQL server using the same methodology. I still have a lot to learn about windows database connection drivers (ODBC, OLEDB, ACE, etc), but it seems like it should be fairly straight forward:

  1. Install psqlodbc driver (I don’t believe I can use any default windows drivers for this)
  2. Determine connection string that correctly references the new driver with db details

I’m curious if anyone has had success directly interfacing with a remote SQL database using this method and if there were any helpful resources you found along the way.

3 Likes
2

The following post discusses this specific topic, and contains numerous links to examples and reference material for using VENUS to interface directly with SQL (and other style) databases, as well as a link to guidance on connection strings and database drivers for different database options. Based off of the example content in your post, you are likely knocking on the door already.

https://forums.pylabrobot.org/t/best-way-to-interface-with-sqlite-database/1217

Good luck, and hope this helps.

-Nick

3

Thanks for the expeditious response. I was able to successfully connect to a local database instance using the 32bit psqlodbc driver.

This is the connection string that worked for me

“Provider=MSDASQL.1;Persist Security Info=False;User ID=postgres;Data Source=PostgreSQL35W”

PostgreSQL35W is the name of an ODBC data source (32bit) I made using the psqlodbc driver configured as a User DSN on the local machine.

I got the connection string by using this method
UDL files and connection strings – SQLServerCentral

2 Likes
4

Awesome! Thanks for sharing the working parameters.

-Nick