MS Access pyodbc.Programming Error 4200: Help with syntax and building SQL statements

Dear all,

I'm using python to open a netcdf file and create a database table using pyodbc.

I've been able to connect to the database I'm having issues with the syntax.
pyodbc.ProgrammingError: ('42000', '[42000] [Microsoft][ODBC Microsoft Access Driver] Syntax error in field definition. (-3553) (SQLExecDirectW)')

I am trying to create a table, then execute insert into table commands for each row of the netcdf data.   When inserting data into the table how to specify to auto generate the ID?  Or do I need to keep a count and increment it.

I've used saved imports to access the csv and create a table wasn't sure if its possible to execute it from python.

with open(r'C:/NetCDF/noutput.csv', 'wb') as csvFile:
    outputwriter = csv.writer(csvFile, delimiter=',')

    SQL = 'CREATE TABLE NOutput (id integer, Latitude double, Longitude double' 
    for date_val in date_strings:
        header.append(date_val)
        SQL += ','+ date_val + ' double'
    outputwriter.writerow(header)
    SQL+= ');'
    print SQL

    connection.cursor().execute(SQL).commit()
 for lat_index, lat in enumerate(lats):
        for lon_index, lon in enumerate(lons):
            content = [lat,lon]
            for time_index, time in enumerate(times[:]): 
                data = f[time_index,lat_index,lon_index]
                content.append(data)
                # insert into table code here not sure how to build the string 
                #SQL_Insert = 'INSERT INTO NOutput (field names from first block) VALUES (values from this block)

            if(data != "--"):
                outputwriter.writerow(content) 
   

Open in new window


Thanks
AndyC1000Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Walter RitzelSenior Software EngineerCommented:
Assuming that in the part of the code that you are not showing you have connected sucessfully, what you dont need on your create statement is the commit().

Also, if you are trying to create the table every time, maybe would be interesting drop it first.
AndyC1000Author Commented:
Yes the connection to the databases is successful. I will try dropping the table after the issues when building the string SQL are resolved. Can you see the problem with it? It works if I define it in one statement as a test but when I try to integrate the netcdf data the error in the question occurs.

I'm also unsure if how to implement the insert into using the netcdf data (same issue)
Walter RitzelSenior Software EngineerCommented:
please send your complete code and a small sample of  the netcdf file, so I can debug and fix it for you.
SolarWinds® Network Configuration Manager (NCM)

SolarWinds® Network Configuration Manager brings structure and peace of mind to configuration management. Bulk config deployment, automatic backups, change detection, vulnerability assessments, and config change templates reduce the time needed for repetitive tasks.

AndyC1000Author Commented:
I've attached the complete code, I'm not able to send a sample netcdf file.  This file was given to me and I'm not sure how to modify.  

A bit of background about the code, python was used to convert the netcdf dataset to a csv file. It was then imported into a Access database where a number of queries are used.  I'm trying to automate this process so that the table is automatically inserted into the database.

I'm hoping the code where the netcdf is read and saved to csv will be enough to demonstrate the format of the netcdf.  

Would it help if I create a few lists to insert data into the table?  

Thanks
dbconnectionNetCDF.py
AndyC1000Author Commented:
I added brackets around the date values see below, the table is inserted successfully.  I'm working on inserting the data.  Any tips or examples to build the SQL string in this format?

with open(r'C:/NetCDF/output.csv', 'wb') as csvFile:
    outputwriter = csv.writer(csvFile, delimiter=',')
    SQL = 'CREATE TABLE OutputTable(ID integer, Latitude double, Longitude double'
    for date_val in date_strings:
        header.append(date_val)
        # note the bracket surrounding the date value
        SQL += ','+ '['+ date_val + ']' + ' double'
    outputwriter.writerow(header)
    SQL+= ')'
    print SQL
    connection.cursor().execute(SQL).commit()
    connection.close()
    for lat_index, lat in enumerate(lats):
        for lon_index, lon in enumerate(lons):
            content = [lat,lon]
            for time_index, time in enumerate(times[:]): 
                data = f[time_index,lat_index,lon_index]
                content.append(data)
                # insert into table code here
                #SQL_Insert = not sure how to build the string 
            if(data != "--"):
                outputwriter.writerow(content) 

Open in new window

Walter RitzelSenior Software EngineerCommented:
Ok, I think I got it. But I need some clarification: the netcdf data is in a file or is in the database? You want to get this data and dump to a csv or insert into a table?
I'm asking because you already said both things, so I'm confused. Your code seems to want to write a CSV file, but also is creating a table... If you can enumerate the steps you really want to do, would be nice.
Walter RitzelSenior Software EngineerCommented:
And about the brackets, this means that youe field names have spaces in it... Access uses brackets to allow that.
AndyC1000Author Commented:
I've attached another version of the code.  I have enumerated the steps below.

1. Read NetCDF File
2. Write to OutputTable CSV and Output Database Table (requested by users different ways of accessing data)
3. Run Query using Output database table and others
4. Write to ModelData CSV

The code is now mostly working except for writing the header to the CSV file ModelData_Output.csv.  See task 4.  All other data is formatted correctly.

When I print the string to screen it looks correct (see below) however when it's printed to the file, i.e.  outputwriter2.writerow(output_Header) each character is printed to column instead of breaking up the headers according to comma delimited.

LocationID, NAME, ID, Latitude, Longitude ,[01-12-2014],[02-12-2014],[03-12-2014],[04-12-2014],[04-12-2014],[05-12-2014],[06-12-2014]


Another issue is the error message "pyodbc.error: ('HY000', "[HY000] [Microsoft][ODBC Microsoft Access Driver] Could not lock table 'OutputTable'; currently in use by user 'admin' on machine.  I've found this only happens if I've opened and closed the database, some users may want to do this.   I've checked the connection is closed is there any other way to resolve this?
dbconnectionNetCDF.py
Walter RitzelSenior Software EngineerCommented:
I don't see anything wrong with the way you have written the header. Can you send the model data.CSV with the header?
AndyC1000Author Commented:
I just realised I copied the header example incorrectly, it should be
LocationID, NAME, ID, Latitude, Longitude ,[01-12-2014],[02-12-2014],[03-12-2014],[04-12-2014],[05-12-2014],[06-12-2014],[07-12-2014]

I've also attached ModelData.
ModelData.csv
AndyC1000Author Commented:
I'm still having issue's with the header see output file Model Data.csv, each character is being written to a column.
Walter RitzelSenior Software EngineerCommented:
I have analyzed the source code and it does not seems that there is anything there coded to generate that behavior.
But I have one suggestion to make:
replace this line in your code (I think it should be line 81):
with open(r'C:/NetCDF/ModelData_Output.csv', 'wb') as csvFile:

Open in new window


by this line
with open(r'C:/NetCDF/ModelData_Output.csv', 'w', newline='') as csvFile:

Open in new window


If this still does not work, please attach here your code (if it have changed and mainly with the query used at the end), the *.nc file or at least a few lines of it, so I can run your script properly.
AndyC1000Author Commented:
I found the issue it was the formatting of the header string.
Walter RitzelSenior Software EngineerCommented:
Can you post the lines you have fixed?
AndyC1000Author Commented:
I realised I shouldn't be using the variable SQL_Table_Def to build the header string because it contains brackets around the date fields for use in MS Access.  Instead I defined output_header, added the header values I knew of then used output_header.append(date_val) to include the header values that are contained in the netcdf file.
 

output_header = ['LocationID', 'NAME', 'ID','Latitude', 'Longitude']

with open(r'C:/NetCDF/output.csv', 'wb') as csvFile:
    outputwriter = csv.writer(csvFile, delimiter=',')
    SQL = 'CREATE TABLE Output(ID integer, Latitude double, Longitude double'
    SQL_Table_Def = 'ID, Latitude, Longitude'
    for date_val in date_strings:
        header.append(date_val)
       output_header.append(date_val)
        SQL += ', '+ '[' + date_val +']' + ' double'
        SQL_Table_Def += ','+ '['+ date_val + ']' 
    outputwriter.writerow(header)
    SQL+= ')'

# write the file
with open(r'C:/NetCDF/ModelData_Output.csv', 'wb') as csvFile1:
    outputwriter2 = csv.writer(csvFile1, delimiter=',')
    outputwriter2.writerow(output_header)
    for row in rows:
        outputwriter2.writerow(row)

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Python

From novice to tech pro — start learning today.