databases

[pliant pendant]

SELECT s.folder_name
FROM SFW_AnimeArt_img s
WHERE image_id = $1
INNER JOIN SFW_AnimeArt_author a
ON s.folder_name = a.folder_name

why isnt this working hmm

[harsh pulsar]

what's not working about it

(also if this is postgres you can save yourself some typing and write USING (folder_name) instead of ON s.folder_name = a.folder_name)

oh

WHERE comes after JOIN

order matters in sql

[pliant pendant]

i see

yeah now it works, thanks

also i wonder

from thread above

whats best

aiopg or asyncpg

[harsh pulsar]

asyncpg is theoretically better since it uses the postgres binary API instead of sending text over a socket

but aiopg is probably better supported

[pliant pendant]

which to prefer?

i mean i'll undoubtedly run everything on linux

[harsh pulsar]

i use asyncpg because i don't have a reason not to use it

that said magicstack is a weird company, i don't know how they make money, and i'm afraid that one day they will disappear

[pliant pendant]

ah thats sad

but they seem to be alive on tutor

[harsh pulsar]

yeah

[pliant pendant]

teaching startups

getting them to work

[harsh pulsar]

oh thats good

cause afaik edgedb is in alpha still

i guess they make money consulting

[pliant pendant]

yeah probs

oh one question

how do i get the results...

from asyncpg

[harsh pulsar]

wdym

[pliant pendant]

i get the result

but it says like

record row

info is in there

[harsh pulsar]

show the output?

[pliant pendant]

[<Record row=('miyasaka_miyu', None, None, None)>]

dont care about the none

its me who havent inputed manual information

[harsh pulsar]

check out the docs, evidently they have their own data type

[pliant pendant]

i read the docs

from what i seen...

[harsh pulsar]

probably you can do [0] on it?

[pliant pendant]

returned aboslutely no difference

yes

actually

from [] to just record row

so

results[0][0]

to get the results

and results [0][0][0] to get first item in results

this is ridiculous

[harsh pulsar]

results is evidently a list of Record objects

[pliant pendant]

actually thats true

[harsh pulsar]

https://magicstack.github.io/asyncpg/current/api/index.html#record-objects

[pliant pendant]

is there a way to return something different

or is the record objects obligatory

[harsh pulsar]

its obligatory but it has all the features you might want

i prefer it to a plain tuple

or dict

[pliant pendant]

but how am i supposed to send it over

from my server to API user

[harsh pulsar]

what data format are you using in the api?

[pliant pendant]

{"item_name" : "text_item", "item_name2" : "text_item2", ...}

wheras itemname is the column name

[harsh pulsar]

looks like you can do dict(record.items())

[pliant pendant]

hmmmmmmmm

it just does

yeah i mean it wont work good enough

is there a way to make it possible trough sql

[harsh pulsar]

it wont work good enough

what do you mean

[pliant pendant]

i solved it...

cause columns arent displayed

instead i made premade keys variable with a list of key values

then i use zip

[harsh pulsar]

did you try .items()?

[pliant pendant]

it returned a dict

with row

being the only dict item

with a tuple in it

[harsh pulsar]

you used .items() on the row itself?

[pliant pendant]

yes

no wait that didnt work

you couldnt do .items().items()

[harsh pulsar]

wait thats weird

https://magicstack.github.io/asyncpg/current/api/index.html#asyncpg.Record.items

it says Record.items() is supposed to return an iterator over (field, value) pairs

[dawn pulsar]

Is there an easy fix for this?

@harsh pulsar You're incredibly smart, any ideas?

[harsh pulsar]

none whatsoever

personally i'd google it

or just not use that program

[dawn pulsar]

I've google it but their all outdated ):
Thanks for the help anyway

[dawn pulsar]

I fixed it woo

[torn sphinx]

I need advice.. for building the next big query

basically.. I'm not allowed to use public cloud

so, trying to build something scalable for fast querying

[echo finch]

import sys, gspread
from oauth2client.service_account import ServiceAccountCredentials
scope = ["https://spreadsheets.google.com/feeds","https://www.googleapis.com/auth/drive"]
credentials = ServiceAccountCredentials.from_json_keyfile_name("./json/credentials.json", scope)
google = gspread.authorize(credentials)
usernamePasswordDatabase = google.open("usernamePassword").sheet1

I need some help
I dont want to get the authenticate from a json file
I need to authenticate within the program
I cant leave a json file containing the private key
Can someone help out
I am really stuck

[harsh pulsar]

@torn sphinx what kind of query load are you actually expect

Lots of queries per second? Big aggregates? Etc

[echo finch]

I honestly give up

I am so stuck

[harsh pulsar]

@echo finch unfortunately I'm not sure what you're asking

Which oauth lib is that

[echo finch]

oauth2client @harsh pulsar

[rich trout]

@echo finch you can use ServiceAccountCredentials.from_json_keyfile_dict and pass in your own credentials dictionary

https://blog.authlib.org/2018/authlib-for-gspread May also help, as the oauth2client library is deprecated

Speaking of which, I have my own projects using gspread that I ought to make sure I have plans to migrate if things stop working

[frozen osprey]

Hey. What might not be working here?

await self.db.inventory.update_one(
        {"user": self.user_id},

        {"$set": {f"cards.{index}.deck": True}}
     )

I have a list in doc looking like this:

cards: [
    {
        ...
        "deck": False
    }
]

I wanted to change deck to True. index is the index of this dict in cards

This is the structure of the document

{
            "user": 1234,

            "money": 0
            "inv_max": 100,
            "cards": [
                {
                    "name": "somename",
                    "power": 11,
                    "health": 10,
                    "rarity": 'bronze'
                    "deck": False,
                }
            ],

            "packs": {
                "bronze": 0,
                "silver": 0,
                "gold": 0,
                "diamond": 0,
                "mythical": 0
            }
}

In cards array I'll store data about cards this user has

[tacit dagger]

hi. i want to inner join my covers table pic to the albums table id in my db. how can i do that? so far i wrote this statement but won't work.

"SELECT covers.pic, albums.name FROM covers INNER JOIN albums ON covers.album_id=albums._id"```

db schema is:

```sql CREATE TABLE artists (
_id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL
);
CREATE TABLE sqlite_sequence(name,seq);
CREATE TABLE IF NOT EXISTS "albums" (
_id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL,
artist INTEGER,
year INTEGER
);
CREATE TABLE IF NOT EXISTS 'covers' ('album_id' integer primary key, 'pic' blob);

[echo finch]

@rich trout im going to try that now

I really appreciate this

[harsh pulsar]

@tacit dagger you have a lot of syntax issues

you're mixing up " and '

actually that might be the only issue

in general you need to share an error traceback as well as your python code

[tacit dagger]

sqlite> .schema
CREATE TABLE `artists` (
    `_id`    INTEGER PRIMARY KEY AUTOINCREMENT,
    `name`    TEXT NOT NULL
);
CREATE TABLE sqlite_sequence(name,seq);
CREATE TABLE `albums` (
    `_id`    INTEGER PRIMARY KEY AUTOINCREMENT,
    `name`    TEXT NOT NULL,
    `artist`    INTEGER,
    `year`    INTEGER
);
CREATE TABLE `covers` (
    `album_id`    INTEGER,
    `pic`    BLOB,
    PRIMARY KEY(`album_id`)
);

[echo finch]

@rich trout You are Jesus man thanks so much

I would of been stuck without your help

[tacit dagger]

i changed it. my problem is that it inserts all the pics, but i want to filter them according to album_id.

code is this:

def readImage(self):
        try:
            covers = self.cur.execute("SELECT covers.pic, albums.name FROM covers INNER JOIN albums ON covers.album_id=albums._id")
            for cover in covers:
                data, *_ = cover
                ba = QByteArray(data)
                base64 = ba.toBase64().data().decode()
                self.ui.textBrowser.insertHtml(
                    """<img height="200" width="200" src="data:image/png;base64,{}"/>""".format(base64))
        except TypeError:
            self.ui.textBrowser.insertPlainText("No cover available")

[heavy basin]

Hi, I'm working with SQLalchemy and am trying to form a query that limits a 1:many relationship to the most recent entry (highest row id ), inside of several joins. Here is an example showing how the models are setup: https://paste.pythondiscord.com/tifimozuda.py

Basically I want 1 and only one address for each user, the most recent address, and then to get a count of how many of each type of pet that user has at that address. Currently I'm getting all the pets from all the addresses for the users, so I'm having trouble limiting it to 1 address for each user.

[azure torrent]

Hi. I'm running a small website using SQLite3 as a back end database. The number of users is quite light - around 100, and operations are very basic.

What are some bright lines I should look for in switching from SQLite3 to Postgres?

[midnight verge]

@azure torrent why do you want to switch to PG ? The only acceptable answer is

After having deeply analyzed it, my app cannot properly match my expectations because of SQLite

[azure torrent]

Not really, but I don't want people to be locked out because someone else is doing a database update. From what you are saying, it's fine to just stick with SQLite3, is that correct?

I prefer to do that because it's less work.

I have no tech motivation to switch.

[midnight verge]

@azure torrent Why would people be locked out ?

[azure torrent]

My understanding is that with SQLite3 two people can't access the db at the same time, right?

[midnight verge]

Yeah, at least for writes

But is that causing performance issues atm ?

Does it make your app unacceptably under-performant?

• yes -> migrate
• no -> do not migrate

[azure torrent]

That's good advice, thanks.

Kinda obv, I guess.

[unborn sentinel]

For a quick clarification on that, a large number of users can read from the database at the same time, but only one thing can be written to it at a time

If things come in small batches to the DB, that's not a big problem, and only really matters if you need multiple things updated right then and there or you have a constant, steady stream of things needing to be written

For SQLite, I mean

[pliant pendant]

tag_id_results = []
        prep = await connection.prepare(query.rev_tag_name_query())
        for tag in taglist:
            result = ((await prep.fetch(tag)))

            if result == []:
                return [None, 404]

            else:
                tag_id_results.append(result[0][0])

For asyncpg (PostgreSQL), is there another way to return all results at the same time

could replace fetch with fetchrow i know
but thats not what im looking for

btw sqlite is quite bad due to its lack of multithreadability
not suitable for larger databases, but it's still quite good if you plan to use it for more local database

[midnight verge]

@unborn sentinel the point was, if he's not experiencing performance issues and having identified that they are caused by this, then there is no reason to migrate for now

[unborn sentinel]

Oh right, absolutely

I just can't help but clarify points sometimes

[pliant pendant]

still need help tho

[midnight verge]

@pliant pendant https://docs.python.org/3/library/asyncio-task.html#asyncio.create_task

https://docs.python.org/3/library/asyncio-task.html#running-tasks-concurrently

[pliant pendant]

is that really the only way

to split the threads

psycopg2 had a way to use executemany

asyncpg have it too but it returns None by default

cause its only for insert

[midnight verge]

@pliant pendant my links do not use threads 🤔

[quiet ermine]

Seeing as sqlalchemy doesn't support UUIDs greatly, what's a good way to not have a counting integer for primary keys?

As in not 1,2,3,4,5,6 for users

[pliant pendant]

@midnight verge i know but why would it be faster

@quiet ermine 1 sec

[midnight verge]

@pliant pendant because you'll make multiple resquests asynchronously, instead of waiting for I/O

(within your function)

[pliant pendant]

@quiet ermine import string and uuid, then use uuid.uuid4().hex to get an UUID, then use "PRIMARY KEY" statemen in sql

@midnight verge like opening multiple tunnels?

@midnight verge its already in an async code doe

[midnight verge]

@pliant pendant your code is async but only within your program as a whole, in the code you sent here the fetch requests to your db will be sent sequentially, after having retrieved the previous one result, which is not asynchronous in that context

[pliant pendant]

#TODO optimize
async def animeart_search_by_tags(image_type, taglist):
    return_keys = ["image_id"]

    query = await check_image_type(image_type)
    if query == None or len(taglist) > 10:
        return [None, 400]

    taglist = [tag.lower() for tag in taglist]
    print(taglist)


    async with datastore_prep.getConnection_async("test") as connection:

        # Find ALL tag_id linked to ALL tag_names
        tag_id_results = []
        prep = await connection.prepare(query.rev_tag_name_query())
        for tag in taglist:
            result = ((await prep.fetch(tag)))

            if result == []:
                return [None, 404]

            else:
                tag_id_results.append(result[0][0])

        
        # Find ALL images linked to ALL tag_ids
        result = []
        prep = await connection.prepare(query.image_id_from_tag_id())

        first_run = True
        for tag_id in tag_id_results:
            curr_raw_result = (await prep.fetch(tag_id))
            curr_clean_result = [res[0] for res in curr_raw_result]


            #Saves first round, then get IN values on the other rounds
            if not first_run:
                list1 = set(result)
                list2 = set(curr_clean_result)

                result = list(list1 & list2)

                if result == []:
                    return [None, 404]
                
            else:
                result = curr_clean_result
                first_run = False


        return [dict(zip(return_keys, result)), 200]

[midnight verge]

hence the point of using the asyncio's functions I sent above

hence the point of using the asyncio's functions I sent above

[pliant pendant]

yes i think i understand what you mean but i dont see the point opening multiple tunnels

i think you say that i'll create multiple asyncio awaits

to increase the speed

but in the psycopg2 you could wrap all the items into a list and let it request all the values in a single run

rather than iterating it trough

doe i'll use your solution @midnight verge if no other solutions appear

[midnight verge]

no, multiple await will not speed up everything, because they will all wait for the query response anyway, they'll just make the program switch context as a whole if another's available

but that function itself, will not run the requests asynchronously, in the function's context

[pliant pendant]

@midnight verge someone gave me the solution using ANY() statement

[midnight verge]

Yeah, I did not look at the SQL query, I was just talking about Python code

[pliant pendant]

ahh i see

no worries doe

the gather function is gonna definetily be useful in future

also the create_task feature

thanks for the facts ``

[quiet ermine]

So

SQLAlchemy gives a really nice example for commonly-used uuids

And doesn't actually implament it, only uses it as a tutorial

https://docs.sqlalchemy.org/en/13/core/custom_types.html#backend-agnostic-guid-type

[dawn pulsar]

I have lots of rows with data, and I have a table with an integer, I'd like to see if the amount of rows in the first table exceeds the number in the second, I tried this but no luck
"SELECT plots.owner_name FROM plots, plot_limits WHERE count(*) > plot_limits.uuid AND plots.owner=plot_limits.uuid GROUP BY owner_name"

mysql.connector.errors.DatabaseError: 1111 (HY000): Invalid use of group function

[harsh pulsar]

@dawn pulsar first of all, plot_limits is a table name, not a column name. what's the column name you want?

[dawn pulsar]

uuid

Does that look better?

[harsh pulsar]

that doesn't make much sense, you're comparing a count to a UUID?

SELECT
    plots.owner_id, plots.owner_name
FROM
    plots, plot_limits
WHERE
    plots.owner_id = plot_limits.uuid
GROUP BY
    plots.owner_id
HAVING
    count(*) > ???

@dawn pulsar

not sure what's supposed to go where the ??? is, but that's the right technique

[dawn pulsar]

Oh wait

[dawn pulsar]

@harsh pulsar

mysql.connector.errors.ProgrammingError: 1054 (42S22): Unknown column 'plot_limits.massive' in 'having clause'

Buttt

Well I did this (I did something wrong)

"SELECT \
             plots.owner, plots.owner_name \
             FROM \
             plots, plot_limits \
             WHERE \
             plots.owner = plot_limits.uuid \
             GROUP BY plots.owner \
             HAVING \
             count(*) > plot_limits.massive"

But when I print cursor descriptions, I get this: ['uuid', 'basic', 'large', 'massive']

[torn sphinx]

await self.client.pg_con.execute("UPDATE fun SET money = $1 AND time = $2 WHERE member = $3", user["money"] + 250, datetime.datetime.utcnow() + datetime.timedelta(minutes=1), ctx.author.id)```

Command raised an exception: DatatypeMismatchError: column "money" is of type bigint but expression is of type boolean

[harsh pulsar]

@torn sphinx you effectively wrote SET money = ($1 AND time = $2), which of course is boolean

use , to separate

[torn sphinx]

ok

[harsh pulsar]

SET money = $1, time = $2

[torn sphinx]

oh

[harsh pulsar]

@dawn pulsar try this

SELECT
    plots.owner_id,
    plots.owner_name,
    count(*) n_plots,
    /* plot_limits.massive should be constant within the group, so min() will just take the first one */
    min(plot_limits.massive) massive
FROM
    plots, plot_limits
WHERE
    plots.owner_id = plot_limits.uuid
GROUP BY
    plots.owner_id
HAVING
    n_plots > massive

[torn sphinx]

ty salt 👍

[pliant pendant]

i'd want to make an sql statement

into a table that has (tag_id, image_id)

i want to search for lets say 3 tags (tag_id_1, tag_id_2, tag_id_3)

i want to return all image_id's that matches the 3 tags

as in

(image_id, tag_id_1)

(image_id, tag_id_2)

(image_id, tag_id_3)

the database is structured in toxi method

https://stackoverflow.com/questions/1810356/how-to-implement-tag-system

[pliant pendant]

# Returns a list of images matching all tag results
async def search_by_tags(image_type, taglist):

    query = await datastore_prep.check_image_type(image_type)
    if query == None or len(taglist) > 10:
        return [None, 400]

    # all tags are lower by default
    taglist = [tag.lower() for tag in taglist]


    async with datastore_prep.getConnection_async("test") as connection:

        # Find ALL tag_id linked to ALL tag_names
        tag_id_results = []
        result = await connection.fetch(query.rev_tag_name_query(), [[tag] for tag in taglist])
        
        # Returns if one of the tags has 0 connection
        for item in result:
            if item[0] == None:
                return

            else:
                tag_id_results.append(item[0])

        
        # Search images matching all tags
        pre_result = []
        raw_items = await connection.fetch(query.image_ids_from_tag_ids(), tag_id_results)

        for items in raw_items:
            countval = ([item[0] for item in raw_items]).count(items[0])

            if countval == len(taglist):
                pre_result.append(items[0])


        if pre_result != []:
            print("finish")
            return pre_result

        else:
            return None

I solved this by using two queries

one to fetch all tag tag_id's from tag_names

and one to fetch all image_id's matching atleast one of the tags

then i use count see if the results are the same as the amount of tags

is there a way to optimize this further?

[harsh pulsar]

show the queries too

[torn sphinx]

So I have this mongo document

{
    '_id': 2
    'info': {
        'social': 'TW'
        'role': 'builder'
        'status': 'pro'
        'friends': '3'
    }
}

I also have this dictionary

{
    'info': {
        'role': 'contractor'
        'friends': '8'
    }
}

Now given these 2

how can I update the document, replacing only the values in the second dictionary

so in the end it looks like

{
    '_id': 2
    'info': {
        'social': 'TW'
        'role': 'contractor'
        'status': 'pro'
        'friends': '8'
    }
}

pymongo btw

[torn sphinx]

Any help?

[harsh pulsar]

Never used mongo sorry

[torn sphinx]

@harsh pulsar is there a way to add 2 dictionaries together?

that would work

cuz any fields would be overwritten

Found an answer

>>> x = {'a': 1, 'b': 2}
>>> y = {'b': 3, 'c': 4}

>>> z = {**x, **y}

[harsh pulsar]

@torn sphinx yep. in fact i wish they would make & on dicts do that

or +

[torn sphinx]

Yeah

but another issue cropped up

help 6

[torn sphinx]

fggjfkdeksdksdkalkfmmfkrorkokjgfrewoleookjflke0pfkegoirgk03kg3wr4e-0op34we['

[pearl tree]

radgnhddgnhadhgnnfndhfgmhhdndhndgmd

[cobalt cipher]

!e

from collections import Counter
from pprint import pprint
pprint(Counter("fggjfkdeksdksdkalkfmmfkrorkokjgfrewoleookjflke0pfkegoirgk03kg3wr4e-0op34we['"))```

[delicate field]

@cobalt cipher Your eval job has completed.

001 | Counter({'k': 13,
002 |          'f': 7,
003 |          'e': 7,
004 |          'o': 7,
005 |          'g': 6,
006 |          'r': 5,
007 |          'j': 3,
008 |          'd': 3,
009 |          'l': 3,
010 |          'w': 3,
... (truncated - too many lines)

Full output: https://paste.pythondiscord.com/daqucosoko

[pliant pendant]

        BEGIN
        IF EXISTS(
            SELECT user_id
            FROM AnimeArt_userBookmarks
            WHERE user_id = $1)
        THEN
            INSERT INTO
            AnimeArt_userBookmarks(user_id, image_id_list)
            VALUES ($1, $2)

            SELECT image_id_list
            FROM AnimeArt_userBookmarks
            WHERE user_id = $1
        ELSE
            UPDATE AnimeArt_userBookmarks
            SET image_id_list = $2
            WHERE user_id = $1;
        END

Why isnt this SQL statement workin

        IF EXISTS(
            SELECT user_id
            FROM AnimeArt_userBookmarks
            WHERE user_id = $1)
        BEGIN
            INSERT INTO
            AnimeArt_userBookmarks(user_id, image_id_list)
            VALUES ($1, $2)

            SELECT image_id_list
            FROM AnimeArt_userBookmarks
            WHERE user_id = $1
        END
        ELSE
        BEGIN
            UPDATE AnimeArt_userBookmarks
            SET image_id_list = $2
            WHERE user_id = $1;
        END

this wont work either

[pliant pendant]

        INSERT INTO
        AnimeArt_userBookmarks(user_id, image_id_list)
        VALUES ($1, $2)
        
        ON CONFLICT (user_id) DO
            UPDATE AnimeArt_userBookmarks
            SET image_id_list = $2
            WHERE user_id = $1;

still NOTHING

3 hours

huh, i'm drowsy

[harsh pulsar]

@pliant pendant does postgres even support that if/else syntax

[pliant pendant]

@harsh pulsar most likely not, atleast i solved it after 4 long going hours

        INSERT INTO
        AnimeArt_userBookmarks(user_id, image_id_list)
        VALUES ($1, $2)
        
        ON CONFLICT (user_id) DO
            UPDATE
            SET image_id_list = $2
            WHERE AnimeArt_userBookmarks.user_id = $1;

i need to reference WHERE to the table, cause it doesnt do it within the ON CONFLICT statement

[harsh pulsar]

yeah there you go

get comfortable reading the postgres docs

the syntax is a big scary but its very thorough once youre used to it

[pliant pendant]

yeah i'll spend more time on the docs ^^

[tribal furnace]

is there a way to dynamically display all pickle files in a specific folder

[harsh pulsar]

@tribal furnace use glob and look for .pkl?

!d g glob.glob

[delicate field]

`glob.glob`

glob.glob(pathname, *, recursive=False)```Return a possibly-empty list of path names that match *pathname*, which must be a string containing a path specification. *pathname* can be either absolute (like `/usr/src/Python-1.5/Makefile`) or relative (like `../../Tools/*/*.gif`), and can contain shell-style wildcards. Broken symlinks are included in the results (as in the shell).

If *recursive* is true, the pattern “`**`” will match any files and zero or more directories and subdirectories. If the pattern is followed by an `os.sep`, only directories and subdirectories match.

Note

Using the “`**`” pattern in large directory trees may consume an inordinate amount of time.

Changed in version 3.5: Support for recursive globs using “`**`”.

[pliant pendant]

decided to dig deeper and deeper into PostgreSQL

currently i got new ideas

        CREATE TABLE sfw_animeart_author(
        folder_name text PRIMARY KEY NOT NULL,
        Author      text,
        Website     text,
        Category    int);

        CREATE TABLE sfw_animeart_img(
        image_real_id   serial PRIMARY KEY,
        image_id        text NOT NULL
        folder_name     text REFERENCES sfw_animeart_author(folder_name) ON UPDATE CASCADE ON DELETE CASCADE,
        ready           boolean NOT NULL DEFAULT FALSE
        ); 

        CREATE TABLE AnimeArt_tag(
        tag_name    text,
        tag_id      serial PRIMARY KEY
        );

        CREATE TABLE SFW_AnimeArt_tagsearch(
        image_id    int REFERENCES sfw_animeart_img(image_real_id) ON UPDATE CASCADE ON DELETE CASCADE,
        tag_id      int REFERENCES animeart_tag(tag_id) ON UPDATE CASCADE ON DELETE CASCADE
        );

it's for many to many relation (tag & img) however the author will be one to many relation

i wonder if i did correct, so if i delete one author, all images related to it will be removed

and if i delete either an image or a tag the tagsearch references will be removed to it

also should i use foreign key or not?

[limber stone]

I'm totally lost with postgres. All I want is a database that I can create tables in from a d.py bot.

I made a role with my linux username and set a password to it. I created a database.

But now when I try to access it with a similar to this:
https://pythonspot.com/python-database-postgresql/

I get an error:
Error: invalid DSN: scheme is expected to be either "postgresql" or "postgres", got ''

I thought scheme was schema so I created a schema named postgres in the database but apparently that wasn't it.

Also using this instead of psycopg2
https://github.com/MagicStack/asyncpg

Well, I fixed that issue. keywords were wrong and basically everything else

[quiet ermine]

You could use sqlalchemy instead of doing it directly though psycopg2

[lunar flame]

What would be the way to bundle in a tiny database in a python package?

I'm looking to store just one table

currently serve it via a backend and an API, might be smart to bundle it in the package.

Ref: https://github.com/SwagLyrics/swaglyrics-backend/blob/deae012efbb3670179f41e6b0defe1e1adc8c6bc/issue_maker.py#L57

[polar pulsar]

yo can I get some help with SQL and python coding

[ornate isle]

@polar pulsar be more specific and ask the actual question 🙂

@lunar flame why do you want to bundle the data? do you want to be storing it in your git repo as well? will having all this data baked in already be expected behavior by users? (i.e. is this just for your own use or will the server be publicly available by folks who may not want the data)

@lunar flame also does the data change? kinda awkward if new stuff is getting added to the api regularly but it isn't reflected in the package... and similarly it would be awkawrd to have to repackage the app every time you add data

i like to make importing sample data optional, and usually what i'll do is have a command for it that goes and fetches it from somewhere like s3

that way it doesnt clutter your repo, you can update the data dump separately from the app itself, etc. i also would probaby be storing this data as something portable and readable, like json or csv or something rather than a binary sqlite db. but that's just me

[dull scarab]

@timber coral What does your table look like?

[timber coral]

https://i.imgur.com/A0iJhIg.png

[dull scarab]

How did you create the table

[timber coral]

db = sqlite3.connect('members.db')
dbCursor = db.cursor()
dbCursor.execute("""CREATE TABLE Member (
                Member integer,
                Link text,
                ETC""")```

into python console

[dull scarab]

But you're trying to insert into a table named members

dbCursor.execute(f"INSERT INTO members(Member) VALUES({ctx.author.id})")

[timber coral]

oh yeah mb

members is the table name

[dull scarab]

And you shouldn't be using string formatting for your queries

in this case i guess it's "safe" as the user cannot change or set their id, but as a rule of thumb, dont use string formatting to avoid having your database destroyed through a sql injection

Prepared statements is what you want to use.

Nontheless, are you sure your names are all correct?

[timber coral]

yeah it worked when i typed it into the python console

[dull scarab]

where did you open the python terminal

[timber coral]

pycharm

[dull scarab]

is the .db file in your bots directory?

[timber coral]

yep

[dull scarab]

So the file is named members.db, table is named members, and the field you want to set is called Member

[timber coral]

yes

[dull scarab]

If you put a print at the top of your command, does it print.

[timber coral]

yes

[dull scarab]

what if you change your query to select * from members;

[timber coral]

        print('test')
        dbCursor.execute("select * from members;")
        db.commit()
        await ctx.send(f"{ctx.author.id} added to db")```

like this?

[dull scarab]

yeah, does it run then?

[timber coral]

the test prints

[dull scarab]

It wont show you any results, but that's a pretty basic query

[timber coral]

but bot doesnt send anything

[dull scarab]

do you connect to your db anywhere else?

[timber coral]

sqlitestudio

[dull scarab]

in your bot i mean

[timber coral]

nope

[dull scarab]

is that the only place you connect to it

[timber coral]

it is

[dull scarab]

what does your filestructure look like

[timber coral]

https://i.imgur.com/zjjZrBW.png

the right is inside cogs

[dull scarab]

NEWT.py is your bot?

the file you run

[timber coral]

yes

[dull scarab]

could you try doing py db = sqlite3.connect('members.db') dbCursor = db.cursor() res = dbCursor.execute("select * from members;") for row in res: print(row[0])

at the top of the file where you already connect

restart your bot and see if it prints anything

Cause you should have 1 entry in you db right?

[timber coral]

yeah

it printed it out

[dull scarab]

My guess is it's somehow dropped when the cogs are loaded

Try putting it in the cogs init

as self.db and self.cursor

[timber coral]

don't really know how to do that

[dull scarab]

The same as you did

but you prepend self. to it all

inside your class

class Cog:
    def __init__(self, bot):
        self.bot = bot
        self.db = sqlite3.connect(...)
        self.cursor = db.cursor()```

and then use those in your command

see if that works

[timber coral]

discord.ext.commands.errors.ExtensionFailed: Extension 'cogs.ranking' raised an error: ProgrammingError: Cannot operate on a closed database.

[dull scarab]

Well, now you have an error at least

What does you code look like now

[timber coral]

it seems i fixed it

[dull scarab]

Great!

[timber coral]

db = sqlite3.connect('members.db')
dbCursor = db.cursor()

class Ranking(commands.Cog, name='Ranking'):
    def __init__(self, bot):
        self.bot = bot
        self.db = sqlite3.connect("members.db")
        self.dbCursor = self.db.cursor()

    @commands.Cog.listener()
    async def on_ready(self):
        print('Ranking module loaded')

    @commands.command()
    async def ct(self, ctx):
        print('test')
        self.dbCursor.execute("select * from members;")
        self.db.commit()
        await ctx.send(f"{ctx.author.id} added to db")```

[dull scarab]

Remove the first 2 lines

[timber coral]

doesnt work then

NameError: name 'db' is not defined

[dull scarab]

That makes sno sense

[timber coral]

oh

nvm

i have db.close() at the end

[dull scarab]

Yeah, don't do that

[timber coral]

thank you so much

[dull scarab]

np

[vocal jay]

need help with sqlalchemy:

roles_users = db.Table('roles_users',
    db.Column('user_id', db.Integer(), db.ForeignKey('users.id')),
    db.Column('role_id', db.Integer(), db.ForeignKey('roles.id'))
)

class User(db.Model, UserMixin):
    # ...
        roles = db.relationship('Role', secondary=roles_users, backref=db.backref('users', lazy='dynamic'))

I want to get all users with a particular role

mysql, yes -- but this is sqlalchemy so there should be a db agnostic solution

[rich trout]

Wouldn't that just be role.users?

[vocal jay]

I...

[rich trout]

Since that's what you named the backref?

[vocal jay]

I think I need a lot of sleep, probably

[rich trout]

Nw :P

[vocal jay]

omigod thank you SO much

I was forever stuck and I was at the point where I was like "gonna just write this in plain sql"

[pseudo ruin]

Hello. I have application written in Python with MySQL DataBase. I want to convert Python Files to EXE. How can I do this with database?

[harsh pulsar]

You still need a separate database

At best you can switch to sqlite and store the sqlite database in %APPADATA%

[pseudo ruin]

@harsh pulsar So I can not do this with MySQL? I need to write an application with setup.

[harsh pulsar]

How do you plan to run mysql?

You can connect to remote database, sure. But are you going to have users install mysql and run the database server separately?

Maybe you can bundle mysql in your installer somehow but that's far beyond just packaging a python application into an exe

[pseudo ruin]

Ok, thanks for answer

[pliant pendant]

should i open a connection with asyncpg per function run

or would it be more suitable having a global variable connection, so i dont need to open and close connections all the time (it seems to take 60ms just to open a connection)

[civic rover]

I want to execute a schema.sql with psycopg2. It creates some tables and grants ownership to a user. Is there a way to get the user executing the sql? (Because I want to create the tables for the user who executes it)

[dull scarab]

@pliant pendant Open a connection when you initialize your app, or a pool depending on how much you are going to use it concurently.

[pliant pendant]

@dull scarab python is not dependant on multithreading capabilities, if i were to open it on initialization and use it for async

will it still be able to run safetly?

[dull scarab]

You'd open it when you start going async

asyncpg will handle the concurencys with async implementations

[pliant pendant]

do i have to open it trough a global variable within a .py file

and distribute it with functions refering to it?

or is there other ways

[dull scarab]

What are you using it with?

do you have an object that's common for all your files?

[pliant pendant]

hmm

datastore_prep is a common file

that's used to connect each client

@asynccontextmanager
async def getConnection_async(datastore_name):
    connection = await asyncpg.connect(
        user='',
        password='',
        database=datastore_name,
        host='127.0.0.1',
        port="1234")
    
    try:
        yield connection
    finally:
        await connection.close()

@dull scarab

currconnection = await getConnection_async("test")
async def getSharedConnection_async():
    return currconnection

this is how i think i could share it

but it will be executed everytime something runs in the file

[dull scarab]

if you have a common object just attach the conection to it when you create it

else check if it's created in that function before opening the connection

[pliant pendant]

@dull scarab i've came a little further

currentpool = None
@asynccontextmanager
async def pool():
    currentpool = currentpool

    if currentpool != None:
        return (await currentpool.acquire())
        
    else:
        currentpool = await asyncpg.create_pool(
        user='',
        password='',
        database='',
        host='127.0.0.1',
        port="7774")
        return (await currentpool.acquire())

however this one has an issue

RuntimeWarning: coroutine 'pool' was never awaited

this is what gives that error

async with datastore_prep.pool() as connection: ```

it seems to not work... at all

current progress:

[pliant pendant]

fixed

@asynccontextmanager doesnt seems to work with what im doing

wierd

weird

but i gotta go with it i guess

[lunar flame]

@ornate isle If you see the link then you'll notice that the database is communicated to via api calls while the unsupported.txt gets downloaded whenever the app is run. I was thinking of adding an arg that allows the user to get the database locally as well

[spare shuttle]

Hi.
I made a web app using Flask which has a form to be filled by the users. I want to store this data that the users enter.
Which database should I use?
I have never worked with databases, only worked with CSV files and text files to store data
I also want to be able to view the data later, and will also be using this data in another script.

[torn sphinx]

@spare shuttle if you have a previous database knowledge for one kind, you should probably use that
if you don't, then non-relational entries can be stored in Mongo, which is very easy to learn and it has great documentation
if you want this data to be relational later on, you might want to store it in SQL - there are a couple of versions of that, the simplest and most basic is sqlite, it takes basically nothing to set it up. An added benefit to this solution would be that SQL is good to know

[spare shuttle]

@torn sphinx thanks for the advice. I will try mongo first. I will also be learning SQL in my college so perhaps that would help me understand the difference between both

[upbeat lily]

I'd really advise against mongo

The required amount of sql is miniscule for what you're want to do

[spare shuttle]

@upbeat lily what problem will it cause if I use Mongo?

[midnight verge]

@spare shuttle you'll, very very very probably, end-up reinventing SQL but buggier, slower, unsafer, with more work

Stick with RDBMS unless you can't

[spare shuttle]

@midnight verge thanks for your advice. I will then perhaps start with sqlite and read more about the databases

[midnight verge]

Very good idea 👍

[lavish ferry]

hi, i cannot install the postgresql, someone can help me ?

[limber stone]

I know it's a syntax error and "You should know this" but I'm fried. I don't know what's wrong

await con.execute(f"UPDATE threads SET permission_values={permissions} WHERE channel_id={channel.id};")```

returns `PostgresSyntaxError: syntax error at or near "{"`

Now, ignore my bad query structure. But this is the *only* query that is not working. The only thing different about this is that `permissions` is a serialized dict. The permission_values column is a jsonb type.

Even if I change the query to ```python
"UPDATE threads SET permission_values=%s WHERE channel_id=%s;", (permissions, channel.id)``` I get the same error. Just replace `{` with %s

[surreal pebble]

Am i able to use blank database for heroku

as a database

[lament scroll]

Uber n00b here, if I exposed a DB to a website, would it be unwise to show the elements id (so, they order) to said frontend?

as far as I know I should not give details of how many elements the DB has, but I'm wondering how efficient searchs are if the DB has to look up something by an arbitrary name (like YouTube ids)

I'm pretty much asking stuff about database 101

[midnight verge]

@lament scroll thumb rule is showing only required data

[lament scroll]

@midnight verge I assumed so... so if I were working with a form that lets the user pick another user account for something, then what should be shared?

the exact id for that other user?

[midnight verge]

Yes, and name for display, for instance

[lament scroll]

(which is not what the user reads of course)

mmm

would it be okay if that id was from an ordered table?

I find it somewhat troublesome if the id were like 500, the user could know there are another 499 users at least

unless it was encrypted, but I don't know if that's a thing

[midnight verge]

So use UUIDs instead of autoincrement IDs

Yeah you could « encrypt » them with a server-side private key, but that's a lot of absurd overhead imo

Just use UUIDs

[lament scroll]

thanks, I need to read more about this

[jolly stirrup]

the only situation i would use uuids would be if latency is an issue or distributed concerns

otherwise most DBs autoincrement is the general solution

plus the storage and performance advantage

[midnight verge]

@jolly stirrup no performance advantage on PG, non-perceptible storage advante on PG, for instance

If ID fields are your storage bottleneck, you are probably not using the DB the right way

[jolly stirrup]

cool, i dont use PG much, but SQL Server Autoincrement is default

there are more advantages for AI than UUID in general

[midnight verge]

Why ?

[jolly stirrup]

your point earlier is just security through obscurity which is moot

[midnight verge]

It's not, it's just much more resilient design

With UUID
Hide business data ? check
Prevent data deletion by targeting the wrong table? check
Simplify horizontal scaling/data distribution? check

https://www.clever-cloud.com/blog/engineering/2015/05/20/why-auto-increment-is-a-terrible-idea/

Also prevents misusing IDs by considering them a value rather than an identifier

So yeah, just go with UUIDs, unless you really need that autoincrement

[torn sphinx]

on this topic, I heard about this from a python talk a week or so ago: https://hashids.org/python/

[ionic pecan]

i always go for numeric ids, except from the last point your points make no sense to me

[plain radish]

I also use numeric IDs

snowflakes in particular due to it's ability to be always unique even in a multi-server microservice setup while still also being able to be reasonably accurate with datetime sorting

it's efficient

[harsh pulsar]

There's nothing wrong with auto incrementing IDs for internal use imo

But don't expose them to the world

[vernal glade]

can anyone help me create a database and link it with QCalendarWidget as i want to create 4 scheduling options for any day that gets selected, hope that makes sense lol

[vernal relic]

Is Psycopg2 still the most used library for working with PostgreSQL?

[gleaming frost]

I use asyncpg

[vernal glade]

can anyone help with sqlite3

[vernal glade]

i need to figure out how to send data to my database from another file

import os
import sqlite3
from sqlite3 import Error


def create_connection(db_file):
    conn = None
    try:
        conn = sqlite3.connect(db_file)
        return conn
    except Error as e:
        print(e)

    return conn

def main():
    currentDirectory = os.getcwd()
    database = r"dose_sch_database.db"
    # create a database connection
    conn = create_connection(database)

    # create tables
    if conn is not None:
        # create dosage_schedule table
        create_table(conn, sql_create_dosage_schedule)
    else:
        print("Error! cannot create the database connection.")


def create_dosage_schedule(conn, dosage_schedule):
    """
    Create a new task
    :param conn:
    :param task:
    :return:
    """

    sql = ''' INSERT INTO dosage_schedule(Date,Setting,Time,Repeat,Id)
              VALUES(?,?,?,?,?) '''
    cur = conn.cursor()
    cur.execute(sql, dosage_schedule)
    return cur.lastrowid

if __name__ == '__main__':
    main()```

[lament scroll]

I feel I'm lost now

maybe I'm not thinking this out, so I'll try with a different question

(for those who would use numeric IDs) how would you give a user a reference to another user? some secondary and unique key?

considering you'll have to perform a query with that

I'm also not sure what goes in a database anyway

I'm assuming a user profile is dynamically generated by querying the DB after every visit

[pure cypress]

References are done using foreign keys

You can create a column that stores the primary key (presumably the id) of the other user

[lament scroll]

like the path to their picture or their age, but that could be wrong

[pure cypress]

and then make the column have a foreign key constraint

Well this is assuming you are using a relational database

as opposed to like nosql

[lament scroll]

by "reference" I meant just "have some intermediary data so I can find this user later", I didn't mean it in a... "software way", like a reference to an object or a table

just something I can give to the user, that doesn't reveal how many users the DB has

since I've been told I should not put pks in the wild, I assume a non-secret unique username should be fine

[pure cypress]

In that case you can make the primary key be some GUID instead of an auto incremented integer

[lament scroll]

or some secondary key that looks like garbage

[pure cypress]

You could use a secondary key but that seems redundant

[lament scroll]

mmm that's what I've been told 🤔 maybe what I want isn't possible

but hey here goes another question

[pure cypress]

Well what about what I suggested

the GUID

[lament scroll]

how does a service like YouTube does it?

or how do you think

[pure cypress]

No idea

[limber stone]

Why does this: python await con.execute(f"""UPDATE threads SET permission_values='{permissions}'::json WHERE channel_id={channel.id} AND guild_id={channel.guild.id};""")
work fine but when I convert to:python await con.execute("""UPDATE threads SET permission_values='$1'::json WHERE channel_id=$2 AND guild_id=$3;""",(permissions, channel.id, channel.guild.id))
I get a token error at $

[harsh pulsar]

Dont use the quotes maybe

The single quotes around $1

Idk how :: interacts with query params, can you use CAST instead

[limber stone]

I'll try tomorrow. And removeing the quotes makes a complaint about me only passing in one argument instead of 3

[pliant pendant]

with amazonAWS s3 using boto3, is the fastest way to iterate trough one folder to iterate trough all items in a Bucket?

[harsh pulsar]

@pliant pendant maybe just list everything in the bucket and filter as required

[torn sphinx]

I need some help

if I want to write records to mongodb continuously and scale it depending on my capacity requirements

how do I go about it

can I partition by date and move entries before a certain date to another type of storage.. so newer records are easier to query?

the data is essentially log data

so it has timestamps

[harsh pulsar]

Normally you would have some kind of script run every weekend or something like that to export the data and then delete the old data that was just exported

I don't know if mongo specifically has functionality to take care of that

[vernal glade]

can anyone helo me with a sqlite3 database, im struggling with how i update it and delete specific data from a different script, i can post my github and i also have DB Browser (SQLite)

[harsh pulsar]

@vernal glade have you use sql before?

[vernal glade]

no only sqlalchemy

but was a struggle back then too and was over a year ago

[harsh pulsar]

hm. you really need a "getting started with sql and relational databases" resource

im not sure what the good ones are

i'll ask around and get back to you

[vernal glade]

all the tutorials ive seen update the listings in the same script

[harsh pulsar]

that shouldn't be a problem

just connect to the database from the other script, no?

[vernal glade]

yeah but how i cant seem to connect

[harsh pulsar]

how are you doing it?

[vernal glade]

import os
import sqlite3
from sqlite3 import Error


def create_connection(db_file):
    conn = None
    try:
        conn = sqlite3.connect(db_file)
        return conn
    except Error as e:
        print(e)

    return conn

def main():
    currentDirectory = os.getcwd()
    database = r"dose_sch_database.db"
    # create a database connection
    conn = create_connection(database)

    # create tables
    if conn is not None:
        # create dosage_schedule table
        create_table(conn, sql_create_dosage_schedule)
    else:
        print("Error! cannot create the database connection.")


def create_dosage_schedule(conn, dosage_schedule):
    """
    Create a new task
    :param conn:
    :param dosage_schedule:
    :return:
    """

    sql = ''' INSERT INTO dosage_schedule(Date,Setting,Time,Repeat,Id)
              VALUES(?,?,?,?,?) '''
    cur = conn.cursor()
    cur.execute(sql, dosage_schedule)
    return cur.lastrowid

def update_schedule(conn, schedule):
    """
    update priority, begin_date, and end date of a task
    :param conn:
    :param schedule:
    :return: project id
    """
    sql = ''' UPDATE schedule
              SET Time = ? ,
              SET Date = ? ,
              SET Setting = ? ,
              SET Repeat = ?,
              WHERE id = ?'''
    cur = conn.cursor()
    cur.execute(sql, schedule)
    conn.commit()

if __name__ == '__main__':
    main()```

database script

[harsh pulsar]

so do you get an error?

[vernal glade]

 db.update_schedule(conn, (f"Co2 Scheduled for - {date_str} - {co2_str} - {self.repeat_sch()} -"))```

[harsh pulsar]

btw good job for using query parameters 🙂

[vernal glade]

just an example script i modded 😂

[harsh pulsar]

ok. well they did it right

so look at what main() does

that just creates the table

what is db.update_schedule

can you share the error you're getting

[vernal glade]

import aquariumqt.database as db

 line 196, in co2_sch
    db.update_schedule(conn, (f"Co2 Scheduled for - {date_str} - {co2_str} - {self.repeat_sch()} -"))
NameError: name 'conn' is not defined

was doing this until about 4am lool

the other thing thats really confusing me is that i used DB Browser (SQLite) to make the data base and im not sure if my code matches or if i can use that to make the code etc.

[harsh pulsar]

this is a little confused

you didn't define conn anywhere evidently

and i have no idea what you expect db.update_schedule to do

[royal storm]

are there any async orm libs

[plain radish]

sure, tortoise-orm and GINO

@royal storm

[urban monolith]

hello, not exactly a db question but can you recommend a resource/place to start with for someone who's never touched a db before?

[light blade]

Sql-alchemy

Its a pythonic way of using SQL

https://docs.sqlalchemy.org/en/13/orm/tutorial.html

[upbeat lily]

Sql alchemy is a terrible way to get started. It's very important to have a basic understanding of what sql alchemy is doing under the hood. It would be much better to study with a SQLite tutorial first

[dawn pulsar]

<name> $ sudo dpkg --configure -a
Setting up mariadb-server-10.1 (10.1.38-0+deb9u1) ...
dpkg: error processing package mariadb-server-10.1 (--configure):
 subprocess installed post-installation script returned error exit status 1
dpkg: dependency problems prevent configuration of default-mysql-server:
 default-mysql-server depends on mariadb-server-10.1; however:
  Package mariadb-server-10.1 is not configured yet.

dpkg: error processing package default-mysql-server (--configure):
 dependency problems - leaving unconfigured
dpkg: dependency problems prevent configuration of mysql-server:
 mysql-server depends on default-mysql-server; however:
  Package default-mysql-server is not configured yet.

dpkg: error processing package mysql-server (--configure):
 dependency problems - leaving unconfigured
Errors were encountered while processing:
 mariadb-server-10.1
 default-mysql-server
 mysql-server

Any ideas why this is happening?

[ionic pecan]

I would dig into the post-installation script

Check the .deb archive for the postinst script

[dawn pulsar]

Care to explain what the means in English haha

No but in all seriousness

I'm not really sure how to do that

[sacred apex]

First result when goog'n the last 6 lines of the error:
https://unix.stackexchange.com/questions/249530/mariadb-dependency-problems-leaving-unconfigured

[dawn pulsar]

@sacred apex

.:~$ apt-get remove --purge mysql*
E: Could not open lock file /var/lib/dpkg/lock - open (13: Permission denied)
E: Unable to lock the administration directory (/var/lib/dpkg/), are you root?
.:~$ ps aux | grep -i apt
<name>+ 8375 0.0 0.0 12780 1028 pts/2 S+ 17:37 0:00 grep -i apt
.:~$ sudo kill -9 30096

Oh wow nvm

Sudo is just magic

Ok nice, thanks

[sacred apex]

I love sudo. it's my best friend.

[dawn pulsar]

sudo fixmyproblemsplz /s

[sacred apex]

sweet!

[pliant pendant]

with PostgreSQL in Asyncpg, is there a way to upsert {key : value} into a column?

lets say {key1 : val1, key2 : val2}

and i want to upsert {key1 : val3}

then it becomes {key1 : val3, key2 : val2}

is this possible?

(using jsonb) - postgres 9.5+

[harsh pulsar]

@pliant pendant https://stackoverflow.com/a/44399945

@pliant pendant basically you use || to merge 2 jsonb objects. much like how in python you should be able to use | to merge dicts, but for some reason you can't

[pliant pendant]

@harsh pulsar i do get that but how do i get the object already stored?

[harsh pulsar]

insert into the_table (id, json_column)
values (1, '{"b": 10, "c": 20}'::jsonb)
on conflict (id) do update
   set json_column = the_table.json_column || excluded.json_column;

the excluded is a special table name that refers to the values you just tried to insert

(buried in the docs here https://www.postgresql.org/docs/current/sql-insert.html)

Note that the special excluded table is used to reference values originally proposed for insertion

[pliant pendant]

if this work i'm geniunely willing to hug you, brb

[harsh pulsar]

dont hug me, thank me by going vegetarian

[pliant pendant]

it's rather impossible

due to my allergies

i'm one of the few ones who relies on meat supply

to survive

[harsh pulsar]

i actually have a similar restriction. i was just talking to someone about it a while ago

i dont know too many w/ that problem

[pliant pendant]

if i could fix it i'd want but

[harsh pulsar]

same. small world

[pliant pendant]

i mean soy, quorn, qinua, some grains

every day i get new ones

last one i got this year, strawberries

[harsh pulsar]

what!

thats crazy

have you ever gone on some kind of fasting diet or something

[pliant pendant]

yeah my allergies are due to a VERY extreme case of pollen allergy

[harsh pulsar]

if youre getting new allergies thats really scary

yikes

[pliant pendant]

it's not lethal

[harsh pulsar]

sorry man

[pliant pendant]

but it's really spreading

[harsh pulsar]

yeah

[pliant pendant]

and can be leathal at older ages

i really wish doe it was gone

lactose is also another thing that come recently

but luckily its weak enough i can ignore it kinda

[lament scroll]

I'm reading about databases checking out flask_sqlalchemy along the way... is it possible to update the structure of a table if I update an entity model? such as having a column under the type String(60) and then String(70)

[pliant pendant]

@harsh pulsar all hopes for the lab grown meat, that'll probs change my lifestyle forever

[harsh pulsar]

that really brutal @pliant pendant . might be time to consider some of the weird stuff like intensive qigong, acupuncture, intermittent fasting... and yeah lab grown meat will be huge for me. but we should probably continue this discussion in an off topic channel 🙂

[pliant pendant]

tru ^^

[harsh pulsar]

@lament scroll normally you need some kind of database migration for that. not sure what flask_sqlalchemy offers specifically, but "migration" is your search term

[lament scroll]

oh I see

I mean, I know this is not something you want to do, it basically means something was poorly planned

but it would save time in my short tests

[harsh pulsar]

database migrations are a fact of life

[pliant pendant]

        INSERT INTO
        user_bookmarks(user_id, {0}_image_id_list)
        VALUES ($1, $2)
        RETURNING 0
        
        ON CONFLICT (user_id) DO
            UPDATE SET {0}_image_id_list = user_bookmarks.{0}_image_id_list || excluded.{0}_image_id_list
            RETURNING 1;

is there a way to make returns?

[harsh pulsar]

https://stackoverflow.com/a/40880200

@pliant pendant

[lament scroll]

are all int the same size in SQL servers?

I googled it but Microsoft has documentation for their stuff and idk if it applies for their servers or everywhere else

[harsh pulsar]

across different sql implementations? i dont know if the standard specifies...

i doubt its the same

[final lion]

I wouldn't rely on it anyway, you end up having to tailor to whichever implementation you're using anyway

[pliant pendant]

@harsh pulsar i'm overjoyed, thanks alot!!!

[harsh pulsar]

this is great, im learning all about ON CONFLICT through you

[torn sphinx]

How can I build a WHERE _ IN ? query with a placeholder? I tried a couple of variants and I keep getting either syntax error near "?" or parameter binding errors

[torn sphinx]

Ah, maybe I could just join the iterable before passing it to the sql

[harsh pulsar]

@torn sphinx different database libraries use different placeholders

ther are in fact 4 acceptable placeholder styles -- the library documentation should tell you which one to use

oh, i see what you're saying

constructing a "dynamic" WHERE ... IN (...) is pretty rough w/ the dbapi

imo it's better to construct the sequence of placeholders than to construct the data itself... still saves you from quoting issues

items = # long list of items to check against

placeholders = ','.join(['?']*len(items))
query = f'''
select *
from my_table t
where t.id in ({placeholders})
'''
cursor.execute(query, items)

[torn sphinx]

Thanks, I'll try that

[ornate carbon]

Hi everyone, I have a MySQL Database with more than 1GB, which I exported, using the following command .\mysqldump.exe -u root -p databasename > databasefile.sql
Everything worked ok on the dump.

But now I want to import this file again to the database and when I use the command .\mysql.exe -u root –p databasename < databasefile.sql
I get an error about ERROR: ASCII '\0' appeared in the statement, but this is not allowed unless option --binary-mode is enabled and mysql is run in non-interactive mode. Set --binary-mode to 1 if ASCII '\0' is expected. Query:
I already searched about this situation and it talks that the file needs to be unzipped. But I never zipped the .sql file and the file extensions is “.sql”.

Someone is aware of what am I doing wrong?

[patent glen]

what does the file look like if you open it in a text editor

@ornate carbon

[ornate carbon]

Hi @patent glen my header file looks like this

-- MySQL dump 10.16  Distrib 10.1.37-MariaDB, for Win32 (AMD64)
--
-- Host: localhost    Database: mydatabase
-- ------------------------------------------------------
-- Server version    10.1.37-MariaDB

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

[pliant pendant]

@harsh pulsar i learn alot from u too ^^, also i keep digging into issues

        INSERT INTO
        user_bookmarks(user_id, {0}_image_id_list)
        VALUES ($1, $2::jsonb)
        
        ON CONFLICT (user_id) DO
            UPDATE SET {0}_image_id_list = user_bookmarks.{0}_image_id_list || excluded.{0}_image_id_list

        RETURNING *, (xmax = 0) AS inserted;

the style is now {id : {tesval : [tag1, tag2, tagn]}}

if i have another list with [stag1, stag2, stagn] using ANY() operator

how do i get all items matching inside of tesval with the other list?

alt.2 is to restructure so all info fits inside one {} format

so both id and tesval is in the same

[harsh pulsar]

im actually not sure. you might get the chance to teach me something here

[pliant pendant]

i'll dig deep to find it

[harsh pulsar]

look into the jsonb docs

[pliant pendant]

yes i'll c:

[cursive cedar]

I have a question, anyone care to help out?

[nova hawk]

!ask

[delicate field]

ask

Asking good questions will yield a much higher chance of a quick response:

• Don't ask to ask your question, just go ahead and tell us your problem.
• Try to solve the problem on your own first, we're not going to write code for you.
• Show us the code you've tried and any errors or unexpected results it's giving
• Keep your patience while we're helping you.

You can find a much more detailed explanation on our website.

[cursive cedar]

Sorry, thanks @nova hawk

My problem deals with PYMongo Database.

from pymongo import MongoClient

client = MongoClient('mongodb://localhost:27017/')

db = client.[database name]

How would I envoke a passed database name within a function call?

example:

def example(passed_method):

db.passed_method.find()

return collection

I originally was going to make a string and format the table names, but the exec python command isn't working.

[nova hawk]

In w hat way is it not working?

[cursive cedar]

str(db.%s.find()) % (dbName), you will get "db.dbName.find()

I was exec() the result, but I am guessing you can use another command to return the database record.

I believe you can use eval to do it, but I was wondering if anyone knew if anything else that is more elegant and might work better.

[nova hawk]

db[col_name].find()```
Would work wouldn't it?

No string interpolation or exec needed for that.

[cursive cedar]

Trying to pass different col_names in a function.

def example(a_name):

db[a_name].find()

This would work for keys, but how about for methods?

db.a_name.find()

[nova hawk]

Not sure what those methods are, sorry.

[cursive cedar]

np, i'll juse use eval then, thanks anyways.

[nova hawk]

I'm curious what these methods would be tbh.

[pliant pendant]

VERY interesting

[sage island]

does anyone know of a good/fast database to store large ammounts of text ? is sqlite out of the questino ?

[tiny otter]

so this is my first time trying to do anything with a database and im getting this error File "C:/Users/jarnold/Desktop/Inventory.py", line 43, in submitdvr c.execute("INSERT INTO DVRs(IventoryID, Notes, DateRecieved, DateSentOut, Property) VALUES ('DVR'+random.randint(10000,99999),1,1,'NA','NA')") sqlite3.OperationalError: near "(": syntax error

and this is the code ```class NewDVR(Screen, LeftMenu):

def submitdvr(conn, self):
    c.execute("INSERT INTO DVRs(IventoryID, Notes, DateRecieved, DateSentOut, Property) VALUES ('DVR'+str(random.randint(10000,99999)),str(self.dvrnotes.text),str(datetime.date.today()),'NA','NA')")
    conn.commit()```

[fringe tiger]

you can't use code inside a string like that

you could with f string but that's a no no for queries

[tiny otter]

so how ouwld i go about using user imputed values to insert into the table

[fringe tiger]

insert_guild_query = "INSERT INTO GUILDS(GUILD_ID, PREFIX) VALUES(?,?)"
connection.execute(insert_guild_query, (guild_id, default_prefix))

here's a random example

(guild_id, default_prefix) is a tuple, you can insert any code there, currently those 2 are just regular variables

do you have a idea now?

[tiny otter]

alright ill try that! also when i change the values to all 1s i get an error of commit not being defined? ill get the exact error in a second

[fringe tiger]

first fix the query then we'll see

[tiny otter]

alright ill do it

    insert_dvr_query = "INSERT INTO DVRs(IventoryID, Notes, DateRecieved, DateSentOut, Property) VALUES (?,?,?,?,?)"

    def submitdvr(conn, self):
        c.execute(insert_dvr_query, (('DVR'+str(random.randint(10000,99999)),str(self.dvrnotes.text),str(datetime.date.today()),'NA','NA')))
        conn.commit()```

alright thats what i have

and i get the errorFile "C:/Users/jarnold/Desktop/Inventory.py", line 45, in submitdvr c.execute(insert_dvr_query, (('DVR'+str(random.randint(10000,99999)),str(self.dvrnotes.text),str(datetime.date.today()),'NA','NA'))) NameError: name 'insert_dvr_query' is not defined

[fringe tiger]

um first value is self no?

def submitdvr(conn, self):

[tiny otter]

sorry ive only been doing python for a few weeks, isnt that what i have?

[fringe tiger]

no self always goes as the first variable in a method

[tiny otter]

oh i see

no idea the order effected it

[fringe tiger]

yes self is like a refercece to itself,and it's the first argument. In your case conn would be self and self would be con

[tiny otter]

changed that but error is the same saying it isnt defined

if it matters i have this up top as well conn = sqlite3.connect('seinv.db') c = conn.cursor()

[fringe tiger]

ye the error was unrelated, but it would cause problem later

[pliant pendant]

@sage island Depends on size

u need huge or massive databases

[fringe tiger]

you get that error because insert_dvr_query is a class variable

so to acces it you need

NewDVR.insert_dvr_query

[tiny otter]

awesome! it starts now

i figured since they were in the same class it would know for some reason

[fringe tiger]

this looks weird an long (('DVR'+str(random.randint(10000,99999)),str(self.dvrnotes.text),str(datetime.date.today()),'NA','NA')))

is that a tuple inside a tuple

aka the first ( and last ) are unecessary?

[tiny otter]

i think youre right

[fringe tiger]

you can make it easier to read if you split each variable outside and then use that variable in the tuple

like

IventoryID = 'DVR'+str(random.randint(10000,99999))
....
c.execute(insert_guild_query, (IventoryID , ...)

Also look up PEP8, it's a style guide. Usually snake case is preffered for variable names. And f strings could make string + even more compact and beautioful

[tiny otter]

so just sat each value as a variable then put in the variable name?

[fringe tiger]

ye

that would be easier to read no?

you don't have too tho

[tiny otter]

i agree it would be way easier

so now when i hit submit i get File "C:/Users/jarnold/Desktop/Inventory.py", line 46, in submitdvr conn.commit() File "kivy\weakproxy.pyx", line 32, in kivy.weakproxy.WeakProxy.__getattr__ AttributeError: 'Button' object has no attribute 'commit'

[fringe tiger]

is it c or conn

[tiny otter]

i thought conn

i can try c

c = conn.cursor()```

[fringe tiger]

you can repaste the updated code

[tiny otter]

i was just following the tutorial/example i have

[fringe tiger]

paste code idk how it looks like

[tiny otter]

alright

c = conn.cursor()
...
...
class NewDVR(Screen, LeftMenu):

    insert_dvr_query = "INSERT INTO DVRs(IventoryID, Notes, DateRecieved, DateSentOut, Property) VALUES (?,?,?,?,?)"

    def submitdvr(self, conn):
        c.execute(NewDVR.insert_dvr_query, ('DVR'+str(random.randint(10000,99999)),str(self.dvrnotes.text),str(datetime.date.today()),'NA','NA'))
        conn.commit()```

and according to the tutorials it should be the conn not the c

[fringe tiger]

def submitdvr(self, conn):
  c.execute(NewDVR.insert_dvr_query, 

c is not defined as far I as see?

[tiny otter]

its defined at the top outside the class, does it need to be inside the class?

its not erroring on that line as of now, but the line below it

[fringe tiger]

welp it should idk why it doesn't

anyway how do you call submitdvr?

it seems you are passing Button object instead of connection

[tiny otter]

its a button that calls it

            pos_hint:{"x":.535,"y":.02}
            size_hint: .15, 0.05
            font_size: (root.width**2 + root.height**2) / 19**4
            text: "Submit"
            background_down: 'blue.png'
            on_release:
                root.submitdvr(self)
                dvrnotes.text = ''```

[fringe tiger]

root.submitdvr(self)
you're passing self aka the button here, so the conn argument in submitdvr will be a button , not a database connection

[tiny otter]

originally it was just root.submitdvr() but i got the error File "C:\Users\jarnold\Desktop\Inventory.kv", line 220, in <module> root.submitdvr() TypeError: submitdvr() missing 1 required positional argument: 'conn' and then i added self

[fringe tiger]

well don't add self

add conn

since you named it like that
conn = sqlite3.connect('C:/Users/jarnold/Desktop/seinv.db')

[tiny otter]

     root.submitdvr(conn)
 NameError: name 'conn' is not defined```

sorry im so last haha

[fringe tiger]

scope might be a problem yeah

uh

you can use global but oof that's bad practise

[tiny otter]

global?

[fringe tiger]

ye

def submitdvr(self)
  global conn
  conn.execute(NewDVR.insert_dvr_query, ('DVR'+str(random.randint(10000,99999)),str(self.dvrnotes.text),str(datetime.date.today()),'NA','NA'))
  conn.commit()

then just call root.submitdvr()

[tiny otter]

so why is that bad practice?

[fringe tiger]

usually you don't use global, you either pass variable to function or set it as instance variable for a object

!tag global

[delicate field]

global

When adding functions or classes to a program, it can be tempting to reference inaccessible variables by declaring them as global. Doing this can result in code that is harder to read, debug and test. Instead of using globals, pass variables or objects as parameters and receive return values.

Instead of writing

def update_score():
    global score, roll
    score = score + roll
update_score()

do this instead

def update_score(score, roll):
    return score + roll
score = update_score(score, roll)

For in-depth explanations on why global variables are bad news in a variety of situations, see this Stack Overflow answer.

[tiny otter]

well look at that! it worked and the DB got updated

[fringe tiger]

👍

[tiny otter]

will that cause issues with the other 6ish screens doing roughly the same thing?

[fringe tiger]

I have no idea, prob not.

[tiny otter]

alright! maybe you know this too, so as of now to generate a serial number im having it do a 3 letter combo plus a random integer between 10000 and 99999, while that mostly works that could create 2 that are the same, is there a way to say the first one i submit is DVR00001 and the next time its DVR00002?

[fringe tiger]

ye

🤔

for avoiding 2 duplicates you can set primary key or unique constraint on the database table, but that's not the best solution

you have like a auto increment option in table

so for each row that value is incremented

[tiny otter]

the Serial Number is set as primary, is that bad?

[fringe tiger]

however idk if it works with your format

nope sounds good

[tiny otter]

so what happens if that random integer does try to submit say DVR22345 when there is already a DVR22345 in the table?

[fringe tiger]

it throws exception

[tiny otter]

i assume as my code stands that just crashes the program?

[fringe tiger]

ye

[tiny otter]

so do something like this?

    con = lite.connect('test.db')

    cur = con.cursor()

    cur.executescript("""
        DROP TABLE IF EXISTS cars;
        CREATE TABLE cars(id INT, name TEXT, price INT);
        INSERT INTO cars VALUES(1,'Audi',52642);
        INSERT INTO cars VALUES(2,'Mercedes',57127);
        INSERT INTO cars VALUES(3,'Skoda',9000);
        INSERT INTO cars VALUES(4,'Volvo',29000);
        INSERT INTO cars VALUES(5,'Bentley',350000);
        INSERT INTO cars VALUES(6,'Citroen',21000);
        INSERT INTO cars VALUES(7,'Hummer',41400);
        INSERT INTO cars VALUES(8,'Volkswagen',21600);
        """)

    con.commit()

except lite.Error, e:

    if con:
        con.rollback()

    print "Error {}:".format(e.args[0])
    sys.exit(1)```

[fringe tiger]

umm idk it by hearth but you could set initial value to DVR000000, then for each addition get the max value, increment it , and add that new one

but then you'll run into problems if some of them get deleted

So idk what would be the best way to deal with this

[tiny otter]

Could I make a IDvar.py and set variables for each device such as dvrid = 000000, then import that file and take that number to add 1 and use that to make dvr00001 and send that to the DB. Then save the variable in so it's 00001 now and the next DVR will add 1 and get 00002 and so on and so forth?

@fringe tiger

[fringe tiger]

no sounds like duplicate code so bad idea

[neat reef]

insert into cars values (1, 'Audi', 52642), (2, 'Mercedes', 57127), ...; I thought you could something like this

[fringe tiger]

can these DVRs be deleted?

[tiny otter]

The can be deleted if one died and we removed it from the field, doesn't have to be

And @neat reef that wasn't my code that was an example

[fringe tiger]

so how will you account for that, there will be holes in numbers, or is that not important?

[tiny otter]

Shouldn't be important

[neat reef]

I see, I'm still curious if you can

[fringe tiger]

ah oke

[tiny otter]

It's jsitnto be able to print a label on the hardware and look up imfor about it

[fringe tiger]

then look up autoincrement

[tiny otter]

ill look at that now and here you go clone ```class NewDVR(Screen, LeftMenu):

insert_dvr_query = "INSERT INTO DVRs(IventoryID, Notes, DateRecieved, DateSentOut, Property) VALUES (?,?,?,?,?)"

def submitdvr(self):
    global conn
    c.execute(NewDVR.insert_dvr_query, ('DVR'+str(random.randint(10000,99999)),str(self.dvrnotes.text),str(datetime.date.today()),'NA','NA'))
    conn.commit()```

as of now its generating a random int but that could possibly create duplicates

so would it be something like this? ```class NewDVR(Screen, LeftMenu):

insert_dvr_query = "INSERT INTO DVRs(IventoryID, Notes, DateRecieved, DateSentOut, Property) VALUES (?,?,?,?,?)"
dvridcount = 0

def submitdvr(self):
    global conn
    dvridcount += 1
    c.execute(NewDVR.insert_dvr_query, ('DVR'+str(dvridcount),str(self.dvrnotes.text),str(datetime.date.today()),'NA','NA'))
    conn.commit()```

[fringe tiger]

nope. If you exit/restart the script it will reset to 0

[tiny otter]

well damn, thats why i thought have a py file just for storing the last id as a variable, but i guess itd just reset too?

[fringe tiger]

you can like use COUNT to get number of DVRs in database

it's a sql statement

so everytime you add new, first you get the count, increment it and add new one with that incremented value

that's one of the few ways I can think of

[tiny otter]

thats not a bad idea

think itll be possible to make it format as 00001 and not just 1

but in a way 11 doesnt become 000011

[fringe tiger]

ah

ye

[tiny otter]

last time i tried something like that it didnt like the leading 0s

[fringe tiger]

num_len = 6
count = str(22)
string_output = "0"*(num_len-len(count)) + count
print(string_output)
->000022

uh this is kinda ugly.

ahh

youd don't want 0

🤦

then nvm my code

[tiny otter]

no i do want leading 0s

[fringe tiger]

ah oke then 🤔

[pliant pendant]

@harsh pulsar rmy head is engraved in pain, but i might got an idea how to solve it... still no clue how to use it doe

json_populate_recordset is able to convert a jsonb into a table format (seen at https://www.postgresql.org/docs/9.5/functions-json.html using CTRL + F), however that means i gotta transform into [{}, {}] format... which means i also gotta find another way to insert values to it

if i use AS i could throw it into its own table and dig information up easily... actually now i think im on the right TRACK

[tiny otter]

i forget what i did last time but my code didnt like that i had leading 0s

[sage island]

@pliant pendant sorry for the late answer. something in terms of 10s of megabytes but I want it to be fast.

[pliant pendant]

@sage island postgreSQL is more than enough smh, however mariaDB and MySQL is also quite good alternatives

[tiny otter]

so to be clear```
1st line - setting max length
2nd line - defining count as the starting integer
3rd line - no idea whats going on
4th - printing

i kind of get line 3 but now sure how it will increment without resetting

unless we define count as the COUNT number from sqlite

[fringe tiger]

3d line just adds "0" to accumulate the missing characters

count is from db ye

[tiny otter]

would this work? num_len = 6 count = (number COUNT gets from DB) count += 1 string_output = "0"*(num_len-len(count)) + count print(string_output) ->000022

[fringe tiger]

yes

[tiny otter]

Brain you helpful genius you

[fringe tiger]

tbh I find this hacky and I'm sure there are others here who could find a better way, something that would eliminate all this problems by just using some whack sql statement

[tiny otter]

seeing as i hadnt even heard of sqlite3 a few hours ago it works for me!

[fringe tiger]

👍

as long as it works hiuhehe

[tiny otter]

well

        global conn
        c.execute("SELECT Count(IventoryID) FROM DVRs")
        num_len = 5
        count = (c.fetchone())
        count += 1
        string_output = "0"*(num_len-len(count)) + count
        c.execute(NewDVR.insert_dvr_query, ('DVR'+str(string_output),str(self.dvrnotes.text),str(datetime.date.today()),'NA','NA'))
        conn.commit()```

     count += 1
 TypeError: can only concatenate tuple (not "int") to tuple```

[fringe tiger]

count = (c.fetchone())
not the COUNT statement

[tiny otter]

what?

[fringe tiger]

https://www.sqlitetutorial.net/tryit/query/sqlite-count-function/#1

[tiny otter]

the coiunt works if i print c.fetchone() i get the correct number "1"

[fringe tiger]

because fetchone does that, fetches one?

[tiny otter]

does it? ill add another to the db and see if it gets 2

i was going off this example rowsQuery = "SELECT Count() FROM %s" % table cursor.execute(rowsQuery) numberOfRows = cursor.fetchone()[0]

[fringe tiger]

in that example fetchone will get the result of count

[tiny otter]

mine doesnt?

[fringe tiger]

cursor.execute(rowsQuery)you say get me the count, this will be stored in cursor
cursor.fetchone() you say get me that count that is stored in cursos, it will be a tuple so we fetch it by indexing aka cursor.fetchone()[0]

something like that

you forgot to index

[tiny otter]

so when i added a entry to ther Db it printed 2 instead of 1

oh i see what you mean

[fringe tiger]

so it worked?

[tiny otter]

well the print did,

[fringe tiger]

I think you need to count = c.fetchone()[0]

[tiny otter]

thats it haha

[fringe tiger]

oki 👀

[tiny otter]

so why is it erroring on count += 1 though

[fringe tiger]

because c.fetchone() is a tuple

is it?

can you do this count = c.fetchone()[0]

[tiny otter]

yeah youre right, its printing as (1,)

so doing that it goes past that step then we get File "C:/Users/jarnold/Desktop/Inventory.py", line 50, in submitdvr string_output = "0"*(num_len-len(count)) + count TypeError: object of type 'int' has no len()

and code looks like this now def submitdvr(self): global conn c.execute("SELECT Count(IventoryID) FROM DVRs") num_len = 5 count = c.fetchone()[0] count += 1 string_output = "0"*(num_len-len(count)) + count c.execute(NewDVR.insert_dvr_query, ('DVR'+str(string_output),str(self.dvrnotes.text),str(datetime.date.today()),'NA','NA')) conn.commit()

[fringe tiger]

ye if you look at my code I stringed count so I can get len easily

[tiny otter]

integers dont have a length?

[fringe tiger]

count = str(c.fetchone()[0] + 1)

[tiny otter]

that worked i think

it works!

[fringe tiger]

👍

[tiny otter]

and doubtful but if anyone is following along it looks like this```class NewDVR(Screen, LeftMenu):

insert_dvr_query = "INSERT INTO DVRs(IventoryID, Notes, DateRecieved, DateSentOut, Property) VALUES (?,?,?,?,?)"

def submitdvr(self):
    global conn
    c.execute("SELECT Count(IventoryID) FROM DVRs")
    num_len = 5
    count = c.fetchone()[0] + 1
    count = str(count)
    string_output = "0"*(num_len-len(count)) + count
    c.execute(NewDVR.insert_dvr_query, ('DVR'+str(string_output),str(self.dvrnotes.text),str(datetime.date.today()),'NA','NA'))
    conn.commit()```

[patent glen]

@ornate carbon sorry, so it's not compressed at all? See if it works with --binary-mode, I guess.

maybe you had a string with nulls in it

if you know how, search the file for null bytes with your text editor

[pliant pendant]

can someone explain json_agg inPostgreSQL for me?

[harsh pulsar]

@pliant pendant

aggregates values as a JSON array
https://www.postgresql.org/docs/current/functions-aggregate.html

[pliant pendant]

@harsh pulsar have been looking all day

no solution

im thinking of doing an [{}, {}] structure

but even then

i dont know how to check the values inside of the parameters

so i'll just do a vague solution

something like {"group 1" : {}, "group 2" : {}, "group 3" : {}}

and let python handle all edits within

[harsh pulsar]

what was your requirement again

[pliant pendant]

lets make it at best way possible

structure:

[
   {
    "image_id" : STR,
    "real_image_id" : STR,
    "title" : STR,
    "u_tag" : LIST,
    "image_type" : STR
   }, ...
]

1. i want to be able to search all images with matching request such as all images with "image_type" = "SFW"

2. i want to be able to compare "u_tag" with a list of tags, and return all images with all requirements, example:
   format: [example_u_tag] [example_request]
   if [tag1, tag2, tag3] contains [tag1, tag3] (TRUE)
   if [tag1, tag2, tag4] contains [tag1, tag3] (FALSE)
   if [tag1, tag3] contains [tag3] (TRUE)

those are the 2 things i'd want it to be able to, it's possible to do it in python but it'd be bonus if i could do it directly in sql

[harsh pulsar]

1. is easy, check data->'image_type' = 'SFW'

[pliant pendant]

how do i iterate trough all of them?

[harsh pulsar]

2. use data->'u_tag' && ['tag1', 'tag3']

ohhh hm

i think in order to iterate you might need to use pl/pgsql or something

maybe there is some kind of unnesting trick available

[pliant pendant]

it'd be easy if i did mass duplication list

but i dont find it nessesary

[harsh pulsar]

@pliant pendant https://www.db-fiddle.com/f/susZ1iBDiBbZvEJRUKT54i/0

@pliant pendant better still https://www.db-fiddle.com/f/susZ1iBDiBbZvEJRUKT54i/1

[pliant pendant]

this is eye candy

doe we got some issues

1. this returns every user with that result

not every json item with that result

i wonder is it possible to use json_to_recordset for this?

[pliant pendant]

@harsh pulsar

select * from jsonb_to_recordset((
    select contents
    from objects
    where userid = 1
)) as x(a int, b int)
where a = 1;

[harsh pulsar]

Oh just don't use the outer query, I thought you wanted the users not the individual elements

[pliant pendant]

its alright i'm working this trough

nah i'll never index users

its gonna take minutes

getting close!!

i need to find a way

to match lets say two lists

[a, b, c, f, g, l, n]

and [f, l, a, c]

if [a, b, c, f, g, l, n] contains ALL values in [f, l, a, c]

which it does

then it passes

current dbfiddle: https://www.db-fiddle.com/f/susZ1iBDiBbZvEJRUKT54i/4

[rich trout]

If you're goal is to get matching lists of tags, you should be using a different database format

Specifically, not json, and a three-table setup, one images, one image-tag relation, and one tags

That way you can do

SELECT image.id FROM  images image, image_tags image_tag, tags tag,
WHERE image_tag.image_id == image.id
WHERE image_tag.tag_id == tag.id
AND tag.id IN (1, 2, 3)
GROUP BY image.id
HAVING COUNT( image.id) = 3

It's called the Toxi Tag schema

There may be a more efficient join structure, but this is a normalized database format and thus the one I'd suggest

[pliant pendant]

@rich trout i'd do it if it was non user-related, which i already do

however

every

single tag can be vastly unique

to a point it's not even worth it anymore

cause the user specify the tag and title

[rich trout]

aah

[pliant pendant]

so thats why i try to make it that way

it'll save alot of time and space

if i can get it to work this way

[harsh pulsar]

@pliant pendant try the 1st one i sent

[pliant pendant]

@harsh pulsar it checks one vs one value, not multiple vs multiple

[harsh pulsar]

@pliant pendant use IN?

[pliant pendant]

that'll make one in multiple

maybe im missing some key aspect of postgres

[pliant pendant]

@harsh pulsar https://www.db-fiddle.com/f/susZ1iBDiBbZvEJRUKT54i/7

it's solved

<@ is an operator that defines "CONTAINS"

i never knew it existed, i lacked knowledge for that

also i misspelled explosion to exposion in that code

[limber stone]

parameterize, I don't get it.

Using asyncpg and have this:

async with self.con.acquire() as con:
            await con.execute(f"UPDATE threads SET last_message_time='$1' WHERE channel_id=$2 AND guild_id=$3;", (int(time.time()), channel.id, channel.guild.id))```

This returns error `invalid input syntax for integer: "$1"`

If I just do: ```python
async with self.con.acquire() as con:
            await con.execute(f"UPDATE threads SET last_message_time=$1 WHERE channel_id=$2 AND guild_id=$3;", (int(time.time()), channel.id, channel.guild.id))```
I get error: `the server expects 3 arguments for this query, 1 was passed
HINT:  Check the query against the passed list of arguments.`

If I use f-strings and replace the $[1-3] holders with the variables it will work just fine.

[harsh pulsar]

That 2nd one looks correct

[limber stone]

well asyncpg doesn't agree

[limber stone]

Apparently, it wont accept a tuple

I all had to do was take the () away and it worked

[harsh pulsar]

Ah, weird

Nonstandard API

[limber stone]

Let me rattle your brain (or anyone elses) with another question.

I need to retrieve rows where a specific criteria is met. But to get the criteria, I need to take a rows current value and use it in an equation. Using words, this would be the query:

get column2 from table where column1 is >= (time - column1) AND column1 is <= (time - column1)

[harsh pulsar]

@limber stone you can write that pretty much as-is

[limber stone]

I tried and couldn't get it to work

[harsh pulsar]

Share what you wrote

[limber stone]

I deleted it because it didn't work and left unfinished. Moved to a different thing

So, when I get back to it I will ask again

[harsh pulsar]

column1 >= column1 - time?

Literally just replace "get" with "select" and delete "is" and that is valid sql syntax

[limber stone]

Will try again tomorrow

[frozen osprey]

Hey. I have a question. I'm using MongoDB Atlas (NoSQL). I wanted to create a cmd which would send a DM to a user after 4h. Every user has it's own decument. How can I do this? I mean, I can create a background task, but how to iterate through all those documents?

[torn sphinx]

Not sure if this is right place. What would be the PostgreSQL equivalent of MySQL Workbench?

[pure cypress]

pgadmin maybe?

[torn sphinx]

I'll check it out thanks

[limber stone]

I figured out my problem.

...WHERE last_message_age < (extract(epoch from now())-15)```

This did what I wanted to

Now my other problem is my query isn't deleting records
But if I copy the same exact query into psql, it works

[carmine tide]

I've many records with same user id but when I want to add any value to only one record then how can I do that?

if I'll use update with where clause then all records having that userid will get changed

[hazy coyote]

@carmine tide I assume that user id is not a unique identifier, right? If so, what is the unique identifier for your records?

[carmine tide]

id

with pk

[hazy coyote]

do you have that id when you want to add a value?

[carmine tide]

no

that's the problem

is it not possible to update only 1 record having that user id randomly?

[hazy coyote]

why would you want to do that? that's illogical

[carmine tide]

suppose I've userids like

123456789
123456789
987654321
123456789

and I want to update value where userid = 123456789 (but only 1 record not all)

[hazy coyote]

randomly?

[carmine tide]

yes

[hazy coyote]

any one of these?

okay. what database are you using?

[carmine tide]

maybe I can just select id from db then I can use that id in where clause so it can change only 1 record

if I'll do c.execute('SELECT id FROM invites WHERE userid = ?;', (uid,)).fetchone()

it'll give a random id

maybe the latest one

or oldest Idk

but it'll give one id then I'll use that id in WHERE id = id that I got

[hazy coyote]

i am pretty sure that fetchone() returns the first item

it's not random, but it's also not all of them. is it good enough?

Last resort, try this:
'SELECT id FROM invites WHERE userid = ?; ORDER BY RAND(); LIMIT 1'

[fringe tiger]

ye just was gonna say that

[carmine tide]

yeah