#StackBounty: #python #sqlite #sqlalchemy SqlAlchemy Create Table Specifying Schema

Bounty: 50

I want to copy a table from SQL Server to Sqlite via SqlAlchemy reflection.

from sqlalchemy import create_engine, MetaData, Table

source_engine = create_engine(...) # create sql server engine
source_connection = source_engine.connect()
source_metadata = MetaData() # (1)

destination_engine = create_engine('sqlite:///test.db', echo=True)
destination_connection = destination_engine.connect()

Table('table_name', source_metadata, autoload=True, autoload_with=source_engine) # (2)

source_metadata.create_all(destination_engine)

The above works with the default schema (dbo) but I want to specify the schema of the reflected tables in SQL Server.

When I change (1) to source_metadata = Metadata(schema='dbo') or (2) to Table(table_name, source_metadata, autoload=True, autoload_with=source_engine, schema='dbo') I get the following error:

sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) unknown database "dbo"

I know the issue is with sqlite. When I create a table in SQLite Studio like below, I get the same error:

CREATE TABLE dbo.table_name(...

but the following creates the table successfully:

CREATE TABLE [dbo.table_name](...

Finally, I have tried the below with no success:

destination_engine.execute("ATTACH DATABASE 'test.db' AS 'dbo'")

How do I create a table specifying the schema using SqlAlchemy?


Get this bounty!!!

#StackBounty: #sql #database #sqlite #time-series #auto-increment How to use time-series with Sqlite, with fast time-range queries?

Bounty: 400

Let’s say we log events in a Sqlite database with Unix timestamp column ts:

CREATE TABLE data(ts INTEGER, text TEXT);   -- more columns in reality

and that we want fast lookup for datetime ranges, for example:

SELECT text FROM data WHERE ts BETWEEN 1608710000 and 1608718654;

Like this, EXPLAIN QUERY PLAN gives SCAN TABLE data which is bad, so one obvious solution is to create an index with CREATE INDEX dt_idx ON data(ts).

Then the problem is solved, but it’s rather a poor solution to have to maintain an index for an already-increasing sequence / already-sorted column ts for which we could use a B-tree search in O(log n) directly. Internally this will be the index:

ts           rowid
1608000001   1
1608000002   2
1608000012   3
1608000077   4

which is a waste of DB space (and CPU when a query has to look in the index first).

To avoid this:

  • (1) we could use ts as INTEGER PRIMARY KEY, so ts would be the rowid itself. But this fails because ts is not unique: 2 events can happen at the same second (or even at the same millisecond).

    See for example the info given in SQLite Autoincrement.

  • (2) we could use rowid as timestamp ts concatenated with an increasing number. Example:

     16087186540001      
     16087186540002
     [--------][--]
         ts     increasing number 
    

    Then rowid is unique and strictly increasing (provided there are less than 10k events per second), and no index would be required. A query WHERE ts BETWEEN a AND b would simply become WHERE rowid BETWEEN a*10000 AND b*10000+9999.

    But is there an easy way to ask Sqlite to INSERT an item with a rowid greater than or equal to a given value? Let’s say the current timestamp is 1608718654 and two events appear:

      CREATE TABLE data(ts_and_incr INTEGER PRIMARY KEY AUTOINCREMENT, text TEXT);
      INSERT INTO data VALUES (NEXT_UNUSED(1608718654), "hello")  #16087186540001 
      INSERT INTO data VALUES (NEXT_UNUSED(1608718654), "hello")  #16087186540002
    

More generally, how to create time-series optimally with Sqlite, to have fast queries WHERE timestamp BETWEEN a AND b?


Get this bounty!!!

#StackBounty: #18.04 #python #python3 #sqlite #sqlite3 Is it possible to install SQLite 3.24+ on Ubuntu 18.04?

Bounty: 50

I am trying to update my SQLite version to 3.24 or above so that a Python app can make use of the new “UPSERT” queries. I have been trying for a few hours to do this with little success; it refuses to update past 3.22.

I have attempted:

  • Using apt to install and reinstall sqlite / libsqlite3-dev (and various versions of this)

  • Downloading packages from launchpad (such as
    https://launchpad.net/ubuntu/+source/sqlite3/3.26.0-2) and attempting to install them

  • Using Python pip to try and update sqlite3

  • Adding a few PPA repos to try and grab it from there

  • Other various suggestions found from google

What I have not tried:

  • Building SQLite from source (this is a bit of a last resort for me)

Is it possible to install a version of SQLite 3.24+ on Ubuntu 18.04? If so, is the only way to build from source or is there an easy way to pick up a more recent version through apt (or similar)?


Get this bounty!!!

#StackBounty: #linux #bash #centos #python3 #sqlite ModuleNotFoundError: No module named '_sqlite3'

Bounty: 50

We have different python version installed and specifically using python3.7 so I have edited my .bashrc file. We are using Centos7 with Linux server.

# .bashrc

# Source global definitions
if [ -f /etc/bashrc ]; then
        . /etc/bashrc
fi

# Uncomment the following line if you don't like systemctl's auto-paging feature:
# export SYSTEMD_PAGER=
# User specific aliases and functions
alias python=python3.7
alias pip=pip3.7


[xyz@innolx20122 ~]$ python
python             python2.7          python3.6          python3.7          python3.7m-config
python2            python3            python3.6m         python3.7m

[xyz@innolx20122 ~]$ which sqlite3
/usr/bin/sqlite3

Its working with python2.7 and python3.6 version

[xyz@innolx20122 ~]$ python2.7
Python 2.7.5 (default, Apr  2 2020, 13:16:51)
[GCC 4.8.5 20150623 (Red Hat 4.8.5-39)] on linux2
Type "help", "copyright", "credits" or "license" for more information.
>>> import sqlite3

[xyz@innolx20122 ~]$ python3.6
Python 3.6.8 (default, Apr  2 2020, 13:34:55)
[GCC 4.8.5 20150623 (Red Hat 4.8.5-39)] on linux
Type "help", "copyright", "credits" or "license" for more information.
>>> import sqlite3

It’s not working with python3.7 version

[xyz@innolx20122 ~]$ python3.7
Python 3.7.0 (default, Sep  3 2020, 09:25:25)
[GCC 4.8.5 20150623 (Red Hat 4.8.5-39)] on linux
Type "help", "copyright", "credits" or "license" for more information.
>>> import sqlite3
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "/usr/local/lib/python3.7/sqlite3/__init__.py", line 23, in <module>
    from sqlite3.dbapi2 import *
  File "/usr/local/lib/python3.7/sqlite3/dbapi2.py", line 27, in <module>
    from _sqlite3 import *
ModuleNotFoundError: No module named '_sqlite3'

Update-

We installed python following instruction in the below link
Python3.7 installation link

Hence my python3.7 version is installed at root level

[root@innolx20122 ~]# ls
anaconda-ks.cfg  Python-3.7.0  Python-3.7.0.tgz
[root@innolx20122 ~]# cd Python-3.7.0
[root@innolx20122 Python-3.7.0]# ls
aclocal.m4    config.status  Doc         Lib              Mac              Misc     PC              pyconfig.h     python-config     setup.py
build         config.sub     Grammar     libpython3.7m.a  Makefile         Modules  PCbuild         pyconfig.h.in  python-config.py  Tools
config.guess  configure      Include     LICENSE          Makefile.pre     Objects  Programs        python         python-gdb.py
config.log    configure.ac   install-sh  m4               Makefile.pre.in  Parser   pybuilddir.txt  Python         README.rst

I saw one link on stack overflow suggesting some workaround.
fix Sqlite3 issue

Kindly suggest if it’s ok to run below commands from same root directory itself

yum install sqlite-devel

./configure
make && make altinstall


Get this bounty!!!

#StackBounty: #linux #bash #centos #python3 #sqlite ModuleNotFoundError: No module named '_sqlite3'

Bounty: 50

We have different python version installed and specifically using python3.7 so I have edited my .bashrc file. We are using Centos7 with Linux server.

# .bashrc

# Source global definitions
if [ -f /etc/bashrc ]; then
        . /etc/bashrc
fi

# Uncomment the following line if you don't like systemctl's auto-paging feature:
# export SYSTEMD_PAGER=
# User specific aliases and functions
alias python=python3.7
alias pip=pip3.7


[xyz@innolx20122 ~]$ python
python             python2.7          python3.6          python3.7          python3.7m-config
python2            python3            python3.6m         python3.7m

[xyz@innolx20122 ~]$ which sqlite3
/usr/bin/sqlite3

Its working with python2.7 and python3.6 version

[xyz@innolx20122 ~]$ python2.7
Python 2.7.5 (default, Apr  2 2020, 13:16:51)
[GCC 4.8.5 20150623 (Red Hat 4.8.5-39)] on linux2
Type "help", "copyright", "credits" or "license" for more information.
>>> import sqlite3

[xyz@innolx20122 ~]$ python3.6
Python 3.6.8 (default, Apr  2 2020, 13:34:55)
[GCC 4.8.5 20150623 (Red Hat 4.8.5-39)] on linux
Type "help", "copyright", "credits" or "license" for more information.
>>> import sqlite3

It’s not working with python3.7 version

[xyz@innolx20122 ~]$ python3.7
Python 3.7.0 (default, Sep  3 2020, 09:25:25)
[GCC 4.8.5 20150623 (Red Hat 4.8.5-39)] on linux
Type "help", "copyright", "credits" or "license" for more information.
>>> import sqlite3
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "/usr/local/lib/python3.7/sqlite3/__init__.py", line 23, in <module>
    from sqlite3.dbapi2 import *
  File "/usr/local/lib/python3.7/sqlite3/dbapi2.py", line 27, in <module>
    from _sqlite3 import *
ModuleNotFoundError: No module named '_sqlite3'

Update-

We installed python following instruction in the below link
Python3.7 installation link

Hence my python3.7 version is installed at root level

[root@innolx20122 ~]# ls
anaconda-ks.cfg  Python-3.7.0  Python-3.7.0.tgz
[root@innolx20122 ~]# cd Python-3.7.0
[root@innolx20122 Python-3.7.0]# ls
aclocal.m4    config.status  Doc         Lib              Mac              Misc     PC              pyconfig.h     python-config     setup.py
build         config.sub     Grammar     libpython3.7m.a  Makefile         Modules  PCbuild         pyconfig.h.in  python-config.py  Tools
config.guess  configure      Include     LICENSE          Makefile.pre     Objects  Programs        python         python-gdb.py
config.log    configure.ac   install-sh  m4               Makefile.pre.in  Parser   pybuilddir.txt  Python         README.rst

I saw one link on stack overflow suggesting some workaround.
fix Sqlite3 issue

Kindly suggest if it’s ok to run below commands from same root directory itself

yum install sqlite-devel

./configure
make && make altinstall


Get this bounty!!!

#StackBounty: #linux #bash #centos #python3 #sqlite ModuleNotFoundError: No module named '_sqlite3'

Bounty: 50

We have different python version installed and specifically using python3.7 so I have edited my .bashrc file. We are using Centos7 with Linux server.

# .bashrc

# Source global definitions
if [ -f /etc/bashrc ]; then
        . /etc/bashrc
fi

# Uncomment the following line if you don't like systemctl's auto-paging feature:
# export SYSTEMD_PAGER=
# User specific aliases and functions
alias python=python3.7
alias pip=pip3.7


[xyz@innolx20122 ~]$ python
python             python2.7          python3.6          python3.7          python3.7m-config
python2            python3            python3.6m         python3.7m

[xyz@innolx20122 ~]$ which sqlite3
/usr/bin/sqlite3

Its working with python2.7 and python3.6 version

[xyz@innolx20122 ~]$ python2.7
Python 2.7.5 (default, Apr  2 2020, 13:16:51)
[GCC 4.8.5 20150623 (Red Hat 4.8.5-39)] on linux2
Type "help", "copyright", "credits" or "license" for more information.
>>> import sqlite3

[xyz@innolx20122 ~]$ python3.6
Python 3.6.8 (default, Apr  2 2020, 13:34:55)
[GCC 4.8.5 20150623 (Red Hat 4.8.5-39)] on linux
Type "help", "copyright", "credits" or "license" for more information.
>>> import sqlite3

It’s not working with python3.7 version

[xyz@innolx20122 ~]$ python3.7
Python 3.7.0 (default, Sep  3 2020, 09:25:25)
[GCC 4.8.5 20150623 (Red Hat 4.8.5-39)] on linux
Type "help", "copyright", "credits" or "license" for more information.
>>> import sqlite3
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "/usr/local/lib/python3.7/sqlite3/__init__.py", line 23, in <module>
    from sqlite3.dbapi2 import *
  File "/usr/local/lib/python3.7/sqlite3/dbapi2.py", line 27, in <module>
    from _sqlite3 import *
ModuleNotFoundError: No module named '_sqlite3'

Update-

We installed python following instruction in the below link
Python3.7 installation link

Hence my python3.7 version is installed at root level

[root@innolx20122 ~]# ls
anaconda-ks.cfg  Python-3.7.0  Python-3.7.0.tgz
[root@innolx20122 ~]# cd Python-3.7.0
[root@innolx20122 Python-3.7.0]# ls
aclocal.m4    config.status  Doc         Lib              Mac              Misc     PC              pyconfig.h     python-config     setup.py
build         config.sub     Grammar     libpython3.7m.a  Makefile         Modules  PCbuild         pyconfig.h.in  python-config.py  Tools
config.guess  configure      Include     LICENSE          Makefile.pre     Objects  Programs        python         python-gdb.py
config.log    configure.ac   install-sh  m4               Makefile.pre.in  Parser   pybuilddir.txt  Python         README.rst

I saw one link on stack overflow suggesting some workaround.
fix Sqlite3 issue

Kindly suggest if it’s ok to run below commands from same root directory itself

yum install sqlite-devel

./configure
make && make altinstall


Get this bounty!!!

#StackBounty: #python #sqlite #blob #zodb #relstorage How are blobs removed in RelStorage pack?

Bounty: 250

This question is related to How to pack blobstorage with Plone and RelStorage

Using zodb database with RelStorage and sqlite as its backend I am trying to remove unused blobs. Currently db.pack does not remove the blobs from disc. The minimum working example below demonstrates this behavior:

import logging
import numpy as np
import os
import persistent
from persistent.list import PersistentList
import shutil
import time
from ZODB import config, blob

connectionString = """
%import relstorage
<zodb main>
<relstorage>
blob-dir ./blob
keep-history false
cache-local-mb 0
<sqlite3>
    data-dir .
</sqlite3>
</relstorage>
</zodb>
"""


class Data(persistent.Persistent):
    def __init__(self, data):
        super().__init__()

        self.children = PersistentList()

        self.data = blob.Blob()
        with self.data.open("w") as f:
            np.save(f, data)


def main():
    logging.basicConfig(level=logging.INFO)
    # Initial cleanup
    for f in os.listdir("."):
        if f.endswith("sqlite3"):
            os.remove(f)

    if os.path.exists("blob"):
        shutil.rmtree("blob", True)

    # Initializing database
    db = config.databaseFromString(connectionString)
    with db.transaction() as conn:
        root = Data(np.arange(10))
        conn.root.Root = root

        child = Data(np.arange(10))
        root.children.append(child)

    # Removing child reference from root
    with db.transaction() as conn:
        conn.root.Root.children.pop()

    db.close()

    print("blob directory:", [[os.path.join(rootDir, f) for f in files] for rootDir, _, files in os.walk("blob") if files])
    db = config.databaseFromString(connectionString)
    db.pack(time.time() + 1)
    db.close()
    print("blob directory:", [[os.path.join(rootDir, f) for f in files] for rootDir, _, files in os.walk("blob") if files])


if __name__ == "__main__":
    main()

The example above does the following:

  1. Remove any previous database in the current directory along with the blob directory.
  2. Create a database/storage from scratch adding two objects (root and child), while child is referenced by root and perform a transaction.
  3. Remove the linkage from root to child and perform a transaction.
  4. Close the database/storage
  5. Open the database/storage and perform db.pack for one second in the future.

The output of the minimum working example is the following:

INFO:ZODB.blob:(23376) Blob directory '<some path>/blob/' does not exist. Created new directory.
INFO:ZODB.blob:(23376) Blob temporary directory './blob/tmp' does not exist. Created new directory.
blob directory: [['blob/.layout'], ['blob/3/.lock', 'blob/3/0.03da352c4c5d8877.blob'], ['blob/6/.lock', 'blob/6/0.03da352c4c5d8877.blob']]
INFO:relstorage.storage.pack:pack: beginning pre-pack
INFO:relstorage.storage.pack:Analyzing transactions committed Thu Aug 27 11:48:17 2020 or before (TID 277592791412927078)
INFO:relstorage.adapters.packundo:pre_pack: filling the pack_object table
INFO:relstorage.adapters.packundo:pre_pack: Filled the pack_object table
INFO:relstorage.adapters.packundo:pre_pack: analyzing references from 7 object(s) (memory delta: 256.00 KB)
INFO:relstorage.adapters.packundo:pre_pack: objects analyzed: 7/7
INFO:relstorage.adapters.packundo:pre_pack: downloading pack_object and object_ref.
INFO:relstorage.adapters.packundo:pre_pack: traversing the object graph to find reachable objects.
INFO:relstorage.adapters.packundo:pre_pack: marking objects reachable: 4
INFO:relstorage.adapters.packundo:pre_pack: finished successfully
INFO:relstorage.storage.pack:pack: pre-pack complete
INFO:relstorage.adapters.packundo:pack: will remove 3 object(s)
INFO:relstorage.adapters.packundo:pack: cleaning up
INFO:relstorage.adapters.packundo:pack: finished successfully
blob directory: [['blob/.layout'], ['blob/3/.lock', 'blob/3/0.03da352c4c5d8877.blob'], ['blob/6/.lock', 'blob/6/0.03da352c4c5d8877.blob']]

As you can see db.pack does remove 3 objects "will remove 3 object(s)" but the blobs in the file system are unchanged.

In the unit tests of RelStorage it appears that they do test if the blobs are removed from the file system (see here), but in the script above it does not work.

What am I doing wrong? Any hint/link/help is appreciated.


Get this bounty!!!

#StackBounty: #sqlite How to automatically rename columns from same table join?

Bounty: 100

When I join two tables to avoid conflicts I use column aliases. But that is prone to errors if there a lot of columns. Also, some ORMS require to use hardcoded or dynamic prefixes to column names. Is there automatic way to rename columns in such a way that all columns from T22 start with “c_t22_” and all columns from T23 start with “c_t23_”?

select T1.id,
       T1.p1, T21.name as p1_name,
       T1.p2, T22.name as p2_name,
       T1.p3, T23.name as p3_name
from T1
join T2 as T21 on T1.p1 = T21.id,
join T2 as T22 on T1.p2 = T22.id,
join T2 as T23 on T1.p3 = T23.id


Get this bounty!!!

#StackBounty: #php #laravel #sqlite Laravel 7, SQLSTATE[23000]: Integrity constraint violation: 19 NOT NULL constraint failed when tryi…

Bounty: 50

I have three tables, User, Company and Department, with their respective models and factories.

I created a test where I’m adding the relationship:

// MyTest.php
$user = factory(User::class)->create();

$company = factory(Company::class)->make();
$company->user()->associate($user);
$company->create(); // it fails here because of NOT NULL constraint, companies.user_id

$department = factory(Department::class)->make();
$department->company()->associate($company);
$department->create();

I get the following error: Integrity constraint violation: 19 NOT NULL constraint failed: companies.user_id (SQL: insert into "companies" ("updated_at", "created_at") values (2020-03-10 07:27:51, 2020-03-10 07:27:51))

My table schema is defined like this:

// users
Schema::create('users', function (Blueprint $table) {
    $table->id();
    $table->string('name');
    $table->string('email')->unique();
    $table->timestamp('email_verified_at')->nullable();
    $table->string('phone');
    $table->integer('user_type');
    $table->string('password');
    $table->rememberToken();
    $table->timestamps();
});

// companies
Schema::create('companies', function (Blueprint $table) {
    $table->id();
    $table->foreignId('user_id')->constrained()->onDelete('cascade');
    $table->string('name');
    $table->string('contact_email');
    $table->string('contact_phone');
    $table->timestamps();
});

// departments
Schema::create('departments', function (Blueprint $table) {
    $table->id();
    $table->foreignId('company_id')->constrained()->onDelete('cascade');
    $table->string('name');
    $table->string('contact_email');
    $table->string('contact_phone');
    $table->timestamps();
});

It is my understanding that there should be no NULL-values in SQL-tables, which is why I am deliberately trying to avoid ->nullable() in my migrations. Especially for foreign keys like these.

EDIT:

I tried doing it this way, I also made a pivot table for users_companies. Now I can attach a company, but I’m still getting an SQL-error when doing the test this way:

$user = factory(User::class)->create();
$company = factory(Company::class)->create();

$user->companies()->attach($company);
$company->departments()->create([
    'name' => 'Department 1',
    'contact_email' => 'department1@example.test',
    'contact_phone' => '95281000',
]);

This also fails with the error stated below:

$company = factory(Company::class)->create();
$company->departments()->save(factory(Department::class)->make());

The error is this: Integrity constraint violation: 19 NOT NULL constraint failed: departments.company_id (SQL: insert into "departments" ("name", "contact_email", "contact_phone", "company_id", "updated_at", "created_at") values (Department 1, department1@example.test, '123456789', ?, 2020-03-11 07:59:31, 2020-03-11 07:59:31)).

CompanyFactory.php

<?php

/** @var IlluminateDatabaseEloquentFactory $factory */

use AppCompany;
use FakerGenerator as Faker;

$factory->define(Company::class, function (Faker $faker) {
    return [
        'name' => 'Company 1',
        'contact_email' => 'company@example.test',
        'contact_phone' => '123456789',
    ];
});

Factories

DepartmentFactory.php

<?php

/** @var IlluminateDatabaseEloquentFactory $factory */

use AppDepartment;
use FakerGenerator as Faker;

$factory->define(Department::class, function (Faker $faker) {
    return [
        'name' => 'Department 1',
        'contact_email' => 'department1@example.test',
        'contact_phone' => '123456789',
    ];
});


Get this bounty!!!

#StackBounty: #android #sqlite #android-room #database-performance One big database versus many small databases

Bounty: 50

My App deals with several similar datasets. That is, they are stored in the same tables, but different data. The user may create more datasets. In any case, these datasets are guaranteed to be disjunct. There will never be any data in one dataset linked somehow to data in another dataset.

I was wondering, would it be better to have a dedicated database for each dataset instead of having all the data in one big database?

I would expect lookup times to improve, if the user works on a smaller database. Is there a rule of thumb, how many entries a database (or table) can hold before I should worry about lookup times?

One drawback I can think of is that opening a database creates some overhead. However, I don’t expect the user to switch datasets frequently.


Get this bounty!!!