Nov-15-2018, 01:48 PM
Nov-15-2018, 02:06 PM
It's working.
Need some sql insert help (output values)..
which value i want insert in SQL...
Need some sql insert help (output values)..
which value i want insert in SQL...
Nov-15-2018, 02:09 PM
Nov-15-2018, 02:35 PM
I tried the below code..
but, it is showing error.
what is the problem in the below code..
cnxn = pyodbc.connect('DRIVER={SQL Server Native Client 11.0};SERVER='+server+';DATABASE='+database+';UID='+username+';PWD='+ password)
cur = cnxn.cursor()
sql = "INSERT INTO test1 (EmpNo, Name, SAL) VALUES (%s, %s, %s)"
val = (Staff_no, Emp_name, emp_dept)
res = cur.execute(sql, val)
but, it is showing error.
what is the problem in the below code..
cnxn = pyodbc.connect('DRIVER={SQL Server Native Client 11.0};SERVER='+server+';DATABASE='+database+';UID='+username+';PWD='+ password)
cur = cnxn.cursor()
sql = "INSERT INTO test1 (EmpNo, Name, SAL) VALUES (%s, %s, %s)"
val = (Staff_no, Emp_name, emp_dept)
res = cur.execute(sql, val)
Nov-15-2018, 02:37 PM
(Nov-15-2018, 02:35 PM)ganeshsai2912 Wrote: [ -> ]but, it is showing error.post the full traceback in error tags
Nov-15-2018, 02:38 PM
sorry. error is below.
---------------------------------------------------------------------------
ProgrammingError Traceback (most recent call last)
<ipython-input-49-e00dcd4f8f95> in <module>()
27 sql = "INSERT INTO test1 (Staffno, EmpName, Department) VALUES (%s, %s, %s)"
28 val = (Staff_no, Emp_name, emp_dept)
---> 29 res = cur.execute(sql, val)
30 cnxn.commit()
ProgrammingError: ('The SQL contains 0 parameter markers, but 3 parameters were supplied', 'HY000')
---------------------------------------------------------------------------
ProgrammingError Traceback (most recent call last)
<ipython-input-49-e00dcd4f8f95> in <module>()
27 sql = "INSERT INTO test1 (Staffno, EmpName, Department) VALUES (%s, %s, %s)"
28 val = (Staff_no, Emp_name, emp_dept)
---> 29 res = cur.execute(sql, val)
30 cnxn.commit()
ProgrammingError: ('The SQL contains 0 parameter markers, but 3 parameters were supplied', 'HY000')
Nov-15-2018, 02:43 PM
you should use
Also note about cursor.commit() and execute/executemany options
https://github.com/mkleehammer/pyodbc/wiki/Cursor
?
, not %s
.Also note about cursor.commit() and execute/executemany options
https://github.com/mkleehammer/pyodbc/wiki/Cursor
Nov-15-2018, 02:52 PM
Yes. i used executemany. below i the error
params = (Staff_no, Emp_name, emp_dept)
#res = cursor.executemany(sql, val)
res = cursor.executemany("insert into test1(Staffno, EmpName, Department) values (?, ?, ?)", params)
cursor.commit()
---------------------------------------------------------------------------
TypeError Traceback (most recent call last)
<ipython-input-54-06ea60fdf959> in <module>()
28 params = (Staff_no, Emp_name, emp_dept)
29 #res = cursor.executemany(sql, val)
---> 30 res = cursor.executemany("insert into test1(Staffno, EmpName, Department) values (?, ?, ?)", params)
31 cursor.commit()
TypeError: ('Params must be in a list, tuple, or Row', 'HY000')
params = (Staff_no, Emp_name, emp_dept)
#res = cursor.executemany(sql, val)
res = cursor.executemany("insert into test1(Staffno, EmpName, Department) values (?, ?, ?)", params)
cursor.commit()
---------------------------------------------------------------------------
TypeError Traceback (most recent call last)
<ipython-input-54-06ea60fdf959> in <module>()
28 params = (Staff_no, Emp_name, emp_dept)
29 #res = cursor.executemany(sql, val)
---> 30 res = cursor.executemany("insert into test1(Staffno, EmpName, Department) values (?, ?, ?)", params)
31 cursor.commit()
TypeError: ('Params must be in a list, tuple, or Row', 'HY000')
Nov-15-2018, 03:10 PM
plase, start using error tags, when post traceback.
in the above code you don't have many employees data, you pass only one employee data, so you cannot use execute many (or more correct - it doesn't make sense to use it.
you either parse all files and collect all data e.g. in list of lists or process/submit one file at a time.
the advantage of process all files and then submit is that you will insert all data or none. if you process one file at a time and you get error somewhere in the middle, then you will have to keep track what is already inserted and what not.
something like this (NOT TESTED)
in the above code you don't have many employees data, you pass only one employee data, so you cannot use execute many (or more correct - it doesn't make sense to use it.
you either parse all files and collect all data e.g. in list of lists or process/submit one file at a time.
the advantage of process all files and then submit is that you will insert all data or none. if you process one file at a time and you get error somewhere in the middle, then you will have to keep track what is already inserted and what not.
something like this (NOT TESTED)
import glob def parse_line(line): for item in ['Emp No', 'Name', 'SAL']: line = line.replace(item, '') return [item.strip() for item in line.split(':') if item.strip()] def process_file(file_name): with open(file_name) as f: # No need to specify 'r': this is the default. line = f.readlines()[6] return parse_line(line) def write_db(data, server, database, password): connection_string = f'DRIVER=\{SQL Server Native Client 11.0\};SERVER={server};DATABASE={database};UID={username};PWD={password}' with pyodbc.connect(connection_string, autocommit=False) as cnxn: cur = cnxn.cursor() sql = "INSERT INTO test1 (EmpNo, Name, SAL) VALUES (?, ?, ?)" cur.executemany(sql, data) pattern = 'C:/Test/Python/Mag/*.txt' files = glob.glob(pattern) all_data = [parse(file_name) for file_name in files] write_db(data=all_data, server=server, database=database, password=password) # replace srever, database, password with actual values
Nov-16-2018, 12:57 AM
Thank You.
Is it possible to read one more item fro the text file..
currently i am using empno, name, sal.
along with this, i need to add two more parameter (relieve date,EmpDesignation,No.ofPrjhandled).
I thing below line is reading particular text from the text fille.
line = my_file.split('\n')[6]
if i want another parameter in another line how do we specify..?
def parse_line(line):
for item in ['Emp No', 'Name', 'SAL']:
line = line.replace(item, '')
return [item.strip() for item in line.split(':') if item.strip()]
my_file = """Please find below emp details,
EMP DETAILS
________________________________
Emp No: 101 Name:RASUL K SAL: 30000
________________________________
Join Date: 10 JAN 2010 From Native: First
Releave Date: 25 APR 2018 To Native: Second
EmpDesignation: Manager
No.ofProjects handled: 10
"""
line = my_file.split('\n')[6]
print(parse_line(line))
Is it possible to read one more item fro the text file..
currently i am using empno, name, sal.
along with this, i need to add two more parameter (relieve date,EmpDesignation,No.ofPrjhandled).
I thing below line is reading particular text from the text fille.
Is it possible to read one more item fro the text file..
currently i am using empno, name, sal.
along with this, i need to add two more parameter (relieve date,EmpDesignation,No.ofPrjhandled).
I thing below line is reading particular text from the text fille.
line = my_file.split('\n')[6]
if i want another parameter in another line how do we specify..?
def parse_line(line):
for item in ['Emp No', 'Name', 'SAL']:
line = line.replace(item, '')
return [item.strip() for item in line.split(':') if item.strip()]
my_file = """Please find below emp details,
EMP DETAILS
________________________________
Emp No: 101 Name:RASUL K SAL: 30000
________________________________
Join Date: 10 JAN 2010 From Native: First
Releave Date: 25 APR 2018 To Native: Second
EmpDesignation: Manager
No.ofProjects handled: 10
"""
line = my_file.split('\n')[6]
print(parse_line(line))
Is it possible to read one more item fro the text file..
currently i am using empno, name, sal.
along with this, i need to add two more parameter (relieve date,EmpDesignation,No.ofPrjhandled).
I thing below line is reading particular text from the text fille.
def parse_line(line): for item in ['Emp No', 'Name', 'SAL']: line = line.replace(item, '') return [item.strip() for item in line.split(':') if item.strip()] my_file = """Please find below emp details, EMP DETAILS ________________________________ Emp No: 101 Name:RASUL K SAL: 30000 ________________________________ Join Date: 10 JAN 2010 From Native: First Releave Date: 25 APR 2018 To Native: Second EmpDesignation: Manager No.ofProjects handled: 10 """ line = my_file.split('\n')[6] print(parse_line(line))