CREATE TABLE stadium (
    stadium_id number,
    location text,
...
)

CREATE TABLE singer (
    singer_id number,
    name text,
    country text,
   ...
)

CREATE TABLE concert (
    concert_id number,
    concert_name text,
    ...
)

CREATE TABLE singer_in_concert (
    concert_id number,
    singer_id text
)

torch
transformers

import torch
from transformers import AutoTokenizer, AutoModelForCausalLM
tokenizer = AutoTokenizer.from_pretrained("NumbersStation/nsql-llama-2-7B")
model = AutoModelForCausalLM.from_pretrained("NumbersStation/nsql-llama-2-7B", torch_dtype=torch.bfloat16)

text = """CREATE TABLE stadium (
    stadium_id number,
    location text,
    name text,
    capacity number,
    highest number,
    lowest number,
    average number
)

CREATE TABLE singer (
    singer_id number,
    name text,
    country text,
    song_name text,
    song_release_year text,
    age number,
    is_male others
)

CREATE TABLE concert (
    concert_id number,
    concert_name text,
    theme text,
    stadium_id text,
    year text
)

CREATE TABLE singer_in_concert (
    concert_id number,
    singer_id text
)

-- Using valid SQLite, answer the following questions for the tables provided above.


-- Give What is the name of the singer who performed at concert_id 12 ?

SELECT"""

input_ids = tokenizer(text, return_tensors="pt").input_ids

generated_ids = model.generate(input_ids, max_length=500)
print(tokenizer.decode(generated_ids[0], skip_special_tokens=True))

import torch
from transformers import AutoTokenizer, AutoModelForCausalLM
import subprocess

def get_sql_create_queries(host,database,user,password.port):
 # Command to execute pg_dump
 command = [
     "pg_dump",
     f"--host={host}",
     f"--dbname={database}",
     f"--username={user}",
     f"--port={port}",
     "--no-owner",
     "--no-privileges",
     # "--data-only",
     "--schema-only",
     "--no-comments",
 ]

 try:
     output = subprocess.check_output(command, stderr=subprocess.STDOUT, text=True)
     # Extract the create table commands Only
     test_sub="CREATE TABLE"
     res = [i for i in range(len(output)) if output.startswith(test_sub, i)]
     print(res)
     create_queries=""
     for start_point in res:
      subs=output[start_point:]
      test_sub=";"
      semi_colons=[i for i in range(len(subs)) if subs.startswith(test_sub, i)]
      just_create_query=subs[:semi_colons[0]]
      create_queries+=just_create_query+"\n"
     print(create_queries)
     return create_queries

 except subprocess.CalledProcessError as e:
     print("Error:", e.output) 
     return ""



tokenizer = AutoTokenizer.from_pretrained("NumbersStation/nsql-llama-2-7B")
model = AutoModelForCausalLM.from_pretrained("NumbersStation/nsql-llama-2-7B", torch_dtype=torch.bfloat16)

# Database connection parameters
host = "localhost"
database = "ashhadulislam"
user = "ashhadulislam"
password = "ashhadulislam"
port = "5432"

create_queries=get_sql_create_queries(host,database,user,password.port)
text=create_queries+'''

-- Using valid SQLite, answer the following questions for the tables provided above.


-- Give What is the name of the singer who performed at concert_id 12 ?

SELECT
'''

input_ids = tokenizer(text, return_tensors="pt").input_ids

# generated_ids = model.generate(input_ids, max_length=500)
generated_ids = model.generate(input_ids, max_length=len(text))

# print(tokenizer.decode(generated_ids[0], skip_special_tokens=True))
output = tokenizer.decode(generated_ids[0], skip_special_tokens=True)
output = 'SELECT' + output.split('SELECT')[-1]
print(output)

SELECT T2.name FROM public.singer_in_concert AS T1 JOIN public.singer AS T2 ON T1.singer_id = T2.singer_id WHERE T1.concert_id = 12