databases

[smoky glacier]

db=mysql.connector.connect(host="localhost",user="ishan",passwd="nashid73",db="testdatabase")

users=[("tim","techwithtim"),("joe","joe123"),("sarah","sarah1234")]

user_scores=[(45,100),(30,200),(46,124)]

mycursor=db.cursor()

q1="CREATE TABLE Users (id int PRIMARY KEY AUTO_INCREMENT,name varchar(50),password varchar(50))"

q2="CREATE TABLE Scores(userID int PRIMARY KEY,FOREIGN KEY(userID) REFERENCES Users(id),game1 int DEFAULT 0,game2 int DEFAULT 0)"

#mycursor.executemany("INSERT INTO Users(name,password) VALUES (%s,%s)",users)
q3="INSERT INTO Users(name,password) VALUES (%s,%s)"
q4="INSERT INTO SCORES(userID,game1,game2) VALUES (%s,%s,%s)"

for x,user in enumerate(users):
    mycursor.execute(q3,user)
    last_id=mycursor.lastrowid
    mycursor.execute(q4,(last_id,)+user_scores[x])

mycursor.execute("SELECT * FROM Users")
for x in mycursor:
    print(x)

this is the code

the error is shown at line 20

it says data is too long for 'password'

any fix?

[grim vault]

Check if the table really does look like q1 is hinting at.
And it's a bad idea to store passwords in plain text, very unsafe (= bad) idea.

[stark sparrow]

i am getting this error. please help me

[strong glade]

eh

too much

for my brain

[stark sparrow]

try

pls try

don't give up

u r the only one came

i didn't give space

[torn sphinx]

are you using atlas? @stark sparrow

[torn sphinx]

maybe he is talking about this mongo_url = 'keep dreaming' there is a space between keep and dreaming

that's not a valid url

yea, it's not a valid URL @stark sparrow

[stark sparrow]

no

[stark sparrow]

it is not the url. there is no space in the real url

[torn sphinx]

local db?

[stark sparrow]

i think it is

[stark sparrow]

i used my public ip in security. does it cause problem?

[torn sphinx]

make sure you have your ip whitelisted in the database config

[stark sparrow]

can u pls tell me how to do that?

[torn sphinx]

sorry I have to go

[stark sparrow]

it's ok

i'll search ggl

[torn sphinx]

your error is basically ReplicaSetNotPrimary

[stark sparrow]

means?

[torn sphinx]

google about the error

[stark sparrow]

ok

[stark sparrow]

it is also ServerSelectionTimeoutError

ig

[warm rain]

Can perhaps yall know about something in #help-mushroom ?

[tranquil hill]

Anyone here using sqlalchemy with encode/databases ? Is it just me or tables created thru declarative_base are completely incompatible with anything requiring raw sqlalchemy core (eg, async stuff)

metadata = sqlalchemy.MetaData()
TableBase = declarative_base(metadata)

class Cat(TableBase):
  __tablename__ = "cat"
  id = Column(primary_key=True)

# ...
database = databases.Database(DATABASE_URL)
# ... snip... database connect etc

# None of this works
await database.execute(Cat.id == 1)
await database.execute(Cat.c.id == 1)

from sqlalchemy import select
await database.execute(select(Cat).where(Cat.id == 1))

Answering myself: https://github.com/encode/databases/issues/105

[tiny nimbus]

Hi Everyone! Quick question: Is there a way to create a db using sqlite3 for a virtualenv only?

Meaning, once I delete the virtualenv the db will be gone as well or keep multiple instances of the same db across different envs

[ionic pecan]

i have never seen VISIBLE. where did you get that from?

[warm rain]

channels = await conn.fetchrow('SELECT modlog, publicmodlog FROM channels WHERE guildid = $1', guild.id)

Will asyncpg return None if for example modlog is available but publicmodlog isn't?

[wind smelt]

By available, do you mean publicmodlog is null?

If modlog is null but publicmodlog isn't, channels will be a Record object that contains both values

[torn sphinx]

Any opinions on harperdb?

Like is it good/better than mongo?

[jade osprey]

Hi i just started using asyncpg, and postgres I just switched from aiosqlite. I want to INSERT OR IGNORE INTO but i'm getting error
here is my code

            sql = ('INSERT OR IGNORE INTO logging (guild_id,message_edit,message_deletion,role_changes,name_update,member_movement,avatar_changes,bans,channels,pins,purge) VALUES ($1,$2,$2,$2,$2,$2,$2,$2,$2)')
            val = (guild.id,False,False,False,False,False,False,False,False,False,False,False)
            await self.bot.conn.execute(sql,val)

the error

  File "asyncpg/protocol/protocol.pyx", line 168, in prepare
asyncpg.exceptions.PostgresSyntaxError: syntax error at or near "OR"

[civic cargo]

postgres has different syntax to express conflict resolution

https://www.postgresql.org/docs/9.5/sql-insert.html#SQL-ON-CONFLICT

youre looking for on conflict do nothing

it goes at the end of your query (there are examples in the docs)

[frigid glen]

Hello All,

At the risk of saturating the ecosystem "even more" with a model management tools, I am happy to share another uni-model approach I have been working on to address the issue of db model management, access, migrations, and caching. Much like sqlmodel, there is only a single model to manage( which happens to be pydantic) but with further abstractions to try and make model creation & relationships a bit easier. Happy to hear your thoughts, I hope this makes your lives a little easier 🙂

https://github.com/codemation/pydbantic

[twilit marlin]

Not sure if this is a database question but here goes.

How do I store permanent data in my application. For example in my banking website, I know that the list of supported banks will always be added to. But every time I change the database, this list of banks gets deleted obviously. One possible solution is database seeding but I was wondering if there are better ones.

[unkempt prism]

In development it is common to blow away your tables and start from scratch. using drop and create. Though for production systems its required to run db migration scripts with alter statements etc to keep the data safe. django has migrations built in, and sqlalchemy has alembic to manage migrations.

[twilit marlin]

So there is not other better method to do this except databases?

[civic cargo]

I'm confused, what do you mean by 'change the database'?

[nimble lotus]

Heyy does anyone know how to create a quiz using python and mysql connectivity ?

[twilit marlin]

Delete the existing one and creating a new one

[civic cargo]

yea so you shouldn't delete the database if you don't want to lose the data 😄

if you want to persist the data regardless you can make a backup, but why are you deleting it in the first place?

you shouldn't develop against the production database, as mentioned above - for local development you would start a local, temporary database, and once your code is ready, it is shipped to production where it connects to the real database

if you need to make changes in the structure of the production database, that's where you'd use migrations

[torn sphinx]

what database should i use for my discord bot, may you provide a tutorial video with it

[grizzled kraken]

does anyone know how to fix this unexpected argument thingy?

im following a tutorial to the dot but it seems to error for me

C:\Users\name\AppData\Local\Programs\Python\Python39\lib\site-packages\flask_sqlalchemy\__init__.py:872: FSADeprecationWarning: SQLALCHEMY_TRACK_MODIFICATIONS adds significant overhead and will be disabled by default in the future.  Set it to True or False to suppress this warning.
  warnings.warn(FSADeprecationWarning(
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "C:\Users\name\FlaskMarket\market.py", line 7, in <module>
    class Item(db.Model):
  File "C:\Users\name\FlaskMarket\market.py", line 9, in Item
    id = db.Column(db.Integer(), primary_key=True)
TypeError: Column() got an unexpected keyword argument 'primary_key'```

this is the error

[simple grove]

is it added to by non-developers as well?

in that scenario you could be forced to hand-write migration scripts, which is no fun

[twilit marlin]

No they aren't. Is there any solution?

[simple grove]

how many items are in one of these tables and how often do they get changed?

[twilit marlin]

So it's for a banking application, and the table is for supported partner banks. So they get changed occasionally

[simple grove]

it's basically configuration data then?

personally i would prefer a JSON flat file or XML file for semi-static data, if the only people doing changes to the DB are developers. then it's impossible for it to get out of sync with the application code

[twilit marlin]

Yes you could say that

But I need foreign keys to this data so i decided the database approach.

[bright arch]

maybe primary_key=True goes into the db.Integer() constructor, not the db.Column() constructor ?

[reef mesa]

Hi guys , I need a little help. I am new to mongodb and I have been trying to understand mongodb atlas so I can set it up with nodejs and express for a react app backend.
I would really appreciate some help or a walkthrough of what I should do because I am stuck

[grim vault]

You sure it's
id = db.Column(db.Integer(), primary_key=True)
and not
id = db.Column(db.Integer, primary_key=True)

[upper rampart]

hey

@bot.command()
@commands.has_role('key')
async def deletekey(ctx, *, arg):
    rkey = int(arg)
    print(arg)
    cursor.execute(f"DELETE FROM `sheesh` WHERE {rkey}")
    await ctx.send(f"There we're no errors, check to make sure key was deleted.")

This won't delete

I get no errors it just doesn't delete

it's phpmyadmin btw

[ionic pecan]

@upper rampart that‘s not how you write the condition, you want to do something like „WHERE x = 3“, also if the argument is a truthy value you will delete anything on the table

what you‘re missing to actually perform the delete though is a connection.commit() call

[upper rampart]

can you show in code

it works just fine

now

sync def deletekey(ctx, *, arg):
    rkey = int(arg)
    print(arg)
    cursor.execute(f"DELETE FROM sheesh WHERE ekey={rkey};")

I did this

oh but the connection.commit

made it actually execute after

i do the command

instead of me having to add something new onto the list

ty @ionic pecan

[vast haven]

There shouldn't really be a reason why you'd need to delete the table just to update or create new records in it.

(If you're dropping tables and needing to recreate them for your application to work, this should be a smell - and means you're doing something wrong)

Additionally, since you're creating a banking application - I would highly encourage you to read up on the laws that pertain to the geographical areas in which you plan on releasing your application. It's quite possible you aren't allowed to delete information from your database and would need to do a soft-delete instead.

Often times when working with financial data, you'd need to store that for a certain period of time, under specific, strict circumstances, and keep records that could be audited.

[slender herald]

Hey guys, do you know how to set greek collation in django for a specific table? I just need it to be sorted correctly according to the greek alphabet in django admin. I'm using postgres.

[tame sierra]

Hey, can you check this code out and see whats wrong. This is sqlite3 code

This works:

userinfo = [usernameinput, passwordinput]
database.execute("INSERT INTO Basic_Credentials (username, password) VALUES (?, ?)", userinfo)

But this doesnt:

userinfo = ['Basic_Credentials' , usernameinput, passwordinput]
database.execute("INSERT INTO ? (username, password) VALUES (?, ?)", userinfo)

can you tell me why I can't pass the list with 3 elements for each of the 3 '?' marks?

[burnt turret]

The qmark placeholders work only for values I believe, not stuff like table/column names

[tame sierra]

ah ok, i fixed this by adding the variable itself into the string

[hollow shoal]

I just started MongoDB and made a basic collection with array
Now I want to query all elements in the array, how do I do that?

new_server = {"server": guild.id, "xp_per_message": 10,
                          "double_xp_role": "NA", "level_channel": "✨・level-ups",
                          "talk_channel": "❄・alex", "quote_channel": "💯・quotes",
                          "ignored_channel": []
                          } 
 db.insert_one(new_server)
ignored_list = db.find_one({"server": message.guild.id}, {"ignored_channel"})
                if message.channel in ignored_list:

ignored_list = db.find_one({"server": message.guild.id}, {"ignored_channel"}) is probably wrong.. can someone tell me how do I get all elements in ignored_list key?

This is wat collection looks like

[tidal urchin]

use XAMPP and Heidisql and then enable mysql

[torn sphinx]

example vid?

because i've never even hurd ofbit

of it

[tidal urchin]

Just youtube it

it's quite popular

[cedar flare]

Hey I have an ec2 instance and I installed xampp and now I am able to access it using it's IP on port 80, however I want to connect to mysql using python which I am not able to, can anyone help me on this?
Python script won’t be running on the localhost, I want to run it remotely

[torn sphinx]

Anyone here who could help me with query optimization? Maybe in DM

[cedar flare]

How can I access mysql hosted using xampp on aws from python? I can access xampp using AWS public IP

[harsh pulsar]

Use the IP as the domain name

[lime lily]

any idea on how to make a flexible connection to different databases?

[cedar flare]

mysql.connect(
                host='12.323.43.12',
                username='root',
                password='password',
                database=mydb
            )

This is what I have been using but it isn’t working

[lime lily]

it should be py mysql.connector.connect(host='12.323.43.12', username='root', password='password', database="mydb" )

[cedar flare]

module 'mysql.connector' has no attribute 'connecter'

[lime lily]

connector* my bad

without the *

[cedar flare]

I have imported mysql.connector as mysql

So the one I have written above is just fine I guess

[harsh pulsar]

What happens when you try to connect? Is there an error? "Isn't working" is not something anyone can help with

[harsh pulsar]

Can you elaborate? Normally in my applications, I write functions and classes that accept a database connection object as a parameter, so i can do things like pass in a mock database connection in my test suite. So maybe that design pattern would be useful here

[cedar flare]

raise errors.InterfaceError(\nmysql.connector.errors.InterfaceError: 2003: Can't connect to MySQL server on '12.323.43.12:3306' (61 Connection refused)

[carmine echo]

SELECT * FROM series WHERE title 'th';
what do I add between title and 'th'; to search for titles with 'th' in them?

[harsh pulsar]

Does it work if you use some other client? Like sequel pro or mysql workbench etc

[harsh pulsar]

title LIKE '%th%' in most databases

The % is a wildcard, matching zero or more letters

[carmine echo]

thankyou i tried like but not the % bit

[boreal ridge]

I would like to start learning about databases, what database should i learn first

[harsh pulsar]

sqlite is a good one to start with because you don't need to run a separate server, it's just a file

[torn sphinx]

Is MySQL a good database with django

[boreal ridge]

i have a home server, can i use that

[harsh pulsar]

it's fine, it does the job

[harsh pulsar]

the "server" is just a separate program, you can even run it on the same computer you use for development. if you want to try using a full server database, personally i think postgresql is easier to use than mysql

[boreal ridge]

okay

thank you

[boreal ridge]

do you know a good source I can go to learn

[harsh pulsar]

there are some in the pinned messages. also read the documentation, it's there for a reason

[boreal ridge]

okay

[frail glade]

thru pymongo how do I emulate collections.distinct but return all fields?

[rich ravine]

Guys, SQL or NoSQL?

I am trying to learn PostgreSQL, but it's TO BORING

Tables are not my style. Should I try NoSQL?

[storm mauve]

PostgreSQL can hold semi structured data through some types of columns such as Json and Xml, but you will need of SQL knowledge even in some NoSQL programs

Which type of database you should use depends on what you want to do, what your data looks like and how much you care about that data, but it is important to know both SQL and NoSQL if you want to work with Big Data

(and just to clarify about "how much you care about that data" - sometimes businesses will just store a lot of data, usually in a NoSQL database (or data "lake" / "ocean"), because they want to have that data available in the future, but do not want to bother with cleaning or processing it at the moment)

[upper rampart]

connection = pymysql.connect(host='sql105.epizy.com',
                             user='XXX',
                             password='XXX',
                             database='XXX',)

Error:

pymysql.err.OperationalError: (2003, "Can't connect to MySQL server on 'sql105.epizy.com'

I'm trying to connect to infinity free database

But it wont work

[storm mauve]

in the first page of google results about epize, I found a link to a Quora question in which all answers say that it is awful and has lots of downtime, so I would probably advise for you not to use that hosting service

maybe try to add the https:// before the host domain though

[torn sphinx]

uhm

can someone

send me a mongodb tutorial

[torn sphinx]

[SSL: CERTIFICATE_VERIFY_FAILED] certificate verify failed: certificate has
expired (_ssl.c:1124)')>]>

wtf

[prisma girder]

Are you sure that your certificate end date is not in the past?

[torn sphinx]

i have no clue what that means

[prisma girder]

Can you give more details what are you doing rn?

[torn sphinx]

trying to use mongodb

[prisma girder]

It says nothing to me

[torn sphinx]

just trying to insert data

i'm new to it

[prisma girder]

What tool are you using? Are you trying to type a command or what? I am not an oracle

You need to write what you are doing to get help

[torn sphinx]

there's literally no commands

i'm literally jsut trying to insert basic data

[pure iron]

Pls help me with MongoDB

[torn sphinx]

how can i import just simple data

with pymongo

or MongoDB

[torn sphinx]

my mongodb database just failed idk it was just working fine minutes ago and started saying this

[torn sphinx]

same

[torn sphinx]

did to me too

[torn sphinx]

found a fix yet?

[storm mauve]

odd, check if your database has any settings, and if so, look for anything related to certificates there

[torn sphinx]

how?

[torn sphinx]

nawl

[storm mauve]

Go to whichever site hosts your database (or if you host it yourself, open whichever GUI app you have) and look for some sort of "settings" or "configuration" page

[torn sphinx]

ok

nothing that is related to certificates i dont think?

[torn sphinx]

nothing

[storm mauve]

no clue then, you might want to try contacting support but they might just fix it soon-ish

[torn sphinx]

omg

[storm mauve]

or see if there's some discord server for mongo db

it might be affecting everyone on your cluster / region assuming that it's an issue in their side

[torn sphinx]

i dont even know why its saying a vertificate is expired im not even sing a cert

[frail glade]

can someone help with some pymongo stuff? I get this error: pymongo.errors.ServerSelectionTimeoutError ... [SSL: CERTIFICATE_VERIFY_FAILED] certificate verify failed: certificate has expired (_ssl.c:1123) when trying to connect to an atlas db but it was working fine a few hours earlier. The db itself is fine as i'm connected w/ a cmd line but pymongo doesn't want to work.

[harsh pulsar]

it looks like your certificate expired

[harsh pulsar]

same ☝️

@frail glade @torn sphinx where is the db hosted?

[frail glade]

hosted on atlas

[harsh pulsar]

maybe atlas goofed

[frail glade]

dunno, i can connect with cmd line

[harsh pulsar]

if the ssl cert expired, that's on them

[frail glade]

so I doubt

[harsh pulsar]

ah

[frail glade]

https://stackoverflow.com/questions/54484890/ssl-handshake-issue-with-pymongo-on-python3

i guess i can try this

[harsh pulsar]

if it was working fine a few hours earlier that's suspect. but did you change the code?

[frail glade]

no

[harsh pulsar]

and you can open a new cli connection, right now?

[frail glade]

yep

just reopened

i just did client = pymongo.MongoClient(uri, ssl_cert_reqs=ssl.CERT_NONE) but i'm just confused why it's acting weirdly now

[harsh pulsar]

hm, that's odd

the reason i suspected a problem w/ the host is that 2 people just posted the same issue in short succession

i guessed that they were using the same misconfigured host

so does this not work?

client = pymongo.MongoClient(uri, ssl_cert_reqs=ssl.CERT_NONE)

that should disable ssl cert checking entirely, as far as i know

[frail glade]

it's fine

[shy comet]

Hi

I have some problems loading my database

[harsh pulsar]

it's possible that the cli is just not doing ssl checking by default, although that'd be weird

[frail glade]

idk maybe

[torn sphinx]

to use database in discord bot.. do we need to keep database online separately?.. or it works some other way?

i'm new to db thing..., so no booliee

[torn sphinx]

also, ping me when u reply

[torn sphinx]

The same thing has happned to me

like out of no where it stop working

locally

but on my host it works fine

[torn sphinx]

🔸 How can I store and delete image files on MongoDB ?

• I want to make an admin panel with Flask for a website I'm developping.

[prisma girder]

Have you seen this? https://www.mongodb.com/developer/how-to/storing-large-objects-and-files/

Also check this answer on StackOverflow: https://stackoverflow.com/a/40017346

[hollow shoal]

Same error but no wonder why it is working no heroku

like It was also working on my PC around <t:1632975185:R>

and I tried restarting the app on heroku, and it works

[torn sphinx]

Thx

[proven ginkgo]

how to fetch and edit data in asyncpg ?

postgres

:/

pls tellll

[prisma girder]

I am guessing that there are no people around which are waiting for questions and respond immediately so give us some time

[prisma girder]

Check usage page in docs - https://magicstack.github.io/asyncpg/current/usage.html#asyncpg-usage

Here you have how to get row, create table and insert data

[proven ginkgo]

idk how they are specifying and changing a specific row

[faint blade]

Do you know SQL?

[proven ginkgo]

i am using postgres

[prisma girder]

Update INSERT INTO query into UPDATE

[prisma girder]

Good question

[proven ginkgo]

on suggestions of this server members :/

lol

[prisma girder]

Have you seen pinned messages?

There is a FAQ for asyncpg

[proven ginkgo]

idk why i am not understanding the docs they have different syntaxes as i saw in yt

[faint blade]

What do you mean different syntaxes?

[hollow shoal]

disabling ssl cert is not a good option right?

[harsh pulsar]

Yes, asyncpg uses a slightly different interface compared to the standard db-api in python

[prisma girder]

It's okay for local development

[harsh pulsar]

I can think of more dangerous things you can do, but if you are running this in some kind of production environment you probably don't want to disable it

Disabling certificate verification is equivalent to not using SSL

[hollow shoal]

Its acting kinda strange, when I hosted on heroku it worked

but my pc is f up

[torn sphinx]

@harsh pulsar do you have fix to the [SSL: CERTIFICATE_VERIFY_FAILED] certificate verify failed: error yet?

[harsh pulsar]

no, i can only guess

[torn sphinx]

its weird becuase it doesn't work on my local machine

but works on my host provider?

[hollow shoal]

same so I used ssl_cert_reqs=ssl.CERT_NONE for local machine

[grim vault]

Maybe it has it somethinbg to do with: DST Root CA X3 will expire on September 30, 2021.

https://letsencrypt.org/docs/dst-root-ca-x3-expiration-september-2021/

[harsh pulsar]

your host probably has the new cert but your local machine doesn't

[torn sphinx]

so how can i get the new cert?

[harsh pulsar]

well, hold on

the cert should be sent by the server

then the client verifies it

you would only need a "new cert" if the root certificate or one of the intermediates changed

so ignore what i said

[whole mica]

pymongo.errors.ServerSelectionTimeoutError: [SSL: CERTIFICATE_VERIFY_FAILED] certificate verify failed: certificate has expired

why is it happening :/

[wispy comet]

My director at work sent out a team-wide email saying to join a tech conference session about PostgreSQL and how it's way better than the Oracle DB servers we do use
@ me who took a database course in uni over 4 years ago where we used Postgres: "yeah this was obvious, are you only learning this now?"
It's all fun and games until you realize you're more qualified than some of your superiors

[harsh pulsar]

you're the 3rd person reporting this here in 2 days. if you're using mongodb atlas, it sounds like they messed up their certificates

[whole mica]

oof

[proven ginkgo]

hi

i am working with postgres

asyncpg

and it is showing this

when i am loading a row's object

[torn sphinx]

mysql.connector.errors.OperationalError: 2055: Lost connection to MySQL server at 'localhost:3306', system error: Connection not available.

i am getting this error when connecting with mysql any suggestions

[ionic pecan]

yes, that's correct, asyncpg returns Record objects

[ionic pecan]

do you have a mysql database running locally?

[proven ginkgo]

that is solved ty

[proven ginkgo]

how to connect postgres with heroku
i was using
pgadmin 4
but when update in heroku
it is saying address not connected

[ionic pecan]

show the full error

[torn sphinx]

no

should i

[limpid crown]

Has anyone connected to a power bi dataset with python?

[wispy comet]

considering your error code is trying to access a database on localhost, then yes you would need to run a mysql server locally

[arctic badger]

Ive been pondering about this for hours..
Trying to make a Quest system where users can select 1 quest at a time to pursue...
I was thinking having 3 possible quests for each game I have.
How horrible is it doing it this way?

CREATE TABLE `Quests` (
  'DiscordID' varchar(64) DEFAULT NULL,
  'ActiveQuest' varchar(30) DEFAULT NULL,
  'BlackjackWins' tinyint(2) DEFAULT 0,
  'BlackjackGames' tinyint(2) DEFAULT 0,
  'BlackjackProfit' tinyint(2) DEFAULT 0,
  'CoinflipWins' tinyint(2) DEFAULT 0,
  'CoinflipGames' tinyint(2) DEFAULT 0,
  'CoinflipProfit' tinyint(2) DEFAULT 0,
```I  have around 6 games in total... so it'd be 6*3=18 columns for games + the DiscordID and ActiveQuest. 20 columns total.

The one they choose, ActiveQuest will be set to that name (such as CoinflipWins). Every time they win in coinflip, that number goes up for CoinflipWins. Once they reach x amount of wins, they complete the quest and can choose another one to start.

[harsh pulsar]

@arctic badger ```sql
CREATE TABLE quests (
discord_id varchar(64),
quest varchar(255),
is_active boolean DEFAULT false,
games integer default 0,
wins integer default 0,
profit integer default 0,
PRIMARY KEY (discord_id, quest)
)

make this "long" format, not "wide"

[arctic badger]

WOW IM FUCKING STUPID.
Yea, quest will also be telling me what game it is lmao. 🤦 Instead of making 3 variables for EACH game

[harsh pulsar]

then you don't have to add or remove a column any time you want to change or add a game

you can't get smart if you don't get stupid first

[arctic badger]

facts.
and wdym, make this "long" format, not "wide"

like are u just talking about my spacing?
and unfortunately, SQLite doesnt have boolean :p thats why i did tinyint(1)

[harsh pulsar]

"wide" format is having one column per quest, "long" format is having a quest column

[arctic badger]

ahh, ok. good to know. well, thank u so much! u just saved my database a lot of storage 😄

[harsh pulsar]

i don't think you save any space this way, just your sanity

consider that tinyint(2) is very restrictive. nobody can play a game more than 99 times

or wait

is this mysql?

[grim vault]

SQLite only knows integer for whole numbers.

[harsh pulsar]

oh you said sqlite

it also only knows "text" and not "varchar", right?

i get all my databases mixed up

[grim vault]

yeah, text only.

[harsh pulsar]

CREATE TABLE quests (
  discord_id text,
  quest      text,
  is_active  integer DEFAULT 0,
  games      integer DEFAULT 0,
  wins       integer DEFAULT 0,
  profit     integer DEFAULT 0,
  PRIMARY KEY (discord_id, quest)
)

[grim vault]

but is discord_id text?

[harsh pulsar]

apparently in their application it is

CREATE TABLE quests (
  discord_id text NOT NULL,
  quest      text NOT NULL,
  is_active  integer NOT NULL DEFAULT 0 CHECK (is_active in (0, 1)),
  games      integer NOT NULL DEFAULT 0,
  wins       integer NOT NULL DEFAULT 0,
  profit     integer NOT NULL DEFAULT 0,
  PRIMARY KEY (discord_id, quest)
)

[grim vault]

I would make that quest a quest_id and link to a second table where I would define the games.

[torn sphinx]

thanks

[lusty tree]

How do I do this: py cursor.execute("ALTER TABLE main RENAME COLUMN user_ovr TO user_rating INT")

sqlite3 btw

[torn sphinx]

Lets say this is my document in a mongodb collection
uid = await self.coll.find_one({"user_id": str(user_id)} I am doing that to find the document how would I now return the reaction in that document?

[burnt turret]

uid will now be a dictionary

so uid["reaction"]

[torn sphinx]

            reaction1 = uid["reaction"]
            await message.add_reaction(reaction1)

So doing that should work right?

[burnt turret]

try it and see

[arctic badger]

hm...
so im doing

questName = "Quest1"

conn = sqlite3.connect(config.db)
sql = f"""INSERT INTO Quests(DiscordID, ActiveQuest)
          VALUES ({ctx.author.id}, {newQuest});"""
conn.execute(sql)
conn.commit()
```but it's saying `no such column: Quest1`

I've never had this issue before.. o.0
It clearly says the questName is a value lol... and im inserting THAT VALUE into the ActiveQuest column..

[burnt turret]

Don't use string formatting for SQL queries, you should be doing something like

sql = "INSERT INTO Quests(DiscordID, ActiveQuest) VALUES(?, ?)"
conn.execute(sql, (ctx.author.id, "Quest1"))
conn.commit()

Apart from the issues that could occur from not properly quoting the values (which is the issue you're having), it is also vulnerable to SQL injection

#databases message explains what SQL injection is

[arctic badger]

ah, ok. i was trying to convert it a different, incorrect way

thanks for the code!

[torn sphinx]

reaction1 = uid["reaction"]
TypeError: 'NoneType' object is not subscriptable

[proven ginkgo]

how to connect pgadmin 4 with heroku?

[burnt turret]

That means your find_one query returned None, because there were no matching documents with that user id

[torn sphinx]

Yeah I figured it out thanks

[rich ravine]

Guys, the SUM() function is not working. What happened?

I made a test, and the problem is the argument.

Everything okay with the function. Sorry guys(NOOB HERE)

[vast galleon]

Hello,
I use sqlAlchemy, and a try to create a Generic class for some query.
for the update query I've got a problem.
when I use

self.session.query(self.table).filter(filter).update()

this method commit automatically my query, and a would like commit later.
I try to use

session.add()

but since my class is generic, I can't use

def update(self, update, filter):
  up = self.session.query(self.table).filter(filter)
    for row in up:
      for key in update.keys():
        row.name = update[key] # it's impossible beaucuse I would like use generic methode
      self.session.add(row)

update({TableTest.name:"Arthur"}, TableTest.name=="Jean")

If someone has an idea?

[burnt cloak]

the wrong in abc.execute

how can i fix

[twilit marlin]

should be VALUES

-- CreateTable
CREATE TABLE "SentEmail" (
    "id" UUID NOT NULL,
    "type" TEXT,
    "sentOn" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "status" BOOLEAN NOT NULL DEFAULT false,

    CONSTRAINT "SentEmail_pkey" PRIMARY KEY ("id")
);

In this table, how can use ALTER (or anything else) to change the datatype of type to TEXT[]?

[burnt cloak]

ob ok

[burnt cloak]

it still that

[twilit marlin]

whats the error?

[twilit marlin]

change . to ,

[burnt cloak]

oh ok thanks

Ignoring exception in on_message
Traceback (most recent call last):
  File "C:\Program Files\Python39\lib\site-packages\discord\client.py", line 343, in _run_event
    await coro(*args, **kwargs)
  File "c:\Users\luffy\Desktop\All Things\Code File\Python\Discord.py\MineHardcore.py", line 135, in on_message
    currentownerxp = d[0][0]
IndexError: list index out of range

what

[grim vault]

Your price column is from type varchar which can not be summed.

[burnt cloak]

.

[grim vault]

Take a look at d. Looks like your fetchall() didn't select anything which makes d and empty list and d[0] and index error.

[burnt cloak]

else:
        xp = generateXP()
        abc = userleveldb.cursor()
        abc.execute(f"SELECT xp FROM exp WHERE client_id = {str(message.author.id)}")
        d = abc.fetchall()
        currentownerxp = d[0][0]
        if len(currentownerxp) == 0:
            if message.author.id not in guild_id_used:
                ownerid = message.author.id
                h = generateXP()
                guild_id = message.guild.id
                abc.execute(f"INSERT INTO xp VALUES(%s,%s,%s)",(ownerid,h,guild_id))
                abc.commit()
                print(d)
            else: 
                ownerid = message.author.id
                h = generateXP()
                guild_id = message.guild.id
                abc = userleveldb.cursor()
                abc.execute(f"INSERT INTO xp VALUES(%s,%s,%s)",(ownerid,h,guild_id))
                abc.commit()
                print(d)

but i added if d is None then it insert it

[grim vault]

BAD:
abc.execute(f"SELECT xp FROM exp WHERE client_id = {str(message.author.id)}")
Good (edit: switched ? to %s):
abc.execute("SELECT xp FROM exp WHERE client_id = %s", (message.author.id,))

You need do check d to know if the select returned any row or not.

Looks like you want if len(d) == 0: and not if len(currentownerxp) == 0:

and the inner if/else are looking the same?

and the select has xp as columnname and exp as tablename, but the insert is using tablename xp?

[burnt cloak]

oh

[burnt cloak]

Ignoring exception in on_message
Traceback (most recent call last):
  File "C:\Program Files\Python39\lib\site-packages\discord\client.py", line 343, in _run_event
    await coro(*args, **kwargs)
  File "c:\Users\luffy\Desktop\All Things\Code File\Python\Discord.py\MineHardcore.py", line 153, in on_message
    abc.execute("SELECT xp FROM exp WHERE client_id = %s, guild_id = %s",(message.author.id,message.guild.id,))
  File "C:\Users\luffy\AppData\Roaming\Python\Python39\site-packages\mysql\connector\cursor.py", line 568, in execute
    self._handle_result(self._connection.cmd_query(stmt))
  File "C:\Users\luffy\AppData\Roaming\Python\Python39\site-packages\mysql\connector\connection.py", line 846, in cmd_query
    result = self._handle_result(self._send_cmd(ServerCmd.QUERY, query))
  File "C:\Users\luffy\AppData\Roaming\Python\Python39\site-packages\mysql\connector\connection.py", line 656, in _handle_result
    raise errors.get_exception(packet)
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 ', guild_id = 872362022375915580' at line 1

where

[grim vault]

If you have more than one where condition you need to and/or them:
abc.execute("SELECT xp FROM exp WHERE client_id = %s AND guild_id = %s", (message.author.id, message.guild.id))

[burnt cloak]

ok

thanks

[grim vault]

maybe play at https://sqlbolt.com/ for some SQL learning.

[burnt cloak]

Ignoring exception in on_message
Traceback (most recent call last):
  File "C:\Users\luffy\AppData\Roaming\Python\Python39\site-packages\mysql\connector\cursor.py", line 82, in __call__
    return bytes(self.params[index])
IndexError: tuple index out of range

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "C:\Program Files\Python39\lib\site-packages\discord\client.py", line 343, in _run_event
    await coro(*args, **kwargs)
  File "c:\Users\luffy\Desktop\All Things\Code File\Python\Discord.py\MineHardcore.py", line 157, in on_message
    abc.execute("UPDATE xp SET exp = %s WHERE client_id = %s AND guild_id = %s",(message.author.id,message.guild.id))
  File "C:\Users\luffy\AppData\Roaming\Python\Python39\site-packages\mysql\connector\cursor.py", line 557, in execute
    stmt = RE_PY_PARAM.sub(psub, stmt)
  File "C:\Users\luffy\AppData\Roaming\Python\Python39\site-packages\mysql\connector\cursor.py", line 84, in __call__
    raise errors.ProgrammingError(
mysql.connector.errors.ProgrammingError: Not enough parameters for the SQL statement

huh

[burnt cloak]

ok

[grim vault]

There are three %s but you give only two arguments

[burnt cloak]

oh

my eye got broke

[grim vault]

and you used the tablename and columnname the wrong way around.

[burnt cloak]

wut

exp is the tablename and xp is the column

[grim vault]

UPDATE tablename SET columnname = ...

[burnt cloak]

oh

yes lol

my columnname is confused

i will change it

[whole mica]

is mongodb fixed yet oof

[vast galleon]

Nothing for help me?

[untold thorn]

does anyone have any experience with snowflake?

[torn sphinx]

Can I generate a dictionary with a custom name to a file?

Whenever I save a dictionary as is j just get the contents

[harsh pulsar]

how are you saving a dictionary? you usually should not try to just write string-ified python things to a file. you should use json

and you definitely shouldn't try to enforce the name of the python variable in the file

[civic swift]

I want to store rating 0-5 for users in my table, do I need to have another table to store all the previous ratings so I can calculate the average rating. If so, How can I make a value in one table be the result of a calculation of data from another?

[torn sphinx]

not stringafied but yeah i guess i'll just use a json

its saving the output of the dictionary

[harsh pulsar]

how else are you saving it if not as a string? with pickle?

[torn sphinx]

dumping it

with json

with open('rr.json', 'w') as convert_file:
     convert_file.write(json.dumps(new_dict))```

this saves the content of the dictionary

but I want to be able to iterate over each dictionary generated

I want something like this

        self.options = {
            discord.PartialEmoji(name=emote_name, id=emote_id): role_id,

but instead of self.options =

i want to make the dictionary be dependent on a user input

so user supplies...

id, emote_name, emote_id, role_id

        id = {
            discord.PartialEmoji(name=emote_name, id=emote_id): role_id,```

than it saves that and writes to a file

or appends it if it already exists

[harsh pulsar]

the problem with json is that it's hard/impossible to append to it cleanly without overwriting it

can you be more specific about what data you want to store?

[torn sphinx]

its just values

sec

    discord.PartialEmoji(name=emote_name, id=emote_id): role_id,
    }```

on my end i type like...

/rr 2 test 1 2 3

2 test 1 2 3 being the variables mentioned above

id, emote_name, emote_id, role_id

[harsh pulsar]

json.dumps doesn't throw an error about discord.PartialEmoji?

[torn sphinx]

i didnt try dumping that specifically

i assumed it wont work

lemme try

[harsh pulsar]

you probably assumed right. you'd need to serialize that thing to some other representation, e.g. emote_id

[torn sphinx]

yeah it errors out

i may have multiple sets of values per message_id as well

so its not as simple as just tossing in some key values

and using that

[floral lark]

i have asked a db and flask realated question in #web-development , can anyone help?

[primal zinc]

not on their end, not sure if there will be a fix on their end tbh, its probably as intended

gonna work out resolution in next couple of hours and will update with how i fix it

don't really like the idea of having to install certificates my end, but maybe that'll be the way to go

https://www.mongodb.com/community/forums/t/keep-getting-serverselectiontimeouterror/126190

or potentially as someone linked previously:https://stackoverflow.com/questions/54484890/ssl-handshake-issue-with-pymongo-on-python3

[harsh pulsar]

unfortunately this is the nature of things. either you use pickle (bad idea, unsafe), or you have to actively think about how to serialize and deserialize information between your disk and your program

[torn sphinx]

Yeaaa

I think this just a case of thing it into a db

Or just let it be hard coded

[primal zinc]

https://letsencrypt.org/certificates/

Install ISRG Root X1, ISRG Root X2, Let’s Encrypt R3 .der certificates and works

[arctic badger]

im in a complicated problem...
I have this function:

@staticmethod
def fetchOne(sql):
    conn = sqlite3.connect(config.db)
    cursor = conn.execute(sql) 
    data = cursor.fetchone()
    conn.close()

    return data
```but I am trying to utilize better programming practices by passing in the variables to the execute functions, such as `conn.execute(sql, (value1, value2)) `
but sometimes i have 1 value, 2 values, 3 values (3 max)... so is there a way to create one function to do all this?

edit - would it be a bad idea to do it like this?
```sql
@staticmethod
def fetchOne(sql, values: list):
    conn = sqlite3.connect(config.db)
    if len(lst) == 1:
        cursor = conn.execute(sql, values[0]) 
    if len(lst) == 2:
        cursor = conn.execute(sql, values[0], values[1]) 
    if len(lst) == 3:
        cursor = conn.execute(sql, values[0], values[1], values[2]) 
    data = cursor.fetchone()
    conn.close()

[harsh pulsar]

conn.execute(sql, *values) 🙂

[arctic badger]

oh waittttttttttttttttttttttt.
The second value of execute takes in a list, right? so I can literally just used values?

[arctic badger]

hmm, *?

[harsh pulsar]

In most cases yes, actually. There is one library that takes them as separate arguments, so I thought that's what you were using at first

So just execute(sql, values) for sqlite

Explanation of the * syntax: https://stackoverflow.com/q/36901

[arctic badger]

lol appreciate that 😄

[shy comet]

Hi

Im learning a little about SQL databses

Can someone give me some help?

[median wave]

Sure ask your issue

[carmine bluff]

    c.execute(f"SELECT * FROM user_reactions WHERE reaction_message_id = '{reaction.message_id}'")

will this work?

[median wave]

is this aiosqlite?

[carmine bluff]

it is sqllite

3

# setting up SQLLite3 connection
connection = sqlite3.connect('user_reactions.db')
c = connection.cursor() # create cursor
# c.execute('DELETE FROM user_reactions')

this is how I got my connection

[median wave]

yes, aiosqlite is an async version of sqlite3 which is better as it is not blocking

!blocking

[delicate field]

Why do we need asynchronous programming?
Imagine that you're coding a Discord bot and every time somebody uses a command, you need to get some information from a database. But there's a catch: the database servers are acting up today and take a whole 10 seconds to respond. If you do not use asynchronous methods, your whole bot will stop running until it gets a response from the database. How do you fix this? Asynchronous programming.

What is asynchronous programming?
An asynchronous program utilises the async and await keywords. An asynchronous program pauses what it's doing and does something else whilst it waits for some third-party service to complete whatever it's supposed to do. Any code within an async context manager or function marked with the await keyword indicates to Python, that whilst this operation is being completed, it can do something else. For example:

import discord

# Bunch of bot code

async def ping(ctx):
    await ctx.send("Pong!")

What does the term "blocking" mean?
A blocking operation is wherever you do something without awaiting it. This tells Python that this step must be completed before it can do anything else. Common examples of blocking operations, as simple as they may seem, include: outputting text, adding two numbers and appending an item onto a list. Most common Python libraries have an asynchronous version available to use in asynchronous contexts.

async libraries
The standard async library - asyncio
Asynchronous web requests - aiohttp
Talking to PostgreSQL asynchronously - asyncpg
MongoDB interactions asynchronously - motor
Check out this list for even more!

[carmine bluff]

should I swap to aiosqlite

[carmine bluff]

yo @median wave

can SQLLites BLOB hold a py class

[shell ocean]

you would need to serialise it in some way

[carmine bluff]

sorry

[carmine bluff]

    c.execute(f"DELETE FROM user_reactions WHERE reaction_message_id = '{int(reaction.user_id)}' AND WHERE user_id = '{int(reaction.user_id)}'")

why is this erroring

operational error near where

[unkempt prism]

Replace the 2nd WHERE with~~ a another AND~~ nothing

[carmine bluff]

so AND AND?

[torn sphinx]

slimer

[carmine bluff]

just AND?

[unkempt prism]

Yes just 1 AND

[torn sphinx]

i wanna get this emoji out of the db to add as a embed reaction

but its not pulling the data

await self.bot.db.execute('SELECT lastfm_emoji FROM user_data WHERE user_id = ?', (ctx.author.id,))

[carmine bluff]

don't you need () around the ?

[torn sphinx]

what

[carmine bluff]

nevermind ig

[torn sphinx]

??

[velvet ridge]

Fixed?

Can you show a bit more of code?

[torn sphinx]

if not upvote:
      await message.add_reaction("👍")
      await message.add_reaction("👎")
else:
      vote = self.bot.db.execute('SELECT lastfm_emoji FROM user_data WHERE user_id', (ctx.author.id,))
      await message.add_reaction(vote)

[carmine bluff]

bruh

[velvet ridge]

You need to use smth like fetchone/fetchall

[torn sphinx]

oh really

[velvet ridge]

Yes

That way you get the data you want

[torn sphinx]

so instead of

self.bot.db.execute

it would be

self.bot.db.fetchall

?

or

fetchone

since im getting 1 emoji for the reaction

[velvet ridge]

No not like that

[torn sphinx]

[velvet ridge]

You need to execute it first

[torn sphinx]

oword

[velvet ridge]

I can try to find example from somewhere, it may take a few minutes on phone

[torn sphinx]

ok

lmk

[velvet ridge]

cursor = await db.execute("whatever")
 row = await cursor.fetchone()

[torn sphinx]

oh really

vote = self.bot.db.execute('SELECT lastfm_emoji FROM user_data WHERE user_id', (ctx.author.id,))
row = vote.fetchone('lastfm_emoji')

?

[velvet ridge]

Remove the thing inside brackets in last line.

[torn sphinx]

the comma

?

here?

[velvet ridge]

Not that

row = vote.fetchone() is correct

And print row to see what it gives.

[torn sphinx]

ok

its

not printing anything

[velvet ridge]

Show the code?

[velvet ridge]

WHERE user_id = ?

Try that

[torn sphinx]

what

OH

still

nothing

[velvet ridge]

Add if statement ```py
vote = ...
if vote is None:
print("nothing in db")
#other code

[torn sphinx]

OK

ok

[velvet ridge]

Oh wait now I know

row = await vote.fetchone()

[torn sphinx]

oword

still didnt print anything

[velvet ridge]

Does the database have anything?

[torn sphinx]

yes

my id: 452332876986318848

[velvet ridge]

And current code looks like?

[torn sphinx]

this is the command used to enter the emoji

[velvet ridge]

Did the print(row) do anything?

[torn sphinx]

nope

[velvet ridge]

Then it can't find anything I guess. But I can't help any further on mobile, sorry.

[torn sphinx]

thanks for

trying tho

[balmy ivy]

for i,row in PatientData.iterrows():
sql= '(INSERT INTO PatientData(DIAGNOSIS_AGE,SEX,ETHNICITY_CATEGORY,HISTOLOGY,ADJUVANT_TREATMENT,ECOG_PERFORMANCE_STATUS,SMOKING_HISTORY,SMOKING_HISTORY_PACK_YEAR_VALUE,RELAPSE_FREE_STATUS,RELAPSE_FREE_STATUS_MONTHS,UBIQUITOUS_ASSAY_PANEL,PERCENT_NECROSIS,TUMOR_VOLUME_CM3,TUMOR_STAGE,POSITRON_EMISSION,cFDNA_INPUT_NG,LYMPH_NODE_INVOLVEMENT,KI67_PERCENTAGE,CT_SLICE_SPACING,PATIENT_ID) VALUES(%d,%s,%s,%s,%s,%d,%s,%d,%s,%f,%d,%d,%f,%s,%f,%d,%s,%d,%f,%s))'
cursor.execute(sql, tuple(row))
print("Record Inserted")
cursor.commit()

Error While Connecting to sql Not all parameters were used in the SQL statement

what error is there can anyone help #databases

[unkempt prism]

have you tried to use the dataframe to_sql() function? It will be faster then iterating through all rows in a pandas dataframe.

[balmy ivy]

If you have reference please share I'm new to this

[unkempt prism]

the offical docs -> https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.to_sql.html

[balmy ivy]

can I use this concept for XAMPP SERVER

[unkempt prism]

i don't know XAMPP

[balmy ivy]

I have csv file and I try to insert a data from csv file using python

[grim vault]

There is only one placeholder %s for all datatypes (mysql connector).

[balmy ivy]

for float,int also we can use %s may I know its correct

[grim vault]

Yes, only use %s

[balmy ivy]

ok thank you

[balmy ivy]

But I got Error While Connecting to sql 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 'INSERT INTO PatientData(DIAGNOSIS_AGE,SEX,ETHNICITY_CATEGORY,HISTOLOGY,ADJUVA...'

[balmy ivy]

INSERT INTO PatientData(DIAGNOSIS_AGE,SEX,ETHNICITY_CATEGORY,HISTOLOGY,ADJUVANT_TREATMENT,ECOG_PERFORMANCE_STATUS,SMOKING_HISTORY,SMOKING_HISTORY_PACK_YEAR_VALUE,RELAPSE_FREE_STATUS,RELAPSE_FREE_STATUS_MONTHS,UBIQUITOUS_ASSAY_PANEL,PERCENT_NECROSIS,TUMOR_VOLUME_CM3,TUMOR_STAGE,POSITRON_EMISSION,cFDNA_INPUT_NG,LYMPH_NODE_INVOLVEMENT,KI67_PERCENTAGE,CT_SLICE_SPACING,PATIENT_ID) VALUES(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)

is this syntax is right

@grim vault let me know

[grim vault]

Looks ok to me.

[wanton dagger]

how do I select the bottom row in an sqlite3 table?

[shell ocean]

define "bottom"

rows are not inherently ordered

[burnt cloak]

Ignoring exception in command topxp:
Traceback (most recent call last):
  File "C:\Program Files\Python39\lib\site-packages\discord\ext\commands\core.py", line 85, in wrapped
    ret = await coro(*args, **kwargs)
  File "c:\Users\luffy\Desktop\All Things\Code File\Python\Discord.py\MineHardcore.py", line 164, in topxp_command
    abc.execute("SELECT xp,client_id FROM exp WHERE guild_id = %s ORDER BY desc",(ctx.guild.id,))
  File "C:\Users\luffy\AppData\Roaming\Python\Python39\site-packages\mysql\connector\cursor.py", line 568, in execute
    self._handle_result(self._connection.cmd_query(stmt))
  File "C:\Users\luffy\AppData\Roaming\Python\Python39\site-packages\mysql\connector\connection.py", line 846, in cmd_query
    result = self._handle_result(self._send_cmd(ServerCmd.QUERY, query))
  File "C:\Users\luffy\AppData\Roaming\Python\Python39\site-packages\mysql\connector\connection.py", line 656, in _handle_result
    raise errors.get_exception(packet)
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 'desc' at line 1

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

Traceback (most recent call last):
  File "C:\Program Files\Python39\lib\site-packages\discord\ext\commands\bot.py", line 939, in invoke
    await ctx.command.invoke(ctx)
  File "C:\Program Files\Python39\lib\site-packages\discord\ext\commands\core.py", line 863, in invoke
    await injected(*ctx.args, **ctx.kwargs)
  File "C:\Program Files\Python39\lib\site-packages\discord\ext\commands\core.py", line 94, in wrapped
    raise CommandInvokeError(exc) from exc
discord.ext.commands.errors.CommandInvokeError: Command raised an exception: 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 'desc' at line 1

help

[white briar]

Why so many %s?

[balmy ivy]

20 parameters so I can use 20 %s

It's correct or wrong

[white briar]

20% of a thing named s?

[balmy ivy]

No

Table property with values

[grim vault]

You have 20 columns which needs 20 values. Using 20 %s is correct.

[grim vault]

You need soemthing to order (eg a columnname) ... ORDER BY xp DESC

[burnt cloak]

oh

hey

top2 = allten[1][0]
top2id = allten[1][1]
top2name = await client.fetch_user(top2id)
if not top2:
        b = None
else:
        b = f"**#2 - {top2name} - {top2}***"

i set if top2 is None then b is None
but it said the top2 is None and didn't set the b is None

Ignoring exception in command topxp:
Traceback (most recent call last):
  File "C:\Program Files\Python39\lib\site-packages\discord\ext\commands\core.py", line 85, in wrapped
    ret = await coro(*args, **kwargs)
  File "c:\Users\luffy\Desktop\All Things\Code File\Python\Discord.py\MineHardcore.py", line 170, in topxp_command
    top2 = allten[1][0]
IndexError: list index out of range

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

Traceback (most recent call last):
  File "C:\Program Files\Python39\lib\site-packages\discord\ext\commands\bot.py", line 939, in invoke
    await ctx.command.invoke(ctx)
  File "C:\Program Files\Python39\lib\site-packages\discord\ext\commands\core.py", line 863, in invoke
    await injected(*ctx.args, **ctx.kwargs)
  File "C:\Program Files\Python39\lib\site-packages\discord\ext\commands\core.py", line 94, in wrapped
    raise CommandInvokeError(exc) from exc
discord.ext.commands.errors.CommandInvokeError: Command raised an exception: IndexError: list index out of range

``` the issue

[grim vault]

This:

    top2 = allten[1][0]
IndexError: list index out of range```
is the issue. either `allten[1]` or `allten[1][0]` does not exist. It has nothing to do with the if.

[burnt cloak]

so how can i do like if the top2 varible is None then it will do nothing and if it exists then it will set b = "a"??

[grim vault]

You'll need a top2 first because allten[1][0] doesn't exist.

[burnt cloak]

can u make an example with my code??

[grim vault]

I don't know what allten is, so no I can't.

[burnt cloak]

oh

abc = userleveldb.cursor()
abc.execute("SELECT xp,client_id FROM exp WHERE guild_id = %s ORDER BY xp DESC",(ctx.guild.id,))
allten = abc.fetchall()

here

[grim vault]

if len(allten) > 1:
  # now index 1 does exist
  top2 = allten[1][0]
  top2id = allten[1][1]
  top2name = await client.fetch_user(top2id)
  b = f"**#2 - {top2name} - {top2}***"
else:
  b = None

But maybe you want to loop over allten

[burnt cloak]

ok

[grim vault]

something like:

top_list = []
for idx, row in enumerate(allten, 1):
  name = await client.fetch_user(row[1])
  top_list.append(f"**#{idx} - {name} - {row[0]}**")
  if idx == 3:
    # I only want the first three
    break

or so

[burnt cloak]

oh ok thanks

instead of a is None but it print None , how can i set a so it will print nothing??

[grim vault]

You still want to print with no output? If yes you need to set a to an empty string "" instead of None.

[burnt cloak]

ok thanks

u r the best helper

[grim vault]

I'm just the one with time right now, but thanks.

[burnt cloak]

can i use range instead of enumerate ??

[grim vault]

If you want, of course.

[half glacier]

i need help

[pseudo summit]

Go ahead and ask

[arctic badger]

I am trying to specify my column name for my SELECT statement through a variable..

sql = "SELECT ? FROM Quests WHERE DiscordID = ? and ActiveQuest = ?;"
cursor = conn.execute(sql, (rest, ctx.author.id, value)) 
data = cursor.fetchone()
conn.close()
```Doing it this way will literally select the string `rest` and return that back, which makes sense. It's like doing " SELECT 1 ", it'll return back 1 if record found.

I realized I can do `sql = "SELECT " + rest + " FROM Quests WHERE DiscordID = ? and ActiveQuest = ?;"` and it works, but I was told it's bad programming practice using variables directly in the string..
Anyone know how I can do this?

[faint blade]

You cannot use ? with columns no. Where do you get rest from?

[arctic badger]

rest is just a string set between a few if statements and passed into the function for the sql statements

[faint blade]

So the thing is, it is okay if you know it can be trusted

If this is your string that you picked then it is okay, but you should never use a string you got from a user (like through input or a Discord command) without heavy validation

[arctic badger]

ah yea lol
none of my sql statements take in any actual user input

thanks!

[arctic badger]

Hmm, my code started randomly saying "parameters are of unsupported type" for my conn.execute statement?

sql = """INSERT INTO Economy(DiscordID)
          VALUES (?);"""
conn.execute(sql, user.id)
```Edit - Oh... so I changed it to `conn.execute(sql, str(user.id))` and it says "Incorrect number of bindings supplied. The current statement uses 1, and there are 18 supplied."

[grim vault]

conn.execute(sql, (user.id,))

[arctic badger]

hm, ill try that.
what's that extra comma for :p

[grim vault]

The second argument must be a iterable, I guess your string is 18 chars long.

To get a tuple.

conn.execute(sql, [user.id]) will also work.

The get a tuple with an single entry you need the comma.

[arctic badger]

ahh.. i keep brainfarting.
yea, that's exactly how i do it for my other functions lmfao

ah ok, thanks for the help!

[dreamy hatch]

Can you pass expressions as parameterized queries with asyncpg?

[whole mica]

oh my god it works thanks

[sharp herald]

Hi, I am currently doing a project in predicting Collective Variables for studying Molecular Dynamics using Deep Learning. If possible, I would like to check models using some datasets already available online. I need multiple trajectories of a single system(like a simple protein) with the same conditions. If anybody could provide me some resources, it would be really helpful.

[proud crater]

maybe try channel data-science-and-ai

[sharp herald]

Sure! Thanks

[regal drift]

Hey can someone help me writing a query ?

!paste

https://paste.pythondiscord.com/sihecinuvo.sql

the portion i dont know to do is a subquery

(select sum(debit) from account_move where account_move.id =aml.id and aat.type 'income'  ) as untaxted)```

i have never written one before.

[faint blade]

Just change the last ) to a ; and it might just work?

[sick nacelle]

Trying to get used to using sql. I have this problem where Im supposed to calculate retention rate for users that paid the previous month and current month. But Im having a hard time figuring out how to go about doing this

Any pointers?

[harsh pulsar]

How do you define retention rate? And how is the data structured?

[sick nacelle]

So the columns I have right now are payment_id, account_id, account_created_at, receipt_created_at, receipt_expires_at, transaction_number and trial_period_flag

-trial_period_flag is a boolean column that indicates whether the user is in the trial period or not.

-a retained user is someone who paid both the previous month and current month

-so im assuming retention rate month over month would be users who paid previous month and current month divided by total users for that month

-have to calculate month over month retention rate for users not in the trial period

-also using postgres

select date_trunc('month', receipt_created_at)::date as month_year, count(*) as total
from scan_table sc_total
group by 1

This is what I have so far, but this just returns total user month over month that are no longer in the trial period

[vast palm]

hello

am trying to get into government bootcamp , and i got stuck in two question about machine learning models , any one can offer a help pls

[harsh pulsar]

Hm. That's a lot to do all in sql, maybe you can get ideas from this (unanswered) question: https://stackoverflow.com/q/37521045

[sick nacelle]

Yeah it would've been so much easier in python lol

[harsh pulsar]

Is this an assignment of some kind

[sick nacelle]

Yeahhh, Im taking a bootcamp to be a data engineer and this is the sql portion of the assignment. Complex sql stufff doesn't click with me like python does so this part is hard to figure out

[vast palm]

same same lol

[sick nacelle]

Sooo I think the biggest thing Im having trouble with is figuring out is

-trying to figure out who paid current month and previous month to determine a retained user

Like should I just count if user paid 30 days after the receipt_expire_date?

[paper radish]

Hey everyone - I recently started learning postgres recently and right now I'm trying the DELETE statement, only problem is it literally won't delete

My SQL is DELETE FROM tasks WHERE title='test'

when i run from any the postgres console it works

from anywhere else just a nope

My rls policy:

create policy "Tasks are deletable by task manager."  
ON tasks
for delete using (
  auth.role() = 'taskmanager'
);```

and I ran GRANT taskmanager DELETE on tasks

running the same query with select returns the expected value

a single row

i've tried writing it in pg-node for nodejs and psycopg2 for python

neither work

code

cursor = conn.cursor()

cursor.execute("DELETE FROM tasks WHERE \"title\"='test' RETURNING *")
conn.commit()
print(cursor.fetchall())
cursor.close()
conn.close()```

i left out the connection part for obvious reasons

when i turn off rls it works

[burnt turret]

is this on supabase?

[paper radish]

Yup

[burnt turret]

I believe auth.role() returns either authenticated or anon in most cases then right? are you sure you've got the right field there?

[paper radish]

It works for other stuff

I just removed the policy

And replaced it with granting permissions

[burnt turret]

Alright

[paper radish]

But it still doens't want to work

[burnt turret]

what does the error say?

[paper radish]

No error

Just an empty list returned

[burnt turret]

I asked in the supabase server, and that's not supposed to work apparently; where do you define this taskmanager role for users?

[paper radish]

CREATE ROLE ___

seemed like it worked before but is was probably the lot of grants lol

[burnt turret]

ah yeah that's not the role that's returned by auth.role()

[paper radish]

but even when i removed it and replaced it with grants

it does not work

/:

[burnt turret]

auth.role() is set by supabase itself, and is assigned for every user that has signed up through it's authentication system

[burnt turret]

and you've connected to the db using this role itself?

[paper radish]

    dbname = "postgres",
    port = "5432",
    user = "taskmanager",
    host = "db.supabaseidthingy.supabase.co",
    password = "password"```

@burnt turret

[harsh pulsar]

honestly, yeah. anything else is probably overthinking it

[sick nacelle]

But then don't I need to somehow tell if next receipt created date is after the current receipt_expiration_date? Also the same user is listed multiple times

[burnt turret]

that looks right, can you show your GRANTs as well?

[harsh pulsar]

i don't want to reveal too much because this is an assignment, but you probably need to join the table to itself

[paper radish]

GRANT SELECT, INSERT, UPDATE, DELETE ON tasks TO taskmanager;

[burnt turret]

that looks right to me 🤔 if it was a lack of permissions for the role I think it should've raised an error there (but don't quote me on that) i've got something else to do rn, but I'll try looking into it again in a bit if the issue isn't solved

[paper radish]

Thanks!

[sick nacelle]

Been at it since Friday and haven't made any progress. I think just figuring out how to think about going about this is difficult for me. Its due tomorrow.

-So right now I have one table that counts all the users, is grouped by receipt_created_at where trial_period is 'false'

-So should I create another table that counts the users that has the next created_receipt_date within 30 days after the current receipt_expired_date?

[tiny talon]

Hello its possible use MySQL server for create database in a server and connect with mobil app ?

[paper radish]

ended up solving it by ditching rls for grant and revoke

[burnt turret]

lol alright 👌

[lost echo]

I am using myphpadmin for MySql, Here's the code:

query = """
            SELECT 
                EXISTS(
                    SELECT 1
                    FROM RequestItem 
                    WHERE RequestItemId = %s AND DeliverAfter < %s
                    LIMIT 1
                );
            """

            time_now = datetime.datetime.now()
            values = (request_item_id, time_now)

            db_cursor.execute(query, values)
            result = db_cursor.fetchall()[0][0]

            if result == 1:
                return True

            elif result == 0:
                return False```

My question is:
in `datetime.datetime.now()` I can do something like `datetime.datetime.now(pytz.timezone('US/Pacific'))` to get the datetime of a specific time zone. But say my server is not in the same timezone. How will the date time be compared? Based on the server datetime or the machine I'm on?

please mention me in your response, thanks 😄

[paper radish]

It's probably not the best idea but it's easier so 😅

[burnt turret]

yeah lol

[paper radish]

And what I would be doing with RLS can be more easily done with GRANT + REVOKE

[wanton dagger]

How do I fetch the bottom most row in an sqlite3 table?

[harsh pulsar]

you don't need to make 2 tables, but you will probably need a subquery

[harsh pulsar]

sqlite tables shouldn't be treated as "ordered". however you can select the row with the largest id value

[wanton dagger]

how?

[harsh pulsar]

SELECT *
FROM mytable
ORDER BY id DESC
LIMIT 1

[wanton dagger]

try:
        while True:
            cur.execute(f'SELECT User, Language FROM {tb_n} ORDER BY id DESC LIMIT 1')
            row = cur.fetchone()
            print(list(row))```

it spams on for infinity because of the while loop

but the limiter should limit it, correct?

[harsh pulsar]

no?

the limit just limits the number of rows in the query

if you are making the same query in an infinite loop then the database will just keep re-running the query

computers are less magical than you think

if you tell your program to loop, it will loop

[sick nacelle]

this is what I have so far. I believe this is giving me how many purchases were made each month being grouped by account_id and payment_id. But you mentioned a subquery, so I shouldn't use a join right?

select sk_ret.account_id, sk_ret.payment_id, date_trunc('month', sk_ret.receipt_created_at)::date as month, count(*) 
from skann_table sk_ret
inner join (select account_id, payment_id
            from skann_table sk_2
            group by 1, 2)
            sk_2 on sk_2.account_id = sk_ret.account_id 
where trial_period_flag = 'false'
group by 1,2

[harsh pulsar]

you are joining with a subquery here, that's fine. you are maybe thinking of "correlated subqueries". also why are you using column numbers for grouping here? i don't think it's good style unless it's necessary (i.e. because the column is computed and not just a name)

[sick nacelle]

only groupipng by account_id, and payment_id so the relationship between the two joined tables are 1 to 1. Though Im pretty confused on this. I didn't think this would feel so complicated lol. So should the subquery be total users by month or users retention count by month? I think Im just confused on how to solve this step by step to get the right solution.

[harsh pulsar]

ultimately you need something like this, right?

user_id | month | was_member_last_month | is_member_this_month

i think you're overthinking it

[sick nacelle]

So this is the prompt.

A retained user is someone who paid both previous month, and current month. Using SQL calculate month over month retention rate for users who are no longer in the trail period

-I was thinking the output would look more like
month_year | number_of_retention_users_per_month | total_users_per_month | retention_rate

[harsh pulsar]

so are there just 2 months here?

it's kind of a vague prompt

i was not suggesting a final output, i was suggesting an intermediate structure from which you can easily derive the final output

[sick nacelle]

I think about 6 distinct months

[sick nacelle]

6 distinct months i believe

[sick nacelle]

ahh okay. But yeah I mean thats the prompt. I know how to do this in python but have no idea how to go about getting to this in sql. Soo I originally started with my first query as

select account_id, payment_id, receipt_created_at, count(*) as total_users
from skann_table skt
where no_trail_flag = 'false'

should I have started this another way?

[harsh pulsar]

hard to say how exactly you should have started... it's maybe better to work backwards from your goal

you rightly identified that receipt_created_at can be used as a proxy for the most recent month in which a user was subscribed

ok, so now you can build up into

account_id | receipt_month

and then you can self-join WHERE t_curr.receipt_month = t_prev.receipt_month + 1

which is more or less the crux of the stackoverflow post i sent

and from there you just have to group by "current month" and count the number of users who were a member in both months

[sick nacelle]

you can do joins in where clauses?

[harsh pulsar]

FROM a
  INNER JOIN b
    ON a.x = b.y

is syntactic sugar for

FROM a, b
WHERE
  a.x = b.y

but you can of course use JOIN syntax for this too

imo you don't really understand joins until you understand why those two queries are semantically equivalent

[sick nacelle]

So you're basically just doing the join in the from clause for the second one using the where clause as the joins

[harsh pulsar]

yes

[sick nacelle]

But if its possible to do this without joins, I'd like to try that way.

[harsh pulsar]

i don't think it is, nor do i see why you would want to avoid joins

[sick nacelle]

thought it would be easier but if its not possible then okay

[sick nacelle]

so is this giving me user retention by month?

select sk_curr.receipt_created_at, count(*) as total_users
from skann sk_curr
inner join (select receipt_created_at
from skann sk_prev
group by 1
) sk_prev on sk_curr.receipt_created_at = sk_prev.receipt_created_at + 1
group by 1

[harsh pulsar]

not unless receipt_created_at is a month?

[sick nacelle]

Sooo thats where I'd be extracting month and year from.

But when I try using `date_trunc('month', receipt_created_at)::date as month. I get an error. But when i run it like this I get something back

[harsh pulsar]

what error? what did you try?

you might want to "pre-compute" that with yet another subquery or a CTE

you could probably also do this with lateral if you wanted to be really slick

https://stackoverflow.com/a/65847555/2954547

[mellow pond]

how do i make a compute and cpu ???????

hellooooo

need help make computer

[sick nacelle]

nvm it ran and returned the number of users by month. So when I add these numbers up they equal the total number of users who aren't in the trial period. Sooo just to reiterate and be clear

sk_prev on sk_curr.receipt_created_at = sk_prev.receipt_created_at + 1

is self-joining the two tables based on current/previous users for that given month?

-when i first started looking at this I thought I would've had to do something like lead(receipt_created_date) - receipt_expired_date < 31 31 being the number of days in between.

[simple grove]

really?

[harsh pulsar]

depends on how you define a "month". the important part is the self join

[sick nacelle]

so how do I get total users for that month?

[harsh pulsar]

at this point i think you're asking questions that you should know the answer to, if you're applying to a data engineering role

[sick nacelle]

not a role, a bootcamp assignment. but thanks! Ill try to figure out the rest

[harsh pulsar]

ah, i misunderstood. but yes, i think you have all the tools you need at this point

(hint: don't forget about count(*) ...)

[true hatch]

Is PostgreSQL pretty much the industry standard when it comes to data bases out in the wild?

[harsh pulsar]

Not "the" but "one of"

I think it's common in small projects nowadays

[true hatch]

Any big differences between MySQL and Postgres?

[harsh pulsar]

Not that big, they support different sets of extensions

Postgres has a nicer collection of data types and a lot more functions

(imo)

[faint elm]

with pymongo.update_one(original_dict,new_value)

can i just call the dict object for the first param. ? jw cuz if i print the dict object it wont return a dict even tho it is one i theory

[cedar trellis]

create database Man ;

Name varchar(20),
Height int ,
Weight int ,
Salary int
 ) ;
 
 insert into Abilities (Name,Height,Weight,Salary)
 
 values
 ('Aniket',5,72,10000),
 ('Karan',6,78,20000),
 ('Aakash',5,58,15000),
 ('Divesh',5,55,288000) 
 
 select * from Abilites;```

[cedar trellis]

i'm getting error on this

[grim vault]

Abilities != Abilites

Also missing the ; before the select (or after the insert).

[balmy ivy]

Hello anyone know how SQL data convert json file using python

[balmy ivy]

@grim vault please check if let me know

[storm mauve]

don't ping random people

You want to convert a JSON file to a SQL database?

[balmy ivy]

No I want convert a SQL database to Json file

[storm mauve]

is there any reason for you to want to do that?..

you can read from it then convert each row to a dictionary in a json array, but usually converting structured data (sql, csv) to unstructured or semi-structured (nosql, json, xml) does not makes much sense

[balmy ivy]

Ok

[wheat olive]

sqlalchemy question:
What is the way to store datetime.timedelta objects?

[rocky mountain]

Hi guys
have you ever seen this error in DBeaver
SQL Error [16777232]: Query failed (#20211004_115307_00151_s2r9w): Error reading tail from s3://some-bucket/folder/folder/part-00010-0287d64b-292f-428e-9da5-10e61bd353c1-c000.snappy.parquet with length 16384
I have delta table in S3

[wheat olive]

https://docs.sqlalchemy.org/en/14/core/type_basics.html#sqlalchemy.types.Interval
found it

[fair crescent]

Hi, I have these Object types and Object table. ```sql
CREATE OR REPLACE TYPE Student_T AS OBJECT
(Name VARCHAR2(30),
Phone INTEGER,
Course VARCHAR2(30))
NOT FINAL;

CREATE OR REPLACE TYPE PostGrad_T UNDER Student_T
(ResearchGrantAmount_T INTEGER,
ResearchArea VARCHAR2(30))
NOT FINAL;

CREATE OR REPLACE TYPE Undergrad UNDER Student_T
(Academicyear NUMBER,
status VARCHAR(5))
NOT FINAL;

CREATE TABLE COLLEGE_STUDENTS OF STUDENT_T; ```

I'm having issues understanding inheritance in terms of inserting rows.
I need to insert a row for both PostGrad_T and Undergrad but I'm not sure how, could someone help or show an example of how it should be done?

[harsh pulsar]

what database supports this?

i don't know the answer but i'm pretty good at finding things in documentation

[fair crescent]

I'm using oracle apex

[harsh pulsar]

it looks like you can insert instances of Undergrad_T into that COLLEGE_STUDENTS table

https://docs.oracle.com/en/database/oracle/oracle-database/19/adobj/inheritance-in-sql-object-types.html#GUID-FC96C63D-4DE4-4C4A-9C7D-A6E945644A0B

[zenith shadow]

Anyone have any experience with peewee?

I can't insert a string into my database, as it simply does nothing

https://github.com/coleifer/peewee/issues/2469

[ionic pecan]

looks like that was solved

[paper radish]

Quick question

In postgres I'm trying to use RLS to check if something a user is submitting in a query matches something in my db.

Got the from db part done now just need to know how to get one of the insert values out

[paper radish]

any1 know how to do this?

[paper radish]

no response it's been an hour

[wispy comet]

Are you asking how to make a SELECT statement

[wary spire]

I am having a problem with MongoDB

and I am very new to it..

[wary spire]

basically how do I check if an object IN an array which is IN a document exists?

for ex, this is my collection

now you see, inv here is an array

I want to check wether a specific item exists in the array.

For example, if lock exists, return True and how much exists.

[burnt turret]

you need to check the values that are going to be inserted?

you'll need to use WITH CHECK in the RLS policy then (instead of USING)

[steel rover]

so im going to add a database to my discord bot, and because i didnt find any solution for accessing the db like mongo, i'll be transferring the db files between both my pc's when i want to work on them. my question is, how big does the file get? because if it gets as big as a few GB's, that'll be a problem for me

[tall anchor]

i dont think it will be that big(but depends a lot on the sample size)

[steel rover]

oh ok

[tall anchor]

r u using django for accessing the db

[steel rover]

no

[tall anchor]

u should use django tho it has some problems with windows

[steel rover]

i'll look into that

[tall anchor]

django makes database managament a lot easier

im making my personal website using django

[steel rover]

im not making a website actually

[tall anchor]

ik

[steel rover]

im making a discord bot

[tall anchor]

but django is still great for databases

[steel rover]

i'll check that out

[regal drift]

Hello can someone help me with a sql query i'm trying to join two tables and one of the columns is a sum value.
Because the values are repeated from the second table the sum is wrong.
how do i correct this ?

[wintry iron]

what is the best way to convert a sqlite output from a single row to a vertical list, and also remove any symbols? I just want the straight text

[regal drift]

I used to export the results of query as csv before but that was in postgres

[wintry iron]

ah ok, at this point I'm able to get the output of a column into a row, but i need it to be a vertical list for a discord bot I'm making

[harsh pulsar]

are you trying to do a find query? it looks like inv is not an array, but an object

@wary spire but if you do have an array, you can treat the array as if it were a single field, as in https://stackoverflow.com/a/18148872/2954547, or use $elemMatch https://docs.mongodb.com/manual/reference/operator/query/elemMatch/#mongodb-query-op.-elemMatch

[fading patrol]

Show your code?

[wary spire]

i tried these but aint working

i went simple

def check_for_item(user, item):
    d = db.collection.find_one({"uid": int(user.id)})
    try:
        if d["inv"][item] >= 1:
            return True
    except:
        return False```

i did this

[harsh pulsar]

def check_for_item(user, item):
    result = collection.find_one({
        "uid": int(user.id),
        f"inv.{item}": {"$gte": 1},
    })
    return result is not None

like that maybe?

[wary spire]

no i had to do try except

cos some errors were coming up

and the way i did is working perfectly

[harsh pulsar]

except: is almost always a bad idea

it catches too many things

what were the errors?

i assume you were trying to catch KeyError

def check_for_item(user, item):
    d = db.collection.find_one({"uid": int(user.id)})
    return d.get("inv", {}).get(item, 0) >= 1:

def check_for_item(user, item):
    d = db.collection.find_one({"uid": int(user.id)})
    try:
        return d["inv"][item]
    except KeyError:
        return False

note that in your code, you return None if the item exists but is 0

[vestal barn]

Hi, I need to create a "List of Values", inside the dictionary key - "Phone". so that whatever the input the users give, I can append to the already created JSON file, instead of overwriting it. Any help?

Hi, I need to create a "List of Values", inside the dictionary key - "Phone". so that whatever the input the users give, I can append to the already created JSON file, instead of overwriting it. Any help?

Hi, I need to create a "List of Values", inside the dictionary key - "Phone". so that whatever the input the users give, I can append to the already created JSON file, instead of overwriting it. Any help?

def CreateJson(data):
    with open("Databasemgmt/thanos.json", 'w') as f:
        json.dump(data, f, indent=2)

data_var = {}

data_var['name'] = input("Enter your name: ").lower()
data_var['age'] = input("Enter your age: ").lower()
data_var['phone'] = []
data_var['phone'].append() = input("Enter your phone: ").lower()
data_var['address'] = input("Enter your address: ").lower()
data_var['id'] = {}
data_var['pass'] = {}

[harsh pulsar]

so that whatever the input the users give, I can append to the already created JSON file, instead of overwriting it.
you can't modify json data in place. you must modify it in memory and overwrite the file. this is inherent in the design of modern filesystems in general and json in particular

if you need to modify data in-place, consider using a database

as they say, "json is not a database"

[vestal barn]

Which is why, I'm creating a "list" object for phone number, so that later on, any new entries made can be appended to that list, thereby retaining the existing data and also adding the new one at the same time..

This could be a work-around right?

[harsh pulsar]

no, it doesn't work like that in json

you can make a list in python, sure

but you can't make a json array and just expect it to magically work

[strong tulip]

I'm working on a Goodreads-esque book tracking platform, but having some trouble wrapping my head around how to structure the PostgreSQL database I'm connecting to with Flask

What I'm hoping to achieve is something like the below image.

• many-to-many relationship between books and users; one book can have many readers; one reader can have many books
• many to one relationship between categories and users; one user can have many categories they shelve their books in
• many to many relationship between categories and books; one book can be in multiple categories; one category can have multiple books

However, this is made even more complicated by the fact that a category can be set to 'ordered', so the order of the books displayed can be set. With this constraint, I'm at a loss at how to structure the DB. Any suggestions?

So far I have user and book models, which are connected to each other through a many-to-many helper model (which also serves to keep track of the users library). I'm confident on connecting the users to the categories, but not sure about how to proceed afterwards. Make another helper table to connect categories and books? And how will the order be set, where a book can have multiple 'current_order' fields based off the category?

[torn sphinx]

can someone help me connect my mongo database

[torn sphinx]

what and the fuck

is that

[harsh pulsar]

i think another many-to-many table of books and categories makes sense like this:

create table m2m_book_category (
  category_id integer,
  book_id integer,
  book_order_in_category integer default null,
  primary key (category_id, book_id)
)

and a many-to-many table of users and books:

create table m2m_user_book (
  user_id integer,
  book_id integer,
  primary key (user_id, book_id)
)

[harsh pulsar]

that's their diagram of their database system

[harsh pulsar]

what have you tried so far?

[torn sphinx]

i need help with the connection

i'm willing to let you have the thing

if you can help

[harsh pulsar]

i'm not interested in "the thing"

[torn sphinx]

the database connection url

[harsh pulsar]

i suggest reading the pymongo or motor documentation

i'm not interested in the database connection url, and nobody else here is either

[torn sphinx]

every yt tutorial i watch doesn't work

orrr

i'm doing it wron

[harsh pulsar]

probably, mistakes happen. that's why i asked what you already tried

!paste post your code, use this site 👇

[delicate field]

Pasting large amounts of code

If your code is too long to fit in a codeblock in discord, you can paste your code here:
https://paste.pythondiscord.com/

After pasting your code, save it by clicking the floppy disk icon in the top right, or by typing ctrl + S. After doing that, the URL should change. Copy the URL and post it here so others can see it.

[harsh pulsar]

you can/should remove any "real" information: your name, database credentials, passwords, etc.

[strong tulip]

Thanks for the suggestion, I'll take a stab at doing it this way. Was just a bit worried that it would be redundant to have both a category and categoryassociation.

[harsh pulsar]

meh! if you don't have any category "metadata" then you can just omit the category model entirely, although i imagine you'd at least have a name or something

[strong tulip]

yes, I do have a name, description, and then private/public boolean. So will probably just keep them both like this

[torn sphinx]

hey am new to databases is sqlite3 a good place to start ?

am trying to do a password manager

[paper radish]

I was using with check still didn’t work. Got help from a Supabase dev, idk why they helped me but they were very helpful lol

[harsh pulsar]

yes

[paper radish]

this is my query

(
  SELECT
    raw_user_meta_data->>'provider_id'
  from
    auth.users
  WHERE
    id = auth.uid()
) = new."DiscordID";

rls query

new."DiscordID" is supposed to grab what the user is trying to insert

I get Error adding policy: syntax error at or near ">"

because it auto corrects to - > >

this just isn't working

solved part of the problem

SOLVED!

[paper radish]

Or not

/:

[vestal barn]

JSON accepts lists too... :/
it did work anyways.

[harsh pulsar]

you still have to overwrite the entire json file

once you load the data into python there's no such thing as "json" anymore

[vestal barn]

Yeah, that's the same in any case right.

[harsh pulsar]

it's all lists, dicts, numbers, strings

right. that's what i was trying to emphasize

[vestal barn]

Yep. Yep. got that.

Ah! Okay.

my bad. was out of my head last night, after all of those things and the code not working 😅

Many suggested me to use SQL, with python.... as DB instead of JSON, any suggestions on this?

I don't really have a big data structure, I just want to save personal informations of people, which would be their basic info and their digital IDs, etc....

If i use SQL, is it necessary for me to have mySQL installed in the PC? like I'm not sure, but JSON works anywhere without any pre-requirements right? how about SQL?

Any other DB suggestions?

[harsh pulsar]

sqlite is a sql library that doesn't require you to install any software

it's a single file that you can interact with by importing the sqlite3 module that is included with python

!d sqlite3

[delicate field]

sqlite3

Source code: Lib/sqlite3/

SQLite is a C library that provides a lightweight disk-based database that doesn’t require a separate server process and allows accessing the database using a nonstandard variant of the SQL query language. Some applications can use SQLite for internal data storage. It’s also possible to prototype an application using SQLite and then port the code to a larger database such as PostgreSQL or Oracle.

The sqlite3 module was written by Gerhard Häring. It provides a SQL interface compliant with the DB-API 2.0 specification described by PEP 249, and requires SQLite 3.7.15 or newer.

[harsh pulsar]

however you can also use a graphical program to interact with it, e.g. https://sqlitebrowser.org/

and yes, if you need to frequently modify or query small sections of the data, a database is a good choice

json is good for when you need to save or load data at the start or end of an application, e.g. a config file, or for sending pieces of data between programs or machines

[vestal barn]

Oh.. okay

Thanks. I'll study about SQL first then... and understand it.

[hearty ermine]

With SQLite3, is it better practice to have a single db/cursor variable and use that in all of my code, or to open a DB/cursor, do stuff, close it each time? It's for a Discord bot, and there's quite a lot of calls at this point.

[regal drift]

                             CASE
                                 WHEN ap.payment_type = 'inbound' THEN aml.credit
                                 WHEN ap.payment_type = 'outbound' THEN aml.debit
                               END                 AS amount,
                               CASE
                                 WHEN ap.payment_type = 'inbound' THEN aml.second_credit
                                 WHEN ap.payment_type = 'outbound' THEN aml.second_debit
                               END                 AS second_amt,
                               CASE
                                 WHEN ap.payment_type = 'inbound' THEN aml.third_credit
                                 WHEN ap.payment_type = 'outbound' THEN aml.third_debit
                               END                 AS third_amt```

is there a better way to write this part without repetition

its part of a sql query

[grim vault]

Two selects joined with an union all and the condition in the where?

Or if there are only two options use the else to get rid of one repetition.

[harsh pulsar]

sometimes sql is just really verbose

[velvet sparrow]

I was wondering if anyone could enlighten me as to what this command means, or does exactly: sqlite3 database.db < schema.sql. I am using a SQLite database in Python, in a Flask app. I followed a tutorial and had to use that command, but when I tried to alter the schema.sql code to fit my project, everything failed. So I want to understand more about this. The documentation is not helpful.

I understand that it's piping the schema.sql file into the sqlite3 command's input, but I don't know what that means really.

[harsh pulsar]

sqlite3 can read sql commands/queries from stdin. so schema.sql should be a text file containing various CREATE TABLE, INSERT, etc. commands, and sqlite will run them one at a a time. < is typical unix shell syntax for redirecting standard input from a file

[velvet sparrow]

Okay, thanks. I will come back at a later date because my next question is going to be able the structure of the schema.sql file, because obviously something's not working in the schema.sql file I wrote.

[harsh pulsar]

post it here

!paste

[delicate field]

Pasting large amounts of code

If your code is too long to fit in a codeblock in discord, you can paste your code here:
https://paste.pythondiscord.com/

After pasting your code, save it by clicking the floppy disk icon in the top right, or by typing ctrl + S. After doing that, the URL should change. Copy the URL and post it here so others can see it.

[harsh pulsar]

and show us what error(s) you get

[velvet sparrow]

Here is the code.. I will have to come back later for the error(s) because this is something I worked on a year ago.. so I will have to go back into the project and follow the 34 minute tutorial a second time.. to get the code running them make my modifications to reproduce the error.. https://paste.pythondiscord.com/xabopexuze.sql

[harsh pulsar]

that looks valid to me

[velvet sparrow]

I'm just trying to create a table called refcards that has an id and two text fields. Yeah well I will come back with the actual error. Maybe it wasn't because of the schema.sql file and something else in my models.py file..

I'll probably have to ask the question in web-development because now it's going to become a question about my app.py and models.py files in a Flask app. Thanks for the input anyway.

[harsh pulsar]

good luck

[burnt cloak]

mysql> create table userprefix(client_id bigint not null primary key, username TEXT not null, prefix TEXT not null default !);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '!)' at line 1

what wrong

[velvet sparrow]

Thanks. 🙂

[burnt cloak]

.

[harsh pulsar]

mysql apparently doesn't like the !

[burnt cloak]

so how can i fix it? i mean in the type of column prefix

[harsh pulsar]

what's the ! supposed to mean?

[burnt cloak]

str

[harsh pulsar]

who told you that? i haven't used mysql in years, but i don't remember that ever being valid syntax

is it in the docs?

[burnt cloak]

i searched for it and it said the TEXT type is for str

[harsh pulsar]

yes, of course. i am asking about !

why is that there? the ! is the cause of the error

[burnt cloak]

but i want to set the default of column is !

how can i

[harsh pulsar]

it's a string, so you need to put it in single quotes

'!'

[rotund mural]

ok

[burnt cloak]

oh ok thanks

[burnt cloak]

Traceback (most recent call last):
  File "C:\Program Files\Python39\lib\site-packages\discord\client.py", line 343, in _run_event
    await coro(*args, **kwargs)
  File "c:\Users\luffy\Desktop\All Things\Code File\Python\Discord.py\MineHardcore.py", line 164, in on_message
    abc.execute("INSERT INTO exp VALUES(%s,%s,%s)",(message.author.id,message.author,"!"))
  File "C:\Users\luffy\AppData\Roaming\Python\Python39\site-packages\mysql\connector\cursor.py", line 556, in execute
    psub = _ParamSubstitutor(self._process_params(params))
  File "C:\Users\luffy\AppData\Roaming\Python\Python39\site-packages\mysql\connector\cursor.py", line 435, in _process_params
    raise errors.ProgrammingError(
mysql.connector.errors.ProgrammingError: Failed processing format-parameters; Python 'member' cannot be converted to a MySQL type

what wrong

[harsh pulsar]

Python 'member' cannot be converted to a MySQL type
that's what

[burnt cloak]

idk

help me pls

[lime lily]

import mysql.connector
class databases(object):
     def __init__(self,password,database):
        self.database = database

        self.password = password

        self.con = mysql.connector.connect(host="localhost",user="test",password=password,database=database)

        self.curse = self.con.cursor()```

tried to make this a module and import it into another file but it wont work

any idea why?

[fading patrol]

Import how? Won't work how? Check your file paths and __init__.py

[harsh pulsar]

this probably isn't a databases question. show your file layout

[torn sphinx]

https://felony.is-a.fail/5b_w7khNl

cluster = MongoClient("my mongo link")
database = cluster["Feature"]
``` is returning ```database = cluster["Feature"]
TypeError: string indices must be integers```

[harsh pulsar]

that isn't what's in your screenshot, cluster = ("...) looks like it's a string wrapped in parentheses

line 6

[torn sphinx]

okay can you help me with one more thing

My connection is saying it's expired

[faint elm]

when trying to update a mongo value i get
pydiscord.ext.commands.errors.CommandInvokeError: Command raised an exception: WriteError: Cannot apply $addToSet to non-array field. Field named 'response' has non-array type string, full error: {'index': 0, 'code': 2, 'errmsg': "Cannot apply $addToSet to non-array field. Field named 'response' has non-array type string"}

on
newdict = {'$addToSet':{"response":response}}
col.update_one(find,newdict)
nothing in the docs stating about labelling it as a list when inserting, before updating so

already a value in response for there to add to

[torn sphinx]

using mongodb, how do i find a document and then add on to it?

[torn sphinx]

how can i get mongodb' doing

ping , or how long it takes me to insert something

[velvet sparrow]

I'm having a hard time inserting a timestamp into my table, in a SQLite3 database in Python, in my schema.sql file. I've tried every possibility I could find on-line and it's not working. I got it to work for 5 seconds, but then it broke again. How do I write it in the create table expression, the way I write id integer primary key autoincrement??? I think I've got the rest of the code working. Or maybe it's some browser cache thing in Chrome, I don't know. I'm writing a Flask app. Thanks in advance.

[harsh pulsar]

there is no native datetime/timestamp column in sqlite. you have to either use text and pick a standard format, or use numbers and use epoch seconds

[velvet sparrow]

I was able to get it to work with this:

created_at DATETIME DEFAULT (STRFTIME('%d-%m-%Y   %H:%M:%S', 'NOW','localtime'))

[harsh pulsar]

ah yeah. note that those are really going to be strings

you can register "converters" and "adapters" in the python sqlite3 library to automatically convert

[velvet sparrow]

That's what I want, though, strings.

I'm just using it to put a timestamp at the top of a note in a notepad...

I should have specified that.

[harsh pulsar]

yeah, should be fine then

[velvet sparrow]

I really had to work hard to get at this solution. I tried every possibility I could find. Nothing worked until poof it finally worked. I don't really know what I'm doing in SQLite.. Someone suggested I practice using it, so that's what I've been trying to do, but boy is the documentation lacking, in my view. I guess it's probably because I was looking for a nonexistent function... lol

[harsh pulsar]

yeah, sqlite has okay "reference" docs, but for getting started you will want to follow some more general sql things

however there is https://sqlbolt.com

sqlite also has some quirks compared to other databases, especially:

• column data types are "recommendations", not strict checks
• many fewer data types

[velvet sparrow]

Thanks. I will check this out. 🙂

[elfin palm]

Hello All, I am looging for a python 3 package to connect sql server. My script runs on a Redhat machine. So I am not sure which library should I use. It would be if I get advise

[grim vault]

$ sqlite3
SQLite version 3.35.5 2021-04-19 18:32:05
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> .mode table
sqlite> drop table if exists test;
sqlite> create table test (
   ...> id integer primary key,
   ...> created_at_d default current_date,
   ...> created_at_t default current_time,
   ...> created_at_ts default current_timestamp
   ...> );
sqlite> insert into test(id) values (1), (2), (3);
sqlite> select * from test;
+----+--------------+--------------+---------------------+
| id | created_at_d | created_at_t |    created_at_ts    |
+----+--------------+--------------+---------------------+
| 1  | 2021-10-07   | 06:48:44     | 2021-10-07 06:48:44 |
| 2  | 2021-10-07   | 06:48:44     | 2021-10-07 06:48:44 |
| 3  | 2021-10-07   | 06:48:44     | 2021-10-07 06:48:44 |
+----+--------------+--------------+---------------------+
sqlite> ```

[grim vault]

I would suggest using UTC as timezone not localtime. It should be easy to convert for display purpose. And if using '%Y-%m-%d %H:%M:%S' as format you can use the column for an chronologic order by.

[quartz gazelle]

i wanna db with a ui run on 32 bit os

[velvet sparrow]

I thought I tried created_at default current_timestamp and it didn't work. I'll try again. Thanks.

[velvet sparrow]

I tried

created_at default current_timestamp

but it gave me a time 4 hours off... for some reason, 4 hours later than the actual current time where I live.

[grim vault]

Yes, that's because it's UTC.

[drowsy sky]

Hello, i have some memory leak issue with mariadb

import mariadb
class Maria:
    def first_mariadb_connect_and_basics(
        self,  host_=config.db_host
    ):
        # Simple connect to mariadb server
        self.conn = mariadb.connect(
            user=config.db_user,
            password=config.db_passwd,
            host=host_,
            port=config.db_port,
        )
        ....

while True:
    maria = Maria()
    items = maria.first_mariadb_connect_and_basics(
        "test", "items", config.db_host
    )
    ....
    maria.conn.close()

Anyone have a idea please ?
Same issue with only connect and close while, and tracemalloc detect memory issue on mariadb.connect
Here log from tracemalloc after 1min of load

i never have a issue with that before, but now my script need to be load permanently, i reach out of memory after some hour

[sacred finch]

Hello, I have some code that looks like this:

async def my_method(session: AsyncEngine) -> None:
    async with session.connect() as conn:
        query = select(class_a, class_b) \
            .join(class_b)

        result = await conn \
            .stream(query)

        result = await result.all()

This returns me a list of primitives instead of a list of (class_a, class_b), is there some way to change that?

[harsh pulsar]

what's AsyncEngine?

but in general, the answer is to use something like marshmallow, cattrs, or pydantic to do the "deserializing" from "raw data" to "objects"

[sacred finch]

im using the async version of sqlalchemy and the thing is that in queries where i have no joins (so i return one table) the conversion is done automatically

so I am assuming that there is a way to tell sqlalchemy hello the output is a tuple of these 2 classes and not a list of strings

async engine is from sqlalchemy.ext.asyncio, its the async version of the normal sync engine

[harsh pulsar]

ah, that i'm not sure