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