![]() The following command will make a bunch of changes to the table structure in one go: sqlite-utils transform manatees.db locations \ The sqlite-utils transform command can be used to apply transformations to a table - rename columns, dropping columns, assigning primary keys and more. This would make a good primary key for our table. The X and Y columns are duplicates of the latitude and longitude.įinally, the FIELDID column looks to be a unique identifier for every row in the database. The created_user, last_edited_user and STATE columns aren't interesting: they always store the same value. Renaming those columns to latitude and longitude will make that more obvious, and will also let us visualize the data using a Datasette plugin in a moment. The LAT and LONG_ column look like they might be latitudes and longitudes. Transforming the columnsĮxploring the data helps identify improvements we can make to the way it is structured. Then navigate to to start exploring your data. Install that, ( brew install datasette works for Homebrew users) and run it against your database: datasette manatees.db ![]() "select REPDATE, MORTALITY, COUNTY from locations limit 10"ģ 00:00:00 00 Undetermined: Other Browardħ 00:00:00 00 Human Related: Watercraft Collision Miami-DadeĠ 00:00:00 00 Human Related: Watercraft Collision Miami-Dadeģ 00:00:00 00 Human Related: Other Volusiaġ 00:00:00 00 Human Related: Other Brevardĥ 00:00:00 00 Human Related: Watercraft Collision Citrusħ 00:00:00 00 Undetermined: Too Decomposed Indian Riverġ 00:00:00 00 Verified: Not Necropsied Brevardĩ 00:00:00 00 Human Related: Watercraft Collision Miami-Dadeĭatasette provides a browser-based interface for exploring a SQLite database. Let's specify some columns, limit to 10 and add -t to output the results as a table. We can use the sqlite-utils tables command to see those tables: sqlite-utils tables manatees.db -counts The columns have the appropriate types thanks to the -d option we passed to sqlite-utils insert. The locations table here has been created with columns that match the columns that were present in the CSV file. The output of that command looks like this: CREATE TABLE "locations" ( Having created the database, we can use the schema command to view its schema: sqlite-utils schema manatees.db With this option columns that have numeric data in will be used to populate numeric columns. Without this, every column will be treated as text. The -d option tells the tool to automatically detect the types of data.-csv tells the tool to treat the incoming data as CSV data.Manatee_Carcass_Recovery_Locations_in_Florida.csv is the CSV file we are importing. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |