Team Ai
Apppublic

Codexzzz/sql-env

sourceHugging Faceupdated 6mo agoView on Hugging Face
0likes
sql_environment.cpython-311.pyc156 linesDownload Raw Back to __pycache__
1�

2�h�iI+����ddlZddlmZddlmZddlmZ	ddlmZm	Z	n#e3$rddlmZm	Z	YnwxYwddd	gd4�dd�d
ddddgdd�dddgd�dd�d�ZGd�de��ZdS)�N)�uuid4)�Environment)�State�)�	SqlAction�SqlObservationz�Find the full name and email address of all customers who live in 'New York'. Return results sorted alphabetically by name (A to Z).z�5CREATE TABLE customers (6    id        INTEGER PRIMARY KEY,7    name      TEXT    NOT NULL,8    email     TEXT    NOT NULL,9    city      TEXT    NOT NULL,10    age       INTEGER11);12a�13INSERT INTO customers VALUES (1, 'Alice Brown',  'alice@email.com',  'New York', 28);14INSERT INTO customers VALUES (2, 'Bob Smith',    'bob@email.com',    'New York', 34);15INSERT INTO customers VALUES (3, 'Carol Davis',  'carol@email.com',  'Chicago',  25);16INSERT INTO customers VALUES (4, 'David Lee',    'david@email.com',  'New York', 41);17INSERT INTO customers VALUES (5, 'Eve Wilson',   'eve@email.com',    'Boston',   30);18))�Alice Brownzalice@email.com)�	Bob Smithz
bob@email.com)z	David Leezdavid@email.com�)�description�schema�seed_sql�expected�	max_stepsz�Find each customer who has placed MORE THAN 2 orders. Return their name and total amount spent (sum of all their order amounts). Sort by total amount spent, highest first.z�19CREATE TABLE customers (20    id    INTEGER PRIMARY KEY,21    name  TEXT    NOT NULL22);23CREATE TABLE orders (24    id          INTEGER PRIMARY KEY,25    customer_id INTEGER NOT NULL,26    amount      REAL    NOT NULL,27    order_date  TEXT    NOT NULL28);29a!30INSERT INTO customers VALUES (1, 'Alice Brown');31INSERT INTO customers VALUES (2, 'Bob Smith');32INSERT INTO customers VALUES (3, 'Carol Davis');33INSERT INTO orders VALUES (1,  1, 120.00, '2024-01-10');34INSERT INTO orders VALUES (2,  1,  85.50, '2024-01-15');35INSERT INTO orders VALUES (3,  1, 200.00, '2024-02-01');36INSERT INTO orders VALUES (4,  2,  45.00, '2024-01-20');37INSERT INTO orders VALUES (5,  2,  95.00, '2024-02-10');38INSERT INTO orders VALUES (6,  2, 160.00, '2024-02-15');39INSERT INTO orders VALUES (7,  3,  30.00, '2024-01-05');40)r	gXy@)r41g�r@u;Generate a monthly revenue report for the year 2024. For each month and product category return: month in 'YYYY-MM' format, category name, number of distinct orders, and total revenue (quantity × price). Order by month ascending, then total revenue descending within each month. Exclude any data from outside 2024.a�42CREATE TABLE categories (43    id    INTEGER PRIMARY KEY,44    name  TEXT    NOT NULL45);46CREATE TABLE products (47    id          INTEGER PRIMARY KEY,48    name        TEXT    NOT NULL,49    category_id INTEGER NOT NULL,50    price       REAL    NOT NULL51);52CREATE TABLE orders (53    id         INTEGER PRIMARY KEY,54    order_date TEXT    NOT NULL55);56CREATE TABLE order_items (57    id         INTEGER PRIMARY KEY,58    order_id   INTEGER NOT NULL,59    product_id INTEGER NOT NULL,60    quantity   INTEGER NOT NULL61);62aA63INSERT INTO categories VALUES (1, 'Electronics');64INSERT INTO categories VALUES (2, 'Books');65INSERT INTO products VALUES (1, 'Laptop',       1, 999.00);66INSERT INTO products VALUES (2, 'Phone',         1, 599.00);67INSERT INTO products VALUES (3, 'Python Book',   2,  49.00);68INSERT INTO products VALUES (4, 'SQL Handbook',  2,  39.00);69INSERT INTO orders VALUES (1, '2024-01-15');70INSERT INTO orders VALUES (2, '2024-01-20');71INSERT INTO orders VALUES (3, '2024-02-10');72INSERT INTO orders VALUES (4, '2024-02-28');73INSERT INTO orders VALUES (5, '2023-12-01');74INSERT INTO order_items VALUES (1, 1, 1, 1);75INSERT INTO order_items VALUES (2, 2, 3, 2);76INSERT INTO order_items VALUES (3, 2, 4, 1);77INSERT INTO order_items VALUES (4, 3, 2, 1);78INSERT INTO order_items VALUES (5, 4, 3, 3);79INSERT INTO order_items VALUES (6, 5, 1, 1);80))�2024-01�Electronics�g8�@)r�Booksrg a@)�2024-02rrg��@)rrrg`b@�)�
select_basics�aggregate_filter�81multi_joinc�n�eZdZdZd�Zdd�Zdedefd�Zde	fd	�Z82ed83���Ze
defd���ZdS)
�SqlEnvironmentTc��d|_d|_ttt	����d���|_dS)Nrr��84episode_id�85step_count)�_conn�86_task_namer�strr�_state��selfs �HC:\Users\Prabhav\Downloads\SQL-OpenEnv\sql_env\server\sql_environment.py�__init__zSqlEnvironment.__init__�s2����87�)����s�5�7�7�|�|��B�B�B�����Nc�j�|�dd��}|tvrd}||_t|}|jr|j���tjd��|_|j�|d��|j�|d��|j���t|ptt����d���|_t|d|d���gd	d88i|ddd
��	�	S)N�taskrz:memory:r
rrrr�z;Episode started. Write a SQL query to solve the task above.rF��	�task_description�schema_info�query_result�
error_message�feedback�score_breakdown�attempts_remaining�done�reward)�get�TASKSr!r �close�sqlite3�connect�
executescript�commitrr"rr#r�strip)r%�seedr�kwargs�	task_namer*s      r&�resetzSqlEnvironment.reset�s���J�J�v��7�7�	��E�!�!�'�I�#����Y����:�	��J�������_�Z�0�0��89��90� � ��h��0�0�0��91� � ��j�!1�2�2�2��92�������z�'A�S����\�\�a�P�P�P����!�-�0��X��,�,�.�.���R��#�K�0���9394�9596�9798�99	100r(�action�returnc��|jxjdz
c_t|j}|d|jjz101}	|j�|j��}|���}|�||d|j��\}}}d}	n4#t$r'}102d}g}d|103�d�}ddi}t|104��}	Yd}105~106nd}107~108wwxYw|d	kp|d109k}t|d|d���d
�|D��|	||td110|��|ttdtd|��������	�	S)Nrrrr+g��������zSQL Error: z . Fix your syntax and try again.�executegffffff�?rrr
c�,�g|]}t|����S�)�list��.0�rs  r&�111<listcomp>z'SqlEnvironment.step.<locals>.<listcomp>�s��2�2�2�a�$�q�'�'�2�2�2r(g����������?r-)r#rr8r!r rF�	sql_query�fetchall�_grade�	Exceptionr"rr>�max�float�min)r%rCr*r4�cursor�resultr6r2�	breakdown�	error_msg�excr5s            r&�stepzSqlEnvironment.step�s{������!�#����T�_�%��!�+�.���1G�G��112	"��Z�'�'��(8�9�9�F��_�_�&�&�F�*.�+�+�f�d�:�>N�PV�P`�*a�*a�'�F�H�i��I�I���	"�	"�	"��F��F�L�s�L�L�L�H�#�U�+�I��S���I�I�I�I�I�I�����	"������~�8�!3�q�!8���!�-�0��X��,�,�.�.�2�2�6�2�2�2�#��%�"�1�&8�9�9����U�C��V�$4�$4�5�5�6�6�113114�115116�117118�119	120s�AB�121C�$C�CrOc	���i}d|d<|s	d}d}|||fStd�|D����}td�|D����}|rt|d��nd}	|rt|d��nd}122|	|123krdn$dt|	|124��t|	|125d��zz}t	|d	��|d126<tdt|��tt|��d��z��}d|z}
t	|
d	��|d<|�||��}d
|z}t	|d	��|d<d|����dd��v}|rdnd}||d<|d|z|
z|z|z}t|dz��}|dkr|dkr
|
dkr|sdnd}n0|dkrd|�d�}n#|dkrd|�d�}n|	|127kr128d|	�d|129�d�}nd |�d!�}t	|d	��||fS)"Ng�������?rFzJQuery ran but returned 0 rows. Check your WHERE clause or JOIN conditions.c3�4K�|]}t|��V��dS�N��tuplerJs  r&�	<genexpr>z(SqlEnvironment._grade.<locals>.<genexpr>�s(����4�4��5��8�8�4�4�4�4�4�4r(c3�4K�|]}t|��V��dSr^r_)rK�es  r&raz(SqlEnvironment._grade.<locals>.<genexpr>�s(����6�6��5��8�8�6�6�6�6�6�6r(rg�������?r��columnsrN�rowsg�������?�valueszselect *� r+r,�130efficiency�dzPerfect! Exact match.uACorrect result but avoid SELECT * — target only needed columns.g�������?zVery close! z>% of values match. Check column ordering or data type casting.g�?zPartial match: z?% correct. Re-read the task description and check your filters.zGot z column(s), expected z. Fix your SELECT clause.zMostly incorrect (z5% match). Start from the schema and re-read the task.)	�set�lenrUrS�round�_f1�lower�replace�int)r%rWrrOrXr2�total�131result_set�expected_set�result_cols�
expected_cols�	col_score�	row_ratio�	row_score�f1�	val_score�	uses_star�	eff_score�pcts                   r&rQzSqlEnvironment._grade�s����	� $�	�)���	.�c�H��E��(�I�-�-��4�4�V�4�4�4�4�4�132��6�6�X�6�6�6�6�6��-3�;��F�1�I����!��,4�;��H�Q�K�(�(�(�!�
�'�=�8�8�D�D�d�c�+�Wd�Fe�Fe�hk�lw�zG�IJ�iK�iK�GK�?L�	�$�Y��2�2�	�)����c�&�k�k�C��H�
�
�q�,A�,A�A�B�B�	��I�%�	�!�)�Q�/�/�	�&���X�X�j�,�7�7���B�Y�	�#�I�q�1�1�	�(�� �9�?�?�#4�#4�#<�#<�S�"�#E�#E�E�	�%�/�S�S�4�	�"+�	�,���)�$�y�0�9�<�y�H�9�T���"�s�(�m�m��
��9�9��d�*�*�y�D�/@�/@�6?�I�.�.�FI�H�H�
�3�Y�Y�i�c�i�i�i�H�H�
�3�Y�Y�m��m�m�m�H�H�
�M�
)�
)�h�k�h�h�
�h�h�h�H�H�f�C�f�f�f�H��U�A����)�3�3r(c���|s|sdS|r|sdS||z}t|��t|��z}t|��t|��z}||zdkrdSd|z|z||zzS)NrNr,rr)rl)rsrt�intersection�	precision�recalls     r&rnzSqlEnvironment._f1
s����	�,�	��3��	��	��3�!�L�0����%�%��J���7�	���%�%��L�(9�(9�9���v���"�"��3��9�}�v�%��V�);�<�<r(c��|jSr^)r#r$s r&�statezSqlEnvironment.states133���{�r()NN)�__name__�134__module__�__qualname__�SUPPORTS_CONCURRENT_SESSIONSr'rBrrr[r"rQ�staticmethodrn�propertyrr�rHr(r&rr�s�������#'� �C�C�C�135136�137�138�139�:140�9�141��142�143�144�145�B24�#�24�24�24�24�h�146=�147=��\�148=���u�����X���r(r)
r:�uuidr�"openenv.core.env_server.interfacesr�openenv.core.env_server.typesr�modelsrr�ImportErrorr8rrHr(r&�<module>r�st������������:�:�:�:�:�:�/�/�/�/�/�/�1�2�2�2�2�2�2�2�2�2���1�1�1�0�0�0�0�0�0�0�0�0�0�1����
E���149�150�151�152�5��>
9���
$�#�153��E#�#�N
2��,�&154�155�156��q9�9�G}	�}	��HI�I�I�I�I�[�I�I�I�I�Is�!�
1�1