Click here to Skip to main content
15,351,229 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I'm trying to save data in SSMS (SQL) but can't able to insert in DB.

The Error which I get - 'type' object is not subscriptable


Error Block:
try:
for record in records:
print(record)
cursor.execute(insert_statement, record)
except Exception as e:
cursor.rollback()
print(e)
print('Transaction Rolled Back')
else:
print('Record Inserted Sucessfully')
cursor.commit()
Conn.close()
finally:
if Conn.connected == 1:
print('Connection Closed')
Conn.close()


What I have tried:

Full Code:
try:
				
				def GetLinkedin():

					URLs_all_Page = GetURLsonPages()

					for Linkedin_URL in URLs_all_Page:
						driver.get(Linkedin_URL)
						time.sleep(2)

						page_source = driver.page_source
						soup = BeautifulSoup(page_source, "html.parser")

						ExeName = soup.find('dt', {'class':'flex align-items-center'}).find('span', {'class':'profile-topcard-person-entity__name t-24 t-black t-bold'})
						soup_ExeName_1 = BeautifulSoup(ExeName.text.replace("\n", ""), 'html.parser')
						soup_ExeName = BeautifulSoup(soup_ExeName_1.text.replace("  ", ""), 'html.parser')
						#------------------------------------------------------------------------
						if soup.find('dd', {'class':'profile-topcard__previous-positions flex mt3'}) is not None:
							PreCompany = soup.find('dd', {'class':'profile-topcard__previous-positions flex mt3'}).find('span', {'class':'t-14 t-black t-bold'})
							if PreCompany is None:
								PreCompany = soup.find('dd', {'class':'profile-topcard__previous-positions flex mt3'}).find('span', {'class':'align-self-center'}).find('a', {'class':'li-i18n-linkto inverse-link-on-a-light-background t-14 t-black t-bold'})
								soup_PreCompany = BeautifulSoup(PreCompany.text,'html.parser')

							elif PreCompany is not None:
								soup_PreCompany = BeautifulSoup(PreCompany.text,'html.parser')

						elif soup.find('dd', {'class':'profile-topcard__previous-positions flex mt3'}) is None:
							soup_PreCompany = "$No Data$"
						#------------------------------------------------------------------------
						NewTitle = soup.find('span', {'class':'align-self-center'}).find('span', {'class':'profile-topcard__summary-position-title'})
						soup_NewTitle = BeautifulSoup(NewTitle.text,'html.parser')
						#------------------------------------------------------------------------
						Company = soup.find('span', {'class':'align-self-center'}).find('span', {'class':'t-14 t-black t-bold'})	
						if Company is None:
							Company = soup.find('span', {'class':'align-self-center'}).find('a', {'class':'li-i18n-linkto inverse-link-on-a-light-background t-14 t-black t-bold'})
							soup_Company = BeautifulSoup(Company.text, 'html.parser')

						elif Company is not None:
							soup_Company = BeautifulSoup(Company.text,'html.parser')
						#------------------------------------------------------------------------
						tenur = soup.find('span', {'class':'align-self-center'}).find('span', {'class':'profile-topcard__time-period-bullet'})
						soup_tenur_1 = BeautifulSoup(tenur.text.replace("\n", ""),'html.parser')
						soup_tenur = BeautifulSoup(soup_tenur_1.text.replace("  ", ""),'html.parser')
						#------------------------------------------------------------------------		
						CRMIO = soup.find('button', {'class':'button--unstyled label-16dp color-black6 pv1 ph2 _crm-badge_5gr6fh'})
						if CRMIO is None:
							CRMIO = soup.find('button', {'class':'artdeco-button artdeco-button--muted artdeco-button--1 artdeco-button--tertiary ember-view label-16dp cursor-pointer color-black6 pv1 ph2 _crm-badge_5gr6fh'}).find('span', {'class':'artdeco-button__text'})	
							soup_CRMIO = BeautifulSoup(CRMIO.text.replace("\n", ""),'html.parser')
							soup_CRM = BeautifulSoup(soup_CRMIO.text.replace("  ", ""),'html.parser')
						elif CRMIO is not None:
							soup_CRMIO = BeautifulSoup(CRMIO.text.replace("\n", ""),'html.parser')
							soup_CRM = BeautifulSoup(soup_CRMIO.text.replace("  ", ""),'html.parser')
						#------------------------------------------------------------------------	
							print (soup_ExeName,"|",soup_PreCompany,"|",soup_NewTitle,"|",soup_Company,"|",soup_tenur,"|",soup_CRM)
						
							try:
								records = [
									[soup_ExeName, soup_PreCompany, soup_NewTitle, soup_Company, soup_tenur, soup_CRM]
								]
								
								DRIVER = 'SQL Server'
								SERVER_NAME = 'SRIRAM\SQLEXPRESS'
								DATABASE_NAME = 'DataSet'

								Conn_string = f"""
									Driver={{{DRIVER}}};
									Server={SERVER_NAME};
									Database={DATABASE_NAME};
									Trust_Connection=yes;
								"""

								try:
									Conn = odbc.connect(Conn_string)
								except Exception as e:
									print(e)
									print('Task is Terminated')
									sys.exit()
								else:
									cursor = Conn.cursor()

								insert_statement = """
									INSERT INTO RecordSet
									VALUES (?, ?, ?, ?, ?, ?)
								"""

								try:
									for record in records:
										print(record)
										cursor.execute(insert_statement, record)
								except Exception as e:
									cursor.rollback()
									print(e)
									print('Transaction Rolled Back')
								else:
									print('Record Inserted Sucessfully')
									cursor.commit()
									Conn.close()
								finally:
									if Conn.connected == 1:
										print('Connection Closed')
										Conn.close()
								
							except Exception as e: 
								print(e)
Posted
Updated 27-Dec-21 22:30pm
Comments
Richard MacCutchan 27-Dec-21 12:39pm
   
And where does that error message occur?
Sriram Krishnamoorthy 2021 27-Dec-21 12:44pm
   
In Error Block under this range

cursor.execute(insert_statement, record)
except Exception as e:
cursor.rollback()
Richard MacCutchan 27-Dec-21 12:47pm
   
My mistake, the list appears to be correct. I suggest you check the documentation for the execute function.
Sriram Krishnamoorthy 2021 27-Dec-21 13:14pm
   
Thank You, Richard

But, I can't able to find the exception on it. Output which I get are below. It's terminating while inserting data.
Code Line: cursor.execute(insert_statement, record).

[Arianne M Collette, Morgan Stanley, Chief Operating Officer, Morgan Stanley, 4 mos, Not in CRM]
'type' object is not subscriptable
Transaction Rolled Back
Connection Closed

Gerry Schmitz 27-Dec-21 13:32pm
   
You created a Python array instead of an "object"; use "()" instead of "[]".
Richard MacCutchan 28-Dec-21 3:40am
   
That is rather odd, and the error message even odder. A Python list is subscriptable, as are most compound types.
Sriram Krishnamoorthy 2021 28-Dec-21 10:44am
   
I tried that as you mentioned, but still I'm getting the same result? But I tried it separately, its working!

[LinkedIn Member, '$No Data$', Senior Vice President, Portfolio Manager, Wellington Management, 38 yrs, Not in CRM]
'type' object is not subscriptable
Transaction Rolled Back

import sys
import pypyodbc as odbc

records = [
[' Sriram K ', ' ForwardLine Financial ', ' Principal & Chief Technology Officer ', ' Platinum Equity ', ' 2 yrs ',' In CRM ']
]

DRIVER = 'SQL Server'
SERVER_NAME = 'SRIRAM\SQLEXPRESS'
DATABASE_NAME = 'master'

Conn_string = f"""
Driver={{{DRIVER}}};
Server={SERVER_NAME};
Database={DATABASE_NAME};
Trust_Connection=yes;
"""

try:
Conn = odbc.connect(Conn_string)
except Exception as e:
print(e)
print('Task is Terminated')
sys.exit()
else:
cursor = Conn.cursor()

insert_statement = """
INSERT INTO RecordSet
VALUES (?, ?, ?, ?, ?,?)
"""

try:
for record in records:
print(record)
cursor.execute(insert_statement, record)
except Exception as e:
cursor.rollback()
print(e)
print('Transaction Rolled Back')
else:
print('Record Inserted Sucessfully')
cursor.commit()
Conn.close()
finally:
if Conn.connected == 1:
print('Connection Closed')
Conn.close()

[' Sriram K ', ' ForwardLine Financial ', ' Principal & Chief Technology Officer ', ' Platinum Equity ', ' 2 yrs ', ' In CRM ']
Record Inserted Sucessfully
Richard MacCutchan 28-Dec-21 11:39am
   
I guess I missed that. The items in the values set must be a set of actual values, not variable names.

The error message could maybe be more helpful.
Richard MacCutchan 28-Dec-21 12:27pm
   
I think you may be able to do what you want by modifying your INSERT statement to set the VALUES list as a string of parameter names, each preceded by a colon, thus:
								insert_statement = """
									INSERT INTO RecordSet
									VALUES (:soup_ExeName, :soup_PreCompany, :soup_NewTitle, :soup_Company, :soup_tenur, :soup_CRM)
								"""
Sriram Krishnamoorthy 2021 28-Dec-21 12:52pm
   
I got the solution, The problem which occurs due to BeautifulSoup !

so I just convert into a string: Title = str(soup_NewTitle)

NewTitle = soup.find('span', {'class':'align-self-center'}).find('span', {'class':'profile-topcard__summary-position-title'})
soup_NewTitle = BeautifulSoup(NewTitle.text,'html.parser')
Title = str(soup_NewTitle)

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900