8/27/2024

Setting Up Ollama with MySQL Databases: The Ultimate Guide

If you’ve ever thought about integrating Large Language Models (LLMs) into your workflow, look no further! This guide will take you through the process of setting up Ollama with MySQL databases, enabling you to develop sophisticated applications and gain insights from your data in ways you never imagined. So grab a cup of coffee, because we’re in for a ride!

What is Ollama?

Before we dive into the setup, let’s clarify what Ollama is. Ollama serves as a powerful open-source tool that provides a seamless way to run various LLMs on your local machine without grappling with hefty cloud costs. According to Ollama's official site, it gives users the ability to personalize their models and control privacy, making it an ideal choice for businesses handling sensitive information.

Benefits of Using Ollama

Local Execution: Run models directly on your hardware!
Cost-Efficient: Save big by avoiding cloud-based services.
Flexibility: Supports various models, like Llama 2, Mistral, and more.
Easy Integration: Easily integrates with databases like MySQL and PostgreSQL.
Before You Begin: System Requirements
To get started, here are the recommended system requirements according to the official Ollama documentation:
Operating System: Ubuntu 20.04, macOS 11, or later versions.
RAM: At least 8GB for running smaller models, and 32GB for larger ones.
Disk Space: About 12GB for installing Ollama along with additional space for models.
CPU: Minimum of 4 cores, ideally 8 cores for better performance.
GPU (Optional): Utilizing a GPU will enhance performance significantly.

Step 1: Install Ollama

To begin, you’ll need to install the Ollama framework. To do this, simply run:

1
2

bash
curl https://ollama.ai/install.sh | sh

By following Ollama’s installation guide, you can get it set up effortlessly.

Pulling Your Desired Model

Once you've installed Ollama, pull the model you wish to use. For example:

1
2

bash
ollama pull llama2:7b-chat

This command will download the Llama 2 model, which is known for its conversational accuracy.

Step 2: Setting Up MySQL

Next, we’ll need to set up your MySQL database. If you don’t have it installed already, you can find the necessary installation instructions on MySQL’s official website.

Create Your Database

Once MySQL is installed:

Open your terminal and log in to MySQL:
1 2bash mysql -u root -p
Create a database. Let's name it
1ollama_db
:
1 2sql CREATE DATABASE ollama_db;
Use the newly created database:
1 2sql USE ollama_db;
Now create a sample table (for this example, let’s add a table for users):
1 2 3 4 5 6sql CREATE TABLE users ( id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255) NOT NULL, email VARCHAR(255) NOT NULL );

Step 3: Configuring Ollama to Connect to MySQL

Setting Up Python Environment

First, let’s create a Python environment to manage dependencies:

bash
mkdir ollama-mysql && cd ollama-mysql
python3 -m venv venv
source venv/bin/activate  # For Windows use: venv\Scripts\activate
pip install -q mysql-connector-python

Here, we’re installing MySQL Connector which is vital for connecting your Python code to the MySQL database.

Step 4: Write the Connection Script

Now, let’s create a Python script to send queries to our MySQL database. ```python import mysql.connector

Connect to MySQL database

def connect_to_db(): return mysql.connector.connect(
host="localhost",
user="your_username",
password="your_password",
database="ollama_db"
)

Fetch users

def fetch_users(): db = connect_to_db() cursor = db.cursor() cursor.execute("SELECT * FROM users") users = cursor.fetchall() db.close() return users

Main execution

if name == 'main': users = fetch_users() for user in users: print(user)

1
2

``  
Save this as

fetch_users.py

. Adjust the

your_username

and

your_password` fields to your MySQL credentials.

Step 5: Run the Script to Test the Connection

You can now run the script to ensure that your connection works and fetches users from the

users

table:

1
2

bash
python fetch_users.py

If all goes well, you should see your users' data printed to the terminal!

Step 6: Integrating Ollama for Natural Language Queries

Now that we’ve got the basic setup working, let’s throw in Ollama so it can generate SQL queries based on natural language.

Configuring the LLM to Handle Queries

Create a Python file called
1ollama_query.py
with the following code: ```python from ollama import Ollama

Initialize Ollama model

model = Ollama(model="llama2")

Process a natural language question

def get_query_from_ng(nl_query): return model.generate(nl_query)

if name == 'main': question = "What are the names of all users?" sql_query = get_query_from_ng(question) print("Generated SQL Query:", sql_query) ```
This script uses the Ollama model to translate a natural language query into an SQL query.

Running the Query

Now, to see if the whole setup works, you’ll run:

1
2

bash
python ollama_query.py

Adjust

question

to test various natural language inputs.

Step 7: Automate SQL Query Execution

You can enhance

ollama_query.py

to execute the generated SQL query against the MySQL database. Here’s how: ```python

Full integration script

import mysql.connector from ollama import Ollama

model = Ollama(model="llama2")

def connect_to_db(): return mysql.connector.connect( host="localhost", user="your_username", password="your_password", database="ollama_db" )

def get_query_from_ng(nl_query): return model.generate(nl_query)

def execute_query(sql_query): db = connect_to_db() cursor = db.cursor() cursor.execute(sql_query) results = cursor.fetchall() db.commit() # for insert or update commands db.close() return results

if name == 'main': question = "Fetch all user emails" sql_query = get_query_from_ng(question) results = execute_query(sql_query) print("Results:", results) ```
By doing this, you're making your LLM not just generate queries but also execute them! Talk about INTERACTIVE!

Conclusion

Setting up Ollama with a MySQL database can empower you to build applications that utilize the enormous potential of LLMs. With the steps outlined in this guide, you should now have a solid foundation to work with databases and create custom query-generating conversational agents. So get out there & start building something that might just change your workflow!

As you journey into the world of conversational AI, remember you can always rely on Arsturn to create custom ChatGPT chatbots effortlessly. With Arsturn, you’ll boost audience engagement & conversions, allowing you to create meaningful interactions across all your digital channels with NO coding required.

Happy coding!

FAQs

Q1: What databases can I use with Ollama?
You can connect Ollama to various databases such as MySQL, PostgreSQL, and more through the Python connectors.

Q2: Do I need a powerful GPU to run Ollama?
While it’s not mandatory, having a GPU can significantly improve inference times for larger models.

Q3: Can I customize my Ollama model?
Absolutely! Ollama allows you to fine-tune the models according to your specific requirements.