如何使用Python中的两个键通过字典值来枚举字典值?

发布于 01-23 20:22 字数 1981 浏览 2 评论 0原文

我的数据看起来像下面,它在带有扩展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 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(1

貪欢2025-01-30 20:22:56

您可以利用一个事实,即“ column_name”“ column_type”列表以使用单个迭代来减少问题,以下事实。变量:

# Simulated Excel Data
ws = {
    'max_row': 3,
    'cell': [
    ['Name', 'String'],
    ['Salary', 'Numeric'],
    ['Date', 'Date'],
    ['Phone', 'Int']
    ]
}

mylines={"Column_name":[],"Column_Type":[]}

for i in range(0, ws['max_row']+1):  
    name = ws['cell'][i][0]
    name1=ws['cell'][i][1]
    mylines["Column_name"].append(name)
    mylines["Column_Type"].append(name1)

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 += ", "

# OLD
print("CREATE TABLE TRANSIENT TABLE STG_EMPLOYEE({});".format([(mylines[k]) for k,v in mylines.items()]) + "\n")
# NEW
print("CREATE TABLE TRANSIENT TABLE STG_EMPLOYEE({});".format(theString) + "\n")

当我运行此功能时,我得到了以下输出(旧版本的第一行,以及新版本的第二行):

CREATE TABLE TRANSIENT TABLE STG_EMPLOYEE([['Name', 'Salary', 'Date', 'Phone'], ['String', 'Numeric', 'Date', 'Int']]);

CREATE TABLE TRANSIENT TABLE STG_EMPLOYEE( Name String, Salary Numeric, Date Date, Phone Int);

当然,如果您在电子表格中有实际的数字和其他非弦乐数据,则您' 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:

# Simulated Excel Data
ws = {
    'max_row': 3,
    'cell': [
    ['Name', 'String'],
    ['Salary', 'Numeric'],
    ['Date', 'Date'],
    ['Phone', 'Int']
    ]
}

mylines={"Column_name":[],"Column_Type":[]}

for i in range(0, ws['max_row']+1):  
    name = ws['cell'][i][0]
    name1=ws['cell'][i][1]
    mylines["Column_name"].append(name)
    mylines["Column_Type"].append(name1)

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 += ", "

# OLD
print("CREATE TABLE TRANSIENT TABLE STG_EMPLOYEE({});".format([(mylines[k]) for k,v in mylines.items()]) + "\n")
# NEW
print("CREATE TABLE TRANSIENT TABLE STG_EMPLOYEE({});".format(theString) + "\n")

When I ran this, I got the following output (the first line from the old version and the second line from the new version):

CREATE TABLE TRANSIENT TABLE STG_EMPLOYEE([['Name', 'Salary', 'Date', 'Phone'], ['String', 'Numeric', 'Date', 'Int']]);

CREATE TABLE TRANSIENT TABLE STG_EMPLOYEE( Name String, Salary Numeric, Date Date, Phone Int);

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 to theString.

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 in mylines.

~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文