databases

[lone island]

well, since i'm connected to database using pool, set_type_codec cant be used on it 😩

[unkempt nest]

you guys know of a way to ping a mysql database?

basically i keep getting mysql.connector.errors.OperationalError: 2013 (HY000): Lost connection to MySQL server during query and i checked the timeout, it's set to 100 sec. What is the best command to ping a mysql server?

[brazen charm]

pass a coroutine to connect_pool with the init kwarg

async def init_connection(conn):
    await conn.set_type_codec(...)

await create_pool(..., init=init_connection)```

[thorn gull]

I need help, im confused about where to start with databases and how to start and do them, some assistance would be appreciated

[austere portal]

Take a look at the built in sqlite3 module

[thorn gull]

ok ill search that up

[harsh pulsar]

https://sqlbolt.com/

[thorn gull]

thanks:)

whenever i attempt to make a database, line 10 always has an error? Is that common?

[harsh pulsar]

what is line 10?

show your code and the error

[austere portal]

That is not common

[lone island]

actually i simply converted the fetch str return to dict by eval() function

[brazen charm]

Dont do that

That is one of the most insecure way of doing that

[lone island]

why is it

i mean there is no much difference than if it just returned it as a dict

[brazen charm]

because it runs it as if it's python code

meaning If you save some user input,

i can theoretically inject my own code into your server when you eval() it

[lone island]

userdata = await bot.db.fetchval('SELECT items FROM gametb WHERE user_id = $1', userid)
userpick = eval(userdata["pickaxe"])
# after i do the editings on userdata 
await bot.db.execute('UPDATE gametb SET items = $1 WHERE user_id = $2', userdata, userid)```
@brazen charm even if the way i write it makes no difference with no eval ?

[brazen charm]

makes no diffrence

dont eval arbitrary data

should never be used in production

[brazen charm]

also this is way easier to us

[lone island]

well okay if ur saying then

[lone island]

also could you help me on passing the coroutine please

[brazen charm]

you're passing init to to the wrong function

needs to be in the create_pool function

[lone island]

oh

yes

[lone island]

uhm

im still getting the pool error

[faint blade]

As a matter of fact, eval() is sometimes never even included in Python was memory and space uses afaik.

Surely there's a better way to do this?

[lone island]

yeah there is a better way as @brazen charm says but im still getting some errors which i hope he can still help me with

[brazen charm]

code?

[lone island]

async def init_connection(db):
    await db.set_type_codec(
        'json',
        encoder=json.dumps,
        decoder=json.loads,
        schema='pg_catalog'
    )```

[brazen charm]

whats the full code

[lone island]

async def init_connection(db):
    await db.set_type_codec(
        'json',
        encoder=json.dumps,
        decoder=json.loads,
        schema='pg_catalog'
    )
bot.db = bot.loop.run_until_complete(asyncpg.create_pool(user='',
                                                         password='',
                                                         database='',
                                                         host='',
                                                         init=init_connection))```

[brazen charm]

hmmm

thats, weird

can you add print(type(db)) just before the set_type_codec

[lone island]

everything is working fine
||i forgot to remove the first set type codec code from the on_Ready event|| 😨
i really appreciate the help sorry for the time tho <333

[brazen charm]

ah

nice

[elfin girder]

do API questions go in this chat channel?

[mortal light]

For psycopg2, is there are limit to how big a concatenated value string can be?

I did this with 5k rows and 4 columns and it worked fine, but seems like it would have a limit.

        query = f"""
                    INSERT INTO {self.table_name}
                    (column1, column2, column3, column4)
                    VALUES {multiple_rows_values_string}
                """

And multiple_rows_values_string looks like:
('column1 foo1', 'column2 foo1', 'column3 foo1', 'column4 foo1'), ('column1 foo2', 'column2 foo2', 'column3 foo3', 'column4 foo4')

[brazen charm]

dont do that

well

dont format queries like that

massive injection vulnerability

[brave bridge]

ye don't do that

there's executemany for that

[lost nebula]

How to use the MariaDB?
For Discord Bot

I already installed the Database

need to connect it to the bot

[mortal light]

I read executemany was slow, so I thought to do this instead.

I shouldnt format queries like that, because of indentation, f-string, new lines, and multiple values string?

[harsh pulsar]

https://mariadb.com/resources/blog/how-to-connect-python-programs-to-mariadb/

[brazen charm]

it is slow in psycopg2, a for loop of execute's are faster in psycopg2 (not in other drivers though)

but all queries support placer holders, string formatting makes you liable to sql injections

[harsh pulsar]

That's so weird, why is that?

[brave bridge]

it is slow in psycopg2, a for loop of execute's are faster in psycopg2 (not in other drivers though)
Why???

[harsh pulsar]

Because of the text protocol they use?

Maybe it will be fixed in psycopg3

[brazen charm]

hell if i know

[harsh pulsar]

It's not like that in asyncpg right

[brazen charm]

Its one of the many WTF moments in that lib

[brave bridge]

is it faster even with a remote database?

[brazen charm]

asyncpg is fine

[mortal light]

Are you saying execute in a for loop is faster than executemany?

I think it could be because executemany runs a commit after each insert. At least, that's what I had read somewhere.

What's the convention for running execute in a loop?

for loop:
  cur.execute(query)
conn.commit()

[brazen charm]

well generally it's a very bad idiom because you should use executemany

but psycopg2 just has it's wtf moments

[mortal light]

ok thanks for mentioning about injection vulnerability and to not format queries how I had done it.
I'm going through this post https://realpython.com/prevent-python-sql-injection/

[silk storm]

does anyone know hwo to connect Django to a Postgres database? I looked online and none of the guides helped me

[torn sphinx]

Hi, I hope I'm in the correct discussion.
I played with the jupyter notebook a bit. And created a sql orientated kernel. See here:
https://github.com/mtatton/sqlok
My question is. I would like to have everything in one file kernel.py. But I wasn't able to remove the init.py otherwise the kernel won't function properly. Why is that?

[faint blade]

This is because Jupyter imports sqlik the folder, which will check in __init__.py. You can seperate things into multiple files, as long as you import them in that __init__.py file

This is not really a databases question, even though what you're making is about databases. The problem isn't about databases. You're better off grabbing a help channel next time! See #❓｜how-to-get-help

[torn sphinx]

!code

[delicate field]

Here's how to format Python code on Discord:

```py
print('Hello world!')
```

These are backticks, not quotes. Check this out if you can't find the backtick key.

[torn sphinx]

can someone help me with docker+django+postgres?
my api doesn't work

settings.py:
DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.postgresql',
        'NAME': 'junglechallenge',
        'USER': 'jungleuser',
        'PASSWORD': 'challenge',
        'HOST': 'pgdb',
        'PORT': 5432,
    }
}


docker-compose.yml:
version: '3.0'

services:
  django_api:
    build: .
    image: chrisemke/junglechallenge:0.1
    container_name: django
    volumes:
      - django_api:/code
    ports:
      - 8000:8000
    depends_on:
      - pgdb
  pgdb:
    image: postgres:13
    container_name: postgres
    ports:
      - 5432:5432
    environment:
      - POSTGRES_USER=jungleuser
      - POSTGRES_PASSWORD=challenge
      - POSTGRES_DB=junglechallenge
    volumes:
      - postgres_data:/var/lib/postgresql/data/

volumes:
  postgres_data:
  django_api:

It stopped working after I try to implement postgresql instead of sqlite

[harsh pulsar]

@torn sphinx what does "stopped working" mean?

[torn sphinx]

with sqlite it was working but when I tryed postgresql it stopped @harsh pulsar

[brave bridge]

Do you get any errors?

how did you determine that it's not working?

[torn sphinx]

i try making an get on the api

[torn sphinx]

no

[brave bridge]

and what happens?

[torn sphinx]

it's like if the api is not running

[brave bridge]

Do you get a response?

We don't understand what's not working

[grim zephyr]

@brave bridge

Sry for ping but I am facing a error

Can u plz help me

[grim zephyr]

  File "C:\Users\USER\AppData\Local\Programs\Python\Python39\lib\site-packages\discord\ext\commands\core.py", line 85, in wrapped
    ret = await coro(*args, **kwargs)
  File "C:\Users\USER\Desktop\ZENESIS DEVELOPEMENT\ZENESIS\AFK\cogs\afk.py", line 86, in afk
    await cursor.execute("CREATE TABLE IF NOT EXISTS afk(guild_id STR, user_id STR, note STR)")
  File "C:\Users\USER\AppData\Local\Programs\Python\Python39\lib\site-packages\aiosqlite\cursor.py", line 37, in execute
    await self._execute(self._cursor.execute, sql, parameters)
  File "C:\Users\USER\AppData\Local\Programs\Python\Python39\lib\site-packages\aiosqlite\cursor.py", line 31, in _execute
    return await self._conn._execute(fn, *args, **kwargs)
  File "C:\Users\USER\AppData\Local\Programs\Python\Python39\lib\site-packages\aiosqlite\core.py", line 129, in _execute
    return await future
  File "C:\Users\USER\AppData\Local\Programs\Python\Python39\lib\site-packages\aiosqlite\core.py", line 102, in run
    result = function()
sqlite3.OperationalError: database is locked``` this error is always happening with my aiosql db

[brave bridge]

It happens because you're making two writes at the same time. Can you show more code?

[grim zephyr]

ok showing

!paste

[delicate field]

Pasting large amounts of code

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

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

[grim zephyr]

@brave bridge

https://paste.pythondiscord.com/dopijayago.py

here it is

[brave bridge]

You should close connections, just liike you close files.

db = await aiosqlite.connect(database)
try:
    ...
finally:
    await db.close()

or better,

async with aiosqlite.connect(database) as db:
    ...

if you leave the connection unclosed, your database will hang in a locked state

[grim zephyr]

wait it tried to close and again the same error occured

https://paste.pythondiscord.com/obirilozem.py

[brave bridge]

Can you show the list of files in the directory where your database is?

[grim zephyr]

this

[brave bridge]

That's not how you should close the connection. This will leave it unclosed if an exception occurs in between. That's why you should use try-finally or async with, as I showed

[grim zephyr]

well there is finally too

lol

[brave bridge]

hm?

[torn sphinx]

What vscode theme is that?

[grim zephyr]

nothing but how shall i close the

[grim zephyr]

monokai pro

[torn sphinx]

Tnx

[grim zephyr]

np

@brave bridge so how am i supposed to close it

[brave bridge]

<#databases message>

[grim zephyr]

so i am supposed to use finally

?

right

?

[brave bridge]

async with is better to use

Have you ever worked with files?

[grim zephyr]

no not with files

[brave bridge]

A with block makes sure that a file is closed even if an exception happened while working with it

async with is the async version of with

[grim zephyr]

i see

so what if an error raises, will it close the db automatically or i need to add await db.close()

[brave bridge]

It will close the connection automatically

[grim zephyr]

its good

@brave bridge@brave bridge@brave bridge

sry for the ping again

but what if the program executed properly

will it close the connection

[brave bridge]

yes, it will close the connection in any case

[grim zephyr]

ok

let me try

[brave bridge]

You have a db-journal file because you left a connection hanging. The journal keeps track of unclosed transactions, and it allows SQLite to make sure the data doesn't get corrupted in case the application crashes etc.
To safely remove the log, you should execute a VACUUM; query on the database.

1. Run sqlite3 the-db-file.sqlite3 and then enter VACUUM;, but I'm not sure if sqlite3 is available on Windows
2. if it's not, just open the Python console and run ```py
   import sqlite3
   conn = sqlite3.connect("the-db-file.sqlite3")
   conn.execute("VACUUM;")

[grim zephyr]

its aiosqlite3

so will it work

[brave bridge]

yes

[sharp tundra]

wat is blob in sqlite

[torn sphinx]

Binary Large Object iirc

[sharp tundra]

but i cannot like

call it

[austere portal]

sqlite being sqlite

[austere portal]

Will closing all connections fix the problem?

[unkempt nest]

help you guys are good at databases right?

basically i decided to host my mysql

it went fine

but i can't access phpmyadmin

i followed this https://www.digitalocean.com/community/tutorials/how-to-install-and-secure-phpmyadmin-on-ubuntu-20-04

but it's reffusing to connect when i visit the http://localhost:8080/phpmyadmin/

[dense smelt]

Hi guys I'm wondering if the following can be achieved directly in postgres or I have to do it in memory

Say I have 5 rows like this

 VEHICLE         LIST     DAY        ZONE       TOTAL
"Køretøj 1"    "Alle"    "FRIDAY"    "Ingen"    887
"Køretøj 1"    "Alle"    "MONDAY"    "Ingen"    888
"Køretøj 1"    "Alle"    "THURSDAY"  "Ingen"    977
"Køretøj 1"    "Alle"    "TUESDAY"   "Ingen"    1030
"Køretøj 1"    "Alle"    "WEDNESDAY" "Ingen"    930

Could I compress these by creating new columns the DAY and TOTAL by each vehicle/list combination:

Example of what I want to achieve

VEHICLE       LIST        ZONE        MONDAY_TOTAL   TUESDAY_TOTAL , etc
"Køretøj 1"  "Alle"     "Ingen"    "888"          1030

[brave bridge]

What DBMS are you using?

[dense smelt]

POSTGRES

[grim vault]

SELECT
   "VEHICLE", "LIST", "ZONE"
   , MAX(CASE WHEN "DAY" = 'MONDAY' THEN "TOTAL" ELSE 0 END) AS "MONDAY_TOTAL"
   , MAX(CASE WHEN "DAY" = 'TUESDAY' THEN "TOTAL" ELSE 0 END) AS "TUESDAY_TOTAL"
   , MAX(CASE WHEN "DAY" = 'WEDNESDAY' THEN "TOTAL" ELSE 0 END) AS "WEDNESDAY_TOTAL"
   , MAX(CASE WHEN "DAY" = 'THURSDAY' THEN "TOTAL" ELSE 0 END) AS "THURSDAY_TOTAL"
   , MAX(CASE WHEN "DAY" = 'FRIDAY' THEN "TOTAL" ELSE 0 END) AS "FRIDAY_TOTAL"
 FROM test
GROUP BY "VEHICLE", "LIST", "ZONE"```

[brave bridge]

yeah I suppose that's what you want

[brave bridge]

wait, does group by accept numbers?

[grim vault]

Why not?

I'm lazy, you can use "VEHICLE", "LIST", "ZONE" if you want.

[brave bridge]

ah

I wasn't aware you could use positions, sorry

[dense smelt]

Thanks a lot guys! That's a clever trick

[grim vault]

if postgres has FILTER this also works:

SELECT
   "VEHICLE", "LIST", "ZONE"
   , MAX("TOTAL") FILTER (WHERE "DAY" = 'MONDAY') AS "MONDAY_TOTAL"
   , MAX("TOTAL") FILTER (WHERE "DAY" = 'TUESDAY') AS "TUESDAY_TOTAL"
   , MAX("TOTAL") FILTER (WHERE "DAY" = 'WEDNESDAY') AS "WEDNESDAY_TOTAL"
   , MAX("TOTAL") FILTER (WHERE "DAY" = 'THURSDAY') AS "THURSDAY_TOTAL"
   , MAX("TOTAL") FILTER (WHERE "DAY" = 'FRIDAY') AS "FRIDAY_TOTAL"
 FROM test
GROUP BY "VEHICLE", "LIST", "ZONE"```

Looks a little bit nicer.

[dim sluice]

Is pymongo async, as in can I use it with my discord bot?

[brave bridge]

If you don't need to await I/O operations, it's not async.

[dim sluice]

So it basically stops my whole bot?

[brave bridge]

You should either use motor or wrap the calls into loop.run_in_executor

[dim sluice]

Can you explain more about the loop.run_in_executor?

[brave bridge]

Yes, if you do something like ```py
x = time.sleep(5)

[brave bridge]

It allows you to run a blocking function in a separate thread or process.

That's what some libraries like motor or aiosqlite use internally, and then they wrap a blocking library like pymongo or sqlite3.

[dim sluice]

Thread?

No but I mean like if I do time.sleep(5) Doesn't all of my bot stop for 5 seconds

[brave bridge]

Yes.

[dim sluice]

So all of my bot runs in the same thread

[brave bridge]

Yes.

[dim sluice]

Ah

[dim sluice]

Wdym internally

[brave bridge]

At the core of an asynchronous application there's an event loop, it slices your code into chunks, so when you run an await ..., the function you're running suspends (like a generator), and then, when the data arrives, the event loop continues executing it. At one point in time, only one coroutine will actually be running.

[brave bridge]

They use run_in_executor or something similar to turn a set of blocking functions (that a library like sqlite3 provides) into a set of async functions by running them in a separate thread. E.g. you could implement asyncio.sleep like this:

async def sleep(duration):
    loop = asyncio.get_running_loop()
    await loop.run_in_executor(None, time.sleep, duration)

[dim sluice]

So I should either use motor or do the pymongo things in a loop.run_in_executor

[brave bridge]

yes

[dim sluice]

Which one would you suggest?

[brave bridge]

I don't know how good motor is, haven't used it, but try it if it has good documentation

[dim sluice]

So not run_in_executor?

[swift narwhal]

++

[austere portal]

There is no reason to use loop.run_in_executor for a blocking library if there is an async variant of it

[jaunty galleon]

Yeah motor is pretty basic like pymongo, but it has something weird, pymongo has (I think it's called like that) collection.update, but motor only has collection.update_one or insert_one. And ofc they need to be awaited

[brave bridge]

well, it depends on how the wrapper is written

how good the documentation is etc.

I tried reading motor's source code, I am still recovering

[austere portal]

Yeah but in this case there is no use for loop.run_in_executor, just use motor

[brazen charm]

the async variant in this case is just a glorified run_in_executor

[brave bridge]

yeah, except you don't need to write it out

[brazen charm]

I dont even bother dealing with motor, just run it in a executor

[brave bridge]

one advantage of pymongo is that you get editor autocompletion

well... basic autocompletion, it doesn't seem to have type stubs

[brazen charm]

I prefer the more jank solution of aliasing pymongo until runtime where it monkey patches it with a custom executor class

[frigid flare]

// we use mysql2 library to request connection pool from postgres database
let mysql = require('mysql2');

// we connect to pg using pool we requested
let con = mysql.createConnection({
  host: "localhost",
  user: "raus",
  password: "raus123",
  database: "raus",
  multipleStatements: true
});


con.connect(function(err) {

  if (err) throw err;
  console.log("Connected!");

  let sql = "CREATE TABLE mentees (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(40), age VARCHAR(40), department VARCHAR(255), role VARCHAR(40), status VARCHAR(255), createdAt DATE)";
  
  let tableCreateMsg = (err, result) => {if (err) throw err; console.log("Table created"); }
  con.query(sql,tableCreateMsg);
  
}); 

I tried running this in mysql and I got this error Error: Access denied for user 'raus'@'localhost' (using password: YES) what could be causing this?

[harsh pulsar]

wrong password?

[hazy hazel]

someone please help django.db.utils.IntegrityError: could not create unique index "Occupy_clique_occupiers_id_0da8700b_uniq"
DETAIL: Key (occupiers_id)=(6) is duplicated. (thanks)

[brave bridge]

It means that you are trying to create a unique index on an occupiers_id field, but the table already violates this constraint, i.e. you have 2 users with occupiers_id = 6

[hazy hazel]

thanks for this reply !

[brave bridge]

if you want to know what exactly is wrong, you should make a query into the table and find out what violates that constraint

[hazy hazel]

do i do that from the console ?

[lost echo]

Is there any way by which the database can ping the connected clients that some change has been made to it? Basically a socketio type of behavior but with database?

[proven arrow]

That shouldn’t be the job of your database but rather your application.

[tawdry pawn]

how do i use upsert with mongodb

[tawny socket]

i use django extensions library..which u can have ipython...then set the seetings show the query of database...more easy debug

[tawny socket]

https://stackoverflow.com/questions/19725213/ping-mysql-to-keep-connection-alive-in-django

[tawny socket]

https://github.com/gpennington/django-ping

[upper basin]

how do i go about adding to a json value in pgsql?

UPDATE users SET t.stats = jsonb_set(stats,"3",oldval + $1)

was looking for something like this?

[unkempt prism]

docs are a little confusing though jsonb_set seems to be a good fit.

What is oldval and what is $1

[upper basin]

i just want to increase the old value by $1, which might vary

[unkempt prism]

is it wish count?

[upper basin]

normally i select it, get the value, add the two values, then update it, but i was looking to do it in a single statement

ye whenever i run the command, wishcount gets updated by 10

[unkempt prism]

should you use (stats->>'wish_count')::int instead of oldvalue

edit: this will cast it to int

[upper basin]

sweet, let me try it out, thanks!

[unkempt prism]

let me know how you go as I couldn't quite get the syntax right

with discord_json as (
    select jsonb_build_object('3', 598,'4',69, '5', 3, 'wish_count', 67) as stats
)
select  jsonb_build_array((stats->>'wish_count')::int+$1), jsonb_set(stats, '{0,wish_count}'::text[], jsonb_build_array((stats->>'wish_count')::int+$1), true) from discord_json

With my param as 55 I got this:

jsonb_build_array|jsonb_set |
-----------------+---------------------------------------------+
[122] |{"3": 598, "4": 69, "5": 3, "wish_count": 67}|

[snow niche]

any way to await this?

self.c = self.conn.cursor()
``` bcz im using aiosqlite which is async version of sqlite3

[austere portal]

self.c = await self.conn.cursor()

[snow niche]

I tried but said can only use with async function

[austere portal]

oh, you didnt say the function wasnt async

you cant have await outside async functions

You can use asyncio.loop.run_until_complete

!d asyncio.loop.run_until_complete

[delicate field]

asyncio.loop.run\_until\_complete

loop.run_until_complete(future)```
Run until the *future* (an instance of [`Future`](https://docs.python.org/3/library/asyncio-future.html#asyncio.Future "asyncio.Future")) has completed.

If the argument is a [coroutine object](https://docs.python.org/3/library/asyncio-task.html#coroutine) it is implicitly scheduled to run as a [`asyncio.Task`](https://docs.python.org/3/library/asyncio-task.html#asyncio.Task "asyncio.Task").

Return the Future’s result or raise its exception.

[snow niche]

Near bot.run() ?

[austere portal]

no

[snow niche]

Then

[austere portal]

self.c = self.bot.loop.run_until_complete(self.conn.cursor())

[snow niche]

Hmm

[white mauve]

Oh wait, I figured it now

[tired prairie]

Hi!
I am using postgress and want to do something like:
await connection.fetch("SELECT user_id, credits FROM info ORDER BY credits DESC OFFSET=$1 LIMIT 10",offset)

However I get the error:
Command raised an exception: PostgresSyntaxError: syntax error at or near "="

What am I doing wrong?

I know something like this is right because:
await connection.fetchrow("SELECT * FROM info WHERE user_id=$1",user.id) works

[grim vault]

it's ... LIMIT 10 OFFSET $1 first limit then offset without the =

[torn sphinx]

Im using sqlite3, but how to see if something is in a table?

[upper basin]

see something is in a table as in?

<@&831776746206265384>

[hazy hazel]

how please ? edit ?

[upper basin]

UPDATE users SET stats = JSONB_SET(stats,'wish_count', (stats->>'wish_count'::int) + $1

on more testing n finagling, this isnt working, anyone else have any idea?

[shadow raven]

kennt jemand ein gutes SQlite tutorial?

Sorry, i forgot that this is a english sever, does anybody know a good SQlite tutorial, i would prefer Text to Video and i have basically no experience with SQL so it would be nice if it contains the basics of SQL

[torn sphinx]

what does ORDER BY means in sql ?

[brisk phoenix]

It presents the rows in a specific filter, for example, a column. Usually this happens with Id's, where you can see every row in numeric order, but this can be done with nonnumerical characters too.

[torn sphinx]

alright thanks

[torn sphinx]

https://sqlbolt.com/lesson/

[dim sluice]

How do I use find_one with motor?

[shadow raven]

much appreciated thank you

[gusty mulch]

I keep getting this error when trying to add a channel ID to mt db.sql UndefinedColumnError: column "editedmessages" of relation "data" does not existmy command code ispy @commands.has_permissions(administrator = True) @commands.guild_only() @commands.command(description = "Sets the log for edited messages.", alaises = ["eml", "editlog"]) async def edited(self, ctx, channel: discord.TextChannel = [], *, check = None): if check: await ctx.send(embed = discord.Embed(description = f"The channel is not found. Please try again.", colour = self.bot.error_colour,)) return async with self.bot.pool.acquire() as conn: await conn.execute("UPDATE data SET editedmessages =$1 WHERE guild=$2", channel.id, ctx.guild.id,) await ctx.send(embed = discord.Embed(description = f"The channel is updated successfully to {channel.mention}.", colour = self.bot.primary_colour))and I have a few commands which are almost identical which do work. \the only difference is they are looking for a different section of the db. I very much do have the table as well and I copy pasted the name to make sure I didn't have a spelling mistake.

[ocean tapir]

Hello, I’m using MongoDB and I’m wondering if it’s possible to get some data like the latency. Just curious.

[torn sphinx]

# Pass data to fill a query placeholders and let Psycopg perform
# the correct conversion (no more SQL injections!)
>>> cur.execute("INSERT INTO test (num, data) VALUES (%s, %s)",
...      (100, "abc'def"))

anyone know what exactly it is that Psychopg2 (or any Python db API) is doing that prevents SQL injections? like what's a correct conversion and what's an incorrect conversion?

snip is from https://www.psycopg.org/docs/usage.html

[torn sphinx]

SELECT title, domestic_sales, international_sales 
FROM movies
  JOIN boxoffice
    ON movies.id = boxoffice.movie_id;``` i need to learn how does , join , on , works / means

[upper basin]

is there any way to dynamically set column name within pgsql itself? im doing an INSERT ON CONFLICT UPDATE and i only want to update a specific column based on user input, and rihgt now i just use an f-string like

f"INSERT INTO users (id, uid, namecard) VALUES ($1, $2, $3 ON CONFLICT (id) DO UPDATE SET {row}=$2"

but i was wondering if there's a better way to do it

[harsh pulsar]

i think typically it creates a prepared statement on the database server, which have parameters to which you can bind values

so cur.execute creates the prepare statement INSERT INTO test (num, data) VALUES (%s, %s) and then binds (100, "abc'def") to its parameters

[harsh pulsar]

https://sqlbolt.com/lesson/select_queries_with_joins

[harsh pulsar]

this is the only way, but instead of accepting arbitrary user input you should check it against a list of whitelisted known-valid names

[upper basin]

yee, by user input i mean what they want to change, its checked within a different function if its a valid row or not

[harsh pulsar]

these are columns, not rows

think in a spreadsheet, the rows go across, the columns go up and down like columns that hold a building up

# BAD
def do_query(colname, *values):
    db.execute(f"INSERT INTO users (id, uid, namecard) VALUES ($1, $2, $3) ON CONFLICT (id) DO UPDATE SET {colname}=$2", values)

# GOOD
def do_query(colname, *values):
    if colname not in {'uid', 'namecard'}:
        raise ValueError('Invalid column name.')
    db.execute(f"INSERT INTO users (id, uid, namecard) VALUES ($1, $2, $3) ON CONFLICT (id) DO UPDATE SET {colname}=$2", values)

if you are doing something like the 2nd version you are OK

[torn sphinx]

thanks ya that makes sense

Prepared statements are resilient against SQL injection because values which are transmitted later using a different protocol are not compiled like the statement template. If the statement template is not derived from external input, SQL injection cannot occur.

[torn sphinx]

lmao thats what i was learning from , got stuck

[harsh pulsar]

where did you get stuck?

[torn sphinx]

on the join, on thing

i couldnt really understand it

[harsh pulsar]

so you are confused about what a join is?

[torn sphinx]

join , on

both of those

[harsh pulsar]

do you know what a join is?

[torn sphinx]

You might see queries where the INNER JOIN is written simply as a JOIN. These two are equivalent, but we will continue to refer to these joins as inner-joins because they make the query easier to read once you start using other types of joins, which will be introduced in the following lesson.

but i dont get it

[harsh pulsar]

yes, what part don't you get? this is just explaining that INNER JOIN and JOIN are the same thing

[torn sphinx]

oh

[harsh pulsar]

do you not understand what it means to "join" two tables?

[torn sphinx]

Using the JOIN clause in a query, we can combine row data across two separate tables using this unique key. The first of the joins that we will introduce is the INNER JOIN.

yeah

[harsh pulsar]

ok

[upper basin]

yee thats what im doin, thanks!

[torn sphinx]

it just adds both of the databases to one database ?

[harsh pulsar]

kind of

it matches rows between two tables in the same database

when the condition in the ON clause is true, two rows are considered a "match"

[torn sphinx]

how does it find a match ?

[harsh pulsar]

don't worry about how the database engine actually finds the match at a technical level (the answer is, "it depends")

[torn sphinx]

oh alright

[harsh pulsar]

in a query like

SELECT
  a.x,
  b.y
FROM
  a JOIN b ON a.id = b.id

this says "for each pair of rows in a and b, return the pairs where a.id = b.id is true"

[torn sphinx]

what does .id mean ?

[harsh pulsar]

it's just a column name

have you seen this notation for column names before?

<tablename>.<columnname>

a.id is the column id in the table a

imagine that table a looks like this:

id  x
1   "hello"
2   "text"
3   "stuff"
4   "wow"

and table b looks like this:

id  y
1   555
3   999
5   888
7   777

if you run this query:

SELECT
  a.x,
  b.y
FROM
  a JOIN b ON a.id = b.id

the result is:

id  x        y
1   "hello"  555
3   "stuff"  999

[torn sphinx]

notation ?

whats a notation

OH

[brave bridge]

a way to write something

[torn sphinx]

should i be concerned about it

[harsh pulsar]

you should probably know what the word "notation" means because it appears a lot in english-language technical writing. and you should know that a.id means "table a, column id", because it is a basic part of sql

[torn sphinx]

oh

i got it

thanks

[upper basin]

hey, another question, is ther any way to do an INSERT ON CONFLICT SELECT

[harsh pulsar]

do you want to return the existing data in the case of a conflict?

[upper basin]

yes

ive tried RETURNING * but i get a None, and i suppose that is because nothing is being inserted

[harsh pulsar]

if this is postgres, use the special excluded table for the values you were trying to insert https://www.postgresql.org/docs/current/sql-insert.html

hm, i'm actually not sure if excluded works inside the RETURNING clause

try it and see

[upper basin]

wait, a min, i think i messed up. instead of conflict

i want to select a row if it exists, if it doesnt, insert

[harsh pulsar]

https://wiki.postgresql.org/wiki/UPSERT#RETURNING_behavior

Note that RETURNING does not make visible the "EXCLUDED.*" alias from the UPDATE

[harsh pulsar]

using e.g. psycopg2

with conn.cursor() as curs:
    curs.execute('select x, y, z from things where i = %s', (row_id,))
    rows = curs.fetchall()
    if len(rows) == 0:
        curs.execute('insert into things (i, x, y, z) values (%s, %s, %s, %s)', (row_id, x, y, z))

i don't know if there's a way to do this in a single postgres query

[upper basin]

i used to do this using asyncpg

usdata = await self.bot.katdb.fetch("SELECT * FROM users WHERE id = $1", ctx.author.id)
    if not user: 
        print("getting user")
        user = await self.bot.katdb.fetch("INSERT INTO users (id, uid, namecard, crystals, last_vote, total_votes, stats, inventory) VALUES ($1, $2, $3, $4, $5, $6, $7, $8) RETURNING *",ctx.author.id, 135131632, 'blakanwlk', 69, 'wjda',420,'{"3": 0, "4": 0, "5": 0, "wish_count": 0}','{"Beidou": 1, "Bennett": 1, "Yanfei": 4}')

was just looking at a more efficient way

[harsh pulsar]

oh hold on

[upper basin]

ah i think i got it

INSERT INTO users (id,{row}) VALUES ($1,$2) ON CONFLICT (id) DO UPDATE SET id = EXCLUDED.id RETURNING *

seems to return everything

[harsh pulsar]

that will overwrite the existing row if there is a conflict

that doesn't sound like what you want

https://stackoverflow.com/q/34708509
https://stackoverflow.com/a/42217872

the answer is, it's complicated

[upper basin]

😢

[upper basin]

maybe ill just stick to this

[torn sphinx]

WITH input_rows(usr, contact, name) AS (
   VALUES
      (text 'foo1', text 'bar1', text 'bob1')  -- type casts in first row
    , ('foo2', 'bar2', 'bob2')
    -- more?
   )
, ins AS (
   INSERT INTO chats (usr, contact, name) 
   SELECT * FROM input_rows
   ON CONFLICT (usr, contact) DO NOTHING
   RETURNING id  --, usr, contact              -- return more columns?
   )
SELECT 'i' AS source                           -- 'i' for 'inserted'
     , id  --, usr, contact                    -- return more columns?
FROM   ins
UNION  ALL
SELECT 's' AS source                           -- 's' for 'selected'
     , c.id  --, usr, contact                  -- return more columns?
FROM   input_rows
JOIN   chats c USING (usr, contact);           -- columns of unique index

good lord my brain hurts reading this

[torn sphinx]

how do i use that ?sql on

[upper basin]

is there any way i can update multiple fields of a json entry in a pgsql record using jsonb_set

nvm found it

[torn sphinx]

The module contains objects and functions useful to generate SQL dynamically, in a convenient and safe way. SQL identifiers (e.g. names of tables and fields) cannot be passed to the execute() method like query arguments:
https://www.psycopg.org/docs/sql.html#module-psycopg2.sql

# This will not work
table_name = 'my_table'
cur.execute("insert into %s values (%s, %s)", [table_name, 10, 20])

i dont understand why this wont work, table names are literal strings. why do SQL identifiers need to be treated differently, is it different injection exploits?

A table name, however, is not a plain string.
https://realpython.com/prevent-python-sql-injection/#using-sql-composition
ya i dont get why this is

[harsh pulsar]

@torn sphinx because the underlying postgres machinery (the libpg library) supports binding "data" but not "identifiers"

don't think of it as a string

think of it as generating sql source code

note that psycopg2 does not use prepared statements for parameterized queries. it uses libpq https://www.postgresql.org/docs/current/libpq-exec.html

[torn sphinx]

thanks ill have a look at this

[harsh pulsar]

constructing a query out of valid identifiers is a different task from binding data into placeholders in a query and is right to be treated differently

[snow niche]

so im trying to build sqlite3 table but im using aiosqlite, so can i still name the file .sql?

[sinful ridge]

is there any good tutorial for mongodb with motor ?

[faint blade]

Yes

Well, actually no not the table sorry.

An .sql file is like a Python file (.py), it has text or code. In this case an .sql file is simply a text file made up of SQL queries.

[upper basin]

i am trying to compare a timestamp object endtime with the current time LOCALTIMESTAMP,

SELECT * FROM timers WHERE endtime > LOCALTIMESTAMP

but it return None, am i doing something wrong here

[snow niche]

It's just to build table and when I tried it I can enter some code in it, the doubt I'm having is will it create the table?

[unkempt prism]

I get this with pg 12.5

select localtimestamp

localtimestamp     |
-------------------+
2021-08-16 20:41:59|

Do you have any rows where endtime is in the future

[faint blade]

There should be NOW() for you to use?

[faint blade]

So it's just code to setup the database? Yes you'd use a .sql file for that

[snow niche]

Yes, just to create table

[dim sluice]

How can I measure my database latency with motor?

[austere portal]

I don't know if there is a built in way to do that in motor but you can use the time module

[dim sluice]

yeah but like what should I do? Edit some data or find something or what?

[austere portal]

import time

start_time = time.time()
# run a query
end_time = time.time()
time_took = end_time - start_time```

[dim sluice]

Yes but what query

Like what do you mean by query

[austere portal]

Like getting data from the db

[dim sluice]

bruh ok

[dim sluice]

Seems to have worked

[obtuse leaf]

Hi, anyone knows how to change the timezone to my local timezone for this db?

[austere portal]

what is the timezone of the data?

utc?

[obtuse leaf]

the timezone is in UTC+0.

[austere portal]

!d datetime.tzinfo.fromutc

[delicate field]

datetime.tzinfo.fromutc

tzinfo.fromutc(dt)```
This is called from the default [`datetime.astimezone()`](https://docs.python.org/3/library/datetime.html#datetime.datetime.astimezone "datetime.datetime.astimezone") implementation. When called from that, `dt.tzinfo` is *self*, and *dt*’s date and time data are to be viewed as expressing a UTC time. The purpose of [`fromutc()`](https://docs.python.org/3/library/datetime.html#datetime.tzinfo.fromutc "datetime.tzinfo.fromutc") is to adjust the date and time data, returning an equivalent datetime in *self*’s local time.

[austere portal]

wait nevermind

[torn sphinx]

ALTER TABLE Movies
    ADD COLUMN Aspect_ratio FLOAT DEFAULT = 2.39;``` someone explain what i did wrong ?

Add a column named Aspect_ratio with a FLOAT data type to store the aspect-ratio each movie was released in.```

[crisp arch]

it might change depending on what database you are running, but I know for many, you do not put an = sign for defaults

[torn sphinx]

trying to install oracle xe and having a bad time

[raw blade]

i want to get decent with mysql, does anybody have a pretty comprehensive tutorial or guide they'd like to recommend?

[grim vault]

Looks like a job for: from email.utils import parsedate_to_datetime

from email.utils import parsedate_to_datetime

rows = [('Tue Nov 05 02:32:13 +0000 2013',), ('Mon Aug 11 03:24:34 +0000 2014',)]
for (date_str,) in rows:
    dt_obj = parsedate_to_datetime(date_str)
    print(dt_obj, dt_obj.astimezone())

2013-11-05 02:32:13+00:00 2013-11-05 03:32:13+01:00
2014-08-11 03:24:34+00:00 2014-08-11 05:24:34+02:00```

[obtuse leaf]

thank you! 🙂

[sacred tartan]

Are there some general rules when it comes to using PostgreSQL with Django to optimize query speed? Do I generally want to have many smaller tables, or fewer large tables? Things along those lines.

[cloud current]

What usually helps is to make sure you don't actually issue to many queries. This may be unpopular advice, but for considerably complex queries just writing it in raw SQL and punting it to the DB also works wonders.

[harsh pulsar]

also controlling table size is less important than setting up useful indexes

[sacred tartan]

can you expand on that?

I'm pretty green when it comes to actual db architecture. Even if you can point me at what to read up on.

I just don't want to make some really costly early mistakes that will cost me later and be hard to rework.

[harsh pulsar]

you can always add indexes later

i'd focus on just modeling your data in a way that is semantically sensible

[sacred tartan]

So are indexes essentially a roadmap for queries to get somewhere faster?

[harsh pulsar]

yep. specifically it allows the database to perform WHERE filters more efficiently

a lot of interesting data structures and algos work that goes into it

e.g. postgresql uses something called a "b-tree" index by default in many cases

[sacred tartan]

Ok I'll read up on that.

[harsh pulsar]

create table log_entries (
  id uuid primary key,
  action_time timestamp,
  action_message text
)

if that table is really big, this query could get really slow

select *
from log_entries
where
  action_time >= '2020-01-01' and
  action_time < '2021-01-01'

if you create an index on action_time, you are instructing postgresql to analyze the data and create a data structure that can it can use to significantly speed up that query

databases require manual "care and feeding" like this sometimes

[sacred tartan]

Hmm, interesting.

[harsh pulsar]

i still suggest focusing on semantically meaningful data modeling

you can read about the database "normal forms" too, although the terminology gets very dense and i think intuitively most people build normalized-ish databases anyway

[sacred tartan]

What does "meaningful" mean in this case?

[harsh pulsar]

let's say you're building a hacker news clone in django - it would make sense to have a User class, a Thread class, and a Comment class

no reason to worry about whether that's an efficient format for the database. it makes sense, and let django take care of the database stuff.

when you have more sophisticated an specific needs, you can do more sophisticated and technical things

[astral scaffold]

Hi there,

I got an issue with the AES_ENCRYPT and AES_DECRYPT methods on MySQL and I am losing my mind over this 😅

I insert some code with the following statement :

demandeur_id = str(718896251234565617)
key = 'fm-REDACTED-wlk'
date_intervention = datetime.datetime.strptime('16/08/2021', '%d/%m/%Y').date()
heure_debut = '15'
heure_fin = '17'
membre_id = str(415865498911962625)

When I try to decrypt the ids, I get a none value for both.

cursor.execute(('SELECT *, '
'CAST(AES_DECRYPT(UNHEX("%s"), "%s") AS char) demandeur_id_decrypt, '
'CAST(AES_DECRYPT(UNHEX("%s"), "%s") AS char) membre_id_decrypt '
'FROM table_name_here')
% ('demandeur_id', 'fm-REDACTED-wlk', 'membre_id', 'fm-REDACTED-wlk',))```

```py
[(bytearray(b'3CDB0E016E-REDACTED-3A2CF4'),
  datetime.datetime(2021, 8, 16, 0, 0),
  15,
  17,
  0,
  bytearray(b'AA1FAA70F0EB-REDACTED-0EF052'),
  None,
  None),
 (bytearray(b'3CDB0E016E-REDACTED-3A2CF4'),
  datetime.datetime(2021, 8, 16, 0, 0),
  15,
  17,
  0,
  bytearray(b'AA1FAA70F0EB-REDACTED-0EF052'),
  None,
  None)]
MySQLCursorBuffered: SELECT *, CAST(AES_DECRYPT(UNHEX("demand..```

Here are the criteria of the columns:
```py
('demandeur_id', 'varbinary(200)', 'NO', '', None, '')
('date_intervention', 'datetime', 'NO', '', None, '')
('heure_debut', 'tinyint(1)', 'NO', '', None, '')
('heure_fin', 'tinyint(1)', 'NO', '', None, '')
('prevenu', 'tinyint(1)', 'NO', '', '0', '')
('membre_id', 'varbinary(200)', 'NO', '', None, '')```

Any ideas? 😕

[grim vault]

I'm not sure but isn't mySQL interpreting UNHEX("%s") as string literal and not as identifier? Have you tried backticks (or no quotes at all)?

[earnest rover]

Heya all - I just took a course on API development, with python and django, and I have a question(s). Presently, I have stood up a flask server to run arbitrary SQL select queries, and return it in a html frame. It seems to me, that I could be a whole lot more efficient using an API, but I haven't mentally gotten over the hump of a simplified 10 object 3 column db to what I want it to do. Would I pull everything in raw into a dataframe, then do my manipulations from there (stuff that I would do in my where, group by and order by clauses)?

[pseudo sparrow]

Anyone know a good tutorial for MongoDB.py for Replit?

[earnest rover]

https://replit.com/talk/learn/Using-MongoDB-with-python-using-PyMongo/16720

https://www.youtube.com/watch?v=rE_bJl2GAY8

[elder elk]

can anybody help me with dynamic legend title in geoserver sld

while calling it as wms

what missing here

          <Name>Rainfall1</Name>
          <Name>"Need this dynamic"</Name>
          <ogc:Filter>
            <ogc:PropertyIsLessThanOrEqualTo>
              <ogc:PropertyName>rainfall_mm</ogc:PropertyName>
              <ogc:Function name="env">
                <ogc:Literal>rain1</ogc:Literal>
                <ogc:Literal>-1</ogc:Literal>
              </ogc:Function>
            </ogc:PropertyIsLessThanOrEqualTo>
          </ogc:Filter>
          <PolygonSymbolizer>
            <Fill>
              <CssParameter name="fill">#fcf988</CssParameter>
            </Fill>
          </PolygonSymbolizer>
</Rule>```

[upper basin]

what data type would i need to declare an array as if i want to store both int and text

[torn sphinx]

import mysql.connector

mydb = mysql.connector.connect(host='localhost', user='root', passwd='my password is here')

mycursor = mydb.cursor()

mycursor.execute('CREATE TABLE IF NOT EXISTS lucid( name TEXT , age INT, year FLOAT,)')
``` i tried this 
mysql.connector.errors.ProgrammingError: 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')' at line 1


and this 

```py
mycursor.execute("""CREATE TABLE IF NOT EXISTS lucid
    name TEXT,
    Version FLOAT,
    Download_count INT,
""")
mycursor.execute("INSERT INTO lucid VALUES (lucid, 1.8 , 2000)")```

`error mysql.connector.errors.ProgrammingError: 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'name TEXT,`
 Version FLOAT,
  Download_count INT,' at line 2

[terse stump]

what is TEXT?

do you mean VARCHAR ?

[torn sphinx]

in a course i was in

[terse stump]

ermm... are you sure thats mysql?

[torn sphinx]

it showed me that this is the way to make a string value

thats sql

[terse stump]

your using mysql right?

[torn sphinx]

yes

[terse stump]

SQL is the language

MySQL is the "dialect"

[torn sphinx]

oh

[terse stump]

so you need to adjust to the correct syntax depending on your dialect

[torn sphinx]

i got it

can u send me the link of the docs ?

[terse stump]

also varchar needs to specify a size

VARCHAR(255)

[torn sphinx]

varchar ?

what does that mean

[terse stump]

i dont use mysql, so cant help you with that detail

[terse stump]

this means "TEXT"

[torn sphinx]

oh alright

and the (number)

is to define the size of the columns ?

[terse stump]

correct

i think 255 is the largest size VARCHAR can use

but i forget, have not used mysql in like 15 years

[torn sphinx]

oh damn

thanks either ways

[terse stump]

my pleasure

[torn sphinx]

so

do i need to define the other column sizes too ?

mysql.connector.errors.ProgrammingError: 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'name VARCHAR(258),
    Version FLOAT,
    Download_count INT,' at line 2```

oh damn

[terse stump]

yes, always

there are a few exceptions though

like if you want an auto-increment field, you dont have to specify size (i think)

the ID field is often common to use as auto-increment and primary key

[torn sphinx]

like this ?

[terse stump]

so 258 is probably way to high

255 is max i think for varchar

and for Float and Int

well.. the number of atoms in the visible universe is INT(80)

[terse stump]

so keep it more sensible in size

[torn sphinx]

oh alright

i'll make them 10 for now

[terse stump]

yeah.. 10_000_000 is a large number

[torn sphinx]

    Version FLOAT(10),
    Download_count INT(10),' at line 2

[terse stump]

there are also limits to each datatype

[torn sphinx]

its a bit weird

yeah

also what can i do with mysql workbench ?

[terse stump]

i dont know, workbench did not exist when i used mysql, sounds like a way to run SQL commands and work with the database

yeah, its a nice tool to work and look at your data

[pure sleet]

you can manage your database with a ui

[torn sphinx]

is it better than doing the stuff i'm doing in python ?

[pure sleet]

what? why would you compare? it's just a tool to help,

[torn sphinx]

i meant stuff like this

[pure sleet]

look man, at some point you might not have access to the gui, it's not meant to replace what you can do in the terminal. or python rather. it just makes it easier

[torn sphinx]

meanwhile i have access to it , so might just take advantage of it

to i run it ?

[grim vault]

SQL does not allow a last comma without anything.

[torn sphinx]

oh yes my bad

[upper basin]

anyone?

[torn sphinx]

what are you using ?

[upper basin]

pgsql

[torn sphinx]

mostly its either what they showed

also @terse stump they updated it , they made it text ig

[latent fractal]

line 115, in add
self.cursor.execute(f'UPDATE GuildTable SET GUILD_NAME = {str(msg)} WHERE GUILD_IDS = {ctx.guild.id}')
sqlite3.OperationalError: near "<": syntax error

[torn sphinx]

!code send your code here, please

[delicate field]

Here's how to format Python code on Discord:

```py
print('Hello world!')
```

These are backticks, not quotes. Check this out if you can't find the backtick key.

[latent fractal]

 try:                
                msg = await self.bot.wait_for('message', check=check, timeout=30)

                self.cursor.execute(f"SELECT GUILD_NAME FROM GuildTable WHERE GUILD_IDS = {ctx.guild.id}")
                result = self.cursor.fetchone()
                if result is None:
                    self.cursor.execute(f'INSERT INTO GuildTable(GUILD_NAME) VALUES({str(msg)})')
                elif result is not None:
                    self.cursor.execute(f'UPDATE GuildTable SET GUILD_NAME = {str(msg)} WHERE GUILD_IDS = {ctx.guild.id}')
                       
                self.db.commit()
                self.cursor.close()
                self.db.close()

[torn sphinx]

I'm sorry, but is this your first time with SQLite or have you just recently started?

[latent fractal]

yea this is my first learn SQL

[torn sphinx]

Let me just give you an example..

self.cursor.execute("SELECT GUILD_NAME FROM GuildTable WHERE GUILD_IDS = ?", (ctx.guild.id))

This is how it should be done

[shell ocean]

why do you want to do that?

[brave bridge]

(ctx.guild.id,) *

[latent fractal]

  try:                
                msg = await self.bot.wait_for('message', check=check, timeout=30)

                self.cursor.execute("SELECT GUILD_NAME FROM GuildTable WHERE GUILD_IDS = ?", (ctx.guild.id))

                result = self.cursor.fetchone()

                if result is None:
                    self.cursor.execute(f'INSERT INTO GuildTable(GUILD_NAME) VALUES({msg})')
                elif result is not None:
                    self.cursor.execute(f'UPDATE GuildTable SET GUILD_NAME = ? WHERE GUILD_IDS = ?', (msg, ctx.guild.id))
                       
                self.db.commit()
                self.cursor.close()
                self.db.close()
``` Like this?

self.cursor.execute("SELECT GUILD_NAME FROM GuildTable WHERE GUILD_IDS = ?", (ctx.guild.id))
ValueError: parameters are of unsupported type

[torn sphinx]

ah yeah better to make it a tuple

[torn sphinx]

put a comma after ctx.guild.id

like what fix error said above

[grim vault]

The second parameter must be a iterable, (var) is not. To make it a tuple add a comma: (var,)

                self.cursor.execute("SELECT GUILD_NAME FROM GuildTable WHERE GUILD_IDS = ?", (ctx.guild.id,))

                result = self.cursor.fetchone()

                if result is None:
                    self.cursor.execute('INSERT INTO GuildTable(GUILD_NAME) VALUES(?)', (msg,))
                elif result is not None:
                    self.cursor.execute('UPDATE GuildTable SET GUILD_NAME = ? WHERE GUILD_IDS = ?', (msg, ctx.guild.id))```

[torn sphinx]

[latent fractal]

self.cursor.execute(f'UPDATE GuildTable SET GUILD_NAME = ? WHERE GUILD_IDS = ?', (msg, ctx.guild.id,))
sqlite3.InterfaceError: Error binding parameter 0 - probably unsupported type.

[torn sphinx]

what data type did you put into the fields?

[latent fractal]

all is str

[torn sphinx]

ahh

put int if you are putting ID's

[latent fractal]

self.cursor.execute('UPDATE GuildTable SET GUILD_NAME = ? WHERE GUILD_IDS = ?', (msg, ctx.guild.id))
sqlite3.InterfaceError: Error binding parameter 0 - probably unsupported type.

Another code working normally

[faint elm]

Well you are using the wrong format type

Use sql formatting not f strings

How can I find the length of a mongo array? It won’t let me len it and iirc the $size operator just limits how many results it returns in a array

[rough frost]

How to solve this issue
If Col1 and col3 has duplicate then col2 will get summed but if col1 has duplicate but col3 doesnt then col1 should be renamed

[errant drum]

I have two tables: Persons and Properties. Persons has ID and name, Properties has person_ID and boolean value.
How can I select all persons, who have a True boolean value?

Can you provide me with some examples of cross table queries?

[upper basin]

well its very hard to make columns, i was looking to make an array of [["char",2],["char",3]]

iits hard to make columns because they keep increasing

and making a column everytime a new char is out seems inefficient

[brave bridge]

@upper basin Can you be more concrete on what these columns are? There should be a better way

[pseudo flame]

why is it that why i try to connect to my mongoDB instance with a database at the end of the URI, i get authentication failed?

mongodb://username:password@ip

that URI works

mongodb://username:password@ip/dbname

this doesn't

[thick root]

does anybody have a good source website/tutorial for absolute beginners?

i started python just yesterday on my own but i dont know where to proceed

[upper basin]

Well each user has an inventory, which consists of multiple item, and the amount of items they have

i cant do a normal table with user and amount, because the amount of items keep increasing

and then everytime user opens a box, they get multiple items, so id have to check if they have that in that inventory and then update count

[brave bridge]

The way it's usually done is like this:

inventory
user_id | item_id | count
--------+---------+------

items
id      | name    | description | etc....
--------+---------+-------------+--------

and instead of storing a single field in the user's row, you store several rows in inventory

[upper basin]

ye but a user having multiple items would have multiple rows and the database is quite big

[brave bridge]

well, in your case you have a giant field in a single row, that's worse

[upper basin]

thats true

[brave bridge]

For example, if you want to answer a question like "how many users have <item_name>?", you'll need to go over all the users.

[upper basin]

does loads of rows affect performance

[brave bridge]

But if you have a classic many-to-many approach (like I showed), you can make an index on item_id, and then this operation will complete in O(log n) instead of O(n)

[brave bridge]

it depends on what kind of queries you need to perform

Do you have some extra information about items beside their name?

[upper basin]

no

all i have to do is check if user has item then add

and display all items user has

[brave bridge]

How many items (approximately) will a user have?

[upper basin]

that depends

wait items specefically

[brave bridge]

on what order (100, 1000, 100000)?

[upper basin]

max would be around 30 for now

but amount of each item would 1000s

[brave bridge]

I mean, how many elements are in each array?

if it's 30, then it probably doesn't matter, you can store it in a JSON field if you want (it's called JSONB in Postgres, don't know what you're using)

[upper basin]

max would be 30, increased later

[upper basin]

if i make a table like this, itd be around 8.2 million rows

[brave bridge]

In the array approach, you'll need to scan the entire array to see if the user has a particular item.

[brave bridge]

do you have 273k users?

[upper basin]

ye around that much

36 rows per user

[brave bridge]

well, I guess you can benchmark on a test database and see which one is faster 🙂

[upper basin]

[upper basin]

is there a way to update multiple array elements at once

[brave bridge]

So if a user has 1000 distinct items, you'll have to potentially scan through all the 1000 items to see if a user has a particular item.

[brave bridge]

what DBMS are you using?

[upper basin]

pgsql

[brave bridge]

I don't see how that would help

if you want to insert an item, you'll have to do a linear search

I guess you could use JSONB and store the item type as the key and amount as the value

[upper basin]

i think ill just do it this way

[elfin orbit]

What's the different between mysql and mongodb?

When should i use either

[upper basin]

mysql is RDBMS, mongodb is more NoSQL

[elfin orbit]

Erm.... can u be more specific.

?

If better, With examples about one of them being better choice than the other one

[brave bridge]

@elfin orbit Relational databases (like MySQL) store data in relations (tables) with fixed structure, document databases (like MongoDB) store data in documents (nested structures with no constraints on shape))

Relational databases are the default choice for most applications nowadays

at least that's the impression I get

[elfin orbit]

So for example if i'm making a website to store all users of a company, there's no difference in using either one?

[brave bridge]

it depends ™️

If you have 1000 users, it probably doesn't matter at all

[elfin orbit]

And?

[harsh pulsar]

having worked with both kinds of databases professionally, i strongly suggest going with relational unless you specifically need otherwise

[brave bridge]

I don't know much about document databases to be honest

[elfin orbit]

When do i specifically need otherwise?

[harsh pulsar]

e.g. if you need to store arbitrarily- and inconsistently-shaped json data in a way that you can easily query down into the data

that said, postgresql has great json support nowadays and you can do most of what you would want to do in mongodb in postgres

but postgres is unusual in that regard

[brave bridge]

There's a talk by Martin Fowler about when you might need a NoSQL database, seems legit https://www.youtube.com/watch?v=qI_g07C_Q5I

[harsh pulsar]

yeah you pretty much don't need it for most hobby projects

then again some people really just hate schemas and sql and they use mongo for everything

i think it's good to start with sql, learn it, do the hard thing, learn about tables and indexes and insert and joins etc.

then once you already know what you're doing, feel free to poke around with mongo

[elfin orbit]

I have learned about mysql a long time ago

And i'm planning to learn mongodb

So i guess it wouldn't hurt to learn it anyways

And thanks for the information guys, really appreciated it

[quasi mortar]

Hi everyone, I'm looking for a python package which will let me represent a SQL table schema in memory. I don't need the data, just the schema

My ideal package would do the following
Connect to the SQL database
Copy over the SQL database schema
** Preserve the FK/PK relationship**
i.e. if A.<field> Reference B.<field> then there should be a pointer/edge between the two nodes

Does anyone know of such a package?

[austere portal]

SQL Injection detected

https://xkcd.com/327/

[harsh pulsar]

sqlalchemy can build classes from a database schema, but you might have to dig into the library a bit if you are trying to do a lot of heavy introspection

[quasi mortar]

@harsh pulsar I'll check that out. That's almost exactly what I need to do.

[dense barn]

how would i delete duplicate records from a table?

[pure sleet]

how do you even have duplicate records?

[dense barn]

add the same exact thing again?

[pure sleet]

bro, constraints are there to help you reduce duplicate data, which is redundant data tbh

[grim vault]

Most DBs have a special column called ROWID or something like that, which is unique and only displayed if requested.

It can be used to delete only one of the duplicate entry.

[pure sleet]

if you're using a sql based db you can target them with a delete statement

[dense barn]

Ye I'm using postgres

[pure sleet]

so just use a delete statement and target the column with the repeating data

[dense barn]

But the duplicate column has all the things same as the other

[pure sleet]

can you show me what this looks like?

[dense barn]

Uhh

Im currently outside, can I ping you after I'm at home?

[pure sleet]

k

[faint elm]

how can i get the length of a mongo array? if i len() it errors and all i can see is the $size operator but it just looks like it will limit the array it returns

when i want to return a count

i could probably just create my own len

[torn sphinx]

mysql.connector.errors.ProgrammingError: 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '%s)' at line 1

how can i fix this

[faint elm]

syntax error

means you messed something obvious up

[torn sphinx]

c.execute("INSERT INTO ApiKeys (apikey) VALUE (%s)", (data_apikey))
db.commit()```

[faint elm]

it does give you a hint

[hot lagoon]

Has anyone here used sqlalchemy before?

[faint elm]

check that its the right sql format for that version as the error does say

[austere portal]

change VALUE to VALUES

[torn sphinx]

doesnt change anything

[grim vault]

and (data_apikey,) to make it a tuple.

[torn sphinx]

thanks

[faint elm]

.

[dense oar]

MongoDB question - I've got a collection with documents that goes like:

[{'_id': ObjectId('611bf9434097ed196332412e'), 'color': 'blue'},
 {'_id': ObjectId('611bf95362b1362e1af973cf'), 'color': 'red'},
 {'_id': ObjectId('611bf95dca6f5312d87494d2'), 'color': 'red'}]

I'd like to create an aggregation that returns the following:

[{"color": "red", "count": 3, "percentage": 75},
 {"color": "blue", "count": 1, "percentage": 25}]

Basically I want to count how many times each color appears in the collection, calculate the percentage of them, and sort it by count in descending order.
So far I've got this:

data = [i for i in collection.aggregate([
    {"$group": {"_id": "$color", "count": {"$sum": 1}}},
])]

It returns the following so the structure is not quite correct and I couldn't figure out the percentage yet:

[{'_id': 'blue', 'count': 1},
 {'_id': 'red', 'count': 3}]

Can someone help with this?

[dense barn]

@pure sleet ok this is what it looks like:

woah the joindates are different

but its not supposed to have 2 of the same records

[pure sleet]

bro, you can use as many columns as you want for the WHERE clause using AND with it.

[dense barn]

thats not what i mean, i did that to show you i have a duplicate row of the same id

[pure sleet]

you want to delete don't you?

[dense barn]

yes i do

i have a lot of those but with different ids

[pure sleet]

DELETE FROM TABLE WHERE COL1=something AND COL2=something. AND ... etc

you don't have a unique identifier here?

[dense barn]

no

[pure sleet]

man

is this even a production app?

[dense barn]

wdym

[pure sleet]

is this test data or is this database already being used in production? that's what i am asking

[dense barn]

its already being used

[pure sleet]

damn,

[dense barn]

yea...

[pure sleet]

you're using python?

[dense barn]

yes

also, delete function only deletes 1 row at a time correct?

[pure sleet]

no it targets whatever matches your query

[dense barn]

ah

[pure sleet]

it's gonna delete everything basically

[dense barn]

is there a way i can make it delete only 1 at a time?

[pure sleet]

https://www.mssqltips.com/sqlservertip/1103/delete-duplicate-rows-with-no-primary-key-on-a-sql-server-table/

you should test on dummy data first

[dense barn]

yea

[jaunty galleon]

Exception has occurred: InvalidPasswordError
password authentication failed for user "Student"```
Why do I get this error?

I use asyncpg.create_pool and pass in dsn

?

[pure sleet]

the error is self explanatory

[jaunty galleon]

Yeah but I don't understand why

This is the correct link

[pure sleet]

bro, the exception is literally saying invalid password error

[jaunty galleon]

But

It is the correct one I don't know what else to say

[dim lantern]

is anyone here familiar with SQLAlchemy?

[faint blade]

Perhaps the role/user is case-sensitive. Is it really called Student?

[pure sleet]

clearly it's not if the exception is being raised

[jaunty galleon]

No? Student is some dir in my root, and Student is not even in the URL

[faint blade]

I mean, if you don't want to help you can just be quiet

[faint blade]

Can you send this DSN? (removing the password or any other sensitive information)

[jaunty galleon]

Sure

postgres://username:The password @hattie.db.elephantsql.com/username

The username is also the database name in elephantsql

[faint blade]

/username is actually the database

[faint blade]

Ah okay

[faint blade]

Can you try connecting through pgAdmin first?

Or similar management tools if you have any

[jaunty galleon]

I don't have any

Actually I do have pgadmin4 installed on my computer

But that connects to the local server

[faint blade]

Terribly sorry for the delay, there should be an option to create another connection

https://www.pgadmin.org/docs/pgadmin4/development/connecting.html

https://www.linode.com/docs/guides/how-to-access-postgresql-database-remotely-using-pgadmin-on-windows/

[shell coral]

the latest version of postgresql i will download doesnt matter, right?
or there is a older version better?

[inner ivy]

whats a good database to use for a gw system

[unkempt prism]

@shell coral Latest Postgres 13.4 is rock solid.

[torn sphinx]

i am new to database things... so which database suits well with python, while making an api

[storm mauve]

depends on your needs, but PostgreSQL is probably a safe choice

[torn sphinx]

hmm

could u link a documentation by which i can link postgres with python

coz linking postgres with express and nextjs was easy

[steel rover]

ok so i was making a level system for my discord.py bot, and im using mongodb, but when i run the bot, and someone sends a message, the bot crashes with this error:
pymongo.errors.ServerSelectionTimeoutError: cluster0-shard-00-02.dimok.mongodb.net:27017: [WinError 10054] An existing connection was forcibly closed by the remote host,connection closed,cluster0-shard-00-01.dimok.mongodb.net:27017: [WinError 10054] An existing connection was forcibly closed by the remote host, Timeout: 30s, Topology Description: <TopologyDescription id: 611c80dec0c80cd524f43272, topology_type: ReplicaSetNoPrimary, servers: [<ServerDescription ('cluster0-shard-00-00.dimok.mongodb.net', 27017) server_type: Unknown, rtt: None, error=AutoReconnect('connection closed')>, <ServerDescription ('cluster0-shard-00-01.dimok.mongodb.net', 27017) server_type: Unknown, rtt: None, error=AutoReconnect('cluster0-shard-00-01.dimok.mongodb.net:27017: [WinError 10054] An existing connection was forcibly closed by the remote host')>, <ServerDescription ('cluster0-shard-00-02.dimok.mongodb.net', 27017) server_type: Unknown, rtt: None, error=AutoReconnect('cluster0-shard-00-02.dimok.mongodb.net:27017: [WinError 10054] An existing connection was forcibly closed by the remote host')>]>

someone said its because there are multiple connections

how do i close other connections?

[austere portal]

You need to use a database driver like psycopg2

[torn sphinx]

I need some help finding a language(?) to learn to suit my needs. I am doing machine learning and my training data consists of tabular data. The problem is that I would like to have hundreds of millions of these stored without filling hard drives, so I need bit level storage, since utf-8 csv files are too large. Most of the data consists of integers less than 2^10 and 'words' which come from pools with also < 2^10 entrie. Thus every field can be described with at most 10 bits, and many with just 1

I need a way to store these and also access their values fairly efficiently, since I will be doing it constantly. The tabular data will be be 'decompressed' and used to define tensors or used for random forest etc

The rest of the project wont be ready enough to need this for a few months, but I need to get oriented

[torn sphinx]

oh..ok

will try that out

[inner ivy]

^

[austere portal]

what is a gw system?

[narrow moth]

The intermediate psycopg2 stuff is pretty hard for me to get my head around. The basics are very covered and documented, and it looks like the advanced stuff makes the combo of python and postgres really powerful (a novice assumes), but that step past the beginner stuff is not easy.
I'm not sure how SQLAlchemy works, but it might be worth looking into too.

[torn sphinx]

hmm ok

thanks mate

[steel rover]

ok so my level system in the discord.py bot partially works, and i just wanna clear all data in a collection in mongodb, because the rank command works for some users only. so i want to clear the entire collection and start logging the data again. so how do i do that, without deleting the actual collection?

[burnt turret]

Just passing an empty dictionary as the filter to collection.delete_many should work I believe

[steel rover]

i'll try it out, thanks

[snow niche]

am i creating the table correctly, bcz it says no table named warns_data

CREATE TABLE IF NOT EXISTS warns_data (
    guild_id int,
    admin_id int,
    user_id int,
    reason text
)

[torn sphinx]

what kinda stuff was trickier for you out of curiousity? im familiar with postgres but learning psyco and need ideas to explore

[narrow moth]

tokenising user input and (I think it requires) the .register_adapter() extension. I doubt others would have difficulty with implementing their own parser, but I had to dumb it down a lot and do a fair bit of circular functions and basically just ... wing it.

[brisk cedar]

I’m building a little web app w flask rn and I’m trying to learn how to use databases: Is it worth learning SQLAlchemy or should I just start learning MySQL?

[snow niche]

sqlite is easier to use ig

[faint blade]

Ah, yeah using CSV files will have pretty huge amounts of overhead.

What you're looking for seems to be a "data warehouse", so perhaps search around with those keywords.

[torn sphinx]

add a semi-colon after )

CREATE TABLE IF NOT EXISTS warns_data (
    guild_id int,
    admin_id int,
    user_id int,
    reason text
);

[latent fractal]

Thank you for 1 day i though i just need to convert into str

   try:                
                msg = await self.bot.wait_for('message', check=check, timeout=30)

                self.cursor.execute("SELECT guild_name FROM GuildTable WHERE guild_ids = ?", (ctx.guild.id,))

                result = self.cursor.fetchone()

                if result is None:
                    self.cursor.execute('INSERT INTO GuildTable(guild_name) VALUES(?)', (msg,))
                elif result is not None:
                    self.cursor.execute('UPDATE GuildTable SET guild_name = ? WHERE guild_ids = ?', (str(msg), ctx.guild.id,))

And it works

[wise goblet]

I am being an idiot somewhere:

select * from passwd

but when I ran

select password
from passwd
where user='test';

I get nothing

[brave bridge]

@wise goblet Maybe you have spaces in the user field?

also, please never ever store passwords in plain text -- only stores hashes (like blake2 or argon2)

[wise goblet]

yeah yeah, I know. But it is a special case

I am working with third party auth system

and password would be temporal token anyway

[brave bridge]

if it's a token that you need to read in plain text then yeah, there's no way around it

[wise goblet]

made

select user from passwd

apperently I have some sort of conflict

table has test and test2 as user names

but user perhaps is already reserved name for postgres users

[brave bridge]

ah, maybe

well, call it username

[wise goblet]

indeed. it fixed.

sneaky way to facetable.

[elfin orbit]

For mongodb isit possible that a collection consists of data differents fields

For example {{"Username":"Tom","Age":16},{"Username":"Lee","Height":100}}

[snow niche]

Still saying no table I changed by adding double quotes

[austere portal]

Maybe you are connecting to the wrong db

[torn sphinx]

Yea can be

[snow niche]

is there any way to connect to that file and then create? maybe thats the problem

i have tried downloading sqlite opener and creating table... but i have to know what the problem is

[snow niche]

changed to this

CREATE TABLE "warns_data" (
    "admin_id"    INTEGER,
    "user_id"    INTEGER,
    "guild_id"    INTEGER,
    "reason"    TEXT
);

[lone island]

hi i'm trying to insert json data using asyncpg but it adds a \ at the start of each value, how do i fix this ?

[shell coral]

thanks, sorry for not reading your comment yesterday, i went to bed

[unkempt prism]

No worries.

[brave bridge]

Can you show the code?

Can you select items -> "bal"?

[elfin orbit]

Can anyone explain this pls would kindly appreciate it

[torn sphinx]

What data type would I use for making it so members can choose channels for their messages?

Code: Discord.py

Db: PostgreSQL

Using: PgAdmin4

can someone give me an example on how to check the whole database , there is this thing i tried i guess i failed

from database import *

mycursor.execute('select username from aus')
result = mycursor.fetchall()
for i in result:
    print(i)
    if i == 'lucid':
        print('lucid is there')```

[frank parrot]

What are you trying exactly

[lone island]

it says it doesn't exists

itemsvar = {"bal": {"money": 0, "mmoney": 0}}
    userinf = await bot.db.fetchval('SELECT user_id FROM gametb WHERE user_id = $1', str(ctx.author.id))
    if not userinf:
        await bot.db.execute('INSERT INTO gametb (user_id, items) VALUES ($1, $2)', str(ctx.author.id),
                             json.dumps(itemsvar))```
heres the code, i shortened the `itemsvar` here @brave bridge

tried with or without json.dumps

[stray fulcrum]

What is the terminology for, like, "caching" or saving user information on the side so you don't have to call the DB every time?

[lone island]

use it in a variable and global it ?

or you can use files there is a lot of ways

[lone island]

im still stuck on this if anyone could help

[austere portal]

probably BIGINT

[torn sphinx]

Ok

[faint blade]

Caching

[brave bridge]

Can you show the error?

Did you set up any encoders/codecs/whatever they're called when connecting?

[lone island]

yes

[brave bridge]

Can you show them?

and the error

[lone island]

async def init_connection(db):
    await db.set_type_codec(
        'json',
        encoder=json.dumps,
        decoder=json.loads,
        schema='pg_catalog'
    )```

[brave bridge]

@lone island oops, it should've been -> 'bal'

' is for strings in SQL, and " is for using reserved words for column names and other stuff you probably don't need

[lone island]

it still returns null column tho

its probably the codec causing the issue

[brave bridge]

@lone island If you have a codec set up and you're sending a string, you're going to write a string document as JSON

you should just ```py
await bot.db.execute('INSERT INTO gametb (user_id, items) VALUES ($1, $2)', str(ctx.author.id),
itemsvar)

So what you did is you stored a string encoding the dict insteads of the dict

[lone island]

it was string at the first place

it's the same but i'm going to try it again now to make sure

[brave bridge]

{"foo": "bar"} is a dict ('object' in JSON terms), while "{\"foo\": \"bar\"}" is a string

what you have in the database is the latter

[lone island]

yes it's working, thank you

[stray fulcrum]

🤣

[brave bridge]

Btw @lone island , you should probably use the JSONB type. JSON stores the JSON as a string and has to parse it every time, while JSONB stores it in an efficient format, like a tree

[formal coral]

I have a sqlite db, with a table named messages, it has 2 columns, user_id INTEGER PRIMARY KEY and count INTEGER DEFAULT 1.

I have this query, SELECT * FROM messages ORDER BY count DESC LIMIT 10;. This gets 10 entries with highest counts.
My question is, how can I get the next 10 entries in descending order?

[brave bridge]

add OFFSET 10, I think

[formal coral]

thank you!

[lone island]

i'll do that, but for now json is working properly

[faint blade]

You should use a cursor, what wrapper do you use?

[formal coral]

aiosqlite

[faint blade]

https://aiosqlite.omnilib.dev/en/stable/api.html?highlight=Cursor#aiosqlite.cursor.Cursor

Do you see the fetchmany method?

That should allow you to fetch 10, and then the next 10, and the next 10, etc.

[jade reef]

poor jeffrey

[austere portal]

haha

[west furnace]

bet he put some weird unicode somewhere in it

[delicate field]

@copper garden Please don't try to ping @everyone or @here. Your message has been removed. If you believe this was a mistake, please let staff know!

[jaunty galleon]

Hi! I use PostgreSQL(asyncpg with elephantsql), and I get this error when trying to connect:
https://paste.pythondiscord.com/wecohinufi.sql
This is how I try to connect from code:

self.pool = self.loop.run_until_complete(asyncpg.create_pool(dsn=self.retrieve_dsn, min_size=1, max_size=5))

self.retrieve_dsn returns the URL given by elephantsl

[dim lantern]

please help in #help-chestnut , I keep getting this error https://media.discordapp.net/attachments/454941769734422538/877637203826708480/unknown.png

[austere portal]

You cant have a default value null for a column which is not null

Make the column nullable or remove the default value

[dim lantern]

I'm trying to make the default value 0

Even when i tried making it 1, I still got the same error @austere portal

[austere portal]

hmm

[austere portal]

Show the code

[dim lantern]

Ok

class Post(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    title = db.Column(db.String(30), nullable=False)
    content = db.Column(db.Text, nullable=False)
    date_posted = db.Column(db.DateTime, nullable=False, default=datetime.utcnow)
    user_id = db.Column(db.Integer, db.ForeignKey('user.id'), nullable=False)
    has_been_updated = db.Column(db.Boolean, default=True)
    likes = db.Column(db.Integer, nullable=False, default=1)

    def __repr__(self):
        return f'{self.username, self.email, self.likes}'

i'm sort of a beginner, so let me know if you need to see anything else.

[austere portal]

which column is the error coming from?

oh wait found it

[dim lantern]

the likes

yeah

[jaunty galleon]

?

[austere portal]

can you please copy and send the error

[dim lantern]

Ok

sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) Cannot add a NOT NULL column with default value NULL
[SQL: ALTER TABLE post ADD COLUMN likes INTEGER NOT NULL]
(Background on this error at: http://sqlalche.me/e/14/e3q8)

[austere portal]

The only thing I can think of that can be causing the issue is the format of the dsn is wrong, try passing in the information as kwargs

[jaunty galleon]

Wdym in kwargs?

Like options = {'dsn': url} and **options?

[grim vault]

Maybe try:

from sqlalchemy import text
...
likes = db.Column(db.Integer, nullable=False, server_default=text('0'))```

[austere portal]

no like host=..., port=..., password=...

[jaunty galleon]

Oh

[dim lantern]

Oh my goodness it looks like it worked

I just need to implement it in my website to see if it actually works properly, but wow

That's so weird, thank you

[jaunty galleon]

I do I get these from elephantsql?

[austere portal]

get what?, the credentials?

[jaunty galleon]

Yeah

I only have the URL

postgres://username:[e-mail]/db_name

What should I do?

[austere portal]

cant you parse it?

[jaunty galleon]

uh what?

[austere portal]

you already have the dsn string right?

[jaunty galleon]

Oh ok so I created a new db and it works

No idea how

Thank you anyways!

[austere portal]

👍

[torn sphinx]

is there an sql auto formatter like YAPF? i keep seeing beautiful sql and it's making me wonder

[faint elm]

having trouble when using find() in pymongo

it is able to count it accordingly but will not return a thing

@warns.command()
    async def user(self,ctx,user: discord.Member = None):

        collection = cluster["warn"][str(ctx.guild.id)]
        find = collection.find({"user_id": user.id})

        count = 0
        for i in find:
            count += 1

        if count > 7:
                
            ms = ""

            for x in find:

                if int(x['timestamp'].strftime("%d")) > 3:
                    strftime = "%B %dth at %H:%M %p"
                else:
                    strftime = "%B %dnd at %H:%M %p"

                ms += f"Warn: #{x['count']}\nUser Name: {x['user_name']}\nUser ID: {x['user_id']}\nWarn Reason: {x['reason']}\nUser Avatar: {x['user_av']}\nTimestamp: {x['timestamp'].strftime(strftime)}\nWarned by:{x['author_name']} {x['author_id']}\n"

            paste = await mystbin_client.post(ms)
            await ctx.send(str(paste))
            em = discord.Embed(color=self.config["embed_colour"])
            em.description = f"{count} Server Warns. {paste}"
            await ctx.send(embed=em)

        else:
            ms = ""

            for i in find:

                if int(i['timestamp'].strftime("%d")) > 3:
                    strftime = "%B %dth at %H:%M %p"
                else:
                    strftime = "%B %dnd at %H:%M %p"

                ms += f"**{i['count']} - {i['timestamp'].strftime(strftime)}**\nMember: **{i['user_name']} - {i['user_id']}**\nReason: **{i['reason']}**\nModerator: **{i['author_name']} - {i['author_id']}**\n"

            await ctx.send(ms)
            em = discord.Embed(color=self.config["embed_colour"],title=f"{count}. {user} Warns")
            em.description = ms
            await ctx.send(embed=em)
            print(ms)

so in this instance it will just send an embed title with no description and if i just sending it raw it returns empty message error

find({"user_id":user.id}) is correct but im struggling to find out why it wont give me anything back

[elfin orbit]

In mongodb if i have set the username and password for a database do i need to specify it when using MongoClient(...) ?

[blissful rapids]

Hello Everyone, i'm confused to inget data from Mongodb into gcp . any best practice to save data in gcp as a csv or json in cloud storage or bigQuery , many thanks

[jolly urchin]

Can i ask question related to json?

[unkempt prism]

airflow can be useful. My old company went for airflow and we spend months getting started with it though it makes some ingestion tasks like this a breeze. GCP has a hosted airflow solution.

[jolly urchin]

import json

create_id = input('Type y for creating a new id. Type n for quit')

if create_id == 'y':
    id = str(uuid.uuid4())

    with open('ids.json', 'a') as f:
        json.dump(id, f, indent = 4)
    print('Your id has been created!')

else:
    exit()```

This is my code

Which creates a special id then stores it into json

But i am getting an error

[unkempt prism]

best to include the stack trace of your error

[jolly urchin]

Errors

[unkempt prism]

your dumping a string. normally when using the json.dumps or json.loads it makes sense to work with dictionaries

[jolly urchin]

Object of type UUID is not JSON serializable

UUID can't be stored in json

that's why i converted it to string

@unkempt prism

[unkempt prism]

When I run your code I get

[jolly urchin]

but when i append, it shows me this error

[jolly urchin]

...

[unkempt prism]

I see

I think you want a list of ids.

Best to read the file into a list of dicts.

And then add the new ids

And then rewrite (not append) the file with the entire list ids. Old + New

[jolly urchin]

But how would i do that

[unkempt prism]

if the ids.json file exists. use json.loads

[jolly urchin]

instead of json.dump?

[unkempt prism]

no. instead of appending a text file.

You need to load an existing file (if there is one) into memory. add the new ids to memory and write the contents to the ids.json

[blissful rapids]

i see , is cloud composer to run airflow ?

[torn sphinx]

self.coll_users.insert(
            {
                "name": self.name,
                "email": self.email,
                "password": self.password,
                "id": self.user_id,
                "token": self.token,
                "birthday": date,
                "created_date": datetime.utcnow() 
            }
        )

Why don't work?
DB - mongo
date: datetime

[unkempt prism]

Yes thats it.

I only used airflow locally in docker containers though we were looking at migrating to google cloud at one point as the whole company was moving from AWS to GCP.

[thorn canyon]

10        self.conn = sql.connect('levelling.db')
11        self.conn.execute('''
12        CREATE TABLE IF NOT EXISTS settings (
13            guild_id integer PRIMARY KEY,
14            disabled boolean NOT NULL,
15            exp integer NOT NULL,
16            exp_for_lvl integer NOT NULL,
17            level_up_message text NOT NULL,
18            level_up_cnl integer
19        )''')
20
21        self.conn.execute('''
22        CREATE TABLE IF NOT EXISTS exps (
23            user_id integer PRIMARY KEY,
24            exp integer NOT NULL,
25        )''')``` raises ``` File "/b/Zenesis/levelling.py", line 21, in __init__
    self.conn.execute('''
sqlite3.OperationalError: near ")": syntax error```, what am I doing wrong?

[storm mauve]

an extra comma on line 24

[blissful rapids]

awesome , i run docker airflow on local .
but i try to use airflow mongohook mongodb and load data in gcp but i did not found . just found form it
https://github.com/airflow-plugins/Example-Airflow-DAGs/tree/master/etl/mongo_to_redshift , ia any way to that ?

[unkempt prism]

Probably best to check the versions as airflow has made v2.X release in last 12 months and some examples might still be using 1.X

2.X syntax makes more sense.

Can you work out a way to get mongo into the GCP store (s3 equivalent) in 1 operator. and another operator to move it from the store into the destination?

[unkempt prism]

There is a airflow slack that was quite active last time I looked.

[thorn canyon]

thx

[blissful rapids]

sure, i use airflow 2.0.1 version. but did not found move destination to gcp
for this version .

[torn sphinx]

Hey guys, I am trying to display every location and total vaccinations per source_name. Though its printing out the same source_name for every location

SELECT
    vaccinations.location,
    source_name,
    vaccinations.total_vaccinations
FROM
    location.location, Vaccinations.vaccinations
WHERE
    location.location IN (SELECT vaccinations.location FROM vaccinations)
GROUP BY
    vaccinations.location;```

location column exists in both database location and vaccinations

[grim vault]

You are using two tables without a join.

[faint blade]

Where is source_name from?

[torn sphinx]

from database location

[torn sphinx]

😟

[grim vault]

This:
location.location IN (SELECT vaccinations.location FROM vaccinations)
most likely should be:
location.location = vaccinations.location

[torn sphinx]

yes worked haha thank you man

[grim vault]

You also don't need the group by as far as I can see.

[torn sphinx]

nope, just tried it without it

caused the problem

[faint blade]

Can you show the schema of these two tables?

[torn sphinx]

WHERE Country='Mexico';``` Lets say I want to display from Mexico and Australia, How would I type the syntax

I tried AND and ,

but doesnt work

[torn sphinx]

Its all working now, thank you

[faint blade]

Pretty sure there should be an OR operator, otherwise you could do SELECT * FROM Customers WHERE Country IN {'Mexico', 'Australia'};

Those may need to be brackets ([]) I am not completely sure about the difference (if there is one)

[torn sphinx]

Yes it was the () brackets lol

thank you

LAST QUESTION IM SORRY

SELECT
    location,
    MAX(total_vaccinations)
FROM
    Vaccinations.vaccinations
GROUP BY
    location;```
This is not grabbing the latest or largest value from `total_vaccinations` It grabs the largest number for some rows but leaves it blank for others.

as u can see, its supposed to display the value within the blue box but its displaying nothing

[grim vault]

add WHERE total_vaccinations IS NOT NULL

[torn sphinx]

Still printing out empty results 😟

[grim vault]

What column type is total_vaccinations?

maybe try: WHERE total_vaccinations > 0

[torn sphinx]

its a INTEGER

still not working

[grim vault]

I'm flying blind here: WHERE total_vaccinations IS NOT NULL AND total_vaccinations != ''

And does Austria even have that much people to vaccinate?

[torn sphinx]

it worked and lol I guess

[grim vault]

Population
• October 2020 estimate
Neutral increase 8,935,112[6] (97th)

[torn sphinx]

thank you

apparently almost 10 million vaccinated in Austria

[grim vault]

We don't have that much people!

[torn sphinx]

Ministry of Health is the source

🤷‍♂️

[grim vault]

Ah, not people but administered doses (so I have two of them).

5,412,659 people (60.59% of the total population) have received at least one COVID-19 vaccine, 5,061,419 (56.66%) of whom are fully vaccinated.

[formal coral]

is there any specific thing idk about for deleting from a table if the record exists? to avoid errors? sqlite

nvm

[grim vault]

You don't need to check if a record exists.

[formal coral]

yea I just checked that lol

ty

[grim vault]

It just will not delete anything if no row meets the condition.

[formal coral]

yeah

[sudden snow]

these are the models for ecommerce website, can you suggest any improvements?

[sudden snow]

that orange row means one to one relation

[viral furnace]

I am thinking about having redis as the main db (primary db not cache) for one of my projects. what are the issues that i might face ?(edited)

[brave bridge]

Are these mutually exclusive?

also, what DBMS are you using?

[sudden snow]

no they are not

[stuck shell]

@sudden snow it can work but it is quite trivial implementation. For example: add table payment that will contain all information about payment (card nuber, date etc..) and put id_payment as nullable FK to order. Also reject, delivered etc are quite stupid because they are exclusive. Maybe another table containing order progress (placed, paid, accepted, packed, shipped, delivered, finished). Another thing is your order table duplicating as basket i presume. That is quite not good idea. Better is to have two tables (Basket and Order). Etc etc...

[brave bridge]

they said they're not exclusive

but I guess you could move some of them to an enum, like (pseudocode) state: preparing | shipping | delivering | completed | rejected (I don't know the exact process you're using, but something like that)

[sudden snow]

i am going to use order table as a cart 🙂

and will use stripe for payment

or cod

[stuck shell]

Ok maybe not exclusive but they can lead to errors.... for example rejected = True + delivered = True will mean what?

@sudden snow stripe?

[sudden snow]

we can have validation for that right? not at db level tho

[brave bridge]

You can place constraints on the table

[sudden snow]

i use django and it has something called orm so i never needed to think much about db lol

[stuck shell]

YOu have to have constraint on db level for that which is bad design so nope 🙂

[sudden snow]

if there is a boolean field like paid, placed, dispatched, delivered etc and you want to save their datetimes as well then should you just create a nullable datetimefield? i am asking because i dont know about good design.

[stuck shell]

Don't do that, that is bad design. I think better will be have a Order history table which will contain all operations of that order (recieved, shipped, confirmed, etc..). It can be simply exteneded in the future just by adding another operation types like for example: approved, packed, tracking number added etc.... Also if somebody will call your eshop with some question about their order you can simply list whole history of the order.

Also.... you will need some kind of history anyway and those booleans are just duplications => bad design.

Also you can add table with list of order history states and define what state can lead to what state which will made a simple state machine which can be hooked to ACL so not everybody can make every change.

@sudden snow

[dim lantern]

I'm trying to implement likes in my blog made with Flask, can someone help? #help-pancakes

I'm getting an error that I don't understand how to fix, and I'm guessing it's all stemming from the way I created the models in the first place. Not sure if I did it right

[arctic badger]

I am trying to convert my 3 SQL statements into one execute/commit.. Is that possible?

db = pymysql.connect(host=config.host, port=3306, user=config.user, passwd=config.passwd, db=config.db, autocommit=True)
cursor = db.cursor()
sql = f"""INSERT INTO Economy(DiscordID) VALUES ('{ctx.author.id}'); 
          INSERT INTO Inventory(DiscordID) VALUES ('{ctx.author.id}'); 
          INSERT INTO Totals(DiscordID) VALUES ('{ctx.author.id}');"""
cursor.execute(sql)
db.commit()
db.close()

This is currently erroring for me... on that 2nd insert statement.

pymysql.err.ProgrammingError: (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INSERT INTO Inventory(DiscordID) VALUES ('547475078082985990'); \n\t\t\t\t  INSERT IN' at line 2")

[stuck shell]

The question is, why?

There is almost no difference between 3 executes and one long execute.

But one possible solution should be to remove new lines and tabs from query.... maybe?

@arctic badger

[arctic badger]

i just thought this looked ugly:

db = pymysql.connect(host=config.host, port=3306, user=config.user, passwd=config.passwd, db=config.db, autocommit=True)
cursor = db.cursor()
sql = f"""INSERT INTO Economy(DiscordID)
          VALUES ('{ctx.author.id}');"""
cursor.execute(sql)
db.commit()

sql = f"""INSERT INTO Inventory(DiscordID)
          VALUES ('{ctx.author.id}');"""
cursor.execute(sql)
db.commit()

sql = f"""INSERT INTO Totals(DiscordID)
          VALUES ('{ctx.author.id}');"""
cursor.execute(sql)
db.commit()

db.close()

7 lines looks way better than 15 or whatever lol

[stuck shell]

Ehmmm this behave differently than one statement. You making 3 commits instead one.

[arctic badger]

that's what i'm trying to fix

[stuck shell]

Ergo no rollback on error

[brave bridge]

just make one commit in the end, no?

[arctic badger]

i wrote this code like 3 years ago

so just completely removing the other 2 db.commit()'s is ok?

[stuck shell]

Dunno.... it depends if you want to do it in one session or not?

[arctic badger]

so db.commit() is just the save function.. and cursor.execute(sql) is changing the local database

[arctic badger]

well u said the code i have right here ^

[stuck shell]

commit is COMMIT; command in SQL

take a look at sql transactions 🙂

[brave bridge]

also, please never ever use f-strings to build queries -- you should do ```py
sql = "INSERT INTO Totals(DiscordID) VALUES (%s);"
cursor.execute(sql, (str(ctx.author.id),))

This will protect you against SQL injection attacks

[arctic badger]

wouldn't that imply i allow users to provide input for my sql statements?

like i get what ur saying... "better safe than sorry" type of thing. but if i'm only using non-changeable, discord-defined variables, i don't see too much of an issue

[brave bridge]

you aren't?