Question

Python Question. I am trying to connect to a database and create a table using Python in this code but I keep getting an error on line 48 saying that "Quotes" is not defined. Could anyone please help me set this up correctly? I am using Azure SQL Database to create this.

import pyodbc
import json
import requests as r
import pprint

connection_string = 'Driver={ODBC Driver 17 for SQL Server};' \
                    'Server=randomquotegenerator.database.windows.net,1433;' \
                    'Database=RandomQuoteGenerator;' \
                    'Uid=carterholliday;' \
                    'Pwd=MIS54002k19;' \
                    'TrustServerCertificate=no;'

conn = pyodbc.connect(connection_string, autocommit=True)

curs = conn.cursor()

curs.execute(
     '''
     if not exists(
        SELECT * 
        FROM INFORMATION_SCHEMA.TABLES 
        WHERE TABLE_NAME = N'Quotes'
        )
     begin
         create table Quotes(
         ID int primary key clustered identity(1,1)
         ,Quote varchar(5000)
         ,QuoteSource varchar(5000)
         )
     end
     else
     begin
        truncate table Quotes
     end

     '''
     )

endpoint_1 = 'https://ron-swanson-quotes.herokuapp.com/v2/quotes/100'
endpoint_2 = 'http://quotesondesign.com/wp-json/posts?filter[orderby]=rand&filter[posts_per_page]=40'

rs_quotes = r.get(endpoint_1)
to_quotes = r.get(endpoint_2)

insert_query = 'insert into Quotes (rs_quotes) values (?)'
insert_query_2 = 'insert into Quotes (to_quotes) values (?)'

for entry in Quotes:
    rows_to_insert.append(entry,)
curs.executemany(insert_query, rows_to_insert)
curs.executemany(insert_query_2, rows_to_insert)

conn.commit()
conn.close()

Answer 1

The issue here is that,

Quotes variable is not defined, when you are trying to use it. If i understand this piece of code correctly, then you are trying to download 2 kind of quotes from endpoint1 and endpoint2, which are kept in rs_quotes and to_quotes
Then you are trying to insert those quotes in the tables..

For this, instead of below code:

for entry in Quotes:
    rows_to_insert.append(entry,)

use below code:

for entry in rs_quotes :
    rows_to_insert.append(entry,)

Let me know if any issues.. I hope i clarified the issue well.