SQTHON

SQTHON

Connect to multiple databases, run raw SQL queries, perform analysis and make visualization.

Currently working on:

SqthonAI: generate SQL queries using a LLM of your choice 🤖
Security improvements
New Features
custom exception for better error showcase

Package is not published to pypi yet and is being made using poetry.

Currently, this package will work on windows only.

And for your safety create a virtual environment.

Installation

1. Clone the repository.


https://github.com/HimrajDas/SQTHON.git
https://github.com/HimrajDas/SQTHON.git
https://github.com/HimrajDas/SQTHON.git

Enter fullscreen mode Exit fullscreen mode


cd sqthon
cd sqthon
cd sqthon

Enter fullscreen mode Exit fullscreen mode

2. Install poetry (if not installed)

Using Windows powershell


(Invoke-WebRequest -Uri https://install.python-poetry.org -UseBasicParsing).Content | py -
(Invoke-WebRequest -Uri https://install.python-poetry.org -UseBasicParsing).Content | py -
(Invoke-WebRequest -Uri https://install.python-poetry.org -UseBasicParsing).Content | py -

Enter fullscreen mode Exit fullscreen mode

Using Linux, macOS, Windows (WSL)


curl -sSL https://install.python-poetry.org | python3 -
curl -sSL https://install.python-poetry.org | python3 -
curl -sSL https://install.python-poetry.org | python3 -

Enter fullscreen mode Exit fullscreen mode

Using pipx


pipx install poetry
pipx install poetry
pipx install poetry

Enter fullscreen mode Exit fullscreen mode

3. Install dependencies using poetry


poetry install
poetry install
poetry install

Enter fullscreen mode Exit fullscreen mode

Alternative install

pip install git+https://github.com/HimrajDas/SQTHON

Now how do I use it🤓

1. Create a .env file in your project root. [a must-do step]

set database passwords like this: <username>password

2. Let’s connect to a database.


<span>from</span> <span>sqthon</span> <span>import</span> <span>Sqthon</span>
<span># Instantiate the class. Passwords gets fetch from the .env file (that's why you have to create it) </span><span>sq</span> <span>=</span> <span>Sqthon</span><span>(</span><span>dialect</span><span>=</span><span>"</span><span>mysql</span><span>"</span><span>,</span> <span>user</span><span>=</span><span>"</span><span>root</span><span>"</span><span>,</span> <span>host</span><span>=</span><span>"</span><span>localhost</span><span>"</span><span>,</span> <span>service_instance_name</span><span>=</span><span>"</span><span>MySQL service instance name</span><span>"</span><span>)</span>
<span># Connects to a database </span><span>conn1</span> <span>=</span> <span>sq</span><span>.</span><span>connect_to_database</span><span>(</span><span>database</span><span>=</span><span>"</span><span>dbname</span><span>"</span><span>,</span> <span>local_infile</span><span>=</span><span>True</span><span>)</span> <span># local_infile controls the infile settings for the client. </span><span>conn2</span> <span>=</span> <span>sq</span><span>.</span><span>connect_to_database</span><span>(</span><span>"</span><span>dbname</span><span>"</span><span>)</span>
<span># or you can connect like this: </span><span>conn3</span> <span>=</span> <span>sq</span><span>.</span><span>connect_db</span><span>.</span><span>connect</span><span>(</span><span>database</span><span>=</span><span>"</span><span>dbname</span><span>"</span><span>)</span> <span># not preferred . </span>
<span>from</span> <span>sqthon</span> <span>import</span> <span>Sqthon</span>
<span># Instantiate the class. Passwords gets fetch from the .env file (that's why you have to create it) </span><span>sq</span> <span>=</span> <span>Sqthon</span><span>(</span><span>dialect</span><span>=</span><span>"</span><span>mysql</span><span>"</span><span>,</span> <span>user</span><span>=</span><span>"</span><span>root</span><span>"</span><span>,</span> <span>host</span><span>=</span><span>"</span><span>localhost</span><span>"</span><span>,</span> <span>service_instance_name</span><span>=</span><span>"</span><span>MySQL service instance name</span><span>"</span><span>)</span>

<span># Connects to a database </span><span>conn1</span> <span>=</span> <span>sq</span><span>.</span><span>connect_to_database</span><span>(</span><span>database</span><span>=</span><span>"</span><span>dbname</span><span>"</span><span>,</span> <span>local_infile</span><span>=</span><span>True</span><span>)</span> <span># local_infile controls the infile settings for the client. </span><span>conn2</span> <span>=</span> <span>sq</span><span>.</span><span>connect_to_database</span><span>(</span><span>"</span><span>dbname</span><span>"</span><span>)</span>

<span># or you can connect like this: </span><span>conn3</span> <span>=</span> <span>sq</span><span>.</span><span>connect_db</span><span>.</span><span>connect</span><span>(</span><span>database</span><span>=</span><span>"</span><span>dbname</span><span>"</span><span>)</span> <span># not preferred . </span>
from sqthon import Sqthon
# Instantiate the class. Passwords gets fetch from the .env file (that's why you have to create it) sq = Sqthon(dialect="mysql", user="root", host="localhost", service_instance_name="MySQL service instance name")

# Connects to a database conn1 = sq.connect_to_database(database="dbname", local_infile=True) # local_infile controls the infile settings for the client. conn2 = sq.connect_to_database("dbname")

# or you can connect like this: conn3 = sq.connect_db.connect(database="dbname") # not preferred .

Enter fullscreen mode Exit fullscreen mode

If your MySQL server is not running then providing service_instance_name will start the server automatically.
If you are not running the script as an administrator, it will ask for admin privilege to start the server.

3. Queries. ⭐

Suppose you have a database named dummy 🤓

Connect to the database.


<span>dummy_conn</span> <span>=</span> <span>sq</span><span>.</span><span>connect_to_database</span><span>(</span><span>database</span><span>=</span><span>"</span><span>dummy</span><span>"</span><span>)</span>
<span>dummy_conn</span> <span>=</span> <span>sq</span><span>.</span><span>connect_to_database</span><span>(</span><span>database</span><span>=</span><span>"</span><span>dummy</span><span>"</span><span>)</span>
dummy_conn = sq.connect_to_database(database="dummy")

Enter fullscreen mode Exit fullscreen mode

Now, how do I run some queries?


<span># Suppose, You have a table named sales in the dummy database. </span><span>query</span> <span>=</span> <span>"""</span><span> SELECT customer_name FROM sales; </span><span>"""</span>
<span>customer_names</span> <span>=</span> <span>dummy_conn</span><span>.</span><span>run_query</span><span>(</span><span>query</span><span>=</span><span>query</span><span>)</span> <span># it will return the result as pandas dataframe. </span>
<span># Suppose, You have a table named sales in the dummy database. </span><span>query</span> <span>=</span> <span>"""</span><span> SELECT customer_name FROM sales; </span><span>"""</span>

<span>customer_names</span> <span>=</span> <span>dummy_conn</span><span>.</span><span>run_query</span><span>(</span><span>query</span><span>=</span><span>query</span><span>)</span> <span># it will return the result as pandas dataframe. </span>
# Suppose, You have a table named sales in the dummy database. query = """ SELECT customer_name FROM sales; """

customer_names = dummy_conn.run_query(query=query) # it will return the result as pandas dataframe.

Enter fullscreen mode Exit fullscreen mode

run_query have several params other than query, they are: visualize: bool = False,
plot_type: str = None,
x=None,
y=None,
title=None.
If you make visualize=True and provide x, y and plot_type args then it will return a graph along with
the data which I don’t think is good for later use of the variable.

4. Visualization.


<span>from</span>  <span>sqthon.data_visualizer</span> <span>import</span> <span>DataVisualizer</span> <span>as</span> <span>dv</span>
<span>conn1</span> <span>=</span> <span>sq</span><span>.</span><span>connect_to_database</span><span>(</span><span>"</span><span>store_sales</span><span>"</span><span>,</span> <span>infile</span><span>=</span><span>True</span><span>)</span>
<span>query</span> <span>=</span> <span>"""</span><span> SELECT YEAR(sales_month) as sales_year, SUM(sales) AS sales, kind_of_business FROM us_store_sales WHERE kind_of_business IN (</span><span>'</span><span>Men</span><span>''</span><span>s clothing stores</span><span>'</span><span>, </span><span>'</span><span>Women</span><span>''</span><span>s clothing stores</span><span>'</span><span>, </span><span>'</span><span>Family clothing stores</span><span>'</span><span>) GROUP BY sales_year, kind_of_business; </span><span>"""</span>   <span># a query I performed on my database  </span>
<span>yearly_sales</span> <span>=</span> <span>conn1</span><span>.</span><span>run_query</span><span>(</span><span>query</span><span>=</span><span>query</span><span>)</span>
<span>dv</span><span>.</span><span>plot</span><span>(</span><span>data</span><span>=</span><span>yearly_sales</span><span>,</span> <span>plot_type</span><span>=</span><span>"</span><span>line</span><span>"</span><span>,</span> <span>x</span><span>=</span><span>"</span><span>sales_year</span><span>"</span><span>,</span> <span>y</span><span>=</span><span>"</span><span>sales</span><span>"</span><span>,</span> <span>hue</span><span>=</span><span>"</span><span>kind_of_business</span><span>"</span><span>)</span>
<span>from</span>  <span>sqthon.data_visualizer</span> <span>import</span> <span>DataVisualizer</span> <span>as</span> <span>dv</span>

<span>conn1</span> <span>=</span> <span>sq</span><span>.</span><span>connect_to_database</span><span>(</span><span>"</span><span>store_sales</span><span>"</span><span>,</span> <span>infile</span><span>=</span><span>True</span><span>)</span>

<span>query</span> <span>=</span> <span>"""</span><span> SELECT YEAR(sales_month) as sales_year, SUM(sales) AS sales, kind_of_business FROM us_store_sales WHERE kind_of_business IN (</span><span>'</span><span>Men</span><span>''</span><span>s clothing stores</span><span>'</span><span>, </span><span>'</span><span>Women</span><span>''</span><span>s clothing stores</span><span>'</span><span>, </span><span>'</span><span>Family clothing stores</span><span>'</span><span>) GROUP BY sales_year, kind_of_business; </span><span>"""</span>   <span># a query I performed on my database  </span>
<span>yearly_sales</span> <span>=</span> <span>conn1</span><span>.</span><span>run_query</span><span>(</span><span>query</span><span>=</span><span>query</span><span>)</span>
<span>dv</span><span>.</span><span>plot</span><span>(</span><span>data</span><span>=</span><span>yearly_sales</span><span>,</span> <span>plot_type</span><span>=</span><span>"</span><span>line</span><span>"</span><span>,</span> <span>x</span><span>=</span><span>"</span><span>sales_year</span><span>"</span><span>,</span> <span>y</span><span>=</span><span>"</span><span>sales</span><span>"</span><span>,</span> <span>hue</span><span>=</span><span>"</span><span>kind_of_business</span><span>"</span><span>)</span>
from  sqthon.data_visualizer import DataVisualizer as dv

conn1 = sq.connect_to_database("store_sales", infile=True)

query = """ SELECT YEAR(sales_month) as sales_year, SUM(sales) AS sales, kind_of_business FROM us_store_sales WHERE kind_of_business IN ('Men''s clothing stores', 'Women''s clothing stores', 'Family clothing stores') GROUP BY sales_year, kind_of_business; """   # a query I performed on my database  
yearly_sales = conn1.run_query(query=query)
dv.plot(data=yearly_sales, plot_type="line", x="sales_year", y="sales", hue="kind_of_business")

Enter fullscreen mode Exit fullscreen mode

5. Importing CSV to a Table.

I have isolated this feature for several security reasons. What do I mean is that it uses a separate
engine to import the csv to a table which you don’t need to worry about

It exists in the util.py as a separate method devoid of life from others.
Currently it supports mysql only.

Method Name: import_csv_to_mysqltable

Params it has:

user: str
host: str
database: str
csv_path: str
service_instance: str = None
table: str

user: username,
host: host,
database: database name,
csv_path: relative or absolute path to the csv file.

table: table name, if it doesn’t exist then it will create the table according to the csv file.
You don’t need to worry about data types. It will handle it.


<span>from</span> <span>sqthon.util</span> <span>import</span> <span>import_csv_to_mysqltable</span>
<span># just call the method with correct args. Password fetched automatically. </span><span>import_csv_to_mysqltable</span><span>(</span><span>user</span><span>=</span><span>"</span><span>dummy</span><span>"</span><span>,</span>
                         <span>host</span><span>=</span><span>"</span><span>host</span><span>"</span><span>,</span>
                         <span>database</span><span>=</span><span>"</span><span>dummy</span><span>"</span><span>,</span>
                         <span>csv_path</span><span>=</span><span>"</span><span>universe/milkyway/our_solar_system/earth</span><span>"</span><span>,</span>
                         <span>service_instance</span><span>=</span><span>"</span><span>your service instance</span><span>"</span><span>,</span>
                         <span>table</span><span>=</span><span>"</span><span>table</span><span>"</span><span>)</span>  <span># if table don't exist it will create it according </span>                                        <span># the csv file holds. </span>
<span>from</span> <span>sqthon.util</span> <span>import</span> <span>import_csv_to_mysqltable</span>
<span># just call the method with correct args. Password fetched automatically. </span><span>import_csv_to_mysqltable</span><span>(</span><span>user</span><span>=</span><span>"</span><span>dummy</span><span>"</span><span>,</span>
                         <span>host</span><span>=</span><span>"</span><span>host</span><span>"</span><span>,</span>
                         <span>database</span><span>=</span><span>"</span><span>dummy</span><span>"</span><span>,</span>
                         <span>csv_path</span><span>=</span><span>"</span><span>universe/milkyway/our_solar_system/earth</span><span>"</span><span>,</span>
                         <span>service_instance</span><span>=</span><span>"</span><span>your service instance</span><span>"</span><span>,</span>
                         <span>table</span><span>=</span><span>"</span><span>table</span><span>"</span><span>)</span>  <span># if table don't exist it will create it according </span>                                        <span># the csv file holds. </span>
from sqthon.util import import_csv_to_mysqltable
# just call the method with correct args. Password fetched automatically. import_csv_to_mysqltable(user="dummy",
                         host="host",
                         database="dummy",
                         csv_path="universe/milkyway/our_solar_system/earth",
                         service_instance="your service instance",
                         table="table")  # if table don't exist it will create it according                                         # the csv file holds.

Enter fullscreen mode Exit fullscreen mode

原文链接：Connect to multiple databases, make or generate SQL queries, analyze or visualize.

展开阅读全文

文章版权声明 1、本网站名称：拾光赋
2、本站永久网址：https://www.blogs.ink
3、本网站的文章部分内容可能来源于网络，仅供大家学习与参考，如有侵权，请联系站长QQ：805375623进行删除处理。
4、本站一切资源不代表本站立场，并不代表本站赞同其观点和对其真实性负责。
5、本站一律禁止以任何方式发布或转载任何违法的相关信息，访客发现请向站长举报
6、本站资源大多存储在云盘，如发现链接失效，请联系我们我们会第一时间更新。

THE END