databases

[runic pilot]

yeah it's similar to a URL

[atomic coral]

ok so how do I connect my db with heroku?

You mean I have to connect via Mongodb instead of Heroku?

[runic pilot]

same way you would outside of heroku MongoClient(os.environ.get('MONGO_URI'))

[atomic coral]

I'm don't really understand anymore

same way you would outside of heroku MongoClient(os.environ.get('MONGO_URI'))
@runic pilot I do that in my code for my bot?

[brazen charm]

the the DB uri be an environment variable and then get the var using the method he showed u

[runic pilot]

it's best practice, but not necessary, to use environment variables instead of hard-coded database URIs

you can do MongoClient('mongodb://some.url.to/my-database')

[brazen charm]

Speaking of mongo

i string the pre generate Id (I forgot to assign my own ids and mongo cant handle changing > 16k items)

but then i need to be able to take that string

and index it with the db

but the issue is its not IdObject anymore

[atomic coral]

you can do MongoClient('mongodb://some.url.to/my-database')
@runic pilot I have that in my code

[runic pilot]

ok then that will work

[atomic coral]

cluster = MongoClient("link to my cluster")
db = cluster["Discord_bot"]
collection = db["botdb"]```
that's this right?

[runic pilot]

why is it an issue that they're not ObjectIds anymore?

yeah, that should work

(I'd assume)

[atomic coral]

so what do I do now?

[runic pilot]

just as you were before

[brazen charm]

Mongo isnt locating them when doing find

[atomic coral]

just as you were before
@runic pilot huh?

[runic pilot]

it should work, you shouldn't have to change anything else

how are you calling find ?

(it's been a little while since I used mongo, but I remember there were a few variations)

[brazen charm]

im doing find_one sorry

but when checking if mongo will actually locate stuff with the id being a string i tried it with compass to no avail

It does seem to have the Id be diffrent to a string which makes life hard

really should of thought about using my custom Ids before i filled the db but 😫

[runic pilot]

I'm pretty sure you need to create an ObjectId instance from your _id variable

[brazen charm]

which is the issue

because i cant find it lol

[runic pilot]

https://pymongo.readthedocs.io/en/stable/tutorial.html#querying-by-objectid

sorry I'm confused, what type is _id ?

[brazen charm]

_id thats getting fed to the function is a string

i have found the converter i think

[atomic coral]

@runic pilot it works without heroku, but if I host the bot with heroku and try a command that uses the database I get this error: Command raised an exception: ServerSelectionTimeoutError: connection closed,connection closed,connection closed

[brazen charm]

under the bson module not pymongo

okay nvm

pymongo crashes if you give it a bson object cuz it cant dump the json

🤔

ig im gonna have to convert the Id objects in the db to strings

[runic pilot]

not being able to dump the json seems like a pretty fundamental flaw, I'm sure there's a way around that

@atomic coral and it's the same URI both when you run locally and when it runs on heroku?

[atomic coral]

It's the same code

I do git push heroku master

[brazen charm]

the mother of all borked

wtf is up with this 🤔

[runic pilot]

from bson import json_util

json_util.dumps(document)

read about that here https://stackoverflow.com/a/27024423/8015233

[atomic coral]

is that what I need?

[runic pilot]

I don't think so, it sounds like you might be denying access to connect to your mlab mongodb server

[atomic coral]

but why would it work by hosting via PyCharm and not via Heroku

if it is just the exact same code?

[runic pilot]

PyCharm isn't hosting

PyCharm is running your application on your machine for you

the host is your computer

if it's the exact same code, there might be some connection configuration you have to do with your mongo server to allow connection from the heroku server

[atomic coral]

is this necessary?

[runic pilot]

no

see if any of these answers helps https://stackoverflow.com/questions/36082423/connecting-to-mongodb-database-on-mlab-fails-authentication

wait actually can you paste the full stacktrace of the error you saw?

[atomic coral]

I just make my bot send the error on discord for the moment...

actually where can I see errors and such things if I run an application on heroku?

[brazen charm]

@runic pilot oml so turns out i miss read that error as pymongo fucking up

i completely forgot i dump the result to json

oops

[runic pilot]

oh heck, well that's easy enough to solve at least

you can see the logs for your heroku app at https://dashboard.heroku.com/apps/YOUR_APP_NAME/logs

[atomic coral]

2020-06-09T20:24:50.176116+00:00 app[worker.1]: The above exception was the direct cause of the following exception:
2020-06-09T20:24:50.176116+00:00 app[worker.1]:
2020-06-09T20:24:50.176139+00:00 app[worker.1]: Traceback (most recent call last):
2020-06-09T20:24:50.176196+00:00 app[worker.1]: File "/app/.heroku/python/lib/python3.6/site-packages/discord/ext/commands/bot.py", line 892, in invoke
2020-06-09T20:24:50.176196+00:00 app[worker.1]: await ctx.command.invoke(ctx)
2020-06-09T20:24:50.176197+00:00 app[worker.1]: File "/app/.heroku/python/lib/python3.6/site-packages/discord/ext/commands/core.py", line 824, in invoke
2020-06-09T20:24:50.176197+00:00 app[worker.1]: await injected(*ctx.args, **ctx.kwargs)
2020-06-09T20:24:50.176214+00:00 app[worker.1]: File "/app/.heroku/python/lib/python3.6/site-packages/discord/ext/commands/core.py", line 94, in wrapped
2020-06-09T20:24:50.176214+00:00 app[worker.1]: raise CommandInvokeError(exc) from exc
2020-06-09T20:24:50.176269+00:00 app[worker.1]: discord.ext.commands.errors.CommandInvokeError: Command raised an exception: ServerSelectionTimeoutError: connection closed,connection closed,connection closed

@runic pilot ^^

[runic pilot]

that doesn't look like it's your fault

it also doesn't look related to mongo

[atomic coral]

you mean a problem with their servers for the moment?

or will it never be possible to host the bot on Heroku and use the database?

[runic pilot]

I don't know how to answer that, though I think #discord-bots has some opinions on how to host a bot correctly

[minor ruin]

which is, don't use Heroku

[atomic coral]

anything else I can use to host the bot for free other than Heroku (and which is really easy to use...)

[minor ruin]

Heroku will shut down your bot

they basically load it into container and will close it after a while

which is error you are getting

[runic pilot]

you'll be hard pressed to find a provider that'll run your code for free forever

AWS has a free tier that lasts a year and you can spin up a t2.micro server which should work for what you want to do

[atomic coral]

If anybody has a discord bot which uses mongodb and runs it with Heroku, pls let me know!

[zinc maple]

you can go pretty far on aws and google free tiers 😄

[atomic coral]

problem is that you need credit card I think

[runic pilot]

most places will accept visa gift cards as a valid credit card

[atomic coral]

I don't have that

[runic pilot]

you can buy one

[torn sphinx]

                try:
                    conn.execute(f"SELECT name FROM settings WHERE type = 'table' AND name = '{ctx.guild.id}")
                except:
                    conn.execute(f"CREATE TABLE {ctx.guild.id}")

I'm making my Discord bot (so you can ignore ctx.guild.id)
I want to check if a table already exists. Would this work?

[celest blaze]

it might, but you might be able to do something simpler: CREATE TABLE whatever IF NOT EXISTS. It depends on the flavor of SLQ you're using, but sqlite lets you do that

then you'd put that before the SELECT, and just always run it

if you stick with your way, catch just the one relevant exception class, not all exceptions

[torn sphinx]

I am using SQLite

thank you for your support

🙂

[torn sphinx]

1code

!code

[delicate field]

Discord has support for Markdown, which allows you to post code with full syntax highlighting. Please use these whenever you paste code, as this helps improve the legibility and makes it easier for us to help you.

To do this, use the following method:

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

Note:
• These are backticks, not quotes. Backticks can usually be found on the tilde key.
• You can also use py as the language instead of python
• The language must be on the first line next to the backticks with no space between them

This will result in the following:

print('Hello world!')

[torn sphinx]

why sqlite3 doesnt select a file fro the current folder

[wintry stream]

Hey there, I just started getting into DBes, and I chose to use postgres as I saw a lot of good things on it. I got it installed on an ubuntu VPS, and I tried creating a new user table with the normal account I use, which works as long as i do psql DBname. That's fine by me so I dont mind

Do you know if it's possible to connect the pgadmin or psql console on my local machine to a remote database?

I am in the psql from a putty terminal atm

[mossy blaze]

Hello. How can I insert a value inside of a row in phpmyadmin MySQL? I remember having to double click the value I want to change, but it does not seem to work now.

[torn sphinx]

I have a postgres table called config with a json column called cycle . cycle has following fields

number int, day bigint, night bigint, votes bigint

I want to select all the fields of cycle. I don't want cycle, I want the fields. Output like:

number | day | night | votes
----------------------------
value   value  value  value

I have no idea how to write query for that. Any help with it would be greatly appreciated

[round turtle]

What are some things that mongoengine can do that pymongo cannot?

[torn sphinx]

@torn sphinx pass your json column to jsonb_to_recordset and unnest

I'll try, thanks!

[restive linden]

I've used this tutorial for a setup of my database. But when i go to VS Code to connect it it doesnt work.

https://youtu.be/HXV3zeQKqGY?t=3382

What am i supposed to write in the different fields in the connection phase?'

[torn sphinx]

Is it ok if I log into the database at the start of the script and logout when I want to? Like I keep logged into the db the whole time, it can be a bot which is running all the time and the database is opened.

Is it okay if I do that?

Or do I need to close the connection whenever I am done using it...?

[harsh pulsar]

yes that's usually okay @torn sphinx

[torn sphinx]

Thanks

[harsh pulsar]

@torn sphinx depending on how your bot is designed, it might make sense for the bot to require a database connection at startup

[torn sphinx]

Hm, well there's 2 events for my bot, one is for when a new member joins my server and the other for when someone leaves, both make and delete data for a specific member that joined/left.

[harsh pulsar]

for example:

class MyBot:
    def run(self, db_conn):
        ...


def main():
    db_conn = sqlite3.connect(":memory:")
    my_bot = MyBot()
    my_bot.run(db_conn)

yeah, that should be fine

[torn sphinx]

Thanks 2x

[wintry stream]

Hey there, I asked a bit ago about how to remotely connect, and whilst doing my own digging i found something very interesting

my postgres install has no data folder to set up config

yet i can still access the database from the remote putty console

and the postgres folder does exist, i can see it on filezilla, it only has a bin and lib folder, no data folder with config files

Anyone know what's up with that?

[stuck ridge]

Hey guys, I'm looking for the best/easiest to use SQL parser. I just need to get the list of tables and fields used (unambiguous) in any SQL or stored procedure. Any recommendation?

For example for a SQL like: select a.field as 'label' from MyTable a; I would like a parser that indicates the tables used are: MyTable and the fields used: MyTable.field

(Btw, I'm a newbie here on Discord, so thanks to tell me if I'm doing something wrong: wrong topic, etc.. Thanks in advance 🙂

[solar pollen]

Would you recommend postgresql or mysql?

Is PostgreSQL better than MySQL?
Both PostgreSQL and MySQL are time-proven solutions that can compete with enterprise solutions such as Oracle and SQL Server. MySQL has been famous for its ease of use and speed, while PostgreSQL has many more advanced features, which is the reason that PostgreSQL is often described as an open-source version of Oracle.

Based on this (https://www.postgresqltutorial.com/postgresql-vs-mysql/), I think I'll go with MySQL?

[wintry stream]

What is your use case @solar pollen

[solar pollen]

What is your use case @solar pollen
@wintry stream Large discord bot

[wintry stream]

Do you need the extra features and open source that postges adds?

or do you preffer more light weight (mysql)?

[solar pollen]

No, I won't be modifying the source/referring to it, I'm not sure what extra features postgres ads?

Does light weight mean that it will bottleneck for larger projects?

[wintry stream]

MySQL has a few less features so it needs less memory and starts faster

possibly also every so slightly faster response time

[solar pollen]

So, what are the extra features that postgres adds?

[wintry stream]

Postgres Advantages over MySQL
Postgres is an object-relational database, while MySQL is a purely relational database. This means that Postgres includes features like table inheritance and function overloading, which can be important to certain applications. Postgres also adheres more closely to SQL standards.

Postgres handles concurrency better than MySQL for multiple reasons:

Postgres implements Multiversion Concurrency Control (MVCC) without read locks Postgres supports parallel query plans that can use multiple CPUs/cores Postgres can create indexes in a non-blocking way (through the CREATE INDEX CONCURRENTLY syntax), and it can create partial indexes (for example, if you have a model with soft deletes, you can create an index that ignores records marked as deleted) Postgres is known for protecting data integrity at the transaction level. This makes it less vulnerable to data corruption.

https://developer.okta.com/blog/2019/07/19/mysql-vs-postgres#postgres-advantages-over-mysql

[solar pollen]

Alright, I'm not entirely sure what those are; but I think that means I probably don't need them for right now

So, in that case I think I'll go with MySQL

[runic pilot]

CREATE INDEX CONCURRENTLY is a pretty sweet feature, it lets you optimize queries in your database with virtually no downtime

[pseudo summit]

I pretty much always default to postgres for things I build, but you can't really go wrong choosing either. It's not like you're building out enterprise software. MySQL will be fine as the db for a large discord bot for sure.

[wintry stream]

Gonna quickly bump my issue as it got covered by the other convo

https://canary.discordapp.com/channels/267624335836053506/342318764227821568/720289063277690892

[mossy blaze]

Hello, I have

self.cursor.execute(f'SELECT user_money FROM {guild} WHERE user_id = {member.id}')
                self_balance = self.cursor.fetchone()[0]

This query works perfectly the first time, but after running the command that runs the query the 2nd time, even though the values in the database update, the self_balance variable does not. Anyone has any idea why?

[lapis oriole]

You didn't commit

I've got a question : what is the point of using a connection pool with asyncpg instead of a single connection ?

forget it, found an answer

[mossy blaze]

wait, i have to commit when using fetchone? after the fetch or before it?

nvm I got it. thanks

[harsh pulsar]

you don't have to commit before/after fetching

[lapis oriole]

How exactly do you use asyncpg.fetchval ?

Can I select the whole Row in the query ?

Or at least the row containing the val fetched ?

[harsh pulsar]

@lapis oriole asyncpg.fetchrow instead

[lapis oriole]

No I mean, I want to fetch one value from a row. My question is, is there a specific syntax for the query ?

Also, for an ARRAY type, can I directly pass a list as argument of the execute method ?

[harsh pulsar]

oh, no. you just write a query as normal

[lapis oriole]

K, and for the array ?

[harsh pulsar]

i believe so, but you have to try it. i don't have a postgres setup available right now

they have a mapping between python types and postgres types https://magicstack.github.io/asyncpg/current/usage.html#type-conversion

[lapis oriole]

thanks gonna check it

Looks like conversion is perfect. Thanks

[harsh pulsar]

great

asyncpg is a very nice library

i kind of wish they kept the old convention of passing the query params as a tuple/list instead of varargs, but nothing is perfect

also postgres is just an amazing software project

[wintry stream]

Well I uninstalled and reinstalled twice and now my issue is gone

I have a working remote admin page and database

[lapis oriole]

For a postgre array, i should put CREATE TABLE mytable (myarray INT[]), right ?

Also is there a rowid for postgre ?

[harsh pulsar]

nope, rowid is a sqlite3 thing

in postgres you have to explicitly create an integer auto-incrementing primary key

[wintry stream]

Just gonna share a bit of an experience, not sure if anyone is interested. I'm very happy I finally made my own database running on my VPS after many reinstalls and whatnot. IDK what all went wrong but now it's working exactly as I want it. Next up is learning about posgres from within python so I can integrate it to my discord.py bot and keep track of a lot of things.

I will be looking into tutorials and guides and whatnot in like a week or 2 since next up I have some school work to do

[lapis oriole]

@harsh pulsar what about the array ? is CREATE TABLE mytable (myarray INT[]) right ?

@wintry stream You're also running a bot ? Good job

[harsh pulsar]

glad to hear you're learning @wintry stream

@lapis oriole from what i remember that's correct

[lapis oriole]

Thanks

[harsh pulsar]

CREATE TABLE mytable (
    id serial primary key,
    values integer[]
)

@lapis oriole

serial is an autoincrementing integer

[lapis oriole]

Yeah yeah. It's just that I had implemented some code with rowid in aiosqlite, but I found another, better, way to do it. Thank you anyway

[solar pollen]

https://www.youtube.com/watch?v=x7SwgcpACng&list=PLB5jA40tNf3tRMbTpBA0N7lfDZNLZAa9G

I'm looking at this video

2 minutes in he says to open the workbench..

All I have is the msi installer and the server zip

What do I do to get to where he is at 2:01 ?

[harsh pulsar]

that's mac-specific

oh nvm

you have to download mysql workbench and install it

[atomic coral]

I'm trying to run my discord bot using Heroku, and I use Mongodb as database for the bot. I get this error (only when running from Heroku): ServerSelectionTimeoutEroor: connection closed.
I think this is the problem that I'm having: https://stackoverflow.com/questions/55005134/app-engine-pymongo-errors-serverselectiontimeouterror-connection-closed-connec
I'm new to all this stuff and I don't really understand how they fixed it. Could anyone break it down for me?

[harsh pulsar]

usually you just double-click an msi file to install

[solar pollen]

👍

I pretty much always default to postgres for things I build, but you can't really go wrong choosing either. It's not like you're building out enterprise software. MySQL will be fine as the db for a large discord bot for sure.
@pseudo summit What should I use for learning it?

I found this: https://stackabuse.com/working-with-postgresql-in-python/

I installed psycopg2, do I need to install anything for the postgres side of it?

Apparently I do..

hm

[harsh pulsar]

you need to install postgres itself

[wintry stream]

Thanks for the support @lapis oriole and @harsh pulsar

Yeah I already had a small discord bot up and running

This is just a free time project of me for 1 bigger-ish server

Since rn we rely on all kinds of other bots and it’s quite annoying having different bots for everything

Like this we are in total control

[wintry stream]

Hey there @atomic coral I know heroku sounds interesting in terms of free and hosting. But it’s quite bad actually as heroku is made for small website hosting and such. You also don’t get 24/7 as you have a limited time of active hours every month. And the rest of the time it will be offline. You are severely limited on what you can do, and you should step away from heroku.

A simple raspberry pi is good for a small one time investment, but you’ll have to manage everything yourself

You can also pay every so often for a VPS (they are quite cheap actually) and you don’t have to maintain the machine and network yourself

[solar pollen]

you need to install postgres itself
@harsh pulsar When I download it, does that install a server for the database?

[harsh pulsar]

postgres is a server

(it does include a client as well)

you then need to start the postgres server, connect to it thru the included client, and create a database

you might want to use a graphical interface like pgadmin

[torn sphinx]

So I keep getting this error after so many hours and i dont realise until after some time or until the user spots it
mysql.connector.errors.OperationalError: MySQL Connection not available.

    raise CommandInvokeError(exc) from exc
discord.ext.commands.errors.CommandInvokeError: Command raised an exception: OperationalError: MySQL Connection not available.```
1) Does anyone have any idea what could be causing the issue?
2) Is it possible to catch this error and then output some sort of message so I am alerted or have a loop where it keeps trying?

I am running on GCP cloud free tier. Ubuntu.

I make the connection the the DB once and reference to this connection each time.
Whenever I need to run a query in a db, i do it in a function as follows:

cursor = self.bot.mydb.cursor()
       cursor.execute("QUERY GOES HERE")
       result = cursor.fetchall()
       self.bot.mydb.commit()```

[modest matrix]

So it works for a while when the bot starts?
Am I understanding this correctly?
Maybe this will fix it, maybe not, but I'd recommend it anyways: use an async DB driver, since it seems you're using one that is blocking, meaning that it can't handle other commands while a DB query is happening

[torn sphinx]

is there a unit for latency

like.. is it always just time in ms

[runic pilot]

yes, but the trick in latency is to very specifically define the "start" and "end" you're measuring

[torn sphinx]

@modest matrix Sorry for late reply. Yes it works like all day and then towards the end of the day sql connection closes.

I then just restart the bot and it works again.

Currently using import mysql.connector

[modest matrix]

Hm
First, I would strongly recommend using an async MySQL lib
Second, I'm not exactly knowledgeable on MySql matters, but from my postgres experience, use a connection pool, rather than this one single connection, which is what you're currently doing, I'm assuming

[torn sphinx]

I see thanks for very much for that. I will try this. I asked many times but no one replied only you. 😄

So to make the pool do i just change from this:

        host="localhost",
        user="my_name",
        passwd="my_pass",
        database="test",
)```
To this?:

client.mydb = mysql.connector.connect(
pool_name = "mypool",
pool_size = 3,
host="localhost",
user="my_name",
passwd="my_pass",
database="test",
)

[harsh pulsar]

@torn sphinx can you share your full code

but yes that looks correct as per https://dev.mysql.com/doc/connector-python/en/connector-python-connection-pooling.html

inside each command you acquire one connection from the pool

[torn sphinx]

Do i need to say which pool i want?

Or is automatic?

[harsh pulsar]

i think it's automatic

if you use pool_name

then you can use client.mydb as if it were a regular connection

[torn sphinx]

Ok my code is like this:

def add_username(self, discord, sc_tag):
        try:
            cursor = self.bot.mydb.cursor()
            cursor.execute("INSERT INTO tags (user_id, sc_tag) VALUES (%s, %s)", (str(discord), str(sc_tag)))
            self.bot.mydb.commit()
        except Exception as e:
            print(e)
            ts = datetime.now().timestamp()
            channel = self.bot.get_channel(71883944834440911380)
            channel.send("Error raised from the add_username() function at: " + str(datetime.utcfromtimestamp(ts)))

Like i have function similar to this each time. But just query is different.

I added try catch today to all function to see where error is coming from

And above function is used like in following command:

@commands.command()
    async def link(self, ctx, sc_tag):
        """Adds username to db"""
        if self.check_duplicate(ctx.author.id) == True:
            embed = discord.Embed(title=":x: bruH...",description="A username with this Discord ID already exists. Can't add a duplicate.", colour=discord.Colour.red())
            await ctx.send(embed=embed)
        else:
            try:
                self.add_username(ctx.author.id, sc_tag)
                embed = discord.Embed(description=":ok_hand: **Bellissimo Username linked successfully**",
                                      colour=discord.Colour.red())
                embed.add_field(name="Details", value=f"{ctx.author} | {sc_tag}")
                embed.set_footer(text=ctx.author, icon_url=ctx.author.avatar_url_as(format="png", size=1024))
                await ctx.send(embed=embed)
            except Exception as e:
                print(e)```

[harsh pulsar]

yeah you should be able to use self.bot.mydb.cursor like that

however i'm not sure you actually need a cursor

does mysql connector allow you to write self.bot.mydb.execute? if so, you should use that

you don't need a cursor for a one-line insert

it's just more complexity

[torn sphinx]

Ok

Thanks

I will try this now, and check for next day.

Also are pool connection automatically recycled? I read by default if none is specified it is 5. So now i put 30. So hopefully it may fix problem, but i was wondering if they get recycled automatically?

[harsh pulsar]

that's a good question

i would read the docs here https://dev.mysql.com/doc/connector-python/en/connector-python-connection-pooling.html

ahh

i see

ok

you use connect inside each function

but use the same pool name

i think its better to use mysql.connector.pooling.MySQLConnectionPool

then you can explicitly acquire and release connections from the pool

[modest matrix]

You have to explicitly give the connection back to the pool in asyncpg, I'd imagine it's similar in this case?

[harsh pulsar]

@modest matrix mysql connector has this implicit pooling ability

but i prefer the explicit acquire/release method

[torn sphinx]

So where to add this: mysql.connector.pooling.MySQLConnectionPool

does mysql connector allow you to write self.bot.mydb.execute? if so, you should use that
@harsh pulsar Also this doesnt work, it sends error. So i keep cursor for now

[harsh pulsar]

ok

your bot is a subclass of discord.Client right?

[torn sphinx]

Yes

Also maybe i forgot to mention, i have on_message command where it adds user xp for every message into the db 😬

[harsh pulsar]

import discord
from discord.ext import commands
from mysql.connector.pooling import MySQLConnectionPool

class MyBot(discord.Client):
    def __init__(self, database, user, pool_name='mypool', pool_size=3):
        self.db_pool = MySQLConnectionPool(
            pool_name=pool_name, pool_size=pool_size,
            database=database, user=user)

    async def add_username(self, sc_tag):
        db_conn = self.db_pool.get_connection()
        cursor = db_conn.cursor()
        cursor.execute('...')
        ...

@torn sphinx do you understand?

i don't know how to release the connection. maybe del db_conn

[torn sphinx]

I kind of understand, not so well though. Here is how my code is currently structured:
Main file that is run at start: https://paste.ofcode.org/ZXPBadwZ2U5CT8G2R44P99
Class where i use db: https://paste.ofcode.org/52bmJk62MVCV8RdqbDR5hG

So would i change the code in my main run file? or leave as it is?
And to just change code like you sent in my cog?

[harsh pulsar]

    def add_username(self, discord, sc_tag):
        try:
            db_conn = mysql.connector.connect(pool_name="mypool")
            cursor = db_conn.cursor()
            cursor.execute("INSERT INTO socialclub (user_id, sc_tag) VALUES (%s, %s)", (str(discord), str(sc_tag)))

try this

if you use the same pool name as in the main file, it will use the same pool

[torn sphinx]

I dont need to add self?

Does it automatically find?

[harsh pulsar]

it automatically finds

because it has the same pool_name

[torn sphinx]

nice

[harsh pulsar]

that is what the documentation says

i have never used it personally

[torn sphinx]

Yes it finds automatically, as i just added something to test

[harsh pulsar]

great

[torn sphinx]

So now i only changed this what you just told me now. Should i change others like this also?

im guessing yes

And so i dont need to do like this, right? #databases message

Yes it finds automatically, as i just added something to test
@torn sphinx Actually that doesnt work 😭 😭 😭 My discord tells me it was succesfull but in db not added

No exception either as it is wrapped in try catch

[torn sphinx]

So my head is hurting, my eyes are hurting, and is almost 5am. So will go sleep now. But thanks you so much for the help. For now i have created the 30 pool and its currently running on the server. I will see if this gives error after some time. Hopefully not.
And maybe if it gives error i will try to change the code so i am then using the aiomysql library maybe to stop any possible blocking as luke said. Hopefully this is ok.

@harsh pulsar Thanks so much, very appreciative. 👍

[torn sphinx]

Am I missing something here? I'm using Sqlite and I'm trying to delete a line from my database but it isn't working. I first checked to make sure that what I was passing in was the correct datatype, then I checked that the actual number matched. It's still not deleting from the database lol what do I do?

[radiant elbow]

@torn sphinx you're passing an integer to execute, not a tuple (non-empty tuples require a ,)

cursor.execute('DELETE FROM orders WHERE userid = ?', (user.id,))

at least, that's the first thing I notice that's wrong.

[torn sphinx]

That's what I was trying earlier. If just tried it again though and it works 😂 thanks!

[wintry stream]

Does anyone have a good resource on how to get started with Postgres in python?

I know a bit of sql (not much but still) and quite well about python but I’m a complete beginner at doing both

[lime grove]

so im pretty new to databases. Im trying to build an app that allows people to search for foodbanks in their area, and all the foodbanks are in a database. The info in the database is public, it doesnt really matter if someone can view it, as long as they dont edit it.

so in my app would it make sense to make 1 account that has select permissions only

and have everyone who downloads the app connect to the mysql database with that one user?

[wintry stream]

You definitely want separate account

You don’t want someone to come along and do DROP DATABASE

[lime grove]

well i only have select permissions on that accont

so i have one account for me to manage the database, one account for the users of the app which can only read the data

would that work

[wintry stream]

If it completely revokes their access to just select from then it shouldn’t hurt

[lime grove]

ok, thanks

[wintry stream]

But one quick question

Why would you want your end users to have direct DB access?

[lime grove]

i dont really know much about databases so im not too sure like how else I would do it

[wintry stream]

Because it looks like a massive liability/risk for like no profit

Almost no one knows sql and users will do unexpected things on purpose or accidentally

An app that graphically displays the output, and also clean the output, should be much better

[lime grove]

yea, thats what it does, the app automatically connects to the db with the account when you hit search

and it displays the results in like boxes

[wintry stream]

Yeah okay but that also means users have direct access to the admin panel using the same account

[lime grove]

even if they have no permissions except select?

basically whats the best way to make like a public read only database

[weak gust]

Hi

Currently using PyMongo for my backend. Isn't there a way to make partial searches? I created text indexes but I don't fancy at all the fact that the user need to write the exact word in the search box in order to get results

I'd like to show similar results or partial matches..

[lapis oriole]

Check that out : https://github.com/Rapptz/RoboDanny/blob/608374feba8d01fcf85f7067f73795d4b1522ee6/cogs/tags.py#L242

Just to be sure, in asyncpg you don't have to commit, except for the savepoints

Am I right ?

[weak gust]

Thats SQL mate

[lapis oriole]

right

[lapis oriole]

Do you have to commit with asyncpg ?

[lost echo]

how to select a row (each row has an ID in table) and then update a value in sqlite?

[lapis oriole]

Use the SELECT statement

And cursor.fetchone()

[keen sundial]

@lost echo https://www.tutorialspoint.com/sqlite/sqlite_update_query.htm

UPDATE mytable SET value = 'banana' WHERE rowid = 1234;

@weak gust in atlas and 3.4 there is a new text engine: https://www.mongodb.com/blog/post/mongodb-atlas-search-now-ga

[torn sphinx]

Hey guys. So I am having a problem with the MySQL database with my bot.
I am currently using the aiomysql library for the DB. But keep getting error where connection to the DB is lost. Once i restart my bot the connection is resumed again. I dont know what I am doing wrong. The code works for like a few hours until it hits this error.

The method here: https://paste.ofcode.org/PhnDXfdh9xVgbfZFaKVnpG is run every time a user sends a message in discord. (it is part of my on_message event)
The code in here: https://paste.ofcode.org/WQTNcZxDVGYY4DTYGNtbc5 is run whenever the user needs to use one of the commands.

I am making the connection to the DB once as below in my main run file, and then refer to this connection each time from the other parts of the code:

import asyncio
import aiomysql

client = discord.Client()
client = commands.Bot(command_prefix=prefix, case_insensitive=True)

async def my_db_connection(loop):
    client.mydb = await aiomysql.connect(
            host="localhost",
            user="aaaaaaaaa",
            password="aaaaaaaaa",
            db="test",
    )
loop = asyncio.get_event_loop()
loop.run_until_complete(my_db_connection(loop))

@client.event
async def on_ready():
    print('We have logged in as {0.user}'.format(client))
    activity = discord.Activity(name='snitches get stitches', type=discord.ActivityType.watching)
    await client.change_presence(activity=activity)

if you have info just ping me please.

[harsh pulsar]

loop.run_until_complete(my_db_connection(loop))

this is ... weird, and probably not correct

can you give an example of how you actually use the database connection in your code?

[torn sphinx]

I put the link above

The method here: https://paste.ofcode.org/PhnDXfdh9xVgbfZFaKVnpG is run every time a user sends a message in discord. (it is part of my on_message event)
The code in here: https://paste.ofcode.org/WQTNcZxDVGYY4DTYGNtbc5 is run whenever the user needs to use one of the commands.

I only use it for these purposes only.

[brazen charm]

run_until_complete is Blocking fyi

and also that loop is completely pointless?, and you have client = discord.Client() for no reason

Connect the DB on_ready() and save it to the client object

[torn sphinx]

Oh

So i move:

client.mydb = await aiomysql.connect(
            host="localhost",
            user="aaaaaaaaa",
            password="aaaaaaaaa",
            db="test",
    )

into my on_ready?

And remove this? py loop = asyncio.get_event_loop() loop.run_until_complete(my_db_connection(loop))

[modest matrix]

oh wow those docs are kinda outdated in places
I think it's your database that just disconnects at some point, since it happened with the other library as well
that should be correct, yes
tho you should probably use connection pools again, which might solve this

[torn sphinx]

I see thanks. So for now since i have removed the loop which may be possibly blocking in my current code i will see this how it goes for the next few hours.
If problem is still existent then i will add pool. Is there example of how to use pool in aiomysql or what i need to change in my code to support pool?

[modest matrix]

There is in the docs, yeahhhh
But it's using outdated async syntax

If you have questions about it after having had a look, feel free to ask

[rich trout]

The github has a good pool example up front:

import asyncio
import aiomysql


async def test_example(loop):
    pool = await aiomysql.create_pool(host='127.0.0.1', port=3306,
                                      user='root', password='',
                                      db='mysql', loop=loop)
    async with pool.acquire() as conn:
        async with conn.cursor() as cur:
            await cur.execute("SELECT 42;")
            print(cur.description)
            (r,) = await cur.fetchone()
            assert r == 42
    pool.close()
    await pool.wait_closed()


loop = asyncio.get_event_loop()
loop.run_until_complete(test_example(loop))

[karmic kettle]

hello all. I am using sqllite 3.8.6 as my db. I have 2 database columns, free and freeNew. This can be an integer with values 1 or 0.

I have two lists for free and freeNew that contain primary keys (integers). the primary keys in these lists should have their respective columns be set to 1.

ex. freeNew = [1, 5, 7]
free = [33, 100, 6]

rows with pk 1, 5 and 7 should have their freeNew col be set to 1.

rows with pk 33, 100, and 6 should have their free col be set to 1.

rows that have 1 for free or freeNew in the db, may need their free or freeNew columns set to 0 if they are not present in their respective lists.

as a negation to freeNew list which contains pk 1, 5, 7...all rows which do not have pk 1, 5, and 7 should have their freeNew col be set to 0.

I'm looking to do an update statement that will reference the free and freeNew lists and make the necessary updates to the db. I have scoured google and I am not clear on how to perform this update. I'm thinking of making free and freeNew in the db 0 before doing this update. I'm not sure how to do the update itself

[mossy blaze]

Hello, I want to have a query that creates a column called column0, but when the query is ran again it creates a column called column1, how can I do that?

[torn sphinx]

def inserimento_dati():
global nome = str(input("Inserire nome: "))
global anni = str(input("Inserire età: "))
global data_di_nascita = eval(input("Inserire data di nascita\n giorno/mese/anno: "))
def scrittura_dati():
file1 = open("Database.txt", "a")
file1.write(f"{nome"

Where is the problem in line 4?

sorry in line 2 or down

[keen zodiac]

Question. I have a column in a postgres database named type. I think that since type is an sql keyword, doing a where is not working correctly.

Example of what I am trying to do:

SELECT * FROM events
WHERE "type" = "foo"
LIMIT 10;

But, Datagrip is telling me "foo" is not a column. I am trying to find all records where the type column has entries "foo". Thoughts?

[pliant cliff]

db.session.delete(BlogPost.query.get((1)))
Traceback (most recent call last):
  File "C:\Users\Zelda\OneDrive\Programs\code\Python\fullflask\venv\lib\site-packages\sqlalchemy\orm\session.py", line 2039, in delete
    state = attributes.instance_state(instance)
AttributeError: 'NoneType' object has no attribute '_sa_instance_state'

what is going on here?

[celest zodiac]

just to be on the safe side, have you confirmed that BlogPost.query.get((1)) actually retrieves anything?

[pliant cliff]

no i haven't...

[celest zodiac]

Start there. :D

[pliant cliff]

would that be foundd via printing?

i get none

??????

[celest zodiac]

yes, you could throw a print in after the .get command to see if it's fetching anything

Seems like your .get isn't correctly formed. I don't know much about the ORM you're using, but it seems like it's not actually fetching according to any criteria

[pliant cliff]

😞

[celest zodiac]

I'd say, go back and re-read the docs on how .get works

[pliant cliff]

i'm following a tutorial 😅
which is why i'm even more confused 😞

[celest zodiac]

this might be a good time to learn how to independently read the documentation for a module. It's SQLAlchemy?

https://flask-sqlalchemy.palletsprojects.com/en/2.x/queries/#querying-records

[pliant cliff]

OH!

it works via the id... 🤔

[celest zodiac]

I.e., the primary key

[pliant cliff]

si... that's why...

[celest zodiac]

also, you don't need the additional parens

[pliant cliff]

muy fresco... gracias

[celest zodiac]

BlogPost.query.get(1) will work fine

[mossy blaze]

Hi, quick question: What should I store discord id's as? Storing them as integers would not work because of the integer limitations in SQL.

[celest zodiac]

@mossy blaze Freeform text

[mossy blaze]

ok. thanks

[modest matrix]

Or bigint, if your DB supports that

[pliant cliff]

BlogPost.query.get(1) will work fine
@celest zodiac noted... gracias

[timber karma]

Hi guys im using mysql workbench but can't delete rows manually

The other options are greyed out

[celest zodiac]

I haven't used it in a while but I think it only allows deletions when rows are actually selected, not just when a row is in focus

[timber karma]

Still not

i have it selected, doing right click and still appears like that

[celest zodiac]

then, not sure, haven't used it in a while

[zinc maple]

“Edit” -> “Preferences” -> “SQL Queries”, uncheck the “Safe Updates”
Try that

[timber karma]

still not lmao

[zinc maple]

ugh only thing I could find on it...

[pliant cliff]

is it ok to use "id" as a field for primary key?

i keep getting the fact that it is shadowing the built in value

[runic pilot]

yes, it's fine and normal to do that

[pliant cliff]

aite

[ancient fiber]

import sqlite3
conn = sqlite3.connect("main.db")
conn.row_factory = sqlite3.Row
c = conn.cursor()
id = "id"
c.execute(f"SELECT * FROM coins WHERE id={id}")
result = c.fetchone()["value"]
if result is None:
    time_streamed = "result is none"
else:
    if result == 0:
        time_streamed = "0"
    else:
        #time_streamed = parse_duration(result)
        time_streamed = result
print(time_streamed)

    result = c.fetchone()["value"]
TypeError: 'NoneType' object is not subscriptable
``` how to fix this error?

it works fine on other tables

[modest matrix]

you sure there's an element with the id "id"?

[ancient fiber]

there isnt, but it should return result is none

i figured it out

using try and except

[pearl heath]

hey, I am making a a few classes that interact with a flask database and I don't want to run the flask server... just have a single file sqlite3 database for stuff. Maybe some more complex schema down the road but for now, just a serverless flask DB that stores information in a portable format...

Problem is, Compound.query.filter_by(cid = query_cid).first() doesn't seem to actually query the database...

What am I doing wrong? The code is here
https://github.com/flyingfishfuse/discord_bot/blob/master/discord_chembot/pubchem_standalone_monolithic_test.py

The database code goes from lines 144 to 450

[zinc maple]

@ancient fiber btw I believe calling fetchone() is safe but you need to ask if what u get form it is none before you try to get ["value"]*

sry for late input 😛

that shortcut doesn't actually apply here my bad

[torn sphinx]

@pliant cliff how did you make that blog post thing? its really cool

[pliant cliff]

i followed a Flask tutorial... but i'm not 100% confident enough to say that i can make it again... i still need more practice with flask

https://www.youtube.com/watch?v=3mwFC4SHY-Y

[ancient fiber]

sry for late input 😛
@zinc maple np btw i get it

[zinc maple]

value = getattr(c.execute(f"SELECT * FROM coins WHERE id={id}").fetchone(), "value", None)
if value is None:
I think you might be able to do this, not 100% sure tho, but something like this ^^

[runic pilot]

fetchone() will return None if there is nothing to get https://docs.python.org/3.8/library/sqlite3.html#sqlite3.Cursor.fetchone

no need to look for the "value" key

[delicate field]

Hey @onyx sierra!

Uh-oh! It looks like your message got zapped by our spam filter. We currently don't allow .txt attachments, so here are some tips to help you travel safely:

• If you attempted to send a message longer than 2000 characters, try shortening your message to fit within the character limit or use a pasting service (see below)

• If you tried to show someone your code, you can use codeblocks
(run !code-blocks in #bot-commands for more information) or use a pasting service like:

https://paste.pythondiscord.com

[onyx sierra]

I am getting this errow while trying to run the code

sqlalchemy.exc.AmbiguousForeignKeysError: Could not determine join condition between parent/child tables on relationship Users.home_work_card - there are multiple foreign key paths linking the tables.  Specify the 'foreign_keys' argument, providing a list of those columns which should be counted as containing a foreign key reference to the parent table.

Here is the code - https://paste.pythondiscord.com/cunumiwuza.py

[dusty helm]

guys,

i'm ultra newbie with databases, tried my first relationships tehse days, idk what to do next.

basically:

i have the tables [User] and the table [Company]

user has 'company_id'
and company has 'users'

then i tried to create dummy data for the company, but got this error:
sqlalchemy.exc.InvalidRequestError: When initializing mapper mapped class Company->company, expression 'User' failed to locate a name ('User'). If this is a class name, consider adding this relationship() to the <class 'atheneFF.models.Company'> class after both dependent classes have been defined.

[soft raft]

man, I dropped my relationship a year ago.

[dusty helm]

hahaahhahhaha

sorry my grammar, i'm not a natural english speaker

[sly maple]

@everyone any free data set db online for practice with python

would be great if any one could help 🙂

[wintry stream]

Bro

1 @ everyone will not work

2 it would be very rude to @ everyone thousands of people for no reason

You're not the central point in the universe

[sly maple]

Sorry, i didn't know that at everyone will disturb everyone ...this is my first time in discord...never used this.

[pearl heath]

I didnt know (at) everyone would message everyone LIES

[spark stone]

ok, i have my discord bot and sqlite browser

then im deleting my row with warn in it

and try to get it by command

it appears that warn not deleted

[wintry stream]

Hey there. I'm using a postgres database and psycopg2 as the interface. And it works perfectly fine, but does someone have a good resource on how to avoid SQL injection?

since the things I found online showed an example but no explanation, or was just a 5 paragraph long stackoverflow answer

[modest matrix]

@wintry stream https://www.psycopg.org/docs/usage.html#passing-parameters-to-sql-queries
use parameters with your execute, which prevents sql injection. DO NOT use any sort of string concatenation

[wintry stream]

oh that's easy enough, thanks @modest matrix

            self.cur.execute(f"INSERT INTO kicks(uid, executor, timedate, reason) VALUES(%s, %s, "
                             f"CURRENT_TIMESTAMP(1), %s)", (member.id, ctx.author.id, reason))
            self.conn.commit()
```This should work?

the first 2 are int and the third one is string, uid and executor are bigint and reason is varchar

[pearl bronze]

Is there a way to see what value is causing your syntax error with PostgreSQL? What I get now is the error: syntax error at or near "%"

[dusty helm]

guys can anybody help me with sqlalchemy relationships?

[runic pilot]

sure

[dusty helm]

saw some videos already, i think my code is correct too, i just need to know how to populate the db and how that will work

thanks ❤️ haha

i'll get my code, 1 sec

#in the Company table:

    users = db.relationship('User', backref='user', lazy=True)
    solicitacoes = db.relationship('CargoRequest_from_Athens', backref='solicitacao', lazy=True)


# in the user table

    cargo_requests = db.relationship('CargoRequest_from_Athens', backref='cargo_request', lazy=True)
    company_id = db.Column(db.Integer, db.ForeignKey('company.id'), nullable=False, default=0)

# in the request table

    company_id = db.Column(db.Integer, db.ForeignKey('company.id'), nullable=False, default=0)
    user_requesting = db.Column(db.Integer, db.ForeignKey('user.id'), nullable=False, default=1)

3 tables, they should be somehow linked

1 company can have multiple users
1 cargo can be requested by 1 user of 1 company

and of course, 1 company can have multiple requests (should)

hahah

yesterday i got an error when i tried to insert 1 company at my table

sqlalchemy.exc.InvalidRequestError: When initializing mapper mapped class Company->company, expression 'User' failed to locate a name ('User'). If this is a class name, consider adding this relationship() to the <class 'atheneFF.models.Company'> class after both dependent classes have been defined.

solicitacoes is like requests

[runic pilot]

hmm 🤔

it looks like sqlalchemy can't find the user class that it's trying to map to

is it imported somewhere that would be found if you run your project?

[dusty helm]

well, now you told me one thing i had not thinked about

"can't find"

my function was above the user class, may that be the problem

?

oh

yes, i guess that was the problem

the company was inserted

[runic pilot]

👍 nice

[dusty helm]

but by the code, the "links"

are correct?

the correlation between tables

those are all the links i have in my code (relationships)

if they are correct i'll use them as base to make the next ones

[runic pilot]

let me repeat them to make sure I understand correctly:

company (has many) users
user (can request many) requests
company (has many) requests (through users)

is that right?

[dusty helm]

yes, exactly

will be 2 kinds of users (customer side)
manager and common user (this one requests)
manager can see all requests from all users

he will see as the company, but the individual users can see only the cargo they requested

[runic pilot]

trying to find a "has many through" relationship, but I might be thinking back to my RoR days

[dusty helm]

;P

in my head i can only think in 3 kinds

[runic pilot]

I might just add a helper property to a Company

class Company(...):
    ...
    @property
    def reqeusts(self):
        return [user.requests for user in self.users]

[dusty helm]

oh that seems very useful

but

i never made nothing related, could you give me a glimpse of how that works?

i use @ for routes and user_loader only, never seen then in other use

[runic pilot]

sure, do all your tables exist?

and what flavor of sql are you using?

[dusty helm]

i'm using sqlite + sqlalchemy

temporary sqlite, might migrate to pgsql in the future

[grim marlin]

@runic pilot can u explain me sql?

[dusty helm]

all my tables exists

[runic pilot]

I would suggest using pgsql, but it's just an opinion

take a look at the user table schema

what are the columns?

[dusty helm]

id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String(20), unique=True)
    email = db.Column(db.String, unique=True)
    name = db.Column(db.String(30), nullable=False)
    password = db.Column(db.String(1000), nullable=False)
    permission = db.Column(db.Integer)

    cargo_requests = db.relationship('CargoRequest_from_Athens', backref='cargo_request', lazy=True)

    company_id = db.Column(db.Integer, db.ForeignKey('company.id'), nullable=False, default=0)

    active = db.Column(db.Boolean, default=True)
    authenticated = db.Column(db.Boolean, default=False)

these are the columns

[runic pilot]

no sorry I mean in raw sql

.schema user will show the you columns in sqlite

@grim marlin no, sorry, that's a very long discussion and I'm not a teacher

I also unfortunately don't have any online sources to learn it, as I learned it in class

[grim marlin]

Okay

Not a problem

But can u give some short info?

[dusty helm]

1 sec, i'll get the schema

[runic pilot]

SQL (Structured Query Language) is a domain-specific language used in programming and designed for managing data held in a relational database management system (RDBMS), or for stream processing in a relational data stream management system (RDSMS).

(look here for more info https://en.wikipedia.org/wiki/SQL)

[grim marlin]

i googled it already but i didn't understand it😅

[runic pilot]

it's a language to manage a database

[dusty helm]

i see i cant use my sqlite trough my cmd

and in the db browser i could not use that command to get the schema

i'm trying to allow sqlite trough cmd

[runic pilot]

make sure to use the sqlite3 command with the db file as the second argument

[dusty helm]

oh got it

[runic pilot]

so company_id the the thing you need to set on the user to associate it with the company

[dusty helm]

yes, i'm really not sure because i know basically nothing of databases

but

[runic pilot]

user.company_id = company.id

[dusty helm]

i think i want to reference the company by id, idk if that's the best practice

[runic pilot]

that's best practice

[dusty helm]

good

user.company_id = company.id

yes

so, what do i need to change?

is it already set and good to go?

i see the best i can do is on the "user creation screen" i make 1 form that fills 2 models, the user and the company, and in that moment i use the model.id to fill the user.company_id

it will automatically use the right number

idk if that's good too

seems correct

[runic pilot]

that's correct

[dusty helm]

perfect

and when i query the cargo? how to do that?

requests = Request.query.filter_by(company_id=current_user.id).all()

something like this? or there is another way

[runic pilot]

to get the cargo for a specific user?

IMO, don't attach a company ID to the cargo, because the user_id implies a company_id

[dusty helm]

oh that makes sense, i'll remove company_id at the cargo

did it, just one more thing :P

you said before about one property at the company class

class Company(...):
    ...
    @property
    def reqeusts(self):
        return [user.requests for user in self.users]

how does this work?

the @property method

[brazen charm]

it basically makes a read only function

so you get company_obi.requests would call the requests function

without using ()

[dusty helm]

oh my, i really don't get it

hahahaa

that is a decorator, right?

i'll read it https://www.freecodecamp.org/news/python-property-decorator/#:~:text=%40property is a built-in,define properties in a class. here

i think this can help

@runic pilot thanks again for saving the day

[dusty helm]

@runic pilot using that @property i'm getting this error:
TypeError: 'list' object is not callable

and on requesting i think it's not selecting the user, idk why

i put an "default=1" in the end for it to work

class Request(...):
  ...
  user_requesting = db.Column(db.Integer, db.ForeignKey('user.id'), nullable=False, default=1)
  ...

[runic pilot]

what's the full stack trace and what's your code for the property?

[dusty helm]

that error i guess is handle, i was calling the function, but properties are not callable i guess, i took off the () and got another error..

now
sqlalchemy.exc.NoForeignKeysError: Can't find any foreign key relationships between 'company' and 'user'.
is the error

but they are linked i guess

user

company_id = db.Column(db.Integer, db.ForeignKey('company.id'))

company

users = db.relationship('User', backref='user', lazy=True)

databases makes me think i'm retarded

idk why it's so difficult for me to understand the logics

the property in the company class

    @property
    def requests(self):
        return [user.requests for user in self.users]```

and there's another error at the end of the compiler:

sqlalchemy.exc.NoForeignKeysError: Could not determine join condition between parent/child tables on relationship Company.users - there are no foreign keys linking these tables. Ensure that referencing columns are associated with a ForeignKey or ForeignKeyConstraint, or specify a 'primaryjoin' expression.

the code who "calls" the requests is this:

company = Company.query.filter_by(id=current_user.company_id).first()
requests = company.requests```

[chrome vault]

need help

i am getting postgresql error that database does not exist

for database i am using heroku and for query i am using sqlalchemy

[chrome vault]

(psycopg2.OperationalError) FATAL:  database "de1b1fqvi7hlup " does not exist

[vestal sinew]

`Command 'sqlite3' not found, but can be installed with:

sudo apt install sqlite3` weird doesn't Sqlite come by default?

[onyx sierra]

help here someone - https://discordapp.com/channels/267624335836053506/342318764227821568/720800088674533437

[lavish ferry]

how can I check if there more than 5 rows?

like,

SELECT fruit, value
    FROM market```
i want to check if the search will get more than 5 rows

[pure cypress]

If you're doing this via code, you can just check the length of the list/tuple of results returned

[lavish ferry]

COUNT(*)

[pure cypress]

like if len(query_results) > 5

[lavish ferry]

i figured it out

[pure cypress]

Yeah count is the sql solution

[torn sphinx]

If I'll try to get a row from my db that doesn't exist, will I get an error or None? An empty tuple?

[pure cypress]

IIRC an empty tuple

If the select doesn't match any rows

[torn sphinx]

Oh ok

[lavish ferry]

how can I get my timestamp without the milliseconds?

select now()```
returns:
`2020-06-12 23:13:13.658568-03`
i'm using postgresql and the data type is timestamp without time zone

[pure cypress]

Use to_char(now(), 'some format') to customise the format

Here's a table of things for the format string https://www.postgresql.org/docs/current/functions-formatting.html#FUNCTIONS-FORMATTING-DATETIME-TABLE

[dusty helm]

guys, i'm getting the user_id number from an query of messages,

how to get the name from the user table?

[lavish ferry]

now()::timestamp(0) works very well

[dusty helm]

using sqlalchemy

[ember steeple]

hello , how do you put two variables with two (%s) away from each other ? because this is getting me error
mycursor.execute("UPDATE rovan price=(%s) WHERE code=(%s)",(j,f));

[languid merlin]

why not ?

[ember steeple]

Not all parameters were used in the SQL statement
it gave me this error

[regal ginkgo]

anyone knows a host provider can host sqlserver 2000 ?

[regal dagger]

i need help please

im using pgadmin4 and want to divide column Food to two sections

Level 1 and level 2

how do i do that

[gaunt frigate]

I'm pretty new to this, in SQL is it suggested to open a connection to the db each time you want to execute a command (inside a function) or otherwise (outside a function)?

[zinc maple]

depends on your project, connection pooling might be good if it applies to your project

I wouldnt open a new connection for every query you'd keep it open until the entire action/request is "done" at very least so if you need to get user item from db, do some things, update home item in db, and then add a new entry to user-to-home relation, you'd use the same connection for all 3 if those 3 are part of one user request

but if you have more of a running server than a rest server, like with sockets, you probably want to look at pooling

[dusty helm]

guys, i'm getting the user_id number from an query of messages,

how to get the name from the user table? using sqlalchemy ?

1 is the user_id (referencing from user table)

---

the code :

all_messages = Message.query.order_by(Message.id.desc()).limit(5).all()```

[regal dagger]

need help

is anyone online?

[dusty helm]

well, let's try

i'm still waiting 😛

what do you need

[nova timber]

At what point is your data set large enough to use a database?

My use is finding image duplicates by comparing hash data. The data set should be in the thousands

[dusty helm]

i'm using user id as identifier

idk if that's the best practice... but it's working

you can use other things too, as 3 first numbers of user documentation + first letter of firstname, + first letter of lastname

these kind of data don't change

again, idk if this is good practice

i suppose it's not

[torn sphinx]

has anyone connected to sql developer using cx oracle/ oracle instant client from python?

[sonic sparrow]

I started collecting activity data (which games members are playing) on a Discord bot. So far I am only collecting the game names and how many times they have been opened. I am thinking about adding unique users column too, but I am not sure how should I do it efficiently. My naive idea is to add a column with string type, and append new users to that string. So, the row structure would be like this: "game_name, open_count, user_count". Any suggestions for a better way?
I am using sqlite btw

[finite lynx]

How do I combine 2 of my queries?

I want to do something like this

products = products.append(Product.query.filter(Product.seller_username.contains(search_name)))

Or should I create a list of queries and then iterate using for example for product in products[0]?

[glad radish]

I apologize in advance for my English, I'm trying to create a bot for a large number of servers, how can I store data with bot settings for each server?

[solar pollen]

Hello! I'm new to databases. I am watching some videos about postgresql, but they are for version 11, if I use version 12, will there be any differences I have to look out for?

[minor ruin]

SuperMazing, probably not

esp at basic SQL level

[solar pollen]

SuperMazing, probably not
@minor ruin Thanks 👍

[minor ruin]

yep, Database software tends to evolve slowly with backwards compatibility in mind

[solar pollen]

How come I the server has an x icon rather than the typical postgresql icon?

When I try to connect to it with the password; I get an error

[brazen charm]

@solar pollen It means it can see a Active postgres server running but you're not actually connected to it

[solar pollen]

@solar pollen It means it can see a Active postgres server running but you're not actually connected to it
@brazen charm How can I connect to it?

[brazen charm]

You need to double click it and sign in

[solar pollen]

I get an error

(the blue boxes are because I wasn't sure what is and isn't stuff unique to the db)

im going to look this error up and see what I get

Alright, I'm looking through this: https://www.pgadmin.org/docs/pgadmin4/development/connect_error.html#:~:text=could not connect to Server,requests on the address shown.

I have to change the one of the files to listen_addresses=’*’

I'm pretty sure the ’ is just supposed to be a single quote

I'll look on stack overflow for where the file is

[finite lynx]

I am interested in how I cpuld do something like this, query the first any mumber of objects and them query the next of that amount etc.?

[turbid flax]

    money = mycursor.fetchall()
    money = money[0]
    money = money[0]

    if money < amount:
        await ctx.send("Not enough money in this account")

    else:
        stock = random.randint(1, 100)
        percent = random.randint(1, 100)

        if percent > stock:

            ten_percent = int(amount) / int(10)

            int(ten_percent)


            total = int(ten_percent) + int(amount) + int(money)

            Winnings = int(ten_percent) + int(amount)


            mycursor.execute(f"UPDATE customers SET money = {total} WHERE name = {ctx.author.id}")
            mydb.commit()

            await ctx.send(f"You Won: {Winnings}")

        else:
            total = int(money) - int(amount)
            mycursor.execute(f"UPDATE customers SET money = {total} WHERE name = {ctx.author.id}")
            mydb.commit()
            await ctx.send(f"You have lost the stock \nyou have lost the money inputed: {amount}")

Anyone know why i have to restart my python file to get updated stats also im using mysql, this is also a discord bot.

[jade dune]

sounds like youre not closing the connection?

idk if there is such thing

[solar pollen]

Alright, I've been trying for a bit and can't seem to figure it out:

SO: https://dba.stackexchange.com/questions/165290/getting-connection-refused-after-installing-postgresql-and-pg-admin-4
Video: https://www.youtube.com/watch?v=fZQI7nBu32M&t=384s

[solar pollen]

Ah, I'm not quite sure how, but I reinstalled, and.. it works 😄

[solar pollen]

Is it recommended to use postgresql with something other than SQL? in this case; python

[harsh pulsar]

what do you mean by that?

[solar pollen]

what do you mean by that?
@harsh pulsar It's okay, I figured it out.

I hadn't known that sql would still be used within Python libraries (still learning databases). So, I was wondering whether or not I should learn SQL, or just a library that i thought wouldn't include SQL. But, turns out that it does. 👍

[modest matrix]

https://sqlbolt.com/
Is pretty neat

Or use something like SQLAlchemy, but I'd say that's more difficult than just learning sql, and at some point you're going to run into a problem where you need raw SQL anyway

[solar pollen]

Oh, wow; that is cool! 😎 Right now I'm looking at https://www.youtube.com/playlist?list=PLi01XoE8jYojRqM4qGBF1U90Ee1Ecb5tt but I think after the first for (basically explain what sql and postgresql is) I'll use that, thanks!

Ah, I just got a question: If most databases use SQL, how are they different?

[pure cypress]

Performance differences, additional support for data types, more functions, other non-standard sql features, etc.

[solar pollen]

Do they add "commands" to SQL?

[pure cypress]

That's what I mean by functions

So, yes

[solar pollen]

😅 Got it 👍 Thanks!

[pure cypress]

But also they can add clauses

Here's one example I dealt with recently

https://www.w3schools.com/sqL/sql_top.asp

Different syntax for the same thing, as you can see

[solar pollen]

What is the role of the DBMS vs. the Server in a database system?

[fossil flicker]

ive been using .npy files to save data from a cluster and download it on to my laptop and its been working fine but i just made the files alot larger and now it say the data is pickled, anyone know why this has happend

[harsh pulsar]

@solar pollen (R)DBMS is the technically-correct name for what some people call a SQL database

i.e. it's a program (usually running in a client-server configuration) that executes SQL queries, and manages data in the database

the server is the program that actually runs the queries and pulls the data

the client is the program that sends queries to the server

[solar pollen]

Thanks 😃 Just to clarify, the server runs the commands and gets them from the DBMS? @harsh pulsar

[harsh pulsar]

no

the DBMS is the whole thing

[solar pollen]

Ah, I see

[harsh pulsar]

postgres, mysql, sqlite are all (r)dbms'es

(relational) database management system

[solar pollen]

Ah, I get it

The server executes commands that get information from the database

The commands are controlled by the client which decides which commands should be run

whereas, the (r)DBMS is the whole system, and each handles it differently

👍 Thanks for the help

It explains a lot, I wasn't entirely sure how the whole system worked

The server is also where the DBMS is being run iirc

[torn sphinx]

I am using aioredis with python, is there a way I can loop through all of the sets with a certain name? Ping me if you respond, thank you.

[vale lodge]

@torn sphinx Can you clarify on, "all of the sets with a certain name"? Each set needs a unique key, are you trying to match a pattern of keys?

[torn sphinx]

I am very very new to Redis, so I don't have all the information on it

So you can't create other sets with the same name was another one?

[vale lodge]

Correct, Redis is a key/value store. So each key has only one "value" (a value can be a set)

[torn sphinx]

Oh okay, but is there a way to loop through each set?

[vale lodge]

You can use SMEMBERS to loop through a set: https://redis.io/commands/smembers

Do you have multiple sets?

Actually, technically if you're looking to loop through the set, SPOP may be more appropriate

[torn sphinx]

I have one set atm, thank you for the help!

Okay I think I have a better understanding of how Redis works, thank you so much for the help.

[vale lodge]

Not a problem, you can also definitely check out redis labs, they have some good documentation and learning resources too, I've done their RU101 course and it was great. https://university.redislabs.com/

(and it's free!)

[torn sphinx]

Sweet, I'll check that out when I get the chance.

[vale lodge]

Or, I think it was free... Someone correct me if I'm wrong there

[torn sphinx]

So I am trying to add a member to the set, but I get this error: WRONGTYPE Operation against a key holding the wrong kind of value

I am currently testing Redis to see how I like compared to PostgreSQL, but here's my code:

import json
user = _guild.get_member(367492468578582539)
data = {
  "name": user.name,
  "id": user.id
}
await _bot.r.sadd("user", json.dumps(data))

I don't know what that error means

But I do just fine adding a set with the same data

[vale lodge]

You probably already have a key user in your redis db that contains something besides a set. Use flushdb to clear it out if you're just testing. Two things to mention, 1) If you're going to be storing a dictionary you can look into hashmaps and then keeping a set of ids or something like that. 2) if you are going to store json blobs which is totally valid, we do it all the time. I recommend compressing them first (zlib.compress) as you'll save a lot of space and thank yourself later.

[torn sphinx]

Ah okay, thank you very much again.

I will keep testing, but I will probably have more questions.

Also does sadd add a new set or something? Sorry if I don't completely understand all of this.

[vale lodge]

Redis is a great tool but it requires some basic knowledge of data structures to design effective schemas. It is very different from relational dbs like postgres. It is much faster and you need to think much more carefully about your access patterns.

sadd adds a new member to a set or creates a set if it doesn't exist.

[torn sphinx]

Ah alright, well I'll keep testing and looking around.

When I use the smembers function for a certain key in a set, it returns this type: <class '_asyncio.Future'>.

Is that the correct response?

[vale lodge]

Yes, I think you mentioned you're using an async redis client so commands will return a future that you'll then have to await. I've never used it myself though.

[torn sphinx]

Oh okay, also I'm still not quite understanding the sadd function, it adds a member to a set but when I try to add a new member to the same set, it returns the same error I got up there, WRONGTYPE Operation against a key holding the wrong kind of value. How would I fix this problem?

[vale lodge]

Hmm, can you send the output of running the commands in redis-cli?

That error should only occur if your key has a non-set value

Maybe just check the value with TYPE key in the redis-cli https://redis.io/commands/type

[torn sphinx]

TYPE "testing" returns string

[vale lodge]

Ahhh I see. So the SET command, does not create a set. It sets a static value. You need to always use SADD when adding members to a set.

lol that is confusing but SET is what you use to set a static value, not create a set.

[torn sphinx]

Okay, so how would I create a set

[vale lodge]

SADD testing brendan
SADD testing biggicheese

SADD will create a set if the key is empty

https://redis.io/commands/sadd

If key does not exist, a new set is created before adding the specified members.

[vale lodge]

👏

[torn sphinx]

What is this?

[vale lodge]

Set the specified expire time, in seconds

https://redis.io/commands/set#options

[torn sphinx]

I never even knew it could do that, dang this is very nice

[vale lodge]

Yes you can expire keys, since scanning is an expensive operation. If you have complicated time-based removal mechanisms your key tracking gets complicated.

[torn sphinx]

That's good

I think I will go test more now, thank you so much for your help!

[vale lodge]

No problem

[torn sphinx]

Since the set command doesn't create a set, how would one add an expiring time to sadd? Ping me if you respond, thank you.

I used await self.bot.redis.expireat(f"channel_create:{server.id}:{moderator.id}", 1800) which seemed to work, but it doesn't seems to be adding the new members

Here is my full code:

                await self.bot.redis.sadd(f"channel_create:{server.id}:{moderator.id}", channel.id)
                await self.bot.redis.expireat(f"channel_create:{server.id}:{moderator.id}", 1800)```

Never mind, figured it out

Instead of expireat, it's expire

dbms

DBMS

[torn sphinx]

DBMS

[torn sphinx]

DBMS

[regal dagger]

need help with pgadmin4

pls ping me

if anyone online

[gray dune]

How can I select the last record from the table?

[jade dune]

https://stackoverflow.com/questions/5191503/how-to-select-the-last-record-of-a-table-in-sql

[heady elk]

    id integer,
    prefix text
    )""")

INSERT INTO prefixes VALUES (486870895978086400,$)```

`Command raised an exception: OperationalError: table prefixes has 2 columns but 1 values were supplied`

 Idk what am I missing

SQLite already have 2 columns

[modest matrix]

put ' around your prefix

[torn sphinx]

what should i do if i get this error

[wintry stream]

Password got rejected

What you should do is open your terminal, login as root

then do su - postgres

then create a new account and create all your databases on there

https://www.a2hosting.com/kb/developer-corner/postgresql/managing-postgresql-databases-and-users-from-the-command-line

something like that should work

[torn sphinx]

how do i enter the root server @wintry stream

how do i log in as root

[wintry stream]

you have the database running on a linux or windows machine?

[regal dagger]

why is this channel always inactive

[modest matrix]

It's not. Just ask your question if you have one, if someone knows, you'll get an answer.

[wintry stream]

It's more that databases are a lot simpler as to say, and for instance #discord-bots is very popular where databases are a lot less

[modest matrix]

There's also a lot of different libraries and software with databases, so the pool of people knowing the solution for your specific problem is even smaller, I guess

[frozen ocean]

whats the difference between using a .sqlite and a .db file? i created a sqlite file using vscode and my db browser(sqlite) created a .db file

[wintry stream]

.db is general databasef ile

should be compatible with any db software

.sqlite is a proprietary file type for sqlite most likely

and means it might be incompatible with other db software

that would mean that within sqlite their proprietary file format might be more efficient or more featureful

[frozen ocean]

i see, that makes sense thanks!

[runic pilot]

should be compatible with any db software
that's not necessarily true, a file extension is just a file extension

e.g. you can't open a .db file with postgresql if it was created with sqlite

[torn sphinx]

can I say that partitions are like a logical index that groups records together

[solar pollen]

If a (r)DBMS is considered the whole system (which includes: server, client, database), and some examples are: postgres, mysql, etc. how come the database part is also from the same options?

Does the rDBMS come "packaged" with the database?

[harsh pulsar]

the rdbms is the database

a "database" is a generic term

either referring to a collection of data or some software that allows you to access and organize said data

a RDBMS or DBMS is a specific kind of database engine/program/system/whatever

postgresql is a RDBMS

Relational Database Management System

it is a management system for relational databases

[torn sphinx]

hello

[harsh pulsar]

@solar pollen you are way overthinking this stuff

[solar pollen]

Ah, okay, so the server executes commands for the dbms, and the client sends those commands

[torn sphinx]

on windows @wintry stream

sorry for the delay

[wintry stream]

ah okay then you just open the psql shell direct

from there you should be able to make an extra account with your own password

[torn sphinx]

simple way: do i just type psql in C:\\users\username?

[wintry stream]

hmm wait i just realized

during setup of the server

it asked you to enter a password

it's the same password on the admin site

and you can make a new account with new password from the psql shell

[torn sphinx]

so i typed psql shell in cmd

and i got this error psql: FATAL: password authentication failed for user "Angelo Hoft"

my name is not Angelo Hoft btw.

so maybe it's asking for his password. If it is i don't know it.

[frozen ocean]

is sqlite a little overkill if i want to just store a list?

[solar pollen]

is sqlite a little overkill if i want to just store a list?
@frozen ocean What is the list being used for?

[frozen ocean]

i'm trying to store a list of disocrd ids, probably no more than 10

[solar pollen]

If it is as small as ten, you might want to store it in a JSON file, but it is really up to you. If it is always the same 10 id's, then it wouldn't be worth either of those because you could just define the list as my_list = [1, 2, 3] with whatever the id's are

[frozen ocean]

okay, yeah i want it to be changable so i think json would be fine

[solar pollen]

👍 The only downside is that you wouldn't want something to ever happen to that JSON file, whereas with a database

But, if you are careful not to mess it up, you'll be totally fine 😄

SELECT COUNT(*) FROM <table name>;

I'm not sure that I get this. I thought that SELECT shows a certain column. But here it is showing a number of the number of rows that are in each column.

Can SELECT show more than just columns?

[harsh pulsar]

@frozen ocean the problem with json is that you need to load and re-save the file every time you want to modify it. and if 2 things try to modify the file at the same time, bad things happen. sqlite does not have that problem

@solar pollen COUNT() is a function. you are selecting 1 column, the result of the COUNT() function

some functions "aggregate" data and some functions do not

count is one such aggregating function

select
    count(*) as n
    sum(column1) as column1_sum
from
    my_table;

count isn't some special syntax, it's still returning rows

[frozen ocean]

okay, if i were using a sqlite file with multiple tables, how would i connect to an individual table within a file? right now i'm using db = sqlite3.connect('main.sqlite') but if i had multiple tables in that file what would i do?

[solar pollen]

Ah, that makes sense, I hadn't thought about COUNT actually returning a column

[harsh pulsar]

@frozen ocean you don't connect to a table. the table is part of the database, you specify the table you want to use in each query

[frozen ocean]

ah, so its db.cursor().execute("SELECT ... FROM {table} WHERE ...")

also, can you manually write in to a database using DB browser for sqlite

[patent creek]

How can I get a SSL certificat for my raspberry pi 4 to connect to my databse, which uses https ?

[stoic harness]

in the aiosqlite package, if I do

async with db.execute('INSERT ....') as c:
  print(c.laswrowid)

do I need to await db.commit() after the context manager?

[wintry stream]

if i quickly skimp over the docs @stoic harness, aiosqlite by default make everything a transaction yes

so you will need to commit/rollback

(only reading doesn't need commit ofc)

[stoic harness]

well thanks for the info!

I wasn't sure as the normal sqlite3 commits for you when you use it as context manager, I think

[wintry stream]

I dont see anything in the docs about manually starting a transaction

so that's why i assume it automatically makes a transaction, and you have the .commit() and .rollback()

you can always run a test to see if it'll work without .commit, but assuming they have the function i reckon it doesn't auto commit

[frozen ocean]

        skillsdb = sqlite3.connect('cogs/skills/skills.sqlite')
        skillscursor = skillsdb.cursor()
        skillscursor.execute(f"SELECT list_of_skills FROM skills WHERE list_of_skills = {arg}")
        result = skillscursor.fetchall()

When I call this function, and use arg as 'woodcutting', why do i get the error: OperationalError: no such column: woodcutting? Here is my database:

I am able to call skillcursor.execute(f"SELECT list_of_skills FROM skills) where it returns [('woodcutting',), ('fishing',)]

[celest blaze]

looks like your columns are "list_of_skills" and "Field2"

I think SQL thinks you're asking "show me all the items in the list_of_skills column that are the same as the item in the woodcutting column"

probably because there's no quote marks in the query

you probably want something like "SELECT list_of_skills FROM skills WHERE list_of_skills = ?", (arg, )

[vocal moon]

InvalidAuthorizationSpecificationError: no pg_hba.conf entry for host "ip", user "po stgres", database "darealbot", SSL off

[celest blaze]

sounds like you passed the string "ip" where you should have passed either an IP address like 127.0.0.1, or a hostname like localhost or friendly.postgresql.server.host.com

[vocal moon]

i replaced my ip with ip so people could not see it

[celest blaze]

which makes it harder for me to diagnose the problem, as you can see

anyway, that was the only idea I had; I don't know anything about pg_hba.conf

[vocal moon]

i didnt think you would need the ip adress of my server?

[solar pollen]

Hello 👋 I was wondering; what is the PRIMARY KEY datatype was and how is it different from int in SQL? also, what does SERIAL PRIMARY KEY mean? Thanks! :D

[tall spoke]

Trying to use asyncpg and using the UPDATE command the data doesn’t change

[harsh pulsar]

@solar pollen primary key is not its own data type

serial is the data type here - equivalent to integer autoincrement in other databases

a primary key is a unique row identifier

typically it is also represents an index, which is an auxiliary data structure that can improve join and where performance in many cases

so the primary key is a unique index which also has the special privilege of being the main row identifier

for more information on primary keys specifically in postgres look here https://www.postgresql.org/docs/current/ddl-constraints.html#DDL-CONSTRAINTS-PRIMARY-KEYS

and perhaps most importantly,

Relational database theory dictates that every table must have a primary key. This rule is not enforced by PostgreSQL, but it is usually best to follow it.

[solar pollen]

Ah, okay, thank you @harsh pulsar :D

[frozen ocean]

whats the best way to go about looping through an entire SQLite database?

[torn sphinx]

Anyone know some good tutorials showing how to use SQLite3 for discord.py?

[thorny crane]

#discord-bots

[harsh pulsar]

@frozen ocean what are you trying to do specifically?

usually any workflow where you have to programmatically loop through tables reflects a weird design

[frozen ocean]

@harsh pulsar i have a loop every minute that checks through a database of users to get their id and a varaible called 'active_skill' inorder to update their skill_xp based on that active skill, if that makes sense? So I have to loop through the whole database to get every user.

[harsh pulsar]

that would get very slow if you had a lot of users

but if you only have a few you can do that

[frozen ocean]

yeah... i'm not sure if there's any other way to do it honestly, the best i could do is slow down the interval for the outer loop deciding how often to loop through the users. i'm hoping to test it out on no more than 20 users

[harsh pulsar]

for good performance you would probably have to build an in-memory cache and periodically update/invalidate it. but for your case you can just select user_id from users and .fetchall() that

a better design might send a trigger to update their active skill whenever it changes

e.g. something like

class MyRPG:
    def __init__(self, database):
        self.database = database

    def _save_active_skill(self, user_id, skill_id):
        self.database.execute('update users set active_skill = ? where user_id = ?',
                              (user_id, skill_id))

    def set_active_skill(self, user, skill):
        user.active_skill = skill
        self._save_active_skill(user.id, skill.id)

this is all hypothetical, but something like this

that completely eliminates the need to loop like you described

[frozen ocean]

yep, i have a function to set skills already, i was wondering if i should use something like

#result = cursor.execute("SELECT user_id, active_skill FROM users")
for user in result:
  do something

also, for the class (i'm doing this in discord.py, but its pretty similar

class SkillsCog(commands.Cog, name = "Skills"):
  def __init__(self, client):
  self.client = client

  self.usersdb = sqlite3.connect('databases/users.sqlite')
  self.userscursor = self.usersdb.cursor()

  build_skills.start()

  @tasks.loop(minutes = 1)
  async def build_skills(self):
    result = self.userscursor.execute("SELECT user_id, active_skill FROM users WHERE active_skill != ?", ('',))
        for user in result:
            print(user)

[harsh pulsar]

hm

i see, you're going the other way

why does this need to run every minute?

like why do you need to do this big data refresh from the database?

also i definitely do not recommend reusing a cursor like that

1 cursor per query

also... i know i'm not really answering your question directly. but sometimes questions don't have a good answer and you should consider just using a different technique instead

[thorny crane]

#discord-bots message

[thorny crane]

ping me if you can help

[strange onyx]

Is anyone good at using MongoDB here?

What code would I right to make a new collection in a specified database

Ping me if you can help

[south sparrow]

what would be good db cloud server to be used with python and raspberry pi?

[unique junco]

Hey guys, I'm using legacy database with DJANGO and I'm trying to make a query that will get me the data of two different models that share a common field. I'm almost there but the orm whill make an inner join instead of a regular LEFT JOIN I need.

my query is

users_data = AdDmPersonne.objects.filter(personne_etablissement__etablissement_id=establishment_id).select_related('ad_personne')

does anyone have a idea how I could switch to a LEFT JOIN , without doing a raw request?

[regal dagger]

anyone here

how do i remove a row that has fish from here?

using pgadmin4

[hazy mango]

DELETE FROM table WHERE name='fish'?

[regal dagger]

i tried that @hazy mango

get this error

tried this

[hazy mango]

'fish' not "fish"

"" looks for a column, '' looks to a string

[regal dagger]

oh thanks a lot!!

[quartz star]

Hey guys how do i run a SQL file in postgresql and see the tables?

[torn sphinx]

Could anyone please help me? I keep getting this error: ```py
from ..db import db
ImportError: attempted relative import with no known parent package

[wintry stream]

@torn sphinx the ..db does not exist or cannot be found

@quartz star i'm sorry?

[torn sphinx]

@wintry stream it works for other people ???

[wintry stream]

did you pip install the lib?

[torn sphinx]

whats the lib ??????????

@wintry stream

[wintry stream]

the thing you are importing

the library

[torn sphinx]

im trying to figure out what its lib is

[wintry stream]

did you pip install the library?

[torn sphinx]

to pip install it

[wintry stream]

if you want to use the asyncio library you first do pip install asyncio and then in your actual code do import asyncio

it's the exact same name

[torn sphinx]

so pip install ..db

says nothing found

i installed db

[wintry stream]

well db is not a lib

[torn sphinx]

?????

what then

[wintry stream]

..db is not a library

for databases you have many options

[torn sphinx]

could you tell me what it is?

been tyring for 30 mins

[wintry stream]

oh i found out that db.py is a library, but it's one that isn't used like at all

but that's db.py

not ..db

[torn sphinx]

how do i install?

[wintry stream]

but get one that fits your needs

find a library that fits your needs

[torn sphinx]

k

[wintry stream]

then pip install it

and then import it

[quartz star]

@quartz star i'm sorry?
@wintry stream I have a code created from sql and want to run it in postgresql i'm still learning how to create and manage a db

[wintry stream]

you'd just have to set up a postgres DB

the server can make the database on its own

I don't think you can add another DB from another server application to postgres

or you might have to google a tutorial on that if it is possible

[quartz star]

I am actually copy pasting it the file format is in sql and is actually reading in psql just want to view the tables but let me try that also

[river escarp]

Anyone here?

[torn sphinx]

no

[river escarp]

But

You

Might you help me?

[torn sphinx]

What's the best way to get familiar with sqlite?

[river escarp]

Any boys here tho?

[pearl heath]

hey, I am making a a few classes that interact with a flask-sqlalchemy database and I don't want to run the flask server... just have a single file sqlite3 database for stuff. Maybe some more complex schema down the road but for now, just a serverless flask DB that stores information in a portable format...

Problem is, Compound.query.filter_by(cid = query_cid).first() doesn't seem to actually query the database... All of this individually worked as I have been running it to work on the formatting and thats how I discovered I needed to add extra database fields... so something changed and I can't find out what please help

What am I doing wrong? The code is here
https://github.com/flyingfishfuse/discord_bot/blob/master/discord_chembot/pubchem_standalone_monolithic_test.py

it seems to mess up at internal_local_database_lookup on line 228
called by validate_user_input on line 468.

[torn sphinx]

When I create a table in sqlite, how can I add more columns to it?

[pearl heath]

@torn sphinxhttps://flask-sqlalchemy.palletsprojects.com/en/2.x/models/

[torn sphinx]

What is this?

[pearl heath]

read it, I bet in less than 60 seconds youll have your answer

[torn sphinx]

It's nothing like I've seen in tutorials.

[pearl heath]

class User(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String(80), unique=True, nullable=False)
    email = db.Column(db.String(120), unique=True, nullable=False)

    def __repr__(self):
        return '<User %r>' % self.username

Use Column to define a column. The name of the column is the name you assign it to.

[torn sphinx]

I'll show you how I've see people from tutorials do it.

import sqlite3

conn = sqlite3.connect('Testing.db')
c = conn.cursor()

c.execute("INSERT INTO Testing VALUES ('Corey', 'Schafer', 50000)")

c.execute("SELECT * FROM Testing WHERE last='Schafer'")

print(c.fetchone())

conn.close()```

This is what I got from a tutorial.

[pearl heath]

ok we should talk about how you use raw SQL commands in your script

I don't ever do that and I don't know anyone who does anymore

[torn sphinx]

tbh i basically no nothing about sqlite

[pearl heath]

neither do I lol

[torn sphinx]

in this server it doesn't seem like anyone does

[pearl heath]

thats why I rely on the backend of the python lib to handle that for me

and I suspect everyone here does as well

you might be better off either learning a more pythonic way of doing SQL stuff, or asking in a specifically SQL discord server

[torn sphinx]

Is there one?

[pearl heath]

lol I dont klnow

[torn sphinx]

i kinda doubt that

[pearl heath]

I am having trouble finding a place I can ask my question

even the IRC channel doesnt reply

[harsh pulsar]

what is your question

oh, i see

usually the same rules apply here as on irc - if nobody knows the answer, nobody says anything

[pearl heath]

ah

[harsh pulsar]

it's not clear what your problem is

you say "it seems to mess up" -- do you get an error traceback?

[pearl heath]

it should be spitting out a bunch opf information but it doesnt query the database

[harsh pulsar]

"doesn't seem to actually query the database" doesn't make much sense either

[pearl heath]

no error, run the script, it just screws up on that one line

and doing a Compound.query() doesnt work except for immediatley after the test lines adding test entries to the db

[harsh pulsar]

what does "screws up" mean

what does "doesn't work" mean

[pearl heath]

doesnt work means , when I use internal_local_database_lookup() to retrieve a sqlalchemy database entry using the line Compound.query.filter_by(id_of_record = entity).first(), it does not seem to return anything , it should return None or something. it doesnt do either and when i try to add a print statment to print a test entry it doesnt work... the same code that works on line 202 does not work else where regardless of scope

and this is happening on both windows and linux, across python in anaconda and regular windows install and on linux in a regular python install and an anaconda install

[harsh pulsar]

when i try to add a print statment to print a test entry it doesnt work

what does that mean?

functions can never truly do nothing

even this function

def f():
    pass

returns None when it's called

if you do this: result = Compound.query.filter_by(id_of_record = entity).first() , what do you see when you print(result)

[pearl heath]

My internet got shut off for some reason not even sixty seconds after getting a text about it

Wtf

It doesn't print anything it doesn't return a none or nonetype or false either

[harsh pulsar]

@pearl heath that's simply not possible

set a breakpoint or enter a debugger on line 221 and manually inspect the value of lookup

https://github.com/flyingfishfuse/discord_bot/blob/master/discord_chembot/pubchem_standalone_monolithic_test.py#L220

[frozen ocean]

cursor.execute("SELECT user_id, active_skill, ? FROM users", (active_skill + "_xp", ))```
How would I go about programming something like this where my table has `user_id, active_skill, and (active_skill)_xp` as columns (example: `user_id, active_skill, woodcutting_xp` where active_skill = 'woodcutting')

[harsh pulsar]

you can't parameterize column names

you have to use an f-string

and you absolutely must be careful not to accept arbitrary user input

generally it's not a good idea to have a database structure like this, for that reason among others

[frozen ocean]

ah yeah i see. my best solution so far was to just pull everything SELECT * FROM users and interate through using a for loop, but the tuples dont contain information about what column they are from, is there a way i could somehow reference what column they are from?

[torn sphinx]

im trying to learn how to work with sqlite, what should i try to make? like a mini project i mean

[noble oak]

Maybe make some sort of password manager

[harsh pulsar]

@frozen ocean the columns are returned in the order they exist in the table

or the order they exist in your query