databases

[rain abyss]

Is it because I have two context managers for the connection?

I just realized that I have two after looking at things

[dusky cape]

Would Anyone know why my SQL database is sorting highest to lowest values in correctly? The screenshot above is some fake data i was playing with and the gross column doesn't appear to sort highest to lowest properly. Unsure as to the cause.

[torn sphinx]

Value inside a value inside a value inside a value inside a value

[fallow ingot]

How can I prevent an connection timeout using MySQL connector? Because everytime I am not using my website for some minutes and then try using again the connection to the database is not active anymore and the website only works when I restart the program

[rich trout]

@dusky cape the values are probably null in the dB, and null is probably set to sort last

[dusky cape]

@Bast the issue is more the fact 8600 is higher than 39520.

[rich trout]

@dusky cape my bad. What you're seeing there is 100% string sorting, I must not have woken up completely

!e ```py
a = ["9520", "8600", "39520"]
print(list(sorted(a)))

[delicate field]

@rich trout :white_check_mark: Your eval job has completed with return code 0.

['39520', '8600', '9520']

[rich trout]

-.-

[dusky cape]

So it's sorting by the first number (of a string) and not the value of an integer

[rich trout]

maybe

but the id's seem sorted anyway, and my test did not behave like I expected it to

Yeah, it appears to be sorted string-wise

!e ```py
a = ['9520', '8600', '39520', '18520', '1100', '0.00']
print(sorted(a)[::-1])

[delicate field]

@rich trout :white_check_mark: Your eval job has completed with return code 0.

['9520', '8600', '39520', '18520', '1100', '0.00']

[dusky cape]

Okay, so I have to figure out why that's the case.

But it at least gives me an error to start with.

[rich trout]

yeah

Honestly I suspect the interface you're using, if the type in the db isn't VARCHAR for some reason

I've run into similar that convert number-like db types into javascript numbers, which breaks discord id's cause they've got too many significant bits, and javascript integers are technically floats..

692079984315203654 would show up as 692079984315203600

[dusky cape]

Okay, when I get home I will check the character type and see if they are saved as strings. I have another database operating correctly. So unsure what the difference is.

[rich trout]

good luck

[torn sphinx]

So how would I make it so that there’s one big chunk (a guild) and inside that chunk, there are users. Each user would usually have a None for the amount of warns they have, but when they’re warned, it would give them a warn. How would I make this? Would this end up with me making a new table for each guild or something?

{ [“guild_id”]{ [“user_id”]{ [“warn”]{ [“mod (who warned”]{ [“date”]{ [“reason”]{ } } } } } }

It looks like a json

[rich trout]

How would you represent this in a spreadsheet?

[torn sphinx]

Guild User ID | Mod ID | Date/Time | Reason

Or would I make it so
Guild ID | User ID | Mod ID | Date/Time | Reason

[rich trout]

yep

should work as a table too right?

[torn sphinx]

Uh, would making multiple tables take up more space or will it take up the same amount?

[rich trout]

best practice is not to have dynamic tables

no reason to, anyway

[torn sphinx]

Dynamic tables?

You mean like tables that have the same purpose, but have different info or something

I’m kinda.. not smart in terms of DBs,

[rich trout]

if there's no reason to have to create or delete tables, you shouldn't be creating or deleting tables

[torn sphinx]

Ah, okay

[rich trout]

usually you hope to be able to make one set of tables that will not need to change, then only work with the data within

[torn sphinx]

Ight, that makes sense, thanks!

[pale sierra]

Anyone that can help with making an ER-Diagram? I have a hard time understanding the relationship my databsae provides.

[rain wagon]

how big is it?

If it is more than 3 tables, you are on your own tbh

but dbeaver provides automatic er diagrams

maybe try out that

@pale sierra

[river dawn]

Hey guys, I am developing a small crud application with flask and mongoDB but I am not sure how collections are created and how to create documents based on schemas..

import os
from flask import Flask, jsonify, request
from flask_pymongo import PyMongo

app = Flask(__name__)
app.config["MONGO_URI"] = "mongodb://localhost:27017/enforceDB"
mongo = PyMongo(app)
db = mongo.db

@app.route("/")
def index():
    realEstate = db.imoveis.find()
    return realEstate

@app.route('/new', methods=['POST'])
def createTodo():
    data = request.get_json(force=True)
    realEstate = db.realEstate.isert_one(data)
    return realEstate

if __name__ == "__main__":
    ENVIRONMENT_DEBUG = os.environ.get("APP_DEBUG", True)
    ENVIRONMENT_PORT = os.environ.get("APP_PORT", 5000)
    app.run(host='0.0.0.0', port=ENVIRONMENT_PORT, debug=ENVIRONMENT_DEBUG)

I think that the collection realEstate is going to be created here, but how would I go about adding a schema for these realEstate like name, address, zip code, etc..

[rain wagon]

with schema you mean the table schema?

Just adhere to database principles.

[river dawn]

by schema I mean a model.. like I would do this in JS

  
import { Schema } from 'mongoose'

const PointSchema = new Schema({
    type: {
        type: String,
        enum: ['Point'],
        required: true
    },
    coordinates: {
        type: [Number],
        required: true
    }
})

export default PointSchema

[pale sierra]

@rain wagon Will that work on csv or xml?

[rain wagon]

@pale sierra If it is a database, DBeaver can connect to it. If it is flat files, no

but that ain't a database

@river dawn Have a look at flask-sqlalchemy

That allows you to create models in python and have sqlalchemy do the db work

[river dawn]

I will take a look, thanks

[pale sierra]

Yeah I basically need to convert my dataset to an ERD and afterwards create a database based on made ERD

[rain wagon]

@river dawn https://pythonhosted.org/Flask-MongoAlchemy/

Apparently you cannot use sqlalchemy directly

@pale sierra DBeaver actually can import csv

I am not sure how well it works though

https://dbeaver.io/ or the Windows store

[pale sierra]

Can you maybe link to the csv import?

Can't seem to find it for some reason

[pale sierra]

Found it

ty

[rain wagon]

You need to install the program

ok 😄

[pale sierra]

Now hoping it will work as intended

[coarse coral]

I am using Flask SQL-Alchemy and I need help. I have two records in a table. Each record has a column called total_sold. Record 1 has a value of 19, record 2 has a value of 12. I am trying to find a way to query the database to find which has sold more.

[strong compass]

import discord
from discord.ext import commands, tasks
from itertools import cycle
import random
import os
import asyncio
from discord.utils import get
import datetime
import sqlite3
import asqlite


class Testing(commands.Cog):

    def __init__(self, client):
        self.client = client

    @commands.command(pass_context=True)
    async def testing(self, ctx):
        db = asqlite.connect('Main.db')
        cursor = await db.cursor()
        cursor.execute(f"SELECT open_job_id FROM logs_id ")
        result = cursor.fetchall()
        await ctx.send(f"{result}")


def setup(client):
    client.add_cog(Testing(client))

error

C:\Users\AFalm\AppData\Local\Programs\Python\Python38-32\python.exe C:/Users/AFalm/PycharmProjects/service/Main.py
Online
Ignoring exception in command testing:
Traceback (most recent call last):
  File "C:\Users\AFalm\AppData\Local\Programs\Python\Python38-32\lib\site-packages\discord\ext\commands\core.py", line 83, in wrapped
    ret = await coro(*args, **kwargs)
  File "C:\Users\AFalm\PycharmProjects\service\Cogs\testing.py", line 21, in testing
    cursor = await db.cursor()
AttributeError: '_ContextManagerMixin' object has no attribute 'cursor'

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

Traceback (most recent call last):
  File "C:\Users\AFalm\AppData\Local\Programs\Python\Python38-32\lib\site-packages\discord\ext\commands\bot.py", line 892, in invoke
    await ctx.command.invoke(ctx)
  File "C:\Users\AFalm\AppData\Local\Programs\Python\Python38-32\lib\site-packages\discord\ext\commands\core.py", line 797, in invoke
    await injected(*ctx.args, **ctx.kwargs)
  File "C:\Users\AFalm\AppData\Local\Programs\Python\Python38-32\lib\site-packages\discord\ext\commands\core.py", line 92, in wrapped
    raise CommandInvokeError(exc) from exc
discord.ext.commands.errors.CommandInvokeError: Command raised an exception: AttributeError: '_ContextManagerMixin' object has no attribute 'cursor'

why this is happening ?

[random sandal]

10. A Doctor can either be an Attending Physician or a Resident .
    Residents assist the Attending Physicians in their work.

Is this the good way to solve this question?

[rich trout]

@strong compass because you're not using the db object correctly, see the docs for examples. It should usually be part of a with statement

[dusty helm]

hey guys, my mongodb is giving me an error, i try to put my string and it gives this message:
Server selection timed out after 30000 ms

[sinful sand]

so trying to migrate bot to a newer server, using pymongo to connect to remote mongodb i get this error

/pymongo/pool.py", line 1184, in _get_socket_no_auth
sock_info = self.sockets.popleft()
IndexError: pop from an empty deque
this issue appears only on the new server so im not exactly whats going on here
complete error log https://pastebin.com/tUvLNrcd

[torn sphinx]

I'm using AWS DynamoDB, what should I use to manipulate and write data using SQL?

I was looking over AWS Athena

but not sure

[twilit arrow]

hey, do you guys know any free MySQL hosting sites?

[void otter]

heroku provides free postgres db for every open project on their repositories

[brisk oar]

if i make module to interact with postgres db, should i connect in every method or i can just connect once ?

[void otter]

you should have some check like if not db_instance.is_connected

if not connected, raise some error or something

[rich trout]

should use a connection pool

[torn sphinx]

so im having trouble with this piece of code here,

    @commands.command()
    async def warn(self, ctx, member : discord.Member, *, reason = None):
        if ctx.message.author.guild_permissions.view_audit_log:
            conn = await aiosqlite3.connect('main.db')
            cur = await conn.cursor()
            unix = time.time()
            date = str(datetime.datetime.fromtimestamp(unix).strftime("%Y - %m - %d %H: %M: %S"))
            reason = reason
            sql = ("INSERT INTO warns(guild_id, user_id, mod_id, datetime, reason) VALUES (?, ?, ?, ?, ?)")
            val = (ctx.guild.id, member.id, ctx.author.id, date, reason)
            await cur.execute(sql, val)
            await conn.commit()
            await cur.close()
            await conn.close()

    @commands.command()
    async def warnings(self, ctx, member : discord.Member):
        conn = await aiosqlite3.connect('main.db')
        cur = await conn.cursor()
        cur.execute(f"SELECT * FROM warns WHERE guild_id = {ctx.guild.id} AND user_id = {member.id}")
        for row in cur.fetchall():
            print(row)
            await conn.commit()
            await cur.close()
            await conn.close()

I warn someone, it records.

But when i use the warnings command

<Future pending cb=[_chain_future.<locals>._call_check_cancel() at C:\Program Files\WindowsApps\PythonSoftwareFoundation.Python.3.8_3.8.496.0_x64__qbz5n2kfra8p0\lib\asyncio\futures.py:360]>

It returns this ^

It gives a ctx.send on a print

  File "Mazaalai.py", line 14, in get_prefix
    return prefixes[str(message.guild.id)]
KeyError: '688443385824018520'

error

def get_prefix(client, message):
    with open('prefixes.json', 'r') as f:
        prefixes = json.load(f)

    return prefixes[str(message.guild.id)]

client = commands.Bot(command_prefix = get_prefix)

code for error

;-;

[rich trout]

For the first one you probably have to use async for

For the second, you're not handling if a server does not have a prefix in the json file

[torn sphinx]

Thx!

Oops, returns with this error

maybe its await?

(nope*

ph actually

no nvm

maybe i set it to a variable?

[rich trout]

Need to await the fetchall

for row in await cur.fetchall()

and the execute

[torn sphinx]

OHH

Makes much more sense now

Thank you!

[torn sphinx]

    raise CommandInvokeError(exc) from exc
discord.ext.commands.errors.CommandInvokeError: Command raised an exception: TypeError: connection is close

When I do warnings, it gives two seperate messages

so i'm guessing it loops

what do I have to do so it sends everything in one message?

    @commands.command()
    async def warnings(self, ctx):
        conn = await aiosqlite3.connect('main.db')
        cur = await conn.cursor()
        await cur.execute(f"SELECT * FROM warns WHERE guild_id = {ctx.guild.id}")
        for row in await cur.fetchall():
            await ctx.send(f"{ctx.guild} has {len(list(row))} warn(s)\n```{row}```")
            await conn.commit()
            await cur.close()
            await conn.close()

It also gives the incorrect amount of warns.

The amount being 5 (Number of fields in table) when there's actually only 2

well, three recorded

2 being sent

;-;-;-; i feel so dumb

im so dumb, idk what it is

[rich trout]

You're closing the conn on every loop but only opening it once

[raw quail]

Hey so I'm trying to do a leaderboard that gets the top 10 and the user's placement, so I tried doing an async loop over every doc and appending it to a list then sorting it, but it took a couple of seconds to loop over just 2k docs (which is lower than what I have) then i tried using generators it didn't really make it much faster, the only solution I found was using .sort()then .limit() but then I can't get the user's placement, any ideas?

I'm using mongodb / motor

[calm charm]

._.

[hazy mango]

I'm trying to connect to a database on my PC but I keep getting an error saying the connection was refused. Any ideas how to fix? ConnectionRefusedError: [WinError 1225] The remote computer refused the network connection

[clever topaz]

@hazy mango What type of database, and I assume it's on another computer?

[hazy mango]

It's same computer, .db file using asyncpg (postgres)

[clever topaz]

Your connection URL must be incorrect.

Can you post it?

[hazy mango]

        db_file_dir = __file__.split("/")[:-1]
        self.db = asyncio.get_event_loop().run_until_complete(asyncpg.connect(host=db_file_dir))```

[clever topaz]

Ah, you cannot connect to a PostgreSQL database in the same way as an SQLite database.

You have to create a postgresql server.

[hazy mango]

Ah, yes

It should be "\" not "/" smh

And I need to join it

And wdym create a server?

[clever topaz]

How did you create the database?

[hazy mango]

I just create a txt file and changed extension to .db

[clever topaz]

Can you post the data (or even a sample)?

I just create a txt file and changed extension to .db
@hazy mango Unfortunately databases don't work that way.

I assume you are trying to load some data from a text file into python?

[hazy mango]

No like

I create a new txt file, change extension to .db, and populate the database using DB Browser software

Then I want to link with that .db in python using postgres (or asyncpg to be precise)

[clever topaz]

Populated which database? A database is very different to a text file - it is a whole separate format.

[hazy mango]

yes IK

[clever topaz]

I assume you are familiar with SQLite databases and the sqlite3 module?

[hazy mango]

But it becomes a db when I change the extension

And yes, I normally use sqlite

the way I create this db is fine when using sqlite so don't see what you're getting at with this txt file thing. It's registered as an empty db once I change the extension

[clever topaz]

Changing a file extension doesn't automatically change the text file into a database. For example, if I had a text file and changed the file format to .py it would still be a text file and I wouldn't be able to run it with python.

[hazy mango]

But you can lol

I do it all the time

[clever topaz]

Must you use asyncpg or would you be willing to use psycopg2? If so then I've found a tutorial that may help you.

[hazy mango]

I just need to use an asynchronous version of postgres and was told that asyncpg was the best

[clever topaz]

Perhaps the tutorial may be helpful anyway - https://pynative.com/python-postgresql-tutorial/

Hey so I'm trying to do a leaderboard that gets the top 10 and the user's placement, so I tried doing an async loop over every doc and appending it to a list then sorting it, but it took a couple of seconds to loop over just 2k docs (which is lower than what I have) then i tried using generators it didn't really make it much faster, the only solution I found was using .sort()then .limit() but then I can't get the user's placement, any ideas?
@raw quail Using select statements (2, perhaps - one to select the top 10 and another to select the user row) rather than extracting all of the data from the database?

[hazy mango]

@clever topaz Now doing asyncpg.connect(user=usr, host=hst, password=pwd, database="discord.db") and getting OSError: [WinError 121] The semaphore timeout period has expired

[clever topaz]

I can assure you that the method you are using is incorrect and I cannot help you if you aren't willing to use the correct method.

One promising avenue (if you are willing to change your approach) is the fact that you created a database with a database browser. I assume you were modifying a different database?

[hazy mango]

the way you are method you are using wdym? @clever topaz

[clever topaz]

i.e. Trying to load a text file with a modified .db extension - I am certain that doesn't work.

[hazy mango]

it does

Try it

As long as you're changing the extension and not the name it works

I don't see what you're saying I'm doing wrong

Because how I'm creating the file is fine, it's how I've always created both .py files and .db files (and can use both as expected).

Maybe not the orthodox way, but it works

I mean how are you saying I should be creating the db file?

@clever topaz

[clever topaz]

With the database browser program or with the postgresql command.

For your purpose I think postgresql will be overkill and require a lot of learning.

I think sqlite will be very sufficient.

[hazy mango]

I can't use sqlite because of how it works

I was originally using sqlite but I keep getting database locked errors because my bot is try to access too much (which I can't fix)

Was told that postgres doesn't lock db so should use that instead

And just tried creating the db with db browser and still not working

@clever topaz

[clever topaz]

I was originally using sqlite but I keep getting database locked errors because my bot is try to access too much (which I can't fix)
@hazy mango There is a solution to that which is much simpler than changing to postgresql.

[hazy mango]

?

[clever topaz]

Which is to extract/reall all the rows into a list at once and then not read from the DB again.

[hazy mango]

which then means I have to store it all in memory

Which is gonna cause issues because of how big it is

Can you please just help me with my current problem, which is the semaphore timeout

OSError: [WinError 121] The semaphore timeout period has expired

@clever topaz

[clever topaz]

Have you got postgresql installed? And are you familiar with the command line?

[hazy mango]

I'm just installing it, and if by command line you just mean like command prompt/bash then yes

@clever topaz

[clever topaz]

You're on Windows though, right?

[hazy mango]

yes

[clever topaz]

OK, once you've installed postgres I'll walk you through creating a database. Which DB browser are you using, BTW?

[hazy mango]

Db browser for SQLite is what it's called. I'm guessing I'm gonna have to get something else for postgres? @clever topaz

[brisk oar]

sorry for interrupt, but is it ok, if i do like this?:py def __init__(self, db_name: str, js: str): loop = asyncio.get_event_loop() self.pool = loop.run_until_complete(asyncpg.create_pool(host='localhost', port=5432, user='postgres', database=db_name, password='1234'))

[clever topaz]

@brisk oar Does it run? If so then it should be fine, though if you look at the asyncpg github page - https://github.com/MagicStack/asyncpg and https://github.com/MagicStack/asyncpg/blob/9cbca1cef9d9c40d12576e72f73d40d3d22043d6/docs/usage.rst - they show the correct way to load a database in async.

[brisk oar]

they have only one func

[clever topaz]

I'm not 100% familiar with async, but I'd await it - which will require a function, I think. So the function could be:

async def _get_pool(self):
    self.pool = await asyncpg.create_pool(host='localhost',
                                                                port=5432,
                                                                user='postgres',
                                                                database=db_name,
                                                                password='1234')

And the call that in __init__:

    def __init__(self, db_name: str, js: str):
        loop = asyncio.get_event_loop()
        loop.run_until_complete(self._get_pool())

[brisk oar]

u need to use async def

[clever topaz]

Oh yes.

But that should work.

[brisk oar]

hm, ok, i ll try

asyncpg.exceptions.ConnectionDoesNotExistError: connection was closed in the middle of operation```

  File "C:\Users\PC\PycharmProjects\untitled\venv\lib\site-packages\asyncpg\pool.py", line 468, in _get_new_connection
    con = await connection.connect(
  File "C:\Users\PC\PycharmProjects\untitled\venv\lib\site-packages\asyncpg\connection.py", line 1668, in connect
    return await connect_utils._connect(
  File "C:\Users\PC\PycharmProjects\untitled\venv\lib\site-packages\asyncpg\connect_utils.py", line 652, in _connect
    con = await _connect_addr(
  File "C:\Users\PC\PycharmProjects\untitled\venv\lib\site-packages\asyncpg\connect_utils.py", line 631, in _connect_addr
    await asyncio.wait_for(connected, timeout=timeout)
  File "C:\Users\PC\AppData\Local\Programs\Python\Python38-32\lib\asyncio\tasks.py", line 483, in wait_for
    return fut.result()```

[clever topaz]

Did your original code work?

[brisk oar]

same error

[clever topaz]

Oh wait.

There's a simpler method.

    def __init__(self, db_name: str, js: str):
        # Create a database connection pool
        self.pool = await asyncpg.create_pool(...)

(The ... is a placeholder)
This is from the asyncpg docs (https://github.com/MagicStack/asyncpg/blob/9cbca1cef9d9c40d12576e72f73d40d3d22043d6/docs/usage.rst - at the bottom of the page).

If you get the same error then it isn't the pool loading.

But that's a much simpler method anyway.

[brisk oar]

i cant make __init__ async

[clever topaz]

Then can you move that into the _get_pool function.

(As I said async isn't my strong point)

[brisk oar]

ah

wait

[clever topaz]

Wait - that is what you already have. The error must be something else.

[hazy mango]

Okay I've finished installing it @clever topaz. I now want to create a database called discord.db with a table called messages with the below 'schema'. Ideally I'd also have a default value of 0 for deleted (meaning deleted would also be not null) -- and also the content can be not null since will never be empty.

[clever topaz]

@hazy mango Which database browser?

[hazy mango]

https://discordapp.com/channels/267624335836053506/342318764227821568/692686579226902611 @clever topaz

[clever topaz]

https://sqlitebrowser.org/ ?

The name is very generic.

[hazy mango]

Yeah, that's the one

[clever topaz]

There are two ways - visual (browser - using pgadmin (4, I think)) or from the command line.

[hazy mango]

I've installed pgAdmin4

[brisk oar]

ok, it worked(there was another problem, i just didnt start db, lol)

[clever topaz]

https://www.youtube.com/watch?v=lG2Nes-wi54

[brisk oar]

btw, thanks

[clever topaz]

ok, it worked(there was another problem, i just didnt start db, lol)
@brisk oar Thought so. Your welcome.

[brisk oar]

is it ok for syntax:sql Delete from table1, table2 where id=1?

[clever topaz]

I can't say for sure, but looks OK. Perhaps try it without a commit to ensure that it does what you want.

[brisk oar]

ok

[clever topaz]

Doesn't seem to work:

sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) near ",": syntax error
[SQL: Delete from table1, table2 where id=1]

Separate statements work.

[delicate field]

Hey @clever topaz!

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

[clever topaz]

Granted I used an sqlite memory database - not postgres.

import dataset

db = dataset.connect('sqlite:///:memory:')

table = db['table1']
table.insert(dict(name='John Doe', age=37))
table.insert(dict(name='Jane Doe', age=34, gender='female'))

table = db['table2']
table.insert(dict(name='John Doe', age=37))
table.insert(dict(name='Jane Doe', age=34, gender='female'))


try:
    db.query('Delete from table1, table2 where id=1')
except:
    print('Cannot delete from both tables')
    db.query('Delete from table1 where id=1')
    db.query('Delete from table2 where id=1')


for i in db['table1']:
    print(i)

for i in db['table2']:
    print(i)

Output:

Cannot delete from both tables
OrderedDict([('id', 2), ('name', 'Jane Doe'), ('age', 34), ('gender', 'female')])
OrderedDict([('id', 2), ('name', 'Jane Doe'), ('age', 34), ('gender', 'female')])

[brisk oar]

how to fetchrow whole table in postgres?

[clever topaz]

values = await conn.fetch('''SELECT * FROM mytable''')

@hazy mango Any progress?

[hazy mango]

@clever topaz yea, it worked, thanks

[clever topaz]

You were able to connect to the database?

[viral crystal]

Hi all,

I would need your help please as I cant get thru this. I`m having 60+ milions rows in a postgress dataframe. There are tick information regading ask/bid for eur/usd, used to get me better in python and algorithmic trading.

Now what I try to make is:

1. pull different chunck size tick numbers like 5, 34, 144 etc
2. initially assign them to a df so i can visually check if its correct
3. create from those ticks OHLC ( open quotation, high quotation from all, low and close quotation)
4. upload to a different table once its ready ( this i know) for later mathematical operations

Like the below all the rows are fetched, I dont know were my limit is beeing defined ( 5, 34, 144) to be able to apply OHLC and get the ask or bid numbers out of it.
I have read multiple pages and one option is with this itersize ( default its 2000), but how i separate the rows based on the itersize number ( 5, 34, 144 etc etc) so i can apply the OHLC on those ticks?

import psycopg2
import pandas as pd
with connection.cursor (name='custom_cursor') as cursor:
cursor.itersize = interval # chunk size
query = "SELECT date, ask FROM eurusd ORDER BY date ASC"
cursor.execute(query)

 for row in cursor:
     print(row)

[brisk oar]

i mean make list of fetchrows

of whole database

p.s. i use asyncpg

[torn sphinx]

You're closing the conn on every loop but only opening it once
So how do I fix this

How do I close it after the loop is run

[minor matrix]

Hello world, what's the difference between the two queries: ```
SELECT
SUM(la_liga_goals + copa_del_rey_goals + champions_league_goals) as res
FROM
goals;

SELECT
(la_liga_goals + copa_del_rey_goals + champions_league_goals) as res
FROM
goals;```

[clever topaz]

@brisk oar Are you trying to get the rows as list? If so, then what I wrote should work.

[brisk oar]

hm, ok, thx

[clever topaz]

Try it - if not, I'll take another look.

[brisk oar]

didnt know

[clever topaz]

Yup, fetchrows runs an SQL query underlying.

@minor matrix The second gives a per row result - the first sums the results.

@minor matrix KhanAcademy's SQL editor is very useful for debugging SQL. I've made a quick script showing the difference - https://www.khanacademy.org/computer-programming/new-sql-script/5018363864301568

[minor matrix]

@clever topaz Thank you abymii! ❤️

[clever topaz]

@minor matrix You're welcome! A very interesting problem - never thought of trying that before.

[hazy mango]

Well kinda @clever topaz. I've created a server but can't connect to it still - same semaphore error

[clever topaz]

Yeah, I was going to get to that.

So you've created the server, but have you created a DB?

On the server.

[hazy mango]

Yes

I've created a db called discord.db

[clever topaz]

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

[hazy mango]

I just realised the actual connect thing seems to work, it's the asyncio thing that appears to be causing the issue - self.db = asyncio.get_event_loop().run_until_complete(asyncpg.connect(user=usr,host=hst, password=pws, database="discord.db"))

Just asyncpg.connect(...) works fine

[clever topaz]

As I said before your current method is 100% incorrect.

[hazy mango]

wdym?

@clever topaz

[clever topaz]

That there is no way you're going to be able to create a postgresql db by renaming a file prefix.

And that you cannot open a file as a postgresql database.

[hazy mango]

I'm using pgadmin

Sry I gtg, will @ you when I'm back

[torn sphinx]

You're closing the conn on every loop but only opening it once
So how would I loop it until it’s done giving info, then close it?

[clever topaz]

So how would I loop it until it’s done giving info, then close it?
@torn sphinx Just dedent this:

            await conn.commit()
            await cur.close()
            await conn.close()

So that it is outside of the for-loop. That way it doesn't happen every loop. Simple.

[torn sphinx]

huh

Makes sense

There’s another thing tho, it gives the amount of columns as the amount. Not the actual amount of warns (which is 3, but only actually posts 2).

I also format the way it posts it.

[clever topaz]

Could you please repost the code - it's quite far up and hard to find.

[torn sphinx]

Ok

    @commands.command()
    async def warnings(self, ctx):
        conn = await aiosqlite3.connect('main.db')
        cur = await conn.cursor()
        await cur.execute(f"SELECT * FROM warns WHERE guild_id = {ctx.guild.id}")
        for row in await cur.fetchall():
            await ctx.send(f"{ctx.guild} has {len(list(row))} warn(s)\n{row}")
       await conn.commit()
       await cur.close()
       await conn.close()

@torn sphinx

[clever topaz]

len(list(row)) is probably giving the values of that row (i.e. the column values).

Are you trying to count the number of rows?

[torn sphinx]

No... ohh

len(list(row)) was to give the amount of warns there are

[clever topaz]

I think what you want is:

@commands.command()
async def warnings(self, ctx):
    conn = await aiosqlite3.connect('main.db')
    cur = await conn.cursor()
    await cur.execute(f"SELECT * FROM warns WHERE guild_id = {ctx.guild.id}")

    warnings = len(cur.fetchall())
    for row in await cur.fetchall():
        await ctx.send(f"{ctx.guild} has {warnings} warn(s)\n{row}")

    await conn.commit()
    await cur.close()
    await conn.close()

Though I'm totally assuming as I've no idea what output you expect. Could you give an example of the output?

[torn sphinx]

Uhm alr

[clever topaz]

ALR?

[torn sphinx]

User Warned: [User Warned] Moderator: [Mod] Date | Time: Date, time Reason:

Something like this ^

[clever topaz]

And what is the len(...) supposed to output?

i.e. Where's the number that you want to output?

[torn sphinx]

Number of warns sooo
{ctx.guild} has {warnings} warn(s)\n{row}

[Guild] has [#] warn(s)
User Warned: [User Warned] Moderator: [Mod] Date | Time: Date, time Reason:
(Repeat this how many times needed ^)

[clever topaz]

Yeah, that's what my code does. Did you try it?

Except I may have made a mistake by using fetchrow twice.

[torn sphinx]

I would, but my PC is occupied by a family member rn.. 🙃

[clever topaz]

@commands.command()
async def warnings(self, ctx):
    conn = await aiosqlite3.connect('main.db')
    cur = await conn.cursor()
    await cur.execute(f"SELECT * FROM warns WHERE guild_id = {ctx.guild.id}")

    warnings = await cur.fetchall()
    for row in warnings:
        await ctx.send(f"{ctx.guild} has {len(warnings)} warn(s)\n{row}")

    await conn.commit()
    await cur.close()
    await conn.close()

Haha. Well it should be what you want.

[teal hull]

hello

[outer mica]

How do i create a password for sqlite db from python?

[teal hull]

¯_(ツ)_/¯

I'm new to python

[outer mica]

its fine

[rain wagon]

@outer mica Passwords for local db are useless

[outer mica]

it wont let me access

to the browser

[rain wagon]

You cannot obfuscate the password in Python in any meaningful way

browser?

Explain

You said you want to set a password for a sqlite database

[outer mica]

i mean

like it requires

[rain wagon]

stop

[outer mica]

a passphrase

[rain wagon]

Formulate a whole sentence

Explain what you are doing

[outer mica]

Basically im trying to connect a code to the sqlite browser

i opened the code in the browser and it says "Please enter the key to encrypt the database"

[rain wagon]

what browser are you talking about?

And what code did you open?

[outer mica]

Sorry i meant database not code

i created a database in python

and i want the database to be presented in the browser

[rain wagon]

Are you writing a flask application?

or django?

[outer mica]

no

[rain wagon]

DO you use a browser addon for sqlite?

I am trying to figure out what browser you mean and how that comes into play here

[outer mica]

"DB browser for SQLite"

its the app i downloaded from the website

[rain wagon]

OKay, that is a lot clearer. That app just wants you to encrypt it, however if you use sqlite in python there is absolutely no point to it

Because, as I said, there is no meaningful way to hide that password from the user

[hazy mango]

I've managed to create the database using pgAdmin, now one of the fields is going to be a string (e.g. "hello world"). For the type would I want char[] or cstring[]? @clever topaz

[outer mica]

ok thanks

[rain wagon]

@hazy mango Char is a single character

[outer mica]

ig

[rain wagon]

@hazy mango cstring probably a fixed length

So, if you know ahead of time that your strings always have a fixed length, then use cstring

if not, use text

[hazy mango]

What's the difference between char and char[] then? I assume char[] was a list of single characters? (basically what a string is)

And yea idk the length

[rain wagon]

Then just use text or varying with a max length

[hazy mango]

What does the [] after the type signify?

[rain wagon]

It is an array of that type, but char[] does not translate to strings here

Yeah it's weird, I know

[hazy mango]

Ah I see, so like char[] means ['a', 'b', 'c']?

[rain wagon]

yeah

[hazy mango]

Thanks. Also what's the 'breakpoint' for using integer vs bigint?

[rain wagon]

https://www.postgresqltutorial.com/postgresql-integer/

[hazy mango]

;3

[hazy mango]

And can I use 0 and 1 in a boolean column?

[rain wagon]

yes

You can also use yes and no

[hazy mango]

awesome, thanks

what if bigint is too small?

[rain wagon]

There is no such thing as too small

unless you mean the max negative value

in which case it flips to positive max

[hazy mango]

Na like what if I need a number that's more than 8 bytes?

[rain wagon]

Then you need a different db

or a creative use of their system

[hazy mango]

actually nvm I think it's fine. All discord IDs are less than 8 bytes...

[rain wagon]

You can break it down to scientific display

and store it as string

just as an example

but I doubt you'd need that 😄

or store it as float and a second column for the exponent

[hazy mango]

erm.. how do I change the type? It's not letting me change a integer to a boolean

[rain wagon]

drop the table, recreate it

[hazy mango]

right, yea

lol

[rain wagon]

Using databases is like being a child

you get yelled at for dropping things

[hazy mango]

Hmm

It's not letting me use 0 as a value in the boolean

(as default value)

I guess I have to put false?

[rain wagon]

I rarely use Postgres, idk tbh

[hazy mango]

Yea false seemed to work.

[rain wagon]

but according to documentation, 0 and 1 should be fine

[hazy mango]

Maybe that's when you're inserting, but doesn't work for default

[rain wagon]

oh wait, that is '0' and '1'

so as chars

not ints

[hazy mango]

ah, I see

[outer mica]

bruhhhh

i fixed the problem

lmao

sorry for that

[hazy mango]

Yea that worked, thanks for the help @rain wagon

[rain wagon]

np

[hazy mango]

    await self.bot.db.execute("""INSERT INTO messages (message_id, author_id, channel_id, content) VALUES (?,?,?,?)""", [msg.id, msg.author.id, msg.channel.id, msg.content])
```raises```
asyncpg.exceptions.PostgresSyntaxError: syntax error at or near ","```Does the syntax of this stuff change from SQLite too? (I'm normally use SQLite)

Actually I seem to remember seeing I have to use $n instead of ?

Hm... now got asyncpg.exceptions._base.InterfaceError: the server expects 4 arguments for this query, 1 was passed HINT: Check the query against the passed list of arguments.

[clever topaz]

await self.bot.db.execute("""INSERT INTO messages (message_id, author_id, channel_id, content) VALUES (?,?,?,?)""", msg.id, msg.author.id, msg.channel.id, msg.content)```

So you got the connection working with asyncpg?

[hazy mango]

yes @clever topaz

[clever topaz]

Great. So you see what I mean now about opening a file as a postgres DB?

[hazy mango]

yea, I didn't realise Postgresql worked differently to SQLite, mb sorry

[clever topaz]

No problem.

Glad you've got it working.

It's difficult to explain because the server-database architecture is very different to file-databases.

• not their technical names.

[hazy mango]

yea, I didn't realise that Postgres wasn't a file-db

Anyway, do you know what my above issue is?

[clever topaz]

Oh yeah, I posted an answer.

await self.bot.db.execute("""INSERT INTO messages (message_id, author_id, channel_id, content) VALUES (?,?,?,?)""", msg.id, msg.author.id, msg.channel.id, msg.content)```

You just have to remove the list

They need to be individual arguments.

[hazy mango]

ah, I see

Thanks

How do you commit edits? @clever topaz

Or does it automatically do that?

looks like it automatically commits

[clever topaz]

@hazy mango Not sure if it auto commits. If not then it should be a simple as self.bot.db.commit() - after all of the insertions, not each one.

[hazy mango]

Yea db.commit() raised an error

await self.bot.db.commit() AttributeError: 'Connection' object has no attribute 'commit'

I mean looking at the pgAdmin the record has been added so looks fine

[clever topaz]

When not in an explicit transaction block, any changes to the database will be applied immediately. This is also known as auto-commit.

Now this may affect performace.

Because it will be applying for every single insert.

If you aren't inserting that many (< 1000 probably) then it's not a problem.

[hazy mango]

tbh most of my inserts are only one-insert anyway

how does like cursor stuff work?

[clever topaz]

From what aspect?

[hazy mango]

cur = await self.bot.db.execute("""SELECT * FROM messages WHERE author_id=$1 AND deleted=0 ORDER BY message_id DESC LIMIT $2""", user.id, amount)
messages = await cur.fetchall()

Is that right?

[clever topaz]

That's fine.

[hazy mango]

Cool, so that's the same

[clever topaz]

As?

[hazy mango]

sqlite

Command raised an exception: OperationalError: no such table: messages```🤔

[clever topaz]

Re run the script

Does the script create the messages table?

[hazy mango]

no

It's created on pgAdmin

[clever topaz]

Does the insert still work?

[hazy mango]

Looks like the issue was because I was still using ? instead of $n

but now getting errorpy UndefinedFunctionError: operator does not exist: boolean = integer

    await self.bot.db.execute("""UPDATE messages SET deleted=$1, time_deleted=$2 WHERE message_id=$3""", '1', str(datetime.datetime.now(datetime.timezone.utc)), msg.id)```

[clever topaz]

Try deleted = false?

Or true in this case.

[hazy mango]

that's not valid python though?

Or can I do True and it will translate?

[clever topaz]

No, this is inside a string and it is valid SQL.

So it will work.

[hazy mango]

Right so you're saying set it directly rather than as an arg

[clever topaz]

Yeah

Or you could do False

That would probably work.

[hazy mango]

    await self.bot.db.execute("""UPDATE messages SET deleted=true, time_deleted=$1 WHERE message_id=$2""", str(datetime.datetime.now(datetime.timezone.utc)), msg.id)```gives same error

maybe it's the time_deleted causing the issue?

Because tbh I didn't really know what type to use there

Think I ended up doing timestamp with timezone

[clever topaz]

Check which types you have and ensure they are identical

[hazy mango]

The only one that might not match is time_deleted, because I don't know what timestamp with timezone corresponds to

[clever topaz]

Change the type of time_deleted to string

[hazy mango]

Right, yea. Or rather, text?

[clever topaz]

Yup.

[hazy mango]

Same error

[clever topaz]

https://stackoverflow.com/a/28037876/12829466

[hazy mango]

So.. """UPDATE messages (...) VALUES (..., deleted='true')"""?

nope, that didn't work

[clever topaz]

No

    await self.bot.db.execute("""UPDATE messages SET deleted is true, time_deleted=$1 WHERE message_id=$2""", str(datetime.datetime.now(datetime.timezone.utc)), msg.id)

[hazy mango]

ah, I see

asyncpg.exceptions.PostgresSyntaxError: syntax error at or near "is"```

"""UPDATE messages SET deleted is true, time_deleted=$1 WHERE message_id=$2"""```

@clever topaz

[clever topaz]

https://www.postgresql.org/docs/8.1/datatype-boolean.html

Oh wait.

You are trying to set.

[hazy mango]

?

[clever topaz]

One sec.

What is the type of deleted?

[hazy mango]

boolean

@clever topaz

[clever topaz]

https://rextester.com/live/BSP11827

I've got the syntax correct and my update works. That should work for you too.

G2G be back in ~15 mins.

[hazy mango]

It's not working

UPDATE movies SET test = true WHERE id = 1; is what you shared, UPDATE messages SET deleted=true is what I'm doing

@clever topaz

[sonic mural]

What's the best solution for storing configuration for multiple clients in a database? I was thinking using a redis server but I'm not sure if thats the best thing to use.

To elaborate, in the context of a multiple guild discord bot, each guild has some configuration specific to that server. For example, command prefix, which could be different depending on what the owner specifies.

I'm not sure what the best method for storing something like that is.

[clever topaz]

@hazy mango Same error?

[hazy mango]

yes @clever topaz

[real timber]

anyone know if it's possible to download a bucket/folder from GCP instead of a bucket? Seems to only let me download a bucket using gsutil cp -R gs://<bucket> .

ok, don't have / at the end of the path 🤦 it works now

so gs://bucket/folder not gs://bucket/folder/

[clever topaz]

@hazy mango UndefinedFunctionError: operator does not exist: boolean = integer?
You haven't set message_id to a boolean by any chance?
Try substituting WHERE message_id=$2 with just WHERE id=$2

[hazy mango]

Why 'id' not 'message_id'?

And I checked but message_id is integer in table, and integer in code

@clever topaz

[clever topaz]

Did you try id instead of message_id?

[hazy mango]

I'll try in a sec

Same error @clever topaz

[clever topaz]

Try writing the whole query without args.

Run and also post here please.

So substitute/hardcode the values in the statement

[hazy mango]

I can't hardcode it though

Because it goes through message ids

so it's not one constant value

@clever topaz

Actually might be able to do something, one sec

UPDATE messages SET deleted=true, time_deleted=test WHERE id=692817052334948464```still raises error

@clever topaz

[clever topaz]

Same error? Try:

UPDATE messages SET deleted=true, time_deleted="test" WHERE id=692817052334948464

[hazy mango]

Same error @clever topaz

this is my table schema

wait I figured it out

there was another update elsewhere 🤦

Now got this error AttributeError: 'str' object has no attribute 'fetchall' for ```py
cur = await self.bot.db.execute("""SELECT * FROM messages WHERE author_id=$1 AND deleted=false ORDER BY message_id DESC LIMIT $2""", user.id, amount)
messages = await cur.fetchall()

So apparently cur isn't what I expected it to be

print(repr(cur)) outputs 'SELECT 4'

@clever topaz

[clever topaz]

You've overwritten cur

The line above.

Change:

cur = await self.bot.db.execute("""SELECT * FROM messages WHERE author_id=$1 AND deleted=false ORDER BY message_id DESC LIMIT $2""", user.id, amount)

To:

await self.bot.db.execute("""SELECT * FROM messages WHERE author_id=$1 AND deleted=false ORDER BY message_id DESC LIMIT $2""", user.id, amount)

[hazy mango]

So it automatically assigns it to cur?

[clever topaz]

No. Basically that line assigns to cur the database's reply to your command

Which you don't need.

[hazy mango]

I do??

How else do I cur.fetchall()?

Or just messages = await self.bot.db.execute(...).fetchall()?

[clever topaz]

Can you post all of the code? It's more difficult without.

Nope.

[hazy mango]

The entire code for that section is 83 lines

                cur = await self.bot.db.execute("""SELECT * FROM messages WHERE author_id=$1 AND deleted=false ORDER BY message_id DESC LIMIT $2""", user.id, amount)
                print(repr(cur))
                messages = await cur.fetchall()

                for tup in messages:
                    message = await self.bot.get_channel(tup[2]).fetch_message(tup[0])  #discord.py code
                    await message.delete()  # discord.py code
```is the 'important' bit

And I then do for tup in messages: to access each record in messages

[clever topaz]

Don't you have a cursor already (which is what I'm trying to ascertain)?

[hazy mango]

no?

The only thing I have is self.bot.db which is a connection object

[clever topaz]

values = await conn.fetch('''SELECT * FROM mytable''')

Not sure if there is a fetchall for asyncpg.

(Never used it TBH)

[hazy mango]

Sopy messages = await self.bot.db.fetch("""SELECT * FROM messages WHERE author_id=$1 AND deleted=false ORDER BY message_id DESC LIMIT $2""", user.id, amount)

?

[clever topaz]

That's fine.

[hazy mango]

omg finally

it works

lol

[clever topaz]

Cool.

[hazy mango]

I've been working on this for 3 days lol

[clever topaz]

I'm interested to know how you fixed the "main" error before?

[hazy mango]

I had a deleted=0 in a different UPDATE in another file, that was run in background

[clever topaz]

Oh right, so the error wasn't even in that query.

[hazy mango]

yea

lol

🤦

[clever topaz]

As I said, difficult to debug without the whole script.

Anyway, glad it works now.

[hazy mango]

Thanks so much for your help ;3

And yeah I get it's not easy your end, but the entire script would be 500+ lines spread over about 6 files lol

[clever topaz]

Ah, I see. No problem, then.

Interesting problem.

[hazy mango]

thanks again :~)

[hazy mango]

@clever topaz hey, sorry to bother you, I'm trying to migrate the server to a remote server. I've installed postgresql and pgadmin but getting an error when trying to login saying invalid password for user postgres

How can I see what password was set?

[clever topaz]

Did you set a password? If not, It'll probably be the default.

Which I cannot recall.

But I assume is postgres.

https://www.liquidweb.com/kb/what-is-the-default-password-for-postgresql/

Though if you've got a postgres user then it may be that password.

@hazy mango

[hazy mango]

yea, I think I set a password without realising

So got no clue how to figure out what I set it to 🤦

[clever topaz]

There is a way to reset a PostgreSQL password.

@hazy mango https://stackoverflow.com/a/27108276/12829466

[hazy mango]

thanks

[torn sphinx]

Hello coders of python that use databases and maybe discord.py

    @commands.command()
    async def warnings(self, ctx):
        conn = await aiosqlite3.connect('main.db')
        cur = await conn.cursor()
        await cur.execute(f"SELECT * FROM warns WHERE guild_id = {ctx.guild.id}")

        warnings = await cur.fetchall()
        for row in warnings:
            await ctx.send(f"{ctx.guild} has {len(warnings)} warn(s)\n{row}")
        await conn.commit()
        await cur.close()
        await conn.close()

I have this piece of code here, I want {row} to be sent in one message along with everything behind it.

But, it sends a different posts everytime the command is used

I'm kinda hopeless rn :/

[clever topaz]

@torn sphinx What does "everything behind it" mean?

i.e Could you give an example of what the output looks like now and what it should look like.

[torn sphinx]

Red Hex has 3 warn(s)
('681298087695876108', '670740367989145610', '281226067434405889', '2020 - 03 - 24 20: 59: 07', None)
Red Hex has 3 warn(s)
('681298087695876108', '670740367989145610', '281226067434405889', '2020 - 03 - 24 20: 59: 07', None)
Red Hex has 3 warn(s)
('681298087695876108', '670740367989145610', '281226067434405889', '2020 - 03 - 24 20: 59: 07', None)

This is it rn

Red Hex has 3 warn(s)

('681298087695876108', '670740367989145610', '281226067434405889', '2020 - 03 - 24 20: 59: 07', None)
(‘681298087695876108', '670740367989145610', '281226067434405889', '2020 - 03 - 24 20: 59: 07', None)```

This is how I want it

[clever topaz]

    @commands.command()
    async def warnings(self, ctx):
        conn = await aiosqlite3.connect('main.db')
        cur = await conn.cursor()
        await cur.execute(f"SELECT * FROM warns WHERE guild_id = {ctx.guild.id}")

        warnings = await cur.fetchall()
        print(f"{ctx.guild} has {len(warnings)} warn(s)")
        for row in warnings:
            await ctx.send(f"{row}")
        await conn.commit()
        await cur.close()
        await conn.close()

[torn sphinx]

Wait, instead of print wouldn’t it be await ctx.send?

[clever topaz]

Oh sorry, yes.

[torn sphinx]

Oki, thank you!

[clever topaz]

Worked?

[torn sphinx]

👍

but now, lets say i want it format it so it appears like this:
User Warned: [User Warned] | Moderator: [Mod] | Date/Time: Date, Time
Reason:
for each warn

[clever topaz]

@torn sphinx Should be a simple matter of formatting, right?

[torn sphinx]

yeah, i just gotta figure it on my own, ill come back if I cant

wait, .format and not f""

right?

[clever topaz]

Either.

[torn sphinx]

Can I do like
x = x = x

[real timber]

I'm wondering about the workflow between local data and data stored somewhere like google storage, does anyone do this sort of thing?

[torn sphinx]

        user_id = (f"SELECT user_id FROM warns") = member.name

or```py
user_id = (f"SELECT user_id FROM warns") = ctx.member.name

[sonic mural]

Okay this is a difficult one, and I'm not really sure what is causing it.
I am using SQLAlchemy to interact with a Postgres server using ORM.

I have a model which looks like this:

class ReactionRoles(Base):
    __tablename__ = "reactionroles"

    id = Column(Integer, primary_key=True)
    guild = Column(BigInteger)
    channel = Column(BigInteger)
    msg = Column(BigInteger)
    reaction = Column(BigInteger)
    role = Column(BigInteger)

And interact with it through this (Type hints given to help)

def _addrr(self, guild: int, channel: int, msg: int, reaction: int, role: int):
    new = models.ReactionRoles(guild=guild, channel=channel, msg=msg, reaction=reaction, role=role)
    self.session.add(new)
    self.session.commit()

Which leads to this strange error:
AttributeError: 'str' object has no attribute '_contextual_connect'

Now I'm not one for pasting blocks of code, but I have scoured the internet and spent several hours debugging and can't for the life of me figure out what is causing this.

Additionally, I have the full traceback and logs available here:
https://hastebin.com/tuqexasodo.sql

[clever topaz]

@sonic mural For the sake of testing could you try replace BigInteger with Integer and see if that makes any difference?

[sonic mural]

Sure

Still returns the same error even with all set to Integer

[clever topaz]

Also, I think you're using an older version of SQLAlchemy.

Which may be the problem.

I looked through the GH source code and they removed _contextual_connect in August last year.

Perhaps try updating sqlalchemy and see if it works.

Unless you need the older version.

[sonic mural]

Let me just update it

[clever topaz]

Also, your problem isn't with the model, it's with the session/engine.

[sonic mural]

huh, that's weird. I was updating pip in my virtualenv and it failed

[clever topaz]

Using pip install -U pip?

@sonic mural https://stackoverflow.com/a/15223296/12829466

[sonic mural]

AttributeError: 'NoneType' object has no attribute 'bytes'

[clever topaz]

That's the pip error?

[sonic mural]

yep

[clever topaz]

Use python3 ....

[sonic mural]

It's on a windows installation running 3.7.6

If you want the full traceback: https://hastebin.com/zedotumiwi.coffeescript

Time to completely rebuild the virtualenv I think

[clever topaz]

https://github.com/pypa/pip/issues/7069

You can just update sqlalchemy without updating pip

(Which you're probably aware of)

[sonic mural]

easy_install -U pip fixed pip

[clever topaz]

Hmm, Windows is weird.

I though easy_install was deprecated 5 years ago.

[sonic mural]

easy install is the emergency help I broke pip command

pip list shows:
SQLAlchemy 1.3.15

[clever topaz]

Yup, that's the latest version, now.

[sonic mural]

Getting the same error

[clever topaz]

Hmm, are you sure you're running in virtualenv?

Try:

import sqlalchemy
print(sqlalchemy.__version__)

[sonic mural]

1.3.15

[clever topaz]

I'll check that, but in the meantime, could you post your session code - the error is with the engine, not the model.

[sonic mural]

sure

[clever topaz]

@sonic mural There is one more thing you can try before changing code - try installing directly from source: pip install git+https://github.com/sqlalchemy/sqlalchemy

[sonic mural]

Oooh new error:
AttributeError: 'str' object has no attribute 'connect'

Well, the _contextual part was removed

I'll check that, but in the meantime, could you post your session code - the error is with the engine, not the model.

The session code is kind of spread around the code, I have condensed the actual session part of it:

During init:

engine = create_engine(config.engine) # config.engine contains the db uri
models.init(engine) # Performs the following: Base.metadata.create_all(engine)
bot = Bot("!", config.cogs, config.engine)

Inside the Client:

def __init__(self, ..., engine):
    self.engine = engine
    self.session_maker = sessionmaker(bind=engine)

Inside each Cog:

    def __init__(self, bot):
        self.bot = bot
        self.engine = bot.engine
        self.session = bot.session_maker()

    def _addrr(self, guild, channel, msg, reaction,  role):
        new = models.ReactionRoles(guild=guild, channel=channel, msg=msg, reaction=reaction, role=role)
        self.session.add(new)
        self.session.commit()

[clever topaz]

So it has to be the engine. Try print(self.engine) above new = ...

^ Debugging like a programmer.

Haha.

[sonic mural]

ah yes, print statements

postgres://postgres:<super_secret_password>@localhost:5432/empirebot

[clever topaz]

See, it's a string.

Not an engine object.

[sonic mural]

that is an excellent point

[clever topaz]

You've passed the wrong thing somewhere.

config.engine

I see it.

bot = Bot("!", config.cogs, config.engine) should be just engine.

Simple!

[sonic mural]

god I hate that that is the solution

literal hours of frustration

[clever topaz]

I would say that 80% the mistake is the most basic thing imaginable.

[sonic mural]

print statement now:
Engine(postgres://postgres:***@localhost:5432/empirebot)

[clever topaz]

Works?

[sonic mural]

yes, thank you so much for figuring that out

[clever topaz]

No problem. You don't need the bleeding edge SQLAlchemy either, which you can remove and reinstall from pip if you like.

[sonic mural]

Yep, I'll downgrade that

[clever topaz]

Fun problem! All the best with your bot!

[sonic mural]

ty so much dude

[upbeat lily]

You could convert it to JSON or BSON and store that in the DB. But storing something like a dict into a single column breaks 1NF

So you probably want to do something like move the dict to its own table (and of course unpack it into columns)

[lavish sky]

I am restricted to working with the csv file. So its probably best to unpack and make several 'redundant' new columns or rows?

[upbeat lily]

A CSV can't really meet BCNF

its just a single table

Well, 1nf is kinda the issue. Whether or not you consider the dict to be atomic or not

I think logically it isn't, but you could probably get away with treating it as though it was

If I had that as a university assignment or something, I'd definitely split it out into its own thing. Either rows in the main table (probably not), or its own table

then carry on working up the normal forms

[lavish sky]

I will try and work it out thanks!

[hazy mango]

@clever topaz Hey, I'm having issues creating another column on my table -- it won't let me select a type.

Just always shows No results found when I press Select an item

Also idk if related but get this when I click on the database name

[clever topaz]

Yeah, adding a new column isn't as straightforward. Let me quickly search.

[hazy mango]

aight

[clever topaz]

https://www.postgresqltutorial.com/postgresql-add-column/
https://stackoverflow.com/questions/512451/how-can-i-add-a-column-to-a-postgresql-database-that-doesnt-allow-nulls
https://www.youtube.com/watch?v=sK8INfuDjFQ

[hazy mango]

thanks ;3

So say ALTER TABLE messages ADD COLUMN edit_count integer NOT NULL DEFAULT 0;? @clever topaz

yea, that seems to have worked :~)

Got another question. I want to essentially store the content of each message after each edit. I'm assuming doing type as text[] will do this, but how would I go about actually 'adding' the new content? @clever topaz

(And setting it in the first place) @clever topaz

[clever topaz]

From python.

(asyncpg2)

@hazy mango https://magicstack.github.io/asyncpg/current/usage.html - search INSERT to learn how to use insert statements.

[hazy mango]

Yea, it was the actual array syntax I wasn't sure of

But think I figured it out

Why isn't the edit_count showing to the left? (right is result of view all rows) @clever topaz

[hazy mango]

    @commands.Cog.listener()
    async def on_raw_message_edit(self, payload):
        message = payload.cached_message if payload.cached_message else await self.bot.get_channel(payload.channel_id).fetch_message(payload.message_id)  # this is all discord.py code
        await self.bot.db.execute("""UPDATE messages SET edit_count=edit_count+1, content[edit_count+1]=$1 WHERE message_id=$2""", message.content, payload.message_id)```The database always seems to be one behind on the edits. I send the original message, and that's fine. I then edit the message, nothing changes. I edit message again, and the first edit is added. I edit again, and the second edit is added, etc. @clever topaz

[tropic kayak]

can you use one table in multiple programs?

specifically mongo

[tawdry fossil]

i dont understand why i got this error.

[tropic kayak]

installation failed

[ionic pecan]

@lavish sky can you share the full error and values you inserted?

[ionic pecan]

are you sure the referenced value actually exists?

which database engine is this?

[cursive ibex]

What module would you guys suggest for an async mysql connection?

[tawny sail]

i mostly use aio-sqlalchemy

works for most dbs

its an ORM

https://pypi.org/project/sqlalchemy-aio/

@cursive ibex

[cursive ibex]

Anything particularly for MySQL?

I'm using aiomysql but their docs aren't great

[torn sphinx]

🤔 What's the best way to store/retrieve e-mails with PostgreSQL? Was looking at https://dba.stackexchange.com/questions/68266/what-is-the-best-way-to-store-an-email-address-in-postgresql but I feel like there's something better.

[rain wagon]

emails or email adresses?

and real mails, as in do you run postfix or?

[torn sphinx]

e-mail addresses, sorry about that.

I don't run postfix. Just architecting a DB.

[rain wagon]

I'd just store them as strings

so, text

or varchar with the max length

https://stackoverflow.com/questions/386294/what-is-the-maximum-length-of-a-valid-email-address

An email address must not exceed 254 characters.

so, varchar(255) (+@)

[torn sphinx]

That's what I've got already, was just curious if that's changed. 👍

[clever topaz]

@hazy mango You need to commit changes to the database. See if you can find out how to do so in the documentation first, and if not I'll have a look.

[hazy mango]

Yea I ended up figuring it out, thanks

[bold flume]

with these simple lines i get

    with conn:
        c.execute('DROP DATABASE mydb')```

`sqlite3.OperationalError: near "DATABASE": syntax error`

why?

[pure cypress]

Cause there is no such command for sqlite

If you want to drop the database just delete the sqlite database file

[bold flume]

i want to empty the db

so i drop it first and then create it

though i fixed my problem

[torn sphinx]

can someone help me make a warn system

i know nothing about databases yet

so gonna need some help

to get started

[rain wagon]

@torn sphinx https://www.tutorialspoint.com/sql/index.htm

@torn sphinx https://en.wikipedia.org/wiki/Database_normalization

[torn sphinx]

I'm in the process of making a wanr system rn

    @commands.command()
    async def warnings(self, ctx):
        conn = await aiosqlite3.connect('main.db')
        cur = await conn.cursor()
        await cur.execute(f"SELECT * FROM warns WHERE guild_id = {ctx.guild.id}")
        await ctx.send(f"{ctx.guild} has {len(warnings)} warn(s)")
        sql = ("SELECT user_id FROM warns WHERE guild_id = ?")
        val = (ctx.guild.id)
        await cur.execute(sql, val)
        user_id = await cur.fetchone()

        sql = ("SELECT mod_id FROM warns WHERE guild_id = ?")
        val = (ctx.guild.id)
        await cur.execute(sql, val)
        mod_id = await cur.fetchone()

        sql = ("SELECT datetime FROM warns WHERE guild_id = ?")
        val = (ctx.guild.id)
        await cur.execute(sql, val)
        datetime = await cur.fetchone()

        sql = ("SELECT reason FROM warns WHERE guild_id = ?")
        val = (ctx.guild.id)
        await cur.execute(sql, val)
        reason = await cur.fetchone()
        warnings = await cur.fetchall()
        for row in warnings:
            await ctx.send(f"User Warned: {user_id} | Moderator: {mod_id} | Date - Time: {datetime} | Reason: {reason}")
        await conn.commit()
        await cur.close()
        await conn.close()

This is currently what I have

So basically

I'm trying to format it, but it's not really working :/

@member_mention has (how many) warn(s)
('guild_id', 'user_id' 'mod_id', 'datetime', 'reason')

^ that shows up instead

This is also the error

discord.ext.commands.errors.CommandInvokeError: Command raised an exception: ValueError: parameters are of unsupported type```

[clever topaz]

@torn sphinx If you printed each one of the arguments you'd notice that they are all tuples containing all the data in that row. You probably want to extract one column's value from that row.

[torn sphinx]

I actually can't print them

discord.ext.commands.errors.CommandInvokeError: Command raised an exception: ValueError: parameters are of unsupported type

I get this error if I do ^

;-;

[clever topaz]

Code?

[torn sphinx]

    @commands.command()
    async def warnings(self, ctx):
        conn = await aiosqlite3.connect('main.db')
        cur = await conn.cursor()
        await cur.execute(f"SELECT * FROM warns WHERE guild_id = {ctx.guild.id}")
        await ctx.send(f"{ctx.guild} has {len(warnings)} warn(s)")
        sql = ("SELECT user_id FROM warns WHERE guild_id = ?")
        val = (ctx.guild.id)
        await cur.execute(sql, val)
        user_id = await cur.fetchone()

        sql = ("SELECT mod_id FROM warns WHERE guild_id = ?")
        val = (ctx.guild.id)
        await cur.execute(sql, val)
        mod_id = await cur.fetchone()

        sql = ("SELECT datetime FROM warns WHERE guild_id = ?")
        val = (ctx.guild.id)
        await cur.execute(sql, val)
        datetime = await cur.fetchone()

        sql = ("SELECT reason FROM warns WHERE guild_id = ?")
        val = (ctx.guild.id)
        await cur.execute(sql, val)
        reason = await cur.fetchone()
        warnings = await cur.fetchall()
        for row in warnings:
            await ctx.send(f"User Warned: {user_id} | Moderator: {mod_id} | Date - Time: {datetime} | Reason: {reason}")
        await conn.commit()
        await cur.close()
        await conn.close()

@torn sphinx

[clever topaz]

Where are the print statements that don't work?

[torn sphinx]

    @commands.command()
    async def warnings(self, ctx):
        conn = await aiosqlite3.connect('main.db')
        cur = await conn.cursor()
        await cur.execute(f"SELECT * FROM warns WHERE guild_id = {ctx.guild.id}")
        warnings = await cur.fetchall()
        await ctx.send(f"{ctx.guild} has {len(warnings)} warn(s)")
        sql = ("SELECT user_id FROM warns WHERE guild_id = ?")
        val = (ctx.guild.id)
        await cur.execute(sql, val)
        user_id = await cur.fetchone()
        print(user_id)

        sql = ("SELECT mod_id FROM warns WHERE guild_id = ?")
        val = (ctx.guild.id)
        await cur.execute(sql, val)
        mod_id = await cur.fetchone()
        print(mod_id)

        sql = ("SELECT datetime FROM warns WHERE guild_id = ?")
        val = (ctx.guild.id)
        await cur.execute(sql, val)
        datetime = await cur.fetchone()
        print(datetime)

        sql = ("SELECT reason FROM warns WHERE guild_id = ?")
        val = (ctx.guild.id)
        await cur.execute(sql, val)
        reason = await cur.fetchone()
        print(reason)
        for row in warnings:
            await ctx.send(f"User Warned: {user_id} | Moderator: {mod_id} | Date - Time: {datetime} | Reason: {reason}")
        await conn.commit()
        await cur.close()
        await conn.close()

Sorry. here

[clever topaz]

That has no output?

[clever topaz]

@torn sphinx?

[torn sphinx]

yes

[clever topaz]

@torn sphinx print should work. How are you running the script? Directly on discord? I've no idea how discord bots work.

[torn sphinx]

does anyone know hot to make a website

So basically, I execute the command (_warnings)

@torn sphinx #web-development

and then help it better with python

how*

[clever topaz]

@torn sphinx On discord itself?

[torn sphinx]

@torn sphinx I'm running it on discord through the command (defined as async def warnings) and it goes thorugh the code and cogs

No

[clever topaz]

I'm trying to think why print is causing issues. That's very weird.

[torn sphinx]

It runs on a command prompt

[clever topaz]

Right, so no output from the prints?

[torn sphinx]

no

[clever topaz]

Which line is it failing on?

[torn sphinx]

no output

doesn't say

its weird

like

[clever topaz]

Can you send a screenshot or text copy of the error?

Or is it just discord.ext.commands.errors.CommandInvokeError: Command raised an exception: ValueError: parameters are of unsupported type?

[torn sphinx]

Traceback (most recent call last):
  File "C:\Users\KTG\AppData\Local\Packages\PythonSoftwareFoundation.Python.3.8_qbz5n2kfra8p0\LocalCache\local-packages\Python38\site-packages\discord\ext\commands\bot.py", line 892, in invoke
    await ctx.command.invoke(ctx)
  File "C:\Users\KTG\AppData\Local\Packages\PythonSoftwareFoundation.Python.3.8_qbz5n2kfra8p0\LocalCache\local-packages\Python38\site-packages\discord\ext\commands\core.py", line 797, in invoke
    await injected(*ctx.args, **ctx.kwargs)
  File "C:\Users\KTG\AppData\Local\Packages\PythonSoftwareFoundation.Python.3.8_qbz5n2kfra8p0\LocalCache\local-packages\Python38\site-packages\discord\ext\commands\core.py", line 92, in wrapped
    raise CommandInvokeError(exc) from exc
discord.ext.commands.errors.CommandInvokeError: Command raised an exception: ValueError: parameters are of unsupported type

for a full thing

but discord.ext.commands.errors.CommandInvokeError: Command raised an exception: ValueError: parameters are of unsupported type is probably the only thing of importance

[clever topaz]

https://stackoverflow.com/questions/52583479/discord-ext-commands-errors-commandinvokeerror-command-raised-an-exception-unp ?

Also, does this require any parameters @commands.command()?

[torn sphinx]

Yes, It's in cogs... unless I'm understanding your question wrong

[clever topaz]

What is cogs?

[torn sphinx]

oh

so like

cogs are different components I'll say

They all contribute to the main thing

[clever topaz]

What I mean is that for the decorator, does it need a name? i.e. @commands.command('give_warnings')

[torn sphinx]

no

[clever topaz]

@commands.command(name="translate", pass_context=True)

[torn sphinx]

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

[clever topaz]

Can you try @commands.command(name="give_warnings", pass_context=True)?

[torn sphinx]

what it's defining would be the name

[clever topaz]

I see. How about pass_context?

[torn sphinx]

I'kk try

*I'll

[clever topaz]

According to this doc (https://realpython.com/how-to-make-a-discord-bot-python/), the decorator should be @bot.command(name='create-channel') or something.

[torn sphinx]

Yet the same error

[clever topaz]

Wait, if you remove the print statements does it revert to your original problem (just formatting)?

[torn sphinx]

yeah

so

I think its the variable im giving to the db info

[clever topaz]

I really don't get how the print statements are messing up the code. Unless you overwrote the print function?

But we can fix this without print anyway.

[torn sphinx]

ight

deep within the error code

I found this

    await cur.execute(sql, val)```

[clever topaz]

So your sure you didn't replace print?

Can you provide that line and a few lines around it for context?

[torn sphinx]

59        sql = ("SELECT user_id FROM warns WHERE guild_id = ?")
60        val = (ctx.guild.id)
61        await cur.execute(sql, val)
62        user_id = await cur.fetchone()
63        print(user_id)

[clever topaz]

I think the val shouldn't be a tuple. Set it to just val = ctx.guild.id. Does that get rid of the error?

[torn sphinx]

discord.ext.commands.errors.CommandInvokeError: Command raised an exception: ValueError: parameters are of unsupported type

        sql = ("SELECT reason FROM warns WHERE guild_id = ?")
        val = ctx.guild.id

What if I just put this ^ into the same execute?

[clever topaz]

In which?

But try it.

[torn sphinx]

Ight

that fixed it

kinad

*kinda

User Warned: ('670740367989145610',) | Moderator: ('281226067434405889',) | Date - Time: (None,) | Reason: (None,)

Returns

It only does it for the first warn

I think

        await cur.execute(f"SELECT reason FROM warns WHERE guild_id = {ctx.guild.id}")
        datetime = await cur.fetchone()
        print(datetime)

        await cur.execute(f"SELECT reason FROM warns WHERE guild_id = {ctx.guild.id}")
        reason = await cur.fetchone()
        print(reason)

have to be fixed now

and then turned to

        user_id = await cur.fetchall()
        mod_id = await cur.fetchall()
        datetime = await cur.fetchall()
        reason = await cur.fetchall()

wait no

that'll just have there be multiple

I need it so that it goes through each row

and goes down the list

;-;

[clever topaz]

It's a tuple

So you need to index it.

How many columns are there for user_id, say?

If there are two (hypothetical - say id and user) and you want to select user then use user_id[1].

(You'll have to select the right column for each of the fetchones you have. See what I mean?

[torn sphinx]

well

user_id is a single column

the table however, has 5

the 5th being guild_id

ah

here it is

This is kinda what I'm looking for

But it'll go through the database's table

[clever topaz]

So in the user_id case: user_id = await cur.fetchall()[0]

Does that work?

[torn sphinx]

Lemme see

[clever topaz]

Now similar things for the rest of the fields.

[torn sphinx]

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

returns with this error

cur.fetchall() <- this is the generator i think'

        await cur.execute(f"SELECT user_id FROM warns WHERE guild_id = {ctx.guild.id}")
        user_id = await cur.fetchall()[0]
        print(user_id)

        await cur.execute(f"SELECT mod_id FROM warns WHERE guild_id = {ctx.guild.id}")
        mod_id = await cur.fetchall()[0]
        print(mod_id)

        await cur.execute(f"SELECT reason FROM warns WHERE guild_id = {ctx.guild.id}")
        datetime = await cur.fetchall()[0]
        print(datetime)

        await cur.execute(f"SELECT reason FROM warns WHERE guild_id = {ctx.guild.id}")
        reason = await cur.fetchall()[0]
        print(reason)

Here's how I put it

[clever topaz]

No, it should be fetchone.

I think.

Sorry g2g, it's late.

Hope you figure it out.

[torn sphinx]

Ight, thx

[torn sphinx]

created_at = db.Column(db.TIMESTAMP(),
                       unique=False,
                       nullable=False,
                       timezone=True)

TypeError: Additional arguments should be named <dialectname>_<argument>, got 'timezone'

🤔 This is for PostgreSQL, and timezone should work with this..
https://docs.sqlalchemy.org/en/13/core/type_basics.html#sqlalchemy.types.TIMESTAMP

[torn sphinx]

⚠ User Warned: ('670740367989145610',) | Moderator: ('281226067434405889',) | Date - Time: ('2020 - 03 - 27 20: 49: 19',) | Reason: (None,)

I have this

i need to have the member name and discriminator for user warned and moderator. datetime and reason, i need the parenthesis and quotes off ;-;

[torn sphinx]

How do you open a tfrecord file???

[reef hawk]

How do I go about periodically automatically updating a database?

not updating sorry, backing up

[rain wagon]

@reef hawk Use the binlog

the binlog contains all dml statements, so it also contains all changes

another method is to use a replication slave to backup things in realtime to a second db

however, it also sends any change, so that may be only half the solution

the binlog is the best bet, if you suffer any attack, you can delete the malicious changes out and import it again

Another method is to run a dump, but with MySQL it requires the enterprise edition to do hot backups, not sure about mariadb

[steel plover]

Does anyone know a good video tutorial for postgresql?

[clever topaz]

@torn sphinx Your problem is super simple - just change that line to: await ctx.send(f"User Warned: {user_id[0]} | Moderator: {mod_id[0]} | Date - Time: {datetime[0]} | Reason: {reason[0]}")

@torn sphinx Which library? SQLAlchemy?

@agile scaffold If so, then it should
be as simple as this: https://stackoverflow.com/a/13370382/12829466

[cursive ibex]

I get this error

I'm trying to change the type from INT to BIGINT

CREATE TABLE IF NOT EXISTS users (
    user_id INT PRIMARY KEY,
    money INT 
);

CREATE TABLE IF NOT EXISTS users_channels (
    user_id INT NOT NULl,
    FOREIGN KEY (user_id)
        REFERENCES users (user_id)
        ON UPDATE CASCADE ON DELETE CASCADE,
    channel_id INT NOT NULL,
    FOREIGN KEY (channel_id)
        REFERENCES channels (channel_id)
        ON UPDATE CASCADE ON DELETE CASCADE
);

        channel_id = await self.db.execute(
            "INSERT INTO channels (channel_name, description, category) "
            "VALUES (%s, %s, %s)", (name, description, category))
        user_id = await self.db.execute(
            "INSERT INTO users (user_id) "
            "VALUES (%s)", (uid, ))
        connector_id = await self.db.execute(
            "INSERT INTO users_channels (user_id, channel_id) "
            "VALUES (%s, %s)", (uid, channel_id))
        await self.conn.commit()

Should I do this ^
or this -

        channel_id = await self.db.execute(
            "INSERT INTO channels (channel_name, description, category) "
            "VALUES (%s, %s, %s)", (name, description, category))
        await self.conn.commit()
        user_id = await self.db.execute(
            "INSERT INTO users (user_id) "
            "VALUES (%s)", (uid, ))
        await self.conn.commit()
        connector_id = await self.db.execute(
            "INSERT INTO users_channels (user_id, channel_id) "
            "VALUES (%s, %s)", (uid, channel_id))
        await self.conn.commit()

Do I commit after every query or after a bunch of them?

[clever topaz]

@cursive ibex The first.

[cursive ibex]

And will the variables still have the data?

So like I can use channel_id for the third query? @clever topaz

[clever topaz]

I don't think execute returns anything, but I'm not sure. Can you try print(channel_id) below user_id = await ...?

And see if an id is returned. If so, then yes.

@cursive ibex

[cursive ibex]

It gave an id last time, so I think it does

I'll try

worked

[clever topaz]

Yup, then you'll be able to use it.

(As you've already found, I expect)

[cursive ibex]

Yeah, thanks 🙂

[clever topaz]

@cursive ibex Just to clarify, the reason why the first is better is because each write is a transaction which the database has to prepare for and process and by doing it all in one go, the preparation has to only be done once. See (https://www.justsoftwaresolutions.co.uk/database/database_tip_use_transactions.html) for a bit more elaboration.

[cursive ibex]

Ah, makes sense. I was more worried as the auto_increment id might not return until it's committed.

[vestal geyser]

got this query right now: "UPDATE example SET amount = amount - %s WHERE name = %s AND amount >= %s RETURNING TRUE"
which removes a given amount if that name exists and if he has enough amount. I simply returned True aswell since there would be no return if the condition fails.
What would be the best way to return the amount if the condition fails?

[clever topaz]

@vestal geyser If amount is a parameter that you are passing then why would you want to get back the same value?

[vestal geyser]

I want to return the amount the user has

not the amount I try to subtract

[clever topaz]

So you want an if/else in SQL?

[vestal geyser]

yeah basically

[clever topaz]

Which database?

[vestal geyser]

postgresql

[clever topaz]

Are you updating multiple rows at a time?

Try RETURNING amount.

Apparently you can return columns (https://www.postgresql.org/docs/9.1/sql-update.html).

And that should return either the updated value or the original if the update failed.

[vestal geyser]

only one user
but that only returns if the update works

I want that inverted

[clever topaz]

If not, what does it return?

[vestal geyser]

depends, None, empty list etc

[clever topaz]

If it's only one row then you can do it simply with two statements and conditions in python.

i.e. if not update: (... select that user's amount)

Either that or an IF/ELSE block in SQL which is more complex.

[vestal geyser]

yeah I know but that should be doable with one statement, just wanted to avoid two statements

[clever topaz]

Oh I think I've found it.

The ON CONFLICT clause.

https://www.postgresql.org/docs/current/sql-insert.html#SQL-ON-CONFLICT

For how to use it: https://stackoverflow.com/a/1109198/12829466

[vestal geyser]

know about that but doesnt that only work with primary keys / unique columns

[clever topaz]

Try it and see.

ON CONFLICT (id) DO SELECT amount FROM ... WHERE id = id

Or something similar.

[clever topaz]

@vestal geyser So?

[vestal geyser]

Doesnt work, or I cant get it to work atleast^^
Aswell as If/else

[clever topaz]

Code?

Also, please give the relevant lines above and below.

@vestal geyser ?

[vestal geyser]

Im still on that statement, there are no more lines

[clever topaz]

I mean the code you are running that statement with.

[vestal geyser]

I dont really see what you take from that?
I get sql errors

[clever topaz]

To show how to turn it into an if/else.

Also you gave an example so I cannot give you the correct SQL statement.

[cursive ibex]

@clever topaz I was wrong. It always returns 1. Probably stating that it worked as True

[midnight verge]

Hello, it's more a general software engineering question but deeply related do DB here. I am using SQLAlchemy ORM to work with my DB, I have defined, say, these models

from sqlalchemy import Column, Integer
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

class User(Base):
    __tablename__ = 'users'
    id = Column(Integer, primary_key=True)

class Item(Base):
    __tablename__ = 'items'
    id = Column(Integer, primary_key=True)

I need a function to get the “next” item associated with a given user, which is determined in an arbitrary way that I can model with a DB query. How would you organize your program structure to execute that logic ? Would you add a select_next_item method to the User model (and use an imported Session object) ? Would you define a function select_next_item_for_user(user: User) -> Item in another module (services.py, utils.py or something like that, I guess) ? Would you use another pattern ?

And why that choice (that's what matters, obviously 😄) ?
Thank you

[clever topaz]

@cursive ibex Can you post the code if you'd like further assistance?

[cursive ibex]

    async def add_channel(self, uid, name, description, category):
        if not self.ascii_checks([name, description, category]):
            return 'Bad arguments.'

        await self.db.execute(
            "SELECT * FROM users_channels WHERE user_id=%s", (uid,))
        if await self.db.fetchone() is not None:
            await self.db.execute(
                "SELECT channel_name FROM channels WHERE channel_id=%s", (str(self.db.fetchone()),))
            return await self.db.fetchone()[0]

        channel_id = await self.db.execute(
            "INSERT INTO channels (channel_name, description, category) "
            "VALUES (%s, %s, %s)", (name, description, category))
        user_id = await self.db.execute(
            "INSERT INTO users (user_id) "
            "VALUES (%s)", (uid, ))
        await self.conn.commit()
        print(channel_id)
        print(user_id)
        print(uid)
        print(self.db.lastrowid)
        # connector_id = await self.db.execute(
        #     "INSERT INTO users_channels (user_id, channel_id) "
        #     "VALUES (%s, %s)", (uid, channel_id))
        await self.conn.commit()
        return 'Successful'

I need to somehow get the information I inserted back.

Or atleast, the id I inserted

[clever topaz]

@vestal geyser The simplest way (I just tried a few things) is to use an if-else in python. It's very simple. Just use if amount > ...: UPDATE else SELECT amount WHERE name = name

[cursive ibex]

Without executed a select statement

[clever topaz]

@midnight verge Could you elaborate on "next item"?

@cursive ibex Can't you just use self.db.fetchone() to get the latest entered row and the extract the id/user_id.

[cursive ibex]

You can do that in aiomysql?

I'll try

[midnight verge]

@clever topaz in my case it's actually selecting a random Item from the database that have not been already selected for that user. It's basically a database query returning an Item object depending on an User “argument”

[clever topaz]

@cursive ibex Ah found something better: https://stackoverflow.com/a/706770/12829466

[cursive ibex]

Oh, that doesn't exist in aiomysql

[midnight verge]

My question is more about the right way to organize my code, especially models and business logic related to them

[cursive ibex]

I tried lastrowid and I just got 0

[midnight verge]

Considering that business logic requires issuing a DB query

[clever topaz]

@cursive ibex Have you got a cursor?

[cursive ibex]

Yeah, thats db

[clever topaz]

@midnight verge So if the user selects item #100, say, you want to be able to select #101?

@cursive ibex Did you also try self.db.insert_id()?

[cursive ibex]

Doesn't exist in docs

[midnight verge]

@clever topaz yeah or whatever, just an item that has not been selected by that user before. But that's not really my problem

[clever topaz]

@cursive ibex Did you try it anyway - not all commands are in the docs.

[cursive ibex]

I'll try it. Problem is it's async so something like that might not work

[clever topaz]

@cursive ibex I'm not 100% with async but try awaiting that line too. (And same with lastrowid)

[cursive ibex]

k

[midnight verge]

More precisely

user: User
selected: Set[Item] = set()

while True:  # assuming infinity of `Item`s
    item = user.select_next_item()
    assert item not in selected
    selected.add(item)

My point is, should it define select_next_item as a method of User ? Or should I separate it from it ? Why ? And in what way if so ?

[clever topaz]

@midnight verge How would you track what items have been viewed - or is that your question?

From what I can see you are asking how to select the next item - if so, then yes select_next_item is fine being a function of User.

[midnight verge]

@clever topaz not that's not my question, I can track them (my actual data model is more complex than that and records previously selected items, that what just for the sake of example)

[clever topaz]

Why not?

Simplest is best, whenever possible.

[torn sphinx]

@clever topaz I'll look into it, thank you

[clever topaz]

@torn sphinx What's it - sorry, I've forgotten?

[torn sphinx]

https://discordapp.com/channels/267624335836053506/342318764227821568/693402727882948648

[clever topaz]

Ah, thanks.

[midnight verge]

if so, then yes select_next_item is fine being a function of User.
If so, would I be better off importing a Session object (to issue my DB query) in my models.py module ? That's feels like wrongly high coupling to me, how to properly deal with that ? (also assuming this Session will not be the same depending on execution environment, dev, test, staging, prod, etc.) 🤔

I am used to Django where I do not really need to explicitly deal with that stuff, I am not sure if I should try to mimic it or do otherwise

[clever topaz]

Hmm, this isn't easy to comment on without understanding the structure better. From what I see you have a User which has many Sessions (one-to-many relationship). And the User also can view many Items (one-to-many relationship, again). Is that correct?

Now you want the user to not see an Item ever again regardless (?) of Session?

[midnight verge]

@clever topaz no Session is an SQLAlchemy's Session, UoW needed to actually query the DB (https://docs.sqlalchemy.org/en/13/orm/session.html) Assume I do not have any other model than User and Item here

[clever topaz]

Also, if you could give an example of a Django model, that'll make it easier to understand.

So this is a simple form of your question - "Shall I query directly from model or use a session?"

[midnight verge]

And the User also can view many Items (one-to-many relationship, again). Is that correct?
@clever topaz not exactly, they are not directly related.

Also, if you could give an example of a Django model, that'll make it easier to understand
Probably something in the lines of

from django.db import models

class User(models.Model):
    def select_next_item(self) -> Item:
        # assume first never returns None
        return Item.objects.filter(whatever(self)).first()

class Item(models.Model):
    pass

Here UoW details are hidden behind the manager Item.objects, but that's not the case with SQLAlchemy

[clever topaz]

UoW?

[midnight verge]

Unit of Work

[clever topaz]

So this is a simple form of your question - "Shall I query directly from model or use a session?"
@midnight verge is this correct?

[midnight verge]

Not really, I always have to use a session, my question is more: is it really a good idea to couple it with a model ? If so how should I do it (Django takes advantage of managers for that, but that's feels heavy for my usecase) ?

[clever topaz]

By "couple it with a model", what do you mean?
Is this relevant at all? https://stackoverflow.com/questions/12350807/whats-the-difference-between-model-query-and-session-querymodel-in-sqlalchemy

[midnight verge]

Because in Django one does

o = Model.objects.filter(...).get(...)

But in SQLAlchemy

o = session.query(Model).filter(...).one()

[clever topaz]

Yes, I know that, but what's the alternative?

[midnight verge]

One alternative to defining a method in my model is moving it out of it to a function along those lines

def select_next_item_for_user(user: User) -> Item:
    session = Session()
    return session.query(Item).filter(whatever(user)).one()

[clever topaz]

Ah right, I see what you mean.

I don't think I'd make multiple sessions.