databases

[harsh pulsar]

or is it taking 40 seconds and returning the same data?

what does initial_db_read do?

[whole coral]

oh wait. I have an idea

it seems that it can be connected to logger

lemme run it without logging ON

[harsh pulsar]

40 seconds seems like a very long time for a query like this

if you just run each query in a postgres client, how long does it take?

the problem with this is you don't know which part is taking 40 seconds

adding some of your own logging could help, not using the discord logger but create a new one for your app

[whole coral]

i'm very new to logging...can you point me at any guide, or just the documentation will suffice?

and no, seems like not a logger issue, I was wrong. With logger section commented I still have to wait for a long time

[harsh pulsar]

the official docs are are OK, but not the best if you are totally new to it:
https://docs.python.org/3/howto/logging.html#logging-basic-tutorial
https://docs.python.org/3/howto/logging.html#logging-advanced-tutorial
https://docs.python.org/3/howto/logging-cookbook.html

it might be easier in your case to just print(time.perf_counter()) between each line and see which line takes the longest to execute

[whole coral]

doing it

okay, now I have a bunch of numbers like
3 117.2003458
4 117.9515448
5 118.7009403
6 118.7010365

where 3,4,5 are queries to database and 6 is a function for counting hours

and queries from items 3,4,5 takes 0,5-0,7 seconds each

@bot.command()
async def top(ctx, count: int = 10):
    result_list = []
    #await ctx.message.delete()
    db = await pool.acquire()
    users_count, users_ids = await initial_db_read()
    print(1, time.perf_counter())
    checkrole = discord.utils.find(lambda r: ('СОКЛАНЫ' in r.name.upper()), ctx.guild.roles)
    print(2, time.perf_counter())
    for member in ctx.guild.members:
        if member.id in users_ids and checkrole in member.roles and not (member.id == member.guild.owner_id):
            gold = await db.fetchval(f"SELECT gold from discord_users WHERE id={member.id};")
            print(3, time.perf_counter())
            if int(gold) > 0:
                warns = await db.fetchval(f"SELECT warns from discord_users WHERE id={member.id};")
                print(4, time.perf_counter())
                thirty_days_activity_records = await db.fetch(
                    f"SELECT login, logoff from LogTable WHERE user_id={member.id} AND login BETWEEN '{datetime.datetime.now() - datetime.timedelta(days=30)}'::timestamptz AND '{datetime.datetime.now()}'::timestamptz ORDER BY login DESC;")
                print(5, time.perf_counter())
                activity = await count_result_activity(thirty_days_activity_records, warns)
                print(6, time.perf_counter())
                result_list.append((member.mention, activity))
    res = sorted(result_list, key=itemgetter(1), reverse=True)
    print(7, time.perf_counter())
    count = len(res) if count > len(res) else count
    output = "".join(f"{i + 1}: {res[i][0]}, актив: {res[i][1]} часа(ов);\n" for i in range(count))
    print(8, time.perf_counter())
    embed = discord.Embed(color=discord.Colour(int('efff00', 16)))
    embed.add_field(name='Топ активности', value=output)
    await ctx.send(embed=embed)
    await pool.release(db)```

[harsh pulsar]

ok, so we know it's not your queries. those are clearly fast

what about the difference between 2 and 3?

[whole coral]

well, there are just quite many of them. The timer goes from 23 to 113+ while the function is being executed

1 44.2718655
2 44.2727759
3 44.7847913
4 45.2790097
5 45.7720971

this is the first sequence

after it ends I'll post the last

3 84.2718272
4 84.5214431
5 84.7721952
3 85.0218643
3 85.271848

this time it was faster

41 seconds

[harsh pulsar]

what do you mean "while the function is being executed"

oh, you do this in a loop

i want to know:

1. is acquiring the connection slower?
2. are the queries slower?
3. is releasing the connection slower?

[whole coral]

nvm, it seems I know the real reason...Someone else connected to the machine where my VM is situated and now overutilizing it

[harsh pulsar]

hah, there you go

[whole coral]

[jade reef]

so i just got a vps whats the easiest way to copy my db from my local machine to vps?

[austere portal]

How can the cpu usage exceed 100%

[austere portal]

Take a look at this
https://www.digitalocean.com/community/tutorials/how-to-migrate-a-mysql-database-between-two-servers

[whole coral]

upload it...

[whole coral]

dunno, someone decided to squeeze out everything it has I suppose...and maybe some CPU overclocking occurred.

[austere portal]

oh

[whole coral]

now, when the grip loosened my function executes in like less than a second

shared core VMs are like this, but they're too cheap to say no xD

[austere portal]

[whole coral]

right now I pay like $0,06 a month. the next cheapest VM instance will be $9,15 a month

feel the difference

[austere portal]

1000% value

[oak carbon]

hello i have aws vps and i installed postgresql on it and i am trying to enable remote access to it but im getting error while connecting from my pc

https://linuxconfig.org/ubuntu-20-04-postgresql-installation/
i did these things as of now ^

[austere portal]

is the db on the vps running?

[oak carbon]

yes

@austere portal

[austere portal]

hmm

Sorry I don't think I know what's the issue here

[formal coral]

you can lookup some guide, akaik you have to edit some files, as maybe the postgres server is only listening for localhost

by default it listens only localhost

https://www.linuxcloudvps.com/blog/how-to-install-postgresql-on-ubuntu-18-04/
personally I used this guide to setup mine

[late crag]

If i code something in python how do i get it onto a database so it can run 24/7

@ me

[torn sphinx]

I just use json files lmfao

[torn sphinx]

https://dash.bluefoxhost.com

Free hosting

[harsh pulsar]

you need to run a server 24/7 to run a program 24/7. the easiest option is to use sqlite, because the database is just a single file, and you can store that file on the server where your python code runs

[wheat swan]

I have this code wit Mongo DB Atlas but getting this error ```py
@client.command()
async def setcountchannel(ctx, channel1):
try:
channel = client.get_channel(channel1)
to_store={
"_id": ctx.guild.id,
"game-channel" :111111111111111111,
"hc": 0,
"lost-name" : "None",
"highest-gon" : 0,
"type":"server-storage"
}
Collection.insert_one(to_store)
await ctx.send(f"{channel.mention} is been set as the counting channel.\nuse command =sc 1 to start the game")
except:
if channel == client.get_channel(channel1):
channel = client.get_channel(channel1)
result = Collection.find_one({"_id" : ctx.guild.id})
a = result["game-channel"]

        prv = {"game-channel" : a}
        a_up = {"$set":{"game-channel" : channel.id}}
        Collection.update_one(prv , a_up)
        
    else:
        await ctx.send("an error ouccered")   ```

error Ignoring exception in command setcountchannel: Traceback (most recent call last): File "c:\Users\Soham\Desktop\alphagamer bot\test.py", line 1297, in setcountchannel Collection.insert_one(to_store) File "C:\Program Files\Python39\lib\site-packages\pymongo\collection.py", line 705, in insert_one self._insert(document, File "C:\Program Files\Python39\lib\site-packages\pymongo\collection.py", line 620, in _insert return self._insert_one( File "C:\Program Files\Python39\lib\site-packages\pymongo\collection.py", line 609, in _insert_one self.__database.client._retryable_write( File "C:\Program Files\Python39\lib\site-packages\pymongo\mongo_client.py", line 1552, in _retryable_write return self._retry_with_session(retryable, func, s, None) has no attribute 'id'

[late crag]

is it trusted

like is it legit

[torn sphinx]

Yes....

[torn sphinx]

^^

[late crag]

k

[torn sphinx]

it’s been around for a while and has a lot of uses

[harsh pulsar]

@wheat swan what is Collection? where is it defined?

[wheat swan]

thank you for the help but the problem is solved

[delicate field]

Hey @small wadi!

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

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

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

https://paste.pythondiscord.com

[torn sphinx]

why isn't this working

@client.command()
async def work(ctx):
    db = sqlite3.connect('main.sqlite')
    cursor = db.cursor()
    cursor.execute(f"SELECT money FROM main WHERE user_id = {ctx.author.id}")
    result = cursor.fetchall()
    
    if result == "Null":
        cursor.execute(f"INSERT INTO main (money, user_id) VALUES ({random.randint(50, 100)}, {ctx.author.id})")
    else:
        cursor.execute(f"UPDATE main SET money = money + {random.randint(50, 100)} WHERE user_id = {ctx.author.id}")

    db.commit()```

At first

it wouldn't add

then it would keep making new rows

even if it was me

so I would have like 5 of the things with my ID

now it doesn't do anything

[austere portal]

Try doing if not result:

[harsh pulsar]

+1, "Null" doesn't mean anything in this case

if there are no results, result will be an empty list

also don't use f-strings for sql

https://docs.python.org/3/library/sqlite3.html ctrl-f for "Instead, use the DB-API's parameter substitution"

@torn sphinx ☝️

[torn sphinx]

I did if result == "Null" or not result:

now it works sorta

my friend is helping me

btw thanks salt rock

[harsh pulsar]

1. also don't reuse cursors for multiple queries
2. don't use f-strings for putting data into sql queries
3. "Null" is just a string and it isn't what you meant; the result will never be "Null"

a better version might look like this:

@client.command()
async def work(ctx):
    db = sqlite3.connect('main.sqlite')

    cursor = db.execute(
        f"SELECT money FROM main WHERE user_id = ?",
        (ctx.author.id,),
    )
    result = cursor.fetchone()

    if result is not None:
        db.execute(
            "INSERT INTO main (money, user_id) VALUES (?, ?)",
            (random.randint(50, 100), ctx.author.id),
        )
    else:
        db.execute(
            "UPDATE main SET money = money + ? WHERE user_id = ?",
            (random.randint(50, 100), ctx.author.id),
        )
    db.commit()

better still, if user_id is the primary key of your table (and it should be), you can make use of the database more effectively:

@client.command()
async def work(ctx):
    db = sqlite3.connect('main.sqlite')
    query = """
        INSERT INTO main (money, user_id)
        VALUES (:money, :user_id)
        ON CONFLICT(word) DO UPDATE SET
          money = money + excluded.money
    """
    params = {'money': random.randint(50, 100), 'user_id': ctx.author.id}
    db.execute(query, params)
    db.commit()

this is called an "upsert" operation - update and insert in the same query. see here: https://sqlite.org/lang_upsert.html. note also the use of named parameters (:foo) as opposed to unnamed parameters (?)

edit: this will only work if you have the user_id column set to PRIMARY KEY

[torn sphinx]

okay

thanks

I'll look into that

@client.command()
async def hunt(ctx):
    db = sqlite3.connect('main.sqlite')
    cursor = db.cursor()
    cursor.execute(f"SELECT boar FROM main WHERE user_id = {ctx.author.id}")
    result = cursor.fetchall()
    
    if result == "Null" or not result:
        cursor.execute(f"INSERT INTO main (boar, user_id) VALUES ({random.randint(1, 3)}, {ctx.author.id})")
    else:
        cursor.execute(f"UPDATE main SET boar = boar + {random.randint(1, 3)} WHERE user_id = {ctx.author.id}")
    embed = discord.Embed(
        colour = discord.Color.random()
    )

    embed.add_field(name="You went hunting", value=f"You caught {random.randint(1, 3)} boar", inline=False)

    await ctx.send(embed=embed)

    db.commit()```

This is another thing I have

for some reason it's not doing anything

when I run it

[harsh pulsar]

i find that hard to believe, do you see an error message in the console?

do you understand what's wrong with result == "Null" at least?

[torn sphinx]

uhhhh no?

[harsh pulsar]

"Null" is a string

it is text

it is not "special" in any way

fetchall never returns text

it returns a list of tuples containing data from the database

result == "Null" will always be False because it's a meaningless comparison

it's like asking if the color of your house is "shoe"

"shoe" is not a color

it is a piece of clothing

not result is the same as len(result) == 0 -- this is the actual comparison that you want to perform

[austere portal]

!e py print(type("Null")) print(type(None))

[delicate field]

@austere portal :white_check_mark: Your eval job has completed with return code 0.

001 | <class 'str'>
002 | <class 'NoneType'>

[torn sphinx]

@harsh pulsar so it would be Null just with no quotes?

[austere portal]

No, it should be not result

[torn sphinx]

ok

[austere portal]

Null is a type in js, sql but not in python

[torn sphinx]

still didn't fix it

@client.command()
async def fish(ctx):
    db = sqlite3.connect('main.sqlite')
    cursor = db.cursor()
    cursor.execute(f"SELECT fish FROM main WHERE user_id = {ctx.author.id}")
    result = cursor.fetchall()
    
    if not result:
        cursor.execute(f"INSERT INTO main (fish, user_id) VALUES ({random.randint(1, 3)}, {ctx.author.id})")
    else:
        cursor.execute(f"UPDATE main SET fish = fish + {random.randint(1, 3)} WHERE user_id = {ctx.author.id}")

    embed = discord.Embed(
        colour = discord.Color.random()
    )

    embed.add_field(name="You went fishing", value=f"You Caught {random.randint(1, 3)} fish", inline=False)

    await ctx.send(embed=embed)


    db.commit()```

[harsh pulsar]

i still find that hard to believe

how are you running this?

i would imagine that this produced an error in the console if it didn't return anything in discord

[torn sphinx]

no error

[faint blade]

Do you have an error handler?

[grim vault]

I would also use a variable for random.randint(1, 3) otherwise the db insert/update can be different than the embed-output.

And which command/function are you changing/testing: hunt() or fish()?

[mint tundra]

Hello, I'm trying to get the delta between two timestamps, but unfortunately they are formatted differently:
2021-08-03 13:35:00+00:00
2021-08-03 13:37:00

Help me get these two minutes timedelta object! please :)

[ionic pecan]

you have a timezone-aware timestamp and a timestamp that's naive. that's not really something you should calculate with each other. do you know which timezone the second timestamp is in?

also, from where are you retrieving the data? seeing as this is #databases

[torn sphinx]

how to print each line of a .json file?

[ionic pecan]

what have you tried already?

[mint tundra]

I'm getting my data from Alpaca's API, both of them are in the same timezone i guess

[torn sphinx]

me?

[ionic pecan]

Yes

[torn sphinx]

nothing, i dont know where to begin lol

[ionic pecan]

then you're looking for datetime.datetime.strptime to parse it for one:

>>> import datetime
>>> dt = datetime.datetime.strptime('2021-08-03 13:37:00', '%Y-%m-%d %H:%M:%S')
>>> dt
datetime.datetime(2021, 8, 3, 13, 37)

for the one with the timestamp, you can either split it off entirely, or parse and discard it

then you just need to subract the datetime instances from each other

[ionic pecan]

the thing is, if you really want to print each line, that would basically just output the json file. do you not want to do any parsing of it?

[torn sphinx]

so, ill explain what im trynna do

im lazy but i need to add "**" to EACH line. What im thinking is having it print each line + what i need to add, so it does it for me\

[mint tundra]

Which one of the timestamps should be inserted to strptime?

[ionic pecan]

You mention adding it to the line, but that's not going to produce valid JSON, that is intentional, right? Or do you want to modify the JSON structure?

[torn sphinx]

yes, i just want it to print out the line, + my added thing

like...

print(line + "this here")

[ionic pecan]

So strptime takes two arguments:

• the string to parse
• a formatting string that determines how it should be parsed
  The formatting string above works for parsing the stamp without timezone.

Actually now that I look at it a bit more closely, you can parse this with fromisoformat (since it's in a standardized form):

>>> datetime.datetime.fromisoformat('2021-08-03 13:35:00+00:00')
datetime.datetime(2021, 8, 3, 13, 35, tzinfo=datetime.timezone.utc)
>>> datetime.datetime.fromisoformat('2021-08-03 13:37:00')
datetime.datetime(2021, 8, 3, 13, 37)

To kill off the tzinfo on the first datetime object, you can use datetime.datetime.replace:

>>> datetime.fromisoformat('2021-08-03 13:35:00+00:00')
datetime.datetime(2021, 8, 3, 13, 35, tzinfo=datetime.timezone.utc)
>>> _.replace(tzinfo=None)
datetime.datetime(2021, 8, 3, 13, 35)

[ionic pecan]

I found a nice article that explains working with files and has an example for reading each line in a file: https://realpython.com/read-write-files-python/#iterating-over-each-line-in-the-file that you can check out

[mint tundra]

Thank you! i'll try that

[torn sphinx]

@ionic pecanhi

;-;

[ionic pecan]

@torn sphinx where did you get the 0 from?

[torn sphinx]

i saw it online e.e

@ionic pecan

[ionic pecan]

check the link I sent you. There's no 0 being passed there. Now that I think about it, I've never seen that being passed anywhere...

[torn sphinx]

@ionic pecani keep running into an issue

[ionic pecan]

Ah yes, wonderful Windows, don't we love it.

I'll assume that the file is UTF-8 encoded, change the line saying

open("test.txt", 'r')

to

open("test.txt", 'r', encoding='utf-8')

[mint tundra]

Is there a way to use pd.apply just on the index of a dataframe?
in order to kill that tz with .replace

[ionic pecan]

sorry, I have no idea of pandas :(

[mint tundra]

That's the problem i face, these timestamps are in a Dataframe as the index column, i wish to get rid of the tz in one of them... any idea?

[torn sphinx]

hi, so i have an issue

ok so

i need to add a LONG list to a .json file

not numbers or anything

but words

100+

idk how ;-;

[faint blade]

I am not sure if there's a better place to ask your question, but it's a bit off asked here.

Honestly, what I would do is load the json, add the list wherever you need to, then dump it again. 100+ words is like nothing

[torn sphinx]

@faint bladeok so

im not trying to do it with a command

i need to add 100+ lines to a list in a .json file

but idk how

e.e

think i got it

lemme try sum

SyntaxError: EOL while scanning string literal

k

got it

[ionic pecan]

Sorry, I really don'r know pandas :(

[mint tundra]

I got it i think! thanks anyway!

[torn sphinx]

@ionic pecanhey

# Open file    
fileHandler = open("data.txt", 'r', encoding='utf-8')
# Get list of all lines in file
listOfLines = fileHandler.readlines()

# Iterate over the lines
for line in listOfLines:
    print( "**" + line.strip() + "**")

how do i add quotations to the printing?

without it messing up

i want it to be like... adding quotes, astrisks and the words

[harsh pulsar]

i have to ask... why would you want to remove the time zones?

[harsh pulsar]

!code @cosmic pollen you can ask for help, but you should use your own help channel. see #❓｜how-to-get-help . also, please post your full code and the full error output as text if possible. see below 👇

[delicate field]

Here's how to format Python code on Discord:

```py
print('Hello world!')
```

These are backticks, not quotes. Check this out if you can't find the backtick key.

[cosmic pollen]

thank you will keep that in mind

[harsh pulsar]

@cosmic pollen this channel is more for specific discussion about databases. for your kinds of questions, the helpe channels will be more useful.

[jade reef]

how can i get just the boolean value from that

[burnt turret]

That record object acts like a dictionary-tuple hybrid

so record["exists"] or record[0] either works

[jade reef]

i figured it out its return blacklisted_channel[0].get("exists")

[torn sphinx]

if you want quotes inside of something use single quotes so ' "" ' line.strip() + ' "" '

[torn sphinx]

i figured it out haha, thx

[mint tundra]

Because some have timezone and some don't, which makes it impossible to compare between them, i was able to do it using index.tolist(), then list comprehension on the index column

[mint tundra]

old_timestamps = df.index.tolist()
new_timestamps = [timestamp.replace(tzinfo=None) for timestamp in old_timestamps]
df.index = new_timestamps 

[harsh pulsar]

@mint tundra in the future you can use df.index.tz_convert and df.index.tz_localize

also - wouldn't removing all timezone info just make the problem worse?

[lost echo]

suppose I have a list of IDs and I want to fetch a record from a table that should not have those IDs. Is there any way to do this in mysql?

[austere portal]

You can do something like this ```sql
SELECT * FROM table_name
WHERE id_column NOT IN (list_of_ids)

@lost echo

[lost echo]

query = """
SELECT * FROM table_name
WHERE id_column NOT IN %s
"""
value = (101,102,103,104)
db_cursor.execute(query, value)
result = db_cursor.fetchall()

Like so? @austere portal

[austere portal]

Yeah, that should work

[lost echo]

alright, i'll test it out

[austere portal]

ah wait

you need to replace table_name and id_column with the actual values

[lost echo]

oh yeah, that's obvious

def get_test_records():
    query = """
    SELECT * FROM requestitem
    WHERE RequestItemId NOT IN %s;
    """
    value = (101,)
    db_cursor.execute(query, value)
    result = db_cursor.fetchall()

    return result

Traceback (most recent call last): File "D:\Coding Stuff\UpWorkStuff\li_bot_server\test_sql.py", line 81, in <module> print(get_test_records()) File "D:\Coding Stuff\UpWorkStuff\li_bot_server\test_sql.py", line 72, in get_test_records db_cursor.execute(query, value) File "C:\Users\maila\.virtualenvs\li_bot_server-uHVWTe_a\lib\site-packages\mysql\connector\cursor.py", line 568, in execute self._handle_result(self._connection.cmd_query(stmt)) File "C:\Users\maila\.virtualenvs\li_bot_server-uHVWTe_a\lib\site-packages\mysql\connector\connection.py", line 686, in cmd_query result = self._handle_result(self._send_cmd(ServerCmd.QUERY, query)) File "C:\Users\maila\.virtualenvs\li_bot_server-uHVWTe_a\lib\site-packages\mysql\connector\connection.py", line 573, in _handle_result raise errors.get_exception(packet) mysql.connector.errors.ProgrammingError: 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '101' at line 2

[harsh pulsar]

you have to manually add the ()

[lost echo]

didn't work @austere portal

[harsh pulsar]

def get_test_records():
    query = """
    SELECT * FROM requestitem
    WHERE RequestItemId NOT IN (%s)
    """
    params = (101,)
    db_cursor.execute(query, params)
    result = db_cursor.fetchall()

    return result

[lost echo]

where? in query?

alright

works, thanks guys!

[austere portal]

👍

[scenic gale]

Does anyone know how I can delete all null values and any whitespace

from my mongodb

[mint tundra]

The thing is all timezoned timestamps are in UTC so the timezone doesn't matter at all

[harsh pulsar]

that does make sense then

[mint tundra]

the +00:00 killed me, so i eliminated it first

[cerulean jackal]

semi-offtopic question, in case there are people who work with mongodb on arch linux - did you install it from site or from aur?

[torn sphinx]

anyone know what postgres program this is? psql outputs this as a bunch of columns in a different format (is there a way to make it rows?)

[brave bridge]

@deft badge is responsible for the above, I assume 👀

[harsh pulsar]

this might be pgcli https://www.pgcli.com/

[torn sphinx]

this looks sick, made my day

[brave bridge]

oh, you were asking where the screenshot came from, I didn't get your question

[glacial hatch]

Is it bad practice to establish a new connection to my sqlite database every time I want to use it? (I'm making a discord bot) I've seen some examples of people instead creating a connection at runtime and using that for further queries. Should I use a single connection across all queries or is the way I'm doing it now fine?

[brave bridge]

Are you familiar with transactions in SQL?

[austere portal]

Does the sqlite3 module have something like Cursor.transaction?

[brave bridge]

As far as I know, SQLite supports one transaction per connection.

So if you reuse the same connection, you basically can't use transctions -- it will be all over the place.

[glacial hatch]

no

[austere portal]

Well that sucks

[brave bridge]

Does postgres support more than 1 concurrent transaction on a connection?

[deft badge]

this is psql

jump into psql, use \x to throw on extended mode, profit

[deft badge]

have never seen this lol

but yeah it's just a psql shell on our prod Postgres with \x mode on

[austere portal]

Not sure if it supports more than one concurrent transaction

[torn sphinx]

Expanded display is on.

[torn sphinx]

thank you!

[brave bridge]

All writes in SQL happens (or, well, should happen) inside a transaction. A transaction is a safeguard to only change data atomically (i.e. all at once, not in parts). In SQLite, when you perform your first write, a transaction is opened. Then you can either commit it (to actually make the changes) or rollback it (to undo the changes).

This prevents data from being partially written, which will create inconsistencies. For example, if you want to execute 6 cursor.execute(...) calls, and the third one fails with an exception, you'll be able to undo the first two.

So if you reuse the same connection, you won't be able to create separate transactions. So just create new connections as you need them: it doesn't take a long time, about 25 microseconds on my machine. The maintainer of aiosqlite (which is what you should be using if you use async/await) also advocates for this approach from what I understand

[austere portal]

.so Does postgres support more than 1 concurrent transaction on a connection?

[humble sundial]

can i connect to a mysql database hosted on a website with python?

[glacial hatch]

Awesome! That's really great to know. Thank you so much for such a thorough response too!

[brave bridge]

Just make sure to use transactions correctly. You'd want to do this:

Command transfer_money(user1, user2, amount ):
    Inside the transaction:
        execute(
            "UPDATE balance SET money = money - :amount SET WHERE user_id = :user_id",
            {"user_id": user1.id, "amount": amount}
        )
        execute(
            "UPDATE balance SET money = money + :amount SET WHERE user_id = :user_id",
            {"user_id": user2.id, "amount": amount}
        )
        send_message(f"Transferred {amount} from {user1} to {user2}")
```, not

Command transfer_money(user1, user2, amount ):
Inside the transaction:
execute(
"UPDATE balance SET money = money - :amount SET WHERE user_id = :user_id",
{"user_id": user1.id, "amount": amount}
)
Inside the transaction:
execute(
"UPDATE balance SET money = money + :amount SET WHERE user_id = :user_id",
{"user_id": user2.id, "amount": amount}
)
send_message(f"Transferred {amount} from {user1} to {user2}")

(let's ignore checking that there's enough money)

[glacial hatch]

Thanks again. This really clears everything up!

[austere portal]

sorry i misunderstood that

[crisp arch]

Has anyone had any experience with https://keydb.dev/ before? It seems to be a drop in replacement for redis, but much higher ops/sec

[faint blade]

No, because PostgreSQL is meant to have a lot of connections at the same time.

Each connection refers to a process PostgreSQL has spawned, you should not share connections asynchronously back- and forth in your application. You should manage a connection pool and acquire, release connections from there.

[brave bridge]

right

[austere portal]

#discord-bots

[kind phoenix]

shii

mb

[austere portal]

So there is no other way for transactions other than creating a new connection?

[brave bridge]

You can start and end a transaction as many times as you want, but you can't have two different transactions on a connection at the same time

[austere portal]

Got it, thanks

[normal swan]

Oi lindas

[west pelican]

Is there a way I can display the data from my database with the column names in SQL

[harsh pulsar]

can you be more specific

in general, you can usually get column names from the description attribute of a cursor, if the library is db-api compliant

!pep 249

[delicate field]

**PEP 249 - Python Database API Specification v2.0**

Link

Status

Final

Created

29-Mar-2001

Type

Informational

[west pelican]

so like in my select query how can I get the column names along with all the other data

[harsh pulsar]

try using the description attribute on the cursor like i said above

[west pelican]

Ah thanks

[next night]

Where can I get free ElasticSearch, mongodb, redis, mysql, mqtt server ?

[high geyser]

how can I do input validation with sqlalchemy?
I am using sqlalchemy with graphene (a graphql framework)
but I need to validate input.
this does not come out of the box with graphene sadly

[inner ivy]

i have a code that is supposed to save a channel id associated with a server id and then when u use a diff command fetches that channel id based on the servers id using sqlite3, heres the code:

  await client.wait_until_ready()
  client.db = await aiosqlite.connect("suggest.db")
  await client.db.execute("CREATE TABLE IF NOT EXISTS suggest (guild_id int, channel_id int, PRIMARY KEY (guild_id, channel_id))")
  await client.db.commit()


@client.command()
async def stest(ctx):
  cur = await client.db.execute("SELECT channel_id FROM suggest WHERE guild_id = ?",(ctx.guild.id))
  res = await cur.fetchone()
  if res is None:
    suggest = 0
  else:
    suggest = res
  if suggest != 0:
    await client.get_channel(suggest).send(f"{ctx.author.mention}")
  

@client.command()
async def channelset(ctx, channel : discord.TextChannel):
  await client.db.execute("INSERT  INTO suggest (guild_id, channel_id) VALUES (?,?)", (ctx.guild.id, ctx.channel.id))
  await ctx.send("done")```

the setup works and so does the channelset

but the stest command doesnt and it errors with this:
`Ignoring exception in command stest:
Traceback (most recent call last):
File "/opt/virtualenvs/python3/lib/python3.8/site-packages/discord/ext/commands/core.py", line 85, in wrapped
ret = await coro(*args, **kwargs)
File "main.py", line 93, in stest
cur = await client.db.execute("SELECT channel_id FROM suggest WHERE guild_id = ?",(ctx.guild.id))
File "/opt/virtualenvs/python3/lib/python3.8/site-packages/aiosqlite/core.py", line 184, in execute
cursor = await self._execute(self._conn.execute, sql, parameters)
File "/opt/virtualenvs/python3/lib/python3.8/site-packages/aiosqlite/core.py", line 129, in _execute
return await future
File "/opt/virtualenvs/python3/lib/python3.8/site-packages/aiosqlite/core.py", line 102, in run
result = function()
ValueError: parameters are of unsupported type

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

Traceback (most recent call last):
File "/opt/virtualenvs/python3/lib/python3.8/site-packages/discord/ext/commands/bot.py", line 939, in invoke
await ctx.command.invoke(ctx)
File "/opt/virtualenvs/python3/lib/python3.8/site-packages/discord/ext/commands/core.py", line 863, in invoke
await injected(*ctx.args, **ctx.kwargs)
File "/opt/virtualenvs/python3/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: ValueError: parameters are of unsupported type`

and idk how to fix it.

[median wave]

cur = await client.db.execute("SELECT channel_id FROM suggest WHERE guild_id = ?",(ctx.guild.id,))

try that

also are your value types correct?

[inner ivy]

ok so when i do that, suggest becomes the (channelid,) so then it cant rly fetch the channel to send stuff in it

`Ignoring exception in command stest:
Traceback (most recent call last):
File "/opt/virtualenvs/python3/lib/python3.8/site-packages/discord/ext/commands/core.py", line 85, in wrapped
ret = await coro(*args, **kwargs)
File "main.py", line 101, in stest
await client.get_channel(int(suggest)).send(f"{ctx.author.mention}")
TypeError: int() argument must be a string, a bytes-like object or a number, not 'tuple'

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

Traceback (most recent call last):
File "/opt/virtualenvs/python3/lib/python3.8/site-packages/discord/ext/commands/bot.py", line 939, in invoke
await ctx.command.invoke(ctx)
File "/opt/virtualenvs/python3/lib/python3.8/site-packages/discord/ext/commands/core.py", line 863, in invoke
await injected(*ctx.args, **ctx.kwargs)
File "/opt/virtualenvs/python3/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: TypeError: int() argument must be a string, a bytes-like object or a number, not 'tuple'`

[median wave]

okay

check your value types

also this

@client.command()
async def channelset(ctx, channel : discord.TextChannel):
  await client.db.execute("INSERT  INTO suggest (guild_id, channel_id) VALUES (?,?)", (ctx.guild.id, ctx.channel.id))
  await ctx.send("done")

you put above to input the ctx.channel.id there, which i assume you did by mistake

and meant to do channel.id

[inner ivy]

wdym

[median wave]

wait

can you try this

await client.db.execute("INSERT  INTO suggest (guild_id, channel_id) VALUES (?,?)", (ctx.guild.id, ctx.channel.id,))

for your channelset command (remove the comma for the stest one)

afaik this should fix it

[inner ivy]

ok

[inner ivy]

`Ignoring exception in command stest:
Traceback (most recent call last):
File "/opt/virtualenvs/python3/lib/python3.8/site-packages/discord/ext/commands/core.py", line 85, in wrapped
ret = await coro(*args, **kwargs)
File "main.py", line 93, in stest
cur = await client.db.execute("SELECT channel_id FROM suggest WHERE guild_id = ?",(ctx.guild.id))
File "/opt/virtualenvs/python3/lib/python3.8/site-packages/aiosqlite/core.py", line 184, in execute
cursor = await self._execute(self._conn.execute, sql, parameters)
File "/opt/virtualenvs/python3/lib/python3.8/site-packages/aiosqlite/core.py", line 129, in _execute
return await future
File "/opt/virtualenvs/python3/lib/python3.8/site-packages/aiosqlite/core.py", line 102, in run
result = function()
ValueError: parameters are of unsupported type

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

Traceback (most recent call last):
File "/opt/virtualenvs/python3/lib/python3.8/site-packages/discord/ext/commands/bot.py", line 939, in invoke
await ctx.command.invoke(ctx)
File "/opt/virtualenvs/python3/lib/python3.8/site-packages/discord/ext/commands/core.py", line 863, in invoke
await injected(*ctx.args, **ctx.kwargs)
File "/opt/virtualenvs/python3/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: ValueError: parameters are of unsupported type`

[median wave]

hmmmm then i think the SELECT needs a comma too

yeah, it does need one

my bad

[inner ivy]

like this?
cur = await client.db.execute("SELECT, channel_id FROM suggest WHERE guild_id = ?",(ctx.guild.id))

[median wave]

noo, put if after ctx.guilld.id

so it would be ctx.guild.id,))

[inner ivy]

ah ya that makes more sense

[inner ivy]

`Ignoring exception in command stest:
Traceback (most recent call last):
File "/opt/virtualenvs/python3/lib/python3.8/site-packages/discord/ext/commands/core.py", line 85, in wrapped
ret = await coro(*args, **kwargs)
File "main.py", line 101, in stest
await client.get_channel(int(suggest)).send(f"{ctx.author.mention}")
TypeError: int() argument must be a string, a bytes-like object or a number, not 'tuple'

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

Traceback (most recent call last):
File "/opt/virtualenvs/python3/lib/python3.8/site-packages/discord/ext/commands/bot.py", line 939, in invoke
await ctx.command.invoke(ctx)
File "/opt/virtualenvs/python3/lib/python3.8/site-packages/discord/ext/commands/core.py", line 863, in invoke
await injected(*ctx.args, **ctx.kwargs)
File "/opt/virtualenvs/python3/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: TypeError: int() argument must be a string, a bytes-like object or a number, not 'tuple'`

[median wave]

bruh

[inner ivy]

and it went back to having it be (channelid,)

[median wave]

i give up

wait

oh

i see now

you are doing int() on a tuple

so need to index the value

so it would be

 await client.get_channel(int(suggest[0])).send(f"{ctx.author.mention}")

should work^

[inner ivy]

it did work but, whenever i reran the bot for some reason i would need to reuse the channelset command

[median wave]

wdym

[inner ivy]

ok so if i used channelset command

then used stest command it works now,

but

if i rerun the bot and try using stest command again

[median wave]

hmm you did not commit the INSERT

every write operation needs to be commited

[inner ivy]

how do i do that?

im guessing its something to do in the channelset command

[median wave]

yeah, after the insert you did in that command, do await client.db.commit()

[inner ivy]

ah right ig i forgot that

[median wave]

like how you did for the CREATE TABLE IF NOT EXISTS

[inner ivy]

ya i see now

thanks man u have no idea how much this helped me @median wave.

i rly needed to switch databases for lots of systems in a bot of mine, now i can ditch json, i wish i knew better when i first started tbh

[median wave]

np

[inner ivy]

there is 1 command left @median wave and thats a delete channel id command like to remove it from the database

@client.command()
async def sremove(ctx):
  await client.db.execute("DELETE channel_id FROM suggest WHERE guild_id =?", (ctx.guild.id,))
  await client.db.commit()
  await ctx.send("done")```

I thought that would work ^

but it errors with this:

`Ignoring exception in command sremove:
Traceback (most recent call last):
File "/opt/virtualenvs/python3/lib/python3.8/site-packages/discord/ext/commands/core.py", line 85, in wrapped
ret = await coro(*args, **kwargs)
File "main.py", line 105, in sremove
await client.db.execute("DELETE channel_id FROM suggest WHERE guild_id =?", (ctx.guild.id,))
File "/opt/virtualenvs/python3/lib/python3.8/site-packages/aiosqlite/core.py", line 184, in execute
cursor = await self._execute(self._conn.execute, sql, parameters)
File "/opt/virtualenvs/python3/lib/python3.8/site-packages/aiosqlite/core.py", line 129, in _execute
return await future
File "/opt/virtualenvs/python3/lib/python3.8/site-packages/aiosqlite/core.py", line 102, in run
result = function()
sqlite3.OperationalError: near "channel_id": syntax error

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

Traceback (most recent call last):
File "/opt/virtualenvs/python3/lib/python3.8/site-packages/discord/ext/commands/bot.py", line 939, in invoke
await ctx.command.invoke(ctx)
File "/opt/virtualenvs/python3/lib/python3.8/site-packages/discord/ext/commands/core.py", line 863, in invoke
await injected(*ctx.args, **ctx.kwargs)
File "/opt/virtualenvs/python3/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: OperationalError: near "channel_id": syntax error`

[median wave]

hmm

yeah that is wrong

"DELETE FROM (table) WHERE (row) = ?", ((the row to delete from ))```

[inner ivy]

like this?
await client.db.execute("DELETE FROM suggest WHERE guild_id = ?", (ctx.guild.id,))

or at least i think its like that bc that seemed to work

[median wave]

yep

like that

[inner ivy]

thanks man

[fierce sand]

Wait i just relized

Pixel phone have unlimited G-drive storage

aBUsE

[faint blade]

No? Didn't they revert that?

[crisp arch]

Yea, it was axed on June 1st

15GB limit now iirc

[dense oar]

I'm having trouble getting profiling to work in MongoDB. What I'm doing is use test and then db.setProfilingLevel(2), after that I check the setting with db.getProfilingLevel() which returns:

{
  was: 2,
  slowms: 500,
  sampleRate: 1,
  ok: 1,
  '$clusterTime': {
    clusterTime: Timestamp(1, 1628180999),
    signature: {
      hash: Binary(Buffer.from("b42dbb3f30979f696ece17acb4aef1be5a3b5188", "hex"), 0),
      keyId: Long("6992299061030158341")
    }
  },
  operationTime: Timestamp(1, 1628180999)
}

Then I do some random queries and inserts on a collection in the test db, then check the profiling logs with db.system.profile.find_one() and it returns None. The system.profile collection doesn't exist

What am I doing wrong?

[torn sphinx]

https://www.python.org/dev/peps/pep-0249/ python thought of everything...

[dense oar]

Is that for me?

[torn sphinx]

no sorry

[tight hemlock]

is there someone willing to help me with a python fueled sql based question in burrito? ❤️

[torn sphinx]

Actually this is normal for other languages as well not just python

[gleaming cipher]

Hey so I'm completely new to python databases, what exactly do you guys use to make good databases?

[torn sphinx]

What do you want a database for?

There’s many so what kind you need depends on what you want to do

[gleaming cipher]

I mean really just storing and accessing data that my code creates from user input

[ionic pecan]

@gleaming cipher generally you don't want to build your own database, as it's a project in itself. if you want to store and access data in your code, your best bet is going with an SQL database, such as the built-in sqlite of Python, or PostgreSQL if you want to start out a little bit bigger

[austere portal]

You can use the built in sqlite3 module to interact with sqlite databases

[solemn root]

Hi, I'm using the built-in Sqlite3 module, and I'm running into an issue.

Line 109 is prompting this error, but I don't know why:

  File "main.py", line 109, in <module>
    sql_command("""SELECT * FROM discord_Web;""")
TypeError: 'str' object is not callable```

[gleaming cipher]

Okay thank you I'll look into that

[prime falcon]

how do i setup a search system where it checks if the message reacted to is the same as one of the ids in th DB and then check if the emoji that was reacted matches one of the emojis tied to that message id for my reaction roles module?

[austere portal]

sql_command is a string, you can't call it. I think you meant crsr.execute("SELECT * FROM discord_web;")

[torn sphinx]

hey, can someone help me do a query with 3 tables with sqlalchemy?

[solemn root]

That was the issue. Now when I run:

db.commit()
rows = crsr.fetchall()
for row in rows:
    print(f"row{[0]} row{[1]} row{[2]} row{[3]} row{[4]} row{[5]} row{[6]}")
db.close()

the print statement never runs because rowsis empty for some reason. Why is rows empty even though there are several data entries in the table its fetching from?

[torn sphinx]

Yeah it shouldnt run

[austere portal]

You need to call db.commit, eg db.commit()

Show your SQL query also

[torn sphinx]

You don’t even have the correct syntax with the brackets so not sure why you would expect it to run

[austere portal]

And that

[solemn root]

I do have db.commit(), must've been a typo when translating it over here.

[solemn root]

Could you elaborate? Aren't you able to print the index of a row by the element number and print that as an f-string?

Like:
print(f"row{[0]}")

[brazen charm]

that would print row[0]

[solemn root]

I see my mistake... I was doing print(f"row{[0]}") and not print(f"{row[0]}"). Suttle, but simple mistake.

[tawdry pawn]

anyone had error pymongo.errors.ServerSelectionTimeoutError: localhost:27017: [WinError 10061] No connection could be made because the target machine actively refused it using pymongo with dpy

@commands.Cog.listener()
    async def on_ready(self):
        await self.client.change_presence(activity=discord.Activity(type=2, name='$help'))
        channel = self.client.get_channel(872935199812513862)
        try:
          await channel.edit(name="Online-🟢")
          print(Fore.CYAN + "[Status] Updated Status Channel." + Fore.RESET)
        except:
          print(Fore.RED + "[Status] Failed to Update Status Channel." + Fore.RESET)
          pass
        print(Fore.CYAN + '\n=======================\n{0.user} has connected to Discord\'s API\n'.format(self.client) + Fore.RESET)
        guilds = self.client.guilds
        dbguilds = []
        for item in db.find():
            try:
                dbguilds.append(item['guild_id'])
            except:
                pass
        for guild in guilds:
            if len(dbguilds) == 0 or guild.id not in dbguilds:
              try:
                db.insert_one({
                  "guild_id": guild.id,
                  "prefix": [
                    os.environ.get('default_prefix'),
                  ],
                  "premium": "False",
                })
                print(f"[{Fore.GREEN}+{Fore.RESET}] {Fore.GREEN}Added Server To Database. {Fore.RESET}({Fore.GREEN}{guild.id}{Fore.RESET})")
              except:
                print(f"[{Fore.RED}+{Fore.RESET}] {Fore.RED}Failed To Add Server To Database. {Fore.RESET}({Fore.RED}{guild.id}{Fore.RESET})")```

firing on `for item in db.find():`

[thorny field]

How can I edit a row(that I got buy using fetchone()) as an tuple in a SQLite database?
Code:

# Buy stock command
@bot.command()
async def buystock(ctx, arg1, arg2):
    user_id = str(ctx.message.author.id)
    guild_id = str(ctx.message.guild.id)

    cursor.execute(
        "SELECT * FROM main WHERE user=? and guild=?",
        [user_id, guild_id]
    )

    do_query = """SELECT * from main"""
    cursor.execute(do_query)

    valueCheck = cursor.fetchone()

    if (valueCheck is None):
        await ctx.send("You need to be registered in this server to use this command.")
    elif (valueCheck is not None):
        if (int(arg2) < 0):
            await ctx.send("You can't buy negative shares!")
        elif (int(arg2) == 0):
            await ctx.send("You can't buy 0 shares!")

[hazy mango]

To edit the row within the database you'd use an UPDATE statement

UPDATE table SET col=val, col=val, col=val WHERE xxxx

With the WHERE xxxx being optional

[thorny field]

ohh cool, can u send me a link or a tutorial? i have to use variables in the update statement btw

[hazy mango]

https://www.w3schools.com/sql/sql_update.asp seems good

[thorny field]

ok, ty 🙂

[candid night]

idk if this is possible but need some help with mongo, I have the following document, and I want to make it so I can use $addToSet to add a value to one of the items in votes, but remove that item from all the other items in votes but have no idea how

{
  _id: '872952643117518909',
  questions: [
    { question: 'a', number: 1, dropDownInfo: [Object] },
    { question: 'b', number: 2, dropDownInfo: [Object] },
    { question: 'c', number: 3, dropDownInfo: [Object] }
  ],
  votes: {
    '1': [ '619284841187246090', '662697094104219678' ],
    '2': [ '619284841187246090', '662697094104219678' ],
    '3': [ '662697094104219678', '619284841187246090' ]
  },
  question: 'abc',
  timestamp: 1628198528903,
  finished: false,
  channel: '812038854302892064'
}

[quiet birch]

in mongodb can I get all document's values of a key that is of type array

[marsh ivy]

can any one tell me how to create store procedure from python?

[fallen raptor]

Hey guys! I have a question about Django and setting up models...

This is probably pretty simple but I am having a mental block so help is appreciated. I have a discussion app which contains a Message model. I want to relate the Message to several other models (e.g. Video, Note, etc.).

I don't feel that it is right to add a field for each model onto the Message model but I want the messages to "cascade delete" when the parent object is deleted (e.g. if Video is deleted all related Messages should be deleted as well).

How can I accomplish this?

class Message(models.Model):
    """
    Stores information pertaining to a message.
    """
    sender = models.ForeignKey(
        Employee,
        on_delete=models.CASCADE,
        null=True,
        related_name='messages'
    )
    message = models.TextField()
    parent = models.ForeignKey(
        'self',
        related_name='children',
        null=True,
        blank=True,
        on_delete=models.SET_NULL
    )
    created_on = models.DateTimeField(auto_now_add=True)

    # this seems wrong
    # video = models.FK
    # note = models.FK
    # etc...

I am using django-tenant-schemas so I am concerned that generic fks would mess up my db on implementation.. Is there a scalable way to do this without generic fks?

[chrome raven]

#bot-commands

[green iris]

I am working with Django and am trying to migrate my User data from Postgres into another Django project running Postgres. Am I correct in thinking that datadump on auth would be the way to go for this? I want to transfer everything, included the passwords and permissions.

[vernal tide]

Hi, I've apt installed a postgres and pgadmin4. Now how do I connect the one to the other?

[next sun]

guys

I am struggling a big time to create an ssh tunnel to access a remote postgresql database through the command line

has anyone ever done that here?

[torn sphinx]

How can I find the most common number in a column without using LIMIT?

[prisma girder]

What do you mean? It depends what do you want to limit

[torn sphinx]

How do I find the correct names of people who share the same PANUM with a specific ACNUM For example.
I have two tables, one table consists of PANUM Paper Numbers that isn't unique to individuals and multiple People can share the same PANUM but every individual has a unique ACNUM that is specific to them. I want to find the names of the People who share the same PANUM of the ACNUM 202 How can I write this out? The names of the ACNUM can be found in another table and both ACNUM is found in both tables. I hope that doesn't sound complicated

SELECT 
    ACADEMIC.ACNUM,
    GIVENAME,
    FAMNAME,
    PANUM
FROM
    ACADEMIC,
    AUTHOR
WHERE 
    AUTHOR.ACNUM = 202``` This is what I got so far but it displays everything

[harsh pulsar]

@torn sphinx you aren't specifying any kind of condition for joining the academic and author tables. maybe you want this?

SELECT 
    ACADEMIC.ACNUM,
    GIVENAME,
    FAMNAME,
    PANUM
FROM
    ACADEMIC,
    AUTHOR
WHERE 
    ACADEMIC.ACNUM = AUTHOR.ACNUM AND
    AUTHOR.ACNUM = 202

or its equivalent

SELECT 
    ACADEMIC.ACNUM,
    GIVENAME,
    FAMNAME,
    PANUM
FROM
    ACADEMIC
    JOIN AUTHOR ON AUTHOR.ACNUM = ACADEMIC.ACNUM
WHERE 
    AUTHOR.ACNUM = 202

usually JOIN and INNER JOIN are synonyms

or maybe you want something more complicated..

[torn sphinx]

Hmm let me see rq

[harsh pulsar]

it helps if you clarify which table you want what data from

[torn sphinx]

one moment plss and thank you

[harsh pulsar]

if you can upload example data to https://www.db-fiddle.com/ and provide example output, it'd help a lot

[torn sphinx]

site doesnt work

[harsh pulsar]

fixed

[torn sphinx]

nvm

[torn sphinx]

how do i upload my .db to the site

[harsh pulsar]

just example data would be fine. it's easier to see what's happening in that case anyway

you have to write CREATE TABLE and INSERT statements on the left panel

then you can write queries on the right panel

[torn sphinx]

oh because I got the data from my Uni, but I will try my best

[torn sphinx]

So this got the Name and etc. of the ACNUM 202 What I want is the names of those who share the same PANUM with ACNUM = 202 PANUM only exists within the table AUTHOR and ACNUM exists within both ACADAMIC and AUTHOR Names all exist within ACADEMIC

[harsh pulsar]

ok, it helps to know which columns are in which tables

[delicate field]

Hey @torn sphinx!

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

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

[torn sphinx]

Whoops

I tried to upload the .db

[harsh pulsar]

that's ok

i think i can help

first you need to get the AUTHOR row with your desired ACNUM, right?

SELECT
  AUTHOR.ACNUM,
  AUTHOR.PANUM
FROM
  AUTHOR
WHERE
  AUTHOR.ACNUM = 202

[torn sphinx]

So
Table AUTHOR:
PANUM ACNUM
Table ACADEMIC:
ACNUM DEPTNUM FAMNAME GIVENAME INITALS TITLE

[harsh pulsar]

does this work? the condition is literally "where AUTHOR.ACNUM = 202 and where AUTHOR.PANUM is the same as ACADEMIC.PANUM"

SELECT
  AUTHOR.ACNUM,
  AUTHOR.PANUM,
  ACADEMIC.GIVENNAME,
FROM
  AUTHOR,
  ACADEMIC
WHERE
  AUTHOR.ACNUM = 202 AND
  AUTHOR.PANUM = ACADEMIC.PANUM

[torn sphinx]

I am trying to get the FAMNAME GIVENAME and ACNUM From ACADEMIC but only those who have the same PANUM as the individual with the ACNUM 202 since ACNUM are unique and PANUM are not

[torn sphinx]

that gives an error

hold on

it says no such column

Spelling looks right

Yeah strange

it doesn't seem to work

https://ufile.io/lx2ytsol

thats the database file I am using

[harsh pulsar]

@torn sphinx you posted GIVENAME, did you actually mean GIVENAME or GIVENNAME?

[torn sphinx]

I figured wat was wrong, you put ACADEMIC.PANUM but PANUM Doesn't exist within ACAMEDIC

I think it worked but just a slight problem

[harsh pulsar]

oh, i thought it was in both

[torn sphinx]

let me see if its right

oh yeah big problem

[harsh pulsar]

oh... this is totally different then

[torn sphinx]

it just printed out PANUM with every name like 10times

lol

almost 8k

[harsh pulsar]

ok, well we agree that this is the right way to get the desired author, right?

SELECT
  AUTHOR.ACNUM,
  AUTHOR.PANUM
FROM
  AUTHOR
WHERE
  AUTHOR.ACNUM = 202

[torn sphinx]

Find the academic number and full name of academics who have written one or more papers with
Adam Kilg. Do not include Adam Kilg in the results. Do not include duplicates. Sort the results by surname and then first name in alphabetical order.

[torn sphinx]

Yes

[harsh pulsar]

...is this homework?

[torn sphinx]

practical exercises

[harsh pulsar]

well then i certainly can't do your homework for you

but i can suggest that you might need to join the table to itself

[torn sphinx]

its not homework

we dont get graded on this

just to practice

how would I join the tables?

with JOIN?

[harsh pulsar]

well again, start with the table that you know has the desired PANUM in it

you can do a nested query

[torn sphinx]

hmm

so like

JOIN ACADEMIC ON AUTHOR.ACNUM = ACADEMIC.ACNUM

This is more complicated then I thought

SELECT
  ACADEMIC.ACNUM,
  AUTHOR.PANUM,
  ACADEMIC.GIVENAME,
  ACADEMIC.FAMNAME
FROM
  ACADEMIC
INNER JOIN AUTHOR ON AUTHOR.ACNUM = ACADEMIC.ACNUM
WHERE
  AUTHOR.ACNUM = 202```

this produces all name of the ACNUM 202 Though I need it to produce those who share the same PANUM with ACNUM = 202

so close yet so far

[torn sphinx]

Okay I got it working but how do I filter out like name?

or a specific number?

[torn sphinx]

today we mess with psycopg2... i wonder why they called their libpq wrapper psycho lol

[proven arrow]

haha

[austere portal]

https://dev.to/glasnt/why-is-it-called-psycopg2-4efj
lol

[torn sphinx]

lmao

[ripe rapids]

db = await aiosqlite.connect(...)
cursor = await db.execute('SELECT * FROM some_table')
row = await cursor.fetchone()
rows = await cursor.fetchall()
await cursor.close()
await db.close()
``` how do i create a table tho

and to execute how do i execute x

[remote plinth]

await db.execute(
  """CREATE TABLE some_table (
     column_1 datatype
  )""")

and commit() after it

[ripe rapids]

some table being optional name?

[remote plinth]

yeh name it whatever you want

there's also a gui for it

[ripe rapids]

alright tysm

[remote plinth]

https://sqlitebrowser.org/dl/ @ripe rapids

[ripe rapids]

i have to download that for sqlite?

im so confused can i dm you after i eat dinner @remote plinth

[remote plinth]

no, but it can help you creating table/managing stuff easily

[remote plinth]

uh sorry i don't allow dms

[ripe rapids]

oh ok ill ping you here after im done

[ripe rapids]

@remote plinth how would i start a database connect bc db = await would raise cant be used outside a async function

[remote plinth]

!d asyncio.loop.run_until_complete

[delicate field]

asyncio.loop.run\_until\_complete

loop.run_until_complete(future)```
Run until the *future* (an instance of [`Future`](https://docs.python.org/3/library/asyncio-future.html#asyncio.Future "asyncio.Future")) has completed.

If the argument is a [coroutine object](https://docs.python.org/3/library/asyncio-task.html#coroutine) it is implicitly scheduled to run as a [`asyncio.Task`](https://docs.python.org/3/library/asyncio-task.html#asyncio.Task "asyncio.Task").

Return the Future’s result or raise its exception.

[remote plinth]

do you want a global db connection?

i mean add it as attribute to Bot

?? @ripe rapids

[ripe rapids]

yes that would be alright

[remote plinth]

Bot already have a loop attribute then db = bot.loop.run_until_complete(aiosqlite.connect())

[ripe rapids]

so db = that

[remote plinth]

yeh

[ripe rapids]

ok ok

[frank pollen]

Hello, just a little dumb question (which may be seems dumb for you, I'm sorry), I'm a new french developer who wants to develop a game in an "Advance Wars" or "Fire Emblem" style with nothing but IDLE, DBBrowser and Aseprite
To create units, I decided to make a class "unit" with every stats (attack, defense, HP, etc...), and to put different type of units, and their stats on a database
To put the stats from the database to the object, I want to create a method that will take the id given at the object's creation and use it to give to the object the stats of the unit I (or the player) want

[ripe rapids]

but wouldn't it say bot is not defined

dont i have to import asyncio then do it?

[remote plinth]

you subclass it right?

[ripe rapids]

instead of bot is i want it not.db

yes

[remote plinth]

then self.loop

[ripe rapids]

ok

ima try

@remote plinth and 1 more thing where would i create the table

when a command is ran or

[remote plinth]

await db.execute("create table...")
await db.commit()
``` just ^^

anywhere

i prefer making tables in gui apps

[remote plinth]

like ^^

[ripe rapids]

alright alright

[remote plinth]

no, just make one table

for example a table for tempbans and another for prefixes etc ...

[dim sluice]

How can I get a mongodb latency with pymongo?

[ripe rapids]

how would i create only 1

run until complete?

[remote plinth]

just execute a query like every other query you do

do you have exp with sql?

[ripe rapids]

just started learning

@remote plinth can i ask something?

im so sorry for disturbing

[dim sluice]

How can I get a mongodb latency with pymongo?

[grim vault]

...
cur.execute("SELECT vendor_id, vendor_name FROM vendors ORDER BY vendor_name")
print("The number of parts: ", cur.rowcount)
print(cur.description)
for row in cur:
    print(row)
...```

[torn sphinx]

awesome thank you

[grim vault]

The print(cur.description) may need some work.

[remote plinth]

hmm?

i was offline

[ripe rapids]

so how would i insert something

[remote plinth]

await db.execute("INSERT INTO my_table (column_1, column_2) VALUES (?, ?)", [column_1_value, column_2_value])

.execute(query, args_here)

[torn sphinx]

colnames = [desc[0] for desc in cur.description] did it

[remote plinth]

each arg will replace the ? with it

[ripe rapids]

the column 1 and 2 are the text you put in right the data

[remote plinth]

i don't understand

[ripe rapids]

where do i put the data i store

[remote plinth]

in the column_1/2_value

in the args

[remote plinth]

^^

you need sql exp before it ig

i learnt here https://sololearn.com

[ripe rapids]

thats a list so can i do lists too

[ripe rapids]

ill learn thk u!

[remote plinth]

there's a (?, ?) in the query it will get replaced with the values in the list in the args argument

[remote plinth]

oke

[ripe rapids]

it workedd thk u sm

and

does rows = await cursor.fetchall() return a list @remote plinth

[tawdry pawn]

has any1 had this error? pymongo.errors.ServerSelectionTimeoutError: localhost:27017: [WinError 10061] No connection could be made because the target machine actively refused it, Timeout: 30s

internet has no answers for me, using dpy pymongo

[remote plinth]

list of tuples

each tuple is a row

[ripe rapids]

yep just what i needed thk u

[ripe rapids]

@remote plinth anything wrong

    @commands.command()
    @commands.is_owner()
    async def blacklist(self, ctx, m: discord.Member):
        cursor = await self.bot.db.execute("INSERT INTO blacklist (column_1) VALUES (?)", [m.id])
        await self.bot.db.commit()

        await ctx.send(embed = discord.Embed(description = str(m) + "has been blacklisted.", color = self.bot.theme))

anyways ima sleep

ping me with answer ima start learning sql with ur link that u gave me

[thorny field]

How can I use the update statement on the userdbCheck variable?
Code:

# Buy stock command
@bot.command()
async def buystock(ctx, arg1, arg2):
    user_id = str(ctx.message.author.id)
    guild_id = str(ctx.message.guild.id)

    cursor.execute(
        "SELECT * FROM main WHERE user=? and guild=?",
        [user_id, guild_id]
    )

    do_query = """SELECT * from main"""
    cursor.execute(do_query)

    userdbCheck = cursor.fetchone()

    if (userdbCheck is None):
        await ctx.send("You need to be registered in this server to use this command.")
    elif (userdbCheck is not None):
        if (int(arg2) < 0):
            await ctx.send("You can't buy negative shares!")
        elif (int(arg2) == 0):
            await ctx.send("You can't buy 0 shares!")

[steep ingot]

Would userdbcheck = userdbcheck + 5 or f’{userdbcheck} blah blah’ work? You can do something similar if it’s a dictionary. userdbcheck[‘item to update’] = blah

Not familiar with that db but I hope you can figure it out from there

[thorny field]

Yea, that’s what I want, but idk how to do that

Everyone says use the update system

[steep ingot]

Oh I see, what database are you using?

[thorny field]

SQLite

[steep ingot]

Yeah, never touched it. Docs and find the update function and it’s formatting. Pass a variable that is the data you want. That var can contain the original value if need be.

https://www.sqlitetutorial.net/sqlite-update/

Might be useful.

[lost echo]

Is there any way by which I can emit an event in python socket.io server when a new row is added to a mysql table?

[sturdy narwhal]

i wanted to know how i can use MongoDB

like for my master plan i have

So it can save Channel ID

for chatbot

[torn sphinx]

how to install postgress on ubuntu?

[sturdy narwhal]

https://www.postgresql.org/download/linux/ubuntu/

[remote plinth]

do column_1 exists?

and the blacklist table

[ripe rapids]

yes

[remote plinth]

um idk

it looks valid tho

error?

[uncut egret]

    c.execute("SELECT ID,guildID,name FROM players WHERE name REGEXP '(^|\D{1})536(\D{1}|$)' AND GUILDID = 760366380901728257")
sqlite3.OperationalError: no such function: REGEXP```

how do i setup a function in python for REGEXP
im currently using sqlite3 to pull data
it works in sqlite3 just not when i use it in python

nvm got it

[ripe rapids]

nope it just doesnt even insert the data

when i made it print the list it just printed

[]

[grim zephyr]

Someone help

I am getting a error

database is locked 

[thorny field]

TY, i will definitely look at it

[thorny field]

ok, what database do u have?

[grim zephyr]

aiosqllite

3

[thorny field]

ok, are u using an app to browse the database? like this one:

[grim zephyr]

No

[thorny field]

ohhh

[grim zephyr]

It's for my discord bot

[thorny field]

ik

[grim zephyr]

But not working

[thorny field]

ok....

[grim zephyr]

Showing error

[thorny field]

are using a app to browse the database?

[grim zephyr]

This

[thorny field]

ik

i have faced it too

[grim zephyr]

No

[thorny field]

damn

[grim zephyr]

I am not using any

[thorny field]

code?

[thorny field]

wait, how do u see the contents of the database?

[grim zephyr]

aiosqllite3

Inserts and fetches

[thorny field]

ok

hmmm

[grim zephyr]

But I don't use any app to see

[thorny field]

did u face this error before?

[grim zephyr]

No

Never

[thorny field]

so just rn?

[grim zephyr]

Yep

[thorny field]

oki doki

let me see smth

[grim zephyr]

Can u tell me why is this error occuring

I mean What's the cause of it

[thorny field]

ok for me, i use a app to browse the database. when i run the app and open the database it locks the db. so i have to keep the app closed and then run the code

mostly smth is using the db

idk

[grim zephyr]

Yep

[thorny field]

are using a hosting service?

[grim zephyr]

@thorny field

[thorny field]

yea

[grim zephyr]

A vps

[thorny field]

okkkk

wait wait

[grim zephyr]

Ok

Waiting

[thorny field]

the bot is running right?

[grim zephyr]

Waiting

[grim zephyr]

Yep

[thorny field]

bcz of the hosting service?

right....???

[grim zephyr]

Maybe

Don't know

[thorny field]

and u are trying to run the code from ur computer?

[grim zephyr]

No

[thorny field]

ohh

so mostly hosting service probs?

like u get the error from the hosting service?

[grim zephyr]

No vps

[thorny field]

vps?

[grim zephyr]

SQL error

[thorny field]

hmm, send me a screen shot

@grim zephyr here a stackoverflow problem regarding ur problem: https://stackoverflow.com/questions/151026/how-do-i-unlock-a-sqlite-database

[grim zephyr]

@thorny field

[thorny field]

yee

[grim zephyr]

Well shall I open the db once and close it once

[thorny field]

wdym?

[thorny field]

ohh, yea

like stop the code and run it again

[grim zephyr]

I have connected it in all the codes but didn't close

[thorny field]

hmm

smth is definitely using it

what is ur ide?

[grim zephyr]

I got it

[thorny field]

how?

@grim zephyr what was the prob, good job!

[somber breach]

error: asyncpg.exceptions.InvalidCatalogNameError: database "test" does not exist

but i already made a db with that name
connection:

async def connect_database(self):
        self.main_db = await asyncpg.create_pool(
            database="test",
            user="postgres",
            password=self.secrets["POSTGRES"],
            host="1234",
        )

Alternately it connected when i supplied the db name to postgres the default one
but when im running queries ie:
SELECT * FROM guild
it throws this error: no table "guild" exists

These are all the tables inside postgres (the db)
u could clearly see theres a table called guild

[manic dust]

is anyone able to give me a hand with a django rest serializer please? im trying to use simple_history to get historical data of models into my frontend but having some issues not being able to pass in the correct methods

[ornate gust]

what does sample_history actually doing? i'm looking into the documentation, but can you give a short explanation about when this package can be used?

[tawdry pawn]

pymongo.errors.ServerSelectionTimeoutError: localhost:27017: [WinError 10061] No connection could be made because the target machine actively refused it, Timeout: 30s, Topology Description: <TopologyDescription id: 610ed726fc175b71b0a23e2a, topology_type: Single, servers: [<ServerDescription ('localhost', 27017) server_type: Unknown, rtt: None, error=AutoReconnect('localhost:27017: [WinError 10061] No connection could be made because the target machine actively refused it')>]>

anyone encounter this error b4? using dpy and pymongo; nothing on the interent relating to dpy,

[long dome]

I'm having an issue with discord.py and asynpc:

async def db_conn():
    bot.connection = await asyncpg.create_pool(
    host="localhost",
    user="postgres",
    password="xxxx",
    max_inactive_connection_lifetime=10)


async def getuserprofile(user):
    conn = await bot.connection.acquire()
    result = await conn.fetch("SELECT * FROM main WHERE discordid = $1", str(user))
    return result

result is returning [] and empty list for some reason instead of the proper result. print(user) shows the right userid, everything works fine if I use individual connections although thats 5x slower than pools with asyncpg

[faint blade]

Can you show the code that works?

[torn sphinx]

I'm probably still going to make this for fun.. but just so I know is there a package that formats sql queries from psycopg2 to look like they do in psql? like i know i can do rowcount and fetchmany and description but it's really ugly and not in a pretty table like psql

[brazen charm]

Not that im aware of but should be possible with a couple lines of string formatting and loops

[torn sphinx]

also different data types have different indentation 😫

[pine flax]

db.execute("SELECT UserId FROM users WHERE UserId = \"%s\"", (userid)) is giving me error ValueError: Could not process parameters

can someone help me with that pls

[pine flax]

but db.execute("SELECT UserId FROM users WHERE UserId = \"%s\""%(userid)) is working

[storm mauve]

you must use (userid,) to make it a tuple, otherwise it's just the same as userid alone
also, use '' instead of "" in SQL - though I don't think that you should need of it at all there?

[uncut egret]

hi there

[gleaming cipher]

How can i view the data tables in my sqlite3 database?

[storm mauve]

https://www.sqlite.org/schematab.html
SELECT * FROM sqlite_schema

[gusty mulch]

Is it possible to make a bigint[] into a non list bigint in postgres? I'm currently storing a list of channels but the list seems to be having a problem so I'm going to separate the list items into separate entries in my table rather than making them a bigint[]

[austere portal]

You can run an alter statement and change the data type

ALTER TABLE table_name
ALTER COLUMN column_name1 SET DATA TYPE new_data_type```

[gusty mulch]

thanks

[austere portal]

And don't forget the docs
https://www.postgresql.org/docs/current/sql-altertable.html

[gusty mulch]

So I tried sql ALTER TABLE data ALTER COLUMN countchannel SET DATA TYPE bigintand gotsql ERROR: column "countchannel" cannot be cast automatically to type bigint HINT: You might need to specify "USING countchannel::bigint". SQL state: 42804

[austere portal]

try doing ```sql
ALTER TABLE table_name
ALTER COLUMN col_name TYPE current_data_type
USING col_name::new_data_type;

[hot grove]

Hey, im trying to select manageable amounts of data from my database so it can be displayed on pages, in groups of 10. However, im getting duplicates, and the offset isnt doing what it should. Anyone seen this before, is there a known reason for this to happen?

SELECT USER, Level FROM Game.Players ORDER BY Level DESC LIMIT 20, 10;

After looking at the whole player list, instead of starting the the bottom of Level 14 and leaving off where it started, its starting at the top of the offset. The userid is in position 26, even though the offset is set to 30??

https://cdn.no1irishstig.co.uk/82mt1.png [20 Offset]
https://cdn.no1irishstig.co.uk/v0gtz.png [30 Offset]

As you can see the data isnt even what it should be

[pseudo flame]

what is going on here?

why wont my data update?

after the update statement, it says it matched one but didnt update one

despite those two IDs being clearly different

[earnest cradle]

i want to install greenplum on my job, but im dumb (currently using mariadb and suck at speed). is it worth the pain? inb4: decided to hire devops for this kind of jobs

@dark oxide what r u conding?

[rustic laurel]

with pymongo's .find method can you provide multiple queries??

[midnight kraken]

Hihi is there someone that can help me with some things related to microsoft access pleasee?

[thorny field]

Hii! So I am using a SQLite database for my discord bot. In one of the commands I have used the update system with some variables. I am getting an error which idk how to solve
Code:

# Buy stock command
@bot.command()
async def buystock(ctx, arg1, arg2):
    user_id = str(ctx.message.author.id)
    guild_id = str(ctx.message.guild.id)

    cursor.execute(
        "SELECT * FROM main WHERE user=? and guild=?",
        [user_id, guild_id]
    )

    do_query = """SELECT * from main"""
    cursor.execute(do_query)

    userdbCheck = cursor.fetchone()

    if (userdbCheck is None):
        await ctx.send("You need to be registered in this server to use this command.")
    elif (userdbCheck is not None):
        if (int(arg2) < 0):
            await ctx.send("You can't buy negative shares!")
        elif (int(arg2) == 0):
            await ctx.send("You can't buy 0 shares!")

        for name, column in zip(["mango", "megasoft", "elecar", "soulairlines", "givida", "planetmoney", "wallmall", "chickdonald's", "gamestart", "fuber"], ["mango", "megas", "ele", "soul", "givida", "pm", "wm", "cd", "gs", "f"]):
            if (str(arg1.lower()) == name):
                cursor.execute("""
                    UPDATE main
                    SET ? = ?
                    WHERE 
                        user = ?;
                        guild = ?;
                """, [column, str(arg2), user_id, guild_id]
                )
                db.commit()

Error:

Ignoring exception in on_command_error
Traceback (most recent call last):
  File "C:\Users\becin\AppData\Local\Programs\Python\Python39\lib\site-packages\discord\ext\commands\core.py", line 85, in wrapped
    ret = await coro(*args, **kwargs)
  File "c:\Users\becin\Documents\computer science\Python\Discord stocks game\bot.py", line 313, in buystock
    cursor.execute("""
sqlite3.OperationalError: near "?": syntax error

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

Traceback (most recent call last):
  File "C:\Users\becin\AppData\Local\Programs\Python\Python39\lib\site-packages\discord\client.py", line 343, in _run_event
    await coro(*args, **kwargs)
  File "c:\Users\becin\Documents\computer science\Python\Discord stocks game\bot.py", line 87, in on_command_error
    raise error
  File "C:\Users\becin\AppData\Local\Programs\Python\Python39\lib\site-packages\discord\ext\commands\bot.py", line 939, in invoke
    await ctx.command.invoke(ctx)
  File "C:\Users\becin\AppData\Local\Programs\Python\Python39\lib\site-packages\discord\ext\commands\core.py", line 863, in invoke
    await injected(*ctx.args, **ctx.kwargs)
  File "C:\Users\becin\AppData\Local\Programs\Python\Python39\lib\site-packages\discord\ext\commands\core.py", line 94, in wrapped
    raise CommandInvokeError(exc) from exc
discord.ext.commands.errors.CommandInvokeError: Command raised an exception: OperationalError: near "?": syntax error

Can any help me solve it?

[whole widget]

SET ? = ? You cannot parameterize the column name, just the value being assigned.

[thorny field]

but i need to set the column name based on the i variable of the for loop

@whole widget can u help me?

[whole widget]

The library will not do that for column name or table name. Just the values. There's too much risk for injection at those points, if I understand the reason why.

[thorny field]

ig, but i have to set it based on the for loop, so what do i do?

[grim zephyr]

Guys I have made a command which is using a db but it always work for the first time and the second time I use it, it gives an error telling "database is locked"

[whole widget]

You are pulling the entire row on the first query. I'd work with that to add the arg2 value to the correct column and then run an UPDATE which updates then entire table row with the columns explicitly named.

[thorny field]

ohh, how do i do that? sorry i am a noob

[grim zephyr]

Help!

[thorny field]

delete the db, and try again?

[grim zephyr]

I tried many times

[thorny field]

oooo

stackoverflow?

its the last resort

[grim zephyr]

Nothing from stackoverflow

[thorny field]

i meant post ur question

[grim zephyr]

I want to ask here

It's better than waiting

For about many days

[whole widget]

Unfortunately I need to head out this second. userdbCheck is all of the columns of your table's row in the order they were created. You want to loop through that and the names (like you have already). Instead of updating the database though, you want to create a new list of values and when the name matches arg2 make sure to change that value.
That gets you a list of the updated table row which you can then use UPDATE to send back to the database and, since you're updating eveything, you won't have to use a variable for a column name.

[grim zephyr]

Can I have some help

[thorny field]

ok, the main confusion is how to update the row using a new list

[thorny field]

i asked a question got answered in a day

[grim zephyr]

Nice

[thorny field]

u should

since A LOT of devs use it

and experts are there

[grim zephyr]

Maybe I will try it

[thorny field]

stackoverflow is pretty much google for devs

[thorny field]

u should, there is nothing loose, right?

[grim zephyr]

Well yeah I will try

[grim zephyr]

Still this is making me mad

Why working for the first and not working for the second

[torn sphinx]

yes. you didn't commit the database

can you send your code?

[grim zephyr]

Ok wait a sec

[thorny field]

yea i had similar problem. it was bcz i used an app to see my db and i ran the code while the app was open. but it fixed it 😅

[torn sphinx]

Cool

[austere portal]

To fix the data base is locked error you need to close all connections to the db

[torn sphinx]

commit and close perhaps

[austere portal]

Is there a journal file?

[torn sphinx]

But won't just keeping a single connection to the DB and commiting it every time data is edited work? @austere portal

[grim zephyr]

Yep

[austere portal]

The data base doesn't get locked when a connection is made

When you insert data sqlite locks the database so when that is happening you can't do anything else until the database is unlocked

https://stackoverflow.com/a/3172950

[delicate field]

Hey @grim zephyr!

It looks like you tried to attach a Python file - please use a code-pasting service such as https://paste.pythondiscord.com

[torn sphinx]

Ah, yeah

If you don't have already, btw, make sure you have a single connection with your database within your code @grim zephyr

Instead of connecting to it everytime you need to use it

[grim zephyr]

I am using it multiple times but have a single connection

[torn sphinx]

So you connect to it everytime you need?

[grim zephyr]

No

[torn sphinx]

Oh ah, I get you

Nevermind

Alright

[austere portal]

It's not a error in your code, it's just the limitations of sqlite

[grim zephyr]

!paste

[delicate field]

Pasting large amounts of code

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

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

[grim zephyr]

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

@torn sphinx @torn sphinx

[torn sphinx]

Okay, and what's the error?

[grim zephyr]

Database is locked

[austere portal]

Your code looks good

[torn sphinx]

Well can I know which line?

[grim zephyr]

It's not showing the line

[austere portal]

SQLite is meant to be a lightweight database, and thus can't support a high level of concurrency. OperationalError: database is locked errors indicate that your application is experiencing more concurrency than sqlite can handle in default configuration. This error means that one thread or process has an exclusive lock on the database connection and another thread timed out waiting for the lock the be released.

Python's SQLite wrapper has a default timeout value that determines how long the second thread is allowed to wait on the lock before it times out and raises the OperationalError: database is locked error.

If you're getting this error, you can solve it by:

Switching to another database backend. At a certain point SQLite becomes too "lite" for real-world applications, and these sorts of concurrency errors indicate you've reached that point.
Rewriting your code to reduce concurrency and ensure that database transactions are short-lived.
Increase the default timeout value by setting the timeout database option

[torn sphinx]

Yeah..

[austere portal]

Look at the last paragraph @grim zephyr

[grim zephyr]

Yeah

[torn sphinx]

By the way, open SQLite and check if the Write changes button hasn't been pressed. If it hasn't, that could be the reason this is happening.

[grim zephyr]

Looks like I need it switch

To another db

@torn sphinx

Hey bud

What db should I use

[torn sphinx]

Postgres 🐘

[torn sphinx]

I used SQLite and it worked fine.
If it isn't working, consider Postgres. It can handle more than SQLite and is good for storing a lot of data/good for big bots

By good I mean better than SQLite

[austere portal]

Yes,

[torn sphinx]

[grim zephyr]

Well I need to host it right

[torn sphinx]

Wym?

[grim zephyr]

???

[austere portal]

You can host it locally

[torn sphinx]

It can be hosted locally like SQL

If you know how to use SQL, using PG shouldn't be too hard.

[grim zephyr]

I need to host it using my computer switched on for 24 hrs right??

[torn sphinx]

Oh that yes, you mean hosting the bot

[grim zephyr]

No

[austere portal]

Well, if you turn your computer off your bot will turn off

[torn sphinx]

Huh?

[grim zephyr]

I mean hosting the db

[torn sphinx]

Hosting a DB?

[grim zephyr]

I am new to portgre

[torn sphinx]

You don't need to keep it open

[grim zephyr]

Noob

[grim zephyr]

Wdym

[torn sphinx]

Maybe go through the documentation

[grim zephyr]

How will it be online

[torn sphinx]

It doesn't need to be running 24/7

[grim zephyr]

Is it like mongo

[grim zephyr]

Wdym?

[torn sphinx]

I think it works, just like SQL works, it'll work even if the SQL application is closed on your PC

Since you are making a connection to it

[grim zephyr]

I mean if I turn off my pc so will it db be closed too

??

[torn sphinx]

no, but if you are locally hosting your bot and turn of your PC, it won't work then

because well of course it won't

are you hosting your bot?

[grim zephyr]

Using a vps

@torn sphinx

[torn sphinx]

Aight

[thorny field]

what's a vps?

virtual post service?

[torn sphinx]

virtual private server

[grim zephyr]

Virtual private server

@torn sphinx

[thorny field]

ohhh

[torn sphinx]

I know

[thorny field]

are they free?

do they require ur computer to be always on?

[grim zephyr]

So if I switch off my pc still the portgre will be running right

[grim zephyr]

No

[thorny field]

ok

[grim zephyr]

@torn sphinx

[torn sphinx]

Yes, do you mind not pinging so much and waiting for a response?

[grim zephyr]

Sry foor the ping 😅😅

It means it somehow works like mongo

Well a last question

Can u help

??

@torn sphinx sry for the ping but for the last time

is it possible for PortgreSQL to manage and update and store data of 8000 members

[torn sphinx]

@grim zephyr maybe

[brave bridge]

You can also increase the timeout on the SQLite side

PRAGMA busy_timeout = 100; (is in ms)

[bright dragon]

Hi, I need some help. I have a products table with a parent_product column, this columns stores the parent product id, which is in the same table, but has Null in this column. How can I get this parent product with the data of its childs?. To get the parent product I do this query SELECT * FROM products WHERE parent_product IS NULL but I don't know how to do a JOIN to the same table

Also to clarify products can have a maximum of 4 childs. Child products can't have more sub childs products. I'm using mysql server, and for testing in local phpmyadmin. I want all products with all their children. All childs of a product have the parent product id in a column named parent_proudct.

In the image, column names are in spanish, but this is how a product would look. 2290 is the parent product, and 2291, and 2992 are subchilds. In practice they would not say Parent, Subchild 1, and Subchild 2.

[torn sphinx]

[] makes it an array of bigint i think

CREATE TABLE contacts (
    id serial PRIMARY KEY,
    name VARCHAR (100),
    phones TEXT []
);
INSERT INTO contacts (name, phones)
VALUES('John Doe',ARRAY [ '(408)-589-5846','(408)-589-5555' ]);
INSERT INTO contacts (name, phones)
VALUES('Lily Bush','{"(408)-589-5841"}'),
      ('William Gate','{"(408)-589-5842","(408)-589-58423"}');
SELECT
    name
FROM
    contacts
WHERE
    phones [ 2 ] = '(408)-589-58423';
UPDATE contacts
SET phones = '{"(408)-589-5843"}'
WHERE id = 3;

[torpid sky]

whats the difference between bigint and bigint[]
with postgresql

[torpid sky]

array as inn

like a list

[torn sphinx]

why did you delete and repost that?

[torpid sky]

ppl make massive clunky code and dont use a mb

[torn sphinx]

it's for you dude

[torpid sky]

hmm

ok

so

array as in a list

or

array as in numpy

[torn sphinx]

whats best database for discord.py bots?

[brazen charm]

generally postgres is regarded as the best

generally a very solid choice in combination with the asyncpg driver

[brave bridge]

In relational databases you lay out data as tables and their connections. For example, you might have a table members with fields (id, name) and another table rooms with fields (id, name, capacity). If you want to store the members in a room, you'll have another table room_members with fields (room_id, member_id). This is the structure you would have (not actual syntax of anything, just a diagram):

# members 
id | name
---+-------
1  | alice
2  | bob
3  | charlie

# rooms
id | name | capacity
---+------+----------
1  | foo  |  3
2  | bar  |  2

# room_members
room_id | member_id
--------+-----------
      1 | 1
      2 | 1
      2 | 3
      1 | 2

Then you can set up different constraints, such as:
"all id values in members are unique"
"all id values in rooms are unique"
"all room_id values in room_members refer to an id in rooms"
"all member_id values in room_members refer to an id in members"
"all rows in room_members are unique"

Yes, you could have another table like that, or just store those info in the members table

yeah

I recommend https://sqlbolt.com/ if you need a tutorial on SQL

what exactly are you referring to?

[ornate gust]

join

[brave bridge]

"foreign key" is probably what you need

[austere portal]

Yes

[grim zephyr]

I need help

[median wave]

It's best to state your question so when someone sees it they can help you out faster

[grim zephyr]

    await bot.db.execute("INSERT INTO Prefix(guild_id, prefix) VALUES ($1, $2)", (message.guild.id, DEFAULT_PREFIX))
  File "C:\Users\USER\AppData\Local\Programs\Python\Python39\lib\site-packages\asyncpg\pool.py", line 530, in execute
    return await con.execute(query, *args, timeout=timeout)
  File "C:\Users\USER\AppData\Local\Programs\Python\Python39\lib\site-packages\asyncpg\connection.py", line 299, in execute
    _, status, _ = await self._execute(
  File "C:\Users\USER\AppData\Local\Programs\Python\Python39\lib\site-packages\asyncpg\connection.py", line 1625, in _execute
    result, _ = await self.__execute(
  File "C:\Users\USER\AppData\Local\Programs\Python\Python39\lib\site-packages\asyncpg\connection.py", line 1650, in __execute
    return await self._do_execute(
  File "C:\Users\USER\AppData\Local\Programs\Python\Python39\lib\site-packages\asyncpg\connection.py", line 1697, in _do_execute
    result = await executor(stmt, None)
  File "asyncpg\protocol\protocol.pyx", line 183, in bind_execute
  File "asyncpg\protocol\prepared_stmt.pyx", line 129, in asyncpg.protocol.protocol.PreparedStatementState._encode_bind_msg
asyncpg.exceptions._base.InterfaceError: the server expects 2 arguments for this query, 1 ```

@median wave

[grim vault]

Did you change the database module? Because asyncpg doesn't expect an iterable as second argument, but just for each substitution one argument, so in your case:

await bot.db.execute("INSERT INTO Prefix(guild_id, prefix) VALUES ($1, $2)", message.guild.id, DEFAULT_PREFIX)```

I think asyncpg is not PEP-249 conform.

[grim zephyr]

Traceback (most recent call last):
  File "C:\Users\USER\AppData\Local\Programs\Python\Python39\lib\site-packages\discord\client.py", line 343, in _run_event
    await coro(*args, **kwargs)
  File "C:\Users\USER\AppData\Local\Programs\Python\Python39\lib\site-packages\discord\ext\commands\bot.py", line 979, in on_message
    await self.process_commands(message)
  File "C:\Users\USER\AppData\Local\Programs\Python\Python39\lib\site-packages\discord\ext\commands\bot.py", line 975, in process_commands
    ctx = await self.get_context(message)
  File "C:\Users\USER\AppData\Local\Programs\Python\Python39\lib\site-packages\discord\ext\commands\bot.py", line 886, in get_context
    prefix = await self.get_prefix(message)
  File "C:\Users\USER\AppData\Local\Programs\Python\Python39\lib\site-packages\discord\ext\commands\bot.py", line 831, in get_prefix
    ret = await discord.utils.maybe_coroutine(prefix, self, message)
  File "C:\Users\USER\AppData\Local\Programs\Python\Python39\lib\site-packages\discord\utils.py", line 343, in maybe_coroutine
    return await value
  File "C:\Users\USER\Desktop\ZENESIS\PREFIX\bot.py", line 21, in get_prefix
    prefix = [0].get("prefix")
AttributeError: 'list' object has no attribute 'get'```

[jaunty galleon]

Did you read the error?

I mean the line

[grim zephyr]

no attribut

names get

[jaunty galleon]

You can't just do list.get

You need RecordObject[0].get('prefix') ig

[grim zephyr]

am i supposed to use the recordobject

[jaunty galleon]

!e

print([0].get('prefix'))```

[delicate field]

@jaunty galleon :x: Your eval job has completed with return code 1.

001 | Traceback (most recent call last):
002 |   File "<string>", line 1, in <module>
003 | AttributeError: 'list' object has no attribute 'get'

[jaunty galleon]

Depends on what you are doing

[grim zephyr]

well can you tell me what should i do next as i am noob in db

[jaunty galleon]

You are trying to get the prefix from the db right?

[grim zephyr]

async def get_prefix(bot, message):
    if not message.guild:
        return when_mentioned_or(DEFAULT_PREFIX)(bot, message)
    

    prefix = await bot.db.fetch("SELECT prefix FROM Prefix WHERE guild_id = $1", message.guild.id)
    if len(prefix) == 0:
        await bot.db.execute("INSERT INTO Prefix(guild_id, prefix) VALUES ($1, $2)", (message.guild.id, DEFAULT_PREFIX))
        prefix = DEFAULT_PREFIX
    else:
        prefix = [0].get("prefix")
    return commands.when_mentioned_or(prefix)(bot, message)```

[jaunty galleon]

prefix = await bot.db.fetch("SELECT prefix FROM Prefix WHERE guild_id = $1", message.guild.id)```
`prefix` is a list with ig one value of Record object

So to get the record object, you need to do prefix[0],

And than .get('prefix')

[grim zephyr]

ok

lemme try

Traceback (most recent call last):
  File "C:\Users\USER\AppData\Local\Programs\Python\Python39\lib\site-packages\discord\ext\commands\bot.py", line 896, in get_context
    if message.content.startswith(tuple(prefix)):
TypeError: tuple for startswith must only contain str, not list

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "C:\Users\USER\AppData\Local\Programs\Python\Python39\lib\site-packages\discord\client.py", line 343, in _run_event
    await coro(*args, **kwargs)
  File "C:\Users\USER\AppData\Local\Programs\Python\Python39\lib\site-packages\discord\ext\commands\bot.py", line 979, in on_message
    await self.process_commands(message)
  File "C:\Users\USER\AppData\Local\Programs\Python\Python39\lib\site-packages\discord\ext\commands\bot.py", line 975, in process_commands
    ctx = await self.get_context(message)
  File "C:\Users\USER\AppData\Local\Programs\Python\Python39\lib\site-packages\discord\ext\commands\bot.py", line 909, in get_context
    raise TypeError("Iterable command_prefix or list returned from get_prefix must "
TypeError: Iterable command_prefix or list returned from get_prefix must contain only strings, not list

@jaunty galleon

[jaunty galleon]

Show code

[grim zephyr]

ok

import discord
from discord.ext import commands

import asyncpg
from discord.ext.commands.bot import when_mentioned, when_mentioned_or


DEFAULT_PREFIX = "!"


async def get_prefix(bot, message):
    if not message.guild:
        return when_mentioned_or(DEFAULT_PREFIX)(bot, message)
    

    prefix = await bot.db.fetch("SELECT prefix FROM Prefix WHERE guild_id = $1", message.guild.id)
    if len(prefix) == 0:
        await bot.db.execute("INSERT INTO Prefix(guild_id, prefix) VALUES ($1, $2)", (message.guild.id, DEFAULT_PREFIX))
        prefix = DEFAULT_PREFIX
    else:
        prefix[0].get("prefix")
    return commands.when_mentioned_or(prefix)(bot, message)



bot = commands.Bot(command_prefix= get_prefix)


async def create_db_pool():
    bot.db = await asyncpg.create_pool(dsn = "removed the link")
    print("connected to database")


@bot.event
async def on_ready():
    print("i am in")


@bot.command()
@commands.has_permissions(administrator = True)
async def setpre(ctx, new_prefix):
    await bot.db.execute("UPDATE Prefix SET prefix = $1 WHERE guild_id = $2", new_prefix, ctx.guild.id)
    await ctx.send("prefix updated")


token = 'token'
bot.loop.run_until_complete(create_db_pool())
bot.run(token)

[jaunty galleon]

ok one sec

[grim zephyr]

ok

[jaunty galleon]

You need to do prefix = prefix[0].get('prefix') and than prefix would be the string

[grim zephyr]

lol i though of doing this but just thought didn't do lol

[grim zephyr]

hey nir my bot isin't responding

[jaunty galleon]

No error?

[grim zephyr]

no

[jaunty galleon]

Try to add print statements in the get_prefix function

[grim zephyr]

ok

[grim zephyr]

<function get_prefix at 0x000002166D167F70>

async def get_prefix(bot, message):
    print(get_prefix)
    if not message.guild:
        return when_mentioned_or(DEFAULT_PREFIX)(bot, message)
    

    prefix = await bot.db.fetch("SELECT prefix FROM Prefix WHERE guild_id = $1", message.guild.id)
    if len(prefix) == 0:
        await bot.db.execute("INSERT INTO Prefix(guild_id, prefix) VALUES ($1, $2)", (message.guild.id, DEFAULT_PREFIX))
        prefix = DEFAULT_PREFIX
    else:
        prefix = prefix[0].get("prefix")
    return commands.when_mentioned_or(prefix)(bot, message)```

[jaunty galleon]

Yeah I know it's a function

Add more prints in the entire get_prefix function

[grim zephyr]

but what should i print

[jaunty galleon]

1 and 2 or something

Actually

Print the prefix right before the last line

[grim zephyr]

ok

[torn sphinx]

@grim zephyr This is the code?

[fierce sand]

I think im gonna make my own cloud service business :)

[grim zephyr]

well a part of the code

[torn sphinx]

Send the line where you defined bot

[jaunty galleon]

He passed it correctly

[torn sphinx]

I see your error in #discord-bots

Can you send that code?

[grim zephyr]

i got it thanks @jaunty galleon
thanks @torn sphinx for trying to help me out

@torn sphinx

i have a question/

[torn sphinx]

Wat?

[grim zephyr]

?

a portgres db of 20 mb can store how many guilds prefix

@torn sphinx

[torn sphinx]

Uh

[grim zephyr]

approx

[torn sphinx]

I don't know

[grim zephyr]

more than 2000?

[torn sphinx]

[fierce sand]

@grim zephyr 1 prefix if only 2 char is 1B

1000B is 1KB

1000KB is 1MB

[grim zephyr]

true

[fierce sand]

So you may have 100000+

[median wave]

https://www.postgresql.org/docs/12/limits.html maybe this would help?

[grim zephyr]

and what about the guild ids

[fierce sand]

Guild ID in otherwr hand

[median wave]

definitely more than 2000

alot more

don't worry about it

[fierce sand]

Its around 8char

[grim zephyr]

more than 10000?

[median wave]

alot more

bruh just keep it at it can store alot

[fierce sand]

Yeah, ik MB is not as big these day but they are still big in the world of only char

[grim zephyr]

more than 15000?

last time plz

[fierce sand]

Probably

[grim zephyr]

ok it means not sure right

[fierce sand]

@grim zephyr are you using cloud storage?

[median wave]

either way

you arent going to reach that limit

[fierce sand]

If no then maybw buy some SD or MICROsd card for 15GB+ storage

Definitely not a small portion of prefix and guildID

[finite silo]

Is this channel also for SQL?

[fierce sand]

Yes

SQL, json, all that related to database

[grim zephyr]

portgres

[fierce sand]

So you are using cloud?

[grim zephyr]

yep

[jaunty galleon]

Did you just say JSON?

[fierce sand]

@grim zephyr i used to saved userdata in .txt file

How good is dynamic typing

[finite silo]

(I don't know the terminologies)
I have a table with a foreign key (TableF) and null. I delete the item in TableF and I want to delete the item that it referenced. I tried to use delete cascade bu then the null constraint just stops it.

[fierce sand]

JSON sux
readLines()
writeLines()
Better

[fierce sand]

Someone will come to help you hopefully

[finite silo]

I just don't know how to put it into google friendly words

[brave bridge]

Is asyncpg missing half the type hints, or is there an issue with my type checker? I'm using pyright

For example, when I do py async with pool.acquire() as conn: , conn's type is inferred as Unknown

[deep rover]

is there any good way to task queue sqlite update/changes? I tried using aiosqlite but when there are multiple changes at the same time the 'databases is locked' still occur

[grim zephyr]

Well which is faster mongoDB or PortgreSQL

[uneven lodge]

mycursor.execute("SELECT * FROM Users")
        for x in mycursor:
            print(x)

the above code returns nothing

[prisma girder]

NoSQL databases are faster than SQL ones afaik

[prisma girder]

Are you sure that you have results in your table?

[faint blade]

AioSQLite does queue changes, but it can't if you use multiple connections. You should be using one connections and sharing it

[deep rover]

I see thanks

[faint blade]

There are some race conditions when using transactions, but for many use-cases you will not be using transactions

[uneven lodge]

Mysql is taking too long to execute my query

its like 1minute and its still executing my query

is there any problem with it

[faint blade]

Well you're dropping a complete database, if this is big I would expect it to take a while. Especially if you have a slower disk.

[indigo flare]

There are stubs, gimme a second to find them

https://github.com/bryanforbes/asyncpg-stubs

[uneven lodge]

sql = "INSERT INTO Users ( id, wallet, bank ) VALUES (%s %s %s)"

[uneven lodge]

what is wrong in this syntax

[prisma girder]

Add , between values

sql = "INSERT INTO Users(id, wallet, bank) VALUES (%s, %s, %s)"

[nocturne pewter]

Does anyone know how to use SQLite database for discord bot?

[austere portal]

You create the database and connect to it

[nocturne pewter]

K

[austere portal]

You can use the aiosqlite module, the built in sqlite module is blocking

[nocturne pewter]

@austere portal i create a database file

How to connet?

[austere portal]

You need to connect to it using the aiosqlite module

!pypi aiosqlite

[delicate field]

aiosqlite v0.17.0

asyncio bridge to the standard sqlite3 module

[indigo flare]

How do I avoid asyncpg.exceptions.ForeignKeyViolationError: insert or update on table "nicknames" violates foreign key constraint "nicknames_guild_id_fkey" DETAIL: Key (guild_id)=(698223755582898207) is not present in table "guilds".by like, automatically inserting into the foreign table if not present