如何使用Python中的两个键通过字典值来枚举字典值?
我的数据看起来像下面,它在带有扩展XLSX的Excel文件中,因此我使用OpenPyXl
库来读取Python中的文件,并从第6行中提取2列的数据,我是将提取的数据附加到字典中,并使用键“ column_name”和“ column_type”。这样做的原因是因为,我想在提取的数据之间添加额外的字符串。我要添加的字符串是创建表瞬态表STG_EMPLOYEE();
到目前为止,我的代码和输出看起来像下面。我在TXT文件中的预期输出也如下所述。如果您注意到,我想同时从两个密钥中枚举价值,我不确定如何在python
中实现这一目标,以便对任何帮助表示赞赏?
事先感谢您的时间和精力!
数据
File Name: Employee
Sheet Name: Employee
File Type: csv
Field Name Type
Name String
Salary Numeric
Date Date
Phone Int
到目前为止的代码
from openpyxl import load_workbook
data_file='\\test.xlsx'
# Load the entire workbook.
wb = load_workbook(data_file)
ws = wb['Employee'] #Access Sheet
outputFile = open('/output.txt', 'w') # Text file Output
outputFile.write("CREATE TABLE TRANSIENT TABLE STG_EMPLOYEE({});".format(theString) + "\n")
mylines={"Column_name":[],"Column_Type":[]} #Getting 2 columns data from row 6
for i in range(6, ws.max_row+1):
name = ws.cell(row=i, column=1).value
name1=ws.cell(row=i, column=2).value
mylines["Column_name"].append(name) #Appending dictionary key "Column_name"
mylines["Column_Type"].append(name1) #Appending dictionay key "Column_type"
theString = " "
# This relies on the lists 'Column_name' and 'Column_Type' always being the same length
# I.e., there should always be a value for each key
for i in range(len(mylines['Column_name'])):
theString += mylines['Column_name'][i] + " " + mylines['Column_Type'][i]
if i < ws.max_row:
theString += ", "
outputFile.close()
在TXT文件中输出上述代码:
CREATE TABLE TRANSIENT TABLE STG_EMPLOYEE([['Name', 'Salary', 'Date', 'Phone'], ['String', 'Numeric', 'Date', 'Int']]);
txt文件中的预期输出
CREATE TABLE TRANSIENT TABLE STG_EMPLOYEE( Name String, Salary Numeric, Date Date, Phone Int);
My data looks like below, and it is in excel file with extension xlsx, so I am using openpyxl
library to read the file in python and extract the data from row 6 onwards for 2 columns,and I am appending the extracted data into dictionary with keys "Column_name" and "Column_Type". The reason for doing this way is because, I would like to add extra string between the extracted data. The string I would like to add is CREATE TABLE TRANSIENT TABLE STG_EMPLOYEE();
So far my code and output looks like below.My expected output in txt file is also mentioned below. If you notice, I would like to enumerate value from both keys at same time, I am not sure how to achive this in python
so any help is appreciated ?
Thanks in advance for your time and effort!
Data
File Name: Employee
Sheet Name: Employee
File Type: csv
Field Name Type
Name String
Salary Numeric
Date Date
Phone Int
Code so far
from openpyxl import load_workbook
data_file='\\test.xlsx'
# Load the entire workbook.
wb = load_workbook(data_file)
ws = wb['Employee'] #Access Sheet
outputFile = open('/output.txt', 'w') # Text file Output
outputFile.write("CREATE TABLE TRANSIENT TABLE STG_EMPLOYEE({});".format(theString) + "\n")
mylines={"Column_name":[],"Column_Type":[]} #Getting 2 columns data from row 6
for i in range(6, ws.max_row+1):
name = ws.cell(row=i, column=1).value
name1=ws.cell(row=i, column=2).value
mylines["Column_name"].append(name) #Appending dictionary key "Column_name"
mylines["Column_Type"].append(name1) #Appending dictionay key "Column_type"
theString = " "
# This relies on the lists 'Column_name' and 'Column_Type' always being the same length
# I.e., there should always be a value for each key
for i in range(len(mylines['Column_name'])):
theString += mylines['Column_name'][i] + " " + mylines['Column_Type'][i]
if i < ws.max_row:
theString += ", "
outputFile.close()
Output in txt file with above Code:
CREATE TABLE TRANSIENT TABLE STG_EMPLOYEE([['Name', 'Salary', 'Date', 'Phone'], ['String', 'Numeric', 'Date', 'Int']]);
Expected output in Txt file
CREATE TABLE TRANSIENT TABLE STG_EMPLOYEE( Name String, Salary Numeric, Date Date, Phone Int);
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

您可以利用一个事实,即
“ column_name”
和“ column_type”
列表以使用单个迭代来减少问题,以下事实。变量:当我运行此功能时,我得到了以下输出(旧版本的第一行,以及新版本的第二行):
当然,如果您在电子表格中有实际的数字和其他非弦乐数据,则您' LL还希望使用
str()
将变量值施加到字符串,然后再将它们附加到thestring
。与您的代码的唯一区别(除了我将代码工作作为独立的更改之外从
mylines
中的两个列表中。You could take advantage of the fact that there should always be the same number of items in the
"Column_name"
and"Column_Type"
lists to reduce the problem to iteration using a single variable:When I ran this, I got the following output (the first line from the old version and the second line from the new version):
Of course, if you have actual numeric and other non-string data in your spreadsheet, you'll also want to cast the variable values to strings using
str()
before appending them totheString
.The only difference from your code (other than the changes I made to have the code work as a standalone without an actual spreadsheet) is replacing the
format
parameter in the last line with a string built by appending the values from both lists inmylines
.