Search code examples
pythonsqlcsvpypyodbc

pypyodbc network path not working properly


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');

Solution

  • 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')