PostgreSQL support

[mgathena]

Hi,

How do I make premsql work with PostgreSQL? The db is running in docker and the URL becomes "postgresql://postgres:postgres@localhost:5432/db". But it doesn't seem to work. The custom PostgreSQLExecutor mentioned here also doesn't work. Is there a working example for PostgreSQL?

When I say db_connection_uri=postgresql://postgres:postgres@localhost:5432/db, the error is

OperationalError: unable to open database file

I also tried to replace postgresql:// with postgresql+pg8000://

Thanks

[Anindyadeep]

Hi,

How do I make premsql work with PostgreSQL? The db is running in docker and the URL becomes "postgresql://postgres:postgres@localhost:5432/db". But it doesn't seem to work. The custom PostgreSQLExecutor mentioned here also doesn't work. Is there a working example for PostgreSQL?

When I say db_connection_uri=postgresql://postgres:postgres@localhost:5432/db, the error is

OperationalError: unable to open database file

I also tried to replace postgresql:// with postgresql+pg8000://

Thanks

Hi, thanks for the issue, I wanted to know what is the script you are using to connect to postgres (without using PremSQL), is postgres is getting connected with this:

import psycopg2
from psycopg2 import sql

db_connection_uri = "postgresql://postgres:postgres@localhost:5432/db"

try:
    conn = psycopg2.connect(db_connection_uri)
    cursor = conn.cursor()

    cursor.execute("SELECT version();")
    db_version = cursor.fetchone()

    print("Connected to:", db_version)
    cursor.close()
    conn.close()

except Exception as e:
    print("Error connecting to the database:", e)

[Anindyadeep]

Because if simply connecting to postgre is not working, then it would not work with PremSQL.

[mgathena]

Hi, thank you for reaching out.
Yes, this script does work. It prints out

Connected to: ('PostgreSQL 17.2 (Debian 17.2-1.pgdg120+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 12.2.0-14) 12.2.0, 64-bit',)

In fact, I experimented with Langchain's create_sql_query_chain and that was working too quite well (with gpt4o-mini). But I couldn't get proper results with the prem-1B-SQL model. And that is why is was interested in premsql itself.

[Anindyadeep]

Hi, thank you for reaching out. Yes, this script does work. It prints out

Connected to: ('PostgreSQL 17.2 (Debian 17.2-1.pgdg120+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 12.2.0-14) 12.2.0, 64-bit',)

In fact, I experimented with Langchain's create_sql_query_chain and that was working too quite well (with gpt4o-mini). But I couldn't get proper results with the prem-1B-SQL model. And that is why is was interested in premsql itself.

Yes, I mean you can still use PremSQL with other models (closed source) ones as well.

So if possible, can you let me know about this two things?

1. Its great langchain's sql_create_chain is working with gpt-4o-mini, but is your usecase working with PremSQL as well? If not, can you tell me more.
2. If PremSQL is working, but Prem-1B-SQL is not, can you tell me where you see prem-1b-sql is not able to work?

Thanks

[mgathena]

1. The query generated by premsql's model (via Langchain) wasn't good - even on basic queries. Switching to Llama also improved results
2. PremSQL (API) is working but I only got it to work (correctly) on a small sqlite local db file, which I only used for testing. I wasn't able to connect it to my PostgreSQL db where my actual data resides.
   I am looking for help on the second issue.

[Anindyadeep]

PremSQL (API) is working but I only got it to work (correctly) on a small sqlite local db file, which I only used for testing. I wasn't able to connect it to my PostgreSQL db where my actual data resides. I am looking for help on the second issue.

So you are not able to connect to postgres db using PremSQL?

[mgathena]

yes, that is the issue.

[Anindyadeep]

However you can connect your postgres with langchain? If yes, can you share me the code for it? Like what code you wrote for langchain with postgres?

[mgathena]

This is the code:

from langchain_openai import ChatOpenAI
llm = ChatOpenAI(model="gpt-4o-mini")
url = "postgresql://postgres:postgres@localhost:5432/db"
from langchain_community.utilities import SQLDatabase
db = SQLDatabase.from_uri(url)
sql_chain = create_sql_query_chain(llm, db)

[Anindyadeep]

And what code did you use in PremSQL which is not working? Like can you show me the code?

[mgathena]

Here

from premsql.agents import BaseLineAgent
from premsql.generators import Text2SQLGeneratorHF
from premsql.agents.tools import SimpleMatplotlibTool
from premsql.executors import SQLiteExecutor

text2_sqlmodel = Text2SQLGeneratorHF(
    model_or_name_or_path="premai-io/prem-1B-SQL",
    experiment_name="test_generators",
    device="cuda:0",
    type="test"
)

analyser_and_plotter = Text2SQLGeneratorHF(
    model_or_name_or_path="HuggingFaceTB/SmolLM2-360M-Instruct",
    experiment_name="test_generators",
    device="cuda:0",
    type="test"
)
url = "postgresql://postgres:postgres@localhost:5432/db"

agent = BaseLineAgent(
    session_name="testing_hf",
    db_connection_uri=url,
    specialized_model1=text2_sqlmodel,
    specialized_model2=analyser_and_plotter,
    plot_tool=SimpleMatplotlibTool(),
    executor=SQLiteExecutor()
)
question = "Can you give me the name of all departments?"

response = agent(
    f"/query {question}"
)

The response is

OperationalError: unable to open database file

[Anindyadeep]

Ok I see, can you try this

from premsql.agents import BaseLineAgent
from premsql.generators import Text2SQLGeneratorHF
from premsql.agents.tools import SimpleMatplotlibTool
from premsql.executors import ExecutorUsingLangChain

text2_sqlmodel = Text2SQLGeneratorHF(
    model_or_name_or_path="premai-io/prem-1B-SQL",
    experiment_name="test_generators",
    device="cuda:0",
    type="test"
)

analyser_and_plotter = Text2SQLGeneratorHF(
    model_or_name_or_path="HuggingFaceTB/SmolLM2-360M-Instruct",
    experiment_name="test_generators",
    device="cuda:0",
    type="test"
)
url = "postgresql://postgres:postgres@localhost:5432/db"

agent = BaseLineAgent(
    session_name="testing_hf",
    db_connection_uri=url,
    specialized_model1=text2_sqlmodel,
    specialized_model2=analyser_and_plotter,
    plot_tool=SimpleMatplotlibTool(),
    executor=ExecutorUsingLangChain()
)
question = "Can you give me the name of all departments?"

response = agent(
    f"/query {question}"
)

Can you check now??

[mgathena]

The query gets executed (at least). But the results are incorrect. e.g. it generates
SELECT DNAME FROM dept; and then gives error Couldn't run sql: column "dname" does not exist. The correct query should have been SELECT "DNAME" FROM dept;