databases

[torn sphinx]

pg_dump discord > C:\Users\DevletiOsman\Desktop> gives error, invalid command \Users Try \? for help.

[proven arrow]

Don't run it from psql @torn sphinx

On windows inside your postgres installation folder there will be a bin folder in which there is pg_dump.exe

Thats what you need to be running and what gkrou meant.

pg_dump.exe -U username database_name > C:\path\to\backup\folder\backup_name.sql```
Try this

[torn sphinx]

alright thanks i try now

[remote vessel]

ok, so i had a discussion last night about not creating dynamic tables, but i have a question about creating controlled tables that dont allow for user input as the table variable

[torn sphinx]

thx so much guys that worked, and sorry i misunderstand you gkrou @tepid cradle @proven arrow

[remote vessel]

def newTableName(c_name="CompSci"):
    path = getPath(c_name, "json")
    with open(path) as JsonReader:
        db_tables = json.load(JsonReader)
    t_id = len(db_tables) + 1
    table_name = f"t_100{t_id}"
    return table_name

def buildDB(c_name="CompSci"):
    timestamp = buildTS()
    t_name = newTableName()
    param_dict = {
        "name": "SYSTEM",
        "msg": "Welcome to the chat!",
        "timestamp": timestamp,
        "table": t_name
    }
    path = getPath(c_name, "db")
    print("building database")
    conn = sqlite3.connect(path)
    sql_placeholder = "CREATE TABLE " + param_dict["table"] + "(ID INTEGER PRIMARY KEY AUTOINCREMENT,NAME TEXT, MESSAGE TEXT, TIMESTAMP TEXT)"
    conn.execute(sql_placeholder)
    sql_placeholder = "INSERT INTO " + param_dict["table"] + "(NAME,MESSAGE,TIMESTAMP) VALUES (?, ?, ?)"
    conn.execute(sql_placeholder, (param_dict["name"], param_dict["msg"], param_dict["timestamp"]))
    conn.commit()
    conn.close()
``` is there something wrong with this to create dynamic tables? i know, im missing some code, but thats the basic idea

[clever elk]

Nope all good

[remote vessel]

there was a discussion last night about not creating dynamic tables, but this is the solution i came up with to allow for table creation, just with no user input into building the table. i would just rather not rebuild the entire rest of my code to do it out of one table

[boreal niche]

SQL.execute("CREATE TABLE user_inventory (itemname TEXT,userid INT NOT NULL,count INT NOT NULL DEFAULT 0 CHECK(count>=0),itemid INT NOT NULL,FOREIGN KEY(itemid) REFERENCES shop(itemid)")
```  `check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1` help?

[pseudo cove]

ah yes mysql and its ed-tier error messages

missing close paren at the end @boreal niche

[boreal niche]

ah yes mysql and its ed-tier error messages
@pseudo cove thx

[dark solstice]

ed is the standard text editor

[pseudo cove]

?

[simple berry]

is there any python module to convert csv file to SQL file?

cuz my csv files have many columns and even creating that table with the headers first and then using copy command is a pain

[verbal cosmos]

I am working through a code question and IDK why I'm getting this error. I'm using Repl.it. We are supposed to use Python and SQLite to organize a csv file to a db file. The code spits back an error, but still gives me a db file. The error is also confusing, and I don't understand

is it okay if I post the code here? I am brand new and don't know exact etiquette yet. (I did watch the rules video tho)

[pseudo cove]

yes

please post both

[verbal cosmos]

okay so now I worked through the rror

I can still post

now Im trying to sort the data by average per month

[glad wraith]

yo

[verbal cosmos]

howdy

[flint wadi]

Let’s say you store a user's profile picture name in a database, but you also have a few different sized profile pics named e.g. name-small.jpg. Would you
a) store all names in the database, or
b) only store original name, and modify it to get the others (each time you retreive it)?

[proven arrow]

I would go with option b as it easier to scale, since it allows for different sizes to be added/removed in the future too.

[flint wadi]

Thanks.

[proven arrow]

So you are only doing string manipulation to get the different sizes which would be easier as well.

[flint wadi]

Yeah, I see what you mean.

[broken estuary]

i needed some help how to draw this kind of schema:
i got a "project-code" that may have one or more "contracts";
but the "project-code" can sometimes have multiple "subproject-code", if this happens;
the "project-code" will not have the "contracts" instead the "subproject-code" will have one "contract" per "subproject-code"

the schema i have is:

class Project(db.Model):
  code = db.Column(...)
  title = db.Column(...)
  cont_id = db.Relationship("Contract"...)
  subproj_id = db.Relationship("SubProject"...)

class SubProject(db.Model):
  proj_id = db.Column(...db.ForeignKey("project.id"))
  code = db.Column(...)
  title = db.Column(...)

class Contract(db.Model):
  proj_id = db.Column(...db.ForeignKey("project.id"))
  subproj_id = db.Column(...db.ForeignKey("sub_project.id"))
  cont_code = db.Column(...)

in this approach i can kinda do that technically, but it will have a lot of NULL values in my table for "project" that doesnt have "subproject" and vice versa, which i think is not a good practice.

im using flask-sqlalchemy

[glass gorge]

bleh for some reason mysql is inserting duplicate rows

[rough hearth]

Is there a place where you can do mysql commands online starting from an empty database?

[proven arrow]

@rough hearth https://www.db-fiddle.com/ Is this what your looking for?

It has support for postgres too

[rough hearth]

@proven arrow I'll try it, thanks!

[torn sphinx]

hello

i am using oracle sql and want to know how can i do a group by weekly where weeks start on tuesday?

[shy viper]

async def setup_db(bot):
    await bot.wait_until_ready()

    bot.my_db = await aiosqlite.connect(...)

bot.loop.create_task(setup_db(bot))

I can put this in the central point where all the cogs are connected

[pseudo cove]

@shy viper wrong channel?

#discord-bots

[rough hearth]

given student(sid, studentName, gpa)
I'm not sure why this doesn't return all the students who share the highest GPA

select studentName, gpa from student s1 where s1.gpa = (select MAX(gpa) from student s2 where s1.sid=s2.sid);

select studentName from student where gpa=4.0; works but I can't assume that someone has a 4.0. select studentName from student where gpa=max(gpa); is invalid syntax.

[proven arrow]

You can do SELECT studentName FROM student WHERE gpa=(SELECT MAX(gpa) FROM student) @rough hearth

[rough hearth]

huh that worked @proven arrow

wonder why I didn't think of that

thanks!

[shy viper]

    cursor = await self.client.db.cursor()
AttributeError: 'Bot' object has no attribute 'db'

[copper hazel]

question: can you create a data base using only python? everything I have searched wants you to import my sql or something else. I have a project due and the professor has given minimal information.

[rough hearth]

@copper hazel you can use python libraries to do database stuff without having to write any actual sql code, but python isn't a database language.

[clever elk]

Probs

[copper hazel]

CSCU 220 Signature Assignment: DMV Database
You will create a Python command line application that allows the creation, editing, and storage of a
database of DMV records. See the video of an example execution for clarification.
Create a Python file dmv_record.py that contains the definition of a class named DmvCarRecord. The
class should include an init method that initializes the following fields using optional
parameters:
• license_num
• maker
• model
• year
• owner_id
• reg_exp_date
It also should include a str method that returns a string representation of an instance of the
class, for example "License Num DEF456 is a 2001 Honda Civic owned by AB4242 expires on 20200203".
Create a Python file dmv_app.py that contains the definition of a class named DmvApp. The class should
include a method called command_loop that presents the following menu (or something similar) to the
user:
0: Exit application
1: List vehicles
2: Display vehicle record
3: Add vehicle
4: Remove vehicle
5: Load DB
6: Save DB
7: Remove all vehicles

The user will enter a number to select a particular command. The commands should include:
• Exit application - exits the application
• List vehicles - lists all of the vehicles in the database
• Display vehicle record - prompts the user for a license_num and then uses that to find and
display the vehicle record for that vehicle
• Add vehicle - prompts the user for vehicle data and adds a new vehicle record to the database
• Remove vehicle - prompts the user for a license_num and removes the identified record
from the database
• Load DB - prompts the user for a file name and loads the database from that file
• Save DB - prompts the user for a file name and saves the database to that file
• Remove all vehicles - empties the database

that was the assignment... i know its a lot but it says nothing that really helpful

[proven arrow]

huh that worked
That would be due to the order of how the query is evaluated. You cant have MAX in a WHERE clause, because as the rows are being read by the DB they would be checked for the WHERE condition. And since the WHERE clause is applied to each row, you cant calculate the MAX because for the MAX to work you need all the rows. If that makes sense? In short its just the order of operations. @rough hearth

[clever elk]

Perfect!! Thanks!

[boreal niche]

    @client.command(aliases=["inv"])
    async def inventory(ctx):
        USER_ID = ctx.message.author.id
        SQL.execute("SELECT shop.itemname FROM user_inventory INNER JOIN shop USING(itemid) WHERE user_inventory.userid = %s", (USER_ID,))
        inventory1 = SQL.fetchmany(2)
        SQL.execute("SELECT user_inventory.count FROM user_inventory INNER JOIN shop USING(itemid) WHERE user_inventory.userid = %s", (USER_ID,))
        inventory2 = SQL.fetchmany(2)
        embed = discord.Embed(
            title = f"{ctx.author.name}'s Inventory",
            colour = discord.Colour.dark_gold()
        )
        embed.add_field(name=f"{inventory1[0]}➖{inventory2[0]}", value=f"*ID*➖{inventory1[0]}")
        embed.add_field(name=f"{inventory1[1]}➖{inventory2[1]}", value=f"*ID*➖{inventory1[1]}")
        await ctx.send(embed=embed)
``` why is it only able to get one row eventhough there is 2
it keeps getting the index out of range error

    cursor = await self.client.db.cursor()
AttributeError: 'Bot' object has no attribute 'db'

@shy viper its just db.cursor() since discord.py has nothing to do with databases

[simple berry]

is there any performance difference between using psql and sqllite with python?

[broken estuary]

i needed some help how to draw this kind of schema:
i got a "project-code" that may have one or more "contracts";
but the "project-code" can sometimes have multiple "subproject-code", if this happens;
the "project-code" will not have the "contracts" instead the "subproject-code" will have one "contract" per "subproject-code"

[pulsar kestrel]

anyone familiar with sqlalchemy. I need help to get my datas dynamically to my database when a user fill the input fields

i am always getting this

[timber jasper]

will that work:

hug = await self.bot.db.fetchval(f"SELECT image, category FROM discordguildssql.emotions WHERE id = nextval() ORDER BY random()")

        if hug == 'hug':      # also if hug in category then it select the image hug
            if ctx.author:
            embed = discord.Embed()

[lethal depot]

https://mystb.in/LiquidPrototypeRotary.python
why is it saying that casenum is always 1

[torn sphinx]

First time coding a multi-guild Discord bot. Going pretty well 😅. Using MongoDB
https://media.discordapp.net/attachments/751880938387931146/769699668668973077/unknown.png
https://media.discordapp.net/attachments/751880938387931146/769700305428283452/unknown.png

[glass gorge]

so

given that mysql only supports date times in the format of yyyymmdd

am i to assume that if an end user needed to use dates

id have to take their format (in my case mmddyyyy) and write a query to transform that input into the mysql format

and vice versa

[limber stone]

When you use as, how can you use the name you give that as a condition? For example:
select data -> 'active_threads' as "AT" from ts_data where ("AT")::text <> '{}'::text;

Or do i need to use something different?

[torn sphinx]

I'm making an economy system discord bot and i'm creating a new table per user

this seems to be inefficient

any better design ideas?

the table is for a user's inventory

[shell ocean]

okay

so I'm going to guess that items have their own table?

and a user can have multiple items?

do you understand the idea of a foreign key?

[torn sphinx]

no

here's a short summary of how my economy system works

a user joins a server and gets a random item with a rarity value

if they leave the server before a specific time they lose the item

[shell ocean]

okay

but

okay wait so each user can only have one item?

[torn sphinx]

no

they can have multiple items

but if that's a problem i can just implement coins instead

[shell ocean]

no

it's just that

okay wait

so what is an item?

how are items created?

[torn sphinx]

an item is an integer

i have a dictionary with each integer corresponding to an item

for now an Item just has a str name

i might add rarity and other values soon

actually think of it in terms of coins

a user joins a server

they get a coin

they leave it before 3 days

they lose the coin

this is the system basically

[shell ocean]

hm

why isn't it stored

in the database too, then

[torn sphinx]

which one isn't stored?

[shell ocean]

anyway, if it's like that just have an item_id column in your User table

which one isn't stored?
@torn sphinx itesm

[torn sphinx]

ditch the item thing

think in terms of coins

a coin is just a coin

users have a balance, when they earn a coin the balance is incremented

[shell ocean]

then just store it as a field in a User table

each user is a row

[torn sphinx]

what about invite tracking

for invite tracking i would need to store the guild ID, the invite generated by the bot, and the Invite.uses from the previous run

so when a new member joins the server the bot checks if the Invite.uses has gone up

if it has, the new member joined through the bot

so the balance is incremented

so at least two tables

if I have a User table, I need to store the balance and the guilds the user has joined (along with the guild data i.e invites)

[shell ocean]

yeah, an Invite table that you compare to a cache

[torn sphinx]

__User__
Guild | Join_date | Balance

see the problem?

[shell ocean]

if I have a User table, I need to store the balance and the guilds the user has joined (along with the guild data i.e invites)
@torn sphinx one balance per server?

[torn sphinx]

no

one universal balance

[shell ocean]

then why do you need to store the guilds

in the user table

[torn sphinx]

i need to store the guilds so i can keep track of which the member has just joined

i need to track whether the member leaves before 3 days

so the guild ID and the timestamp

and the invite and invite count

and the balance, in a single table

Guild_id | Date | Balance
1234 | 20434030 | 2
2234 | 20434004 | 3
3553 | 45850458 | 4

that's a waste of space

[shell ocean]

okay, so if a user joins two different guilds

and leaves one before 3 days are over

what happens

[torn sphinx]

they get one coin for each guild

so 2 coins

then they leave one

-1 coin

= 1 coin left

[shell ocean]

okay.

so

[torn sphinx]

the penalty might be different later on

[shell ocean]

a third table

which has user_id, guild_id, join_date

[torn sphinx]

CREATE TABLE IF NOT EXISTS user_{userid} (
            guild_id integer NOT NULL,
            join_date integer NOT NULL
            );

CREATE TABLE IF NOT EXISTS guilds (
            guild_name text NOT NULL,
            guild_id integer NOT NULL,
            invite text NOT NULL,
            invite_uses integer NOT NULL
            );

CREATE TABLE IF NOT EXISTS balance (
            user_id integer NOT NULL,
            user_balance real NOT NULL
            );

these are my tables atm

the first one is generated separately for each user

i guess that can be changed

thanks for suggesting that

[shell ocean]

yeah, you shouldn't have a table per user

that's not really appropriate for relational databases

[torn sphinx]

yeah I never managed to wrap my head around database structure

kind of went along with it

thanks for the idea i'm testing it out

[shell ocean]

np

[torn sphinx]

So I realized that if a user joins mutliple guilds, I'd have something like this

User_id | Guild_id | timestamp
User1 | server_1 | 29283
User1 | server_2 | 39398
User2 | server_1 | 18292

So the two repeated user1s are a waste of storage

@shell ocean

What do you think?

[proven arrow]

Is that your users table?

If so then you should have a user_join table and reference it with a foreign key.

So a 1-M relationship

[torn sphinx]

Can you explain how that would work?

I'm still trying to grasp the concept of a foreign key

Is that your users table?
@proven arrow yes it is

[proven arrow]

Each table has a primary key, which is the key that uniquely identifies each row in that table right?

[torn sphinx]

Yeah

[proven arrow]

So then to link two table you can link them using a foreign key. So for example, your users_join table would have a column called user, and this would hold the Primary Key that identifies the user record in the users table.

Do you see how they would link?

[torn sphinx]

So it's like a subtable in a sense, except that it's not nested

Nvm

Got it

So could you make a simple diagram of both tables?

[proven arrow]

Not really. It's just a way of linking tables. Your basically referencing two tables together.

[torn sphinx]

Like this

User_id | Guild_id | timestamp
User1 | server_1 | 29283
User1 | server_2 | 39398
User2 | server_1 | 18292

@torn sphinx

[proven arrow]

I'm on mobile

Let me try

[torn sphinx]

Okay

If it's too much trouble you can just type the column names

[shell ocean]

So the two repeated user1s are a waste of storage
@torn sphinx why do you think it's a waste of storage

[torn sphinx]

It isn't?

[shell ocean]

help me understand why you think so

[torn sphinx]

It's unnecessary

[shell ocean]

so how should it look, then?

IYO

[torn sphinx]

It can't be null, and I'm not sure how it'd look

I suppose something like a pointer

Maybe that's what a foreign key is

No idea

[shell ocean]

yes

this is literally what a foreign key is

[torn sphinx]

Oh lol

[shell ocean]

okay

so think of each table

as modelling something, right?

[torn sphinx]

Yeah

[shell ocean]

specifically, relationships.

this table is modelling the user-guild relationship.

so each row is a relationship between a user and a guild.

[torn sphinx]

Yeah

[shell ocean]

given that, it is of course possible that multiple rows may have the same user ID

because the same user may have a relationship with multiple guilds

same thing for guild IDs

this is what is called a many-to-many relationship

so yes, in a sense, the data is duplicated

but that is exactly how it should be represented

you're probably thinking of how it might look in a non-relational setting

[proven arrow]

https://paste.pythondiscord.com/femohocihi.rb

See that.

[torn sphinx]

Thanks

[proven arrow]

Obviously the PK column you decide can be different

[shell ocean]

like:

data = {
    user_1: [
        {'guild': server_1, 'timestamp': some_timestamp},
        {'guild': server_2, 'timestamp': some_other_timestamp}
    ]
}

[proven arrow]

But that is how they are linked

[shell ocean]

(with more stuff)

but the benefit of storing data in this way

is that it allows efficient joins

and queries

like:

data = {
    user_1: [
        {'guild': server_1, 'timestamp': some_timestamp},
        {'guild': server_2, 'timestamp': some_other_timestamp}
    ]
}

@shell ocean if you want to know what guilds a user is in, this is simple

but what if you want to know what users a guild contains?

or the timestamps for a guild?

then you're screwed

you need to traverse the whole table

always

@torn sphinx
@torn sphinx whereas when you store data like this, with a proper index you don't have to

so querying becomes a lot more efficient

[torn sphinx]

Ah that makes a lot of sense

So, do the duplicates take up extra space? Or does SQLite merge them into one and store it as a pointer? I have no idea how SQL works under the hood. I'm just trying to keep it as efficient as I can

[shell ocean]

So, do the duplicates take up extra space? Or does SQLite merge them into one and store it as a pointer? I have no idea how SQL works under the hood. I'm just trying to keep it as efficient as I can
@torn sphinx why?

let the database worry about such things.

this is the canonical way to store data in a relational database for a reason

don't think of it as duplication.

[torn sphinx]

Okay

Thanks :+1:

[shell ocean]

yw

oh, one last thing

SQLite isn't (really) meant for production in any case

you might want to look into other SQL dialects

[torn sphinx]

@shell ocean such as?

[shell ocean]

I use Postgres for my work

[torn sphinx]

MySQL seems to be a server-client thing, my database and program are on the same machine

Postgres, I'll check that out

[shell ocean]

I mean

you can host an SQL server on the same machine

that's not a problem

[torn sphinx]

It's just one program accessing it at any given time

[shell ocean]

there are a few things about SQLite

[proven arrow]

How often will you be reading/writing?

[shell ocean]

in particular, it is (basically) weakly typed

which is a bit 🥴

I only use it for prototyping, basically

[torn sphinx]

Depends on bot usage, I suppose at best, a thousand commands per second?

[proven arrow]

Then go postgres

[shell ocean]

It's just one program accessing it at any given time
@torn sphinx but there might be multiple connections, right

[torn sphinx]

Not sure, the bot will probably not be that popular any time soon

[shell ocean]

I cannot imagine you wouldn't have a connection pool

[torn sphinx]

I'm not sure how i would go about implementing that

[shell ocean]

what are you using?

just raw SQL?

i.e. no ORM

[torn sphinx]

I'm using async discord.py, I'm not sure how Postgres async works

Yeah, raw SQL

[shell ocean]

so you're using an async SQLite driver?

[torn sphinx]

No, blocking

[shell ocean]

🥴

[torn sphinx]

It's fine for now since not many requests at the same time

[proven arrow]

Here's sqlites info of when to use their DB, https://www.sqlite.org/whentouse.html
But if you want something scalable like 1000 commands per sec then you will want a server based DB like postgres.

[shell ocean]

sure, I gues

I mean

[torn sphinx]

I was wondering what would happen when scaled up

[shell ocean]

IMO it's p simple to just set up Postgres once and use it

rather than migrate if it gets popular

[torn sphinx]

How would I go about implementing async read/write with postgres?

[shell ocean]

find an async Postgres driver

there are two IIRC

asynciopg

or something like that

it's easily Googleable

[proven arrow]

asyncpg is the one you'll want

[torn sphinx]

Okay

I'll check that out

Thanks a lot guys ❤️

[shell ocean]

yw

[torn sphinx]

Seems like postgres is also a server-client thing

:/

[torn sphinx]

My first day of trying to setup Postgres with Python on Linux
apt-get ran in the background and finished without asking for a password
Now I have a postgres user and don't know the password
I tried the root password and a blank password
didn't work

hi

@shell ocean any ideas?

[vale sphinx]

Does anyone know a solution to this error: sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) unable to open database file (Background on this error at: http://sqlalche.me/e/13/e3q8)

[tepid cradle]

@torn sphinx There's no password, you can use it without a password. Of course, that's not a good idea, so you'll need to set up a password.
I'd recommend following the steps in this tutorial. I had used this and everything worked out smoothly
https://www.postgresqltutorial.com/install-postgresql-linux/

[torn sphinx]

I tried that tutorial but didn't work for me

anyway a guy in the postgres server helped me set it up

it works now

[tepid cradle]

👍

[torn sphinx]

@simple berry to what?

[simple berry]

found it

[torn sphinx]

Ok

[simple berry]

the server

[torn sphinx]

Ok

[torn sphinx]

Hello guys, I'm trying to reach my data base, and for it, I'm using "psycopg2" library, and it is unable to import it. I faced the issue by the past, and I installed the binary version to make it works. But unfortunately it still doesn't. Any idea how can pass over this issue ?

[sharp magnet]

p2 = p.groupby(['colN']).get_group('value')
p2.drop_duplicates(subset = 'colN1', keep = 'first', inplace = True)

I'm using the code above to group a specific set of data to then detect the duplicates of however when removing the duplicates it only returns the grouped data with the missing duplicates.
How can I get that ungrouped data back?
This is using the pandas module.
(edit I started a #help-corn so if you have an answer go there)

[tepid cradle]

@sharp magnet
With the caveat that this is the wrong channel to ask about Pandas, look at the duplicated function in Pandas. That is specifically meant to handle cases of duplication.

!d pandas.DataFrame.duplicated

[delicate field]

`pandas.DataFrame.duplicated`

DataFrame.duplicated(subset=None, keep='first')```
Return boolean Series denoting duplicate rows.

Considering certain columns is optional.

Parameters  **subset**column label or sequence of labels, optionalOnly consider certain columns for identifying duplicates, by default use all of the columns.

**keep**{‘first’, ‘last’, False}, default ‘first’Determines which duplicates (if any) to mark.

• `first` : Mark duplicates as `True` except for the first occurrence.

• `last` : Mark duplicates as `True` except for the last occurrence.

• False : Mark all duplicates as `True`.

Returns  SeriesBoolean series for each duplicated rows.

See also

[`Index.duplicated`](pandas.Index.duplicated.html#pandas.Index.duplicated "pandas.Index.duplicated")Equivalent method on index.

[`Series.duplicated`](pandas.Series.duplicated.html#pandas.Series.duplicated "pandas.Series.duplicated")Equivalent method on Series.... [read more](https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.duplicated.html#pandas.DataFrame.duplicated)

[torn sphinx]

I just don't get it. The tables are there, but it says that they don't exist. Addressing them as public."colors" doesn't work either
It's postgres btw

Okay, it was stupid, but "colors" had a cyrillic c

[tame nova]

@tender cloak

[tender cloak]

yeah

im here

[tame nova]

Can u send me what tutorial your watching

So I an understand

[tender cloak]

okay

https://www.youtube.com/watch?v=Le_RNN4po30&list=PLYeOw6sTSy6ZGyygcbta7GcpI8a5-Cooc&index=9

this is the one

im trying to do

but the database one

https://www.youtube.com/watch?v=4EIy0bw7s-s&list=PLYeOw6sTSy6ZGyygcbta7GcpI8a5-Cooc&index=5

is this

[tame nova]

Oh ok

[tender cloak]

im doing all this

[tame nova]

Ty

[tender cloak]

just for a poll command

[tame nova]

just for a poll command
@tender cloak why dont u use json

Instead of database

[tender cloak]

u can do that?

no

[tame nova]

Yes

[tender cloak]

im doing this

coz

[tame nova]

U can create json file

[tender cloak]

self.bot.scheduler

[tame nova]

If u have some servers

self.bot.scheduler
@tender cloak oh

[naive sandal]

That tutorial is using sqlite3. Please use aiosqlite if you are using this for a discord bot

[tame nova]

Uhhu

Uhh

[tender cloak]

https://stackoverflow.com/questions/30669474/beyond-top-level-package-error-in-relative-import

i found this tread

[tame nova]

Hmmm

Hi hmmmm

[pseudo cove]

full traceback + code

[tame nova]

Nice name

[tender cloak]

most of them say

use import sys

[pseudo cove]

you can do it without sys.path hackery

[tame nova]

you can do it without sys.path hackery
@pseudo cove u can?

Pls tell us then

[pseudo cove]

whats your folder structure

and how are you running the code

@tame nova

wait wrong ping @tender cloak

[tender cloak]

wdym

[tame nova]

Oh its ok

They pinged me instead of you

[tender cloak]

i know

wdym by folder structure and the way i run the code

its a discord bot

and im trying to make a poll command

but to end the poll

i need

self.bot.scheduler

and for that

i need a database

i guess

[pseudo cove]

can you screenshot the file explorer or whatever

in the ide

[tender cloak]

ok

actually

its kinda messy now

i dont have the folder opened

[pseudo cove]

thats the wrong one

[tender cloak]

becasue

i get some

weird errors

even tho the code is alright

[pseudo cove]

error message

post your code

[tender cloak]

no

when i import

the folder

[pseudo cove]

listen

i dont know what folder you're importing

and from what file

[tender cloak]

i know

ok

i fixed

it

now im sending

nvm

import praw works perfectly

until i open the folder

@pseudo cove

u want this?

[pseudo cove]

which import is failing

[tender cloak]

from ..db import db

and right now

[pseudo cove]

in reactions.py?

[tender cloak]

import praw is failing too

in reactions.py?
@pseudo cove yes

[pseudo cove]

are you running the code using python3 bot.py?

[tender cloak]

yes

its failing in

init.py too

[pseudo cove]

uh try adding __init__.py to the top level and to Cogs

[tender cloak]

umm ok

[pseudo cove]

are you in a venv?

[tender cloak]

btw

there are 2 __init__.py

files

[pseudo cove]

ik that

[tender cloak]

the one it the

folder bot

gives error

[pseudo cove]

the import beyond top level error?

[tender cloak]

yes

[torn sphinx]

hey guys, anyone familiar with ms sql?

[pseudo cove]

if you're running it using python bot.py, then from db import db should work

[tender cloak]

so no..?

[pseudo cove]

absolute import since the current directory is part of sys.path already

which would be the top level dir

[tender cloak]

it fixed it

but these give error

now

and praw

gives the same error

[glass gorge]

what's the best way to deal with xls data using flask? I have a mysql db. My goal is to do things like load xls files into my server through the web app, and download reports in xls format

should I use flask-excel?

[zealous parcel]

Hello guys, I have a problem when I connect to the database and it does not stop the connection, knowing that I put the lock code
use mysql

I used cursor.close() but it keeps calling

[proven arrow]

What do you mean it does not stop the connection?

cursor.close only closes the cursor, not the database connection

[zealous parcel]

Also used connection.close() does not stop the connection
Look
http://prntscr.com/v7ae1n
@proven arrow

[proven arrow]

Can you show how you close the connection in the code? And how you defined it

[zealous parcel]

Contact : http://prntscr.com/v7aiqz
Close : http://prntscr.com/v7aj7b

[proven arrow]

And what user do you connect to the DB as?

[zealous parcel]

root

[proven arrow]

connection.close() should close your connection unless you are opening the connection many times elsewhere

[zealous parcel]

Just one connection

Previously it was working but now it is not working

[proven arrow]

Hmm, well something is still keeping a connection to the database, although its idle or maybe your server is playing up i am unsure.

Is that output when you do SHOW PROCESSLIST ?

[zealous parcel]

No this Processes

[torn sphinx]

Is firebase a database?

[proven arrow]

Hmm i am unsure then

@torn sphinx Its more a platform, which has database products like their realtime database and firestore

[torn sphinx]

i can use with python?

nvm i find it

yes is compatiable

[glass gorge]

does mysql store timestamps automatically

or do i have to add it in my db model

[proven arrow]

You would have to add to your model

[glass gorge]

k tx

do i need to set the timezone everytime i connect to my db through a session?

[dawn rain]

Hi

I have to implement rest api in Django and postgresql
A ticket selling platform
And I have a problem with tickets availability (quantity)
For example I have normal ticket, 10k
Every reservation, before reservation, after reservation expired (after 15 minutes ticket has to return) I need to make select for update
If will be many requests at the same time, There will be problem with lock at this field
I think this will be insufficient solution.
How can I do it differently?
I am thinking about creating row for every ticket, this way I can select and lock row with ticket but other rows will be available

[timber nymph]

e

hey sorry guys how do u join voice calls

for this sever

server*

[raw elm]

hi, i have been trying to get a Python wrapper for SQLCipher to work on my Mac, I already have SQLCipher installed via brew install sqlcipher, and tried pip3 install sqlcipher3, but it seems like the wheel isn't building:

(env) jkelol111@MBPikachu server % pip3 install sqlcipher3    
Collecting sqlcipher3
  Downloading sqlcipher3-0.4.2.tar.gz (40 kB)
     |████████████████████████████████| 40 kB 465 kB/s 
Building wheels for collected packages: sqlcipher3
  Building wheel for sqlcipher3 (setup.py) ... done
  WARNING: Legacy build of wheel for 'sqlcipher3' created no files.
  Command arguments: /Users/jkelol111/Documents/Projects/Iamages/server/env/bin/python -u -c 'import sys, setuptools, tokenize; sys.argv[0] = '"'"'/private/var/folders/gq/lbg57vxd6qx3kbz82wrx03100000gn/T/pip-install-g6qazcog/sqlcipher3/setup.py'"'"'; __file__='"'"'/private/var/folders/gq/lbg57vxd6qx3kbz82wrx03100000gn/T/pip-install-g6qazcog/sqlcipher3/setup.py'"'"';f=getattr(tokenize, '"'"'open'"'"', open)(__file__);code=f.read().replace('"'"'\r\n'"'"', '"'"'\n'"'"');f.close();exec(compile(code, __file__, '"'"'exec'"'"'))' bdist_wheel -d /private/var/folders/gq/lbg57vxd6qx3kbz82wrx03100000gn/T/pip-wheel-tlg57vet
  Command output: [use --verbose to show]
  Running setup.py clean for sqlcipher3
Failed to build sqlcipher3
Installing collected packages: sqlcipher3
    Running setup.py install for sqlcipher3 ... done
Successfully installed sqlcipher3

is there any other way to use SQLCipher in Python?

I found pysqlcipher3 on PyPi too but it seems like the library is deprecated

[naive sandal]

I'm creating an RPG, how should I store player inventories? A column per item type?

I need to store the Player ID, ItemID, and quantity. I have another table to store Item IDs and the attributes of that item.

[proven arrow]

@naive sandal What do you mean by column per item type?
If you already have a table that stores items and the attributes shared between those items, then in your inventory table you can have the columns you listed, where PlayerID and ItemID are both foreign keys. You would also need an extra column for the primary key, or you could make the primary key from (PlayerID, ItemID) assuming a player can only have one of each item.

[naive sandal]

Thanks!

[serene peak]

Which performs better and why?

For example assume that below are the columns in a table
created_date, modified_date and group.

Now in my program at various stages I need unique created_date values and 'group' values.

To write good code, should I write 3 queries i.e. to fetch unique dates, to fetch unique groups and to fetch all the values

Or
Fetch all the values at first and then do the required operations on the result to get unique dates and unique groups.

At present data isn't huge but it might reach 100k plus in the future.

I'm fetching data from MySQL db from python

[hasty juniper]

how can i fix

[velvet coyote]

Does asyncpg support dict?

[dawn rain]

Hi, is it possible to limit making objects in database? Django, for example I made endpoint for creating a tickets, how to implement that it will be creat exactly 1000 tickets ? And no more ?

[proven arrow]

@dawn rain You can create a trigger on the database, however why not handle this on your application side?

[dawn rain]

I want on app, but is it possible? If I make transaction with count rows and creating objects, it will prevent from making to much tickets?

What happened if two users wants to buy 3 tickets and tickets and the same time a count will return 995?

[proven arrow]

@serene peak It depends how your data is stored, and other factors as well. I would recommend to do so in a single query, and let the database handle the processing if possible. Mainly because your making less trips to the database, in total will be reading less records,

[scenic pasture]

Hey everyone just a question regarding SQLite3 DBs in python. I am new to sqlite and im trying to set up a database and I want to store a genre list [horror,action,comedy] in a column and Ive read that I should use a 'one-to-many' relationship where I have a separate table for my genres where i can associate many different genres with the PRIMARYKEY in my main table. That seems sensible to me but i was wondering if this is the general approach or there is something i am missing? I've read about storing the list as a JSON object but then that messes with normalisation and i want the data to be relational without having to parse it on the python end. Thanks in advance

[torn sphinx]

@scenic pasture this is normal is called relationships.

But what is the relationship you want to define?

One movie can have one genre or many?

[scenic pasture]

many genres

[torn sphinx]

Ok then you need many to many relationship

Because movie can have many genre, and genre can have many movies

understand?

[scenic pasture]

ah i hadnt even thought about it that way

the same would apply for directors and actors too

[torn sphinx]

yes probably because they can also happen the same

[scenic pasture]

lets say for argument that i simple associate each film row with a second table that contains the different genres for that specific film so its just 1 to many. can i still query the database to find 'all films with genre x' or would i have to go for many to many to make that work?

if im explaining it correctly

[torn sphinx]

you can still do this but then for example one movie cant appear in multiple genres this way

because you only give 1 relationship

[scenic pasture]

hmm i will have to see how complicated many-to-many implementation is and then decide from there

thank you for your help

[torn sphinx]

No problem

[shell ocean]

hmm i will have to see how complicated many-to-many implementation is and then decide from there
@scenic pasture you need a 3rd table

so one with movies, one with genres, and one with movie ID and genre ID as foreign keys

that's basically it

[oblique walrus]

Hello, I hope this is the right place to ask this question (regarding querying a Firebase Firestore database):

I am currently working on a Python project that needs to query my Firebase Firestore database using the 'array_contains' operator. I have been able to query the database successfully using the '==' operator, but not 'array_contains'. Here is an example of what a document looks like in the collection I am querying:

size: 'Large'

colour: ["Yellow", "Green", "Purple", "Orange", "Blue"]

made_in: 'USA'

I am able to query the collection successfully like this:

docs = db.collection('product_info').where('size', '==', 'Large').stream()

for doc in docs:

print(doc.to_dict())

But I get nothing printed in the console when I query like this:

docs = db.collection('product_info).where('colour', 'array_contains', 'Green').stream()

for doc in docs:

print(doc.to_dict())

I have looked over the documentation, and can't seem to find what I am doing wrong here. Any help would be greatly appreciated!

[near anchor]

I got a flask sqlalchemy form where I want to save a file, and associate that file with a number of persons. When I add the file to the session and try to retrieve the id of it, I only get None. Can I get the id before commiting somehow, or do I need to call it again to retrieve the id?

    if form.validate_on_submit():
        if form.archive_file.data:
            saved_file = save_file(form.archive_file.data)
        upload = File(
                        title=form.title.data,
                        description=form.description.data,
                        date=form.date.data,
                        source=form.source.data,
                        archive_file=saved_file,
                        uploader=current_user)
        db.session.add(upload)
        #for value in form.person_ids.data:
        #    archive_entry(db, person_id=value, file_id=upload.id)
        db.session.commit()
        print(upload.id)

[dense lynx]

i am getting this error

could anyone tell me whats wrong with my code

[proven arrow]

@dense lynx Try passing your arguments as a tuple

[lavish oriole]

how can I write mongod query in while loop, for example
I am trying to implement this

A related to B
B related to C
C related to D

and user enters A and D

then my program will output something like A>B>C>D

I have db str with fields "main", "relation", "second"
so when user enters a name (A) it will find in which main it is and then query it's second in loop till we able to find D

[quartz lynx]

        updaterScoren = "UPDATE bois SET pinkode_brugt = pinkode_brugt +1, score = %d WHERE id = %s" %(score, f)

        myresult = mycursor.fetchall()

        row_count = mycursor.rowcount
        print(myresult)
        print("number of affected rows: {}".format(row_count))
        if row_count == 1:

            print(row_count)
            print(app.timerValue)
            while (i != app.timerValue) and (i <= app.timerValue) and (doorButton != 1) and (app.stopButton != 1):
                print('%.2f' % i)
                i = i + 0.01
                timeLeft = app.timerValue - i
                iTwo = app.timerValue
                print("THIS IS X", app.timerValue)
                time.sleep(0.01)
            # Hvis spillet stoppes, or i ikke når timerens slutværdi, vil scoren gemmes
            if (i != app.timerValue):
                score = i
                print("Dette er dit resultat: {}".format('%.2f' %score))
                mycursor.execute(updaterScoren)
                

            else:
                print("Ingen point")
                mycursor.execute(updaterScoren)


        i = 0
        mydb.commit()```
Does anyone know, why score isn't added to the selected row's score column, and why the "Pinkode_Brugt"-variable doesn't increment?

Please @ at me, if you figure out a solution 🙂

[glass gorge]

best way to export sql data as xls?

flask-excel? pandas? can't SQL do this also?

[shell ocean]

flask-excel? pandas? can't SQL do this also?
@glass gorge not to Excel

but CSV is fine, I think

[glass gorge]

what do you mean csv is fine

@shell ocean

[pseudo cove]

you can convert csv to excel anyways

[high geyser]

So I am creating a RESTful api for my postgres database

for that I have a python client library

I have explained my issue here

https://github.com/Kong/py-postgrest/issues/7

pls ping me when help

thanks!

[fallow blaze]

Where would be a good place to ask about postgres stuff? I doing geospatial stuff and have an array of IDs made from array_agg that I want to unwrap to fill 1-3 columns depending on its length

[shell ocean]

Where would be a good place to ask about postgres stuff? I doing geospatial stuff and have an array of IDs made from array_agg that I want to unwrap to fill 1-3 columns depending on its length
@fallow blaze here I guess?

but that sounds a bit advanced TBH so not sure how many people will be able to help

[fallow blaze]

Hah, I figured it out—this is day 1 of using postgre (& like day 4 of sql in general) so I think I prob just phrased it poorly rather than it being hugely complex

Because I was just working in cli before using sqlite my brain keeps forgetting I can write multiple consecutive sql calls and not do everything in 1 behemoth heh

So I just decided to dump it in a json column and then extract the elements in a separate call

[potent cloud]

How would i get the position of someone in a descending list?

like

SELECT userName, Kills FROM players ORDER BY Kills DESC LIMIT 15;

How would i get the position in this array for a specific username?

[formal cosmos]

https://stackoverflow.com/questions/13550750/how-to-add-row-index-as-a-column-to-sql-select-query/13550826

I did a google search

[wintry stream]

@potent cloud depending on your driver, i would expect the returning values to be a list, so you can grab the index of the list i would assume

[potent cloud]

@potent cloud depending on your driver, i would expect the returning values to be a list, so you can grab the index of the list i would assume
@wintry stream Yeah but the list is about 500k entries

Would rather just get the index directly somehow?

[wintry stream]

ah yeah

[potent cloud]

https://stackoverflow.com/questions/13550750/how-to-add-row-index-as-a-column-to-sql-select-query/13550826
@formal cosmos Gonna try this, thank you

[wintry stream]

what database are you using?

[potent cloud]

MySQLi

[tidal turtle]

hello, small question. Which datase is the best to use in 2020? I am now using MySQL. But is there anything better or faster? If yes, which one and should i upgrade? Tag me if you respond.

[compact silo]

@tidal turtle I'm currently using PostgreSQL and MSSQL17

For the faster performance, I'm not sure abt it.

https://www.trustradius.com/relational-databases

this might help you making a decision tho

[torn sphinx]

oh...

ok

ok

i need help

I'm doing a programming and in it resultset last() is not working

I don't know why

[pseudo cove]

post code

[torn sphinx]

my program stop here

[pseudo cove]

bruh

thats java

this is a python server

[torn sphinx]

okay

[earnest parcel]

Lol...

[potent plinth]

Hey everyone, I'm need some help setting my db relationships in flask-sqlalchemy. I have a UserModel and a ExperienceMode. a user can have multiple experiences.
ex. user1 can have experience1(languare="java", level="0") and experience2(language="python", level="3").

I want to set it up so that ExperienceModel is made up of two composite primary keys: user_id and language.
This is what I have so far..

I think i need to setup foreignKeyconstraints? but im not sure on the syntax

[compact silo]

how about db.ForeignKey('some attribute here')

@potent plinth

https://flask-sqlalchemy.palletsprojects.com/en/2.x/models/

maybe

[potent plinth]

yea thats what i originally had in my experiencemodel commented out. but my team lead wants user_id and language to be a composite key, i think the purpose is so usermodel cant have multiple experiences of the same language

[compact silo]

oh no

i have no idea about this

How about adding ```python
UniqueConstraint("user-id","language")

[potent plinth]

thanks, i added uniqueconstraint to tableargs and gave the intended result

[inner apex]

Hey! I'm looking for some good way of storing a livestream analysis in a database rather than storing the images themselves using cv2. Does anyone mind telling me how I can store the numpy array as a single insert? I understand postgresql may not be the best for this

What I've done so far is breaking up the image into single arrays of 3 values, then storing those, but it's typically 600 to 1000 inserts per image. Looks messy

[toxic ridge]

Am I allowed to ask for questions regarding Microsoft Access here?

Or if anyone knows about a Discord where I can ask, it's really simple questions.

[minor ruin]

I mean, here would be best place but I doubt you will find a ton of people using Microsoft Access, I imagine SQLite is preferred local file database

[potent sparrow]

With SQL, what do you use to read it? I was going to try JetBrains, but , is there a good reader, something that you can look at a database by itself? And add to it?

[pseudo cove]

which dbms?

@potent sparrow

[potent sparrow]

@pseudo cove what? I’m using Sqlite with Django

[pseudo cove]

sqlitebrowser?

[upper cloak]

Anyone here using digitalocean free $100?

[inland stone]

SELECT 
    testi.`pos`.`POID` AS `POID`,
    testi.`pos`.`SubtotalOrigCurr` AS `SubtotalOrigCurr`,
    testi.`pos`.`Currency` AS `Currency`,
    testi.`pos`.`IDAtCustomer` AS `IDAtCustomer`,
    testi.`pos`.`NameAtCustomer` AS `NameAtCustomer`
FROM
    testi.`pos`
WHERE
    ((testi.`pos`.`NameAtCustomer` IN (SELECT 
            testi.`pos`.`NameAtCustomer`
        FROM
            testi.`pos`)
        OR testi.`pos`.`NameAtCustomer` IN (SELECT 
            testi.`jobs2`.`NameAtCustomer`
        FROM
            testi.`jobs2`))
        AND testi.`pos`.`NameAtCustomer` IN (SELECT 
            testi.`Invoices`.`NameAtCustomer`
        FROM
            testi.`Invoices`)
        IS FALSE);

How can I add a column to the select from another table here? e.g. testi.jobs2.NameAtCustomer

[obtuse iron]

@inland stone use a join in your sql query, then you can use another table in your select statement see: https://www.w3schools.com/sql/sql_join.asp

[velvet coyote]

What's The difference between psycopg2 and asyncpg?

[pseudo cove]

asyncpg is async

psycopg2 is sync

asyncpg is faster than psycopg2

[velvet coyote]

i see

[toxic ridge]

Are these tables 3NF? Sorry in advance if I cannot ask these type of questions here.

[old raven]

is anyone here experienced with motor?

I'm trying to convert my code from pymongo to motor and idek where to start

[forest hinge]

Hey guys

Anybody here used SQLAlchemy?

[lime echo]

I am finding some troubles importing my postgres database to Heroku.
first of all, I dump the file by using pg_dump -Fc --no-acl --no-owner -h localhost -U myuser mydb > mydb.dump then I upload that file to a public URL website then when I try to do pg:backups:restore '<SIGNED URL>' DATABASE_URL it does not work saying that pg:backup restore couldn't read the file.
pg_restore: error, did not find magic string in file header is what I am getting. And I used https://gofile.io/ to upload my .dump file to get a public HTTP accessible URL.

[gaunt garden]

When i call data from a database like sqlite3 what do you put in the index brackets? do you put the order of the data you called or the order of the columns in the table, like:

db = sqlite3.connection('mydb.sqlite')
cursor = db.cursor()
cursor.execute(f"SELECT user, reason, moderator FROM warningcache WHERE guild_id = {ctx.author.id}")
#do i get the int from the cursor.execute? ex: user = 0, reason = 1, mod = 2. or from the table?
users = cursor.fetchall()[which-do-i-put-here]

Any info will be greatly appreciated

[warped crane]

Index should be in exact order of query to get full benefit.

If multi column index

[gaunt garden]

that's what i thought but i keep getting "IndexError: List index is out of range"

[warped crane]

Wait you are talking about list index?

[gaunt garden]

even though i put users = cursor.fetchall()[0]

[warped crane]

Fetchall returns list if there isn't any data it would be empty

[gaunt garden]

there is data in the table if thats what you mean.

[warped crane]

Data satisfying your query?

Dry run your query directly on dstabase using something like sqlite browser

[gaunt garden]

alright hold on a sec ill run it

alright so i ran this in the browser SELECT user, reason, moderator FROM warningcache and it returned the exact data i wanted it to

[visual meadow]

Did you but the where statement in?

[warped crane]

First of all, don't use f-string here. It:s bad security practice. Use. DB-api syntax.

[visual meadow]

Put*

[warped crane]

Are you getting expected guide_id in the table

Yes ofcourse problem is with f-string

[gaunt garden]

there is only one guild_id i can add an entry with a different one and try it

[warped crane]

cursor.execute("SELECT user, reason, moderator FROM warningcache WHERE guild_id = ?", Id)

[gaunt garden]

can i do something like Id = ctx.author.id before i run it. the bot will be in multiple servers so need it to check against guild_ids

[warped crane]

@gaunt garden that's not the problem. Always use db-api syntax it will take care of datatypes

@gaunt garden yes Id can be any variable

[gaunt garden]

okay thanks

[warped crane]

U see for string it should be where name='bob' in sql but when someone uses name= "bob"and f"where name={name}" then you won't get single quoted string like you need for SQL.

[gaunt garden]

makes sense.

[warped crane]

You can fix it easily but why do that, db-api syntax are better and they also prevent sql injection

[gaunt garden]

alright i changed it to this
cursor.execute("SELECT user, reason, moderator FROM warningcache WHERE guild_id = ?", ids)
now im getting an error
ValueError: Parameters are of unsupported type

[warped crane]

What's type of ids?

[proven arrow]

Parameters should be passed as a tuple

[gaunt garden]

yeah i didnt do that 🤦

[warped crane]

Oh yeah forget to tell that

[gaunt garden]

alright fixed that, im still getting list index is out of range though. idk excuse my crappy code, i have literally been trying anything i can think of for two days now 😂

async def allwarns(self, ctx):
        ids = [str(ctx.author.guild.id)]
        db = sqlite3.connect('warningsys.sqlite')
        cursor = db.cursor()
        cursor.execute("SELECT user, reason, moderator FROM warningcache WHERE guild_id = ?", ids)
        users = cursor.fetchall()[0]
        reasons = cursor.fetchall()[1]
        moderators = cursor.fetchall()[2]
        results = []
        for users in users and reasons in reasons and moderators in moderators:
            results.append(f"{users} | {reasons} | {moderators}")

[timid plank]

Does it work now?

[gaunt garden]

no xD

[proven arrow]

@gaunt garden You can't fetch from a cursor twice like that. And what is the state of that for loop.

You fetch once then there is nothing left to fetch hence the error

[gaunt garden]

ohhhh

[timid plank]

Woops XD

[proven arrow]

Instead you should do
result = cursor.fetchall()
Which stores the result in result

Then get the values of what you want from that

And that for loop is right off, so that's another issue.

[gaunt garden]

the for loop was my attempt to get an output formatted to be posted in an embed on discord like:

user | reason | moderator
us1  | noob   | Moros

[proven arrow]

No. A single execute function.

Your execute will return a result which as I showed above you can store in a variable.

[gaunt garden]

yeah i got you know, it will definitely work now that we solved that issue with the guild_id

Your execute will return a result which as I showed above you can store in a variable.
i can call the values by result[0] or result[1] right?

[proven arrow]

Also your code is in an async function, so try and use an async library for the database also, if your application is async.

[gaunt garden]

ahhh okay

[proven arrow]

i can call the values by result[0] or result[1] right?
@gaunt garden Well that depends on what you are expecting the database to return.

Fetchall() returns all records that match that

So what you have there will return a list of tuples where each tuple is a record

[gaunt garden]

the return as tested is ('user', 'reason', 'moderator')

[proven arrow]

I meant fetchall() returns in the following format: [(user, reason, mod)] so you would have to index like result[0][0] to get the user value.

If you want it to just return the first matching value then you can use the fetchone() method which will just return you a single tuple and so then you can do result[0]

The methods do what they mean. Pretty self explanatory.

If that makes sense?

[gaunt garden]

yeah it does, thanks a bunch for your help!

[crimson glacier]

Hey, does anyone have experience with MongoDB dashboards?

[versed sluice]

Can one do the .paginate function belonging to Basequery class when using postgres with sqlalchemy?

[potent sparrow]

What is best to use to read a Database? I’m using SQLite3, in VSC

[loud wharf]

Was there some major breaking change in MySQL 8.0.22 that has decided to just roll over and hate sqlalchemy?

[worn nimbus]

Hello, i'm relative new to databases and i'm trying to build up a small chat app. I came up with this database design where chats belong to both users and messages are associated to a chat, my doubts are if is it a good approach? any cons of doing it this way?

constant: a chat will always have only two users.

Many thanks in advance!

[south cobalt]

whats the best way to get data from an SQL db into excel via python

i would like to use pandas but i don't really know how to go about it

[shell ocean]

i would like to use pandas but i don't really know how to go about it
@south cobalt look up read_sql from pandas

[south cobalt]

i am trying that right now can I paste what i have here?

def ExcelWriter(self):
        script = """SELECT * FROM [FIC-100]"""
        df = pd.read_sql_query(script, self.cnxn)
        return df

i think this should work

[south cobalt]

hey @shell ocean would you hapen to have any idea if read_sql can handle multiple queries i need to read from multiple tables

[visual sail]

I am a complete novice, I know that databases store data in tables in the same manner as xlsx files, but is there any file type for them?

[lime echo]

Is pushing data from local database to heroku and using pg:backups:restore the same?

@visual sail for the data or for the database?
for the data? yes. You can use int or str or blob for a global type.

[visual sail]

for the whole database

[lime echo]

I am not sure but it should have something like .sql or .sqlite etc.

[velvet coyote]

    conn = await asyncpg.connect('postgresql://postgres@localhost/test')

is this how we connect in asyncpg?

[proven arrow]

Is pushing data from local database to heroku and using pg:backups:restore the same?
@lime echo Pg backups, is to restore dumps from other sources to your heroku. https://devcenter.heroku.com/articles/heroku-postgres-import-export#import

@velvet coyote That is one way to do so yes.

[velvet coyote]

ohh but what is the link as the argument??

[proven arrow]

What do you mean?

[velvet coyote]

postgresql://postgres@localhost/testWhat's this thing?

[proven arrow]

In what you sent the database is called "test", user is called "postgres" and you are connecting to localhost.

[lime echo]

I read that aritcle btw.

@proven arrow when using PGUSER=postgres PGPASSWORD=password heroku pg:pull HEROKU_POSTGRESQL_MAGENTA mylocaldb --app sushi it says that PGUSER is not a command.

[velvet coyote]

oh but i don't have database set u at all, i just got that from the docs?
How do u acutally set up the database?

[proven arrow]

Do you have postgres installed?

[lime echo]

I do.

[velvet coyote]

ahhhhhh, no. I thought that you could do it with python only like how you could do it with
sqlite

[lime echo]

heroku pg:pull HEROKU_POSTGRESQL_MAGENTA mylocaldb --app sushi works better, but it logins in with an incorrect user. @proven arrow

[proven arrow]

@velvet coyote sqlite is a file based database, and python supports it out the box whereas postgres is server based

[velvet coyote]

oh

So I would also need postgres installed and I would have to setup the database there, and my python code would connect to that database?
Is that how it works?

[lime echo]

Yes.

[velvet coyote]

ohhh

ok

So i guess I will do that then

[lime echo]

@proven arrow do you have any idea on how to login by using heroku pg:pull HEROKU_POSTGRESQL_MAGENTA mylocaldb --app sushi

alright, @velvet coyote .

[proven arrow]

I never have used heroku, so I am unsure how any of it works.

Did you copy that example from somewhere? If so you would have to replace certain values with your own

[lime echo]

Yeah I replaced it but it still don't work.

It's a database login issue rather than a Heroku one.

I believe that I know how to solve it.
How do I change the default user of my postgres login system? @proven arrow

[lapis oriole]

Okay guys, I need help with PostgreSQL :
I want to log some usage stats, and I'd like to group the records by time, let's say 15 minutes = 1 record.
Currently, I only have a timestamp column that defaults to NOW(), and I'd like to check if there exists a record less than 15 minutes old to update it, and if not create a new one. But I don't know how to check that

[lapis oriole]

Okay, I found how to get the number of seconds in that : EXTRACT(EPOCH FROM (NOW() - timestamp))

[ionic marsh]

Are concatenate strings safe to use in SQL queries? Ik formatted strings are not

[proven arrow]

No, unless you are thoroughly validating the input @ionic marsh

You should always try and use parameterized query where possible.

[ionic marsh]

Ok. What about the value of an f string? With the function I have, I have an input for the table needed

SELECT * FROM x

Could I use a saved value from an f string? Or is the variable of that value a formatted string as well

query = f”SELECT * FROM {table_name}”
.execute(query)

I assume that’s not good either

@proven arrow

[proven arrow]

The same for that too. You should avoid having dynamic table name queries.

You can always have different codeflow or separate functions for for the different possible table names. If you really really want to go the f string way then validate and clean the input before passing it in, and check if that table actually is a valid table in your DB. @ionic marsh

[ionic marsh]

How would I go about cleaning it? Link to the docs?

[torn sphinx]

Anyone knows about PyMongo here?

I need a bit of an introduction to it.

I'm using Flask + PyMongo to create a RESTful api

[bleak crown]

I'm making a queue system for a discord server and the question i'm asking is, i will use sqlite3 and the queue system will take the first 5 queuer and match them, then refresh itself so should i use a normal list and when i need to save somethings, use sqlite3 or should i use sqlite3 all the time? Can sqlite3 be fast or will it consume extra thing? Like memory or etc.

[proven arrow]

@bleak crown How often will you be reading/writing to the database?

[bleak crown]

Probably a lot

[proven arrow]

Well sqlite is file based so it's less expensive than a server based DB where you have to go through the network so it should be pretty quick. However if it's a lot then in that case you can always maintain a in memory cache like data store and read write to the database in batches.

[bleak crown]

I mean, it is a queue system for x game and the discord bot will (probably) write and read a lot, i can make it with normal list structure but if i reopen the bot, the queue list will be empty so i dont want it but i dont want it to be expensive or too slow

And i have no clue is sqlite fast or not

[long oriole]

im using asyncpg

[proven arrow]

It is pretty fast however it can depend on how many writers you have. Because it doesn't allow for concurrent writers.

[long oriole]

i wanna check if the author of the message is the leader_id or the mate_id

[bleak crown]

Hmm, would you recommend me anything? Or should i just use it

[proven arrow]

This should help you decide. Most important thing I would say is whatever db you do pick, then ensure that you use a async module for it, seen as your coding a discord application which would be based around async code. https://www.sqlite.org/whentouse.html

[bleak crown]

Okay, thank you ❤️ so i should probably learn aiosqlite

[proven arrow]

There is not much to learn, just that you have to "await" I/O calls, but the general syntax is the same.

[bleak crown]

(Probably) thank you sir ❤️

Have a nice day

[proven arrow]

@long oriole You would have to query that row first to get the data. Then use python to check if your value is in that returned result.

[rough hearth]

This works

select department_id, department_name from departments;

This doesn't

select department_id, department_name, avg_salary
from departments join (select department_id, avg(salary) as avg_salary from employees
group by department_id) dept_to_salary
on departments.department_id = dept_to_salary.department_id;

with error message

At line 1```

I guess department id appears more than once in departments?

actually I don't think it does.

[young seal]

What's the best way to use a windows credential in a pyodbc connection string for uid and password?

Please dm me, looking to avoid using encryption libs while staying relatively secure

[gaunt garden]

Does this look correct for deleting data from a specific row in a sqlite database? I read documentation but was alittle fuzzy on understanding.

@bot.event
async def on_guild_remove(guild):
    gildid = guild.id
    db = sqlite3.connect('guilds_config.sqlite')
    cursor = db.cursor()
    cursor.execute("DELETE * FROM guild_config WHERE guild_id = ?", gildid)
    db.commit()
    cursor.close()
    db.close()

[young seal]

Enclose it in a try block so you don't leave connections open using conn.rollback()

[gaunt garden]

alright

thanks

[young seal]

There's some stuff on how to do that online, overlooked imo

[gaunt garden]

yeah, i watched a recorded course online about sqlite but in the entire video they didn't mention about deleting only inserting and fetching. or even anything about a try block for that matter.

Updated my code with the try block thanks! sorry i can't help with your issue, thats a bit over my head right now. 😅

[young seal]

I would check out various SQL resources on how to delete rows from tables, it should be straightforward.

[proven arrow]

@gaunt garden When you do DELETE you cant do specify * since it deletes the row anyways by default. So just remove the asterisk from the query, and your parameters should be passed as a tuple. Like below:

cursor.execute("DELETE FROM guild_config WHERE guild_id = ?", (gildid,))

[young seal]

@rough hearth is departments_to_id defined?

Nvm I see it

[rough hearth]

@young seal department_id is the primary key for departments and a foreign key in employees

[proven arrow]

Yeah so it's ambiguous

You would have to say which table to get it from

As it won't know

[young seal]

Also check the parentheses

[rough hearth]

I see

let me try that

[young seal]

Also, try it without the semi colon at the end depending on ur dbms..

[burnt turret]

How do I use JSON types with Django's ORM (MySQL)?
When I searched online it told me to use something called django_mysql, which in its requirements said it needs mysqlclient v 1.3/1.4, but when I use that version of mysqlclient, I get a big error (switching back to the newer version resolves this). What do I do? (please ping me on response)

[rough hearth]

this works

select departments.department_id, departments.department_name, avg_salary
from departments join (select department_id, avg(salary) as avg_salary from employees
group by employees.department_id) dept_to_salary
on departments.department_id = dept_to_salary.department_id;

Thanks @proven arrow @young seal !

pretty sure it's correct because there's a huge disparity between the executive department and the shipping department. This is why we need a communist revolution.

[rough hearth]

I might be misunderstanding how natural join works

select courseName, studentName, deptName, gpa
from student natural join major natural join enroll natural join course;

I expect this query to have as many rows as there are course enrollments (so a few per student), but some students don't show up at all.

[gaunt garden]

When you do DELETE you cant do specify * since it deletes the row anyways by default. So just remove the asterisk from the query, and your parameters should be passed as a tuple. Like below:

cursor.execute("DELETE FROM guild_config WHERE guild_id = ?", (gildid,))

@proven arrow Thanks, I'll update it

[rough hearth]

Looks like what I wasn't understanding is how natural join accounts for duplicates.

[torn sphinx]

hey got a simple mysql question,

trying to do ```sql
select * from question where subjects='1';

but instead of say '1', i'd like to say 'math' because I have a foreign key for it

create table _subject(
    id varchar(64) primary key,
    subject_name varchar(128) not null
);

subjects references _subject

how can I do this query? I'm new to sql.

[hidden osprey]

yoo im haveing a a prob with my sql

code

comm_message = await commission_channel.send(embed=ce)
        db = await aiosqlite.connect('utils/database.db')
        cur = await db.cursor()
        await cur.execute('INSERT INTO comm_tickets(c_message) VALUES(?)', (int(comm_message.id)))```
error
```bash

discord.ext.commands.errors.CommandInvokeError: Command raised an exception: ValueError: parameters are of unsupported type```

[young seal]

@torn sphinx just type in math instead of 1

Join the primary key from questions to the foriegn key in subject table. Then do where subjecttable.subject = math

[torn sphinx]

figured it out. thanks

[proven arrow]

@hidden osprey Parameters for your query should be passed as a tuple. At the moment your code is being evaluated as cursor.execute(sql, some_integer_value), where the second argument is just an integer.

(1234) # is an integer
(1234,) # is a tuple

Note how the comma converts it to a tuple.

[torn sphinx]

@shell ocean continuing the discussion

PyInstaller fails to pack asyncpg

Any ideas?

The asyncpg.pgproto.pgproto import doesn't work

[burnt turret]

How do I use JSON types with Django's ORM (MySQL)?
When I searched online it told me to use something called django_mysql, which in its requirements said it needs mysqlclient v 1.3/1.4, but when I use that version of mysqlclient, django raises an error (switching back to the newer version resolves this). What do I do? (please ping me on response)

[nocturne basin]

Hey! Which would you rather prefer, an insert to a dB using one large DataFrame (500,000 rows), or the equivalent stored in a dictionary of DataFrames?

Just to be clear, I'm referring to how the data is organized in a function before pushed into a database.

[tepid cradle]

@nocturne basin A list of tuples is probably the best. However, I've found that if you insert from a pandas dataframe using method=multi argument, the speed is quite good and unless you have millions of rows or hundreds of inserts per second, it should work fine (pgsql database).

@burnt turret I know this is not an answer to your question, but why do you want to use JSON type?

[nocturne basin]

@tepid cradle thanks for the info!

[tepid cradle]

@nocturne basin just to add, using Pandas is fine if you're working on data analytics or a hobby project, might not be a good idea if you're building a web application to be deployed in production.

[nocturne basin]

@tepid cradle Im actually using it for production

When I update the dB, about once every day

via a script

I think that would be fine?

but I welcome your thoughts

[tepid cradle]

Oh, it should be fine for that kind of usage. What I meant was it's not fine if the update is taking place via a user action, like adding a post or a comment, because that would be too frequent.

[nocturne basin]

Yeah I figured that's what you were implying

I will be using a PostgREST api

for users to pull data from

haven't used it before so it should be fun.

http://postgrest.org/en/v7.0.0/

[burnt turret]

@tepid cradle I wanted to store some lists

[tepid cradle]

@burnt turret can you normalise it into tabular form?
Using a relational Db and then storing json defeats the purpose of relational Db. You can't use joins with list items. You should store it in a separate table with a foreign-key relationship with the main table.
Here is an example. Instead of this:

Table - users
user_id  name    friends
1        gkrou   [a, b, c, d]

You can do this:

Table - users
user_id  name
1        gkrou

table - friends
user_id  friends
1        a
1        b
1        c
1        d

[burnt turret]

Yeah, but the lists get nested, sometimes a lot :/ and I only need the entire list at once, I won't need joins or anything

[proven arrow]

Is it a list or json as you said previously?

Either way if you don't want to do what gkrou said then you can always serialize and deserialize the object, and store it as a string.

[burnt turret]

Yeah I've decided to do that (storing as string)

Thanks!

[torn sphinx]

Help

what should I type in?

[proven arrow]

You can leave it as that. That is the default port postgres uses

[torn sphinx]

ah Nice

thanks

[bleak crown]

Hi i decided to switch to the aiosqlite from sqlite, because i will use sqlite in a discord bot. So i did this but it seems i'm having problem

But on the pypi page, it seems doable

if i'm not an completely idiot and missed somethings

[proven arrow]

That example just shows you how it can be used. But your error is the basic async/await syntax. It's because you can only "await" something inside an async function.

[bleak crown]

Yeah, i know but

I didnt figure out the correct way, i mean

I thought using it in a function or etc.

But i'm not sure :S

Actually i am not sure is it a database question or async question

[proven arrow]

The await expression can only be used in an async def function, yours is outside.

async def example():
    await ....

That's how it should be used.

[bleak crown]

Hmm, so will i define the connection all the time again?

Or will it work after defining in one func?

because i'm creating the tables like this and i'm planning to read and write from these tables in an async action, like a discord command

(my database structure might be bad, dont judge me pls ;_; )

[proven arrow]

You could just put all those in a single function, and connect inside the same function as well.

[bleak crown]

Hmm, you are right sir

How can i be this fool 🤔

Thank you ❤️

How can i be this fool 🤔
@bleak crown It is indeed weird, probably i have a lot of things in my mind and cant think about general programming 😅

Btw probably it is a silly question but

Should i make global the connection and the cursor? 😄

Or i saw this and should i use this

Neither using the sqlite way

Oh nvm i will figure it out but anyway

Thanks for the help ❤️ @proven arrow

I will global the connection and cursor variable, what can possibly go wrong

[bleak crown]

In the doc it seems i can do cursor = connection.cursor()

https://aiosqlite.omnilib.dev/en/stable/api.html#connection

And after that it seems i can do await cursor.execute() but it gave me this error

[proven arrow]

@bleak crown cursor() is an async function so you have to get it by awaiting.

cursor = await connection.cursor()

And you don't need to have a global cursor

[bleak crown]

Uh okay

Thank you ❤️

[wintry stream]

@bleak crown Cursor is a class, you forgot UpperCase afaik

also often to make it shorter people use con for connection and cur for cursor

[bleak crown]

I know but it is just my prefer :S

Also it worked and in the doc i remember it was cursor

Without uppercase

[tepid cradle]

@wintry stream here cursor is not a class, it's an object of the class cursor. Therefore it's a variable name, hence it should be lower case.
You use upper case when defining a class.

[wintry stream]

ah i guess that's the way that aiosqlite does it then

some drivers make cursor a class and thus it needs to be uppercase

[proven arrow]

@wintry stream Ive not seen any library require the cursor to be called directly as a class, since it's supposed to be retrieved from the connection as an cursor object, as specified in the python DB API. https://www.python.org/dev/peps/pep-0249/#cursor

[wintry stream]

oh i might be thinking completely wrong then

mb and sorry for the confusion

[vital belfry]

hey

[tepid cradle]

@torn sphinx It's not a simple thing to do. You can define triggers in the database. I don't know which all database support triggers, but I've used PostgreSQL and that supports it.
But afaik, triggers need to be defined separately for each table

[sterile sleet]

https://stackoverflow.com/questions/64620542/typeerror-not-all-arguments-converted-during-string-formatting-pymysql

can anyone help? its pretty simple i think, wont take more than a minute of your time

[lime echo]

What is the equivalent of BLOB data type in psyco2, is it ByTea?

[mint jackal]

Hey guys I’m fairly new to databases any tutorials to learn them in python?

[minor flume]

Matters by what database you want to use, mongodb is quite easy to learn. Also learn how Json works and how to write data from and to json

[late lake]

Can someone help me to insert data into first row? #help-pineapple (in sqlite3)

[proven arrow]

@late lake Regarding your question you cant just rewrite a row (without updating, or deleting it and making a new row). However instead you can update the values of that row for its columns.

Look into the sql UPDATE statement

[late lake]

Finished 🙂

@proven arrow is there a way to make it so the database is stored inside the program or something so that there isnt like a settings.db file and that saved info?

[proven arrow]

No databases are an external system. Unless you want to make your own datastore, but even then you would still have to store the info somewhere like a file.

[late lake]

Oh okay

So it should be okay to just store it then

In the same folder lol

[proven arrow]

Yeah it's fine. Sqlite is a file based database so it would create the settings.db file. However there are also server based databases which would not store a file like that and you would connect to it over a network.

[late lake]

Oh cool

Can you name some good ones incase I want to switch in the future?

[proven arrow]

There's relational SQL ones like MySQL, MariaDB Postgres, and also some NoSQL ones like MongoDB

There are many more but you can Google them

[late lake]

Oh cool

@proven arrow I have a sqlite3 db with 1 row and I try to do cursor.fetchone() multiple times but it returns None after

Even though there is only 1 row and it has no other row to increment to

cursor.fetchall() does the same thing

[proven arrow]

@late lake can you show your code

[late lake]

Sure

db_conn = sqlite3.connect("settings.db")

        with db_conn:
            if db_conn is not None:
                db_cursor = db_conn.cursor()
                db_cursor.execute("""CREATE TABLE IF NOT EXISTS settings (
                                            rowid INTEGER PRIMARY KEY,
                                            url TEXT
                                    );""")
                
                db_cursor.execute("SELECT * FROM settings")
                if db_cursor.fetchone() == None:
                    db_cursor.execute("""INSERT INTO settings ('rowid', 'url')
                                            VALUES (1, :default_url)""",
                                        {
                                            "default_url": "https://"
                                        })
                db_conn.commit()
                return db_cursor.fetchone()

The formatting sucks but

This is the database

@proven arrow

[proven arrow]

I feel it's because you are doing fetchone() twice

Since after the first time you do it then it'll be none because you have already fetched

[late lake]

Yes

But after the first time I do it I am checking if the db is empty to create default values

And I need to get it second time to return the new db values

Would I have to reset the connection to get it again?

@proven arrow

[proven arrow]

Let me see hold on

   db_conn = sqlite3.connect("settings.db")

    with db_conn:
        if db_conn is not None:
            db_cursor = db_conn.cursor()
            db_cursor.execute("""CREATE TABLE IF NOT EXISTS settings (
                                                rowid INTEGER PRIMARY KEY,
                                                url TEXT
                                        );""")

            db_cursor.execute("SELECT * FROM settings")

            result = db_cursor.fetchone()

            if not result:
                db_cursor.execute("""INSERT INTO settings ('rowid', 'url')
                                                    VALUES (1, :default_url)""",
                                      {
                                          "default_url": "https://"
                                      })
                result = db_cursor.fetchone()
                db_conn.commit()

            return result

@late lake Try that. It's not perfect since I'm typing from my phone. But should work.

[late lake]

Ok

Still returns None

@proven arrow

[proven arrow]

Try adding print(result) after the first fetchone

[late lake]

It works

But I need it the 2nd time

[proven arrow]

Oh I see

It's because if it's none and you insert the record, however you don't select it again

[late lake]

I managed it get it work by returning the default data if there is no data

[proven arrow]

So after inserting you would have to do the select again

[late lake]

B

It's because if it's none and you insert the record, however you don't select it again
@proven arrow Ohhh would I need to select it again?

That is genius lol

[proven arrow]

So add this after the insert:

db_cursor.execute("SELECT * FROM settings")
db_conn.commit()
result = db_cursor.fetchone()

[late lake]

Yay

Works now

                db_cursor.execute("SELECT * FROM settings")

                if not db_cursor.fetchone():
                    db_cursor.execute("""INSERT INTO settings ('rowid', 'url')
                                            VALUES (1, :default_url)""",
                                        {
                                            "default_url": "https://"
                                        })
                
                db_cursor.execute("SELECT * FROM settings")
                db_conn.commit()
                return db_cursor.fetchone()

Thanks

Does SELECT * FROM settings* go to the start of settings table?

[proven arrow]

It selects all records from the settings table

So every row

[late lake]

Ok

And cursor starts at top right?

[proven arrow]

Yeah from the result set

But since you do fetchone() it will only get one record even if your query was to return multiple

[late lake]

Ok

Thanks

[tough schooner]

is there an elegant way to check if a database entry i want to make exists, and if not add it?

[proven arrow]

@tough schooner which database?

[tough schooner]

sorry, mysql

[brazen charm]

IF NOT EXISTS

is a pretty common method for doing that

[proven arrow]

Or INSERT IGNORE

If the record already exists then there won't be any error, instead just ignored.

[tough schooner]

thx

[proven arrow]

You can read more about it here. Towards the bottom of the page. https://dev.mysql.com/doc/refman/8.0/en/insert.html

[cosmic wave]

is it big difference between postgres and mongo ?

is there any async library ? 😛

[torn sphinx]

yes

for both of them

and a big difference

postgres is sql based and mongo is not

[cosmic wave]

for postgres

[torn sphinx]

what

[cosmic wave]

for both of them
@torn sphinx tell me for postgress 😄

[torn sphinx]

tell what

[cosmic wave]

is there any async library ? 😛
mudkip (gurkan)Today at 7:05 PM
yes
for both of them

[torn sphinx]

yes there is

[cosmic wave]

I am looking for async module to use postgres db

[torn sphinx]

for both

[cosmic wave]

ah

xd

[torn sphinx]

so you should find them

[cosmic wave]

is that russian one?

[torn sphinx]

asyncpg and motor

what?

what does russia have to do with postgresql

[cosmic wave]

nothing

[torn sphinx]

ok

[wet wraith]

is there some thing like update __ if not found, insert___?

[thorn crest]

I'm a newbie at django. Any advice on the database I should start with. I've been using sqllite but it's been condemned by my friends.

[tough schooner]

so i asked earlier about adding an entry only if it does not exist in SQL, but i can't get it to work. i googled a bit on the suggestions i got, and i think my problem is as follows: i need to check every single value in every single column, since i can have entries where only one column value is different. how do i do that if i have 8 columns? do i need to write a seperate check for each? isn't there an elegant command to have all 8 columns checked at once?

i think my issue is that i dont have a primary key

i should use a composite key made up of all my columns i guess

okay i added that composite key, now insert ignore actually works as expected.

[fiery cypress]

Hi so I'm using mongoDB and I have a database where the _id is its position within the collection so the first document is _id : 1, second is _id: 2 and so on.

Is there a way to replace a search_one result but not the _id?
So for example, my search_one resulted in:
{'_id': 1, 'Address': '12345', 'Zipcode': '12345', 'UserID': 88026239364788224}
Is there a way to grab that _id : 1 from the search_one result and overwrite the first document?

Basically how do I grab a key and its value from the dictionary of a search_one result

[burnt prism]

is anyone able to help in simple access query? i forgot the basics and trying to relearn some old stuff

such as "Find the average, maximum and minimum room price of each hotel. List the hotel name and the average price, maximum price and minimum price."

nvm i got it

[bleak crown]

In sqlite what will happen if i PRIMAR KEY two column, like cur.execute("CREATE TABLE IF NOT EXISTS mods(mod_id INTEGER, server_id INTEGER, PRIMARY KEY(mod_id, server_id))") will PRIMARY KEY accept something when both is unique? Or do i have to use unique for that (Also pls remind me how we were using unique 😄 )

[eternal raptor]

sorry if not related, but guys, i have a problem... I downloaded dbeaver and I have used postgresql. How to add primary key unique to column in database automatic, not manual?

and AUTO INCREMENT

[proven arrow]

For an auto increment Primary Key you can use the serial type .

@bleak crown What you have is known as a composite key, where 2 (can be more) columns are used to uniquely identify each row in that table.

Similar to when you would have a single PK, those 2 columns put together can only ever have a same combination once.

[eternal raptor]

How?

[bleak crown]

So i will just change primary key to COMPOSITE KEY(x, y) ? @proven arrow

[proven arrow]

No thats what is known as, but you don't manually specify it. What you had before was correct.

[bleak crown]

Oh hmm

[proven arrow]

@eternal raptor Take a look at this. It shows you how to use it. https://www.postgresqltutorial.com/postgresql-serial/

[eternal raptor]

thanks

[bleak crown]

Thank you for the information + thank you for the help sir, you are really helpful ❤️

So the thing i did was not wrong

Okkay :3

[eternal raptor]

<#

❤️

What is a difference between AUTO INCREMENT and PRIMARY KEY ?

[brazen charm]

Just because something is the primary key doesn't mean itll auto increment