0

Below can not add current time into dateTime of test4 table , how to modify ?

query.exec_("INSERT INTO test values('abc@wdc.com','abc1',dt"))

from PyQt5.QtSql import QSqlDatabase, QSqlQuery
from datetime import datetime
database = QtSql.QSqlDatabase.addDatabase('QSQLITE')
database.setDatabaseName('test1.db')
database.open()
query = QSqlQuery()
query.exec_("CREATE TABLE IF NOT EXISTS test4(id varchar(50) PRIMARY KEY,password varchar(50),dateTime timestamp)")
dt=datetime.now()
query.exec_("INSERT INTO test VALUES('abc@wdc.com','abc1',dt"))
eyllanesc
  • 235,170
  • 19
  • 170
  • 241
Raylene
  • 13
  • 4

2 Answers2

1

I wager an easier solution is to set a default in the create table sql query. As of Sqlite v3.1.0 you can now add current_timestamp. This will save you hassles of needless import when inserting to any table.

CREATE TABLE IF NOT EXISTS test4(
id varchar(50) PRIMARY KEY,
password varchar(50),
dateTime timestamp not null default current_timestamp
)

As shown in enter link description here

mello
  • 21
  • 2
  • 5
0

You are not adding the dt variable, you're adding the "dt" string.

There are also two other problems in your code: you imported QSqlDatabase but you're using QtSql.QSqlDatabase, and you are adding the value to the test table while the one you created is test4.

from PyQt5.QtSql import QSqlDatabase, QSqlQuery
from datetime import datetime
database = QSqlDatabase.addDatabase('QSQLITE')
database.setDatabaseName('test1.db')
database.open()
query = QSqlQuery()
query.exec_("CREATE TABLE IF NOT EXISTS test4(id varchar(50) PRIMARY KEY,password varchar(50),dateTime timestamp)")
dt=datetime.now()
query.exec_("INSERT INTO test4 VALUES('abc@wdc.com','abc1','{}'".format(dt))

Consider that the above will use a natural string representation of the datetime object:

2020-11-12 14:32:13.471729

This requires the quotes around the variable, otherwise the space between the date and the time will be interpreted as a literal query space (used to separate commands), resulting in an error. You could even use the isoformat() for a more standardized representation, allowing further reconversion to datetime objects using fromisoformat().

musicamante
  • 41,230
  • 6
  • 33
  • 58
  • Appreciated your reply for pointing out the problems .Actually i also write "from PyQt5 import QtSql "in my file ,i mixed them up . – Raylene Nov 13 '20 at 03:42
  • Ok, remember that mixing import styles from the same modules is usually not a very good idea: it doesn't get any benefit and may lead to confusion, while also making your code less readable and more incoherent. You should either import the whole QtSql or its classes. – musicamante Nov 13 '20 at 10:02