Selfhosted

Query MySQL with Python and LM Studio: Build Your Own AI-Powered Data Assistant

Query MySQL with Python and LM Studio: Build Your Own AI-Powered Data Assistant
Photo by Adi Goldstein / Unsplash

If you've ever wanted to query your database using plain English—without memorizing SQL syntax—LM Studio makes it possible. By pairing Python with a local LLM (Large Language Model), you can build a private, offline assistant that understands your data and speaks fluent SQL.

🧰 What You’ll Need

  • Python 3.8+
  • MySQL server (local or remote)
  • LM Studio installed with a code-capable LLM (e.g., Mistral, LLaMA, Code LLM)
  • Python packages: mysql-connector-python, requests
pip install mysql-connector-python requests

🔌 Step 1: Connect Python to MySQL

import mysql.connector

def query_mysql(sql_query):
    conn = mysql.connector.connect(
        host="localhost",
        user="your_username",
        password="your_password",
        database="your_database"
    )
    cursor = conn.cursor()
    cursor.execute(sql_query)
    results = cursor.fetchall()
    cursor.close()
    conn.close()
    return results

🧠 Step 2: Send Natural Language to LM Studio

LM Studio exposes a local API endpoint. Here’s how to send a prompt and receive SQL:

import requests

def ask_lmstudio(natural_query):
    prompt = f"Convert this natural language query into SQL: '{natural_query}'"
    payload = {
        "model": "your-model-name",
        "messages": [{"role": "user", "content": prompt}],
        "temperature": 0.3
    }
    response = requests.post("http://localhost:1234/v1/chat/completions", json=payload)
    sql = response.json()["choices"][0]["message"]["content"]
    return sql.strip()

🔄 Step 3: Combine the Magic

def natural_query_to_mysql(nl_query):
    sql_query = ask_lmstudio(nl_query)
    print(f"Generated SQL: {sql_query}")
    results = query_mysql(sql_query)
    return results

# Example usage
nl_query = "Show me the top 5 customers by total purchase amount"
data = natural_query_to_mysql(nl_query)
for row in data:
    print(row)

🛡️ Safety Tips

  • ✅ Use a read-only MySQL user
  • ✅ Review generated SQL before execution
  • ✅ Add schema context to prompts for better accuracy

🚀 Final Thoughts

This setup turns LM Studio into a private SQL assistant—ideal for analysts, developers, or anyone exploring data. You can even build a chatbot or dashboard around it.

Want to take it further? Add a Flask front-end, voice input, or even Discord notifications. The possibilities are endless when your database speaks your language.

Read more