1

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');
Matt
  • 967
  • 2
  • 9
  • 23
  • 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 Jun 13 '17 at 15:58
  • Thanks for your suggestion, but it yielded the same result. – Matt Jun 13 '17 at 16:16
  • What did you try? Did you split the commands or run a stored procedure? Please show. – Parfait Jun 13 '17 at 16:55
  • 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 Jun 14 '17 at 16:19
  • @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 Jun 15 '17 at 13:04
  • It didn't fail in SMSS before, but I added the semi-colon for good measure. – Matt Jun 15 '17 at 17:08
  • And no exception or error is raised? – Parfait Jun 15 '17 at 18:17
  • nope, nothing . – Matt Jun 15 '17 at 18:55
  • Then query should have run ok. Does the completed message appear? – Parfait Jun 15 '17 at 19:09
  • @Parfait I found the solution. I will post shortly. – Matt Jun 16 '17 at 15:00
  • I am very interested to see this solution! – Parfait Jun 16 '17 at 16:01

1 Answers1

1

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')
Matt
  • 967
  • 2
  • 9
  • 23
  • Took too long to find this answer, thank you!! The `connection.comit()` portion isn't even included in the pypyodbc README documentation on github. – cgage1 Jun 17 '20 at 18:25