Sqlite 3 insert row python1/3/2024 The ? is a placemarker - one placemarker for each field value. To protect against this, use parametrized SQL: cursor.execute("INSERT INTO table VALUES(?,?,?,?,?)", args) A maliciously constructed CSV file could take advantage of this line to run arbitrary SQL comands (see SQL injection and Little Bobby Tables). Then call cursor.executemany("INSERT INTO table VALUES(?,?,?,?,?)", manyargs)īy the way, constructing SQL commands with string formatting such as: cursor.execute("INSERT INTO table VALUES(" +values+ ") ")Ĭan be a security risk. To improve the performance, read a chunk of (say, 1000) lines at a time, and accumulate the values to be inserted into a list of lists, manyargs. If you have a huge CSV file, calling cursor.execute once per line can be slow. The above will work for small to moderate-sized CSV. Sql = "INSERT INTO table VALUES ()".format(', '.join(*len(args))) If it works, process the line, if not, continue through the loop: import datetime as DTĭate = DT.datetime.strptime(row, '%m/%d/%Y') Try converting the first item in row into a Python datetime.datetime object. Values = logID + str(row) + str(row) + str(row) + str(row)Ĭursor.execute("INSERT INTO table VALUES(" +values+ ") ") Reader = csv.reader(csvfile, dialect='excel') #code above here opens the database and creates a cursor So far I've got the code which reads in the data and then puts it into a table along with a unique identifier (logID), but at the moment I'm just reading from line 4 onwards - I'd like a more general solution for the case when a file doesn't have the 3 leading rows of blurb. So I want to just import the 4th and 5th rows (and all subsequent rows with a date), but skip the first 3 which describe the file and column headers. The data files I've got tend to have an irregular format at the beginning, but the rows I want will always begin with a date field (dd/mm/yy) and time field (hh:mm:ss).Įxample file is below: Hello I am a file,, , , I'm fairly new to importing data this way and to sqlite3 in general. I am trying to import csv files into a sqlite3 database which I'm writing using python.
0 Comments
Leave a Reply.AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |