AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |
Back to Blog
See Oracle+Python docs.Īdjusted code # looping through each row for each column execute(query, params) which not only insulates SQL injection (in case Excel cells have malicious code by a clever user) but avoids string concatenation and quote enclosure for cleaner code. Parameterize your query by using the second argument in.commit() for any action queries like CREATE TABLE and INSERT INTO to propagate changes. Indent your execute line in row-wise loop.Reversing your nested for loop by running down the rows and iteratively assigning column values and then append each row.If you have more than one sheet, and need to insert all the contents of the sheets into the table, then replace the trailing part of the code, after creating table( cursor.execute(create_table)) with the below one : xl = pd.ExcelFile(file) Where also add the case of dropping the TEST table whether it exists( without forgetting to handling with care for dropping a table). You might prefer a direct( without need of loop for each column of each row) and more performant( with use of cursor.executemany function) method along with the data analytics library(pandas) as follows : import pandas as pdĮXECUTE IMMEDIATE('DROP TABLE """+tab_name+"""') INSERT INTO test (col1,col2,col3,col4,col5,col6,col7) # Inserting all the above variables for each column loop #looping through each row for each column Wb = openpyxl.load_workbook('',data_only=True) Print("Database version:", connection.version) TIA! import openpyxlĬonnection = cx_nnect("", "", "/") I want to load the data as it reads from excel in a loop for every column. I am not sure how can I have 'insert statement' or 'create table' statement as part of this code in Oracle part. Edit: I mean this is just my partial code. I did code few lines to get acquainted as shown below. Is there a way we can read data from excel file and load into Oracle table? Some sample python script would be of great help.
0 Comments
Read More
Leave a Reply. |