0

Any idea how I can sort this so it works. Help would be appreciated - I am trying to call the API to then update a MYSQL database with a number of keys. I also would like the option to add nested array values as well if it was possible. MySQL table is set up as the name students with the fields below.

the connection seems to work and so does the database flush but I can't seem to update the MYSQL with the API details. Should the execute be a function and processed by a function call.

The details here is the mock API not the real API with my username and password for obvious reasons.

Any help would be appreciated.

from urllib.parse import urlencode
import ssl
import requests
import json
import pymysql.cursors


API_USERNAME = 'username'
API_PASSWORD = 'password'
TOKEN_API_URL = 'https://private-anon-404a95250a-progresso.apiary-mock.com/Token'
LEARNER_API_URL = 'https://private-anon-404a95250a-progresso.apiary-mock.com/Learners?Status=Active'
LEARNER_ATTENDANCE_URL = 'https://private-anon-404a95250a-progresso.apiary-mock.com/?Status=Active&StartDate=03-09-2018'


my_db = pymysql.connect(
host = "localhose",
user = "root",
passwd = "password",
database ="test"
)


   # Can't validate SSL connections
    # **** REFERENCE LINK - https://stackoverflow.com/questions/27835619/urllib-and-ssl-certificate-verify-failed-error
context = ssl.SSLContext(ssl.PROTOCOL_TLSv1)
values = {"grant_type": "password", "username": API_USERNAME, "password": API_PASSWORD}
data = urlencode(values).encode()

session = requests.Session()
session.headers = {
        'Content-Type': 'application/x-www-form-urlencoded'
    }
    # session.post('https://api.progresso.net/v2/Token')
response_body = session.post(TOKEN_API_URL, data=data)

    # # Get api access token
access_token = json.loads(response_body.text)["access_token"]

    # Get learner info
session.headers = {
        'Authorization': 'Bearer {}'.format(access_token)
    }

    # Get responses from progresso learner api
learner_responses = session.get(LEARNER_API_URL)
learner_attendance = session.get(LEARNER_ATTENDANCE_URL)
School = session.get(SCHOOL_DETAILS_URL)
json_data = learner_responses.json()

    # flush the existing data
cursor.execute('delete from students;')
conn.commit()
conn = my_db
cursor = conn.cursor()
students = json_data
update_db(cursor, students)

try:
with my_db.cursor() as cursor:
    # Create a new record
    sql=("INSERT INTO 'students' ('LearnerId','LearnerCode','Surname','Forename','DateOfBirth','Year') VALUES (%s, %s, %s, %s, %s, %s,"))

    for student in students:
    cursor.executemany(sql,
                       (student['LearnerId'],
                        student['LearnerCode'],
                        student['Surname'],
                        student['Forename'],
                        student['DateOfBirth'],
                        student['Year'],
                        )
                       )
    conn.commit()

# Remember to exit nicely
finally:
conn.close()
David Draper
  • 21
  • 4
  • Please review your query string (INSERT INTO [...]). Not only does this line raise a SyntaxError (two closing parenthesis, but just a single opening one); the string is malformed from an SQL syntax point of view as well. Object names (table and columns) are passed in as string literals instead of being treated as identifiers (i.e. they are enclosed in single quotes) and the values list is ending on a comma where there should be a closing parenthesis. – shmee Dec 03 '18 at 14:28
  • Oh, and you use `executemany` with a set of parameters that looks very much like it should be passed in via `execute` instead. `execute`: You need a sequence of a number of input values, six in your case. `executemany`: You need a sequence of input value sequences, like a list of six-element-tuples in your example. – shmee Dec 03 '18 at 14:42

0 Answers0