{"id":269,"date":"2025-01-17T10:10:00","date_gmt":"2025-01-17T16:10:00","guid":{"rendered":"https:\/\/douglasstarnes.dev\/?p=269"},"modified":"2025-01-13T15:27:25","modified_gmt":"2025-01-13T21:27:25","slug":"importing-and-exporting-sqlmodel-data-with-a-typer-cli","status":"publish","type":"post","link":"https:\/\/douglasstarnes.dev\/index.php\/2025\/01\/17\/importing-and-exporting-sqlmodel-data-with-a-typer-cli\/","title":{"rendered":"Importing and Exporting SQLModel Data with a Typer CLI"},"content":{"rendered":"\n

In the previous post you saw how to filter and remove data from a database with the SQLModel API. In this post you’ll see how to export the Event <\/code>data to a comma separated value (CSV) file. And you’ll see how to complete the trip by importing CSV data into the database.<\/p>\n\n\n\n

Exporting a CSV<\/h3>\n\n\n\n

Let’s start off with exporting the Events <\/code>to a CSV file. Then we can use the import command as a sanity by importing the exported CSV.<\/p>\n\n\n\n

Create a new command in a function called export_events<\/code>. Adding the command decorator will add an export-events<\/code> command to the CLI. <\/p>\n\n\n\n

<\/circle><\/circle><\/circle><\/g><\/svg><\/span><\/path><\/path><\/svg><\/span>
@<\/span>app<\/span>.<\/span>command<\/span>()<\/span><\/span>\ndef<\/span> <\/span>export_events<\/span>():<\/span><\/span>\n  <\/span>pass<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
The export-events<\/code> command will have a single required argument, the name of the file to store the exported CSV.<\/p>\n\n\n\n
<\/circle><\/circle><\/circle><\/g><\/svg><\/span><\/path><\/path><\/svg><\/span>
@<\/span>app<\/span>.<\/span>command<\/span>()<\/span><\/span>\ndef<\/span> <\/span>export_events<\/span>(<\/span><\/span>\n  <\/span>filename<\/span>:<\/span> <\/span>str<\/span><\/span>\n):<\/span><\/span>\n  <\/span>pass<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
And the export-events<\/code> command will also have an option.  When True<\/code>, the header <\/code>option will include a header row in the exported CSV.  And it will be True <\/code>by default.<\/p>\n\n\n\n
<\/circle><\/circle><\/circle><\/g><\/svg><\/span><\/path><\/path><\/svg><\/span>
@<\/span>app<\/span>.<\/span>command<\/span>()<\/span><\/span>\ndef<\/span> <\/span>export_events<\/span>(<\/span><\/span>\n  <\/span>filename<\/span>:<\/span> <\/span>str<\/span>,<\/span><\/span>\n  <\/span>header<\/span>:<\/span> Annotated<\/span>[<\/span>bool<\/span>,<\/span> typer<\/span>.<\/span>Option<\/span>()]<\/span> <\/span>=<\/span> <\/span>False<\/span><\/span>\n):<\/span><\/span>\n  <\/span>pass<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
You can also include help text for the option with the help <\/code>keyword argument.  In addition, annotating the filename <\/code>parameter with typer.Argument<\/code> lets you add help to the argument.  And adding a docstring lets you include help for the function.<\/p>\n\n\n\n
<\/circle><\/circle><\/circle><\/g><\/svg><\/span><\/path><\/path><\/svg><\/span>
@<\/span>app<\/span>.<\/span>command<\/span>()<\/span><\/span>\ndef<\/span> <\/span>export_events<\/span>(<\/span><\/span>\n  <\/span>filename<\/span>:<\/span> Annotated<\/span>[<\/span>str<\/span>,<\/span> typer<\/span>.<\/span>Argument<\/span>(<\/span><\/span>\n    <\/span>help<\/span>=<\/span>"<\/span>File to store the CSV<\/span>"<\/span><\/span>\n  <\/span>)],<\/span><\/span>\n  <\/span>header<\/span>:<\/span> Annotated<\/span>[<\/span>bool<\/span>,<\/span> typer<\/span>.<\/span>Option<\/span>(<\/span><\/span>\n    <\/span>help<\/span>=<\/span>"<\/span>Include headers in the first row of the CSV<\/span>"<\/span><\/span>\n  <\/span>)]<\/span> <\/span>=<\/span> <\/span>False<\/span><\/span>\n):<\/span><\/span>\n  <\/span>"""<\/span><\/span>\n  Export the Events to a command separated value (CSV) file<\/span><\/span>\n  <\/span>"""<\/span><\/span>\n  <\/span>pass<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
Now take a look at the help for the export-events<\/code> command<\/p>\n\n\n\n
<\/circle><\/circle><\/circle><\/g><\/svg><\/span><\/path><\/path><\/svg><\/span>
$<\/span> <\/span>python<\/span> <\/span>main.py<\/span> <\/span>export-events<\/span> <\/span>--help<\/span><\/span>\n                                                                                      <\/span><\/span>\n <\/span>Usage:<\/span> <\/span>main.py<\/span> <\/span>export-events<\/span> [OPTIONS] FILENAME      <\/span><\/span>\n <\/span><\/span>\n <\/span>Export<\/span> <\/span>the<\/span> <\/span>Events<\/span> <\/span>to<\/span> <\/span>a<\/span> <\/span>command<\/span> <\/span>separated<\/span> <\/span>value<\/span> (CSV) file                                 <\/span><\/span>\n                                                                                      <\/span><\/span>\n\u256d\u2500<\/span> <\/span>Arguments<\/span> <\/span>\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u256e<\/span><\/span>\n\u2502<\/span> <\/span>*<\/span>    <\/span>filename<\/span>  <\/span>TEXT<\/span>  <\/span>File<\/span> <\/span>to<\/span> <\/span>store<\/span> <\/span>the<\/span> <\/span>CSV<\/span> [default: <\/span>None]<\/span> [required]   \u2502<\/span><\/span>\n\u2570\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u256f<\/span><\/span>\n\u256d\u2500Options\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u256e<\/span><\/span>\n\u2502<\/span> <\/span>--header<\/span>    <\/span>--no-header<\/span>      <\/span>Include<\/span> <\/span>headers<\/span> <\/span>in<\/span> <\/span>the<\/span> <\/span>first<\/span> <\/span>row<\/span> <\/span>of<\/span> <\/span>the<\/span> <\/span>CSV\u2502<\/span><\/span>\n\u2502<\/span>                              [default: <\/span>no-header]<\/span>                       <\/span>\u2502<\/span><\/span>\n\u2502<\/span> <\/span>--help<\/span>                       <\/span>Show<\/span> <\/span>this<\/span> <\/span>message<\/span> <\/span>and<\/span> <\/span>exit.<\/span>                <\/span>\u2502<\/span><\/span>\n\u2570\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u256f<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
The help strings now appear in the help for the command, arguments, and options.<\/p>\n\n\n\n
We’re going to use the csv <\/code>module from the Python standard library to export the events.  Import it at the top of the file.<\/p>\n\n\n\n
<\/circle><\/circle><\/circle><\/g><\/svg><\/span><\/path><\/path><\/svg><\/span>
import<\/span> csv<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
Next, in the export_events <\/code>function, open <\/code>a file using the filename <\/code>argument.<\/p>\n\n\n\n
<\/circle><\/circle><\/circle><\/g><\/svg><\/span><\/path><\/path><\/svg><\/span>
with<\/span> <\/span>open<\/span>(<\/span>filename<\/span>,<\/span> <\/span>"<\/span>w<\/span>"<\/span>)<\/span> <\/span>as<\/span> f<\/span>:<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
Use the file handle to create a new DictWriter<\/code>, found in the csv <\/code>module.  The DictWriter <\/code>also takes the names of the column headers in the fieldnames <\/code>keyword argument.<\/p>\n\n\n\n
<\/circle><\/circle><\/circle><\/g><\/svg><\/span><\/path><\/path><\/svg><\/span>
with<\/span> <\/span>open<\/span>(<\/span>filename<\/span>,<\/span> <\/span>"<\/span>w<\/span>"<\/span>)<\/span> <\/span>as<\/span> f<\/span>:<\/span><\/span>\n  writer <\/span>=<\/span> csv<\/span>.<\/span>DictWriter<\/span>(<\/span>f<\/span>,<\/span> <\/span>fieldnames<\/span>=<\/span>[<\/span>"<\/span>title<\/span>"<\/span>,<\/span> <\/span>"<\/span>timestamp<\/span>"<\/span>,<\/span> <\/span>"<\/span>private<\/span>"<\/span>])<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
If the header <\/code>option is True<\/code>, write the header row.<\/p>\n\n\n\n
<\/circle><\/circle><\/circle><\/g><\/svg><\/span><\/path><\/path><\/svg><\/span>
with<\/span> <\/span>open<\/span>(<\/span>filename<\/span>,<\/span> <\/span>"<\/span>w<\/span>"<\/span>)<\/span> <\/span>as<\/span> f<\/span>:<\/span><\/span>\n  writer <\/span>=<\/span> csv<\/span>.<\/span>DictWriter<\/span>(<\/span>f<\/span>,<\/span> <\/span>fieldnames<\/span>=<\/span>[<\/span>"<\/span>title<\/span>"<\/span>,<\/span> <\/span>"<\/span>timestamp<\/span>"<\/span>,<\/span> <\/span>"<\/span>private<\/span>"<\/span>])<\/span><\/span>\n  <\/span>if<\/span> header<\/span>:<\/span><\/span>\n    writer<\/span>.<\/span>writeheader<\/span>()<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
Get a SQLModel session <\/code>and select <\/code>all of the Events <\/code>in the database.<\/p>\n\n\n\n
<\/circle><\/circle><\/circle><\/g><\/svg><\/span><\/path><\/path><\/svg><\/span>
with<\/span> <\/span>open<\/span>(<\/span>filename<\/span>,<\/span> <\/span>"<\/span>w<\/span>"<\/span>)<\/span> <\/span>as<\/span> f<\/span>:<\/span><\/span>\n  writer <\/span>=<\/span> csv<\/span>.<\/span>DictWriter<\/span>(<\/span>f<\/span>,<\/span> <\/span>fieldnames<\/span>=<\/span>[<\/span>"<\/span>title<\/span>"<\/span>,<\/span> <\/span>"<\/span>timestamp<\/span>"<\/span>,<\/span> <\/span>"<\/span>private<\/span>"<\/span>])<\/span><\/span>\n  <\/span>if<\/span> header<\/span>:<\/span><\/span>\n    writer<\/span>.<\/span>writeheader<\/span>()<\/span><\/span>\n    <\/span>with<\/span> <\/span>Session<\/span>(<\/span>engine<\/span>)<\/span> <\/span>as<\/span> session<\/span>:<\/span><\/span>\n      events <\/span>=<\/span> session<\/span>.<\/span>exec<\/span>(<\/span><\/span>\n        <\/span>select<\/span>(<\/span>Event<\/span>).<\/span>order_by<\/span>(<\/span>Event<\/span>.<\/span>timestamp<\/span>.<\/span>asc<\/span>())<\/span><\/span>\n      <\/span>).<\/span>all<\/span>()<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
Iterate over the events<\/code>.  For each one, create a dictionary with the title, timestamp and private columns.  The timestamp will need to be formatted in a string, and the boolean will be stored as an integer.  The dictionary is the value to the writerow <\/code>method.<\/p>\n\n\n\n
<\/circle><\/circle><\/circle><\/g><\/svg><\/span><\/path><\/path><\/svg><\/span>
with<\/span> <\/span>open<\/span>(<\/span>filename<\/span>,<\/span> <\/span>"<\/span>w<\/span>"<\/span>)<\/span> <\/span>as<\/span> f<\/span>:<\/span><\/span>\n  writer <\/span>=<\/span> csv<\/span>.<\/span>DictWriter<\/span>(<\/span>f<\/span>,<\/span> <\/span>fieldnames<\/span>=<\/span>[<\/span>"<\/span>title<\/span>"<\/span>,<\/span> <\/span>"<\/span>timestamp<\/span>"<\/span>,<\/span> <\/span>"<\/span>private<\/span>"<\/span>])<\/span><\/span>\n  <\/span>if<\/span> header<\/span>:<\/span><\/span>\n    writer<\/span>.<\/span>writeheader<\/span>()<\/span><\/span>\n    <\/span>with<\/span> <\/span>Session<\/span>(<\/span>engine<\/span>)<\/span> <\/span>as<\/span> session<\/span>:<\/span><\/span>\n      events <\/span>=<\/span> session<\/span>.<\/span>exec<\/span>(<\/span><\/span>\n        <\/span>select<\/span>(<\/span>Event<\/span>).<\/span>order_by<\/span>(<\/span>Event<\/span>.<\/span>timestamp<\/span>.<\/span>asc<\/span>())<\/span><\/span>\n      <\/span>).<\/span>all<\/span>()<\/span><\/span>\n      <\/span>for<\/span> event <\/span>in<\/span> events<\/span>:<\/span><\/span>\n        writer<\/span>.<\/span>writerow<\/span>(<\/span><\/span>\n          <\/span>{<\/span><\/span>\n            <\/span>"<\/span>title<\/span>"<\/span>:<\/span> event<\/span>.<\/span>title<\/span>,<\/span><\/span>\n            <\/span>"<\/span>timestamp<\/span>"<\/span>:<\/span> event<\/span>.<\/span>timestamp<\/span>.<\/span>strftime<\/span>(<\/span>"<\/span>%Y-%m-<\/span>%s<\/span>"<\/span>),<\/span><\/span>\n            <\/span>"<\/span>private<\/span>"<\/span>:<\/span> <\/span>1<\/span> <\/span>if<\/span> event<\/span>.<\/span>private <\/span>else<\/span> <\/span>0<\/span><\/span>\n          <\/span>}<\/span><\/span>\n        <\/span>)<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
The file is now written and we can display a confirmation to the user.  It would be nice to show how many rows were exported.  Using the count <\/code>function in the SQLAlchemy func <\/code>module, we can get that number.  And then use it in the message.<\/p>\n\n\n\n
<\/circle><\/circle><\/circle><\/g><\/svg><\/span><\/path><\/path><\/svg><\/span>
from<\/span> sqlalchemy <\/span>import<\/span> func<\/span><\/span>\n<\/span>\n...<\/span><\/span>\n<\/span>\nwith<\/span> <\/span>open<\/span>(<\/span>filename<\/span>,<\/span> <\/span>"<\/span>w<\/span>"<\/span>)<\/span> <\/span>as<\/span> f<\/span>:<\/span><\/span>\n  writer <\/span>=<\/span> csv<\/span>.<\/span>DictWriter<\/span>(<\/span>f<\/span>,<\/span> <\/span>fieldnames<\/span>=<\/span>[<\/span>"<\/span>title<\/span>"<\/span>,<\/span> <\/span>"<\/span>timestamp<\/span>"<\/span>,<\/span> <\/span>"<\/span>private<\/span>"<\/span>])<\/span><\/span>\n  <\/span>if<\/span> header<\/span>:<\/span><\/span>\n    writer<\/span>.<\/span>writeheader<\/span>()<\/span><\/span>\n    <\/span>with<\/span> <\/span>Session<\/span>(<\/span>engine<\/span>)<\/span> <\/span>as<\/span> session<\/span>:<\/span><\/span>\n      events <\/span>=<\/span> session<\/span>.<\/span>exec<\/span>(<\/span><\/span>\n        <\/span>select<\/span>(<\/span>Event<\/span>).<\/span>order_by<\/span>(<\/span>Event<\/span>.<\/span>timestamp<\/span>.<\/span>asc<\/span>())<\/span><\/span>\n      <\/span>).<\/span>all<\/span>()<\/span><\/span>\n      <\/span>for<\/span> event <\/span>in<\/span> events<\/span>:<\/span><\/span>\n        writer<\/span>.<\/span>writerow<\/span>(<\/span><\/span>\n          <\/span>{<\/span><\/span>\n            <\/span>"<\/span>title<\/span>"<\/span>:<\/span> event<\/span>.<\/span>title<\/span>,<\/span><\/span>\n            <\/span>"<\/span>timestamp<\/span>"<\/span>:<\/span> event<\/span>.<\/span>timestamp<\/span>.<\/span>strftime<\/span>(<\/span>"<\/span>%Y-%m-<\/span>%s<\/span>"<\/span>),<\/span><\/span>\n            <\/span>"<\/span>private<\/span>"<\/span>:<\/span> <\/span>1<\/span> <\/span>if<\/span> event<\/span>.<\/span>private <\/span>else<\/span> <\/span>0<\/span><\/span>\n          <\/span>}<\/span><\/span>\n        <\/span>)<\/span><\/span>\n      event_count <\/span>=<\/span> session<\/span>.<\/span>exec<\/span>(<\/span><\/span>\n        <\/span>select<\/span>(<\/span>func<\/span>.<\/span>count<\/span>(<\/span>Event<\/span>.<\/span>id<\/span>))<\/span><\/span>\n      <\/span>).<\/span>one<\/span>()<\/span><\/span>\n      console<\/span>.<\/span>print<\/span>(<\/span>f<\/span>"Exported <\/span>{<\/span>event_count<\/span>}<\/span> row(s)"<\/span>,<\/span> <\/span>style<\/span>=<\/span>app_styles<\/span>.<\/span>success<\/span>)<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
Try it out!  Suppose we have these events in the database.<\/p>\n\n\n\n
Title<\/strong><\/td>Timestamp<\/strong><\/td>Private<\/strong><\/td><\/tr>
Past Event<\/td>January 1, 2025<\/td>False<\/td><\/tr>
New Event<\/td>February 2, 2025<\/td>False<\/td><\/tr>
Private Event<\/td>March 3, 2025<\/td>True<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
Using the export-events<\/code> command, export the events to a file named events.csv <\/strong>with a header.<\/p>\n\n\n\n
<\/circle><\/circle><\/circle><\/g><\/svg><\/span><\/path><\/path><\/svg><\/span>
$<\/span> <\/span>python<\/span> <\/span>main.py<\/span> <\/span>export-events<\/span> <\/span>--header<\/span><\/span>\nExported<\/span> <\/span>3<\/span> <\/span>row<\/span>(<\/span>s<\/span>)<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
In the same directory as main.py<\/strong>, open the new events.csv<\/strong> file.<\/p>\n\n\n\n
title,timestamp,private
Past Event,2025-01-01,0
New Event,2025-02-02,0
Private Event,2025-03-03,1<\/pre>\n\n\n\n
Importing a CSV<\/h3>\n\n\n\n
The import command will use the csv <\/code>module as well.  But instead of the DictWriter<\/code>, we will use the DictReader <\/code>that reads a CSV file and returns it in a dictionary-like structure.<\/p>\n\n\n\n
Create the import_events <\/code>function.  Decorate it with the command <\/code>decorator.  And add the filename <\/code>argument and header <\/code>option similar to the export_events <\/code>function.<\/p>\n\n\n\n
<\/circle><\/circle><\/circle><\/g><\/svg><\/span><\/path><\/path><\/svg><\/span>
@<\/span>app<\/span>.<\/span>command<\/span>()<\/span><\/span>\ndef<\/span> <\/span>import_events<\/span>(<\/span><\/span>\n  <\/span>filename<\/span>:<\/span> Annotated<\/span>[<\/span>str<\/span>,<\/span> typer<\/span>.<\/span>Argument<\/span>()],<\/span><\/span>\n  <\/span>header<\/span>:<\/span> Annotated<\/span>[<\/span>bool<\/span>,<\/span> typer<\/span>.<\/span>Option<\/span>()]<\/span> <\/span>=<\/span> <\/span>True<\/span><\/span>\n):<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
Open the filename for reader in a with block.  Use the file handle to create a new DictReader <\/code>from the csv <\/code>module.<\/p>\n\n\n\n
<\/circle><\/circle><\/circle><\/g><\/svg><\/span><\/path><\/path><\/svg><\/span>
with<\/span> <\/span>open<\/span>(<\/span>filename<\/span>,<\/span> <\/span>"<\/span>r<\/span>"<\/span>)<\/span> <\/span>as<\/span> f<\/span>:<\/span><\/span>\n  reader <\/span>=<\/span> csv<\/span>.<\/span>DictReader<\/span>(<\/span>f<\/span>)<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
If the header <\/code>option is True<\/code>, set the fieldnames <\/code>on the reader<\/code>.  If there are no fieldnames<\/code>, the reader <\/code>will process every row, otherwise it will assume column headers are in the first row.<\/p>\n\n\n\n
<\/circle><\/circle><\/circle><\/g><\/svg><\/span><\/path><\/path><\/svg><\/span>
with<\/span> <\/span>open<\/span>(<\/span>filename<\/span>,<\/span> <\/span>"<\/span>r<\/span>"<\/span>)<\/span> <\/span>as<\/span> f<\/span>:<\/span><\/span>\n  reader <\/span>=<\/span> csv<\/span>.<\/span>DictReader<\/span>(<\/span>f<\/span>)<\/span><\/span>\n  <\/span>if<\/span> header<\/span>:<\/span><\/span>\n    reader<\/span>.<\/span>fieldnames <\/span>=<\/span> <\/span>[<\/span>"<\/span>title<\/span>"<\/span>,<\/span> <\/span>"<\/span>timestamp<\/span>"<\/span>,<\/span> <\/span>"<\/span>private<\/span>"<\/span>]<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
Next, iterate over the reader<\/code>.  Each row will be returned as a dictionary.  Create an Event <\/code>for each row.  The timestamp <\/code>will need to be parsed into a Python datetime.date<\/code> and private <\/code>will need to be turned into a boolean.<\/p>\n\n\n\n
<\/circle><\/circle><\/circle><\/g><\/svg><\/span><\/path><\/path><\/svg><\/span>
with<\/span> <\/span>open<\/span>(<\/span>filename<\/span>,<\/span> <\/span>"<\/span>r<\/span>"<\/span>)<\/span> <\/span>as<\/span> f<\/span>:<\/span><\/span>\n  reader <\/span>=<\/span> csv<\/span>.<\/span>DictReader<\/span>(<\/span>f<\/span>)<\/span><\/span>\n  <\/span>if<\/span> header<\/span>:<\/span><\/span>\n    reader<\/span>.<\/span>fieldnames <\/span>=<\/span> <\/span>[<\/span>"<\/span>title<\/span>"<\/span>,<\/span> <\/span>"<\/span>timestamp<\/span>"<\/span>,<\/span> <\/span>"<\/span>private<\/span>"<\/span>]<\/span><\/span>\n  events <\/span>=<\/span> <\/span>[<\/span><\/span>\n    <\/span>Event<\/span>(<\/span><\/span>\n      <\/span>title<\/span>=<\/span>event<\/span>[<\/span>"<\/span>title<\/span>"<\/span>],<\/span><\/span>\n      <\/span>timestamp<\/span>=<\/span>datetime<\/span>.<\/span>datetime<\/span>.<\/span>strptime<\/span>(<\/span><\/span>\n        event<\/span>[<\/span>"<\/span>timestamp<\/span>"<\/span>],<\/span> <\/span>"<\/span>%Y-%m-<\/span>%d<\/span>"<\/span><\/span>\n      <\/span>).<\/span>date<\/span>(),<\/span><\/span>\n      <\/span>private<\/span>=False<\/span> <\/span>if<\/span> <\/span>int<\/span>(<\/span>event<\/span>[<\/span>"<\/span>private<\/span>"<\/span>])<\/span> <\/span>==<\/span> <\/span>0<\/span> <\/span>else<\/span> <\/span>True<\/span><\/span>\n    <\/span>)<\/span><\/span>\n    <\/span>for<\/span> event <\/span>in<\/span> reader<\/span><\/span>\n  <\/span>]<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
Create a new SQLModel session <\/code>and add the events <\/code>list with the add_all <\/code>method.  Then commit <\/code>the changes.  And show the user a  confirmation with the number of events imported.<\/p>\n\n\n\n
<\/circle><\/circle><\/circle><\/g><\/svg><\/span><\/path><\/path><\/svg><\/span>
with<\/span> <\/span>Session<\/span>(<\/span>engine<\/span>)<\/span> <\/span>as<\/span> session<\/span>:<\/span><\/span>\n  session<\/span>.<\/span>add_all<\/span>(<\/span>events<\/span>)<\/span><\/span>\n  session<\/span>.<\/span>commit<\/span>()<\/span><\/span>\nconsole<\/span>.<\/span>print<\/span>(<\/span>f<\/span>"Imported <\/span>{<\/span>len<\/span>(<\/span>events<\/span>)<\/span>}<\/span> event(s)"<\/span>,<\/span> <\/span>style<\/span>=<\/span>app_styles<\/span>.<\/span>success<\/span>)<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
Let’s try it out.  First, delete the events.db<\/strong> file.  Then run the import-events<\/code> command.<\/p>\n\n\n\n
<\/circle><\/circle><\/circle><\/g><\/svg><\/span><\/path><\/path><\/svg><\/span>
$<\/span> <\/span>python<\/span> <\/span>main.py<\/span> <\/span>import-events<\/span> <\/span>events.csv<\/span> <\/span>--header<\/span><\/span>\nImported<\/span> <\/span>3<\/span> <\/span>events<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
The events.db<\/strong> file will be created.  Look inside to see the data.  Again, if you are using Visual Studio Code you can use the SQLite Explorer extension.<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
Summary<\/h3>\n\n\n\n
In this post you saw how to use the csv <\/code>module in the Python standard library, along with SQLModel, to import and export data to and from comma separated value (CSV) files.  You also saw how to add help strings to options, arguments and commands in a Typer CLI.<\/p>\n\n\n\n
<\/p>\n","protected":false},"excerpt":{"rendered":"
In the previous post you saw how to filter and remove data from a database…<\/p>\n","protected":false},"author":1,"featured_media":275,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[18],"tags":[31,4,30,33,32,28,24],"class_list":["post-269","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-python","tag-cli","tag-python","tag-rich","tag-sqlite","tag-sqlmodel","tag-typer","tag-vscode"],"_links":{"self":[{"href":"https:\/\/douglasstarnes.dev\/index.php\/wp-json\/wp\/v2\/posts\/269","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/douglasstarnes.dev\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/douglasstarnes.dev\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/douglasstarnes.dev\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/douglasstarnes.dev\/index.php\/wp-json\/wp\/v2\/comments?post=269"}],"version-history":[{"count":1,"href":"https:\/\/douglasstarnes.dev\/index.php\/wp-json\/wp\/v2\/posts\/269\/revisions"}],"predecessor-version":[{"id":276,"href":"https:\/\/douglasstarnes.dev\/index.php\/wp-json\/wp\/v2\/posts\/269\/revisions\/276"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/douglasstarnes.dev\/index.php\/wp-json\/wp\/v2\/media\/275"}],"wp:attachment":[{"href":"https:\/\/douglasstarnes.dev\/index.php\/wp-json\/wp\/v2\/media?parent=269"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/douglasstarnes.dev\/index.php\/wp-json\/wp\/v2\/categories?post=269"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/douglasstarnes.dev\/index.php\/wp-json\/wp\/v2\/tags?post=269"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}