databases

[arctic badger]

no, people type .start and the bot just uses their discord id to make them account in the database.

[brave bridge]

Python is dynamically typed -- there's no real guarantee that ctx.author.id is an integer. If there is ever a bug in that part of discord.py, you'll be in trouble

It's not very likely in this case, just be consistent in using a single way of substituting parameters.

Otherwise you'll need to evaluate whether it's safe on every occasion. And once in a while you'll get it wrong.

I bet it's also (slightly) faster because it uses prepared statements, but I don't know if pymysql does

[stuck shell]

It is simply good practice to write is as a parameter always.... you will start another project and forget about that and you will be f*cked 🙂

[brave bridge]

yeah, in this case it's not really sorry, more like "better safe than fucked"

[stuck shell]

@brave bridge parameter with %s will not make a prepared statement.... question mark style will do

afaik

[brave bridge]

ah, maybe

I never used mysql

[stuck shell]

i used mysql years back for the last time....

[lean walrus]

what's the correct encoding for the .db file?

[brave bridge]

@lean walrus You can't read SQLite databases in a text editor.

PyCharm has a built-in database viewer: https://www.jetbrains.com/help/pycharm/database-tool-window.html

[austere portal]

you can also use this https://sqlitebrowser.org/

[brave bridge]

or that, yes

[lean walrus]

oh

which one is better?

i'll just prefer the built-in

wait, then how to use it?

[austere portal]

View -> Tool Windows -> Database

[lean walrus]

umm

it's not there?

[austere portal]

hmm

i guess thats a feature in pycharm pro...

[lean walrus]

[austere portal]

this is free

[lean walrus]

https://download.sqlitebrowser.org/DB_Browser_for_SQLite-v3.12.2-x86_64.AppImage this one?

wait no

[austere portal]

what os are you using?

[lean walrus]

windows

[austere portal]

64 bit or 32 bit?

[lean walrus]

64

[austere portal]

https://download.sqlitebrowser.org/DB.Browser.for.SQLite-3.12.2-win64.msi

[lean walrus]

thanks

[real timber]

i have a table in bigquery which I'm appending data to - each dataset has a data_id which is unique to that particular data (but there are duplicates of it within the particular dataset).

Sometimes I want to be able to update the dataset which is in the table, so i might have data locally with several id's - and want to replace each of these in the bigquery table.

I was thinking about just iterating over the data-id's, dropping the rows from the table that contain those id's, then uploading the data to replace them.

I'm just wondering if there's anything standard approach wise here, or if that sounds off at all

ping me if you respond please.

[stuck shell]

@real timber how it come that you have duplicate id's? I know bigdata is not a sql but there is not a pk constrain? Or why it happening?

[real timber]

because the data-id is an id for the dataset, not the row within the dataset

🤔 I could make it so that the row from the dataset was appended to the data-id, and that way it would be a unique id to that row rather than the dataset

[stuck shell]

Maybe generate artificial id's just for purpose of identity of the rows? Sequence or UUIDv4 maybe?

I am quite a fan of uuid's because it is possible to generate them on multiple influx nodes.

[real timber]

I could generate ID's for the row id's sure - tho i'm not following how this helps with the original problem

i have the data_id and the original row number (as this is melted from wide -> long, and i want to be able to obtain the original form)

so if you use [data_id, row_num] you'd have a unique key for each row of the original data

[stuck shell]

Ahh sorry i am idiot... i though you want something else 😄

[real timber]

all good - my issue is just about replacing data in the big table really

kinda scared i'll have cases when i delete, then network drops, and it's never uploaded 🤔

[torn sphinx]

Hi, Does anyone know how to insert an image into qsql table using psycopg2, python3? This is my code so far:

        cursor.execute("""INSERT INTO public.shark_images (species, photo) VALUES (%s, %s);""",
("white", byte('/Users/elenaperez/Desktop/sharks/nurse1.jpg')))

the schema is called public and the table is called shark_images. byte() is coming up as an undefined variable. Any help would be appreciated 😀

[real timber]

maybe that's overthinking a bit for now tho

[stuck shell]

What byte() function do?

[torn sphinx]

it is not actually a function that I have else where in my code. I looked online and someone said that is how you insert an image by putting the location of it inside byte()

so it is wrong that I use byte() but I am not sure how to go about it

[stuck shell]

I can't found any reference of that function in build-ins soooo first test what that function returns really. I think you basically trying to insert nothing.

@torn sphinx basically you have 2 options afaik:

1. make column TEXT and encode image as base64
2. make column BLOB and insert it as binary (https://www.postgresqltutorial.com/postgresql-python/blob/)

HINT: you can ommit public statement inside into. If ommited psql will assume that you using public 🙂

[prime kelp]

aiosqlite how can i rename a field of a db

[torn sphinx]

ok cool, thank you so much. I will try out these options. I thought bytea was used instead of blob these days. Newbie here! Hopefully I can now insert an image!

[stuck shell]

@torn sphinx np, and first figure out what option will be better for you. base64 is a bit wasteful but good directly for web app. blob is better when you use those images later with PIL for example 🙂

[torn sphinx]

ok thanks again, I will see which option is better

[prime kelp]

aiosqlite how can i rename a field of a db

[austere portal]

You can use a ALTER TABLE statement

ALTER TABLE table_name
RENAME COLUMN current_name TO new_name;```

[prime kelp]

in the cursor.execute?

[austere portal]

yes

and commit at last

@prime kelp

[prime kelp]

ah okay cheers

[next sun]

2021-08-20 09:14:31:db_connetconnect_db:ERROR:Error while connecting to PostgreSQL => server closed the connection unexpectedly
    This probably means the server terminated abnormally
    before or while processing the request.
Traceback (most recent call last):
  File "/home/connect_db.py", line 21, in db_conn_ssh
    conn = psycopg2.connect(**config.db_settings, port=ssh_server.local_bind_port)
  File "/usr/local/lib/python3.8/dist-packages/psycopg2/__init__.py", line 122, in connect
    conn = _connect(dsn, connection_factory=connection_factory, **kwasync)
psycopg2.OperationalError: server closed the connection unexpectedly
    This probably means the server terminated abnormally
    before or while processing the request.

I am continuing to get this error everyday (there's a cron job to run the script at 4am)

this is what it logs out from the Error on the psycopg2 module

but when I run the script manually around the same time it actually prints a slightly different message on the console

2021-08-20 09:14:31,878| ERROR   | Could not establish connection from ('127.0.0.1', 46699) to remote side of the tunnel

I feel like I am closer to get the error fixed but there's not much out there when googling

any help?

Many thanksssss in advance

[loud rain]

what is the best way to store data i wanne store Named Entity Recognition data and juse it when its needet

[quartz stump]

[MongoDB] Maybe that's a wrong place to ask - but I really will appreciate any responses.
i searched in the documentation and found https://docs.mongodb.com/manual/reference/operator/aggregation/mergeObjects/#-mergeobjects, but it generates left-join embedding, which I try to avoid.

How to merge data from different collections flat?

Sample data:

db.firstcollection.insertOne({name: "Cheddar", origin: "United Kingdom", region:"Somerset"})
db.secondcollection.insertOne({name:"Cheddar", texture:"very hard", pasteurised: "sometimes"})

and desirable output:
{name: "Cheddar", origin: "United Kingdom", region:"Somerset", texture:"very hard", pasteurised: "sometimes"}
or -
specified fields to return -
{name: "Cheddar", region:"Somerset", texture:"very hard"}.

I hope you like my examples 😄

[smoky radish]

@next sun to me that postgres connection error looks like something is blocking or pausing the script for a long enough time that postgres times out your session because it was inactive for a long time, essentially disconnecting you. could you check whether that could be the cause?

[torn sphinx]

Anyone know the issue?

Code: ```py

DEFAULT_LOGCHANNEL = 'mod-logs'

async def on_ready(bot, message):
if not message.guild:
return DEFAULT_LOGCHANNEL(bot,message)

logchannel = await bot.db.fetch('SELECT logchannel FROM guilds WHERE guild_id = $1', message.guild.id)
if len(logchannel) == 0:
    await bot.db.execute('INSERT INTO guilds(guild_id, logchannel) VALUES ($1, $2)', message.guild.id, DEFAULT_LOGCHANNEL)
    logchannel = DEFAULT_LOGCHANNEL
else:
    logchannel = logchannel[0].get("logchannel")
return logchannel(bot,message)

async def create_db_pool():
bot.db = await asyncpg.create_pool(database = "tutorial2", user = "postgres", password= "20266137" )
print("Connected to the DataBase.")

@bot.command()
@commands.has_permissions(administrator=True)
async def setlogchannel(ctx, logchannel):
await bot.db.execute('UPDATE guilds SET logchannel = $1 WHERE guild_id = $2', logchannel, ctx.guild.id)
await ctx.send(f"The logchannel has been updated! New logchannel: {logchannel}")```

Error: Traceback (most recent call last): File "/Library/Frameworks/Python.framework/Versions/3.9/lib/python3.9/site-packages/discord/ext/commands/bot.py", line 939, in invoke await ctx.command.invoke(ctx) File "/Library/Frameworks/Python.framework/Versions/3.9/lib/python3.9/site-packages/discord/ext/commands/core.py", line 863, in invoke await injected(*ctx.args, **ctx.kwargs) File "/Library/Frameworks/Python.framework/Versions/3.9/lib/python3.9/site-packages/discord/ext/commands/core.py", line 94, in wrapped raise CommandInvokeError(exc) from exc discord.ext.commands.errors.CommandInvokeError: Command raised an exception: UndefinedTableError: relation "guilds" does not exist

[quartz stump]

Relation "guilds" does exist?

[torn sphinx]

?

Like is the error saying that it doesn't exist in the db?

And it's postgresql

[quartz stump]

exactly

[torn sphinx]

ohhh

And also

does the code look right? I just tested it and everything works but I want to make sure it's right and that it really updated the data

[quartz stump]

Write unit/functional test(s) 😉

[torn sphinx]

Oh ok

So now I'm getting an error when I'm trying to send a message to the log channel

Code for it:

@bot.command()
@commands.has_permissions(administrator=True)
async def setlogchannel(ctx, logchannel):
    await bot.db.execute('UPDATE guilds SET logchannel = $1 WHERE guild_id = $2', logchannel, ctx.guild.id)
    await ctx.send(f"The logchannel has been updated! New logchannel: `{logchannel}`")
    await logchannel.send("hello")```


Error: ```    await logchannel.send("hello")
AttributeError: 'str' object has no attribute 'send'

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

Traceback (most recent call last):
  File "/Library/Frameworks/Python.framework/Versions/3.9/lib/python3.9/site-packages/discord/ext/commands/bot.py", line 939, in invoke
    await ctx.command.invoke(ctx)
  File "/Library/Frameworks/Python.framework/Versions/3.9/lib/python3.9/site-packages/discord/ext/commands/core.py", line 863, in invoke
    await injected(*ctx.args, **ctx.kwargs)
  File "/Library/Frameworks/Python.framework/Versions/3.9/lib/python3.9/site-packages/discord/ext/commands/core.py", line 94, in wrapped
    raise CommandInvokeError(exc) from exc
discord.ext.commands.errors.CommandInvokeError: Command raised an exception: AttributeError: 'str' object has no attribute 'send'```

[torn sphinx]

Do you know the issue @quartz stump ?

Like how can I send a message to the new channel

On events, commands and other stuff

[obtuse oasis]

guys how can i see the relationship between 2 tables?

in mysql

[quartz stump]

your logchannel is a str type (instead of something with send method), nothing more nothing less

[unkempt nest]

i need some advices with my mysql.
Should i use varchar to put a hard limit of 2000 on the text length or just use text?
I'm using this for commands and i'm just getting advice on whether varchar is easier to process or should i just skip text and let discord's character limit do the job

[pure sleet]

varchar is limited to 255

[unkempt nest]

wait really?

[pure sleet]

yes. look it up

[unkempt nest]

phpmyadmin says it's a variable from 0 to 65535

[pure sleet]

varchar? nah that's text

[unkempt nest]

check this

here's the one for text

[pure sleet]

well that's weird. but what db are you using?

[unkempt nest]

it's mysql with innodb as engine

[pure sleet]

damn, thought it was 255

that's what i know

[unkempt nest]

yeah varchar is kinda like that

takes 1 + c bytes (for M ≤ 255) or 2 + c (for 256 ≤ M ≤ 65535) bytes of disk space where c is the length of the stored string

i saw this from stackoverflow

varchar basically acts as tiny text when it's smaller than 255 chars

[pure sleet]

i see

[torn sphinx]

Anyone know the error?

Code: ```py

@bot.command()
@commands.has_permissions(administrator=True)
async def test(ctx):
logchannel = await bot.db.fetch('SELECT logchannel.id FROM guilds WHERE guild_id = $1', ctx.guild.id)
await logchannel.send(f"Test complete")```

Error: Traceback (most recent call last): File "/Library/Frameworks/Python.framework/Versions/3.9/lib/python3.9/site-packages/discord/ext/commands/bot.py", line 939, in invoke await ctx.command.invoke(ctx) File "/Library/Frameworks/Python.framework/Versions/3.9/lib/python3.9/site-packages/discord/ext/commands/core.py", line 863, in invoke await injected(*ctx.args, **ctx.kwargs) File "/Library/Frameworks/Python.framework/Versions/3.9/lib/python3.9/site-packages/discord/ext/commands/core.py", line 94, in wrapped raise CommandInvokeError(exc) from exc discord.ext.commands.errors.CommandInvokeError: Command raised an exception: UndefinedTableError: missing FROM-clause entry for table "logchannel"

[grim vault]

logchannel.id means column id from the table logchannel but you only select from a table named guilds, so which table to you want to select from or where does this logchannel came from?

[obtuse oasis]

someone knows a good website to make a visual table connections like this.

[torn sphinx]

Anyone know how I can fix this error?

Error: Traceback (most recent call last): File "/Library/Frameworks/Python.framework/Versions/3.9/lib/python3.9/site-packages/discord/ext/commands/bot.py", line 939, in invoke await ctx.command.invoke(ctx) File "/Library/Frameworks/Python.framework/Versions/3.9/lib/python3.9/site-packages/discord/ext/commands/core.py", line 863, in invoke await injected(*ctx.args, **ctx.kwargs) File "/Library/Frameworks/Python.framework/Versions/3.9/lib/python3.9/site-packages/discord/ext/commands/core.py", line 94, in wrapped raise CommandInvokeError(exc) from exc discord.ext.commands.errors.CommandInvokeError: Command raised an exception: AttributeError: 'list' object has no attribute 'send'

Code: ```py

@bot.command()
@commands.has_permissions(administrator=True)
async def test(ctx):
logchannel = await bot.db.fetch('SELECT logchannel FROM guilds WHERE guild_id = $1', ctx.guild.id)
await logchannel.send("Test complete")

I'm going to post in #discord-bots as well since this is a question related to both channels

[torn sphinx]

Anyone know how to fix the error?

[quartz stump]

That's UML[https://en.wikipedia.org/wiki/Unified_Modeling_Language] with pretty theme/appearance, isn't it?

https://www.youtube.com/watch?v=EM-cvRubP4g - have a nice show 😄

[harsh pulsar]

i've used this before https://editor.ponyorm.com/

[obtuse oasis]

thanks

[loud rain]

hi everyone i have this code```py

ner_results = nlp(example)
some_variable_entity_0 = ner_results[0]['entity']
some_variable_entity_1 = ner_results[1]['entity']
some_variable_index_0 = ner_results[0]['index']
some_variable_index_1 = ner_results[1]['index']
some_variable_word_0 = ner_results[0]['word']
some_variable_word_1 = ner_results[1]['word']
some_variable_start_0 = ner_results[0]['start']
some_variable_start_1 = ner_results[1]['start']
some_variable_end_0 = ner_results[0]['end']
some_variable_end_1 = ner_results[1]['end']

print(ner_results)

data = {
"spoken_sentence": [(example), (example)],
"entity": [(some_variable_entity_0), (some_variable_entity_1)],
"index": [(some_variable_index_0), (some_variable_index_1)],
"word": [(some_variable_word_0), (some_variable_word_1)],
"start": [(some_variable_start_0), (some_variable_start_1)],
"finish": [(some_variable_end_0), (some_variable_end_0)],
"time": [(I_time), (I_time)],
}
#load data into a DataFrame object:
df = pd.DataFrame(data)

print(df)

what do i need to add that the next inportet text the next 2 rows being filled in

this is what i get now py spoken_sentence entity index word start finish time 0 My name is Wolfgang and I live in Berlin B-PER 4 Wolfgang 11 19 20/08/2021 23:57:02 1 My name is Wolfgang and I live in Berlin B-LOC 9 Berlin 34 19 20/08/2021 23:57:02

and i wanne have the next inportet text at row 3 &4

[torn sphinx]

so, like. how do I add has_work: False to an already existing document using motor?\

[harsh pulsar]

use update with {'$set': {'work': False}}

[torn sphinx]

I tried that. turns out it wasn't even that.
it was my if-statement. I wasn't able to check if the variable existed because it would throw a key value error.

[harsh pulsar]

i see. this is why it helps to post your code

[unkempt prism]

My guess is that you are only getting the first 2 rows because as you have only got the 0th and 1st items off your list here:

some_variable_entity_0 = ner_results[0]['entity']
some_variable_entity_1 = ner_results[1]['entity']

List comprehension will let you get all the data.

ner_results = nlp(example)
some_variable_entitys = [nr['entity'] for nr in ner_results]
some_variable_indexs = [nr['index'] for nr in ner_results]
some_variable_words = [nr['word'] for nr in ner_results]
some_variable_starts = [nr['start'] for nr in ner_results]
some_variable_ends = [nr['end'] for nr in ner_results]

print(ner_results)

data = {
  "spoken_sentence": [example] * len(ner_results), 
  "entity": some_variable_entitys,
  "index": some_variable_indexs,
  "word": some_variable_words,
  "start": some_variable_starts,
  "finish": some_variable_ends,
  "time": [I_time] * len(ner_results),
}

If you really only want the 3rd and 4th you could split the list

some_variable_entitys = [nr['entity'] for nr in ner_results[2:4]]  # get the 3rd and 4th elements only

PS: I love pandas though its not a database 🙂

[sturdy belfry]

Does anyone know if SQLalchemy allows changing databases in runtime?

[quartz stump]

simply overwrite connection string (and create a new object) 😄

[sturdy belfry]

Well, i figured the overwriting the string name 😅
But my partner has put together the database code and I'm just learning database stuff in general.
They set it up so that the engine and base object are created in _init_.py and I can't figure out how to put it somewhere where I can change it during runtime so that the base is updated

I just figured that's how it was supposed to be

[quartz stump]

Put the connection string in a file like config.py

[sturdy belfry]

Does changing the string name automatically change the base properties?
Is the ~~engine ~~ base"bind" passed by reference or value?

[quartz stump]

And in the __init__.py you have to import class (like Config :D) and then use connection string.
In another file you can overwrite connection string and recreate object

[quartz stump]

Ad 1: I don't know.
Ad 2: https://docs.sqlalchemy.org/en/13/orm/session_api.html?highlight=bind#sqlalchemy.orm.session.sessionmaker

[sturdy belfry]

Ok, so just creating a new object after updating will adhere to the new database. Which leads me to believe it's passed by reference to the base, then by value to the inheriting object.

I appreciate the info!

[upper basin]

i need some help connecting pgsql to a bot

bot has clusters, so the TooManyConnections error pops up

so I made it so that every bot instance creates a pool but only acquires a single connection

but will it lag or be very slow if i do this?

is there any alternate way

[faint blade]

It will be slow because you only have one connection, meaning that if people do two commands at once only command can use the database at once

It won't be slow, it will just be a lot of waiting and appear slower

How many bot instances are you running?

[upper basin]

there are 25 bot instances

[faint blade]

How much load do they get? Like what kind of bot is this?

[upper basin]

a lot

bot kept getting ratelimited

around 100k servers i think

[faint blade]

On 25 bot instances you should be able to use 4 connections each

Because by default PostgreSQL allows 100 connections

https://stackoverflow.com/questions/30778015/how-to-increase-the-max-connections-in-postgres

You should definitely implement some form of caching

[upper basin]

can i have multiple transactions open in a single connection/

[upper basin]

if I make a connection pool in every bot instance, it runs out of connections so I tried having it make a single connection. then it raises an error because there are a lot of things tryna happen at once on that single connection so I made a single connection pool in the launcher to feed connections to every bot instance. Then it gave me an error because the pool and the connection have to be on the same asyncio event loop
and every bot has it's own event loop

[faint blade]

Pretty sure the answer is no

[upper basin]

yep, cannot use Connection.transaction() in a manually started transaction

[faint blade]

Me and @brave bridge discussed a similar issue with Aiosqlite, though at a much smaller scale.

Why not have one connection for when you do one-off SELECT x FROM y; so that they stay fast, because I think you can do that concurrently on one connection.

Then you have another important connection for each bot when you want to use transactions that need to run 1 on 1.

[faint blade]

This is kind of the same as what you said here ^

The difference being that we have another connection that will allow simple things to stay concurrent and fast

[brave bridge]

or have read replicas

[faint blade]

This only moves the problem though and you should probably look into scaling PostgreSQL more seriously

[faint blade]

Yeah

[brave bridge]

hey I said something smart by accident

[faint blade]

What kind of work does your database do @upper basin?

[upper basin]

read a user's inventory, write to it when a command is run

most transactions are inserts

[faint blade]

Oh you have very heavy usage of the database?

Inventories, are they server-specific?

[upper basin]

no, global

but they get used a lot

[faint blade]

Hmm, that makes it tougher. Because I was thinking you could have each bot instance get its own redis database, since each shards take care of its own set guilds. You will know that a specific shard will get all events for a specific guild.

But since a user may be spread across several guilds

How do you host the bots?

[upper basin]

vps on hetzner

[faint blade]

Ah okay, I was just thinking if you were using some service to host your bots and databases. Surely they would have good support for scaling

I am sorry I don't know how to help you, as this is nothing I've had to do myself. Like I said, you'll have to look into scaling PostgreSQL for more connections

[upper basin]

F

[proven arrow]

What is your schema/queries like. If it can be optimised you would get better performance and connections would be released quicker for others to use.

[primal trail]

Has anyone encountered a slightly esoteric bug where a long running flask + sqlalchemy app will throw a connection error after a long time ?

and if so, should i maybe force a reconnect to the dB?

[upper basin]

<@&831776746206265384>

[lone island]

what error is raised if a sql DELETE FROM query is failed ? asyncpg

if it doesnt find the value to delete

[jaunty galleon]

Don't think it raises an error

[formal coral]

yea it doesn't delete anything if not found

[burnt cloak]

can i ask about class here??

[faint blade]

If it has to do with databases, yes

[dense oar]

What's the correct sort structure for pymongo? Let's say the query is documents = [i for i in collection.find().sort(query_sort)]

I've tried the following but none worked:

query_sort = "_id", -1
query_sort = ("_id", -1)
query_sort = ["_id", -1]
query_sort = {"_id": -1}

[burnt turret]

when you call .sort on the cursor (like you've done), pymongo just expects you to pass them as positional arguments
[i for i in collection.find().sort("_id", -1)] @dense oar

so doing .sort(*query_sort) for any of the first three options you've provided should work

!d pymongo.cursor.Cursor.sort

[delicate field]

pymongo.cursor.Cursor.sort

sort(key_or_list, direction=None)```
Sorts this cursor’s results.

Pass a field name and a direction, either [`ASCENDING`](https://pymongo.readthedocs.io/en/stable/api/pymongo/collection.html#pymongo.ASCENDING "pymongo.ASCENDING") or [`DESCENDING`](https://pymongo.readthedocs.io/en/stable/api/pymongo/collection.html#pymongo.DESCENDING "pymongo.DESCENDING"):

```py
for doc in collection.find().sort('field', pymongo.ASCENDING):
    print(doc)
```  To sort by multiple fields, pass a list of (key, direction) pairs...

[burnt turret]

(pymongo.ASCENDING / DESCENDING are just constants with values 1/-1)

[golden kernel]

hi

[dense barn]

how do i connect my asyncpg postgres database to vultr?

[torn sphinx]

help? where i can make a discord command to read the query or something like that?
SELECT * FROM table;
SELECT * FROM table1, table2;
SELECT field1, field2 FROM table1, table2;
SELECT ... FROM ... WHERE condition
SELECT ... FROM ... WHERE condition GROUPBY field;
SELECT ... FROM ... WHERE condition GROUPBY field HAVING condition2;
SELECT ... FROM ... WHERE condition ORDER BY field1, field2;
SELECT ... FROM ... WHERE condition ORDER BY field1, field2 DESC;
SELECT ... FROM ... WHERE condition LIMIT 10;
SELECT DISTINCT field1 FROM ...
SELECT DISTINCT field1, field2 FROM ...

[austere portal]

What do you mean by "read the query"?, execute it?

[torn sphinx]

yes

[austere portal]

What database driver are you using?

[torn sphinx]

mysql?

[austere portal]

no not the database i mean the python driver

[torn sphinx]

version or?

i dont know what is that driver..

i connected to database i only need to read something from that database

and thats all

[austere portal]

what is the library you are using to connect to the database?

[torn sphinx]

so?..

[next sun]

I am using sshtunnelforwarder and when it fails to create the ssh tunnel, the connection never times out; it hangs

even adding the following:

sshtunnel.SSH_TIMEOUT = 5.0
        sshtunnel.TUNNEL_TIMEOUT = 5.0

it still doesn't work

[torn sphinx]

i have a problem with the database, i succeded in conecting it and to read from table but i need it to read the ID of the player and the message everytime he writes something in chat

[long dome]

I'm using asyncPG with python, I'm writing a program where multiple people will use it at the same time and get data based on a database and they all can interact with said data.

Think of a marketplace.
There's a listing for 1 baseball hat for 10$ and 3 people see it and press buy at the same time.

How can I make sure that only 1 of it goes through and once it does the other 2 will fail? (Essentially avoid allowing all 3 calls to buy go through)

[brazen charm]

this is something that postgresql handles rather than you as a user

It will acquire a lock essentially on that row, so only one thing can happen at a time

[earnest pond]

Hi! I have a dataframe with three columns (df1):
column1: unique Identifier, column 2: 0 or 1, column 3: 0 or 1. I have a second dataframe (df2) with the same three columns, but with 1s and 0s in different rows. I want to join the two, so that if df1 has a 0 for a unique ID in the relevant column where df2 has a 1, df1 gets updated to be a 1. But if df2 has a 0, df1 stays as 1 for that ID, and nothing is done to df1 at all.

Importantly, the lengths of the two dataframes are not the same, and the IDs are not in the same rows, though df2 will always be a subset of IDs in df1.

In reality, my actual databases are around 30 columns as opposed to the 3 in the above example though.

[brazen charm]

e.g. if you have 3 users go to buy it and you issue that postgres, it will make sure that once it's been removed, the next two operations will see that and wont see ghost values

[long dome]

ohhh gotcha, so if one "buys" it will remove the data from the table thus when the other 2 attempt to "buy" and remove from the table it will raise an exception

ty

if i do like result = "DELETE FROM xxxx" what value will result have if it successfully deletes? 1?

[brazen charm]

err no, when you delete it will do it if it exists, otherwise it'll ignore it

generally if you want to be really safe so that users cant do a payment if someone else is already paying / processing a payment

a redis lock comes to mind or similar so that you can lock the resource across workers while its happening

[green cove]

There’s no result as such, a buch of rows, but the DB driver should give you an object with properties like “rowcount”, number if affected rows.

In general, if you issue 3 DELETE commsnds, only one of them should get back to you with rowcount == 1. For the other two, there was nothing left to remove.

Look for DB “cursor” class docs. Works also with UPDATE, if you’d rather mark the market item as “sold”, for example, rather then removing it completely. Anyway, rowcount & co. could help you realize whether the query actually changed anything.

[copper wyvern]

DBMS is sqlite3

[dense barn]

is there a way i can connect to my postgrsql database which i host on my machine from my vultr machine?

[harsh pulsar]

@copper wyvern what is the current schema for your table? can you upload an example to https://www.db-fiddle.com/

[grim vault]

Also BETWEEN is inclusive, eg BETWEEN 10 AND 20 equals 10 <= value <= 20 which will include 10 and 20.

[torn sphinx]

Anyone here can teach me SQL

[storm mauve]

the pinned resources do a better job than anyone would do for generically teaching stuff

[torn sphinx]

select ID , username, password from users 
where username = 'testing'
where password = 'testing';

hm i wanna figure it out

[grim vault]

Only one WHERE allowed, after that use AND or OR for additional conditions.

[torn sphinx]

    def login(self):
        result = mycursor.fetchall()
        for i in result:
            if i[0]== self.username:
                a = mycursor.execute('''select ID , username, password from users 
where username = '%s' and password = '%s' ''', (self.username , self.password))

  ``` would this be good enough to check the password and the username ?
can i check if its NULL and return something maybe a would be None if there isnt that username and password so maybe u could do 
if a is None:
   print("your username isnt there")

[torn sphinx]

I have a problem with update a db in sql, i already have created table with columns but that code no update that, any idea?

[woeful ice]

hi

anyone familiar with redis ?

log = logging.getLogger(name)
db = PostgresqlExtDatabase('ns2pug', user="b1n", password="b1n", threadlocals=True, port=5433)
redis = redis.Redis()
steam = SteamAPI.new()
whats wrong ont his lines ?

[copper wyvern]

Here is a basic example:
https://www.db-fiddle.com/f/uGZqtdWyHKMzNWnj5QZaVA/0

[remote plinth]

can i have multiple statements in await conn.execute in asyncpg?

like having SELECT statement then separate with a ; and another DELETE statement

[faint blade]

Yes, but what would it return?

[remote plinth]

i have a query that doesn't return anything

just makes tables

what does it mean

oh wait

lemme try something cuz i never awaited create_pool

oops yeah worked

[remote plinth]

how do people host a database like what do i need to host a postgresql db in a server

[faint blade]

What type of server? Like a VPS where you have a file-system and everything?

Or pterodactyl-style with Docker containers?

[dim quartz]

Has anyone setup an audit log table for update/insert/delete events in flask/sqlalchemy? Looking @event.listens_for() but its tough to know where to set that up on a larger application.

[remote plinth]

i have pterodactyl server

[tired prairie]

Hi,
Looking for some help with asyncpg and postgress in #☕help-coffee
Getting the error

asyncpg.exceptions.ConnectionDoesNotExistError: connection was closed in the middle of operation```
Looking for a solution from the past 6 days and any help will be greatly appreciated.

[faint blade]

Then you will need to set up a docker container for it, and expose the ports so that they can talk so-to-speak

Or just use a 3rd party database system like ElephantSQL

[steel rover]

so im making a discord bot, and i want it to store data in a database including info per server. which will be better, that is, easier to learn and manage, while having more space. MongoDB, or MySQL?

im very new to databases

[harsh pulsar]

thanks. it looks like part of your question was deleted, can you remind me what you were asking?

[harsh pulsar]

i would just use sqlite and keep the database file on the same machine as the bot

i personally think sql is more useful to know and it forces you to be somewhat intelligent about your data

it's so easy to make a mess out of mongodb

[rare vale]

I have made this design of a logical database, but I don't know how to save the attribute 'dose'. 1 haplotype can have multiple dose, so in order not to save the same haplotype multiple times I could save dose in a seperate entity. But if I do that I don't know how I could know which dose a haplotype of a genotype has. Could someone help me with this?

[sullen pine]

How to do this in mysql
https://www.kodyaz.com/t-sql/repeat-rows-according-to-column-value.aspx

[harsh pulsar]

there's probably a way to do it, but this is the kind of thing i would just do in python code after querying

[harsh pulsar]

store dose on the genotype table as well maybe?

it sounds like each genotype is related to a haplotype,dose pair, not just a haplotype on its own

if that's the case then you want foreign keys to both of those

[sullen pine]

but what if the query results is to be queried with limits? It will create problems. Am i wrong?

[harsh pulsar]

yeah, that could create problems. you would have to "lift" the limit to python code as well. again, there is probably a way to do it in mysql. maybe worth asking on stackoverflow if you don't get an answer here

here's how i would do it in python:

from itertools import islice

def do_weird_query(conn):
    cursor = conn.cursor()
    cursor.execute('select name, num from weird_data')
    for name, num in cursor:
        for _ in range(num):
            yield name

results = list(islice(do_weird_query(conn), 10))

[rare vale]

Do you mean I could put dose in a different entity and then give genotype a foreign key for haplotype and dose? But doesn’t that look like there can be a dose without a haplotype then? Cuz there shouldn’t be a dose without a haplotype

[harsh pulsar]

yeah, possibly

the other option is to have a composite primary key on the haplotype-dose entity, a haplotype id and a dose

i think most databases support composite foreign keys

[rare vale]

I don’t know what a composite primary key is

[harsh pulsar]

when more than one column is the primary key

so the columns individually are not primary keys, but the combination uniquely identifies a row

[rare vale]

Wow I didn’t know that existed

Aah

Thanks for the new knowledge I think I’ll have to go with a composite primary key then

[harsh pulsar]

you might also need/want a surrogate key, which is a non-composite primary key that stands in for a composite primary key

messing around in postgres for example i am struggling to define a foreign key that references a composite primary key

[silk storm]

hey guys im trying to use docker compose to run a postgres db with django

but im constantly gettin gthis error:

django | django.db.utils.OperationalError: could not connect to server: Connection refused
django |        Is the server running on host "127.0.0.1" and accepting
django |        TCP/IP connections on port 8080?

[woeful ice]

Hello guys since 3 days i stuck here i am trying run my codes but cant connect to postgresql i think this is error codes= https://pastebin.pl/view/4c6e388a this my database.py =https://pastebin.pl/view/bc3c5297 and this my worker.py for run =https://pastebin.pl/view/f930fd37

[remote plinth]

can i use time.time() in a 'timestamp without time zone' datatype? postgres

[pliant marsh]

Traceback (most recent call last):
  File "main.py", line 1048, in <module>
    client.loop.run_until_complete(create_db_pool())
  File "/usr/lib/python3.8/asyncio/base_events.py", line 616, in run_until_complete
    return future.result()
  File "main.py", line 25, in create_db_pool
    client.db = await asyncpg.create_pool(database="test bot", user="postgres", password="fdjeejfd")
  File "/opt/virtualenvs/python3/lib/python3.8/site-packages/asyncpg/pool.py", line 407, in _async__init__
    await self._initialize()
  File "/opt/virtualenvs/python3/lib/python3.8/site-packages/asyncpg/pool.py", line 435, in _initialize
    await first_ch.connect()
  File "/opt/virtualenvs/python3/lib/python3.8/site-packages/asyncpg/pool.py", line 127, in connect
    self._con = await self._pool._get_new_connection()
  File "/opt/virtualenvs/python3/lib/python3.8/site-packages/asyncpg/pool.py", line 477, in _get_new_connection
    con = await connection.connect(
  File "/opt/virtualenvs/python3/lib/python3.8/site-packages/asyncpg/connection.py", line 2045, in connect
    return await connect_utils._connect(
  File "/opt/virtualenvs/python3/lib/python3.8/site-packages/asyncpg/connect_utils.py", line 790, in _connect
    raise last_error
  File "/opt/virtualenvs/python3/lib/python3.8/site-packages/asyncpg/connect_utils.py", line 776, in _connect
    return await _connect_addr(
  File "/opt/virtualenvs/python3/lib/python3.8/site-packages/asyncpg/connect_utils.py", line 676, in _connect_addr
    return await __connect_addr(params, timeout, True, *args)
  File "/opt/virtualenvs/python3/lib/python3.8/site-packages/asyncpg/connect_utils.py", line 720, in __connect_addr
    tr, pr = await compat.wait_for(connector, timeout=timeout)
  File "/opt/virtualenvs/python3/lib/python3.8/site-packages/asyncpg/compat.py", line 66, in wait_for
    return await asyncio.wait_for(fut, timeout)
  File "/usr/lib/python3.8/asyncio/tasks.py", line 494, in wait_for
    return fut.result()
  File "/opt/virtualenvs/python3/lib/python3.8/site-packages/asyncpg/connect_utils.py", line 586, in _create_ssl_connection
    tr, pr = await loop.create_connection(
  File "/usr/lib/python3.8/asyncio/base_events.py", line 1033, in create_connection
    raise OSError('Multiple exceptions: {}'.format(
OSError: Multiple exceptions: [Errno 111] Connect call failed ('127.0.0.1', 5432), [Errno 99] Cannot assign requested address
 

what is the problem?

[harsh pulsar]

time.time just returns a number of seconds since epoch. so use the to_timestamp() function in postgresql https://www.postgresql.org/docs/current/functions-datetime.html#FUNCTIONS-DATETIME-TABLE

[remote plinth]

ok

[harsh pulsar]

!d time.time

[delicate field]

time.time

time.time() → float```
Return the time in seconds since the [epoch](https://docs.python.org/3.10/library/time.html#epoch) as a floating point number. The specific date of the epoch and the handling of [leap seconds](https://en.wikipedia.org/wiki/Leap_second) is platform dependent. On Windows and most Unix systems, the epoch is January 1, 1970, 00:00:00 (UTC) and leap seconds are not counted towards the time in seconds since the epoch. This is commonly referred to as [Unix time](https://en.wikipedia.org/wiki/Unix_time). To find out what the epoch is on a given platform, look at `gmtime(0)`.

Note that even though the time is always returned as a floating point number, not all systems provide time with a better precision than 1 second. While this function normally returns non-decreasing values, it can return a lower value than a previous call if the system clock has been set back between the two calls.

[remote plinth]

so i can select that as seconds?

i mean in a SELECT statement

[harsh pulsar]

you might have to be careful about differences between platforms, e.g. with leap seconds (as in the docs)

yeah, you would write to_timestamp($1) or whatever

[remote plinth]

alright

to_timestamp(1284352323) → 2010-09-13 04:32:03+00
how do i change 2010-09-13 04:32:03+00 to 1284352323

[faint blade]

!d datetime.datetime.timestamp

[delicate field]

datetime.datetime.timestamp

datetime.timestamp()```
Return POSIX timestamp corresponding to the [`datetime`](https://docs.python.org/3.10/library/datetime.html#datetime.datetime "datetime.datetime") instance. The return value is a [`float`](https://docs.python.org/3.10/library/functions.html#float "float") similar to that returned by [`time.time()`](https://docs.python.org/3.10/library/time.html#time.time "time.time").

Naive [`datetime`](https://docs.python.org/3.10/library/datetime.html#datetime.datetime "datetime.datetime") instances are assumed to represent local time and this method relies on the platform C `mktime()` function to perform the conversion. Since [`datetime`](https://docs.python.org/3.10/library/datetime.html#datetime.datetime "datetime.datetime") supports wider range of values than `mktime()` on many platforms, this method may raise [`OverflowError`](https://docs.python.org/3.10/library/exceptions.html#OverflowError "OverflowError") for times far in the past or far in the future.

For aware [`datetime`](https://docs.python.org/3.10/library/datetime.html#datetime.datetime "datetime.datetime") instances, the return value is computed as:

```py
(dt - datetime(1970, 1, 1, tzinfo=timezone.utc)).total_seconds()
```   New in version 3.3.

Changed in version 3.6: The [`timestamp()`](https://docs.python.org/3.10/library/datetime.html#datetime.datetime.timestamp "datetime.datetime.timestamp") method uses the [`fold`](https://docs.python.org/3.10/library/datetime.html#datetime.datetime.fold "datetime.datetime.fold") attribute to disambiguate the times during a repeated interval.

[faint blade]

What's the difference 🤔

[harsh pulsar]

@faint blade that's an instance method on datetime objects

stupid lower case class name

from datetime import datetime as DateTime
now = DateTime.now()
print(now.timestamp())

[faint blade]

Oh yeah I am aware. But like, what's the difference between: time.time() and datetime.datetime.now().timestamp()

[harsh pulsar]

probably not much

maybe time zone handling

[remote plinth]

^^

[faint blade]

Huh, is this what it returns. What do you mean?

[remote plinth]

i found in docs here

[faint blade]

I still am not following, when you do want to change 2010-09-13 04:32:03+00 to 1284352323?

Because PostgreSQL stores the latter in the database

[remote plinth]

i just found what i want, thanks for help

[median wave]

is MySql or postgre better for a large discord js bot

[harsh pulsar]

use whichever one you prefer. apparently mysql has some advantages for really really big databases, but i think postgres has much nicer features

[sage scaffold]

what is a easy database i can use

not sqlite3 because thats some weird mutant english i have to learn

[harsh pulsar]

sqlite3

what mutant english?

almost all databases use SQL

[civic swift]

Hi everyone
I am very new to databases, I recently installed mySQL and currently working with mysql workbench to learn. can anyone explain to me what are connections used for, when I should create one? may I just create one to a multiple projects and inside have multiple schemas? and what is the hierarchy in general?

sorry if it's a lot, I am struggling with understanding the meaning of the setup actions, less with the programming

[ionic pecan]

a connection is generally something that you establish to the database in order to run queries for it. for instance, if you use python's mysqlclient, MySQLdb.connect(host='mydbhost', user='www-data') would return a connection. the server (mysql) then keeps track of what you're doing per connection, and one connection can only do one thing at a time

[civic swift]

so the connection I create in mysql workbench is the same as the server would to store and retrieve data? and it allows me to work on the database

[chrome birch]

can anyone help customize the SQLite3 tables?

[civic swift]

doesn't that mean I could have multiple connections to the same database in mySQL workbench?

[long dome]

I'm making a test game and there are 56 different types of materials. How would you guys store which & how many materials a player has in a database? Just do like:

await conn2.execute("CREATE TABLE playermats (playername VARCHAR(255),mat1 INTEGER,mat2 INTEGER,mat3 INTEGER)")

56 times....?
Surely there has to be a better way right...?

[ionic pecan]

in workbench you probably don't want it as it's probably only ever to do one thing at a time, right? it's a GUI, as far as I remember

[ionic pecan]

create a second table storing a reference to the player, the material name, and the material quantity

then when you need the materials for a player, JOIN them together: https://www.w3schools.com/sql/sql_join.asp

[harsh pulsar]

create table player_materials (
  playername varchar(255),
  materialname varchar(255),
  quantity integer
)

[long dome]

Do you know how to create a table in POSTGRESQL where it references 2 other tables

[ionic pecan]

yes, same way that you do it for referencing a single table, just that you have REFERENCES on multiple columns

[dense barn]

ok so, im trying to connect pgadmin4 to my vultr vps, but its sending me this error:

Unable to connect to server:

could not connect to server: Connection refused (0x0000274D/10061)
Is the server running on host "" and accepting
TCP/IP connections on port 5432?
``` i have everything it says in the error, but no idea why its sending that, any idea why?

[faint blade]

Why is host ""?

[dense barn]

i just removed it cuz it shows my ip

[faint blade]

So the bot is running on your VPS, but the database on your computer?

[sacred tartan]

Often the first thing is that postgresql is not running. Did you check the status?

[dense barn]

yea

[dense barn]

yea you could say that

[sacred tartan]

Um ok.

[dense barn]

would it be bad if i host db and bot on it?

[faint blade]

Then you need to port forward the PostgreSQL port to the whole wide world through your router

[faint blade]

I recommend this

[dense barn]

alright, so how would i migrate my current db to there

cuz i already have data in it

[faint blade]

What kind of data?

[dense barn]

user ids, pokemon stuff

[faint blade]

You can generate an SQL file dump and then run it on the server, this is pretty common practice

[dense barn]

pg_dump?

[faint blade]

So you generate a file with queries of all the data, and then run it there. That should setup all tables and data

[faint blade]

I am not exactly sure if that's the one, probably yeah. This is not something I've done and your Google search is as good as mine

[harsh pulsar]

i do think pg_dump is generally recommended for this kind of thing. i think they also recommend if if you e.g. upgrade database versions

[dense barn]

yea i heard someone said to do that, they had the same issue as mine and they used it and worked

im currently looking at this doc, https://www.vultr.com/docs/install-pgadmin-4-for-postgresql-database-server-on-ubuntu-linux but im stuck on the last part of step 3

it just wont load

[astral siren]

Hey how would I go about adding a list into an sql database?

[jolly urchin]

Hello members, I am creating a chat app in flask python which will generate random ids to chat with someone. This part is done!. Now I want that there should be a login logout system. I am using MongoDB as Database. Can anyone give me an idea?

[finite perch]

With mongo each document would come with a unique id, this might be useful to you

[jolly urchin]

yeah so should a i make a list of ids and iterate it?

That would be efficient?

[finite perch]

For filling up chat from the saved messages?

you could filter it by time to avoid fetching everything (might need to be saved as a key)

[jolly urchin]

how?

[finite perch]

First of all, look into mongo and libraries that support it

their documentation should have a corresponding method for this

[austere portal]

Hello, I have seen people use STR as the column constraint in sqlite.
What is it?, is it similar to TEXT?

[storm mauve]

As far as I understand it, they just convert it all to the same type automatically
https://www.sqlite.org/datatype3.html
(more specifically, look at 3.1.1. Affinity Name Examples)

[austere portal]

Oh thanks

[grim zephyr]

@austere portal

here i am

[storm mauve]

...wait, STR? are you sure that it is STR and not something else, and that it is sqlite?

[grim zephyr]

the console is printing that it is commited but no it is not

its only 0

but why

[austere portal]

hmm

[grim zephyr]

@austere portal is it due to the f string

[austere portal]

wait

[grim zephyr]

ok waiting

[austere portal]

Yes

[grim zephyr]

i got the issue

[austere portal]

why are you setting the convo column to convo\n + ?

[grim zephyr]

i want to add the next msg into it and thats where it is causing the error

but i want to add not to update the whole

but how

it making its value 0

if i try to add it

[austere portal]

inset the new message without having 2 messages in a single row

[grim zephyr]

wdym ??

[austere portal]

insert it instead of adding it to an existing column

[grim zephyr]

but then how will i order it later

can u give me a example

of what u r saying

[austere portal]

INSERT INTO ... instead of UPDATE ...

[grim zephyr]

wait a se

sec

@austere portal

i want to add the next line to it but how

@austere portal

[austere portal]

insert it instead of updating

[grim zephyr]

so how will i get it later

[austere portal]

having multiple values in the same row makes no sense

[austere portal]

SELECT ...

[grim zephyr]

i mean i dont know how much the users will talk and even that is not a problem but how willl i order it later

[austere portal]

Sqlite has order by
https://www.sqlitetutorial.net/sqlite-order-by/

[grim zephyr]

so u mean there is no way to make a update in it

[austere portal]

its is possible

But why?

[grim zephyr]

so how

[grim zephyr]

i need to store it in a single row

[austere portal]

ok then

wait a bit

[grim zephyr]

ok

plz do it fast

[austere portal]

Why are you passing the channel name for the user_id column?

[grim zephyr]

its a mod mail

the channel name has been setup like that

[grim zephyr]

this

[austere portal]

oh, you can rename it to channel_name

[grim zephyr]

well no need

i just want to save the values in a single row

but how

?

@austere portal

[austere portal]

Try doing py await cursor.execute("UPDATE transcript SET convo = convo + ? WHERE user_id = ?", (f"\n{str(message.author)}: {message.content}", message.channel.name))

[grim zephyr]

i tried this already but no

[austere portal]

and commit

oh

[grim zephyr]

@austere portal any other way?

[austere portal]

you can inset it as a new row

and channel names can change

[grim zephyr]

so there is no other way

?

[grim zephyr]

@austere portal

???

[austere portal]

sorry i dont know

there is something wrong with the update statement

[grim zephyr]

well i got another way

[austere portal]

ok

[grim vault]

The operator for string-concatenation in SQL is ||

  UPDATE transcript SET convo = convo || ? WHERE user_id = ?```

[grim zephyr]

I already fixed that

[placid oracle]

is this a good place to get help with pandas?

[placid rune]

here or #data-science-and-ml

[placid oracle]

i have a question in #help-corn if someone can please help

[torn sphinx]

Hi, may anyone explain something about database transactions, like when and why they're used, and when should I?
I'm using asyncpg to execute and fetch some data (particularly for a discord bot), so when and why should I use transactions or it's just not that important to be used?

[storm mauve]

as far as I understand, they're more useful when you care about ACID principles or have to execute multiple queries in the same "block", in a way so that if any of them fails, everything fails

In computer science, ACID (atomicity, consistency, isolation, durability) is a set of properties of database transactions intended to guarantee data validity despite errors, power failures, and other mishaps.

[brave bridge]

Consider this:

async def transfer_real_world_money(account1, account2, amount):
    await change_balance(account1, +amount)
    await change_balance(account2, -amount)

what happens if the second call fails, or if your application crashes between the first and the second call?

That's the "atomicity" parts -- transactions allow you to ensure that some action either happens in full or doesn't happen at all

[torn sphinx]

Oh basically for like safe transactions and preventing incomplete and improper data. And if I'm right, for just executing a single statement, this is not too useful.

Thanks this explained a lot. Thank you and etrotta 😄

[brave bridge]

AFAIK, in Postgres you cannot execute something outside of a transaction, asyncpg just opens one if you don't have one already.

[torn sphinx]

Oh I see, that saves me from opening new transactions again and again. Thanks a lot :)

[snow niche]

sqlite: is there any way to connect to that file and then create the table?

[grim vault]

A transaction is also needed for multi-user support because it locks the record so nobody else can change it while you do.

[grim zephyr]

@snow niche I can try to help u

[snow niche]

thanks

[snow niche]

this is my question

and this is what i have rn

[grim zephyr]

U r using sqlite right?

[snow niche]

aiosqlite

async version of it

[grim zephyr]

Good

I am a aiosqllite user too

[snow niche]

nice

my friend said its faster on discord bots

[grim zephyr]

Yea maybe, but it's better cause it is a async version so it is much more compatible with the bot from my opinion

[snow niche]

yeah

so the problem is it says no table named 'table_name'

[grim zephyr]

Use ```py
await aiosqlite.connect("your_db.db")

To connect

And then

[snow niche]

im using a separate file to make tables is that fine?

like build.sql

[grim zephyr]

U r using dpy right

[snow niche]

yes

[grim zephyr]

Make the file and everything for once in the on_ready event that will do all the work at once

[snow niche]

example?

[grim zephyr]

Make the db and connect to it from on_ready event and create everything

[snow niche]

i have this much in build.sql

CREATE TABLE "mod_logs" (
    "guild_id"    INTEGER,
    "channel_id"    INTEGER
);

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

CREATE TABLE "bl_users" (
    "user_id"    INTEGER
);

[grim zephyr]

Why using SQL

[snow niche]

to build table

[grim zephyr]

Use python

Python can make table too

@snow niche

[snow niche]

hmm

[grim zephyr]

So why sql

[snow niche]

i saw in a video

but he's using sqlite idk...

[grim zephyr]

Well u can do all this from python only

Only python is enough

[snow niche]

i used to do this

# ========== DATABASE ============
# warn db
async def dbinit():
    await client.wait_until_ready()
    warndb = await aiosqlite.connect("db's/warnData.db")
    await warndb.execute("CREATE TABLE IF NOT EXISTS warningsData (guild_id int, admin_id int, user_id int, reason text)")
    await warndb.commit()
# blacklist db
    bldb = await aiosqlite.connect("db's/blacklist.db")
    await bldb.execute("CREATE TABLE IF NOT EXISTS userBL (user_id int)")
    await bldb.commit()
# mod-logs db
    mldb = await aiosqlite.connect("db's/modlogs.db")
    await mldb.execute("CREATE TABLE IF NOT EXISTS modlogs (guild_id int, channel_id int)")
    await mldb.commit()



client.loop.create_task(dbinit())
client.run(os.getenv('TOKEN'))
dbinit()
asyncio.run(dbinit.close())

then i tried to do same as Carl
https://github.com/CarlGroth/Carl-Bot

executing in cogs... self.conn = connection, self.c = self.conn.cursor()
but it said we need to await in cursor and putting await doesnt work and it says we should put in async func

[lime lily]

  File "/home/gg/connect.py", line 22, in <module>
    cur.execute(ins_pass, 'name')
  File "/home/gg/.local/lib/python3.9/site-packages/mysql/connector/cursor_cext.py", line 257, in execute
    prepared = self._cnx.prepare_for_mysql(params)
  File "/home/gg/.local/lib/python3.9/site-packages/mysql/connector/connection_cext.py", line 659, in prepare_for_mysql
    raise ValueError("Could not process parameters")
ValueError: Could not process parameters

why am i getting this error?

[brave bridge]

Show the code

rather, the query

[snow niche]

@grim zephyr so what should i do?

[brave bridge]

@lime lily Try cur.execute(ins_pass, ('name',)) -- I think the parameters should be a tuple

[lime lily]

import hashlib
cnct = mysql.connector.connect(
        host = "localhost",
        user="hospital",
        password="password",
        database="hospital",
        )

cur = cnct.cursor()
uid = input()
pas = input()

id_hash = hashlib.md5(uid.encode()).hexdigest()
pas_hash = hashlib.md5(pas.encode()).hexdigest()

ins_uid = 'INSERT INTO username(uid) VALUES (%s)'
ins_pass = 'INSERT INTO passwords(hashed) VALUES (%s)'
cur.execute(ins_uid,id_hash)
cur.execute(ins_uid)
cnct.commit()

[lime lily]

tried that still wont work

[brave bridge]

as I said, the parameters should be tuples, not strings

also, don't use md5, use blake2

[faint blade]

MD5 should NEVER be used for passwords! That is extremely dangerous

MD5 is meant to be a fast algorithm allowing you to verify content, for example you'll see a MD5 hash when downloading. If a single bit has changed that will drastically change the resulting hash.

It's not meant for storing hashes of passwords, as it's pretty easy to calculate and not secure for that purpose.

Bcrypt and Argon2 are both two other good algorithms

[fierce sand]

.txt

Karen

[lime lily]

i used sha256 but it was a bit slow for testing

i only use md5 for testing

[pine flax]

Why i cant delete items in tables, its giving me an error Error Code: 1175. You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column.

[brave bridge]

Can you show your query?

[pine flax]

I just did delete from _mytable;

[torn sphinx]

Does anybody have at least a primer resource on how to optimize offsite/cloud dbs calls?

[pine flax]

the primary key is varchar(255), is that the reason?

[torn sphinx]

Or when is cheaper to use a server?

Im trying to build a thing that rests on doing multiple queries to find an optimal result

Some of this data changes by the day, but other is relatively fixed

[brave bridge]

If you want to delete all items, https://stackoverflow.com/questions/11448068/mysql-error-code-1175-during-update-in-mysql-workbench

[torn sphinx]

So im already thinking in decoupling it so i can let the client have the fixed data, and only query the daily data

But i don't know enough about how dbs work to know at what point is cheaper to let the client call the db multiple times and compute itself, and when its cheaper to pay for server space and have the data there and work the computation in the server itself

[pine flax]

ty

[torn sphinx]

I suppose it obviously depends on the amount of users, but i cant fully grasp the optimal division between a full api solution and a more traditional backend

[zealous pelican]

I need some advice about a project I am thinking about starting. I want to move from logging my trading & finances in Excel to something else and I want to learn python while I'm at it. I thought this would be a good way to combine both. I currently have an excel workbook with one sheet to log my money transfers (between 3 currencies), one sheet to log my trades, which uses another sheet with details of commissions & fees to calculate trade costs & taxes, and finally a "front end" interactive dashboard implemented with Power Pivot and Slicers that shows me my P/L, etc.

I posted about this to the cupcake channel (#help-cupcake message) and based on responses there, my first step is to figure out what kind of a database solution I want to implement. I know next to nothing about properly implementing databases. I know what they are and I have worked with some very light stuff when setting up WordPress websites, but I have never done anything like this before. Where can I start orienting myself about this? How do I choose a database to tackle this project? What all should I be thinking about?

If needed, I can share a link to the Excel Workbook in question.

[torn sphinx]

@zealous pelican check the book Test Driven Development

its kinda tangential to your needs but the whole book uses an example that is exactly what you need

specifically because of "between 3 currencies"

[zealous pelican]

I see

[torn sphinx]

in terms of db

[zealous pelican]

in terms of db, I currently have three distinct ones

[torn sphinx]

three tables you mean

[zealous pelican]

Yes. Might be easier if I just share the sheet, tbh: https://cdn.discordapp.com/attachments/696505521410146326/870262979277508618/Options_Tracker.xlsx

One sheet logs trades, one sheet logs money transfers, one sheet serves as a reference for commissions, fees, tax rates, names, etc. That one doesn't get updated very often

[torn sphinx]

the thing is the way computers work now

you dont really need to use a real database to do what you need

[zealous pelican]

I don't understand

[torn sphinx]

ive given a look to the excel you sent, looks fairly plain

in terms of deciding a databse solution whats most important is the realtionships between tables

[zealous pelican]

Right

[torn sphinx]

Because of how fast computers work, databases are kind of overkill unless you have at least a couple hundred k entries in a couple tables

You could very well make what you want work using csv or xls files

without any noticeable drag in time

[zealous pelican]

Yes, that's what I am doing right now

[torn sphinx]

(in terms of read-write)

Thats why i initially recommended the test driven development book, because it seems to me your main issue would be creating a currency convertion flow

[zealous pelican]

What I don't know is whether this approach is scale-able. What I mean is - will using an excel or csv be viable in say, 5 years? If I add over 500 rows of data per year, I don't know if excel will be able to cope.

Plus, right now, I can't view this excel file on mobile at all.

[torn sphinx]

Ive used JSON files of up to a couple hundred k data points and it reads in seconds

[zealous pelican]

I don't want this project to be restricted to being useable only from my desktop haha

[zealous pelican]

Oh okay!

[torn sphinx]

The problem is the following

You could convert your data to say, SQL

that would make it more scalable, but also more cumbersome

[zealous pelican]

(a second aspect to all this is learning new things that may bring me better employment opportunities)

[torn sphinx]

Well it depends in what kind of skill you want to show off

If you want to learn databases, then give a reading to the differences between relational, non relation, and graph databases

And try implement models for your data in the three of them

[zealous pelican]

That's already a good staring point 🙂

Thank you

[torn sphinx]

A good programming exercise would be setting a frontend that can switch between the three

That you could "plug" any of those different database formats to it

and work the same way

Thats a kind of project that shows off abstract architectural notions

[zealous pelican]

Is automation a factor when making these decisions? Whenever it is my trading platform releases their API, I would like to automate the data entry.

[zealous pelican]

I'm having trouble imagining what this means or looks like

[torn sphinx]

You could make transformation functions for the API data into whatever database model you have

[zealous pelican]

And I straight up don't understand what this means

[torn sphinx]

Look up DAO patterns

[zealous pelican]

Okay

[torn sphinx]

basically you create a sort of bridge class that is then inherited by actual implementations that turn the base methods into whatever each database needs

i can make a simple analogy if that goes a bit over your head, i dunno how much you know about programming

[zealous pelican]

Not a whole lot.

I have worked a lot with R.

[torn sphinx]

you know about classes and inheritance?

[zealous pelican]

I know some amount of HTML, CSS and mild PHP - enough to tinker in WordPress, but it ends there.

[zealous pelican]

Sort of. From the perspective of CSS I do

[torn sphinx]

its less about language and more about the meta

lets say you have a class "Translate" that has methods "hello" "goodbye" , etc

These methods in the Translate class do nothing, they just exist to explicit what is expected from a Translate inheritor class

so you know how in python you don't need to know the type of a variable

you just get an object and assume its a Translate and call obj.hello

the idea is you then do child classes Chinese, French, etc

[zealous pelican]

Oh, uhm, I've never used Python before. I want to get started with it

[torn sphinx]

and each of them do hello() in Chinese, in French, in German, etc

so in essence, your main program DOES NOT CARE what kind of language its saying hello in

it only cares about what Translate says it can do "in general"

it can say hello

That allows you to "plug" any language into your main program in a dynamic way

Thats what i mean when i say "A good programming exercise would be setting a frontend that can switch between the three"

You get what i mean? You completely divide the frontend code from the database code

So the frontend does not know what database is behind

It only knows to call the general methods that all multiple versions of database code inherits from

[zealous pelican]

I see

I see the logic

But I have no clue how to even begin thinking about this from a programming perspective

[torn sphinx]

That way, if an year from now you decide database X is a bad idea

you can change it for Y

and don't have to change all code

[zealous pelican]

Right

[torn sphinx]

just plug a new piece of code to the frontend

and unplug the previous

[zealous pelican]

This... Is def something I want to consider

[torn sphinx]

Its one of the tricks of large-scale software

and scalable

[zealous pelican]

One of the reasons I want to move away from excel is related to this. If I add a new financial product that I am trading, updating all the fee calculation formulas is a nightmare. This is also what I mean by scalability

[torn sphinx]

Well yeah

thats kind of a derivation of this idea

Thats what i meant earlier when i said abstract architectural notions

[next sun]

I am currently building an api based on users, fields and permissions. It happens that I have a table fields with each field represented in geometry points and another table fields_groups with each field group represented in geometry points too. So, basically, I want to reference the geom in fields on the geom in fields_groups. That will enable me to query for examples all the fields that exist in a particular group. Has anyone ever worked with a similar scenario? How could I achieve that? Many thanks in advance
ah! I am working with postgresql

[torn sphinx]

The same is true for currencies

You would ultimately want to make the base of your program be a set of very abstract logic

When anyone learns to program you are taught to do very explicit imperative logic

Do X, then Y, then loop Z times and do W

When you are programming with the idea of a project that grows in functionality you should look into the meta of what you are doing

Say im doing a calculator, i define a sum function

Then i have to do a multiply function

The right way, under this line of thinking, would be to implement multiplication in terms of a looped sum

[zealous pelican]

yes, this is how I think when I think about programming. I never formally learned anything, but I just ended up picking this up

[torn sphinx]

Yeah, the problem is that doesn't work when you are changing program definitions

That works when you have a defined goal for the program

When you KNOW your program will inevitably lead somewhere else, you need to start thinking about the meta rules of your domain

Basically your excel is programming too

What you are seeing when you need to change everything when you add something

is what i mean

[zealous pelican]

Precisely. This is where I am running into issues with my sheet as well. I never though I would use it for logging anything other than options. Then suddenly, when I bought shares of stock, the sheet blue-screened, figuratively speaking.

[zealous pelican]

Yes. All of my formulas (if you look at AI2 or AJ2) are huge IF loops

[torn sphinx]

Yep, its cause you added another dimension lol

i guess

[zealous pelican]

Yes, and there's no telling if there will be more instance like this in the future

[torn sphinx]

a stock means numerative conversion, right?

[zealous pelican]

Or rather, how many instance there will be like this

[torn sphinx]

Like you need to know how much 20 stocks of X are worth in Y currency today?

[zealous pelican]

Not really, no.

[torn sphinx]

And then you are doing math between stocks and currency

[zealous pelican]

The multi-currency thing matters mostly because I earn in Euros, have some life savings in Rupees, and trade in Dollars

So overall, I need to figure out what my net worth is in Euros, as that's the currency I live on

[torn sphinx]

I closed the xls, what do those do?

[zealous pelican]

The issue with stocks is a bit different - that was more a FIFO issue. See with options trades, I open one contract, and close that one contract. But with stocks, you can buy 20 shares today, sell 5 of those next week, 3 of those next month, and remaining ones 3 years later. All the while, you've probably bought more shares along the way. And my excel sheet cannot cope with that. If I buy 20 shares, the only closing transaction I can record is selling 20 shares

[torn sphinx]

You should treat stocks as a currency too

[zealous pelican]

They calculate opening and closing fees.

[torn sphinx]

Thats time information, right?

[zealous pelican]

What I just posted?

[torn sphinx]

Opening and closing fees

[zealous pelican]

No.

The fees depend on what type of product I am trading (i.e. whether it is a stock, crypto, or an options strategy)

[torn sphinx]

Yeah

[zealous pelican]

And if it is an options strategy, the fees further depend on how many options contracts are involved

[torn sphinx]

Exactly

[zealous pelican]

How's that time information though?

[torn sphinx]

Because ultimately you have to define one measurement standard

[zealous pelican]

Honestly, in an ideal world, I wouldn't want to have to run that computation to begin with. I would like to pull this information from the trading platform directly.

[torn sphinx]

What all of that complexity is measuring is value in value out

Id chose dollars cause they are the least inflationary imo

but you could go for euros

[zealous pelican]

Right. So the ultimate goal is to calculate

1. profitability and
2. win rate

Fees will cut into profitability

As will taxes

[torn sphinx]

Exactly, but you need to set a main base

like you need an unit

[zealous pelican]

I'm using USD right now because that is what I am trading in

[torn sphinx]

Once you got an unit everything else is a matter of functions

The problem with financial matters is a bunch of measurements

[zealous pelican]

Right, EUR USD keeps changing

[torn sphinx]

yep

[zealous pelican]

Okay so something I probably should have clarified earlier - I don't need the program to give me any real time information of my positions

[torn sphinx]

Ultimately what you care about is purchase value

Thats the ultimate unit

But thats kinda hard to measure, so chose a currency to use as base

and derive all from that

[zealous pelican]

Right.

[torn sphinx]

By purchase value, i dunno if its the correct english term

[zealous pelican]

Let's just call it "opening cost"

[torn sphinx]

it means "what it costs to buy this"

I use that term cause my coin is inflationary as fuck

[zealous pelican]

It could be negative if you buy to open or positive if you sell to open (if you are short selling for example)

[torn sphinx]

so for example my own economy is not dependent on numbers but on how many goods i can buy relative to my salary

[torn sphinx]

Mmm

In that sense then the unit you are using is not USD

but a sort of relative displacement?

[zealous pelican]

For example, in the first row, I "bought" tesla shares as my opening transaction so I paid 384 USD. When I close this transaction, I will sell those shares (hopefully for more than what I bought them for) and receive USD
However, in row 2, I sold an options strategy as my opening transaction, so I received 3.91 USD. When I close this transaction, I will have to buy those contracts back (hopefully for less than what I sold them for) and pay USD.

[torn sphinx]

I dont understand

you got -384 and + 3.91

Whats missing there?

[zealous pelican]

Okay perhaps I should have posted the entire table.

[zealous pelican]

Nothing is missing in this case. I was just trying to illustrate what I was saying 🙂

[torn sphinx]

Theres a state difference, lower row is a closed transaction

upper one is still open

[zealous pelican]

That an opening transaction need not necessarily be "pay money to open" - it can also be "receive money to open"

[zealous pelican]

Yes, precisely.

[torn sphinx]

Yeah, its just a 1 or 0

[zealous pelican]

And there in lies a potential issue,.

[torn sphinx]

It brings in some architectural complexity yeah

But its literally

just a 1 - 0

You could separate both states into two different tables, or you could just have on table with all the fields of the closed one

and check for NULL in the closing fields to delimit between them

[zealous pelican]

So say I went out and bought 1 share of TSLA every few weeks. Then, a few months later, I sold all of them. That's pretty easy to update - each of those first few rows can be flipped to "closed" and the closing price can be entered.
But then I bought 100 shares of RIOT. Now, if I want to sell only 20 of those, I can't update the row anymore. I would need to split the opening row into 20+80 and then close the row with 20 shares. However, this now creates a problem with the way fees are calculated. The program now calculates fees for 2 opening transactions and one closing transaction, but in reality, there was just one of each.

[torn sphinx]

No

You can have a dynamic "currency" object

by object i mean class

a type

[zealous pelican]

mhm

[torn sphinx]

So you can do math with riot shares

You can also have the notion of "wallets"

Each wallet relates to a currency

so you can fill a wallet with 100 riots and then convert 20 of them into rupees

[zealous pelican]

Right

[torn sphinx]

and 100 rupees into tsla

The Test Driven Development book uses exactly this example

they explain another thing (test driven development)

but the example is a sort of multi-currency budget database

The examples slowly evolve a way to do math and hold different type of values and convert between them

[zealous pelican]

I'll get that book

[torn sphinx]

Its not the only way of course, but might give you some ideas, i can't think of a clear example for your needs

Mostly your issue is architectural

I mean, if you are strong in maths

go for SICP

If you go through that youll end up making your excel fly

But its not gentle and its too abstract if you come from just html and css

but if you feel secure dealing with numbers and that kind of math abstraction go for it

[zealous pelican]

I couldn't get into programming school because I almost failed math 😄

[torn sphinx]

Well

SICP is THE resource for what you need

There are other ways to get there

[zealous pelican]

Though when I say math, I mean things like calculus and the like. Stats is a whole different ball game. THAT I know my way around quite all right

[torn sphinx]

Its not stats

its logic

stats, calculus, etc, are domain problems

working with computers is just list shuffling

the thing is that the clever ways to do stuff are quite abstract

Like, you could 100% transform your excel to very straightforward programming

Just translate your formulas to python code, and keep adding to them

But i guess what you want and why you are here is to approach it from another level

[zealous pelican]

This is honestly the end goal

To make this sheet into a stand-alone program of sorts

Something that is not limited to just being useable on a desktop

[torn sphinx]

That will work for a while

but will ultimately be your demise

[woeful ice]

hey guys anyone can tell me how to create this tables ?

Error: ERROR: object "user" does not exist LINE 1: ... "t1"."impulse", "t1"."stats", "t1"."badges" FROM "user" AS ... ^

for postresql

[torn sphinx]

Making a program work on desktop, mobile, etc

is not the real challenge

[zealous pelican]

So you're saying that a better approach is to make logic that isn't restricted to just working with financial products.

But that financial products just happen to be the variable that the logic is working with in this instance

[torn sphinx]

Nono

[zealous pelican]

Okay then I don't think I've understood perfectly

[torn sphinx]

First you need to come up with a way to work with financial products

This is called domain logic, or business rules

But then you need to learn to abstract and reuse

There is an example in SICP called an "accumulator"

Lets say you want a function that does +1

Thats pretty straightfoward, right? Give it 7 it returns 8, give it 22 it returns 23, etc

[zealous pelican]

right

[torn sphinx]

The idea though, is you go meta and realize

What if i want a function that does -1? Is that all that different?

Do i need two functions? One that does +1 and one that does -1?

[zealous pelican]

No, you just add -1

[torn sphinx]

Or can i have only one function that do botsh

yeah and then, what if i want to *2

or what i want to square the number?

or finds its square root?

its cube?

etc

[zealous pelican]

Okay yeah, by my current logic, I would need to code all of those functions

That's how I think

[torn sphinx]

You end up realizing its all a single function called an "accumulator"

that takes in, a number, a modifier symbol, and an operator

[zealous pelican]

Perhaps not the square and cube, because those I could define as combinations of the multiply function

[torn sphinx]

Yeah

thats true

but you could also have like (x, op, y)

[zealous pelican]

Right

[torn sphinx]

and do (x, +, y) (x, -, y) (x, to the, 2) (x, to the, 3)

the idea is that you define an abstract function that suddenly handles all kind of accumulating functions

It might not seem like it on a first sight, but this idea can also be applied to your problems

The idea being, understand what the logic of your program is

and then derivate meta-laws

The more you understand the "domain" of your program, the more you understand its meta

In your case, you should strive to understand financial operations the more you can, so that you can abstract these operations into a small amount of functions that is what will allow you to scale

Like grab your excel and look at how many of the formulas are fairly similar, have repeating operations

[zealous pelican]

Got it

[torn sphinx]

Thats programming

[zealous pelican]

So you think that it's a better idea to start with this instead of looking for a database solution first?

[torn sphinx]

is how some people get to sth better faster

[zealous pelican]

Because that's also something I am struggling with - this is clearly way more than I am qualified for. Where should I start?

[torn sphinx]

My answer is this: 1- By how fast computers are today, unless you are manipulating several hundred thousand entries, you wont see a performance increase by switching from csv/xls to a real database 2- A database solution does not solve scale issues, proper architecture does

Learning how to use databases is learning a syntax, a logic model, and doing busy work to connect all things together

But learning how to properly confront a computational problem and how to simplify and abstract its operations, is where programming is at

[zealous pelican]

Right

[torn sphinx]

By all means though, transform your excel to a proper db

will teach you a lot

but just keep in mind that wont solve your issues, it will create more

databases are just a fast excel

the actual way to simplify is to understand and rework the data structures

[zealous pelican]

Yes, so then before I even start thinking about transforming the excel to a db, I should think about how to extract the logic from my formulas, think about any other logic I might want in the future, and how to condense all this logic

[torn sphinx]

Thats my viewpoint but its also ideal

In all actuality, if you have time you should do both

Because they will reinforce each other

You transform your excel to a db, and that will get you thinking about the meaning behind your structure

Cause youll meet implementation walls

So, ultimately, all this talk was useless lol

[zealous pelican]

For me, not in the slightest

[torn sphinx]

Transform your excel to a db of your chosing, (read on them before doing so doe)

[zealous pelican]

This is stuff that I hadn't thought on at all

[torn sphinx]

And while you do that you will start having to think really hard about what your data implies

[zealous pelican]

I'm at a stage where I don't know how much I don't know

[torn sphinx]

Thats good

[zealous pelican]

Our entire talk about logic- this isn't something I knew I should think about

[torn sphinx]

The linux guy says that good programming is not about algorithms, is about understanding a problem enough to realize what data structures are better suited to represent it

Oh

[north vale]

any python fast api developer here

[torn sphinx]

im glad i could bring that to your mind then lol

thats programming

[zealous pelican]

I'm FAR from being a programmer. But it's something I want to learn

[torn sphinx]

well if you get anything out of this talk, take that, programming is not knowing a technology, is knowing how to represent a problem in firm logical terms

once you can do that on paper, you can translate it to any computational technology

cause they all work the same way

its just list shuffling

thats all a computer is, a calculator and a list

its the way they are feedbacked into each other what makes the magic

[zealous pelican]

So now going back to the database solutions - how do I figure out what I want?

What questions do I ask to make that decision?

[torn sphinx]

how much relation do you have between your tables?

you basically need a choice between relational and non-relational databases

https://www.logianalytics.com/relational-vs-non-relational-databases/

Sorry, bad link, i just linked the first on google

https://www.google.com/search?q=relational+and+non-relational+databases

give a read to those sites, after a couple you will kinda get the differentiation

[zealous pelican]

There are three dbs here - Log, Transfers, Data.
Log uses information from Data for calculating fees and taxes
Log and Transfers isn't connected
Data and Transfers isn't connected at the moment but will be in the future.

Dashboard pulls data from all three
ER (also a dashboard) pulls data from Log & Data

[torn sphinx]

then maybe sql and have four tables

log, transfers, data and logtodata

unless you can connect log to data with a foreign key

It depends on how you connect stuff, let me give you an example of a program i use

I got Categories, Media and Tags

[zealous pelican]

I use "data" to keep a record of what the strategy names are, what my tax rate is, what my platform's fees are, etc.

IDK if there's a more elegant way to do this

[torn sphinx]

Every Media and Tag belong to 1 category

But, every Tag can be connected to several Media, and Media can be connected to several Tags

Therefore, i have 4 tables, Category, Media, Tags, and TagsToMedia

(this is in sql, a relational db)

So, each media and each tag have a field called "category_id" that is related to the "id" of categories

And TagsToMedia is a table that has "tag_id" and "media_id"

they relate to the ids in the tag table and the media table

You get the difference?

many-to-one vs one-to-many

?

[zealous pelican]

Going to have to read this a couple of times. Gonna do that as soon as I am done with this work call

[lime lily]

whats the most secure hash to use to hash passwords?

[bright dune]

don't know about "most secure", but sha3 is good

[lime lily]

can blake2 work for passwords?

[prisma girder]

You can use Argon2 for example

This kind of questions is more suitable for #cybersecurity channel

You can also use bcrypt

[lime lily]

my bad. ill move there

[prisma girder]

SHA-3 family is highly effective on ASIC/FPGA devices so...

I don't think that storing passwords is a good approach

[woeful ice]

Error: ERROR: object "user" does not exist HAT 1: ... "t1"."impulse", "t1".stats", "t1"."badges" "user" FROM AS ...

cant create user named table

keyword..

[prisma girder]

Can you paste whole query?

[faint blade]

Ooh, is that one good for passwords? Or what do you mean?

[brave bridge]

No, if a hash works really fast on some device, it's bad, not good

a hash should be slow

[prisma girder]

[faint blade]

Oh yeah I was confused by the usage of "effective"

[prisma girder]

I should say it more clearly like @brave bridge did

[hard crown]

Hey, got exact same question as this https://stackoverflow.com/questions/51659259/add-sqlalchemy-foreign-key-id-to-another-table-based-on-the-value

The solution im looking for is a one liner similar to this

VALUES('val_1', (SELECT  val_2 FROM table_2 WHERE val_2 = something))```

[sharp tundra]

wat is numeric in sqlite?

[rigid yacht]

good mrning guys

[long dome]

how can I do a fetch with asyncpg (python/POSTGRESQL) to get the lowest value of a column in a table?

For example, I have a table about the listings in a marketplace, how can I grab the lowest price of all the listings?

ofc I can just iterate over each result and find the smallest but isn't there a more efficient way?

[storm mauve]

you can either order by and limit to 1, or use the MIN PostgreSQL function

[harsh pulsar]

https://www.postgresql.org/docs/current/functions-aggregate.html see min

[harsh dew]

anyon know how to work with mysql/mariadb in python

because i tried sql_connector

but it failed

i made this constuctQuery function

def constructQuery(bookName,bookAuthor,pagesRead,currentPage):
    return (
    'INSERT INTO main ',
    '(name, author, pagesRead, currentPage, timestamp)',
    f'VALUES ({bookName}, {bookAuthor}, {pagesRead}, {currentPage}, {datetime.date.today().strftime("%s")})'
)```
which takes the values and makes a tuple

nvm just switched to using the query command directly

now it works

[brave bridge]

@harsh dew Never use f-strings to construct queries. First, it's just not going to work: strings in SQL must be quotes, specifically in single quotes. Even if you add those, you'll be vulnerable to SQL injection.
Instead, you should use built-in query formatting.

[harsh dew]

i changed to a single line

def constructQuery(bookName,bookAuthor,pagesRead,currentPage):
    return f"INSERT INTO main (name, author, pagesRead, currentPage, timestamp) VALUES ('{bookName}', '{bookAuthor}', '{pagesRead}', '{currentPage}', '{datetime.date.today().strftime('%s')}')"

still sql injection

but also this is not a wabapp and a command line tool i made to track my book reports

so i am the only one with acess to this

[brave bridge]

Either way, it will break if your book title contains an '

[harsh dew]

'

[brave bridge]

Like "Hitchhiker's guide to Python"

[harsh dew]

\' is what i should

escape \'s

[brave bridge]

@harsh dew
You should just use built-in query formatting. ```py
conn.execute(
"INSERT INTO main (name, author, pagesRead, currentPage, timestamp) VALUES (%s, %s, %s, %s, %s)",
(bookName, bookuthor, pagesRead, currentPage, datetime.date.today().strftime('%s'))
)

(the exact syntax will depend on the library, you'll find it in the documentation)

[harsh dew]

mysql_connector is the lib i am using

[brave bridge]

You can find examples here https://dev.mysql.com/doc/connector-python/en/connector-python-example-cursor-transaction.html

[grim zephyr]

Ignoring exception in command enableinvite:
Traceback (most recent call last):
  File "C:\Users\USER\AppData\Local\Programs\Python\Python39\lib\site-packages\discord\ext\commands\core.py", line 85, in wrapped
    ret = await coro(*args, **kwargs)
  File "C:\Users\USER\Desktop\ZENESIS DEVELOPEMENT\ZENESIS\INVITE LOGGER\cogs\invite.py", line 18, in enableinvite
    stats = invite.find_one({"guild_id": ctx.guild.id})
AttributeError: type object 'invite' has no attribute 'find_one'

@brave bridge sry for the ping again but can u help me a bit

[grim zephyr]

Channel id: {'_id': ObjectId('6125e2099ef89451486c0308'), 'guild_id': 853197674899046431, 'channel_id': 868132863017619496}

i want the channel id only but how

[desert sandal]

Anyone know how do i put the filename while importing data into a MongoDB.. like what syntax should the filename be in ?

[zealous flare]

I have an eternal problem with sqlite3

how to make a separate balance for each guild

[faint blade]

This looks to be Mongo?

Either its .channel_id or (more commonly) ['channel_i'].

[grim zephyr]

it is mongo

and i fixed it too

[desert sandal]

..

[woeful ice]

i can see just this line..

Error: ERROR: object "user" does not exist HAT 1: ... "t1"."impulse", "t1".stats", "t1"."badges" "user" FROM AS ...

i tryin create user table on postgres but its dont let me create named user table reserved..

[prisma girder]

So... Can you paste a code then?

[woeful ice]

https://pastebin.pl/view/060ad193

this my database.py

i am trying run this repo https://github.com/b1naryth1ef/GoPlayNao

i convert to python3 but cant connect postgres

in my local machine i can connect but in ubuntu vds cant connect

this is giving in local machine

[desert sandal]

help pls

[jade osprey]

Hi i just started using SQLlite for my bot and I am getting an error
here is the error

Ignoring exception in command warn:
Traceback (most recent call last):
  File "/opt/virtualenvs/python3/lib/python3.8/site-packages/discord/ext/commands/core.py", line 85, in wrapped
    ret = await coro(*args, **kwargs)
  File "main.py", line 82, in warn
    cursor = await bot.db.execute("INSERT OR IGNORE INTO guildData (guild_id, user_id,warn_reason) VALUES (?,?,?)", (member.guild.id, member.id, reason))
  File "/opt/virtualenvs/python3/lib/python3.8/site-packages/aiosqlite/core.py", line 184, in execute
    cursor = await self._execute(self._conn.execute, sql, parameters)
  File "/opt/virtualenvs/python3/lib/python3.8/site-packages/aiosqlite/core.py", line 129, in _execute
    return await future
  File "/opt/virtualenvs/python3/lib/python3.8/site-packages/aiosqlite/core.py", line 102, in run
    result = function()
sqlite3.OperationalError: table guildData has no column named warn_reason

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

Traceback (most recent call last):
  File "/opt/virtualenvs/python3/lib/python3.8/site-packages/discord/ext/commands/bot.py", line 939, in invoke
    await ctx.command.invoke(ctx)
  File "/opt/virtualenvs/python3/lib/python3.8/site-packages/discord/ext/commands/core.py", line 863, in invoke
    await injected(*ctx.args, **ctx.kwargs)
  File "/opt/virtualenvs/python3/lib/python3.8/site-packages/discord/ext/commands/core.py", line 94, in wrapped
    raise CommandInvokeError(exc) from exc
discord.ext.commands.errors.CommandInvokeError: Command raised an exception: OperationalError: table guildData has no column named warn_reason

here is my code:

async def initialize():
    await bot.wait_until_ready()
    bot.db = await aiosqlite.connect("expData.db")
    await bot.db.execute("CREATE TABLE IF NOT EXISTS guildData (guild_id int, user_id int, exp int, warn_reason, PRIMARY KEY (guild_id, user_id))")

@bot.command(aliases=['wr'])
async def warn(ctx,member: discord.Member=None, reason=None):
  if not reason:
    await ctx.send('please provide a person to warn')
  cursor = await bot.db.execute("INSERT OR IGNORE INTO guildData (guild_id, user_id,warn_reason) VALUES (?,?,?)", (member.guild.id, member.id, reason)) 
  if cursor.rowcount == 0:
    await bot.db.execute("UPDATE warnData SET warn_reason = ? WHERE guild_id = ? AND user_id= ?", (reason,member.guild.id,member.id))
    cur = await bot.db.execute("SELECT warn_reason FROM guildData WHERE guild_id = ? AND user_id = ?",(member.guild.id, member.id))
    
    data = await cur.fetchone()
    reason = data[0]
    await ctx.send(f'{member.mention} has been warned for {reason}')
    await bot.db.commit()

I just picked up databases so please dum your answers down

the leveling system works fine

[austere portal]

In the create table query you havent given the warn_reason column a constraint

CREATE TABLE IF NOT EXISTS guildData (guild_id int, user_id int, exp int, warn_reason, PRIMARY KEY (guild_id, user_id))```

[jade osprey]

sorry but what is constraint

[torn sphinx]

Hi, if I execute statements using asyncpg like conn.execute('INSERT INTO abc VALUES ('a', 'b', 'c'), ('d', 'e', 'f');'), will it automatically convert the quotes in VALUES ('a', 'b', 'c') stuff to the valid quotes accepted by postgres?

[jade osprey]

if you don't mind

[austere portal]

You have not given the data type of that column

[jade osprey]

ohh

how do I do that with str

[jade osprey]

is the code providing a constraint

[austere portal]

No

i guess you want to give it TEXT

[jade osprey]

so like
warn_reason TEXT,

like how i did with int

[austere portal]

Yes

[jade osprey]

i changed it

    await bot.wait_until_ready()
    bot.db = await aiosqlite.connect("expData.db")
    await bot.db.execute("CREATE TABLE IF NOT EXISTS guildData (guild_id int, user_id int, exp int, warn_reason TEXT, PRIMARY KEY (guild_id, user_id))")

but i am still getting the same error

[austere portal]

because you have already created the table

[jade osprey]

uhhh

how do i fix that

create a new table?

[jade osprey]

should I delete make a new collum or uhh

[austere portal]

you can delete it and create a new one

sqlite doesnt support changing column constrains iirc

[jade osprey]

so I should remake the table right?

[austere portal]

https://stackoverflow.com/a/4007086

[jade osprey]

Thanks a lot for the help

[austere portal]

👍

[jade osprey]

😄

[lime lily]

  File "/home/gg/.local/lib/python3.9/site-packages/mysql/connector/connection_cext.py", line 513, in cmd_query
    self._cmysql.query(query,
_mysql_connector.MySQLInterfaceError: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '$2b$12$GSbEpKQh1aEKyaVRxp9ix..FGaWzYfvHNz2rMuiJOl47v7g8dOncG'', '12')' at line 1

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "/home/gg/pj/main.py", line 28, in <module>
    register()
  File "/home/gg/pj/main.py", line 17, in register
    curse.execute("INSERT INTO users(hash,uid) VALUES ('{}', '{}')".format(pas_hash,uid))
  File "/home/gg/.local/lib/python3.9/site-packages/mysql/connector/cursor_cext.py", line 269, in execute
    result = self._cnx.cmd_query(stmt, raw=self._raw,
  File "/home/gg/.local/lib/python3.9/site-packages/mysql/connector/connection_cext.py", line 518, in cmd_query
    raise errors.get_mysql_exception(exc.errno, msg=exc.msg,
mysql.connector.errors.ProgrammingError: 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '$2b$12$GSbEpKQh1aEKyaVRxp9ix..FGaWzYfvHNz2rMuiJOl47v7g8dOncG'', '12')' at line 1

i just switched to bcrypt and im getting this error? could it be the columns in the database since i set them as binary they cant store the hash or?

 14     pas = input().encode("utf-8")
 15     uid = input()
 16     pas_hash = bcrypt.hashpw(pas, salt)
 17     curse.execute("INSERT INTO users(hash,uid) VALUES ('{}', '{}')".format(pas_hash,uid))
 18     connection.commit()

this is the code

[unkempt prism]

which mysql library are you using. Instead of treating them like strings using the string format it would be better to bind the values. This way its protects you from sql injection.

[lime lily]

mysql.connector one, and what should i bind the values to?

[unkempt prism]

There is an example here https://dev.mysql.com/doc/connector-python/en/connector-python-example-cursor-transaction.html

see that the data is in a separate param

cursor.execute(add_employee, data_employee)

[lime lily]

yeah i tried it before it i cant seem to get it to work

i had it before and it was pretty buggy

thats not the problem here,im wondering on how i can store the bcrypt hash in mysql

[unkempt prism]

my bet is that pas_hash contains a ' at the end of the hash. And thats why there is duplicates quotes before the 12. Better variable binding should fix it though you can take out or escape any ' and any other chars that will cause you problems.

'$2b$12$GSbEpKQh1aEKyaVRxp9ix..FGaWzYfvHNz2rMuiJOl47v7g8dOncG'', '12')'

[lime lily]

ill try that thanks for the suggestions

[orchid plover]

    async def add_money(self, userid: int, amount: int):
        await self.bot.conn.execute("INSERT INTO Money(userid, balance) VALUES ($1, $2) ON CONFLICT(userid) DO UPDATE SET balance = balance + $2", userid, amount)
        return

    async def remove_money(self, userid: int, amount: int):
        await self.bot.conn.execute("INSERT INTO Money VALUES ($1, $2) ON CONFLICT(userid) DO UPDATE SET balance = balance - $2", userid, amount)
        return

    async def get_current_money(self, userid: int):
        result = await self.bot.conn.fetchrow("SELECT balance FROM Money WHERE userid = $1", userid)
        return result[0]

    @commands.command()
    async def test(self, ctx, arg, amount):
        if arg == "add":
            await self.add_money(ctx.author.id, amount)
        elif arg == "remove":
            await self.remove_money(ctx.author.id, amount)
        else:
            await ctx.send(self.get_current_money(ctx.author.id))

If i try m!test add 10 i get:

column reference "balance" is ambiguous

But i only have this table:

    await bot.conn.execute("""CREATE TABLE IF NOT EXISTS Money (
                                        userid bigint PRIMARY KEY,
                                        balance bigint
                                        )""")

[faint blade]

It's probably complaining about the second balance. Where you do balance + $1

Actually no hmm

Does removing work?

[desert sandal]

What's the MongoDb equivalent of MySQL's select from table where name like %s

[grim vault]

I thiink you need to reference the table:

await self.bot.conn.execute("INSERT INTO Money(userid, balance) VALUES ($1, $2) ON CONFLICT(userid) DO UPDATE SET balance = Money.balance + EXCLUDED.balance", userid, amount)```

And you can use the same function for add/remove if you just use -amount as parameter for remove.

[shrewd frigate]

Can someone help.
TypeError: replace_one() missing 1 required positional argument: 'replacement'
I have no idea what that means. MongoDB by the way.

[grim zephyr]

Ignoring exception in on_member_join
Traceback (most recent call last):
  File "C:\Users\USER\AppData\Local\Programs\Python\Python39\lib\site-packages\discord\client.py", line 343, in _run_event
    await coro(*args, **kwargs)
  File "C:\Users\USER\Desktop\ZENESIS DEVELOPEMENT\ZENESIS\INVITE LOGGER\cogs\invite.py", line 38, in on_member_join
    log_channel = c_id["channel_id"]
TypeError: '_asyncio.Future' object is not subscriptable 

[faint blade]

Are you not awaiting c_id?

[copper dragon]

What's c_id? Is it a cluster, collection or document? You only need to await collections

[desert sandal]

pls

[pallid shard]

curosor.execute("DELETE FROM tempban WHERE guild_id = ? AND member_id = ? ",(guild, member,)) sqlite3.InterfaceError: Error binding parameter 1 - probably unsupported type.

                    await guild1.unban(member1)
                    curosor.execute("DELETE FROM tempban WHERE guild_id = ? AND member_id = ? ",(guild, member,))
                    db.commit()```
 whats wrong

            if time_now >= time:
                guild1 = self.client.get_guild(guild)
                member1 = await self.client.fetch_user(member)
                print(member1)
                banned_users = await guild1.bans()
                print(banned_users)
                if not banned_users:
                    curosor.execute("DELETE FROM tempban WHERE guild_id = ? AND member_id = ? ",(guild, member))
                    db.commit()
                    return
                for member in banned_users:
                    print(member.user.name)
                    if not member1.name and member1.discriminator in member.user:
                        curosor.execute("DELETE FROM tempban WHERE guild_id = ? AND member_id = ? ",(guild, member))
                        db.commit()
                        return
                    
                    await guild1.unban(member1)
                    curosor.execute("DELETE FROM tempban WHERE guild_id = ? AND member_id = ? ",(guild, member,))
                    db.commit()```
 The full part

[grim zephyr]

Wait a sec I need to await collection?

[copper dragon]

when you are getting a document from collection

not to get the collection itself from the cluster

[tranquil zinc]

Using postgresql

why is this wrong

#!/usr/bin/python
import psycopg2


def create_tables():
    """ create tables in the PostgreSQL database"""
    commands = (
        """
        CREATE TABLE friends (
                friendName VARCHAR(255) NOT NULL,
                Age INTEGER NOT NULL,
                FriendID INTEGER NOT NULL,
                PRIMARY KEY (FriendID)
                
        )
        """)
    conn = None
    try:
        # read the connection parameters

        # connect to the PostgreSQL server
        conn = psycopg2.connect(
        host="localhost",
        database="friends",
        user="postgres")
        cur = conn.cursor()
        # create table one by one
        for command in commands:
            cur.execute(command)
        # close communication with the PostgreSQL database server
        cur.close()
        # commit the changes
        conn.commit()
    except (Exception, psycopg2.DatabaseError) as error:
        print(error)
    finally:
        if conn is not None:
            conn.close()


if __name__ == '__main__':
    create_tables()```

Error : can't execute an empty query

@copper dragon

[copper dragon]

huh sorry, i just mongodb lol

[tranquil zinc]

man postgresql confuses me!

[copper dragon]

same

[pallid shard]

??

[torn sphinx]

>>> commands = ("""DROP TABLE x;""")
>>> for command in commands:
...     print(command)
... 
D
R
O
P
 
T
A
B
L
E
 
x
;
>>> 

the brackets are just evaluated to a string i think, if you add a comma at the end and make it a tuple its probably what you want

>>> commands = ("""DROP TABLE x;""",)
>>> for command in commands:
...     print(command)
... 
DROP TABLE x;

[shrewd frigate]

I created server logs and I collected the Channel ID. When someone does another command I want it to update the channel ID that’s in the database and replace it with the new one. I have no idea how.

MongoDB by the way.

[austere portal]

Commands is not a tuple, it is a string

!e py x = (""" Test """) for i in x: print(i) print(type(x))