databases

[torn sphinx]

as you can see on the top

which has userid and money columns

[foggy owl]

I see

Ok I'll give it a shot in a bit

Thank you

[torn sphinx]

yw

[delicate field]

zettelpy/slip_box.py lines 56 to 63

conn.cursor().execute(
    """ CREATE TABLE notes (
        note_id INTEGER PRIMARY KEY AUTOINCREMENT,
        title   TEXT NOT NULL,
        path    TEXT NOT NULL,
        UNIQUE(title)
        )"""
)```
`zettelpy/slip_box.py` line 70
```py
"INSERT OR IGNORE INTO notes (title, path) VALUES ('{}', '{}')".format(```

[primal finch]

okay so I have a table for my users where I store their username, password hash and all that. One of the features I want to implement in my timer site is the ability to create custom timers and that would require storing a purpose for the custom timer as well as the length of the custom timer in a new table. The only issue I have is how I would link the custom timer created to each user so that I only display their custom timers. Anyone know how I would go about solving this issue?
this is for sqlite

[fading patrol]

If I understand the question, yes, this is quite basic to do. The table with the timer data just needs to have a foreign key that corresponds to the user table

That way a simple JOIN query can pull the timer data for any specific user you need

[primal finch]

yep thanks

mb ghost ping

[blazing dawn]

I'm getting quite sidetracked on what I want to achieve

I'll try explain to my best in short:-

check = ["1", "2", "3"]

and now I have a postgresql database with an id column and a jsonb datatype column named lets just say cards
id cards
-----+--------
1 {"1": 3, "4": 2}

thats the data in the table named test

Question:
How do I convert the cards of id->1 FROM {"1": 3, "4": 2} TO {"1": 4, "4":2, "2": 1, "3": 1}

How I expect the changes to occur:
from the check variable, increment all present inside check variable that exist in the cards jsonb as a key thus changing {"1": 3} to {"1": 4} and add the values that don't exist as a key in the cards jsonb with a value of 1 thus changing {"1":4, "4":2} to {"1":4, "4":2, "2":1, "3":1}
purely through postgres even though I may have used python to showcase the check variable it was solely for explanation you can simply just use pure postgres...

now it might look like I'm asking to be spoonfed but I really haven't managed to find anyway to do this so any assistance would be highly helpful

tldr: update statement that checks whether a range of keys from an array exist or not in the jsonb data and automatically increments or inserts respectively the keys into the jsonb with a value of 1

[fading patrol]

If I'm not mistaken the code on StackExchange should do exactly that, you just need to replace their a, b, c keys with your 1,2,3. Did you try it? If it doesn't work the way you need it to, how so?

[blazing dawn]

it only dealt with individual values and not multiple to insert into the db or increment according to existence or non existence

the same with the second answer, the third was for nested

[paper flower]

@blazing dawn I'm just curious - what are you storing in your json field?

[fading patrol]

Is it not an option to modify the dictionary in Python? If you need to do this in pure Postgres I do suspect it's possible but I'm not advanced enough to help you. Using Python this would be very simple if slightly less efficient maybe

[blazing dawn]

(str, int) just a key string of "1","11","2", and so on of card_ids with how many of those cards of that particular id stored with integer values 1,2,3,etc

[paper flower]

You could use a relationship here?

[blazing dawn]

well those card_ids are in a relationship with another table of card_ids, name, description, stats, etc

[paper flower]

Really, use a relationship 😅
What's the table that needs that json field?

[blazing dawn]

Its slightly less efficient because I would need to do two calls one of retrieveing the jsonb data and then i modify it in python and then the other call of updating it accordingly

which is why I was trying to find how it could be done in a single call through pure postgres

[paper flower]

What database are you using?

[blazing dawn]

database as in postgresql?

[paper flower]

yep, rdbms if you want 😅

[fading patrol]

Since this is really a Postgres question, try there dedicated server

[blazing dawn]

I'm using postgresql yes

[paper flower]

sqlite, postgres, so postgres it is

[blazing dawn]

oh they have a discord?

[paper flower]

@blazing dawn I think you could use a many-to-many relationship here?

[blazing dawn]

Idk what that is, I'll look it up however

[fading patrol]

I'm having trouble to pull up an invite but it's called People, Postgres, Data. Maybe not official but definitely active

[paper flower]

@blazing dawn 1, 2, 3.. are card slots?

[nova cove]

its a relationship where multiple record from multiple tables are related to eachother

@blazing dawn

[blazing dawn]

yes

[blazing dawn]

found it, thankyou

[paper flower]

Something like this could work

create table tester(
    id integer primary key
);

create table cards(
    id integer primary key,
    name varchar(255) not null
)

create table test_cards(
    tester_id integer references tester(id),
    card_id integer references cards(id),
    slot integer not null,
    unique(tester_id, slot)
)

[fading patrol]

Ah yes, using a many to many table is a very good idea,.much simpler then messing with JSONB if you don't need to

[nova cove]

where the foreign keys?

[paper flower]

references ig? Not sure if it's valid SDL really 😅

[nova cove]

there can't be a relationship without foreign key

[paper flower]

references tester(id)?

[nova cove]

oh

didnt see

LMAO

[paper flower]

Also unique users won't be able to have more than one card in single slot

Afaik sqlalchemy can map this into a dictionary

[rigid mica]

can anyone help me with setting up postgresql? (connecting it to my file and maybe a little explanation on how to write in it)

[paper flower]

You should use an adapter (psycopg2 or asyncpg)

Or you could use an orm if you need one

[rigid mica]

How do I write to a postgresql column?

[nova cove]

lioke add data to it?

[torn sphinx]

uh... can anyone help me im trying to open a .db file to read its contents but i just get this from sqlite (translated: unable to open database reason: file not a database) even tho im trying to open a .db file am i doing something wrong?

[rigid mica]

yes

[nova cove]

you need to make a query

sql query

[rigid mica]

for example if I make a warn command, how do I make it enter member id, reason and amount of warns

[nova cove]

read the docs

you have to execute a query

you should learn basic SQL also

[rigid mica]

hm

this is all just really really difficult to me

[torn sphinx]

can you share the first few lines of the .db file ?

[rigid mica]

@nova cove can we maybe continue in dms or something?

[nova cove]

k

[torn sphinx]

uh they look like chinese in notepad: first two lines in notepad++%²À÷]’q5•íª‹ã Ì)lènR¨W4ÓIÑ1x¼ƒ”ÑV†1&fPÉQ«Ù”O%”LÅø—dnŠ¸IYò?1ÛpÉ­~·y7ݨöQ7ÚýÝA†Ø¨z×&¹i¸« first two lines in notepad: ╿லଢ଼熒锵௭讪෣⧌剮垨팴䤛턖輱뱸茖톔虖☱ས쥐ꭑ铙╏䲔撗詮렟奉㿲쥰纭禷㝑﷚䇝窨⛗ྐྵ롩ફ埒횎ᴿ墄昋ᇃ诋⺐ಚᾹꜨɌᬷ诂菃䴻ￚꊋ㩧ꢀ㚭羆ꖇ喂映鳢⁁썓롨蒐‽ະ錜ᧂ䙒׫箐싕⸶כ㶟⾅ɩ≟껈峢걂欹绅鈱쪘ⅱ࿦䣜Ɬ踷Ӣ㒲΋笪拕ԣ鮑ნ撿ꢷꥫ赸얟契퍁篭襨卾죰䔶憻ᣢ嚢囖웉恄쫤䘰⎹ꌅ筝繦믡酤ⲏヨ揅㪫峯뙽濲찁歶乻鎞蔏縞斀塮㐣쇔佖䯧詗ꏄዎ東愖궂⛙桀켒ԟ朦䗭캦⿎멙暙꡶鋐렣깚쐎쒛䏺╃ꌪ㲒盢煳㽶歵㝹⛽憰㱶ꬄЀꆛ㡰騎⩼攖櫣铋祦㧩ᙌ歘덜谮㨭⛿觠딣彳ℶ䝥蟽鶴덶ဂ默殇僄≮ﺻ㸇侥䳏ᆓ䄟郫݄锇׌Ῠݢ⽥帯殍ﰱ⨮鰝꽛ಅ㠁鼈쎭컵임뽓蒆ℸ￝啖徉䵃眬쀞励蔞吖ǎ䴸ꭞ罀䑵狺㯐㞡쳞㫭鲌蔂䕚禃芅ἢඦ嬜ᇐ痢ﾚ萲吁ﶻ멚掽堄鍕䫫姭면ᲅ温쿍썛䞖㨗䯓ᩅ鶝瞂㸃ᒒ㋠蘶퀀뿛澹寕ᡇ嵟ᱴ꼱쬸ው蛹㤭锶⅍귎⶞熸犌뭱筡⒢໰䬡篯냬Ɪ伭翈머熐钁덃渞냘쥍䇢殜存籔샻䶺橾纰쏜钇䠐澩欜쿛ᝧ掠⪜킩ꢓ냏냐寯᭙⠪ᒛ⠔뒯箲᷻䑳྇惔䨂㻒䍽륙䶷窛鐥闅뒀ꆎ苠켶死᪫미鶸牾ᜍ偸Ẅ웭ᑏ驳砙ᢷ氲ꑙ쐃量㑳軚⬖锫灨樂戄ﮖ釄ㆨ袁ඝꔐ탐塟䘙鰱絽ᝣ䒈❕煸䝟❞򩛛桛쓢⏶曈缁مⰄ⣁ᘽ㯶稉ﷸ揞훋䖭ﶉ졙쒂З㽑ꝃ髨포↫㙭䥚퍡햠姙닟ㆯ낲妈䎆긍㢂֨ﬥ閑눙푘⚌䇘事橷အ덨ళ嘰옴Θ볣藠娸⽰今릸ঈ宀ﱋ዇퐙킗䗐잝睱墟엵⛃ꆽ㍢赺퉒鷓쌻⳽檟䠩聘桙ჸﱃీ笻ₑ∙ᭈ꧍淫鼬Ṋ었伂쀰圪낻攦⼌㻶ぬ곱Ꝿ쓎巉띭渜锪娧飉奕⪞刺ʋᄃ꫚쿽㈸뙏ᱱ㒹Ⓜࡸꛮ⌲狝诊ᾮ칚끒殥踌䕏㳼箸莧᪰䉼﩯癶웕죱ƽ┦抏羸진䠓롒轨篷摨娩픽봵拘꾼᜞ﻯꘐ￀魙騵軻䵁ኗ젩Ɱ鶵ࠓ멛ᶾ䞆䔐걱쪲쑡똰瘪虑㠜ⷣÞ툡帡逮랩锄ᠦ覝䒁윇ΐ้팣鋁墖Ⲱ蜀哽ꍼᖑႴ䙶詘䔮鸎砣픍膀炼䟏催벙볘푓숿ᕺẵ港ⅶ듵辙횭齇삞ꤘ훅ㇵ✝䏖矯ʛ냜ꛞ偨镃柁荠蓕䩆Ǎ䵶哳ڿ홺쮋椇ᦍ嬇䖿屇㠍䭫舿咦蕵嘤턞꽳눪缷ᐳ餌ሪ⟴尡糖攎뮁ਕᒺඈ헃lᱮ攔凖脌肍ᘝ铀㘰댤奿ꀨ葜⡊揷놦瘁ड़ฅ撜쵢舰ࡠؼ⹱ᡌ䇔ﶫ᪷쯟﬙隊ִ릲㡽Ⱉ瓿⚯莂㹣ᭃᵙ鬂쏔⃣鄑쮂Xᗑꭘ刟膸쐦솬뎝㉇镲⅝ǪᏉ䄎ꮝ汛ꔥ왿ಷ߻ḉ「ᬥ徧뾬ꥍ⋌俍칊竩ヅ줞㼇ꉝ韮᤹᠄昽䋂ᓒƣ珗銯䡲ⓝ쵮뮜棝⡀賔㗝磵⌙쀬挂爋噣ᮟ菿㴼弎鸝釆섗빫껑₶遛凰踕⿻哸䇲ድ䣚난迦殊椼倄鐦⼝꯻⒊蕰叅㽫絢ࣤ兓搅۰ߏ⺇呄ሬꪪ흮潚؍륮쮶鞜퀾詆ㅈ톓ᆬ僓됴뎓ၸᴹ悊ﾕ㈥昀㢇剏廋ᤴ糯佯锘䪁攺㏂蘋铒䃪Ꮊ藓땗ꁁ௓蓥ꙹ嘄鎖䈜窂釟밎㈣⸧㸰뢊៥뜄ጂ믞퀏ꛈ⅘쭳쎥㕀쉒皌犮鞭븋㞇ꓖ뷣䟉㷌赉੥぀﫜즮愈ꄡ牸◄䯲圆ꂔ鐓䧓鲐꼎ꪠ坩賈敠剡蜮࣬╪凭큽ⶊ⼳萕ẇ陆ಝ烒ᕼ㌠㻹ꊁ镋윝쮬憾폍楗⥙ꎎ눲ར䅖䴴᨝娆?鴷Ⳳ₢/뢁ﾄ⬨蔚㘪惏瘖嗒瑐珐৸ʖ젲붂륏ᙒ䥇팰翂Ӹ阹ᰗ횪믳槍㫞囧织㻆赎詨녒շ쯚瞌䀏쯼嗟醨ꉤ漗쯀涟潱츋핱ẋ썞臫dz⥸笎띃䎫軩˛씂胈趔沬뼅䬢켣ˏ也廸⼳蜡ꔄ恞ⓩ⸪邔텔㸺鋔⇾å歆ᮔ杞큨煥愳⊅䮏䖞䥤舎뙭鰕膴쵷淇䏲穠㸴읨婅붭砜ꮚ᪂蕄愀ꇕꚠ嗎櫽䉝ਜ਼ƣ냠챠擑詥䉓訩죆Ꙟ臶栗鈣ഐ髱龬ʳ㔕칯镳婖突߁✧蜽孕垚͂䩈䌮둝ビ囿ꎑ뀎旋鶉❨龪嵶苳㵠ꮌ䣹酠퉏艘鬟싈Ġ欙洉㋪톝随眼豑잋ȴ졇퇦ꖀ諲砤釳者㩣ﲸݱ盨⚲쌽巃規Ἷ䥷㮄摢柌⁍᭏㐣놲☗燛쵾ᣣὤ澧ܞ予큛⛋抳硴ᑴ謈ቇ좍ꈘ줌욑倈䤫㯴ᴆ鈳蚢팠윆챯臁ꕃ퓠藷

'look like' is indeed what it is, this is a Unicode issue, the characters aren't recognized so you get these random Unicode characters instead of your local symbols

no wonder the content is not recognized as a database

did you write the .db file yourself or did someone else ?

[torn sphinx]

no got it from a friend he send me a program with that .db file

did he encrypt it ?

idk

its a .exe file

I'd ask him, it seems like he did

ok

[rigid mica]

    await db.execute('''
        CREATE TABLE IF NOT EXISTS warnings(
            user int,
            warns int,
            reasons text[]
        )''')```

It always gives error to 'something close to user'

am I doing something wrong here?

[fluid glen]

cur.execute("UPDATE orders SET deliverystat, booldone = (?, ?) WHERE id = (?)", (str(defstat), (str(closestat), (str(s1)))))

is giving me issues, is there something wrong with this? I get this error sqlite3.OperationalError: near ",": syntax error

[proven arrow]

In most databases 'user' is a keyword. So you should not use this as a identifier.

@fluid glen query should be, "UPDATE orders SET deliverystat = ?, booldone = ? WHERE ...."

[fluid glen]

ohh shit

thank you mate :)

[rigid mica]

@proven arrow sorry for the ping, could u help me out rq?

('''INSERT INTO warnings (reasons) VALUES ($1) WHERE member = $2''',reason +',', member.id)

I tried this

to make it insert in the right column

but WHERE cant be used in this

[proven arrow]

Please give some background. What are you trying to do with the query?

[rigid mica]

I'm trying to make it add a reason to the tagged member

[proven arrow]

Then if the tagged member already exists, you should be updating the row, not inserting.

[rigid mica]

ah like that

[proven arrow]

See the syntax for update, https://www.w3schools.com/sql/sql_update.asp

[rigid mica]

how do I check if it already exists?

[fluid glen]

cur.execute("UPDATE orders SET deliverystat = ?, booldone = ? WHERE id = ?", (str(defstat), (str(closestat), (str(s1)))))
now with this ^ though I'm getting this error:

sqlite3.ProgrammingError: Incorrect number of bindings supplied. The current statement uses 3, and there are 2 supplied.

what could the issue be

[proven arrow]

Pass values as an array or tuple @fluid glen

[rigid mica]

u got an example for me?

[fluid glen]

could you give an example how I might do that? :P

please 🙏

[proven arrow]

Just wrap the values in square brackets, ```py
cur.execute("UPDATE orders SET deliverystat = ?, booldone = ? WHERE id = ?", [(str(defstat), (str(closestat), (str(s1))))])

[fluid glen]

ay

ty <3

[rigid mica]

@bot.command()
async def warn(ctx, member: discord.Member = None, reason= None):
    try:
        await db.execute('''INSERT INTO warnings (member) VALUES ($1)''',member.id)
    except:
        pass
    await db.execute(
    '''INSERT INTO warnings (reasons) VALUES ($1)''',reason +',')
    await db.execute('''UPDATE warnings SET warns = warns + 1 WHERE member = $1''', member.id)
#remove from database
#await db.execute(f'''UPDATE warnings SET warns = 0 WHERE UserID = {member.id}''')

@bot.command()
async def warns(ctx, member: discord.Member):
    warns= db.record(f"SELECT warns FROM warnings WHERE member= {member.id}") 
    await ctx.send(warns)
@bot.event
async def on_ready():
    await create_db_pool()
    print("Database connected")```

@proven arrow

could u take a look at this

sometimes the reasons are null, even though I gave a reason

discord.ext.commands.errors.CommandInvokeError: Command raised an exception: NotNullViolationError: null value in column "member" of relation "warnings" violates not-null constraint
DETAIL: Failing row contains (null, 0, test,).

[proven arrow]

@rigid mica You are not inserting a reason when you insert.

await db.execute('''INSERT INTO warnings (member) VALUES ($1)''',member.id)

This is your code and there is no reason being added, so its null.

[rigid mica]

the part below adds a reason

await db.execute(
    '''INSERT INTO warnings (reasons) VALUES ($1)''',reason +',')```

[proven arrow]

Are you aware of what insert command does?

[rigid mica]

yh

I need to do update right?

[proven arrow]

Your database table is made up of rows. So each insert will add a new row.

[proven arrow]

Yes which ive already said to you before.

But why not just add the reason whilst inserting in the first place

INSERT INTO warnings (member, reason) VALUES ('lufthansa', 'some reason');

[rigid mica]

bc sometimes there already is a user

and it gave me an error

[proven arrow]

Appears your database design is flawed then. If a user can have many warnings it should be able to add rows regardless of whether there already is a entry for that user or not.

[fluid glen]

okay well

I got dis cur.execute("UPDATE orders SET deliverystat = ?, booldone = ? WHERE id = ?", [(str(defstat), (str(closestat), (str(s1))))])
and I get this error ```sqlite3.ProgrammingError: Incorrect number of bindings supplied. The current statement uses 3, and there are 1 supplied.

[grim vault]

You are doing [(a, (b, (c)))] instead of (a, b, c) or [a, b, c]

[fluid glen]

tyyyy

[rigid mica]

Okay I'm back

Is there a possibility to make it add reasons after the first reason given?

AttributeError: 'Pool' object has no attribute 'record'

is what I get for this

warns= db.record(f'''SELECT warns FROM warnings WHERE member= {member.id}''')

[nova cove]

fetchrow not record

[rigid mica]

yh

[rigid mica]

@grim vault may I ask you something?

    await db.execute('''
        CREATE TABLE IF NOT EXISTS warnings(
            member bigint PRIMARY KEY,
            warns int DEFAULT 0,
            reasons text
        )''')```

I got this now, but the reasons text can always only contain 1 thing. Can I like make it write behind the thing that is already there?

[paper flower]

I'm not sure what you're trying to ask

You want to know how to add new columns here?

[rigid mica]

no, but for example

I warn someone with reason lol

and I warn him again with reason lmao

only reason lmao will be there

since it replaced lol

I want it to be displayed like lol,lmao

behind the previous reasons

[paper flower]

You could get a warn for a user 🤷 if it doesn't exist create one

[rigid mica]

nono, thats not what I wanted to ask

For example mee6

[paper flower]

Use update statement?

[rigid mica]

thats what I do

@bot.command()
async def warn(ctx, member: discord.Member = None, reason= None):
    try:
        await db.execute('''INSERT INTO warnings (member) VALUES ($1)''',member.id)
    except:
        pass
    await db.execute(
    '''UPDATE warnings set reasons= $1 WHERE member = $2''',reason+',', member.id)
    await db.execute('''UPDATE warnings SET warns = warns + 1 WHERE member = $1''', member.id)```

its an update

[paper flower]

So what's wrong then?

[rigid mica]

if I warn someone 2 times with different reasons

it will only save the last given reason

[paper flower]

mhm, you want to save all of them?

[rigid mica]

yes

[paper flower]

So just don't replace the warnings?

[rigid mica]

well how do I do that

[paper flower]

Create a new one, you could get amount of warnings for certain user by using count

[rigid mica]

since it always replaces

[paper flower]

It doesn't, it's how you coded it

[rigid mica]

I wanna save all the reasons

[rigid mica]

well how do I make it not replace it?

[paper flower]

Don't update old warnings but create a new ones using insert

[rigid mica]

it will be under the column of warnings and on the row of user id right?

[paper flower]

I'm not sure what you mean

[rigid mica]

it has to be on the same row

the warns need to be linked to the user

and how do i make it write in the row of the member id?

[paper flower]

Just use a foreign key

[rigid mica]

u got an example?

[paper flower]

create table users(
   id integer primary key
);
create table warnings(
    id integer primary key,
    user_id integer references users,
    ...
);

[rigid mica]

yh I got that

a primary key

[paper flower]

Where?

You don't have a foreign key though

If you use user id as primary key you would only be able to store a single warning for each user

[rigid mica]

I see

so whats a foreign key then?

[paper flower]

Foreign key references a row in another table
https://www.postgresql.org/docs/14/tutorial-fk.html

[rigid mica]

@ebon skiff couldnt I like get the value inside the text column, take it out and overwrite it with the new and old one

[ebon skiff]

He is talking about a inner join

[ebon skiff]

Do you want to have different rows per warning or replace the old row?

I also highly recommend to have warning id's

[rigid mica]

I would want it if I do multiple warns, it will come in the same column like warn1,warn2

[ebon skiff]

Okay.

[rigid mica]

so I tried fetching what was inside the column first

but if the value is null, it gives error: basicly if the user hasnt been warned yet, its error.

[ebon skiff]

I recommend structuring the database first.

[ebon skiff]

Yeah this but it also needs a warning id if you ever want to remove a warning.

[paper flower]

You should create a separate column for your warning id's and make member a foreign key instead of primary key

[rigid mica]

all I would do is clear warnings

never remove a specific one

[ebon skiff]

I don't get why you would want the warn int to also increase that makes no sense.

[ebon skiff]

Ah k

[rigid mica]

if u ask warns it says user 'has ... warns' and below a list of the warns

[ebon skiff]

Well you can just use sql for that

[paper flower]

You could just remove all warnings for specific user or all the warnings, it's not hard

[rigid mica]

yh ik

pandabweer, can i explain it in dutch rq?

[ebon skiff]

I don't get why you would want to append text inside reasons and have all warnings inside one row that is pretty bad.

[rigid mica]

ik wil dus als ik iemand meerdere keren warn, dat het de eerste warning niet replaced met de nieuwe warning, maar in een formaat komt te staan zoals 'warning1,warning2

[rigid mica]

is it possible in multiple rows linked to the same user?

[paper flower]

Best solution would be to store individual warnings in individual rows:

id  user_id  reason
1   me       A
2   me       B

I can just select all warnings for a user:

select * from warnings where user_id = me

[rigid mica]

I see

[ebon skiff]

Yeah i know but this isn't the way to do it you can structure it like that when you get those values with python easily.

[rigid mica]

So new lines would be better?

[paper flower]

You could also store info like who issued that warning and when

[ebon skiff]

A lot, it is designed for that.

[rigid mica]

yh ik

[ebon skiff]

Lemme setup a dummy db you can mess with

[rigid mica]

@bot.command()
async def warn(ctx, member: discord.Member = None, reason= None):
    await db.execute('''INSERT INTO warnings (member) VALUES ($1)''',member.id)
    await db.execute(
    '''UPDATE warnings set reasons= $1 WHERE member = $2''',reason+',', member.id)
    await db.execute('''UPDATE warnings SET warns = warns + 1 WHERE member = $1''', member.id)```

so this would create new lines each time?

[paper flower]

Nope

You still have to change your database structure, also you can do everything you need with a single insert

[rigid mica]

thats true

[nova cove]

I am transitioning my SQLite database over to use the aiosqlite async bridge. I have a db util file, where I added async/await to all of my custom functions for things like execute, commit, etc. In my bot's class, I have a async function of the following:

async def init_db(self):
  for guild in self.guilds:
    await db.execute(f"CREATE TABLE IF NOT EXISTS GUILD_{guild.id} (UserID int NOT NULL, XP int DEFAULT 0, Level int DEFAULT 0, warns int DEFAULT 0, kicks int DEFAULT 0, mutes int DEFAULT 0, bans int DEFAULT 0)")

    for x in guild.members:
      if x.bot != True:
        await db.execute(f"INSERT OR IGNORE INTO GUILD_{guild.id} (UserID) VALUES (?)", x.id)

  db.multiexec("INSERT OR IGNORE INTO guilds (GuildID) VALUES (?)", ((guild.id,) for guild in self.guilds))

  await db.commit()
  await db.autosave(self.scheduler)

The autosave is a cron job: (i am using the AsyncIOScheduler which is what self.scheduler represents)

async def autosave(sched):
    await sched.add_job(commit, CronTrigger(second=0))

My on_ready function consists of: (this is not all of it, the rest is not helpful)

async def on_ready(self):
  if not self.ready:
    self.scheduler.start()
    await self.init_db()
    logging.info("Database initialized.")

The build function, including its decorator function is:

def with_commit(func: Callable[P, R]) -> Callable[P, Awaitable[R]]:
    async def inner(*args: P.args, **kwargs: P.kwargs) -> R:
        func(*args, **kwargs)
        await commit()

    return inner  

@with_commit 
async def build():
    if isfile(BUILD_PATH):
        await scriptexec(BUILD_PATH)

When I run the bot, I expect it to build the db, but instead I get an error: AttributeError: 'Result' object has no attribute 'execute'
Execute function:

async def execute(command, *values):
    await cur.execute(command, tuple(values))

I feel like I might be overcomplicating my code. Help would be appreciated

[paper flower]

async def execute(command, *values):
    await cur.execute(command, tuple(values))

Global state i see

Guess your cursor somehow got assigned a Result

[nova cove]

yeah

this is a new error i have never seen

[paper flower]

You just assigned to cur somewhere

[nova cove]

cxn = connect(DB_PATH, check_same_thread=False)
cur = cxn.cursor()

it is global state, yes

[paper flower]

Probably somewhere else

[nova cove]

hmm

idk how

[paper flower]

Also i'd recommend you to not reuse cursors, just create new ones

[nova cove]

i'd have to have that allover the place then'

[paper flower]

async with connection.cursor() as cursor:
    await cursor.execute(stuff)

[paper flower]

It's not too late to refactor

[nova cove]

this would be what I do when I want to create a new cursor?

[paper flower]

Yep

[nova cove]

ok

[nova cove]

mhmm

[paper flower]

There's actually an example in aiosqlite github:

async with aiosqlite.connect(...) as db:
    await db.execute("INSERT INTO some_table ...")
    await db.commit()

    async with db.execute("SELECT * FROM some_table") as cursor:
        async for row in cursor:
            ...

[nova cove]

yeah ik

i just dont wanna have to have

async with ... as cursor:

all over the place but

its better prob

[paper flower]

It's fine imo

[nova cove]

mhmm

[paper flower]

context managers are great, you don't have to close stuff

[nova cove]

mhmm

so my db util file would no longer have any function

[paper flower]

Yep, pretty much

[nova cove]

prolly for the better

[paper flower]

I'd also recommend you to try sqlalchemy, python objects would be easier to work with

[nova cove]

ik

ive used it before

ORM and shit

aight bro thanks for the help

i was going to start revamping my bot anyways

might as well get rid of the util file that isnt rlly needed

[nova cove]

I am thinking about converting my discord bot's db from SQLITE to PostgreSQL. I was wondering if I should just use asyncpg (which I was learning to use recently), try psycopg3, or use sqlalchemy. Any answers would be appreciated!

[ebon skiff]

asyncpg

[nova cove]

Ok thanks, I was prolly gonna use that anyways

[frigid glen]

you can avoid writing these everywhere with an ORM like pydbantic - an additional abstraction over sqlalchemy

[nova cove]

mhmm that’s why I was asking about sql alchemy above

Or something like it

And I have sql alchemy experience

[frigid glen]

some of the biggest positives of pydbantic are free migrations ( so avoid alembic) & caching integration, API is nice too, but I might be biased

[nova cove]

I’ll check it out

Kinda looks like Django models

[frigid glen]

inspiration for API comes from Django

[true swift]

Yep

@torn sphinx
I haven't got any experience on PostgreSQL, but in MySQL you can connect with a live environment with cmd

But, what OS are you using?

[vapid mist]

im using aiosqlite and am confused

how am i able to just get data? with asyncpg it was as simple as x.fetch(query) but i cant seem to find anything useful with aiosqlite

[nova cove]

async with db.fetchrow(...) as db:
  ...

something like this i believe

[vapid mist]

hm

[random arch]

is there any SQL to python tutorials?

[nova cove]

wdym sql to python

[potent bridge]

I was learning about flask API

in my main directory, why did this pop up

Is it safe to delete

.vs and database.db

This is all that I've imported

[rigid mica]

How do I wipe all lines in my postgresql database where member = member.id

[paper flower]

Sqlalchemy with asyncpg 😉

[paper flower]

Tbh I don't really like active record orms, and alembic makes you review your migrations which is a good thing

[meager niche]

has anyone used popsql?

[proven arrow]

DELETE statements can be supplied with a where clause to filter. You should consider to take a look at some basic sql guides, which should cover this stuff. There are some resources in the pinned messages of this channel.

[rigid mica]

@proven arrow I'm having some trouble

@bot.command()
async def warn(ctx, member : discord.Member = None, *, reason = None):
    await db.execute('''INSERT INTO warnings (member, reasons, moderator) VALUES ($1, $2, $3)''', member.id, reason, ctx.message.author.id )
    await db.execute('''UPDATE warnings SET warns = warns + 1 WHERE member = $1''', member.id)
    await ctx.send(f'{member} got warned by {ctx.message.author}, reason: {reason}')




@bot.command()
async def warns(ctx, member: discord.Member):
    try:
        r = await db.fetchrow(f'''SELECT warns FROM warnings WHERE member= $1''', member.id)
        d = await db.fetch(f'''SELECT reasons FROM warnings WHERE member= $1''', member.id)
        e=  await db.fetch(f'''SELECT moderator FROM warnings WHERE member= $1''', member.id)
        warns = r['warns']
        moderator = e['moderator']
        #await ctx.send(r['warns'])
        if warns > 1:
            embed = discord.Embed(
                title = 'Warns',
                description = f"{member} has got {warns} warnings:",
                color = (0x3498db)
            )
        else:
            embed = discord.Embed(
                title = 'Warns',
                description = f"{member} has got {warns} warning:",
                color = (0x3498db)
            )
        order = 0
        for y in d:
            order += 1
            embed.add_field(name = f'warning #{order}:', value = y['reasons']+ moderator[order] , inline = False)
        await ctx.send(embed=embed)
    except:
        embed = discord.Embed(
                title = 'Warns',
                description = f"{member} has got 0 warnings.",
                color = (0x3498db))
        await ctx.send(embed=embed)``` this is my code

and I input the moderator id in the database, and i try to get it out and enter it in embed when I ask the warns

[mossy onyx]

if i use with statement for connecting to my SQL server like this

with connect(
        host="localhost",
        user=input("Enter username: "),
        password=getpass("Enter password: "),
    ) as connection:
        print(connection)

so does the connection close automatically ?? or i have to do it like this
connection.close()

ping if reply

[nova cove]

connection.close()

[mossy onyx]

so i have too?

[nova cove]

you don’t have to ever close the connection though

but you prolly should when the action is executed

[mossy onyx]

hm okay

[nova cove]

This is MySQL connector correct

[mossy onyx]

yes

[nova cove]

https://dev.mysql.com/doc/connector-python/en/connector-python-api-mysqlconnection-close.html

[nova cove]

CREATE TABLE IF NOT EXISTS guilds
(
    guild_id bigint,
    PRIMARY KEY (guild_id)
);

Anybody know why this raises these errors:
Incorrect syntax near 'IF'. Expecting '.', ID, or QUOTED_ID.
An expression of non-boolean type specified where a condition is expected.
Incorrect syntax near 'guilds'.
Incorrect syntax near 'guilds'. Expecting '('.
Incorrect syntax near 'guild_id'. Expecting '(', or SELECT.
Incorrect syntax near 'guild_id'. Expecting '(', or SELECT.

I am using PostgreSQL btw

[rigid mica]

did u execute it?

[nova cove]

no, its just from writing it. raw sql IF NOT EXISTS never works for me and I want to fix it

[proven arrow]

Please share exactly how you are doing this. Your error does not match the sql you sent.

[nova cove]

i think i fixed it

no more errors are popping up

[clever steppe]

Is there a native and standardize way in SQL to add dates? I'm storing notes in this DB, and I want to have a column with the date of creation of the note, I'm using a sqlite3 database, I'm mentioning it since SQLite sometimes is different than other conventional databases. If not I guess I'll need to create a date TEXT column and use datetime.today() to get a string and insert it there.

If you have a better way of doing it, please mention it. Also ping on reply.

[fading patrol]

You're on the right track https://tableplus.com/blog/2018/07/sqlite-how-to-use-datetime-value.html

[paper flower]

Many orms support declaring defaults.such as datetime.now but databases can natively add current timestamp to newly created records, such as default now() in postgres

Also you should use datetime or date (if applicable) types

[clever steppe]

What does this means?

[paper flower]

It's a type like integer, varchar and others

[paper flower]

datetime would store date and time, date would store date

[clever steppe]

I didn't knew this, I just used datetime for everything, thanks

[paper flower]

It's helpful if you need just date

e.g. person's birthday

[clever steppe]

This was such a good link, it was exactly what I was looking for, thanks for real.

[torn sphinx]

Does anybody know how i can change the view of this so i can only see the databases i can access

[fading patrol]

Looks like PGadmin? I don't think that's possible, but once you save a connection you'll be able to see the one you want that way

[fading patrol]

Or can you specify the database when you create the connection, instead of just connecting to the server itself?

[torn sphinx]

ok ty for the infos

[gritty marlin]

Is this correct way for searching if input matches column on db

cursor.execute("SELECT * FROM `xplt_cases` WHERE casetype='XPLT' AND `future_id` LIKE '%case%' OR `rid` LIKE '%case%'")

[fallen vault]

I have a for loop thats inputing multiple rows of data. Should i commit after each row or after the for loop?

[torn sphinx]

loop

faster than connecting everytime

[paper flower]

Could you share the code?

[fallen vault]

!paste

[delicate field]

Pasting large amounts of code

If your code is too long to fit in a codeblock in discord, you can paste your code here:
https://paste.pythondiscord.com/

After pasting your code, save it by clicking the floppy disk icon in the top right, or by typing ctrl + S. After doing that, the URL should change. Copy the URL and post it here so others can see it.

[fallen vault]

https://paste.pythondiscord.com/muruyogiso.properties

[paper flower]

@fallen vault It should be fine to commit after your loop

[verbal steeple]

Hi

[fallen vault]

It’s not. I forgot to add the Values lmao.

[paper flower]

But it should be ok to commit 😅

[fallen vault]

Having troubles passing the variables. For whatever reason it’s not liking the open curly bracket. What’s the “proper” way to do this? I know passing variables directly opens you up to database attacks (was only using it because the script is the only one writing and it’s not taking user entry)

[paper flower]

It looks fine to me, what error are you getting?

[fallen vault]

sqlite3.OperationalError: unrecognized token: “{“

[paper flower]

Can you show your query?

[fallen vault]

Currently not anymore. Kid is sleep and I’m ssh-ing on a chrome book that I can’t log into discord on lmao

I can however take a pic of the new error message lmao.

Oh wait I have an iPhone.

[fallen vault]

cur.execute(" INSERT INTO countries (Country,NewConfirmed, TotalConfirmed, NewDeaths, TotalDea
ths, NewRecovered, TotalRecovered,Date) VALUES (%5,%5,%5 ,%5 ,%5 ,%5 ,%5 ,%s);""'". (Country,NewConfirmed
• TotalConfirmed, NewDeaths, TotalDeaths, NewRecovered, TotalRecovered, Date) )

The 5 are actually s

And there’s actually three double quotes at the beginning.

[paper flower]

I'm pretty sure you should use ? to substitute your arguments?

[grim vault]

Depends on the database module, but I don't see any { inside the SQL for the error mentioned.

[fallen vault]

Oh I didn’t put the new error, sorry thought I copied it. New error was a syntax error near %.

Alright, been throwing things at the wall. Finally got it figured out. Fortunately my daughter just woke up so i can close DBViewer (that was going to be a problem lol)\

[grim vault]

The error mentioned sqlite3, so you should use ? instead of %s as the doctor ordered :)

[fallen vault]

Same for aiosqlite?

Im only using sqlite to populate the database. About to switch to aiosqlite to actually start working on the bot

Oh the problem was the date field btw. Tried to wrap it in str() but it did not like that. I guess ill just record the date and save it as a module variable when i pull the json from the api

[grim vault]

You paste code is Date = {str(country['Date'])} which is a set of one string entry and sqlite doesn'tknow what to do with it.

[fallen vault]

Itll be alright. I dont really need it since the api is updated once a day for the free version.

Thank you though.

[gritty marlin]

Does this error means I have to update my mysql? or there is something in my code.

                cursor.execute("SELECT * FROM `cases` WHERE casetype='perm' AND 'future_id'=:iarg OR rid LIKE :sarg OR rid=:sarg2 OR did LIKE :sarg OR did=:sarg2 ORDER BY casetype DESC, future_id DESC", {"iarg": int(case), "sarg": "%|" + str(case) + "|%", "sarg2": "|" + str(case)})

[vapid mist]

whats the best way to fetch with aiosqlite?

[rigid mica]

my database is bugging. I warn a user with a discord bot, and it warns twice. I only got it running once so it shouldnt happen

it also doesnt happen for some of the commands

please ping me on response, it only happens in my warn cog, all my other cogs dont go double

[nova cove]

async with db.execute(“SELECT …”) as db:

[vapid mist]

and what will that return? in asyncpg it returns a simple record object, but aiosqlite has been a hard time for me

[random arch]

Could google sheets be viable as a database?

[brave bridge]

depends on how much data and throughput you need, but why?

[paper flower]

Would be simpler to use an actual database, like sqlite

[random arch]

Its not alot of data, someone i know could use a discord bot using the google sheet

[vapid mist]

got something working, however my fetchall() just returns [] even though there is data in the table

conn = await aiosqlite.connect('database.db')
c = await conn.execute("SELECT * FROM reward_roles")
print(await c.fetchall())```

[nova cove]

aiosqlite just uses async context managers. That query will select whatever you want from the table with is why I included SELECT and then ellipses as a placeholder

[vapid mist]

yeah i got that just that its returning []

[random arch]

Does anyone have a good tutorial for MySQL + Python?

[sly garden]

https://sqlbolt.com/
is great imo for learning SQL first
you can find simple examples of MySQL & Python

[random arch]

Do i need to learn every single tutorial

[fading patrol]

I would say, no just get down some of the basics and then start building stuff

[sly garden]

CRUD operations I'd mainly focus on

[hollow pike]

CREATE TABLE users (
    user_id serial PRIMARY KEY,
    email VARCHAR ( 50 ) UNIQUE NOT NULL,
    provider VARCHAR ( 50 ) NOT NULL,
    created_on TIMESTAMP NOT NULL,
    project_id INTEGER UNIQUE,
    CONSTRAINT fk_project
      FOREIGN KEY(project_id) 
      REFERENCES projects(id)
);

CREATE TABLE projects (
    id serial PRIMARY KEY,
    project_id INTEGER UNIQUE NOT NULL,
    name VARCHAR ( 50 ) NOT NULL,
    description VARCHAR ( 50 ) NOT NULL,
    difficulty VARCHAR ( 50 ) NOT NULL,
    repo_url VARCHAR ( 50 ) NOT NULL,
    created_on TIMESTAMP NOT NULL
);

I'm getting a Schema Error: error: relation "projects" does not exist error. Any Ideas? Thanks

[fleet ibex]

prolly would be projects_id(id) .. but i really cant say im 100 on that

[nova cove]

why do you have another project_id in the second table

[hollow pike]

I had to create the projects table before the users table...thats what the error was

[nova cove]

yeah thats what i thought the issue was

[balmy folio]

I'm having trouble translating specific joins from c# to Django. For example I see this kind of code:

db.someTable1.Join(
  db.someTable2,
  l => new { l.machineNumber, l.clientId },
  r => new { r.machineNumber, r.clientId },
  (l, r) => new 
  {
    historyId = l.rd,
    l.machineNumber,
    l.clientId,
    ...
  }
)

For reference, the tables do not contain any foreign keys and I am not able to modify the database.

How would I make the above in Django?

My attempt is

modelForTable1.objects.prefetch_related(machinenumber__machinenumberids_set', 'clientid__machinenumberids_set')

Am I on the right track?

[solemn root]

Hi, I've been working on a new command for my bot, but the code doesn't execute even though it's reachable. here's the structure of the command and the pastebin of my code:

command structure:
~pay [amt] <user mention>

Pastebin:
https://pastebin.com/243TeWtb
The print statements in my code are for debugging, but even they don't print. I don't know where I went wrong. The code complies, but my database doesn't register the changes even though all the database statements are valid & executable.

[balmy folio]

pastebin gives a 404 This page is no longer available. It has either expired, been removed by its creator, or removed by one of the Pastebin staff.

[solemn root]

I originally had it burn after read. I remade the pastebin. It will disappear in 10 mins

[verbal lava]

MongoDB py for user in collection: if user["username"] == username: return "UsernameAlreadyExistsError"`````` return collection_class(self.database, self.name + '.' + name, TypeError: can only concatenate str (not "int") to str

[burnt turret]

show more of your code

the error and the code don't match

[paper flower]

self.name + '.' + name either name or self.name is an integer 🤔

[grim vault]

>>> 1 + "1"
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
TypeError: unsupported operand type(s) for +: 'int' and 'str'
>>> "1" + 1
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
TypeError: can only concatenate str (not "int") to str
>>>```
so it must be `name`

[long summit]

I don't know where is appropriate to give tips like this but I saw this in a merge request today and thought I'd share. It is pretty simple yet useful to know if you want to avoid nested loops while getting field values from query strings.

Double for loop:

list_to_fill = []
    for item in query_string:
        for item_field_value in item.item_field.all():
            list_to_fill.append((item_field_value.type, item_field_value))

Using .values_list() - Could use .values() too, It gives you a list of dictionaries, if you prefer to reference things by their name/location rather than index:

list_to_fill = query_string.values_list(
    'item_field__type',
    'item_field__item_field_value',
)

[frigid berry]

how do i check if a table exists in sqlite3?

I looked and saw in a video a code that looked like this:

"SELECT name FROM sqlite3_master WHERE type='table' AND name='yourtablename'"```

problem is that I'm bugged in this, I put in place of "yourtablename" the name of my table and went to check, but it wasn't right

[frigid berry]

Should return 1 if the table exists and 0 otherwise

[grim vault]

No, you select the name, so it should be empty or the name of the table (and it should be sqlite_master without the 3).

If you want a number use count(*)

!e

import sqlite3

conn = sqlite3.connect(":memory:")
conn.execute("CREATE TABLE test_table(test_id INTEGER PRIMARY KEY)")

sql_name = "SELECT name FROM sqlite_master WHERE type = ? AND name = ?"
sql_count = "SELECT COUNT(*) FROM sqlite_master WHERE type = ? AND name = ?"

for bind in (('table', 'test_table'), ('table', 'unknown')):
    print(bind)
    row = conn.execute(sql_name, bind).fetchone()
    print(f"\tsql_name -> {row = }")
    row = conn.execute(sql_count, bind).fetchone()
    print(f"\tsql_count -> {row = }")

[delicate field]

@grim vault :white_check_mark: Your eval job has completed with return code 0.

001 | ('table', 'test_table')
002 | 	sql_name -> row = ('test_table',)
003 | 	sql_count -> row = (1,)
004 | ('table', 'unknown')
005 | 	sql_name -> row = None
006 | 	sql_count -> row = (0,)

[fallen vault]

Im having binding troubles. py @bot.command() async def a(ctx, *, location): db = await aiosqlite.connect('./covid.db') cursor = await db.execute("SELECT * FROM countries WHERE Country='?'",location) row = await cursor.fetchone() print(row) await db.close()
This give the following error: ProgrammingError: Incorrect number of bindings supplied. The current statement uses 0, and there are 4 supplied

The value being passed is 'Chad'

[willow rain]

auto cmmit?

[fallen vault]

Im just reading from the database, no need to commit. Right?

[willow rain]

What is happening after you run it?

[fallen vault]

i use the command and it gives me the ProgrammingError code. When i had the test message that was sending in my server so its after accessing the database.

[willow rain]

try

cursor = await db.execute(f"SELECT * FROM countries WHERE Country = {location}")

[fallen vault]

Sorry, after the connect command. Should be more specific lol

One second

[willow rain]

and

row = await cursor.fetchall()

because anyways only 1 value is being called right

[grim vault]

The second parameter must be a iterable (list or tuple):

cursor = await db.execute("SELECT * FROM countries WHERE Country = ?", [location])```

[willow rain]

What I gave works too

[fallen vault]

Gave a new error: OperationalError: no such column: Chad

[fallen vault]

One second

[grim vault]

and no ' for the ?

[willow rain]

There is no country in your database named Chad then

It selected everything from countries table where country name was Chad

turns out no Chad

[grim vault]

No it doesn't (missing quotes for the string) and you shouldn't do it like that.

[willow rain]

Well I have done it and it works so why not

and yea the quotes

[fallen vault]

Theres definately a country named Chad in there lol

[willow rain]

Wait let me give you an example

[fallen vault]

Well im not getting an error but im also not getting data. Ill make sure what i added after isnt affecting it

[willow rain]

cursor.execute(
        f"SELECT wallet_amt FROM account_table WHERE user_id = {ctx.author.id};"
    )
    db.commit()
    result_w = str(cursor.fetchone())
    result_w = str(result_w).strip('[](),')
    result_w = int(result_w)

This works

Its just that I commited.

and turned the value into a string nothing else

You can do the same too

[fallen vault]

This is the database. Chad was just the least amount of character for testing lol

[willow rain]

Ok just tell what you are trying to do

it will be better

Ok wait hold up

cursor is not supposed to be that

cursor = db.cursor()

[grim vault]

cursor.execute("SELECT wallet_amt FROM account_table WHERE user_id = ?", [ctx.author.id])
row = cursor.fetchone()
if row is not None:
    result_w = row[0]
else:
    result_w = None

[willow rain]

@bot.command()
async def a(ctx, *, location):
    db = await aiosqlite.connect('./covid.db')
    cursor = db.cursor()
    await db.execute(f"SELECT * FROM countries WHERE Country={location};")
    db.commit()
    row = await cursor.fetchall()
    row = str(row).strip("{}[](),")
    print(f"{row}")
    await db.close()

raw may not work but try it

[grim vault]

You don't need to commit a select, you didn't change the data.

[fallen vault]

This worked. I was running in circles because apparently VS Code opened a new terminal for me so all my attempts weren't actually updating.

[willow rain]

I do it because why not

I have a habit of commiting whenever I use SQL xD

[grim vault]

That: row = str(row).strip("{}[](),") is not good. row is a tuple, just use the index to get the column you want!

[sterile pelican]

How can I make it so if the value becomes negative the database raises an exception? For example, I need it to raise the exception if the money gets negative on update

UPDATE some_table SET money = money - 100 WHERE id = 1234```

Postgres

[proven arrow]

@sterile pelican You can add a check constraint if you want to handle this on the DB level.

[fleet ibex]

or look up custom error handling

[sterile pelican]

Could you show an example please

[proven arrow]

See this fiddle https://www.db-fiddle.com/f/5MMYtrKGHrXebfDc56yXfn/0

[grim vault]

!e

import sqlite3

conn = sqlite3.connect(":memory:")
conn.execute(
    "CREATE TABLE some_table("
       " id INTEGER PRIMARY KEY,"
       " money INTEGER DEFAULT 0 CHECK(money >= 0))"
    )
conn.execute("INSERT INTO some_table VALUES(1234, 50)")
conn.execute("UPDATE some_table SET money = money - 100 WHERE id = 1234")

[delicate field]

@grim vault :x: Your eval job has completed with return code 1.

001 | Traceback (most recent call last):
002 |   File "<string>", line 10, in <module>
003 | sqlite3.IntegrityError: CHECK constraint failed: some_table

[glossy coral]

I get this error when trying to make an AI id

[nova cove]

Where is the query that creates the table

[glossy coral]

didnt do it via query just did it via the ui

[nova cove]

you created the table in SQLite browser?

[glossy coral]

on sqlite

app

[nova cove]

That’s the table with the issue?

[proven arrow]

@glossy coral Which column are you trying to make auto increment for?

[glossy coral]

yes

[glossy coral]

i am trying to create a new table that takes creates int as AI

[proven arrow]

Once again, which field are you trying to use the auto increment feature on?

[nova cove]

I’m guessing you have an entry into the AI column that isn’t an integer

[proven arrow]

The error message is pretty clear

[nova cove]

^

[glossy coral]

sorted

ty

[glossy coral]

    def edit_employee(self):
        while True:
            try:
                verify_code = int(input('Admin code required (To exit type 0): '))
                if verify_code == self.code:
                    user_id = int(input('Enter ID: '))
                    user_decide = input(f"""*To exit type 'done'*
ENTER WHICH INFORMATION YOU WANT TO CHANGE FROM THE FOLLOWING:
Name, Surname, Age, EMail, Number, Country, City, Role
: """)
                    varchar = ['name', 'surname', 'email', 'country', 'city', 'role']
                    integer = ['age', 'number']
                    if user_decide.lower() in varchar:
                        new_value = input(f'Enter new {user_decide}: ')
                        query = f'UPDATE employees SET {0} = ? WHERE id = ?'.format(user_decide)
                        self.mycursor.execute(query, (new_value, user_id))
                        self.connection.commit()
                        print('Information updated.')
                    elif user_decide in integer:
                        new_value = input(f'Enter new {user_decide}: ')
                        query = f"UPDATE employee SET {user_decide}='{new_value}' WHERE id={user_id}"
                        self.mycursor.execute(query)
                        self.connection.commit()
                        print('Information updated.')
                    elif user_decide == 'done': break
                    else: print('Incorrect response, ')
                else:
                    print('Incorrect code.')
            except Error:
                print(f'Error: {Error}')```

acc

!past

[delicate field]

Pasting large amounts of code

If your code is too long to fit in a codeblock in discord, you can paste your code here:
https://paste.pythondiscord.com/

After pasting your code, save it by clicking the floppy disk icon in the top right, or by typing ctrl + S. After doing that, the URL should change. Copy the URL and post it here so others can see it.

[glossy coral]

https://paste.pythondiscord.com/ezaqunabaj.py

I have the problem where if I enter 'name' to update in sql i get the rror

Error: <class 'sqlite3.Error'>

[upper rampart]

   def storedata(self):
        decide = input("Data: ").lower()
        if decide == "name":
            psn = input("PSN: ")
            psn = str(psn)
            cursor.execute(f"INSERT INTO logs (PSN) VALUES({psn})")
            connection.commit()

Every time I try to add a string to my table in phpmyadmin, I get this error

I have the value set as VARCHAR in the table also

Can someone help?

[grand ice]

Value of a string?

[fading patrol]

If you're trying to ask a question appropriate to this channel, it's not very clear

[grand ice]

Nah was responding to the above but VALUE and noticed it said VALUES

Value usually being an evaluation of numeric string

[sinful rivet]

ok, is there any way to add a table name from a variable? ||i bet not understand, so here's code from my understanding||

def add_code(code):
    code = str(code)
    cur.execute("INSERT INTO (codes) VALUES (?)",(code,))
    cur.execute('''CREATE TABLE IF NOT EXISTS ?
             (
             ID             TEXT    NOT NULL
             );''',(code,))
    conn.commit()

:l

[proven arrow]

No, this is not possible since how the prepared statements operate.

[sinful rivet]

noooooo

ok

[proven arrow]

Generally this kind of thing is not necessary, and the only way to do such a thing would be to build the query yourself after sanitising input.

[proven arrow]

Why do you need to dynamically create tables? Maybe there is a better way to achieve what you are doing.

[sinful rivet]

🤷‍♂️ so i wanna make a claimable code for my game, and every time i create a code, it also will create a table named same as the code (so ppl can't claim code twice

[proven arrow]

Yeah that’s bad design.

[sinful rivet]

any idea what should i do instead :V

[proven arrow]

Codes should be stored in a single table.

You can have two columns. code and status. Where status is the value of whether the code has been claimed or not.

[sinful rivet]

uhh, i wanna add the claimer's id instead

[proven arrow]

This approach allows you to add additional data to the code claim process as well. Such as when it was claimed and by who.

[proven arrow]

Yes it was an example. You did not provide full details of requirements. But you get the idea, of how it should be structured.

[sinful rivet]

new question, is it possible to add a list into a row

[proven arrow]

Yes, but again you should try avoiding this.

[sinful rivet]

;-; any good idea then?

[proven arrow]

You can look up database normalisation.

[sinful rivet]

not understand

[proven arrow]

https://en.m.wikipedia.org/wiki/Database_normalization

[sinful rivet]

k ty

[sinful rivet]

can it help me insert list into row

[proven arrow]

It can help you understand why you should not store a list in a column. This violates the first rule of normalisation, which I linked you.

[sinful rivet]

:( i needit

need it

[proven arrow]

So do it the correct way. 🤷‍♂️ You may think it’s silly but this is how databases were designed to be used. Don’t try to fight your database, otherwise maintaining and writing queries later will just be a pain.

[sinful rivet]

:l

as i said before.... it's to keep the id of ppl who alr claim code, and the second row is the code

so

how?

[proven arrow]

You need to create a many to many relationship, between your users, and codes table. For this an extra table is required, which allows you to keep a map of which codes have been claimed by which users.

[paper flower]

It would be one to many relationship if single code can't be claimed by multiple users

Users
id | username
1    Doctor
2    Soap

Codes
id | code
1    abc
2    abc2

Claimed Codes
user_id | code_id
1         1
1         2

You would have 3 tables like these, users and codes tables and one to associate them in some way, in this example it's who clamed which code

I claimed both codes 😃

create table users(
  id serial primary key,
  username varchar(255) not null
);
create table codes(
  id serial primary key,
  code varchar(255) not null
);
create table claimed_codes(
  user_id integer references users(id),
  code_id integer references codes(id)
);

This is a many to many relationship, but if you want code to only be claimed by single user, then you could add user_id column to codes table instead and get rid of claimed_codes table (you don't need a secondary table in one to many relationship)

@sinful rivet

[runic coyote]

can someone ELI5 ORM?

[paper flower]

What's ELI5?

[runic coyote]

explain like i'm 5

[thorn rivet]

i need some help idk the issue lol this is my line of code

watchdb.search(User.avatarid == avatarid and User.userid == ctx.author.id)

i thought this would give all results that meet both conditions but it doesnt it returns results with same userid but different avatarids and not sure why also im using tinydb

finally figured it out was

watchdb.search((User.avatarid == avatarid) & (User.userid == ctx.author.id))

i guess both makes since was pulling my hair out lol

[paper flower]

ORM just creates your python objects from db data

Also helps you to query database from your language without using sql

[runic coyote]

So it just a layer for easier use?

In its core it use raw query?

[paper flower]

Yep, it would translate everything to sql anyway

But if you're selecting something from your database it could map it to python objects

class User(Base):
    __tablename__ = "users"

    id: Mapped[uuid.UUID] = Column(
        UUID(as_uuid=True),
        primary_key=True,
        default=uuid.uuid4,
    )
    username: Mapped[str] = Column(String(length=255), nullable=False)

Doing

select(User).filter(User.username == "Doctor")

would select all users with username "Doctor", it would send sql like this to the database:

select users.id, users.username from users where users.username = 'Doctor';

But it would also map results to instances of User class 😃

[runic coyote]

so for more freedom you would use raw query rather than orm?

[paper flower]

Some ORMs limit you on what sql query can you create from python, some don't

Django ORM is quite limiting imo, sqlalchemy isn't

[runic coyote]

thanks that definitely clears something up

[subtle basin]

hello, anyone uses TortoiseORM?

does any ORM allows to bind multiple models to the same one table?

[paper flower]

Just curious - why do you want to do this?

SQLAlchemy could do this, not sure about tortoise 🤔 You're looking for single table inheritance

Doesn't seem like TortoiseORM supports model inheritance/polymorphism if i'm not missing something

[subtle basin]

I use it for dynamic configuration in ETL implementation. Some data has to be grouped by different models, because I aggregate it with complex logic

[subtle basin]

Inheritance works fine, but if multiple models are used in initialization there's a error about cycle fk relation

[paper flower]

🤔

You're having troubles saving them?

Sorry, i don't follow really 😅

Could you maybe show your models?

[subtle basin]

one row -> one object, and bound to ORM Model, so, if 5 objects have same model they are aggreagated and passes as values

[paper flower]

Mhm

If i simplify (so i can give an example):
You could have multiple Post models and m-m relationshop to Tag model?

And posts 1...5 have the same tag, right?

[gusty path]

is it worth compressing data before putting it in the database?

[paper flower]

I'm not sure but might depend on the data

Usually no

[gusty path]

like a 1080x1080 image

[paper flower]

Don't store images in the database 👍

Also they're already compressed

[gusty path]

or a really long json

[paper flower]

Don't store json in databases 😅
If you need to postgres has bson type - binary json

Should be pretty efficient, i didn't use it tho

[gusty path]

oh cool thanks

[gusty path]

did u mean jsonb?

[paper flower]

Maybe, i don't really remember what it's called 🤔

Usually you can normalize your data to not store json in your tables, and storing images is just a bad idea

[gusty path]

storing images was a failed example I didn't mean that

[paper flower]

Just saying 😅

[gusty path]

my json goes 2 or 3 levels deep so I cant really normalize it without inefficiently using more rows

[paper flower]

If it's well structured i'd say just normalize it

[gusty path]

i wont ever need to analyze any data with select statements (which is the point of normalizing) so its not worth it

[paper flower]

If you want to just store i'd say you can go with json

[sullen sky]

im trying to find difference between dates in sql

but it gives me this error

anyone know why?

[celest zodiac]

@sullen sky apparently it's just two parameters
SELECT DATEDIFF("2017-06-25", "2017-06-15");

[misty vigil]

^

[sullen sky]

ahh okk

[misty vigil]

try removing the Day part at the start

[sullen sky]

every source online has the first parameter for some reason

[misty vigil]

ah it might just be an example

[sullen sky]

hmm

anyway, thank you very much for your time! @celest zodiac @misty vigil

[misty vigil]

"funny valentine theme" so youre a jojo fan huh

glad to help :D

[sullen sky]

uhhhhhhh

[misty vigil]

but does the code work though?

[sullen sky]

yes it works!
thanks!

https://www.w3schools.com/SQl/func_sqlserver_datediff.asp

[misty vigil]

nice!

[sullen sky]

here it says its a parameter

weird

[misty vigil]

great to hear that

ah

w3schools' resources are usually outdated, that might be why

[sullen sky]

ah ok

thanks

[grim vault]

MySQL != SQLServer

https://www.w3schools.com/SQl/func_mysql_datediff.asp

[rigid mica]

is there a way to set a variable in a cog available in another cog?

[fleet ibex]

How would a query look if I need to sort a column by max, and get everything after the nth max .. so like,
100
100
90
90
80 everything 80 and after to 0

the column will have many repeated values of say like 81-100

i think i got it, just have to use a high limit to get offset to besure I catch everything

[paper flower]

Yeah, seems like a case for offset

[fleet ibex]

i fought with it, i could not find a reference that said I had to use it with limit.. was just always referenced with limit in everything I read.. so i guess you cant.. :(

[paper flower]

You can use offset and limit independently

e.g.
select * from users offset 42;
select * from users limit 5;

[fleet ibex]

weird i tried and always got an error near offset.. I will examine it again when i get a chance

[rigid mica]

@paper flower I'm not sure if you know (also sorry for the ping) but how do i for example make something that just adds a value to a row that already exists?
for example on_guild_join I add prefix and guild id, but I would also like to store 2 channel ids in there. But in the same row

i got a command for adding btw

[paper flower]

Hm, i'm not sure what you want to do

[rigid mica]

hold up

[paper flower]

Why do you need to store channel ids?

[rigid mica]

    @commands.Cog.listener()
    async def on_guild_join(self,guild):
        await db.execute('''INSERT INTO setup (guild, prefix) VALUES ($1, $2)''', guild.id, '!')
        print(f'added {guild} to database')

    @commands.command()
    async def welcome(self,ctx,channel : discord.TextChannel = None):
        if channel is not None:
            await db.execute('''UPDATE setup SET welcome = $1 WHERE guild = $2''', channel.id, ctx.guild.id)
            welcome = await db.fetch('''SELECT welcome FROM setup WHERE guild = $1''', ctx.guild.id)
            channel1 = welcome["welcome"]
            await channel1.send('Channel configured as welcome channel')```

[rigid mica]

as welcome channel

[paper flower]

You can get server of the message and look up the prefix if that's what you want

What's stopping you from adding welcome_channel column?

[rigid mica]

I added welcome column

but I dont feel like its actually adding things to my database

maybe an issue of dc atm

[paper flower]

Maybe 🙂

[rigid mica]

it didnt register on guild join

[paper flower]

You didn't commit btw

[paper flower]

^

[rigid mica]

wdym commit?

[paper flower]

What connector are you using?

aiosqlite?

[rigid mica]

async def create_db_pool():
    global db
    db = await asyncpg.create_pool(database = "", user = "", password = "")
    await db.execute('''
        CREATE TABLE IF NOT EXISTS setup(
            guild bigint,
            prefix text,
            welcome bigint
        )''')```

asyncpg

[paper flower]

I see

[rigid mica]

but doctor

would this code work?

    @commands.command()
    async def welcome(self,ctx,channel : discord.TextChannel = None):
        if channel is not None:
            await db.execute('''UPDATE setup SET welcome = $1 WHERE guild = $2''', channel.id, ctx.guild.id)
            welcome = await db.fetch('''SELECT welcome FROM setup WHERE guild = $1''', ctx.guild.id)
            channel1 = welcome["welcome"]
            await channel1.send('Channel configured as welcome channel')```

[paper flower]

Seems like you don't have to issue commit in asyncpg 🤔

[rigid mica]

bc I saw someone who said I used a channel argument instead of a channel object

so I was kinda confused

[paper flower]

Looks fine to me, maybe channel is None?

[rigid mica]

It shouldnt

I mention it

but my bot doesnt want to respond atm

[paper flower]

Debug it 😉

[rigid mica]

oh I lost connection to dc

[paper flower]

Go do other things, you would have troubles developing your bot atm

[rigid mica]

true

    @commands.Cog.listener()
    async def on_guild_join(self,guild):
        await db.execute('''INSERT INTO setup (guild, prefix) VALUES ($1, $2)''', guild.id, '!')
        print(f'added {guild} to database')

    @commands.command()
    async def welcome(self,ctx,channel : discord.TextChannel = None):
        if channel is not None:
            await db.execute('''UPDATE setup SET welcome = $1 WHERE guild = $2''', channel.id, ctx.guild.id)
            welcome = await db.fetch('''SELECT welcome FROM setup WHERE guild = $1''', ctx.guild.id)
            channel1 = welcome["welcome"]
            await channel.send('Channel configured as welcome channel')``` but like could u take a look at it? u think it would work this way?

alright the on_guild_join seems to be working

@paper flower error: discord.ext.commands.errors.CommandInvokeError: Command raised an exception: TypeError: list indices must be integers or slices, not str

in here

        @commands.command()
        async def welcome(self,ctx,channel : discord.TextChannel = None):
            if channel is not None:
                await db.execute('''UPDATE setup SET welcome = $1 WHERE guild = $2''', channel.id, ctx.guild.id)
                welcome = await db.fetch('''SELECT welcome FROM setup WHERE guild = $1''', ctx.guild.id)
                channel1 = welcome["welcome"]
                await channel.send('Channel configured as welcome channel')```

alright, it seems to be working with setting the id in the database

[paper flower]

i guess db.fetch returns list and not a dictionary

[rigid mica]

no more coding for today

got my bot running, but is offline

bot will have trouble logging in

and sending messages

[rigid mica]

but doctor, I have this for my warnings sytem py r = await db.fetchrow(f'''SELECT warns FROM warnings WHERE member= $1''', member.id) warns = r['warns']

and here i use a dictionary too

[paper flower]

fetch is not fetchrow

[rigid mica]

truee

thats the reason

if u fetch a row, u get multiple responses right?

[abstract pivot]

when should you create a transaction from an acquired connection from an asyncpg.Pool/what does it do?

[rigid mica]

wdym?

I'm not that experienced with databases, could u explain it a little more easier?

[abstract pivot]

I think you misunderstood, I wasn't responding to your question

[rigid mica]

oh my bad

[harsh pulsar]

creating a transaction sets up a reset point. if something goes wrong in the code, you can handle it by "rolling back" the transaction, causing the changes in the transaction to be discarded. when you are satisfied that the changes have proceeded correctly, you can save them to the database by "committing" the transaction.

starting a transaction is like opening a new document; committing the transaction is like saving the document; rolling back the transaction is like closing the document without saving.

a transaction also has the property that if anything in the transaction results in an error, then the entire transaction is rolled back. this prevents your database from getting stuck in a bad state with half-finished operations

the wikipedia page has a decent explanation https://en.wikipedia.org/wiki/Database_transaction

[knotty wharf]

I have read on w3schools about prevent SQL Injection
'he mysql.connector module has methods to escape query values:
Escape query values by using the placholder WHERE first_name = %s method

I am using MariaDB, for the WHERE statement they advise on their website to use WHERE first_name = ?
does the '?' also prevent from SQL injection or should I use '%s' ?

[abstract pivot]

thanks! should transactions always be used to prevent any half-finished operations?

or are there more specific cases where they should be used

[harsh pulsar]

different database libraries support different placeholders. %s, ?, and $1 are all common choices. you should consult the documentation for the python database connector library that you are using in your code.

[knotty wharf]

@harsh pulsar okay thank you!

[knotty wharf]

By the way, I already created my database using SQL
But now I want a general user interface (GUI) to do queries like SELECT/INSERT/UPDATE/DELETE
Where should I start? I don’t need it to be really fancy like a website. But just a GUI where you can input some queries and looks up for things.

[nova cove]

as in pressing a button executes a query

[knotty wharf]

I don't get it @nova cove 😢

[nova cove]

For example, press a random button and it fetches data from the db through a query

[knotty wharf]

oh ok, so it would be fine if I'm using TKinter for that?

[nova cove]

it should be fine, but I have never used tkinter before so idk about how it works with databases

[knotty wharf]

what would you advise me to use ? what are you using ?

[nova cove]

wdym what am I using

[knotty wharf]

you are working with database right?

are you using any user interface ?

[nova cove]

I am making a discord bot where I use a database

[paper flower]

There are management systems for databases, like pgAdmin for postgres, sqlbrowser for sqlite, etc...

[nova cove]

I have not implemented databases and worked wit them in websites yet, which I plan on doing

[knotty wharf]

oh alright

thank you @paper flower I'll check into it, I am currently using MariaDB

[paper flower]

I often use db browser built-in into pycharm

[random arch]

Should I learn MongoDB over MySQL?

[paper flower]

They're different in nature (NoSQL / SQL), both are quite popular

[random arch]

i dont know if this is the right channel to ask but if im gonna work for a company or smth, does it matter which one i pick?

[paper flower]

I'd say SQL, it's wide-spread, also learning nosql after sql shouldn't be hard

[random arch]

ok

is there a way to see an SQL database as i would look at a spreadsheet like excel or google sheets?

[paper flower]

Yes, there are tools like sqlitebrowser and pgAdmin, also pycharm professional should have a db browser

VSCode should have an extension too

[torn sphinx]

@random arch Check out mysql2excel if you want a gentle intro into what you're used to vs sql.

[solemn root]

Hi, I've been working on a new command for my bot, but the code doesn't execute even though it's reachable. here's the structure of the command and the pastebin of my code:

command structure:
~pay [amt] <user mention>

Pastebin:
https://pastebin.com/S5kDGx1i
The print statements in my code are for debugging, but even they don't print. I don't know where I went wrong. The code complies, but my database doesn't register the changes even though all the database statements are valid & executable.

[rigid mica]

How do I pass a variable of my database to another cog?

[proven arrow]

Where are you committing the changes to the db?

[rigid mica]

asyncpg.exceptions.TooManyConnectionsError: sorry, too many clients already

can i close a client?

[delicate field]

Hey @shut zephyr!

It looks like you tried to attach file type(s) that we do not allow (.pdf). We currently allow the following file types: .gif, .jpg, .jpeg, .mov, .mp4, .mpg, .png, .mp3, .wav, .ogg, .webm, .webp, .flac, .m4a, .csv, .json.

Feel free to ask in #community-meta if you think this is a mistake.

[proven arrow]

You can use a connection pool if you aren’t already, or if you are already then increase the default connection value to something higher.

[rigid mica]

not sure how I would do that

[proven arrow]

Is this for a discord bot ? If so you can take a look at what the helper has posted, #databases message

[rigid mica]

yeah ik

but I call it multiple times

its not just the loop

for example

    #pingreact
    @commands.Cog.listener("on_message")
    async def pingreact(self,message):
        prfx = await db.fetchrow('''SELECT prefix FROM setup WHERE guild = $1''', message.guild.id)
        prefix = prfx["prefix"]
        if re.fullmatch(rf"<@!?{self.bot.user.id}>", message.content):
            await message.channel.send(f"My prefix here is `{prefix}`")
            return```

in this cmd, I take the prefix out

or should I just add this to the loop?

[proven arrow]

Not sure I follow. How is this related to creating a pool?

[high geyser]

I'm planning to move to postgreSQL from mysql because it natively supports uuids

I want to use uuids, to expose it to the end user. I dont want to show the autoincrement pk to the end user as it is easy to guess

So I'm thinking of having an int pk and uuid to expose to the client.

How would I handle foreign keys here? Would foreign keys be uuids or ints?

[nova cove]

https://dev.mysql.com/doc/refman/5.6/en/create-table-foreign-keys.html

[high geyser]

What I meant to ask was, if I use int as foreign key, I would expose it to the client

So should I be using uuids here?

Does that have any performance hits?

[rigid mica]

Traceback (most recent call last):
File "C:\Users\bert\AppData\Local\Programs\Python\Python38\lib\site-packages\discord\client.py", line 351, in run_event
await coro(*args, **kwargs)
File "C:\Users\bert\AppData\Local\Programs\Python\Python38\lib\site-packages\discord\ext\commands\bot.py", line 1034, in on_message
await self.process_commands(message)
File "C:\Users\bert\AppData\Local\Programs\Python\Python38\lib\site-packages\discord\ext\commands\bot.py", line 1030, in process_commands
ctx = await self.get_context(message)
File "C:\Users\bert\AppData\Local\Programs\Python\Python38\lib\site-packages\discord\ext\commands\bot.py", line 940, in get_context
prefix = await self.get_prefix(message)
File "C:\Users\bert\AppData\Local\Programs\Python\Python38\lib\site-packages\discord\ext\commands\bot.py", line 885, in get_prefix
ret = await discord.utils.maybe_coroutine(prefix, self, message)
File "C:\Users\bert\AppData\Local\Programs\Python\Python38\lib\site-packages\discord\utils.py", line 514, in maybe_coroutine
return await value
File "c:\Users\bert\Downloads\discord bots\Bertie's discord bots\bertie's discord bots.py", line 20, in get_prefix
db = await asyncpg.create_pool(database = "Bertie", user = "postgres", password = "B3rtm0r3@u!!")
File "C:\Users\bert\AppData\Local\Programs\Python\Python38\lib\site-packages\asyncpg\pool.py", line 413, in async__init
await self._initialize()
File "C:\Users\bert\AppData\Local\Programs\Python\Python38\lib\site-packages\asyncpg\pool.py", line 441, in _initialize
await first_ch.connect()
File "C:\Users\bert\AppData\Local\Programs\Python\Python38\lib\site-packages\asyncpg\pool.py", line 133, in connect
self._con = await self._pool._get_new_connection()
File "C:\Users\bert\AppData\Local\Programs\Python\Python38\lib\site-packages\asyncpg\pool.py", line 511, in _get_new_connection
con = await connection.connect(
File "C:\Users\bert\AppData\Local\Programs\Python\Python38\lib\site-packages\asyncpg\connection.py", line 2085, in connect
return await connect_utils._connect(
File "C:\Users\bert\AppData\Local\Programs\Python\Python38\lib\site-packages\asyncpg\connect_utils.py", line 881, in _connect
return await _connect_addr(
File "C:\Users\bert\AppData\Local\Programs\Python\Python38\lib\site-packages\asyncpg\connect_utils.py", line 781, in _connect_addr
return await __connect_addr(params, timeout, True, *args)
File "C:\Users\bert\AppData\Local\Programs\Python\Python38\lib\site-packages\asyncpg\connect_utils.py", line 831, in __connect_addr
await compat.wait_for(connected, timeout=timeout)
File "C:\Users\bert\AppData\Local\Programs\Python\Python38\lib\site-packages\asyncpg\compat.py", line 66, in wait_for
return await asyncio.wait_for(fut, timeout)
File "C:\Users\bert\AppData\Local\Programs\Python\Python38\lib\asyncio\tasks.py", line 483, in wait_for
return fut.result()
asyncpg.exceptions.TooManyConnectionsError: sorry, too many clients already

[rigid mica]

not that, but the prx = await db.fetchrow

I cant run my commands bc of that error

I use my database a lot

[random bobcat]

anybody there

[rigid mica]

me

[random bobcat]

THANK YOU

OK

I

need

halp

with mongo db

[rigid mica]

...

I dont know mongo

[oak oyster]

Don't flood

[random bobcat]

):

[oak oyster]

so ask away

[random bobcat]

ok

[oak oyster]

sooo are you gonna ask your question?

[random bobcat]

yes

i need halp with my mongo db setup for my bot

[oak oyster]

sure

[random bobcat]

THANK YOU

[oak oyster]

soo what part do you need help with in the setup?

[random bobcat]

like i have an account

and i need to make a cluster

[oak oyster]

mhm?

[random bobcat]

and the part where i have to linkit to thecode

is

kinda confusing

[oak oyster]

the mongo uri?

[random bobcat]

yes

[oak oyster]

oh

[proven arrow]

You can also use snowflake ids. Same as discord uses.

[oak oyster]

just copy the link mongo gives you, and paste it where you need to paste it

[random bobcat]

oki gib a min

ryt now i need to go to shared ryt?

or

[nova cove]

I don’t think there should be any MAJOR difference. But, just know that a UUID is 128-bits long so using it might have some performance hits on the program since it isn’t ordered and very large

[random bobcat]

im a peasant basically so i cant use moni FOR NOW

[paper flower]

Inserts could take longer, in general, it should be around the same

[oak oyster]

just choose the free option

[paper flower]

32/64 bits vs 128 bits is not that big of a difference, you're already probably storing string data in your columns too, it's A LOT of bytes

[nova cove]

mhmm

[random bobcat]

where is it tho

OK GOT IT

[oak oyster]

yes

[random bobcat]

okk

now i need to add ip address ryt?

[oak oyster]

yes

[random bobcat]

now?

[oak oyster]

click connect

[high geyser]

How do you do that?

[random bobcat]

ok

@oak oyster

halp

[oak oyster]

second option

[random bobcat]

okk

which version

3.12?

[oak oyster]

3.6 or later

[random bobcat]

okk

now

[oak oyster]

copy the url, replace username/password with your username/password and myFirstDatabase with ur database name

[random bobcat]

okk

then

okk

how do i find the database name

i kept literally nothing

oh wait

nvm

its cluster0

[oak oyster]

no thats your cluster name

[random bobcat]

umm

thenn....

[oak oyster]

cluster: #mongo format
  database:
    collections
``` you have to make a database

[random bobcat]

hmm

[proven arrow]

The column type would be bigint. https://en.m.wikipedia.org/wiki/Snowflake_ID

However, another alternative approach is to stick with your current method of doing it but to add a new column for uuid/snowflake id which acts as a slug. This would cover your security concerns, and also not have that much of an impact on performance.

[oak oyster]

click create maybe

[high geyser]

Okay, but for foreign keys if I use int it would be exposed to the client

[random bobcat]

its showing create cluster not database

[proven arrow]

And so which is why you can add a slug column. That would hold the value you show on the client side.

[high geyser]

So I should use slugs as foreign keys?

[random bobcat]

;-;

[oak oyster]

go back, and click Browse Collections

[random bobcat]

okk

[oak oyster]

then click Add My Own Data

[random bobcat]

theres no database or collections

okk

which is greater

[oak oyster]

it will prompt you for a database & collection name

[random bobcat]

database or collection

[oak oyster]

?

[random bobcat]

like

which is inside which

[nova cove]

collection stores db's

[random bobcat]

is collection inside database

okk

ty

DONE

[oak oyster]

after you've done that, replace myFirstDatabase from the uri earlier to the collection/db name

[random bobcat]

yes i did it

umm

[random bobcat]

shud i include collection name or database name

sry for so much qns im new ;-;

[oak oyster]

not really sure

but i used my collection name

[random bobcat]

hmm

okk

then ill use it

now how to link it

i feel like its the other way

like

[nova cove]

there should be a place to get the URL with the info

i haven't used mongo in a while lmao

[random bobcat]

oof

oki lemme see tuto

[nova cove]

lemm ecreate a db rq

[random bobcat]

okii

[oak oyster]

?

[oak oyster]

try it and see

[random bobcat]

okk

[nova cove]

https://docs.mongodb.com/guides/server/drivers/

[random bobcat]

okk

[proven arrow]

No. Slugs are on the parent table themselves. It’s only used as an alias to the primary key. What is the current issue you have with using the primary key value? What resource do you not want the user to easily guess?

[high geyser]

The user id

If I have a todos table, then todos.user_id returns the users int pk

[solemn root]

I just added commits after I update each user’s cash. Still not seeing any change in the db or an output from my debugger print statements. 🤔

[proven arrow]

You should still be enforcing some sort of access control policy application side as well to see if only the authenticated user can access their their posts. As for people guessing user ids it shouldn’t really matter then as you are controlling that each user can only access or manipulate their own items, which you should be doing anyways. If you really want to obscure the ids then your options are two use a uuid/snowflake as the primary/foreign key.

Regarding slugs the alternative approach I gave you, it would mean you have that you have to make a lookup on the users table with the slug field. You seem to be getting confused on how they work. This should help you, https://stackoverflow.com/questions/19335215/what-is-a-slug

[carmine anvil]

    def registration_stat(self):
        if (self.registration_exp - date.today()) < 14:
            return "Soon"
        elif (self.registration_exp - date.today()) < 0:
            return "Expired"
        else:
            return "Good"```
Would this be an appropriate use of the hybrid property? or is there a better way to check if a datefield has expired

[nova cove]

https://docs.sqlalchemy.org/en/14/orm/extensions/hybrid.html

[paper flower]

Hybrid properties are meant to be executed on db OR client side, if you need only one of them use column_property / property respectively, otherwise you have to two functions, one for python side, second one for an expression

@nova cove linked you the documentation 😉

[nova cove]

👍

[carmine anvil]

Ok so as long I don't need to use the property in a query I should @property?

[paper flower]

Right

Like in any python class

[high geyser]

If snowflakes are used by discord at a very large scale, I think wont be facing any issues using uuids in the same way- as primary ids, right? Obviously I am doing this at a very small scale

So I think I'll use uuids as primary keys

I also found this nice little function to generate snowflakes with postgres btw: https://gist.github.com/beginor/9d9f90bc58e1313f6aecd107f8296732

[carmine anvil]

thanks

[paper flower]

You could even use sqlite, i don't think there would be ANY problems

[abstract pivot]

Could anyone answer this? Thanks

[carmine anvil]

@paper flower if I wanted to change this so that it's applicable to any date field in my model is it as simple as
def registration_stat(self, date):

and then passing the field to the function client side

[carmine anvil]

or rather how do i create a function that can be applied to any field in my model

[woeful torrent]

Is there a more efficient way to getting the code of a query with a limit than having to add the count to every row with func.count().over()?

Ideally I'd like the count and the queried columns in separate rows

but the only way I can think of to do this is to run the query twice - once for counting and once to get the actual columns that are counted

[torn sphinx]

Does anyone know how I can do a perms check with the database, I have a command to add a person in the premium category and I would like her to use only those commands. I use mongodb

[carmine anvil]

nvm @classmethod was the answer

[errant ridge]

you guys are about to hear the simplest question ever

how do i access a .db file using sqlite3 on terminal

its just not letting me

am i being extra thick or

[grim vault]

Seems like there is a space in the filename, use quotes. eg:
.open '/my/path/with spaces in it/file.db'

[woeful torrent]

another option is just adding the filename as a commandline argument like sqlite3 my/database.db or sqlite3 "my/database with spaces.db"

In general in the terminal if you want to use a path with space you need to either 1. add quotation marks around your path or 2. escape spaces and special characters with backspace

Otherwise the space will be interpreted as an argument separator

[hollow pike]

https://www.db-fiddle.com/f/gVEjraNkmAUXyfS9pLB2Gr/3
How can I get my trigger function to work?

Every time a new user is inserted into the "users" table, I want to add a new row to the "profile" table also but doesnt seem to be working

[grim vault]

You're doing the inserts before you created the trigger.

[hollow pike]

@grim vault yea that was the issue. thanks

[vapid solstice]

I'm using postgresql, and I have a query: SELECT * FROM banks WHERE id = (SELECT bank_id FROM balances WHERE user_id = $1 LIMIT 1) LIMIT 1
So as you can see in the where clause there is a check to another select clause. Will the bank_id be selected again with every row (which would obviously be inefficient and slow) or will the bank_id be processed once in the beginning (by selecting the value) and then have that value used for each row (without re-selecting) ?

[proven arrow]

In the example you show, the subquery is not correlated (in other words the inner query is not dependent on the outer). So the query planner will be smart enough to know this and will cache the result.

[vapid solstice]

Alright thx so much

[vapid mist]

await cursor.execute(f'UPDATE invite_cache SET invite_uses = {new_invites} WHERE invite_code = {cached_invite}')

sqlite3.OperationalError: no such column: MYGFVjHUq9

[grim vault]

Don't use f-strings for values, do it with binding parameters:

await cursor.execute('UPDATE invite_cache SET invite_uses = ? WHERE invite_code = ?', (new_invites, cached_invite))```

[vapid mist]

ah thats how you do it, with asyncpg i did $1 $2, didnt know how to in sqlite

[nova cove]

?

Is the placeholder

in SQLite

[vapid mist]

👍

works now

ty

[gusty mulch]

If I'm using serial datatype on my primary key of one of db tables, how would I call that in my code? Normally, as i'm writing a discord bot, I would make the guild ID the primary key and pull the data for that table that way but since i'm using serial for this particular table I'm not quite sure how i call it in my code

[fading patrol]

I don't follow why serial is a problem here. Are you getting an error?

[gusty mulch]

it's not, i'm just not sure how to call it, normally to call my main table i would do data = await self.bot.get_data(ctx.guild.id) but i'm not sure what I should input, if anything, instead of ctx.guild.id for my table that use's serial

this is my sql for the tablesql -- Table: public.suggestions -- DROP TABLE IF EXISTS public.suggestions; CREATE TABLE IF NOT EXISTS public.suggestions( suggestion integer NOT NULL DEFAULT nextval('suggestions_suggestion_seq'::regclass), guild bigint, original bigint, post bigint, member bigint, message text COLLATE pg_catalog."default", CONSTRAINT suggestions_pkey PRIMARY KEY (suggestion) )and this my function of how the bot access's the tablepy async def get_sug(self, suggestion): async with self.pool.acquire() as conn: res = await conn.fetchrow("SELECT * FROM suggestions WHERE suggestion=$1",) if not res: res = await conn.fetchrow("INSERT INTO suggestions VALUES ($1, $2, $3, $4, $5, $6) RETURNING *", suggestion, None, None, None, None, None) return resand this is how i'm writing info to the tablepy async with self.bot.pool.acquire() as conn: await conn.execute("UPDATE suggestions SET ctx.guild = $1, ctx.message.id = $2, post.id=$3, ctx.author.id = $4, message = $5 where suggestion = $3", ctx.guild.id, ctx.message.id, post.id, ctx.author.id, message, serial)and this is the full command: https://sourceb.in/Rt6VRZJZBA

[fading patrol]

I would think it should work the same, does it not? An ID is still and ID regardless of data type

[gusty mulch]

yeah, but the guild id is not the primary key of my suggestions table, the serial is and in my data table db call i'm using the guild ID to as the primary key to find the data for that guild but i'm not sure how to do that for a serial

altho I guess i could store the latest serial in my data table and fetch it that way but with that way I can only get the latest suggestion

[torn sphinx]

im trying to use pymongo but it says ModuleNotFoundError: No module named 'pymongo' even when i already installed it with pip install pymongo

[fading patrol]

Did you install it for the same Python version / virtual environment you are trying to import it in?

[torn sphinx]

how do i know what virtual environment i installed it in

[winged sand]

guys can you help me with sql

SELECT * FROM linjat WHERE numero LIKE ("1[0-9][0-9]%");

i want to select all with first number 1 and 2 0-9 and third 0-9 and fourth being whatever character

but its not working

so that it would select this for example : 123A
but not select this 12A

[storm mauve]

you can use . or \w for "anything" or "any word character", respectively - that is, LIKE ("1[0-9][0-9].%");

shouldn't you use single quotes though? (not sure)

[winged sand]

i dont know about quotes they should work with single or double

thanks bro

[torn sphinx]

Anyone able to help me with a simple dataframe issue or should I post in one of the help channels?

[uncut canyon]

can anyone help me out withgoogle spreadsheets?

[nimble lava]

hey i recently reset my pc so my mysql tables and stuff got deleted but i have the ibd files saved
how can i recover the data ?

[nimble lava]

nvm fixed

[true quail]

lol

[fading patrol]

If you're using one (and you should) 1) make sure it's active and then 2) use pip to confirm what version of the package is installed. If you're not using one, just do #2. I can't remember the exact command to check a package version with pip but you can try installing it again and if it's already installed, pip will tell you that. Or you can run pip freeze and find it on the list

[grim vault]

The LIKE operator only has _ (single char) and % (zero or more chars) as wildcards. No other pattern matching can be done. Most databases have extensions for a more sophisticated patterns matching, eg SQLite has GLOB and REGEXP operator. You'll have to take a look at your database documentaion.

[ebon skiff]

?

Woah

That's new

[brave bridge]

well, maybe you shouldn't be using a third-party client 🙂

[pure raft]

Even with the vanilla client it does the same 🤷‍♂️

As well as the website/mobile version

[torn sphinx]

result_set = sqlalchemy.select([table]).where(table.columns.column1 == '1234')
Is there something wrong with this syntax? It does not give me anyhting.

got a table named table, column named column with value 1234

It should work right? code doesnt crash, but does not give me any output...

[paper flower]

Did you execute it?

https://docs.sqlalchemy.org/en/14/tutorial/data_select.html?highlight=selecting

[torn sphinx]

WHERE table.column1 = :column1_1```

this is what it says in terminal

[paper flower]

You have to execute it first

select() just constructs a statement

[torn sphinx]

thats the tutorial im following

oh ofc

im stupid

[paper flower]

You have to execute it with engine.connect()

>>> with engine.connect() as conn:
...     for row in conn.execute(stmt):
...         print(row)

[torn sphinx]

it worked

xD

Thanks

Sorry for my noobyness

[paper flower]

np

[tame quest]

I use sqlite3,

[shut zephyr]

This is my schema. Please check the rightmost parts relating to user and videogame.

I have inserted some dummy records in those three tables. Now I want to create a MYSQL function that takes in 2 arguments (which are usernames) and returns the videogames which have been played by both the users. How to do so? I have the entire SQL script ready to share for your reference if anyone can help.

I already have made a query to get the games which are played by ALL the users. But don't know enough to write the function described above. Please DM or ping me here if you can help. I'll share the SQL code asap

https://pastebin.com/j4F23dKx

MySQL commands related to my issue are here with a working demo. Please help out.

[grim vault]

Something like:

SELECT *
  FROM VideoGame
 WHERE videogameID IN (
     SELECT videogameID
      FROM UserVideogames
     WHERE userID = 1
       AND videogameID IN (
           SELECT videogameID
             FROM UserVideogames
            WHERE userID = 2
           )
    )

which will select all videogames userID 1 and 2 has in common.

[shut zephyr]

Thank you very much. Been struggling with this a lot. I'll test it tomorrow and let you know if I face any difficulties.

[mild merlin]

i have thic command, and get this error ```python
def check(m):
return m.channel == channel
msg = await j1mk0l.wait_for("message", check=check)
test = msg.content
#try:
mycursor.execute("DELETE FROM license WHERE name=test;")
mydb.commit()

Command raised an exception: ProgrammingError: 1054 (42S22): Unknown column 'test' in 'where clause'

[fading patrol]

Try putting test in single quotes

Like 'test'

[gusty mulch]

Hey so i'm not sure I read data from a table that uses the serial datatype for it's primary key. For my other tables I am using guild ID's so when i call my db function I can just pass the context guild ID into the function for it to give me all of the data from the row related to that guild ID however I am not sure what I need to pass in for the serial datatype since it is postgres itself generating the serial primary key.

[fading patrol]

If I'm understanding correctly, it sounds like you need to add the guild ID as a foreign key to whatever table has the serial primary

[dapper eagle]

heyy all im trying to run sql in pycharm but its not working 😭

it comes up with errors

when i run it

[fading patrol]

What exactly are you doing and what are the errors

[gusty mulch]

well I do have a guild ID column in this table but I'm not sure it needs to be a foreign key. This is how the SQL of my table is set up and as you can see the suggestion is a integer that increments itselfsql -- Table: public.suggestions -- DROP TABLE IF EXISTS public.suggestions; CREATE TABLE IF NOT EXISTS public.suggestions( suggestion integer NOT NULL DEFAULT nextval('suggestions_suggestion_seq'::regclass), guild bigint, original bigint, post bigint, member bigint, message text COLLATE pg_catalog."default", CONSTRAINT suggestions_pkey PRIMARY KEY (suggestion) )how would I call that in my function is really what I'm trying to work out. Since for my data table it's as easy as doingpy data = await self.bot.get_data(ctx.guild.id) # Passing the context guilds ID in so that the bot knows where to look in the db.but doing py suggestions = await self.bot.get_sug(Serial) # I'm not sure what the serial needs to be hereI guess I could restructure the entire table to make the original msg ID the primary key but I'm sure that will cause issus for me later down the line. plus ppl have told me before that making it a incrementing number is the best way to do things

[fading patrol]

Yes, generally you want an incrementing primary key.

Beyond that I don't know what bot.get_data or ctx are so I can't help you without more code

[dapper eagle]

I’m just trying to connect my sql in pycharm and the errors are

[gusty mulch]

okay this is get_data and get_sug ```py
async def get_data(self, guild):
async with self.pool.acquire() as conn:
res = await conn.fetchrow("SELECT * FROM data WHERE guild=$1", guild)
if not res:
res = await conn.fetchrow(
"INSERT INTO data VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11) RETURNING *",
guild, None, None, [], None, None, None, False, [], [], False,)
return res

async def get_sug(self, suggestion):
async with self.pool.acquire() as conn:
res = await conn.fetchrow("SELECT * FROM suggestions WHERE suggestion=$1",)
if not res:
res = await conn.fetchrow(
"INSERT INTO suggestions VALUES ($1, $2, $3, $4, $5, $6) RETURNING *",
suggestion, None, None, None, None, None)
return res```as for ctx; it represents the context in which a command is being invoked under (i.e. the guild, channel, person who used the command etc). Does that answer all of your questions or do i need to provide more info.

I'm not using the ctx.guild.id as a primary key for this table as a guild will likely have more than one suggestion, I could use the ID of the command that was used to make the suggestion but I want the bot to delete the suggestion once it fully completes the command therefore the message associated with the ID will no longer exist, I could use the ID of the new msg that gets sent for the successful post but if that fails to generate for whatever reason then the db call will fail

[fading patrol]

It's telling you that the username and/or password is incorrect

[dapper eagle]

i swear it is correct cause ik im damn sure

[fading patrol]

To me it still looks like the issue is that you don't know what your primary key is, you don't have a foreign key or other condition you can use. I don't know what your data looks like or how you are acquiring it but you need to have some way to find the data you are looking for

When you are incrementing your primary key, a foreign key is important for this reason

[mild merlin]

i have thos command but i get this error ```python
sql = "INSERT INTO customers (name, code) VALUES (%s,%s)"
val = (user,test)
mycursor.execute(sql, val)
mydb.commit()

in redeem
mycursor.execute(sql, val)
Command raised an exception: MySQLInterfaceError: Python type Member cannot be converted

[pallid shard]

Hi , im using sqlite3 and i used the type DATE it return me unix but i dont know how to convert date to this unix
int((datetime.now() + relativedelta(hours=6)).timestamp()) this not works im not sure here or #data-science-and-ml so im typing on both of this channels

[gusty mulch]

Okay i'm getting this errorpy PostgresSyntaxError: syntax error at or near ","which produces this tracebackpy ERROR:cogs.error_handler:PostgresSyntaxError: syntax error at or near "," (In suggest) Traceback: File "/usr/local/lib/python3.8/dist-packages/discord/ext/commands/core.py", line 85, in wrapped ret = await coro(*args, **kwargs) File "/home/modmail/cogs/general.py", line 329, in suggest await conn.execute("UPDATE suggestions SET member = $1, original = $2, message = $3 where post = $4, guild = $5", ctx.author.id, ctx.message.id, suggestion_mesage, post.id, ctx.guild.id) File "/usr/local/lib/python3.8/dist-packages/asyncpg/connection.py", line 320, in execute _, status, _ = await self._execute( File "/usr/local/lib/python3.8/dist-packages/asyncpg/connection.py", line 1659, in _execute result, _ = await self.__execute( File "/usr/local/lib/python3.8/dist-packages/asyncpg/connection.py", line 1684, in __execute return await self._do_execute( File "/usr/local/lib/python3.8/dist-packages/asyncpg/connection.py", line 1719, in _do_execute stmt = await self._get_statement( File "/usr/local/lib/python3.8/dist-packages/asyncpg/connection.py", line 398, in _get_statement statement = await self._protocol.prepare( File "asyncpg/protocol/protocol.pyx", line 168, in preparewhich seems to be caused by thispy await conn.execute("UPDATE suggestions SET member = $1, original = $2, message = $3 where post = $4, guild = $5", ctx.author.id, ctx.message.id, suggestion_mesage, post.id, ctx.guild.id)which is probably because I'm using a serial in one of my columns of the db but i'm not mentioning that in the insert bc i'm not sure how to

[zealous rain]

Hey, any idea what's wrong? py File "/home/kamil/Dokumenty/bert_trainer/migrations/env.py", line 25, in <module> str(current_app.extensions['migrate'].db.get_engine().url).replace( TypeError: get_engine() missing 1 required positional argument: 'app'

from config import Config
from flask_sqlalchemy import SQLAlchemy
from flask_restx import Api, fields, Resource
from flask_marshmallow import Marshmallow
from flask_migrate import Migrate
from flask_swagger_ui import get_swaggerui_blueprint

app = Flask(__name__)

app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///app.db'
# Init config
app.config.from_object(Config())
# Init db
db = SQLAlchemy(app)
migrate = Migrate(app, db)

flask db init

flask db migrate -m "Initial migration."

[grim vault]

Your where clause needs an AND not an ,: ... where post = $4 and guild = $5
Also, a serial is just an auto-incrementing integer. https://www.db-fiddle.com/f/oKo3agZxYqupsVapjjDmn8/0, https://www.db-fiddle.com/f/oKo3agZxYqupsVapjjDmn8/1

[gusty mulch]

okay so I fixed that , to a and, thanks for the correction, i'm still confused about the auto incrementing integer and how i'd represent it in this section ctx.author.id, ctx.message.id, suggestion_mesage, post.id, ctx.guild.id, would I just put a random integer value like 0 and it would overide it?

[grim vault]

You don't give it to an insert, as shown by the example provided. It's done by the database itself. After the insert you can just use it like any other id.

[gusty mulch]

would the serial need to be the final column, I have it in the middle of my tables columns but i keep getting sql DataError: invalid input for query argument $3: 'testing testing 1 2 3' (an integer is required (got type str))and the insert is currentlypy await conn.execute("UPDATE suggestions SET member = $1, original = $2, message = $3 where post = $4 and guild = $5", ctx.author.id, ctx.message.id, suggestion_mesage, post.id, ctx.guild.id)

[grim vault]

The order doesn't matter. And that is an update not an insert. Looks like the message is a integer column? Check your suggestions table.

[ruby topaz]

anyone use AWS or ETL tools for data. Was hoping to have a resource for best practices in my new job.

[fading patrol]

r/dataengineering on Reddit and their respective Discord server are gold