Python sqlite insert datetime

How to insert datetime into sqlite3 database using Python?

Solution: You could use :- The column type is basically irrelevant as in SQLite you can store any type of value in any type of column. With the exception of the special rowid column or an alias of the rowid column (if the column is defined using then it is an alias of the rowid column (or with the AUTOINCREMENT keyword)).

How to insert datetime into sqlite3 database using Python?

I would like to insert datetime into sqlite using Python.

I tried using Python’s datetime and strftime but it didn’t work.

Test’s time field has the type of Numeric (Datetime).

from datetime import datetime as dt rows = db.execute( 'INSERT INTO test (time) VALUES (:time)', time=dt.now().strftime('%Y-%m-%d %H:%M:%S')) 

RuntimeError: (sqlite3.OperationalError) near «‘2019-05-19 17:14:25′»: syntax error [SQL: INSERT INTO test (time) VALUES (‘2019-05-19 17:14:25’)]

rows = db.execute("INSERT INTO test (time) VALUES (datetime('now'))") 

The column type is basically irrelevant as in SQLite you can store any type of value in any type of column.

With the exception of the special rowid column or an alias of the rowid column (if the column is defined using INTEGER PRIMARY KEY then it is an alias of the rowid column (or with the AUTOINCREMENT keyword)). An alias of the rowid column must be an integer up to 64 bit signed.

Working Example

import sqlite3 drop_sql = "DROP TABLE IF EXISTS test" crt_sql = "CREATE TABLE IF NOT EXISTS test (time NUMERIC, time2 TEXT, time3 BLOB, time4 REAL, time5 INTEGER )" db = sqlite3.connect("test.db") rows = db.execute(drop_sql) rows = db.execute(crt_sql) rows = db.execute("INSERT INTO test VALUES(datetime('now'),datetime('now'),datetime('now'),datetime('now'),datetime('now'))") cursor = db.cursor() cursor.execute("SELECT * FROM test") for row in cursor: print("Time is " + row[0], "\nTime2 is " + row[1],"\nTime3 is " + row[2], "\nTime4 is " + row[3],"\nTime5 is " + row[4]) db.commit() db.close() 

Result :-

Time is 2019-07-13 08:59:28 Time2 is 2019-07-13 08:59:28 Time3 is 2019-07-13 08:59:28 Time4 is 2019-07-13 08:59:28 Time5 is 2019-07-13 08:59:28 

How to insert specific json into sqlite database using, The sqlite3 module supports two kinds of placeholders: question marks (qmark style) and named placeholders (named style). Note that you can even insert all rows at once using executemany. So in your case: curs.executemany (‘INSERT INTO data (loanId, noteAmount) ‘ ‘VALUES (:loanId,:noteAmount)’, json.loads () [‘myNotes’]) Share. Code samplecurs.executemany(‘INSERT INTO data (loanId, noteAmount) »VALUES (:loanId,:noteAmount)’, json.loads()[‘myNotes’])Feedback

How to insert image into sqlite3 database?

import sqlite3 import tkinter import time import PIL.Image, PIL.ImageTk from PIL import Image,ImageTk import cv2 import numpy as np from tkinter import Tk, Label, Button, Entry, Toplevel r=Tk() conn = sqlite3.connect('datastorage.db') print("Opened database successfully"); def snapshot(self): # Get a frame from the video source ret, frame = self.vid.get_frame() 

I am capturing a frame from video and I need to insert it into a database which contains a text column and a blob(Binary Large Object) column. There are other similar questions which suggest converting to string and storing but since I already have images in blob format stored and I am extracting them using decode as seen in the code below, I need to store blob only.

blob_data=row[1] nparr = np.frombuffer(blob_data, np.uint8) img_np = cv2.imdecode(nparr, cv2.IMREAD_COLOR) image1=cv2.resize(img_np,(260,200)) #cv2.imshow("data",image1) #break #Rearrang the color channel b,g,r = cv2.split(image1) image1 = cv2.merge((r,g,b)) hsv1=cv2.cvtColor(image1, cv2.COLOR_RGB2HSV) kernel2 = np.ones((3,3),np.uint8) 

I tried using the following query:

cursor=conn.execute("create table if not exists user_6 (id text, img blob)") cursor=conn.execute("insert into user_6 values (. )",(ins,sqlite3.Binary(frame))) 

but I am unable to display it using the same method I used to display all the other entries. The code used to display is the 2nd code block. I am encountering an error as shown:

Exception in Tkinter callback Traceback (most recent call last): File "C:\Users\ABC\AppData\Local\Programs\Python\Python37\lib\tkinter\__init__.py", line 1705, in __call__ return self.func(*args) File "C:\Users\ABC\Desktop\Python tut\gui databse.py", line 71, in display image1=cv2.resize(img_np,(130,100)) cv2.error: OpenCV(4.2.0) C:\projects\opencv-python\opencv\modules\imgproc\src\resize.cpp:4045: error: (-215:Assertion failed) !ssize.empty() in function 'cv::resize' 

I was able to do it using the following code:

img_str = cv2.imencode('.jpg', frame)[1].tostring() cursor=conn.execute("create table if not exists user_6 (id text, img blob)") cursor=conn.execute("insert into user_6 values (. )",(ins,img_str)) conn.commit() 

Although the colors in the image are different from the captured image.

Python SQLite, In the program, we first create a table named STUDENT and then insert values into it using the 1st syntax of the INSERT query. Finally, we display the content of the table and commit it to the database. Python3 import sqlite3 conn = sqlite3.connect (‘geeks2.db’) cursor = conn.cursor ()

How to separate everything inside my sqlite database

i have a database which has two thing in my database, name of a fruit and how long is it in the freezer.

cur1.execute("""SELECT fruit_name, freeze_time, COUNT (*) AS total_fruit FROM fruit_page GROUP BY fruit_name, freeze_days""") rows = cur1.fetchall() 

rows will actually show the name of the fruit, time in freezer and the amount of fruit in batch

[('Apple', '2', 2)('Banana','1',2)('Banana','2',1)] 

How do i separate this, so that

[('Apple', '2', 1)('Apple', '2', 1)('Banana','1',1)('Banana','1',1)('Banana','2',1)] 

Or at least tell me how do i make a list that can contain two same integer in it so when i have 2 piece of apple that is two days in freezer, when i call

for i in range(len(rows)) if fruit_name == apple; basket.append(rows[i][1) 

it will print actually print [2, 2] not just 2

What you want is all the rows of the table with the columns fruit_name and freeze_time and a 3d column with the value 1:

SELECT fruit_name, freeze_time, 1 AS columnname -- change the alias of this column FROM fruit_page 

Источник

Python SQLite working with Date and DateTime

This lesson demonstrates how to work with SQLite date and timestamp types in Python and vice-versa. Most of the time, we need to insert Python date or DateTime value into an SQLite table. Also, we need to read the SQLite date and timestamp values stored in the SQLite3 database and convert them into Python date and DateTime types.

Table of contents

Prerequisites

Before executing the following program, please make sure you have an SQLite table with a timestamp as a column from which you want to retrieve/insert data.

For this lesson, I am using the ‘new_developers’ table present in my SQLite database.

If a table is not present in your SQLite database, then please refer to the following articles: –

Python example to insert/Retrieve DateTime from SQLite table

In a usual scenario, when you execute the insert query with the DateTime object, the Python sqlite3 module converts it into a string format instead of an actual DateTime. And when you run a SELECT query from Python to read DateTime values from SQLite table, the sqlite3 module will convert it into a string object. Let’s understand this scenario with a simple example.

import datetime import sqlite3 def addDeveloper(id, name, joiningDate): try: sqliteConnection = sqlite3.connect('SQLite_Python.db') cursor = sqliteConnection.cursor() print("Connected to SQLite") sqlite_create_table_query = '''CREATE TABLE new_developers ( id INTEGER PRIMARY KEY, name TEXT NOT NULL, joiningDate timestamp);''' cursor = sqliteConnection.cursor() cursor.execute(sqlite_create_table_query) # insert developer detail sqlite_insert_with_param = """INSERT INTO 'new_developers' ('id', 'name', 'joiningDate') VALUES (?, ?, ?);""" data_tuple = (id, name, joiningDate) cursor.execute(sqlite_insert_with_param, data_tuple) sqliteConnection.commit() print("Developer added successfully \n") # get developer detail sqlite_select_query = """SELECT name, joiningDate from new_developers where cursor.execute(sqlite_select_query, (1,)) records = cursor.fetchall() for row in records: developer = row[0] joining_Date = row[1] print(developer, " joined on", joiningDate) print("joining date type is", type(joining_Date)) cursor.close() except sqlite3.Error as error: print("Error while working with SQLite", error) finally: if sqliteConnection: sqliteConnection.close() print("sqlite connection is closed") addDeveloper(1, 'Mark', datetime.datetime.now()) 

Connected to SQLite Developer added successfully Mark joined on 2019-06-28 21:12:35.799206 joining date type is sqlite connection is closed

As we can see, we inserted a date object, but when we retrieved the details from a table, we got a string type. But we don’t want string type. We want the DateTime type so we can directly use it.

To solve this problem, use detect_types as PARSE_DECLTYPES and PARSE_COLNAMES as arguments in the connect method of the sqlite3 module.

sqlite3.PARSE_DECLTYPES

If you use this parameter in the connect method, then the sqlite3 module parses the declared type for each column it returns.

It will parse the declared type then use the type converters dictionary to execute the converter function registered for that type there.

sqlite3.PARSE_COLNAMES

If you use this parameter in the connect method, then the SQLite interface parses the column name for each column it returns. It will use the converters dictionary and then use the converter function found there to return the value.

Let see the example now. In this example, when we read DateTime from the SQLite table we must get the joining date type as a datetime .

import datetime import sqlite3 def addDeveloper(id, name, joiningDate): try: sqliteConnection = sqlite3.connect('SQLite_Python.db', detect_types=sqlite3.PARSE_DECLTYPES | sqlite3.PARSE_COLNAMES) cursor = sqliteConnection.cursor() print("Connected to SQLite") sqlite_create_table_query = '''CREATE TABLE new_developers ( id INTEGER PRIMARY KEY, name TEXT NOT NULL, joiningDate timestamp);''' cursor = sqliteConnection.cursor() cursor.execute(sqlite_create_table_query) # insert developer detail sqlite_insert_with_param = """INSERT INTO 'new_developers' ('id', 'name', 'joiningDate') VALUES (?, ?, ?);""" data_tuple = (id, name, joiningDate) cursor.execute(sqlite_insert_with_param, data_tuple) sqliteConnection.commit() print("Developer added successfully \n") # get developer detail sqlite_select_query = """SELECT name, joiningDate from new_developers where cursor.execute(sqlite_select_query, (1,)) records = cursor.fetchall() for row in records: developer = row[0] joining_Date = row[1] print(developer, " joined on", joiningDate) print("joining date type is", type(joining_Date)) cursor.close() except sqlite3.Error as error: print("Error while working with SQLite", error) finally: if (sqliteConnection): sqliteConnection.close() print("sqlite connection is closed") addDeveloper(1, 'Mark', datetime.datetime.now()) 

Connected to SQLite Developer added successfully Mark joined on 2019-06-28 20:57:32.352790 joining date type is sqlite connection is closed

As you can see when we retrieved the joining date from SQLite table and we got the result in datetime.datetime type.

Next Steps:

To practice what you learned in this article, Solve a Python SQLite Exercise project to practice database operations.

Did you find this page helpful? Let others know about it. Sharing helps me continue to create free Python resources.

About Vishal

I’m Vishal Hule, Founder of PYnative.com. I am a Python developer, and I love to write articles to help students, developers, and learners. Follow me on Twitter

Related Tutorial Topics:

Python Exercises and Quizzes

Free coding exercises and quizzes cover Python basics, data structure, data analytics, and more.

• 15+ Topic-specific Exercises and Quizzes
• Each Exercise contains 10 questions
• Each Quiz contains 12-15 MCQ

Источник