Python operations write/read csv files

BrandImg

Python Data Storage for Network Engineers (Section 1, CSV file)

A general process of the evolution of network automation operation and maintenance: network engineers have gradually evolved from focusing on configuration and making scripts, and then brushing the configuration on the device, to network engineers focusing on and determining some important control parameters of device configuration, and leaving the task of creating scripts. Leave it to Jinja2 and others to render and generate, and leave the script distribution work to nornir and others. In other words, network workers have the prospect of shifting from equipment operation and maintenance to data operation and maintenance.

Of course, the process of evolution can be traced back and forth, and progress can be achieved when obstacles are encountered; tracing back and following it is like going from getting started to giving up. I feel that I can only do more important things when it is not urgent, so that I can slowly accumulate accumulation, and pick up a few pieces of my own automation along the way ~ I found that it was already a lost sentiment, so I waved my sleeves and continued on my way ~ .

So, where does the data in data operation and maintenance exist, in what form, how to store it, and how to access it? Next I will sort out a little bit about Python data storage. In this article, we will first discuss the CSV file.

〇, reference instructions

Article from: Network Engineer Python Data Storage (Section 1, CSV file)

If you don’t know where to start, it is recommended to start with this “Table of Contents”, the link is as follows:

[Internet Craftsmanship] Column Entrance (General Catalog | I write all open source | Don’t buy pirated books)

Part of this article refers to the book “Python for network engineers”, which is in pure English and is recommended for reading.

This article is maintained for a long time, and you are welcome to participate in corrections (including knowledge points, typos, and text expressions).

If there is any infringement, please send a private message to delete it.

1. Background introduction

Can a network engineer stay in the computer room or network management backend when he is on a business trip? Of course not, you have to stay in a hotel to rest. In the same way, the data processed by Python cannot always stay in the program memory. It must eventually be stored on the hard disk! So in what form is it stored on the hard drive? Most of them are structured data, such as the pickle class in Python, CSV, JSON, YAML forms, and of course powerful database systems.

Since pickle is a unique built-in form of Pyhon, it is currently rarely directly connected to other systems; the database system is slightly larger, and it may be possible to create a separate series to sort it out. Therefore, in this series, let’s focus on CSV, JSON, and YAML formats first.

This time, let’s focus on CSV first. CSV is simply a structured table. Everyone is familiar with tables. For example, when you open Excel, there is a table. What about structuring? A simple understanding is that you must not merge cells in an open Excel table. Merging cells will destroy the structure of the table and it will lose its structure. Of course, my expression may not be rigorous. The key is to make you feel warm.

CSV (comma-separated value), as the name suggests, comma-separated data. This kind of data is obtained from the table or from the database. It can be opened with excel or imported into the database. Yes, it is an information storage medium.

2. CSV operation

2.1 First introduction to CSV

In this format, a row in the CSV file is a row in the table. Although its name is “comma-separated data”, in addition to comma separation, other symbols can also be used for separation. Files separated by different symbols may have slightly different file names, such as TSV (delimited by tabs). Despite this, we usually refer to them as “CSV”.

hostname,vendor,model,location
sw1,Huawei,5700,Beijing
sw2,Huawei,3700,Shanghai
sw3,Huawei,9300,Guangzhou
sw4,Huawei,9306,Shenzhen
sw5,Huawei,12800,Hangzhou

In Python’s standard library, there is a module specifically designed to handle CSV files. As an aside, what is the “Python standard library”? It means those libraries that you can directly import after installing Python without pip installation.

2.2 Reading CSV

Compared to other IDEs, I prefer to use IDLE for demonstration, because it exists as long as Python is installed, without any other joint debugging operations. In addition, I also feel that it can satisfy many daily small applications in the Python industry of Internet workers to a large extent.

Come, let’s open IDLE and type commands.

Use a code block of the form below. I can effectively prevent you from being “lazy”, just copy all the code and run it, thinking that you understand it.

You can only type such a code block by hand, or at least copy it line by line. Only by typing it yourself can you get a better feel for Python indentation and other grammatical structures, and really get started.

You see how well-intentioned I am, come and knock together.

>>> import csv
>>> csv_path = r'E:\***\csv-lab1.csv' #File path, according to your actual situation.
>>> with open(csv_path) as f:
reader = csv.reader(f)
for row in reader:
print(row)

\t\t
['hostname', 'vendor', 'model', 'location']
['sw1', 'Huawei', '5700', 'Beijing']
['sw2', 'Huawei', '3700', 'Shanghai']
['sw3', 'Huawei', '9300', 'Guangzhou']
['sw4', 'Huawei', '9306', 'Shenzhen']
['sw5', 'Huawei', '12800', 'Hangzhou']
>>>

Note that csv.reader returns an iterator.

>>> with open(csv_path) as f:
reader = csv.reader(f)
print(reader)

\t
<_csv.reader object at 0x0000017F1B24CBA8>
>>>

If necessary, you can use the list function to process this iterator into a list.

>>> with open(csv_path) as f:
reader = csv.reader(f)
print(list(reader))

\t
[['hostname', 'vendor', 'model', 'location'], ['sw1', 'Huawei', '5700', 'Beijing\ '], ['sw2', 'Huawei', '3700', 'Shanghai'], ['sw3', 'Huawei', '9300', ' Guangzhou'], ['sw4', 'Huawei', '9306', 'Shenzhen'], ['sw5', 'Huawei', '12800', 'Hangzhou']]
>>>

Normally, the header needs to be used separately and can be treated specially.

>>> with open(csv_path) as f:
reader = csv.reader(f)
headers = next(reader)
print('Headers: ',headers)
for row in reader:
print(row)

\t\t
Headers: ['hostname', 'vendor', 'model', 'location']
['sw1', 'Huawei', '5700', 'Beijing']
['sw2', 'Huawei', '3700', 'Shanghai']
['sw3', 'Huawei', '9300', 'Guangzhou']
['sw4', 'Huawei', '9306', 'Shenzhen']
['sw5', 'Huawei', '12800', 'Hangzhou']
>>>

Sometimes, we also need to combine the header information with the data of each row, then the dictionary data type can be used.

>>> with open(csv_path) as f:
reader = csv.DictReader(f)
for row in reader:
# print(row)
print(row['hostname'],row['model'])

\t\t
sw1 5700
sw2 3700
sw3 9300
sw4 9306
sw5 12800

For simplicity, the print(row) line of code has been commented by me. If you are interested, you can remove the comment line and try running again.

2.3 Write to CSV (writerow)

We have already experienced the CSV reading operation. It is not difficult, right? What about the writing operation of CSV? Let’s try it right away.

>>> import csv
>>> data = [['hostname','vendor','model','location'],
['sw1','Huawei','5700','Beijing'],
['sw2','Huawei','3700','Shanghai'],
['sw3','Huawei','9300','Guangzhou'],
['sw4','Huawei','9306','Shenzhen'],
['sw5','Huawei','12800','Hangzhou']]

>>> csv_path = r'E:\……\csv-lab1-writing.csv' #Save the file path, which can be based on your actual situation.
>>> with open(csv_path,'w',newline='') as f:
writer = csv.writer(f)
for row in data:
writer.writerow(row)

After pressing Enter, there will be some miscellaneous returns later, which are interference items and do not need to be ignored. What we focus on is generating the CSV file.

Note a few points:

The parameter w means writing. If not added, the default is reading r.
newline='', if you don’t add this parameter, the result will be redundant blank lines. You can try it.
If there is Chinese, etc., you may also need to pay attention to encoding and decoding issues, otherwise garbled characters may easily appear.

After the file is generated, looking back, we can also use a Python script to read it.

>>> with open(csv_path) as f:
    print(f.read())

    
hostname,vendor,model,location
sw1,Huawei,5700,Beijing
sw2,Huawei,3700,Shanghai
sw3,Huawei,9300,Guangzhou
sw4,Huawei,9306,Shenzhen
sw5,Huawei,12800,Hangzhou

2.4 Write to CSV (text with comma)

>>> import csv
>>> data = [['hostname','vendor','model','location'],
['sw1','Huawei','5700','Beijing,Xicheng'],
['sw2','Huawei','3700','Shanghai'],
['sw3','Huawei','9300','Guangzhou,Tianhe'],
['sw4','Huawei','9306','Shenzhen'],
['sw5','Huawei','12800','Hangzhou']]

>>> csv_path = r'E:\……\csv-lab1-writing.csv' #Save the file path, which can be based on your actual situation.
>>> with open(csv_path,'w',newline='') as f:
writer = csv.writer(f)
for row in data:
writer.writerow(row)

Now we have changed 'Beijing' to 'Beijing,Xicheng', and changed 'Guangzhou' to 'Guangzhou,Tianhe'. Please pay attention to the interactive execution results.

Interesting? When 'Beijing,Xicheng' and 'Guangzhou,Tianhe' are printed, “small ears” are automatically put on .

This is because there is a comma in the string itself. In order to avoid ambiguity, it is regarded as a delimiter, so the csv module automatically performs special processing.

Simply put on “little ears” for everyone, the parameter quoting=csv.QUOTE_NONNUMERIC will help.

>>> with open(csv_path,'w',newline='') as f:
writer = csv.writer(f,quoting=csv.QUOTE_NONNUMERIC)
for row in data:
writer.writerow(row)

\t\t
40
41
34
42
34
35
>>>
>>>
>>> with open(csv_path) as f:
    print(f.read())

    
"hostname","vendor","model","location"
"sw1","Huawei","5700","Beijing,Xicheng"
"sw2","Huawei","3700","Shanghai"
"sw3","Huawei","9300","Guangzhou,Tianhe"
"sw4","Huawei","9306","Shenzhen"
"sw5","Huawei","12800","Hangzhou"

>>>

As long as it is a string type, everything is enclosed.

2.5 Write CSV (writerows)

In addition to the method writerow, we can also use the method writerows. Let’s adjust our original example.

>>> import csv
>>> data = [['hostname','vendor','model','location'],
['sw1','Huawei','5700','Beijing,Xicheng'],
['sw2','Huawei','3700','Shanghai'],
['sw3','Huawei','9300','Guangzhou,Tianhe'],
['sw4','Huawei','9306','Shenzhen'],
['sw5','Huawei','12800','Hangzhou']]

>>> csv_path = r'E:\……\csv-lab1-writing.csv' #Save the file path, which can be based on your actual situation.
>>> with open(csv_path,'w',newline='') as f:
writer = csv.writer(f,quoting=csv.QUOTE_NONNUMERIC)
writer.writerows(data)

\t
>>> with open(csv_path) as f:
print(f.read())

\t
"hostname","vendor","model","location"
"sw1","Huawei","5700","Beijing,Xicheng"
"sw2","Huawei","3700","Shanghai"
"sw3","Huawei","9300","Guangzhou,Tianhe"
"sw4","Huawei","9306","Shenzhen"
"sw5","Huawei","12800","Hangzhou"

>>>

After the script is executed normally, the file will be regenerated in the experiment folder. Screenshot omitted.

Will it look much simpler? Prove that birows is better? Nonono, these are tools. There are no good or bad tools. Knives and cannons each have their own strengths, and they need to be adapted to specific usage scenarios.

2.6 Writing to CSV (DictWriter)

We can also use the method DictWriter to write dictionary type data directly to the CSV file.

DictWriter operates similarly to writer, but the dictionary type is unordered (to be precise, the dictionary type after Python3.6 is ordered , but within the scope of my application, I have always understood it as unordered). Therefore, we have to introduce another parameter fieldnames to identify the order.

>>> import csv
>>> data = [{
    'hostname': 'sw1',
    'location': 'Beijing,Xicheng',
    'model': '5700',
    'vendor': 'Huawei'
}, {
    'hostname': 'sw2',
    'location': 'Shanghai',
    'model': '3700',
    'vendor': 'Huawei'
}, {
    'hostname': 'sw3',
    'location': 'Guangzhou,Tianhe',
    'model': '9300',
    'vendor': 'Huawei'
}, {
    'hostname': 'sw4',
    'location': 'Shenzhen',
    'model': '9306',
    'vendor': 'Huawei'
}, {
    'hostname': 'sw5',
    'location': 'Hangzhou',
    'model': '12800',
    'vendor': 'Huawei'
}]
>>>
>>> csv_path = r'E:\……\csv-lab1-writing.csv' #Save the file path, which can be based on your actual situation.
>>>
>>> with open(csv_path,'w',newline='') as f:
writer = csv.DictWriter(
f, fieldnames=list(data[0].keys()),quoting=csv.QUOTE_NONNUMERIC)
writer.writeheader()
for d in data:
writer.writerow(d)

\t\t
41
34
42
34
35
>>> with open(csv_path) as f:
print(f.read())

\t
"hostname","location","model","vendor"
"sw1","Beijing,Xicheng","5700","Huawei"
"sw2","Shanghai","3700","Huawei"
"sw3","Guangzhou,Tianhe","9300","Huawei"
"sw4","Shenzhen","9306","Huawei"
"sw5","Hangzhou","12800","Huawei"

>>>

This seems a little convoluted, but we have to point it out a little bit. fieldnames is used to control the order of the headers. For example, the first item is hostname, and the last item is vendor.

2.7 Custom separator (dictwriter)

I have mentioned TSV before, and the delimiter can naturally be other.

At this time, the parameter delimiter is needed to help.

>>> import csv
>>> csv_path = r'E:\***\csv-lab1.csv' #File path, according to your actual situation.
>>> with open(csv_path) as f:
reader = csv.reader(f)
for row in reader:
print(row)

\t\t
['hostname:vendor:model:location']
['sw1:Huawei:5700:Beijing']
['sw2:Huawei:3700:Shanghai']
['sw3:Huawei:9300:Guangzhou']
['sw4:Huawei:9306:Shenzhen']
['sw5:Huawei:12800:Hangzhou']
>>>
>>>
>>> with open(csv_path) as f:
reader = csv.reader(f, delimiter=':')
for row in reader:
print(row)

\t\t
['hostname', 'vendor', 'model', 'location']
['sw1', 'Huawei', '5700', 'Beijing']
['sw2', 'Huawei', '3700', 'Shanghai']
['sw3', 'Huawei', '9300', 'Guangzhou']
['sw4', 'Huawei', '9306', 'Shenzhen']
['sw5', 'Huawei', '12800', 'Hangzhou']
>>>

In this experiment, we use colon as the delimiter, so the parameter delimiter needs to be specified; if not specified, the default delimiter is comma.

3. Summary of this article

CSV is often used as a medium for data export and import in various operation and maintenance application systems, such as alarm information, resource tables, logs, etc.

Python’s csv module is equivalent to an API that allows us to retrieve the data stored in the CSV file on the hard disk through scripts, and then put it into the memory for data processing and processing. After data processing, if you don’t know what form to store it in, then use CSV.