databases

[harsh pulsar]

(i do not know the answer, sorry)

you might want to search for something like "write json sqlalchemy"

[dull ember]

anyone is good at coding and can send me a good tutorial on youtube?

[slim trench]

I mean I can add JSON type, but I don't know(not sure) How can I achieve that with this?

[
        {
        //data
        },
        {
        //data
        },
        {
       //data
        },
        {
        //data
        }
        
],

I mean If I choose JSON then I can add this into the PostgreSQL(data output)?

[harsh pulsar]

i think you should be able to put that data into the appropriate field in your model, and then save your model as normal

or do you want to insert 1 row for each of those dicts in that list? in that case, you'd just loop over the list creating model instances, and then do a batch insert

[slim trench]

No, I just don't know what is the type for this.
I mean, If I set JSON as the data type, I get an error.

[harsh pulsar]

it helps if you show your actual code and the error

[slim trench]

this is what I'm trying now.

[slim trench]

I've been trying to figure out, what happened. Now, If I set the type as JSON, it looks like I don't get any error. When I try to add it to the data output, I just can't type it.

any tips? @harsh pulsar

[harsh pulsar]

what do you mean by "can't type it"?

json does seem like a more reasonable option than pickle

[slim trench]

I mean I can't access that input field.

maybe I should refresh my admin site.

[harsh pulsar]

https://docs.sqlalchemy.org/en/14/dialects/postgresql.html#sqlalchemy.dialects.postgresql.JSONB
https://docs.sqlalchemy.org/en/14/dialects/postgresql.html#sqlalchemy.dialects.postgresql.JSON
i suggest looking at the sqlalchemy docs for using postgres json and jsonb

[slim trench]

I can't type.

Hard to say what is wrong, If I can't test it out.

[harsh pulsar]

maybe you need to re-create the table? i don't use sqlalchemy so i'm not totally sure. but you should at least be able to follow the examples

it's possibly not letting you type in data because the data is in binary format, not text

is this interface pgadmin4?

[slim trench]

yes

@harsh pulsar I'm trying to add an array, but I getting this error. "{" is not a dict? so the array must start "[", isn't it?

[harsh pulsar]

is this a postgres "array" or "jsonb" field?

[slim trench]

so {"test", "test2"} will return:

[harsh pulsar]

don't get confused between python syntax and postgres syntax

[slim trench]

okay

[harsh pulsar]

consider the differences:

• json syntax for representing objects, arrays, and strings
• postgres syntax for inputting json
• python syntax for inputting dicts, lists, and strings
• the correspondence between python data types and json data types

[slim trench]

Thanks

[red oasis]

https://hastebin.com/yerafaciri.rust - error

whitelisted = fields.ManyToManyField("Bot.UserModel", on_delete=fields.SET_NULL, null=True, related_name="whitelisted")
vanity_whitelisted = fields.ManyToManyField("Bot.UserModel", on_delete=fields.SET_NULL, null=True, related_name="vanity_whitelisted")

That error is coming from those 2 lines...

@harsh pulsar do u mind checking this out?

[torn sphinx]

How to insert this type of value in sqlite3 ?? - Dies Irae: Marie's Memory "Michi ni Tsuuzu Kiseki"

anime = '''Dies Irae: Marie's Memory "Michi ni Tsuuzu Kiseki"'''
cmd = f"""INSERT OR IGNORE INTO {TABLE_NAME} VALUES ("{anime}")"""
cursor.execute(cmd)

[slender atlas]

Use proper query parameters

cursor.execute("INSERT OR IGNORE INTO $1 VALUES ($2)", (TABLE_NAME, anime))

You could also use ? but I prefer using $N

Wait, I don't think table names can be query parameters

[torn sphinx]

humummmmmm

[slender atlas]

What is TABLE_NAME anyway

It seems it is a constant, so you should simply write the name of the table in the string

[torn sphinx]

I'm using the script for multiple database

it's eazy to change the name

[slender atlas]

Well, if TABLE_NAME is always a string containing a valid name, you can f-string that part

[torn sphinx]

btw its work??

[slender atlas]

cursor.execute(f"INSERT OR IGNORE INTO {TABLE_NAME} VALUES ($1)", (anime,))

[red oasis]

Roie do u mind checking my issue?

[slender atlas]

I don't know the library

[red oasis]

yes but do u know why the value could be none?

[slender atlas]

What do you mean by value

[red oasis]

that's a model that should contain a value

[torn sphinx]

u know any way to make it sql injector free

[red oasis]

roie i will send u my user model

[slender atlas]

Just write whatever TABLE_NAME is in the string and don't make it an f-string then

[slender atlas]

I prefer if you send it here

[red oasis]

class UserModel(DiscordDBModel):
    """Base User model, meant to be reference by extensions"""

    def discord_get(self, bot: discord.Bot) -> discord.User:
        """Returns the associated user for the given User Model."""

        return bot.get_user(self.id)

yes here

[slender atlas]

You're creating a database for every user?

[red oasis]

that is being called yes

[slender atlas]

Normally you'd have a table in a database wherein you store rows by user IDs

[red oasis]

if u read the entire file it will make more sense why this is happening

[slender atlas]

What are you trying to implement

[red oasis]

basically what this will do is create a table for "trusted members" everytime it will be called the usermodel will be added to the value

this is a Antinuke System its basically a trusted user lists for normal members

[slender atlas]

Why do you need a table for each individual trusted member

[red oasis]

and another whitelisted list but for vanity users

[red oasis]

its just being added automatically to the usermodel when being called so its just going to store there its ID

[slender atlas]

What's usermodel

[red oasis]

the bot will just get the user id

that error didnt even tell me where the error is coming from

so i slowly had to init my db using aerich to see where the issue is coming from

and it resulted from those 2 lines

tortoise.exceptions.OperationalError: syntax error at or near "None"

what this tells me is that the value is empty lol

[slender atlas]

I think the problem is you're using f-strings?

[red oasis]

for related_name ?

[slender atlas]

For the query that raises this exception

[red oasis]

the migrations sadly won't appear because is not being innited

i mean yes it could be an string

but where tf in the model am i adding that lol

because i am clearly not

all im doing is to set it null on_delete

and ofc null=true because i don't want it if the owner won't allow it

[slender atlas]

What's fields

[slender atlas]

In these lines

[red oasis]

its coming from tortoise

it's an import

because im using tortoise to load everything

[slender atlas]

What's tortoise

[torn sphinx]

thank u bro

sankyu

arigatou

[red oasis]

1 sec

[torn sphinx]

douzo yoroshiku onegaishimasu

[red oasis]

https://tortoise-orm.readthedocs.io/en/latest/

and im using it for postgreSQL

https://pypi.org/project/tortoise-orm/

[tight junco]

    result: typing.Optional[asyncpg.Record] = await self.database_pool.fetchrow(query, *data)
AttributeError: 'WarnDB' object has no attribute 'database_pool'```
am getting this error, im using postgresql and heres my WarnDB database_pool attribute

```py
class WarnDB(DatabaseModel):
    async def setup(self, bot: commands.Bot) -> None:
        self.database_pool = bot.database_pool```
what could be causing this?

[red oasis]

@slender atlas Here u can see an example of fields

basically a field can be text, bool...

discordcolor too ofc

[slender atlas]

Fields=columns?

[red oasis]

yes

[slender atlas]

Ok

[red oasis]

https://hastebin.com/hehobuluwi.py here u can see the entire models file

[tight junco]

still need help with this

[red oasis]

@slender atlas like i said it will be an list, that's why im pulling the ID from usermodel

[slender atlas]

@red oasis to me it looks like the library is funky and failed to convert None to NULL or something

In some query

[red oasis]

that's probably whats happening but idk

[slender atlas]

Honestly sad

[red oasis]

roie

i don't think so

because

logs_channel would be also None at first

and that one is not causing any errors

[slender atlas]

Maybe it's just a screwup with fields.ManyToManyField

It's weird because I would assume everything in the library uses the same formatting function for queries or something

[slender atlas]

The function in which you are using self.database_pool.fetchrow might be running before setting up self.database_pool

I know.

I don't know why the error is thrown

[tight junco]

no

Traceback (most recent call last):
  File "C:\Users\Scratcha\AppData\Local\Programs\Python\Python39\lib\site-packages\discord\ext\commands\core.py", line 200, in wrapped
    ret = await coro(*args, **kwargs)
  File "d:\Mod-Bot\Mod\extensions\moderation.py", line 182, in warn
    await self.bot.warn_db.warn_entry(ctx.guild.id, member.id, reason, ctx.message.created_at.timestamp())
  File "d:\Mod-Bot\Mod\database\db.py", line 129, in warn_entry
    data = await self.warn_log(guild_id, member_id)
  File "d:\Mod-Bot\Mod\database\db.py", line 110, in warn_log
    data = await self.exec_fetchone(
  File "d:\Mod-Bot\Mod\database\postgre.py", line 40, in exec_fetchone
    result: typing.Optional[asyncpg.Record] = await self.database_pool.fetchrow(query, *data)
AttributeError: 'WarnDB' object has no attribute 'database_pool'```

this is the full traceback if it helps

[red oasis]

@slender atlas all i know is that is not coming from null=True

lemme see where 1 sec

u may be right

coming from fields.ManyToManyField

do u have in mind any solutions?

[slender atlas]

Nope

See if there's anything online about your specific fields.ManyToManyField use

[red oasis]

im lookin

i don't find the error i get lol

for some explanation

nvm i found a similar one

nvm i just cant find out lol

[paper flower]

Are you sure that your setup function is called?

[tight junco]

yeah

[paper flower]

Where? 🙂

[paper flower]

@tight junco
You're not calling your setup method anywhere, if you want to pass dependencies into class instance you should use __init__:

class WarnDB(DatabaseModel):
    async def __init__(self, database_pool: asyncpg.Pool) -> None:
        self.database_pool = database_pool

[tight junco]

i dont do that in any other db class though

well i am already doing this

class WarnDB(DatabaseModel):
    async def setup(self, bot: commands.Bot) -> None:
        self.database_pool = bot.database_pool```

[paper flower]

setup isn't called anywhere unless i'm missing something

[tight junco]

0-0

OHH

yeah

gotchu

thanks a lot buddy

[long skiff]

Has anyone used firebase and what was your experience with it like?

[bitter summit]

how would I create a new column called "total_charges" via multiplying charges * payment_period? Depending on the payment_period string, it would multiply by a varying value, for example, quarterly would just multiply it by 4, monthly by 12 and so on

i have a feeling you have to use if statements but I don't know how to implement it in this case

[tiny eagle]

I've got this query that returns multiple records, though I'd like to group it by a tuple of the name and its corresponding roles: py await bot.pool.fetch('SELECT name, role_id FROM punishment_info JOIN punishment_roles ON punishment_info.id=punishment_roles.id WHERE guild_id=$1', guild.id)Which returns [<Record name='test' role_id=1002662771281907723>, <Record name='test' role_id=1004721926524125247>, <Record name='toxicity' role_id=1002662771281907723>, <Record name='toxicity' role_id=1001916770224967760>]

For example, (test, (1002662771281907723, 1004721926524125247))

[tight junco]

how can i see heroku postgresql tables?

[slender atlas]

That column can be what's called a generated column

[fading patrol]

However you like. DBeaver is probably easiest if you don't know where to start

[tight junco]

can i use pgAdmin?

i downloaded it already

dunno what to do next

[fading patrol]

Yes, same concept

[fading patrol]

https://dbeaver.com/docs/wiki/Create-Connection/

[slender atlas]

Try

SELECT name, ARRAY_AGG(role_id)
    FROM punishment_info
    JOIN punishment_roles USING (id)
    WHERE guild_id = $1
    GROUP BY name

as a query

[astral geyser]

IntegrityError
sqlalchemy.exc.IntegrityError: (sqlite3.IntegrityError) UNIQUE constraint failed: user.password
[SQL: INSERT INTO user (username, password) VALUES (?, ?)]
[parameters: ('arvin1', '0201316617')]
(Background on this error at: https://sqlalche.me/e/14/gkpj)

do you know what does this error mean?
when ever im trying to do something with the database this comes up :/

from ToDo import db, login_manager
from flask_login import UserMixin

@login_manager.user_loader
def load_user(user_id):
    return User.query.get(int(user_id))

    
class User(db.Model, UserMixin):
    id = db.Column(db.Integer, primary_key = True)
    username = db.Column(db.String(20), unique = True, nullable = False)
    password = db.Column(db.String(50), nullable = False)
    works = db.relationship('Works', backref = 'author', lazy = True)
    def __repr__(self):
        print(f'User("{self.username}", "{self.password}")')


class Works(db.Model):
    id = db.Column(db.Integer, primary_key = True)
    name = db.Column(db.String(100), nullable = False)
    user_id = db.Column(db.Integer, db.ForeignKey('user.id'), nullable = False)
    def __repr__(self):
        print(f'Work("{self.name}")')

These are the models

[fading patrol]

It says the problem is a unique constraint on user.password, but your code doesn't show such a constraint... You might just need to rerun migrations is my guess

[astral geyser]

Im new to flask , can you explain can I do that?

@fading patrol

[tiny eagle]

Thank you so much!

[fading patrol]

How did you set up your DB in the first place? Manually with SQL or are you using something like Flask-Migrate?

[astral geyser]

manually with sql

[fading patrol]

So remove the constraint manually. Or if you want to do things a better way, start over like this: https://qxf2.com/blog/database-migration-flask-migrate/

[astral geyser]

thanks

[fading patrol]

If it's just a small simple project, DROP CONSTRAINT is simple enough but constantly updating your database by hand can get unwieldy for a bigger project

[tropic dagger]

Hi, is there any simple way to convert any type of SQL query to SQLAlchemy?

[bleak bough]

how the frick did all my data disappear??

as soon as i restarted the script everyones data in a table is just gone

there are commits everywhere there should be

whenever i test it, it's saving perfectly fine

there is no way it could have been deleted

[paper flower]

I don't think so, but they're quite easy to convert manually 🤔

[bleak bough]

the only thing i can think is there was some sort of error i missed right at the start of running it that stopped any commits happening

but otherwise everything worked as intended

it makes no sense 😤

[paper flower]

What are you using to connect to your db?

What db are you using?

[bleak bough]

aiosqlite

[tropic dagger]

Even nested requests?

[torn sphinx]

guys

i can't loggin in sql server..

...

[paper flower]

Yes, you can use subqueries with sqlalchemy

[paper flower]

Maybe you dropped db or the tables? 🤔

[bleak bough]

the word drop doesn't appear anywhere in my code for anything

one of my tables looks like it only has 3 entries for some reason when it should have close to 100

it's like it randomly stopped commiting data after running for 3 days

but only for two tables

[tropic dagger]

as separate queries, right?

[bleak bough]

i actually can't recreate the bug

[paper flower]

Well, it would be one db call

[tropic dagger]

Could you give me an example with a classic Employee - Manager schema?

[valid needle]

hey, is sqlite3 faster than postgre on python?

i’m looking to use a database which is fast since it will read data constantly

it’d be a plus if its scalable

[harsh pulsar]

"fast" in small systems is not always equivalent to "scalable"

in this case sqlite is probably a bit faster for simple reads with low volume, but less scalable than postgres. however it also depends on what you mean by "scalable". what volume do you expect to serve? mostly reads, or also writes?

sqlite is very fast and scales reasonably well for read-heavy workloads, but obviously is limited by being a simple file on disk, rather than a network-connected multi-threaded database server.

[paper flower]

What does it has to do with schema? 🤔

[tropic dagger]

What would be the sqlalchemy equivalent of SELECT (SELECT * FROM table) FROM table

[paper flower]

Something like this I think
https://docs.sqlalchemy.org/en/14/orm/queryguide.html#selecting-entities-from-subqueries

SQLAlchemy documentation is quite extensive, you can find a lot of examples here

[dense grove]

so i wanna add picture in my button(i use pygame_gui module) but in their site i found this so i need to create database of images i need. how ? + can i soon edit this database ?? + waht a fonts database??

[paper flower]

No need to ping anyone

[dense grove]

ugh

@torn sphinx help me😭

||lol and that is why i dont use nickname someone lol||

[torn sphinx]

hi can someone help me make a authenticator

not a local one

[torn sphinx]

what is an sql

server

[ionic pecan]

what have you tried to search for the question yourself?

[marble igloo]

how can I read my sqlite db file?

[dire oxide]

I'm using sqlite3. Is there any unbuilt way to +1 to a value.

I'm using discordpy and using the on_message() event. My database structure is: guild_id, member_id, message_count. Everytime that user sends a message I want +1 in message_count.

I know how to do this using fetch() etc. but is there any way in a single execute()?

[paper flower]

update your_table_name
set message_count = message_count + 1;

[keen stump]

Not really related to Python but more to SQL itself: does someone know if it is possible to specify an "empty" value in SQL, so that for example in an UPDATE request, the data for a column is not touched?
My dream would be to be able something like this:

UPDATE jobs SET status=<myvar>, path=VOID

So the "path" column would remain unaltered and keep the value it currently has.
I have a function that takes some parameters that can be updated in input, and I find not very great to make a condition for every possibility of defined/undefined values that can be passed in input, or worse build the query by hand

[paper flower]

Just don't specify the path in your update query, then it won't update

ORMs allow you to do that without dealing with string operations

[keen stump]

Yes but this implies making two different queries in conditionals, one with the path and one without it
For now this is fine but if we have 5 parameters this leaves us with 32 possibilities

[paper flower]

for example like this:

update(YourTable)
.where(YourTable.id == 42)
.values(**dictionary_with_values)

[keen stump]

Else we can still build the query by hand, or do one request per value, but this looks not so clean neither

[paper flower]

You can dynamically construct your query, but as I said there are libraries that do that for you

You could also fetch current values from db and use them as default ones

[keen stump]

True as well

I still think the concept of Void/Empty keyword is something that is missing in languages

It could be very useful in some cases to be able to do this

>>> i = 3
>>> i = Void
>>> print(i)
3```

[paper flower]

This doesn't make sense 🙂

[keen stump]

Here yeah it's useless but it's to show the concept

[paper flower]

You can use coalesce

But it won't work with nullable columns

[dire oxide]

Thanks

[fading patrol]

Installing DBeaver is one way

Or if you mean in Python, then with sqlite3

[neon olive]

hey there, I have a question about sqlite3. Is it possible to 'clean' a result of a fetchall()?

as in, the value in the db is either 1 or 0 but I get [(1,)] in the terminal when I print it

any way to have it be a clean 1 or 0?

        connection = self.database
        cursor = connection.cursor()
        cursor.execute("""
        SELECT "taken" FROM "Seat" WHERE "seat_id" = ?
        """, [self.seat])
        self.seatavail = cursor.fetchall()
        print(self.seatavail)

I assume I have to add something to cursor.fetchall() but I tried with re.sub for ex and strip and it didnt work ):

[granite ice]

i assume seat_id is unique so it will always have at most 1 record and then you get only 1 column so you can go with:

if self.seatavail:
  print(self.seatavail[0][0])```
or use `.fetchone()` to get rid of list and get only row tuple

[neon olive]

oooh [0][0] works as I wanted it to

didnt know you could do that haha, thanks!

I have a feeling the way I'm doing things is very rustic lol

[austere portal]

class UserRelations(models.Model):
    relation_types = (
        (1, "Following"),
        (2, "Blocked"),
    )
    user = models.ForeignKey(User, on_delete=models.CASCADE, related_name="user_rel")
    user2 = models.ForeignKey(User, on_delete=models.CASCADE, related_name="user_rel2")
    type = models.IntegerField(choices=relation_types)

    class Meta:
        constraints = [
            models.UniqueConstraint(fields=['user', 'user2'], name="unique user relation")
        ]
``` is this a good way to implement a followers model? or should I have followers and blocked users stored in 2 separate tables

[torn sphinx]

in pymongo, if i dont have a post with _id : 1, and i update a post in my collection with _id : 1, using the upsert value, will i add a new post?

also, can the _id be a string?

[fading patrol]

On this piece, absolutely yes. On the first part, I think so but not positive off top of my head

[torn sphinx]

post = {
    "_id" : "11",
    "task" : "flex the rainbow, bang it like some skittles",
    "timeH" : 10,
    "timeM" : 30 
}
print( colection.find_one( { "id":post["_id"] } ) )```why does this always return `none` even though I have a document with `_id` 11 in my collection?

[astral bluff]

Is it possible to use a in memory sqlite3 and modify/fetch data between 2 different threads safely? I know it comes thread safe by default but I was wondering if there is a way to speed up ups and use separate threads and an inmemeory database sounds like it could speed things up massively

[paper flower]

Maybe you has to specify _id and not id? 🤔

[paper flower]

Are you sure you need in-memory database? I doubt you need any speed improvements when using sqlite

For most small applications sqlite is completely fine, if you need something more robust you can use postgres or mysql

[wet berry]

Hi I’m learning sqlite right now what could you mean by “more robust”? Thanks 🙂

@paper flower

Just wondering a couple examples maybe or use cases for later when I get to it

[paper flower]

postgresql would be better than sqlite for large amounts of data, also it has more features and extensions

postgresql itself is a server/separate app and would be able to handle more load, also you won't be able to use sqlite by multiple processes, that's not the case with postgres and other RDBMS

[wet berry]

How much is large amounts? I’m building a diet app and I plan on having price history for myself on a few hundred items between 3/4 stores and their nutritional info. As well as some thousands of recipes.

The way it works though you’re not seeing anything accessed more than one thing at a time. Just lots of searching during meal planning.

[paper flower]

I probably wouldn't use sqlite for databases bigger than 1-2GB, that's not your case though

[wet berry]

Okay 👌 I figured as much thanks

[paper flower]

Is it a web app?

[wet berry]

Not yet at least. I plan on trying to monetize it one day at least locally.

[paper flower]

Would be better to just use postgres from the start

sqlite could be useful in embedded systems/client side apps

I heard it's widely used on android

[wet berry]

It’s not hard to later take my data from SQLite and move over to postgres? I’m just learning right now so since it’s possible I’ll probably do it as a console based application in SQLite first

[paper flower]

Yeah, it's not hard, but your data during development shouldn't be valuable anyway 🙂

Also take a look at sqlalchemy

Preferably you should not be using sql directly, because it's a lot of extra work that you don't have to do

[wet berry]

Dang there is always something else you can learn huh lol

[grave eagle]

i'm creating a sqlite3 singeton to connect to the database, and i wanna add a check if the database is connected

[olive reef]

I’m a getting an “cant write into a readonly database” when trying to backup, how can I fix this?

I just created an empty db file and made a connection so my main db would backup there

[wet berry]

@olive reef with sqlite?

[torn sphinx]

Eyo wait i didn't specify _id?

💀 wtf i think i lost my final braincell

[pure mortar]

tell me why in 2022 we are still using on-prem MS SQL Server and paying out the wazzoo for storage + inefficient sql queries used in production + no documentation or data modeling so nobody really knows what these table and field names mean unless you are directly working with them

yet

we still are multicloud but are not leveraging any of their strengths

for example super cheap storage

i cant even

[paper bluff]

because

[jaunty linden]

question: when creating Docker containers of postgres, does POSTGRES_PASSWORD (in docker compose yml) have to be the same as the superuser password of your local PostgreSQL?
afaiu it is a separate container instance so it can have any diff password we define right?

[steel marsh]

Hi, I don't know if this question has a place! -3

[Hi, I create a Form with Tkinter and the idea is to save the data entries in a sqlite3.db The Table is already created and to collect the data from Tkinter/Python a did a Module with a class and method, like this creating an Object of a class and calling the method I can fetch the entry data from the Form and added to the table of the database. As you can see in the code its show: sqlite3.OperationalError: table General_Data_Company has 8 columns but 1 values were supplied. My question if how can I add the data from Tkinter Form to another Module that will add all the data to the Table (I don't want to commit the data into the database from the same Module where is the Tkinter Form. Thanks

[unkempt prism]

2 issues and both have the same solution.

Use binding and not f strings in order to put values into your sql.

An example below. Note the ? as well as the tuple with the values in the second param.

cursor.execute('INSERT INTO artiststable (artist_id, number_loging) VALUES (?,?);', (1,3));

Using bindings will also protect you from sql injection and other security issues.

Edit: Next time also please share code in formatted snippets and not screenshots. You'll find people have a easier time to help you.

[steel marsh]

Thank you very much!!

[wet berry]

I’m vouching for https://www.sqlitetutorial.net I can now understand sqlite.org and the sqlite3 documentation.

[wet berry]

Any good reading for deciding how you’re gonna organize your data?

[buoyant hemlock]

Loook up conceptual data model , maybe use ER-diagram or uml. Then convert it to tables

If u are using the relation model as logical mode

I’m wondering if it’s better to make a procedure for like, inserting customer, or just sent the query directly from python? If I’m not wrong, I think it’s better to just send the query directly, if it doesn’t have values that needs to be calculated? Like if a new customer makes a order, can either have a python function that calculates values and make sql statements. Or I can just make one procedure call from python with all values needed and it will be done internally

[wet berry]

Wow looks like a lot more reading for me @buoyant hemlock but that’s definitely what I was looking for thank you

[torn sphinx]

how can i make a todo list using mongodb? one that reminds me what to do at what time?

🤔

like how can i make the bot remind me about a task at a particular time?

how can i implement tasks.loop into it?

any suggestion?

[tough kettle]

anyone here worked with SQLAlchemy?

guys i need help with SQLAlchemy in #help-carrot

[fading patrol]

You should be able to find tutorials online for all of these questions. There is at least one scheduling library for Python or you could use cron jobs.

[pure mortar]

omg anytime i perform a query tbh

[delicate field]

:incoming_envelope: :ok_hand: applied mute to @sick ice until <t:1659891980:f> (9 minutes and 59 seconds) (reason: duplicates rule: sent 4 duplicated messages in 10s).

[haughty tree]

Hi, so im currently using sqlite3 as a database for my python discord bot. I'm hosting it through heroku and have been having some troubles with it recently. Are there any other better options and/or recommendations for a database I could be using instead?

[icy oyster]

how can I create a folder and then access it to for example create tables in it

[icy oyster]

cant u host it locally?

[haughty tree]

i would prefer to host it online

[icy oyster]

AWS? it gives you a free year

[haughty tree]

hmm

will have to check it out

thanks

[acoustic cave]

Hey, how do I store an array of some sort in redis?
I know it is not a supported type but is there a way I can store it somehow ?
Any help would be appreciated

[paper flower]

Redis supports storing lists

[misty roost]

Trying to create this tables but I get Error:1215 no matter how I try to add the FK. Can someone help me? Using MySQL ```SQL
CREATE TABLE daily_dashboard(
id int auto_increment,
link_id varchar(256) NOT NULL,
serial_number varchar(256) NOT NULL,
iccids varchar(256) NOT NULL,
plan varchar(256) NOT NULL,
creation_date varchar(256) NOT NULL,
claimed_date varchar(256) NOT NULL,
expiration_date varchar(256) NOT NULL,
last_session_begin datetime,
last_session_end datetime,
last_session_duration varchar(256),
last_session_bytes bigint NOT NULL,
last_session_network varchar(256) NOT NULL,
primary key(id, link_id)
)ENGINE INNODB;

CREATE TABLE sessions(
id int auto_increment,
link_id varchar(256) NOT NULL,
date_month varchar(256) NOT NULL,
date_year varchar(256) NOT NULL,
sessions_consumed int NOT NULL,
bytes_consumed bigint NOT NULL,
PRIMARY KEY (id, link_id),
FOREIGN KEY (link_id) REFERENCES daily_dashboard(link_id)
)ENGINE INNODB;

[paper flower]

Foreign key on session doesn't match primary key on daily_dashboard

[misty roost]

I'm sorry, can you be more specific? I can see that both tables have the same primary key.

[paper flower]

Daily dashboard has primary key(id, link_id), you're trying to reference it by just link_id

Composite primary key should be referenced by same foreign key

For example i might have some kind of multiaccount system where i allow people to use same email with different usernames:
primary key (email, username)
I can't reference a row by just email or username since it doesn't identify it fully

Not a good example but I hope it helps

[misty roost]

mmm and how do I do that, like this?```sql
ALTER TABLE sessions
ADD FOREIGN KEY (id,link_id) REFERENCES daily_dashboard(id, link_id);

[paper flower]

only if session id matches dashboard id

[misty roost]

also it's funny 'cus I add the FK on the daily_dashboard instead of sessions it works without any issue

[paper flower]

Why are you using composite primary keys here?

[misty roost]

'cus you cannot make a FK if it isn't a key? thats my understanding.. sorry I'm kinda new to this stuff

[paper flower]

Right, by why do you need composite primary keys?

[misty roost]

I do not tbh, I thought having an PK with autoincrement would be handy if something happens

[paper flower]

Ok, so why not make a primary key just on id?

without link_id

[misty roost]

'cus I wanted to link both tables with link_id so I can display both table info more easy. I know that we can add a FK without being a PK using an index but tbh I do not understand how that works so I went the esy way using a two PK, in the past this worked with other tables, I don't why is not working here

[paper flower]

You usually "link" tables a bit differently

[misty roost]

😮 really? how so?

[paper flower]

create table users(
    id int auto_increment primary key,
    username varchar not null
);

create table posts(
    id int auto_increment primary key,
    title varchar not null,
    user_id int foreign key references users(id)
);

Not sure about exact mysql syntax but this (probably) works in postgres

In this case posts.user_id references users.id and you can easily join them

[misty roost]

I see! Ok will try this way and let you know!

Ok, it works!! Thanks a lot!!

[paper flower]

No problem, you could read more on db design and one-to-many and many-to-many relationships

[minor ledge]

Sorry IDK how to organize this really but how does this look?

[torn sphinx]

some1 here help ne?

[minor ledge]

with?

[slim trench]

How can I define an array in pgAdmin output fields?

is this correct?

here is the model

[final stratus]

Array? It is strange

Just make an another table

And create relation

One2many in your case

[kindred nacelle]

Any reason to use SQLAlchemy rather than pymysql?

I haven't used either of them, but now need to decide which one to use for my website's backend. Heard of both of them, seen both used, but can't pick out a particular reason to choose one over the other

[lapis quiver]

I am developing an IoT based project. The sensor data are sent to the database through a python script. The query has been integrated inside the script to send data to deployed MySQL database over internet. How can I ensure the reliability of this system. If there is a signal failure and can not connect to the internet, the data will not be updated to the database. How can I avoid that kind of problem or what can I do to detect a update failure in the system?

[final stratus]

I recommend you using sqlalchemy because:

1. You can easily edit DB engine
2. ORM queries are better then raw SQL

[final stratus]

My idea:
Sending data from client on server, if code isn't 200 (or 201), save data into queue, then send [current_data] + queue

[fading patrol]

I agree with what Yan said. You want to avoid embedding raw SQL queries in your Python code for any significant project unless you have a very good reason

[paper flower]

I'd say it's not that raw sql is bad, it's just not possible/very hard to dynamically create it:

stmt = select(Post).order_by(Post.created_at.desc())
if search.title:
    stmt = stmt.where(Post.title.ilike(search.title))

[paper flower]

Would be a good idea to send data in batches too , say 1000-ish readings, and only delete them from queue if server responds with 2xx

[slim trench]

What do you mean?

[marble igloo]

Hi I'm having a problem with my sqlite3 database, I made it so that I can store contents inside of the database via a discord bot / commands, and it stores them perfectly and retains them, but every day the table contents reset apart from the ones MYSELF has set

[somber iron]

Hi everyone! Do you guys know how to fix formatting issues? For example, I have a data base with location info and some people wrote NY and others New York

[somber iron]

But there’s so much data I don’t know how many inconsistencies like this are in the database for this column

[marble igloo]

any help?

[fading patrol]

It sounds like you're overwriting your SQLite file. Don't do that. But without seeing any code or other details, I'm just guessing based on what little you said

[final stratus]

Yeah, maybe

[marble igloo]

alright lemme send u the code

[fading patrol]

This is why input forms normally use a drop-down picklist or something. If there are too many inconsistencies in the data you have to fix them programtically, you may be out of luck

[final stratus]

One to many

[marble igloo]

    @commands.command()
    async def addname(self, ctx, name):
        e = conn.cursor()
        x = ctx.author.id
        y = name

        e.execute('''INSERT INTO names
        (user, name)
        VALUES (?, ?);''',
        (x, y))
        conn.commit()

        await ctx.send("Registered")

    @commands.command()
    async def deletename(self, ctx, name):
        e = conn.cursor()
        x = ctx.author.id
        y = name

        e.execute("""DELETE FROM names WHERE user = ? and name = ?;""", (x, y))

        conn.commit()

        await ctx.send(f"{name} successfully deleted")

    @commands.command()
    async def name(self, ctx, name: discord.Member):
        e = conn.cursor()
        id = name.id

        e.execute("""SELECT name FROM names WHERE user= ?;""", (id,))

        await ctx.send(e.fetchall())

    @commands.command()
    async def namelist(self, ctx):
        e = conn.cursor()

        e.execute("""SELECT name FROM names""")

        await ctx.send(e.fetchall())``` @fading patrol

[torn sphinx]

do you use replit by any chance?

[slender atlas]

or Heroku

[tropic dagger]

How can you use the generate_series function in SQLAlchemy? None of my sources showed a concrete usage.

I want to generate a timeline a do a LEFT JOIN with my data

I want to copy a value on a sub-interval of the timeseries's interval

[marble igloo]

heroku

[marble igloo]

yes

heroku

[lapis quiver]

great. thank you

[final stratus]

Yw

[tropic dagger]

Also I don't really understand the aliases in SQLalchemy

[fading patrol]

Ah, that might be your issue then. https://help.heroku.com/K1PPS2WM/why-are-my-file-uploads-missing-deleted-from-the-application

[marble igloo]

ok

[obsidian basin]

I am using flask and sqlalchemy

I am trying to add my 3 classes from my database User, Posts and Followers. How do I add the database without having to manually type all the columns in the database. Would just importing from my models.py file User, Posts and Followers work?

@pytest.fixture()
def new_user():
    
    """
    Given a User model
    When a new user is being created 
    Check the User database columns
    """
    
    # why can't I go plaintext_password() instead of plaintext_password 
    
    plaintext_password = 'pojkp[kjpj[pj'
    hashed_password = bcrypt.hashpw(plaintext_password.encode('utf-8'), bcrypt.gensalt())  
    current_user = User(username='fkpr[kfkuh', hashed_password=hashed_password, email=os.environ['TESTING_EMAIL_USERNAME'],
    confirmation_email=False)
    return current_user

@pytest.fixture()
def pytesting_create_app(config_obj=Pytest_Config):
    app = Flask(__name__)

    app.config.from_object(config_obj)
    
    app.debug = True
    db.init_app(app)
    login_manager.init_app(app)
    email.init_app(app) 

    with app.app_context(User, Posts, Followers):
        User_data = db.User(User)
        Posts_data = db.User(Posts)
        Followers_data = db.Followers(Followers)
        db.session.add(User_data, Posts_data, Followers_data)
        db.session.commit()

    yield app 

I tried this and this and it causes an error. Am I doing something wrong? Here is the error https://pastebin.com/23i5zT5V I have more code I thought I would just show what I think is the important parts,

[slender atlas]

Heroku's worker starts from the files you gave it. About every 24 hours, the worker restarts so any change, even to the files, after the worker shuts down is wiped and the workers starts with the files you gave it.

[marble igloo]

so how do I get around this?

[torn sphinx]

If I plan to make an AI using Python, I better get familiar with incorporating databases using Pycharm

[brazen charm]

[fading patrol]

Don't use SQLite on Heroku. I'm not sure if their Postgres is persistent, but if it is that's one solution. Otherwise I'd go with another host

[storm mauve]

their Postgres works well
you probably shouldn't use Heroku for discord bots anyway though (see the hosting thread in #discord-bots )

[paper flower]

func.generate_series() 🤔 func allows you to call any function by it's name

[tropic dagger]

Because I tried to generate a subquery and func.generate_series on its own raises an exception

[paper flower]

Huh?

from sqlalchemy import func

a = func.generate_series()
print(a)
>>> generate_series()

it doesn't

[tropic dagger]

start_date = datetime(...)
end_date = datetime(...)
freq = timedelta(...)

a = func.generate_series(start_date, end_date, freq).alias('a')

Ok no I think my problem was with the .c argument

[torn sphinx]

@paper flower could you explain the specifics of what you want in here

[paper flower]

So basically i'm choosing between using a table per relationship and a polymorphic association table

First would require creating a separate table per each relationship (e.g. Tag->Post, Tag->Item), second one would allow you to use a single association table for multiple types of objects

[torn sphinx]

Polymorphic meaning the table holds strings identifying relationships?

[paper flower]

Yep, for example

tag_id entity_id discriminator
1      42        post
1      32        item

Second option would not guarantee data consistency though

[torn sphinx]

So the most sql friendly way to do it would be to just have a primary key id in your main table that other tables storing your related entries could be associated with via a foreign key to that id

In other words a seperate table for each type of relationship

So say i have a people table with an id primary column

[paper flower]

I know how foreign and primary keys work, that's no the question here

[torn sphinx]

I would make a seperate table for each association

[paper flower]

I probably agree, it's more consistent and could be autogenerated by some libraries

[torn sphinx]

Yea also cuz sqls relations arent usually meant to be stored in their own table usually, except for mabie verry niche cases

Especially since number of columns shouldnt slow down selecting data anyways since you can specify which ones you want

[dark lion]

Hello guys,
Q:- Can any one help me to solve this problem, "how can I fetch data from SharePoint to Asw s3 bucket directly using python script"?
This is an urgent requirement.
Please help me out I'm actually new to python and AWS, thank you

[slim trench]

I just want to return a list

[slim trench]

I getting an error, Thanks.

   if key not in self._dict:
TypeError: unhashable type: 'list'

the code is here:

class HeroLeft(Base):
    __tablename__ = 'heroLeft'
    image_url = Column(ARRAY(String), primary_key=True, nullable=False)

class ProjectSwitcherCategory(Base):
    __tablename__ = 'ProjectSwitcherCategories'
    tags = Column(ARRAY(String), primary_key=True, nullable=False)

How do I write the data into that?(above, this is the array thing)

[slim trench]

There is another problem.
I get an error when I try to map over it.
Because some reason the postgre does not want to return a list type.
This is the endpoint with two dict.
If I print the type I get an object.
and that is why I can't map over it.
Someone can help? What could be wrong in the frontend, because If I know right in the postman We get a list with two dict.

[final stratus]

Just Google what is many2many. It is basics of DB

[fading patrol]

Get with this: https://github.com/vgrem/Office365-REST-Python-Client

Put with boto3: https://boto3.amazonaws.com/v1/documentation/api/latest/reference/services/s3.html#S3.Bucket.upload_file

[valid needle]

hello

i’m currently using sqlite3 to store (dateString, ticker) objects as their primary keys

however, it takes about 1sec when performing queries such as SELECT date WHERE ticker = ? ordering by dateString LIMIT 1

should i add an index to speed up ordering by?

RAM isn’t an issue

[harsh pulsar]

maybe, but always check the query plan with EXPLAIN https://www3.sqlite.org/eqp.html

[tender phoenix]

Is SQL easy to learn?

Im trying to understand databases bttr

[fading patrol]

The basics of making simple queries and stuff are super easy, but more advanced stuff can get pretty challenging

[tiny eagle]

After releasing a product, such as a bot to the public, and the init.sql (schema) has to be edited, what's the best and safest way to do this without losing data, such as adding a new column, deleting one, etc. Would it be to simply use something like ALTER TABLE x ... and then add a corresponding bit of SQL into the init.sql to match it?

[paper flower]

Most common solution is using a migration tool like alembic

[tiny eagle]

Ah I see, what if I'm not using an ORM?

[paper flower]

You're not required to use orm with it

If you want to use .sql files you could create a file per migration

e.g. adding a table, altering something

[torn sphinx]

well, anyone knows why i get error with this mysql connector? any alternatives?

[paper flower]

It's not an error, you already have this package installed

[torn sphinx]

yes but its not working

[paper flower]

Then you installed it into a different venv or python interpreter

[torn sphinx]

its not detecting it

[torn sphinx]

oh

makes sense

yeah, works, danke fuhrer

[cobalt imp]

Trying to upsert something to azure table

table = TableClient(endpoint=endpoint, credential=credential, table_name=self.table_name)

await table.upsert_entity(asdict(SomeEntity(somevalue=5)), mode=UpdateMode.REPLACE)

@datclass
class SomeEntity:
PartitionKey: str = "Something"
RowKey: str = "SomethingElse"
somevalue: int = 1

[2022-08-09 19:52:51,036] {selector_events.py:54} DEBUG - Using selector: KqueueSelector
[2022-08-09 19:52:51,203] {main.py:55} ERROR - One of the request inputs is out of range.
RequestId:28ce7ef9-a002-000b-0918-ac6891000000
Time:2022-08-09T17:52:51.2838103Z
ErrorCode:OutOfRangeInput
Content: {"odata.error":{"code":"OutOfRangeInput","message":{"lang":"en-US","value":"One of the request inputs is out of range.\nRequestId:28ce7ef9-a002-000b-0918-ac6891000000\nTime:2022-08-09T17:52:51.2838103Z"}}}

keep getting the OuOfRangeInput error

[torn sphinx]

I am trying to create this database: import mysql.connector

mydb = mysql.connector.connect(
host="localhost",
user="yourusername",
password="yourpassword"
)

mycursor = mydb.cursor()

mycursor.execute("CREATE DATABASE mydatabase")

However, I am encountering: _mysql_connector.MySQLInterfaceError: Can't connect to MySQL server on 'localhost:3306' (10061)

[torn sphinx]

cant connect to mysql server on localhost:3306

[paper flower]

What do you mean?

[kindred nacelle]

Got it, thx!

[kindred nacelle]

Btw, why is ORM syntax superior to raw SQL in code?

[final stratus]

Yw

[final stratus]

Because

1. No dialects
2. Lower SQL injection chence
3. Easily building hard queries
   Etc.

Oh, I like databases topic most of others. Because there are always something interesting.

[final stratus]

Have you runned DB? Is loggin and password correct?

[clear otter]

Got a small problem here, I'm using sqlite3 to simply just get into databases, but it looks like PyCharm is encoding the *.db file in a wrong way. This is my current code:

import sqlite3

Connection = sqlite3.connect("./test.db")

Cursor = Connection.cursor()

Cursor.execute("""CREATE TABLE IF NOT EXISTS users(
   userid INT PRIMARY KEY,
   name TEXT);
""")
Connection.commit()

print(Cursor)

[paper flower]

Db files can't be opened in a simple text editor

Pycharm Professional has a database plugin which you can use

Alternative would be something like sqlite browser

[clear otter]

May you give me an example? I'm a newbie when it comes to this kind of stuff

[torn sphinx]

Hmm?

[minor ruin]

Is RowKey actually unique?

also make sure it's properly returning a dict.

I use Azure Table storage at work all time with Python, almost all errors were caused by Non Unique Row Key

[paper flower]

https://sqlitebrowser.org/

Double clicking your db file should work too if you have pycharm professional

[clear otter]

Yup, got it, this whole database thingy is more complicated then I thought 😅
Thank you for the help, appreciate it

[marble igloo]

can someone help me with mysql

[paper flower]

Don't ask to ask, just ask 🙂

[marble igloo]

ok

@paper flower

how do I set up a db in mysql workbench

like I want to create a table

user, name

user is int

name is string

@paper flower

[fading patrol]

No need to keep pinging people like that, please. Does this help? https://www.quackit.com/mysql/workbench/create_a_table.cfm

[candid aurora]

PyQt / SQLite question. If I create a connection before I open show a window, everything works fine. But if I create the connection after, I can't query the database. I'm not sure why

def create_connection():
  con=QSqlDatabase.addDatabase("QSQLITE")
  con.setDatabaseName("path/to/database.db")
  con.open()


class window(QMainWindow):
  def __init__():
    self.model = QSqlTableModel(self)
    #create_connection("path")
    self.loadSQL()
    self.view = QTableView()
    self.view.setModel(self.model)
    self.view.resizeColumnsToContents()
    self.setCentralWidget(self.view)

  def loadSQL(self):
    self.model.setTable("tableName")
    self.model.setHeaderData(0, Qt.Horizontal, "id")
    self.model.select()
    print(f"Number of Rows, {self.model.rowCount()}")

if __name__ == '__main__':
  app = QApplication(sys.argv)
  #create_connection("path")
  win = Window()
  win.show()
  sys.exit(app.exec_())```

If create_connection is called in `'__main__'` everything works fine. If it is called just before the actual use of the database, I get no errors and it connects but I get no results from the SQL query. Can anyone help me figure out why?

[marble igloo]

dI don't have schemas tab

https://wolvtmg.who-tf.ru/XGGXB4Kz @fading patrol

[clear otter]

Hey, I got a small problem. My current code fetches the argument "aboutyou" for a column for some reason.

@bot.slash_command(description="Database testing.")
async def register(inter, aboutyou: str):

    Connection = await aiosqlite.connect(r"./testingusers.db")
    Cursor = await Connection.execute(f"SELECT * FROM userregistration WHERE userid={inter.author.id}")

    if not await Cursor.fetchall():
        AuthorID = inter.author.id
        CurrentTimeStamp = round(datetime.datetime.utcnow().timestamp())
        AboutUser = aboutyou
        await Cursor.execute(f"""INSERT INTO userregistration (userid, timestamp, description) VALUES({AuthorID}, {CurrentTimeStamp}, {AboutUser});""")
        await Cursor.close()
        await inter.response.send_message("Successfully registered! Please use '/profile' to view your profile.")

    return await inter.response.send_message("You are already registered.")

I'm entering "test" as the "aboutyou" argument.

[fading patrol]

Did you create the DB? https://www.quackit.com/mysql/workbench/create_a_database.cfm

[cobalt imp]

CREATE EXTERNAL TABLE fact(
col_a int ,
col_b int
)

is it possible to specify int or none

[marble igloo]

help please

[marble igloo]

    @commands.command()
    async def addname(self, ctx, name):
        x = ctx.author.id
        y = name

        sql = "INSERT INTO `data`.`names` (user, name) VALUES (%S, %S)"
        val = (x, y)

        mycursor.execute(sql, val)

        await ctx.send("Registered")``` ```Traceback (most recent call last):
  File "C:\Users\llVll\AppData\Local\Programs\Python\Python39\lib\site-packages\discord\ext\commands\bot.py", line 939, in invoke
    await ctx.command.invoke(ctx)
  File "C:\Users\llVll\AppData\Local\Programs\Python\Python39\lib\site-packages\discord\ext\commands\core.py", line 863, in invoke
    await injected(*ctx.args, **ctx.kwargs)
  File "C:\Users\llVll\AppData\Local\Programs\Python\Python39\lib\site-packages\discord\ext\commands\core.py", line 94, in wrapped
    raise CommandInvokeError(exc) from exc
discord.ext.commands.errors.CommandInvokeError: Command raised an exception: ProgrammingError: Not all parameters were used in the SQL statement```

[torn sphinx]

is that mysql?

[marble igloo]

yes

[torn sphinx]

its %s

[marble igloo]

AH

LOWERCASE

OFC

    await injected(*ctx.args, **ctx.kwargs)
  File "C:\Users\llVll\AppData\Local\Programs\Python\Python39\lib\site-packages\discord\ext\commands\core.py", line 94, in wrapped
    raise CommandInvokeError(exc) from exc
discord.ext.commands.errors.CommandInvokeError: Command raised an exception: DataError: 1264 (22003): Out of range value for column 'user' at row 1``` @torn sphinx

that's the table

[marble igloo]

can someone help?

aaaaaaaaaaaaaaa

[rocky spruce]

cur.execute('''INSERT INTO ID VALUE (?)''', [str(ctx.author)])

Sqlite3 is hard

For me

[marble igloo]

@rocky spruce good luck waiting for a response

I've been here since 18:00

[jade horizon]

"""INSERT INTO ID VALUE (?)""", (str(ctx.author),)

oops

[marble igloo]

woah

I got a response

[jade horizon]

Heres the command

[marble igloo]

after what, 4 hours

[jade horizon]

lmfao

[marble igloo]

better late than never

but thanks brother

[jade horizon]

it wasnt for u thats the sad part

[marble igloo]

.

ur taking the piss

[jade horizon]

databases channel is ded

xd

I wanted to ask a question aswell

but it seems i wont get much help quick

[marble igloo]

I might as well just potato peel my ball sack and dip them in 180 degrees boiling oil

[jade horizon]

but it atleast exists

[marble igloo]

ask your question

I might as well try and help you

while I suffer

[jade horizon]

well I have multiple tables which are logs

[fierce leaf]

I've got a startup business anyone wanna help me build my database

[rocky spruce]

I did a data base code and it doesn't work, is it bc I'm on mobile.?

[jade horizon]

? shouldnt be

you missed a comma

[jade horizon]

Your 2nd parameter should be a tuple

acc list would work

so idk

[rocky spruce]

I can send the code

[marble igloo]

no idea what the question even is

🤣

[rocky spruce]

I'm kinda new with python

[jade horizon]

got sidetracked

[jade horizon]

Basically I want to get all the rows with a specific user id between all tables

aka get all the logs of 1 user

[marble igloo]

    @commands.command()
    async def addname(self, ctx, name):
        x = ctx.author.id
        y = name

        sql = "INSERT INTO `data`.`names` (user, name) VALUES (%S, %S)"
        val = (x, y)

        mycursor.execute(sql, val)

        await ctx.send("Registered")``````exception: DataError: 1264 (22003): Out of range value for column 'user' at row 1```Anyone wanna help me with this? It's mysql

[jade horizon]

this is in sqlite but obv help is appreciated

[rocky spruce]

mport os, discord
from discord.ext import commands
import sqlite3

bot = commands.Bot(command_prefix='.')


                
@bot.event
async def on_ready():
	 con = sqlite3.connect('newfile.db')
	 cur = con.cursor()
	 print('bot located')
	 cur.execute('''CREATE TABLE IF NOT EXISTS game
		(
		ID text,
        Murdere text,
        Innocent text, 
         Alive text
                )''')
	 con.commit()
	




@bot.command()
async def start(ctx):
		      cur = con.cursor()
		      if game == False:
		      	cur.execute('''INSERT INTO ID (VALUE (?)''', [ctx.author])
		      	con.commit()
		      else:
		      	await ctx.send('Waiting for more players, `.join`')
		      	

@bot.command()
async def join(ctx):
	cur = con.cursor
	if game == True:
	       cur.execute('''INSERT INTO ID (VALUE (?)'''
	       [str(ctx.author)])
	       await ctx.send('ID has been authored')
	else:
		await ctx.send('Game hasnt been started')

@bot.command()
async def start_game(ctx):
	cur = con.cursor


global game

No errors

[jade horizon]

so u dont get errors?

then whats the proble,

problem

[rocky spruce]

It doesn't work

Doesn't put any data in the database file

Doesn't send messages when I do the command

Doesn't work at all

Nothing happens

[jade horizon]

well you wrote if game == True

where is the game variable?

does it write "ID has been authored"

in chat

[rocky spruce]

Nope

[jade horizon]

i see global game but how does your script know when game == True or False?

remove game== True and it will work

so you can see the error

[rocky spruce]

I want it to know when the game has been setup tho

[jade horizon]

cant you just rewrite this to INSERT INTO data (user, name) VALUES (?,?), (x,y)

well then you must assign the game variable a boolean value of True or False, because right now it has none of that

[rocky spruce]

But even if it didn't work, when the code runs there are supposed to be data in the database

[jade horizon]

did you change it to my version?

the sql insert

[rocky spruce]

I just did that

Didn't work

[jade horizon]

to this.

[rocky spruce]

Um.

Damon my brain

Lmao

[rocky spruce]

Bro what the hell

Why did this message send me to my gf's dm

[marble igloo]

mycursor.execute("INSERT INTO names (user, name) VALUES (?,?);", (x,y))```
 Why is this saying not all parameters were used in mysql
the table is inside of names
and the table consists of user = int, name = str

@commands.command()
    async def addname(self, ctx, name):
        x = ctx.author.id
        y = name

        mycursor.execute("""INSERT INTO names (user, name) VALUES (%s, %s);""", (x, y))

        await ctx.send("Registered")

    @commands.command()
    async def removename(self, ctx, name):
        x = ctx.author.id
        y = name

        mycursor.execute("""DELETE FROM names WHERE user = %s and name = %s;""", (x, y))

        await ctx.send(f"{name} successfully deleted")

    @commands.command()
    async def name(self, ctx, name: discord.Member):
        id = name.id

        mycursor.execute("""SELECT name FROM names WHERE user = %s;""", (id))

        await ctx.send(mycursor.fetchall())

    @commands.command()
    async def namelist(self, ctx):
        mycursor.execute("""SELECT name FROM names""")

        await ctx.send(mycursor.fetchall())```

Can someone help me?

[torn sphinx]

add a , after every ending parameter like (x,y,) should work

[marble igloo]

alright

lol why r u a likely spammer @torn sphinx

[torn sphinx]

wtf

I think because I joined now in the discord

[harsh pulsar]

i wish there was a way to report someone as "not a likely spammer"

[rocky spruce]

@jade horizon

Can I use json for this

[rocky spruce]

Can I use Jason for this.?

[fading patrol]

Store data in a JSON file instead of SQLite for a bot? Doesn't seem feasible but I've never tried

[spring hazel]

Hey there! Is there any way I can use redis search for Embedded lists (JSON models)
Here's my schema (I'm using python redis OM)

class MemeCache(JsonModel):
    memes: List[Meme] = []

Here's my Meme object

class Meme(EmbeddedJsonModel):
    username:str = Field(index=True)
    user: str
    profile_image_url: AnyHttpUrl
    user_id: str
    tweet_id: str = Field(index=True)

Now I just want to get all memes in community_memes that are uploaded by a particular user.

[ebon steeple]

Hey there, I am using sqlalchemy to extract json objects stored in a single column into a csv where each level 0 attribute is a column. sqlalchemy adds b' at the start of the column. Any idea why? 🤔

[muted wharf]

Hello 👋
I am using MongoDB database.

Let’s assume that it is like this

Data={
_id= ….
P1= …..
P2=…..
P3= ….
.
.
.
Pn=….
}

I would like in my code for my Discord bot to make a look which read Data.P1 …. To Data.Pn but I don’t see how to call it… Does it have a name, as Data.[0]… Data.[n] ?

[ebon steeple]

The column in question is of type mediumblob.

[fading patrol]

https://stackoverflow.com/questions/6269765/what-does-the-b-character-do-in-front-of-a-string-literal

[ebon steeple]

Thanks for the hint! 🙌

[lapis harbor]

Hi there, I have these two models build with Django ORM:

class ModelOutput(models.Model):
    target = models.CharField(max_length=270)

class Feedback(models.Model):
    target = models.CharField(max_length=270)
    model_output = models.OneToOneField(ModelOutput, on_delete=models.CASCADE)

Assume I have an AI model, the output of this model is ModelOutput and the actual value of this model in Feedback
what I'm trying to accomplish is to calculate the confusion matrix + precision, recall, F1 score

---

I have this queryset:

query = ModelOutput.objects.values('target', 'feedback__target').annotate(
            result=Count(
                Case(
                    When(target=str(F("feedback__target")), then=Value(1)),
                    default=Value(0),
                    output_field=IntegerField(),
                )
        ),            
     )

and the result is something like that:

[
        {
            "target": "cat",
            "feedback__target": "cat",
            "result": 2
        },
        {
            "target": "dog",
            "feedback__target": "cat",
            "result": 1
        },
        {
            "target": "dog",
            "feedback__target": "dog",
            "result": 1
        },
]

---

Based on these result from annotate() how can I calculate precision, recall, F1 score for each target using the same approach?
I'm not familiar with AI terms, so if there is something wrong, feel free to correct me :))

[tardy lichen]

Question: How do I translate this sql query into sqlalchemy?

SELECT *
FROM EVENTS E
    JOIN EVENT_DETAIL ED ON (E.ID = ED.EVENT_ID)
WHERE ED.WORKGROUP_ID = P_WORKGROUP_ID
    AND ED.UPDATE_DATE = (
        SELECT MAX(UPDATE_DATE) FROM EVENT_DETAIL
        WHERE WORKGROUP_ID = P_WORKGROUP_ID
    );

[paper flower]

What's P_WORKGROUP_ID?

[tardy lichen]

It's a parameter

[paper flower]

If i understand correctly you want to select event with latest even detail?

[tardy lichen]

Pretty much

[paper flower]

What rdbms are you using?

[tardy lichen]

I've came up with this so far

subquery = (
        db.query(detailModel, func.max(detailModel.update_date))
        .filter(detailModel.workgroup_id == workgroup_id)
        .subquery()
    )

    return (
        event_join(db)
        .filter(detailModel.workgroup_id == workgroup_id)
        .filter(detailModel.update_date == subquery)
        .offset(skip)
        .limit(limit)
        .all()
    )

Not sure it works

[tardy lichen]

psql

[paper flower]

Seems to be ok, I came up with similar query

You can use distinct on that's unique to postgres

Actually you might not even need it here

[tardy lichen]

Oh, you right
Is there a way to use it from sqlalchemy?

[paper flower]

I'm just not sure if using a subqery would be faster than sorting

You have a db dump that i can use by any chance?

[tardy lichen]

Sadly no, my other classmates are working on that. That's why I haven't been able to test it myself

[paper flower]

Is it one to one relationship?

Or its one event to many event details? And you want to fetch a latest pair

[tardy lichen]

Not really concerned with speed but I will stick to the subquery

[tardy lichen]

Yeah, I want to get all the events with their lastest details

Wait, I'm dumb then

[paper flower]

Maybe

select * from EVENTS distinct on (EVENTS.ID)
join EVENT_DETAIL on EVENTS.ID = EVENT_DETAIL.EVENT_ID
order by EVENT_DETAIL.UPDATE_DATE desc
limit 1;

You can additionally order by event column too

Would look something like this in sqlalchemy

stmt = (
    select(Event, EventDetails)
    .distinct(Event.id)
    .join(EventDetails, Event.id == EventDetails.event_id)
    .order_by(EventDetail.updated_at.desc())
)

https://docs.sqlalchemy.org/en/14/orm/query.html#sqlalchemy.orm.Query.distinct.params.*expr

you should also use sqlalchemy 2.0 syntax, it's available since 1.4

https://docs.sqlalchemy.org/en/14/tutorial/index.html
https://docs.sqlalchemy.org/en/14/changelog/migration_14.html

[tardy lichen]

Oh yeah that's way easier to read

[polar isle]

Just installed PostgreSQL on my ubuntu 20.04 and trying to connect to it via asyncpg library: ```py
async def init(con):
await con.set_type_codec(
'jsonb', schema='pg_catalog', encoder=_encode_jsonb, decoder=_decode_jsonb, format='text'
)
if old_init is not None:
await old_init(con)

pool = await asyncpg.create_pool(uri, init=init, **kwargs) # type: ignore
return pooland getting this error:Traceback (most recent call last):
File "/home/bogdan/HokkerBot/launch.py", line 33, in run_bot
pool = await Postgres.create_pool(config["DATABASE"]["uri"], **kwargs)
File "/home/bogdan/HokkerBot/cogs/backend/db.py", line 561, in create_pool
pool = await asyncpg.create_pool(uri, init=init, **kwargs) # type: ignore
... (a lot of package lines listed here)
socket.gaierror: [Errno -2] Name or service not known``` in my config file the uri is postgresql://hokker:my_password@localhost/hokker. I created role and database via these commands: CREATE USER hokker WITH LOGIN PASSWORD 'my_password'; CREATE DATABASE hokker OWNER hokker;, here is proof that they exist:

[paper flower]

Your host value seems to be wrong

according to error

[polar isle]

what can it be except localhost? i tried changing it to 127.0.0.1 didn't work

[paper flower]

Are you sure it's passed into python code correctly?

[polar isle]

uhm, how do I make sure?

[paper flower]

debug or print

[polar isle]

oh wait I have @ in my password, when I had problems with password I always was afraid of that and now it happens(

anyways, thank you, now it works

[paper flower]

You can use an update statement to change positions of certain slides, also sqlalchemy supports ordering list: https://docs.sqlalchemy.org/en/14/orm/extensions/orderinglist.html

[slim trench]

I'm getting this error: sqlalchemy.exc.NoReferencedTableError: Foreign key associated with column 'ProjectSwitcherCategoryChildTable.parent_id' could not find table 'parent' with which to generate a foreign key to target column 'id'

what could be wrong, Thanks.

[final stratus]

In foreign key

You just write children

Write (name of table).id

[slim trench]

ProjectSwitcherCategories.id?

[final stratus]

Yup

[slim trench]

I getting this error

[warped sky]

Errmm, Hi guys. Does anyone here know how to display the lartgest value of this attributes named "Total expenditure"?

And I'm using MySQL software

If you need more elaboration, I will try my best

[final stratus]

Use max() function

Like select max(...) from ...

[warped sky]

I did but its shows error

*it

This is where the headache part, I have to inner join three tables in order to get the infor but I can't do argregate function

[fading patrol]

I'm new to this stuff and thinking out loud here... It might depend on the ORM but I think if you want to add a nullable constraint you probably need to manually null out that data itself first.

Not sure if that's best practice but I think it would work

[civic cargo]

Yes, you generally have 2 options - add a non-nullable column with a db-side default, which will populate all rows (some value instead of null), or add the column as nullable, populate with data, and then make it non-nullable

It depends - if you want to populate all rows with some default value, option 1 does this for you

but if you want to e.g. calculate the value for each row from some other columns, option 2 will allow you to run a custom update query before adding the non-null constraint

it also depends on if you want the column to retain the default value after your migration

postgres allows you to run ALTER TABLE in a transaction, so you can easily do option 2 safely in a single atomic migration that will rollback if it fails mid-way

[strange bluff]

Can I do something like this in sqlite?

cur.execute("""
INSERT INTO TheTable
(:fieldA :fieldB :fieldC)
VALUES
(:valA :valB :valC)""",
{"fieldA":"name","fieldB":"age",...})

I'm trying to work out the best way to dynamically insert data from a collection of dicts when I don't know which fields a dict actually contains

[civic cargo]

no, you cannot parametrise column names like that

if you're not using any ORM, i'd recommend you use a lightweight query builder like pypika to dynamically build the query at runtime

it will make it much easier to dynamically construct the query without having to manipulate the string

[strange bluff]

alr, thanks

[warped sky]

Errmm anyones here can help me to solve this out?

[lean plover]

How can I most easily test sqlite urls to see if flask_sqlalchemy will accept them (find the sqlite database)?

Kinda a burden to have to spin up my app to elicit a sql query so it tries to access the database

[pure mortar]

how come people dont comment their sql queries

its hard to understand any business logic just from a file of sql queries and the mess of databases

on prem

honestly

tragic

[frozen python]

https://medium.com/@Dev-BlackHeart/learning-sql-part-8-sql-select-statement-more-query-s2-80cf5c9c1cbc

[versed robin]

Working on a solution where the data are stored in a sqlite database and use SQLmodel as the ORM.
I have a challenge understanding why this would not work, and only updata the rows in the table that does not contain a observed date:

def update_observed(session):
    statement = (
        update(Property)
        .values(observed=str(date.today()))
        .where(Property.observed is None)
    )
    session.execute(statement)
    session.commit()

[paper flower]

is none is python operator that cannot be overwritten

unlike others

It sounds weird but you should use == here

Also use session.flush unless you absolutely need to commit

In most apps you could commit once before returning your response

[slim trench]

Hi, I've got these models.
I get an Internal Server Error when I try to "call" them.
What could be wrong, I using sqlalchemy, fastapi and postgre?
Thanks.

How can I create an array in Postgres, maybe this is the problem.

[paper flower]

"Internal Server Error" Doesn't tell much, you should also send the traceback you're getting and code where your error is originating from

[slim trench]

is this a good array syntax in the postgre?

[paper flower]

Looks ok 🤔

You can always look up postgres docs too

[slim trench]

Yes I know

but If I create this array then I getting a blank page

[paper flower]

If there's an exception being thrown you should share it

[slim trench]

what do you mean by Exception, Should I share the all code?

[paper flower]

Exception is basically an error that you can throw

You should read on them tbh

[slim trench]

I don't get an error, I just get a blank page.

the terminal is clear

in the postman

[paper flower]

You just said that you're getting an internal server error

So there should be an exception raised in your app

[slim trench]

I don't get an internal server error anymore, just a blank page.

@paper flower what do you think?

[paper flower]

Wdym blank page? You're using something like jinja2?

[slim trench]

I using react, When I declare that list in the postgre, I get an error.

@paper flower Do you see anything, what should I do?

[slim trench]

Do you have any tips? @paper flower

[pure condor]

What is the best website to design sql (diagrams)?

[tiny eagle]

How often would you guys recommend I perform a database dump for a public moderation bot?

Currently have it set to every 24h, though I'm worried this might not be enough, I have my dumps uploaded to a bucket anyways

[buoyant hemlock]

hello! I have exam on monday and i know there will be a section of constraints. These are 4 main ones, according to my book:
• Domain Constraints
• Entity Integrity
• Referential integrity
• General constraints
But when i ctrl+f on my pdf i found other contraints like
• Structural Constraints
• Participation Constraints
So im wondering if there are more i need to know about?

well i guess participation is a type of structural constraint

[shell terrace]

is there a way to have a foreign key choice for a model in django?

[fading patrol]

Depends on everything... Is losing up to 24h of data likely enough and bad enough that you want to do more backups?

[fading patrol]

Are you asking if Django ORM supports foreign keys? Yes, of course: https://zerotobyte.com/complete-guide-to-django-foreignkey/

[fading patrol]

It's a question of taste and there are endless options so play around and make your pick.

[whole vale]

i want to know about how to save data which use user id like 360961229562314768 and a list of variables and how to access the variables with python in repl

[shell terrace]

hi srry for interrupting you, do you think we could chat in dms ?

[fading patrol]

No thanks

[fading patrol]

Here's one way https://replit.com/talk/learn/How-to-create-an-SQLite3-database-in-Python-3/15755

[shell terrace]

well i don't think that was my case, did you try to see the image file i provided in my message?

[whole vale]

thank you

[fading patrol]

I have no idea what you're asking then,.but you can try #❓｜how-to-get-help

[shell terrace]

im trying to implement a variable choice for the foreign key of a single column of a model

does it make any sense to you?

[fading patrol]

Not really... You can have multiple foreign keys if you need them, but you can't add or remove them without running migrations

[keen minnow]

What problem are you trying to solve with this approach?

[paper flower]

You're trying to create a generic relationship?

For example a Tag model that could be used with any other model/table?

[thorn sequoia]

How can I check, if a row exist? PS.: I am using pymysql module. Thanks.

[paper flower]

You just want to check if row exists using it's id?

[thorn sequoia]

Yeah

[paper flower]

You can use sql exists operator

[thorn sequoia]

Yeah i know, but how can i use it as a function like:

def ifExist():
if ....:
return True

[paper flower]

Just selecting that row would work too

You'd have to write sql query for that

[thorn sequoia]

Okay i'll try, thanks very much.

[paper flower]

How to use that query in your code is really up to you

but generally you could just select your row:

select **list of columns here** from your_table_name where id = ?

Or wrap it in exists which would return boolean value```sql
select exists(select id from table where id = ?)

[thorn sequoia]

Thanks.

[paper flower]

What do you want to do though?

[thorn sequoia]

I want to do an Insert, before i have to check if a row exist.

[paper flower]

WIth a specific id? Why?

[thorn sequoia]

What do you mean?

[paper flower]

How do you want to check if a row exists?

Using what information?

[thorn sequoia]

Ah, I will use the SELECT EXIST Query

[paper flower]

You didn't understand, using what property/field/column would you check if a row exists?

[rich mirage]

Hello

Im having some issues with Current_Date() function (i keep getting syntax errors)

[paper flower]

Share your error and query

[rich mirage]

I fixed that one error but do you happen to jnow how to turn int into string in an SQL statement? Or would i have to do that prior?

nvm

[delicate field]

:incoming_envelope: :ok_hand: applied mute to @spring turtle until <t:1660454416:f> (9 minutes and 59 seconds) (reason: duplicates rule: sent 4 duplicated messages in 10s).

[muted wharf]

Hi ! Is there someone who knows how to update a list in a MongoDB data base?

I know there is the findOneAndUpdate but I cannot find how to use it for a list

Data = {
_id = …
Channel = [“id1”, “id2”,…]
}
Or maybe how to add an entry ? Is this possible?

[rapid narwhal]

i just got started with sqlite3

how do i append the count for the first entry here?

can anyone show me an example of it or redirect me how to update values

try:
    crsr.execute("""UPDATE messages SET count=25 WHERE guildid=267624335836053506 AND userid=273847633205002260""")
    print('a')
except:
    crsr.execute("""INSERT INTO messages VALUES (267624335836053506, 273847633205002260, 1)""")
print("Done!")

``` its printing `a` and `Done!` but not updating the value of the first entry in `count` to `25`

[paper flower]

Why do these look like floats? 🤨

You also should commit btw

connection.commit() if I'm not wrong

[rapid narwhal]

im doing that though

[rapid narwhal]

while creating them i put it was real

crsr.execute('''CREATE TABLE messages
             (guildid real, userid real, count real)''')```

[paper flower]

Create table causes implicit commit

[rapid narwhal]

im so confused rn :o

i need a little bit more explanation pls

[paper flower]

Create table autocommits

many other statements like update don't

[rapid narwhal]

how is that done

[paper flower]

It's done on db level

In most databases any DDL statement will cause autocommit, that includes creating a table

DDL - Data Definition Language

DML - Data Manipulation Language - select, insert, update, delete statements don't autocommit

Anyway, you just need to commit here

[rapid narwhal]

oh

I didnt fully get it

[paper flower]

What exactly you don't understand?

[rapid narwhal]

the part where my code isnt updating count

[rapid narwhal]

In here

[paper flower]

You have to commit

[rapid narwhal]

import sqlite3

# connecting to the database
connection = sqlite3.connect("new.db", isolation_level=None, timeout=2)

crsr = connection.cursor()

#crsr.execute('''CREATE TABLE messages
#             (guildid real, userid real, count real)''')

try:
    crsr.execute("""UPDATE messages SET count=5 WHERE guildid=267624335836053506 AND userid=273847633205002260""")
    print('a')
except:
    crsr.execute("""INSERT INTO messages VALUES (267624335836053506, 273847633205002260, 1)""")


print("Done!")

connection.commit()

connection.close()```

i did though

But it's not committing

[rapid narwhal]

here is my full code

[paper flower]

Well, that should work

[rapid narwhal]

It isn;t working in my case

[paper flower]

You're using floating point numbers for your ids

That might be the issue

[rapid narwhal]

how do i ensure those arent floats

i need them all to be ints

Real means float?

[paper flower]

Yes

[rapid narwhal]

OH BRUH

now i see why

[paper flower]

Real values are real numbers with decimal values that use 8-byte floats.

You need bigint for 64 bit integers

[rapid narwhal]

how do i delete a table again

[paper flower]

drop table ... *

[rapid narwhal]

alright

Thanks

[rapid narwhal]

i used int though

int works for storing discord ids right

[paper flower]

No, you need bigint

[rapid narwhal]

how do i simply append the count's value

[rapid narwhal]

oh ill switch to that

count=count+= or smth

nvm i got that working

with count+1

[paper flower]

count = count + 1 if you want to increment

[rapid narwhal]

yup i tried that and it worked lol

:yay:

[paper flower]

You shouldn't use == with floats in any language

Including sql

[rapid narwhal]

oh

[paper flower]

try 0.2 + 0.1 == 0.3 or something like that

[earnest wave]

How can I create a user_id field in the model project and give reference or ID in the User model?

[fading patrol]

I have no idea what ORM that is, but you seem to be asking about a basic foreign key, probably one to many (each project has one user, each user can be on many projects)? Here's how that works in SQL alchemy, you should be able to find the equivalent doc for whatever that is you're using https://docs.sqlalchemy.org/en/14/orm/basic_relationships.html#one-to-many

[torn sphinx]

heyy i need help : i have project which asks to use python and mysql[as back end] , i am kinda unfamiliar with mysql.
can u suggest me a nice topic

[frozen grotto]

a college management system (basic project) is always fun
https://www.youtube.com/watch?v=sFzj6ZfUxus

[torn sphinx]

ayee thanks iII keep that in mind 😄

[torn sphinx]

but u sure its not too big for 8 marks 0.0??

[frozen grotto]

I don't know what that means, but I don't believe a project can be too big for a grade, it's good to try and learn from

[torn sphinx]

ayee that fired me up xD
okay on it then sirrrrr/maam

[torn sphinx]

weII i hope i can trouble u with my doubts if im stuck T.T

im not reaIIy good at sqI 😓

[torn sphinx]

what if.....i reduce it to a ........coaching instituion ?? just to simpIify

[frozen grotto]

yeah asking here is usually best though (not in DM), because I haven't done the project myself yet but I put it on my to do-list for later

[frozen grotto]

doesn't sound simpler per se

[torn sphinx]

haha kooll

[torn sphinx]

T.T

weIp then college management it iss

[pine kernel]

I am trying to learn a database before my first internship (have a lot of experience in Python) but I am stuck on if I should utilize something like Django's database or try expanding into something like PostgreSQL or MySQL?

[storm mauve]

Django's "database" is an ORM which can connect to some relational databases
it is not an actual database on it's own - more like a wrapper

you might want to learn SQLAlchemy, but I would recommend just using SQL directly without an ORM first

do not focus on Django's unless you're only ever planning to use it

[stoic pewter]

@bot.command()
@commands.has_permissions(administrator = True)
@commands.bot_has_permissions(ban_members = True)
async def warn(ctx, member: discord.Member = None, *, reason = 'Отсутствует'):
    try:
        if member is None:
            await ctx.send("Выберите участника")
            return

        warnings = await bot.db.fetch("SELECT * FROM warnings WHERE guild_id = $1 AND user_id = $2", ctx.guild.id, member.id)
        today = datetime.datetime.now()
        date_time = today.strftime("%m/%d/%Y, %H:%M:%S")
        await bot.db.execute(f'INSERT INTO warnings(guild_id, user_id, warn, moderator_name, moderator_id, Time, reasons) VALUES($1, $2, $3, $4, $5, $6, $7)', ctx.guild.id,member.id,0,str(ctx.author),ctx.author.id,date_time,reason)

        await bot.db.execute(f'UPDATE warnings SET warn = warn + 1 WHERE guild_id = $1 AND user_id = $2', ctx.guild.id, member.id)

        emb=discord.Embed(title=f"<![admin](https://cdn.discordapp.com/emojis/991323514709745807.webp?size=128 "admin")991323514709745807> {ctx.guild.name} | Warn", description=f"`{member.name} [{member.id}]` **получает предупреждение.**\n`Причина:` **{reason}**.", color=discord.Color.from_rgb(128, 0, 255), timestamp=ctx.message.created_at)
        emb.set_footer(text=f"Выдал: {ctx.author.name} | {ctx.author.id}", icon_url=ctx.author.avatar_url)
        await ctx.send(embed=emb)
        
        await member.send(f"**{member.name}**, вы получили предупреждение, на сервере **{ctx.guild.name}**. `Причина:` {reason}**", components = [Button(label =f'Отправлено с сервера {ctx.guild.name}', emoji = '📩', disabled = True)])

        warn = await bot.db.fetch("SELECT warn FROM warnings WHERE guild_id = $1 AND user_id = $2", ctx.guild.id, member.id)
        
        if warn == 3:
            print(1)
            ```

emb=discord.Embed(title=f"<![admin](https://cdn.discordapp.com/emojis/991323514709745807.webp?size=128 "admin")991323514709745807> {ctx.guild.name} | Ban", description=f"`{member} [{member.id}]` **был заблокирован.**\n`Причина:` **3/÷ строгих предупреждений**", color=discord.Color.from_rgb(128, 0, 255), timestamp=ctx.message.created_at)
            emb.set_footer(text=f"Бан от: {ctx.author.name} | {ctx.author.id}", icon_url=ctx.author.avatar_url)
            await ctx.send(embed=emb)
            await bot.db.execute("DELETE FROM warnings WHERE guild_id = $1 AND user_id = $2", ctx.guild.id, member.id)
            
            await member.send(f"**{member.name}**, вы были заблокированы на сервере **{ctx.guild.name}**. **`Причина:` 3/3 предупреждений**", components = [Button(label =f'Отправлено с сервера {ctx.guild.name}', emoji = '📩', disabled = True)])
            await member.ban(reason=reason, delete_message_days=0)
            return
    except Exception as error:
            await ctx.send(f"{error}")
            print(error)

When 3 warns are typed, if fails, why?

[frosty halo]

What is the best way to integrate an old database into a new API project?

[fading patrol]

It's not clear to me what you mean to ask here. What ways are you considering?

[paper flower]

What are you using to create that API?

Is there anything that manages current database schema? (e.g. django migrations)

If you're using a modern backend framework like fastapi i'd probably use sqlalchemy+alembic

[glass mason]

anyone good with sqlite db

bash: sqlite3: command not found

can anyone help me with this?

[fading patrol]

Did you install it?

[glass mason]

its solved

Can anyone help me out with this-->> *django.db.utils.IntegrityError:The row in table 'flights_flight' with primary key '1' has an invalid foreign key: flights_flight.origin_id contains a value 'New York' that does not have a corresponding value in flights_airport.id."

I am getting this error while making migrations

[frosty halo]

@fading patrol @paper flower sorry it was a poor question.

I have a large DB (Microsoft SQL). I am now looking to create an API that uses that DB as the main data source. However, while I can connect to it, I can't seem to find any examples of using frameworks like Flask or fastAPI where they weren't building the whole thing from scratch (such as the classes for SQLAlchemy). The DB I have is very large and to build a class for each table will be very long.

[paper flower]

Do you want to control your schema from that application too or not? 🤔

[frosty halo]

what do you mean by control?

it won't be used in terms of updating or modifying records. Solely for get requests.

[paper flower]

Like migrating your schema

Adding/removing/altering tables

[frosty halo]

there won't be an immediate need for that

it's updated by another existing application

[paper flower]

I guess you could try sqlalchemy's automap 🤔
https://docs.sqlalchemy.org/en/14/orm/extensions/automap.html

[keen minnow]

famous last words.
Regardless of when it will happen, it will help you understand how you want to control the schema

[paper flower]

They could add alembic later, it's an existing database

[keen minnow]

sure. The point is to make sure they are aware of their options.
Not caring about how to maintain their software is a sure way to paint yourself into a corner

[frosty halo]

I take your point @keen minnow - indeed it's the optimal approach to build generally (for the future), and of course it increases my knowledge.

[frosty halo]

trying the automap feauture as suggested by @paper flower ```py
from sqlalchemy.ext.automap import automap_base
from sqlalchemy.orm import Session
from sqlalchemy import create_engine

Base = automap_base()

engine, suppose it has two tables 'user' and 'address' set up

engine = create_engine("mssql+pyodbc:///?odbc_connect=%s" % params)

reflect the tables

Base.prepare(autoload_with=engine)

mapped classes are now created with names by default

matching that of the table name.

User = Base.classes.Bets

I get an error for all tables AttributeError: Bets

looking at the traceback is ```
return self._data[key]

KeyError: 'Bets'

is there anyway to print out the schema generated by Base.prepare ?

[paper flower]

Maybe name is different, try inspecting the classes field

[frosty halo]

classes isn't defined until then

it's creating an object ```
Base.classes
Out[74]: <sqlalchemy.util._collections.Properties at 0x262204e11c0>

[paper flower]

Try Base.classes._data

Also much better to use a debugger in a tool like PyCharm or VSC

[frosty halo]

yes I can see some tables have been mapped

about 30% of them

[grave eagle]

is there a good way to insert a "list" of values, with the samye id ( in pairs, value id) into the database without loops in the code?

[paper flower]

What do you mean by "with same id"?

[grave eagle]

server ID - tag , where a server can have more than one tag

[paper flower]

What are you using to communicate with your db?

[grave eagle]

"INSERT INTO Blacklist (Tag, ServerId) VALUES (?,?)", (guild, tags)

[grave eagle]

sqlite3

id prefer to do a "bulk" query instead of a for tag in list: blablabla

[paper flower]

There's cursor.executemany

[grave eagle]

explain

[paper flower]

https://docs.python.org/3/library/sqlite3.html#sqlite3.Cursor.executemany

[grave eagle]

oh! oh... OH

the issue, is i cant do that tho

[paper flower]

Why?

[grave eagle]

i'm using small "system" i made to have singleton, and a queryqueue, and there is no way i cant make the connection return executemany, to the query

bot > dbcontroller > querycontrolled & query queue > singleton connection

and i make the queries in the db controller

[paper flower]

If there's such method on cursor you should be able to use it

If your architecture doesn't allow it, well

[unkempt prism]

You might have some luck with https://www.sqlite.org/json1.html#4.14.1

INSERT INTO Blacklist (ServerId, Tag) SELECT ?, json_tree(?);

I never used it myself though it seems to behave like unnest would in postgres

[grave eagle]

mhm, passing a key value of id: tag? or {id: id, tag: tag}

[unkempt prism]

I assume an single ServerId had multiple tags. So you can pass an array of tags.

[grave eagle]

nop

sqlite doesnt like arrays x.x

[grim vault]

!e

import json
import sqlite3

guild_id = 1234567890
tag_list = ["Tag_1", "Tag_2", "Tag_3"]

conn = sqlite3.connect(":memory:")
curs = conn.cursor()

sql_stmt = 'SELECT ?, tags.value FROM json_each(?) AS "tags"'
sql_bind = (guild_id, json.dumps(tag_list))

rows = curs.execute(sql_stmt, sql_bind).fetchall()
print(rows)

[delicate field]

@grim vault :white_check_mark: Your 3.11 eval job has completed with return code 0.

[(1234567890, 'Tag_1'), (1234567890, 'Tag_2'), (1234567890, 'Tag_3')]

[drowsy yew]

ive seen this done but how can i create my own file type

like this one

[tacit rune]

Hi,
I am not sure to understand why this condition changes the result :

WITH reac_vouch as (
        -- Voucher that are part of reactivation voucher campaign since 01-01-2021
        SELECT
        v.merchant_id as merchant_id
        ,m.country
        ,v.created_on::date as created_on
        ,MAX(v.id) as voucher_id

        FROM bc.vouchers as v
        LEFT JOIN wkda.voucher_references       as vr       on vr.voucher_id = v.id 
        LEFT JOIN bc.merchants                  as m        on m.id = v.merchant_id
        
        WHERE 1
        AND v.created_on BETWEEN '2021-01-01' AND date_trunc('day', dateadd(month, -2, current_date))
        GROUP BY 1, 2, 3
),

sales as (
    SELECT cs.buyer_id as merchant_id
    , cs.id as car_id
    , nvl(cs.b2b_deal_datetime, cs.sell_datetime)::date as sell_date
    
    FROM reac_vouch                     as rv
    LEFT JOIN bc.car_sales              as cs   ON cs.buyer_id = rv.merchant_id
    LEFT JOIN       wkda.car_leads      as cl   USING (id)
    LEFT JOIN       bc.merchants        as m    ON m.id = cs.buyer_id 
    WHERE cl.status_id in (14, 114)
),

count_bad_rv as (
    SELECT rv.voucher_id
    , rv.country
    , COUNT(CASE WHEN 
        sales.sell_date BETWEEN dateadd(day, -89, rv.created_on) AND dateadd(day, -1, rv.created_on) THEN 1 END) is_bad_rv
    , COUNT(CASE WHEN 
        sales.sell_date BETWEEN dateadd(day, -70, rv.created_on) AND dateadd(day, -5, rv.created_on) THEN 1 END) is_very_bad_rv
    FROM reac_vouch as rv
    LEFT JOIN sales USING (merchant_id)
    -- WHERE sales.sell_date BETWEEN dateadd(day, -90, rv.created_on) AND rv.created_on
    GROUP BY 1, 2
        
)

SELECT country
, COUNT(voucher_id) as qty_RV
, 100 * COUNT(CASE WHEN is_bad_rv > 0 THEN 1 END)::numeric / qty_RV::numeric as bad_RV_share
, 100 * COUNT(CASE WHEN is_very_bad_rv > 0 THEN 1 END)::numeric / qty_RV::numeric as very_bad_RV_share

FROM count_bad_rv
GROUP BY 1
ORDER BY 3 DESC

I am working on Redshift

Have limited experience with SQL but been thrown into it

The commented WHERE do change all the 3 count significantly and I am not sure to understand why

Would appreciate any help, I have tried a lot of things

As a side question, would you rewrite the last CTE differently ?

[paper flower]

That looks like a very complex query, maybe if you share schema of your tables and what you want to query people may come up with different solution

[unkempt prism]

As the WHERE contains sales it is effectively a INNER JOIN

If thats not the intention you can use a OR statement in your where where sales is not matched on merchant

WHERE sales.merchant_id is null OR (sales.sell_date BETWEEN dateadd(day, -90, rv.created_on) AND rv.created_on)

Edit: add brackets for order of operations

[glad bobcat]

Hi everybody. I'm playing around with Postgres JSONB field and I'm curious if there's a simpler way to write that:

SELECT
    jsonb_path_query_array(data, '$.info.players[*].win'),
    jsonb_path_query_array(data, '$.info.players[*].puuid'),
    data->'metadata'->>'match_id'
FROM lor_game
WHERE TO_DATE(data->'info'->>'game_start_time_utc', 'YYYY-MM-DD') > '2022-07-15'

I'd like the first two fields to make a single array together, but I'm not really finding a good syntax for it with nested arrays inside a JSONB. Also I have no clue how to write that in SQLAlchemy lol

I guess since I know I have exactly two players I can just name them by hand and have 4 fields:

SELECT
    data->'info'->'players'->0->'win',
    data->'info'->'players'->0->'puuid',
    data->'info'->'players'->1->'win',
    data->'info'->'players'->1->'puuid',

But how would I do it with an unknown number of fields?

[paper flower]

You can call any function via sqlalchemy func, for example func.jsonb_path_query_array(table.c.data, "query") but for -> operator I think you can use ["key"] 🤔

stmt = select(User.data["key1"][0]["key2"][1])

SELECT (((users.data -> %(data_1)s) -> %(param_1)s) -> %(param_2)s) -> %(param_3)s AS anon_1 
FROM users

[glad bobcat]

Oh sweet, thanks 😄

I just have to understand how to fuse a JSONB array into an array without taking all keys...

[jaunty linden]

my pgadmin4 is not showing default postgres server on startup... shouldn't it already do that ?

postgres service is activated

just once i would like my queries answered in this stupid channel

[crimson tangle]

what 400mb one contain ?

[jaunty linden]

desktop version

[spring tartan]

TIL You can call functions in SQL

[crimson tangle]

?

i have 64bit os

[jaunty linden]

there is activity in this channel so i assume some users answer queries... i can't get none

[crimson tangle]

@polar isle which one is good for me ?

just asking

free to ignore nvm

[polar isle]

i dont know, dont use windows

[crimson tangle]

which os u use ?

[polar isle]

linux

[crimson tangle]

oh k

[jaunty linden]

any idea why pgadmin4 is not showing postgres server on startup by default? (don't say you use dbeaver)

[spring tartan]

I use terminal

[polar isle]

how did you even know that I use postgres and I use pgadmin4 (btw I installed it like 3 days ago and cant help you)

[jaunty linden]

smh

[spring tartan]

I have used all three, but terminal + IDE worked best for me (dbeaver>pgadmin4)

[polar isle]

i dont care, i just installed it cus i wanna view my database

[spring tartan]

Same here

[jaunty linden]

if you have nothing useful to say it's ok to keep quiet

yeah that was meant for you genius

[hexed elk]

is there a python with mysql connectivity expert here? who can help me with my school project?

[spring tartan]

Well Mr. Genius you also talk nonsense

[somber ember]

wasn't there a famous website that lists multiple open source projects like the one by meta or something to practice sql? I seem to have forgotten the name nor ability to find one

[round drum]

please i need help with something

[spring tartan]

If you find one let me know.
I can send https://www.sql-ex.ru/ for exercises, and some apps do deliver decent introductory SQL
https://play.google.com/store/apps/details?id=randomappsinc.com.sqlpracticeplus

[spring tartan]

Is it about requests?

[round drum]

yes

[wet berry]

So before you actually commit data with sqlite3 can you query the database as if the data is there?

[hidden wedge]

mysql install on mac os x running into problems with not being able to access it

[naive anvil]

The SQLTools extension for VSCode gives me the error Client does not support authentication protocol requested by server; considering upgrading MySQL client and the solution I saw on StackOverflow didn't help fix it. I'm using my root user with no password mysql -u root to open up mysql in the terminal.

[naive anvil]

I seem to have made it worse, as mysql isn't running in my terminal anymore either and gives me the error ERROR 1524 (HY000): Plugin 'mysql_old_password' is not loaded

[fading patrol]

By now maybe you tried it yourself but... No, if you haven't committed data inserts, the data is not in the DB and won't be returned by a select query.

[wet berry]

Thank you! No I am away and was wondering

[crimson tangle]

Where I can get free hosted mySQL

[paper flower]

On your pc 😉

[crimson tangle]

24/7 ? 😳

[keen minnow]

yeah, that's what I do.

[spring tartan]

If not then cloud.

[paper flower]

tldr: No such thing as free hosting

[tacit rune]

is there anyway to do that in SQL :
CTE as (... FROM sales)
SELECT CTE(sales) as past_xx_month, CTE(sales2) as next__xx_months

change the data the CTE is looking from like a function

[sterile pelican]

How to enable autocommit in aiosqlite

[fading patrol]

If it's not in the docs and it doesn't work when you try the obvious, I would guess it's not supported. You could open an issue on GitHub to be sure

[naive anvil]

I entered the following commands while trying to fix another issue I had with SQLTools (as per https://dev.mysql.com/doc/refman/5.6/en/old-client.html)

mysql> UPDATE mysql.user SET plugin = 'mysql_old_password'
mysql> WHERE User = 'root' AND Host = 'localhost';
mysql> FLUSH PRIVILEGES;
mysql> SET PASSWORD FOR
    -> 'root'@'localhost' = OLD_PASSWORD('new_password');

But this made things a lot worse and I'm unable to even get access mysql through mysql -u root because it gives me the error ERROR 1524 (HY000): Plugin 'mysql_old_password' is not loaded. I can't run mysqladmin and sudo mysqld_safe --skip-grant-tables gives me the error

[1] 51759
my-username@Macbook-Pro ~ % 
[1]  + suspended (tty output)  sudo mysqld_safe --skip-grant-tables

When I tried to run mysqld --old-password=1 I got a whole lot of 2022-08-17T11:10:18.149573Z 1 [ERROR] [MY-012574] [InnoDB] Unable to lock ./ibdata1 error: 35

I have tried uninstalling and reinstalling using homebrew for macOS, when that didn't work I also tried manually installing mysql from the website but that didn't work either.

[solemn shuttle]

What is the best practice for updating a database table with some structure based on some input using python.
Currently I check if any of my inputs dont exist
Eg if i had a vehicle table

+----+-----------+--------+---------+-----------+
| id | model     | wheels | windows | bluetooth |
+----+-----------+--------+---------+-----------+
| 1  | Supra     | 4      | 4       | True      |
+----+-----------+--------+---------+-----------+
| 2  | Commodore | 4      | 4       | False     |
+----+-----------+--------+---------+-----------+
| 3  | i-Road    | 3      | 4       | True      |
+----+-----------+--------+---------+-----------+

In python I have something to this effect,

class Vehicle:
  def __init__(self, model, wheels, windows, bluetooth=None):
    self.model = model
    self.wheels = wheels,
    self.windows = windows,
    self.bluetooth = bluetooth
  def gen_sql_insert(self):
    if self.bluetooth is None:
      self.bluetooth = "NULL"
    sql = f"""
          INSERT INTO vehicle (model, wheels, windows, bluetooth) VALUES ({self.model}, {self.wheels}, {self.windows}, {self.bluetooth})
          """
    return sql

My use case is transferring scraped data from an api to a database not vehicles but easier example

[paper flower]

You should use prepared statements with sql, in 99% of cases

instead of string formatting

Also it looks kind of ok, what's the problem?

I would personally probably use pydantic for parsing and validation data and sqlalchemy as an ORM so I don't have to use SQL directly

[solemn shuttle]

Are prepared statements a MYSQL thing or a general sql thing?

The issue i have is that every now and again, I get some information that doesn't quite parse correctly and it breaks.

[paper flower]

Afaik they're handled by dbapi (library), not mysql itself

[solemn shuttle]

I create a list of all the new things scraped and put them into objects and then have some function that pushes threm through to a postgresql db using pg engine

[paper flower]

You just said that you're using mysql 😅

[paper flower]

Imo this is kind of ok, but it could be better

[solemn shuttle]

sorry, I asked if about MYSQL because the first result i found re: prepared statements was all about MySQL implementation

[paper flower]

>>> cur.execute("INSERT INTO test (num, data) VALUES (%s, %s)",
...      (100, "abc'def"))

https://www.psycopg.org/docs/usage.html?highlight=prepared

[solemn shuttle]

Yeah it works but it's currently the thing causing me a few headaches, and sometimes you dont know what you done know 🙂

[paper flower]

Well, firstly I'd use pydantic to parse your data with less headache, are you scraping web pages or using a json api?

[solemn shuttle]

oh wow I dont know how i missed that two years ago

the latter

[paper flower]

Well, pydantic is perfect for that

[solemn shuttle]

def insert_quarter_time_score(match_centre):
    conn = connect_to_database()
    cursor = conn.cursor()
    for period in match_centre.period_scores:
        sql = f"""
                INSERT INTO quarter_time_score
                (match_id, team_id, quarter_time_score_quarter, quarter_time_score_goals, quarter_time_score_behinds,
                quarter_time_score_score)
                VALUES
                ({match_centre.match_id}, {get_team_id(period['team_id'])}, {period['periodNumber']},
                {period['score']['goals']}, {period['score']['behinds']}, {period['score']['totalScore']})
                ON CONFLICT (match_id, team_id, quarter_time_score_quarter)
                DO UPDATE 
                SET
                quarter_time_score_goals = {period['score']['goals']},
                quarter_time_score_behinds= {period['score']['behinds']},
                quarter_time_score_score = {period['score']['totalScore']}
                """
        try:
            cursor.execute(sql)
            conn.commit()
        except Exception as e:
            print(e)
            conn.rollback()

That is one of said functions that I currently have, yeah pydantic sounds great

had not heard of it before