databases

[proven arrow]

So which one do you want to know the answer for? Because they both have different ways of doing it

[torn sphinx]

how do I create a table for my db, im using aiosqlite

[trim lintel]

mysql

[proven arrow]

For MySQL you can select from the information_schema.columns table. You will want to check the table_schema is your schema name, and column_name and the table_name is what your looking for. It will return you some data back but you only need the aggregate count(*) of the result.

If the count is 1 then a column was found otherwise it’ll be 0.

[ruby breach]

select column_name from INFORMATION_SCHEMA.columns where table_name = 'tablename' and column_name = 'column name';

If the column exists, it will return it, otherwise it will return nothing. A way to use it in python would be like this:

# assuming sqlite3
exists = db.fetchval("select column_name from INFORMATION_SCHEMA.columns where table_name = 'tablename' and column_name = 'column name';") is not None

if exists is True:
  print("The column exists!")
else:
  print("The column does not exist")

the exact usage will depend on the library you use

[trim lintel]

Right thanks i have something like this before but didnt know this was the best way

[torn sphinx]

how does inheritance work in tables?

does the other table inherit only the rows or the data in the rows too?

[proven arrow]

What kind of inheritance are you looking at?

There are different types

[torn sphinx]

how do I create a table for my db, im using aiosqlite

I dont know really i just found an option that allowed that

i was wondering if it worked as a shared row

where all data is shared between parent and child table

[proven arrow]

I’ve already answered you question many times over the past few days. Instead of asking the same question try to be more specific of what you exactly need help with.

[torn sphinx]

ok

i cant edit the columns either

[ruby breach]

CREATE TABLE IF NOT EXISTS tablename (
  var1 TYPE,
  var2 TYPE,
  ...
)```

[torn sphinx]

are var1 and var2 the column names?

they are

[ruby breach]

^

[torn sphinx]

btw

I found something strange

In my db GUI

There is money and money[]

data types

[ruby breach]

money[] is an array of money

[torn sphinx]

O ok im so dumb

[ruby breach]

it's like the difference between an int and a list of integers in python

[torn sphinx]

ye ik

i just didnt know brackets meant arrays

do you know about inheritance?

in tables?

[ruby breach]

No I've never used that

[torn sphinx]

ill just mess with them until i know what they do

[ruby breach]

or you could read this https://www.postgresql.org/docs/9.0/ddl-inherit.html

might be helpful

[torn sphinx]

it kinda confused me

after reading that

they do work as a shared column

but they dont redirect the insert into a table to all the child tables

so that the data updates in every single one of them

hmm

@ruby breach what are the list of TYPES?

[ruby breach]

I would have to look at it for a while, and probably mess around with it

[torn sphinx]

thats what im about to do

i really dont believe the part that inheritance works only for columns and not for data

ill test it myself

[ruby breach]

https://www.tutorialspoint.com/sql/sql-data-types.htm

[torn sphinx]

what?

so for guild id's, which would be the best?

inheritance doesnt work like shared columns

[proven arrow]

What is it you are trying to achieve?

[proven arrow]

BIGINT.

[torn sphinx]

I dont really like working with big tables so I want to make "subtables"

so thats why i wanted to make shared columns

[proven arrow]

Well what is making your table so big? What are you storing in it?

[torn sphinx]

i am making a multi purpose bot for my server

[torn sphinx]

ok

so it will have many categories

[torn sphinx]

maybe make separate tables?

currency, awards, titles

etc

thats what im making

multiple tables

but i want a shared row

between all of them

user_id

[proven arrow]

Ok so then you introduce a relationship between the tables

[torn sphinx]

foreign keys?

[proven arrow]

Yes

[torn sphinx]

do they work like that?

[charred fractal]

You could possibly make multiple different databases.

[torn sphinx]

i want to stick to a single database

[charred fractal]

and run them in different files.

[proven arrow]

Yes because a row can reference a row from the user table

[proven arrow]

That is something you would want to avoid

Just leads to more problems and hassle

[torn sphinx]

would i still have to update in both tables each time i insert a new user id?

[charred fractal]

yeah, it could potentially lead to more problems, although what kind of problems?

[proven arrow]

No because the other tables will only reference the user table. So whatever data is in the users table is what they see or have.

You should read into normalisation

It’ll help you understand

[torn sphinx]

so it will work as a shared column

what i wanted to do

cool

ill try it now

and figure out how to do it with pgAdmin 4

[proven arrow]

You can write sql for it. But before you do first decide what kind of relationship you want, between these tables.

[torn sphinx]

i just want the data of the column user_id shared between all of the tables i will make

btw

are tablespaces useful?

[proven arrow]

You should not really worry about that.

[torn sphinx]

i have all my tables in the default one

but sure

i guess they are not really important here

[proven arrow]

Generally that stuff is used by dbas or people who know what they are doing

[charred fractal]

how many tables can you have in a database until it crashes?

[proven arrow]

Many. There’s generally no limit given.

You’ll probably have system issues. Not that the database can’t handle it but your system can’t

[trim lintel]

Hi so i am now design the orders of my database for my shop. I have a question regarding how to store the price as a double like 99.99 or as a whole number? I am confused with mixed responses.
Also in the order lines table, should we reference with a foreign key to the product details or no?

[proven arrow]

For price, screw floats. You should store it as a integer value and so in cents. It’s because sometimes you may end up with wierd rounding issues, especially when you start multiplying and adding multiple quantities of items, discounts etc.

And for the sales lines you should store the details of the product as they are at the point of sale. It’s a hard copy of how much the customer paid, what product code etc.

[trim lintel]

Oh yeah trueee

[charred fractal]

well especially when you start dividing then rounding, because that could get very weird for pricing ranges.

[proven arrow]

If you reference the product with a foreign key and the price changes in the future then your sales table will hold incorrect data.

[trim lintel]

Yeah i understand now. I was confus because i saw some examples and tutorial that did it differently and then some answers on internet said something different too as well. But thanks guys 🙂

[ruby breach]

numeric

[charred fractal]

well, It's very important to use mydb.commit() to make sure it updates in your database aswell, because just updating it in discord doesn't do much good.

[ruby breach]

do not use anything except numeric for discord ids

[torn sphinx]

i found a tutorial that suggested using chars

strings

🤨

[ruby breach]

are you talking to me?

[torn sphinx]

yes

[ruby breach]

text/chars is a bad idea

[torn sphinx]

now my doubts are clear

found in more than one actually

[ruby breach]

Numeric will take up a lot less space

[torn sphinx]

like 3

almost conviced me

[charred fractal]

using strings for ids, will mostly likely cause more errors

[ruby breach]

the main problem is storage

NUMERIC is much better for numbers, because each digit can only be 0-9, whereas TEXT can be that plus thousands of other characters.

[torn sphinx]

but if i want to store an excessive amount

say

5 sextillion

i would use a string

[ruby breach]

NUMERIC can store integers that are up to a few thousand digits

[torn sphinx]

because numberic only goes up to 8 bytes

[ruby breach]

I don't think that's true

[torn sphinx]

which is in the trillions

[ruby breach]

no

[torn sphinx]

big int?

[ruby breach]

NUMERIC can go up to thousands of digits before and after the decimal point

[torn sphinx]

how?

is there a data type

for that

[ruby breach]

🤷

[torn sphinx]

i mean i know it can go up to that much

but in dbs

it only goes up to 8 bytes

thats what data types say

[ruby breach]

maybe it's only in postgres

"The NUMERIC value can have up to 131, 072 digits before the decimal point and 16, 383 digits after the decimal point"

[torn sphinx]

numeric or numeric(p,s) is a real number with p digits with s number after the decimal point. The numeric(p,s) is the exact number.

or this

o

there is actually

where i read there wasnt a numeric data type

[ruby breach]

I think it depends on if you're using mysql, sqlite, or postgres

[lament estuary]

is there a channel for git?

[torn sphinx]

well at least in postgres

there is NUMERIC data type

i think it does that

[ruby breach]

#tools-and-devops I think

[torn sphinx]

ye, numeric does the job for REALLY big numbers

[amber path]

Does anyone favor postgres over other databases such as mysql or mongodb? If so why?

[ruby breach]

I don't know anything about mysql, but I like SQL type database over mongo because of foreign keys

[amber path]

Which database type do you prefer to use?

[ruby breach]

postgres

[amber path]

Why did you choose postgres? I was going to use it myself. The reason is because I heard it is harder to mess up with postgres compared to mysql.

I'm not sure if it's true because I haven't used it yet, but i will learn.

[ruby breach]

I've never used mysql, I just prefer SQL type database (sqlite, postgres, mysql) over mongodb

[torn sphinx]

i think the numeric data type is the same in every sql database

postgres has the same data types as the others

[torn sphinx]

so I do await db. execute(CREATE TABLE) ?

it goes like that

its ok

[ruby breach]

yeah

[torn sphinx]

i have another question, will there be any difference in speed if i use numeric for almost everything?

from my pov, it leaves other data types redundant

[amber path]

btw do you guys use software to manage you db or do you prefer to just use the command line?

[torn sphinx]

depends if you want to learn to write sql all the time or partially

[ruby breach]

yes, numeric is slower than int, bigint, and smallint

[ruby breach]

I use the command line because I don't really do much outside of my discord bot

[proven arrow]

What do you mean by hard to mess up?

[ruby breach]

also numeric takes up more space than other int types

[torn sphinx]

is space a problem ?

[amber path]

so i heard that its easier to like write the wrong command with mysql and delete your whole db with msql, and i hear postgres has something that protects against that.

[ruby breach]

depends on how big your database is going to get

[amber path]

...or something like that. idk db yet. i only did basic sql on w3schools

but thats my reasoning behind beginning with postgres @proven arrow

[torn sphinx]

we talking about disk space right?

if its that i dont think it will be a problem at all

but well i guess its good practice to use it just when required

[proven arrow]

Yeah that’s not really the case @amber path

[amber path]

idk what you're talking about, i was answering @proven arrow . i kinda broke off when you asked a question @torn sphinx and @ruby breach answered you. You guys were kinda doing you own thing.

[ruby breach]

yeah, but it's good to always optimize disk space, so you're in the habbit of it when it actually matters.

[torn sphinx]

thanks circuit for your time

now im gonna go do all the stuff in my db

cya

[ruby breach]

bye

[amber path]

@proven arrow i heard something like that. But I honestly have no idea about it or weather its true, or even how to describe it. That's why i was asking. I'm about to go finish django app part 2 though. Thank you all for your time.

[torn sphinx]

where should I put this statement btw?

[ruby breach]

It just needs to run before the bot logs in

[torn sphinx]

ok

can I run it outside the code?

@ruby breach

[smoky radish]

rate my PostgreSQL (actually TimescaleDB) query to:

• group and average "count" into 5 minute blocks
• moving average over the last 5
• group by the hour/minute of day to merge the data of multiple days as to compare the general daily trends.

select
  now()::date + Concat(extract("hour" from b."time"), ' hours ', extract("minutes" from b."time"), ' minutes')::interval AS "time",
  Avg(b."count")
FROM (
    SELECT
      e."time",
      avg(e."count") over (ORDER BY e."time" rows BETWEEN 5 PRECEDING and CURRENT row) AS "count"
  FROM (
        select
          time_bucket('300s', p."time") AS "time",
          avg(p."count") AS "count"
        FROM players AS p
        GROUP BY 1
        ORDER BY 1
        ) AS e
    ) as b
GROUP BY 1
ORDER BY 1

[stuck raptor]

I have a sql database where I store my bots server prefixes

How would I update a row which has 2 collems one which is the ID of the server, another is the prefix

[torn sphinx]

pardon?

[stuck raptor]

nvm

[torn sphinx]

hbn

i dont know whats wrong

still points out an error next to foreign

: /

tried with the constraint FK_currency FOREIGN KEY REFERENCES

but the same

[solemn root]

I'm not sure why one INSERT INTO works, but not the other and seemingly not the UPDATE either.

async def on_ready():
  print('Logged in as')
  print(bot.user.name)
  print(bot.user.id)
  print('------')
  #connect to database
  connection = sqlite3.connect('tinker.db')
  #create a cursor
  crsr = connection.cursor()
  #create the table if it doesn't exist
  sql_command = """CREATE TABLE IF NOT EXISTS tinker (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    user_cash INTEGER,
    user_token INTEGER UNIQUE);"""
  crsr.execute(sql_command)

  #crsr.execute("""INSERT INTO tinker (user_cash, user_token) VALUES (NULL, NULL);""") <-- this prints

  ans = crsr.fetchall()
  print(ans, "ans")
  print("**************")

  connection.commit()
  connection.close()

#86400 secs
@commands.cooldown(1, 5, commands.cooldowns.BucketType.user)
@bot.command()
async def work(ctx):
  #random number Gen
  cash_to_add = math.floor(random.random()*100)
  #Retrieve discord author message
  author = ctx.message.author
  #Connect to database
  connection = sqlite3.connect('tinker.db')
  #create cursor
  crsr = connection.cursor()
  
  #if the table is not empty update it
  if("""SELECT FROM tinker WHERE user_token = author.id;"""):
    crsr.execute("""UPDATE tinker SET user_cash = user_cash + ? WHERE user_token = ?;""", (cash_to_add, author.id))
  #else insert the information
  else:
    crsr.execute("""INSERT INTO tinker (user_cash, user_token) VALUES (?, ?);""", (cash_to_add, author.id)) <-- but this doesn't

  print("------------------")

  connection.commit()

  crsr.execute("""SELECT * FROM tinker""")
  rows = crsr.fetchall()
  print("start")
  for row in rows:
    print(row, "row")
    print("middle")
  print("exit")
  connection.close()
  await ctx.send("Your Wallet: **$" + str(cash_to_add) + "**\n")```

I get:

author.id
---------------
start
exit```
or:

author.id

start
(1, None, None) row
middle
exit```
but this is what I want:

author.id
---------------
start
(1, user_cash, author.id)
middle
exit
(repeat above on execution)```

The #'d out INSERT INTO works, the other one and the UPDATE don't. Even though they should?

[torn sphinx]

I managed to link a foreign key to a primary key but for some reason the foreign key wont refer to the primary key

The column is blank even tho its there

Referring to another column primary key which is not

it does exist

and its working

ig

if you know the answer please mention me

[solemn root]

same, if anyone can figure out the error in my code, please @ me so I can find your response here easily.

[steel terrace]

is it easy to build a database in mysql with an encryption level of sha 256?

[final canyon]

uh

sha256 != encryption

sha is a hashing algo it is one way

[steel terrace]

guys why is mysql not asking to setup password in the installation process?

anyone?

[hasty juniper]

#bot-commands

[heady root]

Hi folks, ripping my hair out over SQL syntax - can anyone assist? I have a SQL query like this that i'm trying to adapt to my python script

SELECT books.id, books.name, books.maingenre FROM books WHERE books.name ILIKE '%lord%of%the%rings' ORDER BY books.popularity DESC LIMIT 50

thus far I have the following, but i can't figure out how to insert a % for every word:

query = "SELECT books.id, books.name, books.maingenre " 
            "FROM books" 
            " WHERE books.name ILIKE %(search)s" 
            " ORDER BY books.popularity DESC LIMIT 50"
 book_list = pd.read_sql_query(query, db.session().bind,
                                    params={"search": req['search']})

[steel terrace]

anyone?

[ancient delta]

Can you have spaces after SQL keywords or would that be invalid SQL when you run the query? E.g. is this allowed:

SELECT movies.name, 
       books.name, 
       streaming.name


FROM movies,
     books,
     streaming 


WHERE movie.name IS NOT NULL,
      book.name IS NOT NULL,
      streaming.name IS NOT NULL

The above example is obviously very simplistic, but the reason I'm asking is because I have an SQL query that is 1766 lines that I'm supposed to understand in order to improve upon (reduce). However reading it is a huuuge headache. So I was wondering if I can split it up into sections like above, while it remaining a valid query.

@heady root can't you just escape the % characters?

or do you want to replace every space with the % character? in that case you can do:

mystring.replace(" ", "%")

this will replace every space with % but won't put % in front of the word or behind it

[ruby shore]

I have a design choice qustion. I am running a site that tracks data and it fetches and stores new data every hour. Right now it fetches 231 new datapoints every hour. The data looks like this

{
    "zom-leftclick-dropper": 21458,
    "fossil-island": 4801,
    "chat-translator": 983,
    "party-panel": 3019,
    "magic-secateurs": 5377,
    "bank-heatmap": 10444,
    "essence-running": 10860,
    "...": "..."
}

And this is how I set up my table:

CREATE TABLE if not exists data (
    id integer PRIMARY KEY AUTOINCREMENT,
    plugin varchar(100),
    date DATE,
    installs integer
);

Should I be storing the data another way that is more efficient?

[ancient delta]

however you can easily do this by saving the altered string to a variable and then adding the % yourself like so:

altered_search = search.replace(" ", "%")
query = f"SELECT books.id, books.name, books.maingenre " 
            "FROM books" 
            " WHERE books.name ILIKE %{altered_search}%" 
            " ORDER BY books.popularity DESC LIMIT 50"

Is this what you're looking for? @heady root

Here's the result of the code I showed you:

[heady root]

thanks @ancient delta 😄 - i've actually been trying to repl this inside flask while it's running

[ancient delta]

you're welcome:)

[heady root]

i just found this glorious SO post after being bamboozled by this for hours

https://stackoverflow.com/a/62204136/12137312

i was looking in the wrong place:

"SELECT * FROM books WHERE author LIKE :search", {"search": '%' + search + '%'}

[ancient delta]

glad you figured it out!

[proven arrow]

You can have as much space as you want

[ancient delta]

Thanks for the answer.

Any tips on how to read and understand huge SQL queries?

i dread beginning to understand this monster query

[proven arrow]

Split them up like you said, and understand what they do each do and how they link together

Also you can add comments to the query as well

[ancient delta]

Oh yeah, I forgot about that!

[heady root]

1766 lines is basically the LOTR of SQL queries 😮

gl, godspeed for gondor!

[ancient delta]

It's fucked and I have to understand it and break it down for my bosses for tomorrow

thanks haha

[heady root]

yeah i've never worked with one that big but sometimes I get SQLAlchemy to spit out the queries it generates from the ORM and try to visually parse them like that. If you've got an IDE with syntax highlighting it definitely helps

[ancient delta]

Oh for sure

I'm using VS Code

I wouldn't dare doing it without an editor

[flint imp]

Hey, I'm kinda noob with asyncpg and got a few questions.
Please ping me for any answers.

Do i have explicitly release the connection back to the pool after a request?
Ive read some ppl say it release itself after a certain time and some say I have to release it.

First example was how I did in the first times but it seems like it just creates sessions over and over for any functions I call.
So I did what like in the second example and seems to work.

I just need to know if it was possible to keep my first example and change a parameter in postgres to force close session that are in idle mode?

    @property
    async def get_msglbdata(self):
        async with self.db.acquire() as con:
            return await con.fetch(
                f"SELECT guid, messages FROM messages WHERE guid ~ '^{self.member.guild.id}_' ORDER BY messages DESC;"
            )

    @property
    async def get_msglbdata(self):
        async with self.db.acquire() as con:
            data = await con.fetch(
                f"SELECT guid, messages FROM messages WHERE guid ~ '^{self.member.guild.id}_' ORDER BY messages DESC;"
            )
            await self.db.release(con)
            return data```

[proven arrow]

You don't need to release it back if you aquire it with async with

You can view the 2 different ways here: https://magicstack.github.io/asyncpg/current/api/index.html#asyncpg-api-pool
The second where you aquire with await and release is not recommended.

[flint imp]

I mean, thats how I did in the first way but it keeps creating sessions

And the session remain until i reach the limit and get error @proven arrow

[slender rose]

Traceback (most recent call last):
  File "/home/ubuntu/.local/lib/python3.8/site-packages/discord/ext/tasks/__init__.py", line 101, in _loop
    await self.coro(*args, **kwargs)
  File "activity.py", line 266, in levelup
    db = await db_connection()
  File "activity.py", line 58, in db_connection
    db = await asyncpg.connect(user = "cutie", database = "cutie", password = "TahribTajwar", host = "localhost", command_timeout = 30)
  File "/home/ubuntu/.local/lib/python3.8/site-packages/asyncpg/connection.py", line 1718, in connect
    return await connect_utils._connect(
  File "/home/ubuntu/.local/lib/python3.8/site-packages/asyncpg/connect_utils.py", line 663, in _connect
    con = await _connect_addr(
  File "/home/ubuntu/.local/lib/python3.8/site-packages/asyncpg/connect_utils.py", line 642, in _connect_addr
    await asyncio.wait_for(connected, timeout=timeout)
  File "/usr/lib/python3.8/asyncio/tasks.py", line 483, in wait_for
    return fut.result()
asyncpg.exceptions.TooManyConnectionsError: sorry, too many clients already```
what can we do to avoid this problem

i tried making a pool and acquiring from there but pool doesnt want to work

[flint imp]

are you acquiring it with async with ? Because uve hit the same problem i did with my first example

[slender rose]

nope i didnt

[proven arrow]

You can try increasing the max number of connections in the pool

Or check your max connections allowed with postgres and increase that

[flint imp]

Yeah i know about those params but it looks kinda gross...
It feels like the more users use the features the more sessions are created and remain in idle mode without being closed

[proven arrow]

There could be a number of things which can be causing that. Things like how you are making the queries, and where can impact this as well.

What kind of application are you working on?

[flint imp]

Discord Bot

If thats your question

[proven arrow]

I see, do you make any queries for every message or something like that? Or make queries in a loop of some sort?

[flint imp]

I do make functions for lets say get a messages count for the command author and in that command I call that said function.
It will just create the session and then goes in idle mode

[slender rose]

if i did async with ... and did a return inside the function does it still release the connection?

[flint imp]

No that will put the session in idle mode

At least for me, thats my issue rn

[slender rose]

thats a problem then

[golden warren]

Hello, i have this part of my code:

            for x in DB.usersdb_cursor.execute('SELECT user_points FROM economy'):
                print(x[0])
                current_lvl = int((1/4) ** x[0])

It always return 0 but my tuple is (27,). Have you got idea?

[slender rose]

hm i think it did release the connection for me or smth cause like when it didn't release the connection back in time it'd stop working

[flint imp]

https://i.gyazo.com/2e7520db1479ad1afa5c3dbfa1979206.png

I've hit the max session one time

[slender rose]

oh welp

[flint imp]

And thats only on a remake of my bot

So not alot of requested queries

[slender rose]

thats odd

i was told that if you did async with your connection will release once you get out of the context manager

[slender rose]

print(x[0]) this returns 0?

[golden warren]

Yes.

[flint imp]

All my functions are async with but idk

whats wrong

[slender rose]

weird

i cant check if i'm hitting max sessions but its not giving me any problems yet

[slender rose]

is that mysql?

[golden warren]

sqlite3

[slender rose]

i dont use sqlite3 but arent you supposed to do smth like this

DB.userdb_cursor.execute("SELECT user_points FROM economy")
x = DB.userdb_cursor.fetchone()
print(x[0])```

[golden warren]

I going to try.

[slender rose]

also im assuming its for discord bot then you should use asqlite instead of sqlite3, since asqlite is async

[autumn epoch]

I can't get asyncdb to work, I have been trying for a while. @ruby breach can you help me.

[ruby breach]

asyncpg?

[autumn epoch]

Oh ya

[slender rose]

anyone knows how to check what tables exist in my postgres database

[ruby breach]

@autumn epoch Are you getting error messages?

[autumn epoch]

The same as usual

[ruby breach]

Would you re-upload your current code, and I'll see if I can figure out the problem

[ruby breach]

SELECT *
FROM pg_catalog.pg_tables
WHERE schemaname != 'pg_catalog' AND 
    schemaname != 'information_schema';

try that

[autumn epoch]

Wait no I am getting this error:

in on_guild_join
    self.db.execute(f"INSERT INTO guilds VALUES ({str(guild.id)}, {''})")
AttributeError: 'Setup' object has no attribute 'db'

[ruby breach]

would you upload your code?

[slender rose]

thats the pg_catalog and stuff

[autumn epoch]

Ok

[slender rose]

do i just copy paste that?

[ruby breach]

yes

you can also do this:

[autumn epoch]

Ok

[ruby breach]

SELECT tablename
FROM pg_catalog.pg_tables
WHERE schemaname != 'pg_catalog' AND 
    schemaname != 'information_schema';

[slender rose]

im gonna use python to run the sql command tho

[ruby breach]

it will still work

[slender rose]

not directly

ohh

i need to do fetch right?

[ruby breach]

yeah

[autumn epoch]

Here:
https://paste.pythondiscord.com/wumayiyota.py

[golden warren]

Have you got any idea for a calcul for my leveling system on my discord bot?
current_lvl = (1/4) ** x[0]
This does'nt work.

[ruby breach]

two problems. The first is you forgot to await asyncpg.create_pool, and the second is you have do do this to use pools:

async with pool.acquire() as con:
  async with con.transaction():
    con.execute(...)

[autumn epoch]

I tried something different

Could I do that at the very start of the command?

Or could I make a function out of it like this:

async def createpool():
  async with pool.acquire() as con:
    async with con.transaction():
      return con

[ruby breach]

@autumn epoch I have to go, I'll help more later

[autumn epoch]

Ok

[burnt turret]

@autumn epoch did you make the pool as I'd said earlier?

#databases message like this

this is what you're supposed to do

Some Frequently Asked Questions, regarding asyncpg
Official FAQ page: https://magicstack.github.io/asyncpg/current/faq.html
Using asyncpg with discord.py: #databases message
Parametrized Queries: #databases message
Record objects: #databases message

[burnt turret]

not really, asyncpg lets you directly use execute/fetch-x on the pool object

the query needs to be awaited though, which they haven't

[autumn epoch]

Circuit said to use this:
https://paste.pythondiscord.com/wumayiyota.py

[burnt turret]

but....why

what i'd said first will work perfectly well

[autumn epoch]

I couldnt to get it to work in a cog

[burnt turret]

how were you trying to use it in a cog

read the entire message i'd sent, #databases message it tells you how to use it in a cog

you make only one pool for your entire bot and use it across files

when you do it like this (making a new pool for each cog), your database will soon raise some sort of "operation already in execution" or "too many connections" error i think

[autumn epoch]

Could I put it in on_start

[burnt turret]

but why would you lmao

you're making one in the main file already

now it is there for you, ready to be used

you don't have to be doing anything in on_start

moreoever, on_start isn't even a real event

[autumn epoch]

on_ready I meant to say

I am not doing it in the file though

I don't need it there

[burnt turret]

so do it in the main file lmao

even if you don't

it's the neatest way imo

[autumn epoch]

Would I be able to access it from the other ones?

[burnt turret]

just initialize it and keep, you're initializing it as a bot variable to be made accessible across all files

[flint imp]

U will for sure need it in the future so just set it in your main

[burnt turret]

yes, again, read the message

once you set it to a bot variable, it is accessible across all files

[autumn epoch]

Ya ok I thought was just within the file

Also I have 2 databases. One over all the servers, and one for each server.

Or should I have 2 tables?

[burnt turret]

you'd not need two databases for a single bot

at least, if you're modelling the data properly

[burnt turret]

what kind of data are you storing, that it needs to be separated like this?

but yes i think it'd be better than 2 databases

2 tables means you'd only need one connection pool

[autumn epoch]

Well I am making a currency system and I want it to run across all servers. Compared to the local one which will have xp, levels, settings etc

[burnt turret]

right, two tables is the way to go

you could set up the user_id as a foreign key or something as well

[autumn epoch]

Ok

So for the columns of each servers, how would I add information like xp and level inside of the main column? Could I use a dictionary?

[burnt turret]

wdym

[autumn epoch]

First thing first I am getting these error:

Ignoring exception in on_guild_join
Traceback (most recent call last):
  File "/Users/adam/Desktop/Bot/cogs/setup.py", line 11, in on_guild_join
    await self.bot.db.execute(f"INSERT INTO guilds VALUES ({str(guild.id)}, {''})")
AttributeError: 'Setup' object has no attribute 'bot'

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "/Library/Frameworks/Python.framework/Versions/3.8/lib/python3.8/site-packages/discord/client.py", line 343, in _run_event
    await coro(*args, **kwargs)
  File "/Users/adam/Desktop/Bot/cogs/setup.py", line 23, in on_guild_join
    if channel.permissions_for(self):
  File "/Library/Frameworks/Python.framework/Versions/3.8/lib/python3.8/site-packages/discord/abc.py", line 481, in permissions_for
    if self.guild.owner_id == member.id:
AttributeError: 'Setup' object has no attribute 'id'

[burnt turret]

i think a table like

userid  PKEY
guildid
xp
level

and then for your other table you could have

userid FOREIGN KEY, referencing the other userid
balance etc

[burnt turret]

show the code

[autumn epoch]

Ok

Bruh

Python keeps removing it

Its saying this:
Per Rule 6, your invite link has been removed. If you believe this was a mistake, please let staff know!

[burnt turret]

well the error doesnt seem to be related to your database at all

[autumn epoch]

Really?

[burnt turret]

yeah its saying that guild isn't what you think it is i think, cant be sure without seeing the code
no wait

[burnt turret]

just use the pastebin

!paste

[delicate field]

Pasting large amounts of code

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

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

[autumn epoch]

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

[burnt turret]

lmao have you called your instance of commands.Bot as client

i think that's the issue

you'd be doing self.client.db in that case

[autumn epoch]

Ya

Thats what I am doing

[burnt turret]

that's not what the error says

[autumn epoch]

At first I was following a tutorial

[burnt turret]

in the error, you're using self.bot.db

[autumn epoch]

I said self.bot

Ya

[burnt turret]

you call your instance of commands.Bot as client

so bot is an undefined variable

you should be doing self.client.db

[autumn epoch]

Ya

I noticed that after

New and same error:

Traceback (most recent call last):
  File "/Users/adam/Desktop/Bot/cogs/setup.py", line 11, in on_guild_join
    await self.client.db.execute(f"INSERT INTO guilds VALUES ({str(guild.id)}, {''})")
  File "/Library/Frameworks/Python.framework/Versions/3.8/lib/python3.8/site-packages/asyncpg/pool.py", line 518, in execute
    return await con.execute(query, *args, timeout=timeout)
  File "/Library/Frameworks/Python.framework/Versions/3.8/lib/python3.8/site-packages/asyncpg/connection.py", line 295, in execute
    return await self._protocol.query(query, timeout)
  File "asyncpg/protocol/protocol.pyx", line 316, in query
asyncpg.exceptions.PostgresSyntaxError: syntax error at or near ")"

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "/Library/Frameworks/Python.framework/Versions/3.8/lib/python3.8/site-packages/discord/client.py", line 343, in _run_event
    await coro(*args, **kwargs)
  File "/Users/adam/Desktop/Bot/cogs/setup.py", line 23, in on_guild_join
    if channel.permissions_for(self):
  File "/Library/Frameworks/Python.framework/Versions/3.8/lib/python3.8/site-packages/discord/abc.py", line 481, in permissions_for
    if self.guild.owner_id == member.id:
AttributeError: 'Setup' object has no attribute 'id'

Wait accidentally added a parathensie

[burnt turret]

if channel.permissions_for(self): this line is wrong

but that's a dpy error not a database problem

you're not supposed to be passing in self there

[autumn epoch]

Its supposed to be channel

[burnt turret]

permissions_for expects a Member

[autumn epoch]

Oh

Someone told me to do that

What would it be

[burnt turret]

what are you trying to do there

check the bot's own permissions i assume

[autumn epoch]

I am trying to find a channel that I can send the message in

[burnt turret]

yeah, so checking the bot's own permission

[autumn epoch]

Pretty much

[burnt turret]

self in that context refers to the cog class itself - that's why the error says Setup object has no attribute
you want to be passing in the bot user

that, in your case is self.client.user

[autumn epoch]

Ok

So:

if channel.permissions_for(self.client.user)

[burnt turret]

try it and see

[autumn epoch]

Ok

  await self.client.db.execute(f"INSERT INTO guilds VALUES ({str(guild.id)}, {''}")
  File "/Library/Frameworks/Python.framework/Versions/3.8/lib/python3.8/site-packages/asyncpg/pool.py", line 518, in execute
    return await con.execute(query, *args, timeout=timeout)
  File "/Library/Frameworks/Python.framework/Versions/3.8/lib/python3.8/site-packages/asyncpg/connection.py", line 295, in execute
    return await self._protocol.query(query, timeout)
  File "asyncpg/protocol/protocol.pyx", line 316, in query
asyncpg.exceptions.PostgresSyntaxError: syntax error at end of input

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "/Library/Frameworks/Python.framework/Versions/3.8/lib/python3.8/site-packages/discord/client.py", line 343, in _run_event
    await coro(*args, **kwargs)
  File "/Users/adam/Desktop/Bot/cogs/setup.py", line 23, in on_guild_join
    if channel.permissions_for(self.client.user):
  File "/Library/Frameworks/Python.framework/Versions/3.8/lib/python3.8/site-packages/discord/abc.py", line 486, in permissions_for
    roles = member._roles
AttributeError: 'ClientUser' object has no attribute '_roles'

[burnt turret]

oh makes sense

you need to use utils.get or something to get the bot's discord.Member object and then pass it into permissions_for

[autumn epoch]

Ok

So what would it be?

Could it be self.client.bot

[burnt turret]

uh, maybe ask in the dpy channel how to use permissions_for for your own bot's user

[autumn epoch]

Ok

[burnt turret]

kinda busy rn so i can't refer the docs and give you the exact method

[autumn epoch]

Ok thats ok

Im still getting this error though:

Traceback (most recent call last):
  File "/Users/adam/Desktop/Bot/cogs/setup.py", line 11, in on_guild_join
    await self.client.db.execute(f"INSERT INTO guilds VALUES ({str(guild.id)}, 0")
  File "/Library/Frameworks/Python.framework/Versions/3.8/lib/python3.8/site-packages/asyncpg/pool.py", line 518, in execute
    return await con.execute(query, *args, timeout=timeout)
  File "/Library/Frameworks/Python.framework/Versions/3.8/lib/python3.8/site-packages/asyncpg/connection.py", line 295, in execute
    return await self._protocol.query(query, timeout)
  File "asyncpg/protocol/protocol.pyx", line 316, in query
asyncpg.exceptions.PostgresSyntaxError: syntax error at end of input

[burnt turret]

show the query

nvm

await self.client.db.execute(f"INSERT INTO guilds VALUES ({str(guild.id)}, 0") is what you have
await self.client.db.execute("INSERT INTO guilds VALUES ($1)", str(guild.id)) is what you need

[autumn epoch]

What does the ($1) mean?

[burnt turret]

read the second pin in this channel, it explains why the first method (using f-strings) is a bad idea

i think the pinned message explains that as well

[autumn epoch]

Ok

[burnt turret]

Queries with asyncpg
As explained by the pinned message in this channel, you should not be using f-strings (or other methods of string formatting) to execute SQL queries.
In asyncpg, you use the $n as a placeholder in your query, and pass the corresponding parameter to the fetch/execute method.
For example

await connection.fetch("SELECT * FROM tablename WHERE column1 = $1 AND column2 = $2", 1, "anand")

In this query, $1 is the placeholder for 1 while $2 is the placeholder for "anand".
Unlike many other SQL driver modules, you do not have to pass the parameters as a tuple - you can directly pass them in as shown in the example.

Caveats
You CANNOT use this method in a query with IN.

a_list = [1, 2, 3, 4]
await connection.fetch("SELECT * FROM tablename WHERE column1 IN $1", a_list)   # WRONG! THIS WILL ERROR.

a_list = [1, 2, 3, 4]
await connection.fetch("SELECT * FROM tablename WHERE column1 = ANY($1)", a_list)   # RIGHT!

https://magicstack.github.io/asyncpg/current/faq.html#why-do-i-get-postgressyntaxerror-when-using-expression-in-1

[autumn epoch]

Ok

[ruby shore]

Anyone familiar with sqlalchemy? I'm trying to query like this

select date, installs from table where plugin = some_plugin 

And the table has more than just date and installs in it. I got this so far.
Data.query.filter_by(plugin=req['plugin']).all(), but this gives all columns in the table.

[torn sphinx]

should I put my create table statement in bot on_ready statement?

[burnt turret]

no

you do the create table only once

the bot's on_ready can fire multiple times, you don't want to be creating it every time

[torn sphinx]

ok

where should I do it?

[burnt turret]

just create the table once from the database shell or something

what database are you using?

[ruby breach]

@autumn epoch Look at this repo, see if it helps at all: https://github.com/CircuitsBots/Asyncpg-DiscordPy

I created a very basic leveling bot that uses asyncpg with pool, and it works fine for me.

[autumn epoch]

anand helped me and I got it to work

Thx though

I can get references from it

[ruby breach]

👍​

[autumn epoch]

I learned how to use $ thing because of it

[torn sphinx]

ey guys my foreign key broke or idk

its there

but it wont do the refer thing

[proven arrow]

Can you be more precise? What do you mean it wont refer?

[torn sphinx]

to test it if it works

i updated the main column, which the foreign row refers to, and in the other column which the foreign key is applied to wont show it

do i have to config it more like to show updates or deletes?

[proven arrow]

The foreign key is just a pointer. And the data you are referring will only be in a single place.

[torn sphinx]

ik its pointer

it should show iut

it*

[proven arrow]

Can you show an example row, or how you made the foreign ke

[torn sphinx]

aiosqite

sure

[burnt turret]

sqlite right - i dont think it has a shell right? just make a script and run the query once i guess

[torn sphinx]

i only made a new column using " CONSTRAINT currency_user_id_fkey FOREIGN KEY (user_id) REFERENCES public.users (user_id); "

well that was at the end to convert it

to a foreign key

[proven arrow]

Yeah thats fine

[gaunt garden]

in SQLite can you use a WHERE statement with more than two? like if i were to do

c.execute("SELECT * FROM economy_profiles WHERE guild_id = ? and user_id = ? and some_other = ?", (guild, user, some_other,)) 

[torn sphinx]

foreign key is there, i checked using the GUI of pg

[proven arrow]

yeah thats fine

[burnt turret]

yeah that looks valid

[gaunt garden]

hmm ok it didn't work for me....

[burnt turret]

uh you've missed a closing parentheses i think though

[gaunt garden]

yeah i did

[proven arrow]

I honestly have no idea what you are talking about. It makes no sense to me what you are saying

Not only that message but also what you said before

[torn sphinx]

ok ok

lemme explain myself better

how can i use the foreign key now

thats better lol

[proven arrow]

Use the foreign key to do what? Select data?

[torn sphinx]

Ye

[proven arrow]

For that you use a JOIN

[torn sphinx]

hm?

whats that

i already joined the two tables with a foreign key

didnt I?

[proven arrow]

There are different types of joins but the one youll need is inner join probably or thats the most common at least. JOIN is basically joining tables

[proven arrow]

No you only created a reference, but JOIN is for when you want join rows from one table to another

[torn sphinx]

this is "selects records that have matching values in both tables"

i want it to copy all the data from the other column when it updates

is there a way to do that?

O union?

no its not

hmm

[gaunt garden]

i figured it out xD im stupid. forgot to actually add to the quantity, instead the database was just updating the column with the default number instead of adding to it. xD

which make me think i wrote the query wrong xD

[proven arrow]

For example,

CREATE TABLE users (
   id INT PRIMARY KEY,
   name VARCHAR(255) NOT NULL
);

CREATE TABLE orders (
   id INT PRIMARY KEY,
   user_id INT,
   FOREIGN KEY (user_id) REFERENCES users(id)
);


SELECT * FROM orders 
JOIN users on orders.user_id = users.id

The above example is joining to get all orders, and joins on the users table where the primary id of the users table matches the foreign key.
@torn sphinx

Its not postgres syntax but the select is the same

[torn sphinx]

only if it matches right?

[proven arrow]

Yes, so it joins wherever the user_id (the foreign key) matches the id on the users table.

[torn sphinx]

will that work like

many tables with user_id

all of them update

im explaining myself horribly again

lemme rephrase it

wait im gonna better draw what i want to do

[proven arrow]

What do you mean updates? Its just a pointer to another row. Whatever happens in the other table is not the child tables concern unless the parent row is deleted.

[torn sphinx]

there

the main one is where the others lead to

if the main one updates

the others also update

thats what i want to do

thats the relation i want to make

idk if foreign keys can do that tho

because you said they were pointersr

[proven arrow]

Yeah so the first table is the parent. Rest are childs. The childs only point to the parent (i.e. the first).

[torn sphinx]

but you can still access the other tables with the id right?

[proven arrow]

Yes

[torn sphinx]

Ok ok

so it works like that?

coool

which type of join does that?

[proven arrow]

Generally you will only use inner join. But this explains the different joins well: https://blog.codinghorror.com/a-visual-explanation-of-sql-joins/

I need to get back to work

[torn sphinx]

sure np thanks for your time

[burnt turret]

where does NATURAL JOIN fall?

[torn sphinx]

i have a problem

for this lol

[torn sphinx]

ok

[fair thistle]

anyone using MongoEngine?

[proven arrow]

This joins based on the what common columns (i.e. columns with the same name) the tables being joined have. Essentially removing duplicate columns from the result set.

Also its rarely used

[torn sphinx]

what is the syntax error in this statement?

async def create_table():
    await db.execute(CREATE TABLE IF NOT EXISTS config(guild_id NUMERIC, prefix CHAR, nitro CHAR, hyperlink CHAR, antispam CHAR,))

[burnt turret]

You need to wrap the query in quotes

[torn sphinx]

oh :/

[burnt turret]

Oh right

[torn sphinx]

it says db is not defined

[burnt turret]

Then define it

[torn sphinx]

what is this error?

Traceback (most recent call last):
  File "/Users/vasan/discord-bot/bot.py", line 381, in <module>
    asyncio.run(create_table())
  File "/usr/local/Cellar/python@3.9/3.9.1_6/Frameworks/Python.framework/Versions/3.9/lib/python3.9/asyncio/runners.py", line 44, in run
    return loop.run_until_complete(main)
  File "/usr/local/Cellar/python@3.9/3.9.1_6/Frameworks/Python.framework/Versions/3.9/lib/python3.9/asyncio/base_events.py", line 642, in run_until_complete
    return future.result()
  File "/Users/vasan/discord-bot/bot.py", line 377, in create_table
    await db.execute('CREATE TABLE IF NOT EXISTS config(guild_id NUMERIC, prefix CHAR, nitro CHAR, hyperlink CHAR, antispam CHAR,)')
  File "/usr/local/lib/python3.9/site-packages/aiosqlite/core.py", line 175, in execute
    cursor = await self._execute(self._conn.execute, sql, parameters)
  File "/usr/local/lib/python3.9/site-packages/aiosqlite/core.py", line 122, in _execute
    return await future
  File "/usr/local/lib/python3.9/site-packages/aiosqlite/core.py", line 98, in run
    result = function()
sqlite3.OperationalError: near ")": syntax error

@burnt turret

[burnt turret]

Show the code

[torn sphinx]

File "/Users/vasan/discord-bot/bot.py", line 381, in <module>
    asyncio.run(create_table())

[burnt turret]

that's not the query but nvm I can see it in the error

[torn sphinx]

async def create_table():
    db = await aiosqlite.connect("/Users/vasan/discord-bot/config.db")
    await db.execute('CREATE TABLE IF NOT EXISTS config(guild_id NUMERIC, prefix CHAR, nitro CHAR, hyperlink CHAR, antispam CHAR,)')

[burnt turret]

I'm on mobile so I can't understand it properly, I'll reply once I turn my laptop on in case no one is else has answered

[proven arrow]

remove the comma before the closing bracket

[torn sphinx]

yay it works!

how come i can't view the table?

[delicate field]

Hey @torn sphinx!

Uh-oh! It looks like your message got zapped by our spam filter. We currently don't allow .txt attachments, so here are some tips to help you travel safely:

• If you attempted to send a message longer than 2000 characters, try shortening your message to fit within the character limit or use a pasting service (see below)

• If you tried to show someone your code, you can use codeblocks
(run !code-blocks in #bot-commands for more information) or use a pasting service like:

https://paste.pythondiscord.com

[torn sphinx]

@proven arrow how do I add rows to the table now?

[proven arrow]

The same way i already showed you here 🙂

#databases message

[spice granite]

I'm working on an enterprise codebase that operates on enormous amounts of data. We have 3 database connections and it's not uncommon for the code to be interrupted by the user by closing the window or ctrl+c. How worried should I be about those DB connections not being closed properly due to an interrupt?

[proven arrow]

If your concerned about getting data to the db then use transactions. Leaving connections open like that generally is not so much of a concern because you can have them timeout.

[spice granite]

Okay, I'm all set then. I've also been wondering about threading my db queries (I have 3 queries running one after the other and it takes a while). I'm very new to this so I'm wondering how possible this would be.

[proven arrow]

Threading is not guaranteed to make it faster, unless you know for sure what your bottleneck is.

[spice granite]

The bottleneck is that the queries are quite large and they take longer to return when run on large tables of data. When we have 3 of those queries, each being run on a different database to retrieve the data, the wait is a lot longer than if it was just being run on one of the databases. Not sure if that makes sense @proven arrow

[torn sphinx]

Yes, you got multiple DB's on one Server and only so much CPU and Memory to do the queries - what queries are run? Is there a way to only ensure 1 or 2 are running at the same time by using locks?

Could you cache the result and then run them on a CRONjob or are they needed to respond to user input?

[spice granite]

Each DB is on a different server. The code we're working on is a tool that compares each databases contents to ensure correct transfer of data. That is why there are multiple databases being queried because we need to fetch the data from each and compare them to eachother.

Right now we're using multiprocessing to split the job and this has improved the performance. The bottleneck we are facing now is the queries taking a long time to return.

Database 1 data is rarely changed and I was thinking about using feather files to store the data instead of having to query the database each time.

Because of the fact that we have 3 queries running sequentially we are seeing long runtimes. I was wondering if it would even be possible to somehow run the queries in parallel.

[torn sphinx]

compares each databases contents to ensure correct transfer of data how is this data copied into these servers - via replication or log shipping or direct inserts? Cause wouldn't log shipping give you guarantees on import that they up to date? Like are you comparing row by row?

[spice granite]

Honestly not sure. This is a project with 40+ people. I'm not working on the data transfer side, I'm tasked with optimizing the code that was handed to me.

[torn sphinx]

So I would read the query and see what it does? How does it compare - by hashing the tables?

[cloud hornet]

@still bane

[torn sphinx]

is sql case sensitive?

no

ok

select * from table

poggers

but why do people type it in all caps then?

standard / convention / it looks weird in lowercase

oh

do I need quotes when inserting a char value?

yes, I think like 'a'

ok

quotes in SQL are generally '

how can I view the sqlite database table?

like in a spreadhsset format?

[proven arrow]

Use a database browser

https://sqlitebrowser.org/

[torn sphinx]

always make sure to commit you changes!

how do I change a specfic value of a column?

autocommit=true

[torn sphinx]

^

what does this table look like? do you want to change the datatype or change a column entry of a row?

cause sqlite is row based, not column based.

[torn sphinx]

ok, lemme show you an example:

UPDATE SET nitro = "disabled" WHERE guild_id = "123"

ohhhh

[modest pulsar]

hi, how can I look if something is already in the table? (sorry i'm just beginning to learn sql)

[torn sphinx]

define "something"

a row?

a value?

a column?

[modest pulsar]

like, if i want to know if x user already has an "account"

[torn sphinx]

table?

[modest pulsar]

no, i'm doing an rpg to learn sql, so i want to know if the user has already begun the rpg, and if i have to create a nex line for him

[torn sphinx]

Well, what does the schema look like @modest pulsar ?

[torn sphinx]

no like, show the tale

*table

We can't read minds.

^

[modest pulsar]

sorry i have to go 😓, can you explain me pls? i'll see tomorrow...

[torn sphinx]

how do I select stuff from the DB again?

nvm

SELECT columnX,columnY FROM table WHERE condition = z

@torn sphinx after I select, how do I get the calue and store in the variable?

You going to go find a Python tutorial and use that to show you how to do that 😛

cause I'm tired 😪

ok

kek

[torn sphinx]

hey guys so ive been trying to do this parent child relationship between tables with foreign keys, have been looking at the possible joins that may do the job but there is no join that actually works like this

so is there something else to use?

aside from joins

what's this erro?

    result = function()
sqlite3.OperationalError: near "set": syntax error

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/usr/local/lib/python3.9/site-packages/discord/client.py", line 343, in _run_event
    await coro(*args, **kwargs)
  File "/Users/vasan/discord-bot/bot.py", line 296, in on_command_error
    raise error
  File "/usr/local/lib/python3.9/site-packages/discord/ext/commands/bot.py", line 902, in invoke
    await ctx.command.invoke(ctx)
  File "/usr/local/lib/python3.9/site-packages/discord/ext/commands/core.py", line 864, in invoke
    await injected(*ctx.args, **ctx.kwargs)
  File "/usr/local/lib/python3.9/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: near "set": syntax error

@bot.command(description='Sets the prefix for Sir KomodoBot to use in your server.')
async def setprefix(ctx, prefix):
    db = await aiosqlite.connect('/Users/vasan/discord-bot/config.db')
    await db.execute(f'update set config prefix = {prefix} where guild_id = {ctx.guild.id}')

you need to specify the table

i think

between set and prefix

sorry

between

update and set

@torn sphinx now I get this:

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

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/usr/local/lib/python3.9/site-packages/discord/client.py", line 343, in _run_event
    await coro(*args, **kwargs)
  File "/Users/vasan/discord-bot/bot.py", line 296, in on_command_error
    raise error
  File "/usr/local/lib/python3.9/site-packages/discord/ext/commands/bot.py", line 902, in invoke
    await ctx.command.invoke(ctx)
  File "/usr/local/lib/python3.9/site-packages/discord/ext/commands/core.py", line 864, in invoke
    await injected(*ctx.args, **ctx.kwargs)
  File "/usr/local/lib/python3.9/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 0 supplied.

bindings?

hmm

maybe its something related to aiosqlite

i use asyncpg but they should be similar

btw

but still

i think its better to use

the " ? " sign

instead of f strings

ok

i will switch later :D

sure

o i found your problem

https://stackoverflow.com/questions/16856647/sqlite3-programmingerror-incorrect-number-of-bindings-supplied-the-current-sta

just search it lol

it appears top

@torn sphinx

[torn sphinx]

anyone knows about this?

now I have a syntax error near where after adding the comma

you should read more in that post

ok, got it working, but now the new prefix not working

[proven arrow]

No join that works like what?

[torn sphinx]

remember the diagram weird thing made in paint i showed you?

that works like that

[proven arrow]

Well what does that diagram have to do with joins. That was just implementing relationships.

Joins us when querying

What are you wanting to query

[torn sphinx]

if parent table (user_id) gets upated, all the other child tables (user_id) gets updated too

thats it

nothing else

should i just manually update every single one of them

?

where do I set auto commit =. true?

i heard i should do it

be where do I set it?

[proven arrow]

Is the user id the foreign key?

[torn sphinx]

well, every single child table would have a user id which would be the foreign key that points towards the parent table with the main user_id

and also primary key of that main table

because every user will have lots of stuff in their data and i dont want to store it in a big table

like levels , currency, rank, etc

[proven arrow]

But why does your primary key need to change?

When does user id ever change?

Does discord ever change this?

[torn sphinx]

not change

but add

a new user id

[proven arrow]

So then add it

You need to insert the data at some point to make the relationship, but it’s a one time thing

[torn sphinx]

i already did it

oh my bad

update is not the same as insert

i just want to insert data in every table at the same time

so if i insert it into the main user id it would also get inserted in every child user id

[proven arrow]

Your misunderstanding this.
User comes first. It’s the parent, so how can your child records exist without a parent?
Therefore you always need to insert a user record first then insert the others

[torn sphinx]

that

lol

so insert it in the parent user id

and then in all the child user ids

[proven arrow]

1. Insert into Users only once
2. Insert into warnings

A warning can’t exist without a user so the user record must be there first.

[torn sphinx]

whats warnings?

[proven arrow]

Just an example

[torn sphinx]

o ok

ye

thats what i want to do

but the child user ids will work as a primary key even if they are not

ok ok

so how do i do that?

[proven arrow]

I just told you?

[torn sphinx]

so just manually?

there is no relation?

to be made or anything?

o cool

i tho there was a better way of doing it other than just manually

but i guess manually it goes

[proven arrow]

Of course it has to be done manually. It can’t guess what data you want to insert.

[torn sphinx]

lol

well thanks

again

how to delete a row @proven arrow

?

where column = some value?

[proven arrow]

Yeah

https://www.w3schools.com/sql/sql_delete.asp

[torn sphinx]

does sql have trouble handling enormous tables?

[proven arrow]

No, databases are made to store large amounts of data

It’ll only have trouble if you design it poorly

Or do some weird things in your queries

[torn sphinx]

not data

columns

[proven arrow]

Same applies

[torn sphinx]

i should have asked this question earlier

then there would be no need to do subtables since a big table can handle all that

omg im so dumb

[proven arrow]

The sun tables is still better. I’m pretty sure I’ve already said this to you before when I said you should read into normalisation.

[torn sphinx]

did you say that? maybe i didnt see that

[proven arrow]

Or have a read of the answer to this question #databases message

[torn sphinx]

ok ok

make one change make the change reflect in every place you want to change instead of changing all of them hmm

I think im understanding everything much better now

i dont know how lol

ig that helped thanks

[solemn root]

This doesn't update an existing row nor insert a row if one doesn't exist nor print the rows to console even though it should do all of those things. What am I missing/doing wrong?:```
async def work(ctx):
#random number Gen
cash_to_add = math.floor(random.random()*100)
#Retrieve discord author message
author = ctx.message.author
#Connect to database
connection = sqlite3.connect('tinker.db')
#create cursor
crsr = connection.cursor()

#if the table is not empty update it
if("""SELECT FROM tinker WHERE user_token = author.id;"""):
crsr.execute("""UPDATE tinker SET user_cash = user_cash + ? WHERE user_token = ?;""", (cash_to_add, author.id))
#else insert the information
else:
crsr.execute("""INSERT INTO tinker (user_cash, user_token) VALUES (?, ?);""", (cash_to_add, author.id))

#crsr.execute("""INSERT INTO tinker (user_cash, user_token) VALUES (?, ?) ON CONFLICT (user_token) DO UPDATE SET user_cash = user_cash + ?;""", (cash_to_add, author.id, cash_to_add))

print("------------------")
#crsr.execute("""UPDATE tinker SET user_cash = user_cash + ? WHERE user_token = ?;""", #(cash_to_add, author.id))

connection.commit()

crsr.execute("""SELECT * FROM tinker""")
rows = crsr.fetchall()
print("start")
for row in rows:
print(f"{row[0]} {row[1]} {row[2]}")
print("middle")
#print(f"{row[0]} {row[1]} {row[2]}")
print("exit")
connection.close()
await ctx.send("Your Wallet: $" + str(cash_to_add) + "\n")

[stable mural]

[torn sphinx]

Creating a database table which the primary key will be message_id bigint. message_id is a discord message id, which obviously will vary in length (over a long course of time...), what do I do when I can't specify a certain length on the table column?

[burnt turret]

Message IDs don't vary in length?

[torn sphinx]

They will when there is enough messages lol

806739752514486342
That's the message id of your message.

[burnt turret]

I know what message IDs are

They have never changed in length afaik

[torn sphinx]

Once we get to 89999999999999999 we'll jump up an integer length.

[burnt turret]

It's like 10 digits

There are plenty of permutations available

[torn sphinx]

alright ig

Then next question

Well actually doesn't matter nvm

[torn sphinx]

arent these two supposed to be separate elements instead of a single element? ??? ? ?

[<Record user_id=367335461737201664 money=Decimal('0')>]

[burnt turret]

Wdym

You mean the user_id and money?

#databases message read the links in this message

[burnt turret]

Also, you're using fetch instead of fetchrow, so it is expecting many rows (each row is represented by a record object) - and that's why it's in a list
For cases you want just one row like in this example use fetchrow, it'll give you just the single record object

Lmao turns out I'd told this to you before itself huh

[torn sphinx]

omg

yes

i forgot there are many types of fetch

thanks anand

btw

how are the parameters in fetchrow?

i dont seem to find them

in google

this is so strange

i also forgot ide can do that

nvm

thanks

[burnt turret]

it's on the asyncpg docs

The parameters are the same as in fetch

query, *parameters

[trim wave]

how do i get a database

[sinful condor]

I dont really know how I would do this but could someone please explain. In a loop with a mongodb database how would I get all of the documents in there with a for loop.

I am trying to make it so it unmutes it with time from a database so if my bot stops it still unmutes them

[fallen vault]

Is heroku a good platform for hosting an online database? Finally got my bot hosted online and now I need to move my database from my pc.

[burnt turret]

can you explain this better?

it sounds like you need a scheduler, using something like https://pypi.org/project/aioscheduler/ is what i use (or you can set one up yourself, but this is easier i guess)

and when your bot is coming online reschedule all the tasks from the database

[torn sphinx]

Hi everyone, it is my first time storing an image into a psql table using python and in the table i see
[binary data]
is this how it is supposed to be stored? I just want to know if I am on the right track. Thanks!

[torn sphinx]

normally people store image in filesystem

in the database only store path

@torn sphinx thanks for your help, I did not know that. I will store the image in the filesystem 😀

main reason is because image is big, so affects speed performance

[torn sphinx]

ah ok, that makes sense. Thank you

[proven arrow]

Are you referring to the one in Postgres?

Main difference is how it’s stored. Jsonb is stored as a binary representation whereas json is just standard plain text format

[torn sphinx]

Hello
I just started this thing and I really don't know what to type to the database.

async def dbinit():
    global connection
    DB_SETTINGS = {
        'host': 'localhost',
        'port': "can I even share this?",
        'database': 'don\'t know',
        'user': 'postgres',
        'password': "not saying"
    }
    connection = await asyncpg.create_pool(**DB_SETTINGS)
    client.dbconnection = connection```
I've downloaded PostgreSQL 1.13.1, not on path tho cuz it doesn't work for me, not even ``pip``, I always gotta type ``py -m pip``.
When I downloaded it, I think it kinda automatically made a database, I gave it a password and a port, that I remember.
(It's in discord.py, don't mind it, they sent me here)

[proven arrow]

It’s only useful if you are doing processing on it, as it’ll be faster

[proven arrow]

database is the name of your database. But you first need to create a database with postgres, in order to use it

[torn sphinx]

where can I do this? I've seen people on google do this with cmd, but I couldn't do it. I've set the postgres\bin on path, but then

[proven arrow]

Have you tried searching your applications for the postgres shell?

If you installed it, then it should come up

[torn sphinx]

sorry I don't know what that means

could you please explain it? 🥺

[proven arrow]

It means you search for it on your pc

[torn sphinx]

it's there

I can create it here?

[proven arrow]

Yeah that’s the command line tool to interact with postgres

[torn sphinx]

I've got a database now with the password and port?

[proven arrow]

It’s asking for a server, why are you entering SQL commands into it?

Do you see what your typing? 😐

[torn sphinx]

uh

did I ruin something?

[proven arrow]

If it’s your first time logging in then you can leave all those fields blank. It’ll use the default Postgres details. You just need to enter the password at the end.

[torn sphinx]

but I'll log in multiple times

so I would let it there, if it's correct

[proven arrow]

You can read about it here https://www.postgresql.org/docs/13/datatype-json.html

[sinful condor]

So I am making it so when a mute command is used in my bot it puts how much time is left for that mute

I am trying to make a loop that every 15 seconds or so it will subtract 15 seconds from all times

[burnt turret]

you shouldnt be storing how much time is left in the database

you should be storing the time when the mute ends

[sinful condor]

Oh ok

[burnt turret]

https://gist.github.com/AbstractUmbra/eb360904f3c0ff6ebebce2da7bd1c661 this explains the process, it uses postgres but the idea is the same

you'd just have to change the query to fit what you need

[sinful condor]

So then just check it to see if the current time is later than the end time

[burnt turret]

read the link it's a good explanation

or you could use the scheduler i'd sent earlier

[sinful condor]

Ok I will try to add some of this stuff when I can

[quaint stream]

Hi can anyone help me with building my web server at home on wifi router(like kalle hallden's)

[royal jackal]

https://twitter.com/Br3Sc/status/1357328840431910913?s=20

Pierre de Fermat (@Br3Sc)

Free resources to learn #MachineLearning and #DataScience for free🆓:

I've been gathering resources from these wonderful people:
@svpino @PrasoonPratham

#100DaysOfCode #Python #codingtips #CodeNewbie #pythonprogramming #Python3 #Algorithm #data #pythonlearning #code #Tips #ML

Twitter · Feb 4, 2021, 2:03 PM

Sup guys, pls if y'all don't mind take a look in my tweet about learn data science for free

[torn sphinx]

Sure but this is databases ? ? ? ?

[modest pulsar]

hi, how can I look if a user has already a table in my db?

[burnt turret]

how is your data structured?

generally i'd say making a table for each user is not a good idea

#databases message this message explains why table-per-user is a bad idea

[royal jackal]

wrong chat sorry

[torn sphinx]

hello so this is a discord.py related but isnt at the same time

so i am making a command like

>deleteinf @user 6

it will delete

that user's

6th row

that was added

to him

[modest pulsar]

ok, so can you answer to my question of yesterday pls nobody answered me... :/

[modest pulsar]

here and after

[burnt turret]

it really depends on what your table looks like

i mean, what columns you have

[torn sphinx]

they are

await cursor.execute('''CREATE TABLE Modlogs
                                    (Mod_name, reasonn, user_id,time , command)''')

you can see the name e

i use asqlite its not much of a difference

[raven trail]

Hey using SQL Alchemy for dpy how would I be best creating user specific, channel specific and server specific attributes/dbs? Any best practices for that sort of thing?

[burnt turret]

wait i was talking to someone else lmao

what's your issue?

[torn sphinx]

😔

[torn sphinx]

i am making a command called >deleteinf @user 6 which will delete that user's 6th row that was added to him

[burnt turret]

do you know how to SELECT data?

[modest pulsar]

i put a photo some messages after what i sent you

[burnt turret]

yeah i saw that, one sec

you'd use a SELECT query, with a WHERE condition for the specific user

[modest pulsar]

FROM table SELECT column WHERE line ?

[burnt turret]

no

SELECT <columns> FROM table WHERE condition

[modest pulsar]

yes

oh ok

[burnt turret]

check out the pinned message in this channel, there's a good tutorial site on there you can use to learn all this

highly recommend it

[modest pulsar]

ok thx

[modest pulsar]

but can I do SELECT user_id = {ctx.author.id} FROM rpg ?

[burnt turret]

no

that would be SELECT user_id FROM rpg WHERE user_id = <ctx.author.id>

you shouldnt be using f-strings there

read the second pinned message in this channel

[modest pulsar]

why?

ok

[burnt turret]

long story short it opens you up to SQL injections

the message explains it really well

[modest pulsar]

oh yes

so i have to put ?

[burnt turret]

depends on what database driver you're using

[modest pulsar]

sqlite

[burnt turret]

you're using aiosqlite? i think it is ? for it

for a discord bot use aiosqlite

[modest pulsar]

why?

[burnt turret]

sqlite is blocking

basically, all your database calls happen synchronously while your bot is running asynchronously
so every time a database query happens, your bot freezes for that time

and will wait for the database query to finish execution

[modest pulsar]

ooh okk

[burnt turret]

https://discordpy.readthedocs.io/en/latest/faq.html#what-does-blocking-mean

[torn sphinx]

yo guys

how should i get started with databases

[modest pulsar]

but, do i have to add a module?

for aiosqlite?

[burnt turret]

you have to import the aiosqlite module instead yes

[modest pulsar]

ok thx

and is it installed by default?

[burnt turret]

i don't think so

you'll have to pip install it

[modest pulsar]

ok thx

so, i have to import sqlite3 AND aiosqlite?

[burnt turret]

nah just aiosqlite

[modest pulsar]

and does it change something?

i mean in my code?

and the name of the file?

[burnt turret]

you'll be adding awaits before almost all queries

the name of the file wont change

sqlite3 and aiosqlite are just modules used for accessing the same underlying database (sqlite)

just, aiosqlite does it asynchronously

[modest pulsar]

ok thxx

i'll surely need more help sorry

[burnt turret]

that's fine, just ask here and someone who can will answer

[modest pulsar]

yes, thx!

[raven trail]

Is postgresql with sql Alchemy blocking?

[burnt turret]

i think SQLAlchemy 2.0 supports asyncio

i've been recommended to use orm as an asynchronous ORM though

https://github.com/encode/orm

haven't used it yet though

[autumn epoch]

Oops

Would it be possible to add new parameters to a certain column:
So I am trying to start with this
guildid bigint
Then add turn just that one column to this:

guildid bigint
_1 bigint
_5 bigint

Then on and on.

[burnt turret]

i don't understand, can you explain that again

[raven trail]

What are the advanrages of orm vs sql Alchemy? I can research but curious if you know any distinctions as someone recommended it to you, do you remember who recommended it btw?

[burnt turret]

you can add new constraints to an existing column with the ALTER TABLE command

[autumn epoch]

But can I do that to 1 column?

[burnt turret]

it was CF8, you might've seen them around here and in the #async-and-concurrency channel

I don't remember if I asked them about advantages of orm relative to SQLAlchemy, but afaik sqlalchemy 2.0 is still in beta?

[autumn epoch]

Because I am trying to do a settings and I am using 1 parameter for each setting created. Or is there a more efficient way to do it?

[burnt turret]

ORM has been built async too, but I don't really know enough to speak about this

[burnt turret]

yeah ALTER TABLE ... ALTER COLUMN ... ADD CONSTRAINT is the syntax in postgresql i think

[autumn epoch]

You can do that!!!

That helps alot

[burnt turret]

im not really sure if that syntax is right though

https://www.postgresql.org/docs/9.1/sql-altertable.html refer the docs to make sure

but you can certainly add new constraints to single columns later on

[burnt turret]

the advantages that were listed to me was in comparison to other existing async ORMs out there - CF8 said that orm uses asyncpg out of the box while some other async orms out there use the slower aiopg @raven trail

[raven trail]

Okay :) I will make a note and try it out!

[autumn epoch]

Nope dosent work ):

[burnt turret]

what constraint are you trying to add?

[velvet fable]

How come when I go to call DB.execute, I get an error of name 'self' is not defined
My code is

class DB:
    @classmethod
    async def DB_start(cls):
        self = cls()
        self.db = await aiosqlite.connect('Reactions.sqlite')
        return self

    async def execute(statement):
        async with self.db.cursor() as cursor:
              await cursor.execute(statement)

[burnt turret]

you didnt pass self as a parameter to your execute method

hence making it an undefined variable

[velvet fable]

ah, I see now, thank you

@burnt turret, sorry for the ping, but where would I put the self arg?

Ignoring exception in on_message
Traceback (most recent call last):
  File "C:\Users\AppData\Local\Programs\Python\Python38-32\lib\site-packages\discord\client.py", line 333, in _run_event
    await coro(*args, **kwargs)
  File "E:\Code\()Discord-Bot\Acer\cogs\Reaction.py", line 89, in on_message
    await DB.execute(statement=f"SELECT channel_id FROM main WHERE channel_id = {message.channel.id}")
TypeError: execute() missing 1 required positional argument: 'self'

[burnt turret]

all methods you define in a class need self as their first parameter.
in your case, that's

async def execute(self, statement):

this is not the case for

1. classmethods - cls is the first parameter
2. staticmethods - doesn't really have a "special" first parameter

[velvet fable]

so, would i remove statement=?

[burnt turret]

no no you're supposed to edit this in the class definition

[burnt turret]

in here, where you define execute

[burnt turret]

define it the way it is done here - adding self as the first parameter

[velvet fable]

so, add self = cls() above the async with?

[burnt turret]

no, read what i said here (i've also written what you're supposed to do)

the example is literally what you need to put in your code

[velvet fable]

I did that?

    async def execute(self, statement):
        async with self.db.cursor() as cursor:
              await cursor.execute(statement)

[burnt turret]

yes that's what you need

[velvet fable]

but im still getting the error

[burnt turret]

what's the error now

[velvet fable]

Ignoring exception in on_message
Traceback (most recent call last):
  File "C:\Users\AppData\Local\Programs\Python\Python38-32\lib\site-packages\discord\client.py", line 333, in _run_event
    await coro(*args, **kwargs)
  File "E:\Code\()Discord-Bot\Acer\cogs\Reaction.py", line 88, in on_message
    await DB.execute(statement=f"SELECT channel_id FROM main WHERE channel_id = {message.channel.id}")
TypeError: execute() missing 1 required positional argument: 'self'
``` this

[burnt turret]

show your code

you need to be making an instance of DB first

[velvet fable]

my class,

class DB:
    @classmethod
    async def DB_start(cls):
        self = cls()
        self.db = await aiosqlite.connect('Reactions.sqlite')
        return self

    async def execute(self, statement):
        async with self.db.cursor() as cursor:
              await cursor.execute(statement)

    async def DB_close(self):
        await self.db.commit()
        await self.db.close()
```my `__init__` for the cog,
```py
class Reaction(commands.Cog):
    def __init__(self, client):
        self.client = client
        self.client.loop.create_task(self.async_task())

    async def async_task(self):
        self.db = await DB.DB_start()

My code giving the error,

    @commands.Cog.listener()
    async def on_message(self, message):

        await self.client.wait_until_ready()
        
        await DB.execute(statement=f"SELECT channel_id FROM main WHERE channel_id = {message.channel.id}")
        result = await cursor.fetchone()
        print("Connected to DB")
        if not Result:
            print("Result is none")
            return
        elif result is not None:
            if result == message.channel.id:
                await DB.execute(f"SELECT channel_id FROM main WHERE channel_id = {message.channel.id}")
                results = await cursor.fetchall()
                print("Made is this far")
                print(results)
                print(result)

[burnt turret]

yeah you need to be making an instance of DB to use that execute function

why are you making a class like this in the first place though

[velvet fable]

I'm using this database only for this cog, and that's how I was suggested to make it

[burnt turret]

huh

well personally I don't see the point of that entire thing, and it doesn't seem like you've understood the OOP concepts used there (check out corey schafer's video on OOP)
basically you can see that in your init, you call async_task, which makes an instance of DB and assigns it to self.db

now in your queries, you want to be using self.db.execute(

and not DB.execute(

[velvet fable]

could you link the video while i add that to my code?

[burnt turret]

also, read the second pin in this channel - it tells you why using f-strings for SQL queries is a bad idea

[burnt turret]

sure, one second
https://www.youtube.com/watch?v=ZDa-Z5JzLYM

[velvet fable]

should the DB in self.DB.execute() be capital or lowercase?

[burnt turret]

you've called the variable self.db, so

lowercase

[burnt turret]

this is a series of videos explaining object oriented programming in python, i'd recommend you watch them and then understand what you've done with that DB class you've made there

[velvet fable]

so, i have result = await cursor.fetchone(), should I make an async def for it?

[burnt turret]

where is this

[velvet fable]

the line right under the first await db.execute in on_message

[burnt turret]

what do you mean by make an async def for it

[velvet fable]

like in the DB class, I have an async def for connection, closing it, and executing, but not setting the cursor outside of execute.
Should I do something like

async def cursor(self):
    self.cursor = self.db.cursor()
```?

[burnt turret]

no

[burnt turret]

and this is exactly why i said that that class is pointless - it's just unnecessary code really, calling the corresponding aiosqlite method underneath it

[velvet fable]

so, what should i do so it defines cursor then?

[burnt turret]

oh man that class is a mess

i think the easiest method right now would be to have the execute method return the data that it fetched

and then whenever you use the execute method, assign it's result to a variable

[velvet fable]

I was told to set it up that way, but for that, would i do,

    async def execute(self, statement, result):
        async with self.db.cursor() as cursor:
              await cursor.execute(statement)
              result = cursor.fetchone()
              return result
```?

[burnt turret]

close, you don't need the result in the parameters

(in the parameters, meaning inside the parentheses)

[burnt turret]

and uh, whoever told you to do that gave you bad advice imo

or maybe you've misunderstood what they wanted you to do

[burnt turret]

also, check aiosqlite's docs and see if cursor.fetchone() needs to be awaited or not (i'm not sure about it)

[velvet fable]

I mean, I was just using sqlite at first, i tried to execute 2 different things for the on_message, and I was told thats bad to use aiosqlite and thats how they told me to set it up

[burnt turret]

they're right about using aiosqlite

but that class made there is completely pointless

this just adds extra effort which achieves nothing extra

the way i'd recommend is to use the aiosqlite.connect method once, in your main bot file (the one where you define client = commands.Bot( )

and then assign the connection to a bot variable