pypyodbc network path not working properly

Question

I'm using Python 3.6.1 and the pypyodbc library to import a .csv file into a local DB in Management Studio. Can anyone tell me why this code would work perfectly fine in Management Studio, but not in my pypyodbc script?

The Python script runs without any errors, but it doesn't actually insert the new .csv file in C:\MemberMapUpdates into the CSVTest table. When I run that SQL command in management studio, it inserts the values as expected. Any insights are appreciated. Thanks.

import pypyodbc
connection = pypyodbc.connect('Driver={SQL Server};'
                              'Server=SERVER-SQL;'
                              'Database=server1;'
                              'uid=sa;pwd=Pa$$word!')
cursor = connection.cursor()
SQLCommand = (
               '''
                 drop table if exists CSVTest

                 create table CSVTest
                 (dccode varchar(255),
                 member varchar(255),
                 date_sub date,
                 date_add date,
                 sa_update date,
                 buff_rad float,
                 geom varchar(255),
                 sub_type varchar(255),
                 notes varchar(255))


                 bulk
                 insert CSVTest
                 from 'C:\MemberMapUpdates\MemberMapUpdates.csv'
                 with (FIRSTROW = 3, FIELDTERMINATOR = ',', ROWTERMINATOR = '\n')
              '''
              )
cursor.execute(SQLCommand)
connection.close()
print('Process Completed')

updated script with stored procedure inserted

import pypyodbc
#from osgeo import ogr

connection = pypyodbc.connect('Driver={SQL Server};'
                              'Server=SERVER-SQL;'
                              'Database=DB;'
                              'uid=sa;pwd=p@SSWORD!')
cursor = connection.cursor()
SQLCommand = ('exec FirstProcedure')
cursor.execute(SQLCommand)
connection.close()
print('Process Completed')

this is the stored procedure:

             drop table if exists CSVTest;

             create table CSVTest
             (dccode varchar(255),
             member varchar(255),
             date_sub date,
             date_add date,
             sa_update date,
             buff_rad float,
             geom varchar(255),
             sub_type varchar(255),
             notes varchar(255));


             bulk
             insert CSVTest
             from '\\NETWORKSHARE\MemberMapUpdates\MemberMapUpdates.csv'
             with (FIRSTROW = 3, FIELDTERMINATOR = ',', ROWTERMINATOR = '\n');

You have several DDL/DML commands here and cursors only run one at a time. Consider placing all in a stored procedure and have Python call it. — Parfait
What did you try? Did you split the commands or run a stored procedure? Please show. — Parfait
are you still there? I really want to help. Again what did you try? Also, are you sure Python code is exactly as you post it. Very hard to believe no exception/error is raised as syntactically this query is not valid as no semicolon is used between statements. It should fail as is in SSMS. — Parfait
@Parfait I apologize, we had a server go down on us so things have been hectic. THANK YOU for your responses. Give me just a little bit and I will update the case. Thanks. — Matt

Matt Matt · Accepted Answer · 2017-06-19T12:39:55

The missing piece of this puzzle was the connection.commit()

So this is my full query with the same stored procedure as above:

import pypyodbc

connection = pypyodbc.connect('Driver={SQL Server};'
                              'Server=SERVER-SQL;'
                              'Database=DB;'
                              'uid=sa;pwd=PASSWORD;')
cursor = connection.cursor()
SQLCommand = ("exec FirstProcedure;")
cursor.execute(SQLCommand)
connection.commit()
connection.close()
print('Process Completed')

pypyodbc network path not working properly

1 Answers