databases

[fading breach]

discord.py is async

[midnight cloak]

Yes but I don't know how to use motor

[fading breach]

its pretty much the same

[midnight cloak]

@fading breach it's not fix in pymongo?

Anyone there

[gilded sleet]

what is the code

cant help without showing code

[burnt turret]

That error doesn't seem related to mongo

Can't say without seeing code

[solemn root]

@restive pilot Figured it out! 😁 Was missing a DISTINCT clause

[torn sphinx]

is it possible to count how many columns exist for a row with sqlie?

[wintry wraith]

hi, i got a project in school were my group have created a flask server and using sql-Alchemy and a sqlite db. We are doing some testing and using pytest and have created a test client. The server is basic and can create messages, get messages, mark them as read etc. In our testing our post requests works and we got our expected 200 back but our get requests returns 404. To start with is it possible to get data, such as a created message, from the test database? If yes, what could be wrong? All functions works as expected while running on localhost. Its our first flask project so it could be someting easy we have missed. One of our test that fails looks like this:

[hard canyon]

Why I'm getting this error

[proven arrow]

@wintry wraith There is not enough information about the database in the code you showed to know why it gives 404. Have you tried using visiting that url using your servers details? Or try something like postman to debug it.

[proven arrow]

is that url correct?

[hard canyon]

Where to set the url?

[proven arrow]

where it says jdbc url

Make sure its the correct format for your database

[hard canyon]

The first step is this where I'm adding my jar files

and then the next step is shown above where the connection is failed

[wintry wraith]

would you like to explain more in detail what you need to see? 🙂 We havent used postman, just sending via requests

[proven arrow]

Well databases don't give 404, unless you have coded your api to return with this if no data is found in the database. If you think its with a database query then ask otherwise, you probably need to troubleshoot this further to see why the route is not being recognised.

[torn sphinx]

is it possible to make empty columns, then fill then up later?

[wintry wraith]

ah sorry i get it. I missed to explain that our function that gets messages returns 404 if no message found. Here are 2 of our functions

[torn sphinx]

and count the number of columns for a row with actualy content?

[wintry wraith]

is there a way to see how the temporary db created by the test client looks like?

[proven arrow]

You can make a column nullable and leave it out when inserting. That way no value will be inserted for it.

[proven arrow]

You can download and use database browser if you want to inspect with a GUI tool. https://sqlitebrowser.org/ is an easy one to use for sqlite.
Also now you know that 404 is returned because your query is not returning anything, and that's the question you should be asking or trying to figure out why.

[hasty juniper]

for guild in self.bot.guilds:
            for user in guild.members:
                try:
                    await self.bot.con.execute('''INSERT INTO users(GuildId, UserId, UserCash, UserHours, UserMessages, UserPlay) VALUES($1, $2, $3, $4, $5, $6)''',(guild.id, user.id))
                except Exception as e:
                    await self.logChannel.send("already loaded!")
``` I have a simple database, but the problem is that the rest of the values ​​have a default key, how can I add it to the database

[proven arrow]

If they have default values then you dont need to specify them when inserting

[hasty juniper]

u mean

that await self.bot.con.execute('''INSERT INTO users(GuildId, UserId) VALUES($1, $2)''',(guild.id, user.id))

[proven arrow]

Yes like that

[hasty juniper]

but i have error File "asyncpg\protocol\protocol.pyx", line 178, in bind_execute File "asyncpg\protocol\prepared_stmt.pyx", line 120, in asyncpg.protocol.protocol.PreparedStatementState._encode_bind_msg asyncpg.exceptions._base.InterfaceError: the server expects 2 arguments for this query, 1 was passed HINT: Check the query against the passed list of arguments.

[wintry wraith]

Thanks, our query does return what we want to when we run our app with the db we have created by our own but it doesnt work while testing. All post and get requests works fine. The posts works while testing but not the gets

[proven arrow]

The error is not with the database, but the query you send it

Show the full error, because the new query you showed its fine.

[proven arrow]

Did you run all your migrations? Does that value even exist on your servers db?

[hasty juniper]

Ignoring exception in on_ready
Traceback (most recent call last):
  File "e:\Projects\EveloneBot\exts\events.py", line 32, in on_ready
    await self.bot.con.execute('''INSERT INTO users(GuildId, UserId) VALUES($1, $2)''',(guild.id, user.id))
  File "C:\Users\NotToxic89\AppData\Local\Programs\Python\Python38-32\lib\site-packages\asyncpg\pool.py", line 518, in execute
    return await con.execute(query, *args, timeout=timeout)
  File "C:\Users\NotToxic89\AppData\Local\Programs\Python\Python38-32\lib\site-packages\asyncpg\connection.py", line 297, in execute     
    _, status, _ = await self._execute(query, args, 0, timeout, True)
  File "C:\Users\NotToxic89\AppData\Local\Programs\Python\Python38-32\lib\site-packages\asyncpg\connection.py", line 1445, in _execute   
    result, _ = await self.__execute(
  File "C:\Users\NotToxic89\AppData\Local\Programs\Python\Python38-32\lib\site-packages\asyncpg\connection.py", line 1454, in __execute  
    return await self._do_execute(query, executor, timeout)
  File "C:\Users\NotToxic89\AppData\Local\Programs\Python\Python38-32\lib\site-packages\asyncpg\connection.py", line 1476, in _do_execute
    result = await executor(stmt, None)
  File "asyncpg\protocol\protocol.pyx", line 178, in bind_execute
  File "asyncpg\protocol\prepared_stmt.pyx", line 120, in asyncpg.protocol.protocol.PreparedStatementState._encode_bind_msg
asyncpg.exceptions._base.InterfaceError: the server expects 2 arguments for this query, 1 was passed
HINT:  Check the query against the passed list of arguments.

[wintry wraith]

Im sorry but i dont understand what you mean. Im a newbie

[proven arrow]

Oh right i think for asyncpg you dont need to pass a tuple

so remove brackets around your parameters

[hasty juniper]

Do not all databases store everything in a tuple

[proven arrow]

Its best if you check the syntax in their documentation, as i dont remember on top of my head

Yes but asyncpg does not follow the db api

[hasty juniper]

hm

so thanks again

[burnt turret]

yeah asyncpg doesn't need it to be a tuple

[proven arrow]

I mean have you checked your database on the server to see if the value is stored?

[wintry wraith]

yes the server can access everything from the database 🙂

[proven arrow]

If it could access it then it would not be giving a 404. 🤷‍♂️

Your code returns a 404 when it cant find a value. Finding a value in a tables row, is not the same as if the program has access to the database.

[wintry wraith]

thats only what happpens while it runs the test client

our pytest

[proven arrow]

So the GET works fine?

[wintry wraith]

when not using test client yes

this is our test client

[proven arrow]

Only thing i can say is you will have to troubleshoot it further. There could be a dozen things which might be causing it. Inspect the url you use for the get request etc, to see if everything is as it should be.

[wintry wraith]

okey thank you. But it should work to get things from the db while using our test? I have read the documentation and can't find any use of get.

https://flask.palletsprojects.com/en/1.1.x/testing/

apart from this one that also works for us

[torn sphinx]

so i am using this package and it generate me something like this.

i am trying to figure out how is the _lft and _rgt working?

like how its numbering them, this is for my categories of many levels i store in one table

[proven arrow]

@wintry wraith Honestly your better of asking in #web-development as youll get a better chance of someone who uses flask there

@torn sphinx see the image in the example section of this, https://en.wikipedia.org/wiki/Nested_set_model
Its easier to understand through the image, than explaining through words.

[torn sphinx]

alright let me see

[main pelican]

Hey, I want to update "channel" in "categories" , I'm using pymongo for connecting with mongodb. I tried collection.update_one( { "_id" : 704217816940675072 , "categories" : { "channel" : ""}} , { "$set" : {"categories" : {"channel" : 737187074423128106}}}) but it didn't update the id, how to correctly do it ?

[burnt turret]

in your filter part, you only need the "_id" part really to match, the categories is unnecessary
after that, in your update dict, i think you can use dot notation to update the channel, like
"$set": {"categories.channel": <new ID>}

seeing the data, it looks like you're using this for a discord bot? you should be using motor https://motor.readthedocs.io/en/stable/tutorial-asyncio.html instead of pymongo, as pymongo is blocking

[main pelican]

I'll give it a try, thank you

[burnt turret]

you can read more about nested document querying in mongodb here - https://docs.mongodb.com/manual/tutorial/query-embedded-documents/

[main pelican]

Thank you, i'll try this

[solemn root]

Hi, I'm updating a database, and it works fine for a single user, but as soon as another user gets added to the database, the information from the users update each other and I can't figure out how to use the same variable to update the same column in different rows without causing the updated information from one row to affect the other rows.

This is what I'm getting:Ex. #user1 invokes command U1 = (105) #user2 invokes command U1 = (137) U2 = (32) #user1 invokes command U1 = (156) U2 = (137) #user2 invokes command U1 = (247) U2 = (156)

This is what I want:Ex. #user1 invokes command U1 = (105) #user2 invokes command U1 = (105) U2 = (32) #user1 invokes command U1 = (176) U2 = (32) #user2 invokes command U1 = (176) U2 = (109)

[proven arrow]

@solemn root Thats probably because your query is updating every row

In your where clause you should filter to only a specific user

Each row should have a column with some unique value, and you can use this to filter in the where clause.

[solemn root]

This is my update line:
crsr.execute("""UPDATE tinker SET user_cash = ? WHERE user_token = ?;""", (myPurse, author.id))
I want to update each row's user_cash based off the unique author.id of each user

[proven arrow]

If that is the update statement you used, and if user_token is unique, then you would't be getting this kind of issue.

[solemn root]

using the UNIQUE constraint invalidates any further prompts by the same user to update their row now.

[proven arrow]

What do you mean "invalidates"?

[solemn root]

Essentially just created a 1-use command, because if I prompt the db twice, the author.id is the same, but because of the UNIQUE constraint, it throws an error.

[proven arrow]

Then show the error

[solemn root]

IntegrityError

Traceback game me this

[proven arrow]

And whats the query?

[solemn root]

crsr.execute("""INSERT INTO tinker (user_cash, user_token) VALUES (?, ?);""", (myPurse, author.id)) "sqlite3.IntegrityError: UNIQUE constraint failed: tinker.user_token"

[proven arrow]

Well yes your using INSERT so why would you think that is happening?

[torn sphinx]

ok

[proven arrow]

Its pretty obvious, you have a unique constraint and your trying to insert some value again.

You should use the UPDATE statement to update values. https://www.w3schools.com/sql/sql_update.asp

[inland stone]

why is there no free mongo client that isn't shitty

[solemn root]

I removed the INSERT INTO statement... It still doesn't work. Only difference is that I can't see the output in console now

[proven arrow]

We have no idea what you changed your code too, so you need to show any updated code you made.

[solemn root]

Main:```@bot.event
async def on_ready():
print('Logged in as')
print(bot.user.name)
print(bot.user.id)
print('------')
connection = sqlite3.connect('tinker.db')

crsr = connection.cursor()
sql_command = """CREATE TABLE IF NOT EXISTS tinker (
id INTEGER PRIMARY KEY,
user_cash INTEGER,
user_token INTEGER UNIQUE);"""
crsr.execute(sql_command)

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

connection.commit()
connection.close()

myPurse = 0
#86400 secs

@commands.cooldown(1, 5, commands.cooldowns.BucketType.user)
@bot.command()
async def work(ctx):
global myPurse
user_cash = math.floor(random.random()*100)
myPurse += user_cash
#DBFile.dataScript(ctx)

author = ctx.message.author

connection = sqlite3.connect('tinker.db')

crsr = connection.cursor()

#crsr.execute("""UPDATE tinker SET user_cash = ? WHERE user_token = ?;""", (myPurse, author.id))

#crsr.execute("SELECT DISTINCT * FROM tinker")

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

crsr.execute("SELECT DISTINCT user_cash, user_token FROM tinker ORDER BY id DESC")

tst = crsr.fetchall()

for tst in tst:
print(tst)

connection.commit()
connection.close()
await ctx.send("Your Wallet: $" + str(myPurse) + "\n")```

Not sure where I made a mistake^

[proven arrow]

Your queries are fine

[solemn root]

The data updates over multiple rows, which isn't what I want. So, if user1 has $50, and user2 gets added with $39, now user1 has $89 while user2 has $39, same vice versa. (not what I want) I want each user's user_cash to update independently from other user's user_cash.

It might be a scoping issue, but I honestly don't know.

[primal cave]

Hi, how can I make user inventory through my discord bot? Inventory: [{"name": "wood _fishing_rod", "strength": 15}, {"name": "bone _fishing_rod", "strenght": 26}]
And how can I add / remove items through the bot?

[proven arrow]

It probably is a scoping issue. You have a global purse which i have no what that is there for? And your using the value of this in every update.

[hard canyon]

How to resolve this?

[solemn root]

using it for updating the user_cash of an user(author.id). I couldn't figure out how to declare the variable so that myPurse starts as 0 for each user once, but is never reset to 0, and that will probably allow me to separate the author.id user_cash's.

Couldn't figure out how to localize myPurse without having it reset the user_cash to 0 every time.

[proven arrow]

You dont need it. Assuming you want to add some random value to their existing balance you can do the following:

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

where cash_to_add is the amount you want to add

Also in your code there is no insert so unless you are inserting that user somewhere else the update wont take place.

[solemn root]

ok, so how can I print the value of user_cash then since it's a column in the database and not a variable?

[proven arrow]

Select it

[solemn root]

I'm guessing a :
crsr.execute("""SELECT user_cash FROM tinker;""")
ought to do the trick?

[proven arrow]

That will give you all users cash.

[solemn root]

  crsr.execute("SELECT user_cash FROM tinker")

  tst = crsr.fetchall()

  for tst in tst:
    print(tst)

  connection.commit()
  connection.close()
#I want this to print the user's cash instead of what they earned
  await ctx.send("Your Wallet: **$" + str(cash_to_add) + "**\n")

[proven arrow]

Well i think you need to start thinking and figuring this out for yourself. Its not too much really, and you already know how to filter data using where, so you should be able to solve this.

[bitter drift]

Any opinions on dataclasses?

[white geode]

I have a mongo DB and I sort by the "time" field and limit to 10 documents to create a leaderboard.
When I add a new document I want to see where the new score would land in the "top 10".
Is there a way to limit the amount of documents to show all faster documents up to the new document?

I figured if i counted the faster documents than the new one, I could find what place the new document would be in the leaderboard

[polar helm]

alr so I'm not writing this in python, but I dunno where else to ask this (if you know, feel free to tell me / dm me)

sooooo... I'm putting a service into docker so the development environment is a single command away. however, I'm getting:

2021-01-30 22:10:38.187 UTC [191] ERROR:  function digest(text, unknown) does not exist at character 15
2021-01-30 22:10:38.187 UTC [191] HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
2021-01-30 22:10:38.187 UTC [191] QUERY:  SELECT encode(digest(CAST(user_id AS TEXT) || CAST(date_trunc('hour', current_timestamp) AS TEXT), 'sha1'), 'hex')
2021-01-30 22:10:38.187 UTC [191] CONTEXT:  PL/pgSQL function public.seeded_sort_key(bigint) line 3 at RETURN
        automatic analyze of table "dbots.public.bots"

However... digest does exist. (from the output of \df in psql, using the same user I believe):

 public | digest                | bytea            | bytea, text                        | func
 public | digest                | bytea            | text, text                         | func

I'm not quite sure what could be causing the problem / how to fix it

h*ck

i just had to replace digest with public.digest

:/

[thorn canyon]

How can I fix it? py warn_conn.execute(f'''CREATE TABLE IF NOT EXISTS {user.id}()''') warn_conn.commit() warn_conn.execute(f'''INSERT INTO {user.id} ("ku") VALUES ("{reason}")''')

[restive pilot]

@thorn canyon not sure what exact DB type you are working with. But looks like it's normal SQL (RDBMS). I suggest you to do some study on how SQL works using simple SQLite:
https://zetcode.com/db/sqlite/

Because what you are doing is wrong. You don't create a table for each user. You create a table that holds a list of users.

[thorn canyon]

But if I create the table with list of users, I need to add warns to them (not one warn), can I do that?

[torn sphinx]

@client.command()
  async def info(ctx,user):
  await ctx.send(f"{user},Elite D-Day Troops is a community for members that play Roblox D-Day. If you have a question, dont hesitate to ask a staff member.")

"Expected an indented block"

Python btw

[thorn canyon]

not for this channel, and you should know

[torn sphinx]

I just joined stupid

[restive pilot]

@thorn canyon
you create a table holds a list of warns as you call it. The table should have 3 columns id, user warn e.g
So the id just a unique column so you can reference that row later on (if you have to remove a warn)
user the user that this row affects
warn some text probably

So, you can create multiple rows for 1 user having different texts in the warn column

I suggest you to pick up a book or 2 regards to databases 🙂

[thorn canyon]

I know that (hate my english), but don't understand how can I create multiple rows for 1 user having different texts in the warn column

because if I do id, user, warn as I tried to do on my staff bot, warn can be only one, not many

[inland stone]

a good way to do this in sql is using another table with foreign key, I think

[restive pilot]

make sure id is unique and incremental, user should just be varchar and warn can be text or varchar

so you get

+----+----------+----------+
| id | user     | warn     |
+----+----------+----------+
| 1  | master32 | bad      |
+----+----------+----------+
| 2  | master32 | very bad |
+----+----------+----------+
| 3  | coldice  | also bad |
+----+----------+----------+

[thorn canyon]

I'm not doing the same?

[restive pilot]

Yes, foreign keys is good thing, but Master32 is still in basics of SQL.. he / she should pick up a book about it in that case

[inland stone]

That would take far too long

[restive pilot]

No, foreign keys is about referencing to a value in a specific column in one table to another specific column in another table. Also known as relationships

[inland stone]

I prefer collecting bits of information and learning as I go

but it does have its drawbacks, and one needs to know what level they are in and what they really need to understand first.

[restive pilot]

And that's why I said Master32 should learn the basics first. Managing foreign keys can be complicated.
The most basic thing is just to literally just write the string out in the column

[inland stone]

you do have to read something, ultimately

[restive pilot]

yes and then look it up in another table 😛 which just implies relationships, ahahahah

[inland stone]

yeah. google is your friend master...

no i meant reading as in googling

and stack exchange

[restive pilot]

Oh yyea

[inland stone]

i mean

[thorn canyon]

i'm so stupid?

[inland stone]

stack overflow lol

who said anything about stupid

[restive pilot]

anyway @thorn canyon
set up a SQLIte DB (it's plain simple on your machine)
and start reading: https://zetcode.com/db/sqlite/

and once you feel you comfortable with SQL and you create tables manually, you can select / insert / update data, you implement that stuff in python.
Read up on it: https://www.sqlitetutorial.net/sqlite-python/

And when you can connect to the database from your python program, you create your program using the data in the database to select, update, insert, delete and so on 😄

hope that I didn't scare he / she away, lol

[arctic edge]

are the repl.it dbs efficient?

[inland stone]

    update_res = table.update_one({'_id': sub_name}, {"$set": {"subs.$[].po": parsed_dict}})
    update_res = table.update_many({'_id': sub_name}, {"$set": {"subs.$.po": parsed_dict}})

will these two lines achieve the same thing?

i.e. is $[] the same as updateMany?

[inland stone]

^ dumb question

[lyric junco]

is there a way I can connect two sqlite files in same py command?

[restive pilot]

WHAT?! @lyric junco
you create two cursors. It's two databases, one database can't query another database.

Or when I think about... ATTACHis a keyword in SQLIite3 ... maybe you can something like:

connection = sqlite3.connect("mydb.db")
cursor = connection.cursor()
cursor.execute("ATTACH DATABASE ? AS mydb2", ("mydb2.db"))
cursor.execute("SELECT * FROM mydb2.mytable")

@lyric junco ^ that solves your issue?

[lyric junco]

I didn't understand it 😅
I have a command where in the first part I select from file something then in second half I insert something into another file but it idk how I can read from one and insert into another it only reads the first file only

[restive pilot]

I literally gave you an example how to connect one database, attach another database as mydb2 to the same cursor and select from mydb2
you can even select from my.db and insert that into mydb2. I'm sorry but, it' unclear what you are stuck with.

[lyric junco]

Oh ok I'll try it, I didn't understand it at beginning

Thank you

[restive pilot]

E.g to insert from table chairs in mydb to my favorite_items in mydb2 that is another sql file

INSERT INTO mydb2.favorite_items (id, item) SELECT id, chair FROM mydb.chairs as chairs WHERE chairs.id = 1

^ @lyric junco SQL statement provided
of you have to attach database ... AS mydb2

[lyric junco]

😄 thank you this much easier

[restive pilot]

Do people even read these days -- er I mean study

[lyric junco]

I do

Docs exists

[hard canyon]

this is what I'm getting

this is the first step

[hasty juniper]

data = await self.bot.con.fethrow('SELECT UserId FROM users WHERE UserId = $1', user.id)
                    if data != None:
                        await self.bot.con.execute('''INSERT INTO users(GuildId, UserId) VALUES($1, $2)''', guild.id, user.id)
``````cmd
Pool' object has no attribute 'fethrow'
'Pool' object has no attribute 'fethrow'
'Pool' object has no attribute 'fethrow'
'Pool' object has no attribute 'fethrow'
'Pool' object has no attribute 'fethrow'
'Pool' object has no attribute 'fethrow'
'Pool' object has no attribute 'fethrow'
'Pool' object has no attribute 'fethrow'
Exception ignored in: <function _ProactorBasePipeTransport.__del__ at 0x03B281D8>
Traceback (most recent call last):
  File "C:\Users\NotToxic89\AppData\Local\Programs\Python\Python38-32\lib\asyncio\proactor_events.py", line 116, in __del__
  File "C:\Users\NotToxic89\AppData\Local\Programs\Python\Python38-32\lib\asyncio\proactor_events.py", line 108, in close  
  File "C:\Users\NotToxic89\AppData\Local\Programs\Python\Python38-32\lib\asyncio\base_events.py", line 719, in call_soon  
  File "C:\Users\NotToxic89\AppData\Local\Programs\Python\Python38-32\lib\asyncio\base_events.py", line 508, in _check_closed
RuntimeError: Event loop is closed

[burnt turret]

it says it like 10 times lmao, you've misspelled fetchrow

[hasty juniper]

lmao

[burnt turret]

@torn sphinx (answering your question from the DM)

users_col.find({"_id": {"$regex": f"^{ctx.guild.id}"}, "inventory.item": <whatever user input>}).sort("inventory.amount", 1).limit(10)
``` can you try this out, and tell me if it works?

i think it should but i haven't tested it out myself

[torn sphinx]

anyone know how to convert json to mongodb?

[young gyro]

Why?
Error: Operand should contain 1 column(s)
Query: "INSERT INTO table (column1, column2, column3, column4, column5, column6, column7, column8) VALUES (1, 2, 3, 4, 5, 6, 7, 8)"

Mysql

[proven arrow]

Your error is not there but in some other query

[young gyro]

No, this error happened there

[wintry stream]

@meager pawn that's postgres, a database server

just like mySQL

but then something else

for discord bots it's very popular

https://www.postgresql.org/

[proven arrow]

That’s unlikely. You have a sub query error as shown here https://dev.mysql.com/doc/refman/8.0/en/subquery-errors.html

[young gyro]

No, I haven't sub queries

[pine estuary]

how would i connect to an external sql database?

[inland stone]

@pine estuary using a package

[solemn root]

Hi, I need help with my database. My fetchall() statement keeps returning [] when I print it despite updating my table with information in the line before that. I've scoured the web, and "Apparently" I haven't done anything wrong. I'm slowly going insane trying to figure this out solo. I need a fresh set of eyes to see where I forgot something.

@bot.command()
async def work(ctx):
  cash_to_add = math.floor(random.random()*100)
  
  #DBFile.dataScript(ctx)
  
  author = ctx.message.author

  connection = sqlite3.connect('tinker.db')

  crsr = connection.cursor()

  #crsr.execute("""UPDATE tinker SET user_cash = ? WHERE user_token = ?;""", (myPurse, author.id))

  #crsr.execute("""SELECT * FROM tinker""")

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

  crsr.execute("""SELECT * FROM tinker""")

  rows = crsr.fetchall()

  for row in rows:
    print(row)

  connection.commit()
  connection.close()
  await ctx.send("Your Wallet: **$" + str(cash_to_add) + "**\n")```

[hardy ibex]

How would I go about checking which company(ies) a user 'invested' into?
e.x. getting EVERY company they're 'invested' into (with json btw)

using an if statement would get annoying because people can make (almost) as many as they want

[uncut smelt]

I'm creating a recommendation system to provide items to users but not sure how to actually connect it up to a database efficiently. I need to query every user for their ratings for each item and organise this into a dataframe. I'm really confused as to what structure to implement, I am using MongoDB because it has functionality for GeoQuery for the items but not really sure how to structure it. Could anyone point me in the right direction? Thanks 🙂

[torn sphinx]

hi

To use psycopg2, do I have to install PostgreSQL and do something outside of coding in python ?, Sorry if I'm wrong on something, I'm very new to databases and I come up short on sqlite.

[restive pilot]

@uncut smelt #algos-and-data-structs is your channel.
Also you should do some reading on algorithms....
e.g : https://medium.com/@lope.ai/recommendation-systems-from-scratch-in-python-pytholabs-6946491e76c2

@torn sphinx it's fine to SQLite on your local machine to learn and try stuff out on. You don't need PostgreSQL directly.
check out: https://zetcode.com/db/sqlite/

[uncut smelt]

Yeah ik how they work, ive already built it but not sure how to actually hook it up to a database to get the info I need

[torn sphinx]

@restive pilot but it is for bigdata.

[restive pilot]

@torn sphinx so you are new to databases and want to work with data? Pick up a book and start reading

[torn sphinx]

or docs

I know what it is, I am not too newbie, but in authentic databases.

[restive pilot]

@uncut smelt So, you have two scenarios: (note, I've never written anything like this, I can only imagine)
you have X looking at their recommendations right now
you system generating recommendations for user X at any point in given time

In today's world most recommendations are generated beforehand most likely using micro services. In the code it's mostly async, so you would spawn a thread that looks at user Y and gets their recommendations and then stores it to a cache.
So when user Y wants to see their recommended list, the system would go and find their recommendations and display it

[uncut smelt]

Yeah agreed, but its the actual structure of the database im not sure about, does it matter whether it is relational or object?

@restive pilot Tbh would be good if i could voice call? nw if u cant

[restive pilot]

@uncut smelt can't VC

[inland stone]

First update, then commit, then select, then fetch

[restive pilot]

@torn sphinx
I'm confused about your "problem". Go pick a up a book or 2 about databases , get a fundamental understanding on how they work. You are pondering if you have to program anything outside python to make databases work...
But in general, you have setup a databases, configure it to your liking and then you feed it with data from a script or similar.

[torn sphinx]

@restive pilot I just want to know if I should do something outside of python coding, like in other languages.

[restive pilot]

@torn sphinx if you are working with big data in python, you are working with it in big python.... of course you may have to get the data from somewhere and maybe organize it and put into something like elasticsearch or mongodb or postgresql to work with the data, that may or may not require programming depending on what and how you want to store data.

[pure mortar]

why did someone approve this logo

[pure mortar]

its HIVE

a Hadoop+

https://en.wikipedia.org/wiki/Apache_Hive

yeah

and like data engineering thing

and technically databases too

its basically for data warehouses

but ye

since its hadoop+

they copy pasted the elephant from hadoop onto the lower body of a wasp

literally the stuff of nightmares

[inland stone]

say I have

{ 'a': 1, 'b': { 'ba': 1, 'bb': 1, 'bc': 1 } }

and I want to update 'b' with the following document:
{ 'ba': 2, 'bb': 3 }

can I avoid overwriting 'b.bc' (unspecified in the updating doc)?

retain it somehow?

other than doing the update for 'ba' and 'bb' specifically

(but can do it by excluding 'bb')

maybe i should just do it with a for loop for the nested doc fields

[restive pilot]

@inland stone
Assuming MongoDB, it's in the docs with examples:
https://docs.mongodb.com/manual/tutorial/update-documents/

[hasty pike]

hi i have a problem with my tcp chat room it works fine on a local network but i cant get it to run outside my network
can you help me

[inner agate]

Hello everyone. I work with different database attached to my local sql server. Those dbs need to be updated everyday with a sql script. Is it possible to run a script on each db at once using python?

[glossy frost]

Yeah

[granite igloo]

Hello

Sorry for the second post but probably it's better in the database section

Hello, I am trying to extecute a query on Postgre:

query =  """UPDATE guild_rr_config SET 
        rr_emojis = ARRAY(SELECT DISTINCT * FROM unnest(COALESCE(rr_emojis, '{}') || '{$2}'::text[])),
        rr_role_ids = ARRAY(SELECT DISTINCT * FROM unnest(COALESCE(rr_role_ids, '{}') || '{$3}'::bigint[]))
       WHERE id = $1;"""
      
await ctx.db.execute(query, ctx.guild.id, emoji, role.id)

it's giving me this:
InvalidTextRepresentationError: invalid input syntax for type bigint: "$3"

If I try to execute the query on pgadmin it works

thanks

[young gyro]

Why?
Error: Operand should contain 1 column(s)
Query: "INSERT INTO table (column1, column2, column3, column4, column5, column6, column7, column8) VALUES (1, 2, 3, 4, 5, 6, 7, 8)"

[granite igloo]

Not very familiar with SQL, I have to say I am struggling

I basically need to append one element on each array

how you'd do it?

Tried the query on pgadmin it works

it's when I go into python that doesn't

[digital basin]

def code():
    valid = False
    file = open("./data//ACDB.txt","r")
    for codes in file:
        c = codes
        if(c==codes):
            valid!=True
            break
    file.close()
    if(valid!=True):
        print("Access Granted")
        accessed()
    if(valid!=False):
        print("Invalid Liscense Key")```
after i get this granted even for worng keys its like redeem code system i will revel whoel code in dms if you can help dm me pls

i ttried replacecing second with else:

i got saem eroor

and first with h=just if(valid

heres how my old code looked like

def code():
    valid = False
    file = open("./data//ACDB.txt","r")
    for codes in file:
        c = codes
        if(c==codes):
            valid=True
            break
    file.close()
    if(valid):
        print("Access Granted")
        accessed()
    else:
        print("Invalid Liscense Key")```

[torn sphinx]

huh? your error is different to this code you show

@granite igloo you are using which library in python?

[digital basin]

@torn sphinx can ya help with mine

?

[torn sphinx]

what is issue?

also that is not a database man

[granite igloo]

I was trying to get familiar with it using this helper https://github.com/Rapptz/RoboDanny/blob/rewrite/cogs/utils/db.py

[torn sphinx]

huh what is that? 1000 lines of code 🤯

what am i looking at?

what are you trying to do?

[digital basin]

still can ya help me @torn sphinx ?

[granite igloo]

sorry for that 🤣

I am basically trying to execute a query with the good old await con.execute(sql)

where sql is the query above

But I am afraid id doesn't like the syntax '{$3}'::bigint[]

Again, if I modify the query by inserting an int (replacing the argument) it goes through

but if I try to pass the argument it gives me the error

[torn sphinx]

what are you try to do though? insert an array?

[granite igloo]

insert an element in each array

[sinful saffron]

Hi, im having some trouble with sqlite, its a pertty simple note system that im making with discord.py but when I try to get the notes from an user, i get an error

[digital basin]

@torn sphinx am trying to make a redeem code system

[sinful saffron]

This is the table

[digital basin]

umm i meant redeme code

loginr iesgistration system was good with me

[sinful saffron]

Wait, its ok to send code here?

[digital basin]

ye

[digital basin]

so whta am trying to do is

[sinful saffron]

well this is the code and the error says discord.ext.commands.errors.CommandInvokeError: Command raised an exception: OperationalError: near "#1624": syntax error

[digital basin]

i mae a txt file with soem redeem codes and am making the python find it and match it with one user gave

[inner agate]

how could i do that?

actually, it's a .sql file that i need to run on all my dbs

[proven arrow]

In your code you are trying to filter by a column called first. And in your table you don’t have any column with this name.

[sinful saffron]

oh

thanks

btw im not using a f string anymore because someone warned me about sql injection

[proven arrow]

And passing in a value message like that using f strings, will also give an error

Yeah you should avoid using f strings.

Use a parametrised query. It’s not only safer but will also fix the problem I mentioned regarding the error you will get.

[sinful saffron]

This should work, right?

Now im getting the user by their id because they can change their discriminator and name

wait, how do i call a variable there?

the id one

[proven arrow]

Think of what you are doing first. You are doing the same thing I mentioned earlier. You don’t have a column called id in the table.

[sinful saffron]

oh

btw this is the table now, user, user_id and notes

[proven arrow]

I got to go but checked the second pinned message in this channel. It shows how to make the query as I said.

[sinful saffron]

ok, thanks

[inland wave]

o7. Trying to pull data dynamically with input given.

ore = input into fuction (str)
userid= id of user. is also given and is correct (int)
oreamount = should give me the int value stored in the field ore has (should be tuple with int)

For this example I am using Valkite as ORE value.

Running sqlite

The answer should be (120,), however is actually ('Valkite',)

Problem resolved by using some duct tape

[potent vortex]

@inland wave wouldnt that leave you open to sql injection though

[sinful saffron]

Is something wrong with this?

[burnt turret]

Getting an error like error binding parameter - probably unsupported type?
This means that you're passing in something that the database cannot store/is not the data type that you'd set for the column while creating the table. Make sure that you're passing in the correct datatype objects!

A common example of this error is while storing info about discord.Member objects (from discord.py). You'd have set up your table to have a user_id column which expects INTEGERs (or maybe even TEXT) type inputs, but while running a query, accidentally directly pass the discord.Member object, instead of their ID which you were planning on storing.

you probably want to insert only the user's ID

[sinful saffron]

so

I shouldn't insert the user

Just the id and message?

[burnt turret]

don't insert the user

but if you want to retrieve the user later, you store that user's ID

which you can use to retrieve that user object at a later time

[sinful saffron]

ok

[burnt turret]

looks like you are inserting the ID too

also, don't name your variable id

id is a built-in function, and you're overriding it now, rename that variable to something else

[sinful saffron]

ok

So, this should work, right?

[burnt turret]

what are you trying to do with that str(

[sinful saffron]

yeah, i just saw it

[glass gorge]

how do you return the last 5 records in descending order, using flask sqlalchemy

?

it seems to be more complicated than a regular sql query

[sinful saffron]

it worked!

thanks

oh oh..

[burnt turret]

do you have multiple applications accessing the database?

like a GUI to view the data or something

[sinful saffron]

yes, i just closed it

[burnt turret]

there was a longer message here explaining it but i can't find it anymore

yeah that's the issue most of the time

[sinful saffron]

i found something on internet

oh, thanks

[burnt turret]

i see you're using this for a discord bot

switch to aiosqlite, sqlite is blocking

[potent vortex]

so im writing a json API in flask. its gotten rather large over the past 2 years, i found that i have 526 separate functions that just simply execute an sql query (Spread out across 14 modules)

since im stubborn and refuse to convert this over to using SQLAlchemy... does anyone have any advice on how to best organize such a mass amount of functions? (functions that are needed in several different classes btw)

[sinful saffron]

Ok, but its similar to sqlite?

[burnt turret]

yeah, you'll mostly just need to add awaits before queries

[sinful saffron]

oh

Ok, thanks

[burnt turret]

refer their docs, they'll tell you how to set up

[sinful saffron]

👍

[glass gorge]

anyone?

[torn sphinx]

is there a way to add 1 to an integer value?

[proven arrow]

Are all functions in one class or what? Also how big is your api?

[sinful saffron]

With aiosqlite i get this error

[proven arrow]

There’s nothing wrong with having a lot of functions if that’s what’s required.

[proven arrow]

Error says it all. Cursor is not what you think it is.

[torn sphinx]

^

[sinful saffron]

uhm

the thing is it worked with sqlite3

[proven arrow]

Just add 1 like you would anywhere else

[torn sphinx]

oh

[proven arrow]

How are you getting the cursor object? Show more code

[sinful saffron]

It worked just fine until i started using aiosqlite 🤷‍♂️

[proven arrow]

Well the error you showed is in a different part of the code than what you just sent now

[sinful saffron]

oh sorry

[proven arrow]

Your missing await for getting the cursor

[sinful saffron]

thanks

[torn sphinx]

how do I create a db to get started with aiosqlite?

[proven arrow]

The database is the same. You just change the module and some of the python syntax

It’s pretty much just adding await before every db call

[torn sphinx]

interesting how we need a different module just to add the word await to the beginning of all the commands kek

[torn sphinx]

^

[sinful saffron]

btw if i use insert but there was already something there, it will overwrite the old data or just add it next to it?

[proven arrow]

Insert always adds a new entry or attempts to.

[sinful saffron]

So, it would look like, old data, new data, new data 2

[proven arrow]

Yeah

[sinful saffron]

And imagine i need to delete one of those, how can I select a specific one?

[proven arrow]

But generally you don’t keep old data. For that you would update it instead of inserting.

[potent vortex]

92 modules. 16 modules contain the query functions spread out

[proven arrow]

For delete just use the DELETE statement

[potent vortex]

several hundred endpoints (like 300)

[proven arrow]

DELETE FROM table WHERE column = value

[sinful saffron]

So, if i have data 1, data 2 and data3 next to each other, that would work to delete just one of them?

[proven arrow]

Ok makes sense then. Well as I said In my previous message there’s nothing wrong with having many functions if that’s what’s needed. What is it your looking to clean up?

[proven arrow]

Yes. If you don’t supply a where for a delete then it will delete all rows.

[torn sphinx]

halp

[inland wave]

I have installed a filter

[potent vortex]

It’s just very hard to keep things straight. I’d like to condense them in a more class based manner, almost everything else in the API is class based ha

[torn sphinx]

how do I create a db?

[proven arrow]

So then you can store them in your data access layer

[potent vortex]

I’ve found myself taking much longer to locate functions, everything seems disorganized to the point where it’s hindering my performance

[solemn root]

I made those changes, but now it only prints the row I insert on my on_ready() command instead of my update command. I’m confused because it should be printing the updated user_cash instead of the initial definition of the row where I set user_cash as NULL. If I don’t INSERT INTO it doesn’t print to console. I get one problem or the other to resolve, but not both. :/

[proven arrow]

There are certain design patterns you can follow to make this easier. Something like the repository pattern where it would allow you to keep those data access functions in a single class.

Obviously you will have related functions in their own classes.

But that’s one way you can do it.

[solemn root]

Here's the updated code: ```
@bot.event
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);""")

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()

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()

for row in rows:
print(row, "row")

connection.close()
await ctx.send("Your Wallet: $" + str(cash_to_add) + "\n")

I'm not sure why it isn't printing my updated information in the table.

[torn sphinx]

how to create db?

[sinful saffron]

Is this ok? the thing is its supossed to search for the user's notes, if it doesn't have it will create it but if the user has notes it will update it

Ok, i dont think that works

What can i do? I want to update the message

maybe this?

[proven arrow]

What is that function doing? Why do you have the same function for selecting and writing/updating?

[sinful saffron]

Its supossed to look fot that user id and update the notes

Something like that

[proven arrow]

Generally you have one for reading the data. And another separate for writing/updating.

So if it’s for a bot then you will have 2 separate commands.

[torn sphinx]

help?

[proven arrow]

I already answered your question

[sinful saffron]

oh

[torn sphinx]

where?

[sinful saffron]

Using update it would look like this?

[proven arrow]

And the reason it doesn’t work is because your just checking the cursor object. You need to be fetching the data then check, if any data was returned

[torn sphinx]

like, create a database itself

[sinful saffron]

ooh

[solemn root]

Mine’s not updating properly. I’m not sure why. I posted the code back a few msgs.

[inland stone]

I don't know anything about discord py. post your sql here again

[solemn root]

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);""")

  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()

  print("------------------")
  #This doesn't print for some reason, even though I commit before fetching and selecting like you said to do
  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()

  for row in rows:
    print(row, "row")

  connection.close()
  await ctx.send("Your Wallet: **$" + str(cash_to_add) + "**\n")

[sinful saffron]

How can i avoid fetchall looking like this?

would join work?

[solemn root]

It's the bottom function that isn't working right, but idk if the INSERT INTO in my prior function is messing things up.

I just get (1, None, None) row, "row" being temporary so Ik the fetchall executes

[proven arrow]

Because thats all there is in your database

You arent adding anything else to it

[solemn root]

I'm confused. Am I missing parameters in my UPDATE statement?

[static hornet]

does discord.py have a database

[proven arrow]

What does "update" do?

[proven arrow]

No its just a module for developing discord bots

[static hornet]

@lynden why are you printing everything instead of awaiting

[static hornet]

ok

[solemn root]

I intend to update the cash a user has, and keep track of the user's id as well. (bc I want to be able to have a way to check that what is happening in the database is actually happening and not what I think is happening)

[proven arrow]

Update is for updating data,
Insert is for inserting data.

Now look back at your code and see where you are inserting data.

Then youll see why its only printing 1 row

[sinful saffron]

Lufthansa, one question, how can I make the bot to ask for another thing, because It ask for the user and then the note but after that I want it to ask for another thing but using ,* , wont work because I already used that (Its for the command Im making that uses a database)

[proven arrow]

I dont understand what you mean

[sinful saffron]

Should I move on to another channel or i explain it here?

[proven arrow]

If your questions is about a bot then ask in discord py channel

[solemn root]

so how can I insert a new row if the user doesn't already have an entry, else, update it? In a reusable function.

[sinful saffron]

Wait, i think i no longer need that

[solemn root]

I think you had something similar in functionality to what I'm trying to figure out. @sinful saffron Do you think you could explain how you went about it?

[sinful saffron]

Im trying to generate a random id for each note

[sinful saffron]

I was trying to do that but instead i ended up creating a new one if the user already had one, thats why im trying to generate an id for each note so i can delete one of them without deleting them all

[proven arrow]

You can UPSERT for this. https://www.sqlite.org/lang_UPSERT.html

INSERT INTO tinker (user_cash, user_token) VALUES(100, 999)
ON CONFLICT(user_token) DO UPDATE SET user_cash=1; 

Replace the values 100, 999, and the new cash value with your place holders

That will try to insert, however if it finds the same user_token already in the database then it will update the row instead.

[sinful saffron]

Would this work? Its supossed to generate a random id for each note, if theres already a similar id it will generate another one

[solemn root]

I'm getting an OperationalError: near "ON": syntax error.

crsr.execute("""INSERT INTO tinker (user_cash, user_token) VALUES (?, ?) ON CONFLICT (user_token) DO UPDATE SET user_cash = user_cash + ?;""")
If I'm using ? as my placeholders, what's wrong?

[potent vortex]

@proven arrow thanks. ill give something like that a try

[potent vortex]

@solemn root did you make user_token either the primary key or at least a unique index?

when i tried to do your query on a quick test script, i got sqlite3.OperationalError: ON CONFLICT clause does not match any PRIMARY KEY or UNIQUE constraint, but it worked perfectly when i made user_token a primary key

[solemn root]

@potent vortex I have the user_token constrained to being user_token INTEGER UNIQUE

[torn sphinx]

it returns nothing

[potent vortex]

you have an actual index defined?

oh wait

are you passing the data params to the second parameter of crsr.execute

[solemn root]

I thought I was, but I'm guessing I might not be.

[potent vortex]

import sqlite3

conn = sqlite3.connect('testy.db')
cursor = conn.cursor()

sql = 'INSERT INTO tinker (user_cash, user_token) VALUES (?, ?) ON CONFLICT (user_token) DO UPDATE SET user_cash = user_cash + ?'
cash = 300
cursor.execute(sql, (cash, 1, cash))

[stable mural]

i did this but it doesnt update, can someone help?

[torn sphinx]

maybe try c.execute(f"UPDATE userbal SET wallet = wallet + {begged} WHERE wallet > 0"), so it adds the value to the current wallet?

[stable mural]

ok let me try

[torn sphinx]

also are you using sqlite3?

use aiosqlite

for discord bot

[solemn root]

Still get the same error. I gtg for about an hour and a half, but you can dm me a solution if you're not on when I get back.

[stable mural]

yes

[torn sphinx]

@stable mural does what I put work?

[stable mural]

no

doesnt work @torn sphinx

[autumn epoch]

How would I check if there is a certain column in a table in sql?

[torn sphinx]

Is there any way to see the values of a record object

[inland stone]

if I have a mongo search that returns two objects, how do i update the second object?

[jovial notch]

@radiant elbow Hello bro so i got a question i have serial column and whenever i do DELETE FROM warnings the serials dont get reset and they start like from 300 or something like that

[hazy grotto]

quick question: I'm about to setup Ubuntu on a Pi 4b. What should I use for database software that will work well with python?

[pine estuary]

so question for my mongodb im gettin this error Command raised an exception: TypeError: 'Collection' object is not callable. If you meant to call the 'find' method on a 'Database' object it is failing because no such method exists. and this is my script ```py
client = pymongo.MongoClient(
'connectionthangs')
warnings = client.Warning

x = warnings.find({'user':f'{user.id}'})

for data in x:
    await ctx.send(data)``` what do i do to fix this error? idk what to do after `warnings = client.Warning`

[torn sphinx]

import asyncpg
import asyncio

async def start_up():
    conn = await asyncpg.connect(all that info)
    users = await conn.fetch("SELECT * FROM users")
    for user in users: 
        id, name, date = user
        print(id, name, date)
    await conn.close()

asyncio.run(start_up())

is there any way shorter/easier to do this

to retrieve id, name and date and return it

from the table

using asyncpg

[burnt turret]

when you do client.Warnings you've gotten the database object - you need to get the corresponding collection from it and use .find on that

[burnt turret]

Working with asyncpg.Record objects
asyncpg fetch methods usually return Record objects, that when printed out may look something like this:

<Record column1=value1 column2=value2>

This Record object is a tuple-dictionary hybrid.
What this means is that you can access data from it by:

1. Key-access (using it like a dictionary)

# `record` is a variable representing the asyncpg.Record object from the above example
>>> record["column1"]
value1

Note that it also supports other dictionary methods, like items, get etc.

2. Index-access (using it like a tuple)

>>> record[0]
value1
>>> record[1]
value2

Note that dot-access (record.column1) is NOT supported by the default asyncpg.Record class, but you can subclass it and make your own which supports it. https://magicstack.github.io/asyncpg/current/faq.html#can-i-use-dot-notation-with-asyncpg-record-it-looks-cleaner

API Reference for asyncpg.Record - https://magicstack.github.io/asyncpg/current/api/index.html?#record-objects

[torn sphinx]

Ye i just realized

Like a while ago

[burnt turret]

what are you trying to do here

[torn sphinx]

i was trying to retrieve

those values from the rows in a table

so that i can check if they exist

already

[burnt turret]

which values

[torn sphinx]

id, name and date

[burnt turret]

you're retrieving all values from the table right now

[torn sphinx]

ye

[burnt turret]

id, name and data for some specific row?

because you're retrieving all rows now

[torn sphinx]

i was just testing that

i would just want to retrieve id

is there anyways i can make it even more simple

or is that way ok?

[burnt turret]

i don't really understand what you're trying to do 😅

for what rows are you trying to see if those columns exist?

[torn sphinx]

just id

i want to check if an id already exists

wait can i just do a try statement and except (error like primary key)

[burnt turret]

you could query something like SELECT * FROM table WHERE id = $1, pass the ID you want to check, and if the query returns something it exists

[torn sphinx]

do queries return something?

[burnt turret]

depends on what fetch method you use

one second let me refer the docs

[torn sphinx]

thanks anand

i have been trying to search for something like that

for a while now

[burnt turret]

if you use

• fetchrow (meant to retrieve just the first row that matches), and if the query had no matches, it returns None
• fetch (meant to retrieve all rows that match), gives you a list of rows - so i assume if there are no matches it is an empty list
• fetchval (meant to retrieve a single value (a single column's value) for the first row that matches), if the query had no matches, it also returns None

[torn sphinx]

o so basically i can just check if it returns None or if it doesnt

thats cool

i have a last question

i really dont want to make a big table, is it possible to make subtables

because users have different types of currency

so i wouldnt want it to be all there in a single table

is there a way ?

[lyric junco]

Is there a way I can insert on condition? Like select but insert

[burnt turret]

i don't really get what you mean by subtables

you can make another table, add the user-id column and make it a foreign key so that it references your data from the other table

[torn sphinx]

WAIT

IS THAT POSSIBLE

[burnt turret]

yes

[torn sphinx]

Foreign key ok

so how does it work

can you give me an example?

[burnt turret]

one second, i'm searching for some tutorial i'd read on foreign keys haha

[torn sphinx]

Thanks

[burnt turret]

ugh i can't find the thing i'd read before, here's the w3schools link https://www.w3schools.com/sql/sql_foreignkey.asp

[lyric junco]

Anyone knows if this possible?

[torn sphinx]

so i need to insert every id in my first table inside the second one?

is there a way to copy all the id row?

[burnt turret]

what are you trying to do exactly? are you thinking of something that could be done with an UPDATE

[burnt turret]

is there the need for it though

[lyric junco]

I'm trying to save something behind user id but without inserting user id again and again but update didn't work because there's no input before, it was empty

So idrk

And I want to add not to change like if there's 1, 2 in db and I inserted 2,3 I want it to add 3 only

Like update

1s I'll send code

[torn sphinx]

yeah because i want to have "subtables" that lead to the main table with many foreign keys that lead to the primary key in the main table

[burnt turret]

because of the foreign key, you can be sure that you won't insert any user into the subtable that doesn't exist in the main table

[lyric junco]

    async def follow(self, ctx, member: discord.Member):
        user = str(member.id)
        author = str(ctx.message.author.id)
        db = sqlite3.connect('./cogs/database/users.sqlite')
        cursor = db.cursor()
        cursor.execute(f"SELECT * FROM users WHERE AUTHOR_ID = {author}")
        result = cursor.fetchone()
        if "enabled" in result:
            cursor.execute(f"SELECT * FROM users WHERE AUTHOR = {author}")
            results = cursor.fetchall()
            if results:
                await ctx.send("You're already following this user")
                return
            if not results:
                if user not in results:
          -----------user_sql = f"INSERT INTO users(AUTHOR, FOLLOWING) VALUES(?,?)"
                    values = (author, user)
                    cursor.execute(user_sql, values)
                    db.commit()
            await ctx.send(f"You just followed {member.display_name}")
        if "disabled" in result:
            await ctx.send(f"User have disabled users from following him")
            return

look at the dashed line I want to insert "FOLLOWING" only behind AUTHOR_ID without inserting author id over and over (author id already saved in db)

[burnt turret]

so you don't really have to have the entire table made with all the user IDs in it already

[torn sphinx]

o so basically they are like a shared row, if i change something in the primary key it will also be changed in the foreign key

[noble crag]

hi

[torn sphinx]

is it possible to have multiple foreign keys tho?

[burnt turret]

you can yes

[noble crag]

im new here i want to learn coding

[torn sphinx]

you should go to python general

[noble crag]

i heard tht python isvery simple

ok

[torn sphinx]

#python-discussion

[torn sphinx]

but is this a thing?

do they work like this?

[burnt turret]

i haven't tried it

i think it should, or maybe it won't let you edit the primary key value

don't take my word for it though haha im speculating

[torn sphinx]

well

anyways

thank you very much for you time

really have been trying to search an answer for this for a while now

now i must go

cya

[burnt turret]

cya

[burnt turret]

table?

how is the data stored?

fwiw you could just insert a new document with all this details, and change the guild id

[primal cave]

Bruh

[burnt turret]

use ORDER BY

SELECT * FROM tablename ORDER BY usercash ASC;

[primal cave]

How to make the bot add to the table "_id": member.id "guild_id": member.guild.id for each guild, and if this user is on 2 different servers on which there is a bot, he will create entries in the db with "id" : member.id, " guild_id: member.guild1.id", " guild_id: member.guild2.id" so that for each guild the bot creates an entry in the database even if this user is on another server that also has a bot

[burnt turret]

see this is why i asked you how the data is stored - are you dumping all this data in a single collection (note, collection, tables are a relational database thing)

if you are putting it all in one collection, you'd be better off letting mongo auto generate the _id field - because this has to be unique
and then adding a user: member.id field lets you have multiple entries for the same person

[primal cave]

i need member id and guild id :/

[burnt turret]

i didn't tell you not to insert both of them

_id: mongo auto generated id
user: member.id
guild: guild1.id

_id: mongo auto generated id
user: same member as before
guild: different guild id

again, i'm answering this with no knowledge of how you've structure the data

[primal cave]

thx i think this should work

[torn sphinx]

what is the data type for dict in postgresql?

[torn sphinx]

Do nosql databases provide more control over the queries you make to them?

[burnt turret]

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

show your entire code

the sql query i gave will return all rows, ordered by ascending order of usercash and was only an example

don't assign like that

it is giving you a list of record objects

a list of all rows in your table i.e

[torn sphinx]

why cant you use order by?

i dont know friendo sorry

[burnt turret]

sorting the table is exactly what ORDER BY is doing

and it is giving you all the rows, sorted in ascending order

you're trying to unpack that return value like it is a single row with just the user and cash columns but that's not what's happening

you're getting back a list of all rows in your table (with the user and cash columns)

[proven arrow]

Are they wanting the rank to be given by the database?

If you use Order by that will sort it but you have to manually calculate the position

Ok hold I think I have gave example of this here before, I’ll try to find it

You will want to use a window function

[burnt turret]

🤔 didn't realize that's what you were looking for

wouldn't it be simpler to use the python enumerate function and loop over the data from the database while you're formatting it before displaying the result

although getting it from the database itself is a nice option

[proven arrow]

#databases message @hasty juniper See the example here

That will sort it and then assign a row number to each row. You can remove the where clause if you don’t want it just for a single user.

[proven arrow]

You can but if they want a single users position without getting all users then it’s better to let the db handle it

Less data to be transferred and kept in memory too

[burnt turret]

fair enough

[torn sphinx]

@proven arrow i have seen you in laravel server

[proven arrow]

Yeah I remember 😜

Because that’s what you asked for 🤷‍♂️

Position is the rank of that user in the leaderboard

It’s not garbage

It’s a list of objects, where the object type is a Record

I thought you were already given this info #databases message

The link they sent shows how you can use those objects to get data from it

[pulsar kestrel]

cursor.execute("SELECT Name, Email from register WHERE Name='%s' and Email='%s'" % Name , Email)

UnboundLocalError: local variable 'Name' referenced before assignment   can anyone help me?

[torn sphinx]

So i want to know if there is a way to make these queries more efficient. Like they are efficient but its make 5 queries. So in my dashboard i want to display counter for different statuses. Below is what i have currently.

select count(*) as pending from orders where status_id = 1;
select count(*) as accepted from orders where status_id = 2;
select count(*) as processing from orders where status_id = 3;
select count(*) as shipped from orders where status_id = 4;
select count(*) as cancel from orders where status_id = 5;

[torn sphinx]

read error, its because you use name before creating the variable.

[pulsar kestrel]

ok

[proven arrow]

@torn sphinx Use conditional aggregates and put them in a single query

select
count(*) AS total,
count(case when status_id = 1 then 1 end) as pending,
count(case when status_id = 2 then 1 end) as accepted,
... other conditions
from orders

[torn sphinx]

super thanks thats what i was looking for

[torn sphinx]

I'm doing a discord bot, connected the database with mongodb, but when I run it, I get the following error:

discord.ext.commands.errors.CommandInvokeError: Command raised an exception: ServerSelectionTimeoutError: connection closed,connection closed,connection closed, Timeout: 30s, Topology Description: <TopologyDescription id: 601931dfcd68ca0452d2f476, topology_type: ReplicaSetNoPrimary, servers: [<ServerDescription ('cluster0-shard-00-00.sc4qi.mongodb.net', 27017) server_type: Unknown, rtt: None, error=AutoReconnect('connection closed')>, <ServerDescription ('cluster0-shard-00-01.sc4qi.mongodb.net', 27017) server_type: Unknown, rtt: None, error=AutoReconnect('connection closed')>, <ServerDescription ('cluster0-shard-00-02.sc4qi.mongodb.net', 27017) server_type: Unknown, rtt: None, error=AutoReconnect('connection closed')>]>

can anyone help me?

[mint dagger]

how is SQL so fast when it has to do a linear search to find data?

[proven arrow]

It’s not always linear, generally the data will be indexed. But even if it does do linear the engines and query optimisers are pretty smart.

[steel terrace]

guys, is a mysql database better than firestone database?

[proven arrow]

They are 2 completely different databases made designed for different purposes

[steel terrace]

oh

which is more secure?

[proven arrow]

Again they can both be as secure. It’s like comparing apples and oranges

[steel terrace]

oh

cause once my webapp is using a database, I don't want my database to be hacked

[proven arrow]

Well you should first consider what data you want to store and how, then worry about security later

[steel terrace]

I'm making a social media webapp

so it contains passwords of people, their private info and stuff

[proven arrow]

Then make sure to hash and salt the passwords you store.

[steel terrace]

oh

@proven arrow seems like you are super expert in this

[proven arrow]

If you use MySQL then also secure your server, implement access control on the database with privileges and roles. And don’t write code that will open you up to vulnerabilities.

[proven arrow]

No not an expert ahah. Just that I’ve already done this kind of stuff in the past.

[steel terrace]

oh ok

[stable mural]

can you explain more to me?

[torn sphinx]

what is the data type for dict in postgresql?

and can anyone link me docs for dictionary all functions

[proven arrow]

It’ll be json but you probably have to do some conversion of it

[torn sphinx]

can u suggest me something like i am trying to do like when someone sends a message it should +1 to the message.author.id and if its not there it should create one. i dont want to make separate records for it

[proven arrow]

That’s called upsert.

https://www.postgresqltutorial.com/postgresql-upsert/

[ancient delta]

In MySQL, there is the following function:

INSTR(string1, string2)

which returns the position of the first occurence of string2 in string1. I am working with some queries, but what variant of SQL uses this type of instr function?

Instr(1, custom_label_lang, string2)

What does the 1 mean here?

[proven arrow]

I’m pretty sure that is in oracle

[ancient delta]

But what does the first value mean?

I don't understand

[proven arrow]

I think it’s used for where the searching starts

[ancient delta]

So instead of starting at 0 it starts at 1?

row 1 of a table instead of row 0? i.e. start at the second row

[proven arrow]

The function is used for searching substrings so I’m not sure why you are referring to rows and tables

[ancient delta]

so it means start at the second letter and not the first?

[proven arrow]

Yes

[ancient delta]

Ok thank you

[torn sphinx]

how do I get started with creating a db?

@proven arrow

[proven arrow]

There’s a hundred ways to answer that question. Be more specific

[torn sphinx]

i'm planning to create a db to use with aiosqlite, and i have a idea of what the db should look like. the thing is, i dunno how to make the db file. how do I make the db file is my questions

[proven arrow]

import aiosqlite

db = await aiosqlite.connect(“filename.db”)

That would make the file. Their docs show examples how to do it, as well.

[torn sphinx]

ok

the file type is .db?

[full stratus]

how do you get a certain "term" in a document in pymongo

smthing like this

[proven arrow]

No there is no file extension for it. So you can have whatever you want

.db just makes it easier to know what it is

[full stratus]

    "user": "admin",
    "ip": "1.1.1.1",
    "date": "February 02, 2021",
    "time": "08:54 AM"
}```

[torn sphinx]

ok

[full stratus]

how do i get the user in my python code?

[torn sphinx]

how do I make the columns next?

@proven arrow

[burnt turret]

look up for sql tutorials, there's some linked in the first pin of this channel

basically you create tables with the CREATE TABLE statement

[torn sphinx]

OK

[ornate owl]

hiiiii i need help..
so i try to delete a data from my database by selecting a data from a treeview.. i can delete the data in treeview but cannot delete the data if the 'uid' returns an integer... what should i change or add here?
i tried using del_query="DELETE FROM movie_data WHERE movie_id=%s" but it will return a syntax error..
i am using sqlite3 on my database.

here's my code:

def delete_record(tree):
try:
selected_item = tree.selection()[0]
print(tree.item(selected_item)['values'])
uid=tree.item(selected_item)['values'][0]
con1 = sqlite3.connect("movies")
cur1 = con1.cursor()
del_query="DELETE FROM movie_data WHERE movie_id=?"
sel_data=(uid,)
cur1.execute(del_query, sel_data)
con1.commit()
con1.close()
tree.delete(selected_item)

except IndexError:
    pass

[burnt turret]

wrap that in a codeblock please

also, what's the error exactly?

[gaunt garden]

if i want to remove information in a column for a single row in SQLite i have to update/set right? i can't do like "DELETE something FROM table WHERE whatever = ?"

and if so, i can set it back to NULL, right?

[ornate owl]

this is the error if i try to put %s

[burnt turret]

right, i think sqlite uses ? as placeholder

[ornate owl]

this one is the error if i try to put %s

[burnt turret]

what's the issue with the code you sent first?

[ornate owl]

i tried that too but

[burnt turret]

the movie_id is set to some type that doesn't match with your uid variable?

[ornate owl]

i found out that if it detects an integer in movie_id, it won't delete the data.. but it will only delete if it detects a string.

example, if movie_id=0, the data remains.. but if movie_id=a12ha, it will be deleted

[burnt turret]

meaning the movie_id is some kind of character field? so you can just cast uid to str before passing it into the query right?

try passing str(uid) instead of plain uid into the query

[ornate owl]

ohh okay.. i'll try..

[obtuse dock]

i don't know if this is the right place, but i need to organize some data and they are all in xml format

im thinking in converting all for .csv

then I use pandas, but if one cell goes wrong

im fucked

[autumn epoch]

How would I check if there is a certain column in a table in sql?

[burnt turret]

uh, try doing a query like SELECT <column name you want checked> FROM tablename, see if it returns something that isn't None/empty

[ornate owl]

no error, but the data is still there..

[autumn epoch]

I tried that, it throws an error and does not work

[burnt turret]

show what you tried, and show the error

[autumn epoch]

Ok

[burnt turret]

if the error is something like Column Not Found, you have your answer there lmao

[burnt turret]

huh, can you check what type the movie_id column is in your table

[autumn epoch]

This is the error:

column "_1" does not exist

And this is the statement:

 if not cur.execute(f"SELECT _{level} FROM _{int(ctx.guild.id)}"):

[burnt turret]

if the error is something like Column Not Found, you have your answer there lmao

just try except that error

[autumn epoch]

Ok

[ornate owl]

it is on TEXT

[burnt turret]

right, so that would make sense why integer uids wouldn't match, but i'm not sure why it doesn't work after you convert it to string as well 🤔

[autumn epoch]

Still not working, same error

[burnt turret]

how are you try: excepting

because it literally should not raise that error again if you're excepting it

[autumn epoch]

try:
    cur.execute(f"SELECT _{level} FROM _{int(ctx.guild.id)}")
except:

[burnt turret]

except that specific error

[autumn epoch]

Wdym?

[ornate owl]

yeahh.. i've been trying to find answers everywhere but i can't seem to find an answer. anyways, thanks for your help!

[burnt turret]

putting bare except excepts any and all errors that couldve happened

doing something like except ValueError: means it except only the specific error

in this way, except the specific error that the db raises

also, whats your code under the except

[autumn epoch]

So the error would be:

column does not exist

[burnt turret]

no

there'll be something next to that sentence

[autumn epoch]

Like???

[burnt turret]

something like sqlite3.operationalerror or something

[autumn epoch]

Um

I am doing custom errors

I will disable that quickly

Would this be it?
UndefinedColumn

[burnt turret]

show the entire traceback

[autumn epoch]

Ok

Traceback (most recent call last):
  File "/Library/Frameworks/Python.framework/Versions/3.8/lib/python3.8/site-packages/discord/ext/commands/bot.py", line 902, in invoke
    await ctx.command.invoke(ctx)
  File "/Library/Frameworks/Python.framework/Versions/3.8/lib/python3.8/site-packages/discord/ext/commands/core.py", line 864, in invoke
    await injected(*ctx.args, **ctx.kwargs)
  File "/Library/Frameworks/Python.framework/Versions/3.8/lib/python3.8/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: UndefinedColumn: column "_1" does not exist
LINE 1: SELECT _1 FROM _805603954079039538

[burnt turret]

is there something above/below this

[autumn epoch]

Nope

[burnt turret]

discordpy has wrapped the original error into its own CommandInvokeError

[autumn epoch]

Oh wait

Ya

[burnt turret]

yeah send what comes above that

[autumn epoch]

Traceback (most recent call last):
  File "/Library/Frameworks/Python.framework/Versions/3.8/lib/python3.8/site-packages/discord/ext/commands/core.py", line 85, in wrapped
    ret = await coro(*args, **kwargs)
  File "/Users/adam/Desktop/Bot/cogs/setup.py", line 56, in leveluprole
    if not cur.execute(f"SELECT _{level} FROM _{int(ctx.guild.id)}"):
psycopg2.errors.UndefinedColumn: column "_1" does not exist
LINE 1: SELECT _1 FROM _805603954079039538

[burnt turret]

psycopg2.errors.UndefinedColumn this is the error you want to except

[autumn epoch]

Ok

[burnt turret]

also, don't use psycopg2 for discordpy, use asyncpg, as psycopg2 is blocking

[autumn epoch]

So:

except psycopg2.errors.UndefinedColumn:

[burnt turret]

yeah should work

[autumn epoch]

Ok

[burnt turret]

if you've imported psycopg2 normally

and haven't given it an alias or something

[torn sphinx]

is there a way to store lists in a table?

[autumn epoch]

Wdym?

[burnt turret]

ARRAYs exist, yep
i remember you were using postgresql - https://www.postgresql.org/docs/9.1/arrays.html

[torn sphinx]

Ye thanks

Again

[burnt turret]

this doc shows how to create tables with columns with array types

[burnt turret]

meaning itll work if you've imported it as
import psycopg2

[autumn epoch]

Ya

[burnt turret]

if you've given it an alias, like import psycopg2 as pg, then do the error also as pg.errors

[autumn epoch]

Thats how I imported it

[burnt turret]

okay cool

[autumn epoch]

So should I just change it to asyncpg?

[burnt turret]

also, don't use psycopg2 for discordpy, use asyncpg, as psycopg2 is blocking
look into this when you get the time

[autumn epoch]

Then same for the connection?

[burnt turret]

generally people use connection pools with bots

[autumn epoch]

What are those?

[burnt turret]

uh, i'm not knowledgeable enough to explain that well haha

[autumn epoch]

Ok

[burnt turret]

https://magicstack.github.io/asyncpg/current/usage.html#connection-pools the doc explains it well

[autumn epoch]

Ok

And then I will have to await everything

[burnt turret]

it's gonna be a little different

asyncpg doesn't follow the DB API, so there are gonna be differences in some functions

you'd have to spend some time to migrate really

[autumn epoch]

Ok

I will look into it

Thx though

[torn sphinx]

what;s happening?

 File "/usr/local/lib/python3.9/site-packages/aiosqlite/core.py", line 128, in _connect
  File "/usr/local/lib/python3.9/site-packages/aiosqlite/core.py", line 122, in _execute
  File "/usr/local/lib/python3.9/site-packages/aiosqlite/core.py", line 98, in run
  File "/usr/local/lib/python3.9/site-packages/aiosqlite/core.py", line 388, in connector
sqlite3.OperationalError: unable to open database file
Ignoring exception in on_message

[autumn epoch]

How would I use a asyncpg connection pool for a discord bot?

[burnt turret]

Using asyncpg with discord.py

Making a connection pool
You should make a connection pool and assign it to a bot variable, so that it is accessible across all your cogs and extensions.

import asyncpg
from discord.ext import commands

bot = commands.Bot(...)
# assigning to a bot variable - the variable can be called anything, but we're calling ours db here
bot.db = bot.loop.run_until_complete(asyncpg.create_pool(<db details>))

Now you can access the pool anywhere as bot.db (in cogs, that is self.bot.db)

asyncpg Documentation - https://magicstack.github.io/asyncpg/current/index.html
FAQ - https://magicstack.github.io/asyncpg/current/faq.html

don't mind me, formatting that all fancy so that i can link it to people later when they ask again

[torn sphinx]

pin this

[autumn epoch]

Ok thx

Wait what goes in bot?

[burnt turret]

whatever you had before haha

the usual stuff, commands_prefix, intents etc

[autumn epoch]

Oh ok

So for me it is def __init__(self, client): self.client = client

[burnt turret]

the bot is just a variable name, you can do client.db = client.loop...

[autumn epoch]

Oh ok

[torn sphinx]

@burnt turret when I do db = await aiosqlite.connect("filename.db"), where is the filename.db stored?

[burnt turret]

the db is also a variable name, you can call that also whatever you want

[burnt turret]

it should be at the level that the interpreter is at

[torn sphinx]

hmmm?

level?

wdym?

[burnt turret]

imagine you were running your script here

then the db file should also be there

[torn sphinx]

ohhhh

[burnt turret]

i'm not explaining this very well am i

[torn sphinx]

its in my home directory

[torn sphinx]

you mean working directory right?

[burnt turret]

right, it has to be in the same place - or in the .connect you have to pass an absolute path

[burnt turret]

yeah basically

what i was going to there with was that you could be having cogs in .\cogs too - but the script being run is still at the original location so in case you were trying to do that in the cogs, you'd have to write paths like you're in the working directory

if that makes any sense

[torn sphinx]

ok

[autumn epoch]

@ruby breach I was working on that and I was getting this error:

cannot perform operation: another operation is in progress

[ruby breach]

can you show all code?

[autumn epoch]

Yes

[ruby breach]

you can use https://hastebin.com

[autumn epoch]

Like the entire thing?

[ruby breach]

Well how long is it?

[autumn epoch]

Ummm

96 ligns

lines

[ruby breach]

Yeah just copy+paste it into hastebin

[autumn epoch]

Ok

[ruby breach]

(make sure to take out the token)

[autumn epoch]

This is in a cog so that dosent matter

Ok I pasted it in now what?

[ruby breach]

hit the save button, then copy the link and send it to me

[autumn epoch]

Ok

Mhm its not saving

[ruby breach]

once you hit the save button, the link in your browser will change

nvm it's not working for me either

Lol

[autumn epoch]

lol

[delicate field]

Hey @autumn epoch!

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

[autumn epoch]

Here

[ruby breach]

https://paste.pythondiscord.com/

[autumn epoch]

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

[ruby breach]

yeah

@autumn epoch look at line 65 and 66

You're using the connection outside of the pool context

[autumn epoch]

Ok

Oh ya

[ruby breach]

This means that some other part of the bot could also be using that same connection

and that would cause that error

[autumn epoch]

There fixed it:

async with pool.acquire() as cur:
                            async with cur.transaction():
                                cur.execute(f"ALTER TABLE _{int(ctx.guild.id)} ADD _{level} bigint")
                                cur.execute(f"INSERT INTO _{int(ctx.guild.id)}(_{level}) VALUES({int(role)})")

Sorry about the bad formating

[ruby breach]

also

[autumn epoch]

Ya same error

[ruby breach]

I noticed that you're using queries like this:

con.execute(f"SELECT * FROM bla WHERE id={id}")

You should really be doing this:

con.execute(f"SELECT * FROM bla WHERE bla=$1", id)

That will prevent sql injection

[ruby breach]

can you send a traceback?

[autumn epoch]

Ok

[autumn epoch]

Also I am confused

Traceback (most recent call last):
  File "/Library/Frameworks/Python.framework/Versions/3.8/lib/python3.8/site-packages/discord/ext/commands/core.py", line 85, in wrapped
    ret = await coro(*args, **kwargs)
  File "/Users/adam/Desktop/Bot/cogs/setup.py", line 50, in leveluprole
    cur.execute(f"CREATE TABLE _{ctx.guild.id}(id bigint PRIMARY KEY, xp bigint, level integer)")
  File "/Library/Frameworks/Python.framework/Versions/3.8/lib/python3.8/site-packages/asyncpg/pool.py", line 780, in __aexit__
    await self.pool.release(con)
  File "/Library/Frameworks/Python.framework/Versions/3.8/lib/python3.8/site-packages/asyncpg/pool.py", line 654, in release
    return await asyncio.shield(ch.release(timeout))
  File "/Library/Frameworks/Python.framework/Versions/3.8/lib/python3.8/site-packages/asyncpg/pool.py", line 216, in release
    raise ex
  File "/Library/Frameworks/Python.framework/Versions/3.8/lib/python3.8/site-packages/asyncpg/pool.py", line 206, in release
    await self._con.reset(timeout=budget)
  File "/Library/Frameworks/Python.framework/Versions/3.8/lib/python3.8/site-packages/asyncpg/connection.py", line 1137, in reset
    await self.execute(reset_query, 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 301, in query
  File "asyncpg/protocol/protocol.pyx", line 664, in asyncpg.protocol.protocol.BaseProtocol._check_state
asyncpg.exceptions._base.InterfaceError: cannot perform operation: another operation is in progress

Traceback

[ruby breach]

wait

[torn sphinx]

how do you .execute() with connection pools i already have one assigned in bot

do they work the same as connections

[ruby breach]

async with pool.acquire() as con:
  # handle `con` the same way you would use a connection

@autumn epoch why are you creating a table for every guild?

[torn sphinx]

o sure

so just acquire() ok

thanks

[autumn epoch]

Because I want it to be so all the information of the server is in each table

[ruby breach]

Usually you would create a table called guilds, and then insert every guild into that table

For example:

[autumn epoch]

But then how would I add information like xp and level inside a column?

[long oriole]

does someone know what executemany function does in postgres?

[ruby breach]

# Create the table at bot startup
"""CREATE TABLE IF NOT EXISTS guilds (
  id NUMERIC PRIMARY KEY,
  xp SMALLINT NOT NULL DEFAULT 0
)"""

# on_guild_join:
f"""INSERT INTO guilds (id) VALUES ({guild_id})"""

# on_guild_leave:
f"""DELETE FROM guilds WHERE id={guild_id}"""

# on xp increase:
f"""UPDATE guilds
SET xp={new_xp}
WHERE id={guild_id}
"""

[autumn epoch]

You can do that?

[ruby breach]

That's how tables work

[autumn epoch]

Oh

But that still dosent solve the problem

[ruby breach]

I think it runs the same query multiple times, but with different arguments. For example,

query = """INSERT INTO mytable (bla1, bla2) VALUES ($1, $2)"""

con.executemany(query, (("bla1", "bla2"), ("hello", "bye"), ("4", "40")))

[ruby breach]

It might be because you can't create tables while other queries are running, but I'm not sure

[long oriole]

but how you taking 2 args and defining 3 in the execute many

[ruby breach]

It's a list of 2 arguments

[autumn epoch]

It worked with psycopg2 then I tried switching then got the errors

[ruby breach]

That's because your bot is asynchronous, but psycopg2 is not. So each query was running one at a time, but now they're all running at the same time

[autumn epoch]

Oh

[long oriole]

what is the second argument

("hello", "bye")

?

[autumn epoch]

So where do you think the problem is?

[ruby breach]

@autumn epoch did you fix lines 65 and 66?

[autumn epoch]

Ya I added the stuff

[long oriole]

@ruby breach can you tell me the second argument ?

[autumn epoch]

So now it is:

async with pool.acquire() as cur:
  async with cur.transaction():
    cur.execute(f"ALTER TABLE _{int(ctx.guild.id)} ADD _{level} bigint")
    cur.execute(f"INSERT INTO _{int(ctx.guild.id)}(_{level}) VALUES({int(role)})")

[torn sphinx]

where should I put my connect statement to my db, how do I make a table, and how do I create columns?

[ruby breach]

@autumn epoch I think it's probably that your creating tables constantly

well actually

Did that problem occur as soon as you launched the bot?

[autumn epoch]

No

[ruby breach]

list_of_args = [
  ("bla", "bla2"),
  ("hello", "bye"),
  ("400", "6")
]

query = "INSERT INTO bla (var1, var2) VALUES ($1, $2)"

# I can do this:
for var1, var2 in list_of_args:
    con.execute(query, var1, var2)

# Or I can do this:
con.executemany(query, list_of_args)

[autumn epoch]

I am testing it by running the command:

leveluprole

[ruby breach]

ok

one second

[autumn epoch]

Ok

[long oriole]

oh thanks !!

and is more efficient right ?

[ruby breach]

I'd assume so

but I really don't know

[torn sphinx]

^

[ruby breach]

@autumn epoch yeah I'm pretty sure it's because you're creating a table every time it's run

Try creating a guilds table at the bot startup, and then instead of creating a new table, just insert into the guilds table

[autumn epoch]

Well that only runs whenever someone joins

[proven arrow]

@autumn epoch What error are you getting?

[ruby breach]

no it runs when you run the command

[autumn epoch]

Oh ya

It creates a column

[ruby breach]

you create a new table

[autumn epoch]

#databases message

Oh ya that was a test

[torn sphinx]

@ruby breach halp

[ruby breach]

I'm fairly certain that if you just use a guilds table instead of making a new table every time a guild is joined, it will solved the problem

[autumn epoch]

Ok

[proven arrow]

How are you creating the connection?

[ruby breach]

@torn sphinx What language and library do you use

[autumn epoch]

#databases message

Script

[ruby breach]

asyncpg.Pool

[torn sphinx]

python, aiosqlite

[proven arrow]

But how are you running that pool function?

[ruby breach]

It runs at startup

that part isn't the problem

[ruby breach]

I'd look at some sql tutorials, then look at the aiosqlite tutorial

[proven arrow]

It is the problem, how they start the task with the event loop

[ruby breach]

asyncio.run

[proven arrow]

Can you show the code

[torn sphinx]

ok

[autumn epoch]

#databases message

[proven arrow]

Is it for a bot?

[autumn epoch]

Ya

[proven arrow]

Yeah so dont use asyncio.run

[ruby breach]

Using asyncio.run is fine, if it's done before the bot is run

[proven arrow]

Get the bots event loop and create a task on that

Your bot will already have its event loop, so you can do bot.loop.create_task(db())

[ruby breach]

I'd agree that there's a more readable solution, but solving that isn't really a necesity

[autumn epoch]

On line 53 - 61 it does a try and except, but the try is failing as it is sending the message. It is just trying to:

cur.execute(f"SELECT _{level} FROM_{int(ctx.guild.id)}")

[ruby breach]

bot.loop and asyncio.run will both work, so there's not reason to change

[ruby breach]

Do you know what the error is?

[autumn epoch]

Im just testing that out

[torn sphinx]

how do I create a table?

also

[ruby breach]

because that query will return a list of dicts, containing the value you want

[torn sphinx]

if connecting to the db on every message sent a good idea?

[autumn epoch]

This:
InterfaceError: cannot perform operation: another operation is in progress

Same as before

[proven arrow]

Did you do what i said?

[ruby breach]

that won't solve the problem

[autumn epoch]

I could try it

[proven arrow]

How many messages are you having?

[ruby breach]

no that's a bad idea. Store a connection, or use a pool. Also, look at this https://www.w3schools.com/sql/

[ruby breach]

Honestly I would start by using a guilds table. I know it's a lot of work to change, and will require that you rewrite most of what you have, but creating a new table for every guild is a very bad idea. (and I'm pretty sure it'll solve the main problem)

[torn sphinx]

currently only one server, but might increase

[autumn epoch]

Ok I will

I will rewrite the entire thing

I will text you if I have any problems

Have a good day

[ruby breach]

You too

[torn sphinx]

connection?

wut dat?

[proven arrow]

Generally you have a single connection across your application, and use that because its cheaper than making new connections each time.

[torn sphinx]

ok

[ruby breach]

and because there's a 10 connection limit on most libs

[long oriole]

@ruby breach

list = fetch("SELECT * FROM table WHERE user_id = $1", user_id)
if not list: INSERT
else: UPDATE

what was the replacing thing for this the on conflict thing how can i use it

[torn sphinx]

where should I store the connection?

[ruby breach]

one idea is to store it on the bot object -- for example:

import asyncio
import asyncpg

bot = commands.Bot(...)

async def create_connection():
    bot.con = await aiosqlite.create_connection(...)

# Right before `bot.run`:
asyncio.run(create_connection())

# then, whenver you need to do something
await bot.con.execute(...)

This is using a connection, so you'll probably need to lock.

[ruby breach]

What?

[torn sphinx]

bot object?

[long oriole]

there is some on conflict

[ruby breach]

I assume you use @bot.event somewhere in your code? bot is the object I'm talking about.

[torn sphinx]

yes

[ruby breach]

what do you mean by that?

[torn sphinx]

what does.execute mean?

[long oriole]

something that reduces that into one line

[ruby breach]

you've used aiosqlite right?

[torn sphinx]

ohhh right db.execute

[ruby breach]

you can do INSERT if not list else UPDATE, but I'm not sure what you're trying to do

[ruby breach]

that or conn.execute, I haven't used aiosqlite for a while

[torn sphinx]

ok

[trim lintel]

How to check if a column is exist in a table?

[ruby breach]

check if a row exists, or check if a column is on a table?

[trim lintel]

column

[ruby breach]

@trim lintel Well what are you trying to do? Do you just need to know if a column exists, or are you trying to drop a column only if it exists?

[trim lintel]

check if column exists

[proven arrow]

What db are you using?

[trim lintel]

mysql and sqlite, one i use for test other for live server