How to Create Database Application with AI for Text to SQL

This video shows how to create a database application with Ollama, LLM Duckdb 7B, LlamaIndex and MySQL locally.

Code:

sudo apt update

sudo apt install mysql-server

sudo systemctl start mysql.service

sudo mysql

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';

exit

sudo mysql_secure_installation

mysql -u root -p

ALTER USER 'root'@'localhost' IDENTIFIED WITH auth_socket;

CREATE USER 'myllm'@'localhost' IDENTIFIED BY 'my_L1Mpa3s1wd';

GRANT ALL PRIVILEGES ON *.* TO 'myllm'@'localhost' WITH GRANT OPTION;

FLUSH PRIVILEGES;

exit

mysql -u myllm -p

CREATE DATABASE mydatabase;

USE mydatabase;

CREATE TABLE mytable (

  id INT AUTO_INCREMENT,

  name VARCHAR(255),

  email VARCHAR(255),

  PRIMARY KEY (id)

);

INSERT INTO mytable (name, email) VALUES

  ('John Doe', 'john.doe@example.com'),

  ('Jane Smith', 'jane.smith@example.com'),

  ('Bob Johnson', 'bob.johnson@example.com'),

  ('Alice Brown', 'alice.brown@example.com'),

  ('Mike Davis', 'mike.davis@example.com');

 

 

systemctl status mysql.service

====================

ollama run duckdb-nsql

===============

pip install llama-index-llms-ollama llama-index SQLAlchemy pymysql llama-index-embeddings-huggingface

Create file db.config.py and insert following:

db_user = "myllm"

db_password = "my_L1Mpa3s1wd"

db_host = "localhost"

db_port = "3306"

db_name = "mydatabase"

Launch Python3 interpreter and run following:

import db_config

from sqlalchemy import create_engine, text

from llama_index.llms.ollama import Ollama

from llama_index.core import SQLDatabase

from llama_index.core.query_engine import NLSQLTableQueryEngine

from llama_index.embeddings.huggingface import HuggingFaceEmbedding

from llama_index.core import Settings

def get_connection():

  return create_engine(

    url="mysql+pymysql://{0}:{1}@{2}:{3}/{4}".format(

      db_config.db_user, db_config.db_password, db_config.db_host, db_config.db_port, db_config.db_name

    )

  )

llm = Ollama(model="duckdb-nsql", request_timeout=30.0)

print("Selected Model :: ", llm.model)

Settings.embed_model = HuggingFaceEmbedding(model_name="BAAI/bge-small-en-v1.5")

engine = get_connection()

db_tables = ["mytable"]

sql_database = SQLDatabase(engine, include_tables=db_tables)

query_engine = NLSQLTableQueryEngine(sql_database=sql_database,tables=db_tables,llm=llm)

   

query_str = "Find number of  emails."

response = query_engine.query(query_str)

print(response.metadata['sql_query'])

print(response.metadata['result'])

print("Run generated SQL query on database ::>")

with engine.connect() as connection:

  results = connection.execute(text(response.metadata['sql_query']))

  print(results.first())