GenAI: Generate SQL from Text via Ollama Python Library on Local

event 2024-03-24 thumb_up 0 visibility 91 comment 0 insights toc

more_vert

warning Please login first to view stats information.

About Ollama
Install Ollama
Run a LLM
Use Ollama Python library
Install Python library
Use the library
Run the script
More information

GenAI: Generate SQL from Text via Ollama Python Library on Local

This tutorial shows you how to use Ollama Python library to connect to Ollama REST APIs to generate SQL statements from text.

About Ollama

Ollama is a fascinating project that allows you to run large language models (LLMs**)** locally on your machine. You can choose from a variety of models within Ollama: library (ollama.com).

Install Ollama

You can download Ollama installer from Ollama website.

For Windows users, you may encounter an error - could not connect to ollama app, is it running. Follow the page below to resolve it:

Ollama: Error: could not connect to ollama app, is it running? (kontext.tech)

Run a LLM

There are a few open source models that can be used to convert text to SQL statements. This tutorial uses model duckdb-nsql.

Run the following command to pull and run the model once Ollama client is installed:

ollama run duckdb-nsql

Please keep in mind that running the larger models (e.g., 7B, 13B, or 33B) requires sufficient RAM—at least 8 GB for 7B models, 16 GB for 13B models, and 32 GB for 33B models.

Once the model is downloaded, you should be able to open the following URL in browser:

localhost:11434

The result looks like the following screenshot:

This endpoint will be used in the following sections.

Now let's run some demo in the command line interface:

Input the following message:

/set system """Here is the database schema that the SQL query will run on:
CREATE TABLE products (
  product_id INTEGER PRIMARY KEY, -- Unique ID for each product
  name VARCHAR(50), -- Name of the product
  price DECIMAL(10,2), -- Price of each unit of the product
  quantity INTEGER  -- Current quantity in stock
);
"""

The above command sets a system message:

Now input the following message to generate a SQL statement:

Get top 10 products by price

The model generates the following output as expected:

SELECT * FROM products ORDER BY price DESC LIMIT 10;

Use Ollama Python library

Now let's implement the same using Python.

Install Python library

Create a Python project with your favorite tool.

Install the package using the following command:

pip install ollama

Use the library

Now we can use the installed library to convert text to SQL.

Create a Python script with the following content:

from ollama import Client

endpoint = 'http://localhost:11434'
model = 'duckdb-nsql'
client = Client(host=endpoint)
response = client.chat(model=model, messages=[
  {
    'role': 'system',
    'content': """Here is the database schema that the SQL query will run on:
CREATE TABLE products (
  product_id INTEGER PRIMARY KEY, -- Unique ID for each product
  name VARCHAR(50), -- Name of the product
  price DECIMAL(10,2), -- Price of each unit of the product
  quantity INTEGER  -- Current quantity in stock
);
""",
  },
])

print(response)

response = client.chat(model=model, messages=[
  {
    'role': 'user',
    'content': 'Get top 10 products by price',
  },
])

print(response)

# Print out the SQL statement directly.
print(response['message']['content'])

Run the script

Run the Python script, you will get the following out:

{'model': 'duckdb-nsql', 'created_at': '2024-03-24T10:26:12.2886171Z', 'message': {'role': 'assistant', 'content': ' SELECT product_id, name, price, quantity\nFROM products;'}, 'done': True, 'total_duration': 1620373400, 'load_duration': 1149000, 'prompt_eval_count': 109, 'prompt_eval_duration': 617590000, 'eval_count': 15, 'eval_duration': 1000338000}        
{'model': 'duckdb-nsql', 'created_at': '2024-03-24T10:26:13.8730534Z', 'message': {'role': 'assistant', 'content': ' SELECT product_name, price FROM products ORDER BY price DESC LIMIT 10;'}, 'done': True, 'total_duration': 1582343200, 'load_duration': 1113500, 'prompt_eval_count': 32, 'prompt_eval_duration': 393627000, 'eval_count': 18, 'eval_duration': 1186633000}
 SELECT product_name, price FROM products ORDER BY price DESC LIMIT 10;

The last output is the SQL statement.

Note - the SQL statement generate here is slightly different from the CLI output.

More information

Hopefully you get a gist of how to run open source LLMs locally in your computer to develop applications with Python. Have fun!

For more examples, refer to https://github.com/ollama/ollama-python.