databases

[unborn sentinel]

Well actually...

Okay now I'm curious

[rich trout]

Things that make Bast very unhappy: spaces in table names, and caps in table names. s/ /_. s/CAPS/caps.

What were you thinking?

[unborn sentinel]

I was thinking that CONCAT() would add spaces implicitly if you didn't have any string literals but as soon as you have at least one string literal in there, it removes all implicit spaces

Yep, I'm wrong. Not sure why I thought that

[rich trout]

That's what CONCAT_WS is for

[unborn sentinel]

-nods- Right

Wouldn't work in this case since the format needs to be LastName, FirstName MiddleInitial

But yeah, I'm surprised that CONCAT_WS is still fairly new

[rich trout]

Yeah

I kind of see why, as data formatting is not something I'd expect out of a db server

[unborn sentinel]

True but it's typically faster to have the server do it

And something like that is incredibly minor, especially if you're already directly controlling it via a query

Dim you can ask your question, I'm solved. Just still musing about some SQL stuff

[rich trout]

If it was up to me, my argument against it would be that you're already having to format it to output it wherever you're using it, so why not put it there?

[unborn sentinel]

.... and she just sent me an example where the names are separate anyway sooooooo

[rich trout]

But I suppose as is you could use it for some table-table comparisons and similar

lol

[unborn sentinel]

And yeah that was my primary thought

God damn, 26 columns for this report

[rich trout]

yYikes

[unborn sentinel]

This is what happens when accountants want data

[rich trout]

happy tuple unpacking sounds

[unborn sentinel]

Yeah like a freakin' firehose

[rich trout]

lol

[unborn sentinel]

Alright, follow-up dumb question: Is there a way to SELECT a column that will have the same value in all of the rows?

Because apparently there's one particular value that isn't in the accounting program I'm pulling from, but it needs to be part of this report

[distant hazel]

SELECT * FROM table WHERE colname = "sneaky new value"

So if it's company

SELECT * FROM table WHERE company = "US"

You can also do something like
SELECT company, count(*) as count FROM table GROUP BY company

To get an idea of how many rows are in all type of that column if that makes sense

Is that what you're asking? 😬

[unborn sentinel]

Sort of

So I'm not trying to weed out the ones that don't have x value in a column, more trying to insert an additional column (one that doesn't exist in the database) and its only value it holds is a given one

[rich trout]

You can just do SELECT 'value'

I've used that one for some testing rows I believe

[unborn sentinel]

I feel dumb for not thinking of that

Would the header also just be that value?

[rich trout]

Fiddle: https://www.db-fiddle.com/f/5knrjLY5AccDWpuyLNWaYw/0

yes

[unborn sentinel]

Oh I'm currently in Microsoft SQL Server Management Studio and I've got two queries open currently. One for actually writing out the full one and another for testing

I really appreciate the help, guys. This doesn't seem like it's going to be complicated now that I know where the values I need are, but my SQL is quite rusty

[rich trout]

👍

[unborn sentinel]

Oh interesting. (No column name)

I just realized I could probably make this a Python script once I work the kinks out....

[toxic rune]

Oh, my question (I forgot)
Let's say I have a bot (user data, guild data, etc stored on a database) but I also need some place to store data which is generated from a different application (but that the bot queries), is it good to have that data in a different database? I fear I might make some mistake and mess up some day, due to the amount of tables.

[unborn sentinel]

It kind of depends. If the separate app is exclusively for the use of the bot, then it would make sense to have them in one database. Otherwise, if other things tap into the data that the app has but you want to make sure none of the bot data is accessible other than from the bot, then a separate one makes sense

Also consider expandability. If this app is something that might become a bigger thing in the future, it might make more sense to build the groundwork for the separate database now rather than having to pull all the tables out later

I'm not entirely sure if I made sense or not

[toxic rune]

Hmm I see. I'm mostly worried since the bot uses the database a lot (something I should optimize, but there's not much place for optimization here.)

[unborn sentinel]

Well what kind of database is it?

[toxic rune]

PSQL

I'm probably overthinking it though

[unborn sentinel]

And if the bot is mostly doing reads then it's not a big deal. Databases are built to take that kind of abuse

[toxic rune]

Had no issues so far even with poorly designed choices (surprisingly)

[unborn sentinel]

I'm curious, what's the other app?

[toxic rune]

Data from a game we compiled manually which I took the dumb idea of localizing it

It's mostly reads, data inserting barely happens (at least not to worry) but my worry comes that user get points per message, so I'm upsert(ing) a lot and it's a big bot. I thought maybe here updating in batches (let's say every 10 seconds) would be better than doing it every message. (There's a cooldown for it.)

[unborn sentinel]

The database itself should handle any write queuing, so it shouldn't be an issue (I think that's right anyway).

[toxic rune]

On average it's around 1-2m messages / day.

[unborn sentinel]

And if the bot is async it's not like it's going to get stuck behind the read/write

[toxic rune]

Yeah and the query is pretty simple, most if not all of them are

[unborn sentinel]

Then in my (highly limited) experience, I wouldn't worry too terribly about it. Others might have a different thought than I, but if it hasn't broken yet, then I don't think it will

The combination of async + Postgres being an absolute beast helps

[toxic rune]

I do have some longer queries that look complicated (to me, since this is my first "big" database) but they're on tables with ~1000 rows so maybe there's no worry about these, they take a few ms only.

Yeah, using discord.py and asyncpg

[unborn sentinel]

Yeah, 1k rows is fairly low impact

[toxic rune]

But these queries use pg_trgm module (which I'm not sure if it's the best idea) to find the most similar name for a game item an user passed, don't think it's going to affect much either since it's low.

[unborn sentinel]

Yeah that's getting way above my knowledge level about that kind of stuff, unfortunately

Some of the others here might have thoughts on it, though

[toxic rune]

Well if anyone is able to tell me if this is the proper approach for this I can post what I have here.

[unborn sentinel]

I'd say go ahead and post it. Usually the more info that's left the easier it is to help

[toxic rune]

I will do after lunch, I do feel like I know how to break what I made anyways.

[unborn sentinel]

Alright, got stuck again. So I need two columns, one for Home Phone Number and one for Work Number

Now, both of those are stored as one column in the table they're housed in, with a type int that is in another table that tells you what kind it is

How do I go about splitting that single column's fields into two columns

[vocal moon]

Hey im a bit confused

so there is this which is a row

but how do i get data this way

[real timber]

is it possible to use str.replace() with a list? If i have multiple things to replace and only one thing to replace them with?

or some form of list solution, ideally not a for loop, and chaining a bunch of .replace.replace.replace felt off

[mystic surge]

So i need to make over 200k changes to a database
well, over 200k
150k for one table (i have 40 tables)
What would be most effecting way
1 - Run Select Queries and make 1000s of "UPDATE Queries"
2 - Dump database and find & replace ?

[unborn sentinel]

Doing the changes directly with SQL would likely be faster than trying to process the raw data via Python

SQL is typically incredibly optimized, and even that many changes should only be a few minutes (I think that's right anyway)

Although to simplify it, you could wrap it in Python and use an ORM to make the work a bit easier

[mystic surge]

@unborn sentinel any advice / examples you could point me in the direction off ?

[mystic surge]

@unborn sentinel at the moment because data is quite straight forward.. i can easily dump table

Search for 'unique strings' and replace with new 'unique strings'
then import back in to mysql

[sturdy dust]

hey i whant to create a DataBase
Can you guys help me in sqlite3, i have made some databases but i whant this organised and capable of getting it of as a Dictionary

[restive jasper]

i made a table with a serial id as primary key with the goal of each new row i insert getting a new PK. the problem is that i want to create a new row before i know everything about its columns. how can i know which row (which PK) to update once i get the other info?

oh wow, TIL about the RETURNING clause of insert

[undone roost]

How can i connect mariaDB to my project ?

[rustic yarrow]

Anyone ever use RDF? Trying to figure out what kind of graph db to use

[crisp tundra]

How does one exactly go on to turn repeated data into numbers?

So I had a table thats like

------------------
Cat
Cat
Cat
Dog
Bird
Dog

It would turn into:

1
1
1
2
3
1

[rustic yarrow]

Use count() and group by

I would recommend checking out w3schools. Quick way to learn basics pretty fast.

[crisp tundra]

Hmm I don't want to exactly count the rows

I want to relate each element to a number

[rustic yarrow]

You are using SQL I assume?

You can create a stored procedure or function that calculates this or you can just calculate this first

Then join results as necessary to get what you want

You can't magically get the count of something as far as I know of.

[crisp tundra]

alrighty

[undone roost]

Use defaultdict initialize with int

Then put element as keys and increment the value each time it appears....in the end you will get a dictionary with elements as keys and their count as values

[rich trout]

You can do that with bare sql, group by what you want to count, and select a count()

I'm just not certain what's intended here, since it doesn't line up with anything I'd expect

would you want that last dog to be 2?

[undone roost]

Hey bast.... nice timing

I was trying to connect mariaDB

But i am getting an error no application found

I'll show you the code

[rich trout]

Let me help Neko first?

[undone roost]

Sure

[rich trout]

If they are still about?

[undone roost]

I can wait

[rich trout]

SELECT Count(DISTINCT) might also help

that would allow you to calculate, say, the number of unique names in a database

[undone roost]

Cool

[patent glen]

@crisp tundra If you just want arbitrary numbers, look at dense_rank

[crisp tundra]

Preferably in a sequence

[patent glen]

note that dense_rank only exists in sqlite from version 3.25.0, which may not ship with python (my windows python 3.7.3 is 3.21.0) but all the real major database engines have it

[crisp tundra]

Sort of how people keep repeated data from albums in numbers

im using postgres btw

[patent glen]

well then yeah look at dense_rank

it does require you to define the ordering (e.g. it'll be 1 2 3 in alphabetical "bird", "cat", "dog" order) rather than just numbering in the order they're found in

[crisp tundra]

It does what I need, thanks!

[patent glen]

sqlite doesn't have a good way to do this until 3.25 and python lags behind a little, it's not the first time i've had someone ask for something that sqlite can do but only in later versions

but dense_rank exists on all the major real databases

[crisp tundra]

here's to the first time I avoid regrets by not using sql lite ^~^

[harsh pulsar]

python 3.7 uses sqlite 3.29.0

so you should be good

In [12]: sqlite3.sqlite_version
Out[12]: '3.29.0'

what i really want is json1 support compiled in

for all the huge overload of stuff in the python stdlib, and the importance of json in serializing python objects... its really weird how json1 is just left out

fts i could understand omitting

[rustic yarrow]

json1?

[gilded narwhal]

is that 3.7.5 rc1?

im on 3.7.4 and it says 3.28.0

>>> import sqlite3
>>> sqlite3.sqlite_version
'3.28.0'
>>> 

[rustic yarrow]

Does json not work?

[harsh pulsar]

this is the conda build

im actually on 3.7.0

so its possible that anaconda re-built python against a different sqlite version

i hadnt considered that

@rustic yarrow no, the bundled sqlite3 library is compiled with json support disabled

so you have to write and read binary or text blobs, and dont have json indexing or query syntax support

[rustic yarrow]

Oh

I usually used mongo if doing json

[undone roost]

hey .....check if i made the correct changes in config file ....coz it's not working..https://paste.pydis.com/ezujasagoh.py

@rich trout

[toxic rune]

As per what I was discussing earlier yesterday, is it a better idea to INSERT / UPDATE user points in batches rather than doing it per message? For example: (I get around 4 million messages per day, not exactly the amount of updates I do to the database but should be around there.)

Have a list with the data for the UPSERT. When a user sends a message and the user is not in cooldown (stored on the bot) add the (user_id, points_to_give) to a "queue" list.
Every X amount of seconds (let's say 10) I go over every item in said list and INSERT ON CONFLICT DO UPDATE onto the database.

Or is this too much overthinking seeing I currently have no problems?

[full jungle]

is it a better idea to INSERT / UPDATE user points in batches rather than doing it per message?
I'd say it depends on if the messages in each potential batch were coming in at around the same time and being written to the same tables. Batching is generally worthwhile when the main bottleneck is the locks and there's a high degree of concurrency.

Or is this too much overthinking seeing I currently have no problems?
I don't think this would be considering overthinking, you're trying to optimize the performance of the database.

[toxic rune]

@full jungle The data is written always to the same table and it's the module of the bot that would be doing the most writes to that table, while there are other modules in the same bot that will be writing / updating data to that table it's almost never / depending on user command input rather than just when sending a message.

[full jungle]

The data is written always to the same table and it's the module of the bot that would be doing the most writes to that table, while there are other modules in the same bot that will be writing / updating data to that table
@toxic rune In that case, batching the insertions and updates seems like it would be worthwhile. I would highly recommend running some benchmark tests before changing it in production though, to assess the performance difference and ensure there's no unintended consequences.

[vast zodiac]

Quick question about data redundancy. I have a DB for NBA basketball games, and I have a table called Game where one column is home_score and away_score. These are the final scores. I also have a table called TeamGameScore, where it has each team's score for every quarter for every game. Is it redundant to have the final score in the Game table when I can just sum up the quarter scores for a specific game for a specific team?

If this question isn't clear let me know.

[pure cypress]

@vast zodiac I would say that yes, it is redundant to have such a column. This kind of thing can especially be a problem if one of the columns changes and suddenly your total is wrong (though in your case, this is unlikely to happen due to being historical data).

In general, that doesn't necessarily mean you should do it

exceptions can be made

like if its expensive to calculate it

In this case it isn't

[vast zodiac]

Alright that's what I was thinking. Thanks for the advice @pure cypress

[normal rampart]

Anyone know if multiprocessing Queue together with one single thread handling all the sqlite DB related stuff would actually be better than simply using the sqlite with (the built in) locks when using it in single-thread mode (which is the default, and basically requires me to instantiate the DB call and connect and close each time any task is done)

[harsh pulsar]

The latter will block your main thread

The former wont

But the former is harder to debug, handle errors, etc

[normal rampart]

@harsh pulsar the latter doesn't block my main thread per se. Since I have multiple threads doing different things. Only sometimes those threads need to query the db.

Any noticeable speed /latency differences?

[harsh pulsar]

im not sure what your question is then

[normal rampart]

Each thread makes its own connections and close

Rather than one single thread opening one connection leaving it open and having class functions, more elegant in a way

[harsh pulsar]

oh

eh, less overhead theoretically from making new connections?

[normal rampart]

Currently even to read data, connect and close.

Memory overhead only I assume?

Rather than latency

[harsh pulsar]

it takes time to open up a new connection

but not much

[normal rampart]

Would that be faster than multiprocessing queue put and gets? Then one single open connection

[harsh pulsar]

probably irrelevant

doing it in a queue w/ a single thread means you have a better guarantee on the order of query execution

and less having to mess with locking the database

its literally FIFO

[normal rampart]

I see

[harsh pulsar]

as opposed to a free-for-all where you're relying on sqlite locking to do the right thing

[normal rampart]

Locking wise there isn't much I need to do other than have it have a decent timeout on sqlite3.connect()

So no speed benefits I guess

Is there no other db formats which is deployable with no installation (ie freezable) and portable, yet support multiple simultaneous read writes?

Afaik everything requires an install. Postgre etc

[harsh pulsar]

what are you actually trying to do

sqlite has several locking modes

as does the python sqlite module

[normal rampart]

basically something that supports multiple writes with orders being important, and doesnt lock hence no latency

^ might be asking something stupid sorry 😦

[harsh pulsar]

what are you actually trying to do

do you need async here?

[normal rampart]

nope

[harsh pulsar]

multiple writes with order being important afaik is just writing sequentially

i could be wrong

but

you asked about a queue w/ a single database thread, i said its probably a better idea

[normal rampart]

basically i use sqlite atm, but i want to reduce latency as much as possible, hence why i thought maybe the locking is bad

[harsh pulsar]

reduce latency
but what are you doing and why do you need to reduce latency this way

[normal rampart]

sqlite3 isnt exactly going to be great if theres tons of data being written onto it all the time

[harsh pulsar]

have you benchmarked your application

[normal rampart]

i have a trading program

[harsh pulsar]

sqlite3 writes are stupid fast

[normal rampart]

so yes latency is super important when it needs to scale up high

[harsh pulsar]

i mean like

do you need redis

and another process pulling stuff off the redis queue into a database?

[normal rampart]

like for backtesting live data of orders, tons of writes per second, which would be bad when it locks all the time

[harsh pulsar]

from the python side at least, this should partly answer your question

When a database is accessed by multiple connections, and one of the processes modifies the database, the SQLite database is locked until that transaction is committed. The timeout parameter specifies how long the connection should wait for the lock to go away until raising an exception. The default for the timeout parameter is 5.0 (five seconds).

[normal rampart]

well sqlite in memory has no persistence

at least for python

[harsh pulsar]

youre using sqlite in memory?

[normal rampart]

i have used both before

in memory for some of my apps, write to storage for others

[harsh pulsar]

so what are you using for this application

[normal rampart]

this one is write to storage

but i plan to add live data fed in via live websockets

potentially up to 30 connections etc feeding live trades from an (or multiple) exchange

[harsh pulsar]

yeah i mean... if you've benchmarked it and you've found that sqlite3 is in fact the bottleneck

and you dont think async will help

then maybe try something like redis

[normal rampart]

i assume redis is basically an in memory DB with persistence options?

[harsh pulsar]

https://redis.io

how many trades per second are we talking

if you're doing this with python, you'll also probably want to exercise some control over how python manages transactions: https://docs.python.org/3/library/sqlite3.html#sqlite3-controlling-transactions

if this is really high volume, there are purpose built streaming databases

i have no experience with them however

more technical stuff you might want to read through

https://sqlite.org/lockingv3.html
https://sqlite.org/isolation.html

[normal rampart]

redis requires an install still i think

Thanks, had a look at those before, but didnt really come of confident with sqlite handling at peak of thousands of writes per second to disk from multiple threads

i guess i could use seperate sqlite database for each function, since i believe the locking is on a file level (sadly not on a table level)

[undone roost]

hey,i tried to link mariaDB to my project....but no tables are created when i ran the app.https://paste.pydis.com/ihotaxukit.py this is my config file ......https://paste.pydis.com/uvowajobuw.py... and this is my app file...please check if i am doing anything wrong

corrected link

wait...idk how but it started working

thank you for your wishes ..love you all

[undone roost]

hey, so i made a few changes in one model and now when i run upgrade command it gives error .....table already exists.......how do i update my model without downgrading the migration?

[void otter]

What orm are you using @undone roost

[undone roost]

sqlalchemy

[undone roost]

@void otter

[void otter]

db.create_all()

[toxic rune]

I was told to post it here or on off-topic, thought maybe it'd be better here. It's mostly a design / best approach which involves both Python and PostgreSQL.

I currently have the following table which holds user data:

+----+--------------------+--------+--------+--------+
| id | user_id            | locale | prefix | points |
+----+--------------------+--------+--------+--------+
| 1  | 123456790123456789 | en-US  | !      | 0      |
+----+--------------------+--------+--------+--------+

Currently the bot calls the function that handles the command processing on the on_message that's on the bot. Then the on_message inside one of my Cogs will execute a query against this database and either insert or update data to it.
I have multiple commands that rely on this table, the problem is when I have no user data on the table and the user executes a command that require to select / update data from this table, because it will fail 100% due to it not existing yet (because the process is Bot on_message -> bot.process_commands then the Cog on_message -> INSERT / UPDATE)

I have thought only one solution: UPSERT on every command that uses this table, the table has default values so it's not an issue. But it would look kind of ugly to do this on a few commands.

What would be a proper approach regarding this? If I need to post this somewhere else let me know since it covers stuff that can go on two different channels.

Though maybe it'd be better if I bring it to a site like SO.

[wind pelican]

@toxic rune there are prob two main ways to handle this:

1. treat each command/event as a 'session' and at the start retrieve/create the user using a consistent interface
2. have a db/model handler that translates between discord users and users in your db, the first time you request a user thats not in the db have it create the entry. then any operation on that user object that you get will succeed

[toxic rune]

@wind pelican Wouldn't 1) be most likely what I'm currently doing? Let's say:

@commands.command(name='prefix')
async def prefix(self, ctx, *, new_prefix):
    await self.bot.database.execute('INSERT INTO User (user_id, prefix) VALUES ($1, $2) ON CONFLICT (user_id) DO UPDATE SET prefix = $2', ctx.author.id, new_prefix)
    # ...
   
# Anoher module... 
@commands.command(name='locale')
async def locale(self, ctx, *, new_locale):
    await self.bot.database.execute('INSERT INTO User (user_id, locale) VALUES ($1, $2) ON CONFLICT (user_id) O UPDATE SET locale = $2', ctx.author.id, new_locale)
    # ...

Or maybe something like this for 2)? (I wrote this fast and didn't test it, so might not work but the idea is there.)

# {user_id: User()} -> LRUCache, or something else.
self._user_cache = {}

async def fetch_user(self, user_id):
    user_data = await self.bot.database.fetchrow('SELECT * FROM User WHERE user_id = $1', user_id)
    if not user_data:
        user_data = await self.bot.database.execute('INSERT INTO User (user_id) VALUES ($1)', user_id)

    return User(user_data)
    
class User(object):
    def __init__(self, bot, *args, **kwargs):
        self.db = bot.database
        self.user_id = kwargs.get('user_id')
        self.locale = kwargs.get('locale')
        self.prefix = kwargs.get('prefix')
        # do not cache points?
    
    # Have functions to set / get locale, prefix, points... In the case of points always querying the database while locale and prefix using cached data unless setting in which case I change the value here and on the database.

I took into consideration having some kind of database function to deal with this but maybe that's overkill and might not look that nice either.

[wind pelican]

Yeah that looks about right. For 1) I would put it in a function so you don't have to copy-paste it and since update will prob be more common you might want to use a transaction instead of failing on insert

[toxic rune]

@wind pelican I feel like the second approach looks cleaner in this case (I have other functions but separate tables/module that have the queries inside them like 1) but this is not something that would happen in them)
How would the transaction look like? I'm not understanding why it'd be useful in this specific case.

[wind pelican]

you can have multiple sql statements that operate as a group
in this case you should be able to use one to ensure that an update/insert that are after but associated with a select cant be interrupted by a different insert
it likely depends on the db itself some but you should be able to simplify your code a bit if you can figure out what guarantees it can give your transactions

for example in the default mode for postgresql https://www.postgresql.org/docs/9.1/transaction-iso.html it looks like you could have a single transaction check if the user exists and then perform an operation depending on if it already exists or not

if all attempts to mess with the user use this same transaction code then you can ensure that the db will force them to execute an entire transaction in order instead of letting them overlap

in this simple case 'upsert' is prob fine for both 1) and 2)
if you structure your code so that 'get/create user' is always the first thing your events/commands do then for the rest of the event/command operate on the assumption that the user exists in the db then this should be pretty clean

i think your 2 example is a pretty clean way to do it and it resembles how many ORM's present database objects.

[toxic rune]

Sorry for the late reply. @wind pelican (I forgot to mention I'm working with PostgreSQL) So basically something like a function which all the commands use that use the same transaction code? I.e.

async def update_user(self, user_id, query, data):
    async with self.bot.database.transaction():
        # ???
        # do 2nd query?
        
async def prefix(self, ctx, *, new_prefix):
    await self.bot.update_user(ctx.author.id, 'UPDATE User SET prefix = $2 WHERE prefix = $1', (ctx.author.id, new_prefix))
    # ...
   
# Another module... 
@commands.command(name='locale')
async def locale(self, ctx, *, new_locale):
    await self.bot.update_user(ctx.author.id, 'UPDATE User SET locale = $2 WHERE user_id = $1', (ctx.author.id, new_locale))
    # ...

But yeah the example 2 would work like that, it's the first thing that would be run. I do think it could have some concurrency issues because of what I mentioned earlier.
Since if it's a normal message that doesn't run a command that's fine, but if it's one that runs a command and fetch an user and for some reason the fetch that happens when a message is made is not done (to give points) and is done after the one inside the command, it'd raise an exception if there's no user data (maybe mitigated by the ON CONFLICT, though) Sadly didn't have the time to test this all so it's coming from the top of my mind.

[fluid lantern]

?

SELECT * FROM "some_table" WHERE "some_table"."col3_name" = 5

No, it just says "give me whatever columns are there". You could also specify the column names to retrieve, in case you don't care about some of them:

SELECT
    id, "name", "another_column_name"
FROM
    some_table AS t
WHERE
    t.col3_name = 5

[undone roost]

hey, so i ran my app and it created all tables successfully then i entered data into it ...all good.....but then i made some changes in a model and tried migrate and upgrade commands ...migrate says target db not up to date and upgrade says 'table already exists'.....how to make changes in models?

[wind pelican]

@toxic rune with your example you wouldnt have any sql in prefix nor locale, it would all be in update_user
you would want to have a group of functions that handle the db that are all built to either use a transaction that starts with a check to see if the user is in the db, or that uses on conflict
then you just have to document that these functions are 'safe' to use as the first/only database operation in a command/event
as long as you always use one of these 'safe' functions you shouldnt have any concurrency issues

basically the flow would be something like

async def increment_points(self, user_id):
    """Query safe to use as first/only query"""
    await self.bot.database.query("""
INSERT INTO User (user_id, points) 
VALUES ($1, 1) 
ON CONFLICT (user_id) 
DO UPDATE SET points = User.points + 1
""")

@commands.command(name='point')
async def point(self, ctx, *):
    await self.increment_points(ctx.author.id)
    # no matter what there is now an entry in the db for that user and any further operations in this function can assume thats true

for more complicated queries you might use a transaction in your db helper function instead of just running a single atomic query via upsert

either way though you would want your first db operation in a command to always be one that uses upsert/a transaction to create the user
any db operation after that in the same command can just assume the user exists

[toxic rune]

Oh, it makes sense. I used to do something similar to this in an older version but made the switch to have the queries inside the actual commands due to mostly keeping it organized since it's not many queries and they're pretty simple overall, but had this race condition 2 - 3 times in like 9 months but I didn't like it so tried to approach this differently.
I do think I might be going for the User() object approach simply because it would also help when SELECT values as the User object would be created before getting the values from the database so I won't run into any cases where I get no result. (Yeah it can be solved by an UPSERT and RETURNING too I guess.)

Thanks @wind pelican!

[icy jungle]

Anybody here use restdb.io?

[vocal moon]

I have this code

@bot.command()
@commands.has_any_role("[DD] Department CO/XO", "[DBC] Deputy Border Chief", "[BC] Border Chief", "[BA] Board of Advisors", "[DBD] Deputy Commissioner", "[C] Commissioner")
async def info(ctx, name):
    c.execute(f"SELECT * FROM this where roblox='{name}'")
    data_a = c.fetchall()
    roblox = (data_a[0])
    call = (data_a[1]) # This part is giving me errors
    await ctx.send(f""">>> **{roblox} FILE**
    *Agent Username - {roblox}*
    *Callsign - {call}*""")

The error is IndexError: list index out of range.

I also want to fetch columns but im not sure how to do that does anyone know?

[wind pelican]

i think fetchall is a list of rows, so first row first column should be data_a[0][0]

if it only returned one row then data_a[1] doesnt exist

[torn sphinx]

i have an error related in #discord-bots

[vocal moon]

I dont understand @wind pelican

[wind pelican]

fetchall doesnt return a single list with columns as the content

it returns a list of lists eg
data_a[0][0] is first row, first column
data_a[0][1] is first row, second column
data_a[1][1] is second row, second column

[vocal moon]

oh

oooohhhhh

so i want [0][1] right?

[wind pelican]

yea for call

[vocal moon]

no but

wait no yeh

[wind pelican]

and [0][0] for roblox

[vocal moon]

that works

yeh

tysm

[wind pelican]

yw

[undone roost]

#repost....hey, so i ran my app and it created all tables successfully then i entered data into it ...all good.....but then i made some changes in a model and tried migrate and upgrade commands ...migrate says target db not up to date and upgrade says 'table already exists'.....how to make changes in models?

[icy badge]

quick question from an sql beginnerSELECT PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY duration) AS twentyfive, PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY duration) AS fifty, PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY duration) AS seventyfive FROM `bigquery-public-data.london_bicycles.cycle_hire`;

i'm trying to get the 25th, 50th and 75th percentiles of a column named "duration" using the bigquery public database on london bicycles
the code above is what i've tried, but it's returning an error: Syntax error: Unexpected keyword WITHIN at [2:23]
wondering if anyone has some expertise on SQL on why Within Group is causing the syntax error

[ornate isle]

@undone roost using what framework/migration system?

[undone roost]

@ornate isle using migrate and upgrade command in command prompt

flask

sqlalchemy

MariaDB

[ornate isle]

@undone roost did you ever run db.create_all() or do any sort of modification to the database outside of your migrations? That’ll mess it up. Also silly things like giving your table or field names something other than lowercase names. Some little gotchas like that. Aside from that, I am not positive, and I don’t know that much about MariaDB. Assuming this isn’t some app in production with a bunch of data or something, the easiest thing to do to just get it back to where it belongs is kill the db and recreate it with the migration commands

But the error about not being up to date implies the db itself is out of sync, like it were modified elsewhere or it’s pointing at a different database or something

Or if you have your Base model set to declarative_base() instead of db.Model or something

Alembic is rock-solid so it’s likely something silly like one of those

The sync error implies your alembic_version table is messed up though or something

[undone roost]

db.create_all() runs on starting my app i think...it's in my application entry point...manage.py....should i remove it now since the tables are created?

and i modified a few models.......all table names are lowercase names

i am using db.model only

@ornate isle

it might have anything to do with development or production server in config file?

[ornate isle]

@undone roost no, remove it from manage.py, you never need it there to begin with if using flask-migrate

[undone roost]

Ohh

[ornate isle]

And the class names should still be capitalized just if you are using __table_name__ = ‘sometable’ that should be lowercase

[undone roost]

Upgrade still says ...table already exists....even though i added....extend existing as True

There was one error

One table name capitalized

What should i do now ?

And it was the same table as in error

[ornate isle]

Is this something in development? You should really just kill the db and start fresh not running the create command

[undone roost]

I am creating the app only

In middle

How to kill the db properly ?

I can do it at this point

But what if the same problem comes again

Upon changing models

Tell me how to kill db first

[ornate isle]

During dev I use a test db and fully create it and recreate it several times a day. This is common if not in production to be able to test migrations and things

[undone roost]

How to see whether i am in dev or prod ?

[ornate isle]

Especially if you broke something in the migration system which you may have done

[undone roost]

Debug mode?

[ornate isle]

No I dont mean the mode

I mean you and your app haha

[undone roost]

How to see if it's dev or production?

[ornate isle]

If nobody is using the app and it isn’t live somewhere and the data isn’t super important there’s no harm in killing It and starting fresh

I’m not talking about the mode

[undone roost]

Ohh

Okay👍

Gotcha

How to kill it ?

Delete db from Maria DB?

[ornate isle]

Sure or make a second one in MariaDB and try using that

[undone roost]

And where should i put create all method if not at app entry point

Yeah i can make a new one

[ornate isle]

Look at the flask-migrate docs, not the flask-sqlalchemy docs for that

You don’t need it manually anywhere

[undone roost]

How to create it first time then?

[ornate isle]

Flask migrate will do it for you when calling manage.py db init

[undone roost]

And what if i add new models

Ohh

Kk

Gotcha 👍

[ornate isle]

And migrate and upgrade

[undone roost]

Cool thanks

[ornate isle]

It will create everything it needs the first time and from then on only make changes

[undone roost]

So just migrate will create new tables...right ?

[ornate isle]

You don’t create it yourself and you actually should not, doing any changes to the db (including creating tables) outside the migration system will break it. You let it do everything

Your first migrate and upgrade should create everything

[undone roost]

Great

That was a big help ..thanks man🙂

[tacit acorn]

I'm wondering how to add thousands of entries in a list to a DB in a reasonable amount of time while also not having duplicates
I'm using Pony with many-to-many relations. I'm willing to learn and move to pure SQL, just not sure how. But as it is, its taking over a day just to add 12k DB entries.

[rich trout]

That certainly does not sound right

Are they 12k items, each with a many-many relationship to every other?

[tacit acorn]

12k new DB entries

[rich trout]

I've used pony previously, and I successfully added several hundred in less than a second

[tacit acorn]

its probably because im doing alot of duplicate checking

[rich trout]

Are you using unique=True?

You can defined fields as unique, which would enforce no-duplicates

[tacit acorn]

example of the code

[rich trout]

Ah

yeah, you'd want files, resoluitions, and hashes to be unique=true, then you wouldn't have to do any of that checking

[tacit acorn]

but its in the checking if the many to many link is there for that

[rich trout]

I feel like it's already determining that

do a small scale test to see if it allows duplicates

[tacit acorn]

ya i should
question though, ive been questioning what i need and if im over complicating it, but im wondering is it a pony thing to have the intermediary table for all many to many links or all DB's .. im new to db

[rich trout]

The name of a relationship is set, and sets only allow unique pairs

[tacit acorn]

so just try to add and if it errors move on?

[rich trout]

yes

[tacit acorn]

ok thank you

[rich trout]

I think what was happening, was to perform not in on the attributes, it was individually loading every single element in that list from the database

[tacit acorn]

right
any way to maybe load the DB into ram then dump back to disk? the file so far is under a gig so could hold it in ram

[rich trout]

Yes, you could create an in memory sqlite database, then dump it to disk via grabbing the resulting sql

database.iterdump() gives the sql commands line by line, so you'd just have to pass them into an execute() method

But with your loading method, if its not basically as fast, I would suspect something is wrong

[tacit acorn]

ya im changing it now to remove the checks and just push the add's

[rich trout]

I would not expect blistering speed, since sqlite is not a massively concurrent database manager like others. However, it does support the order of thousands of queries a second. Writes are slower, and mixing writes and reads can get painful because determining restrictions and constraints is not as easy as it might seem.

sqlite itself can burn tens of thousands of bare writes per second, with just normal data insertion

[tacit acorn]

what would you recommend for a free DB server?

[rich trout]

I've used mysql, postgres, and mariadb personally

I would suggest sticking with sqlite and figuring out what the bottleneck happens to be

You don't seem to have a need for more than sqlite can give you, and the others require managing a separate database application

pragma journal_mode off may also give you a large speedup, at the loss of crash-safety

[tacit acorn]

how would i do that with pony?

[rich trout]

db.execute("pragma journal_mode off")

[tacit acorn]

at the start or every transaction?

[hearty latch]

heyo, I'm having a bit of trouble working with sqlite, sqlite3.Row objects, and iterations, and I don't quite grasp what I need to change to get the result I'm looking for

[rich trout]

By default, sqlite and other database's maintain a "journal" of all transactions applied, allowing them to undo them if they fail midway, and providing them with a failure-safe state if power is lost mid way

that command is config, and applies to the entire connection, so you should only have to do it once

it disables the journal, which means that the entire management work that must be done to maintain the journal is no longer done, but also means if there's a crash the database is probably hosed

[tacit acorn]

ok ty

[rich trout]

..I read a little further into it

don't use it

use memory instead of off

off causes constraint failures to corrupt the database ;-;

I feel like there's got to be a better way to do this

[tacit acorn]

ya ill look into it

Deadbear seams to have a question

[hearty latch]

yeah sorry, I decided it might be more useful to just push my code to the repo first and link it instead of trying to abstract it for example purposes

[rich trout]

No worries

[hearty latch]

https://github.com/atbrandt/DeadbearBot/blob/database/lib/db.py
this is the database code for a discord bot I'm working on
https://github.com/atbrandt/DeadbearBot/blob/database/lib/main.py#L364
this is the particular section of iteration that I'm having trouble with

[rich trout]

oh

your mistakes is that for is a foreach loop

I think

yes

sqlite.fetchall() returns a list

[hearty latch]

the short version is that the current code returns an error that list indices must be numbers instead of row objects

[rich trout]

so each item in rrlist is a row

there's no need to index into rrlist again

[hearty latch]

hm, not sure I understand.... frankly sqlite3.Row has been difficult for me to get a grip on how to interact with properly

[rich trout]

You're doing this, in your code:

def a():
    return ["a", "b", "c"]

items = a()
for item in items:
    items[item].doathing()

It's failing because items is a list, and indexing into a list with the item you got from a for loop makes no sense

[hearty latch]

hmm... why does printing the list return a list of memory addresses rather than the actual data?

[rich trout]

because the rows are objects, and not the bare data

You can access it's properties by name, say, item.name, item.columname, etc

You can convert it into a tuple with tuple(row) and that should serve easily for printing

See here: https://docs.python.org/3/library/sqlite3.html#sqlite3.Row

>>> r = c.fetchone()
>>> type(r)
<class 'sqlite3.Row'>
>>> tuple(r)
('2006-01-05', 'BUY', 'RHAT', 100.0, 35.14)
>>> len(r)
5
>>> r[2]
'RHAT'
>>> r.keys()
['date', 'trans', 'symbol', 'qty', 'price']
>>> r['qty']
100.0
>>> for member in r:
...     print(member)
...
2006-01-05
BUY
RHAT
100.0
35.14

[hearty latch]

I think this is where I keep getting confused, because I've tried some of the examples in the docs and I seem to not get the same results, e.g. I just tried changing it to print a tuple of the object and it still gives me a tuple of memory addresses

rather than what appears to be just a tuple of the data in the memory addresses a-la the doc examples

[rich trout]

if you were doing tuple(items) instead of tuple(item) that would be the case

[hearty latch]

I think it just clicked what I was doing wrong

a-doy, I just needed to drop the reference to the list because I was already iterating over an item in the list, so i[indice] instead of list[i][indice]

thanks for the help, that fixed it

[rich trout]

👍

[undone roost]

Hey, i am making a theatre with various auditoriums . Each audi has a seat map so i need to initialize another table in which each seat has different properties. I created a function to initialize seat map by inputting rows and columns(using two nested for loops), but now there are other stuff such as slot and date and there will be so many nested loops if i continue the same.....even though an audi has around a 100 seats which means 100*4(slots)*d(dates)...will be a lot of entries ....is there any other way to initialize the seat table efficiently

[rich trout]

The efficient way is.. don't

Or, to be precise, do not initialize every slot, only the full ones

You restrict your possible theatres to theatres that have been provided, attached auditoriums to those provided, and for each seat require a valid date, slot, and auditorium

If all your seats are full, then you do unfortunately have to save every single slots worth of data, because it is indeed that many slots of unique pieces of data

but if seats are not full, then there is no reason to fill empty seats

[undone roost]

So when i get data from the user then only i initiate the seat table ?

[rich trout]

The terms are "create a schema"

you create your restrictions on your data, and then you add entries that obey the restrictions

for example, you restrict each auditorium to having a valid theatre, and each entry to having a valid slot and seat

You restrict valid seats depending on auditorium type, you could say, or just limit the total number per auditorium

[undone roost]

Yes..i have done that...you have to provide theatre id to create an auditorium

[rich trout]

Right, now you need to define your seats, and slots separately

Seats are specific to auditoriums, and perhaps slots are also specific to auditoriums

but slots are not constrained to seats

reservations are constrained to seats and slots

[undone roost]

https://paste.pydis.com/hazacudini.py

this is my current model

[rich trout]

Right

[undone roost]

Do i need different table for slots ?

[rich trout]

Yes

Although at this point it is important to know what data you are making a model for

The trick is to figure out the model that fits your data most accurately through constraints and relations

Each theatre has audis, each audi has seats, is simple enough

but each movie, does each movie have a slot? How do slots relate to each other?

Seats having slots does not make sense, as a seat is not time-dependent

[undone roost]

Hmmm

[rich trout]

That introduces complexity: Seats are not dependent on slots, and a time slot is not dependent on seating

something can be dependent on a slot in a particular seat

but in that case, that is when you use a composite key

[undone roost]

Let me read

[rich trout]

ok

[undone roost]

What is a composite key?

[rich trout]

A composite key is a database structure, used to consider multiple independent columns together

[undone roost]

I will read about it more

[rich trout]

yes

[undone roost]

And yes it's really complex

[rich trout]

If you have a unique composite key composed of seat and slot, then you can ensure that any combination of the two is unique, which allows you to "map" all seat-slot combinations without filling them, and without duplicates

That is to say, a reservation for a movie would be dependent on a seat and slot, or a seat and movie, and movies would be dependent on an auditorium and a slot

that way there is no direct movie-seat relation, and if you use seat/slot, then you don't have the possibility of breaking a constraint by accident

you would be able to get the movie for a reservation, by selecting through the reservations slot into movies, and the reservations seat into audi, into movies

Say, SELECT reservation.name FROM reservations WHERE reservation.slot, reservation.audi == (SELECT movie.slot, movie.audi from movies where movie.id == $1)

That syntax isn't quite right but shows the power of the data model

[undone roost]

All over my head

Need time to grasp the problem first

[rich trout]

nods

[undone roost]

Let me read about composite key first

[rich trout]

ok

[undone roost]

Got composite key

So i can use columns like seat,slot,movie and audi

But all these columns are in different tables

A new model is needed or there is a select method from different tables

[rich trout]

SELECT reservation.name WHERE reservation.seat IN 
    (SELECT seat.id WHERE seat.audi IN 
        (SELECT movie.audi FROM movies WHERE movie.id == $1)) 
AND reservation.slot IN 
    (SELECT movie.slot FROM movies WHERE movie.id == $1)

[undone roost]

I need sqlalchemy query

Oh damn...query in sqlalchemy can be used on a class only

[rich trout]

Well, yes

You're querying for something, so in this case it woould be something like

[undone roost]

Can i make a new table of columns from other tables ?

[rich trout]

Depends on what you mean by that

but the SQL I posted will directly translate into an sqlalchemy query

stmt = config.Session.query(Person).filter(~Person.id.in_(config.Session.query(more query))

is an example

in_ is the IN operator

.filter() is where

[undone roost]

Are there different models used in the query above ?

Config is one

Ohh

Nested query?

That is

[rich trout]

yes

[undone roost]

Ohh kk

[rich trout]

The alternative is a massive join, which is probably inefficient at this point

[undone roost]

Yeah

That's what i meant to say earlier

Like make a join table of those columns and then make a composite key out of it

[rich trout]

The composite key is only for constraining the data, you don't apply it to a join table

It's part of the model not the query

consider it a multi-column primary key

[undone roost]

Yes yes

I will try when i get home . But this sure looks scary

[rich trout]

It is, a bit

It's much simpler than it looks

The reason it looks scary is because it's been taken apart into its individual pieces

The large, nested query up there is really a few simple queries

(SELECT movie.audi FROM movies WHERE movie.id == $1) gets all auditoriums where a movie is playing

(SELECT seat.id WHERE seat.audi IN ... ) fetches all seats in those movies

[undone roost]

That is but making the models and few other things you told above

Like dependecy and constraints

[rich trout]

sqlalchemy thankfully does all of it

to paraphrase,

class Audi:
    theatre = foreignkey(Theatre)

class Seat:
    audi = foreignkey(Audi)

class Rservation:
    slot = foreignkey(Slot)
    seat = foregnkey(Seat)
    seat_slot = composite_key(slot, seat, unique=True)

[undone roost]

no need to use self in last line

?

[rich trout]

right

It's not 100% accurate syntax

[undone roost]

okay

[rich trout]

Passing primary_key=True to both slot and seat appears to be an intended method

which is interesting

here, this oughta help https://stackoverflow.com/questions/10059345/sqlalchemy-unique-across-multiple-columns

So, this, instead of the composite_key line:

    __table_args__ = (UniqueConstraint('seat', 'slot', name='seat_slot_unique'),)

[tawny sail]

hey guys i recently installed pgadmin4 and i already had postgres installed, the problem is that pgadmin does show all the database, in fact it says 0

but when i run from command line, i can see everything

[harsh pulsar]

you have to configure pgadmin to point to your database

[tawny sail]

how can i do that

first time pgadmin 😅

[harsh pulsar]

how are you running postgres

the default port is 5432 i think

try connecting on localhost:5432

[tawny sail]

i think i got it

i used the connections tab, dint see that before 🙂

jeez pgadmin is complicated

[supple fox]

I'm moving all my discord bots data from a bunch of json files to a PostgreSQL database.
But im not sure how to implement certain parts of it.
One of the Tables i made is the one that holds all the Poll data such as: Opening date, Closing date, total votes, title, etc.
However im unsure on to add the voting options since the amount of options can vary from 2 to 13 (and it's not 100% that there won't be polls with more than 13 options in the future)
Do i create a second table and have Poll_Option | Poll_id where i use the poll id to link them to the correct poll or is there a better way of creating table within a table? (or other solutions?)
I'm pretty much brand new to SQL databases so i might have missed something obvious.

[harsh pulsar]

that'd be how an ORM does it

item_id | poll_id | poll_text | votes

[supple fox]

Alright, so that's the best solution then?

[harsh pulsar]

i don't know if it's best

but it's valid

[lofty summit]

Hey, for a project I would need a database and was thinking to use Postgres, because the tables are well fit to use tables. Do you know a pretty complete tutorial? I searched on the internet, but only found "how to get started" things. Ideally the ressource would deal with Postgres in general (including a bit of theory) in a first part, and after that explain how to actually use it in python with the right package.

[tawny sail]

this one @lofty summit

https://www.youtube.com/watch?v=qw--VYLpxG4

I am mid way into the course

pretty good

u probably wanna make notes while going through it as there are a lot of stuff xD

[lofty summit]

@tawny sail Ty, this looks pretty good!

[tawny sail]

👍

[undone roost]

@rich trout https://paste.pydis.com/juwubovoxe.py .......reservation depends on movie,date,seat and slot(timing)......check my model and see what can be improved or what else to add.

[hearty latch]

I'm back again with another database issue that I can't figure out

https://github.com/atbrandt/DeadbearBot/blob/database/lib/main.py#L421 this function is supposed to update a row in the database to reflect the changes made to the user's profile in the code above it, referencing this function https://github.com/atbrandt/DeadbearBot/blob/database/lib/db.py#L155

for whatever reason, the database refuses to update the level, xp, or cash values from their default of 0

and I have no idea why

[rich trout]

@undone roost if movie is dependent on date and slot, then you've got codependencies, and those aren't good to have

Deadbear, I'm not sure why, since those commands seem just fine. Are you certain the user already exists inside the database?

[undone roost]

did i make movie dependent?

how

[hearty latch]

@rich trout absolutely certain, and no other update commands fail in the database so it's not a locking issue as far as I can tell

[undone roost]

ohh in date class?

[rich trout]

Ace, the issue is you can now have two seats in the same auditorium at the same time saying that they show two different movies

Your sql is correct, deadbear, as far as I can tell

Do you maybe have the database open in an editor at the same time? That's caused this kind of issue before

[hearty latch]

not at the same time as it's supposed to log the data, no. I have it closed while testing, then open to check the results

AGH, I JUST REALIZED

this is what happens when you copy-paste recklessly

[rich trout]

?

[hearty latch]

I accidentally kept a return c.fetchone() instead of replacing it with a conn.commit()

it wasn't updating because it was never committed

[rich trout]

I should have seen that

[hearty latch]

facepalm

thanks anyway bast, I really appreciate the help

[rich trout]

No worries :P

Ace, not in the date class

Let's say I have a movie showing in auditorium 1B

I can reserve a seat in auditorium 3A, but have it's movie be the movie playing in auditorium 1B

[undone roost]

where is the fault?

[rich trout]

Data is in too many places

[undone roost]

so many tables you mean

??

[rich trout]

If you have a reservation, there should only be one way to figure out what movie you're seeing

What you've got, if you're careful, should function ok. The issue is you're saving what movie these different things--date, reservation, seat, etc-- in too many places

If your reservation only specifies the date, and the seat, then you should be able to find the movie, by looking for movies that show in the seat's auditorium at that date

[undone roost]

audi should have a column movie_id?

[rich trout]

No

I would suggest having a "showing" class

Which has a movie and a time, and an auditorium

To get the theatre from a showing, you go showing.audi.theatre

Reservations would then be a seat number and a showing

to get the movie for a reservation, you would do reservation.showing.movie

That way all your data is in one place, and one place only

[undone roost]

"reservation.showing.movie" this way we can access columns of other tables ?

[rich trout]

yes

That's the power of sqlalchemy

[undone roost]

okay

let me change it

[undone roost]

all the foreignkeys in showing/reservation class must have a relationship in respective tables ...right?

@rich trout

[rich trout]

yes

I've got to get to sleep now, so good luck! I think you're almost there

They would be many-one relationships

So yes, a foreignkey on the one, and a relationship on the many

reservation.showing is one, so foreignkey

showing.reservations is many, so relationship

[undone roost]

goodnight

[torn sphinx]

Morning! Anyone here using pugsql?

[undone roost]

morning

@rich trout

class Reservation:
tablename= "reservation"
table_args= {'extend_existing': True}
table_args = (UniqueConstraint('seat', 'showing', name='ticket'))

id=Column(Integer,primary_key=True,autoincrement=True)
seat = Column(Integer, ForeignKey('seat.seat_no'))
showing=Column(Integer, ForeignKey('showing.id'))

class Showing:
tablename= "showing"
table_args= {'extend_existing': True}
table_args = (UniqueConstraint('slot', 'movie', 'date', 'audi', name='showing_now'))

id= Column(Integer,primary_key=True,autoincrement=True)
slot = Column(Integer, ForeignKey('slot.id'))
movie= Column(Integer, ForeignKey('movie.id'))
date = Column(Integer, ForeignKey('date.id'))
audi = Column(Integer, ForeignKey('audi.id'))
reservation=relationship('Reservation', backref='showing',lazy='dynamic')

[rich trout]

👍

[undone roost]

do i need relationships for foreign keys of showing class in respective classes?

for slot,date,movie & audi

https://paste.pydis.com/labucoheti.py ....this is the complete model

[lofty summit]

Hey, I installed PostgreSQL a few month ago, and now I can't start it:
postgres -D /usr/local/pgsql/data does not works:
postgres: could not access directory "/usr/local/pgsql/data": No such file or directory

more precisely I can't start running the PostgreSQL database server.

[lofty summit]

tag me if you know, see you

[rich trout]

Yes, ace, you would

[torn sphinx]

@lofty summit does the /usr/local/pgsql/data exists?

[torn sphinx]

and btw i dont think thats the right way to start postgres

[lofty summit]

Hey @torn sphinx Thanks for your help

I don't think

/usr/local/pgsql/dataexists, but I followed the Postgres's guide to start the server

https://www.postgresql.org/docs/11/server-start.html

[rich trout]

If anyone knows an asyncpg function to string-escape a postgres table name I'd be very appreciative

[lofty summit]

I resolved my problem, using:

$initdb test
$pg_ctl -D test -l logfile start

[harsh pulsar]

@rich trout like interpolation? I don't know if it has a native way to do it, you might just have to escape carefully

[rich trout]

I want to run the following query, safely: create table $1 ()

doubt its possible

[supple fox]

async def poll_list(ctx, year=datetime.now(timezone.utc).year):
    polls_year = await bot.pg_con.fetch("SELECT * FROM poll WHERE date_part('year', expire_date) = date_part('year', $1)", year)

I have this discord.py command which i want to use to enable users to get a list of polls from a specific year. However if i try this i get the error

asyncpg.exceptions.AmbiguousFunctionError: function date_part(unknown, unknown) is not unique
HINT:  Could not choose a best candidate function. You might need to add explicit type casts.

Which is from date_part('year', $1)", year).
I can make it work if i replace the $1 part with current_date. But that's not what i need.
what do i need to do to enable the user to query by year?

I can also make it work if replace date_part('year', $1)", year) with date_part('year', current_date - interval '1 year')" but i can't figure out how to switch the 1 with a python varible. the usual $1 doesn't seem to work

[undone roost]

@rich trout they can have either one to many or many to many relationship right?

[supple fox]

Solved the above issue, Didn't need the date_part('year', $1)", year). Could just do a direct = year

[stable violet]

This seems like it shouldn't be difficult but I'm coming up short with the syntax, my problem is that I am retrieving values from an SQL DB and where I'm attempting to search for a specific date, problem is that in the column I'm searching both the date and time are within the same column like so -
2016-10-04 09:44:44.127

My SQL statement is looking like this currently, I thought the LIKE clause might work but I'm beginning to question whether or not this is the case

date = "2016-10-10"
    sql_all_dates_specific = c.execute("SELECT * FROM table WHERE column LIKE ?", (date,))```
And this db is massive, so I can't just bring the entire thing down into memory and hack it apart by any means

[fringe tiger]

maybe something like WHERE columname BETWEEN '2012-12-25 00:00:00' AND '2012-12-25 23:59:59' I googled that btw

https://stackoverflow.com/a/14104364

using LIKE seems it kills the perfomanse

[stable violet]

I'm not overly worried about performance, this would run likely bi-weekly and it'll be faster than the interactions with google sheets api anywho where the real bottleneck occurs (don't ask, I dont want to be using sheets, just the way its worked out). I'll get back to you on the between suggestion though

[fringe tiger]

No I mean judging by those answers it does something horrible with the database (something about checking each column) and kills the perfomance. Might be a issue if you have a huge db as you said

[stable violet]

yeah I was just reading, if I'm understanding right it would make it a non-viable option to use LIKE

[split owl]

i have a question

what's the difference between all the types of databases?

and which one's the most used or the best for python?

[stable violet]

SQL is fairly popular, that much I can tell you. This link might be helpful for you @split owl https://www.tutorialspoint.com/Types-of-databases

@fringe tiger found a person from another discord who said they've used LIKE on DB's with up to 32mill lines, so shouldn't be an issue to use it

so now a new question, how do you provide a string but leave the value on the inside alone?
If I have
("SELECT * FROM TableName WHERE ColumnName LIKE '%?%'", (date,))

This won't work because '%?%' is a string, but removing and making it %?% does not work either. I have tested and
SELECT * FROM TableName WHERE ColumnName LIKE "%2016-10-04%" does work in DB Browser for SQLite, so I'm at a loss as to what to do

Found the solution with some other helpful discord-ers! For the curious
("SELECT * FROM TableName WHERE ColumnName LIKE '%' || ? || '%'", (date,))

[normal musk]

if you're gonna do something in sql repeatedly is it common practice to make it into a stored procedure?

[undone roost]

@rich trout hey, how to provide users a seat map.......i opened book my show.....it shows only one seat map for one time slot even if there are multiple audis present......so how do i give the users the list of available seats in an audi without initializing all the seatmap first?.....coz there might be same seat(like a corner seat ) available in a different audi displaying the same movie.

Or should i make different time slots for each audi....so users have more time slots ?

[rich trout]

Each seat should be specific to an Audi

Audi.seats should be the seats for an Audi

They should only need setting once, and you have to set them, because they can be different, and how would the database know what you know without telling it?

If things are getting out of hand with the model's complexity, I'd suggest drawing it on some physical paper

Draw a theatre, teh audis inside, the seats, etc, and draw boxes for containment and relation

[undone roost]

okay

so i should assign seat numbers after i initialize an audi?

[rich trout]

yes

[undone roost]

and audi.seats ?....didn't get that

[rich trout]

seat=relationship('Seat', backref='audi',lazy='dynamic') in your audi model

[undone roost]

in audi table there is no such thing as seat

[rich trout]

audi.seat should be a list of seats

[undone roost]

__repr function is to be used ?

[rich trout]

?

[undone roost]

how audi.seat will return a list of seats?

sqlalchemy does that?

[rich trout]

yes

[undone roost]

ohh

so i only have to define relationship

[rich trout]

and it fills both sides

[undone roost]

if it is one to many then only id of one is enough to get list of other?

i need to read about relationships more....do you have any link where i can?

went through a few docs but didn't get to know how to implement ....there were very less or none examples

[rich trout]

Yeah, they're not very clear imo

[undone roost]

or any video link

either will do

[rich trout]

https://hackersandslackers.com/sqlalchemy-managing-relationships-data-models/ might help

[undone roost]

thanks

[vocal moon]

@bot.command()
#@commands.has_any_role("[JA] Junior Agent", "[BA] Border Agent", "[BC] Border Commander", "[BI] Border Inspector", "[DD] Department CO/XO", "[DBC] Deputy Border Chief", "[BC] Border Chief", "[BA] Board of Advisors", "[DBD] Deputy Commissioner", "[C] Commissioner")
async def createFile(ctx):
    
    member = ctx.author
    role = member.roles
    
    await ctx.send('`Enter your ROBLOX name.`')
    def check(m):
        return m.author == ctx.author
    robloxIgn = await bot.wait_for('message', check=check)
    
    await ctx.send("`Enter your callsign.`")
    callsign = await bot.wait_for('message', check=check)
    
    hs = discord.utils.get(ctx.author.roles, id='634073747195822090') #630370651537932308
    ofo = discord.utils.get(ctx.author.roles, id='634129126655000586') #630370583959306287

    if hs in role:
        hs_var = {HS}
        c.execute("INSERT INTO this (roblox, call, depname) VALUES (?, ?, ?)", (robloxIgn.content, callsign.content, hs_var))
        conn.commit()
    elif ofo in role:
        ofo_var = {OFO}
        c.execute("INSERT INTO this (roblox, call, depname) VALUES (?, ?, ?)", (robloxIgn.content, callsign.content, ofo_var))
        conn.commit()
    else:
        await ctx.send('ok')
    await ctx.send(f"`Successfully created a user file for {robloxIgn.content}`")

This code does not output anything onto the SQL, not sure whats wrong.

Nor does it output any errors

[atomic plank]

How to save a date and time object from discord.py's datetime/datetime object in postgresql?

like the utc object acquired from

from datetime import datetime

print(datetime.utcnow)

like what would be the appropriate type of column to be able to compare the object with current utcnow

[torn sphinx]

are there any open source alternatives similar to cosmos db

[jade topaz]

ok this is going to sound really weird but is there a NoSQL equivalent to sqlite? I've got JSON that I need to store, and just using flat file(s) seems unoptimal

[midnight verge]

@jade topaz https://docs.python.org/3/library/shelve.html ?

Also if flat files are really unoptimal... consider designing a data model and use SQLite

[gilded loom]

how do i choose between SQLite, MySQL, PostgreSQL, Firebase, etc.?

[jade topaz]

im unfamiliar with shelve - can this be queried the way an sqlite db can?

maybe i should just bite the bullet and invest in setting up mongo/couchdb or something

sqlite is fine and all that, but I'd really like to stick with nosql for this particular need - if I can just shove the json i'm pulling from APIs straight into a db that'd be great

[midnight verge]

@jade topaz no, shelve is basically an “organized” way to store random objects and retrieve them later, but nowhere as efficient as any RDBMS

@gilded loom if SQLite is enough use it, otherwise go for PosgtreSQL, unless you know you need anything else (protip: at that point you don't, otherwise you would not be asking)

[gilded loom]

I'm thinking of making a DB for my web app that I will eventually also make into a mobile app

[midnight verge]

My answer is valid for any project

[gilded loom]

if that affects anything

okay 👍

out of curiosity though, what is firebase for exactly?

[jade topaz]

i'd say use sqlite for prototyping then when you're ready move onto an actual db

[gilded loom]

i've heard that people use it for real-time apps?

[midnight verge]

@gilded loom fast application development/prototyping, but it involves a lot of technical debt and financial costs beyond short-term

[jade topaz]

firebase seems like this "all-in-one" solution for mobile app developers - its got a database, support for authentication, etc. never used it myself but I iknow others that have

[gilded loom]

alright i'll go with PostgreSQL 👍 thanks

[undone roost]

@rich trout this is my Audi and Seat model ....when i try to initialize Seat table after creating and audi using audi.id by passing audi_id in my DTO it shows error : InvalidRequestError: Entity '<class 'app.main.model.theatre.Audi'>' has no property 'audi_id'.......however i am putting data into Seat model not Audi.

class Audi(db.Model):

""" Model for storing auditorium related details """
__tablename__ = "audi"
__table_args__ = {'extend_existing': True}

id=Column(Integer, primary_key=True,autoincrement=True)
audi_name= Column(String(20),nullable=False)
rows = Column(Integer, nullable=False)
columns = Column(Integer, nullable=False)
public_id = Column(String(100), unique=True)
slot1=Column(DateTime())
slot2=Column(DateTime())
slot3=Column(DateTime())
slot4=Column(DateTime())
theatre_id = Column(Integer, ForeignKey('theatre.id'))
seat=relationship('Seat', backref='audi',lazy='dynamic')
movies=relationship('Movie', secondary='audi_movie',backref='audi',lazy='dynamic')
showing=relationship('Showing', backref='audi',lazy='dynamic')

def __repr__(self):
    return "<Audi : '{}'>".format(self.audi_name)

class Seat(db.Model):
""" Model for storing seat related details """
tablename = "seat"
table_args = {'extend_existing': True}

id=Column(Integer, primary_key=True,autoincrement=True)
public_id = Column(String(100), unique=True)
seat_type = Column(String(10), default='silver')
seat_no=Column(String(5))
seat_price=Column(Integer)
status=Column(Boolean,default=True)
audi_id = Column(Integer, ForeignKey('audi.id'))


def __repr__(self):
    return "<Seat : '{}'>".format(self.seat_no)

@property
def seat(self):
    raise AttributeError('seat: write-only field')

@seat.setter
def seat(self,seat,seat_type):
    if seat_type=='silver':
        self.seat_price=theatre.base_price
    elif seat_type=='gold':
        self.seat_price=50+theatre.base_price
    elif seat_type=='platinum':
        self.seat_price=100+theatre.base_price

In short i am having problem in passing audi.id to seats while creating a seatmap for audi

[rich trout]

That's.. interesting

In your DTO, you said?

I have a feeling you're using audi_id somewhere outside this code, since the model looks just fine

Maybe during your creation you're attempting to set audi_id on the audi by mistake

[undone roost]

no no audi_id on seat

class SeatDto:
api = Namespace('Seat', description='Seat related operations')
seat = api.model('Seat_details', {
'audi_id': fields.Integer(required=True, description='audi id of seat '),
'status' : fields.String(required=True,description='availability of a seat'),
'seat' : fields.String(required=True,description='seat number'),
'public_id': fields.String(description='seat Identifier')
})

this is seat DTO

def init_seats_in_audi(data):
r=Audi.query.with_entities(Audi.rows).filter_by(audi_id=data['audi_id'])
c=Audi.query.with_entities(Audi.rows).filter_by(audi_id=data['audi_id'])

for i in range(r):
    for j in range(c):
        if i < r+1 and i > r-2:
            seat=Seat(public_id=str(uuid.uuid4()),
            seat_no=data['seat_no'],
            audi_id=data['audi_id'],
            seat_type='platinum'
            )
            save_changes(seat)
        elif i < r-1 and i > r-4 :
            seat=Seat(public_id=str(uuid.uuid4()),
            seat_no=data['seat_no'],
            audi_id=data['audi_id'],
            seat_type='gold'
            )
            save_changes(seat)
        elif i<r-3:
            seat=Seat(public_id=str(uuid.uuid4()),
            seat_no=data['seat_no'],
            audi_id=data['audi_id']
            )
        save_changes(seat)

and this is seatmap function

[rich trout]

Audi.query.with_entities(Audi.rows).filter_by(audi_id

This is filtering audi on audi_id

audi doesn't have audi_id

There's the error

[unreal tartan]

Unless your filters come from sources out of your control you're usually better of using filter() instead of filter_by and then you can use your model.
It's much more difficult to make mistakes because your editor (hopefully) will tell you that Audi.audi_id is not possible.

Audi.query.with_entities(Audi.rows).filter(Audi.id==data['audi_id'])

[undone roost]

ohh ok

does this returns me the integer value stored in rows column or do i need to do something else to get it

for i in range(r):
TypeError: 'BaseQuery' object cannot be interpreted as an integer

this is the error i am getting

r=Audi.query.with_entities(Audi.rows).filter_by(audi_id=data['audi_id'])

[unreal tartan]

It will return a list of Audi objects.

[undone roost]

ohh

how to get the integer value stored in that column

[unreal tartan]

actually it would return the 'rows' now that i look at it.

[undone roost]

yeah

but look at the error

i am unable to use it as integer

Audi.query.with_entities(Audi.rows).filter(Audi.id==data['audi_id']) ....this query shows error

syntax error : keyword can't be an expression

[unreal tartan]

I think that's because you used 1 = instead of 2 ==

[undone roost]

ohh

[unreal tartan]

As for your other error, I think that it will return a list by default.
so you'd have [3] as a response.
You could try adding .one(), first() or one_or_none()

[undone roost]

yes that was it

first

okay

[unreal tartan]

They're all different.
one() will raise if there are more than 1, or there are no results.
first() will return the first row, or none if there are no results
one_or_none() will raise if there are more than 1, but allow no results (returns None then)

[undone roost]

for i in range(r):
TypeError: 'result' object cannot be interpreted as an integer

it changed to this now

let me try one()

[unreal tartan]

hmmm, it's possible it still returns a row-like object. So youd have to do result.rows , or result[0] to pick the first column.

If you expect exactly 1 row, and 1 column, there's also .scalar()

[undone roost]

after one ?

[unreal tartan]

instead of .one() (replace it)

[undone roost]

look

the audi has only one integer value saved in the column named "rows"

and there is only one audi with each audi_id

[unreal tartan]

yeah, so .scalar() sounds doable then if you only select that column.

[undone roost]

should i print and check it?

okay

[unreal tartan]

You should learn a debugger and put breakpoints instead.
Depends on your editor and personal choice though.

[undone roost]

i use sublime

and i use only print statement to debug for now 😩

but i need to learn a debugger

[unreal tartan]

pdb is the standard one.
But there's fancier ones like ipdb , pudb,..

[undone roost]

cool it worked

and i will learn about pdb...thanks a lot

[vocal moon]

@bot.command()
#@commands.has_any_role("[JA] Junior Agent", "[BA] Border Agent", "[BC] Border Commander", "[BI] Border Inspector", "[DD] Department CO/XO", "[DBC] Deputy Border Chief", "[BC] Border Chief", "[BA] Board of Advisors", "[DBD] Deputy Commissioner", "[C] Commissioner")
async def createFile(ctx):
    
    member = ctx.author
    role = member.roles
    
    await ctx.send('`Enter your ROBLOX name.`')
    def check(m):
        return m.author == ctx.author
    robloxIgn = await bot.wait_for('message', check=check)
    
    await ctx.send("`Enter your callsign.`")
    callsign = await bot.wait_for('message', check=check)
    
    hs = discord.utils.get(ctx.author.roles, id='634073747195822090') #630370651537932308
    ofo = discord.utils.get(ctx.author.roles, id='634129126655000586') #630370583959306287

    if hs in role:
        hs_var = {HS}
        c.execute("INSERT INTO this (roblox, call, depname) VALUES (?, ?, ?)", (robloxIgn.content, callsign.content, hs_var))
        conn.commit()
    elif ofo in role:
        ofo_var = {OFO}
        c.execute("INSERT INTO this (roblox, call, depname) VALUES (?, ?, ?)", (robloxIgn.content, callsign.content, ofo_var))
        conn.commit()
    else:
        await ctx.send('ok')
    await ctx.send(f"`Successfully created a user file for {robloxIgn.content}`")

This code does not output anything onto the SQL, not sure whats wrong nor does it output any errors.

[split owl]

hello friends

has anyone used ZODB before?

if so, what do you think about it?

how easy is it to use for a beginner?

and is it just simple overall?

[junior star]

Hello All, Anyone here has any experience in connecting IBM i server to python code using ibm_db package?

[undone roost]

can we use the unique constraint's name in our query?

[undone roost]

i mean to ask if i can treat it as a column in that table and input it via a DTO?

@rich trout

[rich trout]

I'm not sure

Treat the constraint as a column? I don't think so

[undone roost]

so i have 4 values in my table ,all are Integer value and i want to query by all 4 of them

so i should filter them separately?

i guess

[rich trout]

yes

[stoic kernel]

I'm trying to use the SUM function in Postgres. I want it so that in the SELECT statement for something else I can can the sum of a column in a different table and check if it less than a number.

So in the 'WHEREclause of my select statement I want to check if the sum of a column in another table is less than say, 100. So if I have table points withidandpoints`, I want to check if the sum of the points where id=something is less than 100

Does that make sense?

nevermind i've decided to go a different way

[alpine kindle]

Hello

I am trying a sql statement to update a few things in a table

UPDATE nodes SET fqdn=[PythonPublicIP], memory=[PythonInstanceMem], disk=[PythonGP2Size] WHERE id=1

This is the query, i will replace the [] thing with vars later

These are the columns in the table im trying to update

How to get the timestamp so i can update those cols as well?

Something like

UPDATE nodes SET fqdn=[PythonPublicIP], memory=[PythonInstanceMem], disk=[PythonGP2Size], created_at=[somethinghere for current timestamp], updated_at=[something here again] WHERE id=1

[mystic surge]

I'm running a cursor that gets me a tuple of 2(?) With 300,000 rows of data

I made the mistake of trying to add to a list so I keep getting memory error...

Any idea how to approach this?

So I'm running query like
Select username, email from user_aud

[rain wagon]

slice the data and only request chunks at a time as you display it, using the LIMIT SQL instruction

SELECT username, email FROM user_aud LIMIT 50 for example

when you need the next batch, use OFFSET to get it

SELECT username, email FROM user_aud LIMIT 50 OFFSET 50 would get the next batch

@mystic surge

[mystic surge]

Ah okay wow

So I'd edit the query

Run query
Then append query offset 50

[rain wagon]

yes.

100 for the second batch obviously

so you need to keep track

on a webpage, you could simple use a get parameter for the offset

[mystic surge]

Oh hmm

[rain wagon]

for example http://example.com/?offset=XX

for pagination

[mystic surge]

Whatever offsett mean?

[rain wagon]

it means by how many rows the sql query shifts

if you run a query with limit 50 and want the next batch, offset it with 50 so you get entry 50-99

if you offset by 100, you get 100-149 etc

[mystic surge]

Ah okay

If I had offset 100

But only had 120 rows

Would it just get the 120?

*20

[rain wagon]

yes

[mystic surge]

Wow

[rain wagon]

quick examples: ```
sqlite> select * from t_stations LIMIT 1;
id_stations name url image format

---

1 Rockantenne http://mp3channels.webradio.rockantenne.de/rockantenne radio.png aac```

id_stations  name        url                                                            image       format
-----------  ----------  -------------------------------------------------------------  ----------  ----------
2            Radio GONG  http://mp3.radiogong963.c.nmdn.net/fs_radiogong963/livestream  radio.png   mp3```

see?

[mystic surge]

Yeahh

Seems really good and just what I need

[torn sphinx]

Why do I get this error? 😭

ValueError: operation parameter must be str```

It did work

how can it suddendly not work and give me this error

it makes no sense!! why does it even say that it must be STR???? There is no string, all the needed parameters are int

[rain wagon]

the sql string must be, well, a string

what is self.set_welcome_channel set to?

is it a sql query?

[torn sphinx]

what does sql query mean?

I work in sqlite3

set_welcome_channel = "update Settings set Welcome_channel = ? where Server_ID = ?"

[rain wagon]

that is a sql query there

[torn sphinx]

        return sqlite3.connect("C:\\Users\\Crossfire StationXd\\PycharmProjects\\untitled1\\serversettings.db", isolation_level=None ,timeout = self.sqlite_timeout)

    @commands.has_permissions(administrator=True)
    @commands.command()
    async def set_welcome_channel(self, ctx, *, channel : discord.TextChannel = None):
        guild = await self._ctx_get_guild(ctx)
        if guild is None:
            return await ctx.send("Please try this command in a server.")
        server_id = guild.id
        if channel is None:
            async with self.db_write_lock:
                with closing(self._connect1()) as db:
                    with db:
                        db.execute(self.set_welcome_channel, (None, server_id,))
                        await ctx.send("Please specify a valid text channel. Welcome message DISABLED .")
                        return
        channel_id = channel.id
        async with self.db_write_lock:
            with closing(self._connect1()) as db:
                with db:
                    db.execute(self.set_welcome_channel, (channel_id, server_id,))
                    await ctx.send(f"This server's welcome channel is set to {channel}.")```

oh

here is the code

but all of the commands SUDDENDLY stop working!!

Was there a update or something?

I am freeking out now ;-; it does not really make sense

You there?

Everything was working when suddendly 2 hours later almost everything broke

HOW can someone be so dumb

[rain wagon]

I am there, however, this should actually work, if the error is coming from that line

[torn sphinx]

YEAH

wait

it points the error on line

here is the code part

[tawny sail]

send us the full error

[torn sphinx]

db.execute(self.set_welcome_channel, (channel_id, server_id,))

Traceback (most recent call last):
  File "C:\Users\Crossfire StationXd\PycharmProjects\untitled1\venv\lib\site-packages\discord\ext\commands\core.py", line 79, in wrapped
    ret = await coro(*args, **kwargs)
  File "C:\Users\Crossfire StationXd\PycharmProjects\untitled1\cogs\serversettings.py", line 48, in set_welcome_channel
    db.execute(self.set_welcome_channel, (channel_id, server_id,))
ValueError: operation parameter must be str

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

Traceback (most recent call last):
  File "C:\Users\Crossfire StationXd\PycharmProjects\untitled1\venv\lib\site-packages\discord\ext\commands\bot.py", line 863, in invoke
    await ctx.command.invoke(ctx)
  File "C:\Users\Crossfire StationXd\PycharmProjects\untitled1\venv\lib\site-packages\discord\ext\commands\core.py", line 728, in invoke
    await injected(*ctx.args, **ctx.kwargs)
  File "C:\Users\Crossfire StationXd\PycharmProjects\untitled1\venv\lib\site-packages\discord\ext\commands\core.py", line 88, in wrapped
    raise CommandInvokeError(exc) from exc
discord.ext.commands.errors.CommandInvokeError: Command raised an exception: ValueError: operation parameter must be str

there the error

[tawny sail]

is self.set_welcome_channel a str?

[torn sphinx]

YEAH

set_welcome_channel = "update Settings set Welcome_channel = ? where Server_ID = ?"

I am freeking out ;-; I was doing so well,then everything collapsed

[tawny sail]

where is the self.

is it in the init method?

[torn sphinx]

no?

SALTROCKLAMP teached me that

;-;

like 15-20 days ago

The weird part is ..does that even matter? Because IT WORKED

[tawny sail]

db.execute(set_welcome_channel, (channel_id, server_id,))try this

[torn sphinx]

fine

[tawny sail]

class variables dont need the self

you are not refering to any instance

[torn sphinx]

0.o

wait

[rain wagon]

yeah

[torn sphinx]

yup unresolwed reference

[rain wagon]

ServerSettings.select_welcome_channel should do the trick

[tawny sail]

ahh mb

[torn sphinx]

yeah

it is not read anymore

1min

nope

wait wrong error

wrong command*

[rain wagon]

copy the text and insert it directly

[torn sphinx]

NameError: name 'ServerSettingsset_welcome_channel' is not defined

[tawny sail]

.

[rain wagon]

you forgot the dot

[torn sphinx]

-_- wait it deleted

yeah

[tawny sail]

<class_name>.<var_name>

[torn sphinx]

yeah the dot is nowhere to be found xD

ValueError: operation parameter must be str

same bs again 😭 python hates me

Why even change the self thingy? IT WORKED

the code WORKED for like 2 weeks

And I literally did nothing new

[tawny sail]

wait

rename that var

when you do self.set_welcome_channel, it is calling the function

[torn sphinx]

🤔

oky

perhaps..

[rain wagon]

ohh shit, how did I not see this

I actually thought about a name clash but dismissed it

[tawny sail]

lol

[rain wagon]

yeah, it is passing the function pointer

[torn sphinx]

wait what

[tawny sail]

yea

i cought it on the screenshot

[torn sphinx]

🤔

are we on to something?

[tawny sail]

rename variable

[rain wagon]

You are having a variable and a function with the same name

that clashes

[torn sphinx]

WT.F

[tawny sail]

yep

[rain wagon]

rename either the variable or the function

[torn sphinx]

it changed EXCEPT the database is locked xD

wow

why did that cause the error?

[rain wagon]

that is also the reason why it suddenly stopped working

Python suddenly decided to use the function pointer

[spiral burrow]

Generally things don't suddenly decide to do things differently

[torn sphinx]

@spiral burrow I apologise I was freeking out before..

[rain wagon]

It does when you are dealing with race conditions

[torn sphinx]

I am literally about to cry

[rain wagon]

it works 10000 times, then boom

[torn sphinx]

yeah..

anyways..anyone knows how do I CHECK or SEE what part of the code is keeping the database LOCKED?

[rain wagon]

kill the python process using it

that is the only way

[torn sphinx]

🤔 what

[rain wagon]

if it stays locked, dump the contents, delete it, make a new one

[torn sphinx]

why?

is that some sort of bug?

It also worked before

and I did not touch it 100%

[rain wagon]

SQLite cannot force unlock a database

[torn sphinx]

oh

[rain wagon]

you have to dump the contents as SQL and reimport it to a new one

[torn sphinx]

how do I unlock one? xD

WHY?I am confused

[rain wagon]

open cli, .dump then .read file.sql in a new db

[torn sphinx]

cli? what is that

[rain wagon]

the sqlite3 command line?

[torn sphinx]

wut xD

I never used that before

[rain wagon]

https://www.sqlite.org/2019/sqlite-tools-win32-x86-3300100.zip

https://www.sqlitetutorial.net/sqlite-dump/

[torn sphinx]

wait

so people have to MANUALLY unlock their databases????

Or is it just me,the idiot?

[rain wagon]

if a bug in your program causes it to lock up indefinetely, yes

[torn sphinx]

0.0

wow

let me do that

bruh

I better do this without those programs hahaha

confusing level 100

still locked -_-

I remade the database

I have everything connected with a timeout

I am making the things even more broken,why is it locked -_-

[rain wagon]

So, pragma journal_mode=WAL needs SQLite 3.7.0 according to https://www.sqlite.org/wal.html. But Python 3.5.3 uses only 2.6.0. Does anyone know an alternative mode that can replace WAL mode? NOt really sure which mode with SQLite is the one I want.

Is using shelve a good alternative?

[golden comet]

If someone could help me build a query using peewee (mysql) that will give me the count of each user's items in a table - something equitant to:
SELECT author, COUNT(*) AS cnt FROM Posts GROUP BY author ORDER BY cnt DESC LIMIT 50; - In reading the help docs I'm finding examples but all of them have another col for the count and I'm unsure exactly how to write this. Any help or points in the right direction would be appreciated

[crisp tundra]

async with self.conn.acquire() as conn:
            print(f'Wiki request received! {nation}')
            nation = nation.title()
            keyWord = nation.rstrip().replace(' ', '_').title()
            tag = await conn.fetchval('SELECT tag FROM tags WHERE country=$1', nation)
            x = await conn.fetchval('SELECT one, two, three, four, five, six, seven FROM idea_names WHERE tag=$1', tag)
            if tag is None:
                await ctx.send('Does your nation have Bielefeld as a name? Neko is sure it doesn\'t exist oAo')
                return
            if keyWord in {'England','Great_Britain'}: keyWord = 'England_Great_Britain'
            if keyWord in {'Granada', 'Andalusia'}: keyWord = 'Granada_Andalusia'
            if keyWord in {'Milan', 'Lombardy'}: keyWord = 'Milan_Lombardy'
            if keyWord in {'Holland', 'Netherlands'}: keyWord = 'Holland_Netherlands'
            async with aiohttp.ClientSession() as cs:
                async with cs.head(f'http://modcoop.org/index.php?title=Expanded_Mod_Family/{keyWord}') as r:
                    if r.status == 200:```

Using asyncpg (library for postgresql)

and discordpy just in case

For some reason, self.conn.acquire sometimes fails to work, saying that the class –called 'me'– doesn't have .conn as a object

but sometimes it wont

discord.ext.commands.errors.CommandInvokeError: Command raised an exception: AttributeError: 'me' object has no attribute 'conn'

nvm I found the error

[gritty pine]

You can probably re factor the if-keyword stuff too to make it more general

and marginally faster because i'm assuming you won't have any overlap of keywords so once you detect a match you can ignore all subsequent checks

[undone roost]

sqlalchemy.exc.InvalidRequestError: When initializing mapper mapped class Audi->audi, expression 'Showing' failed to locate a name ("name 'Showing' is not defined"). If this is a class name, consider adding this relationship() to the <class 'app.main.model.theatre.Audi'> class after both dependent classes have been defined.

i'm getting this error on running my app

https://paste.pydis.com/resajasedu.py

this is my model

however i have defined showing class in my model

but it says not defined

[undone roost]

forget this one

I am getting a different error now...table args must be tuple dict or None

class Reservation(db.Model):
tablename= "reservation"
table_args= {'extend_existing': True}
table_args = (UniqueConstraint('seat', 'showing', name='ticket'))

id=Column(Integer,primary_key=True,autoincrement=True)

seat = Column(Integer, ForeignKey('seat.seat_no'))
showing_id=Column(Integer, ForeignKey('showing.id'))

is there something wrong with the definition?

[rich trout]

yes

table_args = (UniqueConstraint('seat', 'showing', name='ticket'))
not only overrides table_args, but is missing a comma

it should be (UniqueConstraint(),)

I suspect you'd have to change it slightly to combine them, as righ tnow it's not extending the existing table

look up how to add a unique constraint with other args

I expect it to be straightforwards

[undone roost]

okei

[torn sphinx]

my bot is not working because the fucking database is locked,WHY is it constantly locked? Does it have a problem with me?

I may be drunk,do not mind that.. I will share my pathetic code now ;-;

https://paste.pydis.com/abeculejek.py