databases

[dense loom]

It is

[wise goblet]

show it

[dense loom]

https://docs.djangoproject.com/en/3.2/ref/models/constraints/#uniqueconstraint

[wise goblet]

nah, that can be done easier

a moment

[dense loom]

No problem

[wise goblet]

it is a way too though

class Commodity(models.Model):
    """"Model for freelancer ingame regular commodities in the shop"""
    pod_appearance = CharField(unique=True)

something like this

more complex contraints are required only for a more advanced conditions

[dense loom]

Oh i see

So ```py
phone_number = PhoneNumberField(unique=True)

Like that

[wise goblet]

yup

[dense loom]

ok thanks, ill try that.

[wise goblet]

it will make error if you will try to insert duplicated data into database

which breaks this costraint

obviously, you will not be able to migrate your db to having this constraint, unless all your data is already unique btw

[half forum]

How do i use SQlite db for an application bot

eg

1-) Mod/Owner sets up the bot with the Guild Id and where he wants the applications sent.
2-) Owner/Mod creates application.
3-) A member of the guild applys for it (eg. ?apply Mod)
4-) The bot dm's him the questions the mod/owner made.
5-) He answers and submits them
6-) the answers are sent to a channel (step 1) with a new number (randomly generated but saved) for the Mods/Owners to review
7-) They can accept or deny the application
8-) Accepting will give them a role and send a message to the user, for denying, it just sends a message.

[hollow pewter]

Try converting time to seconds from some epoch. Then you are comparing two large integers.

[jagged nova]

does anyone know what this error is on pymongo

pymongo.errors.OperationFailure: Authentication failed., full error: {'ok': 0, 'errmsg': 'Authentication failed.', 'code': 8000, 'codeName': 'AtlasError'}

[potent shoal]

I have a question regarding switching from sqlite to postgreSQL. Currently I am using sqlite and open a connection to the database file everytime I get an API call

Is it okay to continue doing that, or is there a way of having a global database connection among all the threads running my flask api?

[keen gorge]

for user in users:
  try:
    cur.execute("INSERT INTO badges VALUE (%s, %s, %s)", (user.id, name, emote))
  except:
    users.remove(user)
    failed.append(user)```
I have this code and `badges` is a table with a unique key for `uid` and `name` (first and second column in the table), but for some reason the except only run one time and the other time its working fine but the value just isnt added

Can someone help me with that?

its like I break the for loop for some reason

[potent shoal]

I think it should be "VALUES", but I could be wrong about htat

[keen gorge]

thats called a pool but idk if and how its working for sqlite, but for postgresql it should be available for the most libaries

[keen gorge]

that doesnt matter afaik

[potent shoal]

pls do

...
except Exception as e:
  print(e)

so that you see what causes the error

[keen gorge]

lemme try

[potent shoal]

btw, is user.id your primary key for the database

[keen gorge]

(1062, "Duplicate entry '431876970322591755-1. Geburtstag' for key 'badges.badges_UN'")

[keen gorge]

have no primary key

dont need it here

[potent shoal]

bist du deutsch btw? 🙂

[keen gorge]

hahahah

[burnt latch]

Hi all, I'm starting a new flask project and in the past I just used a postgres database, but I'm wondering if doing something nosql like mongodb or cassandra would be better?

One of the criticisms I've seen of SQL is the rigidity of the schema when you want to add new columns to a table for example, but doesn't something like alembic make that a non-issue?

[proven arrow]

For server based database like postgres it is better to use a connection pool. So you can avoid the connect/disconnect overhead. It should be supported by the library or framework you use.

[potent shoal]

Yeah thank you, I did indeed implement it with a pool in the end :)

[proven arrow]

Alembic would make it easier to make the migrations and change schema yes, but I wouldn't consider this as a criticism or disadvantage. Its like writing more code for new features and functionality in a software (i.e. changes will be needed.)

[upper citrus]

is it possible to store a list in sqlite?

[finite oracle]

Hello my friends, I have a problem with a SQL query, when I call the main table, I need to add and create a foreign key in the additional one, how can I write the query correctly?

 cursor.execute("""INSERT INTO information_users.users_id VALUES ('last_insert_id()')
FROM information_users AS iu JOIN users ON iu.users_id = users.id_users join
users_imunitet AS ui ON iu.imunitet_id = ui.id_users_imunitet"""
)

[slow grail]

"database is locked" error

Traceback (most recent call last):
  File "/opt/virtualenvs/python3/lib/python3.8/site-packages/discord/ext/commands/core.py", line 85, in wrapped
    ret = await coro(*args, **kwargs)
  File "/home/runner/Tests-Bot/Cogs/Moderation.py", line 239, in warn
    await cur.execute('''INSERT INTO Warnings VALUES (1, ?, ?, ?, ?, ?)''', (ctx.guild.id, user.id, ctx.author.id, datetime.datetime.now(UTC), Reason))
  File "/opt/virtualenvs/python3/lib/python3.8/site-packages/aiosqlite/cursor.py", line 37, in execute
    await self._execute(self._cursor.execute, sql, parameters)
  File "/opt/virtualenvs/python3/lib/python3.8/site-packages/aiosqlite/cursor.py", line 31, in _execute
    return await self._conn._execute(fn, *args, **kwargs)
  File "/opt/virtualenvs/python3/lib/python3.8/site-packages/aiosqlite/core.py", line 129, in _execute
    return await future
  File "/opt/virtualenvs/python3/lib/python3.8/site-packages/aiosqlite/core.py", line 102, in run
    result = function()
sqlite3.IntegrityError: UNIQUE constraint failed: Warnings.ID

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

Traceback (most recent call last):
  File "/opt/virtualenvs/python3/lib/python3.8/site-packages/discord/ext/commands/bot.py", line 939, in invoke
    await ctx.command.invoke(ctx)
  File "/opt/virtualenvs/python3/lib/python3.8/site-packages/discord/ext/commands/core.py", line 863, in invoke
    await injected(*ctx.args, **ctx.kwargs)
  File "/opt/virtualenvs/python3/lib/python3.8/site-packages/discord/ext/commands/core.py", line 94, in wrapped
    raise CommandInvokeError(exc) from exc
discord.ext.commands.errors.CommandInvokeError: Command raised an exception: IntegrityError: UNIQUE constraint failed: Warnings.ID```

is aiosqlite just having a stroke or did i fuck something up

[jaunty galleon]

asyncpg.exceptions.PostgresSyntaxError: syntax error at or near "unique"

await conn.execute('''CREATE TABLE IF NOT EXISTS economy_system (member_id unique , wallet bigint , bank bigint)''')

Anyone knows why this would happen?

[radiant elbow]

Don't you need to provide a type for member_id, like int?

[jaunty galleon]

Thank you it worked!

[slow grail]

could anyone help me with this

  @commands.command(help='Warns the user for the specified reason', aliases=['w'])
  @commands.has_permissions(kick_members=True)
  @commands.guild_only()
  async def warn(self, ctx, user: discord.Member, *, Reason='No Reason Provided'):
    UTC = datetime.timedelta(hours=-1)
    UTC = datetime.timezone(offset=UTC)
    con = await sql.connect('./warnings.db')
    cur = await con.cursor()
    Warnings = await cur.execute('''SELECT Id, User FROM Warnings WHERE User = ? ORDER BY Id DESC''', (user.id,))
    WarningsCount = await Warnings.fetchall()
    if len(WarningsCount) == 0:
      await cur.execute('''INSERT INTO Warnings VALUES (1, ?, ?, ?, ?, ?)''', (ctx.guild.id, user.id, ctx.author.id, datetime.datetime.now(UTC), Reason))
      try:
        await user.send(f'You were Warned in {ctx.guild.name} by {ctx.author}. This is Your First Warning;\n**Reason/Message:**\n{Reason}')
        await ctx.send(f':ok_hand: Warned {user}! This is Their First Warning.')
      except discord.Forbidden:
        await ctx.send(f':ok_hand: Warned {user}! This is Their First Warning - I couldn\'t DM Them.')
    else:
      await cur.execute('''INSERT INTO Warnings VALUES (?, ?, ?, ?, ?, ?)''', (None, ctx.guild.id, user.id, ctx.author.id, datetime.datetime.now(UTC), Reason))
      try:
        await user.send(f'You were Warned in {ctx.guild.name} by {ctx.author}. You now have {len(WarningsCount) + 1} Warnings;\n**Reason/Message:**\n{Reason}')
        await ctx.send(f':ok_hand: Warned {user}! They now have {len(WarningsCount) + 1} Warnings!')
      except discord.Forbidden:
        await ctx.send(f':ok_hand: Warned {user}! They now have {len(WarningsCount) + 1} Warnings - I couldn\'t DM Them.')
    await con.commit()
    await con.close()

that's the code

[full moon]

@clever bough

[clever bough]

What do you want?

[full moon]

Will social miss check our c.w today?

[clever bough]

Not the place to ask, but idk.

[full moon]

Ik, what do you mean by idk?

any guesses?

[clever bough]

Wut?

Bruh not the place for all of this.

[burnt turret]

Perhaps take this to DMs

[opal hill]

go to #discord-bots

[slow grail]

its related to databases not discord.py

aiosqlite is having a problem where the database is "locked"

[ripe moat]

hi

i need someones help

is anybody here using whats app

hello

is anybody using whatsappp

please

tll me

i nneed help

[burnt turret]

just ask your question

provided it is related to databases

[burnt turret]

asyncpg is just a driver, i don't think there are any specific learning resources for it as such

learning how to use SQL is a different thing though

you'd only end up using a few asyncpg functions to run SQL queries

[burnt turret]

sqlbolt is a good SQL tutorial

there's one similar to it specifically for postgres too

https://pgexercises.com/

actually i think sqlbolt might be better 🤔

[ripe moat]

is anyone here doing

a level computer science

please tll me

@burnt turret hi

[burnt turret]

hello, just ask your question

[jaunty galleon]

asyncpg docs - https://magicstack.github.io/asyncpg/current/index.html
PostgreSQL tutorial (not for asyncpg directly, PostgreSQL SQL syntax tutorial at most) - https://www.postgresqltutorial.com/
SQL syntax tutorial - https://sqlbolt.com/
w3schools tutorial for SQL syntax(I used it and it is pretty good, includes exercises dataview, and examples, with a test at the end) - https://www.w3schools.com/sql/sql_distinct.asp
anand suggestion(pgexercises) - https://pgexercises.com/
Links in this channel that would help:
Using asyncpg with discord.py(I know you are using it) - #databases message
Understanding asyncpg return objects(asyncpg.Record) - #databases message
Explains why not to use f-string, quite important- #databases message
And official FAQ site - https://magicstack.github.io/asyncpg/current/faq.html
**MOST OF IT IS BY ANAND, THANK HIM ** I just organized it a little. For any question ask here or open a help channel (see #❓｜how-to-get-help ), and the finest programmers will be there to help!

[torn sphinx]

is there a concept of "hierarchy" in sql? in terms of, if i have two tables that have the same columns, is there a way to abstract them?

say i got "Y" and "Z" tables that have the same columns, is there a way to declare a table "X" and make both Y and Z tables descend from it?

[pure sleet]

two tables with the same data is redundancy

[torn sphinx]

its not the same data, is the same columns

column names i mean

[proven arrow]

Sure there is different types of inheritance.

Can you be more specific, maybe give an example

[proven arrow]

CREATE TABLE x (
x_id integer,
common_column varchar(255),
-- common columns for all sub tables
);

CREATE TABLE y (
x_id integer,
y_column varchar(255),
-- other columns specific to y
FOREIGN KEY (x_id) REFERENCES x (x_id)
);

CREATE TABLE z (
x_id integer,
z_column varchar(255),
-- other columns specific to z
FOREIGN KEY (x_id) REFERENCES x (x_id)
);

This would be class table inheritance. The primary keys of the sub tables also are the foreign key to the parent table.

[torn sphinx]

no, but there you are establishing a relationship between x, y and z, right?

[proven arrow]

Only between parent and child

[torn sphinx]

what i mean is table y is : pk id integer, name string, table z is pk id integer, name string

[proven arrow]

Be more specific

[torn sphinx]

so if these were objects i would do x : pk id integer, name string

gimme a sec ill paste the actual sql code

CREATE TABLE lists (
    ID   INTEGER PRIMARY KEY AUTOINCREMENT,
    NAME TEXT    UNIQUE
                 NOT NULL
                 COLLATE NOCASE
);
CREATE TABLE categories (
    ID   INTEGER PRIMARY KEY AUTOINCREMENT,
    NAME TEXT    UNIQUE
                 NOT NULL
                 COLLATE NOCASE
);

[proven arrow]

What are you trying to achieve? What’s the end goal

[torn sphinx]

they are two different tables, have different data in them

they work differently in terms of code too

and have different relationships

but im writing the daos now, and i realize there are a lot of shared functionality, so i will refactor

so i will make both category and list descend from a common object

or category from list, as category extends the basic functionality of list

so i was wondering if theres a similar way of refactoring tables that have the same functionality in sql

something like:

CREATE TABLE lists (
    ID   INTEGER PRIMARY KEY AUTOINCREMENT,
    NAME TEXT    UNIQUE
                 NOT NULL
                 COLLATE NOCASE
);
CREATE TABLE categories (
    DESCEND FROM lists
);

[proven arrow]

1. What I said and gave example for is exactly what this is doing.
2. This is not really necessary. The table lists itself looks like bad design, as you don’t generally store lists but normalise them.

Your overcomplicating this and will just have more trouble writing queries or code for it.

[torn sphinx]

1. Isn't "FOREIGN KEY (x_id) REFERENCES x (x_id)" making x_id in Y and Z reference the x_id in X?

2. What do you mean?

[proven arrow]

Which makes the subtables inherit all of X.

[torn sphinx]

yeah but i want to inherit the structure of X, not the contents

or rather, not want, but wonder if there is such a thing

[proven arrow]

Yeah you can’t do that.

[torn sphinx]

Alright, fair enough, thank you

What did you mean by "The table lists itself looks like bad design, as you don’t generally store lists but normalise them."?

[proven arrow]

It’s to do with Normal Form

Which states you would want atomic values. So if your storing lists which can be divided then it would break this.

[torn sphinx]

mmm, i didn't quite catch that, my structure is, media objects have a category_id (foreign key) as field, but there's then a list_to_media junction table because a media can belong to many lists

lists are like a kind of "my favorites" grouping in that the user can create as many as they want, and relate any media object into it, and a single media object can be related to many lists, while categories are a hard value of each media, they just happen to have the same exact structure

if it were json it would be:

{
  categories: 
    [
      {"id": 1, "name": "Category 1", "members:" 
                                        [{"id": 1, "name": "Media 1"....}...],
      }
    ]
  lists : 
    [
      {"id": 1, "name": "Favorites", "media_ids": [1, 2, 3, 4]}
      {"id": 2, "name": "Favorites 2", "media_ids": [2, 3]},...
    ]
}

[proven arrow]

Yeah I see but your initial table didn’t show that so well. The name of the table lists is not super obvious as well as it what it’s doing

[torn sphinx]

oh no problem man, im just asking, i realize i didn't post the entire thing. So there's no bad design with the "lists" tables, but i should change the name to make it less confusing?

maybe user_groupings or user_lists?

[proven arrow]

Sure. And for many to many you would need to have a composite primary key.

[torn sphinx]

whats that? im using this right now:

CREATE TABLE lists_to_media (
    LIST_ID  INTEGER REFERENCES lists (ID) ON DELETE CASCADE
                     NOT NULL,
    MEDIA_ID INTEGER REFERENCES media (ID) ON DELETE CASCADE
                     NOT NULL,
    UNIQUE (
        LIST_ID,
        MEDIA_ID
    )
);

[proven arrow]

Yeah that’s fine.

Maybe make them both primary composite keys

https://stackoverflow.com/questions/9789736/how-to-implement-a-many-to-many-relationship-in-postgresql

See that example

[torn sphinx]

thx!

CONSTRAINT bill_product_pkey PRIMARY KEY (bill_id, product_id)  

so basically im using the composite of the foreign keys as a primary key

instead of my UNIQUE( constraint?

[wise goblet]

if you are in ORM (Django), I have alternative how to do that

class CountryAndHub(models.Model):

    country = models.ForeignKey(
        Country, on_delete=models.CASCADE, db_index=True)
    hub = models.ForeignKey(Hub, on_delete=models.CASCADE, db_index=True)
    priority = models.FloatField(default=1.0)
    visible = models.BooleanField(default=True)

    class Meta:
        verbose_name_plural = "countries_and_hubs"
        constraints = [
            models.UniqueConstraint(
                fields=['country', 'hub'], name='unique_country_hub')
        ]

with meta field we could make compsite unique contraint too there

[torn sphinx]

plan on implementing it on django in the future, im doing this project to try several new things, but im not at that point yet, right now im building the data structure and implementing a dao that can work on different dbs

what does your code do?

isn't it just a django implementation of what i posted?

[wise goblet]

essentially yes

it is exactly the same as you posted

if we would remove priority and visible attribute

and rename it to your thing

[torn sphinx]

yep

[wise goblet]

we would receive your lists_to_media

[torn sphinx]

its funny doe how its implementing the constraint with the Meta class

like i assume the class name could be anything? or is "Meta" a keyword?

does django extends python like it does html?

[wise goblet]

class name can be anything

Meta is keyword

[torn sphinx]

so its doing some kind of fancy inspecting/interpreting

it detects Meta is class within a Model class

[wise goblet]

yup, new level of abstraction!

[torn sphinx]

and i assume verbose_name_plural and constraints are keywords too

nice

[wise goblet]

as I read in previous book, you can solve any programming problem with additional levels of abstractions

except the problem of too many abstractions

[torn sphinx]

sicp tier shit hahah

[jaunty galleon]

Can anyone help me connecting to elephantsql?

Isn't it DELETE

Was I correct?

Oh great

[autumn condor]

Had a doubt with django and postgresql

https://www.reddit.com/r/Database/comments/n946en/how_to_store_array_elements_in_sql/?utm_medium=android_app&utm_source=share

Could anyone suggest how they go up with this?

[jaunty galleon]

Can anyone please help me connecting to elephantsql? I am trying to connect to it, I have already made an instance, and went on his data, and in create_pool I entred:
user = username shows under "User & Default database", password = Password shown under "Password", database = database shown under "User & Default database", host = The link shown under "Server"
error:
https://paste.pythondiscord.com/todutuzonu.sql

[soft edge]

hey guys, can any one help me to convert a couple of df from a pickle dictionary to jason / thanks

[torn sphinx]

when i create a new folder, i added the folder to the path of where the data is being stored. when i press next the folder is deleted and gone
and i couldnt find the folder

postgresql

ping when response

[proven arrow]

Although you can store array elements, you will be violating the basic rules of relational databases. So you should try not to store it as an array, and instead create another table to store the data. So each column is atomic.

The rule you're violating is called first normal form and which is the first step in database normalisation.

https://en.m.wikipedia.org/wiki/Database_normalization

[jaunty galleon]

Is it allowed to store "lists" like that:

guild_id | word
---------------
123      | hi
123      | hihi

And than iterate through all of the rows with guild id 123 and convert it into a list of my own?

[autumn condor]

Yes exactly, How do you think I should go about storing this?

[half forum]

How do I use sqlite for discord.py??

[jaunty galleon]

I think sqlite is already async, so check their docs mainly

[brazen charm]

it's not no

i would advise using aiosqlite if its in a async context

[proven arrow]

👍🏻

[proven arrow]

I told you. You can create a new table.

Like what he has done here #databases message

[autumn condor]

Alright then that is

A new table with the message ID and user ID

Cool I guess this won't cause any issues for 3-4 years if it scales I gotta rethink later :p

[proven arrow]

At the moment your list is representing multiple entities of a type. This list can be divided further right?

[still sandal]

Hello, I'm trying to fetch a large number of values using the same key from MongoDB and place them into a List, but i cant figure out how, I dont really have code or an error because I just need help figuring out how to do a thing, not resolve an errror

[proven arrow]

For example, if a Product has multiple colours and we want to store these colours we could do it as a list like [red,blue,yellow]. But we see we can divide the list into three separate colours. So you make a new table with columns product_id, colour. And then store each colour seperatwly on a new row. @autumn condor

This brings so much more benefits such as easier querying/aggregation, ensuring data integrity and making it easy to manage what colours a product has.

[autumn condor]

Yep

[autumn condor]

Ah okay I've worked with SQL, but majorly NoSQL and my work so far with SQL has been with less amounts of data. And currently what I'm building can easily go up to a lot, so had concerns

Thanks! Now I'm a bit clear

[proven arrow]

You might want to look at examples online of similar database schemas, or just to get the idea of how other people structured it. There is a good site for templates here, so you might want to check if it includes something you can use for ideas. https://drawsql.app/templates

[autumn condor]

Oh this looks nice, didn't know about it thanks!

[next blaze]

I've got a daily data extraction process that pulls about 10 files, cleans them and dumps them into GBQ as a data lake. One of these files for example averages about 600k rows of delimited data, with 20 columns

This process runs inside of a container in google compute engine and runs daily

would it be more time & cost efficient to drop any unused columns with python during the cleaning phase before the df is uploaded from the container to GBQ or drop it via a sql command in GBQ

I'm wondering if dropping such a large column from a df would cause a major performance / time increase on the compute engine

[half forum]

!d sqlite3

[half forum]

How do I use aiosqlite for discord.py

[brazen charm]

It's pretty much the exact same you would normal sqlite but with async and await

[half forum]

so would this work?

:/

[half forum]

Any documentation?

[brazen charm]

https://pypi.org/project/aiosqlite/

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

https://sqlbolt.com/ (for SQL help)

[half forum]

ty

[half forum]

And just to check they still work with discord.py right just that i need async and await right?

[brazen charm]

yeah discord.py doesnt really change anything

its just the async await stuff that changes it

[rocky pine]

hey can someone help me with mySQL?

[vital kernel]

what's the issue

[rocky pine]

I wanna download mySQL

Windows (x86, 64-bit), ZIP Archive
Debug Binaries & Test Suite

Windows (x86, 64-bit), ZIP Archive

which of these two I should download, I just started database

[half forum]

zip

[inner ivy]

im using pymongo to save a channel id of a server:

  x = col.insert_one(dic)
  await ctx.send("done")```
but how would i get the channel id by the server id?

await col.find_one({"channel": guild_id})
this is what i tried ^

but it returned "None"

[brazen charm]

by default it does in ascending order

you need to do DESC for decending

at the end of the query

[thorn osprey]

how could i add a value to an array(the array is loaded from a json file)

[gusty halo]

So I have a 2700000000000 of strings, all I need is just do simple queries of another strings to find matches, what db type would be the best for this?

Maybe some simple threaded cpp program to find matches from plaintext would be faster than any sql db for this?

[burnt trout]

HI, I have a scenario. I am using Django and I need to implement a function similar to this.

Suppose I have coordinates of a group of devices. I need to find devices located within a particular radius of a given coordinate. I was thinking of looping through all the coordinates and calculating distance and comparing it with the radius. And it returns the qualifying coordinates.

But there should be better way to do this right, I f there are a lot of coordinates, then this method is not efficient. Can anyone help me??

[shell ocean]

look into PostGIS

[torn sphinx]

Hey Anyone there?

username = self.textEdit.toPlainText()
        password = self.textEdit_2.toPlainText()


        self.Database()
        cursor.execute(
            "SELECT * FROM customer;"
        )
        print(cursor.fetchall())

        cursor.execute(
            "SELECT * FROM customer WHERE username = ? AND password = ?;", (username, password)
        )
        print(cursor.fetchall())
        conn.commit()

And this is the output

[(1, 'FirstUser', 'First', 'User', 'FirstUser', '[e-mail]')]
[]

I'm searching for FirstUser but it's not getting found

[inner ivy]

im using pymongo to save a channel id of a server:

  x = col.insert_one(dic)
  await ctx.send("done")```
but how would i get the channel id by the server id?

```await col.find_one({"channel": guild_id}) ```
this is what i tried ^

it returned "None" tho

[jaunty galleon]

{guild_id : the guild id, channel: channel id}

You'll need to detect and get the ful dict

If you'll do:

data = await col.find_one({"guild_id": ctx.guild.id})

It'll return the full dict that contains this data

[torn sphinx]

@jaunty galleon Any ideas about what I can do to fix this issue?

[tribal cargo]

Hello there. Last times I used sqlalchemy. Now i figured out, that there is an flask-sqlalchemy library. But i don't figured out how I can set the create method in an extra Database.py file. I used to create a Method in an Database file so all my database settings are in one file. It seems flask_sqlalchemy needs the app statement wich i can't call from an other module?

[torn sphinx]

OKay

It's working now for some reason @bitter yoke

I don't even know if I changed anything in the code

[bitter yoke]

Did you change the data in the sqlite db? Or changed nothing?

[wraith shell]

Hi.. is there a way to store many to many hierarchy in RDBMS (not Graph Database) where a child can have n numbers of parents and some parents can be childless as well.

[bitter yoke]

i sort of gave up on using flask-sqlalchemy for that reason -- i found it way too weird (or maybe impossible) to use it if I also wanted to use some of that db code from non-flask-related modules

[tribal cargo]

Okay, thanks for that view. I think I'LL keep using sqlalchemy then, too.

[bitter yoke]

are you using the ORM or just core in SQL Alchemy?

[tribal cargo]

I'm using the ORM. Building relations and tables.

[bitter yoke]

Then you might still consider the Flask-SQLAlchemy thing or not -- what it really does for you is manage the Session objects based on Flask's threads and requests, which is a handy thing compared to doing it yourself -- i just ran into this last week and it was pretty frustrating for me too

[bitter yoke]

here's a pretty long article with way more info than you need about someone else who decided to skip Flask-SQLAlchemy:
https://towardsdatascience.com/use-flask-and-sqlalchemy-not-flask-sqlalchemy-5a64fafe22a4

[bitter yoke]

you can make tables that'll store that kind of relationship, but it can get pretty tough to query them in a clean way

[wraith shell]

Have you explored how your can integrate session management with SQLAlchemy in flask without using Flask-SQLAlchemy. I like SQLAlchemy and it’s much easier to use than Flask-SQLAlchemy.

[wraith shell]

Yes but there won’t be any foreign key referencing to primary key in the same table..

[bitter yoke]

if I recall, that's basically what the author of that article is exploring -- in my case, I was only using core and I didn't need to get fancy with sessions and ORM so I cheated, very badly

[bitter yoke]

so am I understanding that also, in this case, the parents and children are the same "things" -- one table of "things", and they're "parents" if they happen to have children, and they're "children" if they happen to have parents

[torn sphinx]

just the name of the clumn

[tribal cargo]

Session handling is very nice by using normal sqlalchemy

[wraith shell]

Yep.. that’s how it is. Everyone is a parent. But if the parent doesn’t have a child than we have a single entry with null child, otherwise parent Id would get repeated to manage all the child’s of it in each row.

[bitter yoke]

so if there's a 'thing' table with 'id' and 'name', you could have a 'thing_relationships' table with parent_thing_id and child_thing_id, I think --

[wraith shell]

Yes but then there would be relationship id as primary key in thing relationship table.. which I am okay with by the way.

But how would I define the relationship in SQLAlchemy so I can go to any depth of a parent’s tree. It’s relationship is like DAG. It’s acyclical. If a parent has a child, that child cannot be the parent of its own parent which I hope makes sense in a hierarchical model.

[bitter yoke]

Right -- so

this sounds terribly familiar, although I can't remember where I ran into it

i think the tables we discussed there are one of the only ways (if not the only way) to really store these relationships in an RDBMS

and querying them is hell

and I have basically no idea how SQLAlchemy could be taught to understand them

[wraith shell]

Querying is exactly what I am trying to figure out here. I am not even doing any CRUD operation in this database. It’s for reporting and it’s a frequent requirement from business it get the descendents of a parents. I work in telecom and that’s how circuits are defined. You have a 100g pipe, which is broken down into smaller pipes. And we use 5 or 6 such pipes to connect point a to point z

[bitter yoke]

it's (unfortunately) a genuinely interesting problem

there might be a known "clever" way that I'm not aware of, but forgetting about SQLAlchemy for a second --

if you were just doing this with SQL by hand, can you think of a way that wouldn't require you to run a query, check if there were children, run another query, check again for children, run another query, etc?

[wraith shell]

No problem.. I’ll figure something out. I know I am missing something here.
There is the concept of association table but I am not sure how I would use it I define relationship

[wraith shell]

That makes sense.. I am not sure how cost effective it is. Let me try that

[bitter yoke]

i don't think it's going to be efficient at all, I just can't think of a way around it

it'd be a great use of a graph database, though

easy enough to get "all children of thing X" or "what is the parent of thing Y" in a single SQL query, but the arbitrary-depth thing stumps me without looping and doing multiple queries

if you only need the info one "level" at a time like that, though, it might be fine

[wraith shell]

It would break my ORM, all my objects are in RDBMS 🙁

[bitter yoke]

yep, my only gut feeling there is maybe, maybe

if the ORM can be told "this is a many-to-many relationship"

and you can ensure you never tell it to "follow all relationships", but instead exert some control over which ones it follow at which times

it could be possible

[wraith shell]

What do you mean by which one it follows at which times?

[bitter yoke]

so if you have an object for thing X, you'd like to able to call getChildren() on it

[wraith shell]

Yep

[bitter yoke]

but also getParent()

and if i'm not mistaken, in SQLAlchemy you're likely to quickly find it following all parents and children of everything

until it runs out

[wraith shell]

But why would it work quickly, it’s a recursive query. Sorry I meant all decendents not just children.

[wraith shell]

Can you please quickly cover what you mean by this, I didn’t get it

[bitter yoke]

i mean if you say getChildren, and SQLAlchemy gets the children -- won't it also get all of their children, and all of their children, and all of their children

and since each child has a getParent, if would get that parent, and its parent, and its parent, and all of their children

[wraith shell]

That’s where lazy and eager loading comes in

[bitter yoke]

yep

so that's what I meant by making sure you have knowledge and control over when it does and doesn't traverse the relatonships

because it's potentially a big eager load

[wraith shell]

Exactly 😦

I’ll research more

[bitter yoke]

yep we're on the same page

there are people on pages I've googled that seem to be doing this in SQL Alchemy, but so far, they're not really quite doing the same thing

i'd say if you can convince SQL Alchemy that you've defined a many-to-many relationship that just happens to have the same type of object on each side, and you're careful with lazy/eager, you may just have a solution here 🙂

sounds like you've got a bit more SQL Alchemy experience than I have, too, so hopefully it's easier than it sounds to me

[wraith shell]

Yep. That’s where I am. Just need to figure out the relationship at the moment.

Also have you ever tried to connect 2 databases like RDBMS and Graph in one ORM

If it’s a possibility it will solve most of my problems

😩

[bitter yoke]

Nope, and I hope to never have to try

I've had (for instance) an RDBMS with its ORM, and also (say) some NoSQL database (or more than one)

but the interactions between them were all "our code", not anything we could ask the ORM(s) to do declaratively

like, a vague memory of a "user" object from an ORM-with-MySQL from a central company-wide user DB, but for some specific app, a NoSQL record of "user preferences"

[wraith shell]

Yes.. that’s would require a lot of effort to to that. Not even sure if SQLALachemy even has the dialect fot Graph databases

i think it only supports RDBMS

[bitter yoke]

i imagine so

it's very much about SQL and says as much

[wraith shell]

yep.. alrighty back to the drawing board. Thanks mate!

[bitter yoke]

in fairness, we always quickly realized that even if we could cram it all into one ORM-like thing, really you'd lose out on the differences between the SQL, NoSQL, Graph, etc models that made you choose them anyway

yep, good luck -- I hope it stays fun 🙂

[wraith shell]

I know ORM is basically NoSQL just more rigid.
Cheers!

Actually NOSql ain’t rigid at all. Bad anology. Lol

[bitter yoke]

hm, actually -- just stepped away and thought of something -- this is arbitrary in depth but a child can't have two parents, right?

[wraith shell]

No it can. That’s the challenge.

[bitter yoke]

oh, yep

[wraith shell]

Otherwise it would be one to many

Child would become the primary key

[bitter yoke]

i just took three steps and said "wait was this defffinitely many-to-many"

oh well, it was a nice thought for 30 seconds

[wraith shell]

Yeah..

I’ll keep you posted if I find something

Just in case you might need it later

[bitter yoke]

i'll honestly be curious just for the sake of knowing 🙂

[wraith shell]

👍

[surreal flame]

yo

class User(AbstractUser):

    money = models.IntegerField(default=1_000_000)

    def __str__(self) -> str:
        return self.email


class Transaction(models.Model):
    class Types(models.Choices):
        SELL = "Sell", _("Sell")
        BUY = "Buy", _("Buy")

    share_type = models.CharField(max_length=4, choices=TransactionTypes)
    owner = models.ForeignKey(User, on_delete=models.DO_NOTHING)
    currency = models.ForeignKey(Currency, on_delete=models.DO_NOTHING)
    shares = models.IntegerField()
    max_value = models.IntegerField(default=0)
    date = models.DateTimeField(default=timezone.now())


class Currency(models.Model):
    name = models.CharField(max_length=10)
    value = models.IntegerField()

Any idea how i can design database to currency exchange www?

what should i add?

[burnt turret]

add a WHERE clause with the condition you just said

the WHERE will come before the ORDER BY

yep

WHERE invites >= 1

yeah

[steel umbra]

in sqlalchemy orm, if i delete all rows it resets pk to 0, is there a way to stop this? column is currently ```py
challenge_id = Column(Integer, primary_key=True, index=True, autoincrement=True)

thanks

im guessing it has something to do with index=true but im not sure

[torn sphinx]

Yo I'm new to dbs.. so how do I check if a certain value exists in a column or not?

[proven arrow]

select exists (select * from table_name where column_name = value);

It returns value of 1 or 0

[torn sphinx]

Ah i see

[frigid flare]

I was trying out the introductory SQL restore tutorial at https://docs.microsoft.com/en-us/sql/samples/adventureworks-install-configure?view=sql-server-ver15&tabs=ssms and I ran into an issue, I hope the images below will help explain the issue.

More context

[soft edge]

i was trying to read pickle in python as pandas but im getting the error as this. ive been reinstalling updating pandas, pickle module and even though i cannot find a solution for days now. help me please. thanks.

[torn sphinx]

When ordering data would be more efficient to store it already ordered instead of ordering by order by statement?

[proven arrow]

Why do you want to take all this extra work in ordering yourself. Such a pointless thing to do.
Also the first rule of normalisation states, any ordering of the data must be defined by the data and not by the physical ordering of the rows.

And SQL is based on idea of sets, meaning that the only ordering you should rely on is the one you give in your query.

[inner ivy]

ok and then how would i make it extract the channel id saved in it tho?

  x = col.insert_one(dic)
  await ctx.send("done")```
i have this to save a channel id of a channel a user mentions, now i want to make a command that can fetch the channel id and send in it a message, this is what i have for that:
```data =  col.find_one({"guild_id": ctx.guild.id})
  print(data)```
it prints out the guild id + channel id + the object id, how can i make it extract just the channel id?

using pymongo btw, im very new to mongodb in general

it prints this:
{'_id': ObjectId('OBJECT_ID_HERE'), 'guild_id': ACTUAL_GUILD_ID, 'channel': ACTUAL_CHANNEL_ID}
how can i isolate "ACTUAL_CHANNEL_ID"?

[proven arrow]

Looks like a dict so just index it with keyname

print(data['channel'])

[inner ivy]

@proven arrow what if i wanted to edit the value of the channel id

from the channel id to something like 0000

is there like a col.edit_one() or col.update_one()

[cyan yacht]

For that there is update_one

[inner ivy]

ty

[inner ivy]

what are like the arguments that i need to place for that?

  x = col.update_one(dic)```
this doesnt work for sure

[cyan yacht]

First arg is the condition, ie, whatever you give for the corresponding collection.find
2nd arg is the new value

[proven arrow]

See here for an example. You need to use the $set as the key for the new values. https://www.w3schools.com/python/python_mongodb_update.asp

[atomic kraken]

Hey! Does anyone here have experience with SQLAlchemy? Specifically experience with getting the data, submitting data and displaying data on the webpage

[atomic kraken]

I need someone to guide me through setting it up and making it work with my app

[torn sphinx]

in mongodb how do i sort by the size of a list?

[late tinsel]

I am a bit lost in the sauce. I am working on a pipeline that will ingest data ( XML files, EDI ). This data is shipment status data, and to specify these aren't shipments that are going to your door, but full out containers on ships. So the number of these shipments is drastically lower, than something like amazon to your door. The goal is to build an app that the client can go to and see the status of there shipment, currently done using PowerBI. What I am thinking is since this data is coming in XML format, I can easily convert it to JSON and use a no-sql database for example firestore. Where each document is the order-id, and as we get these updates the document would be updated ( Again not a lot of updates happening to the document at once time, maybe a decent amount of reads depending on how many people are checking up on the status). What do you guys think, would a no-sql database be good for this or would a sql database be better for this app. This data at some point will go to a datawarehouse, but it being sql/no-sql really makes no difference for me with this.

[harsh pulsar]

@late tinsel can you elaborate more on the use case? Is there any compelling reason not to just have a bunch of XML files on a filesystem somewhere?

Also PostgreSQL (a popular relational database) has an XML data type with functions for querying data from XML documents https://www.postgresql.org/docs/current/functions-xml.html

[late tinsel]

It will be queried by the app to get that shipments information

Live

[harsh pulsar]

i recommend against trying to generically convert the XML to JSON just so you can put it in some document database, unless you're very confident that the format can be converted without messing things up

[late tinsel]

either way it will need to be converted into some format

Since it will go to a data warehouse

[harsh pulsar]

ideally you'll be storing the original XML somewhere anyway, since it's a data warehouse

[late tinsel]

True

The format is 315 EDI

[harsh pulsar]

https://www.ibm.com/docs/en/scbn?topic=standards-315-status-details-ocean it looks like there's some standard way to encode this EDI data in XML then?

[late tinsel]

    <CarrierSCAC>CHSL</CarrierSCAC>
    <CHRLoadNumber>102388305</CHRLoadNumber>
    <Mode>OCEAN</Mode>
    <ShipmentStatus>
        <StatusName>VD</StatusName>
        <StatusDate>20210429</StatusDate>
        <StatusTime>0000</StatusTime>
        <DateTimeQualifier>ACTUAL</DateTimeQualifier>
        <StatusLocation/>
        <StatusCountryCode/>
        <EquipmentNumber>ABCD7723925</EquipmentNumber>
        <EquipmentStatus>LOAD</EquipmentStatus>
        <EquipmentType>40FT</EquipmentType>
    </ShipmentStatus>
    <ReferenceNumbers>
        <ReferenceNumber>
            <ReferenceQualifier>SHIPMENT NUMBER</ReferenceQualifier>
            <ReferenceValue>102388305</ReferenceValue>
        </ReferenceNumber>
    </ReferenceNumbers>
    <ShipmentStatusDetails>
        <VesselName>NYK TRITON</VesselName>
        <VesselCode>9356713</VesselCode>
        <CountryCode/>
        <Flight_VoyageNumber>086E</Flight_VoyageNumber>
        <LadingQuantity>10</LadingQuantity>
        <LadingUOM>CTN</LadingUOM>
        <NumberOfPallets/>
        <Weight>15522</Weight>
        <WeightQualifier>GROSS</WeightQualifier>
        <WeightUnitCode>KILOGRAMS</WeightUnitCode>
        <Volume>17</Volume>
        <VolumeQualifier>CUBIC METERS</VolumeQualifier>
        <HazMatIndicator>N</HazMatIndicator>
        <SpecialHandling/>
    </ShipmentStatusDetails>
    <PortOrTerminals>
        <PortOrTerminal>
            <PortFunction>PLACE OF RECEIPT</PortFunction>
            <PortName>Port Ningbo</PortName>
            <PortCountryCode>CN</PortCountryCode>
            <PortStateOrProvinceCode/>
            <EstimatedDate>20210326</EstimatedDate>
            <ActualDate>20210326</ActualDate>
        </PortOrTerminal>

            <ActualDate/>
        </PortOrTerminal>
    </PortOrTerminals>
</ROOT_ShipmentStatus>```

Is what I will be getting ( example, sorry if I pasted wrong format )

But this can easily be converted to json format

[harsh pulsar]

you can use 3 ` characters to make it a "code block"

```xml
<ROOT_ShipmentStatus>
</ROOT_ShipmentStatus>
```

[late tinsel]

update hehe

[harsh pulsar]

and you can add xml after the ``` for syntax highlighting

[late tinsel]

The idea, is I will need to query for this data as it is coming.

[harsh pulsar]

yeah this can be converted to json without too much pain. and yes, you could then dump the json version into mongodb and do fairly arbitrary queries against the data without having to worry about flattening it or whether your relational database supports xml or json

[late tinsel]

Or as people want the status to be more exact

[harsh pulsar]

so what's an example of a query you would want to perform?

this could be flattened to relational tables if you wanted, which would be great for doing analytics queries in the data warehouse

[late tinsel]

Realistically it would be for 4 areas, Did the shipment leave the dock, did the shipment arrive at dock, did the shipment pass customs, and did the shipment arrive final destination

Then this data would be moved to a data warehouse

Which is a whole animal of its own, as they currently have it on the same MS SQL server as there SAP b1

Trying to move it somewhere else, but at the same time need cost effective, but not shooting ourselves in the foot. They want to use snowflake, but that is overkill as well <50 Gb

lol

[harsh pulsar]

so the flow would be something like:

1. user requests a status check for shipment 12345
2. your app fetches the status of shipment 12345 which is returned in this format
3. your app saves the raw xml to a blob/file store, extracts the status of the shipment, then forwards the data to the data warehouse
4. your app reports the status to the user

is that right?

[late tinsel]

So, the data doesn't come through the App, the Cargo companies send us this data through sftp. We would load this data somewhere

After data is loaded or placed where ever.

Then the user would:

1. Request status check for shipment 12345
2. App fetches data in whatever format it is
3. Show information in pretty format and time line like chart
4. Money

So the app is more so just for checking the shipment status, where we will be collecting the data from the Cargo Vendors

No other input, maybe an area to escalate to check for more info

[rain obsidian]

Whats the best data base for storing information?

[late tinsel]

What kind of information.

[rain obsidian]

Like variables and levels for discord.py

[harsh pulsar]

sqlite

[rain obsidian]

let me chec kit out

check it

[harsh pulsar]

@late tinsel will you want to flatten all this to a relational table for the data warehouse anyway? or are you intending to leave it as a structured "document" in the warehouse?

[rain obsidian]

Any others?

[harsh pulsar]

not with the same desirable properties as sqlite:

• included with python
• the entire database is a single file
• doesn't require a separate server running
• uses sql for querying, which is mostly standardized and therefore has lots of learning resources online

[rain obsidian]

hmm

@harsh pulsar What about replit's database?

[harsh pulsar]

i was not aware that they had a database

[rain obsidian]

Me too, I found out like 2 days ago

[harsh pulsar]

however i would not use repl.it to host a discord bot or to store data

it's not what their service is for

[rain obsidian]

https://docs.replit.com/misc/database

[rain obsidian]

oh kk

[late tinsel]

@harsh pulsar I will be most likely looking to flatten these files. However doing something of a data lakehouse has crossed my mind, since I have been debating using databricks to replace any ssis pipelines and future pipelines, allowing for easier repository of all pipelines in one place versus each server has it's own ssis packages to maintain and region. Kind of centeralize all of it.

[harsh pulsar]

databricks is cool but it's a very big can of fat slimy worms

i wouldn't recommend it here

[late tinsel]

haha, I come from big data

And these guys are looking to collect everything

So it make sense from a pay now, versus pain later

plus spark isn't too bad to learn.

They want to utilize SAP b1, but abstract out a lot of the workflow by creating apps/power apps

Also start doing things like forecasting etc

But none the less, I am used to handling consumer data

So different mindset

[harsh pulsar]

i haven't ever used ERP software so i can't comment on b1

[late tinsel]

Realistically I think for there needs a postgres based data warehouse would work, or even maybe something like singlestore ( memsql ), since it is similar enough to snowflake, and especially using something like databricks, changing out the database is easy

You just change the sink realistically or can even do the data lakehouse and just cram everything into blob storage

[harsh pulsar]

abstracting away from the tooling, this is what i personally would feel good about:

• sftp upload -> data lake, xml files
• data lake, xml -> data warehouse, normalized relational data
• data warehouse -> app database (optional, omit if the data warehouse can handle queries from the app)

[late tinsel]

My original Idea for the data warehouse/data lakehouse was utilize

1. SQL to Blob storage via ADF in azure
2. Blob Storage to data warehouse/data lakehosue via databricks

[harsh pulsar]

i assume you meant XML in 1?

[late tinsel]

Im talking overall, so it will be a mixture of SQL ( SAP B1 ), XML from Cargo vendors, and APIs as well from where ever else

[harsh pulsar]

ah, hm

[late tinsel]

The worst thing about the SQL is that each region has its own sap b1 setup

So all of these will need to transformed into similar format via some form of pipeline either way, then converted to a dimensional model

The apps are more so for workflows, status updates etc

So it is weird for me because it is a mixture of oltp type apps, and olap type apps

[harsh pulsar]

honestly you know more than i do 😛 so all i will say is that from the data scientist perspective, just keep the original XML files somewhere and don't be surprised if one day someone actually wants to access them

[late tinsel]

Oh yeah, im a data engineer

Always keep raw format data somewhere.

If a data warehouse ever needs to be rebuilt, boom

its all there, if something isn't looking right, boom original is there

history as well

etc

[harsh pulsar]

yep exactly. what i will say is that at my last DS job, the data engineers used azure data factory and databricks for all the ETL, and databricks/dbfs itself was the data lake

[late tinsel]

My last place, we convereted a lot of data to either parquet or avro, but they would have to claw the raw file archive from my teeth lol

saved my ass so many times

[harsh pulsar]

hah exactly

[late tinsel]

Thats also including all the times a pipeline wasn't designed to add new columns lol

[harsh pulsar]

so the data that's in b1 is already in b1 and you aren't looking to migrate. but you are looking to ingest data from a bunch of heterogeneous sources into this data lakehouse, and then hook some of it up to an app (among other things)

[late tinsel]

Yeah

while creating apps that can talk between sap b1

and themselves

a lot of mixture of olap and oltp work loads

Literally just finished making app for ceneralized product code generation

Since codes were getting reused and causing issues lol

[harsh pulsar]

is databricks suitable for oltp? i would imagine not

[late tinsel]

No its not

olap

So we will have to have some form of sql somewhere

[harsh pulsar]

as for the data ingestion and warehousing, that does definitely sound like an adf/databricks use case. i haven't used snowflake but from what i hear it seems like a similar niche

[late tinsel]

Yeah ADF would be used to pull data out of sql

or sftp

Databricks would process it and either act as the warehouse, or push to snowflake

Pretty standard

[harsh pulsar]

i thought snowflake could do its own processing? having both databricks and snowflake sounds expensive

[late tinsel]

It can

Really depends what you are looking to do though

Databricks is pretty much just spark

So calling API's, enriching data etc is easier writing a python script

Albiet snowflake can call functions now anyhow, it's not the same.

Plus databricks integrates with damn near anything

[harsh pulsar]

very true

iirc the lower databricks tiers arent too expensive

[late tinsel]

So spark for the data proccessing, and then snowflake for the data warehouse

[harsh pulsar]

you dont need (or want) all the stupid notebook junk

[late tinsel]

Yeah, I can just use jupyter notebooks lol

Plus most people just write sql and then use something like tableau from my experience.

At least in the analytics we performed

[harsh pulsar]

so how does the data get stored in snowflake? it has a sql query engine?

[late tinsel]

My biggest goal with utilizing spark, is to get the fuck away from ssis

Yeah, you ever heard of redshift or bigquery?

[harsh pulsar]

yeah, used for very small things but never professionally

[late tinsel]

It is just a database designed for olap workloads

in essence

So bigquery, redshift, snowflake all in the same family

[harsh pulsar]

ah got it

so you'd probably have to dump that out to another database to hook the app up to

[late tinsel]

Yeah, for anything involving transactions or single record queries a sql/no-sql db should be used.

Snowflake is pretty strictly olap

So analytics workloads, big data etc

[harsh pulsar]

so your original question was whether to use nosql or sql? id personally always go sql if possible, postgres specifically just for the flexibility and huge feature set

[late tinsel]

Crazy expensive, as well as probably not as responsive to be quering something like Snowflake for user account data

I was thinking of using postgres, but at the same time I feel like maintnance would fall onto me

So I would probably see if azure has a managed service version of it, which they probably do

[harsh pulsar]

...is there an alternative that lets you push the maintenance off to someone else

[atomic kraken]

anyone here good with pymango

[harsh pulsar]

yeah im pretty sure azure has a managed rdbms offering, maybe not postgres specifically

probably some mssql thing....

[late tinsel]

haha the other thing I can use is ms sql

since they already have people maintaining, but I want to get away from ssis

Which, if it is there, there will be someone using it lol

[harsh pulsar]

do you have to use ssis if youre using mssql?

oh, you want to get everyone away from it

[late tinsel]

Yeah, I am a firm believer of do it 1 way.

[harsh pulsar]

https://azure.microsoft.com/en-us/products/azure-sql/

[late tinsel]

That way you don't end up with like a million different proccesses, different tooling, etc

You can move people around etc

Train once, and you are good

versus train the person everytime they move deparments lol

[harsh pulsar]

idk if this is any better, you might end up with vendor lock-in

yeah thats a big plus

[late tinsel]

They are 100% already going for vendor lockin

Looking to get rid of internal emails and use Teams

PowerApps

etc

[harsh pulsar]

ah the ole microsoft bought the vp dinner

[late tinsel]

I mean you can't beat ms when it comes to enterprise and integration.

It's like apple, shit just kinda works

[harsh pulsar]

yeah for sure. azure sql database seems like a good option then

[late tinsel]

https://azure.microsoft.com/en-us/pricing/details/postgresql/server/

[harsh pulsar]

oh thats way better

[late tinsel]

Yeah, I prefer postgres over ms sql

[harsh pulsar]

$0.10/GB/mo + $25/mo for a single core

[late tinsel]

Have to review, because these fuckers will get you with costs everywhere haha

Like network egress

[harsh pulsar]

what would you use to keep the app db in sync with the data warehouse?

nightly update from the latter to the former?

[late tinsel]

Depends on what is needed in the data warehouse

If a nightly is fine, adf can do it

https://hevodata.com/blog/postgresql-cdc/

Really depends on the data, we have had pipelines that push data from a queue to a SQL server as well to the data warehouse at the same time.

Queue -> 2 cloud functions ( GCP )

1. Cloud function for ingestion into SQL

2. cloud function for ingestion into bigquery

Others have been in the case of ms sql, is secondary replica

Secondary replica is updated within seconds, and is read only, there the data can be queried and moved to data warehouse without impacting main server performance

[harsh pulsar]

makes sense

[late tinsel]

Many solutions to the problem

[harsh pulsar]

good to know this stuff, i dont ever want to have to do it myself but its come in handy often to be able to at least sketch out a solution for someone

[late tinsel]

All depends on how much time and money you wanna throw at the problem haha

I feel like DS peeps should know some of DE

If not, thats how you get a million and one jupyter notebooks and not all the data you need.

Why the previous place I worked at went for a data warehouse, because they had like 50 different data sources, and no one knew what data existed and where. Made the DS guys less effective

Also, we were able to model the data into a format that made the DS guys very happy haha

They didn't need to wrangle anything, just do there magic haha

[harsh pulsar]

yeah i enjoy knowing enough to "be dangerous"

i can spin up my own postgres server, do my own web scraping, write my own api clients, etc. but when things get serious i really do want to hand it off to someone so i can focus on the actual ds work

then again im just a swe in my current job anyway so apparently i went the opposite direction...

[torn sphinx]

asyncpg.exceptions.PostgresSyntaxError: syntax error at or near "VARYING"

What this error mean?

[harsh pulsar]

@torn sphinx it means your query has incorrect syntax. post the code

[rocky pine]

How to import mySQL database driver to python?

[torn sphinx]

Okay 👍

I fixed it tho, thanks :)

[rocky pine]

?

[pale crow]

Hello, Any Python Libraries available for neo4j which supports optimistic locking of nodes like spring-data-neo4j?

How to handle concurrent transactions happening through python which might update same nodes simultaneously, which need to perform locking and avoid deadlocks and race conditions?

[elder vessel]

aiosqlite docs?

!pypi AIOSQLITE

[delicate field]

aiosqlite v0.17.0

asyncio bridge to the standard sqlite3 module

[pale crow]

Was this suggestion for my question?

[craggy olive]

Hi

Can anybody hear me

[kind halo]

hi

i am learning python

anybody interested to learn it with me

today's topic function

[silk storm]

if you are learning than you should first take a tutorial online or a class with others (remote of course)

[torn sphinx]

Hello

[rough hearth]

!mute 829895458855059456

[delicate field]

:incoming_envelope: :ok_hand: applied mute to @ripe moat until 2021-05-12 14:41 (59 minutes and 59 seconds).

[stray moss]

async def func_bitcoin():
  while True:
      db = cluster["CapBot"]
      collection = db["bitcoin"]
      users = await get_bitcoin_data()
      exchange_rate = users["exchange_rate"]
      if exchange_rate < 60000:
          if exchange_rate > 40000:
              users["exchange_rate"]+=random.randint(0,500)
              users["exchange_rate"]-=random.randint(0,500)
          else:
              users["exchange_rate"]+=random.randint(1500,2000)
      else:
          users["exchange_rate"]-=random.randint(1500,2000)
      collection.replace_one({"id":1},users)
      await asyncio.sleep(60)

this is pymongo

[half forum]

!paste

[stray moss]

the collection.replace_one() is running but it doesn't work, it doesn't replace

there's no errors either

[slow sand]

try to use _id instead of id

And be sure, that this document has _id == 1, not ObjectId or smth else

also, replace_one is awaitable method. You should use await collection.replace_one(...)

[stray moss]

bru typo moment? lemme check

[slow sand]

if you are using motor here, for sure

[stray moss]

omfg i actually did a typo

[iron drift]

pymongo.errors.ServerSelectionTimeoutError: dsbot1-shard-00-01.fl8o7.mongodb.net:27017: [WinError 10054] The remote host forcibly dropped an existing connection, connection closed, dsbot1-shard-00-02.fl8o7.mongodb.net: 27017: [WinError 10054] The remote host forced an existing connection, Timeout: 30s, Topology Description: <TopologyDescription id: 609bf8ffa44b31621c7a9bd4, topology_type: ReplicaSetNoPrimary, servers: [<ServerDescription.net ('dsbongbod8-s ', 27017) server_type: Unknown, rtt: None, error = AutoReconnect (' connection closed ')>, <ServerDescription (' dsbot1-shard-00-01.fl8o7.mongodb.net ', 27017) server_type: Unknown, rtt: None, error = AutoReconnect ('dsbot1-shard-00-01.fl8o7.mongodb.net:27017: [WinError 10054] The remote host forcefully dropped the existing connection')>, <ServerDescription ('dsbot1-shard-00-02.fl8o7 .mongodb.net ', 27017) server_type: Unknown, rtt: None, error = AutoReconnect (' dsbot1-shard-00-02.fl8o7.mongodb.net:27017: [WinError 10054] The remote host forcefully dropped the existing connection ')>]>

how do i fix this?

[wet stump]

Helllo help me

[wet stump]

IN sql

[jaunty galleon]

Send your question

[wet stump]

I need to List the employees with the lowest salary per each department

FROM employees e, departments d
WHERE e.department_id=d.department_id and salary IN  
( SELECT MIN(salary)  
FROM employees  
GROUP BY department_id);```

sql

SELECT first_name, last_name, salary, department_name  
FROM employees e, departments d
WHERE e.department_id=d.department_id and salary IN  
( SELECT MIN(salary)  
FROM employees  
GROUP BY department_id);```

Result is not okey because I am getting several different employees in same department

[jaunty galleon]

You want the lowest one?

[wet stump]

List the employees with the lowest salary per each department

As I understand I should get employee who has lowest salary in that deparmtnet

[proven arrow]

You can group the data

By department

[harsh pulsar]

switching to JOIN syntax because i think it looks better in this case:

SELECT
  t.employee_id,
  t.first_name,
  t.last_name,
  t.salary,
  t.department_name
FROM (
  SELECT
    e.employee_id,
    e.first_name,
    e.last_name,
    e.salary,
    d.department_name,
    ROW_NUMBER() OVER (
      PARTITION BY d.department_id
      ORDER BY e.salary
    ) salary_rank
  FROM
    employees e,
    departments d
  WHERE
    e.department_id = d.department_id
) t
WHERE
  t.salary_rank = 1

maybe something like this?

when asking sql questions like this, it helps a lot if you can provide example data via https://db-fiddle.com

and ideally also state what rdbms you're using

[wet stump]

how

SELECT last_name AS "Last Name",
       salary AS "Min. Salary",
       department_id AS "Dpt#"
FROM employees
WHERE (department_id, salary) IN (
                                    SELECT department_id,
                                           MIN(salary)
                                    FROM employees
                                    GROUP BY department_id
                                 )```

Here is another way

[harsh pulsar]

note that the tuple IN syntax doesn't work in sqlite and i think also ms sql server

but yeah that's pretty slick, i think it works in postgres

[wet stump]

I am using this in sqllite

[wet stump]

Can I inbox you?

[proven arrow]

Like salt said you should provider some sample data and example database in a fiddle. If you want help I’m sure you can at least put that much effort in to make someone’s life easier? 👍🏻

And makes it easy for you too to get help

[harsh pulsar]

https://www.db-fiddle.com/f/ksJCEUGqw4Tn6SJ8UUzKs7/1

[proven arrow]

You can do something like:

SELECT * FROM employees where (salary) in (select min(salary) from employees group by department_id);

[wet stump]

ok I solved it thanks

SELECT department_name AS 'Department Name', 
COUNT(*) AS 'No of Employees' 
FROM departments 
INNER JOIN employees 
ON employees.department_id = departments.department_id 
GROUP BY departments.department_id, department_name 
ORDER BY department_name;```

Is that ok for List all the departments and the number of employees per each department

[proven arrow]

Is that for a different question or the same as before?

[wet stump]

Different

[harsh pulsar]

is this homework

[wet stump]

No I am practicing

[proven arrow]

Yeah it’s fine. Have you tried yet to see the result?

[wet stump]

I tried but having doubts

[proven arrow]

Well it’s fine. Do you understand what’s going on with it?

[wet stump]

I am not getting all deparmtnets

Here much more department

[proven arrow]

Because then those departments wont have any employees

[wet stump]

How department can't have employees

SELECT city, COUNT(*) FROM employees
INNER JOIN departments  ON departments.department_id = employees.department_id
INNER JOIN locations ON locations.location_id = departments.location_id
GROUP BY city ```

Getting number of employees in each city

My question why Kaunas not appeared

[proven arrow]

Looking at how you post the questions doesn't look like your trying to learn. 😆
Looks more like homework.

But if you want to get departments with 0 employee count then use left join

SELECT departments.name, COUNT(employees.department_id)
FROM departments 
LEFT JOIN employees ON departments.id = employees.department_id
GROUP BY departments.id;

If your wondering why this works then its because, this type of join will return NULL for those departments that do not have a employee. And so the count function which aggregates will not count NULL values and so you get zero for it.

https://blog.codinghorror.com/a-visual-explanation-of-sql-joins/

[wet stump]

So without left join I was got departments with number excepting null?

[proven arrow]

Read that article I linked, it explains it with nice diagrams.
And you can run the different joins locally on your pc to get a better understanding of how it joins.

[wet stump]

why I am not getting all ciyies?

[stray moss]

Traceback (most recent call last):
  File "/opt/virtualenvs/python3/lib/python3.8/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 "main.py", line 20, in <module>
    cluster = MongoClient('mongodb+srv://##########@clustercapitalism.gtqj1.mongodb.net/myFirstDatabase?retryWrites=true&w=majority')
  File "/opt/virtualenvs/python3/lib/python3.8/site-packages/pymongo/mongo_client.py", line 639, in __init__
    res = uri_parser.parse_uri(
  File "/opt/virtualenvs/python3/lib/python3.8/site-packages/pymongo/uri_parser.py", line 500, in parse_uri
    nodes = dns_resolver.get_hosts()
  File "/opt/virtualenvs/python3/lib/python3.8/site-packages/pymongo/srv_resolver.py", line 102, in get_hosts
    _, nodes = self._get_srv_response_and_hosts(True)
  File "/opt/virtualenvs/python3/lib/python3.8/site-packages/pymongo/srv_resolver.py", line 83, in _get_srv_response_and_hosts
    results = self._resolve_uri(encapsulate_errors)
  File "/opt/virtualenvs/python3/lib/python3.8/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'

guys what's this error?

[slow sand]

Hi, try to install dnspython, should help

[final trench]

What would be an SQL trigger to check if a ticket is closed, and then raise an exception if it is?

[proven arrow]

What do you mean if ticket is closed?

[final trench]

I basically have a table of tickets, and I need to make it so if the ticket status is closed, then no update can happen to it

[proven arrow]

Well you have 2 options. One is to have a trigger like you say, the other is to handle it application side.

[final trench]

I need it through a trigger, but just have no idea what to do

EG, here is a short version of what I'm doing:

INSERT INTO Ticket(TicketID, Status) VALUES (1, 'Open');
INSERT INTO Ticket(TicketID, Status) VALUES (2, 'Closed');

And then for the closed ticket, it wouldn't let you update:

INSERT INTO TicketUpdate (TicketUpdateID, TicketID) VALUES (1000, 1);
INSERT INTO TicketUpdate (TicketUpdateID, TicketID) VALUES (1001, 2);

the second one should raise an exception as it's closed

[torn sphinx]

ok

[final trench]

Is it something like this?

"CREATE TRIGGER name BEFORE INSERT ON TICKETUPDATE EXECUTE PROCEDURE procedurename()"

[torn sphinx]

umm i’m trying to use pymongo for the first time

i have no clue

what i’m doing

[stray moss]

i did

[proven arrow]

There are examples given here, on triggers. https://www.postgresql.org/docs/current/sql-createtrigger.html
And with this you can use BEFORE UPDATE

[final trench]

I'll take a look and see if I can find anything, have been stuck on this for hours lol

[proven arrow]

Did you try any triggers yet?

If so you can send here

[final trench]

Not really, haven't been sure where to start. I need to read up on it more, have just had this sitting in the editor: CREATE TRIGGER CHECKCLOSED BEFORE INSERT ON TicketUpdate CHECK Status = 'Closed'

[torn sphinx]

I am new to databases so I am just playing around atm, but I got an error.

    @commands.command()
    @commands.has_role(823787908678942721)
    async def database(self,ctx,  user: discord.Member = None, *,reason = None):
        post = {"_id":{user.id}, "username": {user.name}, "reason":{reason}}
        await ctx.send("Updated")

        collection.insert_one(post)

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

Traceback (most recent call last):
File "C:\Users\leogr\AppData\Local\Packages\PythonSoftwareFoundation.Python.3.9_qbz5n2kfra8p0\LocalCache\local-packages\Python39\site-packages\discord\ext\commands\bot.py", line 939, in invoke
await ctx.command.invoke(ctx)
File "C:\Users\leogr\AppData\Local\Packages\PythonSoftwareFoundation.Python.3.9_qbz5n2kfra8p0\LocalCache\local-packages\Python39\site-packages\discord\ext\commands\core.py", line 863, in invoke
await injected(*ctx.args, **ctx.kwargs)
File "C:\Users\leogr\AppData\Local\Packages\PythonSoftwareFoundation.Python.3.9_qbz5n2kfra8p0\LocalCache\local-packages\Python39\site-packages\discord\ext\commands\core.py", line 94, in wrapped
raise CommandInvokeError(exc) from exc
discord.ext.commands.errors.CommandInvokeError: Command raised an exception: AttributeError: module 'pymongo.collection' has no attribute 'insert_one'

[stray moss]

dnspython or dnspython3?

[torn sphinx]

can someone send a vid on how to setup pymongo

@torn sphinx

https://youtu.be/rE_bJl2GAY8

[slow sand]

without 3. dnspython. It is already for py3 now

[dim sparrow]

Hi, i need help regarding PCA implementation in python, can anyone guide me?

[stray moss]

ohhh that's why it didnt work

[wet stump]

Hi help me

[proven arrow]

Your on the correct lines. You might want to make a function which checks your conditions for each row.

So the trigger would be:

CREATE TRIGGER check_update
    BEFORE INSERT ON TicketUpdate
    FOR EACH ROW
    EXECUTE FUNCTION check_ticket_status();

And you would make a function for it,

CREATE OR REPLACE FUNCTION check_ticket_status()
  RETURNS trigger AS
$$
-- declare current ticket status here;

begin
    -- get ticket status of ticket with a query
    if condition then
        RAISE EXCEPTION 'Failed to update ticket';
    end if;
    return NEW;
end;
$$
  LANGUAGE plpgsql;

You just need to add the conditions you like to check, and a query to get the current ticket status

[dense barn]

@welcome.command()
@commands.guild_only()
@commands.has_permissions(manage_channels=True)
async def channel(ctx):
    db = sqlite3.connect(db_path)
    cursor = db.cursor()
    cursor.execute(f"SELECT channel_id FROM welcome WHERE guild_id = ?",(ctx.guild.id))
    result = cursor.fetchone()
    if result is None:
        sql = ("INSERT INTO welcome(guild_id, channel_id) VALUES(?,?)")
        val = (ctx.guild.id,ctx.channel.id)
        await ctx.send(f"Welcome channel has been set to {ctx.channel.mention}")
        cursor.execute(sql,val)
        db.commit()
        cursor.close()
        db.close()
``` idk why this does not store the guild id and channel id? it works but it just does not insert it into the db

[arctic badger]

i'd suggest doing all the sql together.. and not having that await ctx.send in the middle of the process.
also, don't know how that works.. but i typically do

sql = (f"INSERT INTO welcome(guild_id, channel_id) VALUES({ctx.guild.id}, {ctx.channel.id})")
cursor.execute(sql)
db.commit()
cursor.close()
db.close()

[dense barn]

then how do i notify them that it has set the channel?

[hexed estuary]

personally I'd go further and use context managers instead of closing stuff manually

[arctic badger]

Uh, just send the message after u close the database 😛

[arctic badger]

Yea, idek what that is haha
Please, if u could, show how you’d do it here ^-^

[hexed estuary]

roughly like that:

con = sqlite3.connect(db_path)
with con:
    # equivalent to creating a cursor and executing it:
    result = con.execute("SELECT channel_id FROM welcome WHERE guild_id = ?",(ctx.guild.id)).fetchone()
    if result is None:
        con.execute("INSERT INTO welcome(guild_id, channel_id) VALUES(?,?)",(ctx.guild.id,ctx.channel.id))
    await ctx.send(f"Welcome channel has been set to {ctx.channel.mention}")
con.close()

If I didn't miss anything, that's equivalent to the entire thing

notice how I don't need to commit because the with-block automatically wraps the thing in a transaction.

It's described here, at the end of the docs:
https://docs.python.org/3/library/sqlite3.html#using-the-connection-as-a-context-manager

[dense barn]

ohh

that actually looks cleaner

[hexed estuary]

And in fact, right above it is the con.execute thing mentioned:
https://docs.python.org/3/library/sqlite3.html#using-shortcut-methods

Using the nonstandard execute(), executemany() and executescript() methods of the Connection object, your code can be written more concisely because you don’t have to create the (often superfluous) Cursor objects explicitly. Instead, the Cursor objects are created implicitly and these shortcut methods return the cursor objects.

[half forum]

Wait @hexed estuary is there a way to send things from a DB (sqlite3) when a user says $applications?

[arctic badger]

Hmmm....
Is there a way to specify the global settings for a mysql connection with pymysql?
Like when I run my program, it'll do something like pymysql.connect(pymysql.details, autocommit=True)

Basically, (I think) I need to run my program, give it the login details via a file, but then delete the file right after.

[pure sleet]

use environment variables

[torn sphinx]

poo poo

[torn sphinx]

i use mysql as my database, it has come to my knowledge that its table can only have 100 rows

not more than that

any way to change this?

[burnt turret]

100 rows sound awfully small, i don't think a limit like that exists?

[torn sphinx]

ahh mb its around 65000 bytes

if like every row is 10 bytes

we get 6500 rows

which should be fine

for the time being

[burnt turret]

even that sounds extremely small

that 65000 bytes number you're seeing looks like it's the maximum row size

not the maximum table size

[torn sphinx]

hmm okay ty

[elder vessel]

hey

which DB is best for Currency DBot?

[wise goblet]

the currency is real of fake one

what is expected amount of users?

[elder vessel]

Infinite IG

as I don't limit users to use my bot

I use sqlite3 now

but as sometimes bot restarts it looses some data

any gud suggestions?

[wise goblet]

you did not answer questions

[harsh pulsar]

losing data is unlikely to be the fault of sqlite

[elder vessel]

IG so

but how

i always put commit() after any change

but still

😢

[elder vessel]

what ques?

[wise goblet]

oh... you have commit system

[elder vessel]

hmm

sqlite3 has commit()

[wise goblet]

I just enable sqlite3 in mod 'autocommit' usually

makes no need for commits

[elder vessel]

?

[wise goblet]

alternatively you can use with construction

to ensure commit always happens

a moment, I'll try to find code

[elder vessel]

ok

[wise goblet]

!e

from contextlib import contextmanager


@contextmanager
def autocommit():
    try:
        yield
    finally:
        print("insert commit command here")


with autocommit():
    print("inserting data to db")

[delicate field]

@wise goblet :white_check_mark: Your eval job has completed with return code 0.

001 | inserting data to db
002 | insert commit command here

[wise goblet]

this can make sure that commit always happens during your operations

or alterantively you can seek how to enable auto commit for all sqlite commands

[elder vessel]

ok

[wise goblet]

db = SQLAlchemy(session_options={'autocommit': True})

[elder vessel]

?

whats ^?

[wise goblet]

how to open connection to sqlite with auto commit

but

it will not allow you to make transaction manipulations

rollback and etc

this is useful only if you aren't using them

[elder vessel]

but DML commands would work?

[wise goblet]

what is DML

[elder vessel]

Data Manipulation Language

[wise goblet]

ORM?

[elder vessel]

like update

[wise goblet]

yeah, sure it will work

auto commit makes every your command being a short auto session

[elder vessel]

BTW what is SQLAlchemy

[wise goblet]

automatically update
becomes short hidden version of
session.open
do update
session. commit

[elder vessel]

hmm

[wise goblet]

there are two ways to work with databases, raw SQL
SELECT * FROM TABLE WHERE BLA BLA BLA
and ORM (SQLAlchemy provides it for example, and usually integrated into flask)
commodity.objects.all().filter(name='123').first()

[elder vessel]

well this ORM kinda looks hard 😅

[wise goblet]

actually it is pretty nice and easy

it allows making... auto migrating system

everytime when you need to choose tables, you can change them based on your code project

without lose of your data

[elder vessel]

hmm

[wise goblet]

or any difficult operations

most companies add to requirements: you must learn raw SQL, and not just ORM for dummies 😉

[elder vessel]

but raw SQL is way more easy 🙂

[wise goblet]

nope. ORM is easier

it hides a lot of difficulties

[elder vessel]

like

[wise goblet]

it makes your SQL operations auto optimized
and as I said it has wonderful migrating system to auto create and auto alter your tables, you aren't needing to worry about it ever

[elder vessel]

hmm oki

🙂

[wraith shell]

Hi, is there a way to reflect the database limited to some tables. I know automap reflect is the way here with only parameter but my tables don’t have primary key defined so it only get picked up as a class when I do full scan. I can’t define primary key because those tables are materialised views created by someone else.

[wise goblet]

all I do... just insert makemigrations and then write migrate

it creates tables and their alterations

based on my classes in code)

[wet stump]

hello help me

In database sqllite

?

List the lowest and highest salary per city, the total number of employees per city, the number of employees earning the lowest salary, the number of employees earning the highest salary, the percentage of employees earning the highest salary per city and the percentage of employees earning the lowest salary per city. This information should be shown as 1 row per city containing all the information requested.

SELECT a.city
 , a.Employees_Number
 , a.Employees_Min_Number
 , (a.Employees_Min_Number / a.Employees_Number * 100) AS Min_Rate
 , a.Employees_Max_Number
 , (a.Employees_Max_Number / a.Employees_Number * 100) AS Max_Rate
FROM (
 SELECT x.city, x.Employees_Number
  , (
    select count(*) 
    FROM locations INNER JOIN departments
    ON locations.location_id=departments.location_id INNER JOIN employees
    ON departments.department_id=employees.DEPARTMENT_ID INNER JOIN jobs
    ON employees.job_id=jobs.JOB_ID
    where locations.city = x.city and employees.salary = x.Min_Salary
  ) AS 'Employees_Min_Number'
  , (
    select count(*) 
    FROM locations INNER JOIN departments
    ON locations.location_id=departments.location_id INNER JOIN employees
    ON departments.department_id=employees.DEPARTMENT_ID INNER JOIN jobs
    ON employees.job_id=jobs.JOB_ID
    where locations.city = x.city and employees.salary = x.Max_Salary
  ) AS 'Employees_Max_Number'
 FROM (
   SELECT locations.city, 
    COUNT(employees.employee_id) as 'Employees_Number',
    min(employees.salary) as 'Min_Salary', 
    max(employees.salary) as 'Max_Salary'
   FROM locations INNER JOIN departments
   ON locations.location_id=departments.location_id INNER JOIN employees
   ON departments.department_id=employees.DEPARTMENT_ID INNER JOIN jobs
   ON employees.job_id=jobs.JOB_ID
   GROUP by locations.city
 ) x
) a;```

How to add to select lowest, highest salary per city?

I tried add in select Min_Salary, Max_Salary but not works

[simple kelp]

How would I perform aiosqlite.connect() inside __init__ of a class?

[fast whale]

I have a discord bot and I use postgresql to store data , Right now I am trying to use tortoise orm , I have a lot of tables made under different schemas (i made different schemas because I have a lot tables and everything under public schema looked ugly.)

my question is how do I specify schemas in tortoise-orm, like I have a schema called guild and i have two tables under it , then I have a schema called user with two tables under it .
now how do I use all these 4 tables?

[wet stump]

Hi helpm e

[green raptor]

Hey guys i am stuck i hope someone can help
Database is sqlite

Database:
# CREATE TICKET IN DATABASE async def create_ticket_db(userid, issue): sql.execute("INSERT INTO SupportTickets (UserId, Issue) VALUES (" + str(userid) + "," + issue +")") conn.commit()

Command:
# OPEN TICKET @bot.command() async def ticket(ctx, *, issue = None): userid = ctx.message.author.id if ctx.author != bot.user: # CHECK IF TICKET IS EMPTY if issue == None: # ERROR - NO TICKET IS EMPTY await ctx.send("Der Inhalt deines Tickets sollte nicht leer sein: ticket <grund>") return # Create Support-Ticket await create_ticket_db(userid, issue) # CREATE SUPPORT-TICKET-CHANNEL channelname = "ticket-" + str(await get_support_ticket_id(userid)) await ctx.guild.create_text_channel(channelname) # set channel permission # send embed ticket message else: return

Error: no such column: test

TicketId is Ai and Unique

[wet stump]

Hi help me in sqllite

[pure sleet]

?

[wet stump]

SELECT a.city
  , a.Employees_Number
  , a.Employees_Min_Number
  , (a.Employees_Min_Number / a.Employees_Number * 100) AS Min_Rate
  , a.Employees_Max_Number
  , (a.Employees_Max_Number / a.Employees_Number * 100) AS Max_Rate
FROM (
  SELECT x.city, x.Employees_Number
    , (
        select count(*) 
        FROM locations INNER JOIN departments
        ON locations.location_id=departments.location_id INNER JOIN employees
        ON departments.department_id=employees.DEPARTMENT_ID INNER JOIN jobs
        ON employees.job_id=jobs.JOB_ID
        where locations.city = x.city and employees.salary = x.Min_Salary
    ) AS 'Employees_Min_Number'
    , (
        select count(*) 
        FROM locations INNER JOIN departments
        ON locations.location_id=departments.location_id INNER JOIN employees
        ON departments.department_id=employees.DEPARTMENT_ID INNER JOIN jobs
        ON employees.job_id=jobs.JOB_ID
        where locations.city = x.city and employees.salary = x.Max_Salary
    ) AS 'Employees_Max_Number'
  FROM (
      SELECT locations.city, 
        COUNT(employees.employee_id) as 'Employees_Number',
        min(employees.salary) as 'Min_Salary', 
        max(employees.salary) as 'Max_Salary'
      FROM locations INNER JOIN departments
      ON locations.location_id=departments.location_id INNER JOIN employees
      ON departments.department_id=employees.DEPARTMENT_ID INNER JOIN jobs
      ON employees.job_id=jobs.JOB_ID
      GROUP by locations.city
  ) x
) a;```

List the lowest and highest salary per city, the total number of employees per city, the number of employees earning the lowest salary, the number of employees earning the highest salary, the percentage of employees earning the highest salary per city and the percentage of employees earning the lowest salary per city. This information should be shown as 1 row per city containing all the information requested.

How to add in select finding lowest, highest salary per city?

[dense barn]

@welcome.command()
@commands.has_permissions(manage_channels=True)
@commands.guild_only()
async def channel(ctx):
    db = sqlite3.connect(db_path)
    cursor = db.cursor()
    cursor.execute("SELECT channel_id FROM welcome WHERE guild_id = ?",(ctx.guild.id, ))
    result = cursor.fetchone()
    print (result)
    if result is None:
        sql = ("INSERT INTO welcome(guild_id, channel_id) VALUES(?,?)")
        val = (ctx.guild.id, ctx.channel.id)
        await ctx.send(f"Welcome channel has been set to {ctx.channel.mention}")
    elif result is not None:
        sql = ("UPDATE welcome SET channel_id = ? WHERE guild_id = ?")
        val = (ctx.channel.id, ctx.guild.id)
        await ctx.send(f"Welcome channel has been updated to {ctx.channel.mention}")
    cursor.execute(sql, val)
    db.commit()
``` any idea whats wrong with this? The whole thing works it also sends the message but it wont insert it into the db.

[round tinsel]

@dense barn
One good habbit to get into for any database management is to use a context manager to ensure the db gets closed.

with sqlite3.connect(db_path) as db:

This ensures that even on an error, you still do a db.close() which I do not see in the code you posted. After the db.commit() there should be a cursor.close().

[dense barn]

ahh

[round tinsel]

Not sure if the closes are what is causing the problem but it's a good place to start

[dense barn]

ok tq

[round tinsel]

If you use the with statement, you don't need to do the db.close()

[dense barn]

ye i tried that but didnt quit works so i just came back to that

[round tinsel]

If you do any other database stuff in your code and don't have close() statements, it's likely that the db is being held open by whatever is first accessing it

[stray moss]

Traceback (most recent call last):
  File "c:/Users/user/Desktop/Python Capitalism/webserver.py", line 1, in <module>
    from quart import Quart, render_template, request, session, redirect, url_for
  File "C:\Users\user\Desktop\Python Capitalism\venv\lib\site-packages\quart\__init__.py", line 3, in <module>
    from jinja2 import escape, Markup
  File "C:\Users\user\Desktop\Python Capitalism\venv\lib\site-packages\jinja2\__init__.py", line 9, in <module>
    from .bccache import BytecodeCache
  File "C:\Users\user\Desktop\Python Capitalism\venv\lib\site-packages\jinja2\bccache.py", line 24, in <module>
    from .utils import open_if_exists
ImportError: cannot import name 'open_if_exists' from 'jinja2.utils' (C:\Users\user\Desktop\Python Capitalism\venv\lib\site-packages\jinja2\utils.py)
PS C:\Users\user\Desktop\Python Capitalism>

guys i can't import quart?

or flask either..

[round tinsel]

Maybe you removed a dependancy that it needs? Have you tried removing quart and reinstalling it to your virtual environment?

It looks like it's not finding something from jinja2

[strong parrot]

Hi, someone knows how to change different schema PostgreSQL in SQLAlchemy orm ? i tried all stackoverflow answer but no one work, maybe someone here sucess ?

[wraith shell]

Guys, is there a book and or a better documentation to understand SQLAlchemy (both core and ORM). I really want to go down the rabbit hole but the documentation is so confusing for some reason.

Hi

[opal dawn]

https://www.tutorialspoint.com/sqlalchemy/index.htm

[wet stump]

Hi help me

[wet stump]

I need help in data modeling

Each hotel (of which you want to store your name, address, telephone, year of construction, etc.) is classified in a category (for example, three stars) being able to lower or increase of category.
Hotels have different kinds of rooms (suites, doubles, singles, etc.), which are numbered so that you can easily identify the floor you are in. So, for each room you want to save the code and type of room.
Individuals can make reservations for hotel rooms. The name, address and telephone number will appear in the reservation.
Travel agencies can also make reservations for the rooms. In the event that the reservation is made by a travel agency, the same data as for individuals was needed, in addition to the name of the person for whom the travel agency is making the reservation.
In the previous cases you should also store the price of the reservation, the date of start and the end date of the reservation.

what could be primary key in hotel table?

[dense barn]

how can i get sliqte3 latency?

[opal dawn]

Primary keys are typically just ints or bigints

[wet stump]

Yeah but according the description of model what I have to choose?

[opal dawn]

Use a bigint

As long as your primary keys are unique

Which I think they are by default

[opal dawn]

You might want make the hotel table's primary key act as a foreign key in the room table, and room table primary key a foreign key in the reservation table

[wet stump]

really?

how

[opal dawn]

Idk, does your modeling tool have a way to depict foreign key relationships?

[wet stump]

https://app.diagrams.net/

I am using this

[opal dawn]

Do you plan to write SQL for this, or use an ORM?

[wet stump]

No

I just wanted to create data model according to description

I am not sure is it ok

[opal dawn]

Your diagram looks correct from what I can see

I'd recommend using PonyORM diagramming tool: https://editor.ponyorm.com/

It's more comprehensive, and will actually generate SQL for you in several dialects in case you'd want to test it out.

[proven arrow]

How does it work? What’s a custom reward role?

[proven walrus]

    "retiredJerseyNumbers": ["number" : "99" ,"seasonRetired" : 2000 , "seasonTeamInfo" : 1999 , "pid" : 9 , "text" : "Wayne Gretzky's number has been retired league wide, in honour of the great one."],```
What's wrong with this?

[proven arrow]

How complex of a system do you want?

For a proper system I would expect many more tables, than what you have.

[wet stump]

Ok what tables more?

[dense barn]

any idea how i can get sqlite3 latency?

[final trench]

Can someone explain why this doesn't work?

Priority INTEGER CHECK (Priority <0 AND Priority <4),

Table row called Priority. Is this not a correct check to have the value be between 1 - 3?

I also did Priority INTEGER CHECK (Priority <=1 AND Priority <=3), but that didn't work either

what's wrong?

[shell ocean]

no idea what DB that is but at face value

don't you mean >0?

[final trench]

oh jesus christ

yup and that works

thanks @shell ocean

i'm an idiot

[shell ocean]

yw 👋

[slender roost]

How to count and query in a single line instead of doing this?

Solved, .count()

[waxen halo]

@slender roost is that using sqlalchemy?

[slender roost]

Yes sir

[graceful crown]

Hello, How can I make the models such that the admin role sets the values (ex: upper bound, lower bound) and all the staff roles gets these values when they create a record (admin.upper_bound, etc)?

[dense barn]

@welcome.command()
@commands.has_permissions(manage_channels=True)
@commands.guild_only()
async def channel(ctx):
    db = sqlite3.connect(db_path)
    cursor = db.cursor()
    cursor.execute("SELECT channel_id FROM welcome WHERE guild_id = ?",(ctx.guild.id, ))
    result = cursor.fetchone()
    print (result)
    if result is None:
        sql = ("INSERT INTO welcome(guild_id, channel_id) VALUES(?,?)",(ctx.guild.id, ctx.channel.id))
        await ctx.send(f"Welcome channel has been set to {ctx.channel.mention}")
    elif result is not None:
        sql = ("UPDATE welcome SET channel_id = ? WHERE guild_id = ?",(ctx.channel.id, ctx.guild.id))
        await ctx.send(f"Welcome channel has been updated to {ctx.channel.mention}")
    cursor.execute(sql)
    db.commit()
    cursor.close()
    db.close()

``` any idea why this wont store the guild id and channel id?

[wintry stream]

Postgres question: I have this code to get the first 10 sorted rows from the database, what's the correct way to get the second 10 rows?

data = await con.fetch("SELECT * FROM levels ORDER BY xp DESC LIMIT 10")

[wintry stream]

i am not known with sqlite3 so i won't be able to assist with that, I do have some other tips for your code. For the if statement you can change the elif to else. Also see if you can switch to aiosqlite, sqlite3 is synchronous and is massively blocking your entire bot whenever you do any database operation. aiosqlite is async and won't have this issue

[dense barn]

ok

[harsh pulsar]

don't use LIMIT in the query, instead fetch 10 rows at a time from the cursor. this is the only time when it is acceptable to reuse a cursor. is this aiosqlite?

[wintry stream]

asyncpg

so postgres

[harsh pulsar]

ah, asycpg does things in a nonstandard way

let me see their docs

[wintry stream]

i have a connection pool

that's the wrong query whoops

async with self.bot.pool.acquire() as con:
  data = await con.fetch("SELECT * FROM levels ORDER BY xp DESC LIMIT 10")

[harsh pulsar]

oh wait, in postgress you can use OFFSET

[wintry stream]

ah didn't know that

i'll take a look

[harsh pulsar]

async def fetch_page(self, skip=0, limit=10):
    query = "SELECT * FROM levels ORDER BY xp DESC " \
            f"LIMIT {limit} OFFSET {skip}"
    async with self.bot.pool.acquire() as con:
        data = await con.fetch(query)

i'm not sure if you can use query parameters in LIMIT and OFFSET clauses, so i used an f-string. but try it with parameters, that would be preferable and safer

[wintry stream]

afaik you can use params anywhere with $n ($1, $2, $3, etc.)

i'll test it out

thanks

and i'll keep limit as is

[harsh pulsar]

usually you can't use it with tables or column names

[wintry stream]

oh also i see a lot of people make the query first and store it in a variable, then execute it purely with the variable

are there any benefits for this method? or just personal preference?

[harsh pulsar]

you have more freedom when formatting it

line breaks, triple-quoting, etc

sql is ugly enough already

[wintry stream]

okay fair point

[smoky lodge]

hello everyone can i ask a question here?

[wintry stream]

i mean this is a channel for on topic help and questions and other discussions

[smoky lodge]

oh ok so i can :)

i have more than 1 , multi dimensional numpy array and i need to transfer this to .xlsx file to make a dataset for using my ml project. As a first step i converted it to pandas dataframe and transfferred the data to xlsx file, but when i send to my 2. data its getting complicated therefore there is no index number to sign my multi dimensional arrays. Is there any solution for that

how can i specify this into code, for now computer doesnt know the difference between first and second index?

[wintry stream]

Hmm this doesn't seem exactly database related. I recommend going to another help channel so the correct people will see your question

#❓｜how-to-get-help

@smoky lodge

[austere portal]

Can I use the django ORM in non django projects?

[wintry stream]

@harsh pulsar this worked

    @commands.command(help="shows the top 10 for chat xp", aliases=["lb"])
    async def leaderboard(self, ctx, page=1):
        page = 1 if page<1 else page
        offset = (page-1)*10

        async with self.bot.pool.acquire() as con:
            data = await con.fetch("SELECT * FROM levels ORDER BY xp DESC LIMIT 10 OFFSET $1", offset)

[harsh pulsar]

Great

[harsh pulsar]

It's possible, but you still have to do all the top level django initialization. It's not really worth it. Use Sqlalchemy instead for a similar experience.

[wintry stream]

i wonder what happens if the page number is too high

probably index out of range when indexing the data from the fetch as that would return an empty result

apparently it just works

and it remains empty

[austere portal]

Oh thanks, but I have seen the SQLAlchemy orm but the django orm just looks cleaner for me (my opinion)

[smoky lodge]

ok thanks

[harsh pulsar]

you can mess with database queries on ~~https://db-fiddle.net~~ (wrong url see below)

its a great tool

[wintry stream]

Doesn’t resolve name, I think the url is incorrect

[harsh pulsar]

oh sorry its http://db-fiddle.com

@wintry stream ^

[wintry stream]

Ah that one does resolve haha

Thanks

[remote plinth]

.

[half forum]

ok @ivory igloo

[ivory igloo]

yes

[remote plinth]

[half forum]

Ok so

[half forum]

shoo

I use sqlite3, so go to this and I recommend downloading this https://sqlitebrowser.org.

[remote plinth]

you can continue lel

[half forum]

lol yes i know

[remote plinth]

i learned sql in https://sololearn.com @ivory igloo use it if u want

[half forum]

I learned through youtube

xD

[half forum]

no bad

i am teach sqlite3

it very easy

[remote plinth]

oke bye battery low ;-;

[ivory igloo]

i learned a bit of sql in school

also where on that page do i download that

[half forum]

lol go here https://sqlitebrowser.org

download that

and tell me when you have finished

are you there @ivory igloo ?

[ivory igloo]

i think im done now

[half forum]

Ok so now you are using vsc right or some ide other than replit?

[ivory igloo]

i havent heard of both of those

(._.)

[half forum]

What ide are you using?

if its one on the web... then never mind

if you have on one your machine, then lets continue

Go into your ide and create a new file called main.sqlite

Now open up the DB Brower for SQLite and click Open Database

Find main.sqlite and open it

tell me when you have reached here @ivory igloo

[ivory igloo]

i still dont get what an ide is

[half forum]

Ok so

[half forum]

Where do you code on.

[ivory igloo]

ahhh so vsc is visual studio code

[half forum]

yes

[ivory igloo]

wait lemme open that up

[half forum]

watch this video, if any errors come to me https://www.youtube.com/watch?v=Y9DzfPJsP2s

in #discord-bots

ok?

[stray moss]

              collection.update_one({"id":0},{"$set":{str(user.id):{"inventory":{n_:new_amt}}}})
              collection.update_one({"id":0},{"$set":{str(other.id):{"inventory":{n_:new_amt_2}}}})

guys, this doesn't do ANYTHING. But it runs through it and no errors appear. what's wrong?

[stray moss]

collection.replace_one({"id":0}, users) 

won't work either...

it just won't update the data..?

[slow sand]

What is the response of it? It should return UpdateResult object with some info

[stray moss]

So like result = collection.update.... and then print result

[slow sand]

Yes. Check 'matched_count' firstly there. Doc for the UpdateResult: https://pymongo.readthedocs.io/en/stable/api/pymongo/results.html#pymongo.results.UpdateResult

[stray moss]

i did
result = collection.update_one(whatever I had in here)
and it said
<pymongo.results.UpdateResult object at 0x000001DAB3FA9800>

what next?

[delicate wedge]

Hello Guys!
I am trying connect to a .dir file via the dbm module more specifically "dbm.ndbm"

import dbm.ndbm
with dbm.ndbm.open("C:\Users\Public\__DEFAULT",'r') as state_variables:
print (state_variables)
for key in state_variables:
print(key)

its outputting an error stating that "ModuleNotFoundError: No module named '_dbm'" and its orginating from the "with" statement
Am I not importing it using the "import dbm.ndbm"
Any help would be greatly appreciated?

[stray moss]

@slow sand hey so matched.count is 0....

for some reason

[harsh pulsar]

sounds like either:

1. ndbm isnt supported on your system
2. somehow the ndbm C backend module is missing or not compiled correctly

[delicate wedge]

Yeah I guess your right
its not supported on windows
if anybody wants more info: https://stackoverflow.com/questions/3408272/python-dbm-module-for-windows
let me know if any1 uses any other modules to connect to a DB file (.dir extension) in python

@harsh pulsar thanks!

[green raptor]

How do i get int from database?
i tried this:

    sql.execute("SELECT TicketId FROM SupportTickets WHERE UserId = " + userid)
    conn.commit()
    result = fetch[0][0]
    return result```

but not working

` ret = await coro(*args, **kwargs)
File "W:\SupportBot\bot.py", line 107, in ticket
channelname = "ticket-" + str(await get_support_ticket_id(userid))
File "W:\SupportBot\bot.py", line 54, in get_support_ticket_id
sql.execute("SELECT TicketId FROM SupportTickets WHERE UserId = " + userid)
TypeError: can only concatenate str (not "int") to str

The above exception was the direct cause of the following exception: discord.ext.commands.errors.CommandInvokeError: Command raised an exception: TypeError: can only concatenate str (not "int") to str`

[harsh pulsar]

!d g dbm

[delicate field]

dbm

Source code: Lib/dbm/__init__.py

dbm is a generic interface to variants of the DBM database — dbm.gnu or dbm.ndbm. If none of these modules is installed, the slow-but-simple implementation in module dbm.dumb will be used. There is a third party interface to the Oracle Berkeley DB.

[harsh pulsar]

There are other backends

[delicate wedge]

ill look ino this one
ive tried dbm.dumb, dbm.gnu

[slow sand]

It looks like you have a typo. Try '_id' instead of 'id'

[stray moss]

i just realized

sighs..

[slow sand]

:-)

[stray moss]

how do I stop making typos like "id"