databases

[harsh pulsar]

i won't deny that the sql language can be annoying and difficult to learn

but if you use e.g. sqlite you don't even need to run a separate database server!

a sqlite database is just a file

more than good enough for storing "low-traffic" data that doesn't change often, like server welcome messages

[steel lagoon]

well a file for every single one? or will it all go in one

[harsh pulsar]

no, the whole database is 1 file

[steel lagoon]

well can you help me set it up because i have no sql expericence

[harsh pulsar]

i also have skimmed the contents of https://sqlbolt.com and it looks like a great way to start learning sql

[steel lagoon]

tried that

[harsh pulsar]

that said, if you already wrote your code using mongodb then you might as well keep at it, but i strongly encourage changing your database layout to the one i recommended

your current layout is just going to give you headaches

[harsh pulsar]

and what did you struggle with?

[steel lagoon]

mostly all of it but i quess i'll start again do you mind helping me back on my feet to get back on track this error just set me back at least 10 days from my push date witch was today before it broke entirly

[harsh pulsar]

ok, so look at your current database layout. it seems like you should be doing something like this:

    @commands.Cog.listener()
    async def on_member_join(self,member : discord.member):
        guild_id = member.guild.id
        result = collection.find_one({"_id": guild_id})
        if result is None:
            # Handle the case if the guild id is missing
            # This should probably return an error message
            ...
        else:
            channel_id, welcome_message = result[guild_id]
            ...

the ... is the stuff where you construct the messages and send them to the channel

[steel lagoon]

interesting but would it work or what else would need to change in this code? ```py
@commands.Cog.listener()
async def on_member_join(self,member : discord.member):
guild_id = member.guild.id
result = collection.find_one({"_id": member.guild.id})
if result is None: return

    else:
        channel_id, welcome_message = result[guild_id]
        channel = self.client.get_channel(channel_id)
        first_message = welcome_messsage
        second_message = first_message.replace("{member.mention}",f"{member.mention}")
        third_message = second_message.replace("{member}",f"{member}")
        final_message = third_message.replace("{member.guild}",f"{member.guild}")
        await channel.send(f"{final_message}")```

[harsh pulsar]

server = results["_id"] you already have this, as guild_id 🙂

ok this is pretty confused

            server = results["_id"]
            channel = self.client.get_channel(server[0])

think: what is results["_id"] going to be?

[steel lagoon]

well that was from the original before i removed 2 for loops lol

[harsh pulsar]

well think now

[steel lagoon]

results["_id] would get what i would need but it is taken care of now

it is gone

[harsh pulsar]

answer the question

or show me the updated code

[steel lagoon]

i'm updating it as i go look at the box i just sent

[steel lagoon]

    @commands.Cog.listener()
    async def on_member_join(self,member : discord.member):
        guild_id = member.guild.id
        result = collection.find_one({"_id": member.guild.id})
        if result is None: return

        else:
            channel_id, welcome_message = result[guild_id]
            channel = self.client.get_channel(channel_id)
            first_message = welcome_messsage
            second_message = first_message.replace("{member.mention}",f"{member.mention}")
            third_message = second_message.replace("{member}",f"{member}")
            final_message = third_message.replace("{member.guild}",f"{member.guild}")
            await channel.send(f"{final_message}")```

@harsh pulsar

😦 left me hanging

[brave bridge]

Are you suggesting that they do joins in Mongo?

[steel lagoon]

no they are suggusting me to do it in sql

[harsh pulsar]

it sounds like they don't need a join at all for this

[brave bridge]

ah

[harsh pulsar]

the data is just in a wonky format, they aren't storing channels-as-documents

they are just storing ids anyway

but in a wonky nonsensical format

i am suggesting that sql forces you to make better choices about your database layouts, whereas mongodb lets you do ridiculous things

[brave bridge]

well

you can still store JSON in (many) SQL databases 😉

[steel lagoon]

lol

[fluid crystal]

anyone know how to make a list of all users in a mongodb?

for x in users.find_one():

like that kinda idea

[steel lagoon]

can i see what your inputing?

and it depends on why you need a list of all the users

[fluid crystal]

i dw show the db cause of info

but i can send the function

[torn sphinx]

you can end up with some very nonuseful data when you just serialize everything to jsons.

[fluid crystal]

its just trying to make a python list of all the fields

so i have multiple users in it

and i wanna make a list of their usernames to show who is in the db

[torn sphinx]

listing their usernames would be something you would do with a query of the table.
You would select * in users... etc and do something with that. That part isnt part of organizing the data tables and the fields.

i usually just use django orm these days if i need to model a database tbh

[fluid crystal]

ok thanks for letting me know

[torn sphinx]

@fluid crystal https://www.guru99.com/database-normalization.html

this made me understand the point of relational databases more

[fluid crystal]

ok glooks ill check it out after work

[torn sphinx]

nod

[half forum]

{'pilots': {}}

sorry for long response, I was in the car for 5 hours.

[toxic berry]

I am looking for the best postgresql database with python GUI

[steel lagoon]

@harsh pulsar can you help me further?

[lyric owl]

Could someone explain to my why my SQL OVERWRITE function isn't working? I get an error on this line:

c.execute(f"INSERT OVERWRITE inventory VALUES ({ctx.author.id}, {newgold}, {newexperience}, {newkills})")

    c.execute(f"INSERT OVERWRITE inventory VALUES ({ctx.author.id}, {newgold}, {newexperience}, {newkills})")
sqlite3.OperationalError: near "OVERWRITE": syntax error```

also, it doen't overwrite

all I'm looking for is to add two values to eachother

[unkempt prism]

I'm not that familiar with sqlite3 though I don't think OVERWRITE is a valid keyword. Do you want an upsert ? https://www.sqlite.org/lang_UPSERT.html

[lyric owl]

no i just figured that out lol

it works in other sql but not sqlite

so im using REPLACE

i'll look into the upsert

[lyric owl]

well im doing something right now

I'm getting a NoneType now

think if i use

if c.fetchone() > 0:
  c.execute(f"""INSERT OR REPLACE INTO inventory""")

that should work

[lyric owl]

it doesnt

it keeps giving me this error discord.ext.commands.errors.CommandInvokeError: Command raised an exception: TypeError: 'NoneType' object is not subscriptable
which i understand, but i dont understand why it is a NoneType

because i clearly inserted data into the table

[grim vault]

looks like you just want:

c.execute("INSERT OR REPLACE inventory VALUES (?,?,?,?)", (ctx.author.id, newgold, newexperience, newkills))

if the ctx.author.id is the primary key.

[lyric owl]

yes

ctx.author.id would be under user_id integer PRIMARY KEY

c.execute("""CREATE TABLE IF NOT EXISTS inventory (
            user_id integer PRIMARY KEY,
            gold integer,
            experience integer,
            kills integer
            );""")

[grim vault]

the if is not needed then.

The insert alone would error because of preexisting but adding the or replace will just overwrite any existing entry.

[lyric owl]

that's what im trying to do

        c.execute(f"""INSERT OR REPLACE INTO inventory (user_id, gold, experience, kills)
        VALUES ({ctx.author.id},
                {newgold},
                {newexperience},
                {newkills}
                );""")
        conn.commit()

this is what ur saying i should do

[grim vault]

f-strings BAD

[lyric owl]

but its still gonna read a NoneType

It has to be

[grim vault]

This doesn't read anything it just inserts data.

[lyric owl]

thats right

this inserts it

but

    @commands.command()
    async def pvalues(self,ctx):

        c.execute(f"""SELECT * FROM inventory WHERE user_id = {ctx.author.id}""")
        print(c.fetchone())
        await ctx.send(c.fetchone())

this should read it

anyways

when writing

it says NoneType is not subscriptable

[grim vault]

The first fetchone() will have read all there is, the second one will have nothing.

[lyric owl]

discord.ext.commands.errors.CommandInvokeError: Command raised an exception: TypeError: 'NoneType' object is not subscriptable

[grim vault]

    @commands.command()
    async def pvalues(self,ctx):

        c.execute("SELECT * FROM inventory WHERE user_id = ?", (ctx.author.id,))
        row = c.fetchone()
        print(row)
        await ctx.send(row)

[lyric owl]

is that still gonna read my ctx.author.id

let me try

I think it's not returning anything because it doesn't insert the data

[grim vault]

If row is None then it doesn't find data for that ctx.author.id, yes.

[lyric owl]

that's right

but

i dont think there is any data saved

because

when i run the command to save data

it gives me another nonetype error

[grim vault]

Well, how does you save command look like?

Which line is the error in?

[lyric owl]

It doesnt tell me which line

discord.ext.commands.errors.CommandInvokeError: Command raised an exception: TypeError: 'NoneType' object is not subscriptable

this is all

you want to see the entire command? or just the sql bit

[grim vault]

The entire command would be good.

[lyric owl]

    @commands.command(aliases=['adv'])
    async def adventure(self,ctx):
        kills=random.randint(0,20)
        experience=random.randint(2,20)
        goldloot=random.randint(1,500)
        totalexperience=(kills * experience)
        golddrop=(kills * random.randint(0,30))

        totalgold=(golddrop + goldloot)

        adventureEmbed=discord.Embed(title="Adventure", description="You went on an adventure!", color=CHEEZ)
        adventureEmbed.set_thumbnail(url=ctx.author.avatar_url)
        adventureEmbed.add_field(name="Gold 💰", value=f"You looted {totalgold} gold!", inline=1)
        adventureEmbed.add_field(name="XP ✨", value=f"You gained {totalexperience} xp!", inline=1)
        adventureEmbed.add_field(name="Kills ⚔️", value=f"{kills}", inline=1)
        await ctx.send(embed=adventureEmbed)

        c.execute(f"SELECT * FROM inventory WHERE user_id={ctx.author.id}")

        dbvalue=c.fetchone()
        dbgold=dbvalue[1]
        dbexperience=dbvalue[2]
        dbkills=dbvalue[3]

        newgold=(dbgold+totalgold)
        newexperience=(dbexperience+totalexperience)
        newkills=(dbkills+kills)

        c.execute(f"""INSERT OR REPLACE INTO inventory (user_id, gold, experience, kills)
        VALUES ({ctx.author.id},
                {newgold},
                {newexperience},
                {newkills}
                );""")
        conn.commit()

[grim vault]

Ok, I think your problem is the dbvalue[] if there is no entry if the command is called the first time for the user, you'll need to check that.

        ...
        c.execute("SELECT * FROM inventory WHERE user_id=?", (ctx.author.id,))
        dbvalue=c.fetchone()
        if dbvalue is None:
          dbgold=0
          dbexperience=0
          dbkills=0
        else:
          dbgold=dbvalue[1]
          dbexperience=dbvalue[2]
          dbkills=dbvalue[3]

something like that.

[lyric owl]

what does changing

c.execute(f"SELECT * FROM inventory WHERE user_id={ctx.author.id}")

to

c.execute("SELECT * FROM inventory WHERE user_id=?",(ctx.author.id,))

exactly do?

[grim vault]

You should use binding parameters for values and not insert them directly in the string. The ? is a placeholder which will be replaced by the value given in the second argument (which must be a tuple or list).

[lyric owl]

ah

welp

[grim vault]

If there is more than one value you just user more ?, see: <#databases message> for the insert I gave you.

[lyric owl]

now the string is no longer callable

[grim vault]

The second-last pin explains it.

[lyric owl]

ah great ty

[shy haven]

how do i write in and from files?

[lyric owl]

@grim vault it works, tysm

i misplaced a comma so thats why it couldnt call the str

[azure yarrow]

Hello, I have a hobby project on heroku, and I'm trying to drop and recreate all tables at app startup:

def db_drop_and_create_all():
    """
        drops the database tables and starts fresh
        can be used to initialize a clean database
    """
    inspector_gadget = inspect(db.engine)
    if inspector_gadget.has_table("player"):
        db.session.query(Player).delete()
        db.session.query(Tournament).delete()
    db.session.commit()
    db.create_all()

It works fine locally, but on server the tables are not dropped! Is this the right way to delete tables?

[azure yarrow]

Oof, I reinstalled psql on my ubuntu to have the same version as heroku app but now I can't even connect to my local db, because the psql command is using a user that is not defined yet 😄

[neon bronze]

how i can know when a "CREATE TABLE IF NOT EXISTS " query doesn't execute because the table exists?

[harsh pulsar]

i think this will depend on the database

it'd be pretty nice to have detailed logging like that

[neon bronze]

Find nothing with Asyncpg but "CREATE TABLE" raises a duplicate table error or something

[rare phoenix]

Hello, I was thinking of using Rethink DB for a project of mine. I searched a bit and found that to use it on Windows, I must use something called Docker. But I was wondering if I wanted to put my project into production, I wont be able to keep my PC on the all the time for the db to work. I think the process of connecting to Rethink db is not like MongoDB where all I need is a big url and some password or something.

So, how can I use Rethink db in production?

[harsh pulsar]

im not sure about deploying rethinkdb specifically, but one of the use-cases for docker is the ability to deploy your application as a docker container

[rare phoenix]

Ohh I see

[harsh pulsar]

some organizations develop entirely in a docker container, so the dev environment is literally the same as the prod environment

[torn aurora]

Anyone here familiar with sqlalchemy?

I am having trouble using enums with it

I am using SQLite

[pine hedge]

I'm really lost. This is my first time looking into a good db, (right now I'm using MongoDB), and I'm unsure how I would setup and use any SQL database, and be able to use it both on my VPS and on my PC. Keep in mind this is for a discord bot so for testing purposes this is pretty much needed if possible.

[prime kelp]

well its recommended you use either sqlite (a local file) or postgresql (a hosted sql db, do some research)
you can choose your option:

sqlite:

• free
• easy to set up and use
• fast and efficient

• doesnt scale well
• only a local file so can’t be used from another location

postgresql:

• scales well
• efficient
• can be used in multiple places

• may cost money to host it
• somewhat complicated

wait i’m in the wrong channel

[brave bridge]

PostgreSQL on its own is completely free as in both free beer and free speech

[gusty mulch]

Hi so I have this async func to connect to my postgres dbpy async def connect_postgres(self): self.pool = await asyncpg.create_pool(**self.config.database, max_size=10, command_timeout=60)I'm not sure if creating the pool this way is the best (the docs say it's not recommended but it should still be valid) but I want the db pool to be global to my bot not tied to a specific command(s). I have this amin function (per dpys new asyncio changes) but I'm not sure which of the shown # here's I ought to await the connect_potgres func inpy async def main(self): await self.connect_postgres() # here async with aiohttp.ClientSession() as session: await self.connect_postgres() # here async with self: await self.connect_postgres() # here print("Hello World")

[brave bridge]

@gusty mulch the point of a pool is that you create it just once, and then re-use it across your entire application

[gusty mulch]

oh okay makes sense, not really the way i interpreted this https://magicstack.github.io/asyncpg/current/api/index.html?highlight=create_pool#connection-pools but oh well thanks for the clarification. As for my question about which part of the above bottom function, which part is the best place to call the postgres connection function or does it not really matter which part (of the main func)

[brave bridge]

I'm not very familiar with discord.py, but would something like this be possible?

async def main():
    async with create_postgres(settngs) as pg_pool: 
        async with aiohttp.ClientSession() as session:
            bot = Bot(pg_pool, session)
            await bot.run()
``` if so, that would probably be the best way

[gusty mulch]

I'll have a check to see if it works

[storm mauve]

I think that the current pip version does not allows it, but the github public alpha ish version should

[brave bridge]

this is pseudocode btw

[gusty mulch]

yeah i got that

[brave bridge]

Basically, you use context managers to create all your dependencies - to ensure that they are closed properly (and so that their lifetime is strictly greater than the lifetime of the bot). Then you create your bot with the dependencies.

[dawn hollow]

I was wondering if someone could help me. I would like generate a SQL query to insert or update records from table source to table target
a) Update if column_a and column_b are the same in both tables and column_a_date < column_b_date
b) Do nothing if if column_a and column_b are the same in both tables and column_a_date > column_b_date
c) Else insert the record from source table to target table

[pure cypress]

I'm trying to create a trigger in PostgreSQL that will effectively act as a constraint on a related field. I've read that PostgreSQL does not support related field in constraints, so a trigger should be used instead. However, the documentation for a trigger states "Currently, WHEN expressions cannot contain subqueries."

How is a trigger a solution to this problem then? Does it have something to do with the from clause of the trigger? Even pointing me to an example of how a trigger solves this would be helpful.

After further research, the way to do this might be to omit the when and instead write a subquery in the stored procedure the trigger executes.

[grim vault]

What constraint are we talking about?

[pure cypress]

Essentially table A has columns B and C which are foreign keys. B's table also has a FK for C. The constraint is that A.B = A.C.B.

I plan to change the schema soon so I hope I won't even need this constraint. However, I'm still curious as to how this could be accomplished.

I came up with this to put in the trigger's function but didn't test it out yet ```sql
IF (NEW.c NOT IN (SELECT c FROM B where id = NEW.b)) THEN
RAISE EXCEPTION '...'
END IF;

[bleak bough]

how do i turn on autocommit for sqllite3

[delicate field]

motor v2.5.1

Non-blocking MongoDB driver for Tornado or asyncio

[nimble umbra]

What python json library would you recommend? I need to be able to check if there is a file(easy with os.path), if there is no file create one, and then populate it later on.

[brave bridge]

What's wrong with the built-in json module?

[nimble umbra]

Nothing in particular, this is just my 1st time working with json

and came here for some advice 😄

[oak oyster]

Traceback (most recent call last):
  File "C:\Users\raimy\AppData\Local\Programs\Python\Python310\lib\site-packages\discord\ext\commands\core.py", line 187, in wrapped
    ret = await coro(*args, **kwargs)
  File "C:\Users\raimy\Desktop\not-yggdrasil\main.py", line 34, in reset
    for call in bot.calls:
  File "C:\Users\raimy\AppData\Local\Programs\Python\Python310\lib\site-packages\motor\core.py", line 767, in __getitem__
    return collection_class(self.database, self.name + '.' + name,
TypeError: can only concatenate str (not "int") to str
``` is this a motor error or is it me?

[brave bridge]

Can you show your code?

I would always first assume that my code is not right. Libraries have bugs as well, of course, but they usually already have thousands of users and have existed for a long time.

[lapis harbor]

Hi there 👋

I have a question about Django ORM optimization, assume I have two models like that:

class Product(models.Model):
    # some fields
    ...

class Gallery(models.Model):
    product = models.ForeignKey(Product, on_delete=models.CASCADE)

and I have endpoint that return all products and it's galleries

If I have one product and this product has 5 Gallery it would cost 6 hits on the database, so any thoughts on how can I solve this performance issue?

[oak oyster]

ok here the error said it got raised from reset ```py
async def reset(ctx) -> None:
"""Resets the database."""

for call in bot.calls:
    await bot.calls.delete_one({'_id': call['_id']})

await ctx.reply('Database reset.')

[ionic pecan]

you probably want select_related, see https://www.geeksforgeeks.org/prefetch_related-and-select_related-functions-in-django/

[lapis harbor]

I didn't know about prefetch_related with the reverse ForeignKey relationship, thank you, I appreciate that!

[harsh pulsar]

is there any query builder dsl for mongodb, like sqlalchemy core for sql?

[harsh pulsar]

are you sure that it will make 6 individual queries for a reverse lookup? that seems surprising

e.g. if you do product.gallery_set wouldn't it just do a single query?

@lapis harbor for example:

product1 = Product.get(pk=1)
product1_galleries = product1.gallery_set.all()

this should be exactly 2 queries. and of course you can collapse this into a single query as well:

product1_galleries = Gallery.filter(product=1).all()

in both cases, fetching the gallery items should literally just be a single SELECT ... WHERE ... on the gallery table

[lapis harbor]

You're right, thank you for pointing out my mistake

[grizzled swift]

how does the Unique Constraint work on sql alchemy? i looked on the docs and it seems to be implemented properly, but it seems to still be duplicating entries

class FzDiscordUser(Base):
    __tablename__ = "fz_discord_users"
    id = Column(Integer, primary_key=True)
    tokens = relationship("FzToken", lazy="selectin")


class FzToken(Base):
    __tablename__ = "fz_tokens"
    id = Column(Integer, primary_key=True)
    token_id = Column(String)
    parent_id = Column(Integer, ForeignKey("fz_discord_users.id"))
    __table_args__ = (
        UniqueConstraint('parent_id', 'token_id', name='_token_type_uc'),
    )```

[wise goblet]

Designing database with users, posts and likes/dislikes.
Is there advantage to have likes dislikes as separate tables, or to keep them in one table with boolean for which is it one?

Oh. I have idea of an advantage

if in one table, I could enforce CONSTRAINT (like_dislike + user id)

so the user could choose only one out of them

no idea how to do the same with keeping it as separate table

so I should better to do it as one

TLDR: I have already resolved

[bronze escarp]

Hello, I'm using django with Postgresql, but I ran into a problem with encoding. There are cards on my site that have a name, description and price, so when I fill in the information in English then everything works well, but in Russian I can't even select a table from the database because an error occurs character with byte sequence 0xd0 0x94 in encoding "UTF8" has no equivalent in encoding "WIN1252". If I then write SET client_encoding TO 'UTF8'; the following happens in my database. My base is in utf8 encoding.

[torn sphinx]

how to create data base

[nocturne cloud]

Hello, can anyone help me with database on MongoDB? I'm getting a bit frustrated even doing simple stuff

I am trying to check if a document I am looking exist. To do this, I do collection_find() passing on the requirements inside. In the instance that I do not find it, I want to do something different than if I find it. The question is, how could I make that? That statement returns a cursor.

[open bolt]

Hello, my name is ei. Let's not come in. I don't know if it's okay to leave this question, but I'll leave it.

I am trying to do a database project. Please tell me where I can get a large data set. A minimum capacity of GB is better.

It's too difficult to find with a Google search, and I'm not familiar with the keywords, so I'm posting here.

[next smelt]

@torn sphinxsqlite 3 creates the file automaticaly if it doesn't exist

[sterile pelican]

Anyone knows how to transfer a postgres database from one server to another relatively quickly? Origin server has postgres 12 and destination has 14, would that be an issue

[open bolt]

@sterile pelican https://stackoverflow.com/questions/1237725/copying-postgresql-database-to-another-server

[sterile pelican]

I don't really get what do I need to replace values in the example with

[open bolt]

I looked it up in Korean, but I don't know if it'll be translated... I think this side is rather well explained... Do you mind if I give you an order?

[open bolt]

• https://www.postgresql.org/docs/9.6/app-pgdump.html

[open bolt]

For a moment, I'll write down the instructions on the site I found here.

[sterile pelican]

Like what do I need to replace the localhost, localuser, remotehost, remoteuser with?

[open bolt]

@sterile pelican postgresql command
pg_dump -C -h localhost -U localuser dbname | psql -h remotehost -U remoteuser dbname
or

[sterile pelican]

The question remains

[open bolt]

Let me summarize the instructions for a moment.

-pg_dump -C -h localhost -U localuser dbname | psql -h remotehost -U remoteuser dbname
or

pg_dump -C -h remotehost -U remoteuser dbname | psql -h localhost -U localuser dbname

Connect to a remote host using psql or pg_dump.
If the database is large or the connection speed is slow, it may be faster to dump files and transfer compressed files.

As Kornel said, you don't need to dump it to an intermediate file, you can use a compressed tunnel for compression.

pg_dump -C dbname | bzip2 | ssh remoteuser@remotehost "bunzip2 | psql dbname"
or

pg_dump -C dbname | ssh -C remoteuser@remotehost "psql dbname"
However, this solution requires a session from both sides.

[open bolt]

@sterile pelican https://stackoverflow.com/questions/1237725/copying-postgresql-database-to-another-server
This is the content of this article. If this doesn't answer, I don't think there's a way to help you. I'm sorry.

[misty sundial]

kaggle.com and https://datasetsearch.research.google.com

[open bolt]

@misty sundial Do you happen to know what AWSS and PPCDS mean? I couldn't find a large data set in Kaggle. I only saw about 2000 ~ 10000 data sets on Cagel.
Also, how can I use that search site? Do I just write it as if I'm searching?

[sterile pelican]

I mean what do I have to put instead of localhost, localuser, remotehost, remoteuser? It is obvious that if I put everything exactly like in example nothing will work. Do I have to put remote server IP or something, what user do I have to put

Ig localhost should remain but what about others

[open bolt]

What should I do? I don't think I can answer any more. Sorry.

[sterile pelican]

Okay thx anyway

I will try to figure that out by myself

[torn sphinx]

Hello everyone, I have started at a research institution and I got tasked to retrieve Data with SQL. I can do that easily since I already have experience but this is my first gig and I am honestly struggling to understand the setup of the database. I know there are servers in which the data is stored, but I am using 2 more applications apart from the SQL engine (dbeaver, using MySQL engine) that I have to run to access the data base.

So my question: How to build an understanding of the setup & environment, as well as active applications (apart from dbeaver) that I was given in order to execute SQL ?
I'll add some more info below

-> my supervisor is on holiday, so I am trying to make use of the time
-> I could imagine there to be a way through either IntelliJ or dbeaver that displays or implies the functions of every application it is connected to/with. I don't know where to look for that as tutorials don't get indepth enough
-> I am working with IntelliJ, Docker and Postman, as well as dbeaver

Please tag me if you reply so I can see your reply. Much obliged to anyone commenting!

[harsh pulsar]

definitely take advantage of the graphical interface to physically browse the table layouts, pay special attention to foreign keys

do you have access to the actual source code of applications that use the database?

there isn't really any way to look at the database itself and determine which applications are using it

[torn sphinx]

Thank you for commenting on my issue. Are you refering to dbeaver's graphical interface here?

[harsh pulsar]

yes, use it to get a sense of the actual structure of the data

what kind of task is it?

[torn sphinx]

My current understanding of the entire setup is sadly not enough to answer this question

[harsh pulsar]

sometimes you just can't proceed without help. welcome to the real world

do as much as you can, just poke around and observe things

[torn sphinx]

Alright, I'll note this down

[harsh pulsar]

practice writing sql queries, if nothing else

[torn sphinx]

Data is being collected through an interaction with a device. I was tasked to take that generated data and eventually create a dashboard in which our researchers can see the current state of the device and how many interactions it had during the day

[harsh pulsar]

so do you understand the various tables in the database, how they interact, what they mean, etc? if so, then you can start sketching out a solution

at the "other end", do you have a clear sense of what pieces of data will be required to power the dashboard?

you can work at it from both ends. that's a good common problem solving technique in general

[torn sphinx]

Alright, I'll do my best to make use of the "extra" time then! I just wanted to ensure I'm taking advantage of all the options I have; hence my question here

[torn sphinx]

Yes! Luckily the attributes are semantic and mean what they say - just like the records

[harsh pulsar]

great, you're in luck

[muted narwhal]

I am currently trying to connect to a mysql db using SQLAlchemy v1.4.
This is the code:

with open("src\\config.json") as json_file:
    data = json.load(json_file)
    connectionString = data["connectionString"]

engine = create_engine(connectionString, echo=True)

It throws ModuleNotFoundError and wants MySQLdb. When I try to install it though, the console replies, that no matching distro has been found.

[harsh pulsar]

i've worked with databases that contain 250 tables with column names like CLA0_HST1

[harsh pulsar]

this probably isn't specifically a "databases" problem. if you open a help channel with this question and @ me, i can help you work through the troubleshooting steps

[muted narwhal]

Sounds great

[torn sphinx]

I am in a much better position in that regard 😅 sounds like you dealt with a load. Anyhow, thank you very much, at least I got some clarity about the commonality of my situation

[harsh pulsar]

be patient. you asked an open-ended question less than a minute ago, in a channel where people are already having a discussion.

as for your question: it depends, there are lots of different kinds of databases. the simplest kinds of databases consist of a single file that lets you retrieve and write data in the file efficiently. an example of this kind of database is sqlite. i suggest that sqlite is a good place to start for learning about databases.

it's good to ask questions. but following up with "any help????" after 40 seconds is impolite and counterproductive

[dusky field]

@harsh pulsar here are 2 tables. I want to add a third column to the top table which contains - for each record - the respective frequency in the third column of the bottom table (e.g., the first record in the transformed table would be 22 dusting 4). been stuck on this one! it is recommended that I used multiple joins and first() aggregation. any suggestions would be appreciated, cheers.

[harsh pulsar]

is this relevant to something we were talking about? i don't have any context for this quetion @dusky field

[dusky field]

trying to write a SQL command that does what I described @harsh pulsar you seem experienced, so I decided to ask you.

[harsh pulsar]

ok, fair enough. note that normally we discourage "random pings" on this server, since everyone here is a volunteer and might not have time or knowledge to answer any given question

i'm happy to take a look though

[viral rivet]

helllo guys

is there something else i can use

[dusky field]

ah, i see. i am new to the server. what is the recommended way to ask questions?

[viral rivet]

besides mongo db

[dusky field]

and thank you!

[harsh pulsar]

you can drop your question in a channel and wait for an answer. it's ok to re-post if you don't get one

so you just want to get the frequency of each sid in the bottom table, and append it to the top table?

[dusky field]

yes, exactly

but it was recommended that I use multiple joins and first() aggregation (the latter of which makes no sense to me - maybe you have an idea)

[harsh pulsar]

for what purpose? for a general database yes, i think mongodb is a poor choice and you should use a traditional sql-based database. for a document database, not really, although postgresql and sqlite have built-in json support

[harsh pulsar]

that seems a lot more complicated than you need. start with one thing at a time: how would you compute the frequency of each sid in the 2nd table?

this actually seems really easy, to the point where i'm wondering if you're leaving out some relevant details

[dusky field]

yes, I agree - which is why I was confused by the recommendation to use multiple joins and first aggregation lol

but I have to follow the method of the exercise

(note: this is a learning exercise; I am new to databases)

[harsh pulsar]

can you show the actual exercise?

[half forum]

ok so i am trying to append data to a json and it works
now I want to append it to a certain ID

I tried but it keeps saying index list out of range

[lucid crane]

Send the full traceback

[half forum]

newstaff = staff[123456789]
IndexError: list index out of range

[lucid crane]

That's not part of the code you shared

Anyway, can you show the json file?

[half forum]

right i put the wrong code sorry

[half forum]

#import json
import json

#Append data to file
def append_data(data):
    with open('./testing/balh.json', 'r') as f:
        staff = json.load(f)
    #make staff into a list
    staf = staff[123]
    sta = list(staf)
    sta.append({
        123: [data]
        })
    with open('./testing/balh.json', 'w') as f:
        json.dump(sta, f, indent=4)
    
#Read data from file
def read_data():
    with open('./testing/balh.json', 'r') as f:
        staff = json.load(f)
    return staff


var = append_data("1293i912i39123")
var1 = read_data()

[lucid crane]

Your code still doesn't correspond with the error

[half forum]

    staf = staff[123]
IndexError: list index out of range```

[lucid crane]

Okay yeah, show the json file

A screenshot of the contents would be preferred

[vivid briar]

there is below 124 items in the json list

[ebon skiff]

You need to use staff['123'] since dump on a dict converts all int's to str.

Since json only allows strings as keys.

[half forum]

    staf = staff['123']
TypeError: list indices must be integers or slices, not str```

[half forum]

its just {}

I dont have anything in it yet

[vivid briar]

You tryna make it a list or dictionary

?

[half forum]

dict

i made it a list to append

[vivid briar]

You can append with a dict?

You want nested?

nested dict?

Something like

{ctx.author.id:{“gold”: 1}}

in the json file right?

[half forum]

Yep it worked with previous code

[half forum]

yes sir!

[vivid briar]

For example:

with open(“name.json”, “r”) as f: json.load(f)

(First part)

[ebon skiff]

You do need to dump the whole dict again.

[half forum]

[vivid briar]

Actually wait

i should prob replace r with +

[ebon skiff]

r+

read and write mode

[vivid briar]

If I am not wrong + means read and write already?

I forgot

Anyways

[half forum]

changed thispy sta = staff['123'] KeyError: '123'

[vivid briar]

users[ctx.author.id] = {} users[ctx.author.id][“gold] = 0 with open(“name.json”, “w”) as f: json.dump(names, f)

continuing from first part btw

[half forum]

ik

[half forum]

but I need to append not overwrite

[vivid briar]

IT ISNT OVERWRITING

It’s like “appending” to the dict

Try it

I can give you proof

[half forum]

bro I have used it before, it overwrites previous. I need to append a list of pilot id's for each server so I can use them for a queue

[vivid briar]

No let me show you a image

[ebon skiff]

# Import json
import json

# Read data from file
def read_data() -> dict:
    with open('./testing/balh.json', 'r') as f:
        return json.load(f)

# Dump data
def dump_data(data: str) -> None:
    with open('./testing/balh.json', 'w') as f:
        json.dump(data, f, indent=4)

# Append data to file
def append_data(data):
    # Get json data
    staff = read_data()

    # Add item to dict
    staff['123'] = data

    # Dump data using the function
    dump_data(sta)


var = append_data("1293i912i39123")
var1 = read_data()

[vivid briar]

Is this called overwriting?

[half forum]

budster, it needs to have 100 user ids in a single guild id that can always add more ids

[abstract pivot]

Fwiw you shouldn't be using json as a database, for something like this you would use sql

[ebon skiff]

They( #discord-bots ) told him already. He is just learning atm.

[vivid briar]

why do you need to do that?!?

[ebon skiff]

@half forum I sended some code, does this help?

[half forum]

trying it now :D

[half forum]

I wanna use json for now, ill use sql later

[half forum]

[half forum]

its a big bot for a big reaason

[vivid briar]

well honestly if you want to do something like that learn SQL

[half forum]

It works tysm

[ebon skiff]

Glad to help :)
staff['123'] = data this did the trick really i just also cleaned it up a bit :3

[half forum]

yep, last thing i have a question for, it keeps creating the same "123" so should I use a if statement to check if its in the JSON and then if it is, how do I append the []

[ebon skiff]

You mean create unique keys?

[half forum]

Doing this now

# Import json
import json

# Read data from file
def read_data() -> dict:
    with open('./testing/balh.json', 'r') as f:
        return json.load(f)

# Dump data
def dump_data(data: str) -> None:
    with open('./testing/balh.json', 'w') as f:
        json.dump(data, f, indent=4)

# Append data to file
def append_data(data):
    # Get json data
    staff = read_data()

    # Add item to dict
    staff[123] = ""
    staff[123]['p'] = [data,]

    # Dump data using the function
    dump_data(staff)


var = append_data("1293i912i39123")
var1 = read_data()```

This error: ```py
    staff[123][1] = [data,]
TypeError: 'str' object does not support item assignment```

[half forum]

Unique key as guild id
and then a list of user ids

[ebon skiff]

# Import json
import json

# Read data from file
def read_data() -> dict:
    with open('./testing/balh.json', 'r') as f:
        return json.load(f)

# Dump data
def dump_data(data: str) -> None:
    with open('./testing/balh.json', 'w') as f:
        json.dump(data, f, indent=4)

# Append data to file
def append_data(data):
    # Get json data
    staff = read_data()

    # Add item to dict
    staff[str(ctx.guild.id)] = [member.id for member in ctx.guild.members]

    # Dump data using the function
    dump_data(staff)


var = append_data("1293i912i39123")
var1 = read_data()

[prime kelp]

panda write my code for me

[half forum]

staff[str(ctx.guild.id)] = [member.id for member in ctx.guild.members] this one can it be staff[str(ctx.guild.id)] = [data,]?

[ebon skiff]

If data is a list of members you can just do staff[str(ctx.guild.id)] = data

[ebon skiff]

lol

Been learning c# and rust

[prime kelp]

what happened to kotlin

[half forum]

it would be like !add user 123129838912838

[half forum]

now it wont add a new data, and it overwrites it

[prime kelp]

panda i believe someone in discord bots requires assistance

[ebon skiff]

That as well but kotlin is pretty similar to python + c#

[ebon skiff]

With this code? Doesn't think it does that, does it?

[half forum]

It does

[ebon skiff]

Ohhh you want to add the user to the data

i understand now

[half forum]

Ya

so guild id => pilots => [user ids]

[ebon skiff]

# Import json
import json

# Read data from file
def read_data() -> dict:
    with open('./testing/balh.json', 'r') as f:
        return json.load(f)

# Dump data
def dump_data(data: str) -> None:
    with open('./testing/balh.json', 'w') as f:
        json.dump(data, f, indent=4)

# Append data to file
def append_data(data):
    # Get json data
    staff = read_data()

    # Check if list item exists
    if not staff.get(str(ctx.guild.id)):
        staff[str(ctx.guild.id)] = []

    # Add item to dict
    staff[str(ctx.guild.id)].append(data)

    # Dump data using the function
    dump_data(staff)


var = append_data("1293i912i39123")
var1 = read_data()

woops 1 sec

[half forum]

works :D tysm

[half forum]

oop what did you change

[ebon skiff]

Edited, otherwise if it doesn't exists it crashes lol

[half forum]

ah ok

[half forum]

yep works tysm!! :DDD

[ebon skiff]

Yeah np :3 recommend looking into dicts
print out the results makes a lot of sense then.

[half forum]

yeeppp

[glossy spruce]

How do I make the embed message that I made appear when someone does a command?

[rigid yacht]

hello guys good morning...!

[tropic onyx]

does anyone know sqlalchemy?

[ionic pecan]

yes, just ask your question

[ionic pecan]

read into the sqlite3 module, it has a good introduction. you basically want to create a table for your warns, insert it when you warn someone, and select from it when someone searches for warns

[ionic pecan]

probably a question for #discord-bots

[uncut idol]

does anyone maybe know if mongodb is a good option to store a lot of images in like 20k images (i want to use a database that is easy with tensorflow and mongodb seemed like a good option but i dont know how it will be for images) i want to store the images like numpy arrays

[glossy spruce]

srry

[harsh pulsar]

no, i would use sqlite; you can store the raw numpy arrays as binary blobs

[uncut idol]

what for advantages would that have?

[harsh pulsar]

better performance, maybe less space on disk, don't need to run a separate server for the database

if you do want a separate database server, i'd sooner recommend postgres with the same, storing the numpy images as binary blobs

you could also just put the 20k images in a directory on disk in npy format

that's basically what you would write to the database, npy format byte strings

reading lots of small files from a database as binary blobs should generally be faster than reading from disk

writing actual integer arrays to mongo is wildly inefficient by comparison

also in general, mongo is almost never the right solution

heck you could also use hdf5, although i dont know how well it scales into the range of 20k individual arrays

it seems like some people do use hdf5 for storing large image datasets

that actually seems like it's pretty common for deep learning practitioners

so yeah.. literally any of those options beat mongo

mongo would be maybe my last choice for this task

[uncut idol]

currently i have them as separate png's and import them to a single var that keeps loaded in memory

[harsh pulsar]

you import all 20k at once?

[uncut idol]

no with a for loop so kindoff

[harsh pulsar]

honestly i would just keep doing that, if it works well

[uncut idol]

it doesn't work well that it why i want to use databases

[harsh pulsar]

what doesn't work well about it?

did you try using tf.data?

[uncut idol]

it is slow because it has to decode every png separate

[harsh pulsar]

fair enough, also the filesystem is pretty slow for lots of individual reads

https://www.dbersan.com/blog/neural-boilerplate/

[uncut idol]

i have but that does have some downsides. when training a model you need a input and a output in my case are both images and tensorflow seems to not like images as output in their tf.dataset when importing it that way

so i want to put it in a database to be able to load them directly in a tf database

[harsh pulsar]

this blog post shows HDF5ImageGenerator which does the same with hdf5 files

not sure if that works for you

read through the blog post and see if the hdf5 method works

if not, move up to sqlite

the nice thing about hdf5 is that you can store the actual arrays in there, so you have less overhead in decoding them

[uncut idol]

are there options that are non relational because there are only x and y images wont need any entities or relations for that

but i will take a look at hdf5

[harsh pulsar]

you can just ignore the relational stuff. make 1 table with 1 column, or maybe 3 columns (image_id, label, image)

but yeah i'd start with hdf5

[torn sphinx]

isn't this contradictory

[torn sphinx]

@bot.command()
async def пред(ctx, member: discord.Member = None, *, reason = None):
    print(45)
    cursor.execute("""CREATE TABLE IF NOT EXISTS warning(guild BIGINT, userid BIGINT, warn INT, count INT, reasons VARCHAR);""")
    base.commit()
    print(1)

    if member is None:
        await ctx.send("Выберите участника")
        return
        
    if reason is None:
       cursor.execute('INSERT INTO warning(guild, userid, warn, count, reasons) VALUES(%s, %s, %s, %s, %s)', (ctx.guild.id,ctx.author.id,1,1,'Отсутствует'))
       base.commit()
       print(2222)
       cursor.execute('UPDATE warning SET warn = warn + 1 WHERE userid = %s AND guild = %s', (member.id, ctx.guild.id))
       base.commit()
       await ctx.send(f"**{member}** Выдал предупреждение {ctx.author.name} , причина ``Отсутствует``.")
    else:
       cursor.execute('INSERT INTO warning(guild, userid, warn, count, reasons) VALUES(%s, %s, %s, %s, %s)', (ctx.guild.id,ctx.author.id,1,1,reason))
       base.commit() 
       cursor.execute('UPDATE warning SET warn = warn + 1 WHERE userid = %s AND guild = %s', (member.id, ctx.guild.id))
       base.commit()
        await ctx.send(f"**{member}** Выдал предупреждение {ctx.author.name} , причина {reason}.")    

not working. Nothing output excepting 45 and 1, why?
postgresql

[brave bridge]

@torn sphinx what output do you expect if member is not None and reason is not None?

[torn sphinx]

._.

[brave bridge]

?

Do you get any exceptions? And what does the Discord bot do?

[torn sphinx]

at the beginning, the "Укажите участника" check is triggered, then I ping the person and enter the reason, there is no error, the bot is silent

[brave bridge]

Well, after the bot prints "Укажите участника", the command handler is done

return ends a function

A command expects you to pass all the arguments in one go. There isn't any state machine going on

So you should do !пред @__the_member__ "go away!"

[torn sphinx]

okay

@brave bridge bot ignor

although I did, &пред @member reason

[bleak bough]

hey guys, how do i make the default value for text an empty string?

[torn sphinx]

i'm trying to append things to a array using pymongo but its making a new document with an array instead. i have db.insert_one({'$push': {'users': f"{id}:{username}:{password}"}})

[ionic smelt]

what db

[pliant sequoia]

im trying to create a table for microsoft access but I keep getting syntax errors, can somebody tell me what's wrong ?

I have a feeling its something to do with the datatypes but I just dont know :/

found out the issue is to do with AUTO_INCREMENT as the program works after I delete that but what's the right way to do it ?

[tropic onyx]

does anyone know anything about pymysql?

[compact marlin]

Whats the difference between sqlite and postgresql

[river matrix]

Hey guys. Question.

When reading through the rules of the 1st Normalization. One of the rules is that each cell must contain only one value so not 'blue, green' Right?

My question is, when normalizing of iterating through these cells, how does one insure that there are in fact just one value?

Say records have about 10 000 records. I could not iterate though all of them manually

[velvet lotus]

If I'm making a website using some python framework like flask or an async version of it, should I use motor over pymongo for the database driver?

[marble dagger]

Hi Guys,
I have a question , I want to send an array of ids through axios get request to flask alchemy . How do we do that? Can you give me an idea? I mean how to make alchemy handle array of ids and get information based on those ids?

[hardy ibex]

How can I check if an entry exists in an SQLite table?

I need to be able to take if it exists or not and return different things

I was using something weird before, but I'm not sure what it was and I'm not at my desktop

[storm mauve]

"entry" as in row?
just select it and see if you get anything back I think

[torn sphinx]

Why am I getting this error 😦

[storm mauve]

you might need a ; after the first query?

[torn sphinx]

can mongodb store emojis fine?

[torn sphinx]

Tried, that did not work. The error message says 'CREATE VIEW' must be the first statement in a query batch

[past hare]

Does reverse engineering in mysql create a reliable er diagram. If it's just a few tables?

[vapid arch]

what does this number mean?

the max amount of characters?

[tropic dagger]

Suppose I want to restructure my table in two separate tables and some data has already been implemented on it. How should I do it?

[nova cove]

ye. its the length in bytes

[vapid arch]

ty

[torn sphinx]

Anyone know how to make a view ?

[tropic dagger]

You have to generate a SQL expression and create a view from it

// Crée la vue
CREATE VIEW utilisateurs_gmail_vw AS SELECT * FROM utilisateur WHERE email LIKE "%gmail.com";

// Affiche le résultat
SELECT * FROM utilisateurs_gmail_vw;

// Remplace la vue
CREATE OR REPLACE VIEW utilisateurs_gmail_vw AS
SELECT *
FROM utilisateur
WHERE email LIKE "%gmail.com";

// Supprime la vue
DROP VIEW utilisateurs_gmail_vw;

CREATE VIEW view_name AS [query]

[torn sphinx]

when I do that I get "must be the first statement in a query batch"

[grim vault]

lmgtfy: https://database.guide/sql-server-error-111-must-be-the-first-statement-in-a-query-batch/

[rough viper]

how to connect mysql in python program?

[hardy ibex]

So pretty much just catch whatever error it throws?

[rough viper]

also whenever i enters password in command line and press enter, it suddenly closes it own windows

[hardy ibex]

I really don't want to catch an error because it isn't an error if there's no row with what I'm looking for

[rough viper]

what can i do now?

[storm mauve]

it would probably just be checking if it is None, .empty / .empty() or == [] in this case - depends on how they give you back the results

[hardy ibex]

cursor.fetchone()

[tropic onyx]

does anyone here know sql

[rough viper]

yes;

little little;

[tropic onyx]

how do you the result of COUNT() in a function?

[rough viper]

it's same like len() of python

[tropic onyx]

so something like count(SELECT * FROM mytable)

[rough viper]

no

[tropic onyx]

because when I try to do like

[rough viper]

it's attribute should be column

[tropic onyx]

set var = COUNT(SELECT * FROM mytable) it throws some error complaining about misusing a group function

[rough viper]

no it's wrong

waitt

select COUNT(column) from [db_name]

@tropic onyx^

[tropic onyx]

yeah but i need it to be an int

because it's part of a function computation

oh

select count() column will be 0

[rough viper]

then do column int

[tropic onyx]

*select COUNT(column) FROM db

doesn't select return a table though

so it'll take my count command and turn it into a table

or is sql smart enough to know that count returns a bigint so the select just takes it as an int?

[rough viper]

it would be int

not confirm

[tropic onyx]

So doing this

SELECT COUNT(*) FROM table

does that yield a table or an int as the result?

because it looks like i get a 1 row table with a value

[twin crater]

anyone here knows django+mngodb

[grim vault]

.fetchone() will return a tuple with one value. As COUNT(*) will always return a value you don't need to check for None.

[tropic onyx]

Thanks

I found out the hard way that the bug was caused by naming a variable to be the same as a column name…

[nocturne stratus]

I have a question for how to design database for a recipe app/website . I understand there are a ton already this is just for fun. I appreciate anyones help!

Would it make more sense to scrape and create my own database of recipes with ingredients which would make it easier to filter for ingredients? Or would just having a database of different links work better? What would be quickest aswell? Are there other things I should consider?

[loud merlin]

help me fix this error

i using sqlite database also

[torn sphinx]

it looks like you're using parameter syntax (*args, **kwargs) as arguments, that's not possible afaik

[dusty halo]

#python-discussion

[torn sphinx]

TypeError: Cannot read properties of null (reading 'click')
    at click_date (C:\Users\Admin\Desktop\norg\utilities\other\TG\gen.js:74:13)
    at runMicrotasks (<anonymous>)
    at processTicksAndRejections (node:internal/process/task_queues:96:5)
    at async fill_discord (C:\Users\Admin\Desktop\norg\utilities\other\TG\gen.js:88:5)
    at async create_accinfos (C:\Users\Admin\Desktop\norg\utilities\other\TG\gen.js:197:5)
    at async C:\Users\Admin\Desktop\norg\utilities\other\TG\gen.js:240:21

Anyone?

[brave bridge]

Is this related to Python or databases? Can you provide some more details?

All I can say from the error is that you ran some JavaScript and then tried to access the property click of null

Show some code, maybe?

[meager portal]

Anyone know how to resolve these error

[brave bridge]

@meager portal This is a Python server. If you have a C++ question, see this lists of guilds:

!guilds

[delicate field]

Communities

The communities page on our website contains a number of communities we have partnered with as well as a curated list of other communities relating to programming and technology.

[torn sphinx]

Y does smss hate me

I can’t make a view and then call that view in another query

[torn sphinx]

Hi, I'am trying to install sqlite3 for 1 hour now, can some1 helps me ? What I have to do when I am in the downloads page and What I have to do after ???

mention me if you can help me

[unkempt arrow]

sqlite3 is builtin

you don't need to install it

!e

import sqlite3

[delicate field]

@unkempt arrow :warning: Your eval job has completed with return code 0.

[No output]

[unkempt arrow]

^^^

[torn sphinx]

yes but when I don't arrive to recuperate my data after this

[unkempt arrow]

¯\_(ツ)_/¯

[calm grotto]

Khan Academy have an SQL unit suitable for beginners:
https://www.khanacademy.org/computing/computer-programming/sql

[brave bridge]

!mute @wintry grotto 1d Extraordinary volumes of spamming

[delicate field]

:incoming_envelope: :ok_hand: applied mute to @wintry grotto until <t:1648487352:f> (23 hours and 59 minutes).

[brave bridge]

!tvmute 551700211605438464 2w This is not the way to get access to voice chat

[delicate field]

:incoming_envelope: :ok_hand: applied voice mute to @wintry grotto until <t:1649610607:f> (13 days and 23 hours).

[lunar valley]

mariadb can i use this db in pycharm

because i dont finde the hoste name or the port

[harsh pulsar]

yes. pycharm is just a code editor. python is still python.

[harsh pulsar]

you need to know the hostname and port in order to connect

[lunar valley]

you know where i finde this

i googled and tryed but dont found it

[harsh pulsar]

are you using a hosting service?

[lunar valley]

nvmd you edited you messages

okay, thank for help

[harsh pulsar]

if you are using a hosting service, that service should provide you with the connection details

if you are hosting it on your own machine, you should already know that information

[violet grove]

hello, i'm french (sry for my english) and i've a problem to connect my python script to a MySQL db... i want to connect my script, i use a simple thing that i found on the internet, but i have a timeout error at every attempt... here is my script :

import mysql.connector

mydb = mysql.connector.connect(
    host="hostexample.com",
    user="myid",
    password="mypassword",
    database="the database i want to connect",
    port=3306
)

print(mydb)

and there is the error i have :

Traceback (most recent call last):
  File "C:\Python310\lib\site-packages\mysql\connector\connection_cext.py", line 236, in _open_connection     
    self._cmysql.connect(**cnx_kwargs)
_mysql_connector.MySQLInterfaceError: Can't connect to MySQL server on 'z84xh.myd.infomaniak.com:3306' (10060)

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "g:\Mon Drive\prog\parser\test.py", line 5, in <module>
    mydb = mysql.connector.connect(
  File "C:\Python310\lib\site-packages\mysql\connector\__init__.py", line 272, in connect
    return CMySQLConnection(*args, **kwargs)
  File "C:\Python310\lib\site-packages\mysql\connector\connection_cext.py", line 85, in __init__
    self.connect(**kwargs)
  File "C:\Python310\lib\site-packages\mysql\connector\abstracts.py", line 1028, in connect
    self._open_connection()
  File "C:\Python310\lib\site-packages\mysql\connector\connection_cext.py", line 241, in _open_connection
    raise errors.get_mysql_exception(msg=exc.msg, errno=exc.errno,
mysql.connector.errors.DatabaseError: 2003 (HY000): Can't connect to MySQL server on 'z84xh.myd.infomaniak.com:3306' (10060)

please help me, i search 2 entire day on the internet but i found nothing...

i'm not an expert in mysql database, i just want to connect on it like if i go on this website (my host) https://h2-phpmyadmin.infomaniak.com/sql.php

[pine hedge]

is anyone willing to talk to me in vc/dms with me and help me find some ideas for a logical process of storing an accessing certain data for my discord bot?

i can explain the ideal result i'm just lost on how to organize the data

[torn sphinx]

💀 what do ya mean by sit down?

like.. if ya just saying that u gonna be explaining all that via text/vc.. fine.. but.. sit down?

[pine hedge]

eh, just hopping in a vc or talking in dms

[torn sphinx]

owo well i'm cool with DMs if u ok with it rn (cuz it's 2AM here, can't VC at this time)

[pine hedge]

oh alr

that's fine

[torn sphinx]

alr

[harsh pulsar]

a timeout error probably means that the host and/or port are incorrect

[obsidian basin]

When I run the code in visual studio code I am getting the error below

C:\Users\n\.virtualenvs\flaskblog2-leL8dPgn\lib\site-packages\flask_sqlalchemy\__init__.py:851: UserWarning: Neither SQLALCHEMY_DATABASE_URI nor SQLALCHEMY_BINDS is set. Defaulting SQLALCHEMY_DATABASE_URI to "sqlite:///:memory:".
  warnings.warn(
C:\Users\n\.virtualenvs\flaskblog2-leL8dPgn\lib\site-packages\flask_sqlalchemy\__init__.py:872: FSADeprecationWarning: SQLALCHEMY_TRACK_MODIFICATIONS adds significant overhead and will be disabled by default in the future.  Set it to True or False to suppress this warning.
  warnings.warn(FSADeprecationWarning(

But SQLALCHEMY_TRACK_MODIFICATIONS is set to false.
Why am I getting the error.
Also I googled SQLALCHEMY_BINDS it means I have 2 databases.
But I only had 1. I have test.db. Does migrate folder also count as database?
https://flask-migrate.readthedocs.io/en/latest/

Also I am getting an operational error I assume after I migrate I have to create the database using

from app import db,create_app
from app.models import User , Posts, Followers 
app = create_app()
with app.app_context():
    db.create_all()

How do I fix this?

Thanks

[violet grove]

hi, the host is the same i use when i go on phpmyadmin and i tried a lot of different port so idk...

one of my friend success to connect with a c# script, i don't know what he changes, it's the same host and he don't set any port...

but he tried in python and again it failed

[rough viper]

why does my commandline mysql suddenly closes after entering password
pls help

[torn sphinx]

^

[rough viper]

what do you mean by drop table?

[nova forge]

Hey guys I currently had to shift to mariadb from mysql due to server change now the old python script used to work well but now whenever two diff databases are called using the bot it shows error that "MySQL closed connection"

Example:
Bot at first runs a command for database A [this works fine]
Later it run for database B [here it throws error]

I read something abt cursor.close() I dont think thats needed bcs that will close the intial cursor

[nova forge]

Bcs you are entering wrong pass

[harsh pulsar]

mariadb and mysql both use the port number of 3306 by default. try that, or try omitting port= entirely

[harsh pulsar]

!paste show your code. read below for instructions:

[delicate field]

Pasting large amounts of code

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

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

[nova forge]

There aint much code let me show you an example

cursor.execute("use database1")
cursor.execute("") #select query
# using the data fetched

cursor.execute("use database2") # this shows an error

cursor.execute("use database1") # works fine eventhough it threw error

[harsh pulsar]

oh, you are literally trying to switch the currently active database

which mysql library are you using? i wonder if you can even do that, or if you need to open a new connection

definitely do not re-use cursors between execute calls

one cursor per execute

[nova forge]

Thats used to work fine with mysql

[harsh pulsar]

try making a new cursor for each operation

[nova forge]

import mysql.connector as db

connect = db.connect(host="",user="",passwd="")

cursor = connect.cursor()

[nova forge]

Umm like I should close the cursors?

Like once it gets executed?

[harsh pulsar]

hmm... you're probably supposed to. that's quite annoying though

[green sorrel]

ig it's name should be
passwd instead of password

@harsh pulsar hey sir

[nova forge]

Ohh well noone told me thanks for letting me know....Ill fix it thanks a lot man!!

[harsh pulsar]

@nova forge it might be easier to just have 2 separate database connections. or you can maybe write a function to do it, so it's not so many lines of code

def switch_database(conn, new_db):
    curs = conn.cursor()
    try:
        curs.execute(f'use {new_db}')
    finally:
        curs.close()

switch_database(connect, 'database2')

[green sorrel]

so i've been working on python from almost a year now including web frameworks n also freelanced(i have exp in py)
rn im learning to use sql with python but im not that into sql but as im a web dev (backend guy ) so ik the relation n databases/the logic n the modeling of any database but i dont use MySQL as web-framework has it own db (we have tojust write models)/in django , in flask i use phpmyadmin

so i've just started course of techwithtim of py with sql

[nova forge]

Aye thats a good idea but man I have wrote more than 1500 lines with more than 500 sql queries I need to fix all that so yea ill start with it but thanks a lot man!!

[green sorrel]

how long it would take me to get elite in this skill (if im already a software engineer n have gr8 exp in coding)

[harsh pulsar]

in that case i would suggest having 2 separate db connections. you don't want to have to wonder which database your connection is currently connected to

[harsh pulsar]

django does not have its own database. it just is a layer over the database. it will still be mysql, postgres, etc.

[nova forge]

Umm ok a que that if I have this connection opened and if I have simultaneous two cursors is that allowed?

[harsh pulsar]

maybe, that's a good question

[green sorrel]

ik lil bit postgres + phpmyadmin n yeah it uses sqlte3 as default

we just have to know the logics for

[nova forge]

The problem is my script is for python bot and its used at the same time at many servers so if I change cursors for queries then I have to have more cursors at the same time

[harsh pulsar]

to be expert-level you will want to learn the details, not just the high-level things @green sorrel

[green sorrel]

manytomany relationship 1 to 1 , 1 to many/forieng key rows column the main str quering from the db

[green sorrel]

ohh

[harsh pulsar]

if it's for a bot, you will really want to have a connection pool

[green sorrel]

dbs r just for like CRUD

nthg else

[harsh pulsar]

this is not the attitude of an expert

[nova forge]

Umm and ho to achieve that?

[green sorrel]

sry my bad

but my question is

if the web framework is doing all the stuff by own

[harsh pulsar]

https://dev.mysql.com/doc/connector-python/en/connector-python-connection-pooling.html

[green sorrel]

but the default sqlite3 db cant handle alot of requests at same time

so we switch to postgres or mysql

its simple as that

[nova forge]

Aye let me checkout that!!thanks

[green sorrel]

or we have py proj of which we dont wanna store data in .csv or .txt
we will use db's
but mysql.connector()

[harsh pulsar]

sure, although sqlite can handle a surprising number of read-only queries. it mostly gets bad/slow with concurrent writes. but there are good reasons to have a separate database server anyway for a professional project, unrelated to read/write performance.

[green sorrel]

ohh

so this is a seperate field?

same as a full stack web dev or cloud computing engg,etc...

[harsh pulsar]

understanding your tools at a detailed level is necessary in every field, to proceed beyond "advanced beginner"

[green sorrel]

ohh

sure dude

so im following

techwithtim rn

n now i'll use MySQL as db while workiing for clients when making backend of web apps

[harsh pulsar]

yeah, i suggest using the database that you are most familiar with

however, consider the tradeoffs

[green sorrel]

cool man

[harsh pulsar]

with mysql, you or your client has to maintain a separate server and deal with security/permissions, networking, etc

whereas with sqlite the database just lives on the filesystem

[nova forge]

@harsh pulsar Hey I just wanted to ask an out of box que that whats the best panel I can use with vps [free if possible] as I dont want to spend time on cli based controlling

[green sorrel]

oh

postgres op

[harsh pulsar]

postgres same thing

think

one benefit of a central database server that if you have multiple apps or servers/services talking to the same database, then having a single centrally-hosted database means your data all lives in one place. and you can scale up/down the database independently from the servers. and yes, the standalone servers will tend to have better performance

[green sorrel]

damn dude u're genious

umm

mysql syntax r good

but while inserting

why we use

%s

;-;

n what is the use of
.cursor()

why we do all stuff inside it

[torn sphinx]

Hello can anyone one tell me how to create database in python

[green sorrel]

do u have mysql installed on ur device?

[nova forge]

import mysql.connector as db

con = db.connect(host="",user="",passwd="")

cursor= con.cursor()

cursor.execute("CREATE DATABASE database_name")

**Note : ** this for mysql you need to mysql installed and also mysql connector for python

[harsh pulsar]

those are placeholders for query parameters. the mysql library injects data into those placeholders with correct quoting and escaping. programmers cannot reliably do this by hand. look up "sql injection" for examples of how things go wrong if you don't use them.

[nova forge]

@harsh pulsar lol you teach better than my teacher

[harsh pulsar]

this is part of python's "db-api" specification, described in PEP 249. it's basically a compatibility layer over a specific set of functionality and i think requiring the use of cursors was a design mistake

!pep 249

[delicate field]

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

Link

Status

Final

Created

29-Mar-2001

Type

Informational

[nova forge]

Send in your cv am gonna recommend you to my principle

[harsh pulsar]

hah, i've thought about teaching

[green sorrel]

cool man

[harsh pulsar]

that's why i hang out here. sometimes you just need to be pointed in the right direction

[green sorrel]

cool

yeah u can teach

i'm ready to pay for learning from u

[harsh pulsar]

some database libraries let you invoke .execute directly on the "connection" object @green sorrel , but mysql connector requires that you create a cursor first. idk why they did it that way

[nova forge]

Yea you already are sure to get one.....

[green sorrel]

from execute ig it means run in the db (AS name say)

[nova forge]

You have good knowledge!

[harsh pulsar]

yes, exactly

"execute" has 2 very different meanings in english 😆

[harsh pulsar]

knowledge is only one part of teaching. teaching is actually pretty hard and is a whole separate skill

[green sorrel]

lol

so u're a clg student? @harsh pulsar or in job

[nova forge]

Thats true!! I used to tutor maths and it was completely diff than understanding it but yea I got better at what I taught

[harsh pulsar]

i am a software engineer and formerly a data scientist

[green sorrel]

oh

thats also a gr8 reason u're into #databases n stuff 😆

[rough viper]

then how can i reset password of MYSQL?

it would be same as Root password? right?

[nova forge]

@harsh pulsar can help you with that! but you can try https://youtu.be/dyc5b3yT2tI

[harsh pulsar]

i actually have no idea how to do that

[nova forge]

Umm no worries maybe the yt video helps

[rough viper]

guys do i need xampp for mysql and python@nova forge@harsh pulsar

and how to deal with it?

its logs says:

[nova forge]

Naah not at all you can directly install mysql

[rough viper]

then what's the use of xampp?

[nova forge]

Its ofc used for that but you can install mysql too

[rough viper]

Oo

means i can uninstall it now?

@nova forge^

[nova forge]

https://dev.mysql.com/downloads/installer/ use this

[nova forge]

Umm yea if you want to

You can directly install command line based mysql

[rough viper]

Oo

why is this showing now?

@nova forge

@harsh pulsar

[proven arrow]

Because you do not have mysql in the path.

[rough viper]

Oo

how can i add it?

[proven arrow]

I think this is a bug with xampp. I’ve seen it before. Reinstall would fix it, but I just switched it out for another tool.

Not sure if it’s still the same issue but in my case it was not worth trying to fix it or find a solution for it.

[rough viper]

Oo

how can i add to env variable

?

@proven arrow

[proven arrow]

Just click on shell from the xampp window

From there you should be able to access it, like you are doing now.

[delicate field]

Hey @ashen depot!

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

[ashen depot]

Hi All, I'm new here but looking for help in getting the foreign key to work in sqlite3. I have included the section of code relevant to creating the database via python 3.9.5. I have also added a screen shot of what I get as an output. As you can see the primary key works but the foreign key doesn't. I have been looking for weeks to try and find out what the problem is, but can't find a working answer. All help is welcome. Thanks

link to code
https://paste.pythondiscord.com/ihumisigax

[rough viper]

but what about env variable?

[proven arrow]

Looks like the foreign key should be on the games table. A game has a genre no?

[ashen depot]

I was going on that is on the sqlite site to create the link. This not the final version of the database, I just need to get the link working; as I'm still new to database.
https://www.sqlite.org/foreignkeys.html

I can change the genre for an INT to TEXT as needed but either way according to the link above my code should create a foreign key link but doesn't and I can't see why.

[proven arrow]

The example on that site is correct. Just your applying it slightly differently.

[ashen depot]

oh

[proven arrow]

In your example, assuming a game can only have one genre, then the foreign key should be on the child table (on the many side of a one-to-many relation).

[ashen depot]

As for as I know, foreign key is turned off by default and needs turning on. Have I done that right?

In relation to the genre table, it is not complete yet as I will be adding more columns to it; I'm still working out the final layout. It is far from done. As I say still very much a noob at this part.

If I can just get the keys working that will be great. As for the database layout, I'm still figuring that out as well. If any one knows of some good resources I could read, that will be great as well.

[proven arrow]

Alright I understand. Also depending on how complex your design will be, it is usually easier to draw the schema out so you have a better visualisation of the tables and columns and how different tables relate to each other.

[ashen depot]

I have an .ods file for that, but as a noob at it I have no idea if I have done it write. It did give me an idea of the layout but that is not yet included in the example file. Just the first part.
Once I get my head round getting the foreign keys working I can assess the table again to see if the layout is right. After spending so much time on it I can't move on till I have understood how to do it.

If you know some way of checking if the foreign key is enabled via python, then I would at lease know if they are even working.

conn.execute("PRAGMA foreign_keys = ON") I can't tell if it works or not. I have changed the 'ON' to 'YES' '1' and 'TRUE'. All of which say you can use to active it.

[proven arrow]

You can insert some data which will violate the foreign key constraint. You should get an error when trying to do so.

[ashen depot]

ok I will give it ago. but it will take me a few mintes to do so

[ashen depot]

Interesting. was that my code?

https://paste.pythondiscord.com/urebojuris

updated code

it does add data but not to the second table, ( i could have done it wrong , lol)

if the second insert is removed, this is what you get. But as soon as you add it back in, it errors out

https://paste.pythondiscord.com/tuzemoyafi

error in the updated code. fixed now

c.execute("""INSERT INTO genres(genre) VALUES (1)""")
sqlite3.OperationalError: foreign key mismatch - "genres" referencing "games"

I now get this error, well it is progress

[storm mauve]

you might need to ALTER your tables or drop & recreate

[ashen depot]

I have no idea how to do that. Is there a good resource you could point me too so I can read up on it.

[storm mauve]

do you have any data in the tables right now?

[ashen depot]

only what you see in the screen shot above. As it is a test table. Still in the process of build the game database program

[storm mauve]

oh wait never mind

[ashen depot]

nvm? still a bit of a noob myself

[storm mauve]

(when I tested I ran it out of order)

[ashen depot]

do you get the error above. I still think it may be that I haven't or can't enable the foreign key in my build of python 3.9.5 for some reason.

[storm mauve]

Ok, it looks like the issue is that the column referenced by the foreign key must either be the primary key or have an UNIQUE constraint

either way, in this case you probably should have the category_genre column in games reference the genre column in genres instead of the other way around

c.execute("""CREATE TABLE IF NOT EXISTS genres (
    genre INT PRIMARY KEY
)""")

c.execute("""CREATE TABLE IF NOT EXISTS games (
    id_game_main INTEGER PRIMARY KEY AUTOINCREMENT,
    title TEXT,
    description TEXT,
    publisher TEXT,
    game_barcode TEXT,
    platform_disc TEXT,
    platform_cart TEXT,
    platform_eshop_linux TEXT,
    platform_eshop_windows TEXT,
    platform_eshop_mac TEXT,
    category_genre INT,
    FOREIGN KEY(category_genre) REFERENCES genres(genre)
)""")


c.execute("""INSERT INTO genres(genre) VALUES (1)""")
c.execute("""INSERT INTO games(title, category_genre) VALUES ("test", 1)""")

[ashen depot]

thanks, I will give it ago.

It is tea time here in the UK so I have to go and eat. Thanks everyones help, but my time is up here. Thanks again.

[full geyser]

what ORM do ppl use for sqlite stuff?

[grizzled wadi]

I would recommend https://github.com/RobertCraigie/prisma-client-py

Although massive disclaimer: I am the maintainer

[storm mauve]

I'm pretty sure that SQLAlchemy should support SQLite, but you probably do not need of an ORM at this point tbh
(not much harm in using one though)

[full geyser]

ngl that looks awesome, but i'd rather go with SQL Alchemy for this since i'm teaching it to a beginner friend, and i think a schema+generator would be too complicated for them

[grizzled wadi]

Thank you and fair enough. Do you think the fact that it has to be generated makes it potentially too confusing for beginners?

On the schema side of things there's both positives and negatives for beginners compared to SQLA. On the one hand if you're using VSCode you can install the Prisma extension and you'll get really good autocomplete, pretty formatting and error checking without the noise of the Python equivalent. But even still, you have to write it in a DSL, you can't write the schema in Python which would be ideal for beginners so they can learn about classes and so forth. Another downside is the lack of Python specific tutorials for it, although I am working on that.

[full geyser]

personally i don't find it complicated at all, but i'm also used to generated stuff from libraries such as protobuf and thrift - to me your examples in the README make perfect sense
when you say beginners, it depends how beginner you mean - my friend is just learning classes now, so even though your schema file is fairly simple, i'm not sure having him learn 2 languages and a tool to combine them is that simple for his level

[grizzled wadi]

Yeah I get that, someone who's very new to python would probably find having to use two separate languages somewhat daunting. I need to try and figure out if there's anything I can do to make this easier for them

[full geyser]

honestly, i'm not entirely sure they should be your target audience

[grizzled wadi]

that's a fair point actually

[clear stirrup]

does prisma handle alter column in sqlite databases?

[full geyser]

like, an advantage of your schema file (i assume) is being able to statically check it against your database itself - no beginner is going to make use of that

[grizzled wadi]

You can make changes to your schema file and propagate the changes to your sqlite db with Prisma Migrate https://www.prisma.io/migrate

[clear stirrup]

neat

[grizzled wadi]

Yeah it's really cool and if you're just wanting to make straight up changes to the database without having to deal with migrations you can run prisma db push

[clear stirrup]

Yeah that's also really nice

[grizzled wadi]

Yeah that is a core selling point, I know a lot of people don't actually use static type checking yet but the benefit for them is the autocomplete you get

[clear stirrup]

though I don't really get how that would work with like, setting new defaults or other things that aren't easily reversible

[grizzled wadi]

Which is even more useful because it highly decreases the learning curve

[grizzled wadi]

Yeah migrations are really complicated, Prisma doesn't actually support rollback migrations for that very reason. If your forward migration breaks for some reason and you need to rollback the changes then the rollback migration could break even more

rollback migrations can also give you a sense of false security as you would think, "oh if my migration doesn't work I can always just run a rollback migration", but they can fail in subtle ways or just straight up not work because the data in the database is incompatible with the changes

[clear stirrup]

Yeah, I've had to deal with it in django. I'm fairly comfortable with django's migrations and writing custom reverse scripts in the migration files

I'm trying to reason about how automatically updating the database as you are changing the schema would work

the autocomplete/typehinting is really cool though. It's something that's fairly lacking in django's orm

[grizzled wadi]

Yeah the autocomplete and the typing is the main reason I built it. There's no other Python ORM that can offer it to the same extent because of the codegen

The only annoying thing with the autocomplete is that not all editors will autocomplete TypedDicts yet :/

[rapid narwhal]

how do i get my postgres database url from herouku

[clear stirrup]

I'm using neovim with pyright

I think it will work

[grizzled wadi]

Yeah that'll work

[hoary badge]

hello

can any one help me

i have a question

if any one familiar with django admin pannel databases

actually i am new in databases and django framework

how do i add more columns in auth_user

i migrated admin db to mySQL db

this right here holds all admin info, any way to create a model so that i can add more fields

[clear stirrup]

you want to create a custom user model OR create a new table with extra data that has a one to one relation with the user model

[rapid narwhal]

me?

[clear stirrup]

hooman

[grizzled wadi]

I haven't actually used neovim though, I just know that pyright has support for autocompleting it so if you do try it out could you let me know if it works for you please @clear stirrup

[clear stirrup]

custom user model is better, but if you've already created the database and you can't start over from scratch it might be annoying to try to migrate to it

[hoary badge]

i actually looking to create custom one, but as new to django i discovered django authentication system, i wanna create signup model, so i was thinking if i add more field to auth_user and use the existing one

[clear stirrup]

Yeah I'm interested in giving it a try. I've been trying to find an ORM external to django's that I like using and prisma does seem nice

I'll let you know

[grizzled wadi]

Thanks :)

[hoary badge]

how can i do that in the existing table ? actually auth_user was already created

[grizzled wadi]

Yeah I didn't actually use an ORM before I created Prisma Client Python because I found them annoying to use because of the learning curve and then if I wanted to use a different language I'd have to relearn everything again

[clear stirrup]

https://docs.djangoproject.com/en/4.0/topics/auth/customizing/#extending-the-existing-user-model

[hoary badge]

okayy i take a look

[clear stirrup]

ideally you would want to look at "substituting a customer user model"

[clear stirrup]

That's probably a smart way to go about it. I feel like I've had to fill in sql knowledge gaps because I only understood sql through the django orm

[hoary badge]

Alrightt

[clear stirrup]

it's hard to say without seeing the connection string. Could you paste it with changed values?

[rapid narwhal]

Yeah

you need the url right

[clear stirrup]

the url inside of your .env

yes

[rapid narwhal]

postgres://qftweqetkrgzp:[e-mail]:5432/dep23525

[clear stirrup]

and where are you trying to connect from?

inside of your heroku app?

[rapid narwhal]

pc

[clear stirrup]

you are trying to connect to your db through psql?

locally?

[rapid narwhal]

yeah

[clear stirrup]

there might be an ip whitelist

maybe you need ?sslmode=require

https://devcenter.heroku.com/articles/connecting-heroku-postgres#external-connections-ingress

[grizzled wadi]

Yeah even if you use an ORM you should understand how SQL works so that you don't accidentally run into a massive performance problem because you haven't added an index somewhere

[clear stirrup]

too late

[kindred pagoda]

Why doesn't the "mongo" database add more than 4 members to the database list?(discord.py)

[torn sphinx]

You need to provide more info, we (unfortunately) can't read minds around here. Show us what you've tried, and what isn't working.

Show us your code, etc.

[runic narwhal]

r/place is coming back and we'll have some interesting data to work on !

Do you have any creative ideas on how to visualize it ?

That could also be a great opportunity to make the Python logo using bots

[shadow fossil]

hey guys, Ihave a code that I will show you, and in tis code I'd like to vizualize my databse with SQL. My problem is that idk what is the "right SQL".

It's a simple db :

import sqlite3
nb_clients = int(input("Combien il y a t-il de clients ?"))
conn = sqlite3.connect('my_database.sqlite')
cur = conn.cursor()

cur.execute ('''DROP TABLE IF EXISTS clients''')
cur.execute('''CREATE TABLE clients (id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE, name TEXT, email TEXT)''')

for x in range(nb_clients) :
nom_client = input("Nom (client) : ")
email_client = input("email (client) : ")

cur.execute('''INSERT INTO clients(name, email) VALUES (?,?)''',(nom_client, email_client))



conn.commit()

for row in cur.execute('''SELECT id, email, name FROM clients'''):
print(row)

cur.close()

What I have to install to vizualize that on a board ???

Please mention me if you can help !

Thanks in advance

[torn sphinx]

your code with formatting: ```py
import sqlite3
nb_clients = int(input("Combien il y a t-il de clients ?"))
conn = sqlite3.connect('my_database.sqlite')
cur = conn.cursor()

cur.execute ('''DROP TABLE IF EXISTS clients''')
cur.execute('''CREATE TABLE clients (id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE, name TEXT, email TEXT)''')

for x in range(nb_clients) :
nom_client = input("Nom (client) : ")
email_client = input("email (client) : ")

cur.execute('''INSERT INTO clients(name, email) VALUES (?,?)''',(nom_client, email_client))

conn.commit()

for row in cur.execute('''SELECT id, email, name FROM clients'''):
print(row)

cur.close()

it looks correct, any errors ?

can someone please help me im getting this error when trying to create a database:
c.execute("INSERT INTO Workout (Exercise,Reps,Sets,RPE,Weight) VALUES(?,?,?,?,?)", (self.Exercise,self.Reps,self.Sets,self.Rpe,self.Weight))
sqlite3.InterfaceError: Error binding parameter 0 - probably unsupported type.

[waxen finch]

self.Exercise likely isnt a type that sqlite supports

i.e. it has to be one of None, int, float, str or bytes

[torn sphinx]

thanks!!

[torn sphinx]

Hello is it possible to verify that primary key contraint is fulfilled in a dataframe before inserting it into database table?

[brave bridge]

Why do you want this?

[torn sphinx]

I'm using sqlalchemy

Prevent from inserting if the primary key contraint is not fulfilled

[brave bridge]

If the constraint is not fulfulled, you will get an exception on insertion.

Just catch it.

[torn sphinx]

That's another way of doing it, i wondered if there was a way to tackle it using sqlalchemy inspect or something like that

[brave bridge]

You can't really predict it in advance until you try to commit the transaction. A transaction gets a consistent view of the database, so if someone changes the database while your transaction is in progress, you will only observe the change when you try to commit the transaction.

[torn sphinx]

Roger that, but suppose the following scenario though. A db table is empty and I have the df that will be inserted into that particular table. If the column of df that corresponds to primary key column in the database table has duplicate values, you can conclude that the insert operation will fail with the corresponding exception

[half forum]

Hey again, was wondering, how do I delete a specific line or item in an array in json
Like js { 192389128398123: { 120391023, 1203901293 } } and I want to remove 1203901293

[hexed estuary]

that's not valid JSON:

1. JSON keys can only be strings, not ints
2. JSON doesn't have sets

[rough viper]

why print statement is returning None?
whereas for loop not?

[nova forge]

@harsh pulsar hey so I rewrote the whole program by implementing mysql pool but the pool only allows 32 connections and yea if I close cursor do I need to close connection too?

[rough viper]

can i do delete these?

[torn sphinx]

DROP DATABASE db_name;

oh wait

u talking about deleting those default onesS?

eh.. idk.. never thought of doing..

:'/

[rough viper]

lol

can i hide these because whenever i do 'show databases;' these all appears and create crowd which is of no use. so any way of hiding them from showing?

[kindred pagoda]

What is this error?
Command raised an exception: TypeError: '_asyncio.Future' object is not subscriptable

[torn sphinx]

ik it's annoying to see them.. but i dont think we can hide 'em

:'/

[viral island]

Can anyone suggest best resource for learning DBMS

[grim vault]

SHOW databases WHERE `Database` NOT IN ('information_schema', 'mysql', 'performance_schema', 'sys');```

[torn sphinx]

I don't think it's convenient for anyone to type that much.. someone would just prefer to ignore them instead

Anyways.. that's cool

[torn sphinx]

For quick learning you can check out SQL course of sololearn and for practice u can use sqlbolt

P.S: idk if it's the best resource lol

[grim vault]

What I've read it's dependent of which privileges the user has which does execute the command.

[torn sphinx]

I got u bro.. but don't u think that someone would just prefer to ignore them instead of typing that much💀 (in case of sql terminal, not program)

[grim vault]

I think I'm not using mysql, so I do not have an opinion about it. How often does one use the command to get annoyed by four entries?

[torn sphinx]

About others? Idk
About myself? I rarely use lmao.. cuz ik what dbs I've created

[loud merlin]

how to make table in sqlite database like:

user_id | item_name | amount
----------‐-----‐-------------------------------------------
63826318272915| item player has | amount of each
... | obtain | items
... ...

[nova cove]

CREATE TABLE [IF NOT EXISTS] table_name (
  col_name datatype [other fuckery],
   …
);

What flavor of SQL database are you using

[queen bloom]

is there an orm for aiosqlite?

[nova cove]

Prisma client is an ORM for almost all SQL flavors

so is SQLAlchemy

[harsh pulsar]

no, the point of the connection pool is that the connections all stay alive for the lifetime of the application. the cursors are short-lived; one per query/request.

[queen bloom]

is it async

[harsh pulsar]

prisma is a js library. there is python support for it, but i'm not sure it qualifies as an orm

[harsh pulsar]

normally orms like sqlalchemy are "higher level" and use libraries like aiosqlite as backends

[nova cove]

The whole point of Prisma is to be an ORM

[queen bloom]

ah

[harsh pulsar]

https://prisma-client-py.readthedocs.io/en/stable/ i stand corrected, it is actually an orm

i thought it was more of a parser for the schema format

[nova cove]

🗿

[nova forge]

Ohh so I can have as many as cursors from the same connection and then close them....

[harsh pulsar]

normally you would fetch a connection from the pool, create a cursor, execute your query, then release the connection

[nova forge]

Umm I am closing the cursor after am done with it

[harsh pulsar]

yes, good

[nova forge]

Then create new one when needed

But it has limit of 32

[harsh pulsar]

like this @nova forge

from contextlib import closing

@bot.command
async def something(self):
    db_conn = pool.acquire()
    try:
        with closing(db_conn.cursor()) as cursor:
            cursor.execute('query1')
        with closing(db_conn.cursor()) as cursor:
            cursor.execute('query2')
        with closing(db_conn.cursor()) as cursor:
            cursor.execute('query2')
    finally:
        db_conn.release()

ideally the library provides with integration but you can do without in this case, using contextlib.closing

!d contextlib.closing

[delicate field]

contextlib.closing

contextlib.closing(thing)```
Return a context manager that closes *thing* upon completion of the block. This is basically equivalent to:

```py
from contextlib import contextmanager

@contextmanager
def closing(thing):
    try:
        yield thing
    finally:
        thing.close()
```...

[harsh pulsar]

ah i see how it works in mysql, you just provide the pool name https://dev.mysql.com/doc/connector-python/en/connector-python-connection-pooling.html

so yes, in this case you do just .close the connection

but when you connect, you need to specify that you are using a connection pool

i.e. calling .close on a connection from a pool doesn't close it; it just releases it back to the pool

although personally i would use the "explicit" method described in that page, with MySQLConnectionPool and get_connection

[nova forge]

Yea I just do that

Get_connection --> create cursor --> use it --> close cursor --> close connection

[nova forge]

Yea created a connection pool and from it fetching

Am just worried what happens if 32 connections are crossed

[harsh pulsar]

crossed?

[nova forge]

I mean exceed that limit

[harsh pulsar]

good question. the docs should explain this. hopefully it just "blocks" (i.e. waits), but i guess it's possible that it raises an exception or something similarly idiotic

[nova forge]

Yea am thinking of errors but yea it wont be a problem for a while as my bot is not that famous but yea it may cause issues in future

[harsh pulsar]

the point of connection pooling is to prevent errors by forcing the application to wait until you have a free connection in the pool

For each connection request, the pool provides the next available connection. No round-robin or other scheduling algorithm is used. If a pool is exhausted, a PoolError is raised.
ahh... yikes

this is what the docs say

[nova forge]

Umm so if the new connection is made then there will be error shown

Well o well I can do one thing

[harsh pulsar]

yeah, that's annoying. you have to try/except PoolError and... try again later? that's stupid and really difficult to use

[nova forge]

That if the connection fails in first attempt then use try except and again try the same thing waiting for few secs

[harsh pulsar]

this almost seems worse than just reusing one single connection, for basic usage

[nova forge]

Sheesh man we said the same thing!!!!!!

[nova forge]

Yeah at first I thought there was no limit I set it to 2000 later it threw error

[harsh pulsar]

keep in mind that this library isn't async anyway. i wonder if there is a better option out there for simpler use

just because it's "official" doesn't mean it's the best for a small hobby application

i see a lot of people using aiomysql https://aiomysql.readthedocs.io/en/stable/

[nova forge]

Umm thats true

[nova forge]

Never used that but ill check that out

[harsh pulsar]

they have a proper high-level connection pool too https://aiomysql.readthedocs.io/en/stable/pool.html

async with pool as conn:
    cur = await conn.cursor()

this is the interface i would expect to see

[nova forge]

Yea but ukw man

For setting that up

I need to again change more than 600 lines of my code.....

https://tenor.com/view/baby-crying-baby-crying-gif-5943733

[nova forge]

Wish I knew that before rewriting my code twice

[harsh pulsar]

maybe just keep it in mind for the future

for now, i'd say just use one connection and don't bother with the pool business

since your app doesn't have high traffic, i wouldn't worry about two cursors on the same connection.

do keep in mind that because you are not using an async database library, a long-running query will block up the entire discord bot

[tender salmon]

is it possible to make warnings different on other servers? Type not global.

sqlite3

[nova forge]

Yupo I have to

[nova forge]

Sheesh man I already rewrote my code for pool

[harsh pulsar]

what kinds of warnings? can you be more specific?

[nova forge]

It was creating error but with pool it aint

[harsh pulsar]

it happens. sometimes you have to do something before you realize that you forgot to ask X Y and Z important questions before doing it

[nova forge]

Thats true well if my bot boosts up then ill consider rewriting code with async one

[harsh pulsar]

i see. another option is to put a lock around the database so that only one command can access the connection at a time

[nova forge]

Lol and am the one who asks lots of ques but skipped those even though those popped up at that time

[nova forge]

Thats a good idea

[harsh pulsar]

it also helps to mistrust strangers and to read the docs for yourself 🙂

otherwise i guess you can poll with a delay and retry, but that's really bad. there are other options (such as setting a "waiter" event that wakes up when the pool has a free connection) but at that point you are reimplementing stuff by hand, that would be part of an async library by default

[nova forge]

😀 I hate docs tbh I only read docs for threading and nginx docs

[harsh pulsar]

have to get used to it. without docs programming literally wouldnt be possible at the scale we do it at

[nova forge]

Umm I guess I need to research abt async....

[nova forge]

Very true but no worries ill start looking into async docs and see if it can be implemented without too much hardwork in existing code

[tender salmon]

store the guild ids with the user maybe or create tables for each guild?

[harsh pulsar]

are you asking about the "waiter" business? the docs won't help. look at the implementation of asyncio.Queue if you really want to see how this pattern looks in practice

[harsh pulsar]

i see, so you have some kind of moderation bot and you are warning users? you need to make your actual task clear

[nova forge]

Umm naah am talking abt implementing async database library

[harsh pulsar]

you definitely do not want to do that on your own

you'd be far better off using aiomysql than trying to async-ify mysql connector

[nova forge]

No I dont mean making the whole library am talking abt using the already made one

[nova forge]

Aye I should be

[nova forge]

Man thats a lot of queries ill just say if you wanna store user id and guild id store in one table that will be easier then making diff tables for each guild

@tender salmon

cursor.execute("CREATE TABLE server (serverno int(11) PRIMARY KEY auto_increment,guildid varchar(50) NOT NULL,userid varchar(50) NOT NULL,reason varchar(100))")

There you go

Now if you wanna search for a user if he did something in past

Just search for the user with WHERE userid = 'user.id' AND guildid = 'guild.id'

And yea use varchar and not int bcs theres a limit on how big a number can be with int format and ids of discord always exceed that

So when selecting id just change it back to int using int()

[harsh pulsar]

i would store the guild id with the user on one big table

i agree, different table per guild seems like a mess

i would make the columns warning_id, guild_id, user_id, and then whatever info you need for the warning (timestamp, reason, etc)

[grim vault]

or use BIGINT which is the 64bit version which should do.

[nova forge]

New knowledge stuff!!Thanks for that Ill lookup that

Some one recently even told me abt varchar2

[nova forge]

Ikr

@harsh pulsar mind if I add?

[pure mortar]

https://www.singlestore.com/

apparently really good at both OLAP and OLTP

[harsh pulsar]

wasn't there some other database posted recently like this? and it turned out they just added 3 indexes to literally every field to make filtering-type queries really fast

We deliver the industry’s first modern relational database for multi-cloud, hybrid and on-premises — bringing you speed, scale and immediate insights.
🙄

the main pitch seems to be that it does everything in one database, instead of one database for analytics, one database for storing realtime data, etc

maybe too good to be true? or maybe just really expensive

[pure mortar]

probably both

like they say its fast but how fast is fast

[pure mortar]

that was me as well

this channel mostly feels like just SQL questions so i try to spice it up

[harsh pulsar]

hah i thought that was you

[pure mortar]

like dont get me wrong relational databases are the bread and butter for most apps

but theres use cases for this new stuff as well

[tender salmon]

Why doesn't the bot take the full name of the server?
erorr: OperationalError: near "Greatest": syntax error.

[random bobcat]

@oak oyster

is SQLite good?

or what do u prefer for beginners like me

[storm mauve]

SQLite is a good place to start learning about databases imo

you can take a look into Postgres later, but for local development, sqlite is fine

[random bobcat]

ohhh

okkkk

[harsh pulsar]

you need to show your code when asking for help about an error

[tender salmon]

name = ctx.guild.name
    cursor.execute(f"""CREATE TABLE IF NOT EXISTS {name} (guild BIGINT)""")

You wanted the code, keep it. Point out the error and help fix it.@harsh pulsar

Dude,can you help me? @harsh pulsar

Just a capricious person

[rough hearth]

@tender salmon salt rock lamp is a volunteer. Please be patient. It's not guaranteed that they're still available.

You are likely to get a better answer if you share the whole error message starting from Traceback: and several lines of code before and after the one that causes the error.

[pale oriole]

hey guys so I have a question, I am building a Project (Dashboard) in dash plotly.
Now I am using data from kaggle with about 12.5 million rows of sales.
I created a postgres database made the tables everything is ready.
The first obvious kpi in my dashboard is the total profit, now to find that out the query takes quite a long time to process.
and that popped a question in my mind, how do big companies that have billions of rows make an interactive dashboard that works fast? After all we don't want to wait 10 minutes for the dashboard to just load up.
is there a way to make a kind of ready queries or like views that calculated the values before we even make the select query and of course update the values if we insert new values?

[storm mauve]

(partially guessing)
cache / run the queries first then save the results for later
or keep track of the running total and add to it every time instead of recalculating it

though, just taking the sum of one column in a 12 million rows dataset should still be relatively fast

[alpine orchid]

@civic crypt ask your question again in a new, I have a possible fix

and ping me in it

[civic crypt]

i just put it outside the class

because it was too annyoing

@alpine orchid

[alpine orchid]

Okay, I can show you an alternative if you like

up to you

[civic crypt]

if u have something working i will use it instead

[alpine orchid]

mydb = mysql.connector.connect(
host=config['db_host'],
user=config['db_username'],
password=config['db_password'],
database="s72_economy"
)
with mydb as sql_client:
    if sql_client.is_connected():
        db_Info = sql_client.get_server_info()
        print("Connected to MySQL Server version ", db_Info)
        mycursor = sql_client.cursor()

this is how I do my mongo connections, using with

[civic crypt]

the error doesnt show up when that is called

its when i try to use it later on

i try mydb.execute or something then it says its not defined