#databases

1 messages · Page 134 of 1

torn sphinx
#

mysql.connector.errors.ProgrammingError: 1054 (42S22): Unknown column 'score' in 'field list'

#

oh shoot

#

how could I set it up to do that?

#

i was copy pastinb ecause ive done that version and still got an error

#

OOO it hink i see the syntax error

#

instead of going through every table and dropping them, it'd be nice if I could just drop the original value from a table and have all other ones referencing it drop as well

#

i did ROUND(SUM insted of ROUND SUM(

#

holy fuggg

#

so why is that a syntax error when i literally have been using round(sum?

restive pilot
proven arrow
torn sphinx
#

so why is your example working when i couldn't get mine to work

restive pilot
torn sphinx
#

that helps a lot, removes a ton of my code lol, thanks @proven arrow

torn sphinx
#

because ive used your syntax and produced an error

#

and now i copy paste it againa dn no error

restive pilot
carmine pilot
#

so

#

hi

#

where is the main chat

torn sphinx
#

i was literally the one who posted the w3schools link dude

#

telling you thats what i was reading

#

can you not read?

#

none of those examples have the sum and round operations being used, it's all hard coded

restive pilot
#

My bad, I saw Lufthansa posted the same link at earlier Today at 8:12 PM UTC+1...

restive pilot
torn sphinx
#

my patience has been tested today because it seems people who like to help people like to talk down to them a lot

#

telling me to "learn SQL" isn't helpful, it's quite dismissive and rude

#

you would see that im trying to learn it, but obvioulsy not everyone can immediately and perfectly grasp things the second they see them

restive pilot
#

What else I'm supposed to tell you? I practically gave you an example because obviously you were stuck, because you said you had"read sql tutorial" earlier. So I though to help out by giving a full example based of your existing code.

torn sphinx
#

the issue here is syntax now, because ive used the update version with round(sum before and was getting a syntax error

restive pilot
#

Alright, that's fine

torn sphinx
#

only after copy pasting a second time your suggestion did it work

#

so my question is, why did round(sum not work when i did set playerresults = round(sum(handresults), 2) from hands

restive pilot
#

To be fear, SQL is quite strict and make sure to check your quotes and back ticks `

torn sphinx
#

that does seem to be an issue for me, i need to find the clear syntax rules for things

#

now i see that i wasn't selecting and that apparently you need to put certain selects into ()'s

restive pilot
proven arrow
#

If your updating data like that from another table it’s also a sign of bad design. But that you can worry about later

torn sphinx
#

so im guessing that all inner queries? have to be in ()'s

#

i understand i have bad design but i want to learn what bad design is and how to make it better if that makes sense

restive pilot
#

Try removing () after SET PlayerResults = (around the select thing... )and click "Build Schema", you will see that you get an SQL error 🙂
As I said, SQL is quite strict on it's syntax.

torn sphinx
#

so in order to grab any value i have to select it, i cant just run an operation on the column, i have to select that operation so to speak, good to know

#

this is something that was missing from the tutorial i posted, because the values were hard coded as strings, so it was hard to see any variation into that

#

but i am very thankful that you are patient with me and have been helped me solve this issue and more

restive pilot
torn sphinx
#

thank you for that insight, i had learned about unique and primary key and other constraints the other day and hadn't gotten around to FOREIGN KEY yet

#

huh so my row is still empty

restive pilot
#

Update !== Insert

torn sphinx
#

so do i have to insert every row specifically for each column or if i make rows for one column, wouldn't the other columns produce rows? even if empty?

restive pilot
#

looks correct to me ^
e.g updating values after you have data in the table....

-- Update one
UPDATE `Players` SET `PlayerResults` = 3.14 WHERE `PlayerName` = "PolarFox"
-- Update All
UPDATE `Players` SET `PlayerResults` = 3.14
torn sphinx
# 
            result_insert = "UPDATE Players " \
                            "SET PlayerResults = (SELECT ROUND (SUM(PlayerResults), 2) FROM Hands) " \
                            "WHERE PlayerName = %s"
            cursor.execute(result_insert, (self.player_name, ))
restive pilot
#

Looks good to me

torn sphinx
#

so the value comes out to 1.10 last i checked, so im expecting to see that in playerresults for polarfox

#

but as with the pic ive shown, after running the command, thats what i get

restive pilot
#

Did you commit?..

torn sphinx
#

no i used playerresults instead of handresults

#

its working now

#

i need to eat something as im clearly not myself

#

again i appeciate all the patients and insight

restive pilot
#

Np

half kettle
#

In MySQL, how can you set the default value of a datetime column to 3 days in the past? I tried setting the default to DATE_SUB(NOW(), INTERVAL 3 DAY) but I got a syntax error

flint imp
#

Yo, anyone knows how to store object in asyncpg? Ping me, thanks

restive pilot
#

@flint imp store object in asyncpg, what do you even mean? Are you asking if you can save objects in PostgreSQL?

flint imp
#

Yes and i know you can but im not a pro sql

torn sphinx
#

How to learn postgresql if you don’t understand the logic of it at all even after you looked at the tutorial?

restive pilot
#

@flint imp save it using JSONB field in postgre by serializing your data to JSON.

#

@torn sphinx try using Excel or writing it out on a paper

torn sphinx
restive pilot
#

In the simplest form, excel is similar to database, you have rows of data in columns. You can combine data and get data

half kettle
half kettle
#

Uhhh for some reason after I did that, it won't let me make any more changes as it constantly complains about the default value for it, even if I'm trying to edit a completely separate table

#

If I delete the column, it fixes itself, but if I add a column where the default value is (NOW() - INTERVAL 3 DAY), it makes the change and works, but then any future change will complain about it

#

To clarify, I'm using DBeaver, so the issue might be in that, so I'll quickly try it from the mysql command line to see if it makes a difference

#

Does the same from the command line this is such a weird bug lol

📎 unknown.png
fallen vault
gaunt garden
#

im here.

solemn root
#

So my first question is: how would I update and populate the db from my bot if I create it manually and how do I link the two?

gaunt garden
#

you create it using the software and just connect to it like you normally would

db = sqlite3.connect('your-db.sqlite')
#

from there you can read any information that is stored via code or updated manually using the browser.

solemn root
#

My second: how can I create backup copies once I have data and a main db in case the main gets corrupted or accidentally wiped?

gaunt garden
#

you could just copy and paste the file as a backup and store it in a backup folder in your bots directory. or are you talking about automatically backing it up?

solemn root
#

automatically backing it up

gaunt garden
#

hmm not sure about that one.

half kettle
#

There’s an extension for discord.py called tasks which lets you do things periodically, so you can just have a tasked function that backs it up for you

gaunt garden
#

that would have been my other suggestion, writing a code that has your bot export it to a set file or location every so often.

solemn root
#

Writing in VS Code but SQLTools isn't working

gaunt garden
#

what do you mean?

solemn root
#

In VS Code

gaunt garden
#

are you trying to install an extension? from the extension search tab?

solemn root
#

well, update the version of sqlite3 that an extension is using

gaunt garden
#

you shouldn't need that. you should just be able to query or store information like normal. when you create a db in DB Browser just make sure the file format is .sqlite or .db then you can do

db = sqlite3.connect('my-db.sqlite')
c = db.cursor()
c.execute("your-query-or-updates")
db.commit()
c.close()
db.close()

as long as you put the file name in .connect() and the file is somewhere the bot can access it, you should be good.

#

think of DB Browser as a visual aid

solemn root
#

my code is just creating a db file instead of finding the db file the DB Browser has. :?

#

Got it working 🙂

torn sphinx
#

how can I do something like ```sql
where (x.a and y.a) = value

#

right now i'm doing where x.a=y.a and x.a=value

#

not sure if thats the best way though

solemn root
#

I haven’t done anything like that, but I think WHERE (x.a = value) and (y.a = value) is what you want (or something similar)

torn sphinx
#

yeah that looks nicer

half kettle
solemn root
#

So what are you trying to do exactly?

#

Get the date from 3 days ago and insert it into a table?

half kettle
#

Make a column default to 3 days in the past

#

It works, except stops me from using any mysql commands after

#

Until I delete the column

solemn root
#

I assume something like this would work:```
currentTime = datetime.datetime.now()
timedelta(days=3, hours=0, minutes=0)
#3days ago
PastDays = currentTime - timedelta(days=3)

CREATE TABLE IF NOT EXISTS mytable (id PRIMARY KEY AUTOINCREMENT, date INTEGER DEFAULT PastDays);

half kettle
#

No, because I want the column to default to 3 days in the past from when the used. As in, if a row is added, it automatically has that column set to 3 days in the past at that point, not from when I made the column

#

And to clarify, I’m not just using this database from Python so I can’t just use Python to make the datetime every time I’m adding a row. It needs to be from mysql

solemn root
#

Ah, mysql is slightly different than what I'm using, sqlite3. But if you clarify the default variable as a datetime, you can pass it in using the datatype TIMESTAMP (mysql) in the table creation so that upon insertion of new rows, you default the TIMESTAMP to 3 days before the row insertion.

half kettle
solemn root
fallen vault
#

Can someone explain this line of code loop.run_until_complete(test_example())

quartz heath
#

How to compare saved time with current time on mongodb

#

Suppose I saved a specific time in mongodb and I want to check if that specific time has pass the current time or not

#

How to compare time in mongodb python

jaunty galleon
#

Hi, i need help downloading sqlite

#

Anyone knows how?

torn sphinx
#

@jaunty galleon this isn't downloading, it's switching between the folders, in this case you would switch to the sqlite folder in C:\ with the cd command

jaunty galleon
#

So i don't need this step?

torn sphinx
#

no, you don't need this one

#

because I don't see any cmd commands there

proven arrow
torn sphinx
#

I used the Parametrized Queries, and it's telling me that there's an error by the $1

half kettle
proven arrow
proven arrow
half kettle
#

So why isn't it

proven arrow
#

Why isn’t what?

half kettle
#

Why isn't it working for me?

#

Well, it does

#

But then I get an error the next time I try to use a MySQL command

#

Until I delete the column with the default value

proven arrow
#

What error?

half kettle
proven arrow
#

Remove the brackets around Now

half kettle
#

I've tried that, and then it says invalid default value while setting it

#

Right now, it says the default value is fine until the next command used

half kettle
# proven arrow Remove the brackets around Now

Oh wait you mean on my allowMsg. No matter what I write there, it makes no difference. I can even try and just SELECT on a different table, and it still gives me the error for lastActive

#

This is a discord.py 'question', but this is a #databases channel. But, to solve your problem, you're on an old version of Python and Pip. Please update, as aiohttp (something needed for discord.py) requires a newer version

half kettle
#

@proven arrow Any ideas?

proven arrow
half kettle
proven arrow
#

No not that. run describe <table name>

#

and show output

#

and show the statements you used to create the table or alter it

half kettle
half kettle
proven arrow
#

run show create table <table name> to get that

half kettle
proven arrow
#

Only thing i can think of is you have some incorrect values in the table so when you try to alter it you get some error. Values can be incorrect due to the some certain sql modes you might have enabled

half kettle
#

Currently, the database works fine. It's only once I add that column that it has issues

#

I haven't enabled any 'sql modes' afaik

proven arrow
#

You might want to include the alter table statements in your initial ddl statement

half kettle
#

I've tried that and it works, but I'd still like the ability to interact with my table after haha

proven arrow
#

Well you still can, its just for some reason the way you do it currently, you will have values in that table which mysql doesnt like

half kettle
#

Which disables me from being able to interact with the rest of my database, which I would class as a can't lol

trim lintel
#

Hey

#

So for joins statement i can specify from and then below i also specify tables to join.
So for example, select * from members join products on . My confusion is what table goes in the from? does it matter the order of the table i put here.

proven arrow
#

For inner join order doesnt matter

#

And the table that appears after from is not the one you select from. You select from the result of the join. So in your example your not selecting from the members table. You select from the result set that is made when you join the members and products.

trim lintel
#

ok merci. "result set that is made when you join the members and products" That make sense now. And yeah i read about the inner join somewhere online but didnt know how the from works.

white ravine
#

i use postgres and i wanted to ask how i should go abt doing this

#

i would like it so servers can add more than 1 prefix

#

should i make more coloums

#

or is there a way to store that info in that 1 coloumn

torn sphinx
#

@proven arrow this, for example:

@bot.command()
async def setup(ctx, message):
    async with bot.db.acquire() as connection:
        async with connection.transaction():
            await connection.execute("UPDATE first_table SET first_message = $1", message)
            await ctx.send("**All set!**")
#

syntax error by $1

#

wait that's right

#

Ar!okay first_table

# 
@bot.command()
async def okay(ctx, ok):
    async with bot.db.acquire() as connection:
        async with connection.transaction():
            value = await connection.fetchval("SELECT * FROM $1", ok)
            await ctx.send(f'**{value}**')```this had no output now
#

yes by okay

#

syntax error at or near "$1"

proven arrow
#

@torn sphinx You cant use table names as a paramter like that

proven arrow
white ravine
#

hmmmmm

proven arrow
#

Postgres supports arrays and has array functions

white ravine
#

let me check on it i am not that good

torn sphinx
#

I want it like if I say Ar!okay first_table then it says what's in the first message (which is it's only column)

proven arrow
#

Why do you want the user to be able to select directly from a table they like?

torn sphinx
#

I'm just testing currently

#

It'll be only for me

proven arrow
#

Well then you can’t use parameterised query for that. Since that only allows you to specify values

#

You have to manually format a string

torn sphinx
#

okay

#

ty

half kettle
trim lintel
#

what is your code?

half kettle
#

Since there's a lot of messages now, I'll sum it up rq

#

Any ideas why, when setting a default value to (now() - INTERVAL 3 day), it works fine until I try to make any other change in the database (does the same if I try to add a varchar(100) to a different table, even!) at which it complains about an invalid default value? Once I removed the column from the table, the rest of my database works fine again, but meanwhile I get an error if I try to do anything

📎 unknown.png
half kettle
trim lintel
#

no i mean sql code

half kettle
#

Ah, well that's just in the screenshot lol

#

It's pretty much just that one line at the top

trim lintel
#

that is one part it doesnt help

#

you need show how you made table

#

also dont share screenshot

half kettle
#

I'm using a VPS and I can't copy from the VPS

trim lintel
half kettle
#

ALTER TABLE members ADD lastActive datetime DEFAULT (now() - interval 3 day) NOT NULL;

CREATE TABLE `members` (
  `user_id` bigint unsigned NOT NULL,
  `server_id` bigint unsigned NOT NULL,
  `cash` int NOT NULL DEFAULT '10',
  `bank` int NOT NULL DEFAULT '1440',
  PRIMARY KEY (`user_id`, `server_id`),
  KEY `fkIdx_3` (`server_id`),
  CONSTRAINT `serverMember` FOREIGN KEY (`server_id`) REFERENCES `servers` (`server_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4_0900_ai_ci```
#

I think I got all that right

#

Might have got one or two things wrong but

trim lintel
#

works fine for me

#

maybe you are doing something incorrect then

half kettle
#

As in ALTER TABLE members ADD lastActive datetime DEFAULT (now() - interval 3 day) NOT NULL; works for you? Because that works for me too- I only get the error for future commands

pure stone
#

no

half kettle
#

?

trim lintel
trim lintel
pure stone
#

wrong channel

half kettle
half kettle
trim lintel
half kettle
#

So it works for you, but not me- what could possibly be the difference that could cause my issue? I don't see how there could even be a single issue, other than the lack of my other tables, but I don't see how that would cause this

#

Anybody got any ideas how with the same table and same code, cavani's works but mine won't?

delicate fieldBOT
#

Hey @half kettle!

It looks like you tried to attach file type(s) that we do not allow (.sql). We currently allow the following file types: .3gp, .3g2, .avi, .bmp, .gif, .h264, .jpg, .jpeg, .mkv, .mov, .mp4, .mpeg, .mpg, .png, .tiff, .wmv, .psd, .ai, .aep, .xcf, .mp3, .wav, .ogg, .webm, .webp, .flac, .afdesign, .m4a, .csv.

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

half kettle
torn sphinx
#

yeah but I already fixed my problem thx

half kettle
#

So maybe try that and if that works, then it'll be a MySQL setting or a bug or something, if it doesn't then it's something to do with one of my other tables or the data in those tables

restive pilot
proven arrow
#

They on 8.0.23,

half kettle
#

^^^

proven arrow
half kettle
#

idk whaat sql mode is so 🤷

proven arrow
#

You can change the mode like I did and it would fix it

#

This doesn’t happen in mariadb so I guess it’s MySQL specific

half kettle
#

Ah, alright, thanks! What exactly is the sql mode though?

proven arrow
half kettle
#

Thanks

proven arrow
#

In short it’s basically what sql syntax to use. Or like what checks to perform

half kettle
#

Ah, makes sense 👍 tysm 😄

restive pilot
#

@proven arrow I was just reading about it in SQL modes.. NO_ZERO_DATE turns out to be issue, but it's strange
https://dev.mysql.com/doc/refman/8.0/en/sql-mode.html#sqlmode_no_zero_date

because you specify DEFAULT (now() - interval 3 day) NOT NULL as the default, so it would imply it has a date of 3 days ago... eg the 5th of February.
But once you alter the column, the column does not have a date (0000-00-00) because it's an expression and SQL does not like that

#

wondering if he is actually a Lufthansa pilot

half kettle
proven arrow
#

And I’m no dba so I only a little about these internals

restive pilot
#

^ me 2. I'm not a DBA at all 😂

half kettle
#

Aren't you a DBA by default if you manage a database, even if it were just as a hobby?

#

cos, I mean, you're still administering a database lol

proven arrow
#

** not a professional certified dba 😄

half kettle
#

Fair enough haha

#

Me neither, only used databases for 3 days now lmao

half kettle
inland stone
#

say i have two types of mongodb objects that are similar in nature and structure, but not identical. So, some queries work for both types (often I need one object from either of the two types), some queries differ. There is no relationship between objects that differ in type, just as there is no relationship between objects of the same type (they are all instances with ultimately identical standing in hierarchy).

Should I use one or two collections?

#

type 1:

/* 184 */
{
    "_id" : "123",
    "subs" : [ 
        {
            "full_name" : "123",
            "po" : {
                "PMEmail" : "1",
                "_id" : 15966,
                "total" : 35.76,
                "AddVAT" : false,
                "date_received" : "2017-08-30",
                "inv" : {
                    "invoice_id" : 400496
                },
                "payment" : {
                    "payment_id" : "321",
                    "payment_date" : "2020-01-10"
                }
            },
            "not_on_pd" : true
        }
    ]
}```

type 2:
```json
{
    "_id" : "123",
    "subs" : [ 
        {
            "submission_id" : 345,
            "date_received" : "2019-01-08, 15:48:00",
            "full_name" : "8483",
            "po" : {
                "total" : 202.5,
                "_id" : 7511374,
                "po_date" : ISODate("2019-01-10T00:00:00.000Z"),
                "inv" : {
                    "rips_id" : 123,
                    "invoice_id" : 90300,
                    "payment" : {
                        "payment_id" : "123432",
                        "payment_date" : "2019-03-19"
                    },
                    "invoice_date" : ISODate("2019-02-27T00:00:00.000Z")
                }
            }
        }
    ]
}```
half kettle
shy haven
#

Hello everyone, I'm a rooky python developer with the fundamentals down but looking for the next step in the right direction. I wanted to start programming mobile banking programs and I was wondering if anyone could tell me where to get started on developing databases

trim lintel
rain plank
shy haven
#

Thanks

torn sphinx
#

hi

trim lintel
torn sphinx
#

I got a problem with sqlite database

#

do you mind helping me?

rain plank
#

what problem though

torn sphinx
#

can aiosqlite cache values?

autumn epoch
#

How would I get the top value of a certain column? I have a column called XP and I want to get the top 5 values and their corresponding id from the table.

rain plank
autumn epoch
#

I will try

rain plank
#

yeah that was wrong

autumn epoch
#

Ya

rain plank
autumn epoch
#

Ok

rain plank
# 
SELECT column FROM table ORDER BY column FETCH FIRST 5 ROWS ONLY
autumn epoch
#

Ok

#

I will try it out

#

Would that be in a fetchrow?

autumn epoch
rain plank
#

what column do you want to order it by

autumn epoch
#

The column is called xp

rain plank
#

then order by xp

autumn epoch
#

And I also want it to fetch the userid with that column

rain plank
#

this is asyncpg right

autumn epoch
#

Yep

rain plank
# 
await connection.fetch("SELECT * FROM your_table_name ORDER BY xp FETCH FIRST 5 ROWS ONLY")
#

and no it wouldn't be fetchrow

autumn epoch
#

Ok

#

What is the difference between fetch and fetchrow

rain plank
autumn epoch
#

Oh ok

rain plank
#

so

Connection.fetch -> List[asyncpg.Record]
Connection.fetchrow -> asyncpg.Record
autumn epoch
#

I also am getting this error:

the server expects 1 argument for this query, 2 were passed

And this is the code:
await self.client.db.execute("UPDATE users SET xp = xp + 1 WHERE userid = ($1)", guildid, userid)

#

Oh nvm

#

Found it

#

Stupid me

sick fog
#

Hi , is there a way to get all the days of a month using the calendar module into a json file and add stuff to particular days using the same ?

torn sphinx
#

Hey guys,

# 
class XPsystem(commands.Cog):
    def __init__(self, bot):
        self.bot = bot
        bot.loop.create_task(self.async_init(bot))
        
    async def async_init(self, bot):
        bot.db = await aiosqlite.connect("expData.db")
        await bot.db.execute("CREATE TABLE IF NOT EXISTS guildData (guild_id int, user_id int, exp int, PRIMARY KEY (guild_id, user_id))")

    @commands.Cog.listener()
    async def on_message(self, message):
        self.bot.multiplier = 3

        if not message.author.bot:
            cursor = await self.bot.db.execute("INSERT OR IGNORE guildData (guild_id, user_id, exp) VALUES (?,?,?)", (message.guild.id, message.author.id, 1))```
#

im using sqlite3

#

and I keep getting an operationalerror: near "guildData: syntax error

#

last 3 lines

#

why?

#

I am making a discord bot btw.

proven arrow
#

It’s insert or ignore into

#

Your missing “into”

torn sphinx
#

oh

#

thanks lol

torn sphinx
#

what's a primary key?

proven arrow
#

Aiosqlite is just a module that allows you to interact with your database

#

It doesn’t allow you to do caching, that’s your databases job if it supports it, or do the caching yourself

fair cloak
#

hi, recently I'm working to a project and I'm searching for an online database where to store files, what could I use for this type of work?

lost whale
fair cloak
#

looks interesting, thank you

barren marsh
#

hi! im new to databases and stuff and i want to know what is the easiest database for a noob to begin with? i want this database to store information about currency for my discord games bot

gaunt garden
#

postgres or sqlite3 would probably be a good place to start for a discord bot.

barren marsh
#

ohh which one is the easiest among both? @gaunt garden

#

sry for the ping

gaunt garden
#

its okay, i personally us sqlite, as it was the first one i came across but i would recommend postgres, they both have their similarities and although sqlite3 comes built into python, there are some things you would need to work to avoid, like preventing blocking, since sqlite3 by default isn't an asynchronous module. although i have heard that there is one. I would suggest doing some further research on the two yourself and choosing based on your research which one you think will best fit your application.

#

i am still new to python and databases so don't take my word as law, but that is my opinion 🙂

barren marsh
#

ohhh ill google both and read their docs, thanks a lot for ur input ! :D

torn sphinx
#

hi, in aiomysql, how should i format? Since TypError returns me.

allrequest = await temp.execute("SELECT * FROM levels WHERE id = $1;", message.author.id);

TypeError: not all arguments converted during string formatting
torn sphinx
# 
await connection.execute("SELECT * FROM in_game; DELETE * FROM in_game WHERE name = " + after.name)
```how would you do this? I get a syntax error
#

"at or near *"

rain plank
torn sphinx
#

no need for the *?

#

okay

#

I'll try it like that

torn sphinx
small hatch
#

anyone know how to use mysql

#

im using it as my database for a discord bot and idk how to add to my table

proven arrow
# torn sphinx help

aiomysql uses %s as the placeholder, and the parameters need to be passed as a iterable

#

allrequest = await temp.execute("SELECT * FROM levels WHERE id = %s", [message.author.id]);

torn sphinx
#

Thanks

restive pilot
torn sphinx
#

will the memory db be lost if if i shutdown computer?

restive pilot
#

In memory yes, on file no.

torn sphinx
#

Can I link my help thread in here? it's about sqlalchemy basics.

restive pilot
#

But if you took 1 hour to read what I sent you, you will have a great starting point.

torn sphinx
#

@proven arrow if I need to add more than one value, do I just add the following element to the list?

proven arrow
#

what do you think?

torn sphinx
#

idk, create another list.

#

imao

proven arrow
#

I dont know why you would think like that but no. You would pass as a single list

restive pilot
proven arrow
#

Yeah thats what they have already

lost whale
#

can someone with Firebase knowledge dm me please

torn sphinx
lost whale
#

it's hard to find someone who knows firebase well lol

#

but ye

#

My question is : I use the update function to update my realtime database (firebase) but when i update it clear my datas and write new one

#

instead of simply overwritting the concerned stuff

proven arrow
#

@lost whale What do you mean clears all data?

lost whale
#

ok lemme find an exemple

#

brb

proven arrow
#

Make sure you update to the deepest level

lost whale
#

ok so

#

here i have an inventory

#

the guy id, mine : 351454846014062592 has a virtual inventory

#

with the Newbie Badge : Amount : 1 Type : Reward

#

right ?

#

Now i manually add wood in my inventory

#

My "character" now has 6 woods which type is material

#

now i want to craft something with the wood, a crafting table, so i use my command

#

here

#

The code for it is :

    @commands.command()
    async def craft(self, ctx, args):
        ref = db.reference('users')
        info = ref.get()
        ref_inv = db.reference('inventories')
        data = ref_inv.get()
        ref_crafts = db.reference('crafts')
        crafts = ref_crafts.get()
        if str(ctx.author.id) not in info:
            embed = discord.Embed(title="An Error Occurred", description="You need a character to do this.", color=0x0e4db6)
            await ctx.send(embed=embed)
        else:
            if args == "crafting_table":
                if "wood" in data[str(ctx.author.id)]:
                    if data[str(ctx.author.id)]["wood"][0] >= 4:
                        new_wood_num = data[str(ctx.author.id)]["wood"][0] - 4
                        if "crafting_table" not in data[f'{ctx.author.id}']:
                            data[f'{ctx.author.id}'][args] = [1, "tool"]
                            ref_inv.update({
                                str(ctx.author.id) : {
                                    'wood' : [new_wood_num, 'material'],
                                    'crafting_table' : [1, 'tool', '50']
                                }
                            })
                            
                        else:
                            new_c_t_num = data[f'{ctx.author.id}']["crafting_table"][0] + 1 
                            c_t_dur = data[f'{ctx.author.id}']["crafting_table"][2]
                            ref_inv.update({
                                    str(ctx.author.id) : {
                                        'wood' : [new_wood_num, 'material'],
                                        'crafting_table' : [new_c_t_num, 'tool', c_t_dur]
                                    }
                                })
#

and now my inventory looks like :

#

and no more Newbie Badge

#

and i didn't delete it in my code

#

i used update

#

Question 1 : Did i use it wrong ?
Question 2 : Does a better way exist to subtract number to a value in a list without creating a new var ?

proven arrow
#

well you are overwriting all its children with str(ctx.author.id) : {...}

#

think of it as a python dict

lost whale
#

yep

#

but i thought update function wouldn't delete my Newbie Badge

#

i thought only function set() would

lost whale
proven arrow
lost whale
#

hmm

proven arrow
# lost whale hmm

Ok so actually what i said would be correct. It seems their wording is confusing in their docs

#

The update would overwrite other childs at that level

lost whale
#

especially the translation in my main language

lost whale
proven arrow
#

In this case none

lost whale
#

hmmm ok but then i'll need way more code lines to do it ?

#

and do you have an answer to my second question btw ?

lost whale
proven arrow
#

I think set is what you need actually

#

That would try to insert or update if it exists, which you can do. Also if you specify the deepest level then you wont have this issue of overwriting other data

#

For basic write operations, you can use set() to save data to a specified reference, replacing any existing data at that path.

lost whale
#

ok imma try

#

thanks for your help

lost whale
twilit isle
#

Hey, anyone here familiar with JSONB?

proven arrow
silk vortex
# 
  File "exo.py", line 907, in <module>
    client.pool = loop.run_until_complete(asyncpg.create_pool(user='user', password='pass', database='db', host='ip'))
  File "/usr/lib/python3.6/asyncio/base_events.py", line 484, in run_until_complete
    return future.result()
  File "/usr/lib/python3.6/asyncio/tasks.py", line 537, in _wrap_awaitable
    return (yield from awaitable.__await__())
  File "/usr/local/lib/python3.6/dist-packages/asyncpg/pool.py", line 398, in _async__init__
    await self._initialize()
  File "/usr/local/lib/python3.6/dist-packages/asyncpg/pool.py", line 436, in _initialize
    await asyncio.gather(*connect_tasks)
  File "/usr/local/lib/python3.6/dist-packages/asyncpg/pool.py", line 125, in connect
    self._con = await self._pool._get_new_connection()
  File "/usr/local/lib/python3.6/dist-packages/asyncpg/pool.py", line 487, in _get_new_connection
    connection_class=self._connection_class)
  File "/usr/local/lib/python3.6/dist-packages/asyncpg/connect_utils.py", line 642, in _connect_addr
    await asyncio.wait_for(connected, timeout=timeout)
  File "/usr/lib/python3.6/asyncio/tasks.py", line 358, in wait_for
    return fut.result()
asyncpg.exceptions.TooManyConnectionsError: remaining connection slots are reserved for non-replication superuser connections
sys:1: RuntimeWarning: coroutine 'HTTPClient.close' was never awaited```Does anyone know how to fix this error?
restive pilot
#

@silk vortex
sys:1: RuntimeWarning: coroutine 'HTTPClient.close' was never awaited
Error is literally right in front of your eyes.
If you took 5 minutes to read https://docs.python.org/3/library/asyncio-task.html
You would understand that you need to put an await in front of HTTPClient.close since this is a coroutine so it can wait for everything to finish before closing the client.

I also see TooManyConnectionsError thrown by asyncpg, maybe increase the amount of connections in the pool? 🙂
Default is 10 for the amount of simultaneous connections... increase min / max when creating the connection pool https://magicstack.github.io/asyncpg/current/api/index.html#connection-pools

half kettle
restive pilot
#

This is python / general db help, not dbeaver support.

#

@half kettle
for the sake of it since I'm bored
if you took the time to read... you would understand that DBeaver just uses JDBC to connect to the SQL database, so by adding zeroDateTimeBehavior=false to the connection properties https://github.com/dbeaver/dbeaver/wiki/Database-drivers
you effectively disable the exception thrown with zero dates (0000-00-00) and allows for that. See docs https://dev.mysql.com/doc/connector-j/8.0/en/connector-j-connp-props-datetime-types-processing.html
You can read all about the props here:
https://dev.mysql.com/doc/connector-j/8.0/en/connector-j-reference-configuration-properties.html

Also with further reading: https://dev.mysql.com/doc/connector-j/8.0/en/connector-j-connp-props-session.html
you find this special variable called sessionVariables and with that you can specify sql_mode which is initialized up on connection

half kettle
#

I spent nearly an hour reading up about it but I wasn't getting anywhere, and also had to go do something. Once I was back from doing that thing, I asked here again, before continuing to try and research it myself on the off chance I manage to find it. So I assure you I took the time to read, I'm just too dumb to find what I'm looking for lmao

#

Thanks though

restive pilot
#

sessionVariables only allows for SET... variables e.g SET sql_mode=... which what you are after @half kettle

half kettle
#

Ah right

#

I was in the assumption that sql_mode was a settings saved in the database

#

So thought that DBeaver must have been overriding or ignoring that setting

restive pilot
half kettle
#

Welp sorry but I mustn't have seen that 😦

white ravine
# 
 RuntimeWarning: coroutine 'Pool.fetch' was never awaited
  prefix = bot.pg_con.fetchrow("SELECT * FROM guildsettings WHERE guild_id = $1", message.guild.id)  ```
#

what does this mean

# 
RuntimeWarning: coroutine 'Pool.fetchrow' was never awaited
  pass
RuntimeWarning: Enable tracemalloc to get the object allocation traceback```
# 
in get_prefix
    return prefix['prefix']
TypeError: 'coroutine' object is not subscriptable ```
restive pilot
#

"RuntimeWarning: coroutine 'Pool.fetch' was never awaited
Error is literally right in front of your eyes.
If you took 5 minutes to read https://docs.python.org/3/library/asyncio-task.html"

Anyway, that is only warning and not an exception and should not cause an issue.

in get_prefix
    return prefix['prefix']
TypeError: 'coroutine' object is not subscriptable 
``` but his an issue, here you are trying to get a value from a function that has not finished yet. Suggest you read up on what I provide above

Also, check out [#async-and-concurrency](/guild/267624335836053506/channel/630504881542791169/)
white ravine
white ravine
#

ahhhh

# 
async def get_prefix(bot, message):
    if not message.guild:
        return commands.when_mentioned_or("!")(bot, message)
    
    prefix = await bot.pg_con.fetch("SELECT guildsettings WHERE guild_id = $1", message.guild.id)
    print(prefix=prefix)

    if not prefix:
        await bot.pg_con.execute("INSERT INTO guildsettings (guild_id, prefix) VALUES ($1, !)", message.guild.id)
        return commands.when_mentioned_or("!")(bot, message)

    else:
        prefix = await bot.pg_con.fetchrow("SELECT * FROM guildsettings WHERE guild_id = $1", message.guild.id)
        return prefix['prefix']```
#

i am getting the error

#

column "guildsettings" does not exist

#

i cant understand i have never specified that as a column

#

guildsettings is the name of the table

slender atlas
#

you forgot to type stuff in the first fetch then

torn sphinx
#

I've been trying to solve that god damn problem, do you mind helpin' me?

#

sqlite databas

torn sphinx
#

asyncpg.exceptions._base.InterfaceError: cannot call Connection.fetchval(): connection has been released back to the poolI don't understand this problem

proven arrow
torn sphinx
#

OH

proven arrow
#

If you use a async with block to aquire the pool then make sure you fetch inside this

torn sphinx
#

yeah

#

got it

#

thank you

lost whale
#

@proven arrow i'm still lost about the yesterday stuff lol

#

could you show me an example of code

#

lemme do the settings

#

wanna add Newbie Badge with code without moving crafting and wood and if there is already a newbie badge then adding one

#

so my actual code would be

# 
#commands code stuff
ref_inv = db.reference('inventories')
data = ref_inv.get()
ref_inv = ref_inv.child(f'{ctx.author.id}')
if "Newbie Badge" in data[str(ctx.author.id)]:
    new_num_badg = data[str(ctx.author.id)]['Newbie Badge'][0] + 1
    ref_inv.set({
    'Newbie Badge' : [new_num_badg, 'Reward']
    })
                            
 else:
    ref_inv.set({
    'Newbie Badge' : [1, 'Reward']
    })
#

but that actually overwrite my whole 351454846014062592 dict

#

you told me to go deeper but i don't know how, in this case, i can

#

oh wait i think i found out how i could do it

#

sorry for ping

celest sleet
#

What would be best db to use for economy/game in discord.py? sqlite? mysql? mariadb?

#

seems like i go for sqlite

fallen vault
#

Should i use the datetime object or time stamp when adding time objects to a database

fallen vault
celest sleet
#

yeah its going to be on same vps

#

can always change later if need remote access or such

#

i have a question tho with Sqlite, numeric for boolean. What should i type True / False, or 0 / 1 as "default" @fallen vault

#

nvr

#

SQLite does not have a separate Boolean storage class. Instead, Boolean values are stored as integers 0 (false) and 1 (true).

proven arrow
#

Better to store as 0 or 1 int value

#

That’s how some other databases do it internally as well

celest sleet
#

cool, few years since i was doing with databases

royal cosmos
#

I've been trying to work with sqlite but and I'm trying to count some data in it, but all i get is this:

        print(gncount)
        if gncount > 0:
            ctx.send("The name you selected is already taken, try again!")```
celest sleet
#

that looks correct for my eyes

#

@royal cosmos no errors?

slender atlas
#

what does COUNT(*) do? just curious 👀

royal cosmos
#

whenever i print gncount it gives me this for some reason: <sqlite3.Cursor object at 0x03A293E0>

slender atlas
#

oh ye that's what i thought

#

cursor/connection.execute returns a cursor

royal cosmos
#

oh

slender atlas
#

from which you need to use cursor methods to get the data

royal cosmos
#

how should i fix it?

celest sleet
#

like c.fetch()

#

or something

slender atlas
#

ye

royal cosmos
#

ohhhh

slender atlas
#

gncount.fetchall()

#

or whatever

royal cosmos
#

alr tysm

celest sleet
#

i remember little 😄

#

u can also remember that it sais its an object,

slender atlas
#

me, who works with aiosqlite and used dir() because i have no idea what's the equivalent in sqlite3: hehe

#

they're supposed to have literally the same methods and stuff though

#

so ye

celest sleet
#

dir() :S

#

no logic for me 😄

slender atlas
slender atlas
celest sleet
#

so whats the pros with use aiosqlite?

slender atlas
#

aiosqlite is literally sqlite3 except it's also asynchronous

celest sleet
#

sounds like i should look into that instead of regular sqlite3?

#

am gonna make a textbased game/economy stuff

royal cosmos
#

its still not working

#

idk why

slender atlas
#

idk pal i used aiosqlite because i had to, i was working with discord.py and all the things must be asynchronous, otherwise the bot doesn't respond asynchronously to many users commands

royal cosmos
#

i changed it to this c.execute("SELECT COUNT(*) FROM botdata WHERE gameName=?", (gamename,)) conn.commit() gncount = c.fetchall() conn.commit() print(gncount) if gncount > 0: ctx.send("The name you selected is already taken, try again!")

slender atlas
#

no need for conn.commit()

royal cosmos
#

on both of them?

celest sleet
#

@slender atlas thats what i was thinking of

slender atlas
#

on none of them

celest sleet
#

Alot of commands being thrown sametime

royal cosmos
#

oh

slender atlas
#

SELECT doesn't need commits

#

i don't think

#

ever.

#

gncount is probably a list or a tuple

#

maybe a list of tuples

celest sleet
#

i belive commit() is basicly u telling it to save aka write.

royal cosmos
#

yeah you're right it says its a list

slender atlas
#

if COUNT(*) should return only one thing then you can do gncount = c.fetchone()[0]

#

for some damned reason fetchone returns a one value tuple

celest sleet
#

[0] should't be needed tho

#

oh i see

#

i guess cause lists

royal cosmos
#

IT WORKEDD

#

lol tysm

round osprey
# 
            c.execute('''INSERT INTO reminders (?, ?, ?, ?) VALUES (?, ?, ?, ?)''', ("author", "event",
                                                                                     "timeExecuted", "channel", ctx.author.id, "Test", str(datetime.datetime.now()), ctx.channel.id))```
What am I doing wrong here?
white ravine
#

asyncpg.exceptions.DataError: invalid input for query argument $2: ['@cracked#4801 ', '<@!797733324... (expected str, got list)

#

this is the error i am getting

burnt turret
#

it's telling you what's wrong: it is expecting a string but you've given it a list

white ravine
#

prefixes = commands.when_mentioned_or("!")(bot, message)
await bot.pg_con.execute("INSERT INTO guildsettings (guild_id, prefixes) VALUES ($1, $2)", message.guild.id, prefixes)

burnt turret
#

(expected str, got list)

white ravine
#

is there a way to fix this

burnt turret
#

don't pass it a list?

white ravine
#

since i want a list to be inserted

burnt turret
#

then that column has to be defined to be taking arrays

white ravine
#

even i thought that was the prob

burnt turret
#

wait one second, let me just look up the docs on when_mentioned_or

#

i don't think you have to be storing that into the database

white ravine
#

lmao

#

i can just do

#

nvm

#

no what i thought was dumb it wont work

burnt turret
#

and i'll tell you what you've got to do

white ravine
#

k

# 
async def get_prefix(bot, message):
    if not message.guild:
        return commands.when_mentioned_or("!")(bot, message)
    
    prefix = await bot.pg_con.fetch("SELECT * FROM guildsettings WHERE guild_id = $1", message.guild.id)

    if not prefix:
        prefixes = commands.when_mentioned_or("!")(bot, message)
        await bot.pg_con.execute("INSERT INTO guildsettings (guild_id, prefixes) VALUES ($1, $2)", message.guild.id, prefixes)
        return commands.when_mentioned_or("!")(bot, message)

    else:
        prefix = await bot.pg_con.fetchrow("SELECT * FROM guildsettings WHERE guild_id = $1", message.guild.id)
        return prefix['prefixes']
    ```
burnt turret
#

you basically just want the guilds to have custom prefixes stored in the database and the bot ping to also be a prefix right?

white ravine
burnt turret
#

what list are you even trying to insert there

white ravine
#

since i have future plans of letting there to be more than 1 prefix

burnt turret
#

what you're doing there seems a little pointless

white ravine
#

when_mentioned_or = ['<@botid>', '<@!botid>']

burnt turret
#

if no prefix is set in the database, what prefix do you want the guilds to have?

white ravine
#

!

burnt turret
#

!, and the bot ping right

white ravine
#

yes

burnt turret
#

well there are multiple ways to handle this

#

you could set a default value of ! for the prefix column in your table

#

or you could straight away just return commands.when_mentioned_or("!")(bot, message) in the case where your database returns None

#

i don't see the point of that insert you're trying there

white ravine
#

prefixes = commands.when_mentioned_or("!")(bot, message)
await bot.pg_con.execute("INSERT INTO guildsettings (guild_id, prefixes) VALUES ($1, $2)", message.guild.id, prefixes)
return commands.when_mentioned_or("!")(bot, message)

burnt turret
#

the query in the else case is unnecessary

low iron
#

what is the best way of storing lists using SQLite? should i just turn it into a string and eval it when i need it?

burnt turret
#

because you already have that data pulled from your database

low iron
#

i'll load them at the beginning of the program

white ravine
burnt turret
#

list isn't, because you've defined your column to be varchar

white ravine
#

yeah i am dumb

#

why list is not accepted

#

since i read the docs

burnt turret
#

if you'd defined your column to have an array type it would be accepted

white ravine
burnt turret
#

the easy way to put in a list is to just dump the str version of it into your varchar column

#

and then do json.loads on it when you retrieve it

#

so you'll get back the list from the string version

white ravine
#

but let me change it to a list type

burnt turret
#

!e

import json
a_list = ["foo", "bar"]
# insert `str(a_list)` to your TEXT type column
# now when you retrieve it, it is a string -> '["foo", "bar"]'
# doing json.loads on it gives you your list back
back_to_list = json.loads('["foo", "bar"]')
print(type(back_to_list))
delicate fieldBOT
#

You are not allowed to use that command here. Please use the #bot-commands channel instead.

burnt turret
#

wow my connection was slow i sent this a while back

white ravine
burnt turret
#

i've pinged you in the bot-commands channel where you can see that working haha

ancient delta
#

I've only seen the bitwise AND operator & used with numbers. In the SQL query that I'm trying to understand, it's being used on two strings. How does it work then?

#

Like so:

SELECT IIF(B.Fase IS NULL, IIF(B.Fase IS NULL, "Fase ukendt", B.Fase), B.Fase) & " (" & stg_CUSTOM_LABEL_LANG.CUSTOM_LABEL_LANG & ")" AS FELTER_INKL_FASER
#

Both the Fase field of table B and the CUSTOM_LABEL_LANG field of stg_CUSTOM_LABEL_LANG are strings.

#

I suppose it simply uses the binary values for each string and computes the binary value after the AND operation is applied. But what does this achieve exactly when it's used like this i.e. with two strings?

#

Ah I'm just dumb. This operator is used for concatenation in MS Access SQL.

half kettle
#

Hey! I'm really new to SQL (used it for the first time only 3 days ago lol) but have done a lot with it so far and want to make the most of it, so I'm looking to see if there's any ways to improve my code so far or any criticisms before I get too ahead. Mostly, if there's places where I'm making several requests to the database where I only really need to make one or two to do the same thing. I've probably ignored a few standards in SQL programming by accident so I think sharing my code here will be the best way to learn what I might be lacking. Here's my code so far, but it's mainly from lines 68 to 110 that I'm spamming the database with requests, and this code is executed every minute. Thanks so much in advance 😄 https://mystb.in/WikipediaAnnouncedIntroducing.python

torn sphinx
#

hi

#

I try to get the results of this ```
<Future finished result=((469529314661236739, 0, 150, 0, 0),)>

Try using attributes, dictionary, indixe. But I still can't find a way to pick up an element.
#

Is there any way to do it?

half kettle
#

I think you're needing to await the function where you get this Future object

#

Then you'll be given a list of lists of attributes

#

Since this looks like the way what I use (aiomysql) formats it, I also recommend switching to the aiomysql.DictCursor; makes it easier to access the elements you need

#

And if you don't know what await is but are using aiomysql, then you probably have no reason to be using aiomysql in the first place haha

#

@torn sphinx

torn sphinx
#

thanks

ocean walrus
#

Hello, im a noob with mongodb and i im using pymongo with flask i wanted some help extracting the values of the keys to show on the website.

#

i have the following json dump [{"_id": {"$oid": "60201bde29cfcc0cc341606e"}, "payload": "[{\"_id\":\"602013488ecdd241ecdaff45\",\"name\":\"DS18B20\",\"pin\":\"7\",\"status\":\"\"},{\"_id\":\"602013488ecdd241ecdaff46\",\"name\":\"ph_sensor\",\"pin\":\"\",\"status\":\"\"},{\"_id\":\"602013488ecdd241ecdaff47\",\"name\":\"turbidez_sensor\",\"pin\":\"\",\"status\":\"\"},{\"_id\":\"602013488ecdd241ecdaff48\",\"name\":\"o2_sensor\",\"pin\":\"\",\"status\":\"\"},{\"_id\":\"602013488ecdd241ecdaff49\",\"name\":\"condutividade_sensor\",\"pin\":\"\",\"status\":\"\"}]"}]

#

how do i get the name of each sensor?

ocean walrus
#

i got the following search that returns the above

# 
        find_sensors= db.sensor.find()
        listcur=list(find_sensors)
        dump_json=json_util.dumps(listcur)
        return dump_json
half kettle
#

[sensor['name'] for sensor in dump_json[0]['payload']]? This isn't a mongodb thing, this is JSON which you can pretty much think of in the Python scope as lists and dicts

#

@ocean walrus

half kettle
ocean walrus
#

sorry if i didnt explain myself properly

half kettle
#

Ah alright. Well I've never used monogdb so I wouldn't be able to help you there. Sorry

ocean walrus
half kettle
#

Alright 👍

torn sphinx
#

how do i change foreign key reference of existing foreign key?

#

TypeError: '>=' not supported between instances of 'asyncpg.Record' and 'int'

#

I thought that when you unpacked an asyncpg.Record it automatically converted to the data type

# 
exp = await con.fetchrow("SELECT exp FROM public.levels WHERE user_id = $1", message.author.id)
#

thats the way im doing it

restive pilot
#

so if you do type(exp) you should see it is a asyncpg.Record and not int

torn sphinx
#

wait i think even if its just one value that doesnt mean i have unpacked it right?

#

yep its not unpacked

restive pilot
#

just do exp[0] when comparing 😛

torn sphinx
#

i think its more simple to just unpack 2 values

#

thats easier

#

and you dont have to use brackets later

#

anyways

restive pilot
#

ye ye

torn sphinx
#

thank you

restive pilot
#

😄

torn sphinx
#

whats the difference between a validated and a not validated foreign key?

wintry plover
#

Anyone got any recommendations for Graph-DBs? I've got Neo4J setup on my workstation but it feels like overkill for what I'm doing. I just want to insert/read/traverse/filter nodes/edges with attributes on each. I've done some stuff with Gremlin & AWS Neptune and that was pretty good but too expensive for me lol

jaunty galleon
#

!docs

delicate fieldBOT
kindred crow
# 
    global travel_from
    global travel_to
    global g
    travel_from=input('Where do you want to travel from: ')
    travel_to=input('Where do you want to travel to: ')
    mycursor.execute('Select * from class')
    rows= mycursor.fetchall()
    for row in rows:
        print(row)
    class_type=eval(input('Enter the option corresponding to the type of class you want to travel in: '))
    s='select * from class where sno= %' % (class_type)
    mycursor.execute(s,class_type)
    rows=mycursor.fetchall()
    for row in rows:
        g=row[2]```
#

i dot understand,, why does it show incomplete format

slender atlas
#

the % inside the string doesn't have an s

kindred crow
#

oh

#

thanks its working now

hasty juniper
#

I always initialized database in on_ready () and I saw that you can run init.sql in which all tables will be created, I use asyncpg and I did not find anything about this in the documentation, How can i do this

jaunty galleon
#

How can i make a welcome command, that also says who invited the member?

hasty juniper
torn sphinx
hasty juniper
#

@torn sphinx

torn sphinx
#

Oh that

#

that is for when you want to create tables and setup the program

#

but you dont need to create table everytime, its only once

torn sphinx
#

does anyone knows about mongodb?

#

huh?

burnt turret
#

Ask your question

#

If someone knows, they'll answer

torn sphinx
#

how to get mongodb username,password,host and port?

#

@burnt turret pls say

burnt turret
#

Don't you set the username and password yourself

torn sphinx
#

mongodb atlas?

burnt turret
#

Port is 27017 on localhost I think

torn sphinx
#

yeah

#

and host?

#

how can i get username and pw?

#

hey @burnt turret

burnt turret
#

on localhost

#

Patience please

torn sphinx
#

where is localhost?

#

yes

burnt turret
#

localhost is...localhost

torn sphinx
#

localhost is localhost?

burnt turret
#

That's what you write in the connection URI

#

Or the host field if you're using atlas

torn sphinx
#

hey

burnt turret
#

It'll be autofilled to "localhost" generally

torn sphinx
#

in port should i write 27017?

burnt turret
#

If you're running mongodb locally on your computer all these fields are already filled

#

Port: 27017
Host: localhost

#

You wouldn't have to change anything

torn sphinx
#

and username and password?

#

where can i get them?

#

@burnt turret

#

can u say where can i get them?

burnt turret
#

This is mongodb atlas?

torn sphinx
#

yah

burnt turret
#

Don't they give you a connection URI

torn sphinx
#

in gmail?

burnt turret
#

Well somewhere on your cluster's dashboard

#

I don't think the URI is something they'll email and send

torn sphinx
#

manage your mongodb account?

burnt turret
torn sphinx
#

thanks @burnt turret

#

i got that!

burnt turret
#

After reading this you want to read the "connecting via compass" article that's at the bottom of that page

torn sphinx
#

thnx

#

i got it

#

i found the solution to my prob

burnt turret
torn sphinx
#

hey @burnt turret how to get mongodb database?

#

could you tell me?

burnt turret
#

Sorry I gotta go now

burnt turret
#

Wait a minute, I need to refer the docs myself haha

#

Is user the array?

#

That looks right I think then

#

All good

#

You can ping me here, I'll answer when I can

torn sphinx
#

hey @burnt turret

#

can u send a website

#

to see the mongodb database

#

pls

hasty juniper
hasty juniper
#

@proven arrow i know you are good at database could you help me(sry for ping)

proven arrow
#

@hasty juniper Thats just a Sql file. Where have you seen them execute the file directly?

#

Reading file is not async also so it’s unlikely asyncpg would support it

hasty juniper
#

I just assumed that it is possible so as not to write it in py code

proven arrow
#

However you can execute multiple statements inside the execute function

#

But files like that are a one time operation used when you want to setup/initialise the database.

hasty juniper
#

i need to run this file on another server

proven arrow
#

So do it then

burnt turret
#

If this is in pymongo you have to put all dollar operators in quotes

#

"$in":

#

Check this out

#

It's got an explanation as to why pymongo shouldn't be used with discord.py, and what you should be using instead

rain plank
#

yes

torn sphinx
#

Whats the difference between a validated and a non validated foreign key?

ancient delta
#

@burnt turret he cut right to the chase, which is nice to see. no bullshit history lesson first.

#

short and sweet - the best way really

#

oh wait it's you lol

rain plank
#

xd

burnt turret
#

Haha thank you :D

burnt turret
#

i'm here @torn sphinx

burnt turret
#

.find always returns a Cursor object

#

you need to be using the $inc operator in an update/update_one there

#

upsert is to update-if-exists, else insert

#

it's passed as a kwarg to collection.update

#

but your example only seems to be incrementing the value?

#

yeah

#

right

#

yeah upsert=True in an .update is how you upsert

#

close the parentheses as well, yeah

#

no need of {} around member_id but i'm guessing that's an example

#

the second dict has to tell how to update, common options are
{"$set: {"key": new value}} -> sets key to a new value
``{"$inc: {"key": 1}}` -> increments key by 1

#

to decremenet you'd inc by negative numbers

#

+👍

#

ah damnit

#

you get the idea haha

#

the cursor object is iterable

#

you can loop over it with a for loop

#

still an iterable, with just one document

#

try list(cursor) and see if it flattens it

#

i'm not sure if it'll work though

torn sphinx
#

These are properties of a foreign key

#

What do they mean?

#

Or do

proven arrow
torn sphinx
#

Do you have any resource to learn from what do these do exactly?

proven arrow
torn sphinx
#

hi

#

How can I do auto increment in aiomysql?

proven arrow
#

Auto increment has nothing to do with aiomysql

#

Aiomysql just allows you to talk with your database

torn sphinx
# 
line 109, in raise_mysql_exception
    raise errorclass(errno, errval)
pymysql.err.InternalError: (1075, 'Incorrect table definition; there can be only one auto column and it must be defined as a key')
proven arrow
#

Yeah so you can’t have multiple auto increment columns which is what you have probably

torn sphinx
#

I only have one

proven arrow
#

Is it primary key?

torn sphinx
#

no

proven arrow
#

So make it primary key then

torn sphinx
#

but I don't know if it will work, something happens if there are numbers in the same column the same?

proven arrow
#

Well you don’t have to make it a primary key, if you don’t want. Just need an index on it.

#

But if you make it a primary key, then the column will only allow unique values in that column

torn sphinx
#

yes I did it

#

now

# 
line 109, in raise_mysql_exception
    raise errorclass(errno, errval)
pymysql.err.ProgrammingError: (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '' at line 7")
proven arrow
torn sphinx
# 
await temp.execute("""CREATE TABLE levels
        (id BIGINT,
        level BIGINT NOT NULL AUTO_INCREMENT,
        req BIGINT,
        xp BIGINT,
        plus BOOL,
        PRIMARY KEY (id)""");
#

Hiya
So i am trying to desing a permission system for my app, but am confused how to do this in the database? Anyone have any good ideas of how to do this. I want more than just a many to many between user and roles. I need more detailed permissions like a role has many permissions or a multiple roles can have same permissions etc?

#

Ive been trying to think for a few hours now but no idea how to proceed

torn sphinx
proven arrow
#

Your primary key column id is what should be auto increment, not level

torn sphinx
#

I forgot.

#

but now, I have this error
pymysql.err.InternalError: (1075, 'Incorrect table definition; there can be only one auto column and it must be defined as a key')

proven arrow
#

For the third time do you even read what I send?

torn sphinx
#

yes

#

I understood what it says

#

but

proven arrow
#

So fix it

torn sphinx
#

It's for a level system, I don't have much idea how to do it if I can't auto increment the level column.

proven arrow
#

You don’t use auto increment for levels

torn sphinx
proven arrow
#

Auto increment is used to generate new numbers which can identify your row uniquely automatically

#

Instead of you manually figuring out the value of what to set as the primary key, auto increment can do this for you.

#

Level is something you should calculate yourself and insert/update accordingly in your table.

#

Or you can have a computed/generated column, which you can also look into.

torn sphinx
#

well, I try to retrieve the range of the id > level

#

for example, retrieve in int format in the range that is the level where id.

waxen lagoon
#

if anyone has any familarity with sql alchemy i could use a hand in #☕help-coffee

silk vortex
restive pilot
#

@silk vortex because of the error it sounds like you are working with async code, then you also have to await the client close peacefully when the code exist
Unless you are sending something like SIGKILL in linux

pure dirge
#

Lets say I want to track prices of items over time. Should I save databases of each state of all the prices each day or should i be doing some method that putsi t all in the same database

shell ocean
#

item ID, date, price

#

in one table

pure dirge
#

it just makes a new row every date update?

#

what if there's 400 different items, should i just have 400 tables in the database

shell ocean
white ravine
# 
  @commands.Cog.listener(name="on_guild_leave")
    async def on_guild_leave(self, guild):
        guild_id = guild.id
        await self.bot.pg_con.execute("DELETE FROM guildsettings WHERE guild_id = $1",guild_id)
``` is the sql right in this
#

i want it to deleted the row in which there is the guild id

torn sphinx
#

Hi guys,

can somebody help me with this issue here?

raise ConfigurationError(str(exc))
pymongo.errors.ConfigurationError: query() got an unexpected keyword argument 'lifetime'

here is my code:

mongo_url = "mongodb+srv://souldev:notrealpassword@botdc.rnzrx.mongodb.net/notrealdb?retryWrites=true&w=majority"
        cluster = MongoClient(mongo_url)
        db = cluster["databaseall"]
        collection = db["level"]
        author_id = message.author_id
        guild_id = message.guild_id
        user_id = {"_id": author_id}
#

here is the full traceback:

Traceback (most recent call last):
  File "C:\Users\matheus.ferrari-ext\AppData\Local\Programs\Python\Python39\lib\site-packages\pymongo\srv_resolver.py", line 72, in _resolve_uri
    results = resolver.query('_mongodb._tcp.' + self.__fqdn, 'SRV',
TypeError: query() got an unexpected keyword argument 'lifetime'

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "C:\Users\matheus.ferrari-ext\AppData\Local\Programs\Python\Python39\lib\site-packages\discord\client.py", line 343, in _run_event
    await coro(*args, **kwargs)
  File "c:\Users\Administrador\Desktop\redeSlime\eventos\message.py", line 29, in on_message
    cluster = MongoClient(mongo_url)
  File "C:\Users\matheus.ferrari-ext\AppData\Local\Programs\Python\Python39\lib\site-packages\pymongo\mongo_client.py", line 639, in __init__
    res = uri_parser.parse_uri(
  File "C:\Users\matheus.ferrari-ext\AppData\Local\Programs\Python\Python39\lib\site-packages\pymongo\uri_parser.py", line 500, in parse_uri
    nodes = dns_resolver.get_hosts()
  File "C:\Users\matheus.ferrari-ext\AppData\Local\Programs\Python\Python39\lib\site-packages\pymongo\srv_resolver.py", line 102, in get_hosts
    _, nodes = self._get_srv_response_and_hosts(True)
  File "C:\Users\matheus.ferrari-ext\AppData\Local\Programs\Python\Python39\lib\site-packages\pymongo\srv_resolver.py", line 83, in _get_srv_response_and_hosts
    results = self._resolve_uri(encapsulate_errors)
  File "C:\Users\matheus.ferrari-ext\AppData\Local\Programs\Python\Python39\lib\site-packages\pymongo\srv_resolver.py", line 79, in _resolve_uri
    raise ConfigurationError(str(exc))
pymongo.errors.ConfigurationError: query() got an unexpected keyword argument 'lifetime'
white ravine
#

cant ther be strings in arrays ?

burnt turret
white ravine
# burnt turret guild.id there

i have one more q

INSERT INTO guildsettings (guild_id,prefixes) VALUES (761172818465390593,ARRAY['@cracked#4801','@cracked#4801','797733324281479218','!']);

when it inserts it in to the table the data becomes
{@quartz moat,@quartz moat,797733324281479218,!}
are these still like a string

#

{@cracked,@cracked,797733324281479218,!} = ['@cracked#4801','@cracked#4801','797733324281479218','!']

burnt turret
#

i think they're still strings

#

you could easily check that by just retrieving it?

white ravine
hasty juniper
# 
asyncpg.exceptions.InvalidTableDefinitionError: column "blockedidlist" pseudo anyarray
``````sql
CREATE TABLE IF NOT EXISTS blacklist (
    EventerId BIGINT,
    BlockedIdList ANYARRAY,
    BlockedStatus BOOL
);
restive pilot
hasty juniper
#

@restive pilot then how can create list in db

white ravine
hasty juniper
#

Thanks<3

white ravine
#

oof its my first time helping someone related to coding lmao i am a noob

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

this is the error i am getting
i really dont understand what it means ig it means
the content in the list should not be a str

burnt turret
#

this isn't really a database question;
you use indices to access data from a list
indexing starts from 0, so the first element in a list is 0, the next is 1 and so on....

>>> a_list = ["foo", "bar", "foobar"]
>>> a_list[0]   
'foo'
>>> a_list[1]
'bar'

now indices (the stuff inside the []) can only be integers, and your error says you're passing in a string value

white ravine
#

ahhhhhh

foggy juniper
#

What’s a beginner db I can use with d.py?

burnt turret
#

SQLite is pretty good for a small bot

#

you'd be using aiosqlite to make non-blocking calls to the database

foggy juniper
#

How do I use with d.py?

#

How do I grab the stuff from the db and send it as a Discord message?

burnt turret
#

that's...too broad a topic for anyone to just explain over discord, you should search for tutorials online

foggy juniper
#

There are no tutorials

burnt turret
#

there are literally millions of tutorials lmao

pure gust
#

Can I ask beginner question here?

torn sphinx
torn sphinx
foggy juniper
torn sphinx
#

is anybody active to help in this chat

proven arrow
#

This is probably a little more than you need, so you can remove some columns like model_type, guard_name.

  • Wherever it says model you can replace with user. Reason this has model is because its a general and can model permissions for many things.
  • The two tables in the middle (permission, and roles) is where you store the permission values.
  • Table on right is for achieving the M-M relation and allowing a permission to belong to multiple roles. (which is what you said you want)
  • Tables on left are just for storing what permissions a user has or what roles they have (also M-M). This allows a User to inherit all permissions from a role, as well as be assigned individual permissions.
torn sphinx
#

Thanks that is what im looking for, looks like it covers all.
But thinking how to make the queries for this hurts me

#

🥴

quartz island
#

Hello, I'm just getting started with MongoDB, real noob here. I'm using the pymongo driver to access my mongo atlas cluster. I have also installed dnspython library. I have set up a database in Atlas called McDonalds and one collection in it called 'Drinks'. my python code :

import pymongo
cluster = pymongo.MongoClient(<'blablasomething?Yash:<my password>.bla bla something @/McDonalds bla bla'>)
db = cluster['McDonalds']
coll = db['Drinks']
record = {'name':'coke-float', 'sizes':['small','medium','king'], 'prices':[80,115,145], 'extra':['ice','cream']}
coll.insert_one(record)

But I always get some error like ServerSelectionTimeoutError. The jupyter notebook hangs for like 30 seconds and throws out this error. Can someone please help me what I can do in this situation. I read the docs it said that either the servers aren't available (but I've been trying for 8 hrs now) OR some other primary secondary node thingy which I didn't understand.

autumn epoch
#

Why am I getting this error:

Syntax error at or near "$1"

And this is the code:

 await self.client.db.execute("UPDATE users SET ($1) = ($1) + ($2) WHERE userid = ($3)", item, amount, ctx.author.id)
burnt turret
#

what column are you trying to change there?

autumn epoch
#

The column of the item

burnt turret
#

gonna assume it is a column called item, you'd then want

UPDATE users SET item = item + $1 WHERE userid = $2, amount, ctx.author.id
autumn epoch
#

No

#

It would be called basicnet

#

For the exemple I am testing with

#

I have a column for every item

torn sphinx
#

can anyone say how to create heroku api_key?

quartz island
mint dust
#

How can I find all the duplicate items based on 2 columns (if name=x and if src=youtube) in a database? I'd have a list of items and I'd need to fetch all the duplicates and update the values

#

atm I have

def db_get_duplicates(items:list):
    q = "SELECT * FROM items WHERE name=? AND src=?"
    params = [tuple([i["name"], i["src"]]) for i in items]
    c.executemany(q, params)
    return [db_cnvrt_to_item(i) for i in c.fetchall()]

but noticed that executemany doesn't work with SELECT

restive pilot
#

@quartz island your code does make any sense at all, can you please provide more details on the SQL part, e.g sample data / table structure
and what you want to achieve? Drop python part as this is SQL related 😛

solemn cipher
#

How would I loop through all documents in a collection in mongoDB

#

and then add a key-value pair to each document

burnt turret
mint dust
quartz island
quartz island
autumn epoch
#

@burnt turret How do I insert into a table if it is not already excisting?

burnt turret
#

is this SQL?

autumn epoch
burnt turret
#

if it is, you'd have to create the table first anyhow, I don't know of any other way

#

try: except the error that'll be raised for "table doesnt exist" and have it create the table there

autumn epoch
#

But when I insert the information in, how do I do that if it is the userid is already in the table

burnt turret
#

what use-case is it though that you'd be trying to insert information in to a non-existing table

autumn epoch
#

No the table is already there

burnt turret
autumn epoch
#

I am doing this:

await self.client.db.execute("INSERT INTO users VALUES ($1), 0, 0, 0)", member.id)
burnt turret
#

oh you're looking for an upsert

#

you want to insert if doesn't exist, update if does exist i assume?

autumn epoch
#

Yes

white geode
#

I'm using umongo and I can use find_one() but when I try o iterate over find(), nothing happens

#

I dont understand

burnt turret
autumn epoch
#

Ok

autumn epoch
burnt turret
#

sure

#

that'll do nothing if the user already exists

autumn epoch
#

Ok

#

Yes

solemn cipher
#

well Ill keep that in mind later ty

burnt turret
#

Ah lmao my bad, I tend to turn the ping off because there are people who don't like it

solemn cipher
#

¯_(ツ)_/¯

torn sphinx
#

What do you all think about the SQLAlchemy module?

torn sphinx
#

That’s like saying do you like pizza

#

It’s a orm so it has benefits

#

I reply take that as an "obviously good, it's very popular" . edited

white geode
# 
entry = await MapData.find({"map_name": "eichenwalde"})
for e in entry:
  ...

why does this not work when

entry = await MapData.find_one({"map_name": "eichenwalde"})

does

#

D:

burnt turret
white geode
#

oh

torn sphinx
#

Most benefit of ORM is how you can make advanced queries using oop manner. And very little python code will allow you to do a lot of Sql code. @torn sphinx

white geode
#

i should read the async docs

#

thank you

burnt turret
#

👍 the relevant docs are linked in the gist as well

torn sphinx
#

Is there any use case for when you can have multiple tables per entity like a user. Or is it a bad idea?

torn sphinx
dim chasm
#

could someone help me out, showing how to use mongodb facet with multiple queries and adding limit ?

#

wasted 3 hours already trying to get the pipeline together, getting errors at all times

fast whale
#

hey so I am not very sure but there must be a way to do sort results like this , I want a list of webhooks where automeme_toggle is True and automeme_time (datetime obj) must be same as now or must be in past (i simply wanna compare it with current_time). if there's a way , how do I do it?

current_time = datetime.now()
record = await conn.fetch("SELECT automeme_webhook FROM autoevents WHERE automeme_toggle = $1 AND automeme_time = $2",True,i_don't_know_what_to_write)

I am searching for a direct way so kindly don't recommend comparing time after fetching the values , i know how to do that.

quartz island
#

MongoDB Atlas takes an IP address in order to access the cluster. But my IP is dynamic, what should I do?

chrome vapor
#

i have my database on my windows computer but now i wantt to run my discord bot on my raspberry pi 24/7

#

im having trouble trying to get it to work

#

i need to have the database running on the pi now, im new to this though so im having trouble even staarting

#

currently im having an InvalidAuthorizationSpecificationErorr when just running the code as is

ebon pulsar
# 
    pointsCursor = mydb.cursor()
    select_statement = "SELECT `points` FROM `user` WHERE `uuid` = %(uuid)s"
    pointsCursor.execute(select_statement, { 'uuid': memberid}, multi=False)
    userdata = pointsCursor.fetchone()
    pointsCursor.close()
    print(userdata)```




does anyone have any idea why I always get the same value with every query, although entries in the database have been changed?

Many thanks for the help
long oriole
#

what would you need to host a postgres db

fluid bolt
ebon pulsar
#

I get one point per message and if I then enter $ rank where I display my points, it first gives the correct one and then always the same from the first time I issue commands

fluid bolt
#

i'm no sql expert but it may be because of this line pointsCursor.execute(select_statement, { 'uuid': memberid}, multi=False)

ebon pulsar
#

I write messages between the two commands and the value in the database is also changed

#

what's wrong with the line?

fluid bolt
#

pointsCursor.execute(select_statement, memberid, multi=False)

ebon pulsar
#

wait, i test it

fluid bolt
#

i believe when making queries with sql you pass in *args, not **kwargs (dictionary)

fluid bolt
dim chasm
#

what's the best way to get multiple object with different keys last outputs in mongo db?

fluid bolt
#

@dim chasm could you describe it better?

ebon pulsar
#

I've tried it now and it doesn't work, it now gives me the error code

You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '%(uuid)s' at line 1
dim chasm
#

i got a collection where i got stored 2M entries. here's an example of one {'_id': ObjectId('6024e363b030eb0750158cc0'), 'type': 'ticker', 'sequence': 10194734611, 'product_id': 'BTC-EUR', 'price': '36935.39', 'open_24h': '38470.25', 'volume_24h': '3506.76562143', 'low_24h': '36050', 'high_24h': '39100', 'volume_30d': '97723.78494984', 'best_bid': '36911.87', 'best_ask': '36935.39', 'side': 'buy', 'time': '2021-02-11T07:57:19.617245Z', 'trade_id': 36104105, 'last_size': '0.00407635'} I want to be able to look up different product_ids and check for the latest price

#

lets say i got like 100 different product_id keys i want to look up and combine the output, hope that makes sense

sinful saffron
#

Hey, how do I connect to a database with aiosqlite?

dim chasm
# 
def getdata(base, query: dict = {'product_id': 'BTC-EUR'}, filter_fields: list = [], limit: int = 10,
            order: int = -1) -> list:
    filter = {'_id': 0}
    for field in filter_fields:
        filter[field] = 1
    return base.find(query, filter).sort([('$natural', order)]).limit(limit)


current_currency = 'BTC-EUR'
filters = ['price', 'time', 'product_id']
query = {'product_id': current_currency, "time": {"$gte": date_from, "$lt": date_to}}
entries = getdata(coinbase, query, filters, limit=-1)``` i was using this atm to get one entry
#

i tried to use facets, but that's taking like 5seconds to for output, and that's with out the datetime filter, a bit slow py def test(): pipeline = [ { '$facet': { 'BTC-EUR': [ {'$match': {'product_id': 'BTC-EUR'}}, {'$sort': {'natural': -1}}, {'$limit': 1}, {'$project': { "price": 1, '_id': 0, }}], 'BTC-ETH': [ {'$match': {'product_id': 'BTC-ETH'}}, {'$sort': {'natural': -1}}, {'$limit': 1}, {'$project': { "price": 1, '_id': 0, }}], } } ] return list(coinbase.aggregate(pipeline))[0]

dim chasm
#

anyone?

long oriole
#

what would you need to host a postgres server

quartz island
fluid bolt
#

yeah

#

there's an option to whitelist all ips so you can just click that

#

then you can connect from anywhere

torn sphinx
#

can i talk about databases for my d.js bot?

ebon pulsar
# 
    pointsCursor = mydb.cursor()
    select_statement = "SELECT `points` FROM `user` WHERE `uuid` = %(uuid)s"
    pointsCursor.execute(select_statement, { 'uuid': memberid}, multi=False)
    userdata = pointsCursor.fetchone()
    pointsCursor.close()
    print(userdata)```





Hello everyone,
does anyone have any idea why I always get the same value with every query, although entries in the database have been changed?

Many thanks for the help
solemn cipher
#

@burnt turret are you still on 🥺 I have a quick question

#

regarding that find() thing

# 
        rankings = Board.find().sort('points',-1)
        i = 1
        embed = discord.Embed(title="Rankings: 🔺", color=discord.Color.red())
#

Im making a game leaderboard

#

right adn

#

and*

#

uh

#

I did for a in rankings

# 
Traceback (most recent call last):
  File "C:\Users\Tom the Bomb\AppData\Local\Packages\PythonSoftwareFoundation.Python.3.8_qbz5n2kfra8p0\LocalCache\local-packages\Python38\site-packages\discord\client.py", line 343, in _run_event
    await coro(*args, **kwargs)
  File "c:\Users\Tom the Bomb\FishBot.py", line 112, in on_command_error
    raise error
  File "C:\Users\Tom the Bomb\AppData\Local\Packages\PythonSoftwareFoundation.Python.3.8_qbz5n2kfra8p0\LocalCache\local-packages\Python38\site-packages\discord\ext\commands\core.py", line 85, in wrapped
    ret = await coro(*args, **kwargs)
  File "c:\Users\Tom the Bomb\event.py", line 188, in leaderboard
    for x in rankings:
TypeError: 'AsyncIOMotorCursor' object is not iterable
white geode
#

Is it possible to search for a fragment of a value in mongodb?

Like if the document was
{"creator": "Person1 & Person2"}

can I use find() and exclude "& Person2"
find({"creator": "Person1"})

#

I guess I could use $regex right?

quartz island
burnt turret
burnt turret
white geode
#

works wonderfully

quartz island
crude shard
#

Is this the place where I can ask about mysql?

burnt turret
#

sure, go ahead

crude shard
#

What does this mean? mysql.connector.errors.NotSupportedError: Authentication plugin 'caching_sha2_password' is not supported

import mysql.connector

db=mysql.connector.connect(
    host='localhost',
    user="root",
    passwd="abc123"
    )
#

Found the mistake. Was using a previous version of it

hazy smelt
#

Any idea on how to set up MySQL on petrodactyl

sudden warren
#

hello im just new to sqlite3 i'd like to know what syntax error am i committing in this code block and what could be the solution?

order_sql = "INSERT INTO orders (date, customer_id) VALUES (?, (SELECT id FROM customers))"
date = "1944-02-22" # ISO formatted date 
cur.execute(order_sql, (date,customer_id))
order_id = cur.lastrowid
print(order_id)
con.commit()
#

I'm receiving this ProgrammingError: Incorrect number of bindings supplied.

sudden warren
#

let me change the question, how do i make customer_id take the value of id of that specific customer from customers table?

torn sphinx
#

@sudden warren why are you doing like this?

#

customer id needs to be passed from application to query. Not query to query like you do now

#

and when you do select id from customers it gives you list of all ids in the table so it will fail as well like that

sudden warren
#

I'm sorry I'm very new to this. I tried changing the code to this one but the select query became string instead lull

order_sql = "INSERT INTO orders (date, customer_id) VALUES (?, ?)"
date = "1944-02-22" # ISO formatted date 
customer_id= "SELECT id FROM customers INNER JOIN orders ON customers.customer_id=orders.customer_id"
cur.execute(order_sql, (date, customer_id))
order_id = cur.lastrowid
print(order_id)
con.commit()
torn sphinx
#

the customer_id needs to be an integer. you pass a string

#

What your query looks like currently is like this, INSERT INTO orders (date, customer_id) VALUES ('1944-02-22', 'SELECT id FROM customers INNER JOIN orders ON customers.customer_id=orders.customer_id')

sudden warren
#

Ok so i changed it to this:

order_sql = "INSERT INTO orders (date, customer_id) VALUES (?, ?)"
date = "1944-02-22" # ISO formatted date
cur.execute(order_sql, (date, customer_id))
cur.execute("SELECT customers.id FROM customers INNER JOIN orders ON customers.id=orders.customer_id")
order_id = cur.lastrowid
print(order_id)
con.commit()

but all customer_id became 7 instead

terse shale
#

how can I make a field in sql have the same value as a field in another table?
(always, I can't just hardcode both of them)

shell ocean
#

what do you want to do exactly

terse shale
#

so I have a table called clans and a table called members
member should have a clan_tag attribute that is mapped a clan tag from clans
if I change the clan tag in clans, I want clan_tag in members to change too
if I change clan_tag in members, I want to have a new mapping to another clan tag

shell ocean
#

why don't you just have a clan_id in the members table that is a FK to the PK of clans

#

you can even have clan tag be the PK if you want shrugs (though there are tradeoffs)

terse shale
#

because I have no clue of sql and I didn't know what FK and PK are

#

oopsie

shell ocean
#

you should read a bit about the relational model of data

#

PK = primary key, FK = foreign key

#

basically PK is like the "id" of each row

#

it has to be unique

#

and is normally a number

#

and a FK is a column which "links" one table to another

#

so a FK must be a PK value that is present in the linked table

#

it might look like this:

# clans

id | clan_tag
 1 |       EG     
 2 |       OG
 3 |      [A]

# members

id | username | clan_id
 1 |      Fly |       1
 2 |     Abed |       1
 3 |   Topson |       2
#

so you can join on clan_id to get the clan_tag for any number of rows in the members table

terse shale
#

also thanks a lot for your help so far

torn sphinx
#

because the column will have an index, and index can become large

#

also each time you make insert/update/delete or like this then you also have to update the index, and so it can take longer. But you probably wont even notice like this tbh

terse shale
#

when I let pk autoincrement does it make a difference whether unique?

torn sphinx
#

primary key will always be unique

#

by default i am sure it has these attributes of unique included so you dont need to do