sqlite – How can I keep the dateonly field

Hi, I have a table in Postgres and one of the fields is Date type with the format '2019-09-10' and I am transferring this table to an SQLite BUT in this SQLite I get this field as Date with this format 'Fri Sept 13 2019 00:00:00 GMT+0000 (UTC)' and I don't want it to be just like in the Postgres table '2019-09-13'

This is the Postgres DDL:

CREATE TABLE public."Checks" (
    "customerId" int4 NOT NULL,
    "checkDate" date NULL
);

and this is the DDL for the SQLite:

CREATE TABLE `Checks` (`customerId` INTEGER NOT NULL NOT NULL, `checkDate` DATE);

perform operations and store in sqlite

I am carrying out very simple operations, such as giving Python $ 800 sales value and subtracting its VAT + commissions, but I would like all these values ​​to be stored in sqlite, as I can do.

python: a persistent file system operations queue that uses sqlite and JSON text fields

I want to implement a persistent queue for file system operations (copy, delete, move) that must be secure for processes and threads. The module will be used by a demon (called qcp) that you can accept Tasks from multiple clients It must also be possible to process several queued elements in parallel.

I am new to Python and seek general advice (even the picky type) about Python best practices and about object-oriented programming in general. I am especially curious if my implementation of Task.from_json() as @staticmethod it is appropriate. I wasn't sure about this because it conditionally creates instances of subclasses of Task instead of a simple Task.

task.py

from pathlib import Path
from typing import Union, Optional
import shutil
import os
import json
import sqlite3    
import logging

Pathish = Union(Path, str)


class Task:
    """Abstract class for qcp Tasks. Should not be instantiated directly."""

    def __init__(self) -> None:
        self.type = 0

    def run(self) -> None:
        """Run the Task"""
        pass

    @staticmethod
    def from_dict(x, validate: bool = False) -> "Task":
        """Create a Task of the appropriate subclass from a python dict"""
        task_type = x("type")

        if task_type == -1:
            return KillTask()
        elif task_type == 0:
            return Task()
        elif task_type == 1:
            return EchoTask(x("msg"))
        elif task_type == 2:
            return FileTask(x("src"), validate=validate)
        elif task_type == 3:
            return DeleteTask(x("src"), validate=validate)
        elif task_type == 4:
            return CopyTask(x("src"), x("dst"), validate=validate)
        elif task_type == 5:
            return MoveTask(x("src"), x("dst"), validate=validate)
        elif task_type == 6:
            raise NotImplementedError
        else:
            raise ValueError

    def __repr__(self) -> str:
        return 'NULL'

    def __eq__(self, other) -> bool:
        return self.__dict__ == other.__dict__

    def __ne__(self, other) -> bool:
        return self.__dict__ != other.__dict__


class KillTask(Task):
    """Kill the qcp server"""
    def __init__(self) -> None:
        self.type = -1
        super().__init__()

    def run(self) -> None:
        raise NotImplementedError

    def __repr__(self) -> str:
        return 'KILL'


class EchoTask(Task):
    """Log a message"""
    def __init__(self,  msg: str) -> None:
        super().__init__()
        self.msg = msg
        self.type = 1

    def run(self) -> None:
        print(self.msg)

    def __repr__(self) -> str:
        return f'Echo: "{self.msg}"'


class FileTask(Task):
    """Abstract class for all file-based tasks"""
    def __init__(self, src: Pathish, validate: bool = True) -> None:
        super().__init__()
        self.validate = validate
        self.src = Path(src).as_posix()
        self.type = 2
        if validate:
            self.__validate__()

    def __validate__(self) -> None:
        if not Path(self.src).exists():
            raise FileNotFoundError(f'{self.src} does not exist')
        elif not (Path(self.src).is_dir() or Path(self.src).is_file()):
            raise TypeError(f'{self.src} is neither a file nor directory')


class DeleteTask(FileTask):
    """Delete a file"""
    def __init__(self, src: Pathish, validate: bool = True) -> None:
        super().__init__(src=src, validate=validate)
        self.type = 3

    def run(self) -> None:
        os.unlink(self.src)

    def __repr__(self) -> str:
        return f'DEL {self.src}'


class CopyTask(FileTask):
    """Copy a file"""
    def __init__(self, src: Pathish, dst: Pathish, validate: bool = True) -> None:
        super().__init__(src=src, validate=False)
        self.dst = Path(dst).as_posix()
        self.type = 4
        self.validate = validate
        if validate:
            self.__validate__()

    def __repr__(self) -> str:
        return f'COPY {self.src} -> {self.dst}'

    def __validate__(self) -> None:
        super().__validate__()
        if Path(self.dst).exists():
            raise FileExistsError

    def run(self) -> None:
        self.__validate__()
        shutil.copy(self.src, self.dst)


class MoveTask(CopyTask):
    """Move a file"""
    def __init__(self, src: Pathish, dst: Pathish, validate: bool = True) -> None:
        super().__init__(src=src, dst=dst, validate=validate)
        self.type = 5

    def run(self) -> None:
        super().__validate__()
        shutil.move(self.src, self.dst)

    def __repr__(self) -> str:
        return f'MOVE {self.src} -> {self.dst}'


class TaskQueueElement:
    """An enqueued Task"""

    task = None  #: A Task
    status = None  #: Status of the queued Task
    priority = None  #: Priority of the queued Task

    def __init__(self, task: Task, priority: 1) -> None:
        self.task = task
        self.priority = priority

    def __lt__(self, other) -> bool:
        return self.priority < other.priority

    def __gt__(self, other) -> bool:
        return self.priority > other.priority

    def __eq__(self, other) -> bool:
        return self.__dict__ == other.__dict__

    def __ne__(self, other) -> bool:
        return self.__dict__ != other.__dict__


class TaskQueue:
    """A prioritzed queue for tasks"""
    def __init__(self, path: Pathish = 'qcp.db') -> None:
        """
        Instantiate a TaskQueue

        :param path: Path to store the persistent queue
        :type path: Path or str
        """

        self.con = sqlite3.connect(path, isolation_level="EXCLUSIVE")
        self.path = Path(path)

        cur = self.con.cursor()
        cur.execute("""
           CREATE TABLE IF NOT EXISTS tasks (
              priority INTEGER,
              task TEXT,
              status INTEGER,
              owner INTEGER              
            )              
        """)
        self.con.commit()

    @property
    def n_total(self) -> int:
        """Count of all tasks in queue (including failed and completed)"""
        cur = self.con.cursor()
        return cur.execute("SELECT COUNT(1) from tasks").fetchall()(0)(0)

    @property
    def n_pending(self) -> int:
        """Number of pending tasks"""
        cur = self.con.cursor()
        return cur.execute("SELECT COUNT(1) FROM tasks WHERE status = 0").fetchall()(0)(0)

    @property
    def n_running(self) -> int:
        """Count of currently running tasks"""
        cur = self.con.cursor()
        return cur.execute("SELECT COUNT(1) FROM tasks WHERE status = 1").fetchall()(0)(0)

    @property
    def n_done(self) -> int:
        """count of completed tasks"""
        cur = self.con.cursor()
        return cur.execute("SELECT COUNT(1) from tasks WHERE status = 2").fetchall()(0)(0)

    @property
    def n_failed(self) -> int:
        """count of completed tasks"""
        cur = self.con.cursor()
        return cur.execute("SELECT COUNT(1) from tasks WHERE status = -1").fetchall()(0)(0)

    def put(self, task: "Task", priority: Optional(int) = None) -> None:
        """
        Enqueue a task

        :param task: Task to be added to the queue
        :type task: Task
        :param priority: (optional) priority for executing `task` (tasks with lower priority will be executed earlier)
        :type priority: int
        """

        cur = self.con.cursor()
        cur.execute(
            "INSERT INTO tasks (priority, task, status) VALUES (?, ?, ?)", (priority, json.dumps(task.__dict__), 0)
        )
        self.con.commit()

    def pop(self) -> "Task":
        """
        Retrieves Task object and sets status of Task in database to "in progress" (1)

        :raises AlreadyUnderEvaluationError: If trying to pop a tasks that is already being processed  (i.e. if a race
        condition occurs if the queue is processed in parallel)
        """
        cur = self.con.cursor()
        cur.execute("SELECT _ROWID_ from tasks WHERE status = 0 ORDER BY priority LIMIT 1")
        oid = cur.fetchall()(0)(0).__str__()
        self.mark_running(oid, id(self))

        cur.execute("SELECT owner, task FROM tasks WHERE _ROWID_ = ?", oid)
        record = cur.fetchall()(0)
        if record(0) != id(self):
            raise AlreadyUnderEvaluationError

        task = Task.from_dict(json.loads(record(1)))
        task.oid = oid
        return task

    def peek(self) -> "Task":
        """
        Retrieves Task object without changing its status in the queue
        """
        cur = self.con.cursor()
        cur.execute("SELECT * from tasks ORDER BY priority LIMIT 1")
        record = cur.fetchall()(0)
        oid = record(0).__str__()
        task = Task.from_dict(json.loads(record(1)), validate=False)
        task.oid = oid
        return task

    def print(self, n: int = 10) -> None:
        """
        Print an overview of the queue

        :param n: number of tasks to preview
        :type n: int
        """
        assert isinstance(n, int) and n > 0
        cur = self.con.cursor()
        cur.execute("SELECT status, task from tasks ORDER BY priority LIMIT ?", (str(n), ))
        records = cur.fetchall()
        for record in records:
            print(f"({record(0)}) {Task.from_dict(json.loads(record(1)))}")

    def mark_pending(self, oid: int) -> None:
        """
        Mark the operation with the _ROWID_ `oid` as "pending" (0)

        :param oid: ID of the task to mark
        :type oid: int
        """
        cur = self.con.cursor()
        cur.execute("UPDATE tasks SET status = 0, owner = NULL where _ROWID_ = ?", (oid, ))
        self.con.commit()

    def mark_running(self, oid: int, owner: int) -> None:
        """Mark the operation with the _ROWID_ `oid` as "running" (1). The "owner" Id is to ensure no two processes
        are trying to execute the same operation

        :param oid: ID of the task to mark
        :type oid: int
        :param owner: Id of the process that is handling the operation
        :type owner: int
        """
        cur = self.con.cursor()
        cur.execute("UPDATE tasks SET status = 1, owner = ? where _ROWID_ = ?", (owner, oid))
        self.con.commit()

    def mark_done(self, oid: int) -> None:
        """
        Mark the operation with the _ROWID_ `oid` as "done" (2)
        :param oid: ID of the task to mark
        :type oid: int
        """
        cur = self.con.cursor()
        cur.execute("UPDATE tasks SET status = 2, owner = NULL where _ROWID_ = ?", (oid, ))
        self.con.commit()

    def mark_failed(self, oid: int) -> None:
        """
        Mark the operation with the _ROWID_ `oid` as "failed" (-1)

        :param oid: ID of the task to mark
        :type oid: int
        """
        cur = self.con.cursor()
        cur.execute("UPDATE tasks SET status = -1, owner = NULL where _ROWID_ = ?", (oid, ))
        self.con.commit()

    def run(self) -> None:
        """Execute all pending tasks"""
        if self.n_pending < 1:
            logging.getLogger().warn("Queue is empty")

        while self.n_pending > 0:
            op = self.pop()
            op.run()
            self.mark_done(op.oid)


class AlreadyUnderEvaluationError(Exception):
    """This Task is already being processed by a different worker"""
    pass

A quick demo in pytest:

import tasks
import pytest

def test_TaskQueue(tmp_path):
    """TaskQueue can queue and execute tasks"""
    src = tmp_path.joinpath("foo")
    src.touch()

    q = tasks.TaskQueue(tmp_path.joinpath("qcp.db"))
    q.put(tasks.CopyTask(src, tmp_path.joinpath("copied_file")))
    q.run()
    assert tmp_path.joinpath("copied_file").is_file()
    q.put(tasks.MoveTask(tmp_path.joinpath("copied_file"), tmp_path.joinpath("moved_file")))
    q.run()
    assert not tmp_path.joinpath("copied_file").is_file()
    assert tmp_path.joinpath("moved_file").is_file()
    q.put(tasks.DeleteTask(tmp_path.joinpath("moved_file")))
    q.run()
    assert not tmp_path.joinpath("moved_file").is_file()
    assert src.is_file()

p.s.: I am aware that I am importing logging for a single registration call, but I plan to include a more extensive registration in the future

c ++ – RAII container for SQLite transactions

I am using this beautiful C ++ container to work with SQLite3 in my project. I wanted to handle errors gracefully and automatically revert if a piece of code fails, so I wrote this simple auxiliary class:

class SqlTransaction { // RAII class to rollback on error
    public:
        SqlTransaction(std::shared_ptr db_): db(db_) { *db << "begin;"; };
        ~SqlTransaction() { if (!finished) rollback(); };
        void commit() { if (!finished) { *db << "commit;"; finished = true; } };
        void rollback() { if (!finished) { *db << "rollback;"; finished = true; } };

        // Disable both copying and moving
        SqlTransaction(const SqlTransaction&) = delete;
        SqlTransaction& operator=(const SqlTransaction&) = delete;
        SqlTransaction(SqlTransaction&&) = delete;

    private:
        std::shared_ptr db;
        b

It should be used like this:

SqlTransaction trans(db);
// work with db here, may throw if something goes wrong
trans.commit();

SQLite – Obtain all other rows with any duplicate column value

I have a user table, with information about them and the combination of the fields and UserName It is unique (a row as a whole, not just in a field)

Users

+----------+--------+--------+--------+
| UserName | Field1 | Field2 | Field3 |
+----------+--------+--------+--------+
| UserNum1 | AAABBB | CCCDDD | EEEFFF |
| UserNum2 | AAABBB | IIIJJJ | KKKFFF |
| UserNum3 | LLLMMM | CCCDDD | PPPQQQ |
| UserNum4 | RRRSSS | TTTUUU | VVVWWW |
+----------+--------+--------+--------+

Given the value of a column to find, I have to find other users with ANY column value for all columns dynamically.

For example, I only provided the username "UserNum1" as UserName, You must return User 2 and 3. for the values ​​of his Field1 and Field2 respectively.

Desired output:

+----------+--------+--------+--------+
| UserName | Field1 | Field2 | Field3 |
+----------+--------+--------+--------+
| UserNum2 | AAABBB | IIIJJJ | KKKFFF | # Field1 has same value on User1 & User2
| UserNum3 | LLLMMM | CCCDDD | PPPQQQ | # Field2 has same value on User1 & User3
+----------+--------+--------+--------+

Are there ways to do this in a query? Or do I really have to check each column and do the other processing in my backend?

For your information: The initial problem is finding possible duplicate user accounts or "alts" based on similar fields

sql – Need help extracting values ​​from the URL link in SQLite

I am using DB Browser for SQLite.

I have a table called & # 39; df & # 39; and a column with lots of URL links called & # 39; links & # 39 ;. All the links are from the same website and follow the same structured format:

https: // & # 39; website name & # 39; / & # 39; language & # 39; / & # 39; type of content & # 39; / & # 39; content id & # 39; / & # 39; content name & # 39;

Some examples of language:
1. en_au
2. fr_ca
3. en_us
etc.

Also, there is only one unique value in the & # 39; content type & # 39; of the link.

Goal:
I need help to extract all the content identifiers from the link.

I tried the regular expression, but this extracts all the information that matches the regular expression instead of the content ID. I have also used the substr function (see code below), but this poses some problems:
1. Some of the links do not start with & # 39; www & # 39 ;.
2. Not all content identifiers have the same length of characters.
3. Not all language identifiers have the same length of characters.

SELECT
substr (links, 36, instr (links, & # 39; / & # 39;) +1) AS content_id
From df

Data processing Sqlite and Python

Good day friends!!!!

I have a very basic question, it is in terms of data processing which I obtain from a database, in this case there would be 2 columns, one called "clinical_ studies" and another "cost" where a study and its cost are in the same row practically related, within my graphical interface the clinical studies are selected from an OptionMenu, and they appear in a very strange way I leave image:

enter the description of the image here

These data are displayed in a tuple and on the terminal screen they are shown in another way:

Data obtained from OptionMenu: ('GLUCOSE',)

and the tuple is stored with the name self.menu_p and it is shown:

[('ANTIDOPING EN ORINA',), ('BIOMETRIA HEMATICA COMPLETA BHC',), 
 ('ESPERMATOBIOSCOPIA DIRECTA',), ('EXAMEN GENERAL DE ORINA',), ('GLUCOSA',)] 

is so that the screen is not full I decided to cut

I want to obtain the index of where the GLUCOSE study is located;

index = self.menu_p.index (self.estudio)

but I get the error as an answer:

index = self.menu_p.index (self.estudio)
ValueError: "('GLUCOSE',)" is not in list

Obviously the answer is that it does not find the value "('GLUCOSE',)" inside the tuple but it is practically written in the same way XD now if I change the data type as list with the line: self.estudio = list (self.test.get ()) it appears like this:

index = self.menu_p.index (self.estudio)
ValueError: ['(', "'", 'G', 'L', 'U', 'C', 'O', 'S', 'A', "'", ',', ')'] is not in list

Does anyone know what mistake I'm making ???

Attach the code:

                    def menu_desplegable (self): # Drop-down menu obtained from the BD
         query = 'SELECT clinical_classes FROM price_list ORDER BY clinical_classes'
         self.menu_p = self.run_query (query) .fetchall ()
         if self.menu_p is None:
            self.menu_p = 'There are no studies'
         else:
            self.menu_p = list (self.menu_p)

     def sums (self):
           self.estudio = (self.test.get ())
           print (type (self.estudio)) #the type of data obtained is a String
           print (self.estudio, ' n')
           print (self.menu_p)
           index = self.menu_p.index (self.estudio)

Thank you very much again!!!!

sqlite – How to fix the different versions of Sqlite3 using Python?

I had the sqlite version 3.7.17 in cent.
Then I installed from the source code 3.28.0.

Unfortunately, the sqlite3 Python module is still using the version 3.7.17.

Do I have to recompile Python?

[root@jenkins ~]# python3.6
Python 3.6.8 (default, June 21, 2019, 09:50:01) 
[GCC 4.8.5 20150623 (Red Hat 4.8.5-36)] in linux
Write "help", "copyright", "credits" or "license" to get more information.
>>> import sqlite3
>>> sqlite3.sqlite_version
& # 39; 3.7.17 & # 39;
>>> exit ()
[root@jenkins ~]# sqlite3
SQLite version 3.28.0 2019-04-16 19:49:53
Enter ".help" to get suggestions for use.
Connected to a transient database in memory.
Use ".open FILENAME" to reopen in a persistent database.
sqlite> .exit

java – Export and import SQLite database

Good evening, it can be something very simple for some, but I have difficulties to copy my database of the application that I have developed. I need to do it in a way that will have an activity only for BD import and export options. I need to copy the "example.db" of the folder where it is created by default for a folder that we assume is called "Backup" and will be inside a folder with the name of the application in the internal memory of the device. I have read many similar questions, I have seen several resolutions but still I did not get it, could someone help? And please, not only from fewer points does the question tell you that it is something too simple, I really need help.

How to configure "PRAGMA foreign_keys = ON" every time I connect to db sqlite through Datagrip

In my pet project, I'm working with a small sqlite dl and I just realized that, for In the elimination of waterfalls Actually I do the thing, I need to configure PRAGMA foreign_keys = ON Every time I connect to db.

However, I am using Datagrip and since it is a fairly new product for me, I do not know what is the best way to configure this option every time.