databases

[high geyser]

like I have a list called test_list which has to be updated with external functions

how will I check if keyword not in that list inside query?

I tried and found out that list cannot be parameterized

[tepid cradle]

Try passing a list for that, it should work. Just do NOT IN ? and pass a list as the value

[high geyser]

I tried that

you cannot paramterize a list

thats why it didnt work I checked stack overflow pages too

[tepid cradle]

Oh, sqlite, ok. I was thinking of Postgres

[high geyser]

ohh

[tepid cradle]

Come to a help channel, I'll show you how to do it. Can't use eval command here

[high geyser]

okay

[tepid cradle]

ping me from a help channel

[high geyser]

c.execute("""SELECT keyword,
                            tag_use_count,
                            COUNT(keyword),
                            SUM(tag_use_count)
                    FROM tags
                    WHERE guildid = ?
                    group by keyword
                    ORDER BY tag_use_count DESC
                    LIMIT 3""", (ctx.guild.id,))```But if I do this I dont get the sum of tagusecount for all the keywords selected
only the one for the first keyword
same for count(keyword)
count(keyword) remains 1

pls help

@tepid cradle

like keyword has a column called tag_use_count I dont get the sum of taguse count or the count of keyword correctly. Whys that?

[torn sphinx]

Is there any provider to keep json files on the server and fetch them using api

[dusky plaza]

does sqlalchemy support UUID with mysql(mariaDB)?

[ember marsh]

not natively, because mysql doesn't have uuid types.

@dusky plaza I think you'd either have to set up a trigger in mysql or use this: https://docs.sqlalchemy.org/en/13/core/custom_types.html#backend-agnostic-guid-type

it says GUID but you can modify it to use any uuid version, I'm sure.

[grizzled dagger]

can anyone tell me why SQLAlchemy do not detecting changes like shown below to Base inherited Player class I made?

instead I have to use s.query thing which I don't want

[snow garden]

Anyone have experience configuring postgres on ubuntu? I created a user in terminal and gave it a superuser role, but when i try to run psql with the user i get errors saying that i can't access database of name user.

[high geyser]

How to create a user with password for psql to access the db?

like a role?

[mossy light]

I have a questions that's more about database design, is that alright to ask here?

[ember marsh]

Ask away.

[velvet coyote]

Is there a database that can support dictonaries?

if u can answer my question then pls ping me!

[ember marsh]

@velvet coyote wdym? you're wanting to shove a python dict into a column?

you could serialize to json and use a mysql or postgre json data type

what's your use case?

[velvet coyote]

i got it

somone suggested me to use mongo db

that's what I am gonna be using

[velvet coyote]

Would somone care to help me in #help-dumpling

[boreal crag]

hi everyone

are there anyone use mongodb?

hi?

[torn sphinx]

nope

never used it

[boreal crag]

hmmm

i make a bot use #discord-bots

[torn sphinx]

@boreal crag Dont use mongodb for bot

[boreal crag]

i want to make a user's rank for my bot

@boreal crag Dont use mongodb for bot
@torn sphinx why

[torn sphinx]

because bot is use async code. So you need also async database driver to communicate with the database. But the problem with mongodb async driver is that it is not so good.

[boreal crag]

import discord,sys,platform
from discord.ext import commands
import pymongo

# 设置bot命令
bot = commands.Bot(command_prefix='$')
# 链接数据库
client = pymongo.MongoClient('127.0.0.1', 27017)
# 创建数据库
db = client['zzysite_bot']

# 创建用户集合
db.create_collection('bot_user_and_channels')

# 注册用户
async def reg_user(ctx):

    pass

# 登录用户
async def login_user(ctx):
    
    pass

i want to register discord user to my database to make user's rank

what's next step

[torn sphinx]

does sqlite not have big integer?

my item 9223575736854775807 is stored as 9.22357573685478e+18 ughhh

[uneven lava]

using sqlalchemy, is there a way to get all element of one column ?

Like just get all IDS in a list ?

[boreal crag]

hmmm

how can i use mongodb for pymongo

[fleet prism]

@uneven lava you probably need a comprehension 🙂

[row[0] for row in ssn.query(...)]

I don't think SqlAlchemy has a tool for columns

[uneven lava]

yes thx, I found that :

[g_id[0] for g_id in db.session.query(db.Guilds.guild_id).distinct()]

[plush vector]

Hi everyone, I'm creating an API with FastAPI which posts stuff on different services using user's authentication tokens for those services. I have to store those auth tokens in DB to reuse them, how can I safely store them in the DB?

[high geyser]

the server quits connection with error before even asking me for the password. Why is that?

pls help me

pls ping me when help

How can I get into the psql tool if something like this happens?

[brazen charm]

why not just use pgadmin...

[high geyser]

I have to use terminal because only then I will know how to use it

[brazen charm]

what

[boreal crag]

are there anyone know mongodb

I want to a help

[proven arrow]

@boreal crag You should just ask your question, and if someone is able to help they will answer you, rather than waiting to see if anyone knows. You'll be more likely to get a response this way.

[boreal crag]

@boreal crag You should just ask your question, and if someone is able to help they will answer you, rather than waiting to see if anyone knows. You'll be more likely to get a response this way.
@proven arrow ok

I want to use the pymongo module to count the number of documents, how should I do it

[burnt turret]

collection.count_documents() should do i think

[weak charm]

Hello guys, can you suggest me a schema in mongodb for a chat app?

[gentle plaza]

How do i add line ends to a JSON file so that it doesn't look like one big line of JSON code?

[quaint tiger]

How do i add line ends to a JSON file so that it doesn't look like one big line of JSON code?
@gentle plaza ```py

d = {"foo": "FOO", "bar": 42}
import json
json.dumps(d)
'{"foo": "FOO", "bar": 42}'
json.dumps(d, indent=2)
'{\n "foo": "FOO",\n "bar": 42\n}'
print(json.dumps(d, indent=2)) # add indent to format nicely
{
"foo": "FOO",
"bar": 42
}

[gentle plaza]

Oh, indents. Thanks!

[mossy light]

I'm looking for advice on database design and performance. So I have a discord bot that caches the messages in all the servers it's in so that they can be queried quicker by user commands (wordcloud, statistics, etc). I have a Servers, Channels, and Messages table. Recently I implemented a feature where the bot, upon losing permissions to a channel, wouldn't immediately delete the messages in its database but instead marks it for deletion 24 hours later (in case of accidents, so bot doesn't have to fetch messages again). I do this by setting a column in the Channels table from NULL to the timestamp when it should be deleted.

I would like the queries to the Messages table in my user commands to ignore any messages from a channel marked for deletion. I could do this simply by writing my queries to fetch me every message that fits my criteria and its corresponding channel entry is not marked for deletion. My concern with this is that this includes a join of the channels and messages table. I'm trying to write this with the potential to scale for a decent sized Channels table and a very large Messages table. I honestly don't know if the performance impact would be significant so that's part of why I'm asking for advice.

The other two options I considered are:

1. Adding a column to the Messages table indicating whether it's been marked for deletion. This would be redundant, since the Channels table already has that info, but would avoid the join.
2. Upon a channel being marked for deletion, create a new table to store that data and remove it from the main Messages table. If the channel gets reinstated, the data is moved back to the main table, otherwise it and the table is deleted when time runs out.

Oh and I'm using postgres, any advice is appreciated.

[ember marsh]

@mossy light have you looked into postgres views?

I'd have a marked for deletion column and a last updated timestamp. You view could just return rows that aren't marked for deletion.

[potent sparrow]

What stock api is recommended to use with pandas?

[versed sluice]

Hi, I have an issue with sqlalchemy.
I did so

1. Changed the model by changing column name from image to logo.
2. dropped all tables
3. created all again
   But when I do so, the column still comes out as "image". What am I missing?

Edit: After about the 5th time trying it worked. I somehow had 2 DB files in the repo (in different folders though). Maybe that influenced

[torn sphinx]

?

omg...

ok ya im dumb

[mossy light]

@ember marsh I did, but I only considered using materialized views as a sort of temp table for marked messages/channels. I didn't consider putting the non-marked data in a view but that does sound like a good idea. Do you suggest I use regular or materialized views? Regular views sound like they just run the more complex query in the background whenever you query it so there won't be any performance benefit, unless it does some sort of caching/wizardry I'm unfamiliar with.

Also, could you clarify for me where the two columns you suggested would go? Channels, Messages, or into the view? What would be the purpose of the last updated timestamp?

[ember marsh]

Regular views does just run a query in the background. If you've set up a partial index of non-deleted messages, I think that would be pretty fast.

I'd put them in messages.

Actually the last updated part would be excessive. Your idea works with an index of not null deletion timestamp values.

[mossy light]

Oh ok gotcha. Your suggestion is basically that I go with my option 1 (adding a redundant column to Messages), add a partial index to the Messages table for non-deleted messages, and add a non-materialized view to simplify the queries, correct?

For reference, I do currently have an index of messages by the ID of the message sender

CREATE INDEX idx_MESSAGES_AuthorID ON MESSAGES 
USING btree
(
    AuthorID
)

I would just need to slap on a WHERE statement to exclude messages marked for deletion

I'm just concerned because I would be denormalizing the database without really having the need for it yet (though I do anticipate it in the future).

[high geyser]

the server quits connection with error before even asking me for the password. Why is that?
@high geyser

https://docs.cpanel.net/knowledge-base/sql/how-to-resolve-password-authentication-failed-errors-in-postgresql/

This helped I guess

[torn sphinx]

@client.command()
async def whitelist(ctx, serverid:int):
    conn = await aiosqlite.connect('servers.db')
    await conn.execute("INSERT INTO servers VALUES (?)", (serverid))
    await ctx.send("Added")
    await conn.commit()
    await conn.close()

This would work for adding a server id

sry i am not that advanced 😦

[craggy girder]

wait, dont you insert into a table?

[torn sphinx]

wdym?

[craggy girder]

not u

[torn sphinx]

oh ok

the table is called servers

[craggy girder]

but your db is called servers too

[torn sphinx]

ok?

[craggy girder]

oof

[torn sphinx]

its not that big of a issuse

[craggy girder]

ok, but i got confused

[torn sphinx]

async def create_db(client):
    await client.wait_until_ready()

    conn = await aiosqlite.connect('money.db')

    await conn.cursor()

    await conn.execute("""CREATE TABLE sev (
           serverid integer PRIMARY KEY
           )""")

    await conn.commit()

    await conn.close()


client.loop.create_task(create_db(client))

@client.command()
async def whitelist(ctx, serverid):
    conn = await aiosqlite.connect('servers.db')
    await conn.execute("INSERT INTO sev VALUES (?)", (serverid))
    await ctx.send("Added")
    await conn.commit()
    await conn.close()

[craggy girder]

okok

so are you getting an error?

[torn sphinx]

have not tried it yet

its been 3 mouths since i have worked with aio sqlite

[craggy girder]

ooh

this should work,

[torn sphinx]

how could i make it have a error if its already in the db
i did this a while ago but i dont understand it anymore

[craggy girder]

i only know sqlite, is aio sqlite any different?

[torn sphinx]

aio is awaited

[craggy girder]

ok, so then

[torn sphinx]

so theres no blocking with discord

[craggy girder]

yea

so to check if its alr there you will have to use select right?

select from

[torn sphinx]

yes but... its been a while

this is from my old ecom bot

[craggy girder]

ok so i will give you an example

so i have a db username_login

[torn sphinx]

    conn = await aiosqlite.connect('money.db')
    id = ctx.author.id
    print(id)
    ids = await conn.execute("SELECT id FROM money")
    values = await ids.fetchall()
    print(values)
    cursor = await conn.execute("SELECT cash FROM money WHERE id=?", (id,))
    records = await cursor.fetchone()
    print("Id: ",  id)
    if records:
        await ctx.send("Oh, No i found you in the database use `>work` to start working!")
        return
    else:
        await conn.execute("INSERT INTO money VALUES (?, ?)", (id, 1))
        print(f"I have added {id} To my DB")
        await ctx.send("I have added you to the DataBases!")
        await conn.commit()
        await conn.close()

thats from my old command

[craggy girder]

oohok

[torn sphinx]

but this is not the bot im doing

[craggy girder]

take a look at this

[torn sphinx]

0_0

[craggy girder]

this checks if the username is already in use

you need a command like this right?

[torn sphinx]

yes only with a server id if its already added

[craggy girder]

yes then you will have to use that

conn = sqlite3.connect('username_login.db')
c = conn.cursor()
c.execute("""SELECT user_name from usernames where user_name is :user_name""",
    {
        'user_name': u_ent.get()

        })
name = c.fetchall()
if name != []:
    messagebox.showerror("Login", "Username already in use")```

[torn sphinx]

this looks nothing like aiosqlite

LoL

[craggy girder]

see this, here, it selects from the table if the username is there already

[torn sphinx]

yes

[craggy girder]

because its sqlite

you just need to change a little things

you have to convert

[torn sphinx]

hhm

[craggy girder]

see you have done the same thing here, the syntax is just a little different

[torn sphinx]

is there a online convertor

LOL

[craggy girder]

lol

no, try comparing yours and mine and you will get the answer

[torn sphinx]

@craggy girder

@client.command()
async def whitelist(ctx, serverid):
    conn = await aiosqlite.connect('money.db')
    print(serverid)
    sevids = await conn.execute("SELECT thesevid FROM sev")
    values = await sev.fetchall()
    print(values)
    cursor = await conn.execute("SELECT thesevid FROM sev WHERE id=?", (serverid,))
    records = await cursor.fetchone()
    print("Id: ",  thesevid)
    if records:
        await ctx.send("Server is already in the db sorry, m8 :x:")
        return
    else:
        await conn.execute("INSERT INTO sev VALUES (?)", (serverid))
        print(f"I have added {serverid} To my DB")
        await ctx.send("I have added you to the DataBases!")
        await conn.commit()
        await conn.close()

that would work ?

[craggy girder]

yes it should,

[torn sphinx]

k

[high geyser]

alfred_wigans=> CREATE TABLE "nsfw_word_lookup" (
alfred_wigans(> "id"INTEGER,
alfred_wigans(> "guildid"INTEGER,
alfred_wigans(> "word"TEXT,
alfred_wigans(> "inclusive"BOOL
alfred_wigans(> PRIMARY KEY (id),
alfred_wigans(> UNIQUE("guildid","word"));
ERROR:  syntax error at or near "("
LINE 6: PRIMARY KEY (id),```

whatsthe error here why cant I create primary key like this in postgresql

pls help

[tight hollow]

o7 . hope all is well.

with sqlalchemy, is there a concept of a unique row? as in, multiple columns within a given row are used to create the unique identifier

[torn sphinx]

Traceback (most recent call last):
  File "C:\Users\hedge\AppData\Local\Programs\Python\Python38\lib\site-packages\discord\ext\commands\bot.py", line 903, in invoke
    await ctx.command.invoke(ctx)
  File "C:\Users\hedge\AppData\Local\Programs\Python\Python38\lib\site-packages\discord\ext\commands\core.py", line 855, in invoke
    await injected(*ctx.args, **ctx.kwargs)
  File "C:\Users\hedge\AppData\Local\Programs\Python\Python38\lib\site-packages\discord\ext\commands\core.py", line 94, in wrapped
    raise CommandInvokeError(exc) from exc
discord.ext.commands.errors.CommandInvokeError: Command raised an exception: OperationalError: no such column: id

@craggy girder

@client.command()
async def whitelist(ctx, serverid):
    conn = await aiosqlite.connect('servers.db')
    print(serverid)
    sevids = await conn.execute("SELECT thesevid FROM sev")
    values = await sevids.fetchall()
    print(values)
    cursor = await conn.execute("SELECT thesevid FROM sev WHERE id=?", (serverid,))
    records = await cursor.fetchone()
    print("Id: ",  serverid)
    if records:
        await ctx.send("Server is already in the db sorry, m8 :x:")
        return
    else:
        await conn.execute("INSERT INTO sev VALUES (?)", (serverid))
        print(f"I have added {serverid} To my DB")
        await ctx.send("I have added you to the DataBases!")
        await conn.commit()
        await conn.close()

[craggy girder]

hmmm

[torn sphinx]

coming from line 6/5 in that code

[craggy girder]

ahh, you did a typo

[torn sphinx]

hm

[craggy girder]

is there a column for id in your db?

[torn sphinx]

    #await conn.execute("""CREATE TABLE sev (
           #thesevid integer PRIMARY KEY
           #)""")

dont mind the #

[craggy girder]

no there isnt

see, theres only thesevid

no column as id

[torn sphinx]

so i would put thesevid

as that

[craggy girder]

yes

[torn sphinx]

so it would set the server id to sevid

Ok

[craggy girder]

try putting thesevid

[torn sphinx]

ok

ok now this is happening

Traceback (most recent call last):
  File "C:\Users\hedge\AppData\Local\Programs\Python\Python38\lib\site-packages\discord\ext\commands\bot.py", line 903, in invoke
    await ctx.command.invoke(ctx)
  File "C:\Users\hedge\AppData\Local\Programs\Python\Python38\lib\site-packages\discord\ext\commands\core.py", line 855, in invoke
    await injected(*ctx.args, **ctx.kwargs)
  File "C:\Users\hedge\AppData\Local\Programs\Python\Python38\lib\site-packages\discord\ext\commands\core.py", line 94, in wrapped
    raise CommandInvokeError(exc) from exc
discord.ext.commands.errors.CommandInvokeError: Command raised an exception: ProgrammingError: Incorrect number 
of bindings supplied. The current statement uses 1, and there are 18 supplied.

@craggy girder

[craggy girder]

hmm

[torn sphinx]

hm?

any fix

[torn sphinx]

@craggy girder

Fixed it

now can u help me with removing it?

[craggy girder]

wdym?

[torn sphinx]

like !unwhitelist 434343434343

[craggy girder]

oohh

like you wanna delete it from the db?

[torn sphinx]

ye

[craggy girder]

yep then theres a command for it too,

[torn sphinx]

how

i know how to replace not delete '

[craggy girder]

looks like this

[torn sphinx]

uhuh

0_0

[craggy girder]

also if you wanna check if the account you are trying to delete is not added you can use

[torn sphinx]

it would be a column i need to remove correct?

@client.command()
async def unwhitelist(ctx, serverid):
    conn = await aiosqlite.connect('servers.db')
    print(serverid)
    await conn.execute("DELETE FROM sev WHERE thesevid=?", (serverid,))
    await ctx.send("Done!")
    await conn.commit()
    await conn.close()

@craggy girder

[craggy girder]

yes

[torn sphinx]

REALLY

[craggy girder]

yes it should work

[torn sphinx]

YEA

laste question

how can i make my bot leave a server if there server id is not in the db

@craggy girder

[craggy girder]

whhat

better ask that in d.py, not sure how to do that,

[torn sphinx]

well i know how to make the bot leave

but how would i check if tthe id is not in the db

      await ctx.guild.leave()```

[high geyser]

Whats the best practice to make a connection pool for a discord bot?

Create a connection pool.

Can be used either with an async with block:

async with asyncpg.create_pool(user='postgres',
                               command_timeout=60) as pool:
    await pool.fetch('SELECT 1')```
Or to perform multiple operations on a single connection:

```py
async with asyncpg.create_pool(user='postgres',
                               command_timeout=60) as pool:
    async with pool.acquire() as con:
        await con.execute('''
           CREATE TABLE names (
              id serial PRIMARY KEY,
              name VARCHAR (255) NOT NULL)
        ''')
        await con.fetch('SELECT 1')```
Or directly with await (not recommended):

```py
pool = await asyncpg.create_pool(user='postgres', command_timeout=60)
con = await pool.acquire()
try:
    await con.fetch('SELECT 1')
finally:
    await pool.release(con)```

I got this from the docs. Why isnt the third option recommended?
Please help

[high geyser]

await self.client.conn.execute("""SELECT keyword
                    FROM tags
                    WHERE guildid = $1
                    UNION
                    SELECT alias_name
                    FROM alias_storage
                    WHERE guildid = $1;""", (guildid))
        keywords_found = c.fetchall()``` error
```py
    raise CommandInvokeError(exc) from exc
discord.ext.commands.errors.CommandInvokeError: Command raised an exception: DataError: invalid input for query argument $1: 744151626230530078 (value out of int32 range)``` how to fix this error? pls help. guildid datatype is INTEGER

[proven arrow]

You can use BIGINT as the column type, since Integer allows value upto 2147483647

[torn sphinx]

how would i correct di this

@tasks.loop(seconds=5)
async def checkehitelist():
    conn = await aiosqlite.connect('servers.db')
    guilds = client.guilds
    for guild in guilds:
        if guild.id not in db:
            print("Not whitelisted")
            owner = guild.owner
            await owner.send("Your not on the whitelist! :x:")
            await guild.leave()
        else:
            pass

Help please

trying to remove bot from any server thats not in db

[proven arrow]

You haven't defined db anywhere

Also your not querying the database to get a collection of the Guild IDs

[torn sphinx]

async def create_db(client):
    await client.wait_until_ready()

    conn = await aiosqlite.connect('servers.db')

    await conn.cursor()

    #await conn.execute("""CREATE TABLE sev (
           #thesevid integer PRIMARY KEY
           #)""")

    await conn.commit()

    await conn.close()


client.loop.create_task(create_db(client))

thats my db how would i say if server id is not = to server id in db then leave else stay

[high geyser]

You can use BIGINT as the column type, since Integer allows value upto 2147483647
@proven arrow I did

same errror

Now

[proven arrow]

Did you change for both the tables?

[high geyser]

oh sorry

am doing it now

thanks

[torn sphinx]

@proven arrow

https://discordapp.com/channels/267624335836053506/342318764227821568/762553687003627540

[proven arrow]

SELECT thesevid FROM sev would be your query

That would give you the IDs from that table. Depending on what type aiosqlite returns you can check if your guild ID is in that collection.

[torn sphinx]

<aiosqlite.cursor.Cursor object at 0x00000292479EB4C0>

thats what it printed

[proven arrow]

Because you would need to use fetchall() to get the results on that cursor object

[torn sphinx]

[(761774217632940063,)]

printed

witch is the only one in the db

so

yea

now what

[proven arrow]

Well what do you think is next?

[torn sphinx]

loop over client guilds and check if that guilds id is = to the ones in the db

not that sure on how to do that tho

[proven arrow]

You already had the code for that. Read what I sent previously. You just need to replace db from your code if statement to the tuple that result from the database is providing you with.

[torn sphinx]

@tasks.loop(seconds=5)
async def checkehitelist():
    conn = await aiosqlite.connect('servers.db')
    guilds = client.guilds
    cursor = await conn.execute("SELECT thesevid FROM sev")
    records = await cursor.fetchall()
    print(records)
    for guild in guilds:
        if guild.id not in records:
            print("Not whitelisted")
            owner = guild.owner
            await owner.send("Your not on the whitelist! :x:")
            await guild.leave()
        else:
            pass

[proven arrow]

You would want if guild.id in records[0]

[torn sphinx]

ooooooooh yea

[proven arrow]

Since they are stored in the first element of that list

[torn sphinx]

soooo py @tasks.loop(seconds=5) async def checkehitelist(): conn = await aiosqlite.connect('servers.db') guilds = client.guilds cursor = await conn.execute("SELECT thesevid FROM sev") records = await cursor.fetchall() print(records) for guild in guilds: if guild.id in records[0]: print("Not whitelisted") owner = guild.owner await owner.send("Your not on the whitelist! :x:") await guild.leave() else: pass

[proven arrow]

Try it and see

[torn sphinx]

i dont got the time but ok

[high geyser]

hi

How would I store a datetime object into postgresql

like would it be datetime.datetime.now()

can I just do something like hat

that

[shell ocean]

@high geyser yes, there's a datetime type

[high geyser]

Column     |            Type             | Collation | Nullable | Default
---------------+-----------------------------+-----------+----------+---------
 keyword       | text                        |           |          |
 key           | text                        |           |          |
 guildid       | bigint                      |           |          |
 authorid      | bigint                      |           |          |
 tag_use_count | integer                     |           |          |
 date_created  | timestamp without time zone |           |          |
 id            | integer                     |           | not null |```

this is how my table currently is

@shell ocean should I use timestamp or datetime

whats better

[shell ocean]

timestamp is the type I meant

[high geyser]

okay

[shell ocean]

"datetime" was an imprecise term

[high geyser]

what would I insert

datetime.datetime.now

??

[shell ocean]

what?

I didn't understand that

[high geyser]

If i want to insert the current time

what would be the object I would insert

datetime.datetime.now()```???

[proven arrow]

Yes thatd work

Or you could also have the database column have a default value so it inserts the timestamp for you to current time

[torn sphinx]

@proven arrow

@tasks.loop(seconds=5)
async def checkehitelist():
    conn = await aiosqlite.connect('servers.db')
    guilds = client.guilds
    cursor = await conn.execute("SELECT thesevid FROM sev")
    records = await cursor.fetchall()
    print(records)
    for guild in guilds:
        if guild.id in records[0]:
            print("Not whitelisted")
            owner = guild.owner
            await owner.send("Your not on the whitelist! :x:")
            await guild.leave()
        else:
            pass

dident work

the server is on the whitelist yet it leaves

[proven arrow]

So you want it to not leave the guild ID stored in the database?

[torn sphinx]

crap

[proven arrow]

Because your doing if guild.id in records[0]

[torn sphinx]

its the other way around

[proven arrow]

Yeah

[torn sphinx]

@tasks.loop(seconds=5)
async def checkehitelist():
    conn = await aiosqlite.connect('servers.db')
    guilds = client.guilds
    cursor = await conn.execute("SELECT thesevid FROM sev")
    records = await cursor.fetchall()
    print(records)
    for guild in guilds:
        if guild.id in records[0]:
            pass
        else:
            print("Not whitelisted")
            owner = guild.owner
            await owner.send("Your not on the whitelist! :x:")
            await guild.leave()

so that will make it leave if there not in the id list aka db

[proven arrow]

Or you could've just done not in

[torn sphinx]

: |

[proven arrow]

Either works

[torn sphinx]

👍

[high geyser]

Or you could also have the database column have a default value so it inserts the timestamp for you to current time
@proven arrow how do I do that

dude ping me when help

pls

[proven arrow]

creatwed_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP a column like this I meant. @high geyser

[high geyser]

oh thanks

@proven arrow

await self.client.conn.execute("""BEGIN TRANSACTION;
                                INSERT INTO tags (keyword, key, guildid, authorid,date_created,tag_use_count)
                                VALUES ($1,$2,$3,$4,$5,$6,$7);
                                COMMIT;""",(key, final_key_msg, ctx.guild.id, ctx.message.author.id, now, 0))```

over here I am getting this error

    statement = await self._protocol.prepare(stmt_name, query, timeout)
  File "asyncpg\protocol\protocol.pyx", line 163, in prepare
asyncpg.exceptions.PostgresSyntaxError: cannot insert multiple commands into a prepared statement```

what can I do?

I have a connection pool as self.client.conn

in postgresql

pls ping me when help

my connection pool cannot handle multiple statements in one query

why not

wait I fixed it

await self.client.conn.execute("BEGIN TRANSACTION;")
                                await self.client.conn.execute("""INSERT INTO tags (keyword, key, guildid,
                                                                   authorid,date_created,tag_use_count)
                                                                    VALUES ($1,$2,$3,$4,$5,$6);""",(key, final_key_msg, ctx.guild.id, ctx.message.author.id, now, 0))
                                await self.client.conn.execute("COMMIT;")```error

discord.ext.commands.errors.CommandInvokeError: Command raised an exception: InterfaceError: the server expects 6 arguments for this query, 1 was passed
HINT:  Check the query against the passed list of arguments.```

Why did this error occur? pls help me fix it

I fixed that

[proven arrow]

You can remove the brackets around your params

[high geyser]

now = datetime.datetime.now(datetime.timezone.utc)
                                print(now)
                                await self.client.conn.execute("BEGIN TRANSACTION;")
                                await self.client.conn.execute("""INSERT INTO tags (keyword, key, guildid, authorid,date_created,tag_use_count)
                                                                    VALUES ($1,$2,$3,$4,$5,$6);""",key, final_key_msg, ctx.guild.id, ctx.message.author.id, now, 0)
                                await self.client.conn.execute("COMMIT;")``` error:```py
raise CommandInvokeError(exc) from exc
discord.ext.commands.errors.CommandInvokeError: Command raised an exception: DataError: invalid input for query argument $5: datetime.datetime(2020, 10, 5, 7, 5, 15,... (can't subtract offset-naive and offset-aware datetimes)```

You can remove the brackets around your params
@proven arrow yeah I did that thanks

bro but u told me that I can do datetime.datetime.now it errs @proven arrow

[proven arrow]

Your are mixing the timezone types. Database is not not aware of timezone and python is. So you are are mixing timezone-naive and timezone-aware types.

In your database change timestamp to use timestamptz

[high geyser]

okay

NOT NULL DEFAULT CURRENT_TIMESTAMP

would I do not null default timestampz

@proven arrow

alfred_wigans=> CREATE TABLE "tags" (
alfred_wigans(> "keyword"TEXT,
alfred_wigans(> "key"TEXT,
alfred_wigans(> "guildid"BIGINT,
alfred_wigans(> "authorid"BIGINT,
alfred_wigans(> "tag_use_count"INTEGER,
alfred_wigans(> "date_created"TIMESTAMPZ,
alfred_wigans(> "id"INTEGER PRIMARY KEY,
alfred_wigans(> UNIQUE ("keyword","guildid")
alfred_wigans(> );
ERROR:  type "timestampz" does not exist
LINE 7: "date_created"TIMESTAMPZ,```

also this @proven arrow

wait I fix it thanks

await self.client.conn.execute("BEGIN TRANSACTION;")
                                await self.client.conn.execute("""INSERT INTO tags (keyword, key, guildid, authorid,date_created,tag_use_count)
                                                                    VALUES ($1,$2,$3,$4,$5,$6);""",key, final_key_msg, ctx.guild.id, ctx.message.author.id, now, 0)
                                await self.client.conn.execute("COMMIT;")``` Dude id is a primary key but I get this error. Why? ```py
  File "asyncpg\protocol\protocol.pyx", line 196, in bind_execute
asyncpg.exceptions.NotNullViolationError: null value in column "id" violates not-null constraint
DETAIL:  Failing row contains (test, hey, 744151626230530078, 695598628756127755, 0, 2020-10-05 13:01:16.974151+05:30, null).```

[quaint tiger]

"id"INTEGER PRIMARY KEY, you need to use serial, not integer, otherwise it won't set a default value.

[high geyser]

ok so I use fetch or row @torn sphinx

"id"INTEGER PRIMARY KEY, you need to use serial, not integer, otherwise it won't set a default value.
@quaint tiger thanks dude

[torn sphinx]

@high geyser wdym fetch or row?

[high geyser]

should I use fetch or row?

[torn sphinx]

just use fetch()

[high geyser]

okay

[quaint tiger]

Hey folks. I wanted to know is it possible to use Webpack with Django and Python? And how hard is it if that’s possible?
@celest quiver #web-development

Don't cross-post, this has nothing to do with DBs

[torn sphinx]

So i have these 2 tables. Orders and OrderItems. Relationship is each Order can have many OrderItems
OrderItem Table

id INT PRIMARY KEY
name TEXT
quantity INT
pack_value INT
order_id INT FOREIGN KEY REFERENCES ORDER_ID ON ORDERS TABLE
product_id INT FOREIGN KEY REFERENCES PRODUCT_ID ON PRODUCTS TABLE

Orders Table

order_id INT PRIMARY KEY
status TEXT
user_id INT FOREIGN KEY REFERENCES USER_ID ON USERS TABLE

The below query works but is not thinking about the status of an order when making query. I want to adapt it so that i can provide a where clause so it filters where the order status is X. For example, where status='pending'

SELECT name, 
       SUM(quantity * pack_value) AS quantity,
       1 AS pack_value,
       order_id
FROM order_items
GROUP BY name,
         order_id;

Can someone help me how i would do this?

I made this fiddle with the table and test data, to make easier for you. https://www.db-fiddle.com/f/iQNJsQrEajHpib1UyxorS2/2

[proven arrow]

@torn sphinx You can use the JOIN clause

[torn sphinx]

hmm can you provide example?

[quaint tiger]

SELECT name, 
       SUM(quantity * pack_value) AS quantity,
       1 AS pack_value,
       order_items.order_id, orders.status as status
FROM order_items left join orders on order_items.order_id = orders.order_id
GROUP BY name, order_id
HAVING orders.status = "Pending";```

[proven arrow]

beat me to it lol

[quaint tiger]

Sorry, was in that db-fiddle lol

@torn sphinx ^

[proven arrow]

Oh i didnt even see that well yeah power of joins

[torn sphinx]

Yeah nice, that is look like is working. Thanks a lot!!

[high geyser]

I have a doubt

[<Record key='hey'>]```

this is my fetched list

postgresql

How can I get the value of str key from this list

[torn sphinx]

@quaint tiger Sorry again, but if i have user_id for each order then how to get the user name from a users table?

[high geyser]

record = [<Record key='hey'>]
str = record["key"]``` is this correct?

when I do this I get an error though

TypeError: list indices must be integers or slices, not str```

[torn sphinx]

@high geyser you have there list of record objects not one single

[high geyser]

okay

[torn sphinx]

so you need access first like record[0]['key']

[high geyser]

oh

okay thans

[torn sphinx]

or if you will have many record, then you can maybe make loop through it

[high geyser]

no I will get only one the conditional is designed like that

thanks

[torn sphinx]

So i did like this,

SELECT name, 
       SUM(quantity * pack_value) AS quantity,
       1 AS pack_value,
       order_items.order_id, orders.status as status, users.name
FROM order_items 
left JOIN orders ON order_items.order_id = orders.order_id
JOIN users ON orders.user_id = users.user_id
GROUP BY users.name, order_id
HAVING orders.status = "Pending";
``` But it says `Column 'name' in field list is ambiguous`

https://www.db-fiddle.com/f/iQNJsQrEajHpib1UyxorS2/2

[quaint tiger]

@torn sphinx you need to specify which column to read name from. select users.name...

[torn sphinx]

i manage to fix that by doing order_items.name but now i get #1054 - Unknown column 'users.user_id' in 'on clause'

SELECT order_items.name, 
       SUM(quantity * pack_value) AS quantity,
       1 AS pack_value,
       order_items.order_id, orders.status as status, users.name
FROM order_items 
left JOIN orders ON order_items.order_id = orders.order_id
JOIN users ON orders.user_id = users.user_id
GROUP BY users.name, order_id
HAVING orders.status = "Pending";

[proven arrow]

Well yeah if you read the error it says users.user_id column does not exist.

And if you look at your users table, from the fiddle you have id bigint(20) unsigned NOT NULL AUTO_INCREMENT,

So your column is called id and not user_id

[torn sphinx]

F

2 Stupid mistake of mine 😅

Thanks a lot, spent how long on this idk for such little thing

[proven arrow]

Normally reading the error helps, but if its the first time you've seen this kind of error then i guess you will now know for next time.

[high geyser]

                        alias_obtained = await self.client.conn.fetch("""SELECT alias_name
                        FROM alias_storage
                        WHERE guildid = $1;""", ctx.guild.id)
                    
                        if asyncpg.Record(key) not in alias_obtained:```

am using postgresql

so basically alias_obtained returns a list of record, right

I want to check if the record aliasname with the name of var key exists in the list of records. How can I do this? please help me

records = [record alias_name = "strhere",record alias_name = "strhere"]``` Over here I want to check if `record alias_name = key` is in the records list

[proven arrow]

Can you not iterate through the records list?

And then check if your value is inside that record or not

[dusk oxide]

Can anyone just explain how MongoDB works?

I mean the rough structure.

[shell ocean]

I mean the rough structure.
@dusk oxide it's basically

a big dict.

[dusk oxide]

Done.

That is all the explanation I needed

[shell ocean]

👋

[dusk oxide]

https://tenor.com/view/the-office-bow-michael-scott-steve-carell-office-gif-12985913

[high geyser]

Use postgresql It was highly recommended to me too so am using it

It has lot if features bro I am discovering

[digital shore]

how do i like do mongo with python as website backend?

[high geyser]

                    alias_check = await self.client.conn.fetchrow("""SELECT alias_name
                        FROM alias_storage
                        WHERE guildid = $1 AND alias_name - $2;""", ctx.guild.id,key_word)```error:```py
    statement = await self._protocol.prepare(stmt_name, query, timeout)
  File "asyncpg\protocol\protocol.pyx", line 163, in prepare
asyncpg.exceptions.UndefinedFunctionError: operator does not exist: text - unknown
HINT:  No operator matches the given name and argument types. You might need to add explicit type casts.``` why did this error occur?pls help ping me hwen help pls

[quaint tiger]

AND alias_name - $2 shouldn't that be = instead of - @high geyser

[high geyser]

ohh thanks

[icy crane]

I have a Python web-app with flask hosted on Heroku that uses 34 Global variables. I want to get rid of Global variables, and put them in a database of some kind.

Do you have tips on how to go about this? Should I just throw all 34 Globals in a database and call it a day?

[icy crane]

I've looked into Redis and it seems like that would be the better option for large-scale apps, but my app is pretty small (max 100 users)

I'm probably gonna try SQLite and see if that holds water

[torn sphinx]

yeah sqlite is fine

[icy crane]

Alright, nice

good to know :)

[torn sphinx]

Hi again, so i have this query:

SELECT order_items.product_id AS product_id,
       order_items.name, 
       SUM(quantity * pack_value) AS quantity,
       1 AS pack_value,
       order_items.order_id, orders.status as status, users.name
FROM order_items 
left JOIN orders ON order_items.order_id = orders.order_id
JOIN users ON orders.user_id = users.id
GROUP BY users.name, order_id
HAVING orders.status = "Accepted";```

So currently it gives result like this

You see it has 2 rows with same product_id. I want to do so if there are multiple rows with same product_id then merge them into 1 row by adding up the quantity values. Other values in the row are identical so will stay same.

The order_id, and name column can be removed

[proven arrow]

You dont really need to add anything for that. Just remove some fields.

SELECT order_items.product_id AS product_id, 
       order_items.name, 
       SUM(quantity * pack_value) AS quantity, 
       1 AS pack_value, orders.status as status 
FROM order_items 
left JOIN orders ON order_items.order_id = orders.order_id 
JOIN users ON orders.user_id = users.id 
WHERE orders.status='Accepted' 
GROUP BY product_id;```

I just removed order_items.order_id, and users.name from the columns, grouped it by the product_id. I also moved your HAVING into a WHERE clause so it doesnt consider orders that may be in another status.

@torn sphinx Take a look at that, and see how it works out for you

[torn sphinx]

alright i will try thanks for the help

[glass gorge]

hmm so i managed to load some data into my table

however it seems to have deleted 20 rows for some reason

it seems to be a lot of Warning | 1265 | Data truncated for column 'phone_number' at row 1 | | Warning | 1262 | Row 1 was truncated; it contained more data than there were input columns |

but the columns match up

[torn sphinx]

@proven arrow Super!!! Thanks so much.!!

[glass gorge]

why would it contain more data than columns, the csv file that I uploaded doesn't have data in any extra columns or anything.

[torn sphinx]

umm idk, can you show like example row it has deleted?

@glass gorge

[glass gorge]

from the csv or the table

[torn sphinx]

csv

[glass gorge]

it seems to be just the phone number column

[torn sphinx]

but is doing this for every row in csv or just some?

[icy crane]

In my Flask app, I have multiple global list variables, such as:

location = ['address 1', 'address 2', etc]
appointmentTime = ['time 1', 'time 2', ect]

The values in the list are user-specific.
I want to replace these global variables with a database. Is it possible to put these list variables in one database?
So, I'd have 1 column for user_id, a column for location and a column for appointmentTime.

[torn sphinx]

maybe because some phone number is different format? maybe? idk you will wait have to for someone who is experience in this

[glass gorge]

it looks like it's only doing it for rows 1-32

i have it set as phone_number | varchar(11) | YES | | NULL | |

[torn sphinx]

no show csv row man

[glass gorge]

i understand

[torn sphinx]

not table

@icy crane yes you can, but maybe not best design you know

sorry bad english 😬

[glass gorge]

,1,sarah,connor,[e-mail],$2b$12$n3JP66sQ5NKxVYU16.O41uHbLeUkhmM5jwdRq4jTllkMCS4ppYKl2,12392875872,,Call Center Rep,Expedient Staffing Solutions

[icy crane]

@torn sphinx What would you recommend as an alternative?

[torn sphinx]

better would be to have multiple table, i forgot what they call this process, but you are basically storing specific data in a specific table

so like you will store user details in one, and if user has for example orders, you will store orders in different table

@glass gorge you can show me a line after row 32?

[icy crane]

Right

[torn sphinx]

i just want to see maybe if there is any difference between the 2 rows

[glass gorge]

,33,john, connor,[e-mail],$2b$12$dvtazLd52HAUZmp/dICLue5wdeXR0JygT7orbtoiA4JGFN9xCKq4S,16785599408,,Call Center Rep,Eclaro

[torn sphinx]

hmmmm

is the same

[glass gorge]

yea

[torn sphinx]

strange

[glass gorge]

:/

[torn sphinx]

i am not sure then man sorry, i have been also banging my head all day with database problem

[glass gorge]

ok no problem

i changed the phone number value type to varchar so this error shouldnt exist

but it still does exist

and it only exists for the first 30 entries

[vestal vine]

async def write_to_db(column, value, guild_id):
        self.db.execute('''
                UPDATE servers 
                SET ?=? 
                WHERE guild_id=?''', 
                (column, value, guild_id))
        self.conn.commit()
```am I allowed to use parameter substitution for column names in sqlite?

[subtle solstice]

can u not create the string and shove it into a variable and execute that?

This might help u https://stackoverflow.com/questions/13880786/python-sqlite3-string-variable-in-execute

[glass gorge]

does not specifying the character types (like utf-8) or whatever, impact the way your data gets loaded into the table?

[potent sparrow]

Who uses Bloomberg API for news/stocks? Is it good?

[torn sphinx]

My issuse when there more then 1 server id in the db the last one to get ented to the db it dosent leave but all the others leave so like example

[(39843984948394)] it would never leave this one

[(454656545664565), (39843984948394)]
And now it dosent leave the one in front it leaves the id in the back i dont want it to leave any of the guilds that are in the db

So insted of leaving all the ids in the back and only stay in the front id
I want the bot to stay in any ids in the db

@tasks.loop(seconds=5)
async def checkehitelist():
    conn = await aiosqlite.connect('servers.db')
    guilds = client.guilds
    cursor = await conn.execute("SELECT thesevid FROM sev")
    records = await cursor.fetchall()
    print(records)
    for guild in guilds:
        if guild.id not in records[0]:
            print("Not whitelisted")
            owner = guild.owner
            await owner.send("Your not on the whitelist! :x:")
            await guild.leave()
        else:
            pass

[glass gorge]

alright well it looks like it's not even loading this data properly into the table

some of the columns are off

for example

data is not being placed in the correct column

[torn sphinx]

want me to show you my whitelist command and unwhitelist command

[glass gorge]

?

no I can't help im trying to meet a deadline :/

[torn sphinx]

huh?

i thought u were helping me

[glass gorge]

oh no sorry

i was typing more information for my question

as i am debugging

sry for confusion

im very stressed xD

[torn sphinx]

loL

ok

My issuse when there more then 1 server id in the db the last one to get ented to the db it dosent leave but all the others leave so like example

[(39843984948394)] it would never leave this one

[(34443434343434,), (761774217632940063,)]
And now it dosent leave the one in front it leaves the id in the back i dont want it to leave any of the guilds that are in the db

So insted of leaving all the ids in the back and only stay in the front id
I want the bot to stay in any ids in the db

@tasks.loop(seconds=5)
async def checkehitelist():
    conn = await aiosqlite.connect('servers.db')
    guilds = client.guilds
    cursor = await conn.execute("SELECT thesevid FROM sev")
    records = await cursor.fetchall()
    print(records)
    for guild in guilds:
        if guild.id not in records[0]:
            print("Not whitelisted")
            owner = guild.owner
            await owner.send("Your not on the whitelist! :x:")
            await guild.leave()
        else:
            pass

[proven arrow]

@torn sphinx If [(34443434343434,), (761774217632940063,)] is your result from the DB then you records[0] will only match the first element of that list

[torn sphinx]

how would i make it check all ids

so it dosent leave the one in the back

like if i have 100 ids in the db i dont want it leaving all 99 and staying in the 1 i want it to stay in the 100

@proven arrow

[proven arrow]

@torn sphinx A simple way would be to do all_ids = [item[0] for item in records]

Then check if guild.id not in all_ids

[glass gorge]

does anyone know if using varchar in your model

is valid

im working with sqlalchemy

[proven arrow]

Looking at the docs it doesnt look like it does: https://docs.sqlalchemy.org/en/13/core/type_basics.html

[ember marsh]

It would probably be a String type.

There's a translation between sqla types and the underlying db depending on what db you're using.

It's good practice to put a length on your sqla String type like with varchar, though.

do we say char or car around here?

[glass gorge]

i say car

but i also pronounce the h

as in cool whip

😛

jk

car

does the fact that sqlalch doesnt support varchar

is that gonna impact my db model, because on my model it's labeled as varchar

and im having this issue where it's not properly loading data into the right columns

[ember marsh]

what's your backend?

[glass gorge]

mysql

[ember marsh]

I'd assume it would just blow up if you used an unsupported type.

Have you tried logging the raw sql?

set echo=True on your engine

[glass gorge]

ive been doing show warnings

[ember marsh]

There's probably nothing fatally wrong

[glass gorge]

it shows me error in one column for each row, but the problem is earlier down the line I think. Because it has essentially moved everythign over in the data set

[ember marsh]

Oh weird.

[glass gorge]

where do i set echo=True

[ember marsh]

create_engine statement

[glass gorge]

oh

i have all that setup in my config file

database uri

this is literally my output for one of my first columns

[ember marsh]

queries look good though?

[glass gorge]

uhh no everything is off by 2 columns

i think it's something to do with the datatypes

[ember marsh]

are you managing this with sqla or is this legacy stuff?

[glass gorge]

im just trying to push my seed

giggiity

so this is legacy(?), mysql

it's on my server

im really confused here

in sqlalchemy they are defined as str

in mysql there's no str data type

instead it's text

or char

or variations of that.

idk what that actually means for my dataset

is there a difference between the syntax? i mean i thought sqlalchemy was supposed to handle this?

[brazen charm]

alchemy's string types are a TEXT types

its because they have to keep a universal system ignoring how the syntax changes across dbs

[glass gorge]

so since my model is filled with str types

i should go into my db and change them to text types? Currently in my db they are represented as varchars

[brazen charm]

thats vine if theyre varcahrs

sqlalchemy should handle all that for you

[glass gorge]

so you don't think my issue is a data type problem?

[brazen charm]

i doubt it

whats your error

[glass gorge]

basically it is loading my data

2 columns off

[brazen charm]

🤔 waht

[glass gorge]

so if i have 10 columns

everything is shifted to the right by 2

[brazen charm]

Okay well this is a first from me

[glass gorge]

lol

uhhh one moment i will show you

[ember marsh]

sqlalchemy should have a TEXT and String (VARCHAR) type

and your model definitely matches your column names?

[glass gorge]

ummm

idk how to actually show you my query data set

but basically i get a warning on the phone number column

and it states basically too much data, and they had to truncate it

and the values it's trying to place are the emails

so schedule = hashed password

phone number = 0, because it couldnt fit the emails in there

password_hash = last name

everythign is basically off by 2

[ember marsh]

what the hell

[brazen charm]

first time ive ever seen that happen

[glass gorge]

probably how im loading in the data?

like delimiters?

so in my csv i deleted the values in the id and employee id columns

which also happen to be the first two

[ember marsh]

what was the raw query?

[glass gorge]

you mean to load it?

[ember marsh]

wait are you getting this selecting or inserting?

[glass gorge]

what is "getting this"

[ember marsh]

the error you're seeing

[glass gorge]

so on and so on

up to row 32

i get no actual errors when i query the data

it's just putting out the wrong values

and as i mentioned, it looks to have loaded everything by 2 columns to the right

so for example ill query the schedule

and that's the hashed password

or i will query the phone_number

and it would have deleted the entry, as per the loading error.

or query the password_hash

and get the last name

[ember marsh]

hmm. so what raw sql is sqlalachemy sending with echo on?

[glass gorge]

idk, because i never set it. in my code I don't have an engine var

i just use a config file var that points to the db uri

im doing all of this

in the terminal

on my server

using mysql syntax

like the example queries i gave were done in mysql not sqlalchemy

[ember marsh]

oh

so you're using like a load data statement?

[glass gorge]

yeah

load data infile 'asdjlkasjdkas'

like i said im pushing the seed

[ember marsh]

do you have any empty values?

[glass gorge]

yes

like i mentioned the first two columns are empty

however I do not load the first colum (the id)

but i do load the second column (employee_id)

and there are random empty values scattered about

would you like to see a sample of the csv and the load query

[ember marsh]

sure

I did some reading and I think you need all the columns or else specify them

and nulls should be \N

[glass gorge]

if i include the id column, then it wont autoincrement through

and a line from the csv

[fresh jacinth]

How much money would it cost me for one of you guys to show me how I am messing up so bad with django heroku postgresql databases?

[torn sphinx]

is there any way to get mysql without putting in a credit card?

[minor ruin]

mysql is free

[warped lark]

^

you dont have to sign up or anything

[mortal nymph]

Lmao good meme

[high geyser]

someone pls help

I have a table and I want to get the rank of a row using a column

in postgresql

alfred_wigans=> SELECT * FROM tags;
  keyword   |           key           |      guildid       |      authorid      | tag_use_count |           date_created           | id
------------+-------------------------+--------------------+--------------------+---------------+----------------------------------+----
 test       | hey                     | 744151626230530078 | 695598628756127755 |             0 | 2020-10-05 13:07:54.480875+05:30 |  1
 welp       | hey                     | 744151626230530078 | 695598628756127755 |             0 | 2020-10-05 13:26:52.913145+05:30 |  3
 506        | geeeeeeeeeeeeeeeeeeeeee | 744151626230530078 | 695598628756127755 |             0 | 2020-10-05 13:27:10.593818+05:30 |  4
 1022222222 | delete                  | 744151626230530078 | 695598628756127755 |             0 | 2020-10-06 09:08:00.27047+05:30  |  6
(4 rows)```

This is my table

I want to get the rank for a keyword using its column tag_use_count

Like if the count is higher rank is lower

[high geyser]

        results =await self.client.conn.fetch("""SELECT keyword,
                                        RANK () OVER (
                                        ORDER BY tag_use_count DESC
                                        ) AS rank
                                        FROM tags
                                        WHERE guildid = $1""",guildid)``` I did this and got record objects keyword and the correct rank

But how can I get the rank specific for a keyword from it?

[<Record keyword='test' rank=1>, <Record keyword='1022222222' rank=2>, <Record keyword='welp' rank=3>, <Record keyword='506' rank=3>]```

this is the list of record I get now

I want the rank for a specific keyword

how can I get that inside the same query itself? pls help

I got it booooiiis I used a subselect query instead and it WORKED

!close

oops

[tepid cradle]

You don't need to use sub-query

Just do a partition

Select *, rank() over (partition by tag order by tag_use_count desc) 
From tags

@high geyser

[fathom topaz]

anyone know of an in-memory SQL database written in pure python? trying to find examples of things like query planners, etc written in python

[grim lotus]

No one would write a database in python

for learning Purposes , there may be few available

[fathom topaz]

No one would write a database in python
@grim lotus That's... not really true, but sure.

[grim lotus]

well Databases in Python is actually not the choice until and unless you are making it for learning purpose, if you are production grade you'll never choose python for something like database

considering Databases , not small CacheManagers

[fathom topaz]

hard disagree. it depends on the nature of the database.

distributed analytical databases with an object store as the primary storage mechanism? python's a great fit.

lots of proprietary stuff out there, written primarily in python

[brazen charm]

There is actually a full dB written in python built off of postgre

[fathom topaz]

if its built off of postgres, im guessing its using postgres for all the heavy lifting

[brazen charm]

Sorta

https://github.com/edgedb/edgedb

[fathom topaz]

huh, neat.

ooooh they're using rust for their high-performance libs

thanks for the link

[brazen charm]

Edgedb aka magics stacks have made some of the biggest contributions to python and async recently

[strong shale]

ok but is there any reason why this code works at the top of the script but at the bottom it doesn't

from time import sleep
from myriadinsert import addsong
client = pymongo.MongoClient(hahano)
db = client['tunes']
collection = db['music']
while True:
    count = 1
    carts = [1]
    songs = collection.aggregate([{"$sample": {"size": 5}}])
    for i in songs:
        collection.update_one({f"song": i["song"]}, {"$set": {"pick": count,}})
        collection.update_one({f"song": i["song"]}, {"$set": {"votes": 1,}})
        count += 1
    sleep(30)
    tc = 1
    picks = collection.find().sort("votes",-1)
    for i in picks:
        if tc > 3:
            break
        else:
            carts.append(i["cart"])
            tc += 1
    print(carts)
    for i in songs:
        collection.update_one({f"song": i["song"]}, {"$set": {"pick": 0}})
        collection.update_one({f"song": i["song"]}, {"$set": {"votes": 0}})```
i'm talking the for i in songs loop at the top sets pick and votes correctly but then at the bottom it doesn't set either to 0 in the mongodb database? i'm watching it with compass

[high geyser]

search_results =await self.client.conn.fetch("""SELECT keyword
                 FROM tags
                 WHERE guildid=$1 AND keyword % $2
                 ORDER BY similarity(keyword, $2) DESC
                 LIMIT 100;
              """,guildid,key_word)```error:```py
  File "asyncpg\protocol\protocol.pyx", line 163, in prepare
asyncpg.exceptions.UndefinedFunctionError: operator does not exist: text % unknown
HINT:  No operator matches the given name and argument types. You might need to add explicit type casts.``` why this happens? pls help. Datatype for keyword is text

[proud forge]

@round temple

hey

so this is you?

CREATE EXTENSION pg_trgm;``` I created this extension but when I am using ```py
 query = """SELECT keyword
                 FROM tags
                 WHERE guildid = $1 AND keyword % $2
                 ORDER BY SIMILARITY(keyword, $2) DESC
                 LIMIT 100;
              """
        search_results =await self.client.conn.fetch(query,guildid,key_word)``` I get this error ```py
    raise CommandInvokeError(exc) from exc
discord.ext.commands.errors.CommandInvokeError: Command raised an exception: UndefinedFunctionError: function similarity(text, unknown) does not exist
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.``` why? pls help ping me when help pls

[round temple]

no need to ask and ping in two servers :) somebody else here might be able to help

[hidden nest]

hey ! Just a quick question. Is there a way to check if a database contains values? I mean I don't wanna check if there is a specific value, just if there are values already inside. I am using postgresql and I wanted to make a small script in python to check if the database already has something inside so that my script that fills all the database doesn't execute each time 🙂

[gleaming arch]

You can use an ON CONFLICT clause to make it ignore the query if destination rows already exist

With postgres its- ON CONFLICT DO NOTHING

[reef ledge]

Hello ! I have been banging my head against a wall try to do what should be very simple and I just can't fine an example of the syntax.

I want to call a stored procedure with sqlalchemy from a scoped_session so im not added my DB URL all over my application. I can find plenty of information on how to do so with flask_sqlalchemy but I'm using pyramid as my web framework. I dont have the time to rewrite it in flask for this one goal. Call the raw query from the DBSession.execute is less than ideal but is currently my best answer apart from calling the db like 7 times to get the data and building the same results. Any ideas?

[boreal crag]

hi everyone!
i have a question about pymongo.
how to count collection's all of document in a database use pymongo
if u can,plz send a demo code
Thanks!

[west furnace]

@boreal crag what?

[boreal crag]

@boreal crag what?
@west furnace can we talk use dm?

[west furnace]

of course :)

[west furnace]

PyMongo is messed up though

WHY CAN I DO THIS?????

this is awful

[brazen charm]

because mongo creates collections if they dont already exist in your case

the standard method is also client['my_db'] for the database
and my_db['collection'] for the collection

dont blame pymongo for doing what its supposed to :P

[west furnace]

It doesn't create the client, if that doesn't exist

dont blame pymongo for doing what its supposed to :P
Well, then they could disable it

because mongo creates collections if they dont already exist in your case
because like, what??? This seems like begging for bugs

Imagine you make a freaking typo and end up creating a new db and then after a while you're wondering where all the data went

[quaint tiger]

Imagine you make a freaking typo and end up creating a new db and then after a while you're wondering where all the data went
@west furnace or you could learn the tools you work with.... that is expected and documented behaviour. Don't make typos, and write tests. https://pymongo.readthedocs.io/en/stable/tutorial.html

If you find that too permissive, I suggest you use something with more restrictive schemas, like SQL.

[burnt turret]

I like mongodb because of that same permissiveness

[quaint tiger]

It has its uses. If you are afraid of making typos, you can always use a constant.

DB_NAME = "foo"
db = conn[DB_NAME] # or presumably, conn.getattr(DB_NAME)

[west furnace]

or you could learn the tools you work with.... that is expected and documented behaviour. Don't make typos, and write tests
Can be said for literally any shitty library 🤣
If you find that too permissive, I suggest you use something with more restrictive schemas, like SQL.
Nah, I like the document model

It has its uses. If you are afraid of making typos, you can always use a constant.
Literally just one example, this sort of behavior is general really unsafe

[quaint tiger]

Can be said for literally any shitty library 🤣
I mean, that isn't just the (pymongo) library. If you insert documents into a collection that does not exist, mongodb itself will also create the collection for you.

If you think that is "shitty" feel free to write a better DB server. You could make millions.

[west furnace]

Nah, it's not like I use pymongo for my own stuff

but my friend does and "钟致远电脑服务 Official Discord" needed help

[glass gorge]

does anyone know

when instantiating your app; do you need to have a python if __name__ == "__main__": db.create_all() app.run()

specifically the db.create_all()

like do i need to create the db every time? probably not...

[quaint tiger]

Nah, you don't. Unless your app is meant to be deployed by end users, not just you?

Even then, migrations should be applied in CI rather than at startup, if you do things properly.

[glass gorge]

well I deploy it, but they use it

but they might need to do things like add users

to the db

[quaint tiger]

Adding users should be done by forms.

Into an existing DB.

[glass gorge]

yeah 🙂

alright cool thx

but what if I make changes to the db schema?

[quaint tiger]

Depends on the way you deploy.

Normally, you can just apply migrations (or make changes) while your server is running.

Be sure to have backups lol

I deploy semi-automatically and apply migrations as part of building my containers.

[glass gorge]

i see so i can go into the cli and run like db.flask_migrate or something

and it will update

[quaint tiger]

Yup. Don't know the exact command, because I am not even sure what you are using. Flask and SQLAlchemy?

[glass gorge]

yea

i mean i can look up the command

I have migrate imported somewhere in my app

[quaint tiger]

Don't really have SQLAlchemy exp, but if you have doubts, I am sure someone here can help too.

But yes, no real need to restart the server to apply those migrations.

[glass gorge]

yeah I have a migrate = Migrate(app, db) in my run file

i guess that kinda auto updates it

but from there on, once it is running. I have to apply the command(whatever it may be) in the cli

[quaint tiger]

Yup. I mean, if you prefer restarting the server and having it applied then, it's not a bad thing.

Actually, it may even be safer.

Since otherwise Flask may cache old files 🤔

So ignore what I said, and don't change it is my recommendation xD

[glass gorge]

uhhh

i am no better off than I was when I started xD

lmao

[weak charm]

Anybody knows if pymongo protects me against nosql injections?

[brazen charm]

well think about it

no

it will try but you should still try sanities your inputs

[weak charm]

And how to do that, if a string starts with $ remove the $?

Nvm i will take a look on internet

[pliant cliff]

async with aiosqlite.connect("MYDB.sqlite3") as db:
            await db.execute("""CREATE TABLE IF NOT EXISTS Users (
                Name TEXT NOT NULL,
                ID PRIMARY KEY UNIQUE INTEGER NOT NULL,
                Level INTEGER NOT NULL,
                Exp INTEGER NOT NULL,
                ExpThresh INTEGER NOT NULL
                );""")```
sqlite3.OperationalError: near "INTEGER": syntax error
I keep getting this error...

but i'm not seeing where I messed up

[modern mulch]

I think you're supposed to put INTEGER at the beginning

ID INTEGER PRIMARY KEY UNIQUE NOT NULL,

[tidal turtle]

can someone tell me whats wrong with it?

new_word = testing
cursor.execute("UPDATE table SET random_word = %s", new_word)

ERROR:

Ignoring exception in on_message
Traceback (most recent call last):
  File "C:\Siebe\Python\Discord Bots\venv\lib\site-packages\discord\client.py", line 333, in _run_event
    await coro(*args, **kwargs)
  File "C:\Siebe\Python\Discord Bots\MaxerG v3\minigames\guess_the_word.py", line 77, in on_message
    cursor.execute("UPDATE table SET random_word = %s", new_word)
  File "C:\Siebe\Python\Discord Bots\venv\lib\site-packages\mysql\connector\cursor.py", line 569, in execute
    self._handle_result(self._connection.cmd_query(stmt))
  File "C:\Siebe\Python\Discord Bots\venv\lib\site-packages\mysql\connector\connection.py", line 599, in cmd_query
    result = self._handle_result(self._send_cmd(ServerCmd.QUERY, query))
  File "C:\Siebe\Python\Discord Bots\venv\lib\site-packages\mysql\connector\connection.py", line 487, in _handle_result
    raise errors.get_exception(packet)
mysql.connector.errors.ProgrammingError: 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '%s' at line 1

database = MYSQL

[modern mulch]

Maybe using [new_word] instead of new_word would work

[tidal turtle]

thx

[pliant cliff]

I think you're supposed to put INTEGER at the beginning
@modern mulch oh?
I didn't know it mattered

thanks

[glass gorge]

uhhh

do bigintegers take arguments?

[torn sphinx]

how do i get mysql without inputting a credit card

[proven arrow]

@glass gorge What do you mean arguments?

how do i get mysql without inputting a credit card
@torn sphinx mysql is free

[glass gorge]

BigInteger(20)

for example

[torn sphinx]

i know, but it says it needs credit card info even though its free

[proven arrow]

@glass gorge No it doesnt take any args

[glass gorge]

Mr Pilot man

you are the real mvp

🙂

[torn sphinx]

most valuable pilot

[proven arrow]

i know, but it says it needs credit card info even though its free
Where exactly are you seeing this?

[glass gorge]

best pilot

[torn sphinx]

https://www.oracle.com/cloud/free/

which was on https://www.mysql.com/downloads/

[proven arrow]

Yeah thats is oracle cloud. You dont need that particular one.

[torn sphinx]

so where do i make an account?

[proven arrow]

What OS are you on?

Do you want a GUI to interact with the Database as well?

[glass gorge]

boooo no gui

[minor ruin]

You really don’t want Oracle

[proven arrow]

MySQL default link takes you the oracle tier

[minor ruin]

Read entire page

[proven arrow]

Im aware, he needs https://dev.mysql.com/downloads/installer/

[minor ruin]

Free links are there

[proven arrow]

Or other links to other os platforms here https://dev.mysql.com/downloads/

[glass gorge]

sneaky affiliates

[minor ruin]

What you really want is MariaDB

[glass gorge]

why mariadb

[torn sphinx]

i'm on windows 10

[proven arrow]

mariadb is a fork of MySQL. There are quite varying reasons but most people I hear say this because MySQL is part of oracle

@torn sphinx https://dev.mysql.com/downloads/installer/

[torn sphinx]

which one should i download from https://dev.mysql.com/downloads/installer/?

top or bottom or both?

[proven arrow]

Any you like. I think the first is just the installer, which downloads the additional stuff from the internet.

When it asks you to create an account, there should be a link at the bottom that says No thanks, just start my download.

[torn sphinx]

alr i'm downloading it. thanks!

[proven arrow]

Oracle are really pushing people to create accounts for some reason. Even when downloading JDK.

[brazen charm]

user data™️

[keen gorge]

so I connect to my server, selecting if the user is on cooldown and if so, return

should I close connection before returning?

its for my bots levelsystem

        temp=sql.c()
        mydb=temp[1]
        myuser=temp[0]
        mydb.execute("SELECT * FROM levelsetup WHERE gid=%s", (message.guild.id,))
        level_is_enabled = mydb.fetchone()
        if level_is_enabled:
            mydb.execute("SELECT * FROM levelcooldown WHERE gid=%s AND uid=%s", (message.guild.id, message.author.id,))
            cooldown = mydb.fetchone()
            if cooldown:
                myuser.close()
                return```

currently Im closing the conncection

[proven arrow]

Generally you would have a single connection across your application

Since opening/closing connections can be expensive

[keen gorge]

but like its an on_message event

[proven arrow]

Even worse then

[keen gorge]

but can I just conncect once to my server?

I thought I would get a timeout

[proven arrow]

Since on_message would be triggered every message you should try and avoid such stuff inside it.

[keen gorge]

how could I create a levelsystem then?

[torn sphinx]

what are databases

[keen gorge]

a place where u can store data

its a base for the data

so its a database

btw lufthansa-pilot r u german

[proven arrow]

You can make a single connection, and then each time you want to use the database, you would use that connection to run your query. Or better use a connection pool

[keen gorge]

okay didnt know that

ty

[sturdy orbit]

Hey guys, anyone around for a quick question about influxdb / grafana?

[quaint tiger]

Hey guys, anyone around for a quick question about influxdb / grafana?
@sturdy orbit https://dontasktoask.com

[sturdy orbit]

😄 thanks @quaint tiger turns out i was just storing the time incorrectly. all is working now thanks ❤️

[high geyser]

CREATE EXTENSION pg_trgm;``` I created this extension but when I am using ```py
 query = """SELECT keyword
                 FROM tags
                 WHERE guildid = $1 AND keyword % $2
                 ORDER BY SIMILARITY(keyword, $2) DESC
                 LIMIT 100;
              """
        search_results =await self.client.conn.fetch(query,guildid,key_word)``` I get this error ```py
    raise CommandInvokeError(exc) from exc
discord.ext.commands.errors.CommandInvokeError: Command raised an exception: UndefinedFunctionError: function similarity(text, unknown) does not exist
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.``` why? pls help ping me when help pls

Dude how to do this

Pls ping me when help

[torn sphinx]

@client.command(aliases=["work"])
async def clean(ctx):
    conn = await aiosqlite.connect('money.db')
    await conn.cursor()
    id = ctx.author.id
    author = ctx.message.author
    bossmsg = "None."
    ranmsg = ['The Gulag showers.', 'The Gulag.', 'The Prison']
    rancash = random.randint(5,150)
    randommsg = random.choice(ranmsg)
    if rancash < 130:
        bossmsg = "Wow, nice scubbing, you really got deep there."
    else:
        if rancash < 50:
            bossmsg = "You could of done better. Try again next time!"
        else:
            if rancash > 35:
                bossmsg = "Your terrbile, keep it up and you wont have a life!, JK"
            else:
                pass
            
    await conn.execute("UPDATE money SET cash = cash + ? WHERE id=?", (rancash, id))
    embed=discord.Embed(color=0xe31616)
    embed.add_field(name=f"{author.name}", value=f"You worked in: `{randommsg}` \nAnd got: {rancash} Cash \nBoss says: {bossmsg}", inline=False)
    embed.set_footer(text=f"Requested by: {author.name}")
    embed.set_thumbnail(url=discord.User.avatar_url)
    await ctx.send(embed=embed)
    await conn.commit()

I made this work command, how would i make this buy command?

[torn sphinx]

If I open the connection to a db at the beginning of running my bot, shouldn't I be opening and closing it on each interaction and not leave a perpetually open connection?

just open between commands

like i kinda did

@torn sphinx

thanks m8 I thought thats how I was supposed to do it

sorry I cant help u

[next sand]

how can I add description to the following table?

[torn sphinx]

please help anyone

???

[burnt turret]

Looks like this is some question paper of sorts, and I don't think we're supposed to help with graded work here

[pseudo cove]

!rule 5

[delicate field]

Rules

5. Do not provide or request help on projects that may break laws, breach terms of services, be considered malicious or inappropriate. Do not help with ongoing exams. Do not provide or request solutions for graded assignments, although general guidance is okay.

[proven arrow]

@torn sphinx If you are not confident enough with SQL atm you can get some practice at https://sqlbolt.com/
They have interactive lessons which will introduce you to the concepts of SQL, and cover most of the questions you have in your message.

[torn sphinx]

anyone has a good lecture or video why indexing is importance?

[proven arrow]

@torn sphinx I have watched this one from Laracon. Although its not so short its a good talk. https://www.youtube.com/watch?v=HubezKbFL7E

[torn sphinx]

oh ok thx

[crude cedar]

In postgresql how can I always put tomorrow's date in where condition?

[jovial yew]

@crude cedar try current_date+1

[crude cedar]

@jovial yew thanks for the reply.
My mistake actually I want only yesterday results
I tried your suggestion but still got today's records mixed
Pls see this screenshot

[jovial yew]

@crude cedar can you send the query ?

[crude cedar]

@jovial yew thanks got it

[jovial yew]

@crude cedar didn't do anything 👀

[crude cedar]

@jovial yew man i was stuck for hours and your hint just do the wonder for me.

[sick mural]

Hey, guys,
i'm stuck on a problem i can't solve and maybe one of you already had something similar.
I have set up a MongoDB and query it with MongoEngine.
To access it I provide an API which accepts and delivers JSONS.

It now contains the following classes (the classes are shortened for better overview)

class Summary(me.EmbeddedDocument):
count_measurements = me.StringField(),
count_events = me.StringField(),
time = me.EmbeddedDocumentField(Time)

class Trip(me.EmbeddedDocument):
summary = me.EmbeddedDocumentField(Summary)
measurements = me.ListField(me.EmbeddedDocumentField(Measurements))

class Trips(me.Document):
trip = me.EmbeddedDocumentField(Trip)

The problem now is, if I want to make a trip object out of the JSON (
current_trip = Trips(**posted_data)) it always takes only the last entries (e.g. for Summary only "time" is available).

if I specify count_measurements in my json, which I deliver to the API, I get " "message": "The fields "{'count_measurements'}" do not exist on the document "Summary"",
"status": 500"

if I change the position of time and count_measurements (
class Summary(me.EmbeddedDocument):
count_events = me.StringField(),
time = me.EmbeddedDocumentField(Time),
count_measurements = me.StringField()

count_measurements is available and I cant post e.g. time

[torn sphinx]

@client.command(aliases=["work"])
async def clean(ctx):
    conn = await aiosqlite.connect('money.db')
    await conn.cursor()
    id = ctx.author.id
    author = ctx.message.author
    bossmsg = "None."
    ranmsg = ['The Gulag showers.', 'The Gulag.', 'The Prison']
    rancash = random.randint(5,150)
    randommsg = random.choice(ranmsg)
    if rancash < 130:
        bossmsg = "Wow, nice scubbing, you really got deep there."
    else:
        if rancash < 50:
            bossmsg = "You could of done better. Try again next time!"
        else:
            if rancash > 35:
                bossmsg = "Your terrbile, keep it up and you wont have a life!, JK"
            else:
                pass
            
    await conn.execute("UPDATE money SET cash = cash + ? WHERE id=?", (rancash, id))
    embed=discord.Embed(color=0xe31616)
    embed.add_field(name=f"{author.name}", value=f"You worked in: `{randommsg}` \nAnd got: {rancash} Cash \nBoss says: {bossmsg}", inline=False)
    embed.set_footer(text=f"Requested by: {author.name}")
    embed.set_thumbnail(url=discord.User.avatar_url)
    await ctx.send(embed=embed)
    await conn.commit()

I made work command anybody know how to make a buy command

[torn sphinx]

Ok so I am pretty new to this but I need help with this.. Can I create a database as a json file and request data from it for a command in discord.py?

[harsh pulsar]

@torn sphinx you can use python to read and write json files. So yes?

But json is not a good format for data that needs to be modified

It is not suitable as a database

[torn sphinx]

Oh.. so which one should I use?

[harsh pulsar]

What is your use case

What kind of data are you storing and retrieving? How much data? Where is the discord bot being hosted?

[velvet coyote]

Which database supports dict ?

[torn sphinx]

I basically want to set a command which is like ??requireage {days} and save that to a db for each server according to their needs and if a user from less than that age joins the server the bot kicks him

Sorry internet died

[velvet coyote]

if you could asnwer my queation the pls ping me

[harsh pulsar]

@velvet coyote you can write json to sqlite and postgresql, so you can write a dict as json to those databases. Or you can use something like mongodb. Or you can write it as text to a key-value data store. But I don't recommend focusing on the "dict" part. It's better to consider what data you have and how it should be stored, in general.

[velvet coyote]

ok

hmm ok

[harsh pulsar]

@torn sphinx have you used sql before? The problem with json is that you cannot modify part of a json file, you can only overwrite it. Which makes a big mess

[torn sphinx]

Oh.. No I am completly new to dbs

[harsh pulsar]

Either use sqlite or use the dbm module to store a mapping between user id's and ages

[torn sphinx]

I am pretty new to this lol so would I import sql and creat a db with it

?

@harsh pulsar Mind if I dm?

[harsh pulsar]

BRB one moment

No please keep discussion here in the server

[torn sphinx]

Oh ok..

https://paste.pythondiscord.com/arufozejam.py So this code was given to me by someone to create a sql db is this a working and or good code?

@client.command(aliases=['bribe', 'leave'])
async def escape(ctx):
    conn = await aiosqlite.connect('moneylist.db')
    id = ctx.author.id
    ids = await conn.execute("SELECT id FROM money")
    author = ctx.message.author
    role = discord.utils.get(ctx.guild.roles, name="TheGulag")
    values = await ids.fetchall()
    print(values)
    cursor = await conn.execute("SELECT cash FROM money WHERE id=?", (id,))
    records = await cursor.fetchone()
    print("Id: ",  id)
    print("Cash: ", records[0])
    usercash = records[0]
    if usercash > 1:
        if usercash >= 1000:
            await conn.execute("UPDATE money SET cash = cash - ? WHERE id=?", (1000, id))
            await author.remove_roles(role)
    else:
        await ctx.send("You dont got the funnds!")

    await ids.close()
    await conn.close()```

Any good way to do this and did i do it right?

@harsh pulsar

[glass gorge]

any good resources on making mysql queries using python?

[harsh pulsar]

@torn sphinx sql is a bit complicated. You will need to learn the SQL language in order to use a database

Lots of concepts to learn

Maybe use DBM instead

!d g dbm

[delicate field]

`dbm`

Source code: Lib/dbm/__init__.py

dbm is a generic interface to variants of the DBM database — dbm.gnu or dbm.ndbm. If none of these modules is installed, the slow-but-simple implementation in module dbm.dumb will be used. There is a third party interface to the Oracle Berkeley DB.

[harsh pulsar]

Unless you want to start learning sql. It's a good skill to learn but it isn't necessarily something you can learn overnight

I also very very strongly recommend against using code you do not personally understand

That code someone wrote for you is fine, but it's not simple

You will have to spend time learning how it works

[torn sphinx]

Ohk thanks

Yeah I agree

[harsh pulsar]

👍

@glass gorge it's no different from any other sql database

Install the relevant library, connect to database, make queries

[glass gorge]

im using pymysql because of the hardware requirements

i didnt realize you needed to connect using pymysql.connect

since I already have a app.config["SQLALCHEMY_DATABASE_URI"] =

is that the case? seems redundant?

[torn sphinx]

Should I use a UUID for accounts Id's inside of a database table or is there a better data type for this purpose?

[velvet coyote]

c.execute("""CREATE TABLE """)

are create table keywords?

WHy do I get this error?

import sqlite3

conn = sqlite3.connect('employee.db')

c = conn.cursor()

c.execute("""CREATE TABLE employees (
            first text, 
            last text, 
            pay integer
            """)

conn.commit()

the error is

Traceback (most recent call last):
  File "C:\Users\user\Desktop\database\database.py", line 7, in <module>
    c.execute("""CREATE TABLE employees (
sqlite3.OperationalError: incomplete input

[modern mulch]

Maybe the parentheses don't match?

[velvet coyote]

yes

[harsh pulsar]

@glass gorge what is "app"?

[glass gorge]

app = Flask(__name__) @harsh pulsar , I made progress tho. Just confused why I have to connect to the db again if i already have that info in my uri

[glass gorge]

any idea why I'm getting this error

it does indeed exist according to the docs

https://pymysql.readthedocs.io/en/latest/modules/cursors.html

A cursor which returns results as a dictionary```

[harsh pulsar]

@glass gorge check spelling and capitalization

also flask doesn't have any relationship to or knowledge of the database

that's just a piece of config data

you should use that config data to connect to the database

[glass gorge]

ahh

[harsh pulsar]

unless you're specifically using flask-sqlalchemy https://flask-sqlalchemy.palletsprojects.com/en/2.x/

[glass gorge]

I am using flasksqlalchemy

[harsh pulsar]

in which case you should follow their instructions

[glass gorge]

k

[harsh pulsar]

https://flask-sqlalchemy.palletsprojects.com/en/2.x/quickstart/#a-minimal-application

don't guess

read docs

[torn sphinx]

anyone knows why i get this

mysql.connector.errors.InterfaceError: 2013:

Lost connection to MySQL server during query

[glass gorge]

bleh invalid salt, according to google the error code is usually indicative of a larger problem [for which the error code does not help solve]

[stable violet]

I'm being a bit daft, I'm trying to make a query with an unknown amount of parameters, I tested in postgresql with pgadmin4 and the query itself is fine when I pass in params, but I'm having trouble converting it to python. I keep receiving tuple index out of range

    placeholder = []
    for pcs in viewer_ids_list:
        placeholder.append('%s')
    placeholders = ', '.join(placeholder)

    print(f"162 viewer_ids_list length = {len(viewer_ids_list)}, placeholders length = {len(placeholders)}\n"
          f"{viewer_ids_list}\n{placeholders}")

    c.execute(f"""
    SELECT active_seconds, inactive_seconds, viewer_id, game 
    FROM daily_data 
    WHERE streamer_id=%s 
    AND date=%s 
    AND viewer_id IN {placeholders}
    """,
              (streamer_obj.twitch_id, curr_date, viewer_ids_list))```
I'm not entirely sure where to go from here, but I do know that placeholders is where my issue lies

I've tried passing in both a string and list as well

[glass gorge]

idk about postgres

but i know i couldnt use f strings in mysql

i literally had this issue like 40 minutes ago

I can't help beyond that, sorry friend

[brazen charm]

postgre would be an array type

[stable violet]

hmm that could be part of the issue, I'll have to doublecheck that then, how do you get around the issue? I'm not really fond of formatting sql queries

[brazen charm]

x IN ANY($n::type<array block>)

example would be x IN ANY($1::TEXT[]) which would be a single depth array of TEXT types (strings)

[stable violet]

so it'd look like -

AND viewer_id IN ANY($1::INT[{placeholders}])``` if I'm understanding correctly

[brazen charm]

no

because you shouldnt use string formatting

$1 is the postgre place holder

idk how psycopg2 is gonna fuckery that but ig we'll see

[stable violet]

ah I've been using %s as the placeholder so far, didn't know it was supposed to be $1

[brazen charm]

its not for psycopg2

every other postgre system and postgre itself does it

but psycopg2 likes to be weird and inconsistent like most of its stuff

[stable violet]

ahh alright I see

[harsh pulsar]

@glass gorge you wrote Dictcursor but it's supposed to be DictCursor

[glass gorge]

Yeah I fixed tht

[stable violet]

pretty sure I've written this wrong, but testing it is throwing a value error @brazen charm

c.execute(f"""
SELECT active_seconds, inactive_seconds, viewer_id, game 
FROM daily_data 
WHERE streamer_id=41659699 
AND date='2020-10-01'
AND viewer_id IN ANY (%1::INT[])
""",
          (viewer_ids_list,))```

[glass gorge]

invalid salt is because of something else, it was the new error after I fixed it

[stable violet]

not entirely sure how or where I'd be passing in my list in this instance

[glass gorge]

I probably have to look more closely at my code somewhere

[stable violet]

I figured it out, literally just needed to be AND viewer_id IN %s for anyone curious

[glass gorge]

there you go

good job

[stable violet]

Thanks, just needed to play around with it a bit and come back after a break

[mortal nymph]

getting a syntaxe error (mysql) near %s , I don't understand . please help
discord_id = mycursor.execute("SELECT discord_id FROM discord WHERE guild_id = %s",(guild))

[modern mulch]

Try [guild] instead of (guild)

[mortal nymph]

thanks idk how it worked lol

[modern mulch]

@mortal nymph [guild] makes it a list, which is the iterable that execute expects. If you want to use a tuple, do (guild, )

Comma is mandatory for 1-element tuple

[burnt mauve]

Is anyone familiar with Dask dataframes?

[torn sphinx]

yo

yo

[torn sphinx]

i'm trying to create a database with mongoDB but it's not letting me import motor.motor_asyncio. I'm on the latest discord.py version and i've also tried to install motor but it's not working
and since it says motor isn't a module but installing it is a no-go for some reason

[velvet coyote]

For SQLITE3 What does CREATE TABLE do?

[sick mural]

For SQLITE3 What does CREATE TABLE do?
@velvet coyote
If you cant answer that yourself by the name 'create table', you should really get to know the basics of sql

[velvet coyote]

wdym by table?

[sick mural]

@velvet coyote please do yourself a favor and watch a video series on YouTube or similiar about "basics of sql" before you try to use it. I mean I like the hands on mentality but you should know your 1x1

[torn sphinx]

i'm trying to create a database with mongoDB but it's not letting me import motor.motor_asyncio. I'm on the latest discord.py version and i've also tried to install motor but it's not working
and since it says motor isn't a module but installing it is a no-go for some reason

???

[pseudo cove]

error message on install? @torn sphinx

[torn sphinx]

i get no error msg

[sick mural]

How do you Install it? Is it in a Docker environment or locally?

[torn sphinx]

through cmd and inside of PyCh terminal

both nothing

[sick mural]

Which command?

[torn sphinx]

python3 -m pip install motor

@sick mural

[velvet coyote]

Anybody know why I get this error :

Ignoring exception in on_member_join
Traceback (most recent call last):
  File "C:\Users\user\AppData\Local\Programs\Python\Python38\lib\site-packages\discord\client.py", line 333, in _run_event
    await coro(*args, **kwargs)
  File "C:\Users\user\Desktop\database\db.py", line 34, in on_member_join
    connect_to_db.execute("INSERT INTO joined_members VALUES (:id, :joined_date)", {':id': member.id, ':joined_time':joined_date})
sqlite3.ProgrammingError: You did not supply a value for binding 1.

The code

is

import datetime
import sqlite3
from datetime import date, time

import discord
from discord.ext import commands

intents = discord.Intents.all()
client = commands.Bot(command_prefix='|', intents=intents)



@client.event
async def on_ready():
    print (f"Logged on as {client.user}")

@client.event
async def on_member_join(member):

    joined_date = member.joined_at.strftime("%b-%d-%Y %H:%M:%S")

    connection = sqlite3.connect('member_info.db')
    connect_to_db = connection.cursor()

    try:

        connect_to_db.execute("""CREATE TABLE joined_members (
            id integer,
            datetime text

        ) """)

        with connection:
            connect_to_db.execute("INSERT INTO joined_members VALUES (:id, :joined_date)", {':id': member.id, ':joined_time':joined_date})

        connection.commit()


 

    except sqlite3.OperationalError:
        with connection:
            connect_to_db.execute("INSERT INTO joined_members VALUES (:id, :joined_date)", {':id': member.id, ':joined_time': joined_date})

    print(connect_to_db.fetchall())

    connection.close()

[high geyser]

you didnt define joined_date in the dictionary that follows the query

@velvet coyote

[velvet coyote]

ye i dound the mistake

thx though

[high geyser]

np

is this for a discord bot though

[velvet coyote]

ye

lol

duh

[high geyser]

my opinion is dont use sqlite3 because it blocks a lot and isnt ideal for discord bots

u need an asynchronous database

I used sqlite3 before too

but now I switched

use psql asyncpg its the best for discord bots

[velvet coyote]

psql?

[high geyser]

[velvet coyote]

huh?

[high geyser]

Postgresql

[velvet coyote]

oohh ok

let me learn that then

[high geyser]

use the asyncpg module for python

its easy it has similar syntaxes when compared to sqlite3

but is really powerful

[velvet coyote]

ohhhhh ok

so ima learn postgresql

[high geyser]

yeah

[velvet coyote]

postgresql is a module right?

@high geyser

[high geyser]

no Its a database thing

the module is called asyncpg

[velvet coyote]

ohh

@high geyser Postgresql also supports json

that's nice

[high geyser]

yeah