databases

[uneven glacier]

Hello, anyone can help me to find a json dataset with 1 million+ entries? I am doing a college job where I have to turn that into a relational model

[proven arrow]

because you aren't checking to see if any data is there. Also i'm not sure why you keep adding and removing different parts of your code each time you paste some code here. Its very hard to help like that, when each code paste is different.

[normal glade]

I am checking if there is data with if not data right?

It's a boolean?

[proven arrow]

In your last paste this is what your doing:

c = db.cursor()
data = c.fetchall()
if not data:

Thats always going to be an empty list.

[normal glade]

Why?

[proven arrow]

Because cursor has nothing to fetch, since you never execute any query on the database

[normal glade]

So I should use data = c.fetchall(data)?

Or is that not a thing?

[proven arrow]

cursor.execute("SELECT rowid FROM data WHERE pay = ?", (pay,))
data = cursor.fetchall()
print(data)

if not data:
  print('There is no data stored')
  
  pay = input("Please enter how much you earn an hour: ")
  hours = input("Please enter how many hours you work a day: ")
  
  cursor.execute("INSERT INTO data(pay,hours) VALUES(?,?)", (pay, hours))
  db.commit()
else:
  break

@normal glade

[normal glade]

So now it's that

from login import *
import __main__ as main

def information():
  if main.is_logged_in:
    with sqlite3.connect("users.db") as db:
      c = db.c()
    while True:
      c.execute("SELECT rowid FROM data WHERE pay = ?", (pay,))
      data = c.fetchall()
      print(data)

      if not data:
        print('There is no data stored')
  
        pay = input("Please enter how much you earn an hour: ")
        hours = input("Please enter how many hours you work a day: ")
  
        c.execute("INSERT INTO data(pay,hours) VALUES(?,?)", (pay, hours))
        db.commit()
    else:
      break
  else:
    pass```

[proven arrow]

db.c() is not a thing, so dont just "replace all" if thats what you did 🤨

[normal glade]

Yeah fixed that

But this won't work

Because it now refers to pay before the input(pay)

[proven arrow]

As i said its because you keep changing your code every time you paste. Here you sent code with pay as a function parameter, #databases message

[normal glade]

Yeah

[proven arrow]

Those are simple fixes which you should be able to do, also your break is not inside the loop.

[normal glade]

Because there I put py pay = input("Please enter how much you earn an hour: ") hours = input("Please enter how many hours you work a day: ") outside of the function

[cosmic smelt]

def insert_into(name, number, department):
    SQL = ('''INSERT INTO employees (Name, Number, Department) 
              VALUES (%s, %s, %s)''')
    record = (name, number, department)
    cursor.execute(SQL, record)
    conn.commit()
    print('success')

insert_into('brian', 4, 'manufacturing')

returns an error sqlite3.OperationalError: near "%": syntax error
can somebody tell me why? thx :}

[proven arrow]

its ? not %s

[cosmic smelt]

amazing. thank you so much

[torn sphinx]

hello

I have Orders table i have made like this,

CREATE TABLE orders (id INTEGER PRIMARY KEY, order_date DATE, user_id INTEGER FOREIGN KEY) # some fields i leave out to be simple 

user_id is a foreign key to products table. Now i want to group all the users orders, but also number them individually start from 1. I dont know if this makes sense?

so let me give example

id | order_date | user_id | numbering
---------------------------------------
7      .....        100         1
34     .....        100         2
75     .....        100         3
3      .....        222         1
87     .....        222         2

so for each user group it start at 1...x

oh and the counting is ordered by order date 😬

[proven arrow]

Use a window function, with ROW_NUMBER()
You can PARTITION BY user_id ORDER BY order_date
See this link for example, https://www.sqlitetutorial.net/sqlite-window-functions/sqlite-row_number/

[torn sphinx]

oh let me see, but that looks confusing 🥵

[proven arrow]

Well see the example in that link, as it does the same thing you want. You just need to change col names.

[torn sphinx]

ya i got it lol thanks!

[scarlet nymph]

hey guys tl;dr I'm making a bunch (like, thousands) of INSERT SQL queries with pyodbc and it's taking a lot longer than I think it should, is this something that I could accelerate with 'transactions'?

[proven arrow]

No

[scarlet nymph]

😦 is there another way I could speed it up? the total amount is like 22MB, and it takes several minutes, which is off by orders of magnitude. perhaps multiple connections at once?

I guess a different way of asking is, if I want to insert ten thousand records at once, what's the fastest way to do it?

[proven arrow]

What are you using to insert? And when are you committing?

[scarlet nymph]

what am I using... like, the query? query = 'INSERT INTO call_records (answer_time, end_time, start_time, direction, disposition, duration, caller_name, caller_number, callee_name, callee_number, leg_id, queue_id, queue_name) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?);'

[proven arrow]

I meant the function to insert or execute the statement

[scarlet nymph]

I'm not familiar with the commit terminology, but maybe you mean with cursor.execute(query, call['answerTime'], call['endTime'], call['startTime'], call['direction'], int(call['disposition']), int(call['duration']), call['caller']['name'], call['caller']['number'], call['callee']['name'], call['callee']['number'], call['legId'], queue_id, queue_name): return 0

[proven arrow]

That's probably why

Use executemany

[scarlet nymph]

woah. lol that's probably exactly what I need :0 I will look at it

thank you!

[proven arrow]

I don't use pyodbc but if you check their docs they should have a executemany function. Also I once read they had something like fastexecutemany which can further speed it up. But you can check their docs for the full details.

[scarlet nymph]

yeah I'm reading it right now, this is precisely what I need

[mortal nymph]

update names set id=Null,lastname_id=Null where id=3

this is giving me unknown column 'id' in where clause

how do I fix this

[rotund ravine]

hey all. i'm trying to use the request library to make api request to fetch a json then store the resulting json response in sqlite. should i think about serializing the json before adding it into sqlite? (its a single line json response, but i'll be doing computation on it, so my main focus is performance) ty 😄

[pastel torrent]

Anybody know how can I get rid of red-underlines in Azure Data Studio for mac? I'm working with a SQL script and my syntax is correct.

[wary helm]

can I use commas in sql statements

this is for theory work no programming

so if i wanna return 3 different collumns

id be like

SELECT Title, Forname, Surname

From Customer

Where Town = 'New town'

would this be elligible

[pastel torrent]

That's totally fine

[paper flower]

https://www2.sqlite.org/cvstrac/wiki?p=ConverterTools

[autumn rune]

hi I need some help, so I have a discord bot which uses MySQL, and I want to store data for each user, each user will have an inv and a bal column, should I create a table for each user, or just create one table which contains all the users, please assume that the bot is famous and thousands of users use it.

@ on response

[terse wigeon]

create a table for users

[fading sage]

Hello, im using mongodb and i wanna find one and update im using

formAccepted = wlforms.find_one_and_update({ 'status': 'accepted' }, { 'status': 'acceptedSent' })

and im getting that error

Unhandled exception in internal background task 'sending_forms'.
Traceback (most recent call last):
  File "C:\Users\makow\Documents\Projekty\letsrp-discordbot\venv\lib\site-packages\discord\ext\tasks\__init__.py", line 101, in _loop
    await self.coro(*args, **kwargs)
  File "C:\Users\makow\Documents\Projekty\letsrp-discordbot\forms.py", line 8, in sending_forms
    formAccepted = wlforms.find_one_and_update({ 'status': 'accepted' }, { 'status': 'acceptedSent' })
  File "C:\Users\makow\Documents\Projekty\letsrp-discordbot\venv\lib\site-packages\pymongo\collection.py", line 3242, in find_one_and_update
    common.validate_ok_for_update(update)
  File "C:\Users\makow\Documents\Projekty\letsrp-discordbot\venv\lib\site-packages\pymongo\common.py", line 557, in validate_ok_for_update
    raise ValueError('update only works with $ operators')
ValueError: update only works with $ operators

Where i should write the $ operator?

[normal glade]

from login import *
import __main__ as main
import sqlite3

def information():
  if main.is_logged_in:
    with sqlite3.connect("users.db") as db:
      c = db.cursor()
    while True:
      userID = ("SELECT userID FROM users")
      f = open("userID.txt", "w")
      f.write(str(userID))
      f.close()
      checkID = ("SELECT pay FROM data WHERE userID = ?")
      c.execute(checkID,[userID])
      data = c.fetchall()
      print(data)

      if not data:
        print('There is no data stored')
  
        pay = input("Please enter how much you earn an hour: ")
        hours = input("Please enter how many hours you work a day: ")
  
        insertData = """INSERT INTO data(pay,hours)
        VALUES(?,?)"""
        c.execute(insertData,[(pay),(hours)])
        db.commit()
        break
      else:
        pass
  else:
    pass```

Hi

Right now I have this peace of code

But I keep getting this error

And I don't see what's wrong with the indentation

[proven arrow]

You have a mix of tabs and spaces in the code, so dont use both. Only use one of them.

[burnt turret]

@fading sage the second dictionary you pass should have the $ operator, something like
{"$set":{"status":"acceptedSent"}}

[fading sage]

thanks

it works

[hazy smelt]

Hi I am getting this super annoying error

Can someone help pls

[golden warren]

Hello there!
I would like to call my dict self.degrees in my windows.py file. Can you help me ?
functions.py :

class HoursToDegrees:
    def __init__(self, target, hours: int):
        # Initialize global variables.
        self.target = target
        self.hours = hours
        self.degrees = {'1': None, '2': None, '3': None, '4': None, '5': None,
                        '6': None, '7': None, '8': None, '9': None, '10': None, '11': None, '12': None}

windows.py

#!/usr/bin/python

import tkinter
import turtle
from src.functions import *


class Window:
    def __init__(self):
        # Initialize global variables.
        self.win = tkinter.Tk()
        self.c = tkinter.Canvas(self.win, height=900, width=1200)
        self.turtle = turtle.RawTurtle(self.c)
        self.gsv = GetSetVar(self.turtle)
        self.htd = HoursToDegrees(self.turtle, 12)

    def draw_circle(self, radius=200):
        # Set position to write circle.
        self.turtle.up()
        self.turtle.goto(0, -radius)
        self.turtle.down()

        # Draw circle.
        self.turtle.circle(radius)

        # Set position to center.
        self.turtle.up()
        self.gsv._set_pos(0, 0)
        self.gsv._get_pos()

        # Rotate cursor.
        position_degrees = self.htd._from_htd()
        # self.turtle.right(position_degrees)

    def generate_window(self):
        # Generate window.
        self.win.title('PILOTE DE MONTURE EQ')
        self.draw_circle()
        self.c.pack()
        self.win.mainloop()

Can you help me ?

[proven arrow]

@hazy smelt Its because you have unfetched results

You probably have a select statement before that with the same cursor, which is returning multiple rows. You are not fetching all the rows hence, and trying to use the cursor, which is why you get the error.

To fix, use a buffered cursor, or do fetchall() when getting the results.

@golden warren Make the object, and call the class

from functions import HoursToDegrees

hours_class = HoursToDegrees()
degrees_dict = hours_class.degrees

[feral thorn]

Say i have 10 tags saved in 1 table column, how can i fetch all at the same time?

Database - Postgres

[proven arrow]

Do you mean 10 tags in a single field?

[feral thorn]

Yes..

Like -
Tag1
Tag2
Tag3
So on...

[proven arrow]

SELECT column_name FROM table_name WHERE some_condition

[feral thorn]

Will try, thanks again capt :)
Successfully made add prefix and remove one till now 😀

[quartz moon]

I'm trying to update an existing document by running:
await self.client.members.update_by_id(objeto)
objeto = {'_id': 494170672122167296, 'disc_nick': 'Danelue11', 'nick': 'Jabato311', 'level': 40, 'created': '2020-11-26 16:23', 'otherusers': {1: {'_id': 1, 'nick': 'Tarmo', 'level': 40, 'created': '2020-11-26 17:24'}}}I define my own api for mongodb with some useful queries, here is the update_by_id() implementation:

async def update_by_id(self, dict):
        """
        Modify an existing document
        """
        if not isinstance(dict, collections.abc.Mapping):
            raise TypeError('dict expected')
        if not dict["_id"]:
            raise KeyError('_id not found')
        if not await self.find_by_id(dict["_id"]):
            return
        id = dict["_id"]
        dict.pop("_id")
        await self.db.update_one({"_id": id}, {"$set": dict})```
The existing document has 'otherusers': { } empty and I'm trying to add a new entry to that dict. Any help is very welcome

[quartz moon]

Fixed I didn't realise that I had to convert the key inside otherusers dict into string first. I think that I need a rest😅

[fading sage]

how can i get the user who use command?

[quartz moon]

You mean in discord.py?

[fading sage]

yes

[quartz moon]

ctx.author.name or ctx.author.id if you want to obatain the id

[fading sage]

ctx.message.author?

ok

thanks

[quartz moon]

no worries

[warped frigate]

is there a way to make mongodb discard or ignore all updates to a specified document for x seconds after the discard/ignore command is run

reason: I have a large codebase that has to use websockets to connect to another codebase, and I have to transfer data between the two. However, when the code starts, I have to load each document into a Python object for quick usage, and then save it to mongodb when im done with it. At some point though, I made the code have more than one copy of the document for some long-forgotten reason, and now when I receive data from the websocket to save to the database, the websocket data saves first, then some local copy saves next and overwrites the websocket data, effectively making the websocket useless. I want to make it so that when the websocket data saves, any attempted overwriting in the next second or two fails silently, as I don't want to go through a hellish 20k+ line codebase spread out among like 30 files searching for a single line of saving.

[warped frigate]

ok i made it work by just making a dict of the last save times for each document and referencing that when saving

[lusty slate]

how would I get a specific part of a cell in sqlite, not as a tuple but get the info as a string

So for example:
I have:
TestTable:
Name = John | Age = 18 | email = [e-mail]| How can I return just the email instead of the entire cell

[nova phoenix]

Correct me if I'm wrong but that sounds like a simple SELECT statement.
If you just want to grab the email you can do:

SELECT email FROM table WHERE name=?```

WHERE, is optional. Depends if you want every email from the table (in that case dont use WHERE). If you want a specific email, e.g: John's. Just do

WHERE name=? , ("John",)

[lusty slate]

TY

VM

[nova phoenix]

Np

[zealous widget]

is this the place to ask about mysql?

[placid rune]

what's a good way to turn a big dict into an sql file

[graceful dust]

anyone know how to make a custom user defined aggregate function in MYSQL ?

i need to find median for each column group by column name

so

select col_name, median(some_col)
group by col_name;

[warped aspen]

what's a data base used for?

[shell ocean]

storing data

but in such a way that certain guarantees are made

for example, if there's a crash, the database won't be in an inconsistent state

look up ACID (relevant to SQL)

[warped aspen]

so in discord.py i can use data bases?

[shell ocean]

you should if you want to store anything permanently

[warped aspen]

ok

does my data base need to be on a website or it can be on my pc?

@shell ocean ^

[proven arrow]

@warped aspen depends what database you use and where your application is running

Some people have it on the same system, and some on a separate. Depends on how much resources you would need.

[warped aspen]

what if i do a whitelisting system dont i need a database to store the keys?

and generate

etc

[proven arrow]

What kind of whitelisting system?

[tulip mortar]

Hello, new to postgreSQL my script take 1 minute to add ~60 000 rows :

cur.execute("CREATE TABLE characters (cid serial PRIMARY KEY, firstname varchar, name varchar, gid integer);")
query = "INSERT INTO characters (cid, firstname, name, gid) VALUES (%s,%s,%s,%s) ON CONFLICT DO NOTHING"
recordlist = []
for response in responses:
  # add 60 000 tuple to recordlist
cur.executemany(query, recordlist)

it work in ~50 secondes, with sqlite it take 5 secondes, would love to down that to a second, thanks

[proven arrow]

Use executemany

[warped aspen]

i want to make a program when you open it a key is created in the database

[tulip mortar]

I did, maybe wrong ? look at the last line

[warped aspen]

and the user that opened the program has his pc name next to the key created

[proven arrow]

Ah ok I missed that

Is it pscyopg2 you are using?

[warped aspen]

me?

[tulip mortar]

yes

[proven arrow]

Ok well their current executemany is not the most efficient, in fact just execute in a for loop would be faster. Or take a look at the functions given in this link which is supposed to also be more performant than executemany as well https://www.psycopg.org/docs/extras.html#fast-exec

[tulip mortar]

thanks

[proven arrow]

If you want all user data to be together in a single place then you can use a database

[warped aspen]

oh so how do i make a database any website or tutorial?

[proven arrow]

You would first want to be familiar with SQL, which is the language that allows you to interact and perform actions on the database system.

In terms of databases there are different ones. SQLite is probably the easiest and simplest to get started with, and its libraries come pre packaged with python. So if you are new then just have a play around with that first.

[cedar needle]

Hello, #help-croissant please help me

[subtle flax]

If there is an application using sqlite database, is it ok for my script to use this database by creating my own indexes on tables? Application does not provide functions that I need, I can only read/write this info directly from database

Application is opensourced so I know the table structure and everything

[subtle flax]

Maybe there is a way to make my indexes outside of main db?

[velvet coyote]

Hey I am trying to insert a timestamp to the db, (postgres)
like this, but i am etting this error:

now_formatted_nicely = datetime.datetime.utcnow().strftime("%m-%d-%Y %H:%M:%S")
            delete_time = datetime.datetime.utcnow() + datetime.timedelta(hours=1)
            formated_delete_time = delete_time.strftime("%b-%d %H:%M:%S")
```I am insering these values, but while doing so, i am getting this error could somone help me.
```asyncpg.exceptions.DataError: invalid input for query argument $4: '11-27-2020 10:00:04' (expected a datetime.date or datetime.datetime instance, got 'str')

[shell ocean]

what do you think the error means

[velvet coyote]

it wants a datetime but it got a string

but when I try to insert the same values through the command line it works

[shell ocean]

yeah, so pass it a datetime instead?

[proven arrow]

Their documentation is not so clear, but yes it should since it implements the python dbapi

Yeah because it doesnt have a date type.

Because aiomysql is based on that

So maybe it needs a different version

[foggy quarry]

Hi i want to make a databasevin pytgon is there any easier way instead of using django

[proven arrow]

What exactly is it you want to do with the database?

[craggy girder]

in postgresql, do i not have to create a cursor? (asyncpg), i am asking this because i didnt see the code in the docs creating a cursor

[proven arrow]

Its not necessary no

[craggy girder]

ooh

[proven arrow]

They have their use cases, which you can read here, https://magicstack.github.io/asyncpg/current/api/index.html#cursors

[craggy girder]

alright thanks

[foggy quarry]

i wantbto create a database

[craggy girder]

'''INSERT INTO quirks(username, quirk) VALUES ({}, {})'''.format(str(ctx.author), quirk)``` is this the correct way of inserting variable values? (asyncpg)

[foggy quarry]

i use ? instead

[craggy girder]

oh

but does format works here?

or can you show me an example on how to use ?

[proven arrow]

Dont use format, its bad.

[craggy girder]

yes i heard that from somewhere, so what should i do?

[proven arrow]

conn.execute("INSERT INTO quirks(username, quirk) VALUES($1, $2)", str(ctx.author), quirk)

[craggy girder]

oooh

epic, thanks

[proven arrow]

Well what is stopping you? What are you stuck on?

[foggy quarry]

is there a library that can help me do that

[proven arrow]

What database are you using?

[foggy quarry]

postgres

[proven arrow]

So do you want to write raw SQL queries or use an ORM (library) that generates the queries for you?

[foggy quarry]

a migration system only

that produces sql quereis

i useu couchdb

but it doesnt working

[proven arrow]

I see, I think the most popular is SQLalchemy, so you can check that out. Ive never used any python orms so not so sure which is the best. But you mentioned Django in your original question so if you're doing Django project then use the Django built in one.

[foggy quarry]

no i dont need website for this case

django migratoin is ok btw although i dont need all of the features

[proven arrow]

Well you mentioned you use couchedb so neither of what I said would help you.

[craggy girder]

if i am selecting a value from a postgres db, if it isnt found will it return an empty list like sqlite?

[foggy quarry]

yeah i search for SQLalchemy now

[proven arrow]

It doesnt support couchedb

[foggy quarry]

no i used couchdb now im using postgres for this project

[craggy girder]

or does it return Null or something?

[proven arrow]

Oh yes I forgot you said that, my bad.

[proven arrow]

Depends what function you use to fetch

[craggy girder]

fetchrow

[proven arrow]

I think fetchrow, and fetchval will return None

[craggy girder]

oh i see

[proven arrow]

fetch returns a empty list

[craggy girder]

hmm, whats the difference between fetch and fetchrow, like will fetchrow find from a row while fetch will find from the whole column?

[proven arrow]

fetch will return all matching rows of your query, whereas fetchrow will return the first matched row

[craggy girder]

ooooh

but my query cant be same, so it wont be much different right

[proven arrow]

What do you mean?

[foggy quarry]

thank you dude

[craggy girder]

nvm

thanks

[foggy quarry]

it returns false or null in sql

pip install SQLAlchemy==1.4.0b1

this should work for me

[craggy girder]

so a fetchrow in asyncpg returns a asyncpg.Record object, i dont understand how will you access a particular value from it can someone help

apparently its this method, but i dont understand how to use this

like in this case ```py
row = await conn.fetchrow("SELECT spins FROM quirks WHERE userid = $1", int(ctx.author.id))

will i do

row.get('spins')``` ?

[long slate]

i was trying to insert a test document in mongo db using motor but it threw some kinda error

  File "C:\Users\DELL\AppData\Local\Programs\Python\Python38\lib\site-packages\pymongo\helpers.py", line 167, in _check_command_response
    raise OperationFailure(msg % errmsg, code, response,
pymongo.errors.OperationFailure: bad auth : Authentication failed., full error: {'ok': 0, 'errmsg': 'bad auth : Authentication failed.', 'code': 8000, 'codeName': 'AtlasError'}```
code :
```py
async def test():
    bot.cluster = cluster
    bot.db = bot.cluster['discord_bot']
    bot.collection = bot.db['prefixes']

    results = await bot.collection.insert_one({'_id': 0, 'prefix': '.'})
    print(results)

the password, username, and ip is correct

When i defined it was working fine but when i tried to use insert_one it threw this error

[proven arrow]

They are record objects you can access through index or name. See the example here, https://magicstack.github.io/asyncpg/current/api/index.html#record-objects

[craggy girder]

ye i got it

btw, to change the datatype of a column, i will do

conn.execute("""ALTER table_name ALTER column_name [SET DATA] TYPE text""")``` ?

like exactly like that

[proven arrow]

No

ALTER TABLE table_name ALTER COLUMN col_name TYPE TEXT;

[craggy girder]

oh

so they are two different statements?

[proven arrow]

no single, i was typing from mobile so hit enter

[craggy girder]

oh

ah i see alright

[vague badger]

yoo d to the b channel.... I never really paid much attention to databases in the past. I have been observing the data-science channel noticing a lot of users coding single use scripts within a comp dev environment as a tool for analytics. Which got me thinking -- why not just build tools into whatever interface/console/platform is used to access manage or maintain new and good ole fashion databases?

and after researching

I'm aware of Druid and meta modelling...

Druid is

anyone here have experience working with developing or implementing the following:

A library of similar metamodels has been called a Zoo of metamodels.

if you can try to get involved or help https://github.com/SMTorg/smt

[low iron]

I'm making a discord bot and I'm making a warn system, but I don't know how to organise the warns.
The bot stores settings about each server in a table and I want to make it store warns and information about the warns, but how can I do that for each server?

[vague badger]

accelerate its development adoption 110% pls. id like to in the near future develop a GUI for it with apps in the abstract over the meta

thanks in advance :👍

[vague badger]

for every user generated server? are the discord chat servers even "servers"? prob more like instances created on some cluster or container console and if you had access to whatever model or schema which manages discord user created objects with name/location info you could create a global discord emergency broadcast warning system. alternatively you could build it bottom up have an external model which is created when users install the bot but i would mask it and most definitely ensure transparency by fully disclosing it.

to clarify you the dev create one datastore/node/schema/model basically like a MLS of discord servers. MLS being a multiple listing service for real estate property here in the US not sure how real estate works in other countries. the individual listings would be auto-generated when users install bot on their servers.

the listings should be masked( in no particular order) and only use generic meta data (category, hype, country)

or if you are clever you can make a zillow for discord servers and let people buy and sell discord real estate property?

idk..

it wouldn't be a warning system tho... it be more like a broker bot and lucrative like domain parking which is annoying and not my style. id rather create value not gamble/invest....

[proven arrow]

I think you are just over complicating this for them?

[vague badger]

altho i could use some $$$ to get out of a bad situation pay ppl to help me chase big dreams worth chasing

still not my style tho

oh im always on the complex plane

[proven arrow]

Yeah a little too much 😐

[vague badger]

yup that is the usual response i get but speaking of which

[low iron]

Instances of discord.Guild, and only for the servers the bot is in

[vague badger]

right that makes more sense

probably just write a script that updates an external simple file list

[low iron]

Hmm

[vague badger]

or a google doc

[low iron]

someone in the dpy server told me to read about normalization and many to many databases but the entire day i've been confused about it

i'll just manage it normally

i'll store the server id in a column, info about the warn in other columns

different servers will be mixed up in one table, but that shouldn't be a problem right?

[vague badger]

not if its bottom up nah

[proven arrow]

yeah this is what you need

[vague badger]

but im not experienced in these matters so take my advices with a grain of salt

[low iron]

i don't really understand what to do, how should i organise it?

[low iron]

aight thanks

[proven arrow]

You would have 2 tables. Guild, and Warns, and a relationship between them. A relationship is just a way of linking tables through foreign keys.
In your case you would want a One-Many relationship between the two tables. For example, any one guild can have many warns.
Regarding your confusion about normalisation, try and have a read of this answer i gave someone else before. Maybe it helps make sense, https://discordapp.com/channels/267624335836053506/342318764227821568/778249491009765416

[vague badger]

yup

[low iron]

@proven arrow Thank you
I have one question, can I do make a one-many relationship between the Guild table and another table I would later make?

[vague badger]

i dont have experiences with guilds or warns either

[gentle mural]

is it normal that the log made by sqlite3 db is not showing ?

[vague badger]

is Warns an abstract for some fictional strategy game or something? warns sounds like warning alerts or notifications

[proven arrow]

Yes so to give you example, of how the relationship works look at these two tables.
Guilds Table

--------------------------------------------
id         | guild_name |  guild_id  | .....
--------------------------------------------
    1         Python        xxx
    2         Java          xxx

Warns Table

--------------------------------------------
warn_id | details | moderator | guild_id
--------------------------------------------
    1       xxx       432            1
    2       xxx       432            1
    3       xxx       123            2

WARNS table has a Foreign Key column called guild_id which points to a entry in the guilds table. @low iron

[low iron]

a warning system, for warning users if they break the rules, but not mute them or kick/ban them

[gentle mural]

hello ?

[vague badger]

ahhh

[low iron]

@proven arrow Ah, that makes sense
I see, thank you
I'll do that, luckily, I only have one table I use a little so I won't have to re-design the entire database

Thanks

[gentle mural]

hello ..

umm

...

[vague badger]

👋

[gentle mural]

is it normal that the log made by sqlite3 db is not showing ?

@vague badger

[vague badger]

no idea im a wannabe user experience designer i never paid attention to databases.

[gentle mural]

im following a tutorial

and'

the guy had a log

but i didn't

[vague badger]

MariaDB

[gentle mural]

i think it could be the version of sqlite but not sure

[gentle mural]

?

[vague badger]

might be

MariaDB was suggested by pilot guy

seems like a simple easy relational database to use

[gentle mural]

k

[vague badger]

im here to inquire what Druid and meta modelling is all about and how it differs from relationship or good old fashion db

[proven arrow]

i did? where xD

@gentle mural What kind of log?

[vague badger]

in the past in your repost im not sure if you did or someone else did but i assumed you either like or have experience in the db

I probably should ask the real question "speaking of which @proven arrow what is your preferred db software, interface and means of management?"

i agree with the idea of one single source of truth and to prevent redundancy. how would you apply that motto to something like the "hypercore protocol" tho?

idea sentiment work ethic motto

truth is relative for humans but....

when you say truth it is more "origin" not a factual or counter factual sense

signal over noise then right

im a broad spectrum generalist with an insatiable curiosity

so thats why the old db are being replaced because they allow multiple listings and redundant data

ok but what if your perception of tables and the relationship/reference links was more like....

origin log of data/info in the center. emergent reference layers stacked on top

idk im trying to understand meta modelling

[burnt turret]

what is this image? i dont really understand any of these words, i remember reading about van der waals forces in chemistry or something lmao

[vague badger]

ahh its a polar coordinate data visualization graph for "global brain rhythm database"

for language the word 'harm' lets see a polar graph of relationships

im still trying to understand meta analysis too. sorry for thinking outloud. im assuming those red blocks are manually entered polar opposites? idk its over my head ill have to read more about it...

Data federation is a type of data virtualization. Both data virtualization and data federation are techniques for integrating data that are designed to simplify access for front end applications. The term data federation is used for techniques that resemble virtual databases with strict data models.

[low iron]

i did that but i have one more question, how do i get a row from the Warns table where guild_id = xxx, since I only have the id from the first column as the foreign key, how do i get them by the guild's actual id?

[proven arrow]

I tend to use what is enough to solve the problems im working. Most of the work I do on a daily basis makes use of MySQL (or postgres sometimes), so thats what I mostly use. Postgres for my own stuff, and when I'm working on mobile apps my go to is Firebase.

@low iron You can use JOIN

[low iron]

Thank you

[sturdy blaze]

I'm not sure if this belongs in another channel or not, but I'm wathing https://www.youtube.com/watch?v=xaWlS9HtWYw&list=PL-osiE80TeTsKOdPrKeSOp4rN3mza8VHN and the guy is on a mac system. I'm on ubuntu 20.04; He's using PostgreSQL for this SQL Tutorial for Beginners, and I ran sudo apt-get install postgresql and went through the terminal for that, but I'm not sure what else I need to do past that. In the video, the guy unzips the file, and that's where I stopped because the file isn't in my /home/shellbyy/Downloads folder. I did the youtube link like that so that it didn't pop up the embed, and I tried to ask in 2 different discords linux and ubuntu but no one answered so I thought I would try here

[low iron]

pgAdmin keeps throwing this error when I try to make a column a foreign key though:
ERROR: there is no unique constraint matching given keys for referenced table "Guild"
the names don't match at all

I deleted and created the table again 3 times already

and gave it a different name each time

[proven arrow]

@low iron Your parent table needs a column with a unique constraint

[low iron]

oh what
thanks
hmm i made a foreign key before and it worked (but i had to delete the table because i couldn't edit the foreign key and created it again)

sorry for all the trouble lol

[proven arrow]

@sturdy blaze That should have installed your postgres installation, now you can just login and start using it

[sturdy blaze]

I created a new user Mekasu and I created a new DB named mekhit0124 but when I type sudo -u Mekasu psql I get this error

shellbyy@shellbyy-HP-Laptop-17-ca2xxx:~$ sudo -u postgres psql
psql (12.5 (Ubuntu 12.5-0ubuntu0.20.04.1))
Type "help" for help.

postgres=# \q
shellbyy@shellbyy-HP-Laptop-17-ca2xxx:~$ sudo -u postgres createuser --interactive
Enter name of role to add: Mekasu  
Shall the new role be a superuser? (y/n) y
shellbyy@shellbyy-HP-Laptop-17-ca2xxx:~$ sudo -u postgres createdb mekhit0124
shellbyy@shellbyy-HP-Laptop-17-ca2xxx:~$ sudo -u Mekasu psql
sudo: unknown user: Mekasu
sudo: unable to initialize policy plugin
shellbyy@shellbyy-HP-Laptop-17-ca2xxx:~$ sudo -u mekasu psql
sudo: unknown user: mekasu
sudo: unable to initialize policy plugin
shellbyy@shellbyy-HP-Laptop-17-ca2xxx:~$ sudo -i -u Mekasu psql
sudo: unknown user: Mekasu
sudo: unable to initialize policy plugin
shellbyy@shellbyy-HP-Laptop-17-ca2xxx:~$ 

and I tried to use the database name inplace of Mekasu, but it gave the same error

any suggestions?

@proven arrow

[proven arrow]

@sturdy blaze Try login to postgres first and then do it. Or try follow the commands in this link, https://wiki.postgresql.org/wiki/First_steps

[sturdy blaze]

I'm not root though

[vague badger]

@proven arrow Sorry for the longest question ever asked... The reason I'm here in this channel is to ask "Hypothetically speaking let's say I was in the field of ML specifically NLP to NLU pipelines. https://github.com/huggingface/datasets Those are the datasets I would be utilizing built with an Apache Arrow Table architecture to manage big data levels of batch text without going broke to aws or heroku. Here is a live viewer of the individual datasets https://huggingface.co/nlp/viewer/?dataset=aeslc These datasets are snapshots hosted online to be loaded into models for processing via command code within a software development environment. They are the tip of the iceberg from an emerging cascade of redundant-redundant data. Many more custom user generated datasets will be needed in the library and countless extrapolation datasets layered ontop for NLU to be generalized. Which is why I'm wondering. Instead of having to call a dataset like downloading and installing an application within a development environment wouldn't it be more practical to build more features into the live viewer instead? They are busy curating the datasets to improve the usefulness of their models. Shouldn't we help them out and build a more robust application of which the live viewer is just a part of? I got the abstract covered and a design in mind. I just need help getting hype on the dreams I'm chasing. Sorry for the long winded post ...

[proven arrow]

you just need sudo privs

[vague badger]

It's going to take a lot of redundant-redundant data

[sturdy blaze]

yea that link is very confusing. I'm just going to put a pin in databases for later

[proven arrow]

I think your issue is more to do with data science, or if your looking for a collaborator on that project im probably not the guy. I realised this some time ago, when i did my thesis which was actually in ML/NLP (and i think this is where you are headed), and realised that my interests were aligned elsewhere.

[sturdy blaze]

I honestly just need to learn about databses. I don't care which database I use. I just need to learn about them at some point. I'm on Ubuntu 20.04, and I use PyCharm Edu, and Visual Studio Code if any of that matters

[vague badger]

yup that's what I'm chasing

I want to build a meta container or something like that? but that's just half of the equation it's engineered data entry and systems engineering for meta functions.

the other half is entirely in the hands of users

user generated

[sturdy blaze]

ERROR 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 ''localhost'' IDENTIFIED BY 'password' at line 1``` I changed my password to the word `password` but what am I doing wrong?

[vague badger]

I'll hop out tho thanks for your insight @proven arrow

[proven arrow]

@sturdy blaze remove the space around the @

[sturdy blaze]

ok I did that, and now it's like

mysql> CREATE USER 'shellbyy'@'localhost' IDENTIFIED BY 'password'
    ->

does that mean it worked?

[proven arrow]

no

thats a new line for your query

add a ; when you done then press enter

[sturdy blaze]

is that where I would add

mysql> GRANT PRIVILEGE ON database.table TO 'shellbyy'@'localhost'

?

[proven arrow]

Yeah but PRIVILEGE needs to be replaced with a privelege

[sturdy blaze]

I accidentally closed my terminal, and now when I run mysql it tells me ERROR 1045 (28000): Access denied for user 'shellbyy'@'localhost' (using password: NO)

[proven arrow]

then login as the root user

[sturdy blaze]

I never rooted my computer, so how do I log in as the root user?

nvm. I'm stupid. sudo mysql

[proven arrow]

yeah that will login as root

[sturdy blaze]

ok so how do I delete the user I just created so I can start over?

[proven arrow]

root is just super user

[sturdy blaze]

I don't know what that means, but ok. So how do I delete that user I just created so I can start over?

[proven arrow]

You dont need to start over, just grant the privileges

[sturdy blaze]

ok so I ran GRANT ALL ON test.table TO 'shellbyy'@'localhost'; and it spat back ERROR 1410 (42000): You are not allowed to create a user with GRANT sooooo like how am I creating a user??

or either that means that the user I tried to create never got created

[proven arrow]

Yeah

[sturdy blaze]

ok there we go. I did the create user command that i was trying to do earlier, and I did the grant all on test.table to 'shellbyy'@'localhost'; and bot entries gave me
Query OK, 0 rows affected (0.08 sec) so Im' guessing that's a good thing?

[proven arrow]

Yes

[sturdy blaze]

@proven arrow how do I get out of this screen. every button I push isn't doing anything

or not out of the screen, but back to where I can enter commands?

this screen is because I typed systemctl status mysql.service

[proven arrow]

i think its q

[sturdy blaze]

wow i feel stupid

ty

[cold quail]

@sturdy blaze Controll a+d

[torn sphinx]

I luv DBs

[silent spruce]

Dragon Ball??

😄

[junior hinge]

Curious, has anyone tried ibis-framework or if you have decided against using it, can you share why?

[torn sphinx]

i have a database and if i load every row from the database and make every row a python object, memory efficent
i mean i know it is not the best idea but will a computer handle that

[sturdy blaze]

anyone have any references on making embeds with flippable pages? like page 1/4 click the right emoji to go up in pages, and the left emoji to go down in pages?

[silk vortex]

#discord-bots

[sturdy blaze]

ok

[low iron]

I can't make my postgresql table primary key go back to 1
I cleared the table and I made it default to 1 but it default to 2 everytime

SELECT setval('guild_id_seq', 1)

my code

how can i make it go back to 1

[sturdy blaze]

if I have an extremely long message that I want to put in like this:

embed1 = discord.Embed(name="question", value="I want to make an extremely long embed that is over 20k characters")
``` would I just use multiple `value=""` or would I use description?

[low iron]

#discord-bots please
Embeds don't have name and value, only title and description

[sturdy blaze]

oops sorry

[regal blade]

is there something like https://www.npmjs.com/package/quick.db in python AND that works with async?

[silk vortex]

        connection = await asyncpg.connect(user='postgres', password='pass',
                                    database='\"exo\"', host='127.0.0.1')
```This is the connection that I'm trying to run on my Ubuntu 18.04 VPS

[regal blade]

yeah but with that you need to actually work with the sql right? with qdb its just its own syntax

[silk vortex]

I created the database with quotes accidentally

@torn sphinx I just tried doing ALTER DATABASE "exo" RENAME TO exo; in psql but it says Error: database "exo" already exist even though that's not what I'm asking it to do

drop table? It's a database tho

so drop database?

k

@torn sphinx how do I create a new database on my root user?

well yes but like how do I go into psql on my root user?

[silk vortex]

oh ok

[silk vortex]

https://docs.boundlessgeo.com/suite/1.1.1/dataadmin/pgGettingStarted/firstconnect.html#connecting-to-postgresql-on-linux-for-the-first-time

I'm trying to follow this tutorial but where it says "Scroll down to the line that describes local socket connections. It may look like this:" (https://docs.boundlessgeo.com/suite/1.1.1/dataadmin/pgGettingStarted/firstconnect.html#allowing-local-connections) I can't find
local all all peer in my pg_hba.conf file.

[fluid scaffold]

Hi I wanna update the status inside the excel but seems like this code doesn't work I dunno why,

[torn sphinx]

@fluid scaffold what font is that they use it in chinese products

[fluid scaffold]

Ahh sorry about that it will search from the third row

[fluid scaffold]

after that will update the status there

[torn sphinx]

Huh

The font u use while coding

[silk vortex]

Does anyone know how I can login to one of my postgresql users called exo_user in linux? I want to login to exo_user and then create a database inside that user

[craggy girder]

when you use "INSERT INTO table(column1, column2) VALUES(2, 'hi')", how is the row specified? or does it inserts into every row? if yes, then how do i specify which row to insert in?

[proven arrow]

Each insert will go into a new row

[craggy girder]

@torn sphinx there are 5 columns

no i mean, is there way to specify which row the insert will go to

wait nvm

yeah, i was mixing columns with rows my bad

btw, do you have to use conn.commit in asyncpg too? i mean it worked before without a commit but now i feel like the values arent getting commited into the db for one command

here is the code https://paste.pythondiscord.com/ikewuyolic.py

wait i think i know the problem

yes i fixed it

i needed to use UPDATE and not INSERT

[proven arrow]

Not in asyncpg

It has autocommit by default, and it doesn't implement the dB api

[burnt turret]

https://magicstack.github.io/asyncpg/current/faq.html#does-asyncpg-support-db-api

right someone was quicker oof

[proven arrow]

I think the only time you can commit is when using transactions out of a with block

[brazen charm]

If you're building a wheel then that's pretty expectes

Expected*

if youre installing it then you can expect less time cuz it's already compiled but it's still. A big lib that takes time to install if your pc is weak

[eternal raptor]

Hi guys!
I have a problem.
I have a data in a table, but bot has an algoritm:
Check COUNT of rows in a column
Has a queue
If queue > count of rows in a column:
bot reset queue to 1
if queue <= count of rows in a column:
bot send a message

But my data have an ID.

if queue <= count of rows in a column:
bot send a message
Bot send a message, which has an ID = queue
but example:
I added 4 data:
1 bla bla bla
2 ci ci ci
3 el el el
4 du du du

I deleted 3th data:
1 bla bla bla
2 ci ci ci
4 du du du

Bot cannot print fourth data, because count of rows is 4, but queue can be max 3.

My database: PostgreSQL,
programming language: Python

It is possible to set the database in some way so that even if some data is deleted, the bot will number the ID again and it will not be 1,2,4 but 1,2,3?

code snippet:

    @tasks.loop(seconds=10.0)
    async def jobs(self):
        for guild in self.client.guilds:
            chrandom = random.choice(self.client.guilds)
            gsend = self.client.get_guild(chrandom.id)
            if '🥝ninjamanager' in [c.name for c in gsend.text_channels]:
                sql8 = 'SELECT "place_in_line" FROM queue'
                query6 = await self.client.conn.fetchval(sql8)
                sql3 = 'SELECT "owner_id" FROM ads WHERE "ID" = $1'
                sql5 = 'SELECT "description" FROM ads WHERE "ID" = $1'
                sql7 = 'SELECT COUNT("description") FROM ads'
                query1 = await self.client.conn.fetchval(sql3, query6)
                query3 = await self.client.conn.fetchval(sql5, query6)
                query5 = await self.client.conn.fetchval(sql7)
            if ...:
            #code...
            elif query6 <= query5:
            # code...

ok, but I have other problem...

ooo, good idea

I will replace, but Is it posibble

It is possible to set the database in some way so that even if some data is deleted, the bot will number the ID again and it will not be 1,2,4 but 1,2,3?
database: PostgreSQL

:/

[proven arrow]

@eternal raptor If i understand what you mean then what you would want is to use a window function with ROW_NUMBER()

[eternal raptor]

wait

[proven arrow]

You can partition by the id column

https://www.postgresqltutorial.com/postgresql-window-function/

[eternal raptor]

I would want is to sorting id, if bot delete a row.
example: we have 5 data
1: kasdakmda
2: adsmakda
3: kmgfdgnd
4: ansdsamdaks
5: sadas

Bot delete 2nd data, so now table is looking:
1: kasdakmda
3: kmgfdgnd
4: ansdsamdaks
5: sadas

But I would like to bot will numbering columns again or PostgreSQL will numbering columns again
so effect should be this:
1: kaskdakmda
2: kmgfdgnd
3: ansdsamdaks
4: sadas

[proven arrow]

Yeah so what i sent earlier will work, thats what you need

Actually ignore what i said about the partition, you would need to order by the ID column instead

[eternal raptor]

Ok, I will try. If It give a good effect, I will tell to you.

[proven arrow]

import sqlite3

with sqlite3.connect(":memory:") as db:
    db.execute("CREATE TABLE data (id INTEGER PRIMARY KEY, value TEXT)")
    db.execute("INSERT into data values (1, 'a')")
    db.execute("INSERT into data values (2, 'b')")
    db.execute("INSERT into data values (3, 'c')")
    db.execute("INSERT into data values (4, 'd')")

    db.execute("DELETE FROM data WHERE id = 3")

curs = db.execute("SELECT value, ROW_NUMBER() OVER() FROM data")
rows = curs.fetchall()

print(rows)

For example, like this. This is sqlite but syntax will be same for postgres too.

See the output in #bot-commands https://discordapp.com/channels/267624335836053506/267659945086812160/782187015051542569 @eternal raptor

[eternal raptor]

Oooo 😄

@proven arrow thank you so much ❤️ I will try this, I will tell to you later

OVER(ORDER BY id) What does It do?

[proven arrow]

For demonstration.

[eternal raptor]

i will try this in two hours, because i must I have to go, bye and have a nice day

OVER(ORDER BY id) What does It do?

[proven arrow]

Inside the Over() Is how we decide what rows to apply the function to. In your case you can leave out the order by id as you dont want to resort the rows.

so just ROW_NUMBER() OVER() will work

[torn sphinx]

if anyone needs a db and doesn't want to learn SQL, use my db: https://github.com/HippyHippo/EasyGoDB

idc

[glacial osprey]

Can someone tell me the basics of how to make a database

I tried using variables inside tuples inside dictionaries

But that didnt work right

[proven arrow]

@glacial osprey you need to be more specific

[glacial osprey]

Howso?

[proven arrow]

What are you trying to do? What you are storing etc.

[glacial osprey]

I am storing multiple strings and numbers in a spot and then pulling that

Information about people...

As a project for my principal

How do i store information inside of variables, inside of variables, inside of another variable, inside of a disctionary

Something like that

Idk

I just dont know where to start

Tell me the basics

[proven arrow]

What kind of information are you storing? How often will you read/write this data?

[glacial osprey]

Every week or so

To write

[proven arrow]

I know you said information about people but what exactly?

[glacial osprey]

No

Like

Names, phone numbers, room numbers, grades, student id, classes, etc.

[proven arrow]

Ok so that data is relational, and a RDBMS is what you need.

[glacial osprey]

Huh

?

[proven arrow]

It's just short for a database system.

[glacial osprey]

Oh ok

Do i have to install it?

It has to be on python tho

Lol

[proven arrow]

So there are different database systems, some are server based and some file based. The easiest is SQLite which is a file based and it's libraries come built in with python.

[glacial osprey]

I will be editing the database every week or so but he will be reading it everyday

Ok

So i need to make a seperate file for each group of entries?

Like by grade? Or class?

What is the library called? RDBMS?

from RDBMS import *

[proven arrow]

Look it up, and the python module for it is called sqlite3. It uses the SQL language to allow you to interact with the database and manipulate/query it.

And no, so what you do is make a single file (which will be the database). The database can hold many tables. Each table can have many rows. You would first need to think on what data needs to be stored exactly and then design your tables around this. Not all data should be in a single table. Use multiple tables, for example one table to store class data, another for attendance, another for user personal details etc. This is called normalisation and you can look it up (but essentially don't store everything in a single place, instead split it up like you do with classes in programming )

[glacial osprey]

https://tenor.com/view/justkeepswimming-nemo-dory-gif-3894023

Just keep typing, just keep typing

Ok,

Just on moment

Ohhhhhhhh

Okkk

That makes a lot help

Oh ok

Thak you @proven arrow

[mental coral]

How do i see a table schema after creating a table with in postgresql

CREATE TABLE testtable(id INTERGER PRIMARY KEY);

Im trying to create a database for my discord bot, but i think i don't know what im doing

[proven arrow]

@mental coral if you didn't create any schema then the public schema is default one

[tepid crag]

Hey there, does anyone know that what should I pass in my password section in URL for postgresql? They didn't asked me for any password while installing it on my VPS. OS - Ubantu

[proven arrow]

@tepid crag you can change it by logging in as the postgres user and then type \password postgres
But it's better if you make a new user with set privileges and use that in your app

[tepid crag]

I see..let me try both. Thanks :)

I changed password...i tried adding user but it doesn't work for me.. I don't know why.

[mental coral]

@proven arrow ooh, thanks. I have 1 more problem while inserting into a table

Select * FROM people
insert into datas.people(id,roles,name) values (1,"test", "Role");

somehow, this doesnt working

[tepid crag]

I don't know why it's not letting me in even though i have added one user..it's always saying unknown user

[torn sphinx]

cursor.execute("INSERT INTO User (expire) VALUES (?) WHERE username = ?;", (time,username,))

can someone help me with what im attempting to do?
i am trying to find a user, then change the expiration date on the account

[torn sphinx]

did you commit?

[mental coral]

I don't know

I think i need to save, and then try it again?

@torn sphinx It said syntax wrong in or near "into"

[torn sphinx]

capitals

"INSERT INTO not INSERT into

@mental coral

[mental coral]

but then "insert into" works too?

select * FROM people
INSERT INTO people(id,roles,name) values (1,"test", "Role")

Still the same error

[torn sphinx]

what sql variant is it

[mental coral]

its eh, postgresql

[torn sphinx]

i use sqlite

oof

[mental coral]

oof

[torn sphinx]

add a .

,

i mean

INSERT INTO people(id,roles,name,) values (1,"test", "Role",)

i have no clue why this works for me but it does

[mental coral]

hm hecc

idk whats wrong, in sqlite dialect is fine, but in postgresql

it didnt work

[torn sphinx]

cursor.execute("INSERT INTO User (username, password, expire) VALUES (?, ?, ?);", (username, token, 69,))

try to replecate something like that (its how my login creation from admin panel works)

[proven arrow]

You are confusing postgres user with the linux user

You created a postgres user, and not a user for your system

[torn sphinx]

cursor.execute("INSERT INTO User (expire) VALUES (?) WHERE username = ?;", (time,username,))

can someone help me with what im attempting to do?
i am trying to find a user, then change the expiration date on the account
any ideas @proven arrow

[mental coral]

Lufthansa, do you have any idea for mines too?

[proven arrow]

@torn sphinx Insert statement doesnt take a where clause

your inserting a new row

[torn sphinx]

how can i do what im attempting

[proven arrow]

You want to update the record?

[torn sphinx]

yes

[proven arrow]

So use UPDATE

[torn sphinx]

match a record to a username, then update the expire

[proven arrow]

https://www.w3schools.com/sql/sql_update.asp

[torn sphinx]

but its a per user lookup, not the entire table

i need to update a row

matching the username

[tepid crag]

😯...how to fix that thing? :'(
I thought you meant new user for psql

[proven arrow]

read the link first, then if you have questions or are stuck ask

[torn sphinx]

i have read the exact link

prior to asking

xd

no info on what im attempting

[proven arrow]

Well then show what you attempted

[torn sphinx]

only info regradding updating the entire table

cursor.execute("INSERT INTO User (expire) VALUES (?) WHERE username = ?;", (time,username,))

my best attempt

[proven arrow]

Thats insert, again read the link, its highlighted in yellow as well on how you dont update the entire table.

[tepid crag]

Storing data in the default psql account isn't a good option?

[proven arrow]

It has super user priveleges so its better to just make another user which has what they need

[tepid crag]

I am the only user of it

No one else...😀

[torn sphinx]

UPDATE User SET expire = ? WHERE username = ?

[mental coral]

select * FROM people
INSERT INTO people (id, roles, "name")
    VALUES (1, 'Manager', 'Somename');

There is something wrong, i can't insert those values in the "people" table

[proven arrow]

psql -U <role_name> <db_name> is what you would want to connect. You can still just login as the postgres user, do everything with that, and then in your application use the other user. @tepid crag

[proven arrow]

Are you executing all that as a single statement?

[mental coral]

Yes

[proven arrow]

Why do you need that? They should be two seperate statements.

[mental coral]

Oh

I didn't know that

select * FROM people;
INSERT INTO people (id, roles, "name")
    VALUES (1, 'Manager', 'Somename');

will this do it?

[proven arrow]

SELECT is for getting data, INSERT is for inserting data.

That would work yes but im just saying you dont need to do select * FROM people; in order to insert.

[mental coral]

Oh i see, thank you! Its fixed!!

[craggy girder]

uh can anyone help me with asyncpg here? so basically i have a timestamp column and asyncpg handles datetime.datetime objects for that, this was all working for naive datetime objects, but now i am hosting my bot and the host region is est so i need to change the time, but that makes it an aware datetime object, which gives me an error when i try to store it, does anyone know a fix for this?

this is the error py asyncpg.exceptions.DataError: invalid input for query argument $1: datetime.datetime(2020, 11, 28, 11, 23, ... (can't subtract offset-naive and offset-aware datetimes)

now = datetime.now(tz=pytz.timezone('est')) this is the value i am trying to store

[proven arrow]

@craggy girder use timestampz as col type, since database is not not aware of timezone and python is

[craggy girder]

yep figured it out

timestamptz works

[fallen vault]

How do I use a for loop to enter data into a database from a csv.

[hard jolt]

PyInstaller can't build a 'Firebase Firestore python file'
Did anyone know how to solve it?

[proven arrow]

@fallen vault It would be better if you load those values into something like a list of tuples, and then you can insert them as a batch

[fallen vault]

Okay. How would I split each row into a tuple.

[proven arrow]

Have you tried looking it up? First result shows this. https://stackoverflow.com/questions/24662571/python-import-csv-to-list

[toxic jay]

Why is mongodb taking forever

[brazen charm]

cuz its mongo

happy webscale™️

[earnest parcel]

Lol

[tacit plank]

why do I suddenly get this sqlalchemy.exc.IntegrityError

The program run okay before, but when I reload it, it occurs

and why do db.session.commit() caused this error

[torn sphinx]

Hello!
Does anyone have any example database and QT program together?

[regal moss]

skip IDs, they are auto incremented anyway

Karma

[eternal raptor]

@proven arrow sorry, but effect this same...
my code (not full):

                sql8 = 'SELECT "place_in_line" FROM queue'
                query6 = await self.client.conn.fetchval(sql8)
                sql3 = 'SELECT "owner_id", ROW_NUMBER() OVER(ORDER BY "ID") FROM ads WHERE "ID" = $1'
                sql5 = 'SELECT "description", ROW_NUMBER() OVER(ORDER BY "ID") FROM ads WHERE "ID" = $1'
                sql7 = 'SELECT COUNT("description") FROM ads'
                query1 = await self.client.conn.fetchval(sql3, query6)
                query3 = await self.client.conn.fetchval(sql5, query6)
                query5 = await self.client.conn.fetchval(sql7)

Bot doesn't want to send an ad with id 5

I think that I do wrong.

but what I do wrong... :/

[fallen vault]

No, because I had no idea where to even start.

[silk vortex]

Does anyone know if there's a way I can connect to my database that's on my vps with popsql?

[torn sphinx]

im having an issue with mongodb, when i change the conf to authorization: 'enabled' i can do sudo mongod and it works fine but if i do sudo service mongod start i get this error:

[proven arrow]

Because your doing fetchval(), which only gets you a single value

And you don't need the order by id

[eternal raptor]

hm... so... fetchall?

or fetchmany?

[radiant elbow]

You need single quotes, not double.

In SQL, only single quotes are for string literals. Double quotes are for escaping.

[eternal raptor]

@proven arrow when i was trying fetchall, bot send me: pool object has no fetchall

sql8 = 'SELECT "place_in_line" FROM queue'
                query6 = await self.client.conn.fetchval(sql8)
                sql3 = 'SELECT "owner_id", ROW_NUMBER() OVER(ORDER BY "ID") FROM ads WHERE "ID" = $1'
                sql5 = 'SELECT "description", ROW_NUMBER() OVER(ORDER BY "ID") FROM ads WHERE "ID" = $1'
                sql7 = 'SELECT COUNT("description") FROM ads'
                query1 = await self.client.conn.fetchall(sql3, query6)
                query3 = await self.client.conn.fetchall(sql5, query6)

[proven arrow]

https://magicstack.github.io/asyncpg/current/api/index.html#asyncpg.connection.Connection.fetch

[eternal raptor]

aaa

it means, I must use fetch?

[proven arrow]

Yes

[eternal raptor]

effect this same and

😦

            if ':kiwi:ninjamanager' in [c.name for c in gsend.text_channels]:
                sql8 = 'SELECT "place_in_line" FROM queue'
                query6 = await self.client.conn.fetchval(sql8)
                sql3 = 'SELECT "owner_id", ROW_NUMBER() OVER(ORDER BY "ID") FROM ads WHERE "ID" = $1'
                sql5 = 'SELECT "description", ROW_NUMBER() OVER(ORDER BY "ID") FROM ads WHERE "ID" = $1'
                sql7 = 'SELECT COUNT("description") FROM ads'
                query1 = await self.client.conn.fetch(sql3, query6)
                query3 = await self.client.conn.fetch(sql5, query6)
                query5 = await self.client.conn.fetchval(sql7)

[proven arrow]

I dont see the problem? Its doing what you tell it to do

[eternal raptor]

still bot can't send ad with id 5

[proven arrow]

You need to be a little bit more clear, because id 5 and stuff has no meaning to me. I have absolutely no idea whats going on.

[eternal raptor]

effect this same, which was yeterday

i have in a table 3 data, id: 1, 3, 5, bot doesnt't want to send data with id 5

bot commonly doesn't print data with id 5

wait

bot doesn't want send data with id 5

bot ends queue on data with id 3, because in a table are 3 rows. so if id > count of rows, bot resets queue to 1

but I would like to bot send a data, where id is 1,2,3 (first column)

before owner_id

this column noname

@proven arrow

[proven arrow]

which is why you have the ROW_NUMBER() OVER() so dont compare with the ID column and look at the value of this new column

You put it there for a reason, so use it then

[eternal raptor]

ok

hm

now I think so works, but...
Oferta właściciela: [<Record owner_id=22332423 row_number=1>, <Record owner_id=3478653746353 row_number=2>, <Record owner_id=318824628439089152 row_number=3>]

[<Record description='asdahd hsaudha gdDSFJSHDFfs' row_number=1>, <Record description='ugnghnfasdahd ASDADSA gdDSFJadasHDFfs' row_number=2>, <Record description='hello everybody' row_number=3>]

wtf? why [<>, <>, <>] ?? how can i delete this, keep only data?

[proven arrow]

Its a list of objects, shows you here how to access them https://magicstack.github.io/asyncpg/current/api/index.html#record-objects

[eternal raptor]

ooo

thank you

but bot sends all rows...

:/

I need with ID, because without this, bot will send over full time only first data

but when i set WHERE "ID" = $1, bot doesn't send a data with ID 5, because max ID to print is 3

[eternal raptor]

@proven arrow

[eternal raptor]

@proven arrow

Hi, anyone know how to get noname column, where data are: 1,2,3?

                sql3 = 'SELECT "owner_id", ROW_NUMBER() OVER(ORDER BY "ID") FROM ads WHERE "ID" = $1'
                sql5 = 'SELECT "description", ROW_NUMBER() OVER(ORDER BY "ID") FROM ads WHERE "ID" = $1'
                sql7 = 'SELECT COUNT("description") FROM ads'
                query1 = await self.client.conn.fetch(sql3, query6)
                query3 = await self.client.conn.fetch(sql5, query6)
                query5 = await self.client.conn.fetchval(sql7)

I have this (sql3, sql5), but these queries get column ID, not this noname column

[proven arrow]

Thats just row number, its not an actual column for your table.

[eternal raptor]

I know, but it is possible?

[proven arrow]

Yes with ROW_NUMBER() OVER()

[eternal raptor]

.

[proven arrow]

SELECT ROW_NUMBER() OVER() AS id, owner_id, description, guild_id FROM ads;
Gives you the data in that form

[eternal raptor]

But next

I would like to

get a data with row_number = example 3

after SELECT

because how i do

[proven arrow]

Then the row number will always be 1.

[eternal raptor]

i know

[proven arrow]

Just like if you did the same query in dbeaver, it would give you 1

[eternal raptor]

but i would like to first select row_number() over() as id, owner_id, description, guild_id from ads and next from these showed, get a data with number 3

example

[eternal raptor]

but if i do this and fetch, bot always shows all

if i add where id = $1
bot doesn't show a data more than 1 id

is it possible? sorry, but i'm from Poland and my English... is on A2 level

[civic trail]

@client.command()
@commands.cooldown(1, 3600, commands.BucketType.user)
async def crime(ctx):
    money = random.randrange(-150, 400)
    
    cursor = db.cursor()
    cursor.execute(f"SELECT user_id FROM economy WHERE guild_id = {ctx.guild.id}")
    result = cursor.fetchone()
    if result is None:
        await ctx.send("Oh no! You do not have an account! You can create an account by typing command `>open_account`.")
    else:
        if money == 0:
            await ctx.send("Dang... The person you robbed had no money on them")
        elif money <= 0:
            await ctx.send(f"You were caught!! You were fined ${money}.")
        elif money > 5:
            await ctx.send(f"Was it all worth it? You made ${money}.")
        elif money > 15:
            await ctx.send(f"I mean at least you stole ${money}.")
        elif money > 30:
            await ctx.send(f"You stole a phone and made ${money} off of it.")
        elif money > 45:
            await ctx.send(f"You stole ${money}. Nice!")
        elif money > 60:
            await ctx.send(f"You stole everything you could grab in a car. You sold it all for ${money}.")
        elif money > 75:
            await ctx.send(f"You broke into a house and stole a tv! You made ${money} off of it.")
        elif money > 100:
            await ctx.send(f"You stole a laptop and sold it for ${money}.")
        elif money > 150:
            await ctx.send(f"You robbed a dude for his new and expensive shoes. You made ${money}.")
        elif money > 200:
            await ctx.send(f"You pushed a kid off his bike. You were able to sell it for ${money}.")
        elif money > 300:
            await ctx.send(f"You stole a car and scraped it for ${money}.")

    sql = ("UPDATE economy SET wallet = ? WHERE user_id = ? AND guild_id = ?")
    var = (money, ctx.author.id, ctx.guild.id)
    cursor.execute(sql, var)
    db.commit()
    cursor.close()
    db.close()```

whats wrong with my code

[eternal raptor]

maybe give an error

[civic trail]

PS C:\Users\likho\Videos\Coding> & C:/Users/likho/AppData/Local/Programs/Python/Python38/python.exe c:/Users/likho/Videos/Coding/izlam.py
Im Ready
Ignoring exception in command crime:
Traceback (most recent call last):
File "C:\Users\likho\AppData\Local\Programs\Python\Python38\lib\site-packages\discord\ext\commands\core.py", line 85, in wrapped
ret = await coro(*args, **kwargs)
File "c:/Users/likho/Videos/Coding/izlam.py", line 131, in crime
cursor.execute(f"SELECT user_id FROM economy WHERE guild_id = {ctx.guild.id}")
sqlite3.OperationalError: no such table: economy

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

Traceback (most recent call last):
File "C:\Users\likho\AppData\Local\Programs\Python\Python38\lib\site-packages\discord\ext\commands\bot.py", line 903, in invoke
await ctx.command.invoke(ctx)
File "C:\Users\likho\AppData\Local\Programs\Python\Python38\lib\site-packages\discord\ext\commands\core.py", line 859, in invoke
await injected(*ctx.args, **ctx.kwargs)
File "C:\Users\likho\AppData\Local\Programs\Python\Python38\lib\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: no such table: economy

thats the error

[eternal raptor]

OperationalError: no such table: economy

[brazen charm]

low key that is such a in-efficient method of doing multiple responses

[civic trail]

ik

yes but it says in line 94

in line 94 i dont see anything saying "economy"

[brazen charm]

yh, you apparently dont have a table called enconomy

[eternal raptor]

OperationalError: no such table: economy

[brazen charm]

cursor.execute(f"SELECT user_id FROM economy WHERE guild_id = {ctx.guild.id}")

[eternal raptor]

You don't have a table

[brazen charm]

would advise learning how to read stacktraces

really important thing to get good at

[eternal raptor]

check, maybe you have table other named

other

[civic trail]

oh yeah my table is called databases

i forgot

[eternal raptor]

cursor.execute(f"SELECT user_id FROM economy WHERE guild_id = ?", (ctx.guild.id))

[civic trail]

my old one was econmoy

i mean bankaccounts.db

[eternal raptor]

cursor.execute(f"SELECT user_id FROM economy WHERE guild_id = ?", (ctx.guild.id)) more effiency method

[civic trail]

is that bankaccounts.db a table?

[eternal raptor]

yup

it;s bankaccounts database, not table

[civic trail]

SQL.execute(f'select balance from Accounts where user_id="{USER_ID}"')
result_userbal = SQL.fetchone()

is that good

out of not using cursor.execute

[proven arrow]

SELECT row_num, owner_id, description, guild_id
FROM (
   SELECT *, ROW_NUMBER() OVER() AS row_num 
   FROM ads
) result
WHERE id = $1```
Try that then

[eternal raptor]

row_num? what is that?

[proven arrow]

the position of that row

[eternal raptor]

aaaa

ok

[civic trail]

sql = ("UPDATE Accounts SET wallet = ? WHERE user_id = ? AND guild_id = ?")

is this gonna be an error?

do i have to do {USER_ID}

[proven arrow]

The sql is fine

[eternal raptor]

Lufthansa-Pilot, so row_num write in " " ?

[civic trail]

SQL.execute('update Accounts set balance = balance - ? where user_id = ?', (amount, USER_ID))

thats working but the other one isnt

[proven arrow]

no because it doesnt exist in your table, its a column we make

[eternal raptor]

sorry if I use not correct words, but my English is on A2 level.

[eternal raptor]

ok

[civic trail]

@client.command()
@commands.cooldown(1, 3600, commands.BucketType.user)
async def crime(ctx):
money = random.randrange(-150, 400)

db = sqlite3.connect('main.sqlite')
cursor = db.cursor()
cursor.execute(f"SELECT user_id FROM economy WHERE guild_id = {ctx.guild.id}")
result = cursor.fetchone()
if result is None:
    await ctx.send("Oh no! You do not have an account! You can create an account by typing command `>open_account`.")
else:
    if money == 0:
        await ctx.send("Dang... The person you robbed had no money on them")
    elif money <= 0:
        await ctx.send(f"You were caught!! You were fined ${money}.")
    elif money > 5:
        await ctx.send(f"Was it all worth it? You made ${money}.")
    elif money > 15:
        await ctx.send(f"I mean at least you stole ${money}.")
    elif money > 30:
        await ctx.send(f"You stole a phone and made ${money} off of it.")
    elif money > 45:
        await ctx.send(f"You stole ${money}. Nice!")
    elif money > 60:
        await ctx.send(f"You stole everything you could grab in a car. You sold it all for ${money}.")
    elif money > 75:
        await ctx.send(f"You broke into a house and stole a tv! You made ${money} off of it.")
    elif money > 100:
        await ctx.send(f"You stole a laptop and sold it for ${money}.")
    elif money > 150:
        await ctx.send(f"You robbed a dude for his new and expensive shoes. You made ${money}.")
    elif money > 200:
        await ctx.send(f"You pushed a kid off his bike. You were able to sell it for ${money}.")
    elif money > 300:
        await ctx.send(f"You stole a car and scraped it for ${money}.")

sql = ("UPDATE economy SET wallet = ? WHERE user_id = ? AND guild_id = ?")
var = (money, ctx.author.id, ctx.guild.id)
cursor.execute(sql, var)
db.commit()

does anybody see anything wrong?

[eternal raptor]

nubby, write in code blocks

and give an error

[civic trail]

@client.command()
@commands.cooldown(1, 3600, commands.BucketType.user)
async def crime(ctx):
    money = random.randrange(-150, 400)

    db = sqlite3.connect('main.sqlite')
    cursor = db.cursor()
    cursor.execute(f"SELECT user_id FROM economy WHERE guild_id = {ctx.guild.id}")
    result = cursor.fetchone()
    if result is None:
        await ctx.send("Oh no! You do not have an account! You can create an account by typing command `>open_account`.")
    else:
        if money == 0:
            await ctx.send("Dang... The person you robbed had no money on them")
        elif money <= 0:
            await ctx.send(f"You were caught!! You were fined ${money}.")
        elif money > 5:
            await ctx.send(f"Was it all worth it? You made ${money}.")
        elif money > 15:
            await ctx.send(f"I mean at least you stole ${money}.")
        elif money > 30:
            await ctx.send(f"You stole a phone and made ${money} off of it.")
        elif money > 45:
            await ctx.send(f"You stole ${money}. Nice!")
        elif money > 60:
            await ctx.send(f"You stole everything you could grab in a car. You sold it all for ${money}.")
        elif money > 75:
            await ctx.send(f"You broke into a house and stole a tv! You made ${money} off of it.")
        elif money > 100:
            await ctx.send(f"You stole a laptop and sold it for ${money}.")
        elif money > 150:
            await ctx.send(f"You robbed a dude for his new and expensive shoes. You made ${money}.")
        elif money > 200:
            await ctx.send(f"You pushed a kid off his bike. You were able to sell it for ${money}.")
        elif money > 300:
            await ctx.send(f"You stole a car and scraped it for ${money}.")

    sql = ("UPDATE economy SET wallet = ? WHERE user_id = ? AND guild_id = ?")
    var = (money, ctx.author.id, ctx.guild.id)
    cursor.execute(sql, var)
    db.commit()```

ok wait 1 min

[eternal raptor]

                sql8 = 'SELECT "place_in_line" FROM queue'
                query6 = await self.client.conn.fetchval(sql8)
                sql3 = 'SELECT "owner_id", row_num FROM (SELECT *, ROW_NUMBER() OVER() AS row_num FROM ads) result WHERE row_num = $1' # owner_id
                sql5 = 'SELECT "description", row_num FROM (SELECT *, ROW_NUMBER() OVER() AS row_num FROM ads) result WHERE row_num = $1' # description
                sql7 = 'SELECT COUNT("description") FROM ads'
                query1 = await self.client.conn.fetchval(sql3, query6)
                query3 = await self.client.conn.fetchval(sql5, query6)
                query5 = await self.client.conn.fetchval(sql7)

I wrote this. Now works ❤️ thank you so much ❤️

[civic trail]

discord.ext.commands.errors.CommandInvokeError: Command raised an exception: OperationalError: no such table: Accounts

it says that the error

[eternal raptor]

table named Accounts doesn't exists

check

in database

what is its name.

give an full code

[civic trail]

its called Accounts lol

[eternal raptor]

so, give an full error

[civic trail]

i got it to work

now i got another error

var = (money, ctx.author.id, ctx.guild.id)

error: File "c:/Users/likho/Videos/Coding/izlam.py", line 162
var = (money, ctx.author.id, ctx.guild.id)
^
SyntaxError: invalid syntax

[weak tinsel]

can i somehow get the password of MySQL in python through some module?

its for a program that checks for the db password and then allows user input

[civic trail]

did someone ever code a discord economy bot using sqlite3?

[modern parcel]

(
    ISBN char(10) NOT NULL,
    Title varchar(200) NOT NULL,
    YearPublish char(4) NOT NULL,
    PublisherID smallint NULL,
    BookCat char(2) NULL,
 CONSTRAINT [PK_Book] PRIMARY KEY NONCLUSTERED 
(```
what does it mean if i make the primary key nonclustered?

[brazen lodge]

def query1(sql,var,var2=None):
    with contextlib.closing(sqlite3.connect('data.db')) as conn: # auto-closes
        with conn: # auto-commits
            with contextlib.closing(conn.cursor()) as cursor: # auto-closes
                cursor.execute(sql,var)
                return cursor.fetchall()
                


def get_prefix(bot, message):

    if message.guild is None:
        return commands.when_mentioned_or('!t ')(bot, message)
    else:
        guildID = message.guild.id
        prefix = query1("Select prefix from prefix_data where guild_id = ?",(message.guild.id,))
        print(prefix)
        if len(prefix)==0:
            return commands.when_mentioned_or('!t ')(bot, message)
        else:
            prefix_ = prefix
            return commands.when_mentioned_or("!t ")(bot, message)

bot = commands.Bot(command_prefix=(get_prefix))```

here print(prefix) returns an empty list but it exists in db
am i doing smthn wrong in fetchin the data?
and print(cursor.fetchall()) returns empty list

ping me

[haughty spruce]

hello

[torn sphinx]

@brazen lodge

[fringe tapir]

hi

Step 1:
Create 2 tables:

1. University: can have multiple students.
2. Students: student belong to one university.

Step 2:

• Display all students with their university name (Only students who have attained a university)
• Display all students with their university name (even if students didn't attend a university)
• Display all universes with student count (even if the university doesn’t have any student)
• Without using table joins, display all cities that are in university and students tables.

-------------->help me plz

[torn sphinx]

connection = motor.AsyncIOMotorClient('localhost', 27017)

where in this string do i put the username and password?

[brazen lodge]

can someone pls help me with that

[lusty quarry]

can someone help me with firebase python integration?

[steel karma]

@brazen lodge i'm not an expert at sqlite3 but it doesn't seems like that how you connect to an sqlite3 database, please take a look at this example https://www.sqlitetutorial.net/sqlite-python/sqlite-python-select/

[brazen lodge]

i made that functiion to execute sql statements and close connectons

and im pretty sure thats correct

cuz t worked when i tried insertng data

[steel karma]

have you tried to print out your sql statement from cursor.execute()? to see if your query is correct

[steel karma]

@brazen lodge like Rei mentioned above, your query criteria probably not met. try print out message.guild.id to see if it's in your Database

[brazen lodge]

yep t s

ll print the query

[ebon mirage]

check if your quota is out or storage is full

or your db password changed or something like that

[torn sphinx]

@ebon mirage I know the db password hasn't changed but I will check if the storage is full, that is a good idea!

[ebon mirage]

thanks!

[torn sphinx]

im trying to build something in heroku

its a py thing

why isnt it compatible with their official py buildpack

[round fjord]

hello. I have alembic/sqlalchemy problem at #help-honey . Relations are working with the alembic autogenerate (rest is working fine)

if anyone interested

[full plover]

Guys im using mysql
Is creating a table for each guild a bad or good way of saving data for each guild (discord.py)?
If its a bad way, what should I do instead?

[finite ice]

create a column guild_id and store the guild id

for the stuff you need

[full plover]

yeah but like

[finite ice]

for example a prefix table

[full plover]

i want to save members' data for each guild

[finite ice]

for what

[full plover]

level system

[finite ice]

i use postgresql and it works fine, i store guild_id, user_id, exp and lvl

i believe it should work in mysql

[proven arrow]

It's bad.

There's many reasons, if you want a longer answer I can explain but generally a No.

Instead you would have single table and then use foreign keys to link to other tables.

[split sonnet]

Hey guys, can you help me convert this sql query to flask sqlalchemy please? select * from department, (select employee.related_department_id, avg(employee.salary) average_salary from employee group by related_department_id ) result where result.related_department_id = department.id;

[torn sphinx]

I have 500,000 records that haves a uuid property of 255 bytes
i want to chop that uuid property to 32 bytes to save significant space
im using MariaDB, is there any way to do this without changing ALL records?

[white cypress]

How do i export a table from a database into a csv file using asyncpg

[minor zealot]

how can i change the name of my sqlite3 table??

[red robin]

'bonus':Bonus
sqlite3.ProgrammingError: You did not supply a value for binding 1.

[torn sphinx]

sql killin me

I need to learn more about SQL and JSON, anyone got any good vids?

[obsidian hamlet]

can someone help me what should i search for this tutorial? i want to read all this files and search some words with regular expression

[vernal spade]

is postgresql a good choice for storing user info on discord?

[proven arrow]

Yeah

Others like sqlite and MySQL also work fine, too.

[vernal spade]

aight, tyvm

ty

do you know psycopg2? @proven arrow

[proven arrow]

Don't use pscyopg2 for discord bot

Use asyncpg instead

[vernal spade]

ah?

oh because it's asynchronous right?

[proven arrow]

Yeah and it's just generally faster as well

[vernal spade]

ty

you're already being helpful

[vernal spade]

do I need to import asyncio aswell?

[proven arrow]

No

Or depends how you run it, you may have to

[sudden pike]

Can someone pls help me to solve this

[prisma girder]

!site rule 5

[delicate field]

Rules

5. Do not provide or request help on projects that may break laws, breach terms of services, be considered malicious or inappropriate. Do not help with ongoing exams. Do not provide or request solutions for graded assignments, although general guidance is okay.

[proven arrow]

@sudden pike You can use subqueries to achieve this.

MySQL has MONTHNAME() and MONTH() functions which will help you with this.

[sudden pike]

Thank you

[scarlet nymph]

using pyodbc to talk to SQL Server and fast_executemany = True, I get the following exception: pyodbc.ProgrammingError: ('String data, right truncation: length 54 buffer 52', 'HY000'). It doesn't specify which column the problem is in, but I am pretty sure it's a datetime2(6), which holds 26 characters--the actual string being input is something like '2020-12-01T14:49:23.350739Z' which is actually 27 characters because of the Z on the end. My current workaround will probably be to manually strip the Z off every string, but can anyone think of a more elegant solution?

[cloud pebble]

I posted a question regarding Flask and MySql in #web-development Should I post it here?

[proven arrow]

Just ask if someone knows they will reply

[cloud pebble]

Hey guys can you provide me the details or correct my approach regarding this operation.

I along with my 2 other Teammates have been assigned a DBMS mini-project. In this project we have to create a web app that should use any sql database for storing data and the app should demonstrate CRUD operations.
we choose Flask for this purpose as Django would be overkill for this. As for the database we are using MySql.
Now I created a database and 5 tables inside it using the MySql Command Line.
How do I take input from the user?

Here is my approach->

First the user will enter it's detail via html form.
now I need to collect user's detail and store it in some variables.
then I need to pass those variables in SQL insert query . As a result the data would get stored in the tables.

Is this approach correct? if yes can you guys tell me how do I implement it of what do I need to implement it in flask?
Is there any other way to do this thing?

[proven arrow]

Your approach is fine. Just don't directly pass the user input from the form into the database. This is because the no 1 rule is never trust the user.

Instead use a parameterised query, which you can look up how to do with whatever module you to connect to the database.

[cloud pebble]

thanks I need to search about this

[static ingot]

can somebody help me with mysql-connector

[soft fog]

do you all prefer query builders or orms for sql databases

ive heard orms have some issues like n+1 over-fetching as well as under-fetching

but it makes for more elegant code

and sometimes the use of an orm is slower than building a query up using building blocks and then executing in one go

[cloud pebble]

I prefer query builder

[soft fog]

same, just wanted to get other opinions, thanks!

[cloud pebble]

🙂

[proven arrow]

@slow cove you can have temporary user defined variables, https://dev.mysql.com/doc/refman/8.0/en/user-variables.html

although not a list but you can have a comma seperated string

[torn sphinx]

so I am using quart as my web framework for asynchronous work etc etc but I cant seem to find a way to create a pool using aiomysql

nvm

I'll do it the long way

[torn sphinx]

I am getting a connection timed out error, From my mongodb.

Is it from me?

or is it from mongo?

[fresh tundra]

hello

how to check if a specific table exist in sql

(sqllite)

[torn sphinx]

IF (EXISTS (SELECT *
  FROM INFORMATION_SCHEMA.TABLES
  WHERE TABLE_SCHEMA = 'TheSchema'
  AND TABLE_NAME = 'TheTable'))
BEGIN...```

@fresh tundra

[idle stump]

any suggestion to make this code work

[radiant elbow]

you have an = after print on line 11. Remove it.

[idle stump]

ohh yaa ok

thank u soo much @radiant elbow

[glossy gate]

Hi. So is there any database that I can use online because my PC is broken

[cloud pebble]

So I managed to learn how to take inputs via html forms and how to display it in a new page via redirecting. Can you give me some lead about what to do you wanna convey, like what is parameterised query etc.

[fresh tundra]

how can i encrypt sqllite db

(add a password)

[proven arrow]

@cloud pebble So say you want to execute some query and select/insert where some value equals X where X is what the user inputs through your form. If you directly pass this input to your query with something like string interpolation, then you open up yourself to what is known as sql injection. You can look this up but essentially its the following: what if the user through the form inputted some sql code, and you just passed this to your query directly from the form input. It can cause some damage right?
For example,

user_input = "1234"  # input you expect from the form
user_input = "1234; DROP TABLE students;"  # input user actually gives. Note the extra drop command.
cursor.execute(f"SELECT * FROM students WHERE value={user_input}")
# Above query gets translated to: SELECT * FROM students WHERE value=1234; DROP TABLE students;

So a way to avoid this is to use a parameterised query. In this method you isolate the parameters from the actual query string, and instead where the values would go you would replace that with some sort of placeholder.
For example the above code would become,

cursor.execute("SELECT * FROM students WHERE value = ?", (user_input,))

This way the sql query is pre compiled, and the database knows what to expect wherever the placeholder ? is. We then provide the parameters seperately, which will fit into the placeholder. This way if you did provide some other sql statements or code it just wouldnt work, as your parameter will be treated as a value as a whole. Now different database modules implement this slightly differently in the sense of what character they use for the placeholder, but you can find this in the documentation of whatever module you use.

[cloud pebble]

Thank you for explaining it in a beautiful way.

[cloud pebble]

Hey Guys what are the prerequisite for creating an sqllite database in flask?
I have already given the SqlLite Uri which is app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:////tmp/test.db'
but it's showing error

here's the traceback:
sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) unable to open database file
(Background on this error at: http://sqlalche.me/e/13/e3q8)

how do I create that test.db file?

[ornate creek]

Hello, I'm wondering, it is a good practice to create a stored procedure for all of my complex queries, then call it in python... rather than executing the queries itself in python??

[rigid otter]

so I am using mongoDB and I want a specific return if that's even possible

the return would be the number of the index of an ID in a sorted cursor

say I have X cursor with all my users

and I have a key which ranks them...

I want it to show me the position of them given the cursor is sorted by that key

[errant knoll]

I can’t really help with your problem but I am looking into databases for a project of mine... what are you using the mongodb for?

[rigid otter]

discord bot

i just hate sql syntax das why

[errant knoll]

Hmm d.py is asynchronous so r u using motor?

[rigid otter]

yes

asyncio version of it

tornado seems quite trashy

[errant knoll]

Ah, how is it?

[rigid otter]

runs fine

[errant knoll]

What scale?

[rigid otter]

not that big...8k

[errant knoll]

Interesting

[rigid otter]

tbh with mongo optimizing searches and using mongo itself to bring everything sorted makes it way better

[errant knoll]

Yeah, I need to make a proper db for my bot as currently all the files are just .json file it reads and overwrites

I was looking into mongo bc it uses json format but then people advised me that postgres would be better...

So I’m at a bit of a loss

[rigid otter]

I like mongo coz of it's versatility, bot for huge scale it would be postgres

[errant knoll]

Mhm

[rigid otter]

tbh mongo ran fine not even async until I reached 7k servers

literally spamming on_message with updates

I asynced it for fun

[proven arrow]

It's controversial. Personally I'm not a fan of stored procedures and would keep the queries application side.

[proven arrow]

Your data is relational so postgres should be your preferred option.

[errant knoll]

Yea, that is what people keep telling me. I don't want to but I think I am going to go with Postgres - gotta learn SQL first tho

[torn sphinx]

is this the right channel for help with python and mysql commits?

I created the following funct:

def execute_query(mycursor,query,message):
try:
mycursor.execute(query)
print(message+" [OK]")
mydb.commmit()
print('Commit to DB: [OK]')
except:
mydb.rollback()
print('Commit to DB: [KO] - Rollback')
print("ERROR:"+message)
sys.exit()

when I execute the query mycursor.execute(query) it prints the print(message+" [OK]"), but when I commit it goes in error... but it does not say why it goes in error... how can I debug this?

[proven arrow]

your commit has three letter m

[torn sphinx]

oh.. what an eye...

thanks a lot.. I will try this now

it works perfectly!! thanks a lot

is there a way i can get the sql error printed when there is a query that does not work?

ok found a way thanks 🙂

[torn sphinx]

how do I insert a value into only 1 column?

[proven arrow]

specify only 1 column
INSERT INTO table_name (column_name) VALUES (value)

Although for that to work, your other columns should have a nullable constraint

[torn sphinx]

I am making an admin role command which sets the admin role iDs of a guild, how do I store several role iDs for only 1 guild? and how do I make it a list?

[dusty flint]

@torn sphinx should probably go to #discord-bots for that

[torn sphinx]

This is primarily about databases, mark my words

[torn sphinx]

how do I return the full content of an SQL db? and how do I convert a column from a table into a list?

[fallow moon]

hi everyone! does anyone know how to open up a .dic file in python?

[torn sphinx]

@torn sphinx those reactions were terrifying to sse

[torn sphinx]

yes, take off your encryptions and then open your zip file

[torn sphinx]

oh sorry to have frightened you

[round osprey]

What would be a good way to learn sqlite

[viral hill]

**Where do you get your best data?**

Suggest more topics here!

[fresh tundra]

i am making a password manager to learn sql

[fast whale]

where do I find option to whitelist IP address or is there any other way to connect the database with my RDP

it says host denied the connection when I try to create_pool

but the same code works perfectly on my PC

[fast whale]

I have explained my problem more specifically here, https://stackoverflow.com/questions/65121640/how-to-connect-to-my-postgresql-database-from-rdp

[proven arrow]

@fast whale is your application and the database on the same host?

[cloud pebble]

Hey Guys,
I've been trying connecting My flask app with Mysql db.
The connection was succesful but when I am accepting an input from user it shows error:
TypeError: an integer is required (got type str)

here's my flask code:-

from flask import Flask, request, render_template, url_for, redirect
from flask_mysqldb import MySQL
import yaml

app = Flask(__name__)
db=yaml.load(open('db.yaml'))

app.config['MYSQL_HOST']=db['mysql_host']
app.config['MYSQL_USER']=db['mysql_user']
app.config['MYSQL_PASSWORD']=db['mysql_password']
app.config['MYSQL_DB']=db['mysql_db']
app.config['MYSQL_PORT']=db['port']


mysql=MySQL(app)



@app.route('/',methods=["POST","GET"])
def login():
    if request.method =="POST":
        coachDetails=request.form
        name=coachDetails['username1']
        passw=coachDetails['password1']
        cur=mysql.connection.cursor()
        cur.execute("INSERT INTO CoachData(cname) values (%s)",(name))
        mysql.connection.commit()
        cur.close()
        return 'success'
    else:
        return render_template('reg.html')

@app.route('/show')
def user():
    return render_template('show.html')




if __name__=='__main__':
    app.run(debug=True)

here's my Yaml code:-

mysql_host: '127.0.0.1'
mysql_user: 'root'
mysql_password: 'yash'
mysql_db: 'loginEx'
port: '3306'

here's my MySql table structure:-
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| cname | varchar(20) | YES | | NULL | |
| cpass | int | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+

[plush sierra]

can you show the traceback?

[cloud pebble]

Here's the Traceback, sorry for the screenshot, as I am unable to post anything which is more than 2000characters

[plush sierra]

can you show the lowermost part?

you could paste it to dpaste.org if its long, and send the link

[cloud pebble]

yeah sure

[cloud pebble]

here's the link:- https://dpaste.org/OUMv

[plush sierra]

hmm in my point of view it looks correct, the question is, why it needs an integer? i guess the error is raised in that "cur.execute" line

[cloud pebble]

Yeah, but why?

[plush sierra]

ah nooo

i think the port is an string and should be integer?

try

app.config['MYSQL_PORT']=int(db['port'])

[cloud pebble]

ok let me fix this

[cloud pebble]

That's not the error, Tried that and got this message:-

MySQLdb._exceptions.OperationalError: (2003, "Can't connect to MySQL server on '127.0.0.1' (10061)")```

[plush sierra]

hmm but the error is in line 25: https://dpaste.org/OUMv#L25,26

so something with the connection is wrong

[cloud pebble]

yes is there another way to establish a connection?

[plush sierra]

ah got you, the code is from here: https://www.codementor.io/@adityamalviya/python-flask-mysql-connection-rxblpje73 😄

hmm do you know if your db is set up properly? Can you connect to it using the mysql commandline?

[cloud pebble]

well, No but the concept is same

[cloud pebble]

My mysql command line client is running and I created the database by it.

[plush sierra]

hmm can you connect to it like this?:

import MySQLdb
username = 'username'
password = 'pass'
hostname = 'username.mysql.pythonanywhere-services.com'
db = 'username$zipcodes'
conn = MySQLdb.connect(host=hostname, user=username, passwd=password, db=db)
cursor = conn.cursor()

conn.close()```

[cloud pebble]

I'll try this

[cloud pebble]

Tried this got an error. I'll try again in an hour

I'm too frustrated now

[plush sierra]

ok. Maybe i'll look on it again in the evening (its 13:00 here)

[cloud pebble]

Thank you it's 5:31pm here

[bleak scaffold]

Hi is it possible to order in a select query by milliseconds in MySQL?
Cause I got a table with id's that are hashes instead of being numeric and the timestamps look like up to a second, where 1 or 5 records are the same datetime and can't distinguish which is the last row.

using query

SELECT UNIX_TIMESTAMP( 'created' ) ...

I can see that I have duplicate values.

got it, max value 🙂

[steady narwhal]

Anyone have suggestions on what type of database/data processing framework i should use if i were to be storing second-by-second ticker data?

[torn sphinx]

so can we have more than 50 coloumns?

[wintry stream]

32767 in sqlite

[torn sphinx]

and the db won't lag

Oh

[wintry stream]

i doubt you'll ever have more than 30 thousand different items

[torn sphinx]

nono lmao

also can we have multiple tables?

in a single file?

[wintry stream]

well the file in sql is the database

a database can have multiple tables

and a table has multiple columns and rows

a table is a matrix

[torn sphinx]

something like this

[wintry stream]

if you add ; you can make multiple queries without multiple executes

[torn sphinx]

SELECT guild_id FROM settings or player_stats

[wintry stream]

await db.execute('''
  CREATE...
  ...INT
  );
  CREATE....
  INT
  );
  ''')

like this

so you add the ; and it's all combined

[torn sphinx]

oh

[wintry stream]

or at least that works in like mysql and postgres

maybe sqlite is an odd one out

[torn sphinx]

liek that

so will that work the same?

@wintry stream

like compared to my code

[wintry stream]

should work

[torn sphinx]

okai

[wintry stream]

; in sql is seen as an end of query

so you can use it to make multiple queries in 1 go

[torn sphinx]

SELECT guild_id FROM settings or player_stats

so i can select liek this

[wintry stream]

nop

[torn sphinx]

so how?

SELECT guild_id FROM settings

[wintry stream]

that will work

[torn sphinx]

SELECT guild_id FROM player_stats

with the same connection

[wintry stream]

that will also work

[torn sphinx]

okioki

[wintry stream]

as long as they are both valid tables with that column

i can quickly check something 1 sec

[torn sphinx]

oki

[wintry stream]

just checked in pg, seems like you cannot query a select on multiple tables at once

as i expected

[dusky plaza]

if you wanna query multiple tables you must JOIN them

afaik

[wintry stream]

that would put the data side by side right?

[steady narwhal]

Anyone have suggestions on what type of database/data processing framework i should use if i were to be storing second-by-second ticker data?

[gray apex]

Hey all, I have two datasets, one with 140k documents, the other with 740k. I'm trying to match documents between the two via some specific fields. Does anyone know of a fast way to do count with mongoDB?

If I just use find, it finishes in 70 seconds, but the moment I use any type of counting, via count, document_count, explain, it takes 48 hours...

[gray apex]

I think I figured it out, I had an or query that was taking too long

[torn sphinx]

Hi guys, looking for some advises about bigquery. Anyone around here?

import pymongo
from pymongo import MongoClient
import os

mongodbclient_token = os.getenv("My mongo connection would be here")

if mongodbclient_token is None:
    try:
        with open('./mongodbclient.0', 'r', encoding='utf-8') as client_url:
            print("Using MongoDB cluster url provided in file")
            cluster = MongoClient(client_url.read())
    except FileNotFoundError:
        print("File not found [mongodbclient.0]")
        print("Neither environment variable nor client file exist")
        print("Abort")
        exit()
else:
    print("Using MongoDB cluster url provided in environment variable..")
    cluster = MongoClient(mongodbclient_token)```