Upload A CSV File (Or Any Data File) To SQLite Using Python

hey awesome guys in this video I'm going to show you how to load a CSV data file into a sqlite database using pandas right so this is a technique that I use whenever I need to upload a data file into a sqlite database and this method you're not limited to just CSC files you can load a text file a Json file or even load tables from a different database right so if you have a cc file and the file name is going to be data.csv now if I go into my project folder here let me go ahead and delete this database file I'm going to create a new one from scratch all right so in my python script I want to first of all import the sqlite database module which is going to be sqlite3 then I'm going to import penis module now I want to read the system file so from pd.read C3 and the file name is going to be data.csv all right so it's going to be step one low data file now Step 2 is optional in case if you need to uh do some data cleanup and this is going to be we're going to do the data transformation data cleaner or other operations and I'll say I want to make sure that my headers does not contains any extra space from both ends so I can reference The Columns attribute in a reference to string down to stripe the empty spaces and I need to replace the accounts all right so this is going to be data clean up now step two is going to be creating the database or connecting to a sqlite database so it's going to be clear or connect to a SQL line database Now using sqlite3 module it's going to be sqlite3.let angel can either create or connect to a database and because I don't have a database here so I'm going to create the database first now name the database demo.db and I'll name the outputs connection and once I establish the database connection so I can graph the data frame object and this is a method code to SQL method so this is one of the most useful methods in my opinion so these two SQL method will write records storing your diaphragm to a SQL database so it doesn't matter if I will commit SQL live SQL Server Oracle or even bigquery because this method to work with to support almost every single database system and because my objective here is simply upload this data set to my sqlite database so it's going to be step 4. low data file to sqli so inside the to SQL method the first parameter is going to be the table name now let's say I want to name the table as housing developments and followed by the database connection so we know we create the connection here in line 11. so past the connection option now if you want to insert another parameter code if exists right so from this if axis parameter we have two options we can either set the value to fail to place open if we set the if access value to fill then it's going to raise an exception if a table that has the same name of the access first replace is going to replace the existing table and pin is going to basically add the records to the existing table all right so if you want to set the if axis value to place so basically if I'm trying to upload a data set that has the same if I'm assigning the same table name then I simply just want to place the table rackers now I can close the connection because I'm done with the data upload right so uh hear me actually I'm create a database here now let me go ahead and run this code block now go ahead and create the demo DB file now what I want to do here is I want to make sure that this DB file does not have any table all right so we can see that the sqlite database is completely empty now if I run line 15 and it's going to upload my 561 records um this is a data.csv file and need to close the connection right so if I go back and I'll drop the database file again now this time I can see that I now have a table called housing developments and this table has 561 rows of Records so there's going to be every someone come to cover in this video and hopefully you guys find these videos for and feel free to post your question or feedback in the comment section below and don't forget to subscribe and I'll see you guys next time bye

Channel: Jie Jenn

Views: 21,799

Keywords: sqlite, sqlite3, database, python

Id: UZIhVmkrAEs

Length: 5min 44sec (344 seconds)

Published: Tue Dec 06 2022