databases

[dense oar]

Mark paid, explode, send

That's an issue too

Because then bob never got paid

But it got marked as paid

Which is what I had in my example

So basically both needs to be avoided

[harsh pulsar]

But it got marked as paid
i won't, because the tx will be rolled back and bob will not be marked as paid

[dense oar]

Ohh

Right

Sorry just started learning about transactions

So my ideal solution would be

Mark paid (actually update to paid), explode, send payment, but since the session never got to send payment the mark paid update should be reverted

I thought that's how transactions worked my bad

As in it commits the update when the update function runs, but then later reverts it if the session closes without acknowledgement

• after a timeout or something

I was thinking of running it through RabbitMQ, because I could easily solve it with that

[harsh pulsar]

Mark paid (actually update to paid), explode, send payment, but since the session never got to send payment the mark paid update should be reverted
this is how transactions work!

[dense oar]

easily™️

[harsh pulsar]

the only problem here is if the server explodes in the very small window between when the payment is sent successfully to paypal and when the transaction is committed

[dense oar]

But it ain't updated to paid though (when the update function runs)

It would only get updated at the very end when it's committed (which doesn't happen)

When it prints "Updated bob's paid status to True" it should really be updated

Not only when that update at the starts gets committed at the end of the function

I'm super confused lol

[harsh pulsar]

but why do you care about that?

[dense oar]

Because that's the point of failure no?

[harsh pulsar]

the transaction more or less doesn't perform the update until the payment is sent

the update is cached/staged/whatever, payment is sent, then the tx is committed and the update is applied to the db

now if you have some kind of concurrency to deal with, that's another story - you'll need to put some kind of locking around each user's payment status

[dense oar]

So

def send_payment():
    with mongo_client.start_session() as session:
        with session.start_transaction():
            print("Updating bob's status...")
            collection.update_one(
                filter={"user": "bob", "paid": False},
                update={"$set": {"paid": True}},
                session=session
            )
            print("Updated bob's paid status to True")
            print("Sending payment to bob...")

            input()  # Boom server exploded, the paid status update needs to be rolled back to False

            send_payment_to_user(user="bob", value=100)  # Nope not gonna happen
            print("Payment sent to bob")  # Nope
            print("Everything went smoothly, no need to roll back the update")  # Nope we exploded

This is correct after all?

[harsh pulsar]

    with mongo_client.start_session() as session:
        with session.start_transaction():
            mark_paid(session, user="bob")
            input()
            paypal.send_payment(user="bob", value=100)

that's this, right?

[dense oar]

Looks like it

[harsh pulsar]

yes

that would do the right thing

payment never gets sent, bob never gets marked as paid

[dense oar]

Oh I must have missed something obvious

[harsh pulsar]

    with mongo_client.start_session() as session:
        with session.start_transaction():
            mark_paid(session, user="bob")
            paypal.send_payment(user="bob", value=100)
            input()

this is the case that you don't have covered

[dense oar]

Oh right

I don't care about that

[harsh pulsar]

paypal confirms the payment, server explodes, tx never gets committed, bob never gets marked as paid

[dense oar]

That's literally like a sub ms window

[harsh pulsar]

right

[dense oar]

Meanwhile the other can be 1-2 sec window

Let me test it again with my simulated explosions (literally pulling the power of my server) just to make sure

One last question I swear

How is this

with mongo_client.start_session() as session:
    with session.start_transaction():
        mark_paid(session, user="bob")
        input()
        paypal.send_payment(user="bob", value=100)

Different from this

paypal.send_payment(user="bob", value=100)
input()
mark_paid(session, user="bob")

I guess that's what confuses me

Because that's what I had originally

Unless I'm completely braindead

Wait

I can't get it right

Yeah forgot it I have no idea anymore I'm pretty sure you're right

[harsh pulsar]

no, it's a good question

sorry give me a min

i think the idea is that if mark_paid fails, you're stuck

so yeah, think about it this way

in the first version, mark_paid is already queued up on the server

in the second version, mark_paid isn't even requested anywhere

[dense oar]

So basically still not bulletproof just faster?

[harsh pulsar]

if the only thing you're worried about is send_payment crashing, then it probably isn't that different

[dense oar]

My main concern is send_payment explode mark_paid

To avoid sending multiple times because it never get to marking as paid

Is that fixes with this approach?

Sorry I completely lost it at this point lol

[harsh pulsar]

in this case, i think both versions are OK

[dense oar]

Aren't both bad?

[harsh pulsar]

in the tx version, the tx gets rolled back if the payment sending fails. in the original version, bob never gets marked in the first place

it depends on what you're worried about

if you're worried about your server crashing at any minute, then yes both are insufficient

[dense oar]

paypal.send_payment(user="bob", value=100)  # Sent successfully
input()
mark_paid(session, user="bob")  # Exploded, never got marked as paid

This was my original concern

[harsh pulsar]

ah, right. sorry

then yes you need the transaction

[dense oar]

But are you sure that saves it?

If it explodes after sending

[harsh pulsar]

yes, but that isn't the same as this

with mongo_client.start_session() as session:
    with session.start_transaction():
        mark_paid(session, user="bob")
        input()
        paypal.send_payment(user="bob", value=100)

because that explodes before sending

[dense oar]

Yes I know

I meant explode after sending sorry

So

Successfully send

Explode

???

[harsh pulsar]

with mongo_client.start_session() as session:
    with session.start_transaction():
        mark_paid(session, user="bob")
        paypal.send_payment(user="bob", value=100)
        input()

paypal.send_payment(user="bob", value=100)
input()
mark_paid(session, user="bob")

these two are equally bad

[dense oar]

Right

That's what I thought

Just not what I said

[harsh pulsar]

but like you said, there's a sub-ms window when this could happen.

[dense oar]

Not in this case though

[harsh pulsar]

if the issue is send_payment itself timing out and crashing, then both are equally good

[dense oar]

Let's say send_payment always completes successfully

So then yeah the window is small

But wouldn't my idea be bulletproof then?

Update to paid (actually)

Send (successfully)

[harsh pulsar]

yes, if you could get the server to do that

and i don't think mongodb can do that by default

or at all?

[dense oar]

Oh that was your point I see

[harsh pulsar]

i'm not sure if any db can do that, it's a good question

[dense oar]

I thought this would be disaster handling 101

Hence why I assumed this is just 1 line of code

Hm

[harsh pulsar]

transactions in my experience are used for things like updating 3 different tables in the same db at once

[dense oar]

Oh yeah I see

[harsh pulsar]

but i'm not a db admin or distributed systems dev

mind you, other databases have much more versatile options https://www.postgresql.org/docs/current/tutorial-transactions.html

[dense oar]

So if you have a function that involves for example 3 inserts at 3 different stages, it only commits either all of them if everything was fine or none if it wasn't fine

I love MongoDB though 😦

[harsh pulsar]

i don't

[dense oar]

Yeah I hear that from quite a few people lol

So what I'm looking for is more like a rollback to a savepoint?

So like save, update, explode, roll back

Or save, update, send, no roll back cuz no explosion

[harsh pulsar]

you just need more control

[dense oar]

Can postgresql do this by default?

Or well not by default but having the options for it without implementing it myself

[harsh pulsar]

i don't know

could be a good stackoverflow question if you phrase it carefully

[dense oar]

Gonna look into it, ty for the explanations and making things more clear

Yeah I'll probably write it for half a week and then post it on SO

[harsh pulsar]

(if you don't phrase it carefully you'll get downvoted into oblivion)

[dense oar]

Putting the question here again just in case someone has some other input also (trying to ask it properly this time, sorry again)

How is something like this handled in production?

def send_payments():
    unpaid_users = [i for i in users.find(filter={"paid": False})]  # Create a list of unpaid users from the DB

    for user in unpaid_users:  # Current user: bob
        send_payment_to_user(user=user["user"], amount=user["amount"])  # Send payment to bob via PayPal API
        input()  # Boom server explodes (literally, not just the script)
        users.update_one(filter={"user": user}, update={"paid": True})  # Update bob's paid status to True in the DB

Let's say the server that runs this script explodes just after it finished send_payment_to_user() but before it finished users.update_one(), so it sent the payment to bob but it didn't have time to update bob's paid status to True in the DB, which means if this script starts again, bob's payment will be sent again, because he's still not paid according to the DB.

My first idea was to update the paid status first and then send the payment, but, if the server explodes before the send_payment_to_user() completes, revert/rollback the status update on the DB's side to what it was before this update, somehow. This is to avoid leaving it marked as paid when in fact it wasn't. If the server explodes after send_payment_to_user() completes, don't revert the update because the payment was successful. Is this possible with MongoDB somehow, using sessions/transactions or something?

My second idea is to use RabbitMQ (or any message queue) for this and run everything through it to be able to redeliver "functions" if they didn't succeed.

Third idea is to log the payment that will happen (invoice number), check if the transaction happened, if not then send the payment. This should work in this case (I assume) but I'm looking for a solution to this problem in general, if it exists

[unkempt prism]

I gather you are using mongo based on update_one function. I googled it as I'm not familiar. If this assumption is correct read https://www.mongodb.com/developer/quickstart/python-acid-transactions/#what-happens-when-you-run-with-transactions-

[jade void]

Okay so, this is my structure. How do I update a specific list of songs of any specific dict? If I have the key, for eg. 0

(this is MongoDB)

[frosty barn]

Good morning! <3
I have a problem with SQL that I can't figure out...
I have a table in this format (name, date, rank) It contains datapoints of a lot of users.
I want to retrieve the 2 latest datapoints of each user.
For one user it's easy I can just do ORDER BY date DESC LIMIT 2
But I want the latest 2 points for EACH user... No idea how to go about it! D:
If anyone could point me in the right directions I would appreciate it a lot <3
Thanks! :)

[snow niche]

@austere portal hello, ik u use Postgres SQL... So can u tell me where should I host it for free?

[serene oyster]

I have this line :```py
cursor.execute("SELECT lien FROM futura WHERE lien = {0}".format(link[x])) #link[x] is a link like https://.........

but got this error : ``sqlite3.OperationalError: unrecognized token: ":"``

Does anyone can help me ?

[frosty barn]

You shouldn't use .format to format your queries. Characters like : need escaping. Instead use the formating given to you by SQLite:

cursor.execute("SELECT lien FROM futura WHERE lien = ?", (link[x],))

This will automatically escape the special characters

[serene oyster]

escape... like don't put it in my db and remove it ?

[frosty barn]

Escape means that you convert a regular character into a special character that the database knows to ignore. For example if you tried to select a link that has ; in it, the query will see a ; and think it's over and stop. Using what I wrote with the ? Inside the query it will automatically convert any special characters like ; : ? * Into something the db knows how to deal with

[serene oyster]

or just skip special characters and only do what I ask without trying to understand it differently ?

[frosty barn]

So just use that method instead of .format and everything should be fine

[serene oyster]

yeah, I got another error 2 or 3 lines after 😂

thanks @frosty barn

[frosty barn]

No problem!

[ionic pecan]

this sounds like a use case for window functions to me: https://www.postgresql.org/docs/13/tutorial-window.html if you can give me your schema to play with and maybe some data i can give you a more involved example, but the linked documentation already does a great job at explaining it

[grim vault]

so like:

SELECT name, date, rank
  FROM (SELECT name, date, rank,
               row_number() OVER (PARTITION BY name ORDER BY date DESC) AS pos
          FROM tablename) AS Aliasname
 WHERE Aliasname.pos < 3

[austere portal]

You can host a postgresql db free on heroku

https://elements.heroku.com/addons/heroku-postgresql

[snow niche]

But what if I'm hosting my bot on replit?

[austere portal]

you can still have your database on heroku

[dense oar]

I don't think that helps in this case

[dense oar]

Anyone else?

[austere portal]

ah, seems like aiopg.create_pool won't work properly outside async context managers

[errant knoll]

Hi, I want to use the result of a subquery in Postgres twice later on in the main query should I do 2 separate queries (one to get the subquery result, then one to make use of this result) or is there a way to store the subquery result to use later in the main query?

[ionic pecan]

you can use a common table expression for this: https://www.postgresql.org/docs/13/queries-with.html

[faint blade]

By far the easiest way to have "variables" ^^

[errant knoll]

Related to the above: ERROR: argument of OFFSET must not contain variables

Trying to get a random row

The query

                With cte_rarity (rarity_var) AS (
                      SELECT COALESCE((
                        SELECT MAX (rarity)
                        FROM rubber
                        WHERE credit = 421362214558105611
                      ), 0)
                    )
                SELECT id, link FROM rubber, cte_rarity
                    WHERE type = 'yes' AND rarity <= rarity_var
                    OFFSET FLOOR(RANDOM() * (
                      SELECT COUNT(*)
                      FROM rubber
                      WHERE type = 'yes' AND rarity <= rarity_var
                    ))
                    LIMIT 1;

Example data incase needed, help would be greatly appreciated as I am brand new to Postgres

[burnt cloak]

how can i make a columns in mysql that its type is like a str??

i mean like ` ~ ! or more

[steel terrace]

hey guys, I made a webapp and I wanted to connect my mysql db to my website, I tried several tutorials, but I always got an error saying module not found is there any solution to this?
I dont know if the problem is because that I didn't save the mysql db to a file..

[austere portal]

install the module

[steel terrace]

I did install the module using npm

[austere portal]

maybe you have installed the module in a different environment

[steel terrace]

different environment?

[steel terrace]

@austere portal what does different env mean and do?

[steel terrace]

guys anyone?

[torn sphinx]

you using python for that right?

[steel terrace]

no, javascript

[torn sphinx]

:/

[torn sphinx]

no i meant you using python for your website?

[steel terrace]

I used js, html and css

[steel terrace]

for the frontend till now

[torn sphinx]

for backend you should use pip to install python's module

[steel terrace]

so, I can use python for the backend right?

[torn sphinx]

yes you can

[steel terrace]

I was looking for up for tuts where they tried using js, but there were none :/

so, I should type the command pip install mysql?

[torn sphinx]

yeah

[steel terrace]

any tutorials for connecting mysql to python?

[torn sphinx]

https://www.w3schools.com/python/python_mysql_getstarted.asp

[steel terrace]

thank you 🙂

[torn sphinx]

you're welcome 🙂

[steel terrace]

I have a last doubt

@torn sphinx should I save the mysql db file inorder for it to execute?

[torn sphinx]

Well you can save it unless it is erased from memory

[steel terrace]

so, I can directly access it without saving?

I had this doubt for a long time

[torn sphinx]

No, the file will be created as soon as it is executed

[steel terrace]

oh k

I have one last doubt

I have already created a mysql db and in the tutorial doc you sent, they are creating a db

[torn sphinx]

you can use that db created before

[steel terrace]

alright👍

alright, I can access my db using python, but how can I use it to store values?

[jade osprey]

which should I use postgres or mysql
which one is better

[harsh pulsar]

https://realpython.com/python-mysql/

Obviously not all databases are Mysql, but this guide is a pretty good overview and most database libraries in python are have similar designs

[harsh pulsar]

Postgres has more features, so I think it's easier to use without having to think too much about database design, and I think it's fairly easy to set up and work with.

And the postgres docs are very good

[jade osprey]

thanks

[jaunty socket]

how to import into a list from a json file?

[harsh pulsar]

If the json file contains an array, just do json.load

[unkempt prism]

ACID Transactions do do exactly what you describe as somehow here.

Update the paid status first and then send the payment, but, if the server explodes before the send_payment_to_user() completes, revert/rollback the status update on the DB's side to what it was before this update, somehow. This is to avoid leaving it marked as paid when in fact it wasn't.

As for the following it will be outside of the above transaction so it won't revert.

If the server explodes after send_payment_to_user() completes, don't revert the update because the payment was successful.

[dense oar]

Oh my bad then, people said it's impossible to achieve with transactions

Does it actually update the document and then revert on the DB server's end if the server that ran the script broke?

Instead of just "queueing" the update but not updating until it gets to the commit point

[unkempt prism]

If mongo's implementation works like postgres the new updated value will be visible within that transaction only and any queries in parallel will get the old value until the transaction is committed?

Whats the time between the paid status update and the send_payment_to_user? Is it miliseconds, seconds, hours, days?

[dense oar]

Yeah that won't help, I want it to actually update the value in the document and then revert it, not just store it in the script because then it will never get committed if something goes wrong right after the payment has been sent

Milliseconds

[unkempt prism]

I want it to actually update the value in the document and then revert it,

A transaction will do exactly this. Unless there is a error it will automatically revert.

[dense oar]

But it doesn't update the value in the document though, it would only update if it gets to the commit point (which it won't in this scenario)

Like if I'm doing this

def send_payment():
    with mongo_client.start_session() as session:
        with session.start_transaction():
            print("Updating bob's status...")
            collection.update_one(
                filter={"user": "bob", "paid": False},
                update={"$set": {"paid": True}},
                session=session
            )
            print("Updated bob's paid status to True")

            input()  # Boom server exploded, the paid status update needs to be rolled back to False

            print("Sending payment to bob...")
            send_payment_to_user(user="bob", value=100)
            print("Payment sent to bob")

And I just sit at the input()

The document remains untouched

[unkempt prism]

why do you have a input there? Do you want to type something in? This is like having a prompt click Enter to continue.

[dense oar]

That's when the server goes boom

(so the script will never go past that point)

[unkempt prism]

does it go boom or is it waiting for keyboard input. depending on your daemon the keyboard is not connected.

[dense oar]

It's just for visualization there is no waiting for anything

You can replace it with a return or whatever doesn't matter

[unkempt prism]

pythons built in input function will be waiting for keyboard input

[dense oar]

Bruh

def send_payment():
    with mongo_client.start_session() as session:
        with session.start_transaction():
            print("Updating bob's status...")
            collection.update_one(
                filter={"user": "bob", "paid": False},
                update={"$set": {"paid": True}},
                session=session
            )
            print("Updated bob's paid status to True")
            
            print("Sending payment to bob...")
            send_payment_to_user(user="bob", value=100)
            print("Payment sent to bob")
            
            # Boom server exploded, the paid status update needs to be rolled back to False
            # Nothing below this will happen

It's irrelevant it's just a breakpoint for testing the transaction

[unkempt prism]

What does send_payment_to_user look like. Do you need to pass in the session so its the same session/transaction?

[dense oar]

It's a PayPal API call for sending the payment

My original post was more readable

def send_payments():
    unpaid_users = [i for i in users.find(filter={"paid": False})]  # Create a list of unpaid users from the DB

    for user in unpaid_users:  # Current user: bob
        send_payment_to_user(user=user["user"], amount=user["amount"])  # Send payment to bob via PayPal API
        input()  # Boom server explodes (literally, not just the script)
        users.update_one(filter={"user": user}, update={"paid": True})  # Update bob's paid status to True in the DB

Basically I tried convert that into a transaction, but both of these will have the exact same outcome

Because if the server exploded users.update_one() won't happen

And in the transaction example, if the boom happens after payment the document update will never get committed

So both are essentially the exact same

[unkempt prism]

It should be a different transaction once you exit the context manager so something up

[dense oar]

I don't think you can handle server crashes with transactions, only script crashes which I already handle

But not handling that is most definitely not production ready

[unkempt prism]

The tutorial does have

session.commit_transaction()

Even with the context manager.

[dense oar]

But how does it commit the transaction if the server already exploded?

It would have to commit and then revert the commit which you can't do

(unless you can?)

Reverting on the DB server, definitely not from within the script as that's already done

So like this

def send_payment():
    with mongo_client.start_session() as session:
        with session.start_transaction():
            print("Updating bob's status...")
            collection.update_one(
                filter={"user": "bob", "paid": False},
                update={"$set": {"paid": True}},
                session=session
            )
            print("Updated bob's paid status to True")


            print("Sending payment to bob...")
            send_payment_to_user(user="bob", value=100)
            print("Payment sent to bob")

            input()  # Boom server exploded

            session.commit_transaction()

It does actually update the DB in this case

But then how does it get reverted back if the rest of the session didn't finish?

[unkempt prism]

it only makes sense to put the commit after send_payment_to_user

[dense oar]

But then what's the purpose of the transaction? It's the same as send payment then update

Updated the above

You meant it like this?

[unkempt prism]

paid would be false

[dense oar]

But it was paid successfully in this case

It just didn't have time to update it to True

[unkempt prism]

if in the unlikely scenario something went wrong between in the small time between sending the api request to committing to the db then I guess you are out of luck.

[dense oar]

Unlikely == will happen

[unkempt prism]

I somewhat agree.

[dense oar]

If I want to handle this in my small app then I'd very much expect literally every single site that deals with payments to have this bulletproof too

[unkempt prism]

Though perhaps thats why the original link I sent had some fairly robust exception handling.

[dense oar]

But that's just handling within script errors?

Any exception/network outage etc. is already handled

[solemn hinge]

do json files automatically save after you close them like this?

 with open("test.json", "r") as file:
  data = json.load(file)
  file.close()

[unkempt prism]

This is not db related: Your opening in read only. What is there to save?

[solemn hinge]

with open("test.json", "w") as file: 
  json.dump(data, file, indent=4)
  file.close()

Meant this one

[unkempt prism]

Yes writing to the file is saving. With the context manager (with statement) you don't need to close.

[solemn hinge]

oh ok thanks

[harsh pulsar]

Those sites don't use mongo

Mongo is for slinging a huge amount of schemaless JSON blobs at a database and eventually having it all show up there

[warped turtle]

@commands.command(help='[sends the list of reminders from author.]')
async def reminderlist(self, ctx):
      authorid = ctx.author.id
      await ctx.send(f'Author id is: {authorid}')
      await ctx.send(f'that has a type of: {type(authorid)}')
      for row in self.cur.execute("SELECT * FROM reminder WHERE memberid='?'", authorid):
            print(row)

what am I doing wrong? error: parameters are of unsupported type

This is for a discord bot but since it's DB stuff perhaps this is best place

authorid is also an int. and when I save to the database i send an int. However when I created the table, i didnt specify field types.

I tried converting it from int to str but it didnt work either.

[solar umbra]

can someone help me with SQL Database?

[harsh pulsar]

1. what database library? unless this is asyncpg, you need a sequence of parameters, e.g. [authorid]
2. remove the quotes from around ?, the parameterization process automatically adds them when needed

[solar umbra]

@harsh pulsar can you help me?

[solar umbra]

I want to make a discord bot,
when a user joins the server it adds points/score in inviter's wallet

[torn sphinx]

hi

[solar umbra]

hello

@client.event
async def on_member_join(member):
    inviter = await tracker.fetch_inviter(member)
    channel = client.get_channel(872704194337980547)
    cursor = await client.db.cursor()
    await channel.send(f"{member} joined! Invited by {inviter}")
    await cursor.execute("INSERT INTO money (guild_id, inviter_id, money) VALUES (?,?,?)", (member.guild.id, inviter.id, 10))
    
    await client.db.commit()
    await cursor.close()

This giving me error

[torn sphinx]

how are you

[solar umbra]

I am good

[ionic pecan]

you need to post the error for us to be able to help you

and tell us what you've tried to fix it yourself

[crystal compass]

ERROR: Cannot install flask-mysqldb==0.1.1 and flask-mysqldb==0.2.0 because these package versions have conflicting dependencies.

pip install flask

pip install flask-mysqldb

why do i get that error / how can i fixx it

[unkempt prism]

The issue seems to be from https://github.com/alexferl/flask-mysqldb/blob/master/requirements.txt that specifies a specific version of Flask. Installing just flask-mysqldb will automatically install the correct version of Flask for that package. It might be worth finding a adapter compatible with Flask v2 though.

[warped turtle]

How would I check if an entry exists and if it doesnt exist, insert it?

@solar umbra Tried googling it, not working. Lets ask someone who does this database stuff. hopefully they have more answers.

I am using SQLITE3

SQLITE3, my code:

cur.execute("INSERT INTO money VALUES(:guildid, :myid, 10) WHERE NOT EXISTS (SELECT * FROM money WHERE member_id = :otherid)", {"myid": myid, "guildid": guildid, "otherid":myid})

honestly im pretty sure it's just my sql statement lol

[solar umbra]

I think we are near compeletion

just need some assistance

[warped turtle]

Basically my steps are:
attempt to update the database if they don't already exist in database. if they arent in database. insert them into it.

[warped turtle]

I ended up figuring it out \o/

and by figuring it out, I mean I eventually googled the right thing and someone else had a similar issue

[grim vault]

I know about three ways to do that:

1. select, if found update else insert
2. update, if rowcount is zero insert
3. insert on conflict update

[warped turtle]

I ended up doing a insert on conflict update

How do I open a .db file so I can view the contents?

want to make sure it's updating

[grim vault]

I use https://sqlitebrowser.org/ to look into sqlite databases.

Just make sure to call the commit() method of the connection after insert/update/delete statements.

[lusty tree]

Making an example that looks similar to what I actually have.

Table I have in Sqlite database:

id2 | B | 24
id3 | C | 35
id4 | D | 32
id5 | E | 16
...```
List I will work with:

```[id1, id3, id4, id5]```

I want to sort this list by points saved in the Sqlite database.

The list would look like this later:

```[id4, id2, id5, id1]```

This is basically what I want to do for now. I've thought of getting the ids and points of users, and making it into a dictionary in python, but I'm sure how to do that since I'm not experienced with SQLite.

I want to know if theres a way to actually contain the code within SQL queries through more advanced stuff, or if not, the best way I can do it for python.

If the dictionary is a good way to do it, please help me do it. (I've seen some people saying that I should use row factory and such, but idk what that is..)

[faint blade]

How many IDs are there?

[lusty tree]

8 in the list, infinite amt in the db (its a register system so it depends on how many people join)

[faint blade]

Is it always 8?

SELECT id from my_table WHERE id IN (?, ?, ?, ?, ?, ?, ?, ?) ORDER BY letter ASC;

Something like that could work

You will get back the IDs in the order of the letters

[lusty tree]

no no i want it in the order of the points relative to the ids

[lusty tree]

heres an answer i got from stackoverflow but it didnt work

id_list = [id1, id3, id4, id5]

query="SELECT id FROM sqlitetable WHERE id in ({}) ORDER BY points DESC".format(','.join(['?']*len(id_list)))

cursor.execute(query, id_list)

[faint blade]

Well what happened

[lusty tree]

it just didnt work.. it didnt actually do anything the ids were always in the same order

[faint blade]

How did you know they were in the same order?

I assume you checked id_list after making that query?

[lusty tree]

yes i printed it

[faint blade]

But id_list won't change, you should fetch the result which will be the IDs that SQLite returned

[lusty tree]

omg i am so dmub ur right! should i use fetchone() ?

[faint blade]

No fetchall(), you want all IDs sorted right?

😅

[lusty tree]

ah yeah you're right

[lusty tree]

uhh still doesn't work

[faint blade]

Can you show all the code?

[lusty tree]

@lobby1.command()
async def start(ctx):
      db = sqlite3.connect('my_database.db')
      cursor = db.cursor()
      #QUEUE CHANNEL
      queue1 = bot.get_channel(883963916915453962)
      queue1_members = queue1.members

      queue1_members_mention = []
      for member in queue1_members:
          queue1_members_mention.append(member.mention)

      queue1_members_id = []
      for member in queue1_members:
          queue1_members_id.append(member.id)
      
    
      query="SELECT user_id FROM main WHERE user_id in ({}) ORDER BY user_ovr ASC".format(','.join(['?']*len(queue1_members_id)))

      cursor.execute(query, queue1_members_id)
      cursor.fetchall()
      print(queue1_members_id)

theres some irrelevant stuff here too

[faint blade]

      result = cursor.fetchall()
      print(result)

[lusty tree]

it worked! but like this [(860154829224804403,), (756350944202981396,)]

how can i remove this: ( ,) ?

[faint blade]

Ah, you can do this: result = [r[0] for r in result]

That will go through that list, and grab the first item in the tuple

[torn sphinx]

how can I store data in json file in this way?

{
{"a": 1},
{"b": 2}
}

[harsh pulsar]

Replace the outer {} with []

JSON uses [ for arrays and { for mappings ("objects")

[torn sphinx]

but I need to use {} in my code...

[harsh pulsar]

Why? In python lists are also represented with []

Anyway you should use the json library to write and read json

!d json

[delicate field]

json

Source code: Lib/json/__init__.py

JSON (JavaScript Object Notation), specified by RFC 7159 (which obsoletes RFC 4627) and by ECMA-404, is a lightweight data interchange format inspired by JavaScript object literal syntax (although it is not a strict subset of JavaScript 1 ).

json exposes an API familiar to users of the standard library marshal and pickle modules.

Encoding basic Python object hierarchies:

[torn sphinx]

ok ty

[harsh pulsar]

The code you showed is not valid json or python

[torn sphinx]

@harsh pulsar,
can the : , indent=2 help me make it look like I want? (or any other indent)
json.dumps(data, indent=2)

[harsh pulsar]

No, and it sounds like this isn't actually json. Why do you need this?

Depending on what you need you could do it manually by looping, string formatting, etc

But I don't want to give an answer without knowing what you actually need and why

[lunar crystal]

Hi. I have a question about matplotlib. This is my database and I want plot in an histogram the number of events per year in the period of 1900 to 2021.

Do you know how I can do this?

[harsh pulsar]

That's not a histogram, that's a bar chart of counts.

df['Fecha'] = pd.to_datetime(df['Fecha'])

event_counts = df.resample(freq='1y', on='Fecha').size()

event_counts.plot.bar()

[lusty tree]

@lobby1.command()
async def result( ctx, *, result):
      db = sqlite3.connect('my_database.db')
      cursor = db.cursor()
      result = str(result)
      #TEAM CHANNELS
      lobby1_team_a = bot.get_channel(870969338901954606).members
      lobby1_team_b = bot.get_channel(870969338901954607).members
      lobby1_team_a_id = []
      for member in lobby1_team_a:
          lobby1_team_a_id.append(member.id)
      lobby1_team_b_id = []
      for member in lobby1_team_b:
          lobby1_team_b_id.append(member.id)
      if result == 'team a':
          for user_id in lobby1_team_a_id:
              cursor.execute(
                  "UPDATE main SET user_elo = user_elo + (?) WHERE user_id = (?)",
                  (elo_win, user_id))
              user_ign = get_user_ign(user_id)
              user_elo = get_user_elo(user_id)
              await member.edit(nick=f'[{user_elo}] ' + user_ign)
          for user_id in lobby1_team_b_id:
              cursor.execute(
                  "UPDATE main SET user_elo = user_elo + (?) WHERE user_id = (?)",
                  (elo_lose, user_id))
              user_ign = get_user_ign(user_id)
              user_elo = get_user_elo(user_id)
              await member.edit(nick=f'[{user_elo}] ' + user_ign)

when i do the command =lobby1 result team a, it doesnt actually change anything in the database. can someone explain to me why?

[harsh pulsar]

Another way @lunar crystal

df['year'] = pd.to_datetime(df['Fecha']).year

event_counts = df['year'].value_counts()

[lunar crystal]

Is this normal?

[harsh pulsar]

Yes, I probably got the name of the argument wrong 🙂 check the docs for the right version

!d pandas.DataFrame.resample

[delicate field]

pandas.DataFrame.resample

DataFrame.resample(rule, axis=0, closed=None, label=None, convention='start', kind=None, loffset=None, base=None, on=None, level=None, origin='start_day', offset=None)```
Resample time-series data.

Convenience method for frequency conversion and resampling of time series. The object must have a datetime-like index (DatetimeIndex, PeriodIndex, or TimedeltaIndex), or the caller must pass the label of a datetime-like series/index to the `on`/`level` keyword parameter.

[lunar crystal]

Many thanks!

[lusty tree]

..help??

[grim vault]

I don't see a db.commit()?

[low harbor]

Hello, can someone help me to find out code's error?

I am not sure where to ask and new on this channel

[harsh pulsar]

Read #❓｜how-to-get-help and ask in a free help channel. Post your code and the full error output

[naive pike]

Anyone know how to show in the database all the things that i put in a table or module using sqlalchemy?

[lean walrus]

any reason why do I always get sqlite3.OperationalError: database is locked everytime I run this following code:

        self.cur.execute("DELETE FROM data WHERE acc_id=?", (self.id,))
        self.db.commit()

it works perfectly fine tho for SELECT, INSERT INTO, etc.

[harsh pulsar]

can you be more specific about what "things" you want to see?

[gentle roost]

your database is in use somewhere

if you have uncommitted changes, you will get that

[agile nest]

how do i literally just make a database lol

[velvet coyote]

So if we do \d table_name we get an info on the table structure with its column, constraints, column types.
How can we get that trough python while using postgres?

[polar locust]

i got a sqlite line like this:

SELECT company, count(*), max(salary) FROM Employees GROUP BY company;

i gotta do this same line in mongodb using aggregate. how is it done?

[harsh pulsar]

You can query the information_schema table

[velvet coyote]

Oh?

[harsh pulsar]

Sorry not a table, it's a whole schema of database info

https://www.postgresql.org/docs/current/information-schema.html

[velvet coyote]

can't find anythin

ah

u mean a query

ok

leme take a look at it

and i don't understand what its trying to say

[harsh pulsar]

SELECT *
  FROM information_schema.columns
 WHERE
    table_schema = 'public'
    AND table_name  = 'table_name'

[velvet coyote]

what exactly is table_shema?

[harsh pulsar]

Postgres has the poorly-named concept of a "schema" which is a group of tables

iirc the default schema is called public

It's like a logical sub-database within a single physical database

[grim vault]

Something like:

db.employees.aggregate([
  {
    $group: {
      _id: "$company",
      count: {$count: {}},
      max: {$max: "$salary"}
    }
  }
])

[polar locust]

thanks that worked! @grim vault

[velvet coyote]

The data i get is humongous , which does contain the column types but I really can't figure out a way to parse it.

[harsh pulsar]

it says what the columns are here: https://www.postgresql.org/docs/current/infoschema-columns.html so you can select only the info you need

[velvet coyote]

ah ok

[velvet coyote]

Well i found that we can use this to get column data types
asyncio.get_event_loop().run_until_complete(run())

ye it works

In [8]: async def run():
   ...:     con  = await asyncpg.connect(database='learn', user='abhigyapokharel')
   ...:     result = await con.fetch("SELECT column_name, data_type FROM information_schema.columns WHERE table_name = $1", 'people')
   ...:     print(result)
   ...: 
   ...: 
   ...: 

In [9]: asyncio.get_event_loop().run_until_complete(run())
[<Record column_name='id' data_type='bigint'>, <Record column_name='first_name' data_type='character varying'>, <Record column_name='last_name' data_type='character varying'>, <Record column_name='gender' data_type='character varying'>, <Record column_name='dob' data_type='date'>, <Record column_name='email' data_type='character varying'>]

Thanks for the help!

[harsh pulsar]

note that in a typical async application you won't want to use asyncio.run more than once

that is more on-topic for #async-and-concurrency though

[misty sundial]

also typing "\x on" in psql might help make sense of those wide column results

[velvet coyote]

yes I am just testing

[velvet coyote]

ah, thanks

[zinc whale]

Okay, so i'm using MySQL 8.0.26 and python 3.8.3 attempting to make a Phonebook program. my database has a contacts table, and it's columns are ID(int), FirstName(VarChar(45)), Surname(VarChar(45)) and PhoneNumber(VarChar(11))
my code can be found here https://pastebin.com/2iZ689D1
for some reason, whenever i try to insert data to my database, it only inserts null? any ideas why this may be?
please feel free to ping me if you need my attention, i'll probably be working on another project while i think on this

[harsh pulsar]

https://paste.pythondiscord.com/toyucerone.py <- repasted here if anyone wants it

does that foo=%(foo)s syntax work in mysql?

!e print('a'*False)

[delicate field]

@harsh pulsar :warning: Your eval job has completed with return code 0.

[No output]

[harsh pulsar]

that's a nice trick, maybe harder to read than if though

@zinc whale try removing the colname= parts in the query you are constructing, i don't think that's valid mysql syntax

[lean walrus]

I used it in my python project only

[lament trail]

Whats would be the best way to store a set of enums. For example say you has some enum that had the values A, B, C and some entry in the data base can have 1 or many of these values associated with it. Is the best route to have another table that just contains a mapping from id to enum values? Or is there a nicer way?

[lean walrus]

ohhhhhhhhh thanksss so much

the answers in stack overflow are really confusing..

[harsh pulsar]

Do you need to store the "pretty" values in the db at all? You could store the "raw" values and only keep the pretty versions in the python app

eg using IntEnum, just save the int values to the db

[lament trail]

No not really, one thing I was considering is using bit flags

[harsh pulsar]

You could add a check on the column to make sure only those exact values are included

Bit flags? Like 1/0? Or a bitfield like in C programs?

[lament trail]

bitfield

[harsh pulsar]

Bit fields are good for collections of non-mutually-exclusive settings

[lament trail]

yeah thats what I want, to be able to hold a set of enum values

[harsh pulsar]

Ah i see

[lament trail]

just wasnt sure if theres a nicer or more proper way to do it

[harsh pulsar]

You might want to wrap that up in a tidy python api that doesn't involve bit shifting etc but yeah sure

Nothing wrong with it but you might have to explain it in a code comment

[lament trail]

cool, thanks!

[harsh pulsar]

One issue would be lack of database support for bit operations

I actually have no idea what that's like

Eg if you want to do in-database bitfield operations you need to know the integer representation

@lament trail ☝️ before you go

[lament trail]

From what I can tell most have bitwise and aswell as or which are the ones that would matter for my use case

[jade osprey]

how do I add a column to an already existsing table in sqlite3

it it possible?

[velvet coyote]

when I fetch something from postgres, I get something and convert it to {id (column name): 'character varying (data type VARCHAR)'}, now I want to convert this to the Python type or vice versa in accordance with this table

Instead of creating a dictionary of the table above, I was wondering if there was a better alternative.

[zinc whale]

It should, got it from the code examples for the connector https://dev.mysql.com/doc/connector-python/en/connector-python-example-cursor-transaction.html

[jade osprey]

are there major differences between sqlite3 and postgreSQL

I need to switch from sqlite3 becuase of the limited datatypes and lack of features

i'm thinking of using postgres

how hard will it be to make that change

[autumn condor]

Hi,
I've a user's table with id as the pk, I'm going to use analytics and the analytic provider would need a user ID to identify analytics traffic

Should I use my pk or create a new column like a UUID?

Just asking if I should let the row ID out or a UUID to a third party.

[worldly schooner]

CREATE TABLE Blacklist (guild smallint UNSIGNED, channels list ```

hi

so I want guild to be a integer

and channels to be a list of integers

[grim sierra]

Hey! If I just install mysql extension and use . sql to make databases, will it work?

(My teacher taught me to create a python environment first and then connect it, will my method work the same?)

[wet path]

is .sqlite3 file a blob file?

[grim vault]

It's most likely a whole database because a sqlite database is a file.

[stark sparrow]

which database shd i use for making the levelling and economy commands for my bot if i am using replit?

[harsh pulsar]

Afaik it should do those automatically. But none of those are text data! They're all named data types

[harsh pulsar]

Noted. I have no idea then, but your code is a bit dense because of the * trick. Maybe try simplifying it with boring ifs and see if the bug goes away. Often mysterious sql errors come from syntax errors

[velvet coyote]

wdym automatically?

[harsh pulsar]

You'll have to actually set up the database, making user accounts and permissions and such. And you'll have to run it somewhere. The sql part won't be that different

[harsh pulsar]

If the data type in the database is inet, it should emit an IPAddress object. i think?

[harsh pulsar]

I'd personally feel safer with the UUID

[harsh pulsar]

No. A sqlite database is just a file, but a mysql database is more complicated. You need to install and run the mysql server application and interact with it, in order to use a mysql database

[velvet coyote]

This is what I want to do,
x = 'yyyyyy
when inserting the value in the table whose datatype would be VARCHAR, and VARCHAR is str in python want to make sure that x is a string and vice versa

[harsh pulsar]

I'm pretty sure that is a table of all the conversions that asyncpg automatically performs

I certainly wouldn't worry about strings being loaded as anything other than strings

[velvet coyote]

ok then

[autumn condor]

okay alright thanks

[stark sparrow]

Any way to use RethinkDB with replit?

[sour nova]

now how can i convert 2016-06-22 20:44:52.134125-07 into datetime object

in python

2016-06-22 20:44:52.134125-07 is a string

[harsh pulsar]

!d datetime.datetime.strptime

[delicate field]

datetime.datetime.strptime

classmethod datetime.strptime(date_string, format)```
Return a [`datetime`](https://docs.python.org/3.10/library/datetime.html#datetime.datetime "datetime.datetime") corresponding to *date\_string*, parsed according to *format*.

This is equivalent to:

```py
datetime(*(time.strptime(date_string, format)[0:6]))
```  [`ValueError`](https://docs.python.org/3.10/library/exceptions.html#ValueError "ValueError") is raised if the date\_string and format can’t be parsed by [`time.strptime()`](https://docs.python.org/3.10/library/time.html#time.strptime "time.strptime") or if it returns a value which isn’t a time tuple. For a complete list of formatting directives, see [strftime() and strptime() Behavior](https://docs.python.org/3.10/library/datetime.html#strftime-strptime-behavior).

[harsh pulsar]

@sour nova ☝️

Depending on your database and database library, the data might be stored as some kind of timestamp type in the database, and might be converted automatically

[sour nova]

oh

i got it so i am using pg

and using asyncpg for python

so everytime i fetch it will return that as a datetime object

am i right?

[grim sierra]

thank you so much :)

[harsh pulsar]

I believe so

[sour nova]

well this is really helpful thanks a lot

😄

[faint blade]

If you use the type of it. Of course you won't if you store them as ISO strings. But if you use the Postgres timestamp type yes

[sour nova]

yeah i am storing it in timestamp datatype

[faint blade]

Why are they naive if you don't mind me asking?

I've always found it easier and more stable to use aware objects.

[zinc whale]

I think I know what it may have been, but I’ve been at school and unable to test. When I was handing in placeholders, I wasn’t giving the name of the column it’d be filling in, just the name of the data it’d be filling in. Worth a shot

[mellow yoke]

Is mongoDB any good?

[faint blade]

Depends, use it when it's too hard to do in SQL

At least in here, from experience of looking at this channel, there's not equally many here knowledgeable in MongoDB. Additionally, SQL has been out for soooo long

It's much easier to get help with SQL

[harsh pulsar]

The stuff inside the () should be the key in the dict, and i guess the stuff before the = should be the db column name

dbColname=%(dictKey)s 👈 That is what i would assume

[mint tundra]

Hey, did anyone ever connected a python program to Cleardb on Heroku? Having trouble finding info about it on Youtube or the web

[grim vault]

But you do not use colname=value for the VALUES() list in an insert statement.

[grim vault]

I would do it like:

#creates a record in the Phonebook
#Returns Bool(did operation succeed?), ErrorMessage(if something went wrong, what?)
def CreateContactEntry(FirstName, Surname=None, PhoneNumber=None):
    #-----Input Validation-----
    #ensure we have the required FirstName parameter
    if FirstName in ("", None):
        #Invalid! FirstName has to be something!
        return False, "First Name is a required field!"

    #create the dictionary of values to send in
    ValuesDict = {
        "FirstName": FirstName,
        "Surname": Surname or None,             # convert empty string to None for NULL
        "PhoneNumber": PhoneNumber or None,
    }

    #create sql query
    columns = ",".join(ValuesDict)
    values = f"%({')s,%('.join(ValuesDict)})s"
    sql = f"INSERT INTO contacts ({columns}) VALUES ({values});"

    #-----create contact entry-----
    #send sql query
    print(f"{sql=}, {ValuesDict=}")
    cursor.execute(sql, ValuesDict)
    #commit our changes
    phonebookdb.commit()

    #if we got thus far, it must have been a valid query. return true
    return True, None

I would not care about if a parameter is there or not because missing columns in the insert will be filled with NULL anyway.

[zinc whale]

oh. i see. i didn't know that. so i don't need to go around making parameterised queries on the fly?

[grim vault]

If you have a DEFAULT clause, you'll need to parametrize it (and for UPDATEs).

You can do it like:

columns = ",".join(key for key, val in ValuesDict.items() if val)
values = f"%({')s,%('.join(key for key, val in ValuesDict.items() if val)})s"

[cunning jolt]

that looks insecure af

never use f strings for sql queries

they're extremely vulnerable to injection attacks

[grim vault]

The ValuesDict is program controled and no user data is used for the colname or value list.

[cunning jolt]

not really

[grim vault]

The generated sql is with binding parameters: 'INSERT INTO contacts (FirstName) VALUES (%(FirstName)s);'

[cunning jolt]

oh wait, my bad

i completely didnt see that

yeah this is how you should do it

[polar locust]

how would i use begin and commit in python?

do i do

database.execute('begin')
...code
...code
database.execute('commit')

[zinc whale]

actually i just realised that i'm dumb as hell and was overthinking the whole thing, i can just pass null and it'll work fine, with no on-the-fly sql generation

[polar locust]

how can i clear my sqlite database file with python

i gotta run tests with indexing so i gotta insert one million lines a couple of times. if i dont remove the lines its gonna give an error because the IDs are unique

[polar locust]

Delete * from table?

[harsh pulsar]

It looks like cleardb is mysql? If so, you can use any mysql library

[harsh pulsar]

Just delete the file and re-create it

Or TRUNCATE the tables

Or DROP them

Or use the in-memory sqlite

If you use ":memory:" as the filename it keeps all data in a transient in-memory database

[lime abyss]

Hi all, I'm running into a problem that I can tell has a simple solution but google hasn't been helping. I have a database set up in postgreSQL and am trying to connect to it in python using psycopg2 (following the guide at https://www.postgresqltutorial.com/postgresql-python/connect/). I'm getting the error: psycopg2.OperationalError: FATAL: database "MCU_Morals" does not exist. It makes sense to me that my script wouldnt know where to find the database, but the tutorial doesn't mention anything about this. Anyone know the fix? Thanks in advance.

[harsh pulsar]

can you post the actual setup steps you performed, and show the code that you're using to connect?

!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.

[lime abyss]

yeah np, one moment

[lime abyss]

heres the paste, I'm following the guide I posted line for line but the error is on line 7, before it even gets to the function. https://paste.pythondiscord.com/wezowozefu.py

[harsh pulsar]

makes sense. sounds like the MCU_Morals database never got created

[lime abyss]

it does exist, i've been doing querries on it for a few days now

[harsh pulsar]

hm

is this server running on your local machine?

are you connecting on the right port? is it possible that you have two postgres servers running?

[lime abyss]

its running locally, unsure about the port, how would i check what what port psycop2 is using?

i double checked the servers, only have one running

something weird is going on. i get a password error when i use the wrong password...

so its connecting to the right place, but not finding the database

i checked the name, there are no hidden spaces

yeah it's something strange going on with the name. thanks @harsh pulsar , looks like its just your everyday debugging issue lol.

[harsh pulsar]

are database names case sensitive?

it might be mcu_morals

(i wouldn't use capital letters in sql names anyway... case sensitivity can be weird)

[polar locust]

How would you do this?

[sour nova]

hey my pg saying the data type timestamptz doesnt exist when timestampt is working fine

i have pg version 13.4

[faint blade]

I've never heard of a timestampz data type so that sounds correct

[torn sphinx]

can someone send me a tutorial on a big but quick db

[pale lava]

Pymongo:
how can i use update_one with $unset to remove a nested list without removing the outer list? ```py
Document = {
"outerlist": {
"nestedlist1": {},
"nestedlist2": {}
}
}
myCollection.update_one(document, {"$unset": {"outerlist": "nestedlist2"}})

this removes the entire outerlist which isnt what i want

[harsh pulsar]

@pale lava does this work? {"$unset": "outerlist.nestedlist2}

my mongodb skills aren't great either, but that seems like a thing mongo would support

[polar locust]

why does this fail on line 4?

import sqlite3
db = sqlite3.connect('file.db')
db.execute('INSERT INTO Test (x) VALUES (1);')
db.execute('BEGIN')
db.execute('SELECT x from test')
db.execute('BEGIN')
db.execute('UPDATE TEST SET X=2')
db.execute('SELECT X FROM TEST')
db.execute('COMMIT')
db.execute('COMMIT')

[slow pawn]

What's the error?

[polar locust]

"sqlite3.OperationalError: cannot start a transaction within a transaction"

[slow pawn]

Error seems clear

[polar locust]

i got the impression that the first select should give something

[slow pawn]

You haven't committed your insert. It's an operation that changes values, so I believe it starts a transaction for you

[polar locust]

i got a task that has 4 questions. what does the 1st select and 2nd select give

what you mean?

the last 2 questions are if the first transaction works and if the 2nd works too

[slow pawn]

All examples for sqlite in Python show that you have to commit your changes after inserts and stuff
https://docs.python.org/3/library/sqlite3.html
Eg in second code block - they never explicitly opened the transaction but they still need to commit

[polar locust]

okay. whats the right syntax for this then?

just for clarity btw my file.db has one line of x and it has value 1

[slow pawn]

You need to db.commit() after insert, I believe

[polar locust]

i removed the insert as i dont need it

shouldve edited

[slow pawn]

Okay. So what's the error now?

[polar locust]

my table already has the one line i need

its the same

import sqlite3
db = sqlite3.connect('file.db')
db.execute('BEGIN')
db.execute('SELECT x from test')
db.execute('BEGIN')
db.execute('UPDATE TEST SET X=2')
db.execute('SELECT X FROM TEST')
db.execute('COMMIT')
db.execute('COMMIT')

"sqlite3.OperationalError: cannot start a transaction within a transaction"

[slow pawn]

Which line?

[polar locust]

line 5

okay so 2nd transaction doesnt work

first one works second one doesnt

[slow pawn]

https://docs.python.org/3/library/sqlite3.html#controlling-transactions

[polar locust]

it asks me what result does the 1st select and 2nd select give

2nd gives nothing though

because the transaction fails?

[slow pawn]

Transaction cannot be started, yes

[polar locust]

so first select works normally

second doesnt execute because it cant

so what do i respond to what does 2nd select give lol

[slow pawn]

Error? :P

[polar locust]

yeah i tried that too

[slow pawn]

Make sure you were supposed to do the transactions like that

[polar locust]

i think i gotta ask the course..

ill show a pic

just remove the i from testi and u have the same thing lol

but doesnt my code do the same thing as this?

[slow pawn]

If it's supposed to be one query, then it may fail as a whole, I believe. Python reads code line-by-line but when it comes to sql itself, query is often not executed at all when there's an error

I don't really deal with dbs directly, but that's what I recall from my classes, I believe

(I actually came to this channel to ask some questions as well, lol XD)

[polar locust]

lol

yeah ik python does line by line

but id assume it works like sql because im using sqlite3

like i assume my code does what its supposed to in terms of begin and commit

[slow pawn]

When it comes to courses and weird tasks, it's best to use directly what was used in the course. In case of such inconsistencies

[polar locust]

yeah i tried downloading sqlite lol but didnt figure out how to

[austere portal]

the sqlite module already starts a transaction

[harsh pulsar]

There is no standalone Sqlite program. It is a file format and a library. However you can use the program DB Browser to interact with Sqlite databases

[slow pawn]

Polodb vs elaradb
Anyone heard of any of those? Those are lightweight embedded mongo-like bases.
I'm supposed to choose one of them (or something similar) for a project.

[grim vault]

Is the course a general SQL course? Because there are databases which do support nested transactions, SQLite isn't just one of them.

[polar locust]

yeah, but they use sqlite

[slow pawn]

I think I'm drawn to Elara despite it having less stars (polo has multiple bindings, so stars might also be for non-python bindings?). The docs seem more concise, have examples and stuff

[grim vault]

Doesn't mean the question is SQLite specific or just a general one.

[polar locust]

what you mean?

[grim vault]

If the question is SQLite specific, the answer is: You can't start a nested transaction.

[polar locust]

the question is what will the first select give, second select give, will first tx work, will second tx work

[polar locust]

^

apparently both selects should give something, even if the tx fails

[autumn condor]

Hi, I’m building an app and here I’ve users, I read in few places it’s better to keep user profile information separate from the main user credentials table. I’m using django and Postgres, is this followed practice?

[supple shard]

Can anyone provide the info of various db available

[slow pawn]

There are so many dbs that you need to specify what you want

[cunning jolt]

timestamptz is a pg type

difference being that pg converts incoming timestamps to utc

which means its usually the recommended format

[grim vault]

from the doc:

The SQL standard requires that writing just timestamp be equivalent to timestamp without time zone, and PostgreSQL honors that behavior. timestamptz is accepted as an abbreviation for timestamp with time zone; this is a PostgreSQL extension.

[faint blade]

Oh nice I always use TIMESTAMP WITH TIME ZONE 'UTC'

[balmy ivy]

File "/opt/virtualenvs/python3/lib/python3.8/site-packages/pymysql/connections.py", line 613, in connect
sock = socket.create_connection(
File "/usr/lib/python3.8/socket.py", line 808, in create_connection
raise err
File "/usr/lib/python3.8/socket.py", line 796, in create_connection
sock.connect(sa)
OSError: [Errno 99] Cannot assign requested address

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
File "Assignment2.py", line 12, in <module>
connection = pymysql.connect(host="localhost",user="root",passwd="",database="patientdata" )
File "/opt/virtualenvs/python3/lib/python3.8/site-packages/pymysql/connections.py", line 353, in init
self.connect()
File "/opt/virtualenvs/python3/lib/python3.8/site-packages/pymysql/connections.py", line 664, in connect
raise exc
pymysql.err.OperationalError: (2003, "Can't connect to MySQL server on 'localhost' ([Errno 99] Cannot assign requested address)")

what is the issue?

[carmine turret]

sqlite is cute so far. can i ask:
if i am going to be accessing (select insert) a database for an extended period (say a bot will be online for hypothetically weeks or even months at a time), should i open the connection once (and then close at exit), or every time i access something?

[burnt cloak]

how can i move a column into the middle of two others in mysql??

[carmine turret]

(Ref: https://stackoverflow.com/questions/1605144/how-to-change-column-order-in-a-table-using-sql-query-in-sql-server-2005)
There is no reason to do this - the order is entirely irrelevant. If you want to retrieve the columns in a different order though, you can specify:

SELECT column1, column2 FROM table

[harsh pulsar]

As far as i know that should be safe. Sqlite has a system for locking to protect the database file from getting corrupted. Normally in a long running application you would have a long running connection (or pool of connections) that you reuse

For low volumes of queries you can get away with reconnecting once per query

[carmine turret]

I see, so SQLite specifically works with one connection, whereas most other (server dbs?) work better with multiple for each query?

[harsh pulsar]

No

You never want more than one connection per query

That wouldn't even make sense

[carmine turret]

Oh I completely f'ed up the phrasing

Multiple connections, one for each query (in other dbs)

My bad

Anyways thank you

[harsh pulsar]

No you wouldn't normally do this. Connecting and disconnecting is slow and doesn't provide any benefit

You might have one or more connections that you keep open and reuse for the life of the application

I am saying that this should be fine with sqlite, just like with a "server" database

[carmine turret]

Oh, okay. Thanks.

[burnt cloak]

mysql> alter table expstorage
    -> alter column userxp bigint(18446744073709551615) not null default 0;
ERROR 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 'bigint(18446744073709551615) not null default 0' at line 2

what wrong

[torn sphinx]

please dm me the best allrounder db to use, i got no clue about db's (mainly for lite emailing services or web dev stuff) thanks

[torn sphinx]

it says line 2

[burnt cloak]

which

which wrong

in line 2

[faint blade]

Columns have no order

[burnt cloak]

wdym??

[faint blade]

You cannot change the order of columns

Do you want a query that selects the column in that order?

[lime abyss]

Thanks for the tip, I’ll adjust in the future. Also I found the problem, two of my databases were using different ports (not sure why, I didn’t change it manually).

[simple grove]

on Windows you could get a sharing violation

from attempting to open two read/write connections to a SQLite db, iirc

[candid slate]

Why mongodb client does not connect on python executable

[torn sphinx]

can someone dm me a document with visual representations of data bases and the inner working how to connect using python and working with ALOT of data and so on

also another one on cloud storage and how taht works

goodnight

[median quiver]

Hi guys, i need a guide or a recomedation, i am a veteran in python, but new in SQL server and SQL azure, how can i automate a ETL like:
database->SQL_server--> python_code --> SQL_Server

i need to build in SQL azure, i just need a way to run it on server whit few conditions like:
if new database is load
validate
run python_code
validate_output
save_as_new_table_in_SQL

where should i run the python code and how? (any guide or key-word to research in google(i don't find whit my vocabulary) )
use Azure virutal machine? or can i run directly on sql server ?
my goal is to automate retrain of a machine learning algorithm for deploy to a client

[torn sphinx]

hello, I do that:

cursor.execute("SELECT * FROM member WHERE ID = ?", member_id)
```and `member_id` is an `integer`.
So why i got this: 
```py
ValueError: parameters are of unsupported type

[torn sphinx]

also putting this:

cursor.execute("SELECT * FROM member WHERE ID = ?", "7")
```send this:
```py
<sqlite3.Cursor object at 0x000002267BF99960>

Why ?

and the ID is 790601521666588692

ID should be an int, not a str:

ID int PRIMARY KEY UNIQUE

Do someone have an idea ?

.

Problem solved

cursor.execute("SELECT * FROM member WHERE ID = ?", (member_id,))

[loud breach]

Anyone know how to find the _id of a document with only using another element of it eg like this so I can see all the data? In Mongodb btw

                        print(document)```

[jade osprey]

Hi me and my friend have a problom we need to find a way to make it so that both of us can use the same database while using the github repo in postgres

we both want to work with the same data

how?

[median quiver]

SQL server?

[queen laurel]

I have been working on a bot and whenever I run my bot and type the command in a chat somewhere I get this error. Is there anyway someone can help me with this?

[dense plover]

How can I connect mi discord bot with a database and which do you recommend? I am new to databases and I don't know much about them

[topaz wharf]

I recommend you PostgreeSQL in you case you want to handle massive data from many servers, or any case for your private bot SQLite3 is good enough

[dense plover]

do you know about postgreeSQL? Could you teach me?

[desert sandal]

anyone familiar with mongodb querying ?

[steel rover]

so i want to use MySQL for my discord bot, and i want to know if theres a way to work on the same database from different computers like mongodb

[balmy ivy]

File "/opt/virtualenvs/python3/lib/python3.8/site-packages/pymysql/connections.py", line 613, in connect
sock = socket.create_connection(
File "/usr/lib/python3.8/socket.py", line 808, in create_connection
raise err
File "/usr/lib/python3.8/socket.py", line 796, in create_connection
sock.connect(sa)
OSError: [Errno 99] Cannot assign requested address

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
File "Assignment2.py", line 9, in <module>
conn = pymysql.connect(
File "/opt/virtualenvs/python3/lib/python3.8/site-packages/pymysql/connections.py", line 353, in init
self.connect()
File "/opt/virtualenvs/python3/lib/python3.8/site-packages/pymysql/connections.py", line 664, in connect
raise exc
pymysql.err.OperationalError: (2003, "Can't connect to MySQL server on 'localhost' ([Errno 99] Cannot assign requested address)")

How to fix that

I am posting the same stuff for second day?

[torn sphinx]

Are you using docker or something?

[balmy ivy]

I use replit code

import mysql.connector
import csv
import json
d=mysql.connector.connect(
host="localhost",
user="root",
password="")
try:
m=d.cursor()
m.execute("CREATE DATABASE pdatabase")
except:
print("DATABASE ALREADY CREATED...")

[torn sphinx]

Can you try runing this snippet without any IDE in your local machine like copy code to something like temp.py on local drive and run via command line?

[balmy ivy]

can i use notepad to python code and run it through cmd prompt

[torn sphinx]

Yeah you can use no issues

[torn sphinx]

But remeber in notepad to use 4 spaces instead of hitting tab because python compiler might throw error

[balmy ivy]

where can install module for that in python file

[torn sphinx]

You mean that MySQL connector?

[balmy ivy]

I use xampp server

I am trying to write a python code to create a data record in mysql

[torn sphinx]

Xampp server is okay but try running the code locally if it works then replit might be causing issue in running the code

IDK what is replit and how it works

[balmy ivy]

replit is online IDE

[torn sphinx]

Agh I got the issue online ide won't let you connect to MySQL on your local machine

It would be good/best practice to use a local development environment like install python and ide/editor in your local machine and do the coding 😁

[steel rover]

???

[delicate field]

Hey @high notch!

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

[balmy ivy]

using a local environment also error occurs

[torn sphinx]

what is the error?

I guess error might be different now

[faint blade]

Well, you'll have to expose the database to the internet but yes.

[steel rover]

oh ok i'll look into that

[primal notch]

A mongodb question

I need to save trees of objects of the same type. If I want to embed them into each other as children, how do I find one exact object by its id?

Should I instead avoid embedding and save them with links to parentID?

Or links to childIDs

Damn mongo makes me with to have used Postgres instead

Embedding them into each other seems like a good choice because if I delete a root object, all its children are deleted too. But it makes querying much worse

[brazen charm]

Why dont you just set up a foreign reference in mongo

[dapper mesa]

How do I do a google search in python

[burnt cloak]

else:
            newXP = result[0][0] + xp
            cursor.execute("UPDATE expstorage SET userxp = " + str(newXP) + " WHERE client_id = " + str(message.author.id))
            cursor.execute(f"UPDATE expstorage SET name_discriminator = {namediscrim} WHERE client_id = {str(message.author.id)}")
            expstorage.commit()

Ignoring exception in on_message
Traceback (most recent call last):
  File "C:\Program Files\Python39\lib\site-packages\discord\client.py", line 343, in _run_event
    await coro(*args, **kwargs)
  File "c:\Users\luffy\Desktop\All Things\Code File\Python\Discord.py\MineHardcore.py", line 142, in on_message
    cursor.execute("UPDATE expstorage SET name_discriminator = " + str(message.author) + " WHERE client_id = " + str(message.author.id))
  File "C:\Users\luffy\AppData\Roaming\Python\Python39\site-packages\mysql\connector\cursor.py", line 568, in execute
    self._handle_result(self._connection.cmd_query(stmt))
  File "C:\Users\luffy\AppData\Roaming\Python\Python39\site-packages\mysql\connector\connection.py", line 846, in cmd_query
    result = self._handle_result(self._send_cmd(ServerCmd.QUERY, query))
  File "C:\Users\luffy\AppData\Roaming\Python\Python39\site-packages\mysql\connector\connection.py", line 656, in _handle_result
 to use near 'Flownery#9467 WHERE client_id = 687639757693648958' at line 1

my name has a space so mysql doesnt update it

[grim vault]

Use parameter bindings (edit: changed ? to %s for mysql-connector):

else:
    newXP = result[0][0] + xp
    cursor.execute(
        "UPDATE expstorage SET userxp = %s, name_discriminator = %s WHERE client_id = %s",
        (newXP, namediscrim, message.author.id)
    )
    expstorage.commit()```

[burnt cloak]

Ignoring exception in on_message
Traceback (most recent call last):
  File "C:\Program Files\Python39\lib\site-packages\discord\client.py", line 343, in _run_event
    await coro(*args, **kwargs)
  File "c:\Users\luffy\Desktop\All Things\Code File\Python\Discord.py\MineHardcore.py", line 143, in on_message
    cursor.execute("UPDATE expstorage SET userxp = ?, name_discriminator = ? WHERE client_id = ?",(newXP, namediscrim, message.author.id))
  File "C:\Users\luffy\AppData\Roaming\Python\Python39\site-packages\mysql\connector\cursor.py", line 559, in execute
    raise errors.ProgrammingError(
mysql.connector.errors.ProgrammingError: Not all parameters were used in the SQL statement

[faint blade]

That's weird, there's 3 ? and 3 arguments passed.

[grim vault]

Mysql need %s instead of the ?, change that.

I didn't check which database module is in use.

[grim vault]

change all three ? to %s

[burnt cloak]

ok

[brazen charm]

mysql can use either

fun facts™️

although it can be a bit funky in older versions

[dense plover]

Traceback (most recent call last):
  File "C:\Users\ronal\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\ronal\Desktop\Proyectos\database\cogs\config.py", line 12, in prefixset
    data = await self.client.prefixes.find(ctx.guild.id)
  File "C:\Users\ronal\AppData\Local\Programs\Python\Python39\lib\site-packages\pymongo\collection.py", line 1532, in find
    return Cursor(self, *args, **kwargs)
  File "C:\Users\ronal\AppData\Local\Programs\Python\Python39\lib\site-packages\pymongo\cursor.py", line 173, in __init__
    validate_is_mapping("filter", spec)
  File "C:\Users\ronal\AppData\Local\Programs\Python\Python39\lib\site-packages\pymongo\common.py", line 495, in validate_is_mapping
    raise TypeError("%s must be an instance of dict, bson.son.SON, or "
TypeError: filter must be an instance of dict, bson.son.SON, or any other type that inherits from collections.Mapping

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

Traceback (most recent call last):
  File "C:\Users\ronal\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\ronal\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\ronal\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: TypeError: filter must be an instance of dict, bson.son.SON, or any other type that inherits from collections.Mapping ```

can someone help me with this problem?

    @commands.command()
    async def prefixset(self, ctx, prefix=None):
        if prefix is None:
            return await ctx.send('Inserta prefix')
        data = await self.client.prefixes.find(ctx.guild.id)
        if data is None or "prefix" not in data:
            data = {"_id": ctx.guild.id, "prefix": prefix}
        data["prefix"] = prefix
        await self.client.prefixes.upsert(data)
        await ctx.send('prefix nuevo')```

[brazen charm]

you need to pass it as a dict

{"field_to_search": value}

[rustic saffron]

Hi, I wanted to ask how I could do so that when a value changes, it does a function?

in firebase with pyrebase

[toxic basalt]

Hello! How can I in sql select a id but utput it as a name from another table where a refrence been made with id?

[faint blade]

You need a join or subquery

[agile heath]

why does it send this error when i have a column called expires
Command raised an exception: UndefinedColumnError: column "expires" of relation "reminders" does not exist

what am i doing wrong?

[fading patrol]

Show your code? And you able to access other columns from the same table?

[pure mortar]

anybody used pymongo? how is it

[delicate field]

@merry lion Please don't try to ping @everyone or @here. Your message has been removed. If you believe this was a mistake, please let staff know!

[warped turtle]

Hey guys. I'm using SQLITE3.
Here's my table:

cur.execute('''CREATE TABLE IF NOT EXISTS user_stats (guild_id, member_id, command_name, uses)''')

How would I insert into the table if update fails? Since it has no unique key

is there a unique SQL thing I could send? or would I just have to grab the count of how many entries there are for member_id and command_name?

[frail glade]

for mongodb assuming i have:

{"temp": "test", "temp1": "test1", "temp2": 0},
{"temp": "test1", "temp1": "test2", "temp2": 1},
{"temp": "test", "temp1": "test3", "temp2": 2},```

through pymongo

how can i get a return value of

[{"temp": "test", "temp2": 2}, {"temp": "test1", "temp2": 1}]

aka: I want temp to be unique, I want to return multiple fields, and I want to get the biggest temp2

[steel rover]

ok so im trying to add a database to my discord bot, and i chose MySQL after i struggled to make a prefix command in mongodb. My question is, how can i access the database from different computers which arent going to be on the same network? Also, do i need to have an always online PC for that?

[steel rover]

???

[grim vault]

You can do:

cur.execute(
    "UPDATE user_stats"
      " SET uses = uses + 1"
    " WHERE guild_id = ?"
      " AND member_id = ?"
      " AND command_name = ?",
    (ctx.guild.id, ctx.author.id, cmd_name)
)
if cur.rowcount == 0:
    cur.execute(
        "INSERT INTO user_stats(guild_id, member_id, command_name, uses)"
        " VALUES(?, ?, ?, ?)",
        (ctx.guild.id, ctx.author.id, cmd_name, 1)
    )

[elder elk]

how can i create temp table from select query form X database to insert it into Y database?

in postgresql

[unkempt prism]

Have you looked into foreign data wrappers (fdw)? Essentially you can create a "foreign" table on Y db that points to tables on X and do what you like with it.

[elder elk]

ok will check

[winged socket]

like how to store them

wait no

I mean

How to add data using an input's value into a panda DataFrame

[warped turtle]

I am using aiosqlite.

cur.execute('''CREATE TABLE IF NOT EXISTS comstats (guild_id, command_name, uses)''') #Table
commands_cursor = await db.execute("SELECT * FROM comstats WHERE guild_id = ?", (guild_id)) #SQL
``` What am I doing wrong?

```py
    @commands.command()
    async def comstats(self, ctx):
        async with aiosqlite.connect('bot.db') as db:
                #cur.execute('''CREATE TABLE IF NOT EXISTS statistics (guild_id, command_name, uses)''')
            guild_id = ctx.guild.id
            command_name = ''
            command_uses = ''
            commands_cursor = await db.execute("SELECT * FROM comstats WHERE guild_id = ?", (guild_id))
            commands_rows = await commands_cursor.fetchall()
            for i in commands_rows:
                command_name += f"{i[0]}\n"
                command_uses += f"{i[0]}\n"
                
            embed = discord.Embed(title="Statistics for all commands.")    
            embed.add_field(name="Commands", value=command_name)
            embed.add_field(name="Uses", value=command_uses)
            await ctx.send(embed=embed)
``` Full code

I set i to 0 to test and it still wont work

Figured it out.

[burnt cloak]

how can i check mysql if a same value is exist( I mean like i have a and b column, a = 0 and b = 1, i want to check if my data i update has a = 0 and b = 2 then it will insert a new, not update

[burnt cloak]

mysql> select userxp from expstorage where client_id = 687639757693648958
    -> select guild_id_used from expstorage;
ERROR 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 'select guild_id_used from expstorage' at line 2

where

what wrong

[grim vault]

You didn't end the first select with ;

[burnt cloak]

xp = generateXP()
        cursor = expstorage.cursor()
        cursor1 = expstorage.cursor()
        cursor.execute("SELECT userxp FROM expstorage WHERE client_id = " + str(message.author.id))
        cursor1.execute("SELECT guild_id_used FROM expstorage")
        result = cursor.fetchall()
        result1 = cursor1.fetchall()
        guild_id = result1[0][0]
        userxp = result[0][0]

Ignoring exception in on_message
Traceback (most recent call last):
  File "C:\Program Files\Python39\lib\site-packages\discord\client.py", line 343, in _run_event
    await coro(*args, **kwargs)
  File "c:\Users\luffy\Desktop\All Things\Code File\Python\Discord.py\MineHardcore.py", line 134, in on_message
    cursor = expstorage.cursor()
  File "C:\Users\luffy\AppData\Roaming\Python\Python39\site-packages\mysql\connector\connection.py", line 1183, in cursor
    self.handle_unread_result()
  File "C:\Users\luffy\AppData\Roaming\Python\Python39\site-packages\mysql\connector\connection.py", line 1455, in handle_unread_result
    raise errors.InternalError("Unread result found")
mysql.connector.errors.InternalError: Unread result found

what

in mysql it worked

[unkempt niche]

What would be the best way to access several large csvs of data and compare them through python? I'm not really sure how to start with this

[thorn canyon]

where can I get this information for PostgreSQL connectuser="", password="", host="", port=""

[ionic pecan]

you need to run the fetchall directly after the execute, you cannot group them like this

        cursor.execute("SELECT userxp FROM expstorage WHERE client_id = " + str(message.author.id))
        result = cursor.fetchall()
        cursor1.execute("SELECT guild_id_used FROM expstorage")
        result1 = cursor1.fetchall()

like this

[delicate field]

Hey @proud lily!

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

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

[proud lily]

I need help in generating a ms access database report, anyone to assist me?

[unkempt prism]

Are you comparing the contents or entire files? For example if each file is has the same column order, row order you can just just compare contents of the entire text files. If its only rows out of order I'd likely just use sort on unix command on the terminal.

If each file are different columns and column order you will need to parse the files into a data structure to compare. If they fit in memory you might be able to put each in a pandas dataframe using read_csv and from there you can compare the dataframes.

[rustic python]

I have a question about the replication in Redis. I saw that there is a feature to syncronize between multiple clusters in Redis (https://docs.redis.com/latest/rs/concepts/intercluster-replication/), but It's a enterprise solution. If both my cluster are next each other (ex: one cluster are in France and other in Spain), do I need to use this feature? Can't considered one single cluster?

[burnt cloak]

ok thanks

[sudden owl]

Anybody have an easier way to convert dictionaries to csv files besides the csv module? Mine are nested.

[gleaming viper]

would this be the right channel for JSON?

[sudden owl]

I would start by importing csv and pandas modules. They are great for csv analysis. Ironic given my last post; however, dictionaries are different.

[unkempt prism]

How do you want it to look:

Eg

{
  "a": {"i": 1},
  "b": 2,
  "c": {"iii": 3},
}

[sudden owl]

I currently have my dictionary like this:

I would like it to be in a csv with these columns. But each title and subtitle go to the same article and it is causing duplication with empty cells.

idk why this isnt working lol

[unkempt prism]

Format it please?

[delicate field]

Hey @sudden owl!

It looks like you tried to attach file type(s) that we do not allow (.ipynb). 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.

[sudden owl]

this is more formatted. The values are empty.

title, subtitle, and general article details are in one dictionary indexed by the article id.

in the image above 0 is the id

[unkempt prism]

ohh I see

Is it as simple as creating a new list of dicts using

flat_list = [v['article_title_stats'] for k,v in df_analysis.items()]  # v is still a dict of the values within article_title_stats

[sudden owl]

wow! yes it was...

you're amazing thank you!

[unkempt prism]

Still need to work out a way of dealing with article_title_stats as well. Though I guess that can be done separately.

[sudden owl]

so, what you did was created a flat... list. yes. that's true.

[unkempt prism]

I'm using list comprehension to create a flat list yes. Though this only takes article_title_stats currently

[sudden owl]

so would I do that for subtitle sep?

[unkempt prism]

do you want it on the same row. Or should it be a separate row?

[sudden owl]

flat_list_title = [v['article_title_stats'] for k,v in df_analysis.items()] # v is still a dict of the values within article_title_stats ----> tourdownunder on discord helped here
flat_list_subtitle = [v['article_subtitle_stats'] for k,v in df_analysis.items()] # v is still a dict of the values within article_title_stats ----> tourdownunder on discord helped here
print(flat_list_title[2])
print("\n")
print(flat_list_subtitle[2])

This seems to work for at least identifying the subtitle piece. It would be best on the same row.

The claps,read_time,responses variables apply to an article and the title/subtitle are also both applying to one article

[unkempt prism]

your csv will need to prefix conflicting column names with title_ or subtiltle_ etc

[sudden owl]

I see

Don't keel over but here's one way I was working to do that

[unkempt prism]

awesome your all over it.

[sudden owl]

I appreciate the help. If you have any thoughts just DM me!

[steel rover]

can someone help please?

[edgy bough]

Hi! Wondering if someone could give me a hand with a few lines of code

[oak swan]

 mysql -u root -p nextcloud -e "select * from oc_share INTO OUTFILE '/root/sql/test.txt'"```

i run this it throw this error

ERROR 1 (HY000) at line 1: Can't create/write to file '/root/sql/test.txt' (Errcode: 13 "Permission denied")

[jade osprey]

I don't see what I did wrong

my code

conn =psycopg2.connect(dbname='Projects',user=os.getenv('POSTUSER'),password=os.getenv('POSTPASS'))
c = conn.cursor()

c.execute('CREATE TABLE user (username varchar(255),password varchar(255),isadmin boolean,Status text,PRIMARY KEY (username))')
print('created table')

the error

Traceback (most recent call last):
  File "/Users/mainuser/Desktop/Projects/app.py", line 7, in <module>
    c.execute('CREATE TABLE user (username varchar(255),password varchar(255),isadmin boolean,Status text,PRIMARY KEY (username))')
psycopg2.errors.SyntaxError: syntax error at or near "user"
LINE 1: CREATE TABLE user (username varchar(255),password varchar(25...
                     ^

[alpine trail]

can someone help me with this error

[rustic saffron]

import * as firebase from "firebase/app";
^^^^^^

SyntaxError: Cannot use import statement outside a module
    at wrapSafe (internal/modules/cjs/loader.js:1001:16)
    at Module._compile (internal/modules/cjs/loader.js:1049:27)
    at Object.Module._extensions..js (internal/modules/cjs/loader.js:1114:10)
    at Module.load (internal/modules/cjs/loader.js:950:32)
    at Function.Module._load (internal/modules/cjs/loader.js:790:14)
    at Module.require (internal/modules/cjs/loader.js:974:19)
    at require (internal/modules/cjs/helpers.js:92:18)
    at Object.<anonymous> (/root/Copsneak/test/bot1-bot2/src/index.js:2:25)
    at Module._compile (internal/modules/cjs/loader.js:1085:14)
    at Object.Module._extensions..js (internal/modules/cjs/loader.js:1114:10)
    at Module.load (internal/modules/cjs/loader.js:950:32)
    at Function.Module._load (internal/modules/cjs/loader.js:790:14)
    at Function.executeUserEntryPoint [as runMain] (internal/modules/run_main.js:76:12)
    at internal/main/run_main_module.js:17:47

[burnt citrus]

Hi, i have a question about Pandas
I'm a beginner, and i try to do this : mydata.price.fillna(20)
I want to fill the NA value in the column "price" with 20.
But it doesnt work, after i do it if i do : print(mydata['price'].isna().sum())
There is still NA

What do i do wrong ? Thanks

[winged socket]

hi

do you know how to print the top column of a pandas describe()

[burnt citrus]

Yeah but i think i found it thanks, i needed to do this
mydata.price.fillna(20, inplace=True)

[faint blade]

I thought I already said that you need to somehow expose your database to the internet?

Perhaps you can use a host that specializes in SQL databases, the only one I know is ElephantSQL but that's for Postgres. Maybe do some quick Google searches?

[proud lily]

@faint blade can you help me in generating a ms access simple report>

[faint blade]

I do not have Microsoft Access and I don't know what that is, otherwise I would've taken a look at your question sorry.

Have you not had any luck Googling about it?

[proud lily]

ms acess database, everyone usally have it.

[faint blade]

In an enterprise setting yeah I believe it's the most popular

But I am just a hobbyist, my experience consists of PostgreSQL, SQLite and a little bit of MySQL/MariaDB.

[proud lily]

okay..

anyone you know who can help me?

[faint blade]

Personally? I don't think so, have you tried some SQL oriented servers?

[cunning jolt]

even there, i think postgres has become the most popular choice in regards to relational dbs

[faint blade]

Yeah it is, but existing enterprise software won't change so MS Access is still the most popular purely as technical debt.

[cunning jolt]

true

[pure mortar]

ah gotta love that technical debt

[ornate matrix]

anyone know how i could now get something else from the document like the logschannelid thats there?
https://cdn.discordapp.com/attachments/881309496385884180/891347067174268938/unknown.png

[paper bluff]

I think what the user meant was that "everyone has it" because Access is included in Msft Office professional suites. I've not heard of Msft Access actually being used in a corporate setting with any kind of tech focus

[faint blade]

Oh?

I may have been thinking about SQL Server?

[paper bluff]

ymmv, I've not worked in many places lol

yes perhaps, that's definitely popular

[faint blade]

Which is Microsoft's SQL database

Is MS Access similar?

[paper bluff]

mmmm I'd say generally no. it's kind of like Microsoft's other Office applications, anyone can use them because it's nearly all GUI even for forms and reports. Access is also severely limited compared to other SQL tools

https://database.guide/microsoft-access-vs-sql-server/

[cunning jolt]

they use a postgres derivative as their azure sql db

or not, they have both apparently

(an sql server derivative as well)

[jade osprey]

Hi I just started using postgres (coming form sqlite3) and I have a question
I want to insert variables and I am using %s or %(datatype) i am encountering an error while using this
an example

    c.execute('INSERT INTO test (username,password) VALUES (%s,%s,%boolean) ',(username, password,False))

the error

c.execute('INSERT INTO test (username,password) VALUES (%s,%s,%(boolean)s) ',(username, password,True))
psycopg2.ProgrammingError: argument formats can't be mixed

what would be the replacement for ? in postgres

[wicked kiln]

Does this look right...

old code removed

fuck it, im too scared of breaking code that relies on the current schema. Im going to do this later in a container...

[wicked kiln]

-- disable foreign key constraint check
PRAGMA foreign_keys=off;
COMMIT;
-- start a transaction
BEGIN TRANSACTION;


-- CREATE NEW MEDIA TABLE
CREATE TABLE IF NOT EXISTS _media (
    mediaid integer primary key autoincrement, 
    rootid integer NOT NULL,
    path text NOT NULL UNIQUE,
    rating numeric,
    hash text,
    CONSTRAINT fk_roots
        FOREIGN KEY(rootid)
        REFERENCES roots(rootid)
);

-- INSERT INTO NEW TABLE
INSERT INTO _media (rootid, path, rating, hash)
SELECT rootid, path, rating, hash
FROM media;



-- CREATE NEW TAGS TABLE
CREATE TABLE IF NOT EXISTS _tags (
    tagid integer primary key autoincrement, 
    mediaid integer,
    name text NOT NULL,
    kind text NULL,
    CONSTRAINT fk_tagging
        FOREIGN KEY(mediaid)               -- this is the reason why
        REFERENCES media(mediaid) 
);

-- INSERT INTO NEW TAGS
INSERT INTO _tags (mediaid, name, kind)
SELECT mediaid, name, kind
FROM tags;



-- DROP AND RENAME BOTH
DROP TABLE tags;
ALTER TABLE _tags RENAME TO tags;
DROP TABLE media;                          -- this is causing me to error
ALTER TABLE _media RENAME TO media;

-- CLEANUP
COMMIT;
--PRAGMA foreign_keys=on;

-- trying to fix it but 
-- Result: FOREIGN KEY constraint failed
-- At line 42:
--- DROP TABLE media;

[burnt cloak]

are there any software to open a new window and show the result of table in mysql?? bcz i don't want to use select * from db everytime

[eternal wave]

Are you referring to something like MySQL Workbench? https://www.mysql.com/products/workbench/

[burnt cloak]

im using Mqsql 8.0 command line client

[grim vault]

The replacement is %s, the other format %(name)s is for using dictionarys, where the name is the dict-key of the value and NOT the datatype, eg:

c.execute(
    'INSERT INTO test (username,password) VALUES (%(user)s,%(passwd)s)',
    {"user": username, "passwd": password}
)

The column and value count must also be the same (you have 2 columns but three values in your example).

[eternal wave]

I'm not sure what you are trying to do, but if you just want a GUI for the database you should try MySQL Workbench (or something similar).

If you are familiar with the Pandas library you can also just convert the results to a DataFrame.

[eternal wave]

This is what the program looks like if you are curious

[burnt cloak]

oh thanks

i want it

[eternal wave]

Is there a way to do a bulk UPDATE, similar to how I am able to do a bulk INSERT in SQL?

Say I have a SQL database used to keep shipment tracking information with the fields: trackingNumber and deliveryDate.

Is there a way to do bulk UPDATE, similar to how I can do a bulk INSERT like this:

INSERT INTO AllShipments (
trackingNumber,
deliveryDate
)
VALUES ("tracking_a","2021-09-20"),
("tracking_b","2021-09-12"),
("tracking_c","2021-09-14")

My goal is to do a bulk INSERT statement so I don't have to update each row individually (say, I want to update the deliveryDate if the tracking number is already in the table).

[eternal wave]

Nevermind, I figured this out. Apparently you can do this (where trackingNumber is the PK):
INSERT INTO AllShipments (trackingNumber , deliveryDate)
VALUES
('tracking_a', '2021-09-20'),
('tracking_b', '2021-09-12'),
('tracking_c', '2021-09-14')
ON DUPLICATE KEY UPDATE
deliveryDate=VALUES(deliveryDate)

[unkempt prism]

Nice. This is known as a UPSERT

[dense oar]

Looking for some help with a MongoDB aggregate pipeline - how can I put the returned documents into an array under a key?

I'd like to achieve this:

{
  "matched": 105,
  "documents": [
      {"_id": ...},
      {"_id": ...}
  ]
}

I was able to get the match count like this:

data = list(db["test"].aggregate(
    pipeline=[
        {"$match": {"user": "martin"}},
        {"$count": "matched"},
    ],
    allowDiskUse=True,
))[0]

But can't figure out how to put in the documents under the documents array

[harsh pulsar]

you'll need to use a facet for this i think

@dense oar https://mongoplayground.net/p/gE28-N4LyfY you can do this

using the example data from https://docs.mongodb.com/manual/reference/operator/aggregation/facet/

[dense oar]

That returns "matched": "matched.count"

[harsh pulsar]

untested code, typos, etc

[dense oar]

If I change it to "matched": "$matched.count", it works but it puts the int in an array

[harsh pulsar]

yeah, i was actually surprised by the array

[dense oar]

I don't think you're supposed to use facet for this

As that only works with arrays

[harsh pulsar]

it's the only way to do this that i'm aware of. just use $arrayElemAt i guess

"$arrayElemAt": ["$matched.count", 0]

i have used this exact technique before without needing it, though

[dense oar]

Is there no easier way to put the returned documents under a key?

[harsh pulsar]

this is in the source code from something i actually wrote for work, that runs in production

    return [
        # Apply pagination
        {'$facet': {
            'items': pagination_stages,
            'stats': [{'$count': 'count'}],
        }},
        {'$project': {
            'items': 1,
            'count': '$stats.count',
        }}
    ]

[harsh pulsar]

no, welcome to mongodb

ah, nope i just put the array element extraction later in the code

    total = (results['count'] or (0,))[0]

yeah this is just a weird mongo thing i guess

[dense oar]

How do I deal with the case if there is no match?

Currently I get

{
  "matched": 0,
  "documents": []
}

Which is what I'd like

Instead of [{'matched': [], 'documents': []}]

[jade osprey]

is there a way of inserting without the % something like ?

I just want to insert variables

I was told not to use f-strings

[grim vault]

No, just use %s instead of ?, no f-string required.

# sqlite syntax:
c.execute('INSERT INTO test (username,password) VALUES (?,?)', (username, password))

# equivalent psycopg2 syntax:
c.execute('INSERT INTO test (username,password) VALUES (%s,%s)', (username, password))

The %s is just the placeholder for binding variables in the SQL statement string for psycopg2 module just like the ? is for the sqlite3 module.

[jade osprey]

it gives me an error if I mix datatypes

I want to add a boolean value to this statement

[grim vault]

You can't define a datatype, the type is defined by the column the value is inserted.

[jade osprey]

oh ok thanks

[grim vault]

Here is the list of standard mapping used by the psycopg2 module: https://www.psycopg.org/docs/usage.html#python-types-adaptation

[harsh pulsar]

I literally showed you my code that does this

[torn sphinx]

@client.command()
@commands.cooldown(1, 90, commands.BucketType.user)
@commands.has_permissions(manage_channels=True)
async def remove_goodbye_channel(ctx, channel:discord.TextChannel):
  if ctx.message.author.guild_permissions.manage_messages:
    db = sqlite3.connect('.//database//goodbye.sqlite')
    cursor = db.cursor()
    cursor.execute(f"SELECT channel_id FROM bye WHERE guild_id = {ctx.guild.id}")
    result = cursor.fetchone()
    if result is not None:
      sql = ('DELETE FROM bye channel_id = ? WHERE guild_id = ?')
      val = (channel.id, ctx.guild.id)
      await ctx.send(f'Channel has been removed')
    cursor.execute(sql, val)
    db.commit()
    cursor.close()
    db.close()
``` how can i delete things from db?

[dense oar]

That was done in Python not in the pipeline

[simple grove]

that code looks clean

[torn sphinx]

doesnt matter clean or not clean

[torn sphinx]

@client.command()
@commands.cooldown(1, 90, commands.BucketType.user)
@commands.has_permissions(manage_channels=True)
async def goodbye_message(ctx, *, text):
  if ctx.message.author.guild_permissions.manage_messages:
    
    db = sqlite3.connect('.//database//goodbye.sqlite')
    cursor = db.cursor()
    cursor.execute(f"""SELECT msg1 
                       FROM bye 
                       WHERE guild_id = {ctx.guild.id}""")
    result = cursor.fetchone()

    if result is None:
      sql = ("""INSERT INTO bye(guild_id, msg1)
                VALUES(?,?)""")
      val = (ctx.guild.id, text)
      await ctx.send(f"Message has been set to **'{text}'**")

    elif result is not None:
      sql = ("""UPDATE bye
                SET msg1 = ?
                WHERE guild_id = ?""")
      val = (text, ctx.guild.id)
      await ctx.send(f"Message has been set to **'{text}'**'")

    cursor.execute(sql, val)
    db.commit()
    cursor.close()
    db.close()
``` BETTER?

[simple grove]

to me, yeah

[snow niche]

    @commands.command()
    @commands.guild_only()
    @commands.has_guild_permissions(manage_messages=True)
    @commands.bot_has_permissions(manage_messages=True)
    async def warn(self, ctx, member:discord.Member, *, reason=None):
        """
        Warns a user.
        
        Works only if you and the bot has `Manage Messages` permissions.
        """
        warndb = self.conn
        cursor = warndb.cursor()

        if member == ctx.author:
            emb = discord.Embed(title=f'{self.bot.no} Error',description = "You can't warn yourself!", color=discord.Color.red())
            await ctx.send(embed = emb)
            return

        else:
            cursor.execute('INSERT OR IGNORE INTO warns_data (guild_id, admin_id, user_id, reason) VALUES (?,?,?,?)', (ctx.guild.id, ctx.author.id, member.id, reason))
            warndb.commit()

            em = discord.Embed(title=f"{self.bot.yes} Warned",color=discord.Color.blue())
            em.add_field(name='Moderator:',value=ctx.message.author.mention,inline=False)
            em.add_field(name='Member:',value=member.mention,inline=False)
            em.add_field(name='Reason:',value=reason,inline=False)
            await ctx.send(embed = em)
``` this is my warn code... i need it to be stored with srl no: or if sqlite3 has it, how do i make it so users can remove using the warn ID

[burnt cloak]

Ignoring exception in on_message
Traceback (most recent call last):
  File "C:\Users\luffy\AppData\Roaming\Python\Python39\site-packages\mysql\connector\conversion.py", line 180, in to_mysql
    return getattr(self, "_{0}_to_mysql".format(type_name))(value)
AttributeError: 'MySQLConverter' object has no attribute '_member_to_mysql'

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "C:\Users\luffy\AppData\Roaming\Python\Python39\site-packages\mysql\connector\cursor.py", line 431, in _process_params
    res = [to_mysql(i) for i in res]
  File "C:\Users\luffy\AppData\Roaming\Python\Python39\site-packages\mysql\connector\cursor.py", line 431, in <listcomp>
    res = [to_mysql(i) for i in res]
  File "C:\Users\luffy\AppData\Roaming\Python\Python39\site-packages\mysql\connector\conversion.py", line 182, in to_mysql
    raise TypeError("Python '{0}' cannot be converted to a "
TypeError: Python 'member' cannot be converted to a MySQL type

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "C:\Program Files\Python39\lib\site-packages\discord\client.py", line 343, in _run_event
    await coro(*args, **kwargs)
  File "c:\Users\luffy\Desktop\All Things\Code File\Python\Discord.py\MineHardcore.py", line 160, in on_message
    cursor.execute(f"INSERT INTO expstorage VALUES(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)",(message.author.id, userxp,0,0,0,0,0,0,0, message.author, message.guild.id))
  File "C:\Users\luffy\AppData\Roaming\Python\Python39\site-packages\mysql\connector\cursor.py", line 556, in execute
    psub = _ParamSubstitutor(self._process_params(params))
  File "C:\Users\luffy\AppData\Roaming\Python\Python39\site-packages\mysql\connector\cursor.py", line 435, in _process_params
    raise errors.ProgrammingError(
mysql.connector.errors.ProgrammingError: Failed processing format-parameters; Python 'member' cannot be converted to a MySQL type

huh

help me

[burnt turret]

You're trying to insert message.author which is an instance of discord.Member

You'd want to store something like the user ID instead

[burnt cloak]

but i dont want

[burnt turret]

what do you want to store about the user?

[burnt cloak]

name#discriminator

[burnt turret]

so store str(message.author) (that gives you a string with the format you specified)

it would be better to store user IDs though as usernames and discrims can change but IDs will not

[grim vault]

If you take a closer look at the code you'll see that the id is the first column stored.

(message.author.id, ..., message.author, message.guild.id)

so, looks like both will be stored.

[burnt turret]

aha missed that

you don't really need the username + discrim in any case then; you can retrieve those from discord itself at any time using the user ID that you store

[burnt cloak]

mysql> ALTER TABLE expstorage ALTER COLUMN client_id bigint not null;
ERROR 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 'bigint not null' at line 1

help

[grim vault]

Didn't you get an answer telling you to use modify?

ALTER TABLE expstorage MODIFY COLUMN client_id bigint not null;```

[burnt cloak]

it didn't repond

anything

it's just a underline

showed there

what

[grim vault]

Looks ok?

[burnt cloak]

i fixed it

[burnt cloak]

.

[burnt cloak]

fixed my error when run that command

now it didn't change anything

[grim vault]

Well, the column client_id is bigint not null looking at that screenshot.

[burnt cloak]

??

[grim vault]

primary key?

[burnt cloak]

yes

[grim vault]

If you don't want the column client_id to be the primary key, you'll need to:

ALTER TABLE expstorage DROP PRIMARY KEY;```

[burnt cloak]

ok

thanks

it worked

how can i remove a column that have a same value??

i mean i have 2 column is a 999 0 and same and i want to remove one

[burnt cloak]

Ignoring exception in on_message
Traceback (most recent call last):
  File "C:\Program Files\Python39\lib\site-packages\discord\client.py", line 343, in _run_event
    await coro(*args, **kwargs)
  File "c:\Users\luffy\Desktop\All Things\Code File\Python\Discord.py\MineHardcore.py", line 143, in on_message
    userxp = result[0][0]
IndexError: list index out of range

what

[dim relic]

Is anyone here experienced with mongodb

[thorn canyon]

        db.execute(f'INSERT INTO settings (guild_id, prefix) VALUES ({guild.id}, "h!")')``` why does this code raise this error?
```psycopg2.errors.UndefinedColumn: column "h!" does not exist
LINE 1: ...ettings (guild_id, prefix) VALUES (881861527018283008, "h!")```

[burnt turret]

postgres considers anything in between double quotes to be column names iirc

[harsh pulsar]

Don't use format strings for sql. Use "parameterized queries"

[thorn canyon]

wdym?

[grim vault]

db.execute('INSERT INTO settings (guild_id, prefix) VALUES (%s,%s)', (guild.id, "h!"))```
You use `%s` as placeholders in the SQL statement and give the variables/values to be used in the order they should be used.

[dim relic]

Have u connected your db to ur code

Using the connection URL

What set of dating are you storing

[torn sphinx]

Hey

im using json as data base

and i made a command to set it

when i got to set it it changes in the file but does not do anything

my code nr

#--- prefix set ---
def get_prefix(client, message):
  with open('prefixes.json','r') as f:
    prefixes = json.load(f)

  return prefixes[str(message.guild.id)]
bot = commands.Bot(case_insensitive=True, command_prefix=prefix,  help_command=None)

@bot.event
async def on_guild_join(guild):
  with open('prefixes.json','r') as f:
    prefixes = json.load(f)

    prefixes[str(guild.id)] = '!'

    with open ('prefixes.json', 'w') as f:
      json.dump(prefixes, f, indent = 4)

@bot.event
async def on_guild_remove(guild):
  with open('prefixes.json','r') as f:
    prefixes = json.load(f)

    prefixes.pop(str(guild.id))

    with open ('prefixes.json', 'w') as f:
      json.dump(prefixes, f, indent = 4)

@bot.command()
@commands.has_permissions(ban_members=True)
async def prefix(ctx, prefixset):
  with open('prefixes.json','r') as f:
    prefixes = json.load(f)

    if (prefixset == None):
      prefixset = '!'

    prefixes[str(ctx.guild.id)] = prefixset
    with open ('prefixes.json', 'w') as f:
      
      json.dump(prefixes, f, indent = 4)
      await ctx.send (f'The bot prefix has been changed to {prefixset}')```

[quartz gazelle]

guys

i need help in sqlite3

Traceback (most recent call last):
  File "c:\Users\moon\OneDrive\Coding\Python\dis-bot\main.py", line 63, in <module>
    c.executemany("INSERT INTO guildids ( ? )", guild_ids_list)
sqlite3.OperationalError: near "?": syntax error```

this is error ^

codepy db = sqlite3.connect("./db/guildids.db") c = db.cursor() c.executemany("INSERT INTO guildids ( ? )", guild_ids_list)

[grim vault]

That error from an python script?

[thorn canyon]

yes

[grim vault]

Code please.

[thorn canyon]

    async def _prefix(self, ctx, *, new_prefix):
        db = self.bot.database
        db.execute("UPDATE settings SET prefix = %s WHERE guild_id = %s", (new_prefix, ctx.guild.id))
        await ctx.reply(f'Changed prefix to `{new_prefix}`')```

[quartz gazelle]

:(

[grim vault]

c.executemany("INSERT INTO guildids VALUES ( ? )", guild_ids_list)

[quartz gazelle]

thx

[thorn canyon]

@grim vault this

[grim vault]

Hm, looks ok to me.

[thorn canyon]

to me too

but error...

[grim vault]

Maybe try to quote the sql names:

db.execute('UPDATE "settings" SET "prefix" = %s WHERE guild_id = %s', (new_prefix, ctx.guild.id))```

[thorn canyon]

guild_id too?

[grim vault]

You can, but it's very unlikely that's an keyword.

[thorn canyon]

still same error

and when writing command again getting other error

[grim vault]

That's because you're missing a db.commit()

What's the full traceback?

[thorn canyon]

If you're about second error:

@grim vault Thank you for your help! I understood what was the error and fixed, now it's working

[knotty cloud]

flask sql alchemy, what is the best way to implement a friendship model ? one user object from the User class can have a friendship relationship with another user object from the User class as well

[harsh pulsar]

class Friendship(Base):
    __tablename__ = 'friendship'
    user1_id = Column(Integer, ForeignKey(User.id), primary_key=True)
    user2_id = Column(Integer, ForeignKey(User.id), primary_key=True)

    user1 = relationship('User', foreign_keys='Friend.user1_id')
    user2 = relationship('User', foreign_keys='Friend.user2_id')

The pair of user ids forms a composite primary key

You can then add other columns to describe the friendship status, eg who invited who first, date created, etc

[knotty cloud]

what about the User class ?

nothing to add there ?

[harsh pulsar]

You could add a friends attribute that fetches all the users they are linked to in the friendship table

[knotty cloud]

and what about the Friend in the foreign key, is that another table ?

[harsh pulsar]

No, it should be Friendship

I am on mobile so to save time typing i copied from a stackoverflow post 😉

[knotty cloud]

could you link me to it ?

[harsh pulsar]

Not quite the same setup as yours but similar https://stackoverflow.com/a/18854791

[knotty cloud]

the flask sqlalchemy docs is so poor

[harsh pulsar]

Honestly, I used it once. You probably know as much as I do or more

[harsh pulsar]

I believe there are if/else conditional operators in mongo that you can use for this

[jagged storm]

Speaking of flask-sqlalchemy would building models be identical no matter what database platform you chose?

[harsh pulsar]

in a lot of cases, yes, you can take advantage of sqlalchemy to write a schema that could work on multiple databases. in most cases you don't need (or want) to do this, but it makes it easier to read the code or work on various different apps, because you don't necessarily need to know the details of each different database

[jagged storm]

Ahhh I see, thanks for the quick reply!

I was working on making a models file for postgres, but found out you cant have nested tables.

Example of what I am trying to build. There would be a main account that multiple users could be under, each user would have different tables associated to them. These tables (job info, projects, awards) would have multiple rows in each linking back to the specific user. There may be 4 accounts and a single user could be part of 3 accounts, but their can be no account duplicates. Is this concept possible in Postgres?

[stark sparrow]

I am getting this error. Pls tell me why this is happening
Image

 mongo_url = 'keep dreaming'
    cluster = MongoClient(mongo_url)
    db = cluster['databasel']
    collection = db['level']
    author_id = message.author.id
    guild_id = message.guild.id

    user_id = {"_id": author_id}

    if message.author == client.user:
      return
    if message.author.bot:
      return

    if(collection.count_documents({}) == 0):
      user_info = {"id": author_id, "guildID": guild_id, "level": 1, "XP": 0}
      
      collection.insert_one(user_info)

    await message.channel.send('User has logged in!')

this is my code

pls help me why is it happening

i am making a discord bot

i am using mongodb for levelling and currency system

[torn sphinx]

i don't think you can have a space in your url

[smoky glacier]

hi guys

so i have an error in my mysql code in python