Error while uploading a database table - Python mysql

Question

I'm practically new to MySQL databases.
I am trying to make entries and I find this error in the console:

mysql.connector.errors.ProgrammingError: 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'total issues (Timestamp, Count issues, Project) VALUES ('2020-04-17', '908', ''G' at line 1

and this is the code I'm trying to run:

uploadMap = {
    'current total issues': 'apiUrl0...',
    'current total unresolved issues': 'apiUrl1...',
    'total created in the week': 'apiUrl2...',
    'total blocker created in week': 'apiUrl3...'
}

for project in ('Graphic', 'Develop'):
    for tableName, url in uploadMap.items():

        sql = f"INSERT INTO {tableName} (Timestamp, Count issues, Project) VALUES (%s, %s, %s)"

        val = (todaySql, getTotal(url + project), project)

        cursor.execute(sql, val)

Some clarifications:

getTotal always returns an integer
todaySql it is a date of the alleged format that MySQL would need, managed by this object:

class jiraDates:
    def __init__(self):
        self.format = ('%Y', '%m', '%d')

    def getJiraDate(self, daysBack):
        dateBack = datetime.today() - timedelta(days=daysBack)
        return '-'.join([dateBack.strftime(ref) for ref in self.format])

    def getSqlDate(self, daysBack):
        return datetime.strptime(self.getJiraDate(daysBack), '-'.join(self.format))

todaySql = jiraDates().getSqlDate(0)

I hope it is nothing serious...!

Is the table name really "current total issues"? If so, you need to wrap it in backticks "`" — jordanm
– jordanm, Commented Apr 17, 2020 at 15:10
@jordanm Ok I'll try! Yes, I named it on phpMyAdmin. Do you recommend putting names not separated from white spaces? — Memmo
– Memmo, Commented Apr 17, 2020 at 15:31

Memmo · Accepted Answer · 2020-04-21 09:29:50Z

2

What jordanm said is not wrong. If the names are larger than a monogram, you need to use backticks (although I highly recommend using the snake case, e.g. name_of_the_table).
Another thing you need to make sure is that the date must be in this date format: datetime.date(int(year), int(months), int(date)).

So you have to change these two lines:

def getSqlDate(self, daysBack):
    jiraDate = self.getJiraDate(daysBack).split('-')
    return datetime.date(int(jiraDate[0]), int(jiraDate[1]), int(jiraDate[2]))

and

sql = f"INSERT INTO `{tableName}` (Timestamp, `Count issues`, Project) VALUES (%s, %s, %s)"

edited Apr 21, 2020 at 9:29

answered Apr 20, 2020 at 8:56

Memmo

2783 gold badges11 silver badges39 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Error while uploading a database table - Python mysql

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related