SyntaxError on function definition

Question

I'm trying to pull data from an Excel spreadsheet to MySQL. My script can't find the path to the Excel file, and my IDE (Spyder) is giving an error on this line:

def read_excel(r'C:\\Users\\ParaSystems Limited\\Desktop\\main.xlsx'):

 invalid syntax

import openpyxl
import pymysql as mdb

def read_excel(r'C:\\Users\\ParaSystems Limited\\Desktop\\main.xlsx'):
    masterdict = {}
    wb = openpyxl.load_workbook('main.xlsx')
    for sheet in wb:
        for arow in range(2, sheet.max_row+1):
            if sheet['A'+str(arow)].value:
                masterdict[sheet['A'+str(arow)].value] = {
                    'Equipment Number':sheet['B'+str(arow)].value,
                    'Number':sheet['C'+str(arow)].value,
                    'Description':sheet['D'+str(arow)].value,
                    'Manufacturer':sheet['E'+str(arow)].value,
                    'Serial Number':sheet['F'+str(arow)].value,
                    'Country  of Manufacturer':sheet['G'+str(arow)].value,
                    'Functional Location Description':sheet['H'+str(arow)].value,
                    'Functional Location Number (Short)':sheet['I'+str(arow)].value,
                    'Functional Location Number':sheet['J'+str(arow)].value,
                    'COST OF SERVICING AND MAINTENANCE':sheet['K'+str(arow)].value,
                    'Office Location':sheet['L'+str(arow)].value
                    }

    return masterdict

def inputIntoMySQL(masterdict):
    con = mdb.connect(host= '127.0.0.1', user = 'root', password =None,db='scraping')

    cur = con.cursor()
    with con:
        cur.execute("DROP TABLE IF EXISTS main")
        cur.execute("CREATE TABLE main (rid INT PRIMARY KEY, EquipmentNumber VARCHAR(75), Description VARCHAR(75),\
                    Manufacturer VARCHAR(50), SerialNumber INT,CountryOfManufacturer VARCHAR(25), \
                    FunctionalLocationDescription VARCHAR(50), FunctionalLocationNumberShort VARCHAR(75), FunctionalLocationNumber VARCHAR(25),\
                    CostOfServicingAndMaintenance DECIMAL(15,2),OfficeLocation VARCHAR(35))")
        for i in masterdict:
            cur.execute('INSERT INTO DISTRIBUTORS_NESTLE(rid, EquipmentNumber,Description,Manufacturer,SerialNumber,\
            CountryOfManufacturer,FunctionalLocationDescription, FunctionalLocationNumberShort,FunctionalLocationNumber\
            CostOfServicingAndMaintenance,OfficeLocation) VALUES("%s", "%s", "%s","%s","%s","%s","%s","%s","%s","%s","%s")'
            %(i,masterdict[i]['Equipment Number'],masterdict[i]['Description'],
              masterdict[i]['Manufacturer'],masterdict[i]['Serial Number'],masterdict[i]['Country  of Manufacturer'],
              masterdict[i]['Functional Location Description'], masterdict[i]['Functional Location Number (Short)'], masterdict[i]['Functional Location Number'],
              masterdict[i]['COST OF SERVICING AND MAINTENANCE'], masterdict[i]['Office Location']))
        con.commit()
        con.close()

my file is located at r’C:\\Users\\ParaSystems Limited\\Desktop\\main.xlsx’ but I'm having invalid syntax error — AKP
– AKP, Commented Aug 20, 2017 at 17:21

user3535074 · Accepted Answer · 2017-08-20 17:27:15Z

2

The syntax error is because you're defining a function (read_excel) and you're putting the excel filepath directly in the function definition - with this syntax you the excel filepath isnt assigned to a variable so you wouldn't be able to use it within the function.

def read_excel(r'C:\Users\ParaSystems Limited\Desktop\main.xlsx')#Syntax error

To fix this you could create a parameter and make that particular filepath the default value:

def read_excel(excel_file_path = r'C:\Users\ParaSystems Limited\Desktop\main.xlsx')

Then when you call the function, you can call it without any parameters and the excel_file_path will default to that e.g.

read_excel()#Calls with excel_file_path as your default value

or

read_excel(excel_file_path = r'path\to\another\excel.xlsx') #Calls with excel_file_path as the passed parameter value

If there really isn't any need to call this function on any other excel, just declare it in the read_excel function and leave the parameters blank. e.g.

def read_excel():

excel_file_path = r'C:\Users\ParaSystems Limited\Desktop\main.xlsx'

answered Aug 20, 2017 at 17:27

user3535074

1,36810 gold badges29 silver badges50 bronze badges

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

4 Comments

AKP Over a year ago

Thank you sir... I made the adjustment but I can't find the data in the mysql database

user3535074 Over a year ago

Maybe check your parameters in the following line: con = mdb.connect(host= 'localhost', user = 'root', password =None,db='scraping') The documentation is here (examples on page 6): media.readthedocs.org/pdf/pymysql/latest/pymysql.pdf

AKP Over a year ago

Thank you sir... One more question, how do I edit this script so I can connect to a MSSQL ?

user3535074 Over a year ago

You're changing your db? If you're choosing the dB I would go for sqlite3 as its relatively simple, well documented and part of the python3 standard library. Otherwise here is a different python library to connect with a mssql database (read the installation steps carefully as it has lots of dependencies) :pymssql.org/en/stable

Kevin J. Chase · Accepted Answer · 2017-08-20 18:41:01Z

0

This is not a valid function definition:

def read_excel(r'C:\\Users\\ParaSystems Limited\\Desktop\\main.xlsx'):
    masterdict = {}
    wb = openpyxl.load_workbook('main.xlsx')
    ...

You don't have any named parameters inside the parentheses, just a raw string.

It looks like you actually meant something like:

def read_excel(fname=r'C:\Users\ParaSystems Limited\Desktop\main.xlsx'):
    masterdict = {}  # [unchanged]
    wb = openpyxl.load_workbook(fname)  # _Uses_ the parameter.
    ...

Also, since you are using a raw string (r'...'), you shouldn't need to double the backslashes. Single backslashes should work. (You'll have to verify this yourself. I don't have access to a Windows system, so I can't test this.)

edited Aug 20, 2017 at 18:41

answered Aug 20, 2017 at 17:27

Kevin J. Chase

3,9764 gold badges25 silver badges43 bronze badges

4 Comments

AKP Over a year ago

Thank you I checked the db but can't find the data ... invalid syntax error has been handled

Kevin J. Chase Over a year ago

@AKP: I've changed the raw string to use single- backslashes. See if that fixes your file-not-found error. (You might also have to quote or escape that space in ParaSystems Limited... It's been a while since I used anything on a Windows OS.)

AKP Over a year ago

Thank you sir... One more question, how do I edit this script so I can connect to a MSSQL ?

Kevin J. Chase Over a year ago

@AKP That is a completely different question (and one I can't answer, as you may have guessed from my lack of a Windows machine). You should search Stack Overflow (or maybe search Server Fault), and if you can't find an answer, then you should ask a new question

Collectives™ on Stack Overflow

SyntaxError on function definition

2 Answers 2

4 Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related