- IMPORTING CSV INTO SQLITESTUDIO HOW TO
- IMPORTING CSV INTO SQLITESTUDIO CODE
- IMPORTING CSV INTO SQLITESTUDIO DOWNLOAD
After skipping the headers the function iterates through the reader object line by line and adds the content to a list and at the end it returns a list with our csv data. One trick here is the next(reader) line which is skipping the first row (if your csv file does not have headers you do not need this line). The function uses the built in csv module (do not forget to import). This is what the open_csv_file function does. Then opening the csv file and loading the data to a list variable excluding the csv file headers. If the table does not exist, the program creates it for us:
Insert dataĬouple of things happen here: CSV file opening, header row skipping and read the csv content to a listĪt the beginning connecting to the database, check if the referred table exists. Insert_values_to_table('imdb_temp', CSV_FILE_PATH)Īctually there is no change in the connection procedure or logic, it is the same as in the Python SQLite: INSERT data | simple example post. Return ', '.join(column_names), column_count Table_column_names = sql_cursor.fetchall() Table_column_names = 'PRAGMA table_info(' + table_name + ') ' :return: a comma separated string with column names, an integer with number of columns :param table_name: table name to get the column names from Scrape the column names from a database table to a list and convert to a comma separated string, count the number With open(csv_file_path, 'r', encoding='utf-8') as csv_file:ĭef get_column_names_from_db_table(sql_cursor, table_name): Open and read data from a csv file without headers (skipping the first row) Sql_query = 'INSERT INTO ' + table_name + '(' + column_names + ') VALUES (' + values_str + ')'Ĭ.executemany(sql_query, values_to_insert) Values_to_insert = open_csv_file(csv_file_path)Ĭolumn_names, column_numbers = get_column_names_from_db_table(c, table_name) :param csv_file_path: path of the csv file to processĬ.execute('CREATE TABLE IF NOT EXISTS ' + table_name + :param table_name: table name in the database to insert the data into
Open a csv file, store its content in a list excluding header and insert the data from the list to db table :param db_file: absolute or relative path of db fileĭef insert_values_to_table(table_name, csv_file_path): You can use the below SQL query to create the imdb_temp table:ĬSV_FILE_PATH = '.\\Sample_files\\IMDB-Movie-Data.csv'Ĭonnect to an SQlite database, if db file does not exist it will be created I just directly created the table in the database (by the way I am using SQLiteStudio).
IMPORTING CSV INTO SQLITESTUDIO CODE
csv file to a list without the headersįeel free to create new database or table or rename the file, but in the example code I am using these.
IMPORTING CSV INTO SQLITESTUDIO DOWNLOAD
I used an IMDB data-set, you can download it from or you can find it in the github repo (Sample_files folder), it is not too heavy (only 1.000 rows) and it is easy to overview the data structure. Let’s see how you can insert data from csv files to an SQLite database. csv file format is still popular to send, export data.
Let’s continue with a more realistic exercise, but bear in mind this example still rather simplified.
IMPORTING CSV INTO SQLITESTUDIO HOW TO
In the previous post I showed a very very very simple example how to insert data to an SQLite database.
0 kommentar(er)
