only index column supported?

wanghaisheng commented 3 years ago

same db and table with 6 records in it ,post_id is index post_link is not ,

    db=context['db']
    tables=db.get_table_names()
    print('current operating db:',tables)

    for t in tables:
        list = db.select_all(t)  # -> list of 2 items
        # print('------',t,'---------',list)
        if t=='scrape':
            for i in list:
                if i['post_link']==post_link:

                    print('-------------','table',t,'has record\n',i['post_id'])

print is all the result expected

option2

    tablename ="scrape"    
    # print('check link exist',post_link,db.select(tablename, "post_link==?",post_link))
    try:
        count = len(db.select(tablename, "post_link==?",post_link))
        if count>0:
            print('found exising record',count,'--,',post_link)
            records=db.select(tablename, "post_link==?",post_link)
            for r in records:
                print('!!!!!!!!!',r['post_id'])
            return True
        else:
            return False
    except:
        return False

constanly say False. am i mistaken? what if i need to know by whether the same post_link is there

almarklein commented 3 years ago

Could it be that there is an exception raised somewhere in the try block, which causes False to be returned?

wanghaisheng commented 3 years ago

just give you example

{'post_id': 'ymE2_UDkS5-8y3yHjxOXFw', 'taskid': 1631860217, 'updatetime': 1631870181, 'pipelineid': 1631860217, 'sub': 'trashy', 'subnsfw': False, 'post_link': 'https://v.redd.it/gi6ws11r2o271', 'status': 1, 'post_title': 'Wait for it', 'submission_id': 'npvoqa', 'postnsfw': True, 'reddit_post_file': 'data\\raw\\trashy\\ymE2_UDkS5-8y3yHjxOXFw\\ymE2_UDkS5-8y3yHjxOXFw.json', 'image_post_file': 'image_post_file', 'video_post_file': 'data\\raw\\trashy\\ymE2_UDkS5-8y3yHjxOXFw\\videos\\DASH_720.mp4', 'post_type': 'video', 'url': 'https://v.redd.it/gi6ws11r2o271/DASH_720.mp4?source=fallback', 'update_time': 1631878367, 'sceneoutputdir': 'data\\raw\\trashy\\tmp\\ymE2_UDkS5-8y3yHjxOXFw\\scenes\\', 'commentwithoutsound': 'data\\raw\\trashy\\tmp\\ymE2_UDkS5-8y3yHjxOXFw\\comments_videos\\ymE2_UDkS5-8y3yHjxOXFw_c_nosound.mp4', 'commentvideo': 'data\\raw\\trashy\\tmp\\ymE2_UDkS5-8y3yHjxOXFw\\comments_videos\\ymE2_UDkS5-8y3yHjxOXFw_comments.mp4', 'thumbtitlevideo': 'data\\raw\\trashy\\tmp\\ymE2_UDkS5-8y3yHjxOXFw\\thumb_videos\\ymE2_UDkS5-8y3yHjxOXFw_thumb.mp4', 'postvideo': 'data\\raw\\trashy\\tmp\\ymE2_UDkS5-8y3yHjxOXFw\\post_videos\\ymE2_UDkS5-8y3yHjxOXFw_post.mp4', 'uploadmp4': 'data\\raw\\trashy\\tmp\\ymE2_UDkS5-8y3yHjxOXFw\\upload_videos\\ymE2_UDkS5-8y3yHjxOXFw.mp4', 'commentvideodir': 'data\\raw\\trashy\\tmp\\ymE2_UDkS5-8y3yHjxOXFw\\comments_videos', 'framedir': 'data\\raw\\trashy\\tmp\\ymE2_UDkS5-8y3yHjxOXFw\\comments_videos\\frame', 'ttsdir': 'data\\raw\\trashy\\tmp\\ymE2_UDkS5-8y3yHjxOXFw\\comments_videos\\sound', 'titlevideodir': 'data\\raw\\trashy\\tmp\\ymE2_UDkS5-8y3yHjxOXFw\\thumb_videos', 'postvideodir': 'data\\raw\\trashy\\tmp\\ymE2_UDkS5-8y3yHjxOXFw\\post_videos', 'uploaddir': 'data\\raw\\trashy\\tmp\\ymE2_UDkS5-8y3yHjxOXFw\\upload_videos', 'scenepath': 'data\\raw\\trashy\\tmp\\ymE2_UDkS5-8y3yHjxOXFw\\scenes\\\\ymE2_UDkS5-8y3yHjxOXFw.txt', 'scenetext': 'Sex AND violence! In one little clip!Can\'t even fuck in broad daylight anymore without being viciously attacked by bikersMan thought he was gonna regain his consciousness in mid-pump and keep pumpingToo bad that\'s the only way he can finish nowTbh, I thought he was going to park his bike\'s front wheel in the man\'s crackJust think if that was you and you were about to climax pull out and let it go and then bam get hit by a bike and a child support orderA heavy bass line is my kind of silenceliterally bonkedI was confused by the "wait for it" considering he was balls deep from the start, didn\'t really know how this could get worse to blur the mans faceI bet he\'s feeling tiredNothing kills the mood like a bike to the back of the headI believe that\'s called an Everlasting Donkey Punch*Rigging a bike on a string over his bed*  "Okay babe just hear me out"Man blue balls and a mild concussion  Ouch town, population you broPut it between the crack and start cracking on the pedals!!B-b-b-b-b-bonkers', 'scenetuple': [['besaolli', 'Sex AND violence! In one little clip!', 'bg_black'], ['Phobix', "Can't even fuck in broad daylight anymore without being viciously attacked by bikers", 'bg_black'], ['shino1111', 'Man thought he was gonna regain his consciousness in mid-pump and keep pumping', 'bg_black'], ['plumbusinsuranceltd', "Too bad that's the only way he can finish now", 'bg_black'], ['FrannyyU', "Tbh, I thought he was going to park his bike's front wheel in the man's crack", 'bg_black'], ['Scrambles420', 'Just think if that was you and you were about to climax pull out and let it go and then bam get hit by a bike and a child support order', 'bg_black'], ['Big_Burg', 'A heavy bass line 
is my kind of silence', 'bg_black'], ['stoopid_dipnut', 'literally bonked', 'bg_black'], ['Blahvocado', 'I was confused by the "wait for it" considering he was balls deep from the start, didn\'t really know how this could get worse', 'bg_black'], ['CreativeUsernameUser', 'to blur the mans face', 'bg_black'], ['RetrogradeIntellect', "I bet he's feeling tired", 'bg_black'], ['Here_In_Yankerville', 'Nothing kills the mood like a bike to the back of the head', 'bg_black'], ['TrueBlueShabadoo', "I believe that's called an Everlasting Donkey Punch", 'bg_black'], ['wtfVlad', '*Rigging a bike on a string over his bed* "Okay babe just hear me out"', 'bg_black'], ['Vandy1358', 'Man blue balls and a mild concussion Ouch town, population you bro', 'bg_black'], ['Scrambles420', 'Put it between the crack and start cracking on the pedals!!', 'bg_black'], ['amideit', 
'B-b-b-b-b-bonkers', 'bg_black']], 'scenestatus': 1, 'framenum': 17, 'ttsstatus': 0, 'ttssteps': []} 1
Traceback (most recent call last):
  File "D:\Program Files\anaconda3\lib\site-packages\itemdb.py", line 385, in select
    cur.execute(f"SELECT _ob FROM {table_name} WHERE {query}", args)
sqlite3.OperationalError: no such column: status

    db = context['db']
    tablename = "scrape"
    postlist = []
    t = db.select_all('scrape')  # -> list of 2 items

    for i in t:
        if i['status'] == 1:
            print('------',t,'---------\n',i,i.get('scenestatus'))
    postlist.extend(db.select(tablename, "status==?", 0))

@almarklein I can not find what cause this

almarklein commented 3 years ago

Sorry, but I cannot see what's going wrong from these code samples alone. Please try to confine your use-case into one or two code-samples that are self-contained, by which I mean:

A sample of code, or maybe two, that should be ran after each-other.
That demonstrates your problem.
That other people can run on their machine as well.

When you do that, my experience is that either of these happen:

During making this example you find out what your problem was and are able to fix it.
I can reproduce your problem, and I can suggest how to fix it.
There's a bug in itemdb and I can work on fixing that.

wanghaisheng commented 3 years ago

all is about unique key scenestatus is not .post_id is for unique key you can do the following but other keys are not. those following examples are all not working as supposed to be

        count = len(db.select(tablename, "post_link==?",post_link))
    postlist.extend(db.select(tablename, "status==?", 0))

almarklein commented 3 years ago

So any field names provided to ensure_table are indexed. These are the field that you can do queries on. Unique items are indexed and must be unique.

Ok, here's an example that you can run. Perhaps you can provide a modified version that shows your problem?

import itemdb

# Create in-memory database
db = itemdb.ItemDB(":memory:")
db.ensure_table("mytable", "!id", "age")

# Put some items in
with db:
    db.put_one("mytable", id=1, age=20, name="Jill")
    db.put_one("mytable", id=2, age=30, name="John")
    db.put_one("mytable", id=3, age=40, name="Jane")
    db.put_one("mytable", id=4, age=50, name="Jack")

# Now we can get items
print(db.count("mytable", "age > ?", 15))  # 4
print(db.count("mytable", "age > ?", 35))  # 2
print(db.select_one("mytable", "id == ?", 1))  # {'id': 1, 'age': 20, 'name': 'Jill'}
print(db.select_one("mytable", "age == ?", 30)) # {'id': 2, 'age': 30, 'name': 'John'}

# But this raises an IndexError.
# If you add "name" to the ensure_table() call, this will work.
print(db.select_one("mytable", "name == ?", "John"))

wanghaisheng commented 3 years ago

I will try to give you a real example to reproduce my problem what you show is in doc that i know.

almarklein / itemdb

only index column supported? #8