databases

[solar pollen]

What are some good resources for sql, postgres, and python?

Edit: I found this https://www.khanacademy.org/computing/computer-programming/sql, and I have some other things for postgres and python already.

[brazen charm]

sqlbolt do a good bit of stuff

but i learnt most of my stuff with postgres with the normal SQL tutorial with w3schools and then the DO install tutorial

[torn sphinx]

how do i do that sir?

[proven arrow]

@elder ferry you can have the query to select the department names where the name/id is in a subquery

So the where clause is something like:

WHERE dept_id IN (SELECT dept_id FROM employees GROUP BY dept_id HAVING max(age) < 40);

So it transaltes to: SELECT ... WHERE dept_id IN (department ids where every employee is younger than 40)

[elder ferry]

@proven arrow Ah thank you again for your response

like that?

[proven arrow]

Yeah specify the table name before it

Ambiguous is when you have multiple tables in the query that have same column name, so it doesn't know which one you are referring to

[proven arrow]

And try it, I have no idea what your tables look like, but I just sent something you could follow.

[sinful condor]

@proven arrow what is the newest version mine is at 3.22.0

[proven arrow]

I don't remember you'd have to check their release page, but then reason for your error is because window functions was introduced in 3.25

[sinful condor]

oh ok then ill try to update it

[solar pollen]

@brazen charm Thanks, those are great

[sinful condor]

@proven arrow the aiosqlite is the most updated version but the sqlite version isnt

do I have to update sqlite or something

[torn sphinx]

Help

With motor

[sinful condor]

I am not able to update python because I am using a hoster but can I still update sqlite

it is on 3.22.0 and I need at least 3.25

[proven arrow]

Easiest way is to download the binaries from the sqlite download page and replace it with the one python has.

For example, on windows you can download the pre compiled DLL, and then replace it with the sqlite3.dll file in your python installation folder.

[sinful condor]

ok I can download it but is there a way I can replace it in cmd because I use repl.it hosting

you can replace stuff through terminal but there isnt a way I can replace it in the python folder

[proven arrow]

Not sure. I don't use repl. If they allow you to manage the files then yeah otherwise 🤷‍♂️

[sinful condor]

what is the file directory for the file

[hazy smelt]

Hi so I am using an online MySQL database server and the table name is on the form of bytearray, so how do I fix it, like how do I get the name of the table then

The bot is ready
[(bytearray(b'Cricket'),)]
571299307052072980
Ignoring exception in command start:
Traceback (most recent call last):
  File "/data/user/0/ru.iiec.pydroid3/files/aarch64-linux-android/lib/python3.8/site-packages/discord/ext/commands/core.py", line 83, in wrapped
    ret = await coro(*args, **kwargs)
  File "<string>", line 674, in start
  File "/data/user/0/ru.iiec.pydroid3/files/aarch64-linux-android/lib/python3.8/site-packages/mysql/connector/cursor.py", line 551, in execute
    self._handle_result(self._connection.cmd_query(stmt))
  File "/data/user/0/ru.iiec.pydroid3/files/aarch64-linux-android/lib/python3.8/site-packages/mysql/connector/connection.py", line 490, in cmd_query
    result = self._handle_result(self._send_cmd(ServerCmd.QUERY, query))
  File "/data/user/0/ru.iiec.pydroid3/files/aarch64-linux-android/lib/python3.8/site-packages/mysql/connector/connection.py", line 395, in _handle_result
    raise errors.get_exception(packet)
mysql.connector.errors.ProgrammingError: 1146 (42S02): Table 'dOX6EHic32.CRICKET' doesn't exist

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

Traceback (most recent call last):
  File "/data/user/0/ru.iiec.pydroid3/files/aarch64-linux-android/lib/python3.8/site-packages/discord/ext/commands/bot.py", line 892, in invoke
    await ctx.command.invoke(ctx)
  File "/data/user/0/ru.iiec.pydroid3/files/aarch64-linux-android/lib/python3.8/site-packages/discord/ext/commands/core.py", line 92, in wrapped
    raise CommandInvokeError(exc) from exc
discord.ext.commands.errors.CommandInvokeError: Command raised an exception: ProgrammingError: 1146 (42S02): Table 'dOX6EHic32.CRICKET' doesn't exist ```

[frail marsh]

anyone able to help in #help-cupcake

[hazy smelt]

Pls ping me if you know it

[proven arrow]

Yes, it's just a text file

.sql is just file extension name to give it some meaning that it's for sql

[feral thorn]

How can i connect my that database to my website which i have installed on my VPS?
Been watching one Tutorial for web Dev and he's using Mongodb...but i don't wana use it as i am using Pgsql in my bot

[frigid glen]

@feral thorn website is in VPS, database is where?

[feral thorn]

In same VPS...i was confused on it for a bit. Sorted bud, thanks

[sinful condor]

what is wrong with this code it says something with the as async with db.execute('SELECT userguild,xp,level FROM level ORDER BY xp DESC as user_rank WHERE userguild = ?', (userguild,)) as cursor:

[versed robin]

Can you see and issues in thie Sqlalchemy syntax:
sql_filter_items = f"select * from items where name like '%{item_name}%'"

[frigid glen]

@sinful condor should as be AS ?

[radiant elbow]

You've got ORDER BY before WHERE, it needs to be after

@sinful condor

[sinful condor]

Ok thanks I’ll try that when I can

Don’t worry about intents I’m on my phone but I tried this and got an error

.execute('SELECT userguild,xp,level FROM level WHERE userguild = ? ORDER BY xp DESC as user_rank, (userguild,)) as cursor

It says syntax error eol while scanning string literal

@radiant elbow

[radiant elbow]

I think you want:

.execute('SELECT userguild,xp as user_rank,level FROM level WHERE userguild = ? ORDER BY xp DESC', (userguild,)) as cursor

[wide hinge]

what module or library I should use to interact with postgresql database?

[radiant elbow]

asyncpg or psycopg2

[mint dagger]

SQL is good but if I only have 2 values to store, what should I use?

[versed robin]

Why not just us a JSON or XML file?

[mint dagger]

Json would work yeah

[sinful condor]

why does this code return this with no rank ('449756487510654988', 42, 3) async with db.execute('SELECT userguild,xp,level as user_rank FROM level WHERE userguild = ? ORDER BY xp DESC', (userguild,)) as cursor:

userguild is the user id

[radiant elbow]

what do you mean by "with no rank"?

you selected 3 columns, and it returned 3 values, so that looks sane to me.

[sinful condor]

I am trying to get the rank with it to put at the end of the leaderboard if they arent in the top 5 but I cant use rank because of my sqlite version

to say what rank they are

thats why I had as user_rank

[radiant elbow]

all as user_rank is doing is selecting the level column and returning it with the name user_rank

[sinful condor]

ok how can I get what I said

like

1st
2nd
3rd
4th
5th
your place

I have 1-5 but cant figure out this

I am able to get it to work on the new version but I am trying to use repl.it hosting which uses an older version of python

[radiant elbow]

maybe something like this:

select userguild, xp, level, (select 1 + count(*) from level as p2 where p2.xp > p1.xp) as user_rank from level p1 order by xp desc

[sinful condor]

ok ill try that

@radiant elbow Traceback (most recent call last):
File "/opt/virtualenvs/python3/lib/python3.8/site-packages/discord/ext/commands/bot.py", line 903, in invoke
await ctx.command.invoke(ctx)
File "/opt/virtualenvs/python3/lib/python3.8/site-packages/discord/ext/commands/core.py", line 859, in invoke
await injected(*ctx.args, **ctx.kwargs)
File "/opt/virtualenvs/python3/lib/python3.8/site-packages/discord/ext/commands/core.py", line 94, in wrapped
raise CommandInvokeError(exc) from exc
discord.ext.commands.errors.CommandInvokeError: Command raised an exception: OperationalError: near "where": syntax error

oh you put where 2 times did you mean to do that

[radiant elbow]

there's a "where where" in the middle of that that should just be one "where"

edited the one above to fix it.

[sinful condor]

ok

ok now how do I get it so it looks for the userguild that I specify

[radiant elbow]

select userguild, xp, level, (select 1 + count(*) from level as p2 where p2.xp > p1.xp) as user_rank from level p1 where userguild=? order by xp desc

[sinful condor]

ok

[torn sphinx]

MySQLdb._exceptions.OperationalError: (2006, '') this is killing me

what does it mean? 😒

Nvm figured it out. If anybody ever comes across this I had to remove mysql.init_app(app) from my program... various mysql/flask methods/imports got confused I guess, no idea why it stopped working in the first place (which caused me to mess around with alternatives) but at least its working

[tardy perch]

from tkinter import *

root = Tk()

e = Entry(root, width=25)
e.pack()
e.insert(0, "Enter your name: ")
def myClick():
hello = "Hello " + e.get()
myLabel = Label(root, text=hello)
myLabel.pack()

myButton = Button(root, text="Send", command=myClick)
myButton.pack()

root.mainloop( )

how can i store the data people input in here^^

[versed robin]

@tardy perch are you looking for a database solution or somethiing stored in a file ?

[tardy perch]

im not sure, i coded a input box, i want to store the data that people input

[versed robin]

@tardy perch Do you expect large amounts of data, and to you expect it to change a lot

[tardy perch]

probably change a lot

when i store this data, can i store more than 1 input?

import pickle
import os
os.system("clear")

IDs = [1, 2, 3]

print("Original List")
print(IDs)

pickle.dump(IDs, open("IDs.dat", "wb"))

IDs.remove(2)

print("Changed List")
print(IDs)

IDs = pickle.load(open("IDs.dat", "rb"))

print("Original List")
print(IDs)

ok i made this, instead of IDs = [1, 2, 3] i want it to be so when people input shit, i can save it

[versed robin]

you could store more input if you assign e.g. a primary key that is assigned automatically and then save in a local db

[tardy perch]

hm im not sure i understand

[versed robin]

Perhaps this can help ??
https://www.python4networkengineers.com/posts/python-intermediate/create_a_tkinter_gui_with_sqlite_backend/

[tardy perch]

@versed robin this is my code

wait wrong one

from tkinter import *

root = Tk()

e = Entry(root, width=25)
e.pack()
e.insert(0, "Enter your ID: ")
def myClick():
#hello = "Hello " + e.get()
myLabel = Label(root, text=("Thank you."))
myLabel.pack()

myButton = Button(root, text="Send", command=myClick)
myButton.pack()

root.mainloop( )

ok this is the correct one

my problem seems to be i want to save and store the e.insert(0, "Enter your ID: ") part

u can run the code, i want to save the data u input

[versed robin]

Sorry - cant help on that one

[left scaffold]

Hi, I am getting an error with some code. Can anyone help?

# Error
AmbiguousColumnError: column reference "isstaff" is ambiguous

# Code
INSERT INTO staff VALUES($1, $2, $3) ON CONFLICT (userid) DO UPDATE SET owner = True AND isstaff = True # $1, $2, $3 are being inputed 

[proven arrow]

Provide the table name before isstaff

staff.isstaff

[left scaffold]

kk

[left scaffold]

yeah that then shows this error UndefinedColumnError: column "staff" of relation "staff" does not exist

oh wait

[versed robin]

wait for what ?

[elder ferry]

Hi, does anyone know how to get this query to show the oldest employees from EACH department instead of showing just the oldest employee?

[proven arrow]

You can group by department

[dense juniper]

Hey! I'm just new to using databases, I'm using MySQL and mysql.connector.
I need help on getting the list of error codes and when those errors are raised, I tried searching on the net but I couldn't, can anyone help me with it?

[proven arrow]

You can find them all here, https://dev.mysql.com/doc/mysql-errors/8.0/en/

[dense juniper]

Thanks!

[slender mango]

this sql statement runs and gives output

select @completed_tasks;

but when i try to do the same in python i get error

self.cur.execute('select @completed_tasks').fetchone() ```

any idea why ?

Thanks in advance.

[sick perch]

hi, what are current options to consider when wanting to go for a mutli process safe key-value database?

(non-networked, in process)

[velvet gazelle]

what's the error?

[torn sphinx]

good evening, i'm tryn' to connect python to database with my WSL on windows but i keep getting this error

error

code

Please help me, thank u!

[velvet gazelle]

maybe https://stackoverflow.com/a/54683556 ?

"On Authentication method tab- select Use Legacy Authentication method"

[torn sphinx]

thankkkkkksssssssssss a billion dude

you saved a lot of time

thanks again!

but one question how to do it on cli?

[velvet gazelle]

I think this is a server side setting isn't it?

once you change the server, you should be able to legacy authenticate with a cli client

I don't know though, I'm just inferring from the stack overflow

[torn sphinx]

anyway thanks a lot bro

[whole pebble]

hey guys
I'm trying to organise my table and I've stumbled upon a problem with grouping the same values. I want to group marks (2 and 3 together as "oceny slabe", then 3.5 and 4 as "oceny srednie" and 4.5 and 5 as "oceny wybitne")

SELECT CASE WHEN ocena < 3.1 THEN 'oceny slabe' WHEN ocena < 4.1 AND ocena > 3.1 THEN 'oceny średnie' ELSE 'oceny wybitne' END AS kategoria, COUNT(*) AS "LICZBA_OCEN" FROM oceny WHERE rok_akademicki = '2005/06' AND rodzaj_semestru = 'zimowy' AND ocena IS NOT NULL GROUP BY ocena ORDER BY ocena

but what I receive in return is this, which doesn't look so neat. I know that GROUP BY is executed as one of the first commands, so most probably it's still grouping by old "ocena" values (by 2, 3, 3.5, 4, 4.5 and 5)

I tried to do it with a subquery, so that maybe I could retrieve "kategoria" and group by it, which should then group by "oceny slabe", "oceny srednie", "oceny wybitne", but no, it still gives back 6 records
do you maybe see where I'm making a mistake? what do I overlook?

[velvet gazelle]

should you group by kategoria?

OH... you can't do that either... you're going to have to group by the case statement itself

or do the subquery to create that new column temporarily

then group on that

SELECT CASE WHEN ocena < 3.1 THEN 'oceny slabe'
WHEN ocena < 4.1 AND ocena > 3.1 THEN 'oceny średnie'
ELSE 'oceny wybitne' END AS kategoria,
COUNT(*) AS "LICZBA_OCEN"
FROM oceny
WHERE rok_akademicki = '2005/06' AND rodzaj_semestru = 'zimowy' AND ocena IS NOT NULL
GROUP BY CASE WHEN ocena < 3.1 THEN 'oceny slabe'
WHEN ocena < 4.1 AND ocena > 3.1 THEN 'oceny średnie'
ELSE 'oceny wybitne' END
ORDER BY CASE WHEN ocena < 3.1 THEN 'oceny slabe'
WHEN ocena < 4.1 AND ocena > 3.1 THEN 'oceny średnie'
ELSE 'oceny wybitne' END

[whole pebble]

oh, I didn't know I could put case in GROUP BY

let me check

woah, it worked

let me just study it once again

okay, I think I get it now
I shouldn't have grouped it by a column's name "oceny", but rather by the whole condition, right?

is that the case?

SELECT kategoria, LICZBA_OCEN FROM ( SELECT CASE WHEN ocena < 3.1 THEN 'oceny slabe' WHEN ocena < 4.1 THEN 'oceny średnie' ELSE 'oceny wybitne' END AS kategoria, COUNT(*) AS "LICZBA_OCEN" FROM oceny WHERE rok_akademicki = '2005/06' AND rodzaj_semestru = 'zimowy' AND ocena IS NOT NULL GROUP BY ocena )
this is my attempt with subquery, to retrieve that temporary column, also didn't work.

[torn sphinx]

good evening, i am just stuck at a small problem, please check if syntax is correct or not INSERT INTO file(access_key, access_path) VALUES(EIvZsE, EIvZsE.jpeg);

i'm getting this error when i execute this command, any guess why it happens?

[velvet gazelle]

the values should be naming variables or have quotes on them

looks like they're not naming variables to me, but rather you just forgot quotes

[velvet gazelle]

you're still trying to group by ocena. You want to group by kategoria right?

and you want to do it outside of the subquery

[torn sphinx]

damn brooooo you r awesoooommeeeee

[whole pebble]

yes, but when I put "group by kategoria" outside the subquery, I receive an error

[velvet gazelle]

have you removed the group by ocena inside the query?

[whole pebble]

yes

[velvet gazelle]

what's the error?

[whole pebble]

it doesn't work with group by ocena, kategoria as well

though it doesnt make sense

either way

[velvet gazelle]

try putting a sum on your measure (LICZBA_OCEN) in the first line

usually you group everything you select unless it's an aggregate

so you'd have to group by the second column you select or aggregate it

you know you'll have multiple value under each category - you know how to interpret that better than I do knowing your data. Do you want the sum of those? the max? etc.

[whole pebble]

hmm, "group function is not allowed here"
even when I try to group by "sum(liczba_ocen)

I need to count them

[velvet gazelle]

you are counting them in the subquery

that returns a total

for two of each category

you likely want to sum those?

[whole pebble]

yep

I don;t want to see duplicated records

[velvet gazelle]

so select ocena, sum(LICZBA_OCEN)

at the very first line

two sum the result of the count

if they are actual duplicates (multiple entries counted)

then sum will get too much

if it's just splitting the data across two values, then sum should work

[slender mango]

Thanks for asking. I solved it

[whole pebble]

grouping by either "kategoria" nor "ocena" doesn't work

[velvet gazelle]

you want to select kategria at the top

sorry, my bad

then group by kategoria at the bottom

[whole pebble]

instead of ocena, right?

[velvet gazelle]

kategoria is the focus outside the subquery

oceana has been used to make kategoria at that point and is no longer needed

the subquery is turning ocena into kategoria

so yeah, no ocena outside the subquery, just group by kategoria and you shouldn't have to group by the measure since you're aggregating it (with a sum).

[whole pebble]

maybe now conditions are wrong?

now it seems that 2 falls into 1st and 2nd condition

2 and 3

maybe it's messing it up

let me check

[velvet gazelle]

oh, since we're already doing a count, I think the sum can't be done here

[whole pebble]

ah, okay

back to the beginning

[velvet gazelle]

right, which is why we wanted the sum

so you will probably just have to do single query, group by the full case statement

[whole pebble]

okay, just as you showed me

alright, I wanted to unserstand it with a subquery, but maybe there is no way like that

either way, I have now a proper amount of records with the single query
thank you for the big help @velvet gazelle !

you are great

[velvet gazelle]

np, I'm sure there is some clever hack, I just don't know it. I know some people would just use materialized view as an intermediate step

then they'd essentially create the intermediate table and be able to do a sum and group on it

[whole pebble]

okay, I see
no worries
have a great one!

[earnest relic]

Hey Pythoners, planning to build a PostgreSQL in web. Where should I host my database for free?

PythonAnywhere looks like they do not offer Postgres in thier free tier.

[velvet gazelle]

what's your use case? There's free postgres databases

already set up, you just sign up for an account on a shared DB

[earnest relic]

Just for learning

[velvet gazelle]

https://www.elephantsql.com/

[earnest relic]

I have already mongoDB and heroku. But this time I like to learn postgres

[velvet gazelle]

but if you are wanting to learn to setup the server, that's already done here

[earnest relic]

Thanks for the elephant!

[earnest relic]

saw that one. Never thought it is a web postgres

[kindred crow]

How do I insert values into a table using user input in py?

[fickle lagoon]

https://www.sqlitetutorial.net/sqlite-python/insert/

[kindred crow]

it's not asking for user input:/

[fickle lagoon]

you need to code that part separately. Once you have the values from the user, then you can insert them in to the table as shown in the example above.

If you're trying to figure out how to get input from the user, take a look at AtBS 2e, ch8 (https://automatetheboringstuff.com/2e/chapter8/)

[kindred crow]

i don't understand, i want the user to enter the values and in the same func I want that value to be inserted into table

[fickle lagoon]

You can put it all together in one big function... or you can separate out the database portion and the 'presentation logic'.

You're probably going to have multiple functions involving the database any way - creating the database, inserting records, reading them back, updating records, deleting records, etc.

This might be worth taking a look at: https://www.youtube.com/watch?v=iXYeb2artTE

I'm actually poking my way through my own project right now, using that as a base.

[kindred crow]

i see

I actually tried tried to it before but it was showing error, so I wanted to know if I was writing the wrong command

I'll watch the video first-

[fickle lagoon]

This is about the simplest example I can cobble together in a hurry:

`import sqlite3

conn = sqlite3.connect(":memory:")
c = conn.cursor()

create_table_sql = '''CREATE TABLE IF NOT EXISTS todo (
id integer PRIMARY KEY,
item text NOT NULL,
start_date text,
end_date text
);'''

c.execute(create_table_sql)

insert_item_sql = '''INSERT INTO todo(item, start_date, end_date)
VALUES (?,?,?)'''

item = input("Enter the To-Do item: ")
start_date = input("Enter the start date: ")
end_date = input("Enter the end date: ")

c.execute(insert_item_sql, (item, start_date, end_date))

records = c.execute('''SELECT * FROM todo;''')

for record in records:
print(record)`

Returns:
(1, 'browse reddit', '12/19/2020', '12/20/2020')

Note how getting the user input is separate from creating the SQL query to insert the data?

[kindred crow]

ohhh!! i get it

thank you

[torn sphinx]

im using motor package for pymongo

async for item in items.find({}).sort("level", -1): this works

but how do i sort a second time?

tried .sort("field1", -1, "field2", -1): and .sort({"field1": -1, "field2": -1)

[torn sphinx]

Hey guys, I am super new to databases and I am attempting to query through my mongo database, find the objects with a certain tag and append them to a list. I am getting the JSON object must be str, bytes or bytearray, not Cursor when I do this as an error code.

My code is python query = collection.find( { "authentication": "yes" } ) query_parse = json.loads(query) i = (query_parse['Phone number added']) for i in query_parse: sms_list.append(i)

[torn sphinx]

I can't be sure, but shouldn't there be a .fetchall() or .fetchone()?

I sure know it from psycopg2 but I don't know how pymongo works.

(collection.find(..).fetchall())

No scratch that, pymongo is different.

try: for x in query: print(x) to see if that works?

The problematic line is this one query_parse = json.loads(query), since collection.find() returns a cursor. So what you do is iterate the results of collection.find() and then append them to your list. Sorry for the confusion.

[torn sphinx]

yes this works

The problem I am running into is that I only want a specific part of the query appended to the list

so I tried to parse the json to get it but that isn't working, are there any other options for this?

There might be something built in to mongo that I am overlooking

That I don't know sorry 😦

[grim pier]

is it possible to find the closest match of a string using MySQL?

[torn sphinx]

https://github.com/69Vishal/cloud-service-using-python

anyone out there to test my project?

[proven arrow]

You can use sqlite, as an embedded database.

[proven arrow]

There are different options available each with their own benefits. You can look into LIKE, the SOUNDEX function, or full text search.

[torn sphinx]

am i allowed to ask database homework question here?

[narrow schooner]

!rule 5

[delicate field]

Rules

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

[narrow schooner]

I mean...

[torn sphinx]

huh?

[narrow schooner]

Look at rule 5

[torn sphinx]

oh i look at your name think maybe you can help

[narrow schooner]

Oh no, it's me who needs help

[torn sphinx]

oh right ok i shall go read rule 5 now then, thanks

[narrow schooner]

np

[elder ferry]

hello, just wondering if anyone can help? I have found the departments that have sold at least 1 product, but I need to narrow this list down to only include the departments that sell 5 or more different product types (pType)

so I need to find a query that shows me just the department 5 so dID = 5 because they sell products of 5 different product types

[low jay]

nvm i figured it out

help plz

[velvet frost]

your querry is ryt
just at last
and quantity > 5;

[elder ferry]

Sorry could elaborate on just at last and quantity > 5?

[velvet frost]

u want the output to be of only which have dIB as 5 ryt
so
quantity = 5

sry its not > its =

[proven arrow]

I dont think thats what they are after, since they already filter by quantity

They would need to group by the dID, to get IDs that sell 5+ products, and then filter using this.

[proven arrow]

SELECT * FROM newagenttickets WHERE deletedstaff IS NULL

[jovial notch]

alr

[torn sphinx]

guys, i have configured this os.environ["KERAS_BACKEND"] = "plaidml.keras.backend"

but python is still working with *cuda*

i dont have nvidia gpu so it isnt working

before it said "using plaidml backend". But i changed something a weeks ago, and i think i undo the changes, and it isnt working

ah nvm, i got it

[grim pier]

            await cursor.execute(f"SELECT messageid,victimname FROM bounty WHERE victimname ='{victim_name}'")
            data = await cursor.fetchall()
            if data:
                message = [row[0] for row in data]  # Gets the message ID
                name = [row[1] for row in data]  # Gets player name from SQL Query
                print(message,name)```

anyone know why its printing like this?

[790395046021169172] ['G.O.A.T']

is there a way to remove the brackets without using str replace?

[digital wharf]

Your creating list's and wondering why they are lists?

Maybe you meant to just be like
message = data[0],
name = data[1]

Or whatever the requried indexing is

[grim pier]

@digital wharf I tried that, but it said tuple is out of index ill try it again 🙂

[digital wharf]

print(data)

[grim pier]

IndexError: tuple index out of range when i use data[0] and data[1]

printing data gives ((790395046021169172, 'G.O.A.T'),)

[digital wharf]

data[0][0], data[0][1]

[grim pier]

@digital wharf awesome got it. seemed to work, weird i usually just use data[0]

and data[1]

[digital wharf]

Well its a tuple within a tuple

[grim pier]

@digital wharf awesome thanks, so to loop through incase there is more then 1 on the DB i can do

            if data:
                for row in data:
                    if victim_name == data[row][1]:
                        await cursor.execute(f"DELETE FROM bounty WHERE victimname ='{victim_name}'")
                        print(f"Bounty has been cleared from: {victim_name} at : {time}. Updating bounty board.")
                        await sql.commit()
                        return 1```

[digital wharf]

Something like that

Although

That wont work, since looping over the tuple will return the tuple not an int index

so if vic_name == row[1]

[grim pier]

awesome thanks 🙂

[brave bridge]

Also, never format queries like that. It will easily lead to invalid formatting and introduce SQL injection vulnerabilities. All database adapters in Python ship with some safe substitution mechanism, e.g.:

 await cursor.execute(f"DELETE FROM bounty WHERE victimname =?", (victim_name,))

[radiant elbow]

There shouldn't be any quotes around the ?, right?

[velvet gazelle]

I think you need them because you're passing a language to a language

SQL is going to expect quotes or a defined variable

(or a subquery)

[radiant elbow]

I'm quite certain it shouldn't be there.

!e ```py
import sqlite3
conn = sqlite3.connect(":memory:")
cursor = conn.cursor()
cursor.execute("create table bounty (victimname)")
cursor.execute("insert into bounty(victimname) values (?)", ("Some Name",))
print(cursor.execute("select * from bounty").fetchall())
cursor.execute("delete from bounty where victimname=?", ("Some Name",))
print(cursor.execute("select * from bounty").fetchall())

[delicate field]

@radiant elbow :white_check_mark: Your eval job has completed with return code 0.

001 | [('Some Name',)]
002 | []

[grim pier]

@radiant elbow You mean my whole code is wrong with those quotes? xD

[radiant elbow]

well, yes. The way you've written it, it's got a very serious bug in it: it's vulnerable to SQL injection. If the victim_name that you get contains a ' character, this can wind up terminating your statement and then running an entirely different one. For instance, if someone gave you a victim_name like ';drop table bounty; select ' then your f-string would expand to:

DELETE FROM bounty WHERE victimname ='';drop table bounty; select ''

[grim pier]

Is it possible to search for something on MySQL DB by using part of the name. for example if you type part of the name?

@radiant elbow good point never thought of that. ill change it up

[radiant elbow]

which is why fix error is trying to steer you in the direction of using parameter binding instead - it fixes that problem, making it both faster and safer.

[radiant elbow]

you're looking for the LIKE operator. WHERE victimname LIKE '%something%' will find any records where the victimname contains something in the middle of it - the % is a wildcard, so that it can have any characters before or after the something

[grim pier]

ohhh okay cool. ill give it a try 🙂

Thanks coldygeek appreciate it

[grim pier]

@radiant elbow i have this

        await cursor.execute("SELECT PlayerName, PlayerID from players WHERE %s LIKE concat('%%', PlayerName, '%%')", ([name]))
``` doesnt seem to find anyone who has a similar name though

[sonic whale]

Hi. I'm creating a calendar database, and I'm getting an deadline, and I'm trying to automatically delete data that's past this deadline. What should I do? In mysql

Should the program call up the data from the database at 12 o'clock every day?

[nocturne yew]

i am inserting data into postgres table using psycopg2, i got schema error, my table name is movies, my schema is public, isnt it?

[frigid glen]

@nocturne yew did you try inserting into just 'movies'?

[nocturne yew]

yes @frigid glen i created postgres table. i corrected that error, it was wrong database name lol. i am struggling because i am populating that columns with json objects, so i need to use apply.set or something

[scenic bough]

What's the best way on storing international phone numbers in a database. Supporting as many as possible? I can't seem to find a clear answer to this

[scenic bough]

Random question what happens if PlayerName is "DROP TABLE players;" 🤔

Sounds like trouble

[brave bridge]

no, my bad!

[proven arrow]

Do you mean the data type of the column or the actual format of the number?

[scenic bough]

Format

• datatype wouldn't hurt

[proven arrow]

Well don't store it as actual numbers Use varchar/text type. And any formatting on how a number is displayed is not the responsibility of the database but rather your applications frontend/presentation layer should handle this.

As for format there is E.164 format

Twilio had good example of this, and it's what they recommend too. Let me see if I can find their link.

https://www.twilio.com/docs/glossary/what-e164
https://support.twilio.com/hc/en-us/articles/223183008-Formatting-International-Phone-Numbers

[scenic bough]

Thank youi I shall look into it once I am back 👍

[south fjord]

Hello,

how can I get user signup auth refreshtoken and local id from firebase

[bold plaza]

how i can recup this link on mongodb

[floral cedar]

I have a question about no such table error or even better known as (main.auth_user_old)...
How should I fix it?

[pale jay]

Send the screen shot here @floral cedar

[bold plaza]

pls

[floral cedar]

It's loading

[floral cedar]

Got it?

[pale jay]

uh is this django?

[floral cedar]

Yes

[pale jay]

Try removing the main and just do auth_user_old

[floral cedar]

I can't even find a table named auth_user old

[pale jay]

uhh idk

[floral cedar]

Do you know where can I find the answer?

[pale jay]

idk if it is the problem with django or not but you can check #web-development

[floral cedar]

Thanks

[wet sierra]

@client.command()
async def balance(ctx):
    async with aiosqlite.connect("Economy") as db:
        c = await db.execute("SELECT bankaccount FROM economy1 WHERE user = (?)", (ctx.message.author,))
        thedata = await c.fetchone()
        await db.commit()

    if thedata == None:
        async with aiosqlite.connect("Economy") as db:
            await db.execute("INSERT INTO economy1 (user, bankaccount) VALUES ((?), (?))", (ctx.message.author, 30000,))
            await db.commit()
        await ctx.send("Oops! You dont have an account! Oh well, thats fine. I just make you an account and gave you 30,000 credits! Use `.balance` to check your account!")
    else:
        Mbed =discord.Embed(
            title = f"__{ctx.message.author.name}'s Balance__",
            description = f"Bank Account : {thedata}",
            color = 0xdeaa0c)
        await ctx.send(embed = Mbed)

im using aiosqlite, why isnt it working?

[torn sphinx]

Is this how you add a row?

[indigo flare]

How would I get the default value of a column with an SQL query?

[crude basin]

Goood afternoon, has anybody encountered a situation where MongoDB (using Mongoexpress) shows fields in the document list, but when you open the document those fields are missing?

[crude basin]

Here is the document from the list with the missing data.

[crude basin]

Ooops disregard i discovered the error. When my code was doing a field update, it seems to save a NEW object like:
{ "_id": { "$oid": "5f973aeb1c00661a0b594b92" }, "integration_status": "Inservice", "nest_status": "S2 Maintenance" }
and didnt change the original document with the correct info.
{ "_id": "5f973aeb1c00661a0b594b92",

Still working on figuring out what im going to do about it but at least ive identified the problem. Leaving the info up in-case someone ever needs it.

[torn sphinx]

https://cdn.discordapp.com/attachments/587375753306570782/790703084145737728/unknown.png

i keep getting this error

Traceback (most recent call last):
File "D:\jayDEV\Customer Work\DanishPubG\test.py", line 6, in <module>
JsonDict = json.load(OpenFile)
File "C:\Users\Jibriel\AppData\Local\Programs\Python\Python39\lib\json__init.py", line 293, in load
return loads(fp.read(),
File "C:\Users\Jibriel\AppData\Local\Programs\Python\Python39\lib\json__init.py", line 346, in loads
return _default_decoder.decode(s)
File "C:\Users\Jibriel\AppData\Local\Programs\Python\Python39\lib\json\decoder.py", line 337, in decode
obj, end = self.raw_decode(s, idx=_w(s, 0).end())
File "C:\Users\Jibriel\AppData\Local\Programs\Python\Python39\lib\json\decoder.py", line 355, in raw_decode
raise JSONDecodeError("Expecting value", s, err.value) from None
json.decoder.JSONDecodeError: Expecting value: line 1 column 1 (char 0)

[crude basin]

id check and make sure your dict is actually populated it seems right at the moment JsonDict is None

[torn sphinx]

I'm getting "cursor is not connected" with mysql after every so often, not sure if it's queries or time dependent and I cant figure whats going wrong

discord.ext.commands.errors.CommandInvokeError: Command raised an exception: ProgrammingError: Cursor is not connected

mysql.connector.errors.ProgrammingError: Cursor is not connected

[burnt turret]

unsure why that is happening, but i'd recommend using an async library to connect to your mysql database for a discord bot - check out aiomysql

mysql.connnector is blocking

[proven arrow]

Error is because you will have closed your cursor somewhere and then you are trying to use the same cursor again.

[velvet coyote]

What is a pool of connections

and what is re-usable connections?
And how do those work

[craggy jewel]

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

sqlite3.OperationalError: no such table: users```What is my error here?

[frigid glen]

have you created the table users, yet?

[craggy jewel]

yes

this is the db file

import discord
from discord.ext import commands
import random
import os
import sqlite3

conn = sqlite3.connect('mainbank.db')

c = conn.cursor()

client = commands.Bot(command_prefix = "!")



@client.event
async def on_ready():
    print("ready")


@client.command(alieses = ["bal"])
async def bal(ctx):
    user = ctx.author
    await open_account(ctx.author)

    Wallet = c.execute(f"SELECT Wallet FROM employees WHERE userid = '{str(user.id)}'")
    Bank = c.execute(f"SELECT Bank FROM employees WHERE userid = '{str(user.id)}'")

    embed = discord.Embed(title = f"{ctx.author.name}'s balence", color = ctx.author.color)
    embed.add_field(name = "Wallet", value = f"{Wallet}")
    embed.add_field(name = "Bank", value = f"{Bank}")
    await ctx.send(embed = embed)



async def open_account(user):
    users = c.fetchall()
    if str(user.id) in users:
        return False
    else:
        c.execute(f"INSERT INTO users VALUES('{str(user.id)}','0','0')")

    with open ("mainbank.json","w") as f:
        users = json.dump(users,f)
        return True



@client.command()
async def beg(ctx):

    user = ctx.author

    await open_account(ctx.author)

    users = await get_bank_data()

    earnings = random.randrange(1001)

    await profit(ctx.author, earnings)

    await ctx.send(f"Yooo You just got {earnings}$")


async def profit(user, earnings):

    users = await get_bank_data()
    users[str(user.id)]["wallet"] += earnings

    with open("mainbank.json","w") as f:
        json.dump(user,f)
```This is the code

dont mind the syntax i am using it for dpy

[frigid glen]

where is the create_table syntax?

[craggy jewel]

here

c.execute("""CREATE TABLE employees(
            userid real,
            Bank integer,
            Wallet integer
            )""")```

[frigid glen]

@craggy jewel - employees != users

[craggy jewel]

oh ok

[proven arrow]

Its a way to keep database connections open so that they can be reused, instead of creating new ones each time. Basically you have a cache/collection of database connections.

And so when something needs a database connection it takes one from the pool, and when its done with it, it can return it back to the pool.
This way you avoid the overhead with connect/disconnect. And this is the main reason for using a pool, because you want to minimise the cost of opening/closing connections.

[velvet coyote]

i see

how do u create a pool with asyncpg

[proven arrow]

Its shown here https://magicstack.github.io/asyncpg/current/usage.html#connection-pools

And the documentation for it: https://magicstack.github.io/asyncpg/current/api/index.html#asyncpg-api-pool

[torn sphinx]

            sql = ('INSERT INTO islands(channel_id, cp1, cp2, cp3 coopid, bank, storage, guild) VALUES(?,?,?,?,?,?,?,?,?)')
            val = (chann.id, ctx.author.id, "None", "None", coop_id, 0, "None", "blue")
            await db.execute(sql, val)```
this causes error

Command raised an exception: OperationalError: near "coopid": syntax error```
and the error isnt informative at all... ;-;

[proven arrow]

you have a space between cp3 coopid

and you are asking for 9 placeholders with ?, when you just pass 8 with 8 columns

[fading breach]

Hi, I have these two events, but they don't add the server ID to the json, how can I fix this

@bot.event
async def on_guild_join(guild):
  with open('.//cogs//prefixes.json', 'r') as f:
    prefixes = json.load(f)
    prefixes[str(guild.id)] = '--'

  with open('prefixes.json', 'w') as f:
    json.dump(prefixes, f, indent=4)

@bot.event
async def on_guild_remove(guild):
  with open('.//cogs//prefixes.json', 'r') as f:
    prefixes = json.load(f)

    prefixes.pop(str(guild.id))

  with open('.//cogs//prefixes.json', 'w') as f:
    json.dump(prefixes, f, indent=4)

also, dont send this, people can come in and remove all your code

and mess up things

[surreal oyster]

oh thanks for informing, i have the code copied

[true tinsel]

hello there, I don't know much about databases, but I got a task in school to display JSON data using python in a clean way on the screen. With pandas I got picrelated, although tidy, I would like something standalone, like a separate window with data, what can I use to achieve that?

[true tinsel]

I used plotly, thanks anyways! Merry Christmas and Happy Holidays!

[elder ferry]

Good evening. Is there anyone that has experience with two phase locking (2PL) available to help?

[teal plinth]

So this

for customer in customers:
    name = f"""CREATE TABLE IF NOT EXISTS
    {customer.id}(article_count INTEGER, store_id INTEGER)"""

raises sqlite3.OperationalError: near "<customer_id>": syntax error.
<customer_id> is just a placeholder for the id
Does someone have an idea?

[proven arrow]

What is the actual value?

[teal plinth]

234395307759108106

[proven arrow]

Well first why are you creating tables dynamically like that. Is that per user? That's really inefficient, unless you have a real good reason.

And its because name can't start with number

Well it can but you need to escape it

[teal plinth]

Well, I thought it might be a good idea to add a table for every user, when every user has multiple values (or rows - whatever they're called)

[proven arrow]

Thats a very bad way to design it. If you want me to explain why i can, but in this case you should have a single table with each row representing a users details.

Each different value you want to store can be stored in columns of that table.

[teal plinth]

Makes sense.
By escaping you mean putting a \ in front of the {...}, or am I understanding something wrong?

[proven arrow]

Well you dont need to escape it if your table name is not the user id, or are you still sticking to your original idea with each table per user?

[teal plinth]

No, I'm not, but lets say I would like to create a table for every store and save the store by it's id. And to this table I'll add rows for each user

[proven arrow]

Its the same for the store, it should not have its own table. Anyways you can decide for this later.
What database are you using?

[teal plinth]

sqlite

[proven arrow]

Ok so you can put the name in single quotes, double quotes, backticks, or in [ ]

[teal plinth]

Now I get what you mean. The tables name f. ex. would be "stores" and to that you'll add the store_id as row and so on - Had a short brain lag
Thanks sir

[proven arrow]

Yeah thats what i meant and its ok. In fact one of my coworkers recently wanted to do something like this, and asked me so this was part of the response i sent him, in case you were wondering some of the reasons why.

[teal plinth]

Very much appreciated

[umbral lark]

So, I'm trying to make a Discord bot, that saves the messages of a user based on the guild they wrote the message on. But I'm stuck at how I would approach that.
I create a table called "guilds" in which I want to add every server the bot is in. But I have to define the fields when creating the table. How am I suppose to add a new guild after the bot got added to another server?

[proven arrow]

To add data to the database you can use the INSERT statement to add data to a table.

[half lintel]

Hey does anybody have a guide/best practices for connecting to databases? I don't need help, I'm just trying to see if the method i'm using is ideal or I could be doing something better.

[torn sphinx]

Do you use an ORM ?

[half lintel]

Yeah I use Alchemy, pyodbc and psycopg2

My script imports from CSV into one database, then updates 2 MSSQL databases and 1 Postgres database. Right now I just initiate the connections via a Class and then use that class throughout the script when i'm moving to a specific database or calling stored procedures on that database.

[thorn nymph]

guys can someone help me

i forgot my name fro postgresql xd

how can i get it again??

[glad sequoia]

Merry Christmas Everyone. Keep Safe.
Also thanks for everyone helping me too when I need help with coding.

Merry Christmas x 🎄🤶

Keep safe x let's all hope 2021 is going to be better XD 😂😂

[teal plinth]

So, I now have a table called users. The table has a primary key, which is the users id. And another key that is a counter, which increments by 1 every time a listener triggers.
I tried this:

get_current_count = f"SELECT count FROM users WHERE user_id = {user.id}"
current_count = cursor.execute(get_current_count).fetchall()[0][0]
increment_count = f"INSERT INTO users (count) VALUES ({current_count + 1})"
cursor.execute(increment_count)
connection.commit()

But that just adds a new column, with the users id + 1 and the count + 1.
f. ex.

before:
user_id | count
1484932 | 0

after:
user_id | count
1484933 | 1
1484932 | 0

which makes sense, because I didn't define, which users count it should increment. But afaik you cant pass WHERE user_id = user.id into an INSERT INTO command.
How do I solve that?

[radiant elbow]

You want an UPDATE statement to update the data for a row, not an INSERT.

INSERT makes new rows, UPDATE modifies existing rows.

Also, you shouldn't be using f-strings to generate SQL queries. That leaves you with a very common bug called a SQL injection vulnerability. Instead, you should be using parameterized queries.

[craggy jewel]

do you have to documentation of sqlite3

[radiant elbow]

!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.

To use the module, you must first create a Connection object that represents the database. Here the data will be stored in the example.db file:... read more

[craggy jewel]

ty

In sqlite 3

can you use f strings?

like

conn.execute(f"something WHERE name = '{str(input)}'")```

will that work?

[radiant elbow]

You really, really shouldn't.

[craggy jewel]

ok

[radiant elbow]

Check out https://realpython.com/prevent-python-sql-injection/

[craggy jewel]

ok

[craggy jewel]

@client.command()
async def gamble(ctx,amount:int):
    await open_account(ctx.author)
    user = ctx.author
    c.execute("SELECT Wallet FROM users WHERE userid = ?", (user.id,))
    Wallet = c.fetchone()
    if Wallet[0] <= amount:
        value = random.choice([True, False])
        await asyncio.sleep(2)
        if value == True:
            await profit(ctx.author,amount)
            await ctx.send(f"Wow! You won the gamble of {amount}$")
        elif value == False:
            await ctx.send(f"Lol, You suck you lost the Gamble of {amount}$")
            amount = amount * -1
            await profit(ctx.author,amount)
    else:
        await ctx.send("You Don't Have that much Money.")``` This is my code

Traceback (most recent call last):
  File "C:\Users\arun\AppData\Local\Programs\Python\Python38\lib\site-packages\discord\ext\commands\core.py", line 85, in wrapped
    ret = await coro(*args, **kwargs)
  File "C:\Users\arun\OneDrive\Documents\DOCUMENTS\DISCORD\DISCORD\econamy\economy.py", line 82, in gamble
    await profit(ctx.author,amount)
  File "C:\Users\arun\OneDrive\Documents\DOCUMENTS\DISCORD\DISCORD\econamy\economy.py", line 68, in profit
    c.execute('UPDATE users SET Wallet = Wallet + ? WHERE userid = ?', (earnings, user.id))
OverflowError: Python int too large to convert to SQLite INTEGER

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

Traceback (most recent call last):
  File "C:\Users\arun\AppData\Local\Programs\Python\Python38\lib\site-packages\discord\ext\commands\bot.py", line 903, in invoke
    await ctx.command.invoke(ctx)
  File "C:\Users\arun\AppData\Local\Programs\Python\Python38\lib\site-packages\discord\ext\commands\core.py", line 859, in invoke
    await injected(*ctx.args, **ctx.kwargs)
  File "C:\Users\arun\AppData\Local\Programs\Python\Python38\lib\site-packages\discord\ext\commands\core.py", line 94, in wrapped
    raise CommandInvokeError(exc) from exc
discord.ext.commands.errors.CommandInvokeError: Command raised an exception: OverflowError: Python int too large to convert to SQLite INTEGER```

this is my error

what should i do?

[proven arrow]

@craggy jewel Your error is in a different function of the code than the one you sent. And error is because you are trying to store a number larger than the allowed max value. Sqlite can only store 64 bit signed integers.

You can convert it to a string, and store it as text in the database.

[craggy jewel]

ok

actually

i fixed my error

but ty

[leaden ruin]

How can I write this dataframe in csv file

[woeful quarry]

U can use pandas?

[leaden ruin]

using pandas already

[woeful quarry]

whats worng with it

[leaden ruin]

should i have to use csv package?

[woeful quarry]

use anything u know

[leaden ruin]

how to do it using pandas ?

[woeful quarry]

U want to convert rows[['Nikhil,'CEO'....]....] into CSV through pandas?

[leaden ruin]

yes

[torn sphinx]

I’m using GitHub and heroku, and motor async for mongodb, and it seems to not work

[woeful quarry]

https://www.geeksforgeeks.org/convert-a-numpy-array-into-a-csv-file/
I think this link help u with Pandas

[leaden ruin]

thanks @woeful quarry

[woeful quarry]

is i help?

it

[leaden ruin]

i checked https://www.geeksforgeeks.org/saving-a-pandas-dataframe-as-a-csv/
it helped, that was for numpy arrays

[surreal oyster]

hello, what is the syntax if i need to access the second value in a dictionary

[frigid glen]

@surreal oyster what do you mean by second value?

[surreal oyster]

The second value given by user input

[proven arrow]

@surreal oyster Dicts are a key-value pair so you can access the value using the key

But if you want to subscript it and access by index then you can use list(dict.values())[index]

[crisp tundra]

@commands.command(case_insensitive=True)
async def kingdoms(self, ctx, *, empire:str):
    """Gives info about an empire"""
     async with self.bot.db.acquire() as conn:
            result = await conn.fetchval('SELECT Kingdoms FROM CK3_empires WHERE Empires = $1', empire.title())
            await ctx.send(result)```

What's wrong with my sql query?

[rancid badger]

hey anyone how can i execute an postgres sql file with python psycopg2

guys anyone

plsss

[tacit plank]

does anyone have an idea on how to make python run faster when reading and writing large number of json files? In my case, about 18000?

[regal moss]

https://www.w3schools.com/python/python_mysql_select.asp @rancid badger postgres and mysql whatever, is the same

[rancid badger]

hey

@regal moss

can u help me with postgres

plss

def CreateTables(cursor):
    con = connect()[0]
    cursor.execute("""
        CREATE TABLE if not exists customers(
            customerid INT NOT NULL AUTO_INCREMENT,
            customername VARCHAR(225),
            phoneno VARCHAR(30),
            email VARCHAR(225),
            state VARCHAR(2),
            PRIMARY KEY (customer_id));""")
    con.commit()
    con.close()```

[regal moss]

and where is error

[rancid badger]

error py Traceback (most recent call last): File "E:/Varsh_Code/Python/CONNOR_PROJECT/SHIPMENT TRACKER/setup_db.py", line 62, in <module> Setup() File "E:/Varsh_Code/Python/CONNOR_PROJECT/SHIPMENT TRACKER/setup_db.py", line 53, in Setup CreateTables(cur) File "E:/Varsh_Code/Python/CONNOR_PROJECT/SHIPMENT TRACKER/setup_db.py", line 46, in CreateTables PRIMARY KEY (customer_id));""") psycopg2.errors.SyntaxError: syntax error at or near "AUTO_INCREMENT" LINE 3: customerid INT NOT NULL AUTO_INCREMENT, ^

[rancid badger]

here!

[regal moss]

smth wrong with syntax only

[rancid badger]

can u just sort it out

whats wrong in there?

[regal moss]

remove "if not exists"

[rancid badger]

ok

but!! ok

[rancid badger]

still same error!

[regal moss]

ok so fields should be in ' '

'customerid' etc

[rancid badger]

name of the things right

inside quotes or backtics

[regal moss]

single quotes

[rancid badger]

ok lemme try

ok

[regal moss]

works?

[rancid badger]

one sec!

Traceback (most recent call last):
  File "E:/Varsh_Code/Python/CONNOR_PROJECT/SHIPMENT TRACKER/setup_db.py", line 62, in <module>
    Setup()
  File "E:/Varsh_Code/Python/CONNOR_PROJECT/SHIPMENT TRACKER/setup_db.py", line 53, in Setup
    CreateTables(cur)
  File "E:/Varsh_Code/Python/CONNOR_PROJECT/SHIPMENT TRACKER/setup_db.py", line 46, in CreateTables
    PRIMARY KEY (customerid));""")
psycopg2.errors.SyntaxError: syntax error at or near "'customerid'"
LINE 3:             'customerid' INT AUTO_INCREMENT,
                    ^


Process finished with exit code 1

[regal moss]

ok remove quotes from fields now, and add quotes only to 'customers'

[rancid badger]

ok!

ok instead?

[regal moss]

not null primary key auto_incr works fine

oh

[rancid badger]

auto_incr

ok lemm etry

[regal moss]

remove priamry key: it shoudl be not null AUTO INCRMENT

primary key will be oin bottom

[rancid badger]

ITS IN THE BOTTOM

def CreateTables(cursor):
    con = connect()[0]
    cursor.execute("""
        CREATE TABLE IF NOT EXISTS 'customers'(
            customerid INT NOT NULL AUTO_INCR,
            customername VARCHAR(225),
            phoneno VARCHAR(30),
            email VARCHAR(225),
            state VARCHAR(2),
            PRIMARY KEY (customerid));""")
    con.commit()
    con.close()```

[regal moss]

("""
        CREATE TABLE if not exists 'customers'(
            customerid INT NOT NULL AUTO_INCREMENT,
            customername VARCHAR(225),
            phoneno VARCHAR(30),
            email VARCHAR(225),
            state VARCHAR(2),
            PRIMARY KEY (customer_id));""")

[rancid badger]

  File "E:/Varsh_Code/Python/CONNOR_PROJECT/SHIPMENT TRACKER/setup_db.py", line 62, in <module>
    Setup()
  File "E:/Varsh_Code/Python/CONNOR_PROJECT/SHIPMENT TRACKER/setup_db.py", line 53, in Setup
    CreateTables(cur)
  File "E:/Varsh_Code/Python/CONNOR_PROJECT/SHIPMENT TRACKER/setup_db.py", line 46, in CreateTables
    PRIMARY KEY (customerid));""")
psycopg2.errors.SyntaxError: syntax error at or near "'customers'"
LINE 2:         CREATE TABLE IF NOT EXISTS 'customers'(
                                           ^


Process finished with exit code 1

gives me this error!

[regal moss]

no, AUTO_INCERMENT i wrote shortuct

[rancid badger]

lemme try!

[rancid badger]

  File "E:/Varsh_Code/Python/CONNOR_PROJECT/SHIPMENT TRACKER/setup_db.py", line 62, in <module>
    Setup()
  File "E:/Varsh_Code/Python/CONNOR_PROJECT/SHIPMENT TRACKER/setup_db.py", line 53, in Setup
    CreateTables(cur)
  File "E:/Varsh_Code/Python/CONNOR_PROJECT/SHIPMENT TRACKER/setup_db.py", line 46, in CreateTables
    PRIMARY KEY (customer_id));""")
psycopg2.errors.SyntaxError: syntax error at or near "'customers'"
LINE 2:         CREATE TABLE if not exists 'customers'(
                                           ^


Process finished with exit code 1

[regal moss]

k remove quotes from customers again, I had similar issue long time ago and it was smth about quotes, the similar error

[rancid badger]

ok lemme try

Traceback (most recent call last):
  File "E:/Varsh_Code/Python/CONNOR_PROJECT/SHIPMENT TRACKER/setup_db.py", line 62, in <module>
    Setup()
  File "E:/Varsh_Code/Python/CONNOR_PROJECT/SHIPMENT TRACKER/setup_db.py", line 53, in Setup
    CreateTables(cur)
  File "E:/Varsh_Code/Python/CONNOR_PROJECT/SHIPMENT TRACKER/setup_db.py", line 46, in CreateTables
    PRIMARY KEY (customer_id));""")
psycopg2.errors.SyntaxError: syntax error at or near "AUTO_INCREMENT"
LINE 3:             customerid INT NOT NULL AUTO_INCREMENT,
                                            ^


Process finished with exit code 1

after removing quotes!

ughh im behind this thing for more than 3 hours!

[regal moss]

("""
CREATE TABLE if not exists customers(
            customerid INT NOT NULL AUTO_INCREMENT,
            customername VARCHAR(225),
            phoneno VARCHAR(30),
            email VARCHAR(225),
            state VARCHAR(2),
            PRIMARY KEY (customer_id));
""")

run and show error again

[rancid badger]

ok

[rancid badger]

same errorr!!

ok!!

[proven arrow]

You need to use the serial type or generated as identity column for auto incrment

[rancid badger]

love u sooooo much thanks thanks thanks thanks a thousand times!! uff!! thank u

can u just explain that serial and begserial thing

[proven arrow]

Although serial still works generated as identity is the newer way of doing it, and it's more SQL compliant.

[rancid badger]

ohh tnxx!!

wb bigserial

but one problem the table is not created!

Ok

Sure!

[rancid badger]

@torn sphinx

dude

ya

def CreateTables(cursor):
    con = connect()[0]
    cursor.execute("""
DROP TABLE IF EXISTS customers;
CREATE TABLE customers(
            customerid BIGSERIAL PRIMARY KEY,
            customername VARCHAR(225),
            phoneno VARCHAR(30),
            email VARCHAR(225),
            state VARCHAR(2)
            );
""")```

ran this!

no errors

but table is not created!

ohh right!

😅

beginner things!!
sry

bro still no!

no its the first one!

there is not that!

def CreateTables(cursor):
    con = connect()[0]
    cursor.execute("""
DROP TABLE IF EXISTS customers;
CREATE TABLE customers(
            customerid BIGSERIAL PRIMARY KEY,
            customername VARCHAR(225),
            phoneno VARCHAR(30),
            email VARCHAR(225),
            state VARCHAR(2)
            );
""")
    con.commit()
    con.close()```

ohh sorry gud ni8

ok i will

ohh ok!

@torn sphinx

the table is there

[torn sphinx]

dude ,what sql are you useing ?

[rancid badger]

but u know where can i see the table pgadmin4

in pgadmin4

ok

[torn sphinx]

cant you just simply delete the database ?

[rancid badger]

yes bro

thank u!

@torn sphinx I HAVE A 13 LONG DIGIT WITHOUT DECIMALS I WANT TO MAKE IT THE PRIMARY KEY HOW CAN I DECLARE THAT INSIDE A TABLE?

BIGINT RIGHT

SO BIGSERIAL FOR STRINGS?

BARCODE NO!

LIKE 13 DIGITS

YEAH!

NO ISSUES

I WAS JUST ASKING HOW TO DECLARE IT!

AND

THANKS FOR UR HELP!

sry

caps was on

forgot to switch after typing sequel @torn sphinx

@torn sphinx sry to disturb u again !

def CreateTables(cursor):
    con = connect()[0]
    cursor.executemany("""
DROP TABLE IF EXISTS customers;
CREATE TABLE customers(
            customer_id BIGSERIAL PRIMARY KEY,
            customer_name VARCHAR(225),
            phone_no VARCHAR(30),
            email VARCHAR(225),
            state VARCHAR(2)
            );
DROP TABLE IF EXISTS 
CREATE TABLE orders(
            order_id INT,
            customer_id VARCHAR(225),
            unique_id BIGINT PRIMARY KEY
            );
""")
    con.commit()
    con.close()```

ok!

tnxx

ohh i mean i want it on my other table too

ohh yah!!

sorry bro calling u unnecessarily!

def CreateTables(cursor):
    con = connect()[0]
    cursor.executemany("""
CREATE TABLE customers(
            customer_id BIGSERIAL PRIMARY KEY,
            customer_name VARCHAR(225),
            phone_no VARCHAR(30),
            email VARCHAR(225),
            state VARCHAR(2)
            );
CREATE TABLE orders(
            order_id INT,
            customer_id BIGSERIAL PRIMARY KEY,
            unique_id BIGINT PRIMARY KEY
            );
""")```

error
```Traceback (most recent call last):
  File "E:/Varsh_Code/Python/CONNOR_PROJECT/SHIPMENT TRACKER/setup_db.py", line 74, in <module>
    Setup()
  File "E:/Varsh_Code/Python/CONNOR_PROJECT/SHIPMENT TRACKER/setup_db.py", line 65, in Setup
    CreateTables(cur)
  File "E:/Varsh_Code/Python/CONNOR_PROJECT/SHIPMENT TRACKER/setup_db.py", line 54, in CreateTables
    """)
TypeError: function missing required argument 'vars_list' (pos 2)

Process finished with exit code 1

@torn sphinx

uhmm! ok

where, i havent?

its id and name

can u just paste that part in here!

u mean 2 primary key?

i removed it!

ok?

yeah fixed it!

..

can u just tell in which table?

first table or second one

coz both has each!

yah! dude dont get frustrated im really sorry!! i dont have another help im sorry im a beginner!

yah sry!

i will call anyone else

im sorry!

its in 2 diff tables right?

is that a problem!

im just asking

@torn sphinx

[proven arrow]

@rancid badger It's certainly possible to have multiple primary keys per table/across different tables, but what I think Rei is saying is that your orders table should not have the customer_id as its primary key. Instead what you want to do is make a relationship between the two tables, and to do this make a customer_id column in the orders table which acts as a foreign key.

[upbeat slate]

Should i ask about json data processing here or in #data-science-and-ml

?

how can i assign a variable's value to an object in a dict, then read it
noting i have 2 objects only
channel1 and channel2

ping me pls i m away

[rancid badger]

Do u know how to do that

Can u just!! Show me how

[proven arrow]

What exactly is it you want? The relationship?

[rancid badger]

Yeah how to make that relationship @proven arrow

[proven arrow]

Customers table,

CREATE TABLE customers(
    customer_id BIGSERIAL PRIMARY KEY,
    customer_name VARCHAR(225),
    phone_no VARCHAR(30),
    email VARCHAR(225),
    state VARCHAR(2)
);

Orders table,

CREATE TABLE orders(
    order_id BIGSERIAL primary key,
    customer_id BIGSERIAL,
    -- other columns
    constraint fk_orders_customers foreign key(customer_id) references customers(customer_id)
);

[upbeat slate]

pliz

[proven arrow]

Well since you read the dict you can assign the object to it at that point, when you retrieve it.

[upbeat slate]

thaanks

if i knew how to do that i would be glad

sorry im toxic but im tierd af

[proven arrow]

It ok 😜, butl you would have to read it and write it back to the dict. You may want to ask in a help channel or general channel as its more suited for there.

Anyways home time for me

[torn sphinx]

How would I get the first, say 5 most recent entries from a database? SQL, Python, either is fine

[upbeat slate]

yeah ill just ask another day

[rancid badger]

thanks instead if i had 2 tables table_a and table_b that fk think wud have been fk_tableb_table_a isint it?

[glass gorge]

how would i map

a multicheck box

to my db

[blazing wigeon]

@torn sphinx Hello! You can use limit and offset to fetch your first 5 records from the database

[onyx laurel]

Is it neceassary to learn about Apache Kafka rather than Spark or is both intertwined

[proven arrow]

I don't think there is any particular convention to follow, however I normally do it like so fk_ChildTable_ParentTable. But it's not always necessary to give a name. If you were to leave it out the database would automatically generate one for you.

[rancid badger]

ohh ok !!!

[proven arrow]

It depends what your storing. You can do it a single column as an array or you can normalise it.

[rancid badger]

and one more thing this pgadmin is shit!! im now sticking with shell psql

[proven arrow]

Yeah it's not the best experience our there with that ahah

[glass gorge]

I was told that by normalising it, id need to use a many to many relationship?

[proven arrow]

Yes

[glass gorge]

yikes

it sounds intense

because many to many requires a 3rd table

[proven arrow]

Yeah, well it's not as complicated as it seems

[glass gorge]

and id like to just somehow keep this to one table

xD

[proven arrow]

Well what are you storing?

[glass gorge]

survey data

string format

[proven arrow]

Does it need querying through the dB?

[glass gorge]

not sure i understand your q

there are reports generated

off of this info

so the data would be queried at some point

[proven arrow]

Yes but do you need to inspect those individual values of checkbox seperately from the database or is that all done application side?

[glass gorge]

there's nothing in place jsut yet

i was hoping to limit to just one response and have the response stored under one column

because it's categorical data

the rest of the questions are boolean

I was previously using a select field

[proven arrow]

Well I have no idea the importance of your data or how it will exactly be used. But you need to ask yourself, do you need to evaluate those checkbox choices from the customers? For ex, if you wanted to know all the users who have selected a particular option.

[glass gorge]

yes

the answer is yes

or wait hmm

one sec

lets just go with yes

[proven arrow]

Ok so then you can split the data up into multiple tables.

Anyways you may want to take a look online on some survey schemas. My assumptions on how a survery is done or looks like, compared to the one you have for your business needs may be different.

[glass gorge]

thank you

honestly should just be using googles api for this

instead of handrolling everything

[barren wolf]

using tortoise-orm right now
trying to create a setter for a field, but PyCharm complains about there being no attribute reference
any tips?

[spark saddle]

what database do you recommend

[torn sphinx]

Is there someone can check if I have done this exercise correctly? It's about relational algebra expressions

[tribal furnace]

can someone help explain something to me in sqlalchemy

on how to creating threaded comments on a flask application

i need to create a column that takes both a local table id and a foreign key

to return a value of either a id or null

[modest panther]

can anyone helpme

all modules are installed still

what should i do to run this

[runic badger]

either just import mysql or just mydb = connector.connect(...

@modest panther

[keen gorge]

Can I make as many requests to a database as I want when my Code is asynchronous?

[proven arrow]

Yeah why not. It doesn't make a difference to the db if it's async or not. 10 requests is the same as 100000 requests to a db.

[keen gorge]

and it wont crash?

[proven arrow]

Database wont crash no.

[solid minnow]

[2020-12-24 11:57:54] ERROR - Unknown error in submit_modular!
(<class 'MySQLdb._exceptions.ProgrammingError'>, ProgrammingError(1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'rank FROM scores STRAIGHT_JOIN users ON scores.userid = users.id STRAIGHT_JOIN u' at line 1"), <traceback object at 0x7f564c40f3c8>)
Traceback (most recent call last):
  File "handlers/submitModularHandler.pyx", line 537, in handlers.submitModularHandler.handler.asyncPost
    newScoreboard.setPersonalBestRank()
  File "objects/scoreboard.pyx", line 233, in objects.scoreboard.scoreboard.setPersonalBestRank
    result = glob.db.fetch(query, {"md5": self.beatmap.fileMD5, "userid": self.userID, "mode": self.gameMode, "mods": self.mods})
  File "common/db/dbConnector.pyx", line 217, in common.db.dbConnector.db.fetch
    cursor.execute(query, params)
  File "/usr/local/lib/python3.6/site-packages/MySQLdb/cursors.py", line 206, in execute
    res = self._query(query)
  File "/usr/local/lib/python3.6/site-packages/MySQLdb/cursors.py", line 312, in _query
    db.query(q)
  File "/usr/local/lib/python3.6/site-packages/MySQLdb/connections.py", line 224, in query
    _mysql.connection.query(self, query)
MySQLdb._exceptions.ProgrammingError: (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'rank FROM scores STRAIGHT_JOIN users ON scores.userid = users.id STRAIGHT_JOIN u' at line 1")

need help

i was modifed this, and change the straight to join only, but still got error

[crimson granite]

can i ask database related question here?

[velvet coyote]

Sure u can

[crimson granite]

which one would be the proper way in these examples?

transactions [id, type, staffid, date....]
staffs = joe, jack...
types = [sell, trade, buy]

or

staffs[joe, jack.....]

----
sells[id, staffid]
trades[id, staffid]
buys[id, staffid]
-----

[inner minnow]

I like mysql

[proven arrow]

The first one

[proven arrow]

Query looks fine. You may want to try the query directly against you database from a cli or something and see the error then.

[solid minnow]

seems like something wrong on the mysql

mysql> SELECT COUNT(*) as rank FROM scores;
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 'rank FROM scores' at line 1

mysql> SELECT COUNT(*) as ranks FROM scores;
+-------+
| ranks |
+-------+
|     0 |
+-------+
1 row in set (0.00 sec)

mysql>

just add s can count

whats wrong with that?

[crimson granite]

thanks

[burnt turret]

rank was probably a reserved SQL keyword then
edit - yes it is, https://dev.mysql.com/doc/refman/8.0/en/keywords.html#keywords-8-0-detailed-R

[proven arrow]

Right correct, mysql 8 has it as a keyword. They use it for window functions.

[solid minnow]

wow , i was using that in oct it's working properly, when is 8.0 launch?

[proven arrow]

I would thought they would ignore it just like mariadb when not used as a function.

[proven arrow]

long time ago

[solid minnow]

hmm i see

idk, maybe i'm using oldest version

[proven arrow]

In older versions (5.7) it'll work fine, as its not a keyword there.

[torn sphinx]

Hello, i must need to find an efficient way of doing searching products from my database. I currently do LIKE but someone said that is not fast. So what should i do? Can i make it indexed column? Will this fix the issue?

i use mysql

[proven arrow]

Indexing wont necessarily speed it up. Its because of how mysql indexes the words.

It starts from the left so search_term% will work with an index however %search_term% wont

But it also depends how many items you are searching through, and if your wondering what is used generally by applications, then it is Full Text Search. MySQL supports this, or you can use external services like algolia/elastic search/MeiliSearch and many others out there.

[torn sphinx]

ok sir, thanks for clarifying. I currently dont have many product, but in the future will. So i will look into what you said.

[torn sphinx]

Right now I have a SQLite3 Database I am using to store dates and events. The format of the Dates are mm/dd/yyyy. How would I sort this from most recent upcoming to more further upcoming? I want to be using LIMIT, but I'll add that in later. I also want it to not show any dates from before the current date. So far, October, November, and December (with 1s in the month) get put with January. How would I do this? Thanks in Advance.

[proven arrow]

Well ideally to sort it by dates you would want to use ORDER BY however in your current date format that would not be possible because your dates are in mm/dd/yyyy format. This prevents sqlite from ordering by dates because it expects date to be in the format of YYYY-MM-DD. Its explained here under point 2: https://www.sqlite.org/lang_datefunc.html

So you can either convert your data to match the required format, otherwise you can use the substring function to manually parse it into the correct format.

[torn sphinx]

I have it as mm/dd/yyyy hh:mm:ss right now, and It seems to work better, but I haven't had time to go in depth with multiple tests. I also tried YYYY-MM-DD, but I had the same problem

[proven arrow]

Assume your date column is called event_date then to parse it to the correct format you could do the following:

SELECT *, 
substr(event_date, 7,4) || '-' || substr(event_date, 1,2) || '-' || substr(event_date, 4,2) as new_date
FROM your_table ORDER BY new_date DESC;

[torn sphinx]

Thank you

[proven arrow]

That wont give you exactly what you wanted in your original question because i didnt fully understand what you meant but it shows how to convert to a date type, which will allow you to do things like ordering, or comparing against dates.

[torn sphinx]

A friend suggested using the Date module to get the mm/dd/yyyy format. Basically, I want to get all the events that happen on the current day and happen after the current date. I want to get them in probably ASC order, to find the closest events

[proven arrow]

Right i see, makes sense now

So you can do,

SELECT *, 
substr(event_date, 7,4) || '-' || substr(event_date, 1, 2)|| '-' || substr(event_date, 4, 2) AS new_date
FROM your_table
WHERE new_date >= date('now') ORDER BY new_date ASC;

Although above still works, i would recommend to change the way you store the dates, in your table as that would be much easier/better overall.

[torn sphinx]

1. What format should I make them into?
2. If I change to that format, how would I go from the new format you suggest ^^ to mm/dd/yyyy
3. I was going to use this column to add times for the events too, but I think I should make a separate column just for times

[proven arrow]

1. See my first message which contains a link and shows you the different storage formats.
2. If you want to convert to your desired format then you can use the strftime() function. Again this is shown in the link i sent.
3. Not sure what you mean 🤷‍♂️

[torn sphinx]

Ok, thank you

[modest panther]

bro what should i prefer to learn btweeen these two,mongoDB and Mysql?

[chrome saffron]

anyone here

i am given a project to do something with connectivity i cant use tkinter and others i have to use normal pythonn windows only
and i want to do something intresting any ideas

[tame nova]

@south bear so can i give you a video that teachets you aiosqllite?

[south bear]

ok

im back

@tame nova

[tame nova]

ok

https://youtu.be/pd-0G0MigUA

this video will teach about how to use sqllite 3

its very simple to use

[south bear]

👍

[tame nova]

and very robust

[south bear]

should i program it all on main.py?

@tame nova ^

[tame nova]

well you can

[south bear]

should i?

[tame nova]

but i think for storing data stuff and configuring it

it should be done on a different file

will be easier to handle

[south bear]

@tame nova can SQLite still be integrated with a system where the user can see their inventory?

[tame nova]

ofc

[south bear]

ok

[tame nova]

you can create an sql table for a specific user

[south bear]

is that in the tutorial series?

[tame nova]

and that user can access their stats and data

[tame nova]

well that teaches you how to use sqlite 3 for your applications

[south bear]

bc i've got this so far

import sqlite3

conn = sqlite3.connect('gamestorage.db')

c = conn.cursor()

c.execute("""CREATE TABLE inventory (
            first
    )""")```

[tame nova]

ah

[south bear]

it's on the dataStore.py file

the script has made a file called gamestorage.db

so uh

what now

@tame nova (sorry for all the pings) ^

if that's for applications...

[tame nova]

so did you make functions?

i mean lets
!invopen

[south bear]

i've just paused the tutorial and grabbed a soda

(now an empty glass)

[tame nova]

lol

[south bear]

soo

should i continue with the tutorial?

[tame nova]

yes

you will learn a lot of stuff

[distant surge]

whats JDBC version for python

[lusty coyote]

hey dudes im forced to use shelves as my database for flask do you guys know any ways or methods to query shelve data?

im currently using .startswith() and a bunch of for, ifelse

[loud raft]

I wanna try out a new db, any recommendations?
Which of these is quickest to set up: Cassandra, Raven or Cockroach?

[brazen charm]

never heard of raven or cockroach, cassandra is pretty good though for nosql stuff

[paper lynx]

Guys, can somebody help me with MongoDB?

What are clusters, databases and its collections?

What’s the difference between them?

And how should I name my cluster?

I know there is no difference in names

But should I name as my project? Or as my nickname?

[paper lynx]

Help :<

[lusty coyote]

brother its been 2 hours without any respond, why not go watch a youtube tutorial or read some documentation

[torn sphinx]

{"785147745982742541": {"balance": 2443, "cotton": 0, "stone": 0, "wood": 0, "plastic": 0, "copper": 0, "steel": 0, "glass": 0, "concrete": 0, "paper": 0, "alluminium": 0, "iron": 0, "brick": 0, "oil": 0, "bronze": 0, "silver": 0, "gold": 0, "ruby": 0, "emerald": 0, "diamond": 0, "invtaken": 0, "invavailable": 10, "iulevel": 2, "pulevel": 0}
how can i get the like
for user.id in json top_bal = somthing
like find the top balance of the ids in the json

[wet sierra]

code:

async with aiosqlite.connect("Economy") as db:
            c = await db.execute("SELECT * FROM items WHERE 'number' = 1")
            data = await c.fetchone()
            name1 = data[0]
            description1 = data[1]
            cost1 = data[2]
            await db.commit()

error:

Traceback (most recent call last):
  File "C:\Python\lib\site-packages\discord\ext\commands\core.py", line 85, in wrapped
    ret = await coro(*args, **kwargs)
  File "first-bot-test.py", line 358, in store
    name1 = data[0]
TypeError: 'NoneType' object is not subscriptable

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

Traceback (most recent call last):
  File "C:\Python\lib\site-packages\discord\ext\commands\bot.py", line 903, in invoke
    await ctx.command.invoke(ctx)
  File "C:\Python\lib\site-packages\discord\ext\commands\core.py", line 859, in invoke
    await injected(*ctx.args, **ctx.kwargs)
  File "C:\Python\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: TypeError: 'NoneType' object is not subscriptable

pls help
im using SQL

my table

[wet sierra]

please

someone

[wet sierra]

?

[proven arrow]

@wet sierra Your error is because the fetchone method returned nothing from the dB and your trying to index nothing.

Remove the single quotes around the column name from your query and try again.

[glass gorge]

guys how do i bulk add to flask sqlalchemy

[hoary vale]

Hi all

is anybody working with Apache Spark ?

[torn sphinx]

printing your query output is a good way to make sure you're getting the results you want

Does anybody have recommendations on the best way to design a weekly tracking of user data, as in, additional to the cumulative lifetime user data? I want weekly statistics to be viewable separately from overall, but overall to continue updating. The first thought that comes to mind would perhaps be multiple tables, with the overall and weekly tables being updated constantly, but weekly tables wiped each week?

the only problem I can see with this would be that it stores twice the data

[pure cypress]

Interesting question. I think it depends on how you need to use the data. For example, you could store the weekly snapshots permanently and then aggregate them to get the lifetime data for the user.

The aggregation would be done on demand with a query

I'm not sure what the performance of such query would be like versus being able to access the pre-calculated data directly.

Whether performance would even be a factor depends on your use-case - where and how often such data will be needed, how much data there is, etc.

If you store data twice, then there are concerns of integrity between the two tables, database size, and the potential performance impact of having to do an additional operation (since you'd have an additional table) when adding new data.

[torn sphinx]

Thanks for the response, Im really not sure. I was thinking having the data pre-calculated and saved in the same structure would be the easier option since the data entry queries could pretty much be the same, and then for weekly stats only need to query the weekly table(s)

Im unfamiliar with database snapshots however

[pure cypress]

I wasn't referring to anything technical with "snapshots"

I just meant the weekly rows that you store for a user could be aggregated

[wet sierra]

👍

[wet sierra]

Hm, so use fetchall()

?

[proven arrow]

No, fetchone is fine. The single quotes were being misinterpreted as a string literal, instead of a column name

[radiant elbow]

Unlike in Python, in SQL single and double quotes mean different things.

Double quotes are used for quoting the name of a column or table, single quotes are used for string literals.

[proven arrow]

Yep, and sqlite also allows backticks and square brackets alongside the double quotes.

[wet sierra]

hmm

ok

lemme put them in double quotes

actually i cant enclose it with double quotes

cuz the querie is enclosed in double quotes

[proven arrow]

Well as I said in my first message you don't need the quotes. Remove them around the column name.

[wet sierra]

ye ok

[proven arrow]

They are to be used around column names when it contains a reserved keyword or characters like whitespace.

[wet sierra]

ahhhh okkk

[paper lynx]

I need help :(

[torn sphinx]

I get this error from MongoDB.

Command raised an exception: ServerSelectionTimeoutError: connection closed,connection closed,connection closed, Timeout: 30s, Topology Description: <TopologyDescription id: 5fe72e6c9f366684e21e2454, topology_type: ReplicaSetNoPrimary, servers: [<ServerDescription ('numix-shard-00-00.dksdu.mongodb.net', 27017) server_type: Unknown, rtt: None, error=AutoReconnect('connection closed')>, <ServerDescription ('numix-shard-00-01.dksdu.mongodb.net', 27017) server_type: Unknown, rtt: None, error=AutoReconnect('connection closed')>, <ServerDescription ('numix-shard-00-02.dksdu.mongodb.net', 27017) server_type: Unknown, rtt: None, error=AutoReconnect('connection closed')>]>```
Someone please help!

[wintry stream]

It gives a timeout error, meaning the connection to the server was lost

does this error appear right as you start or does it appear after some time?

@torn sphinx

[torn sphinx]

after sometime.

[wintry stream]

so that means that after some time the connection to mongo is closed

it looses connection

[torn sphinx]

How do I fix it?

[wintry stream]

figure out why it looses connection

[wet sierra]

this is my error:

Traceback (most recent call last):
  File "C:\Python\lib\site-packages\discord\ext\commands\core.py", line 85, in wrapped
    ret = await coro(*args, **kwargs)
  File "first-bot-test.py", line 547, in steal
    lobbb = await c.fetchone()
  File "C:\Python\lib\site-packages\aiosqlite\cursor.py", line 54, in fetchone
    return await self._execute(self._cursor.fetchone)
  File "C:\Python\lib\site-packages\aiosqlite\cursor.py", line 31, in _execute
    return await self._conn._execute(fn, *args, **kwargs)
  File "C:\Python\lib\site-packages\aiosqlite\core.py", line 122, in _execute
    return await future
  File "C:\Python\lib\site-packages\aiosqlite\core.py", line 98, in run
    result = function()
sqlite3.ProgrammingError: Cannot operate on a closed database.

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

Traceback (most recent call last):
  File "C:\Python\lib\site-packages\discord\ext\commands\bot.py", line 903, in invoke
    await ctx.command.invoke(ctx)
  File "C:\Python\lib\site-packages\discord\ext\commands\core.py", line 859, in invoke
    await injected(*ctx.args, **ctx.kwargs)
  File "C:\Python\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: Cannot operate on a closed database.

this is my code:
https://mystb.in/CameronTasteApproaches.python

im using SQL btw

helpppp

i cant find whats not operating under a closed database

[glass gorge]

im hoping someone can help me

im having issues with flask migrate

getting a

class Phone_Survey(db.Model):
    __tablename__ = "phone_survey"
    __bind_key__ = "user_data"


    survey_id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    interviewer_id = db.Column(db.Integer, db.ForeignKey('users.id'), nullable=False)
    first_name = db.Column(db.String(20))
    last_name = db.Column(db.String(20))```

class User(db.Model, UserMixin):
    __tablename__ = 'users'
    __bind_key__ = 'user_data'

    id = db.Column(
        db.Integer, primary_key=True, autoincrement=True, unique=True)
    employee_id = db.Column(db.String(11), unique=True)
    first_name = db.Column(db.String(1000))
    last_name = db.Column(db.String(1000))```

i have no idea what the issue is

from what i googled it could be a variety of things, but i may have narrowed it down to a indexing issue

but idk

[cinder trellis]

can anyone tell me about an issue iam facing. Iam trying to connect to oracle database using cx_oracle. it requires a availability of instantclient and but after converting my .py file to exe while running the program i get a error as "unable to find instantclient "Access is denied"". although i put the instant client on the path.

[teal plinth]

I'm a bit confused rn, I tell my program to insert an id (primary key) into a table.
The id I enter is "634838075272331294" but the database registers "2147483647" and I have no clue why

[proven arrow]

Because your column data type will be an INT, which only allows a 32 bit signed integer, and that is the value you are seeing.

In this case a BIGINT should be able to store your value.

[teal plinth]

Ah, okay. Thanks

A few days ago you told me to not use f-string. Should I use %s instead or is there a more efficient way?

[proven arrow]

Yeah that is the way you should be doing it. Use %s,
So like, cursor.execute("SELECT * FROM table WHERE column = %s OR column_two = %s", (value1, value2))

[radiant elbow]

You shouldn't use any type of Python string formatting - not f-strings nor .format() nor % printf-style formatting. Instead, you should include placeholders in your SQL query, and provide the parameters to use in place of those placeholders as the second argument to execute

if the DB API you're using supports %s placeholders then it's OK to do py cursor.execute("SELECT * FROM table WHERE column = %s OR column_two = %s", (value1, value2)) but not to do py cursor.execute("SELECT * FROM table WHERE column = %s OR column_two = %s" % (value1, value2))

[teal plinth]

But if I do this:

product_id = 38748597002983498234598203
sql = "INSERT INTO users (user_id) VALUES (%s)"
cursor.execute(sql, product_id)
db.commit()

I get a ProgrammingError [...] MariaDB server version for the right syntax to use near '%s)' at line 1, so I guess I did something wrong again

[radiant elbow]

it needs a tuple of parameter values, not just one

you need cursor.execute(sql, (product_id,))

[teal plinth]

Ah, okay

[radiant elbow]

also... not all databases support %s for the placeholder. I'm not sure what MariaDB wants, so if it's still an error even when you give a tuple of parameters, try using ? instead of %s

[teal plinth]

Using tuples fixed it. Thanks

[radiant elbow]

if using ? instead of %s works - and it probably does - you should prefer it; it looks less like Python string formatting and won't be as confusing when you revisit it later.

[reef hawk]

Hey, I need some help figuring out how I should store this

I have a bunch of "missions" that users have to complete - how should I store which missions users have completed in a database? Here's the way that I thought of to do so (there's about 300k users, probably 100 missions):

-Storing it in the "User" table using "mission" as a column (int list), so I can store which missions they've completed by assigning an ID per mission.

However, from what I understand (correct me if I'm wrong), the user rows get updated very often. If the list is large, it'll take a long time to update/retrieve the table the longer the mission lists get.

Is this the proper way to do it or is there something better? I feel like I'm thinking about this wrong

[wet sierra]

help? :/

[radiant elbow]

it seems to me that a much more reasonable way to represent that would be a completed_missions table where the key is mission ID + user ID. When a user completes a mission, you just insert into completed_missions(mission_id, user_id) values (?, ?)

[radiant elbow]

all 3 of your lobbb = await c.fetchone() should be lobbb = await lolll.fetchone(). c is an old cursor from an already-closed database.

[wet sierra]

oh

stupid of me

i usually name my variables "c"

thanks a lot tho

[reef hawk]

@radiant elbow gotcha - so there wouldn't really be a pkey then right? I guess I would put an index in user_id column so I can fetch a list of missions the user has completed quickly

[radiant elbow]

right - not having a primary key isn't a problem. If you really want to have one you can just use an autoincrement unique ID or something.

[reef hawk]

thank you so much for your help!

[torn sphinx]

I have this table,

+--------+--------------+
| user_id | used_cmd    |
+--------+--------------+
|      1 | 1,2,3        |
|      2 | 1,2          |
|      3 | 4            |
|      4 | 2,3          |
|      5 | 1,2          |
+--------+--------------+```

Each number in used_cmd is correspond to a command.
How can i see what other commands users of command with id of X also used?

For example, all the users who used 1, also used 2 and 3, And who used 4 didnt use anything else.

[radiant elbow]

that would be much easier if your table were instead:

user_id used_cmd
      1 1
      1 2
      1 3
      2 1
      2 2
      3 4
      4 2
      4 3
      5 1
      5 2

[torn sphinx]

Yeah but table is not like that

If it was up to me I'd drop the whole database and go be a bus driver

[radiant elbow]

can you make a new table that is like that?

[torn sphinx]

Probably not 😩

Do you think it's possible? Or shall I find an excuse to tell the manager 🥺

Maybe my laptop charger broke or something if you know better 🤫 🤣

[radiant elbow]

well - putting multiple values in a single row/column makes it hard to do the sort of relational algebra stuff that you're trying to do. It's definitely possible to do it still, it's just a lot more difficult.

[torn sphinx]

Well I think it's because the table was very big and so index was taking a lot of space, that's what I heard anyways.

@radiant elbow oh so I just realized the table is actually like you said lmao

It's just that I was looking at a different view which was joining all values together

[radiant elbow]

ok - if it's laid out like that, then you can just

select a.user_id, a.used_cmd
from the_table as a
inner join (select user_id from the_table where used_cmd=4) as b
on a.user_id = b.user_id

That would find a list of all commands used by anyone who has used command 4, for instance.

[torn sphinx]

@radiant elbow thanks a lot that should get me through this, is not exactly as I need but is something I can work with.

[craggy jackal]

What you have there a non-normalized table

And those are really bad

Whoever designed it didn't design it well

[grim pier]

Hey i have a database which stores the player cash. is there a way to add everyones cash up together with MySQL Syntax?

[radiant elbow]

select sum(cash) from the_table

[grim pier]

@radiant elbow Got it thanks appreciate it 🙂 Is there a way to UPDATE ALL?

cant seem to find anything about updating all on google. only updating individual fields

[radiant elbow]

what would you want "update all" to do?

[torn sphinx]

My MongoDB keeps timing out, can someone help?

[grim pier]

@radiant elbow Sorry for the late reply. update everyones cash on the DB 🙂

[radiant elbow]

to the same value? By the same amount? Something else entirely?

[grim pier]

by a custom value. so i can run a command like !giveallcash 100 say

[radiant elbow]

update the_table set cash = cash + 100

[grim pier]

oh ofcourse. thanks dude. okay now ive got a really awkward question 😄

Okay so i have a weird question here. i have a bounty system in game. say someone has a bounty on their head for $130 i need to spawn them that cash. To spawn cash in game it comes in $10, $50 or $100 is there a formula or a way to work out how to spawn them that cash with those 3 values? I literally have no idea how to do this one.

[radiant elbow]

you mean how many hundreds, how many fifties, and how many tens to give them?

[grim pier]

To spawn the cash i write it to a DB and then a second bot checks the DB and then spawns it. which is logged in on a second PC

@radiant elbow basically yeah

brb coldy 5 mins

[radiant elbow]

!e ```py
amount = 150
hundreds = 0
fifties = 0
tens = 0
while amount >= 100:
hundreds += 1
amount -= 100
while amount >= 50:
fifties += 1
amount -= 50
while amount >= 10:
tens += 1
amount -= 10
print(hundreds, fifties, tens)

[delicate field]

@radiant elbow :white_check_mark: Your eval job has completed with return code 0.

1 1 0

[grim pier]

@radiant elbow dude your the bomb. i owe you a beer 😄 id of never figured that. brb 2 mins

[radiant elbow]

there's more succinct ways to do it, but that's probably the most readable.

!e One of the more succinct ways would be:

amount = 370
hundreds, amount = divmod(amount, 100)
fifties, amount = divmod(amount, 50)
tens = amount // 10
print(f"{hundreds=} {fifties=} {tens=}")

[delicate field]

@radiant elbow :white_check_mark: Your eval job has completed with return code 0.

hundreds=3 fifties=1 tens=2

[grim pier]

@radiant elbow awesome dude ill have a play around. appreciate it 🙂

[radiant elbow]

divmod is a builtin function where divmod(x, y) returns (x // y, x % y)

the number of times y evenly divides x, and the amount left over.

[grim pier]

so the spawn code is say #spawnitem BP_Cash_10 i need to write this into a DB which equals the amount. id put that into an if statement and loop through and then insert into the DB?

[radiant elbow]

if the denominations matter, then the DB needs to store the number of hundreds, fifties, and tens that each player has, and you'd figure out the amount of each that you're adding, and then you'd do update the_table set hundreds = hundreds + ?, fifties = fifties + ?, tens = tens + ? and pass (hundreds, fifties, tens) as parameters to the statement.

[grim pier]

Here is the DB structure so under "ScumCode" it would write

BP_Cash_100
BP_Cash_100
BP_Cash_50
BP_Cash_10
BP_Cash_10

if the amount is 270

[radiant elbow]

I don't think I understand this structure at all - there's one row in the database per bill that the user has? So if they've got 270 in cash that's 5 separate rows in the database?

[grim pier]

@radiant elbow yeah it writes it all in the same row then my other code will just split the string and loop through and spawn the items

because some items have like 10 codes to spawn their guns and packages etc

[torn sphinx]

How do I get the reason which is Test using the underlined user ID?

[grim pier]

@radiant elbow Does this look right? py hundredspawn = "BPC_Cash_100" fiftyspawn = "BPC_Cash_50" tenspawns = "BPC_Cash_10" result = "" for hundreds in divmod(amount, 100): result += hundredspawn + os.linesep for fiftys in divmod(amount, 50): result += fiftyspawn + os.linesep for tens in divmod(amount, 10): result += tenspawns + os.linesep

or have i made hard work out of an easy situation? 😄

[torn sphinx]

Code: ```py
@commands.command()
async def warn(self, ctx, user: discord.Member=None, *, reason=None):
cluster = MongoClient('MongoDB URL')
collection = cluster.Moderation.warns

    if reason is None:
        await ctx.send(":no_entry_sign: You have to provide a reason.")

    else:
        try:
            embed = discord.Embed(timestamp=ctx.message.created_at, title=f"[Warned] {ctx.guild.name}", description=f"**Type:** Warn\n**Reason:**{reason}", color=0xFC6700)
            embed.set_footer(text="Numix", icon_url=self.config.logo)
            await user.send(embed=embed)
            warn = {"_id":user.id, {f"{ctx.guild.id}":{f"{ctx.message.id}":reason}}}
            collection.insert_one(warn)
            await ctx.send(f"{self.s} {user.name}#{user.discriminator} warned *User was notified*")
        except discord.Forbidden:
            warn = {"_id":user.id, {f"{ctx.guild.id}":{f"{ctx.message.id}":reason}}}
            collection.insert_one(warn)
            await ctx.send(f"{self.s} {user.name}#{user.discriminator} warned *User was not notified*")```

Error:py Command raised an exception: DuplicateKeyError: E11000 duplicate key error collection: Moderation.warns index: _id_ dup key: { _id: 791553406266245121 }, full error: {'index': 0, 'code': 11000, 'keyPattern': {'_id': 1}, 'keyValue': {'_id': 791553406266245121}, 'errmsg': 'E11000 duplicate key error collection: Moderation.warns index: _id_ dup key: { _id: 791553406266245121 }'}

[radiant elbow]

That's not right, it would be:

hundredspawn = "BPC_Cash_100" + os.linesep
fiftyspawn = "BPC_Cash_50" + os.linesep
tenspawns = "BPC_Cash_10" + os.linesep
result = ""
hundreds, amount = divmod(amount, 100)
result += hundreds * ("BPC_Cash_100" + os.linesep)
fifties, amount = divmod(amount, 50)
result += fifties * ("BPC_Cash_50" + os.linesep)
tens, amount = divmod(amount, 10)
result += tens * ("BPC_Cash_10" + os.linesep)

[torn sphinx]

How do I get the warning reason which is Test?

[torn sphinx]

No it was a confusion. It was normalised but I sent wrong table

I sent a view

Does anyone know the syntax for a many to many query? To get data from both sides?

[proven arrow]

You can select from your junction/pivot table, and then have a join for each of your associated tables.

[torn sphinx]

k, right yeah

I thought i had to select from the other table, instead of the connection table.

[proven arrow]

That would work also

For an inner join doesn't really matter which order you pick.

[torn sphinx]

Alright, then i should not worry ahaha because i only ever use with just single joins

[runic mirage]

def get(cursor, col):
    cursor.execute('SELECT %s FROM table ORDER BY Num DESC', (col, ))
    return cursor.fetchone()

whats error in this this not execute?

[proven arrow]

Placeholders for query parameters don't work with column names.

[runic mirage]

so how to do this?

SQL.execute('select user_id from Accounts where user_id=%s and server_id=%s', (USER_ID, SERVER_ID) )

its work properly but that one not

for col name how to do this?

@proven arrow help please sorry for ping

[proven arrow]

The %s placeholder can't be used for column names. You can't have dynamic columns like that.

[runic mirage]

so any other way?

[proven arrow]

The easiest way would be to just have seperate queries for them. Otherwise you get into a mess of getting column names, and validating this against the users input, and sanitising user input.

[runic mirage]

then for each col there is one fun for that

[wet sierra]

can i use ' ' for SQL queries rather than " "?

[wooden reef]

In python, '' and "" are exactly the same.
In SQL, I guess it depends

what do you need to use the quotes for? strings or aliases

[wet sierra]

im using them to enclose the querie

"SELECT * FROM table"

^

[wooden reef]

Whichever than

[wet sierra]

oh ok

[wooden reef]

double may be better, because than, you can use single quotes for strings, which is more common in queries

so "SELECT * FROM mytable WHERE name='sloth'"

[wet sierra]

cuz here:

c = await db.execute("SELECT Weekly-pass FROM inventory WHERE user_id = (?)", (ctx.message.author.id,))

i was thinking of adding " " to enclose the weekly-pass part because that - might be a problem right?

wait

Dali prichash srpski?

[wooden reef]

I use tilde quotes for that ``
Doric Lazar 😉

Лазар Ђорић

[wet sierra]

:)

[spring nest]

yay

[drowsy junco]

Quick question since I'm using DBs for the first time. I'm using sqlite3 and I want to store a dictionary/list, should I use TEXT or BLOB as my datatype?

[wooden reef]

List in a dictionary, or a list of dictionaries?
Either way, you should break it down to single fields.
That's the main thing you should focus on when creating tables.

You can DM me your dict, and I'll send you what your table(s) should look like

[drowsy junco]

Well, basically I'm making a economy/fun discord bot where users have items on their inventories
So I was going to, for example, when they try selling something, check if they had it on the dict, then remove and proceed on the sale and updating the db entry

[spark parrot]

Hello, i want to create a data base of products, clients, suppliers, etc for my family business, but i dont have any experience in databases, what would you recommendt to me for starting?

a database, a tutorial for starting...

[velvet fulcrum]

Hi, is anyone familiar with the term persistance layer?
I need to write a python Enigma simulator that includes persistance layer. As far as I know, It's the layer that holds all opperations of exporting/importing reading/saving files. In my example, It would be a python file that holds reading/saving operations for the main program. Is that correct?

[proven arrow]

That would be correct yes.

Actually reminds me of my first ever program I made. It was also the German Enigma, with some advanced features. Although mine was done in Java.

[velvet fulcrum]

Wonderful. That's exactly what I'm doing;))

Do you mind, If I send you documentation that I'm going to make, so you could look it up if it's correct?

I'm sure it will take me couple of days, I still need to finish my program first

[proven arrow]

That's fine if you want. Although my knowledge of how it works, with all the reflectors, rotors and stuff probably needs refreshing as it was done a very long time ago 😅.

I still got the jar file and source code somewhere on my laptop, and I can take a look.

[velvet fulcrum]

No worries, it will send you a single paged introduction of enigma's anatomy with pictures;))
If you could just briefly look the documentation up, that would be great. Can I add you??

Of course If you have time for that

[proven arrow]

I found the feature list from my documentation. I remember the pain of this 😅

And yeah sure you can message me I can take a look some time when I'm free.

[velvet fulcrum]

Yours look little bit more advanced than mine. I will use only 3 non-changeable rotors and 3 changeable reflectors, also I need to implement exporting/importing enigma settings and ciphered text. Also I did a simple GUI;))

Thanks a lot!

[teal plinth]

I'm running into a problem again. I have a dict, which I iterate through. But whenever I try to do this:

for param in values.keys():
    p = param.split(" ")[0].lower()
    self.cursor.execute("UPDATE cmds SET %s = %s WHERE id = %s", (p, values[param], cmd_id))
    self.db.commit()

I get an error, that says it cannot set '<p>' = <value>. And I believe that the cause of that are the '', that surround the <p>. Is there any way to let the db know, to not see them as a string?

[proven arrow]

The column name can't be used as a parameter in parameterised queries.

[teal plinth]

Well that sucks

[vestal geyser]

what the hell do you use instead of "on conflic" since sqlite3 doesnt have it on my version (shouldnt update because its on my vps)

[radiant elbow]

what do you want to do if there's a conflict?

[vestal geyser]

update, but already got it, using COALESCE now, lovely