databases

[grim vault]

You used more placeholders in the SQL statement than arguments given.

[flat pewter]

I still not see how the current_time value gave 2001. Can you please explain?

[grim vault]

The date wasn't in quotes, so the sql engine thought it was a calculation 2022 minus 6 minus 15 equals 2001.

[flat pewter]

ohh. thanks :). shows how dangerous the f-strings are when you don't see it even with a hint

[remote latch]

conn = sqlite3.connect("customer.db")
cursor = conn.cursor()```

[paper flower]

You don't reuse cursors, right? I'm not entirely sure if it affects update statements but they're not reusable

[icy oyster]

how can I return data from for example Inverse (red circle) using Identifier (blue circle) to make the database know what row I wanna use

In other words, I'll tell the database to give me the value of margin_type where identifier is X value

[icy oyster]

Cause when I do

sql_test = curu.execute('''SELECT user_id from UserConfig WHERE identifier = "ChutiUserID_BTCUSDT_30m_Inverse";''')
print(sql_test)

the terminal prints

<sqlite3.Cursor object at 0x0000027CA6EE1C40>

so I suppose there should be a way to return the value where the cursor is at, right?

[crisp finch]

You need to call curu.fetchall() or curu.fetchone()

[icy oyster]

curu.fetchone(0x0000027CA6EE1C40) ??

that?

Cause the other thing I tried was

row = curu.fetchone()
curu.execute('''SELECT user_id from UserConfig WHERE identifier = "ChutiUserID_BTCUSDT_30m_Inverse";''')
for row in curu:
  print(row)                  

but print(row) returns ('ChutiUserID',) instead of ChutiUserID

[crisp finch]

I meant that you need to replace print(sql_test) with print(curu.fetchone()) to get the actual data instead of <sqlite3.Cursor object at 0x0000027CA6EE1C40>

[icy oyster]

aaaaa

[remote latch]

like using the same cursor variable?

[icy oyster]

like this? curu.execute('''SELECT user_id from UserConfig WHERE identifier = "ChutiUserID_BTCUSDT_30m_Inverse";''') print(curu.fetchone()) cause this returns ```('ChutiUserID',)

[crisp finch]

Try fetchall() instead. It returns a list.

[icy oyster]

[('ChutiUserID',)]

[crisp finch]

https://docs.python.org/3/library/sqlite3.html#sqlite3.Cursor.fetchall

[icy oyster]

same thing :C

I could replace but 🤷‍♂️

funnily enough

curu.execute('''SELECT user_id from UserConfig WHERE identifier = "ChutiUserID_BTCUSDT_30m_Inverse";''')
type = print(curu.fetchone())
print(type)

('ChutiUserID',)
None

🤣

[crisp finch]

Hmm, try removing semicolon from your SQL query

[icy oyster]

kk

; this?

or these ""

I heard of colons, not semi colons :/

[crisp finch]

The ;

[paper flower]

Yep

[icy oyster]

curu.execute('''SELECT user_id from UserConfig WHERE identifier = "ChutiUserID_BTCUSDT_30m_Inverse"''')
print(curu.fetchone())
print(type(curu.fetchone()))
``` ```
('ChutiUserID',)
<class 'NoneType'>

[remote latch]

yeah I do that should I use a diff one every time?

[crisp finch]

Ah, you wanted to retrieve the identifier column. I've rechecked your question. You need to add it to the select statement. SELECT user_id, identifier ...

You can also remove the user_id if you don't need it.

[paper flower]

Yep 🤔

[icy oyster]

its retrieving what I want, just that with (' at the beggining and ',) at the end

[crisp finch]

If you want to get all the columns, use select * from ...

[paper flower]

If you can create a reproducible example that i can run locally it would be great

[crisp finch]

Yes, because it's a tuple. You need to use curr.fetchone()[0]

[icy oyster]

Still none type but printing without (' at the beggining and ',) at the end

🙂

ok solved

@crisp finchTYSM! 💙

[desert hedge]

Discord.py/DB : Hello, is it possible to convert this code into sqlite3 to have a database where all the users + warn and reason can be found ? This is the actual code, I'm available to discuss^^https://paste.pythondiscord.com/dacayocuci

[bleak bough]

SELECT submissions.link, submissions.title, submissions.id, COUNT(votes.link) as Count
FROM submissions
LEFT JOIN votes ON submissions.link = votes.link
ORDER BY Count DESC```
there are two different links in both tables but it only returns one result for `fetchall` with the count being of both links ![7739monkathink](https://cdn.discordapp.com/emojis/960314142030442566.webp?size=128 "7739monkathink")

i know COUNT(votes.link) is probably wrong but can't think how to do it

needs to count individual links from submissions in votes

oh it just needed group by

[brave mountain]

I have a problem with SQLAlchemy. I use the same code in different places, it generates the same SQL query, but in one case sqlalchemy doesn't load related m2m fields

async def get(self, obj_id: str) -> ModelType | None:
    stmt = (
        select(self.__model)
        .where(self.__model.id == obj_id)
        .options(*self.default_options)
    )
    results = await self.session.execute(stmt)
    db_obj = results.unique().scalar_one_or_none()

    if db_obj is not None:
        logger.debug(f"Got {self.name.lower()} {db_obj.id}")

    return db_obj

self.default_options: list[Load] = [
    joinedload(models.Game.sales),
    joinedload(models.Game.platforms),
    joinedload(models.Game.genres),
]

Can anyone help me with this or explain why this is happening? I can send models' code if it's needed

[icy oyster]

is it possible to select a row and return all the data as a dict or json ?

[torn sphinx]

Yez

[icy oyster]

do you have idea how?

[torn sphinx]

Talk is cheap show me code

[icy oyster]

I can but how would that help you

!paste

[torn sphinx]

Let me see how you extract data from db

[icy oyster]

curu.execute('''SELECT user_id from UserConfig WHERE identifier = ?;'''), (config_identifier)
data = curu.fetchone()[0]

I'm extracting 1 per one, it returns a string

but maybe if it can just return a json, its better, maybe

[torn sphinx]

use cur.execute and just iterate and extract all data like tuple

And use it in your way as dict or json whatever

data=cur.execute('select a,b from tablename ...')
for row in data:
    a,b=row;
    ...
    ...

[icy oyster]

for i

what does i mean?

            data = cur.execute('''SELECT user_id, symbol, timeframe, margin_type, leverage, tp1, tp2, tp3, timeout, retries, limit_to_market, limit_chase FROM UserConfig WHERE identifier = ?;'''), (config_identifier)
            for i in data:
                user_id, symbol, timeframe, margin_type, leverage, tp1, tp2, tp3, timeout, retries, limit_to_market, limit_chase = i
                print(i)
``` this would print a dict?

[torn sphinx]

for row in data would be better

[icy oyster]

con = sqlite3.connect(f"{config['Database']['TradesTrack']}.sql", check_same_thread = False)
db = con

right?

so for row in con:

[torn sphinx]

don't print row

Or i

[torn sphinx]

For row in data

[icy oyster]

ahh

wait there is something I dont understand

so here at first I select the data I want returned in a dict from the DB

data = cur.execute('''SELECT user_id, symbol, timeframe, margin_type, leverage, tp1, tp2, tp3, timeout, retries, limit_to_market, limit_chase FROM UserConfig WHERE identifier = ?;'''), (config_identifier)

2nd step is directly to format that into a dict?

and you want to do it this way -->
so

for row in data:
  user_id, symbol, timeframe, margin_type, leverage, tp1, tp2, tp3, timeout, retries, limit_to_market, limit_chase = i

but I dont understand why that = i

that would play only for limit_chase right?

[torn sphinx]

now its row

[torn sphinx]

No it's tuple property of unpacking

All column's data will be in each row

[icy oyster]

idk I'll go read that cause tbh I dont get this

[torn sphinx]

It's python' tuple class property

And you can make in dict format

[grim vault]

For sqlite3 you can set the row_factory to sqlite3.Row and can use the result like a dictionary.
see https://docs.python.org/3/library/sqlite3.html#row-objects

!e

import sqlite3

con = sqlite3.connect(":memory:")
con.row_factory = sqlite3.Row

cur = con.cursor()
cur.execute('''CREATE TABLE stocks
(
  date text,
  trans text,
  symbol text,
  qty real,
  price real
)''')
cur.execute("insert into stocks values ('2006-01-05', 'BUY', 'RHAT', 100, 35.14)")
con.commit()

cur.execute('select * from stocks')
row = cur.fetchone()
print(dict(row))
print("symbol:", row["symbol"])

[delicate field]

@grim vault :white_check_mark: Your eval job has completed with return code 0.

001 | {'date': '2006-01-05', 'trans': 'BUY', 'symbol': 'RHAT', 'qty': 100.0, 'price': 35.14}
002 | symbol: RHAT

[icy oyster]

awesome!!

[steady wraith]

Can someone here want to answer for a simple question that I have?

I want to know if it possible to convert colab to kaggle because kaggle give more gpu and my goal is to do it somhow actually I wnat to use the kaggle GPU

[torn sphinx]

raise errors.ProgrammingError(
mysql.connector.errors.ProgrammingError: Not enough parameters for the SQL statement someone know to help please?

[grim vault]

You used more placeholders in the SQL statement than arguments given.

Show your code so we can take a look.

[fading patrol]

I've never tried it, but I googled it for you: https://galhever.medium.com/how-to-import-data-from-kaggle-to-google-colab-8160caa11e2

[grim vault]

The questions seems to be google colab -> kaggle not kaggle -> google colab.

[opaque wave]

is anyone familiar with the virus called data studio? Need to write a formula to get a count of columns that have a value per row, but this thing is like a braindead sql version

[steady wraith]

thank you friend but I think this method just import data and not the gpu itself

[fading patrol]

I'm not sure I understand, you can't move a GPU from one service to the other

[white elm]

do anyone know if it's possible to store something like a list of names in a record?

Im trying to make a queue system with that

[steady wraith]

I will explain. colab give in their free version the T4 GPU and kaggle give the p100 gpu and this is a big different so that why I want to use colab notebook on kaggle but it's seems to not working I don't know why. do you?

[fading patrol]

I've never really used colab, but what do you mean "seems to not working"? Are you getting an error message?

[steady wraith]

Yes... if I run the same code from collab on kaggle things don't work. for example I want to run this model on kaggle: https://colab.research.google.com/github/multimodalart/MajestyDiffusion/blob/main/latent.ipynb

[verbal lily]

I’m about to code the python project for the inventory management for my job, it’s my first crack at python

[hidden wedge]

what framework are you going to use and who are the endusers

[verbal lily]

To be honest with you, I hadn’t look at what the framework for it. I’m in the brainstorming phrase currently

[hidden wedge]

sounds like your going to need some type of web framework like django to CRUD operations. But it really depends on your operations

[fading patrol]

If you need help troubleshooting your notebook, you can grab a help channel ( #❓｜how-to-get-help ) and let people know in #data-science-and-ml.

[sleek escarp]

I want to create database of a netflix like app in postgres. How do I maintain the user's watch history and his favorites in one table ? I would be updating the watch history again and again since the user will be adding more shows in his history/favs, so what datatype would be suitable?

[paper flower]

Why do you want to store favorites and watch history in the same table?

[sleek escarp]

Yeah I can store them in diff tables. But i want to incorporate them in the db. What datatype would be suitable?

[paper flower]

Datatype for what?

[sleek escarp]

For the watch history

What shows he has watched and is currently watching

[paper flower]

You heard of foreign keys/relationships?

[sleek escarp]

Yeah obv

[paper flower]

Individual columns can have a datatype, that doesn't apply to tables though

For favorites simply many to many relationship would do

Same goes for watch history, but you might want to add some extra data like watch status (i.e. dropped, watching, completed)

[sleek escarp]

Watch history is just one column

[paper flower]

Why?

[sleek escarp]

Wouldn't it be like I will have to create separate watch history tables for each user?

[paper flower]

No, you (almost) never have to create tables for individual users

[sleek escarp]

So you mean I can connect user via foreign key in watch history table and add users and their shows to it?

[paper flower]

Yep

[sleek escarp]

Oh man, this doubt sounded so dumb 😂 . Thank you lol!

[paper flower]

create table watchlist(
    user_id integer foreign key references users (id),
    movie_id integer foreign key references movies (id),    
    status integer,
    primary key (user_id, movie_id)
)

Status type is up to you, you can use strings, integers, and some rdbms support enums @sleek escarp

[grim vault]

datetime column for when it was watched?

[paper flower]

It's up to them what information they want to store, the point is creating a relationship here

But adding a watch date is a good idea

[sleek escarp]

Yeah got it. Thanks mate!

[steady wraith]

Thank you I will

[placid canyon]

I'm trying to put stuff in a database
replit.db
python
but i keep getting this:

db[uid] = {
'devmode': str(devmode),
'active': 'earth',
'earth': {
'wallet': 500,
'bank': 1000,
'shops': {
'bakery': 3,
'pizzeria': 0,
'carwash': 0,
'art_studio': 0,
'airport': 0,
'shipyard': 0
},
'claiming': {
'daily': datetime.date.today(),
'monthly': datetime.date.today(),
'bi_annually': datetime.date.today(),
'annually': datetime.date.today()
},
'job': None
}
}
thats my code

actually

I think i see the problem

how would i turn a date object into a dictionary

oh never mind

how would i turn a dictionary into a date object

[placid canyon]

oh no this isnt working

oh i think i got it

fixed it

[obsidian basin]

hi

# Here is some of __init__.py
app = Flask(__name__)
from flask_migrate import Migrate
migrate = Migrate()
def create_app(config_class=Config): 
        app.config.from_object(config_class)
        migrate.init_app(app, db)

This is for flask migrate https://flask-migrate.readthedocs.io/en/latest/.

When I run ** flask db init** on the command line I get the error in the pastebin.
https://pastebin.com/SCeky6mM

How do I fix this?

Thanks for the help

[unkempt prism]

why do you have

  def create_app(config_class=Config): ```

twice in your formatted code ?

[obsidian basin]

sorry mistype I fixed it

[quartz garnet]

hey I got a SQLlite3 database like this

Menu(**food_id**, price)
Users(**user_id**)
Order(**id**,user_id,total,status,date)
OrderDetail(**order_id**, **food_id**, quanity)

idk why its not bolding

but you get the idea

how do I make the total = sum of (food_id * quantity) ?

with the same order_id

basically calculating the total of an order

is there a query to assign this value or should I calculate it using the data in Python and then insert the calculated value into it?

[paper flower]

select sum(food_id * quantity) from order_details where order_id = ?

[quartz garnet]

how do I assign this query to the total attribute so that it always updates itself?

[unkempt prism]

The docs do call db.init_app(app) first

from flask_sqlalchemy import SQLAlchemy
from flask_migrate import Migrate

db = SQLAlchemy()
migrate = Migrate()

def create_app():
     """Application-factory pattern"""
     ...
     ...
     db.init_app(app)
     migrate.init_app(app, db)
     ...
     ...
     return app

[wispy ridge]

Anyone who is good at django?

[wild pelican]

How could I store d into my database? py d = [r.id for r in [role1, role2, role3, role4, role5] if r] await db.execute('INSERT INTO ticketdatabase VALUES (?,?,?);', (interaction.guild.id, category_channel.id, d)) The result of d is [roleids, roleids2, roleids3, etc]

[torn sphinx]

dont think sqlite3 has array support, you can enter the array as a string and then eval(result) to get the array back, dont think its the best method tho

[rigid venture]

im using mongodb, i try to connect to it but it gives an error "An existing connection was forcibly closed my the remote host"

[wild pelican]

It works 😂

As long as it works, that's a good enough method! Thank you :)

[paper flower]

You can use foreign keys too instead

[grim vault]

I would suggest at least using a json-string (json.dumps(d)) to store the array in the database (reverse is json.loads(stringvar)). Better would be to save each entry by itself.

[paper flower]

Json would certainly be better than eval

[torn sphinx]

mysql.connector.errors.ProgrammingError: 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'moto = NULL, position_company = NULL salary = NULL WHERE employee_id = 9' at line 1 can someone help?

[torn sphinx]

Does somebody know how can I store data in an array in firebase db array without generating a new key I used push() but it generates a new key

[crisp finch]

You need to use IS NULL

[torn sphinx]

i think it is bro already

[grim vault]

You are missing the , before salary = NULL and it is = NULL if it is an update of the column and IS NULL if it is in the where clause to check for null value.

[torn sphinx]

cant find where it is

[grim vault]

moto = NULL, position_company = NULL salary = NULL WHERE employee_id = 9 between NULL and salary is no comma.

[torn sphinx]

in mysql or code itself?

[grim vault]

In the SQL string you send to mysql

[torn sphinx]

okey will see a bit later if i can fix it, thanks a lot👍

[torn sphinx]

mysql.connector.errors.ProgrammingError: 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'moto = 'Strongest choices require strongest wills.', position_company = 'Foun...' at line 1

i dont understand how to fix this, i fixed all , that needed

[grim vault]

No comma after the photo = %s, and the commas after salary = %s, and employee_id = %s, are too much.

[torn sphinx]

so salary and employee_id dont need commas?

thanks, will try

[grim vault]

Yes, in SQL a comma after the last entry of a list is not allowed.

[torn sphinx]

thanks you so much, its working!!!

[torn sphinx]

can you explain why its not allowed or its just a rule?

[grim vault]

In many languages it's not allowed, python is the only one which allows it I know. Maybe it's easier to parse, I don't really know.

[torn sphinx]

i have another problem and its biggest for me

everything is working well but photo, photo is not showing and i uploaded it on mysql, connected with python and still same

[paper flower]

It's more convenient to move elements around this way

[brave bridge]

also better for git diffs

I always make this mistake with SQL and JSON

[coral briar]

Can someone please help me in #help-pear.

[molten gazelle]

{'_sa_instance_state': <sqlalchemy.orm.state.InstanceState object at 0x0000026CFC10>, 'item_count': 96, 'item_mechanisims': [<database.models.OpeningMechanisim object at 0x000002691BE0>]}
"""
How can I get the value of database.models.OpeningMechanisim object at 0x000002691BE0
"""

[wild pelican]

I currently have a class that needs a database connection. It requires await, how could I connect to my database outside the class' async def so I can use it with defining things such as the label:

Connection: py db = await aiosqlite.connect('database.db')

Class: ```py
class TicketSystem(discord.ui.View):
def init(self):
super().init(timeout=None)

@discord.ui.button(label='Support', style=discord.ButtonStyle.green, custom_id='tickets:1')
async def tickets(self, interaction: discord.Interaction, button: discord.ui.Button):```

[austere veldt]

Owing to a long sequence of bad decisions, I'm working with MS Access (which supports a lot of SQL but not all of it). Here's some example exercise data. I have things that are starting and ending on certain days. The goal is to get a summary of all the days on which something happened, and how much. So I can get a count of how many things started on each day, and a count of how many things ended on each day, and then I'd like to combine both of THOSE summaries into one table. The obvious way (to me) would be to Full Outer Join the two summaries together.

However, Access does not actually implement a full outer join.

The other way that I can see is to get a Union of all the days of any relevance first, and then join each kind of daily total to that. Is there a more obvious solution?

(only because the way that I'm thinking of becomes kinda unwieldy if I have to add a lot of different kinds of daily summaries to it, and gut feeling says that that means that I'm doing something unnecessarily complicated)

[sinful rivet]

i use mySQL-connector, and it's really slow when i just use SELECT query (just a table with 2 rows and 4 columns) why

and how to speed up

[brave bridge]

show the code

[sinful rivet]

ok hold up

main code <https://www.toptal.com/developers/hastebin/avonacaciy.yaml database code https://www.toptal.com/developers/hastebin/moyucuteqi.py

[sinful rivet]

up there

[paper flower]

There's 600 lines of code, can you create a smaller snippet?

[woven plover]

Is there a way to put an MP3 file into a MySQL for python?

I wanna make an online database for songs

[hidden wedge]

someone correct me if im wrong but something like s3 would be better or a databse that supports that datatype not sure if mysql will support mp3

[keen minnow]

yeah, in general you would put the content/files themselves in an object storage such as s3, and store the metadata about them in a regular db

[hidden wedge]

what langague are you writing in?

[woven plover]

Someday I don't wanna use another companies database services

[remote latch]

cursor.execute("""UPDATE ? SET ?=? WHERE id == ?""",(database,asp,value,id))```
anyone know what I did wrong here

[naive pike]

it can happen if the id is a primary key, you can't put like a data of the database

[naive pike]

can you show me the error??

[uneven sinew]

oh no

Store the songs (.mp3) in a directory, then store the filepaths in the database

[uneven sinew]

you did a double equals

WHERE id == ?
^^^^

it's supposed to be only one

[wild pelican]

How could I dump the contents of a SQLite database into a CVS file?

[keen minnow]

If I google for SQLite database into a CVS file, here is the first link:
https://www.sqlitetutorial.net/sqlite-export-csv/

[wild pelican]

This seems like it needs to be run from a command prompt?

[keen minnow]

not necessarily, as demonstrated by the link

[grim vault]

You can not use placeholders for table- or column-names.

[paper flower]

There are s3 compatible storages that you can host yourself if you want

[short gate]

You can use the sqlite3.Cursor.fetchall() method to select an entire table and iterate through the list of dictionaries to feed the output into csv.writer

[short gate]

While @uneven sinew has a much more practical answer, you can also store the file itself if you pickle it to serialize and post the binary string as a BLOB column. This is not generally advised though since querying the song file itself (rather than Metadata or the storage path) is a lot of data for the SQL engine to chug through and ultimately is difficult to retrieve things which are useful to query for.

[sleek escarp]

Can anyone tell me what's wrong with this trigger , since the column completestatus is not updating, whenever i try to update a row of the table watchhistory

    returns trigger 
    LANGUAGE PLPGSQL
    as $$
    declare
    useridd integer;
    movieidd integer;
    tottime time;
    begin
        select userid into useridd from watchhistory where userid=old.userid;
        select movieid into movieidd from watchhistory where movieid=old.movieid;
        select totaltime into tottime from movies where movieid=movieidd;        
        if new.watch_time_completed=tottime then
            new.completestatus=true;
        end if;
        return null;
    end;
$$ ;    

create or replace trigger movie_completion
    after update on watchhistory
    for each row
    execute procedure func();

[hidden wedge]

do you have a front end framework for this ticketing system django can cut out alot of manaul database work for you

[inland forge]

I am working on an economy system
such as Dank Memer or something like that
I'm using aiosqlite to make the database storing the user informations, and the aio version cuz I'm using dpy 2.0 and it's more useful
well
the database is created, with the table and everything else
I also worked on some functions to access it
but I'm getting this error below and Idk how to fix it

this is in main.py

        try:
            self.connection = await aiosqlite.connect('.\databases\eco.sqlite')
            cursor = await self.connection.cursor()
            await cursor.execute('''CREATE TABLE IF NOT EXISTS eco (
                user_id INTERGER, wallet INTERGER, bank INTERGER
                )''')
            await self.connection.commit()
            await cursor.close()
        except:
            print(Fore.RED + 'Error loading economy database' + Fore.RESET)
        else:
            print(Fore.GREEN + 'Economy database loaded' + Fore.RESET)

this is economy.py

import discord
from discord.ext import commands
import random
from utils.economy_functions import add_user, fetch_user, fetch_bank, fetch_wallet, delete_user, update_wallet, update_bank


class Economy(commands.Cog):
    def __init__(self, bot):
        self.bot = bot

    @commands.command()
    async def newaccount(self, ctx):
        cursor = await self.bot.connection.cursor()
        if await fetch_user(cursor, ctx.author.id) == ctx.author.id:
            await ctx.channel.send('You already have an account!')
        else:
            await add_user(self.bot.connection, ctx.author.id)
            await ctx.channel.send(f"{ctx.author.name}'s account added.")
        await cursor.close()


async def setup(bot):
    await bot.add_cog(Economy(bot))

this is economy_functions.py

async def add_user(db, user, wallet=0, bank=0):
    cursor = await db.cursor()
    async with db:
        await cursor.execute('INSERT INTO eco VALUES (:user_id, :wallet, :bank)', {
            'user_id': user, 'wallet': wallet, 'bank': bank})
    await cursor.close()


async def fetch_user(cursor,  user):
    await cursor.execute('SELECT user_id FROM eco WHERE user_id = :user_id',
                         {'user_id': user})
    output = await cursor.fetchone()
    if output is not None:
        return output[0]
    else:
        return output


async def fetch_wallet(cursor,  user):
    await cursor.execute('SELECT wallet FROM eco WHERE user_id = :user_id',
                         {'user_id': user})
    return (await cursor.fetchone())[0]


async def fetch_bank(cursor, user):
    await cursor.execute('SELECT bank FROM eco WHERE user_id = :user_id',
                         {'user_id': user})
    return (await cursor.fetchone())[0]


async def delete_user(db, user):
    cursor = await db.cursor()
    async with db:
        await cursor.execute('DELETE from eco WHERE user_id = :user_id', {
            'user_id': user})
    await cursor.close()


async def update_wallet(db, user, wallet):
    cursor = await db.cursor()
    async with db:
        await cursor.execute('UPDATE eco SET wallet = :wallet WHERE user_id = :user_id', {
            'user_id': user, 'wallet': wallet})
    await cursor.close()


async def update_bank(db, user, bank):
    cursor = await db.cursor()
    async with db:
        await cursor.execute('UPDATE eco SET bank = :bank WHERE user_id = :user_id', {
            'user_id': user, 'bank': bank})
    await cursor.close()

and this is the database

[north trellis]

SQLAlchemy question

how do i remove uncommited changes that i added using db.session.add(data)?

like reset it back to the last commit

[floral swan]

Could someone comment on the feasibility of using two database systems simultaneously? eg. MongoDB and PostgreSQL?

I find it rather unintuitive to keep thousand rows of logs each associated with a foreign key when I can just have an array of logs for whichever key

[paper flower]

You can call .rollback i think 🤔 in case you already flushed you changes

[paper flower]

I don't see a problem with using both, also not sure why you need a foreign key for a log entry

<@&831776746206265384>

[floral swan]

the log entries belong to different services and the logs all share the same columns if that makes sense. I have to query logs based on different the different services.

[paper flower]

So foreign key points to a service?

[floral swan]

I read that people have desync concerns over using two different databases

[floral swan]

yes

my team's project had always used nosql, and this is my first time use relational db, I apologize for any dumb question

[paper flower]

You'd link them the same in mongodb, it doesn't work with large arrays that well 🤷

Unless you make log it's own document and... use a foreign key

I didn't use mongodb but i think embedded arrays won't work here

[chrome mango]

hello, i have this printing in my console after i try to use my mysql database, any ideas why?

im not quite sure what could be causing this as ive never seen this before

I think its to do with conn.close()which is wierd because i have it after my sql commands

if i remove that line i dont have the error

idk it still sometimes shows that NoneType: None. please lmk if you know what the issue is

• in a different coroutine, but still shows the error when closing the connection

[grim vault]

You're using a contextmanager to aquire the connection, it will be closed automaticly after the with block is finished.

Don't close it manuylly.

[chrome mango]

yeah, i just wasnt sure on that as the library never said

[grim vault]

Same goes for the cursor.

[chrome mango]

ill do that, then. thank you

wierd how it hasnt done that before

[grim vault]

and please don't use f-strings for data:

!sql-fstring

[delicate field]

SQL & f-strings
Don't use f-strings (f"") or other forms of "string interpolation" (%, +, .format) to inject data into a SQL query. It is an endless source of bugs and syntax errors. Additionally, in user-facing applications, it presents a major security risk via SQL injection.

Your database library should support "query parameters". A query parameter is a placeholder that you put in the SQL query. When the query is executed, you provide data to the database library, and the library inserts the data into the query for you, safely.

For example, the sqlite3 package supports using ? as a placeholder:

query = "SELECT * FROM stocks WHERE symbol = ?;"
params = ("RHAT",)
db.execute(query, params)

Note: Different database libraries support different placeholder styles, e.g. %s and $1. Consult your library's documentation for details.

See Also
• Extended Example with SQLite (search for "Instead, use the DB-API's parameter substitution")
• PEP-249 - A specification of how database libraries in Python should work

[chrome mango]

i know, those vaules arent in-puttable by a user

Im using prepared statements for user inputs

its probably good practice to do it anyway

[grim vault]

Even so, you should still use the placeholder.

[chrome mango]

on non inputtable vaules

yeah, okay

thank you

[chrome mango]

wait, i just doubted myself. this is okay, right?

                await cur.execute(
                    f"INSERT INTO permissions (role_id, permission, state) VALUES (%s, %s, %s)",
                    (role_id, permission, state)
                )

i think it is

[grim vault]

Yes, looks ok, you can loose the f in front of the string.

[chrome mango]

true, thanks

[remote latch]

cursor.execute("INSERT INTO server VALUES(?,'off',0,0,'nil')",(id,))
print("server created")
cursor.execute('''UPDATE server SET commandschannel=? WHERE id == ?''',(message.channel_mentions[0].id,message.guild.id))
conn.commit()```
anyone know why when I stop running the code and run it again it doesn't save.  All data gets deleted but while running it it's still there

[jaunty jacinth]

Anyone have any experience with this

Lost connection to server at 'handshake: reading initial communication packet', system error: 0

I'm trying to set up django to connect to a mySQL database I have on azure.

I'm thinking my engine needs to be set to something other than "django.db.backends.mysql"

And I don't know what should go under host.

Everywhere I read says I need to open my firewall to allow mySQL on port 1433, but I've already done that.

[tiny nimbus]

Has anyone used Python retrieved a file from Amazon S3 Glacier through Python? Can you please share how? Docs are not supportive at all

[steel lagoon]

I want to use google sheet api to create a spreadsheet on event creation on discord and get a list of everyone interested and put there names in the spreadsheet then I can decide what type of event it is either kills or what ever the event is can I make that possible?

[delicate field]

:incoming_envelope: :ok_hand: applied mute to @torn sphinx until <t:1655781386:f> (9 minutes and 59 seconds) (reason: duplicates rule: sent 4 duplicated messages in 10s).

[hazy lily]

Is there any way to update an array element using pymongo?

or any other extension

[short star]

If I do these similar methods without asyncio, i also get same output, so is this only for good code formatting? Or any other purpose

[shy forum]

def clear_alts():
  con = sqlite3.connect(os.path.join(BASE_DIR, "salus.sqlite"))
  cur = con.cursor()
  update ='UPDATE scammers SET alts = NULL'
  cur.execute(update)
  con.commit()
  cur.close()
  con.close()
  return True

In this code, is cur.execute(update) correct?

[inland forge]

I'm using aiosqlite

as discord.py 2.0 is strictly asynchronous, the aio version of sqlite3 is recommended

but still I don't get what the issue is

[grim vault]

I'm not using async but my guess would be that async with db: is the error, because you already awaited the connection and this context block try to start it again and close it afterwards.

[inland forge]

hmm

lemme try

[grim vault]

Dont forget the db.commit() I'm not seeing any.

[inland forge]

that's what the with does

[grim vault]

I don't think so.

[inland forge]

okay it now gives another error 😂 finally a different one

[grim vault]

You need to remove the whole async with db:

async def add_user(db, user, wallet=0, bank=0):
    cursor = await db.cursor()
    await cursor.execute('INSERT INTO eco VALUES (:user_id, :wallet, :bank)', {
        'user_id': user, 'wallet': wallet, 'bank': bank})
    await db.commit()
    await cursor.close()

or maybe like:

async def add_user(db, user, wallet=0, bank=0):
    async with db.cursor() as cursor:
        await cursor.execute('INSERT INTO eco VALUES (:user_id, :wallet, :bank)', {
            'user_id': user, 'wallet': wallet, 'bank': bank})
        await db.commit()

[inland forge]

so something like this

async def add_user(db, user, wallet=0, bank=0):
    cursor = await db.cursor()
    await cursor.execute('INSERT INTO eco VALUES (:user_id, :wallet, :bank)', {'user_id': user, 'wallet': wallet, 'bank': bank})
    await db.commit()
    await cursor.close()


async def fetch_user(cursor,  user):
    await cursor.execute('SELECT user_id FROM eco WHERE user_id = :user_id', {'user_id': user})
    output = await cursor.fetchone()
    if output is not None:
        return output[0]
    else:
        return output


async def fetch_wallet(cursor,  user):
    await cursor.execute('SELECT wallet FROM eco WHERE user_id = :user_id',
                         {'user_id': user})
    return (await cursor.fetchone())[0]


async def fetch_bank(cursor, user):
    await cursor.execute('SELECT bank FROM eco WHERE user_id = :user_id',
                         {'user_id': user})
    return (await cursor.fetchone())[0]


async def delete_user(db, user):
    cursor = await db.cursor()
    await cursor.execute('DELETE from eco WHERE user_id = :user_id', {'user_id': user})
    await db.commit()
    await cursor.close()


async def update_wallet(db, user, wallet):
    cursor = await db.cursor()
    await cursor.execute('UPDATE eco SET wallet = :wallet WHERE user_id = :user_id', {'user_id': user, 'wallet': wallet})
    await db.commit()
    await cursor.close()


async def update_bank(db, user, bank):
    cursor = await db.cursor()
    await cursor.execute('UPDATE eco SET bank = :bank WHERE user_id = :user_id', {'user_id': user, 'bank': bank})
    await db.commit()
    await cursor.close()

yes

it works

@grim vault thx a lot

[hard peak]

I got "flights.csv", Departure time column is written using the 24 hour system, but pandas defaults in recognizing it as an integer since it has no ":" between the hours and minutes.

How do i change the dtype from integers to the 24 hour format within pandas?

*after read.csv

[unkempt prism]

You can create custom date_parser using date_parser method in csv.

On another note please see if you can share the text next time and not a photo. If I had the csv text I'd adapt it within a minimum repeatable example to be more precise to your needs. I'm sure others might be more inclined too.

import io

import pandas as pd

csv_in = """
Location    Date    Price
city 1    01/20/2019    30£
city 1    02/20/2019    25£
city 1    05-21-2019    20£
city 1    09/22/2019    10£
city 2    10/25/2019    5£
city 3    10/27/2019    99£
city 4    11-03-2019    12£
city 3    11-07-2019    100£
city 5    10/24/2019    5£
city 3    10/23/2019    5£"""

def date_parser(date_string, format=None, timezone='Europe/London'):
    return pd.to_datetime(date_string, format=format, utc=False).tz_localize(timezone)


df = pd.read_csv(io.StringIO(csv_in), sep='\t', parse_dates=["Date"], date_parser=date_parser, dayfirst=False)

[hard peak]

Thanks! Sorry for the awful format

[faint pumice]

anybody encountered this error - "ValueError: numpy.ndarray size changed, may indicate binary incompatibility. Expected 96 from C header, got 80 from PyObject"

[unkempt prism]

Don't apologize paste the first few rows LOL

I see now that its multiple columns that need to combine. Not sure if you can pull that off using date_parser.

Might need to be a separate apply function

[hard peak]

Idk if i can share links, but the data set is from kaggle https://www.kaggle.com/datasets/usdot/flight-delays
It's quite huge, here is the data if you are actually interested. Im using this for learning.

[unkempt prism]

I've just learned a better trick then above code sample.

You can combine multiple columns to do the time processing.

I didn't use the full and just created a sample

from datetime import datetime
import io

import pandas as pd

csv_in = """
Year,Month,Day,Booking Time,Paid Time,Timezone,Destination
2017,1,1,0101,0105,Europe/London,London
2017,1,1,1359,1500,Europe/London,Paris
2017,1,1,2300,2309,Europe/London,Dublin
"""

date_parser = lambda Year,Month,Day,Time: datetime.strptime(f"{Year}-{Month}-{Day} {Time}", "%Y-%m-%d %H%M")
date = pd.read_csv(io.StringIO(csv_in), parse_dates={'Booking Date': ['Year', 'Month', 'Day', 'Booking Time'], 'Payment Date': ['Year', 'Month', 'Day', 'Paid Time']}, date_parser=date_parser)

[hard peak]

The io imports the parser function/method needed?

[unkempt prism]

no that is just to fake a csv

[idle moon]

'''py
import pandas as pd
import openpyxl
from openpyxl import load_workbook

from mysql.connector import (connection)

cnx = connection.MySQLConnection(user='root', password='PASSWORD',
host='ACPLPIIOT001',
database='acpls_iiot_cameras')
#cnx.close()

df1 = pd.read_sql('SELECT updatetime FROM imagetemptable', cnx)
Date = df1.values.tolist()
print(Date)
'''
trying to convert dataframe into list but after convertion my datetime object is getting changed to this
[[1655838225000000000], [1655845438000000000], [1654702447000000000], [1655845443000000000], [1655845444000000000], [1655845445000000000], [1655845446000000000], [1655845447000000000], [1655845448000000000], [1655845449000000000], [1655800430000000000], [1655845454000000000], [1655845455000000000], [1655845456000000000], [1655845457000000000], [1655845459000000000], [1655845460000000000], [1655845461000000000], [1655845462000000000], [1655845463000000000], [1655845464000000000], [1655845465000000000], [1637146094000000000], [1653466497000000000], [1653466498000000000], [1655845482000000000], [1655845483000000000], [1655805875000000000], [1640345194000000000], [1655845498000000000], [1655845499000000000], [1655845500000000000], [1655845505000000000], [1655845506000000000], [1655845507000000000], [1655845512000000000], [1655845514000000000], [1655264104000000000], [1655845519000000000], [1655843706000000000], [1655845521000000000]]

[opaque wave]

can someone help me with something very simple?
I have two tables, table A with user, metricA and table B with user, metricB
each user is not unique in neither table and there is some overlap between tableA.user and tableB.user
I want to get the sum of metricA and sum of metricB and do a full outer join
what is the proper way to do it? any pseudocode or logic will help

[buoyant lion]

I am working on a project that scrapes product prices off a website twice a day. There is around 10k products on the website right now. At later stage I want to have an interactive web app where I can see the prices on a graph. Should I store data on something like mongodb or a SQL database? What would be the best approach to this? I am overwhelmed with amount of different databases for a beginner....

[unkempt prism]

Currently your got the whole dataframe tolist you need to select the df column first.

df1.updatetime.values.tolist()

[unkempt prism]

I want to get the ... and do a full outer join
Do you really want to have multiple columns? Or do you want to be able to query as if Table A and Table B were the same table. If they were csv files would you just manually paste the contents of Table B after Table A.

For this you would want a union ( or union all ) and not a join.

Once you complete a union into a subselect you will be able to use group by as if they were a single table.

Select user, sum(metric)
FROM (
  select user, metric FROM TableA
  UNION -- could be UNION ALL
  select user, metric FROM TableB
) as ab
GROUP BY 1

[opaque wave]

I'm not sure what is the more correct approach
TableA should return around 2-3M rows and TableB should have less than that - but overall the tables are over 7M rows
I figured having a wide format saves memory (for not repeating user column)
just to be more precise, I need to groupby user and month for both tables
it's a bit late for me now and can't remember by heart exactly how i did it but it was something like

SELECT COLS
FROM(
SELECT
FROM TABLEA
GROUP BY
WHERE ) AS A
FULL JOIN TABLEB
GROUP BY

^ something like this, but the full join wasn't really working

do you think your approach is the more correct way?

[unkempt prism]

Answer this

Do you really want to have multiple columns? Or do you want to be able to query as if Table A and Table B were the same table. If they were csv files would you just manually paste the contents of Table B after Table A.

[opaque wave]

actually I'm not too sure what you mean, but judging by your code I don't want to sum metricA with metricB

i want users, metricA,metricB

[unkempt prism]

Sorry I was mistaken by the union. Still seems a simple group by though. Worry about getting the results and then worry about memory / performance operations later. That will be SQL engine specific.

[opaque wave]

ok thanks, I'll give your suggestion a try as well - at least it's more readable

[chrome raven]

hey guys i have a quick question

does having 3 different documents ur reading out of have any disadvantages?

Like essentially containers

[trim lintel]

What documents ?

[chrome raven]

I am using cosmodb, i think they are called documents

Like I have one for users, roles, etc.

Would it be wise to combine all this into one document data?

Or having them separate is not a problem?

[trim lintel]

Ok no because it is separate entities so should be separate documents

[chrome raven]

But the roles are related to the users

Like each users have roles[] attribute

still different?

[trim lintel]

I am not familiar so much with cosomo db. Not sure then. I would do what’s easiest for you. In terms of what’s easiest when you plan to retrieve the data and store it.

[chrome raven]

I dont know if opening many containers and reading different files/containers are expensive

or like does it matter if use caching?

[trim lintel]

No not at all. Should be cheap to do. Should not matter.

[chrome raven]

Okay

[chrome raven]

do u know how to implement caching?

[rustic notch]

who ios the best way that i can work with sql database in python? its usings some lib like sqlalchemy? or building my querys with sql?

[elder knoll]

I've got a super simple SQL question if anyone has any time, im trying to create a composite key consisting of two columns in my table but I keep getting an error.

Can anyone see an error in the following

CONSTRAINT GameID PRIMARY KEY (KickoffDateTime, Location)

nvm got it i was being stupid

[nocturne stratus]

You can use SQLite library to connect and work with the SQLite db

[hard peak]

Hi i just tried your code, it works with the sample csv but I notice that it can't handle Nan (none) values

It gives me the ValueError: time data '2015-1-1 nan' does not match format '%Y-%m-%d %H%M'.

Do you reccomend a try&except method?

[candid swallow]

i am new to cmd,
i have setup wsl for windows and was setting up this app on local from github following instruction.

so far i have install rvm, ruby,
some pre-requisited
then i setup postresql

my next step was to "Setup Postgres to use passwords instead of ident" and included following steps:

su - postgres
cd /var/lib/pgsql/data
vim pg_hba.conf
sudo systemctl restart postgresql
exit

su - postgre    cd /etc/lib/postgresql/##/main   
vim pg_hba.conf
logout
sudo systemctl restart postgresql

on step 2 i am unable to locate the file

i mean there isnt pgsql in var/lib
i tried $ dir
it doesnt have pgsql

[dull scarab]

Been beating the bush a bit with this myself, so figured i'd try here.
I'm trying to setup a Row level security system using a modified preorder tree traversal model.
Currently struggling with creating a filter to I can use in a get all this user has read permissions for
(MSSQL)
Tables in the picture,
Attempts ive made that are way too big to be used, and only works in a "Does this user have access to this row", while I want more of a "Get All rows this user has access to",
https://paste.pythondiscord.com/ijerujayis

So the question is how would I go about writing a where clause for this, or alternatively approach it with a temp table to inner join with

[lost summit]

Hello,
i have a Django Modell that has object_id as Primary Key and this is a CharField. I use graphene for API and when i create everything works until graphql has to return something, then it says cannot convert string to float. But i want the primary key to be a string not a float also when graphene returns it

[celest field]

hi, i just started sql. whats the common practice on when i should create a new table vs when i should create a new database file?

[polar scarab]

anyone knows the best practices to collect data ?

its for my internship

I'm still new ..

[whole mica]

i am using mongodb
and there are about 20 fields in every data
how to do the covered query if i only want to include _id field

do i just do db.find({}, {"_id": 1}) and it will only get _id back
or do i need to make it db.find({}, {"_id": 1, "a": 0, "b": 0, "c": 0 ...}) to exclude every other things?

[candid swallow]

did sudo apt-get install postgresql postgresql-contrib libpq-dev

but then this:

[proven dock]

What type of data u want to collect please elaborate.

[proven dock]

It's fully depend on u and ur project, acc to me, u should create new database when u are creating new project otherwise use tables

[ionic pecan]

where did you find that?

if you're on debian, then installing postgresql will create a cluster for you

check pg_lsclusters

[candid swallow]

i am following setup procedure of an app, as asked by my professor

i am using wsl

[civic gorge]

guys i've been searching for an answer to this
Where is temp_tables are stored ? is it RAM or disk ?

[storm mauve]

from the first result googling are temp_tables stored in ram or disk: https://stackoverflow.com/questions/45380231/sql-server-are-temp-tables-stored-in-ram-or-on-disk
seems like it's always on disk, which makes sense, but you might want to check the other questions linked from there - https://dba.stackexchange.com/questions/16385/whats-the-difference-between-a-temp-table-and-table-variable-in-sql-server / https://stackoverflow.com/questions/11857789/when-should-i-use-a-table-variable-vs-temporary-table-in-sql-server

[civic gorge]

in some articles i've seen some say that it is stored in memory but it can overflow to disk

i cant seem to find a definitive answer

[storm mauve]

huh, it's possible that it depends on the database

[civic gorge]

seems like that is the case

yet, i believe it is definitive that view tables are always stored in RAM correct?

[storm mauve]

tbh I have no idea

[civic gorge]

no worries, im thankful for your help

[acoustic cave]

Hey, I'd like to ask about how to handle message storing in a relational db.
I have a sort of a team structure which will have private messages and don't know if the best thing will be to store all the messages in one table?
Perhaps someone could give me some advice!

[torn sphinx]

Does someone know mysql? i am working on final project for college and 1 thing is stoping me few days, everything working correctly but when i wanna import photo to be here it showing me this code or whatever it is, checked multiple times mysql and i dont know where is problem.

[torn sphinx]

trying to save photo in database? i think you should convert it first into a text format like base64 then save that and on load load that but a better approach would be to save the file somewhere and save a path to the file in the database

[torn sphinx]

thanks, will try right now

[torn sphinx]

i tried downloading into base64 its still same

no idea what i should do next and this is my final project

save it as a file and load that instead?

[delicate field]

Hey @torn sphinx!

You either uploaded a .txt file or entered a message that was too long. Please use our paste bin instead.

[torn sphinx]

is it good?

i did like this

thats what i did with my db projects, clearly doesnt feel good but that might be the best solution

[torn sphinx]

maybe youre not displaying the image correctly and its not an issue how you are saving it?

i checked what my profesor did, i did same and my friend was looking over 1 hour and he didnt found anything

cant fix more then 5 or 6 days

[torn sphinx]

and when photo is working in code it show only name last name and born date, after that its not working

[torn sphinx]

are you sure displaying image works? try displaying an image that you know will work

mysql

[torn sphinx]

thats problem, no images are working

i tried many and nothing works

sorry i got no idea how tkinter or what that is, works

its pysimplegui

[torn sphinx]

if your project isnt bound to be in python then i recommend to switch to javascript/electron since your gui will look so much better

i dont know both of that :v

[torn sphinx]

i recommend learning them if youre interested in beautiful desktop applications (discord is built with it) but if you just want to finish your project quick then stick to python and the lib youre using but this isnt the correct channel to figure out how to display an image in pysimplegui

[torn sphinx]

i wanna finish this project but i cant

because i dont know where did i missed something

did everything i could think of and still not working

[brave bridge]

what's the point of base64 though? 🤔

you can save binary data to a database

[torn sphinx]

@brave bridge so the text at the top of the screenshot doesnt happen

still have no idea what to do after all this days trying to fix it

any photo i wanna upload is not working

[bleak bough]

how do i export or print a table in readable format using sqlite

turning on headers and putting it in column mode in python?

[torn sphinx]

this is my problem, With the attached ”pratise.sql” script, delete duplicate rows based on the ”url” column
(leaving only the row with the lowest ID), and update its column ”count” to have the value of
its highest duplicate row ID.
ID First Name Count Url
1 A 10 www.A.com
2 B 21 www.B.com
3 C 12 www.C.com
4 D 31 www.D.com
5 A 13 www.A.com
6 D 18 www.D.com
7 A 5 www.A.com
i need output;
ID First Name Count Url
1 A 5 www.A.com
2 B 21 www.B.com
3 C 12 www.C.com
4 D 18 www.D.com
the quries in the .sql file

[celest field]

ive read that sqlite is quite limited in the number of transactions per second. is that something i should worry about now? or is it only a problem if the program is really large scale

[grim vault]

Shouldn't the result be: sorry misread

[grim vault]

I thought about it, and I would use a temp table to get the needed count values, delete the duplicates and than set the count values after that.

[torn sphinx]

but, it not correct way, know sir

[unkempt prism]

Instead of a lambda like the earlier example you can use a regular function with the same params and then use try / except and whatever you need.

[grim vault]

What would be the correct way?

[torn sphinx]

does someone know why i cant upload some pictures and some i can?

example some png photos work and some dont

[willow sequoia]

The most likely reason is that your image column in the DB is set as "allow nulls" false.

If you do not provide an image with each new row you try to create, the SQL engine will not allow the row to be created, and will throw an exception.

[fading patrol]

What data type is the field? It should probably be some sort of BLOB https://www.tutorialspoint.com/What-is-BLOB-data-type-in-MySQL

[coarse crown]

Hello everyone... I have to look for info in a spreadsheet in excel. I have to find situations where a value in a cell changes, but it's kinda endless to do that by just scrolling. Any tips |:?

[torn sphinx]

python3 main.py
Traceback (most recent call last):
File "main.py", line 6, in <module>
from discord.ext import commands
ImportError: cannot import name 'commands' from 'discord.ext' (unknown location)

anyone?

[torn sphinx]

it is blob, i fixed it

[vivid pecan]

For a Uni exercise I need to explain why it is absolutely necessary to do REDO before doing UNDO in database recovery. I was searching for this question today but I only found that it may be necessary if there is a crash while recovering but I don’t understand the exact reasoning. Can anyone help with this?

[fading patrol]

Don't reinvent the wheel if your goal is to have a ticketing system there are free ones already available.

If you want to build one anyway, I would say first play around with sqlite3 to get the basics of database work, but if you really want to build your own ticketing system you'll also need a web framework like Django and a real database like Postgres. If you've never worked with a DB before it's going to be a long journey before you can design a functional ticket system, but if your main goal is learning and your not in a hurry, go for it

If you want a copy-and-paste tutorial, there are probably others, here's one: https://medium.com/@gingerkirsch/basic-web-app-with-django-1cd947d16240

yes, that tutorial uses a sqlite db

[hidden wedge]

you could enroll in a courses on udemy dm me if you need some courses. but you must be commited to not waste your. own time. also if db managment and engineering inst your main focus done reinvent the wheel

i do ive taken a whole course on it my depth is pretty well roounded

hm as in how much?

@torn sphinx

do you need a course link?

[hidden wedge]

https://www.udemy.com/course/complete-sql-databases-bootcamp-zero-to-mastery/ use a private browser it always gives cheaper deal

[faint elm]

keep getting mongo timeout errors

and statusgator says mongodb is down? but cant find anything else?

[tranquil totem]

before:```py
"_id" : guild.id,
"sticky_messages" : [
{
"channel_id" : 1,
"message_id" : 2
},
{
"channel_id" : 3,
"message_id" : 4
}
]

after:```py
"_id" : guild.id,
"sticky_messages" : [
    {
        "channel_id" : 1,
        "message_id" : 9
    },
    {
        "channel_id" : 3,
        "message_id" : 4
    }
]

How would I do this using motor and pymongo?

I changed the message_id for the record that has channel_id == 1

[faint elm]

https://statusgator.com/services/mongodb

does this have anything to do with my mongo timeout error

[delicate field]

Hey @torn sphinx!

You either uploaded a .txt file or entered a message that was too long. Please use our paste bin instead.

[torn sphinx]

hey could someone help me with reading a writing files in python

[molten gazelle]

CREATE TABLE product (
    id bigserial  NOT NULL,
    country_id int8  NULL,
    description text  NULL,
    weight_packaging real  NULL,
    brand_id int8  NULL,
    CONSTRAINT product_id PRIMARY KEY (id)
);
CREATE INDEX country_id on product (country_id ASC);
CREATE INDEX product_idx_2 on product (brand_id ASC);

-- Table: product_related
CREATE TABLE product_related (
    product_id int8  NOT NULL,
    related_id int8  NOT NULL,
    CONSTRAINT product_related_pk PRIMARY KEY (product_id,related_id)
);

CREATE INDEX product_related_idx_1 on product_related (product_id ASC);
CREATE INDEX product_related_idx_2 on product_related (related_id ASC);

-- Table: related
CREATE TABLE related (
    id bigserial  NOT NULL,
    name text  NOT NULL,
    CONSTRAINT related_pk PRIMARY KEY (id)
);

-- Table: feature
CREATE TABLE feature (
    id bigserial  NOT NULL,
    name text  NOT NULL,
    CONSTRAINT feature_pk PRIMARY KEY (id)
);

-- Table: product_features
CREATE TABLE product_features (
    product_id int8  NOT NULL,
    feature_id int8  NOT NULL,
    CONSTRAINT product_features_pk PRIMARY KEY (product_id,feature_id)
);

CREATE INDEX product_features_idx_1 on product_features (product_id ASC);
CREATE INDEX product_features_idx_2 on product_features (feature_id ASC);

-- Reference: product_features_feature (table: product_features)
ALTER TABLE product_features ADD CONSTRAINT product_features_feature
    FOREIGN KEY (feature_id)
    REFERENCES feature (id)  
    NOT DEFERRABLE 
    INITIALLY IMMEDIATE
;

-- Reference: product_features_product (table: product_features)
ALTER TABLE product_features ADD CONSTRAINT product_features_product
    FOREIGN KEY (product_id)
    REFERENCES product (id)  
    NOT DEFERRABLE 
    INITIALLY IMMEDIATE
;

class Product(Base):
    __tablename__ = 'product'

    id = Column(BigInteger, primary_key=True, server_default=text("nextval('product_id_seq'::regclass)"))

    country_id = Column(ForeignKey('country.id'), index=True)
    description = Column(Text)
    weight_packaging = Column(Float)
    brand_id = Column(ForeignKey('brand.id'), index=True)

    brand = relationship('Brand')
    country = relationship('Country')
    relateds = relationship('Related', secondary='product_related')

t_product_features = Table(
    'product_features', metadata,
    Column('product_id', ForeignKey('product.id'), primary_key=True, nullable=False),
    Column('feature_id', ForeignKey('feature.id'), primary_key=True, nullable=False)
)


t_product_related = Table(
    'product_related', metadata,
    Column('product_id', ForeignKey('product.id'), primary_key=True, nullable=False, index=True),
    Column('related_id', ForeignKey('related.id'), primary_key=True, nullable=False, index=True)
)

class Feature(Base):
    __tablename__ = 'feature'

    id = Column(BigInteger, primary_key=True, server_default=text("nextval('feature_id_seq'::regclass)"))
    name = Column(Text, nullable=False)

    products = relationship('Product', secondary='product_features')

class Related(Base):
    __tablename__ = 'related'

    id = Column(BigInteger, primary_key=True, server_default=text("nextval('related_id_seq'::regclass)"))
    name = Column(Text, nullable=False)

IT IS DRIVING ME NUTS
Why the product has the relateds relationship and not features??
I am using sqlacodegen
I have been stuck in this for 3 days

[grim vault]

Just a guess because I don't know sqlacodegen: Maybe it's a name thing? The many to many table is called product_features but the table is called feature (which is not the case for the related tables). It shouldn't matter because of the FK definitions but who knows, maybe worth a try.
I tried the name thing and it's still the same. Looks like an alphabetical processing order. It first generates the Feature class with the products relation and because of that the features relation is not in the Product class.

[grim vault]

If I rename product to commodity it works as expected (simplified database):

# coding: utf-8
from sqlalchemy import Column, ForeignKey, Integer, Table, Text
from sqlalchemy.orm import relationship
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()
metadata = Base.metadata


class Commodity(Base):
    __tablename__ = 'commodity'

    id = Column(Integer, primary_key=True)
    description = Column(Text)

    features = relationship('Feature', secondary='commodity_features')
    relateds = relationship('Related', secondary='commodity_related')


class Feature(Base):
    __tablename__ = 'feature'

    id = Column(Integer, primary_key=True)
    name = Column(Text, nullable=False)


class Related(Base):
    __tablename__ = 'related'

    id = Column(Integer, primary_key=True)
    name = Column(Text, nullable=False)


t_commodity_features = Table(
    'commodity_features', metadata,
    Column('commodity_id', ForeignKey('commodity.id'), primary_key=True, nullable=False),
    Column('feature_id', ForeignKey('feature.id'), primary_key=True, nullable=False)
)


t_commodity_related = Table(
    'commodity_related', metadata,
    Column('commodity_id', ForeignKey('commodity.id'), primary_key=True, nullable=False),
    Column('related_id', ForeignKey('related.id'), primary_key=True, nullable=False)
)

[fair girder]

quick q how is subtracting from/by null works in sql?

[grim vault]

from SQLite docs:

All operators generally evaluate to NULL when any operand is NULL, with specific exceptions as stated below. This is in accordance with the SQL92 standard.
There may be some other specific behaviour.

[narrow saffron]

Let's say I have a table called Foods and I have another table called Variations I want to add a few variation to food records in the form of foreign keys, but how do i reference more than one record of Variations from a single Foods record, can foreign keys be multivalue?

[limpid portal]

hello guys i hope you are doing good.

[chrome raven]

Hello, has anyone ever implemented Redis into their databases? trying to figure out how to combine Redis + cosmosDB

[torn sphinx]

Anyone have any idea why radius is returning null

[fading patrol]

Yes, look closely at line 13, you have UserID where you should have radius

[torn sphinx]

OH i am a wally haha

By the way

Does anyone know the statement for ALTER COLUMN in sqlite3

c.execute("ALTER TABLE lb MODIFY score integer")

Ive tried

MODIFY, MODIFY COLUMN, and ALTER COLUMN

[fading patrol]

Looks like this is a major limitation of SQLite and you basically have to recreate the table: https://stackoverflow.com/questions/4007014/alter-column-in-sqlite

[torn sphinx]

shambles

had a gut feeling i shouldve done a backup

maybe i should learn something new]

different sql module for python

[shy night]

how to connect my bot to mongobd ?

[broken gazelle]

hi hi ! i'm new to sql and databases in general
could someone help me with how i can drop the second row of the column daily_seq? I don't have a primary key since it's only one row that I will be updating (instead of making new ones) and I added the 2 on accident.

[unkempt prism]

What you can select you can delete. (unless there is a foreign key referencing that column)

select daily_seq FROM label where daily_seq = 2

You can then change select daily_seq to delete

[broken gazelle]

ahh i see, thanks so much for the help ^^

[late nova]

i'm using asyncpg for a discord bot database, and i can't figure out why im getting a certain error:
asyncpg.exceptions.InvalidPasswordError: password authentication failed for user "humble"

the user humble doesn't have a password, the default user postgres i've also tried it's password is "password" and i've tried with both users and no matter what i do try i always get the same error, i am on windows and don't know what else to try

[torn sphinx]

is there a library for sql to use "chained" commands instead of writing long raw SQL queries?

[fading patrol]

Any ORM could help you do that, look into SQL Alchemy if you haven't yet. Pure SQL might be more efficient but an ORM is generally easier if you're more comfortable with Python and you're doing complex stuff

[subtle needle]

mongodb

how can i get the number of records that have the same userid values?

[subtle needle]

mention me

[delicate tangle]

Hi, So I am trying to make a Chat-Bot in Terminal with Python, How do I make it so when I get a input, It will update the value in the JSON file

Like for example enter name, And the name value will be added to JSON file

[umbral lion]

just use a websocket 🤷‍♂️

or host a database on aws and fetch data from there on a time based interval

[fading patrol]

A disadvantage of using a JSON file instead of a database is that you have to replace the file. It's almost as simple as json.dump but I wouldn't recommend it. sqlite3 seems more appropriate

[scenic fable]

first time ever using dicts, using what copilot shat out. whats the issue here?

actually its probably best to use a json or a db

but for now ill use a json since its easy to use

[unkempt prism]

that is not a valid dict. Wrap it into another

insert_one({ your current contents })

This would be much easier to correct if you pasted the code formatted and not the image.

[scenic fable]

oh

[frail zodiac]

what would be the best type of db for a discord bot written in node.js where I want to store key and value pairs? It's hosted on a RPi4.

[fringe sundial]

.

[torn sphinx]

peek-a-boo

[fringe sundial]

What?

[fringe sundial]

You mean smell?

[torn sphinx]

no-

nvm

[fringe sundial]

Anyway

Sqlite or postgresql?

[torn sphinx]

can help you with sqlite only, dont have much experience with postgresql

[fringe sundial]

Do i need to install termux?

[torn sphinx]

no?

[fringe sundial]

Ok

So me get started with sqlite

Do i need to install it

For Android

[torn sphinx]

no

just an async driver

!pip aiosqlite

[delicate field]

aiosqlite v0.17.0

asyncio bridge to the standard sqlite3 module

[fringe sundial]

So i install it

pip install aiosqlite in replit shell

[torn sphinx]

yeah

[fringe sundial]

Done

[torn sphinx]

do you know how to use sqlite...?

[fringe sundial]

Sorry no

I never used a db I mean a cloud db

[torn sphinx]

sqlite isnt cloud...

[fringe sundial]

Si

Local database?

[torn sphinx]

yes

[fringe sundial]

So i installed what's next step

Don't we need an token to connect with db?

Ig

[torn sphinx]

see syntax for the basic CRUD options for sqlite

[torn sphinx]

yesn't?

[fringe sundial]

# Import the sqlite3 module
import sqlite3

# Setup a connection with our database file
connection = sqlite3.connect("myDatabase.db")

# Create a cursor for the database to execute statements
cursor = connection.cursor()

# Execute a statement
cursor.execute("{{SQL STATEMENT}}")

# Save + close the database, never skip this
# or nothing will be saved!
connection.commit()
connection.close()

I got this code from google

[torn sphinx]

CRUD

operations

never told you to see that shit

[fringe sundial]

@torn sphinx you mean this?

[torn sphinx]

yes

[fringe sundial]

I read it

How can I connect to db now?
Means i don't have any database key or token or any name

[torn sphinx]

nobody told you to

smh

what dpy are you using?

version

[fringe sundial]

2

2.0

[torn sphinx]

nicr

nic

nice

are you subclassing Bot or not?

[fringe sundial]

No

Idk how to

[torn sphinx]

okay cool so

does it looks something lile this?

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

[fringe sundial]

Ye

;-;

Me a noob
How you expected this from me?

[torn sphinx]

create a function that connects to the db with aiosqlite and overwrite the setup_hook method of the Bot class with that function

tbh I recommend subclassing

but nvm

[fringe sundial]

Ok I'll do it

[torn sphinx]

dont

i forgot that it's replit

on mobile

wont give you many suggestions

import aiosqlite

async def my_hook():
    bot.conn = await aiosqlite.connect("name.db")
    bot.cur = await bot.conn.cursor()

bot.setup_hook = my_hook

that's how you create a global connection

and you can probably make multiple cursors

[fringe sundial]

But i don't have any db

[torn sphinx]

....

it'll make one for you

am not crazy

[fringe sundial]

How will it make

[torn sphinx]

....

[fringe sundial]

Means like any location

[torn sphinx]

the base dir

[fringe sundial]

Ok

[torn sphinx]

you can specify a whole path

if you require

[fringe sundial]

In my phone storage?

[torn sphinx]

no?

on replit

smh

[fringe sundial]

@torn sphinx

import discord 
import aiosqlite
from discord.ext import commands

bot = commands.Bot(commands_prefix = '>')

@bot.event
async def on_ready():
    print("ready")
    async with aiosqlite.connect("prefixes.db") as db:
        async with db.cursor() as cursor:
            await cursor.execute('CREATE TABLE IF NOT EXISTS prefixes (prefix TEXT, guild ID)')

Is it correct?

[torn sphinx]

I'm out

[fringe sundial]

?

[torn sphinx]

what I did and what you did

[fringe sundial]

Hmmmmmm

[torn sphinx]

I made a global connection not connecting every time

[fringe sundial]

Ok

I'll do your one

[torn sphinx]

nice

and if you want to execute the sql

do it within setup_hook

too

[fringe sundial]

import discord 
import aiosqlite
from discord.ext import commands

bot = commands.Bot(commands_prefix = '>')

async def my_hook():
    bot.conn = await aiosqlite.connect("name.db")
    bot.cur = await bot.conn.cursor()

bot.setup_hook = my_hook 

Now?

[torn sphinx]

yes you can use await bot.cut.execute(...) to create the table here

dont do shit inside on_ready

[fringe sundial]

Ok

import discord 
import aiosqlite
from discord.ext import commands

bot = commands.Bot(commands_prefix = '>')

async def my_hook():
    bot.conn = await aiosqlite.connect("prefixes.db")
    bot.cur = await bot.conn.cursor()
    await bot.cur.execute('CREATE TABLE IF NOT EXISTS prefixes (prefix TEXT, guild_id TEXT)')
    await bot.conn.commit()

bot.setup_hook = my_hook

I did like this

[fringe sundial]

I give up making it
It's very hard

[torn sphinx]

...?

hard..?

[fringe sundial]

Yes

Very hard

[shy forum]

hi guys !

[fringe sundial]

Hi

[shy forum]

So I made it into my request and I want to verify if it's well returning the right result:
SELECT * FROM tableA WHERE value1 < 3 AND NOT EXISTS(SELECT * FROM tableB where tableA.id=tableB.id);

Basically, I need to take all the entries in table A where value1 is less than 3 and id does not exist in table B

[fringe sundial]

import discord 
import aiosqlite
from discord.ext import commands

bot = commands.Bot(commands_prefix = '>')

async def my_hook():
    bot.conn = await aiosqlite.connect("name.db")
    bot.cur = await bot.conn.cursor()

bot.setup_hook = my_hook  

Here is my start code

[uneven sinew]

mm ok

[fringe sundial]

Is it correct?

[uneven sinew]

so what's the issue

try it for urself

[fringe sundial]

Me not understanding what to do next

[uneven sinew]

this code is correct, just the query is not

(prefix TEXT, guild ID)
replace with this
(prefix TEXT, guild_id TEXT)

[fringe sundial]

Me dont know what to do next 😢

@uneven sinew
https://youtu.be/ZTbCowEwQtA
I found this video should I try this code?

[torn sphinx]

I think I should create a detailed walkthrough for sqlite with discord bots

[uneven sinew]

do it

[fringe sundial]

I am really sorry if I disturbed

[uneven sinew]

seems good

[fringe sundial]

Me try it

[torn sphinx]

will do, after I extend the slash commands walkthrough

[torn sphinx]

nah

[fringe sundial]

Are you making documentation?

[uneven sinew]

i will write tests for your markdown ||/j||

[torn sphinx]

no, I have a guide for slash commands with discord.py (see latest pin in #discord-bots), so am just extending that guide

[uneven sinew]

wtf you wrote that

[torn sphinx]

wrote what?

the gist?

yes I did

[fringe sundial]

@torn sphinx whats use of ? in sqlite?

[uneven sinew]

to replace the value

or, put the value there

[fringe sundial]

Oh

I wrote some of code

import discord
from discord.ext import commands
import aiosqlite 

bot = commands.bot(command_prefix=">")

@bot.event
async def on_ready():
    async with aiosqlite.connect("prefixes.db") as db:
        async with db.cursor as cursor:
            await cursor.execute('CREATE TABLE IF NOT EXISTS prefixes (prefix TEXT guild_id TEXT)')

@bot.command()
async def set_prefix(ctx,prefix=None):
    if prefix is None:
        return
    async with aiosqlite.connect("prefixes.db") as db:
        async with db.cursor as cursor:
            await cursor.execute('SELECT prefix FROM prefixes WHERE guild_id = ?',(ctx.guild.id,))
            data = await cursor.fetchone()
            if data:
                await cursor.execute('UPDATE prefixes SET prefix = ? WHERE guild_id = ?')

[uneven sinew]

"INSERT INTO x VALUES (?)", (2,)
will turn into
INSERT INTO x VALUES (2)

[uneven sinew]

ohh nice

[fringe sundial]

Am I doing correct?

[uneven sinew]

yes

well, I think so, I haven't looked deep into the code

[fringe sundial]

Will database reset when i run code again?

[uneven sinew]

no

[fringe sundial]

No need just look from outside

[uneven sinew]

🤣

[fringe sundial]

The console will look into it

[torn sphinx]

placeholder for values which can be passed to a tuple later

[fringe sundial]

Oh

Like we format

Like one of formatter in python

[torn sphinx]

...?

[uneven sinew]

they're talking about string formatting 🤦

big no

!sql-injection

there was a cmd for that

[fringe sundial]

There is one string formatter in which we use place holder

[uneven sinew]

No don't do that

[fringe sundial]

Ik

[uneven sinew]

Don't

[fringe sundial]

Just example

[uneven sinew]

yeah ik

[fringe sundial]

F string better

[uneven sinew]

NO

NO

NOT FOR SQL

[fringe sundial]

I always use f strin

[uneven sinew]

NO

[torn sphinx]

should I really make a gist for sqlite with discord bots 😔 seems a lot of work but is required

[fringe sundial]

Bruh

I tell for python

Not for sql

😂

[uneven sinew]

No

Like for sqlite

You should not use string formatting

[fringe sundial]

Ik bro

I was just giving example using python

[uneven sinew]

yessssssssss

[uneven sinew]

ok just make sure you don't do something like
f"INSERT INTO users VALUES ({id})"

[fringe sundial]

Hard
Make a youtube channel :)

[torn sphinx]

await Cursor.execute("uwuw query ?", ("uwu_placeholder_value")) -> "uwuw query uwu_placeholder_value"

is how you pass in parameters

[uneven sinew]

what sql dialect is that, i want to learn

[grim vault]

looks ok, but I would do it like:

SELECT * FROM tableA WHERE value1 < 3 AND tableA.id NOT IN (SELECT tableB.id FROM tableB);```

[fringe sundial]

I dont even know much about SQL
Do you expect this from me?

[torn sphinx]

wtf, so you cant read but can listen?

[fringe sundial]

Idk

[uneven sinew]

!sql-fstring

[delicate field]

SQL & f-strings
Don't use f-strings (f"") or other forms of "string interpolation" (%, +, .format) to inject data into a SQL query. It is an endless source of bugs and syntax errors. Additionally, in user-facing applications, it presents a major security risk via SQL injection.

Your database library should support "query parameters". A query parameter is a placeholder that you put in the SQL query. When the query is executed, you provide data to the database library, and the library inserts the data into the query for you, safely.

For example, the sqlite3 package supports using ? as a placeholder:

query = "SELECT * FROM stocks WHERE symbol = ?;"
params = ("RHAT",)
db.execute(query, params)

Note: Different database libraries support different placeholder styles, e.g. %s and $1. Consult your library's documentation for details.

See Also
• Extended Example with SQLite (search for "Instead, use the DB-API's parameter substitution")
• PEP-249 - A specification of how database libraries in Python should work

[fringe sundial]

Me listening music and coding 😂

[uneven sinew]

@fringe sundial

[fringe sundial]

Always listen music while coding
It makes us relax

[torn sphinx]

hhm, I'm done updating the slash commands gist, time for the other one ig

[torn sphinx]

relatable 🫂 I got music cranked up at 70 volume on my alexa

I'm rich /s

[fringe sundial]

Me on blue tooth speaker at 100 volume and i can hear nothing 😂

Only music everywhere

[torn sphinx]

you just said you can't hear nothing, contradictory
nvm

this is becoming ot again

[uneven sinew]

oh yeah uh

database

postgres sqlite yeahhh

sql injection badd

[fringe sundial]

Me doing wrong ig

[uneven sinew]

!d discord.ext.commands.Context

[delicate field]

discord.ext.commands.Context

class discord.ext.commands.Context(*, message, bot, view, args=..., kwargs=..., prefix=None, command=None, invoked_with=None, invoked_parents=..., invoked_subcommand=None, ...)```
Represents the context in which a command is being invoked under.

This class contains a lot of meta data to help you understand more about the invocation context. This class is not created manually and is instead passed around to commands as the first parameter.

This class implements the [`Messageable`](https://discordpy.readthedocs.io/en/latest/api.html#discord.abc.Messageable "discord.abc.Messageable") ABC.

[fringe sundial]

I wrote ctx.guild.id everywhere

[uneven sinew]

that's ok lol

[fringe sundial]

Is it correct?

[uneven sinew]

ye

[fringe sundial]

Ok

I did a lot of progress!

import discord
from discord.ext import commands
import aiosqlite 

bot = commands.bot(command_prefix=">")

@bot.event
async def on_ready():
    async with aiosqlite.connect("prefixes.db") as db:
        async with db.cursor as cursor:
            await cursor.execute('CREATE TABLE IF NOT EXISTS prefixes (prefix TEXT guild_id TEXT)')

@bot.command()
async def set_prefix(ctx,prefix=None):
    if prefix is None:
        return
    async with aiosqlite.connect("prefixes.db") as db:
        async with db.cursor as cursor:
            await cursor.execute('SELECT prefix FROM prefixes WHERE guild_id = ?',(ctx.guild.id,))
            data = await cursor.fetchone()
            if data:
                await cursor.execute('UPDATE prefixes SET prefix = ? WHERE guild_id = ?',(prefix,ctx.guild.id))
                await ctx.send(f'Updated prefix to `{prefix}` ')
            else:
                await cursor.execute('INSERT INTO prefixes (prefix, guild_id) VALUES (?,?)',('>',ctx.guild.id,))

[uneven sinew]

Nccc

[fringe sundial]

Me just writing it in another file

[uneven sinew]

Also you made typo in commands.bot

[fringe sundial]

And I'll paste it into my main file after writing al code

[uneven sinew]

its supposed to be commands.Bot

[fringe sundial]

It's just a test file
I'll just copy command and paste in main file

[fringe sundial]

I just put it cause everything was showing red

[shy forum]

ohhhh ok

[vocal fern]

hey, how do i check if the account number entered by a user exists in the table (using def)

[grim vault]

It depends on your dataset. If tableB is very large, building the list might take longer. And if tableA isn't that large it might be faster to check each id with the exists. I would also not use * as columns in an exists select, it doesn't need any data only if it's successful or not. I mostly use 'X', like

SELECT *
  FROM tableA
 WHERE value1 < 3
   AND NOT EXISTS (SELECT 'X' FROM tableB WHERE tableB.id = tableA.id);

[shy forum]

it's a 5k entries table so it's fine :D

[shy forum]

although thanks a lot for these usefull tips my friend !

[fringe sundial]

@torn sphinx
https://paste.pythondiscord.com/isukaneley
Is it correct?
Pls tell

[torn sphinx]

it is fine, but I cant say that it is good

[fringe sundial]

Will it work?

[torn sphinx]

am I an error catcher or what?

[fringe sundial]

Just asking for like (idk what we say it) for my mind

[torn sphinx]

..?

assurance?

try it?

[fringe sundial]

Ok me try the code

[torn sphinx]

though the code is really bad, I cant say nothing

[fringe sundial]

For my level it's good

[torn sphinx]

doesn't matter on level, it's gonna error sometime

[fringe sundial]

But i have no other option

[torn sphinx]

you just dont wanna apply brain

¯_(ツ)_/¯

[fringe sundial]

Me dont have

@torn sphinx i got this error :(

[torn sphinx]

db.cursor()

[torn sphinx]

I saw

dw

[fringe sundial]

There should be a code generator :)

[torn sphinx]

dude

[fringe sundial]

Me really sad cause my code not working

[torn sphinx]

cool

error

?

[fringe sundial]

I want to give up really

Sqlite very hard

[torn sphinx]

postgreSQL is even harder :)

[fringe sundial]

🥵

[torn sphinx]

if you think sqlite is hard, you're nowhere to be called an average programmer

[fringe sundial]

Me dont even know java

So how me average

[torn sphinx]

java isnt related to sqlite-

[fringe sundial]

But related to programmer

[torn sphinx]

still

[fringe sundial]

Me a noob programmer 😢

Idk what to do now

Ig leave sqlite

[torn sphinx]

what part of sqlite are you even finding hard

[fringe sundial]

Making prefixes thing

[torn sphinx]

you do know that more than one trillion applications use sqlite

¯_(ツ)_/¯

how you gonna use other SQL databases if you find sqlite hard

[torn sphinx]

"part" not what you are making

[fringe sundial]

But me don't even know about startup of sqlite

[torn sphinx]

startup?

[fringe sundial]

Should I start making again?

[torn sphinx]

make what?

[fringe sundial]

Custom prefix

With my own Brian

[torn sphinx]

at least learn sqlite first ¯_(ツ)_/¯

[fringe sundial]

@torn sphinx

Whats this ?

[torn sphinx]

how should I know?

it's the data you put in the db file, most probably the tables you created

[fringe sundial]

This file name is prefixes.db

[fringe sundial]

But why i not able to see any row or column?

[torn sphinx]

||Privacy||

sqlite automatically encodes data for you

so that nobody else steals the data

but still having a sqlite database on replit

have fun

keeping your data safe

[waxen finch]

its more that sqlite uses a binary format that's more efficient, making it pretty hard to read with a plain text editor

[torn sphinx]

that too

[fringe sundial]

You mean aiosqlite?

But there is no tutorial on aiosqlite

[torn sphinx]

just learn sqlite 😐 aiosqlite is nothing but the async version of sqlite3

[fringe sundial]

Anyway I read the documentation and understand much of it of sqlite3 module

[torn sphinx]

eh reading their official docs?

[fringe sundial]

No on readthedocs website

Should I start making now?

I want a little help if you can?

[torn sphinx]

mhm

sure

[fringe sundial]

async def my_hook():
    bot.conn = await aiosqlite.connect("name.db")
    bot.cur = await bot.conn.cursor()

Is it important to make this?

[torn sphinx]

very, since it creates a single cursor, but if you wanna make custom prefixes.....hm

lemme see

just a minute

sheesh now am super confused

might as well try asking in #discord-bots

lmao

[fringe sundial]

First time I saw your brain failed but ||it happened with me more than 1 million times ||

[uneven sinew]

who pinged me

[austere portal]

Will a many-to-many relationship be good for user-friends?
(A user will be able to friend another user)

[austere portal]

I am asking this for a second opinion

[fading patrol]

100% yes... A user has many friends and many users can have the same friend

[austere portal]

thanks 👍

[vapid arch]

how do i copy the red table so when i paste it to another sheet it wont just be #REF!

all the cells there are =VLOOKUP functions except the ID colum

[quick chasm]

Hello

Cab somebody gimme a good introduction into data basas

Bases*

A link or smth

[icy oyster]

SELECT userid, tolerance, etc FROM UserConfig WHERE bybit_userid = ? AND tolerance = ?;''', bybit_userid, tolerance)
is this a thing in databases?

the AND

[lean olive]

yes

[fading patrol]

https://www.howtoexcel.org/copy-paste-values/

[fading patrol]

It would help if you can be more specific, but I like the SQL tutorials on DataCamp

[broken gazelle]

what datatype should i set for my column if i plan on storing python list objects on there?

(i'm using postgresql)

[fading patrol]

You probably shouldn't, but JSONB is probably best if you must

[remote latch]

when you switch computers does all your sqlite3 data get deleted

[brave bridge]

If you're storing the data on disk, then no, it doesn't

that's the point of a database 🙂

(but you can do sqlite3.connect(":memory:") and you'll have an in-memory database which will, of course, disappear when the program stops)

[fading patrol]

Unless you're doing the `memory' thing, a SQLite db lives in a file... Don't delete the file and your data should be there

[remote latch]

wait so it'd be fine as long as the python file with it is transferred between computers?

[uneven sinew]

you will need the database file and the script to copy

[vapid arch]

thanks

[broken gazelle]

why is it not recommended though?
in that case whats the preferred way of storing arrays in an sql db?

[paper flower]

Usually you can use relationships to store array-like objects, otherwise if your data is simple (e.g. just a list of numbers) you can use built-in postgres arrays or json

[peak stag]

I'm having major brain block right now. I have a table with say sales revenue by day and location, and I want to have another table where I sum the sales data into 13 columns, the location and then the sum of the sales for each month (so that I can do other things in the data). I am able to do a bunch of case whens to get 12 lines with each corresponding month correct but nulls in the other 11, but how do I structure an insert into (select) so that I have 1 line (and thus 1 row in my new table)

This is for mysql

[grim vault]

some databases do have a FILTER option for aggregate functions, or you can just make 12 case inside the aggregate function.

[peak stag]

I tried making the case but it results in NULLs for 11 of the 12 fields which I don't understand

I either get 12 rows, with 12 columns and the appropriate case when working and nulls in the other 11 for each row (and coalesce doesn't seem to help)

or I get the 11 nulls and whichever condition is met on the group by

[grim vault]

select
  location
  , sum(case when month = 1 then revenue else 0) as "jan"
  , sum(case when month = 2 then revenue else 0) as "feb"
  , ...
from ...
group by location

or if the database supports filter:

select
  location
  , sum(revenue) filter (where month = 1) as "jan"
  , sum(revenue) filter (where month = 2) as "feb"
  , ...
from ...
group by location

[peak stag]

month is achieved through month(sales_date) and revenue is itself a sum, so because of the nested sum I get an "invalid use of the group function" here, unfortunately I oversimplified my question and withheld that.

oh my lord I just had the sum wrong.

Thank you so much for your patience Berndulas

I've finally gotten it

35 degrees outside, my brain was melted clearly.

[white elm]

yo

when sql data looks like this in the output:

[(250630723810099201, None, 'Test $10', 'Test')]

How can you manage to sort all data in seperate variables?

the variable for this data is "result"

so i tried to sort the data out by using "id = result[0]", "guest = result[1]" but it did not work

[paper flower]

id, guest, something, something_else = result[0]

[white elm]

will that seperate everything into those variables?

[astral siren]

guys am I being a dumbass

cur.execute(f"UPDATE artLevels SET exp = {exp}, artAmount = {art}, rank = {level} WHERE member = {id}", )

What is wrong with this statement?

except for the sql injection vunurability

mysql.connector.errors.ProgrammingError: 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'rank = 5, WHERE member = 339866237922181121' at line 1```

[white elm]

i think you cannot use f strings in sql

[astral siren]

you can usually

but fine

Its just more typing

[white elm]

so you would want to do something like this

cur.execute("UPDATE artLevels SET exp = ?, artAmount = ?, rank = ? WHERE member = ?", (exp, art, level, id)

I believe

[astral siren]

you can but you shouldn;t*

yeah Im about to type that out

[white elm]

yeah

i got some issues with my update code too

[astral siren]

same error

[white elm]

you got same error?

[astral siren]

yup th exact same one

[white elm]

hmm

[astral siren]

the syntax is correct right

[white elm]

yeah i mean thats how i wrote my code too

but i got another error

"Error binding parameter 1"

[astral siren]

lets see urs then

[white elm]

cursor.execute("UPDATE user SET guest = ? WHERE secret = :secret", (user.id, {"secret": footer_text}))

this is how i wrote my code

[astral siren]

can you tell me what user.id is?

@white elm

[white elm]

it is the id of a reaction user

which i believe did not work

[astral siren]

its probably a snowflake not an int

why dont you try int(user.id)

[white elm]

hmm thats true

okay i have to reset my database once again to try this lol..

[astral siren]

👍

[grim vault]

The error doesn't match your SQL string. In the error there is a comma , before the WHERE which should not be there.

[astral siren]

yeah I changed it

both have the same error

[grim vault]

Can't be, show your code and error again.

[astral siren]

mysql.connector.errors.ProgrammingError: 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'rank = 5 WHERE member = 339866237922181121' at line 1

[white elm]

Looks like i still have the error

File "C:\Users\user\OneDrive\Dokument\Paid Services Assistance\index.py", line 91, in on_reaction_add
    cursor.execute("UPDATE user SET guest = ? WHERE secret = :secret", (int(user.id), {"secret": footer_text}))
sqlite3.InterfaceError: Error binding parameter 1 - probably unsupported type.

cursor.execute("UPDATE user SET guest = ? WHERE secret = :secret", (int(user.id), {"secret": footer_text}))

[astral siren]

print(int(user.id))

[white elm]

okay

[astral siren]

you using interactions.py?

[white elm]

uhm nah

[astral siren]

discord.py?

[white elm]

yeah

[grim vault]

I don't think you can mix placeholders (and the counting starts with 0, so, parameter 1 is the 2nd one).

[white elm]

oh

which could be the "secret" id?

[astral siren]

what type is footer_text?

[white elm]

footer_text is the variable that has stored the footer content of a embedded message

[grim vault]

Use only one type of placeholers, either ? or :name

[white elm]

and for this bot

it has stored the "secret" id which is listed in the footer

[astral siren]

try and str() it

[white elm]

of a embedded message

[astral siren]

wait id?

then int it

[white elm]

oh okay

[white elm]

yeah

[astral siren]

oh nm berndulas knows more than me lol

is this sqlite3?

[white elm]

yeah this is sqlite3

[grim vault]

cursor.execute("UPDATE user SET guest = :guest WHERE secret = :secret", {"guest": user.id, "secret": footer_text})
# or
cursor.execute("UPDATE user SET guest = ? WHERE secret = ?", (user.id, footer_text))

[astral siren]

ah yeah I use mysql

[white elm]

oh okay thank you i'll try that one

[white elm]

oh okay, so you basically using the language itself and not the python version of sql if u get me

[astral siren]

Theres not really "python version" of SQL you can run SQL commands from any language you want but yeah I have a linux server that hosts mysql and I use a cursor like you do

im just using a mysql-connector

in python

BUT I HAVE NO IDEA WHY MY UPDATE CLAUSE IS WRONG

wtf

[white elm]

lol i see

i think i actually had the same error as you before

but i cannot find out how i managed to solve it lol..

[grim vault]

Could it be that rank is a reserved keyword in mysql? Try `rank`

[white elm]

wait a minute

you do need db.commit() for UPDATE too right?

[astral siren]

Ok have you heard of ACID / transaction programming?

[white elm]

nah not really tbh

[astral siren]

No thats fine!

So basically databases need to follow these rules in order not to break

A, stands for atomicity, which means that an transaction should either be completed fully or not at all

so we have commit() so that we can make sure everything we wanted to do happened, all of it, then we can "save" the changes

and if everything we wanted to didn't happen commit() wouldn't be ran and the whole transaction would fail

so basically if you don't run commit there will be no errors but the database wont change

[white elm]

oh okay i see

yeah because i noticed how the new record did not appear

in the sql viewer haha

[astral siren]

yeah it wouldnt lol

Still dk why my sql statement has an error

[white elm]

yeah lol

[grim vault]

You tried my suggestion to quote the column name?

[topaz bloom]

Does anyone know how to move files from one azure fileshare to another? Basically I have hot storage that's getting out of hand and need to migrate a lot of the data to cool storage but I'm not finding what I need in the documentation

[astral siren]

yes

sorry got busy

Im even trying it in the sql workbench

[grim vault]

Backticks ` not single quotes '

[astral siren]

I think rank is a keyword

oh shit

sorry

lol

[ivory harbor]

a database holds storage

[uneven sinew]

yes Saruezz, a database holds storage. now can anyone read what's on the whiteboard

[ivory harbor]

LMFAO

[fringe sundial]

@uneven sinew

async def my_hook():
    bot.conn = await aiosqlite.connect("name.db")
    bot.cur = await bot.conn.cursor()
    await bot.cur.execute('CREATE TABLE IF NOT EXISTS pretable(prefix TEXT,guild_id INTEGER)')
    await bot.conn.commit()

Is it correct to make a table?

I wrote it at my own

[slender atlas]

You don't need to create a cursor for that. A connection can execute stuff as well.

But I think it will work anyway. I haven't tried.

[fringe sundial]

Oh k

How can I fetch data from a key?

[slender atlas]

From the cursor of the SELECT

[fringe sundial]

Like i have a table called prefixes it have guild_id and prefix columns

I want to select guild_id

And get the prefix of it

How can I do that?

[slender atlas]

Select the record with said guild_id, fetch records and get the first one since there will probably be only one prefix per guild

Well if you're doing this for the command_prefix argument of bots then you can return the entire tuple of fetched records if it's not empty because it also accepts an iterable of prefixes

[fringe sundial]

@torn sphinx

   bot.conn = await aiosqlite.connect("name.db")
    bot.cur = await bot.conn.cursor()

In this why we are use bot.?

[uneven sinew]

!botvars

[delicate field]

Python allows you to set custom attributes to most objects, like your bot! By storing things as attributes of the bot object, you can access them anywhere you access your bot. In the discord.py library, these custom attributes are commonly known as "bot variables" and can be a lifesaver if your bot is divided into many different files. An example on how to use custom attributes on your bot is shown below:

bot = commands.Bot(command_prefix="!")
# Set an attribute on our bot
bot.test = "I am accessible everywhere!"

@bot.command()
async def get(ctx: commands.Context):
    """A command to get the current value of `test`."""
    # Send what the test attribute is currently set to
    await ctx.send(ctx.bot.test)

@bot.command()
async def setval(ctx: commands.Context, *, new_text: str):
    """A command to set a new value of `test`."""
    # Here we change the attribute to what was specified in new_text
    bot.test = new_text

This all applies to cogs as well! You can set attributes to self as you wish.

Be sure not to overwrite attributes discord.py uses, like cogs or users. Name your attributes carefully!

[uneven sinew]

@fringe sundial

[fringe sundial]

@uneven sinew like i used it in my_hook
Do i need to use bot. In every database function like get_prefix?

[torn sphinx]

no?

just for the queries

to access connection and cursor

[slender atlas]

That depends. It looks like your connection is saved as a bot variable, meaning you would need to access the bot before the connection bot variable when you're trying to use the connection from a Cog, for example

[fringe sundial]

@torn sphinx i need some help

[torn sphinx]

?

[fringe sundial]

I am not able to understand how should I write the get_orerix function

[torn sphinx]

very sad

[fringe sundial]

I am done with the my_hook

[torn sphinx]

very nice

[fringe sundial]

Now i am at get_prefix