FEATURE | UPDATE MANY

[kingabzpro]

✅ Describe your idea or issue

Let suppose I want to update the entire table or multiple rows of the table, do I just run the loop? I think we can create some sort of function where it updates multiple columns and rows.

[v1a0]

@kingabzpro maybe you looking for OR argument for insertmany method?

many_values = [[1,2], [3,4], [5,6]]

db.insertmany('table', many_values, OR=REPLACE)

I'm just don't really understand how to create update for many_values. Just because it'll overwrite each other if you set WHERE argument.

I realized it when I already coded it XD

Or each one list of values need some sort of pointer like

values = [
    ["name", "Alex", WHERE, 'id', 123],
    ["name", "Bon", WHERE, 'id', 321],
]

db.updatemany(
    'mytable',
    values
)

So I guess structure like this is kind of wired 😅

[kingabzpro]

db.insertmany('table', many_values, OR=REPLACE)

This doesn't work.

[v1a0]

🤔 Strange..

[v1a0]

FIXED (since v0.1.9.9)!

So i fixed .insertmany + OR=REPLACE and added .updatemany method. Now you can use it like this:

db.updatemany('t6', dataset)

# OR  

db.insertmany('t6', dataset, OR=REPLACE)

Small speed test

from time import time

db = SQLite3x(path='test.db')

db.connect()

db.create_table(
    't6',
    {
        'id': [INTEGER, UNIQUE, NOT_NULL],
        'val': [TEXT, DEFAULT, 'def_val']
    },
    IF_NOT_EXIST=True
)

data1 = [[x, 'hi'] for x in range(100_000)]
data2 = [[x, 'bye'] for x in range(100_000)]

t = time()

db.insertmany('t6', data1)

print(time() - t)  # 0.2731347084 sec - insert 100,000 values 

t = time()

db.insertmany('t6', data2, OR=REPLACE)

print(time() - t)  # 0.3724225811 sec - update 100,000 values 

t = time()

db.updatemany('t6', data2)

print(time() - t)  # 0.3598933219 sec - update 100,000 values 

Thank you, this issue revealed many other bugs of legacy versions that fixed now.

[kingabzpro]

Let me just update the article too.

[kingabzpro]

you don't have this? working?

[kingabzpro]

I am getting this error

Insert many with OR

Update many

Insert many

My table structure

vaccine_db.create_table (
        name='Total', 
        columns={
                "Country": [TEXT, UNIQUE],   
                "Doses_Administered": INTEGER, 
                "Doses_per_1000": REAL,
                "Fully_Vaccinated_Population": TEXT,
                "Vaccine_Type_Used": TEXT                 
                }
                )

My data structure

array([['World', 2415712510, 318.1, '9.7%', nan],
       ['United States', 310645827, 949.5, '44.3%',
        'Johnson&Johnson, Moderna, Pfizer/BioNTech'],
       ['India', 252760364, 186.9, '3.5%',
        'Covaxin, Covishield, Oxford/AstraZeneca'],
       ['Brazil', 78906225, 376.7, '11.3%',
        'Oxford / AstraZeneca, Oxford/AstraZeneca, Pfizer/BioNTech, Sinovac']])

[kingabzpro]

I am going back to 0.1.9.6 this update is causing more problems

[v1a0]

I'm sorry,

I guess problem might be due to numpy.array() object

Can you convert numpy.array to classic python list ?

Is insermany work with numpy.array() for oldest versions?

UPD:

oh yeah, here is the problem. The error raise when you trying call __bool__ method for numpy.array()

I'll fix this tomorrow, just need change one line from if args to if len(angs) > 0

[v1a0]

One more update, sqllex v0.1.9.10

from numpy import array, nan
from sqllex import SQLite3x, TEXT, UNIQUE, INTEGER, REAL

data = array([['World', 2415712510, 318.1, '9.7%', nan],
              ['United States', 310645827, 949.5, '44.3%',
               'Johnson&Johnson, Moderna, Pfizer/BioNTech'],
              ['India', 252760364, 186.9, '3.5%',
               'Covaxin, Covishield, Oxford/AstraZeneca'],
              ['Brazil', 78906225, 376.7, '11.3%',
               'Oxford / AstraZeneca, Oxford/AstraZeneca, Pfizer/BioNTech, Sinovac']])

vaccine_db = SQLite3x('numpy_test.db')

vaccine_db.create_table(
    name='Total',
    columns={
        "Country": [TEXT, UNIQUE],
        "Doses_Administered": INTEGER,
        "Doses_per_1000": REAL,
        "Fully_Vaccinated_Population": TEXT,
        "Vaccine_Type_Used": TEXT
    },
    IF_NOT_EXIST=True
)

vaccine_db.updatemany('Total', data)

Hopefully this is finally fixed now. Thanks for your patience.

[kingabzpro]

I have made an update and its working smooth here