Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- import pyodbc import _csv
- source_path= r'C:UsersuserDocumentsQA Canvasmodule2Module 2 ChallengeUFO_Merged.csv'
- source_expand= open(source_path, 'r')
- details= source_expand.readlines
- print('Connecting...')
- try:
- conn = pyodbc.connect(r'DRIVER={ODBC Driver 13 for SQL Server};'r'SERVER=FAHIMSQLEXPRESS;'r'DATABASE=Ash;'r'Trusted_Connection=yes')
- print('Connected')
- cur = conn.cursor()
- print('Cursor established')
- sqlquery ="""
- IF EXISTS
- (
- SELECT TABLE_NAME ,TABLE_SCHEMA FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'UFO_MERGED' AND TABLE_SCHEMA = 'dbo')
- BEGIN
- DROP TABLE [dbo].[UFO_MERGED]
- END
- CREATE TABLE [dbo].[UFO_MERGED]
- ( [ID] smallint
- ,[COMMENTS] varchar(max)
- ,[FIRST OCCURANCE] datetime
- ,[CITY] varchar(60)
- ,[COUNTRY] varchar(20)
- ,[SHAPE] varchar(20)
- ,[SPEED] smallint
- ,[SECOND OCCURANCE] datetime
- PRIMARY KEY(id)
- ) ON [PRIMARY]
- """
- result = cur.execute(sqlquery).fetchall()
- for row in result:
- print(row)
- print("{} rows returned".format(len(result)))
- sqlstr= """
- Insert into [dbo].[UFO_Merged] values ('(),'(),'(),'(),'(),'(),'(),'(),)
- """
- for row in details[1:]:
- row_data =row.split(',')
- sqlquery=sqlstr.format(row_data[0],row_data[1],row_data[2],row_data[3],row_data[4],row_data[5],row_data[6],row_data[7])
- result=cur.execute(sqlquery)
- conn.commit()
- conn.close()
- except Exception as inst:
- if inst.args[0]== '08001':
- print("Cannot connect to the server")
- elif inst.args[0] == '28000':
- print("Login failed - check connection string")
- else:
- print(inst)
Add Comment
Please, Sign In to add comment