programmatically interacting with databases and running queries in local scripts

[wdduncan]

Sorry if I missed how to do this, but can I use logica in a local script. In general, I would like to use logica to execute my queries programmatically instead of SQL (and not have having to run on collab).

For example, programmatically, I can connect to local db and interact with data something like this:

import sqlite3, pandas
cn = sqlite3.connect('example.db')
df = pandas..read_sql_query('select * from table', cn)

# do more operations on the datafame ....

Does logica have modules/functionality that would allow me to this without having to read in logica scripts (e.g., *.l files)?

Or can I pass in the logica script as a string?

Thanks!

[EvgSkv]

Yes, you could always do it, but I've just added a convenience function to do this simpler. Example usage:

numbers_df = logica_lib.RunPredicateFromString("""
@Engine("sqlite");

N() = 0;
N() = N() + 1;

""", 'N')

CoLab: https://colab.research.google.com/drive/17xeEZOWqYH78mwEYIP8CKbcMGiH1TjBM?usp=sharing

There is also a connection argument, so you access existing database. See the CoLab for examples.

Let me know if this helps!

[wdduncan]

Thanks! I'll give it a try. Sorry for not noticing the RunPredicateFromString method.

Also, it is possible to retrieve the SQL generated by logica?

[EvgSkv]

Sorry for not noticing the RunPredicateFromString method.

Oh, sorry, I just added RunPredicateFromString.
Before you'd basically had to implement it yourself, which is not convenient and not obvious.

Also, it is possible to retrieve the SQL generated by logica?

I've added sql_receiver argument to RunPredicateFromString and added CompilePredicateFromString in case you want to simply compile without running.

Example usage:

from logica.common import logica_lib

s = logica_lib.SqlReceiver()

print(logica_lib.RunPredicateFromString("""
@Engine("sqlite");
N() = 0;
N() = N() + 1;
""", 'N', sql_receiver=s))

print(s.sql)

print(
logica_lib.CompilePredicateFromString("""
@Engine("sqlite");
N() = 0;
N() = N() + 1;
""", 'N')
)

Let me know if you encounter any issues.