databases

[icy oyster]

but

[ruby breach]

it's not a good idea to use f-strings for sql

[icy oyster]

👀

why's that?

[ruby breach]

because if you're dealing with user input they can run raw sql

[icy oyster]

and whats the alternative

[icy oyster]

not user input

in this case

[ruby breach]

ah ok, well it's still a good idea to use params

like this:

curr.execute("INSERT INTO mytable VALUES (?, ?, ?)", 1, 2, 3)

you might have to put the params into a list

curr.execute("INSERT INTO mytable VALUES (?, ?, ?)", [1, 2, 3])

one of those will work

[icy oyster]

okay

perfect

noted

👍

and just for me to confirm, if I want to avoid using this, can I just write NULL?

[ruby breach]

generally it's good to specify the columns, because otherwise you're relying on the order of the columns

plus it's harder to read

but yes, you can just insert null (with no quotes)

[icy oyster]

ok then I'll add it

and end of story 👍 🙂

[torn sphinx]

Hello how can i insert multiple tables into a table itself if that makes sense

i'm using TinyDB

for context what i mean is for example i have a week which is a table that contains the days of the week and i want those days of the week to contain some slots as well that hold information

i'm not sure how to go about it

[paper flower]

You mean like a relationship?

For example you might have users and posts, and they're linked with post.user_id?

[jade wing]

never used TinyDB, but know it's document oriented and stores everything in json files, and it can't handle relationships

[jade wing]

as it's json i guess you can store your lists/arrays and dictionaries under keys more or less any way you like

[forest basin]

Hi, I'm trying to use PyMongo with my mongo database, I want to get the sum of amount on a bet using this request :

totalOnBet = collectionMise.aggregate([
        {
            "$group":
                {
                    "_id": bet["_id"],
                    "count":
                        {
                            "$sum": "montant"
                        }
                }
        },
        {"count": 1}

But I got the following error : pymongo.errors.OperationFailure: Unrecognized pipeline stage name: 'count', full error: {'ok': 0.0, 'errmsg': "Unrecognized pipeline stage name: 'count'", 'code': 40324, 'codeName': 'Location40324'} I made research about it on the Internet but can't manage to find a working solution...

[torn sphinx]

I considered using that but i couldn't come up with a good relationship

[torn sphinx]

I started doing something similar but i'm still not very contempt with how it looks

Thanks for the help both

[paper flower]

If you need relationships it's better to not use tinydb

[jade wing]

how so?
and if it would have been me i would have used a relational sql database instead, even sqlite would do

[paper flower]

@jade wing You have any experience with NoSQL databases?
I'm just not sure what advantages are if in most cases your data is relational and SQL would be a better fit?

[jade wing]

not a whole lot other then some key-value stores and time series databases
even if the different databases has their uses i mostly lean towards relational sql databases, primarily such as sqlite, mysql/mariadb, postgres and oracle
how about you?

[paper flower]

I mostly use postgres, had to use redis for simple caching, i think that's all 🤔

[torn sphinx]

Uh i can't sent snippets from the code now as i am about to head to bed but i'll try to share with you here or in dms if you want tomorrow, basically i wanted to find a small database to use just to store information taken from XL files using openpyxl

I had suggested sqlite for my teacher but he insisted that either UnQL or TinyDB would be better

Since UnQL didn't work for me because of some technical issues i used TinyDB

[torn sphinx]

Yeah i assumed as such after my miserable fail trying it.

[jade wing]

i very much disagree with your teachers assessment of what type of database would be best suited for this, but that's just me
i wounder why he was pushing for you not to use a sql database, if it is to force you to learn something else or if it's just because of what he is most conferrable with him self or if he is just a hipster and want to roll with what is maybe perceived by him as "cooler"

[paper flower]

It's what they say in the docs 😅

sql is cool 🙂

[jade wing]

yeah, but some people just want to jump on that "new thing" that "everyone" is talking about, even though nosql isn't new at all, it just wasn't named that before

[livid moss]

hello please help me with this, why i cant search it using "=" condition ? in postgresql jsonb
sample data

 {
        "_id": "76160c14-a241-4b4e-a54f-d59feb248c7c",
        "data": {
          "info": {
            "array1": [
              1,
              2,
              3
            ],
            "array2": [
              5,
              7,
              6
            ]
          }
        }
      }

example query

select * from sample_table1 where document->'data'->>'info'::varchar = 'array1'

[paper flower]

nosql isn't that new, also if you have some unstructured data postgres has jsonb type which is quite fast

[paper flower]

How exactly you want to filter it?

Just check if info is a string?

[paper bluff]

reading thru the pony orm docs
"Even if a function just reads data and does not make any changes, it should use the db_session() in order to return the connection to the connection pool."

connection pool = ... a pool of available connections to a db?

https://docs.ponyorm.org/firststeps.html#db-session

[paper flower]

Yep, you usually acquire a set number of connections so you don't have to establish new one each time

If you don't release connection into pool depending on pool settings you would essentially starve your app out of db connections, it would try to wait for old ones to be released

[paper bluff]

typically roughly how many connections in a pool?

i'm using postgres

[keen minnow]

It's a function of the number of simultaneous operations you want to handle on a given application server as well as how many opened connection can the db handle.
Unless we are talking high throughput apps, I would just put 5-10 to get started

(there are also ways to monitor the usage of your connection pool [ex: how long to wait for a connection, how many connections used at a given time, etc], but that's probably out of scope of this discussion

[lunar kettle]

select count(payment_id) pending_order from payments where delivery_status = 0 and select count(product_id) total_product from products where active_status=1;

how to make this query into one

select count(payment_id) pending_order from payments where delivery_status = 0;
 select count(product_id) total_product from products where active_status=1;

i need both of them in 1 query or single table output

[paper flower]

https://stackoverflow.com/questions/12789396/how-to-get-multiple-counts-with-one-sql-query

[tardy badge]

help, whats wrong with my increment

[paper flower]

+= isn't a thing in sql

Quantity = Quantity + 3

[jade wing]

i know and that was what i was saying as well
databases that could be termed "nosql" existed way before any sql database did
sql was first standardized in 1986 (ANSI) and 1987 (ISO)
but the term "nosql" wasn't minted until the early 2000s, "new" is kind of relative here 😉

[frail heath]

Hello is there a big difference in performance between or is there a way i can test?

document = await db.test_collection.find_one({'i': {'$lt': 1}})

and

async for check in MongoCommandStats.find({"MemberId": "main" },{search: 1}):
     searchvals = check[search]

[violet token]

Hi, would you be able to help me with my older question, im really sorry for the unnecessary ping

,

this one

[torn sphinx]

No it is for a project i'm working on so i'm not sure

[torn sphinx]

I'm not really familiar with them

[paper flower]

https://tinydb.readthedocs.io/en/latest/intro.html#why-not-use-tinydb

[torn sphinx]

Oh you mean the tiny db documentation yeah i read a bit of it but i didn't see anything about relationships

Sorry be patient with me i'm not familiar with the terms and such

[jade wing]

and you won't find anything about relationships in tinydb as the documentation on the link that @paper flower just posted for you explicitly says that it's not support by tinydb

[jade wing]

yeah, just need to do a few other things first
but i can say there is multiple issues with that code that should be addresses
could you also give an example how that permcache class is intended to be used in code by posting some example code that would use it?

[violet token]

so its basically like a number counter, I am planning to use it on the error handler for my bot, each time there is an error, it assigns an error id with the unique id being the error name itself.

it basically adds a number for each error my bot catches

so I can trace back to the error if a user finds one

[torn sphinx]

Okay okay i understand now, i'll see what i can try with it and if it works i'll keep using it if not i'll have a try at sql lite or something, i'll let you know

[jade wing]

i see, can you provide a usage example in code?
as i though the function name of _add() in the class was a bit of an odd choice with the underscore at the beginning suggesting it's only for internal use within the class

[violet token]

haha my naming skills are on point

ill give you an example 1 second

[paper flower]

@jade wing Ever used select ... for update? I find it useful in case you need exclusive access to a row

Because alternative is a version counter

[jade wing]

yeah, i have
but i think it's @violet token that needs the help, not me 😉
and i would rather use something atomic within the database instead of using a select and then an update or insert in a transaction when the logic permits

[paper flower]

I'm just asking your opinion on using it, if you had any problems, etc

@violet token What's your problem?

[violet token]

Cache instance

# instance of perm cache which increases id value by 1 every time it recieves this error
noperms = permcache(id="no-perms-error")```

Error handler
```py
    # Bot missing permissions
    if isinstance(error, app_commands.BotMissingPermissions):
        embed = discord.Embed(description=f"```diff\n- {error}```", color=discord.Colour.magenta())
        embed.set_footer(text=f"ERROR ID | {noperms._add()}")
        embed.set_author(name="Opal [ERROR]",icon_url=client.user.display_avatar)
        await interaction.response.send_message(embed=embed)

[paper flower]

select for update is essentially atomic though main use is handling concurrent access? 🤔

[violet token]

oops my error handler contained \\

sorry

for the break in the code

    # Bot missing permissions
    if isinstance(error, app_commands.BotMissingPermissions):
        embed = discord.Embed(description=f"", color=discord.Colour.magenta())
        embed.set_footer(text=f"ERROR ID | {noperms._add()}")
        embed.set_author(name="Opal [ERROR]",icon_url=client.user.display_avatar)
        await interaction.response.send_message(embed=embed)

[jade wing]

sort of but not really, as it's a transaction with distinct operations, but it will behave mostly as if it was atomic

[paper flower]

Is there a db problem?

[violet token]

yes, instead of using the db in the library it creates a db in the folder the script is in, and then tries to use it.

[paper flower]

all transactions are atomic, no? 🤔

[paper flower]

You're probably running python {insert_file_name}.py from wrong directory

[jade wing]

eventually when they are commited, yes

[violet token]

the library has been imported from github

[paper flower]

What library? Have no idea what library you're using

[violet token]

.

[paper flower]

Where do you expect db file to be created?

[violet token]

I expect it to use the db file which is provided in the library

[jade wing]

i believe it's @violet token own library on github: https://github.com/gitnotv/permcache.git

[violet token]

yeah thats mine

but for some reason

[paper flower]

Ah, i see

[violet token]

it doesn't use the database provided in the library

[paper flower]

Can you try return Path(__file__).parent / filename?

Also would be helpful if you tell us where it actually creates the file atm

[violet token]

Do you mean where it creates the database when I use the script, or where it creates the database when i download the lib from github.

[trail bloom]

Hi, I use SqlAlchemy and a MySQL-DB. From my Code I will call a stored Procedure. In this procedure I call an insert-statement. I got this error:

MySQLdb._exceptions.ProgrammingError: (2014, "Commands out of sync; you can't run this command now")

Some Ideas what i can do?

[jade wing]

are you sure sqlite can do select ... for update?
i haven't used that with sqlite specifically but with other databases

[paper flower]

I'm not talking about sqlite, just about your experience in general

[jade wing]

oh, i think it works nicely where it's supported 🙂

[paper flower]

I don't have any experience using mysql but reference manual could be helpful? https://dev.mysql.com/doc/refman/8.0/en/commands-out-of-sync.html

[paper flower]

Version counter otherwise?

[jade wing]

mostly other stuff, i think version counters can often be implemented in better ways

[paper flower]

Hm? How?

What else would you use if you need to prevent concurrent writes to an entity?

[jade wing]

@violet token you should probably have a unique constraint on the uniqueid column in your cache table

[violet token]

?

[jade wing]

many databases (postgres, mysql and even newer sqlite) has support for a none standard sql extension called upsert that can be used for this as kind of a insert or update if exists kind of thing

[paper flower]

nonono, that's not the problem:
Imagine you have some entity (for example a bank account) that you need to update
Two connections might simultaneously get data via select and do something like update balance = ? which would essentially result in latest transaction overriding previous one

if you use select ... for update second transaction would have to wait until first one is done
with version counter you get similar result: transaction which update latest would error out and rollback

[jade wing]

my understanding was that upsert was done as a transaction to mitigate that problem

[paper flower]

Upsert just updates or inserts, no?

That doesn't solve concurrency problems 🤔

[trail bloom]

@paper flower Yea thats good thanks. But I think the whole problem is, that I call a Procedure and in this procedure I have an insert statement. And this insert statement gets not called

[jade wing]

update column = column + 1
should be atomic and mitigate the problem as far as i understand it

[jade wing]

you would probably have to flush out the code for the store procedure here in the channel

[paper flower]

That's atomic, but you're likely to use an orm, so it would send update set column = ? instead of column = column + ?

I agree that updates like this solves the problem but fields might also have types different from integer

[jade wing]

you mean update set column = column + ? right?

[trail bloom]

`BEGIN

DECLARE member_id INTEGER;

SELECT id INTO member_id FROM member s WHERE s.memse_id = _memse_id LIMIT 1;
if (member_id IS NULL) THEN

INSERT INTO member (memse_login, memse_name, memse_id, language, description, created_at, memberer_type, insert_at, last_update) VALUES
(_memse_login, _memse_name, _memse_id, _language, _description, _created_at, _memberer_type,NOW(), NOW());
ELSE

SELECT ("Debug");
END IF;
END`

This is the Stored Procedure.

I Call this procedure from python

[paper flower]

Yep

just difference between setting new value and incrementing it

[jade wing]

yep

[jade wing]

instead of:

CREATE TABLE IF NOT EXISTS "cache" (
        "numeric"       INTEGER,
        "uniqueid"      TEXT
)
```i would go with something like:
```sql
CREATE TABLE IF NOT EXISTS "cache" (
        "uniqueid"      TEXT PRIMARY KEY,
        "numeric"       INTEGER NOT NULL DEFAULT 1
)
```and then an insert query like:
```sql
INSERT INTO cache (uniqueid) VALUES(?)
  ON CONFLICT(uniqueid) DO UPDATE SET "numeric" = "numeric" + 1
  RETURNING "numeric"
```but that requires you to have a unique index of some kind (here it's a primary key) on the uniqueid column
i also added `RETURNING` as i saw in your code that you want to return the new id from the function
for all this syntax to work with `sqlite` you'll need version `3.35.0 (2021-03-12)` or newer

however, all together i would probably implement this in a totally different way with having a globally unique id for all errors instead and use a auto increment counter for that
maybe like this:
```sql
CREATE TABLE IF NOT EXISTS error_log (
   id INTEGER PRIMARY KEY AUTOINCREMENT,
   timestamp DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
   name TEXT NOT NULL
);
```and an insert like:
```sql
INSERT INTO error_log (name) VALUES(?) RETURNING id;

[torn sphinx]

@jade wing is it okay if i share something i want to make a for loop inside a db.insert but it won't let me

[jade wing]

go right ahead and we'll see if anyone of us can help you with whatever it is

[jade wing]

what database are you using now, is it still tinydb or have you changed to something else?

[torn sphinx]

yeah yeah i'm still on tinyDB

    'formation' : '',
    'niveau' : '',
    'departement': '',
    'Faculty': '',
    'Week': [{
        'Day' : 'Dimanche',
        'Creneaux': [{
            'start' : WS['A4'].value,
            'end' : WS['A10'].value,
         ## i want to loop sceances
             'sceances':    [{
             'Group' : '',
             'TypeSceance': 'TypeSceance.value',
             'sceance': 'Sceance.value',
             'ProfName': 'ProfName.value',
             'Salle' : 'Salle.value',   
              }]}]
}]
}
)```

[jade wing]

you would first create the python data structure and then insert it
so the loop would go before the db.insert where you would prepare that data structure

[torn sphinx]

but won't that loop the entire thing? i want to loop just the 'seances' part

[wind sleet]

Not sure this is the right place to ask but do you guys know of a good resource that will explain how I can use an API to populate a table in a database? Nothing I've seen has been very specific. I'm currently not a programmer but I've somehow fallen into a role where I need to learn quick

[rancid grove]

So i am trying to make an sql table but the output duplicates the 'inserted items' many times over. I just want it to print out "[("Sjokolade melk",25,40),("Vafler",15,20),("Is",25,37)]".

so here is the code

[jade wing]

you would get the data you need from an api and then insert the data into a database, it's two separate operations that you would have combine in your code one after the other

[jade wing]

have you checked your database so that you don't have a lot of duplicate entries in it?

[rancid grove]

no, i will check now.

[jade wing]

if the entries in the database are fine it's the code
but i would start by checking the data first

[rancid grove]

it doesent seem wrong

it's not duplicated

but the output is duplicating the 'select'

Connect = sqlite3.connect("skoleball")

Connection = Connect.cursor()

#sqlite3.connect("skoleball").cursor().execute("""CREATE TABLE skoleball_varer (

varer TEXT,

pris INTEGER,

antall_kjøper INTEGER

)""")

Many_execute = [("Sjokolade melk",25,40),("Vafler",15,20),("Is",25,37)]

Connection.executemany("INSERT INTO skoleball_varer VALUES (?,?,?)",Many_execute)

G =Connection.fetchall()

Connection.execute("SELECT * FROM skoleball_varer WHERE pris < 100")

Connect.commit()
G =Connection.fetchall()
print(G)

Connect.close()

Have any idea?

[jade wing]

you shouldn't need to fetchall() after the executemany(), instead you want the commit there before you select
but from the look of this it will insert those three rows every time you run the script, so the data would start to pile up into lots of duplicates
you probably also want to go with (even if it's not required here but it's good to make it a habit):

Connection.execute("SELECT * FROM skoleball_varer WHERE pris < ?", (100, ))
```the last `,` in the `()` is required when just sending in one value otherwise python will not treat it as a tuple which it needs to be

[jade wing]

you would have to build the base of your data structure, then create a loop with adds more data to the data structure as you require and then after the loop do the insert of the whole data structure into the database

[torn sphinx]

so i make the seances outside the db.insert loop it then insert it in place of where it was before?

i'm not sure how to do that but i'll search the documentation maybe i can find something

[jade wing]

if datasource is a list of things that you want to add it might look something like (note: untested code):

seances = []
for item in datasource:
    seances.append({
        'Group' : '',
        'TypeSceance': item.TypeSceance.value,
        'sceance': item.Sceance.value,
        'ProfName': item.ProfName.value,
        'Salle' : item.Salle.value,
        })

db.insert({
    'formation': '',
    'niveau': '',
    'departement': '',
    'Faculty': '',
    'Week': [{
        'Day' : 'Dimanche',
        'Creneaux': [{
            'start' : WS['A4'].value,
            'end' : WS['A10'].value,
            'sceances': sceances,
        }],
    }],
})

[torn sphinx]

oh i see i make it a list okay okay i get it

i'll try this out thank you

[jade wing]

what if you run the following in bash or zsh (if you have any of them in your os) you'll see the raw content of the database right now:

echo 'select * from skoleball_varer' | sqlite3 -table -nullvalue '#NULL#' -readonly skoleball

[jade wing]

yeah, a list of dicts

[jade wing]

please come back and tell us how it went 🙂

[deft garnet]

what project for portfolio can i do with databases?

[jade wing]

almost anything that needs to store some persistent data
preferably not binaries (such as images and other files) though, they usually aren't a good fit for most databases and are better stored in a file system, but you can still store the path to the file in a database
what kind of database would you like to target?
do you want your project to be web based or something else like a desktop application or maybe a discord bot?

[deft garnet]

desktop and web

just antyhing to learn

now i am learning django

[jade wing]

why not combine them then and do a django project that uses a database?

[deft garnet]

Good idea

[midnight iron]

Hi there!
I'm trying to refactor my database class, which currently has methods for fetching, inserting and deleting for each table, as in:

def get_foo(self, ...):...
def set_foo(self, ...):...
def remove_foo(self, ...):...

def get_bar(self, ...):...
def set_bar(self, ...):...
def remove_bar(self, ...):...

This lets me typehint the params and return values, so I don't have to remember the db structure.

However, this is very repetitive, so I'd like to use a syntax like:

class TABLE_1_DICT(TypedDict):
    id: int
    description: str

foo = self.db.TABLE_1.fetch_one() # foo is type TABLE_1_DICT
foo["description"] = "new_description"
self.db.TABLE_1.set(foo)

I don't want to define all the table classes manually though, if possible.
Any ideas? (ping me or I might miss the reply)

[torn sphinx]

hey, im using aiomysql, could someone explain me the difference between a normal connection and a pool?

[brave bridge]

Connecting to a database takes a while. A pool holds a bunch of ready to use connections so that when you need one, you get one immediately.

https://stackoverflow.com/questions/4041114/what-is-database-pooling

[torn sphinx]

sweet, does it create a new connection everytime i acquire a new connection from the pool pool.acquire() or the old one is reused?

[brave bridge]

acquire() gives you a pooled connection

(i.e. not (necessarily) a brand new one)

[jade wing]

also, you should return a connection to the pool as soon as you don't need it anymore and then get a new connection from the pool just when you need one again
that way you can do with quite few connections for the whole app which also takes load off the database having to deal with a lot of connections especially when you grow and need to scale the application out with more instances

[torn sphinx]

oh, got it!

[torn sphinx]

hm, that makes sense, and thanks for the suggestion

i've worked only with sqlite3 locally so had no idea about what a Pool is

[jade wing]

connection pools are life savers in big enterprise environments 🙂

[brave bridge]

You can make a pool for SQLite as an exercise 🙂

although that's largely useless, because making a SQLite connection is very cheap

[torn sphinx]

Hi, I want to make an item-based recommendation system. I found some info on the internet and tried to rebuilt their idea. They did the following: I get always these error...

[clear stirrup]

Not really related to databases, but as the error says, metric is not defined, you have to define it before the function. Probably the same thing with "k"

[rancid grove]

I have just recently came in contact with sql so is it possible for you telling me how the code works?

[midnight iron]

Using sqlite3 is it possible to do smth llike

sql = "REPLACE INTO :table :columns VALUES :values"
params = {"table": "Foo", "columns": ["a", "b"], "values" : [1, 2]}
conn.execute(sql, params)

? (ping me, going afk)

[jade wing]

what os are you running?

[jade wing]

no, placeholders can only be used for values
you will unfortunately need to build up the query string in some way, maybe with f-strings or similar but it will not be as clean and it will be vulnerable against sql injection attacks if any of the variables comes from any kind of external input
or you can look into using an ORM such as SQLAlchemy that i believe will do that for you

[midnight iron]

well, f-strings it is. all the variables come from me, so there's no problem

thanks

oh btw, does the :table part not work either?

[lethal stirrup]

hi im trying to curve my measuring values but unfortunately it doesnt curve fit correctly

[jade wing]

no ORM? 🙂
you could also write your own function that will do what you want just to hide it, but it will still come with the same problems

[jade wing]

no, only values can have placeholders
which database and database driver are you working with?

[midnight iron]

I'm writing a pseudo-ORM to simplify extending the db in the future, nothing very fancy

[midnight iron]

just sqlite

[rancid grove]

like platform? I use Chrome OS

but i use a browser built-in IED which works on all platforms.

[jade wing]

then you could dynamically build a string out of that like the following

sql = "REPLACE INTO Foo (a, b) VALUES (?, ?)"
params = {"table": "Foo", "columns": ["a", "b"], "values" : [1, 2]}
conn.execute(sql, params["values"])
```with the same number of `?`  as there are elements in the `values` list
and you would need to validate that there are the equal number of elements in the `columns` list as there are elements in the `values` list

[jade wing]

oh, i see, can you run commands in a shell like bash or zsh?

[rancid grove]

is shell(s) built-in? If not i can't, i am using a school chromebook which doesen't allow downloading stuff.

[rancid grove]

Wait, can i use crosh?

[jade wing]

i have heard that chromebooks can now run linux environments but i think that requires downloading and installing things

[jade wing]

maybe, never heard of crosh 🤷

[rancid grove]

Crosh is the shell built in google chrome i think, you can access it from pressing alt +shift + t

[jade wing]

or are you running the code and sqlite3 on a remote linux system?

[rancid grove]

I am running the code from an IDE online called replit .

[jade wing]

oh, replit... okay
never used it my self

[rancid grove]

So you know about it?

[rancid grove]

So does it work using replit?

[jade wing]

yeah, but not enough to know if you can run any shell commands in that environment to check what is and what isn't in the database

[rancid grove]

Isn't just to check the debugger?

[jade wing]

i just wanted to run a query that shows all the content

select * from skoleball_varer
```on the database file named `skoleball` using the native `sqlite3` cli client

[rancid grove]

Wait a second let me check.

So i have changed according to you but now the Table is empty, "[ ]".

the code goes like this

[rancid grove]

Connect = sqlite3.connect("skoleball")

Connection = Connect.cursor()


Connection.execute("""CREATE TABLE IF NOT EXISTS skoleball_varer  (
                                              varer TEXT,
                                              pris INTEGER,
                                              antall_kjøper INTEGER
                                              
                                              )""")

Many_execute = [("Sjokolade melk",25,40),("Vafler",15,20),("Is",25,37)]

Connection.executemany("INSERT INTO skoleball_varer VALUES (?,?,?)",Many_execute)


Connect.commit()
Connection.execute("SELECT * FROM skoleball_varer ")

G =Connection.fetchall()
print(Connection.fetchall())

Connect.close()

[torn sphinx]

Im on ubuntu, just downloaded postgres, and ran these commands with the super user:

CREATE USER myuser WITH PASSWORD '123';
CREATE DATABASE people WITH OWNER 'myuser';

now in my normal prompt im trying to connect to the database people as myuser

using psql

like such:

psql people myuser

and i get the following error:

psql: error: FATAL: Peer authentication failed for user "myuser"

[jade wing]

you can only do either

G = Connection.fetchall()
print(G)
```or
```python
print(Connection.fetchall())
```but if you call them both after each other the first will empty the results and the second call will not return any data

[rancid grove]

Oh, thank you a lot. You have wasted a considerable amount of time on me this 'lesson' has taught me more than a week hard work.

[jade wing]

did you get any data as output?

[rancid grove]

Yes.

[jade wing]

how much data, many rows?

[rancid grove]

...

well i fixed it temporarily.

[jade wing]

okay, you have been inserting the same three rows over and over again each time you run the script
so you probably don't want to keep doing that

[rancid grove]

Any way to solve that?

[jade wing]

you could delete all content of the table with the following sql statement:

delete from skoleball_varer
```and then insert the rows once by running the script once and then comment out that part of the code

[rancid grove]

So i will just do this? ``` Connection.execute("DELETE FROM skoleball_varer")

Connection.executemany("INSERT INTO skoleball_varer VALUES (?,?,?)",Many_execute)```

[rancid grove]

OH MY GOD IT WORKED!!!

[rancid grove]

sorry i lost my cool.

[rancid grove]

the feeling when you solved something that was pain in the ass is so amazing.

[jade wing]

now you don't want to run the delete again unless you want to empty the table again
and you probably don't want to continue inserting the data each time the script runs
well, if you run both of them in that order you'll always end up in this same state, but that is probably unnecessary to do over and over again each time

[delicate field]

Hey @torn sphinx!

You either uploaded a .txt file or entered a message that was too long. Please use our paste bin instead.

[torn sphinx]

https://paste.pythondiscord.com/zabizaluce

@jade wing what i did is in the paste bin there, i sent it to my prof to review i might have to change some stuff later tho

depending on what i need , thanks for the help i might come ask more again if i can't figure it out

[jade wing]

it it works it's a start, but you probably want to build it more dynamically so that you don't need to hard code in the row numbers or at least be able to loop over the whole set so that you don't need to repeat your code like that

[torn sphinx]

actually i just realized the start and end change values and don't repeat, yeah i'm not sure how i can make it dynamic

https://paste.pythondiscord.com/yuremigewa

well i fixed the start thing but making it dynamic i'm gonna need to think how i can do that

[green raptor]

hey, i am facing an issue with sqlite, idk why.
code:

index = str(random.randint(0, 43))
qry = """SELECT id FROM Babylist WHERE index = ?;"""
fetch = cur.execute(qry, (index,)).fetchall()
con.commit()
result = fetch[0][0]
print(result)  # debug
return```

error:
```error
Command raised an exception: OperationalError: near "index": syntax error```

[paper flower]

It's a reserved keyword 🤔

You should be able to wrap it into double quotes " to escape it

so, "index"

[jade wing]

i don't know what your excel sheet looks like but it looks like it's always 6 rows, at least in this example, but i guess you can derive more from the information in the first column

[green raptor]

fetch = cur.execute(qry, ("index",)).fetchall()```

like this?

[paper flower]

"""SELECT id FROM Babylist WHERE "index" = ?;"""

[green raptor]

@paper flower thank you very much ❤️

[torn sphinx]

here i'll send you a screenshot gimme a sc

[torn sphinx]

here you go

i went trhough the first day with that piece of code

[jade wing]

why paste it twice?
what are you doing with the big single color blocks in the schedule?

[torn sphinx]

i didn't paste the coordinates the first one mb ,
those are like courses that everyone must attend

[rapid ginkgo]

I need to add two documents to the orders table, they will include user_id (from the users table) and item_id (from the items table)

From the user table, select only the user whose login field is "john".

From the table of items, select only those items that belong to the headwear category.

Place new orders in the order table. As user_id, specify the id of the user that you selected from the users table, and as item_id, specify the id of the product that you selected from the items table.

After adding all the values to the orders table, display the contents of the orders table in the following format:

[molten gazelle]

how can I assign several foreign keys to one table
like consider person to be a table which has car_id attribute
and a car table which has an id and make model, etc.
One person can have more than one car
how can I assign the car ids to a single person
under car_id attribute in person table

[violet token]

oh

my goodness

There is a lot to sql

[paper flower]

I'd rather add user_id column to car

[molten gazelle]

what if there're several people that have the same type of car

[paper flower]

hm?

Won't you add new cars into car table?

And link them to users

[molten gazelle]

Like consider I have a bmw 2020 and you have the same car

[bitter vault]

How exactly does linking using natural keys work again?

[paper flower]

It would be a car_model or car_type, individual cars would be stored in another table

If you want to store just model use many to many relationship here, you can expand it with car-specific columns later

[paper flower]

What it has to do with natural keys?

[bitter vault]

Just how linking works in general and how to do it.. also the purpose of linking would@be nice

[paper flower]

You use foreign keys, natural keys refer to something you can use as a primary key that exists outside of db 🤔

You usually use surrogate keys (integer ids, uuids, etc)

email is an example of a natural key

[barren vine]

Hello! I recently got into coding, but have hit a block I haven't been able to pass. I'm trying to connect a google sheets or .xslx to a visual studio python file, but I keep getting a filenotfound error. How can I fix it? (Also I'm using a chromebook and it is notoriously difficult)

[fading patrol]

Looks like you may just be missing a space in the filename

[barren vine]

Sorry, where exactly?

[fading patrol]

Between transactions and (1). It's hard to be sure from a screenshot though

[barren vine]

Tried it but same issue

:/

[fading patrol]

Ah, your working directory in VS Code is not the directory your file is in. I think if you right click the excel file.on the left side you can copy the full path starting with C: or whatever... Use that in your code instead of just the filename

[barren vine]

Can't right click on my chromebook, It's a one click pad

Very annoying

[fading patrol]

Ok, I'm not used to Chromebook but what happens if you run the command pwd at the bottom there

It should hopefully give you the path starting with / and you can just add that plus excel_host before the filename

I'm about to sign off and this is not really on topic here anyway, so if that doesn't solve it for you grab a help channel ( #❓｜how-to-get-help )

[barren vine]

Doesnt do anything:/

Ok, thanks anyways!

[vapid dock]

how do you run the code?

[fading patrol]

If you haven't solved it yet, I think the best solution is to leave your code as is but got to File > Open Folder and choose the folder where your code and the excel file both are .. that should change your working directory to the right one

[vocal fern]

when trying to create a menu driven program in python, how do i make the input statement where the same value goes into 2 tables

[heavy coral]

I need help with Python

[gray flame]

I have a Database and Post related question in #help-ramen. Any assistance at all would be appreciated, thank you so much 😄

[trail patio]

Hi, I have one question. I'm working with sqlite3 and I wanted to know if there is any way that I could get the rowid from a table when I insert a value without having to execute a SELECT

[fading patrol]

That's a Google question https://www.sliqtools.co.uk/blog/technical/sqlite-how-to-get-the-id-when-inserting-a-row-into-a-table/#:~:text=SQLite has a special SQL,executing the last_insert_rowid() command.

[grim vault]

Use returning?

!e

import sqlite3
conn = sqlite3.connect(":memory:")
conn.execute('CREATE TABLE test (test_id TEXT, test_name TEXT, PRIMARY KEY(test_id))')
curs = conn.execute("insert into test values ('ID-0', 'Name-0'), ('ID-1', 'Name-1') returning rowid")
print(curs.fetchall())

[delicate field]

@grim vault :x: Your eval job has completed with return code 1.

001 | Traceback (most recent call last):
002 |   File "<string>", line 4, in <module>
003 | sqlite3.OperationalError: near "returning": syntax error

[grim vault]

Hm, works for me on 3.9.12.

!e import sqlite3; print(sqlite3.sqlite_version)

[delicate field]

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

3.27.2

[grim vault]

Ok, RETURNING was added with sqlite 3.35.0

[distant ferry]

Guys, I have a question about my program that won't work

I have coded a password manager/storage

that saves the inputted details to a txt file

I encrypt it while entering the details

but when trying to read from the file, it doesn't decrypt and doesn't even print

[fading patrol]

#❓｜how-to-get-help

[distant ferry]

will do

[dusk lance]

So I am not super knowledgeable on your issue but I had a similar problem on VS that was caused because something I was working with was not on PATH, causing everything to just not work. Are you sure that all of your imports/starting materials are correctly implemented?

[odd raven]

isnt there some db.commit() to commit the insert

i hate mysql connector for having something like that

[brave bridge]

you hate transactions? 🤔

[wooden thorn]

Why don't you save each information on a different line and then after reading it make sure their isn't any leading space.

Or Use pickle to dump it as a single object

[torn sphinx]

👋 hey, im using aiomysql
when i execute a "CREATE TABLE IF NOT EXISTS <tablename>" query, no errors are raised but i get a warning in my logs saying a table with that name already exists, is that normal?

https://paste.pythondiscord.com/otetexuwom here's the related code for reference

[paper flower]

Well, if table exists it won't be created, what else did you expect?

It's completely normal

[torn sphinx]

oh sweet then

[paper flower]

@torn sphinx You understand that you would have to manually migrate your database in case you need to add/remove columns from your tables?

[torn sphinx]

Hello Im new to coding and i have a few questions if someone can help me
just text me in my DMs
or pin me
if anyone sees this

[paper flower]

Just ask your question, there's a greater chance people would help you this way

[torn sphinx]

oh ok

alright so like i said i am new and when i say im new i mean it lol

so

yea

can someone tell me what a data type is

??

[keen minnow]

Your learning material should cover these type of things. Wikipedia is also a good way to get that type of question answered. See https://en.wikipedia.org/wiki/Data_type for instance.
If you don't understand specific parts or have doubt, it's alright to ask about it. But the people here won't really have the time to teach you everything on a 1-1 basis.

[torn sphinx]

oh ok

thx

[paper bluff]

first time trying to write an app using a database...
i feel like anything with databases needs more careful planning
i.e. modifying tables once they are made is not as easy as just changing a line of code, and i need to use psql to change things, or drop a table and start over

is this where the concept of "migrations" comes in?

[keen minnow]

Definitely!
Note there are tools to help with the migration such as https://flywaydb.org/

Migrating a DB is a bit more tricky than updating some code. If your app has a bug or whatnot, you can always fix and redeploy, or revert to a previous version.
But if you migration delete or corrupt data, then that means you may have lost some data, which could have been quite important to your business

[paper bluff]

do you have an orm of choice?
i picked pony bc of how clean it seemed and for ease of use, but it doesn't support migrations out of the box

[keen minnow]

I am a java person. I would not be the right person to suggest a python orm

[paper flower]

imo sqlalchemy is quite good 🤔

pony orm querying syntax looks weird

[paper bluff]

mk, i have some general high-level concept-type questions/ramblings then:

the bulk of this tool will be ingesting data from various flat files, all in different formats (because they are from different vendors)
i could:

• ingest all that data as is, and then work off of "cleaned" versions of that data via "views"?
  -- a "view" is basically a SELECT query yes?
• clean the data prior to inserting to db

by "clean" here i mean: dropping columns i might not need, adding columns i might need (based on information in that row)

[paper bluff]

yeah they're list-comp-esque

[paper flower]

Views are database objects that are created from a select query 🤔

For example if you have complex query that takes too long to complete you might create a view and refresh it from time to time

[keen minnow]

Given the context I got on your project, I would also suggest to value the "recomputability".
Since your data is mostly offline, there aren't much worries about transactions themselves. And having different stages and staging areas would enable you to recompute results in case of data being updated or code being updated

[paper flower]

Or that's materialized view, i think views itself just represent the query, materialized views also hold result of said query

[keen minnow]

And unless we are talking multiple Gb per file, the impact on the space should not be a concern

[paper bluff]

the largest file i'm working with is 4gb, the 2nd largest is <100mb

[paper flower]

I think depending on the data you might just normalize it?

[keen minnow]

yeah

[paper bluff]

mk mk

[distant ferry]

Each data is being sav ed in a different line. Anyway, i got it resolved

[wanton pagoda]

how do i make like a list that actually update

like if i out list.append("haha") it would be added to the actual code

the list would actually change.

[sharp blade]

lists are mutable it will change the list object

the append function will not return the resulting list

[wanton pagoda]

like the list that i put will still be there even if i rerun the code

[paper flower]

You need to store it somewhere, all data that was in memory would be gone when your program finishes

[wanton pagoda]

and how do i make that?

like how do i store a string on a file

i saw a lot of people user node.js do that. is it possible in python?

[paper flower]

By writing and reading from a file

[wanton pagoda]

teach me

[paper flower]

You can do it yourself

[wanton pagoda]

uhhh.

[paper flower]

There's a lot of tutorials on how to work with files in python

[opaque wave]

hey guys I'm starting in a new job as an data analyst. The company isn't very tech-y; all I know tech-wise is that they use mysql and google data studio. Even though I'm quite proficient in python and other viz tools, I don't have much xp with sql -- other than an idea of database theory and the basic stuff. If anyone can provide any pointers on important aspects of these two that would be helpful

[graceful widget]

#bot-commands

[torn sphinx]

can someone check my question #help-burrito message, maybe u can help me 😄

[fading patrol]

I would say SQL Alchemy is the standard of you're not using a full framework like Django

[burnt turret]

prisma has been really nice to use

been using it on any personal project that needs an ORM lately (although im not sure if the python client is "production ready" yet)

[torn sphinx]

I’m using Firebase-admin, how do I turn a collection into a dict

[minor ruin]

I'll also note this is why NoSQL databases look so attractive

DocumentDB, just throw on another field into document, nothing should be bothered

Narrator voice: it was

[tropic plank]

guys i need help with aiosqlite

[dire oxide]

    @discord.ui.button(label='Join', emoji='🎉', custom_id='join-giveaway')
    async def join_giveaway(self, interaction: discord.Interaction, button: discord.Button):
        joined_users = cursor.execute("SELECT * FROM giveaways WHERE message_id=?", self.giveaway_message.id)
        print(joined_users.fetchall())```

```py
    joined_users = cursor.execute("SELECT * FROM giveaways WHERE message_id=?", self.giveaway_message.id)
ValueError: parameters are of unsupported type```

oh i forgot parentheses

same error though

It works when i put a comma after like this (self.giveaway_message.id,)

[minor ruin]

yep, it's tuple

[dire oxide]

oh i see.

ahh that makes sense

[dire oxide]

putting a comma converts it to a tuple?

[minor ruin]

yep

[lavish charm]

How do you retrieve errors thrown from an SQL procedure via Python?

For example, an invalid path on SQL throws Cannot bulk load. The file "PATH" does not exist or you don't have file access rights. which is from ERROR_MESSAGE(), how do I get this message to show on a Python application? Seems trivial but barely any information on how to do such - can do an error log table but seems an overkill if there's something where I can simply read the error message

[shut jay]

Hi!! I am running:
username = urllib.parse.quote_plus('xxx')
password = urllib.parse.quote_plus('xxx') bot.connection_url = pymongo.MongoClient('mongodb://%s:[e-mail]/?retryWrites=true&w=majority:27017' % (username, password))

and I keep getting the same error: "ValueError: Port must be an integer between 0 and 65535: 27017" -

Any hint on how to solve it?

[fading patrol]

What driver/library are you using ? I would start with the documentation for that

[shut jay]

I am using pymongo.- I checked the docu and the error, and they are quite straightforward, yet... The erros says: ValueError: Port must be an integer between 0 and 65535: 27017' and I have this posrt on the string :S

[fading patrol]

You can't use URL variables like that, it's looking for a hostname and port number: https://pymongo.readthedocs.io/en/stable/api/pymongo/mongo_client.html

[nova forge]

I am getting this error no matter what I try : (2003, "Can't connect to MySQL server on 'localhost'") am using aiomysql

When I run it using python ide it works fine

But from within program it throws error

[nova forge]

Well I tried connecting mysql using terminal and suddenly it works

Idk why it works after connecting from terminal

[torn sphinx]

maybe u guys can help me, convert mediumblob to base64 so it can be seen json

#help-kiwi message

[wanton pagoda]

how do i overwrite all the text in json file

[exotic mirage]

Can't run pgadmin 4
Error: https://gist.github.com/AmazingAkai/222dc95b1bdf47f6816ebe22dc9c45a4

[shut bronze]

dayter bayse

[exotic mirage]

?

[jade wing]

did you see the response you got from @magic bay #help-burrito message on the subject?

[torn sphinx]

already fix my issue

@jagged oracle @short depot thanks btw, i'm just idiot forget to add .dedode() :V

[jagged oracle]

Nice !! Could you show us the result ? I'm curious about that

[torn sphinx]

here:

"foto": base64.b64encode(result[2]).decode()```

[jagged oracle]

I don't really understand where it belongs, could you show me the full source ?

[jade wing]

also remember that when using select * from ... you are dependent on the order of the columns not changing, for example that there will not be another column added in-between
for better future proofing is better to name the columns in the order you expect them to be and/or access the columns by name like with a python dict or similar

[torn sphinx]

u can see my full code here #help-kiwi message , and add that code inside return json.dumps({})

[torn sphinx]

thanks about that this is new things for me

[jagged oracle]

Ok so you get the objects directly from de DB and cast it in the python script ? that's easy

[jade wing]

just a friendly pointer for something that might be good to think about going forward

another one is to always use placeholders for values instead of f-strings
especially important if the variable content is in any way provided by an outside source such as a user
so, this:

cursor.execute(f"SELECT * FROM hewankusayang WHERE namapopuler = '{name}'")
```would then become (with the database connector/driver you're using, as the syntax can be a bit different with others):
```python
cursor.execute("SELECT * FROM hewankusayang WHERE namapopuler = %s", name)
```look ma, no f-strings 😉 
to protect you from possible sql injection attacks
and that's without addressing the former mentioned issue of using `*` in your `select` statements as i don't know the name of your columns
another positive of enumerating the column names in the `select` statement is that you only fetch the data that you want instead of all of the data of the matching rows, for larger tables this is more profound then for small amounts of data, but still good to grow good habits just like with the placeholders/bind variables

[jade wing]

the extra comma is only necessary when you only have one element in the tuple to make python understand that it really should be interpreted as a tuple, otherwise python reads the code as if the parentheses were not there at all

[brave bridge]

Another option is to use a list

There aren't many benefits in using a tuple, as long as the library is ok with a list

[jade wing]

yeah, but that is as you say dependent on what the library requires
it's unclear which library is in use here, but i think it looks like it could be sqlite
aiosqlite would probably be more appropriate in an async program like a discord bot or it's missing await's
i think the async with ... and async for ... pattern would also be nice there in that case

[brave bridge]

Another possibility people often miss is using keyword parameters (syntax depends on the library, but SQLite supports :foo for a dict like {"foo": "Some Thing"})

Makes it easier to read and change queries IMO. You don't have to remember what $4 means

[jade wing]

yeah, that's so very much better for the libraries that support such syntax, it's a pity that most/all libraries don't support it

i must say i prefer ? over %s or $1, but named parameters are arguably the best

[brave bridge]

Yep. One of my gripes with asyncpg is that it doesn't support them.

Although it's not much of its fault -- it uses PostgreSQL's native parameterization thingy

[paper flower]

There are 5 different formats for python dbapi 👀
https://peps.python.org/pep-0249/#paramstyle

[brave bridge]

Yep, and asyncpg uses none of them :)
Well, DBAPI doesn't cover async stuff, so that's generally fine

[warm ridge]

What is the purpose of an activation function?
A. To decide whether a neuron will fire or not
B. To increase the depth of a neural network
C. To create connectivity among hidden layers
D. To normalize the inputs

[brave bridge]

Sounds like a quiz question, also not related to databases.

[jade wing]

!rule 8 maybe?
and as @brave bridge said, very much not on topic for this channel
if you have more questions on the subject that is not quiz questions i think it might belong in #data-science-and-ml

[delicate field]

Rules

8. Do not help with ongoing exams. When helping with homework, help people learn how to do the assignment without doing it for them.

[ionic latch]

Someone really good with databases and programming, that can help me in dm? Dm me

[jade wing]

just ask your questions in the channel to get help here or you can opt for #❓｜how-to-get-help

[ionic latch]

When i ask here nobody answers @jade wing

[brave bridge]

People are more likely to answer if you ask a question

Very few people want to help privately as opposed to here

If you have a question that wasn't answered before, you can ask it again. Try to collect all the information (code, errors, what you've tried etc.) into one message so that it's easier to understand.

[jade wing]

and you might have better luck here then in the loaned help channels as they go dormant after a while of inactivity and people might not have time to see the question and answer within that time window

[brave bridge]

Depends. In a help channel, you have a window of just your question. Here, your question might be buried under other questions/discussions

[ionic latch]

yes thats the problem, i ask here and it goes too long without answers, so other ppl ask questions and they get answered but not mine

With help channels, i always got help

idk why database is not in a help channel or something

[brave bridge]

Well, if you have a question, you can ask it here. You don't always get help immediately.

This is also primarily a Python server. So if your question is mainly about databases, you might want to try a server dedicated to that, or another resource like StackOverflow (where you can nicely tag your question)

[mental pike]

Hey I'm trying to use SQL in Python.
I'll get an error if do the following.

def myFunktion():
cursor.execute(f"INSERT INTO table_name(guild_id) VALUES(982457239223)")

ERROR

-Ignoring exception in myFunktion
-Traceback (most recent call last):
-  File "C:\$project_file\venv\lib\site-packages\mysql\connector\connection_cext.py", line 535, in cmd_query
-    self._cmysql.query(query,
-_mysql_connector.MySQLInterfaceError: Commands out of sync; you can't run this command now

I've read that it is possible to reconnect the cursor to solve this problem.
So I've done this:

def myFunktion():
db.close()
db.connect()
cursor.execute(f"INSERT INTO table_name(guild_id) VALUES(982457239223)")
db.commit()

And it actually worked. But it feels like a bad workaround. Is there a better way to do this?
What causes this error ?

[paper flower]

Try using fresh cursor

[mental pike]

i need more explanation

[paper flower]

iirc cursors aren't reusable, i'm not aware of specifics but you want to use fresh cursor for each operation

[mental pike]

did i understand right. after using a cursor. i cant use it again ?

[paper flower]

I think so

Actually i might not be right, not sure what really causes this error

[brave bridge]

What is db?

[paper flower]

But there's a similar problem: https://stackoverflow.com/questions/11583083/python-mysql-commands-out-of-sync-you-cant-run-this-command-now

https://dev.mysql.com/doc/refman/8.0/en/commands-out-of-sync.html

[mental pike]

if i do something similar as this it'll will work fine.

cursor.execute("SELECT something")
test = cursor.fetchone()

print(test)

cursor.execute("SELECT something diferend")
test2 = cursor.fetchone()

print(test2
)

[mental pike]

mysql.connector.connect()

[shut jay]

Very sad answer - even removing the :27017 throws the same error

[fading patrol]

Your hostname is invalid. The / onwards with all the URL variables doesn't belong there

[simple grove]

@delicate frigate did you ever figure out your sql thing

[delicate frigate]

Yep, I did

Wait, lol how do u remember?

[simple grove]

i was looking back through my mentions to see who i had forgot to get back to lol

glad you figured it out

[torn sphinx]

Hi

[delicate frigate]

Ah, thanks!

[vernal ginkgo]

sam you asked this question in a different channel as well. careful plz

[vernal ginkgo]

fwiw if getting started working with databases, i would look up context management and use it... here's a not-so-old post: https://stackoverflow.com/questions/8067690/context-manager-for-pythons-mysqldb

on the connection side, too, it can be helpful to make sure you don't leave any connections open because exceptions will be handled by the context manager as well

with mysql.connector.connect() as db:
  cursor = db.cursor()
  cursor.execute('SELECT * FROM users LIMIT 1')
  ...

[wraith yoke]

Hello everyone

I'm using pymongo, I want to find how many logs the specific user has created today, so I want to combine these two queries:

"$expr": { 
            "$eq": [ { "$dayOfMonth": "$created_at" }, current_day ] 
    
    }

and this one

{"user_id": id_}

i use find method but queries seem to work separately but when I combine them they dont seem to work, how can I achieve it?

[clever grove]

Hello ! is it possible to simply remove the primary key column in a table(models) in django

[clever grove]

or is it possible that a primary key may be not unique

[jade wing]

on the database side of things primary keys always has a unique constrains as well as a not null constraint

[peak stag]

Truly more of a mysql question but ... I have a table with 12 columns of numbers and I want to create a virtual generated column that is just the standard deviation of those numbers, by row. Is there any way to do this using stdev() or do I have to build out the calculation manually

[jade wing]

you can: https://dev.mysql.com/doc/refman/8.0/en/create-table-generated-columns.html
there is even two types, virtual (for when there are more updates to rows then there are reads) and stored (for when there are more reads then updates)

[peak stag]

I know how to make virtual generated columns vs stored but my question was whether I could use the stdev function across columns within a row rather than the other way, or if I had to build out the equation myself

[paper flower]

Maybe you can feed a tuple into stddev or do something similar 🤔

[jade wing]

oh, now i understand, you would need to pivot/unpivot the columns to look like one column and multiple rows for each current row, i don't think there is such a function in mysql 🤔

[paper flower]

Couldn't you use a tuple here?

[jade wing]

i can't think of a syntax to do that for the stddev_pop() function which only allows for a column name to be specified

[paper flower]

Yeah, youi probably can't use aggregate functions on row level?

Wouldn't something similar to avg((1, 2, 3, 4)) work?

[torn sphinx]

@jade wing sorry for ping, can u help me :/

#help-dumpling message

for some reason i need to change library

already change twice

[torn sphinx]

@jade wing already fix my issue

just need to delete my json.dump

        return result

print(getSpeciesData('nama2'))```

[jade wing]

oh, you only wanted to data, not necessarily in json format?

[paper flower]

It is in json format

They're using DictCursorResult or something like that

[jade wing]

is it?
as json != dict

[torn sphinx]

it's already json format with the same output with this#help-dumpling message

[paper flower]

They removed json.dumps, i think they wanted a dict

[torn sphinx]

wait... what i want json format

{'namapopuler': 'nama2', 'namailmiah': 'ilmiah2', 'foto': None, 'taxonomy': 'tax', 'kingdom': 'king', 'genus': 'genus', 'class': 'class', 'ordo': 'orrdo', 'family': 'familyy', 'species': 'speci', 'deskripsi': 'desk', 'persebaran': 'pers', 'habitat': 'hab', 'iucn': 'iucn', 'tersedia': 5, 'rataumur': 1, 'ratapanjang': 2, 'ratalebar': 3, 'rataberat': 4}```

[torn sphinx]

this format a dict or json ?

[jade wing]

if you print a dict it looks a lot like json, but it's not really json if it's not serialized to a json string

[torn sphinx]

damn, what i want json format then :/

help 😦

[paper flower]

You need a string?

[torn sphinx]

no

json

like this

{"namapopuler": "nama2", "namailmiah": "ilmiah2", "foto": null, "taxonomy": "tax", "kingdom": "king", "genus": "genus", "class": "class", "ordo": "orrdo", "family": "familyy", "species": "speci", "deskripsi": "desk", "persebaran": "pers", "habitat": "hab", "iucn": "iucn", "tersedia": 5, "rataumur": 1, "ratapanjang": 2, "ratalebar": 3, "rataberat": 4}

[paper flower]

Well, python dictionaries are convertable to JSON, json itself is just a string

Languages usually represent it differently in memory (python users lists, dicts and primitive types)

[torn sphinx]

so how to do that :/

[paper flower]

json.dumps

to convert python object to a json string

[torn sphinx]

ah yeah damn

fckg idiot

@jade wing @paper flower thanks btw 😗

[jade wing]

so you solved it fully now with something like:

return json.dumps(result)
```?

[torn sphinx]

yappp

[jade wing]

if you don't want all columns from the table you can change the select to include the only the columns you want and also change the name of the key that the select returns instead of the real column name
your select would be changed from:

cursor.execute(f"SELECT * FROM hewankusayang WHERE namapopuler = %s", name)
```to (in this example i only include 7 columns and change the columns that is not in english to english and let the others be unchanged):
```python
cursor.execute("SELECT namapopuler popular_name, namailmiah scientific_name, foto photo, taxonomy, kingdom, genus, class FROM hewankusayang WHERE namapopuler = %s", name)
```which ever you go with you can still remove the `f` in front of the SQL string as your not using the f-string anyways

[ionic latch]

https://gyazo.com/8662af2fc93d78df31e9015b6d2b6dce

Im trying to solve this, but idk if im doing the right thing

def LargeSales(boundary):
      conn = sqlite3.connect('yeye')
      cursor = conn.cursor()
      SQL = '''SELECT ProductName, Quantity, LastName, FirstName, OrderNumber
               FROM Customer, [Order], OrderItem, Product
               WHERE Customer.Id = [Order].CustomerId
               AND [Order].Id = OrderItem.OrderId
               AND Product.Id = OrderItem.ProductId
               AND Supplier.Id = Product.SupplierId
               AND Quantity >= ?
               ORDER BY ProductName, LastName, FirstName, OrderNumber ASC'''
      cursor.execute(SQL, (boundary,))```

this is my code

[paper flower]

Why you don't use joins?

[ionic latch]

because we learned that its easlier to use where clause when joining multiple tables idk

[paper flower]

Ehm, i'm not sure if your rdbms would optimize this but correct way to do that is to use joins

[ionic latch]

Oh we learned them both, but our proffesor perfers to use WHERE caluse

[paper flower]

Hm, same query plan though 🤔

[ionic latch]

yes, but is it correct?

[jade wing]

i think most databases will have the same query plan for both, just two different ways to write it (syntax)

i also personally prefer it this way, but it's mostly down to personal taste

[ionic latch]

Yes, and for me idk i perfer the where

Less to write and does the same thing

[paper flower]

less? It's the same

i'd argue that using join is more clear since you have table itself and join condition in the same place/line

[ionic latch]

You have to write ON too u know

its less xD

[paper flower]

You have to write where

[ionic latch]

Dont you have to write INNER JOIN [table] ON [table]

thats more to write

[paper flower]

select * from table1
join table2 on table1.id = table2.fk

select * from table1, table2
where table1.id = table2.fk;

[jade wing]

i often use aliases to make the table names shorter and i would also would recommend to prepend the select fields with the table names or there alias just in case there are two columns with the same name in different tables

[ionic latch]

Yes, but i got a prefixed table

[ionic latch]

You see it's more to write with join xD

[jade wing]

i agree with @paper flower here, it's about the same amount of text to write, it's just two different ways to go about doing it

[paper flower]

It's one more character

so, no

[ionic latch]

I'm rlly lazy

but ye same shit

But that code was it correct?

would it do what it asked?

[paper flower]

Your screenshot isn't very readable

post it as text or make another one

[ionic latch]

Your function should do the following:
Retrieve the ProductName, Quantity ordered, customer LastName, FirstName, and OrderNumber for all products where an amount equal or greater to the boundary number of the product were ordered at once.

Sort your result in alphabetical order of productname, then customer lastnaame, firstname and finally ordernumber.

Return all the results in a python list where each row is loaded as a tuple into the list.```

[paper flower]

looks like it's correct, but i'm not totally sure

[jade wing]

i'm just saying you are not explicit about which table all of these columns are referring to:

SELECT ProductName, Quantity, LastName, FirstName, OrderNumber
...
AND Quantity >= ?
ORDER BY ProductName, LastName, FirstName, OrderNumber ASC'''

[paper flower]

ORDER BY ProductName, LastName, FirstName, OrderNumber ASC only OrderNumber has ASC sorting direction

if i'm not mistaken

ah, it's asc by default

[jade wing]

yeah, so ASC could be left out entirely just to keep it consistent

[ionic latch]

oh didnt know

[torn sphinx]

cool i'll use this

thanks 😄

[rancid grove]

base = data.cursor()

base.execute("""CREATE TABLE IF NOT EXISTS skolevarer (

             navn TEXT,
             pris REAL,
             antall INTEGER
            )""")


manyexecute = [("melk",23.4,10),("sjokolade melk",20.5,15),("Melke kake",30.9,50)]

base.executemany("INSERT INTO skolevarer VALUES (?,?,?)",manyexecute)
base.execute("DELETE FROM skolevarer")
base.executemany("INSERT INTO skolevarer VALUES (?,?,?)",manyexecute)
data.commit()
base.execute("SELECT rowid, * FROM skolevarer WHERE antall >=11")
print(base.fetchall())
base.execute("SELECT pris FROM skolevarer WHERE navn ='melk'")

I = base.execute

I("SELECT * FROM skolevarer WHERE navn LIKE '%melk'")

I("""UPDATE skolevarer SET navn = 'melke sjokolade' 
  WHERE pris = 30.9 """)

data.commit()

print(base.fetchall())

why does the output become empty like this '[]' when there is is a item in 'pris' that is == 30.9?

[brave bridge]

@rancid grove what did you expect to be printed at the end?

I don't think UPDATE returns any results on its own. You need a RETURNING clause, which is available in newer versions of SQLite

[rancid grove]

So what i wanted to be printed is "melke sjokolade"

i wanted to 'update' the "melke kake" to "melke sjokolade".

[rancid grove]

I just recently learnt about sql so it possible or convenient to tell the syntax of using it and how to use it?

[brave bridge]

Well, you specified the value yourself when updating - there's no information to receive back from the database. You could just print(["melke sjokolade"])

[rancid grove]

ohh

[brave bridge]

For example:

sqlite> CREATE TABLE foo (bar, baz);
sqlite> 
sqlite> INSERT INTO foo (bar, baz) VALUES
        ('a', '1'),
    ('b', '2'),
    ('c', '3'),
    ('d', '3'),
    ('e', '3'),
    ('f', '4');
sqlite> 
sqlite> UPDATE foo SET baz = 'not 3' WHERE baz = '3' RETURNING bar;
c
d
e
sqlite> 

[rancid grove]

Is that the full syntax? I think i did something wrong since it only prints ["melke sjokolade"]. The output i want is [melke sjokolade,30.9,50]

[mellow trench]

Can someone explain what a "pivot table" is? I don't really use Excel but I know SQL. Is it like a GROUP BY or something more specific?

If I need someone to do a GROUP BY and count(), can I advise them to use a pivot table in their spreadsheet software? Does that make sense?

[fading patrol]

Basically, yes

[rigid mica]

How would I create a database in Linux? I'd need to add user and password as well

I completely forget how to do this on Linux, since I use windows, but my raspberrypi has Linux. Is it like a bash command?

Postgresql btw

[mint dagger]

whats the practical difference between using a local db and a db server running on the same machine?

[fading patrol]

I'm guessing by "local db" you mean SQLite? It's not meant to handle lots of connections, especially concurrently

[mint dagger]

but surely only your backend application (or middleend if you consider the actual db to be the backend) will be interacting with the db for a webserver right?

so only 1 connection?

idk maybe im being stupid lol

[rigid mica]

I'm looking in the manual, but I can't create a role either

[fading patrol]

If you just created the user, you may need to restart

[rigid mica]

I'd just run 'createuser name' right?

Now it says permission denied, whilst createuser should have perms to create a database

[jade wing]

you probably want some kind of concurrency in your application, be it separate servers, VMs, containers, processes, threads or even asyncio to handle more then one client at a time
you might even have several different components accessing the same database server
if each of them has there own connection it will eventually be a strain on the database server
connection pools can help quite a bit in that area, but it still just scales to a certain point
but a database server will still scale much better then using something like sqlite

[long forge]

Hello, i havent used SQL before im looking to write some variables from a .py file to a SQL DB like for each individual user its for a discord bot i can show the code i have and what vars i want to be written.

[serene elk]

How can I create this using python for a new user

JSON

[paper flower]

What exactly you want to do?

[serene elk]

I want to create this kind of format of data on user interaction

for each user

and then

store it inside

then send it in a channel

@paper flower

[paper flower]

store it where?

[serene elk]

in json file

[digital coral]

Import json

Then json.dump?

@serene elk

[serene elk]

yes

[digital coral]

Yes

json.dump

[serene elk]

but how to make it in this format

[paper flower]

Just create a dict?

[digital coral]

Ye

[serene elk]

how to create it in python

I want it to create new for each user that interacted

[digital coral]

dict = {
"name": None
}

[serene elk]

dict = {
"name": None
}
with open("data.json", "w") as f:
            data = json.dump(dict, f)```

[digital coral]

Yes

[serene elk]

o ty

[digital coral]

Try if it work

[paper flower]

Also i wouldn't use json files for storing data

[serene elk]

SQL?

[paper flower]

Yep

[serene elk]

what's so bad about json?

[paper flower]

There are multiple problems

1. It's not scalable across multiple processes
2. It's harder to work with
3. Worse performance
4. Unreliable
5. Just lacks a lot of features you get when using rdbms

[serene elk]

ic

[jade wing]

just like @paper flower i would not recommend going with plane files like that and use a database instead
another thing i wanted to add was that you probably want to go with async io if your writing a bot so that it won't block while waiting on io

[serene elk]

SQLite?

aiosqlite

[paper flower]

You can start with sqlite

[serene elk]

ok

[jade wing]

yeah, sqlite works and aiosqlite as you suggested your self is a good choice for your bot for now to go async
you will still have some of the downsides of only having a local file that it stores the database in
but it should at least be much faster and more stable then going with json files
you can move to another database which runs as a network server later if you feel the need for it

[paper flower]

@jade wing I was kind of surprised that sqlite has foreign key constraints disabled by default 😅

[jade wing]

i think that fits in to the category where one should drop that backwards compatibility consideration by now for the common good

[torn sphinx]

this is my code that runs a query on my db can anyone see why the table says created but isnt created on the bd

this is the create table function

[jade wing]

i wounder how many people do something like conn.execute("PRAGMA foreign_keys = 1") when using sqlite 🤔

[paper flower]

Had to add than into on_connect sqlalchemy event

Was testing server side cascades with sqlalchemy and was wondering why i can add invalid data into db 😅

[atomic jewel]

hi

when to move column from dimension table to fact table?

[torn sphinx]

use

sqlite3

😅

[brave bridge]

Kinda cursed

Well, almost as cursed as dynamically typed SQL

[paper flower]

Look, no sql

create table some_table(
   id integer primary key,
   data jsonb
);

[brave bridge]

entity jsonb, attribute jsonb, value jsonb 😜

Please download my new thing called WhySQL

[paper flower]

why?

[brave bridge]

You will know in the next episode 🙂

(that's not a real thing btw)

[paper flower]

Also it's better to use json rather than eav

but not both 👀

[brave bridge]

It's not just JSON, they can be NULL as well

[paper flower]

it's a valid json too!

So you can have two different nulls!

[fringe sundial]

Guys i want to use mongodb in my discord bot but ke on Android i dint know how to

Will anyone help me pls

[paper flower]

Don't think you can run mongo on android

[bleak bough]

Does it make more sense to serialise a list of numbers or make individual entries, the list would need to relate a long number to a list of long numbers i.e. {123456789: [123456788, 123456787, 123456786]}

it wouldn't need to be searchable by the items in the list

a number - a list
123456789 - blob

or

primary key - a number - one list item
1 - 123456789 - 123456788

[storm mauve]

the traditional way would be to throw the list elements into another table (without a primary key), but if you're using just about anything other than SQLite you should have access to Array data types

[bleak bough]

using sqlite

[storm mauve]

tbh blob probably™️ ain't even that bad if you do not care about what is in that field while it's in the database

or you can try to update sqlite to 3.38+ and use a JSON column, but I wouldn't recommend that

[jade wing]

i would still normalize the data and use a one to many relationship between two tables to be able to accommodate future usage

[bleak bough]

i'll try one to many because i haven't done that before and should probably learn

[jade wing]

what kind of data is this, what does the numbers for the key and for the list of values represent?

[bleak bough]

the key would be a discord message id and the list of values would be discord ids of people who have reacted to it

so the message id would be unique but the discord ids would repeat

[paper flower]

Can't you get that info from discord? 🤔

[bleak bough]

by reacted i mean have pressed a component button

also it'd be the original message id but reacted over multiple servers with multiple messages

oh, one to many is just what i was describing in the second example, done that before

[jade wing]

what second example?

[bleak bough]

    id INTEGER PRIMARY KEY AUTOINCREMENT,
    message_id INTEGER,
    user_id INTEGER
)"""

[vague palm]

i just wanna clarify that mysql and sqlite3 have the same syntax? i mean there difference are but not too meaningful, right?

[storm mauve]

they are different dialects of SQL, so they share most things, but with a few notable differences.
MySQL and PostgreSQL both support many more things than SQLite does, but the basic usage is the same for most part

the first thing you might notice is having to replace sqlite's ? by something else in the queries used as execute(query, (params))

[vague palm]

noted on these, thanks!

[torn sphinx]

https://media.discordapp.net/attachments/268002637905920001/981632137704915024/unknown.png

Is this the right way of storing this data?

the age group table is just lesser columns. But data of each day bifracated based on age groups

[mental pike]

Hey I'm using PostgreSQL for my Discord-bot and I'm storing my token in this database.
I don't know if that's a good idea. bat this isn't the point. I've created a Stored Procedure (to call the SELECT) and trying to call it within my python code.

everything i try throws the same error.

Try 1

sqlSetup.cursor.callproc("getToken")

Try 2

sqlSetup.cursor.execute("CALL getToken()")

ERROR

-psycopg2.errors.UndefinedFunction: ERROR: Function gettoken() does not exist.
-LINE 1: SELECT * FROM getToken()
-                      ^
-HINT: No function matches the given name and argument types. You may need to add explicit type conversions.

I've locked up the Documentation and didn't found something helpful. In mySQL it is done like this

What did i done wrong?

[icy oyster]

Traceback (most recent call last):
  File "D:\Programming\3CTG ToolKit\Genie AT ByBit\Main\main.py", line 76, in genie
    cur.execute(f'''INSERT INTO genie (date, time, trade_id, symbol, timeframe, side, order_price, tp_min, tps, sl, rr, stake) VALUES ("{date}", "{time}", {trade_id}, "{symbol}", "{timeframe}", "{side.capitalize()}", {order_price}, {tp_min}, "{tps}", {sl}, {rr}, {stake_value})''')
sqlite3.ProgrammingError: SQLite objects created in a thread can only be used in that same thread. The object was created in thread id 132 and this is thread id 888.
``` ![think](https://cdn.discordapp.com/emojis/857337002109304833.webp?size=128 "think")

[dim lantern]

If i wanna delete my whole database, is it a bad idea to just right click on the db file and hit delete?

[brave bridge]

If you're using SQLite, then yes, that's a sure way to delete all of your database. You might also want to look at journal files:
https://stackoverflow.com/questions/26209091/what-is-the-journal-sqlite-database-in-android

[dim lantern]

Just to be clear, it’s ok if I do that?

And I’ll check it out, thanks

[brave bridge]

Yes, if you're absolutely sure you don't need that data

[dim lantern]

Ah ok got it

[brave bridge]

SQLite doesn't have any files with hidden settings or caches outside of your database file (and journals)

[dim lantern]

Perfect

[icy oyster]

fix error, are you by any chance here?

[fringe sundial]

@torn sphinx

Can you help with database?

So first i need to create organization in mongo?

[paper flower]

sqlite3.connect(..., check_same_thread=False) you can disable thread safety

[regal cedar]

in a one to many relationship i was able to perform one query using the lazy='joined' method on the parent class and mapped all the parent and child class. but i couldn't do it from child to parent. for example lets say we have person and pet person have pets attribute that is an array of pet and pet have owner attribute that refers to the person, since our lazy method is "joined" sqlalchemy will not perform another query when i access person.pets attribute and all the data is already loaded by one query . but if i query one specific pet and i tried to access the the owner (pet.owner) it will perform another query to fetch the person. so my question is it possible to fetch child rows at the same time fetch parent rows and map the togeather ??

[jade wing]

what @paper flower said
or don't use/share connections or cursors between threads, create new ones local to the thread instead (but that might not be an option if you are using lots of threads)

[torn sphinx]

hey im wondering if there's a shorter way to do what i want here, ```sql
SELECT time_series.series AS time, COUNT(table1.click_time) "amount"
FROM (
SELECT generate_series(CURRENT_TIMESTAMP - INTERVAL '1 day', CURRENT_TIMESTAMP, '1 minute') "series"
) time_series
INNER JOIN
table1
ON (
EXTRACT(MINUTE FROM table1.click_time) = EXTRACT(MINUTE FROM time_series.series)
AND EXTRACT(HOUR FROM table1.click_time) = EXTRACT(HOUR FROM time_series.series)
AND user_id=1
)
GROUP BY time_series.series

To summarize what this is, im counting the amount of rows for every minute over the span of a day, i'm wondering if there's a shorter way to achieve this, im using postgresql

[paper flower]

If you want to count timeseries grouped by minutes why not use groupby?

I don't think you need a subselect here 🤔

[torn sphinx]

because click_time which is the target isnt distinct, example of the data ```py
+----+--------------------+----------------------------------+
| no | user_id | click_time |
+----+--------------------+----------------------------------+
| 1 | 591135329117798400 | 2022-06-01 15:09:14.236443+00:00 |
| 2 | 591135329117798400 | 2022-06-01 15:09:15.024471+00:00 |
| 3 | 591135329117798400 | 2022-06-01 15:09:16.355177+00:00 |
| 4 | 766953372309127168 | 2022-06-01 15:09:19.502443+00:00 |
| 5 | 486148342108127253 | 2022-06-01 15:09:22.985385+00:00 |
| 6 | 522524473447153695 | 2022-06-01 15:09:23.305807+00:00 |
| 7 | 486148342108127253 | 2022-06-01 15:09:24.419323+00:00 |
| 8 | 522524473447153695 | 2022-06-01 15:09:24.674709+00:00 |
| 9 | 522524473447153695 | 2022-06-01 15:09:25.114092+00:00 |
| 10 | 522524473447153695 | 2022-06-01 15:09:25.473190+00:00 |
| 11 | 486148342108127253 | 2022-06-01 15:09:25.614653+00:00 |
| 12 | 486148342108127253 | 2022-06-01 15:09:26.590926+00:00 |
+----+--------------------+----------------------------------+

[paper flower]

Well, you should be able to work with it anyway, can i get it in csv format?

[torn sphinx]

ok lemma generate it

[paper flower]

select
    date_trunc('minute', click_time),
    count(no)
from some_table
group by date_trunc('minute', click_time);

[grim vault]

How about:

SELECT date_trunc('minute', click_time) as "time", count(*) as "amount"
  FROM table1
 WHERE user_id = 1
 GROUP BY 1;

[paper flower]

group by 1?

[grim vault]

index of the column in the select list

[paper flower]

I see, didn't know it work this way

I think would be a bit clearer to name it too

select
    date_trunc('minute', click_time) as click_minute,
    count(no)
from some_table
group by click_minute;

[grim vault]

You should be able to use the alias name for it.

[torn sphinx]

I see, wasnt aware of date_trunc tbh thanks

[paper flower]

me neither, just saw it on stackoverflow and looked it up in postgres documentation 🙂

[grim vault]

I just took a look which date/time functions postgresql has to offer: https://www.postgresql.org/docs/current/functions-datetime.html

[paper flower]

Guess it would be a bit more tricky to do if you want to group by by larger intervals (e.g. 5, 10 minutes)

[grim vault]

see: 9.9.3. date_bin

[paper flower]

Wow, that could be quite handy

[torn sphinx]

Hi, I have the following table can give image if it helps to easier see:

+----+--------+---------------------+---------------------+
| id | bus_id | arrived_at          | departed_at         |
+----+--------+---------------------+---------------------+
| 1  | 7      | 2022-01-01 03:00:00 | 2022-01-01 04:00:00 |
+----+--------+---------------------+---------------------+
| 2  | 7      | 2022-01-01 05:00:00 | 2022-01-01 06:00:00 |
+----+--------+---------------------+---------------------+
| 3  | 7      | 2022-01-01 05:00:00 |                     |
+----+--------+---------------------+---------------------+
| 4  | 12     | 2022-01-01 07:00:00 | 2022-01-01 08:00:00 |
+----+--------+---------------------+---------------------+
| 5  | 12     | 2022-01-01 08:00:00 | 2022-01-01 09:00:00 |
+----+--------+---------------------+---------------------+

It is store the date of when a bus departs and arrives the station.
I want to write a query to see currently which bus are currently departed and not at the station. To know if a bus is departed the departed_at field needs to be filled.

In the example above i want only bus_id 12 to be returned by the query, because although the departed_at field of bus 7 is filled it has one row where it is not filled.

Ive tried quite a few things but can't figure it out

[paper flower]

I'd say it 's possible to get max(arrived_at) and max(departed_at) and see which is higher, grouped by bus_id

I think there's a another way though

[torn sphinx]

also dont have access to window functions

[paper flower]

I think these are aggregate functions, they should work?

[torn sphinx]

its for a package, so cant assume which version client will use

[paper flower]

so can you just use min and max here?

Or you can also select latest row for each bus and see if it has departure time too

[torn sphinx]

not sure what you mean

[grim vault]

You can use a subselect for a not in list with bus ids which do have a null value in the departed column.

select distinct bus_id
  from table
 where bus_id not in (select bus_id from table where departed_at is null);

[paper flower]

I wonder if using max and min would be faster here 🤔

[grim vault]

Maybe an exists, don't think you can do anything with min/max here.

[paper flower]

sorry, actually just max

[paper flower]

It's possible to select something like max(departed_at) > max(arrived_at) to determine if bus is in transit

[plush river]

Suppose i have a health card where each country’s card has differing fields (like name, age) however the visible column boolean value for a field is determined by a specific health card i.e Japan Health Card 2006. How should i design the database? i thought of having a table for HealthCard (containing name such as “Japan health card 2006”, field, visible) and a table for CountryHealthCatd (containing countryName, field)

[torn sphinx]

https://tenor.com/view/yes-sir-yes-boss-military-salute-giving-honor-rip-gif-23161689

cheers boss

Hello I have a db connection string within a config .yml file and I want to be able to easily switch among different databases by simply changing the name of the database I'm connecting to

[proven arrow]

Do you mean switching to different databases like mysql to Postgres?

[torn sphinx]

Switching from different databases in the same instance, for example there is a development and production database

I feel like if I use a environment variable I will force my coworkers to connect to the same database that I'm connected to since we are developing in the same server

[proven arrow]

I don’t see how. I would add db credentials to env over a yaml file

[proven arrow]

For local development do they not run the database on their local machine ?

[torn sphinx]

db:
  postgresql.url: postgresql+psycopg2://user:pass@host:port/flexible_name

[torn sphinx]

We all interact with the same database

Cause we connect remotely to a linux server that has an instance of postgresql running with the databases

The thing is, if I set an environment variable: export db_name=whatever we would have to connect to the same database

db:
  postgresql.url: postgresql+psycopg2://user:pass@host:port/${db_name}

[vague palm]

I would like to know why setting taskid to AUTO_INCREMENT doesnt continue on the highest taskid?

the task table used to have 9 tuples, and i deleted some of them to test a program im making

but when i insert a new tuple, it increments the last highest taskid

[proven arrow]

@vague palm that’s how it is supposed to work. As the name suggests it “increments” the last ID and does not reuse ids previously generated for reasons that can be potentially harmful to your application.

[vague palm]

so its better to let it stay this way? i mean to continue with taskid 10, instead of taskid 4

[proven arrow]

Always. What do you get from filling the gaps anyways?

[vague palm]

yea ure right whats the point anyway 😆 noted on these, thanks!

[north locust]

im trying to use google cloud sql database. can someone help me understand what part of these creds is what i use in my scrips?

[north locust]

well @fathom star i got the database setup on google cloud

now im just stuck on whih details i use

[torn sphinx]

So orcale created mysql which is a relational only database software right? Does oracle make software for non-relational databases?

[jade wing]

Oracle didn't create MySQL, they bought Sun Microsystems (and also got Java from them) that had previously bought MySQL (the company) that made MySQL (the database) many years after they had created the product
Oracle has many database products, their own database that is often just referred to as "Oracle" is the most famous, they also have non-relational and nosql databases in their portfolia
one of the older ones are Berkeley DB from another company named Sleepycat that Oracle also bought

[torn sphinx]

So does mysql the company create any non-relational database products?

Just trying to figure out how to create non-relational databases like I create relational ones with mysql

[jade wing]

the original company behind mysql only had that database product if i remember it right and other products related to that database
but oracle got many different database products you can try, and then there is many other companies that makes databases too

[torn sphinx]

Okay, thanks for clearing up that confusion for me!

[jade wing]

what exactly are you looking for?

[torn sphinx]

Well I read that its bad practice to store, for example, lists that vary in size in table columns, so I figure I will need to link that column to a non relational database somehow.

Not that It would matter much at this scale, but I want to learn best practices.

[jade wing]

that is a problem that you generally solve with normalization in a relational database, a "one to many" relationship for example

you also have "many to many" relationships that you often solve with a separate table between the two tables that needs that kind of data model

[torn sphinx]

I'll have to do some more research on all of this, thanks for the help!

[jade wing]

do you have an example in mind that you would like to model in this way?

[torn sphinx]

One table with information about Projects, with one column being keywords correlating to that project that will be stored in a list. Now that I'm thinking about it, I could just create another table with a keywords column, and a column for the project name, and just select all the keywords that correlate with the project I select.

[jade wing]

yeah, that is exactly what a "one to many" relationship using two tables and foreign keys are all about

[jade wing]

you are talking about using natural keys, it's more common to do data normalization and use surrogate keys instead, like a unique numeric id (for each project in your case) for entries in the table you are referring to

[torn sphinx]

Hi. Is without rowid any useful at the end of create table statement?

[jade wing]

which database, sqlite?

[torn sphinx]

Yep

Don't know what it does

I have primary keys already

Google says that if it's there. You must have a primary key. That's all

[brave bridge]

@torn sphinx Have you read this? https://www.sqlite.org/withoutrowid.html

[jade wing]

i was just about to paste the same url as @brave bridge just did, it has all the information that you should need on the subject and more

[torn sphinx]

Yep. That's what I read

But I read it very fast

Oh got it

Cool

It was simple

[jade wing]

basically, if you have a primary key already you might not need the rowid and you can prevent it from being created by using that command

[torn sphinx]

I need to be more patient while reading

[brave bridge]

is there any point in having a rowid if the table has a primary key?

oh huh

AUTOINCREMENT does not work on WITHOUT ROWID table

[jade wing]

not much as far as i understand, but there is this: https://sqlite.org/autoinc.html

[torn sphinx]

Rowid is just unnecessary. I define my primary keys myself

[jade wing]

yeah, so if you need that you should not omit it when using sqlite, it's part of their legacy design

[jade wing]

as long as you're fine with not being able to use autoincrement for your own primary key

[torn sphinx]

What's that

I don't know what it is. My primary keys are like country code

And stuff

[jade wing]

and there is quite a few more restrictions on a table in sqlite when omitting the rowid, the page @brave bridge linked lists them all

[torn sphinx]

Thanks. Will read on it later.

[torn sphinx]

So I'd want to do this then right? sql CREATE TABLE ProjectKeywords (ProjectID int KEY REFERENCES Projects(ProjectID), keyword TINYTEXT NOT NULL);

[jade wing]

unfortunately i don't think that syntax is supported by mysql
you need to specify the foreign key in a different way with mysql
here is an example (just press "Run" at the top): https://www.db-fiddle.com/f/d83nUXcgQ84GKY3T5FMqRG/0

[torn sphinx]

Woah your example just blew my mind, had no idea you could do all of that with mysql. @jade wing

@torn sphinx

Can you please share the link for the codecademy server

[torn sphinx]

the bots don't allow it usually
but it's just Codecademy when you search for new servers

You can dm me

How to search servers on discord?

[brave bridge]

Does asyncpg automatically start a transaction on a connection automatically? This seems to suggest that it doesn't:

async with pool.acquire() as conn:
    await conn.execute("INSERT INTO foo(bar) VALUES ('abc'), ('def');")
    print(conn.is_in_transaction())

So if I don't explicitly call transaction(), it provides no isolation, right?

[torn sphinx]

https://www.lifewire.com/how-to-find-discord-servers-5092039

Looks like this feature is not on phone

[jade wing]

i couldn't get it to work properly on that site with the syntax you suggested and didn't find support for that exact syntax in the mysql manual pages either
you are free play around with the example on that site and change it as much as you want or try out new things in there
that example barely scratches the surface of what one can do with sql and relational databases, you can do so much more with them than that
what i was doing is called a join between the two tables that i specified in the FROM clause and was also using aliases to shorten the name when i refer to them in the rest of the query
you can use aliases for column names or other expressions as well to change how the name of the column appears in the results
you can also easily do operations like counting, summing up values and such as well as grouping similar data together just to mention some of the most basic functions

[torn sphinx]

I created a database and saved it in a folder. Then I added some tables in it. Do they get automatically saved into the db?

Whatever changes I make while it is running in my sqlite studio

like I added these tables and populated them. Are they in my vaccinations.db file too?

[north locust]

I have my database created on google cloud and I need help configuring the connectivity. since im getting this error

disnake.ext.commands.errors.ExtensionFailed: Extension 'cogs.events' raised an error: OperationalError: connection to server at "34.68.242.84", port 5432 failed: Connection timed out (0x0000274C/10060)
    Is the server running on that host and accepting TCP/IP connections?```

[north locust]

If i could have some assistance connectign to a postgres sql server off of google cloud please let me know. if you have code that will help let me know. also if anyone knows how to fix connections to google cloud databases let me know

[fringe sundial]

@torn sphinx

How to do the ip address thing?

Can you help me?

[torn sphinx]

never used mongodb, so can't say
though it needs the IP address of IP addresses you want to allow to make connections

[fringe sundial]

So what to use them?

[jade wing]

from where are you connecting from, is it from your computer you are using right now?
if it is you can go to https://api.ipify.org/ to get your public ip address that you currently have and copy that ip address än the paste it in to the web form from your screenshot together with a description of what that is (like "home" and maybe the current date)
also know that your public ip address probably will change from time to time, so you might need to do this now and then when it has changed an things stop working, then you can probably remove old entries that belongs to the same location as well

[fringe sundial]

Me on mobile

@jade wing should i use sqlite then?

But idk how to download it

[jade wing]

but your not running your code that connects to the database from you mobile, right?

[fringe sundial]

Me will create database first then code

[fringe sundial]

Is sqlite good or mongodb?

[jade wing]

they are very different

[fringe sundial]

Which one should i use

[fringe sundial]

So i am kn mobile
Which database should i use?

[jade wing]

sqlite uses sql and runs from your code on the same device and typically stores the database in a file and doesn't need a server process to connect to

mongo is a nosql and primarily a document database that typically runs as a server process somewhere on a server

[fringe sundial]

SQL is stored on my mobile?

[jade wing]

are you saying you are running python code on your mobile?
I find that kind of hard to believe

[fringe sundial]

Me use replit ;-;

I need database for my discord bot

Just for prefix

[jade wing]

aha, then it's not running on your own device, it runs in the cloud at replit

[fringe sundial]

Me have no database yet

Can you tell what database should i use?

[jaunty cove]

hi

im not sure how to iterate just the keys in a dict in a jinja2 file

<tr>
    <!-- Iterate through each key in the first entry to get the
    column name -->
    {% for key in bsg_people[0].items() %}

    <!-- Create a <th> tag with the key inside of it, this will be
    our header row -->
    <th>{{ key }}</th>

    <!-- End of this for loop -->
    {% endfor %}
</tr>

bsg_people[0].items() throws an error

[jade wing]

that is up to you, you can use either, and there is even more to choose from

but if it's a discord bot you are building I would go with a async library for the database so the bot will not freeze up when then it's waiting for responses from the database

it might be easiest for you to start with the aiosqlite library which uses sqlite

[jaunty cove]

this is how i pass it in

@app.route('/bsg-people')
def bsg_people():
    cursor = db_connection.cursor()
    query = 'SELECT * FROM bsg_people;'
    cursor.execute(query)
    results = cursor.fetchall()

    return render_template("bsg.j2", bsg_people=results)

[uneven sinew]

Considering that you don't know how to use APIs yet, I'd recommend starting with aiosqlite which uses sqlite

[jade wing]

and you are sure you have your database connection setup to return a dictionaries and not just tuples?

[jaunty cove]

yea

wait

hm

heres the db connection set up

def connect_to_database(host,user,passwd,db):
    '''
    connects to a database and returns a database objects
    '''
    db_conn = MySQLdb.connect(host,user,passwd,db)
    return db_conn

db_connection = connect_to_database(host,user,passwd,db)
cursor = db_connection.cursor()
cursor.execute("SET SESSION wait_timeout=31536000")
cursor.execute("SET SESSION interactive_timeout=31536000")
cursor.execute("SET FOREIGN_KEY_CHECKS = 0")
cursor.connection.autocommit(True)



# just in case we have CORS issues
app = Flask(__name__)

# replace with your own login credentials file
db_connection = connect_to_database(host,user,passwd,db)
cursor = db_connection.cursor()

how would i set it up to return dictionaries

[jade wing]

change your line:

cursor = db_connection.cursor()
```to:
```python
cursor = db_connection.cursor(MySQLdb.cursors.DictCursor)
```any particular reason why you are using the MySQLdb library to connect to mysql?
and do you really want to do `SET FOREIGN_KEY_CHECKS = 0`?

[fringe sundial]

@jade wing

Can you guide me pls

Cause there no tutorial for mobile

So what to do first for database

Me will use replit as ide

[jade wing]

don't think of it as mobile as you are not running the code on the mobile device
your mobile device is just what you use to interact with replit
instead search for tutorials for discord bots on replit that use aiosqlite

[fringe sundial]

https://replit.com/talk/learn/How-to-create-an-SQLite3-database-in-Python-3/15755
I found this there no other tuto
Me not understanding what to do

[jade wing]

i have never used replit my self and i don't know if replit still has problems running sqlite and aiosqlite, but they have had issues with that before

[torn sphinx]

Sir

[fast current]

Are there any MongoDB users here? Of the examples I have seen using MDB with Python, most of the operations that are performed against the database using dictionaries, list and string literals. I'm wondering if it's common to do this in production or if data is passed in and out of the database using classes to map the data in a stricter way. Or perhaps using dataclasses with .as_dict() is a common pattern? What about ODMs like mongoengine?

[paper flower]

Something like https://art049.github.io/odmantic/
uses pydantic to map mongodb entities

[fast current]

@paper flower what is the de-facto standard ODM for Python when using MongoDB?

[paper flower]

I don't know

[grizzled wadi]

https://github.com/RobertCraigie/prisma-client-py supports MongoDB, although it is an ORM and full disclosure I am the maintainer

It is also not the de-facto standard in Python but its counterpart is very popular in TypeScript

[sly pivot]

Anyone that knows how to normalise tables

[torn sphinx]

Import "mysql" could not be resolved can someone help?

[jade wing]

it's:

import mysql.connector
```and you need to install the mysql library in your python envrionment for it to work

[torn sphinx]

can someone explain what is Dictionary=True for mysql

[fast current]

@grizzled wadi @paper flower thanks for the input

[jade wing]

that's the subject of quite a few books out there, so it's a huge subject and you probably need to be more specific
there are many levels of normalization and you don't need to strictly stick to one of the specific levels
you can probably find a more gentle introduction than this, but at least here is some keyword on the subject: https://en.wikipedia.org/wiki/Database_normalization

[jade wing]

that gives you a dictionary data structure (so that you can access each column by name in python) for each row in the database when fetching data instead of a tuples (something that looks like a list and where you access each column by index in your python code)

[grizzled wadi]

Happy to help :)

[vapid lagoon]

def ViewAllData(self):
db = sqlite3.connect('UsersDatabase.db')
cur = db.cursor()
UserInformations = cur.execute('SELECT * FROM users_info')

    for row_number, row_data in enumerate(UserInformations):

        self.tableWidget.insertRow(row_number)
        self.tableWidget.setColumnCount(3)
        for column_number, column_data in enumerate(row_data):

            item = str(column_data)

            if column_number == 0: 
                item = self.GetImageLabel(column_data)
                self.tableWidget.setItem(row_number,column_number, QTableWidgetItem(item.))

            else:
                self.tableWidget.setItem(row_number,column_number, QTableWidgetItem(str(item)))
            self.tableWidget.verticalHeader().setDefaultSectionSize(80)

def GetImageLabel(self, image):
    ImageLabel = QtWidgets.QLabel(self.centralwidget)
    ImageLabel.setText('')
    ImageLabel.setScaledContents(True)
    pq = QtGui.QPixmap()
    pq.loadFromData(image,'jpg')
    ImageLabel.setPixmap(pq)
    return ImageLabel

I want to show image in tablewidget but an error occured and it says self.tableWidget.setItem(row_number,column_number, QTableWidgetItem(item))
TypeError: arguments did not match any overloaded call:
QTableWidgetItem(type: int = QTableWidgetItem.Type): argument 1 has unexpected type 'QLabel'
QTableWidgetItem(str, type: int = QTableWidgetItem.Type): argument 1 has unexpected type 'QLabel'
QTableWidgetItem(QIcon, str, type: int = QTableWidgetItem.Type): argument 1 has unexpected type 'QLabel'
QTableWidgetItem(QTableWidgetItem): argument 1 has unexpected type 'QLabel'

[opaque wave]

hey guys I recently started working with sql smss and I run into a problem. I have a query that looks like this:

WITH CTE AS (SELECT ASDF, RN=ROW_NUMER()OVER... 
            FROM TABLE2 
            WHERE ASDF)

SELECT TABLE1.ASDF
        ....
        CTE.ASDF
FROM TABLE1
LEFT JOIN(
        SELECT ASDF SUMS
        FROM TABLE2 WHERE ASDF GROUP BY ASDF) AS ASDF ON TABLE1

LEFT JOIN CTE ON TABLE1 ASDF = CTE ASDF

WHERE TABLE1 ASDF... AND RN=1
ORDER BY TABLE ASDF

in short I get some sums from table 2 to join with table 1 and the first row of CTE to join with table1. However, the RN=1 causes the query to return less rows than it should (if i remove the CTE I get 2500 rows, with the CTE's RN=1 I get 1900). How do I fix this?

[jade wing]

you might have better luck in #user-interfaces as this looks to be more about the gui then the database part of the code

[paper flower]

I'd expect it to return less rows, you're partitioning over something and selecting only first rows from these partitions
Also which RDBMS are you using? If it's postgres then it could be somewhat shorter

[opaque wave]

hey thanks for replying but it's late for me now. Could i pm you tomorrow?

[paper flower]

Sure

It's quite late for me too

Also maybe better ping me there so other people can help too

[torn sphinx]

Ok so I familiar with both MySQL and PostgreSQL..
But I want to know.. which one of them consume less resources?

Pls ping me when you reply

[jaunty cove]

didnt see this in time but i ended up figuring that out heh

im not sure why its throwing an error here, it giving a key error for 'artist_fname'

here is my flask code

@bopify.route('/artists')
def artists():
    print("Fetching and rendering artists web page")
    db_connection = connect_to_database(host,user,passwd,db)
    query = "SELECT artist_fname, artist_lname from Artists;"
    result = execute_query(db_connection, query).fetchall()
    print(result)


    return render_template('artists.html', rows=result)


@bopify.route('/artists_functionality/', methods=['GET', 'POST'])
def artists_functionality():
    
    db_connection = connect_to_database(host,user,passwd,db)

    artistsquery = "SELECT artist_fname, artist_lname, artist_ID from Artists;"
    artistsresult = execute_query(db_connection, artistsquery).fetchall()
    print(artistsresult)


    if "Submit" in request.form:
        print("Add new Artists!")
        artist_fname = request.form['artist_fname']
        artist_lname = request.form['artist_lname']

        insertquery = 'INSERT INTO Artists (artist_fname, artist_lname) VALUES (%s,%s,%s,%s)'
        insertdata = [artist_fname, artist_lname]

and my html form

<h3>Add New Artists:</h3>
<form id="addartist" action="{{ url_for('artists_functionality') }}" method="POST">
    First name: <input type="text" name="artist_fname"><br>
    Last name: <input type="text" name="artist_lname"><br>
    <input type="submit" name="Submit" value="Add">
    <!--
    <input type="submit" name="Update" value="Update">
    -->
</form>
<br/>

not quite sure where im going wrong

[torn sphinx]

Anyone?

[keen minnow]

it depends on the use case and specific needs and config.
There isn't a better answer than "try and see" here

[torn sphinx]

i'll be using it for discord bot

and just the basic config... like.. i don't have any additional config requirement or stuff

[keen minnow]

then, to be honest, it doesn't really matter. Unless your user base is in the hundredS of millions of people, you won't see a difference

[torn sphinx]

OwO i see

hmm alright

[keen minnow]

to that end, I would have a slight preference towards postgres since it has more features out of the box

but that's more of an opinion than anything

[torn sphinx]

ah even i'm biased towards postgres

and thanks for help tho

[foggy creek]

hey guys!

i need help

my sqlite DB has several columns with that are suppose to contain floats, but because of the data source, it ended up containing strings

This is happened because of the 'minus' sign '-' data is written with 'n hyphen' '–'

Can you help me to figure out how to use either SQL, python or Pandas to go over the specific columns and replace 'n hyphen' with a minus sign?

[keen minnow]

you may want to google about cast and sql and float

[foggy creek]

thank you, will do

[opaque wave]

hey @paper flower, to give you more insight:
I have table1 with 2600 rows and I want to get some deposit sums from 6 different tables (i.e. per user) and the first deposit for each user from the 6 tables which I thought to do like the above pseudocode I pasted.

When I join the sums from table2 to table1, I get back the 2600 rows and NULL for every row that does not have a match with table2. When I join the CTE to get the 'first deposit' my results return 1900 rows - that is because only 1900 of the 2600 users had a first deposit in table2. As you said, I partition by user but I would expect to return NULL for every user not having a deposit instead of limiting the results. Now I'm away from my work pc but maybe theoretically I can select a column with CTE where every user has a value and maybe that can fix this issue?

I have no idea what RDBMS they use, I recently starting working there and I'm not very experienced with sql stuff

also interested if you think there is a better way to approach this

[paper flower]

Can't you create an outer join table to cte so you get 2600 rows but 700? of them would contain nulls

[opaque wave]

i thought i tried that but most likely did it wrong?

will try and let you know

[paper flower]

You're using left join, you could try left outer join i think?

[opaque wave]

but overall what do you think of the approach? is there a better way?

[paper flower]

I'm not really sure, i just woke up 😅

[opaque wave]

i tried left outer join but it does not return the null rows

[paper flower]

oh, wait, just left join should work? 🤔

Maybe that's actually because of your RN = 1 condition?

[opaque wave]

that's what i thought initially but I'm very new to sql. I hypothesized that I need to put the rn=1 clause somewhere else

[paper flower]

You can add RN = 1 condition to join condition i think

e.g. join YOUR_CTE on TABLE1 ASDF = YOUR_CTE ASDF and YOUR_CTE.RN = 1

[opaque wave]

i tried something there but couldn't make it work

let me try your suggestion

nice

it seems that was it

[lapis forge]

any teacher here?
have time for me and interest in guiding me?

any brother?

[proven wagon]

What database would you choose for a (relatively) large table of somewhere between 100m-1b entries

[jade wing]

depends on the nature/type of the data for each entry and a lot of other factors as well as your other requirements you might have on the database

[proven wagon]

It's different types of financial updates in our system

Will give some examples:

Example one is simple updates of a users balance

ExampleOne:

    id = Serial
    before = Int
    after = Int
    user_id = Int

Example two, amount sent from user one to user two

id = Serial
from_user = Int
to_user = Int
amount = Int

For now we've considered timescale as it fits right in with our postgres db, but we'd have to move away from our managed db to managing it ourself

Also considering influxdb

[proven wagon]

oh, and bonus points if we can make bulk inserts

[jade wing]

there is always Oracle, but that's darn expensive and has a very annoying licensing model 😞

i know our DBA team use partioning on tables to overcome performance bottlenecks for huge tables

[pure mortar]

"data lakes: write once, read never"

ahhh im dead

💀

[mint mauve]

how can I create a relationship using sqlite3 in python? (one to many)

[fading patrol]

I Googled it for you: https://stackoverflow.com/questions/7541595/how-to-create-one-to-many-in-sqlite3

[chrome mango]

hello, im using aiomysql and im slightly confused on why one of my commands doesnt seem to be doing anything.
my code is:

        async with pool.acquire() as conn:
            async with conn.cursor() as cur:
                # await cur.execute(create_table_query)
                await cur.execute('CREATE TABLE IF NOT EXISTS guild_settings (id BIGINT, member_count INT, apod_channel BIGINT)')
                for guild in self.bot.guilds:
                    print(f"[DB] [Guild settings] Checking for settings in `{guild.id}`...")
                    try:
                        await cur.execute(f"INSERT INTO guild_settings (id, member_count) VALUES ({guild.id}, {guild.member_count})")
                    except Exception as e:
                        print(f"Error with {guild.id}! \n{e}")

        conn.close()
        pool.close()
        await pool.wait_closed()
        print("[DB] Connection closed.")

There are no errors relating to that line, but my database remains empty. ive confirmed with a manual command that my data is vaild. any ideas?

this is my console (im also not sure why its showing a warning for table exists, when my statement says CREATE IF NOT EXISTS, but i dont think its causing any issues)

[paper flower]

Did you forget to commit?

[chrome mango]

thats always possible, but i wasnt aware that i had to with aiomysql

[paper flower]

You should with most dbapi implementations

It commits current transaction

[chrome mango]

the create table works without a commit

[paper flower]

Ehm i don't think so

Try removing your table right now 🤔

[chrome mango]

okay

dropped 👍

ill test

[paper flower]

Check if it creates table right now

I think it shouldn't

[chrome mango]

yup, it does

[paper flower]

Ok, that's kind of weird

https://dev.mysql.com/doc/refman/8.0/en/implicit-commit.html

It commits implicitly