databases

[jaunty galleon]

client = MongoClient(<<MONGODB URL>>)
db=client.admin```
In this example, how can i get the MONOGDB URL?

[haughty ravine]

on their website bruh

[jaunty galleon]

Where? the url itself above?

[haughty ravine]

connect

press it

[jaunty galleon]

It tells me to add my ip adress

[haughty ravine]

you do bruh

you have to

set ip to 0.0.0.0

[jaunty galleon]

Oh i set it to the real

i mean

the one it auto showed me

Which one of these is the best

[keen spoke]

import sqlite3

def create_table():
    query = "DROP TABLE IF EXISTS login"
    cursor.execute(query)
    conn.commit()

    query = "CREATE TABLE login(username VARCHAR UNIQUE, password VARCHAR)"
    cursor.execute(query)
    conn.commit()

def add_user(username, password):
    query = "INSERT INTO login (username, password) VALUES (?, ?)"
    cursor.execute(query, (username, password))
    conn.commit()

def check_user(username, password):
    query = 'SELECT * FROM login WHERE username = ? AND password = ?'
    cursor.execute(query, (username, password))
    result = cursor.fetchone()
    conn.commit()
    print('[DEBUG][check] result:', result)
    return result

def login():
    answer = input("Login (Y/N): ")

    if answer.lower() == "y":
        username = input("Username: ")
        password = input("Password: ")
        if check(username, password):
            print("Username correct!")
            print("Password correct!")
            print("Logging in...")
        else:
            print("Something wrong")

# --- main ---

conn = sqlite3.connect("users.db")
cursor = conn.cursor()

create_table()  # use only once

username = input("New username: ")
password = input("New password: ")

add_user(username, password)

login()

cursor.close()
conn.close()

[haughty ravine]

bruh, you must choose

[keen spoke]

what is wrong with the check

is check not a function in python??

check is a function in sqlite3

[jaunty galleon]

I don't know what is the best for discord.py

[keen spoke]

should i open a help channel to get help faster

but there's a chance they won't know SQL

[jaunty galleon]

I think there are a lot od people here that know alot about SQL

[keen spoke]

ok i will go ask in a help channel then

brb

lmao bro

it was just bc it should be check_user not check

my dumbass

[jaunty galleon]

Why is it like that?

from pymongo import MongoClient
from pprint import pprint```
These are the two imports the guide said

[spice mango]

Pop a import pymongo above that first one and try again maybe?

[jaunty galleon]

New error

[jaunty galleon]

Yeah worked, new error

Message=The "dnspython" module must be installed to use mongodb+srv:// URIs
Source=C:\Users\Student\Desktop\discordbots\save_130.py
StackTrace:
File "C:\Users\Student\Desktop\discordbots\save_130.py", line 27, in <module>
client = pymongo.MongoClient("mongodb+srv://nirdan12:<password>@cluster0.ypjoy.mongodb.net/myFirstDatabase?retryWrites=true&w=majority")

[spice mango]

That one looks pretty self-explanatory 👍

[crystal jetty]

pip install dnspython

[jaunty galleon]

Some other error from the IDE itself

oh ok

[spice mango]

I would recommend a tutorial on environments and how to build/destroy one works, at some point. There are ways to declare all the things you need for an application and have the system take care of it for you and it's a lifesaver.

[jaunty galleon]

New errors i hate these

And the IDE inner error:

  Message=Authentication failed., full error: {'ok': 0, 'errmsg': 'Authentication failed.', 'code': 8000, 'codeName': 'AtlasError'}
  Source=C:\Users\Student\Desktop\discordbots\save_130.py
  StackTrace:
  File "C:\Users\Student\Desktop\discordbots\save_130.py", line 29, in <module>
    serverStatusResult=db.command("serverStatus")

[pure mortar]

aaaaaaaaaaaaaaaaaa i finally got mysql to work for me

[jaunty galleon]

Nice

[pure mortar]

i messed up the installation and it couldnt find a key folder

so i ended up reinstalling like 5x or so

before just completely wiping it

then trying again

[torn sphinx]

How to update existing key in sql database

[jaunty galleon]

?

[torn sphinx]

May be password issue🤔

[jaunty galleon]

How can i fix it?

[torn sphinx]

Check connection url and password or use autogenerated password

Sometimes due to special characters in password it may happen

[jaunty galleon]

My password is like this:
AAAA0000

Not my real password

just the format of it

[jaunty galleon]

It copied the code that was provided me

[pure mortar]

hmm do i have to shutdown my mysql local server through the command prompt every time

wish it was on the interface

[burnt turret]

i never shut it down tbh

doesn't affect my computer's performance much anyways

[jaunty galleon]

So anyone knows what should I do?

[burnt turret]

you're sure you're using the right details?

@jaunty galleon

[jaunty galleon]

Wdym?

I use the code the website provided me

[torn sphinx]

Is the connection string a cluster ?

[jaunty galleon]

That is what i know

[torn sphinx]

mongodb+srv://<user>:<password>@<cluster_name>.mongodb.net/<

This format may work

[jaunty galleon]

Would you like me to send it?

client = pymongo.MongoClient("mongodb+srv://nirdan12:<password>@cluster0.ypjoy.mongodb.net/myFirstDatabase?retryWrites=true&w=majority")
db = client.test
serverStatusResult=db.command("serverStatus")
pprint(serverStatusResult)```

[burnt turret]

did you allow access for your IP? @jaunty galleon

[jaunty galleon]

Yes

This is where it shows the eror

[jaunty galleon]

So you know?

Anyone knows what is the problem?

[jaunty galleon]

error:

  Message=Authentication failed., full error: {'ok': 0, 'errmsg': 'Authentication failed.', 'code': 8000, 'codeName': 'AtlasError'}
  Source=C:\Users\Student\Desktop\discordbots\save_130.py
  StackTrace:
  File "C:\Users\Student\Desktop\discordbots\save_130.py", line 29, in <module>
    serverStatusResult=db.command("serverStatus")

code:

client = pymongo.MongoClient("mongodb+srv://nirdan12:[e-mail]/myFirstDatabase?retryWrites=true&w=majority&authSource=admin")
db = client.admin
serverStatusResult=db.command("serverStatus")
pprint(serverStatusResult)```

There is a bigger error but this one is from the IDE VSCommunity

[pure mortar]

good to know. thanks

[waxen lagoon]

Hello,

have a sqlalchemy query that is not working as i think it should:

count_query = session.query(
    TimeCheck.station_id,
    func.sum(case([(TimeCheck.in_communication == True, 1)], else_=0))
        ).filter(Station.interval == 60).group_by(TimeCheck.station_id).all()

returns a list of tuples [('SCE-2017', 435),...]

the 435 is the total rows that have interval of 60 I want to get just the sum of the in_communications that are true/false based on the station_id. it seems to just be ignoring the func.sum and case

[jaunty galleon]

?

[floral crater]

if anyone is good with MySQL could they PM me please!

[primal cave]

Does anyone here know mongo because I need some quick help
How would I insert a list into mongo await warnsys.insertone({'id': member.id, 'guild': ctx.guild.id, 'warns': []})
doing [] just makes the value of warns null

[celest zodiac]

@floral crater just ask your question here, please

[floral crater]

no one repys

[celest zodiac]

@floral crater we're all volunteers here, it sometimes takes time and persistence to get an answer. what's your question?

[torn sphinx]

how do I setup a table/db with postgresql?

if i switch to a vps, can I transfer the data?

[floral crater]

i just need some help with mySQL and need to send code through

[celest zodiac]

post it here, or use a pastebin for longer code.

[floral crater]

import pandas as pd
from mysql.connector import Error
import mysql.connector

config = {
  'user': 'root',
  'password': 'root',
  'host': '127.0.0.1',
  'port': 3306,
  'database': 'Client Info',
  'raise_on_warnings': True
}

cnx = mysql.connector.connect(**config)

cursor = cnx.cursor(dictionary=True)

mySql_insert_query = """INSERT INTO Client_Info (Firstname, Secondname, Email, Phonenumber) 
                           (VALUES user_firstname, user_secondname, user_email, user_number) """

cursor.execute(mySql_insert_query)

this getting an error

[celest zodiac]

... well, what's the error?

[floral crater]

Traceback (most recent call last):
  File "/Users/*********/PycharmProjects/pythonProject10/database connect.py", line 23, in <module>
    cursor.execute(mySql_insert_query)
  File "/Users/********/PycharmProjects/gui/pythonProject10/lib/python3.8/site-packages/mysql/connector/cursor.py", line 551, in execute
    self._handle_result(self._connection.cmd_query(stmt))
  File "/Users/**********/PycharmProjects/gui/pythonProject10/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 "/Users/**********/PycharmProjects/gui/pythonProject10/lib/python3.8/site-packages/mysql/connector/connection.py", line 395, in _handle_result
    raise errors.get_exception(packet)
mysql.connector.errors.ProgrammingError: 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'VALUES 
                           `user_firstname`, `user_secondname`, `user_em' at line 2

trying to get client info from chat bot straight to database

[celest zodiac]

OK, I see it now. The way you format queries is wrong

[floral crater]

okay

[celest zodiac]

when you want to insert the contents of variables into a query, there's a special format you have to use. Your string is just inserting the literal strings user_firstname etc. Not the variable contents

[floral crater]

ohh

[celest zodiac]

what's more, you don't actually have any of those variables defined.

[floral crater]

look wait

[celest zodiac]

so even if you did use the right format it wouldn't work anyway.

[floral crater]

import calendar
from datetime import datetime
import random

#express the bot as a dictionary
bot = [
    {
        'match': lambda x: x == 'time',
        'respond': lambda: f"The time is {datetime.now().strftime('%H:%M:%S')}",
    },

]

print("Bot: Hello, I will be helping you today!","\nBot: Before we book your meeting, we will need some information please.", "\nBot: Please could you enter your name.")
user_firstname = str(input("Enter your first name here: "))
print("Bot: What is your second name aswell please.")
user_secondname = str(input("Enter your second name here: "))
print("Bot: What is your email address aswell?")
user_email = str(input("Enter your email here: "))
print("Bot: Could you also enter your mobile number.")
user_number = int(input("Enter your number here: "))
#details_check = print("Are these details correct?", user_firstname, user_secondname, "|",  user_email, "|", user_number, "\nIf they are type 'Yes', if they are not type 'No'")
#details_check_input = input("Please write 'Yes' or 'No': ")
#if details_check_input == 'No' or 'no':
   # redo_details = print(str(input("Enter your first name here: ")),str(input("Enter your second name here: ")),str(input("Enter your email here: ")),int(input("Enter your number here: ")))
#else:
    #print("Good!")

print("Bot: Which year would you like to book it in?")
user_chosen_year = int(input("Enter your year here: "))
print("Bot: Which month would you like to book it in?", "\nBot: Make sure the month is in its numerical form thanks.")
user_chosen_month = int(input("Enter the month here: "))

c = calendar.TextCalendar(calendar.SUNDAY)
str= c.formatmonth(user_chosen_year,user_chosen_month)
print(str)

print("Bot: Please now enter a date.")
user_chosen_date = int(input("Enter the day here: "))
print("These are the available times on the your selected date")

the ot

bot

[celest zodiac]

OK, good

[floral crater]

they defined in there 😄

[celest zodiac]

this is how queries are formatted when you want to use variables inline

mySql_insert_query = """INSERT INTO Client_Info (Firstname, Secondname, Email, Phonenumber) 
                           (VALUES ?,?,?,?) """

cursor.execute(mySql_insert_query, (user_firstname, user_secondname, user_email, user_number))

the ? in a query string indicates a positional variable; the tuple that's the 2nd argument to .execute() contains the variables to insert positionally. (so, user_firstname is first)

[floral crater]

okay

[torn sphinx]

^

[celest zodiac]

@torn sphinx https://realpython.com/python-sql-libraries/

[floral crater]

it still dosent want to work

[celest zodiac]

what error do you get? I can't help you unless you tell me specifically what's wrong

[floral crater]

Traceback (most recent call last):
  File "/Users/samueldoncaster/PycharmProjects/pythonProject10/database connect.py", line 22, in <module>
    cursor.execute(mySql_insert_query, (user_firstname, user_secondname, user_email, user_number))
  File "/Users/samueldoncaster/PycharmProjects/gui/pythonProject10/lib/python3.8/site-packages/mysql/connector/cursor.py", line 542, in execute
    raise errors.ProgrammingError(
mysql.connector.errors.ProgrammingError: Not all parameters were used in the SQL statement

[celest zodiac]

let's see the relevant code

[floral crater]

mySql_insert_query = """INSERT INTO Client_Info (Firstname, Secondname, Email, Phonenumber) 
                           (VALUES user_firstname, user_secondname, user_email, user_number) """

cursor.execute(mySql_insert_query, (user_firstname, user_secondname, user_email, user_number))

[celest zodiac]

no, tha'ts wrong.

[floral crater]

oh

[celest zodiac]

mySql_insert_query = """INSERT INTO Client_Info (Firstname, Secondname, Email, Phonenumber) 
                           (VALUES ?,?,?,?) """

cursor.execute(mySql_insert_query, (user_firstname, user_secondname, user_email, user_number))

note the ?s in the query string. They must be there to indicate where the variables are inserted positionally.

[floral crater]

so i keep the ?

[celest zodiac]

yes, those have to be there.

[floral crater]

Traceback (most recent call last):
  File "/Users/samueldoncaster/PycharmProjects/pythonProject10/database connect.py", line 22, in <module>
    cursor.execute(mySql_insert_query, (user_firstname, user_secondname, user_email, user_number))
  File "/Users/samueldoncaster/PycharmProjects/gui/pythonProject10/lib/python3.8/site-packages/mysql/connector/cursor.py", line 542, in execute
    raise errors.ProgrammingError(
mysql.connector.errors.ProgrammingError: Not all parameters were used in the SQL statement

this now

[modest pulsar]

hi, how can i fetch all the values of a column pls?

[floral crater]

@celest zodiac how to fix this error

[celest zodiac]

@floral crater show the code you currently have now?

[floral crater]

from bot import bot, user_firstname, user_secondname, user_email, user_number
import pandas as pd
from mysql.connector import Error
import mysql.connector

config = {
  'user': 'root',
  'password': 'root',
  'host': '127.0.0.1',
  'port': 3306,
  'database': 'Client Info',
  'raise_on_warnings': True
}

cnx = mysql.connector.connect(**config)

cursor = cnx.cursor(dictionary=True)

mySql_insert_query = """INSERT INTO Client_Info (Firstname, Secondname, Email, Phonenumber) 
                           (VALUES ?,?,?,?) """

cursor.execute(mySql_insert_query, (user_firstname, user_secondname, user_email, user_number))

[modest pulsar]

oh, can you answer to my question too pls

[floral crater]

#results = cursor.fetchall()

#for row in results:
 # CustomerID = row['CustomerID']
  #title = row['Firstname']
  #title1 = row['Secondname']
  #email = row['Email']
  #Phonenumber = row['Phonenumber']
  #print('%s | %s | %s | %s | %s' % (CustomerID, title, title1, email, Phonenumber))

replace the customer id and titles for example with your things

[celest zodiac]

@modest pulsar I'm tasking, please be patient

[floral crater]

@modest pulsar u understand?

[celest zodiac]

@floral crater I think there's one more thing we need to add:

cursor = cnx.cursor(dictionary=True)
needs to be
cursor = cnx.cursor(dictionary=True, prepared=True)

[floral crater]

alright

[celest zodiac]

I haven't worked with MySQL connectors directly in a while

[modest pulsar]

but, why must i fetch all when i want to fetch only one column....?

[floral crater]

now getting ```
Traceback (most recent call last):
File "/Users/samueldoncaster/PycharmProjects/gui/pythonProject10/lib/python3.8/site-packages/mysql/connector/connection.py", line 843, in cursor
return (types[cursor_type])(self)
KeyError: 20

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
File "/Users/samueldoncaster/PycharmProjects/pythonProject10/database connect.py", line 17, in <module>
cursor = cnx.cursor(dictionary=True, prepared=True)
File "/Users/samueldoncaster/PycharmProjects/gui/pythonProject10/lib/python3.8/site-packages/mysql/connector/connection.py", line 846, in cursor
raise ValueError('Cursor not available with given criteria: ' +
ValueError: Cursor not available with given criteria: dictionary, prepared

[modest pulsar]

yep np

[floral crater]

well make it so it fetch 1 column then

send me ur code

via pm

[celest zodiac]

@floral crater oof!

[floral crater]

yep

[celest zodiac]

OK
cursor = cnx.cursor(dictionary=True)
reset that line to that, and then change the query string to this

[modest pulsar]

but, i don't know how to do it... 😂

[floral crater]

send ur code

[celest zodiac]

mySql_insert_query = """INSERT INTO Client_Info (Firstname, Secondname, Email, Phonenumber) (VALUES %s,%s,%s,%s) """

try that, see if it works

[floral crater]

Traceback (most recent call last):
  File "/Users/samueldoncaster/PycharmProjects/pythonProject10/database connect.py", line 22, in <module>
    cursor.execute(mySql_insert_query, (user_firstname, user_secondname, user_email, user_number))
  File "/Users/samueldoncaster/PycharmProjects/gui/pythonProject10/lib/python3.8/site-packages/mysql/connector/cursor.py", line 551, in execute
    self._handle_result(self._connection.cmd_query(stmt))
  File "/Users/samueldoncaster/PycharmProjects/gui/pythonProject10/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 "/Users/samueldoncaster/PycharmProjects/gui/pythonProject10/lib/python3.8/site-packages/mysql/connector/connection.py", line 395, in _handle_result
    raise errors.get_exception(packet)
mysql.connector.errors.ProgrammingError: 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'VALUES 'bog','big','[e-mail]',755919830)' at line 2

the 'bog' and 'big' tjings are the inputs

[modest pulsar]

ok, but i didn't do anything yet...

[floral crater]

oh well what do u want to do, have u got a database set up firstly?

[celest zodiac]

@floral crater AHA! there's an example here for how to do the insert https://dev.mysql.com/doc/connector-python/en/connector-python-example-cursor-transaction.html

[floral crater]

oooh lovely

lemme take a peak

[modest pulsar]

ys of course

[celest zodiac]

Turns out I got a lot wrong, it'

[modest pulsar]

i mean

[celest zodiac]

it's very different from what i thought it was. I'm used to SQLite

but the basic idea is the same - you insert placeholders in the string that the SQL connection layer replaces with your variables

[modest pulsar]

i didn't do anything from the db in the part of code that i'm doing

[floral crater]

well pm me what u want to do, and i can try help

@celest zodiac still not bloomin working

ugh

[torn sphinx]

@celest zodiac if I switch to a vps, can I transfer my postgres data?

[celest zodiac]

I would assume so?

[torn sphinx]

how?

[celest zodiac]

@floral crater sorry - but that doc I linked seems to have the basic idea. I just haven't worked with the MySQL DB connector itself. sorry about the confusion earlier.

@torn sphinx back it up. upload it to the server, restore it there

[torn sphinx]

ah

lemme book mark this

[floral crater]

all good, do u know anyone who is good with MySQL

[celest zodiac]

@floral crater try asking again here at a different time of day? I know that people pop on and off all the time. one other thing you can try is take the example they give you, work with that instead of your existing code, and see if you can get that example running so that you're working with something known good

[floral crater]

oh okay man

ty for the help tho

[floral crater]

@celest zodiac managed to figure it out!

[celest zodiac]

excellent!

[sacred bay]

Hey, sorry if this isn't the right place to ask, but I'm getting no replies anywhere else, including the official redis discord.

I'm running redisRaft, https://github.com/RedisLabs/redisraft. I'm trying to setup netbox, which uses Django, and in the process, my redis cluster now replies ERR Blocking module command called from Lua script and nothing else when any commands are ran, persisting even after rebooting all nodes in the cluster. Google results are very limited and not helpful.

[tacit umbra]

So aiosqlite doesn’t block. So it shouldn’t increase response time by inserting data should it?

[low cloak]

i'm using plain old python 3, no flash or jinja2.

i'm not sure how to code a table up from the data i pull from my database

i can get it to print in the console

but i need the table to print on a webpage

[rain plank]

Blocking doesn't mean slower, it just means the whole code waits for the function to end.

[tacit umbra]

right...

but isnt async basically doing multiple things at the same time?

What is the cause of slow response time if it isn’t blocking @rain plank

[rain plank]

Slow response time is probably just slow internet

[tacit umbra]

No sadly not

I should probably explain

I’m running a bot on a vps with very high internet speeds

and the one cog that causes the response to go from like 20 ms to 20k ms is a mass insert of data into a database every message

Is there a way to get around that?

[opal dawn]

you can use a pandas dataframe to output HTML:
https://pandas.pydata.org/pandas-docs/version/0.23.4/generated/pandas.DataFrame.to_html.html

create a dataframe from your table, and output to html

[low cloak]

oh shit, aaha

this will take time to figure out. i'm not too far into python

not sure if i can use this since it involves downloading the library

and this assignment is just straight up python

[opal dawn]

how are you using sql?

[low cloak]

can you rephrase that?

[opal dawn]

what module are you using to connect to your database?

[low cloak]

mysql/pymsql

pymsql.cursors

if that's what u mean

@opal dawn

[opal dawn]

yeah

[low cloak]

i just can't figure out how to create a html table and display it on the webpage with the data from the database

and i can't really use flask, jinga2, or anything that requires me having to install something. so keeping it vanilla would be best

due to it being an assignment

[opal dawn]

you've already installed pymysql tho 🤔

only other way I can think of is saving the table as json, and using javascript to parse it into a table in HTML

quite impractical iyam

[low cloak]

when i load the webpage

the response displays that

i wonder if i could do that again and build a table out of it

but it wont recognize my data/variables that contain it since the """

[opal dawn]

wow, you actually have to create HTML using python

[low cloak]

yeah

that probably would've helped you if i brought that up earlier

[opal dawn]

if you can iterate through the rows of the dict, you can create a table row with

<tr>
  <td>row['user_id']</td>
  ...
<tr/>

[low cloak]

rn i have this

and i have it printing to the console to make sure it works and prints out all my data

which it does

            cursor = db.cursor()
            
            sql = """INSERT INTO registered_users
                    (Title, FirstName, LastName, Street, City, Province, PostalCode, Country, PhoneNum, Email)
                VALUES
                    (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s)"""
            values = [title, fname, lname, streetname, city, province, postalcode, country, phonenum, email]
            cursor.execute(sql,values)
            db.commit()

            cursor.execute("SELECT * FROM registered_users")
            data = cursor.fetchall()

            for row in data:
                print(row)
                
                
                #id = row['user_id']
                #lname = row['LastName']
                #fname = row['FirstName']
                #city = row['City']

                #response = ( str(id) + "// " + fname + " // " + lname + " // " + city)

            cursor.close()
            db.close()

            import gc
            gc.collect()

[opal dawn]

response = (f"<tr><td>{row['user_id']}</td><td>{row['lastName']}</td></tr>")

[low cloak]

response = (f"<tr><td>{row['user_id']}</td><td>{row['lastName']}</td></tr>")
KeyError: 'lastName'

oh

i know

[opal dawn]

okay, so you have to make sure it's in a table format. So here's an example of how to do that: https://www.w3schools.com/html/html_tables.asp

the first <tr></tr> are your db table column names

[low cloak]

what's the f for?

and its missing <table> anyways

[opal dawn]

f is string formatting. it tells python to treat whatever encapsulated in {} in your string as a variable

[low cloak]

awh

its getting there

gonna be a long fucking statement

[opal dawn]

just remember your first row with table names is denoted by <th></th>

[low cloak]

ill just code this up

but i do have another bug, which isn't database related, but its probably an oversight on my part

When I run and type in values for every field, it works. when i type in one field and leave the rest empty, it works. When i type in something for every field except for one, it breaks.
File "c:/Users/silas/OneDrive/Desktop/php-assignments/silas-python/assignment3.py", line 172, in app
response = html % (titleError, fnameError, lnameError, streetError, cityError, provinceError, postalError, countryError, phoneError, emailError)
UnboundLocalError: local variable 'emailError' referenced before assignment
for example

[opal dawn]

doesn't look like you have emailError initialized where your error variables are

[opal dawn]

try commenting out titleError = '' and see if you get the same error

[low cloak]

it didn't error check at all that time and straight posted

[opal dawn]

hmm

[low cloak]

yeah removing it makes it unboundlocalerror

that non error check was an indentation issue on my end

but i fixed that

[jaunty galleon]

How can i make it save multilines?

[jaunty maple]

I can help

Can you show the code?

[jaunty galleon]

Yes

[jaunty maple]

Show it

So I can help you

[jaunty galleon]

One second

import json
how_many = int(input("How many users are there? - "))
for i in range(how_many):

    name = input("What is your name? - ")
    age = int(input("How old are you? - "))
    city = input("What city you live in? - ")
    data_file = {
        "name": name,
        "age": age,
        "city": city
    
        }
    for i in range(how_many):

        with open("names.json", "w") as write_file:
            json.dump(data_file, write_file)```

This is the current code

[jaunty maple]

in the json.dump line replace it with

json.dump(data_file, write_file, sort_keys=True, indent=4)

[jaunty galleon]

Thank you

[jaunty galleon]

Still won't work

[jaunty maple]

Try
write_file.write(json.dumps(data_file, indent=4, sort_keys=True))

[jaunty galleon]

Nope

[jaunty galleon]

This doesn't work

[opal dawn]

a list is what you want to use

import json

how_many = int(input("How many users are there? - "))
users = []
for i in range(how_many):
    name = input("\nWhat is your name? - ")
    age = int(input("How old are you? - "))
    city = input("What city you live in? - ")
    users.append({
        "name": name,
        "age": age,
        "city": city
        })
   
with open("names.json", "w") as write_file:
    json.dump(users, write_file, indent=2)

[mint dust]

how would one handle multiple connections to sqlite3 database, so that it didn't get locked?

I was thinking of query but I don't want to save the query into another file, but I can't use a global list because I'm using async

can I see if the db is locked?

[jaunty galleon]

How can i get data from json file?

[sharp bobcat]

I'm using SQLAlchemy in my project, with metadata.create_all function, that takes my ORM models and automatically initializes the tables, if they aren't already initialized. But my problem is, that if I update these models, create_all will detect that the table exists and won't do anything. How would I add a column into a database from my ORM structure, whenever it changes, automatically?

[hot sandal]

i started python last week and it feels impossible , i learn comands , know how to use em , forget them next day

[prisma girder]

You should implement (or find) some migrations mechanism

[sharp bobcat]

Can you suggest some?

I don't have that much experience with SQLAlchemy and I'm still just starting out with it, so I don't really feel comfortable writing my own migrations

[burnt turret]

i think alembic is popular

!pypi alembic

!pypi alembic

[delicate field]

alembic v1.5.5

Author

Mike Bayer

Requires Python

!=3.0.*,!=3.1.*,!=3.2.*,!=3.3.*,!=3.4.*,!=3.5.*,>=2.7

Summary

A database migration tool for SQLAlchemy.

License

MIT

[jaunty galleon]

?

[prisma girder]

I never used migrations in Python but alembic looks fine

[prisma girder]

Hey, sorry but it's really basic stuff.. Just data = file.read() and take output to json.loads(data)

Maybe you should read some introduction to Python

[jaunty galleon]

But how can i get a certain data from it?

[jaunty galleon]

I mean how can i use it

[prisma girder]

import json
with open("file.json") as file:
  data = file.read()
data = json.loads(data)

Now you can use data as a dict

[jaunty galleon]

And if i want it to be a list?

So i can choose something random out of it using random.choice

[prisma girder]

It can be list too... Use print(data) to display it

[jaunty galleon]

K

And what if i have two lists in json file, how can i access one of them?

[prisma girder]

It's unallowed to have multiple JSON objects inside one file

[jaunty galleon]

If i want to make a game where the user can also put words in it?

And then use it later

[prisma girder]

I don't get it

[jaunty galleon]

I want to make some sort of a memorize game.
The program shows the user a word, deletes after a seconds abd thab he cab type.
I want there would be anither option that the user can make a liat of words himself, that it woukd save it to use later on

[prisma girder]

Give them an option to add custom wordlists and save them in DB or some files

[jaunty galleon]

And can't I save it in json and use later?

[prisma girder]

Yes, you can

However I don't think that JSON is suitable here

[jaunty galleon]

Well this is what i want

Oh

Than what ia json for

[prisma girder]

If you have list of words you can just store them in one file with new lines as a separator

[jaunty galleon]

So it is possible to do what i wanted with json?

Beacause i try to use mongoDB and it wouldn't work

[prisma girder]

Yes

[jaunty galleon]

Than how can i if I can't access one object from a json file?

[prisma girder]

Can you show your minimal effort?

Try it yourself, if you will have problems come here

[jaunty galleon]

You got good website tuturial si i can learn? And are there docs for it?

[prisma girder]

I don't know any good tutorial, I am using standard library page: https://docs.python.org/3/library/

[cinder portal]

hello everyone, i'm planning to create some kind of like a phone book and store it within a database using MySQL. when i'm done with it, i want to post it on my github. when i publish the code into the github, should i include the "user" name and my "password"? can other people access my database remotely if they know the information above?

[burnt turret]

well if the database is running locally, they won't be able to access it

(unless you've allowed remote access, which by default should be turned off)

BUT it is better to hide the database secrets some other way - a common method is to declare them as variables in a .env file, and then use it in your code. you add the .env to your gitignore so that it isn't uploaded to github

https://vcokltfre.dev/tips/tokens/#env-files-and-the-python-dotenv-module this has an example on how to use .env files

[cinder portal]

I'll try it later on. Thank you so much @burnt turret

[jaunty maple]

someone named theirselves GETHACKED') DROP TABLE users;
Nice try, but the table is called members

[burnt turret]

haha

[torn sphinx]

hi

what is a composite key

is it like a primary key and a foreign key

@torn sphinx yeah

its multiple keys as one

ohhh

thanks

[inner sentinel]

.topic

[viral hill]

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

Suggest more topics here!

[slender rose]

how can we delete something from asyncpg based on 2 checks

for example DELETE FROM table WHERE id = $1, abc = $2", smth_here, smth_here)

it says syntax error near ","

[burnt turret]

you use either AND or OR to combine those conditions

use condition1 AND condition2 if both have to be true

condition1 OR condtion2 if either has to be true

[slender rose]

gotcha

[blissful solar]

how can I access values of an object while updating it with pymongo with UpdateOne e.g.

to conditionally calculate a value to update

[frail jasper]

I am trying to reverse a string in line 19 and I cannot figure out why when I print the variable reverse, nothing comes up

[upper juniper]

not really a database related question

[frail jasper]

what kind of question is it?

[upper juniper]

the help channel kind

[jaunty galleon]

import json

# some JSON:
x = '{ "name":"John", "age":30, "city":"New York"}'

# parse x:
y = json.loads(x)

# the result is a Python dictionary:
print(y["name"])```
I don't understand this code, what does it have to do with a json file?

[burnt turret]

it doesn't really

!d json.loads

[delicate field]

`json.loads`

json.loads(s, *, cls=None, object_hook=None, parse_float=None, parse_int=None, parse_constant=None, object_pairs_hook=None, **kw)```
Deserialize *s* (a [`str`](stdtypes.html#str "str"), [`bytes`](stdtypes.html#bytes "bytes") or [`bytearray`](stdtypes.html#bytearray "bytearray") instance containing a JSON document) to a Python object using this [conversion table](#json-to-py-table).

The other arguments have the same meaning as in [`load()`](#json.load "json.load").

If the data being deserialized is not a valid JSON document, a [`JSONDecodeError`](#json.JSONDecodeError "json.JSONDecodeError") will be raised.

Changed in version 3.6: *s* can now be of type [`bytes`](stdtypes.html#bytes "bytes") or [`bytearray`](stdtypes.html#bytearray "bytearray"). The input encoding should be UTF-8, UTF-16 or UTF-32.

Changed in version 3.9: The keyword argument *encoding* has been removed.

[burnt turret]

in layman terms json.loads is just converting that string into a dictionary

[jaunty galleon]

import json
size = input("What is the pizza size? - ")
price = input("What is the price of the pizza? - ")
toppings = []
toppings_choice = int(input("how many toppings would you like?"))
for i in range(toppings_choice):
    topping_add = str(input(f'What is the {i} topping? - '))
    toppings.append(topping_add)
name = input("What is you name? - ")
phone = input("Phone number? - ")
email = input("What is your email? - ")
pizza_order_data = {
    "size": size,
    "price": price,
    "toppings": toppings,
    "client": {
        "name": name,
        "phone": phone,
        "email": email
        }
    
    }
with open("phone_details.json", "a+") as write_file:
    json.dump(pizza_order_data, write_file)

Wouldn't put the data in the json file, don't mind the names lol

[burnt turret]

you could try it

[jaunty galleon]

Try what?

[burnt turret]

oh i thought you're asking if it would work

what happens?

[jaunty galleon]

It doesn't put the data in the json file

[jaunty galleon]

Ha lol

[minor goblet]

Hi, can there be a SQL statement where I either select rows with column having a particular value and if that value doesn't exist select all the non-null rows/all rows?

I'm new to SQL

[jaunty galleon]

So why won't it work?

[blissful solar]

try:

with open("phone_details.json", "a+") as write_file:
    write_file.write(json.dump(pizza_order_data))

[jaunty galleon]

Still wouldn't work

@burnt turret What should I do?

[burnt turret]

why are you opening the file in a+

[jaunty galleon]

So i can write multiple times

[burnt turret]

but json doesn't work that way

someone already told you this here

you can't have multiple json objects (in this case, the dictionary) in a single file

if you want more dicts in the single file you make it a list

and then the way you add more is NOT opening in append mode

you first open in read mode, load the existing list

.append your new dictionary to this list

and then open the file again, this time in w mode and dump the updated list in

[jaunty galleon]

import json
names = []
name = input("Name - ")
names.append(name)
with open("file_name", "w") as f:
  json.dump(names, f)

How do i go from this?

[burnt turret]

you should read what i said again

[jaunty galleon]

than json.load?

[burnt turret]

did you understand what i said earlier?

[jaunty galleon]

Sort of

[burnt turret]

which part did you not understand

actually, claim a help channel

json isn't a database

[jaunty galleon]

Why claim help?

So what is json for?

[dreamy flint]

Is there any good way to use 1 file to host my database connection, this one file also has my functions which update info to my database.

Whereas I can import the file and use the functions in that file to update to a database?

[jaunty galleon]

I don't understand how to put in data, to add data to the json file without "w"

[burnt turret]

json shouldn't be used as a database - like in your case, where you seem like you'll want to update things often

you can use json to store constants, configuration information and the such

[burnt turret]

read what i said there once more - you can't

[jaunty galleon]

Ok

than what should i do?

[burnt turret]

i already said what you should do

[jaunty galleon]

To use DB?

like mongoDB, SQL and that?

[burnt turret]

you can use a database sure

[jaunty galleon]

mongoDB doesn't work

[burnt turret]

then the discussion would be topical for this channel as well

mongodb works fine

it wouldn't exist if it didn't work

[jaunty galleon]

client = pymongo.MongoClient("mongodb+srv://nirdan12:[e-mail]/myFirstDatabase?retryWrites=true&w=majority&authSource=admin")
db = client.admin
serverStatusResult = db.adminCommand("serverstatus")
pprint(seserverStatusResult)```
I don't understand why this happens:
```py

  Message='Collection' object is not callable. If you meant to call the 'adminCommand' method on a 'Database' object it is failing because no such method exists.
  Source=C:\Users\Student\Desktop\discordbots\save_130.py
  StackTrace:
  File "C:\Users\Student\Desktop\discordbots\save_130.py", line 29, in <module>
    serverStatusResult = db.adminCommand("serverstatus")

[burnt turret]

just wait two minutes please, ill get back to you

[jaunty galleon]

Ok

[burnt turret]

If you meant to call the 'adminCommand' method on a 'Database' object it is failing because no such method exists.

the error seems to be telling you what's going wrong

[jaunty galleon]

Here is the problem:
I don't even know what is collection, so at the ,moment, i just want to learn how to make it work, and than start learn all of this

So please, how can i fix it?

[burnt turret]

well I don't think that's the right attitude for anything, so I'm gonna explain

a collection is a set of data in a database in mongodb

you can think of it as an equivalent to a table from an SQL db

what are you trying to do with "serverstatus" there

[jaunty galleon]

Don't know

told you

this is only a start for me

[burnt turret]

well you should be following some structured mongodb course of some sort

i can't help you without knowing what you're even trying with that there

[jaunty galleon]

ok

[burnt turret]

you generally call methods on the Collection object - methods like find, update, delete

not just call the Collection object directly the way you've done

[jaunty galleon]

k

[celest rain]

hey! so im using sqlite, and im trying to delete a date in a column if its in the past (for example, im gonna delete a feb 25 date because its feb 26), i already know to do:
DELETE FROM inactive WHERE enddate <= timenow
question is, will this work?

[foggy juniper]

Anyone able to help me in #help-chestnut ?

[light zinc]

CREATE OR REPLACE FUNCTION getGoodsId(
    in_vars integer
) RETURNS integer AS $BODY$
DECLARE
    out_id integer;
BEGIN
    SELECT id INTO out_id
    FROM (
        SELECT width, height, id
        FROM (

             ) inner_query
        WHERE width = 3
          AND height = 2
     ) outer_query;
    
    return out_id;
END;
$BODY$LANGUAGE plpgsql

Can I do this without the outer_query select statment?

[torn sphinx]

@burnt turret how can I cehck if a row exists with a certain column volue

[burnt turret]

SELECT * FROM tablename WHERE column=<whatever>

If the query returns something, a row exists there

[torn sphinx]

ok

@burnt turret so i installed asyncpg, how do I create a db?

[burnt turret]

You create the database first, through psql

After this, you'll use asyncpg to connect to that new database

[torn sphinx]

ok

how do I make with psql?

[burnt turret]

Just open it up and type the queries in

[torn sphinx]

open it up?

(sorry i'm dumb)

[burnt turret]

Search psql on your computer

You'll get something like "SQL shell (psql)"

Open it

@torn sphinx sorry for the late response

[low iron]

i have this sqlite statement but it won't delete the row (the connection and other things are right):

"DELETE FROM submission_tracking WHERE submission_id = ?", (submission_id,)

submission_id is a string
i tried putting quotes around ? but it gave me an error:

sqlite3.ProgrammingError: Incorrect number of bindings supplied. The current statement uses 0, and there are 1 supplied.

[burnt turret]

you shouldn't have to put quotes around the ?

what is the type of the submission_id column in your table?

[low iron]

i thought i woudl because it's a string

[low iron]

text

i tried executing the same statement but with a direct value using a database visualiser and it worked

[burnt turret]

That's...odd

I don't see why that shouldn't work; the syntax is right

[low iron]

hmm
i'll try it in my python code just using a value and see what happens
thanks anyway

[burnt turret]

You're sure the submission id you're passing in is a string?

[low iron]

yep
it doesn't run either way, it must be some other problem
i'll look into that, thanks though

[torn sphinx]

Hey,
I am doing this course to learn Pandas and I am stuck trying to select a column and storing it as a variable. This is what I have wrote for it and the error message I am getting back. (apologies if this i the wrong chat for it - can't find a Pandas channel)

I'm writing it the same exact way as the example I was given and the column name is written the exact same

[quartz geode]

Is there any formula for calculating the optimal batch size while inserting the values into table using JDBC.?

[jaunty maple]

Some dude wrote this code 😂

con.query(SELECT * FROM xp WHERE id = '${target.id}')

this is literally so vulnerable

ima go ahead and do that rn

[torn sphinx]

#data-science-and-ml would be a better channel, maybe wrong variable name (data != Data)?

[civic jacinth]

anyone here knows pyrebase ?

how do i get UID

[river sedge]

that's odd. try to run everything from the beginning. also change the name of the variable data

[foggy quarry]

How can i store user id from api

I ve declared user id in my account table

Is it ok

[river sedge]

can you give more details? Generally speaking, you could simply store in a variable only the user id from json return , and then insert it on the database

[torn sphinx]

how do I select stuff from rows again?

SELECT FROM

WHERE

[gaunt sinew]

just got started with MySQL and just wanted to share here (and write it down if I need it later) that this really helped me, it looks a lot less scary from this tutorial https://www.elated.com/mysql-for-absolute-beginners/

the one thing is he says cd c:xamppmysqlbin but it needs to be cd c://xampp/mysql/bin

[foggy iron]

hello someone here use seaborn?

[keen spoke]

wrong channel

[foggy iron]

seaborn is not about databases?

[keen spoke]

@foggy iron no it’s a data visualization library

Like graphs and stuff

[foggy quarry]

How can i set my foreign key to prevent integrity error

[pulsar kestrel]

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

this is my code: row = DBManager().query("SELECT Email, Password, PASSWORD('%s') from register WHERE Email='%s'" % (pwd_escaped, email_escaped))

[foggy quarry]

Can you show your real query

[pulsar kestrel]

@foggy quarry https://paste.pythondiscord.com/uqanaqekiq.less

[quartz moon]

Hello I have a document that contains a field of type NumberLong; "guild" : NumberLong("6674875374525241"), I check its type and it turns out that it is an object:
typeof db.users.findOne().guild
object
The thing is that I'm trying to find by field and I cannot obtain the documents with that guild_id, here is the code I'm using:

async def find_by_field(self, field, value):
        print(field) #type str stored in mongodb as str
        print(value) #type str stored in mongodb as object
        documents = await self.db.find({field: value})
        print(documents)
        return documents```

documents return empty even if there are documents that satisfy the condition

[torn sphinx]

hi how do you install postgis on postgresql

i tried to do

CREATE EXTENSION PostGIS;

but it returned

ERROR:  could not open extension control file "/Library/PostgreSQL/13/share/postgresql/extension/postgis.control": No such file or directory

how do i install postgis to my database?

[quartz moon]

Solved!

[dry patio]

How would I put a default value on my text array in postgresql?

[valid yew]

I saved my Date as a python DateTime object, but when I specify the Date column as the index.. it prints out integers... how do I convert date time object to a pandas DateTimeIndex?

         Close                Date       epoch  ...  Volume    High     Low
Date                                            ...                        
0     134.6100 2021-02-12 07:05:00  1613131500  ...   11829  134.72  134.59
1     134.5300 2021-02-12 07:10:00  1613131800  ...    6182  134.63  134.50
2     134.5900 2021-02-12 07:15:00  1613132100  ...    7473  134.62  134.55
3     134.6500 2021-02-12 07:20:00  1613132400  ...    3938  134.65  134.57
4     134.6600 2021-02-12 07:25:00  1613132700  ...    3909  134.69  134.66
...        ...                 ...         ...  ...     ...     ...     ...
1878  121.8800 2021-02-26 19:35:00  1614386100  ...   18794  121.90  121.80
1879  121.8000 2021-02-26 19:40:00  1614386400  ...   23020  121.88  121.80
1880  121.8399 2021-02-26 19:45:00  1614386700  ...    9625  121.85  121.76
1881  121.8300 2021-02-26 19:50:00  1614387000  ...    9939  121.85  121.81
1882  121.7600 2021-02-26 19:55:00  1614387300  ...   28145  121.86  121.69

[1883 rows x 8 columns]
<class 'pandas._libs.tslibs.timestamps.Timestamp'>
``` Apparently Pandas thinks its a Timestamp

[brave bridge]

Can someone recommend a textbook on relational algebra? I'm familiar with basic abstract algebra and linear algebra.

[left scaffold]

UPDATE economy SET bank + 500 WHERE userid = $1 why does + throw a syntax error?

[brave bridge]

You need to specify the column you're modifying:

UPDATE economy SET bank = bank + 500 WHERE userid = $1;

the right side is the column name, and the right side is an expression, just like in Python.

[left scaffold]

yeah I sorted it with a bit of common sense but

UPDATE economy SET bank = + 500 WHERE userid = $1 works fine

[brave bridge]

!e
@left scaffold I suppose that it's a unary +? Have you actually checked the data in the db?

print(42)
print(+42)
print(+++++++++++42)

[delicate field]

@brave bridge :white_check_mark: Your eval job has completed with return code 0.

001 | 42
002 | 42
003 | 42

[sweet oxide]

any flask db help available?

[torn sphinx]

hey can someone help?

[torn sphinx]

how does a sqlite3 table look like with python? im curious and havent tried that out yet :/

[jagged ridge]

The Python docs for sqlite3 might show you what you are looking for

[rain plank]

When you something like SELECT * FROM table it would come out as a list of Row objects

!d sqlite3.Row

[delicate field]

`sqlite3.Row`

class sqlite3.Row```
A [`Row`](#sqlite3.Row "sqlite3.Row") instance serves as a highly optimized [`row_factory`](#sqlite3.Connection.row_factory "sqlite3.Connection.row_factory") for [`Connection`](#sqlite3.Connection "sqlite3.Connection") objects. It tries to mimic a tuple in most of its features.

It supports mapping access by column name and index, iteration, representation, equality testing and [`len()`](functions.html#len "len").

If two [`Row`](#sqlite3.Row "sqlite3.Row") objects have exactly the same columns and their members are equal, they compare equal.

`keys`() This method returns a list of column names. Immediately after a query, it is the first member of each tuple in [`Cursor.description`](#sqlite3.Cursor.description "sqlite3.Cursor.description").

Changed in version 3.5: Added support of slicing.

[rain plank]

It works like a dict

[torn sphinx]

@app.route("/")
@app.route("/home")
def home():
    return render_template('Home.html')

i got problem with

def home():

def giving me red underline

is it normal >

or should i send the whole code

[soft gorge]

Is there anyway to set a variable in sqlite for both the column and as well the new value as well as the Where I am currently learning sqlite so any information would help me out a lot

@bot.command()
@log_check()
@r_check()
async def sql_edit(ctx):
    member = ctx.message.author
    x = 'apples'
    y = '25'
    c.execute('UPDATE entry SET (?) = (?) WHERE Discord_Name =?', (x, y, str(member),))

[foggy iron]

i was thinking about the differences between data in json, xml, yaml, yml, sql, excel... at the end the best is just about what u prefer?

[deep karma]

Use mariadb!

[rain plank]

I prefer postgresql

[torn sphinx]

What is cardinality for a table?

[burnt turret]

number of rows...i think

let me check

yep, cardinality is the number of rows, while degree is the number of columns

[torn sphinx]

Hmm

So the database is saying cardinlity of 6

But I have over 1000 rows

[burnt turret]

huh

how are you getting the cardinality?

uh AFinger, you can insert datetime objects into the postgres db

[autumn epoch]

You can?

[burnt turret]

there are many date types out there which you can use

https://www.postgresql.org/docs/9.1/datatype-datetime.html

pick one for your column which best matches your needs

[torn sphinx]

By inspecting index

[autumn epoch]

Ok

[burnt turret]

i don't really know, i've only come across these terms while reading 😅; maybe someone else here will know why this is happening

[halcyon fox]

ey, uhm, I got a question, how do I create an older version SQLite database for django? the current one I am using is SQLite3 with django 2.1 which causes No such table: main.auth_user_old when I try to add something to one of the tables through admin panel

[sullen token]

Anyone who knows sql pls dm me. I want to share my sql command line app made in python.

[carmine totem]

just share...

[hasty fern]

is it on github?

[delicate field]

Hey @sullen token!

It looks like you tried to attach a Python file - please use a code-pasting service such as https://paste.pythondiscord.com

[sullen token]

https://paste.pythondiscord.com/owupinotoh.py

[sullen token]

pls try running it. I made it to work like sql command line.

[sullen token]

@carmine totem

[modest pulsar]

hi, quick question, how can i fetch all values from a column with sqlite3 pls?

[burnt turret]

SELECT column FROM table; would be the query you'd use

[sick perch]

What do you use them for Sqlite may be able to get some of the work done

[modest pulsar]

i already tried...

it returns me only the first id

[burnt turret]

can you show the exact query you typed in and the table?

[modest pulsar]

ok

wait

@burnt turret

            await cursor.execute("SELECT user_id FROM rpg")
            users_id = await cursor.fetchone()

[burnt turret]

you're using cursor.fetchone

[modest pulsar]

fatchmany?

fetchmany*

[burnt turret]

i don't know, refer the aiosqlite docs

haven't used it myself

[modest pulsar]

ok thx

[raw blade]

is there a decent public repository that shows an implementation of SQLAlchemy model logic for a decent sized data footprint? larger than a breadbox so to speak

[versed geode]

are foregin keys really necessary? or will joins suffice

[sick perch]

Foreign keys help to inform the db about potentially necessary constraints and speedup

Also for consistency,

[torn sphinx]

hi can someone answer my question on stackoverflow pls

https://stackoverflow.com/questions/66425721/how-to-login-in-to-psql-with-your-admin-details

[dreamy flint]

CREATE TABLE [einardb].[user_data] (
    discord_id int,
    roblox_id int,
    is_premium boolean,
    premium_boosts int
);

I am trying to create a table but something is wrong I don't know why

einardb is the database

[sick perch]

What's the error you see

[foggy iron]

i was thinking about to create a database, and when i was thinking about how i'll write the data i was thinking how easy is to understand a yml or json file so i was asking myself, Why ppl use more sql than yml or json files?

[dreamy flint]

Syntax error

[sick perch]

@foggy iron yaml/json files are useless once you go into modifying data and doing it for multiple users at multiple records + wanting to query them fast

databases are very good at speed, consistency and concurrent usage

most people absolutely and invariably mess those bits completely up when they self roll

[foggy iron]

hmmm

[sick perch]

@dreamy flint without more details, all you do is version of "it doesn't work" you didn't even tell what database you use

[foggy iron]

so the databases are the best on everything about speed or only to update the data? cause i care more about the speed to query

[sick perch]

"best in everything" is hardly right - but its fair to say that databases will beat most self rolled json/yaml file databases by a large margin at all the issues

[foggy iron]

i have a database with 8k lines in a txt file where i do a lot of query's, do u believe if i convert all the data to a sql and the query's from the sql i'll get a response faster than +20% of the speed using a txt file?

[dreamy flint]

MySQl

This was the error

Error Code: 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 '[einardb].[user_data] (     discord_id int,     roblox_id int,     is_premium bo' at line 1

[novel oak]

Hi i come from discord.py channel because they recomend me to use sql for the DB of my discord bot, is there like a library for using sql in python os simply can us it?

[sick perch]

@foggy iron what exactly is that text file doing/containing? depending on what you do it may be fine to just put it into a python data structure and not brother with databases to begin with

(8k elements is a very small amount of rows, depending on use-cases any type of database layer is slower than what you can do in memory there)

[novel oak]

?

[sick perch]

@dreamy flint and for what database is the script, as far as i recall thats not valid for mysql

[dreamy flint]

Ronny

oh

[dreamy flint]

I just want to create a table named that with those rows

[novel oak]

Is the same sql and mysql? and can use sql in python or only mysql??

[foggy iron]

well, its a bunch of string, its about columns of id's and name of items in different languages

[dreamy flint]

I am using MySQL Workbench

[foggy iron]

and the other data is smaller than this one, its about data from servers of discords

so the query take the name of the item and check the id

i was thinking if i change all the data to sql i could had a huge speed improvement

but i dont understand too much about sql, and i understand about yml and json

[dreamy flint]

@sick perch

[sick perch]

@foggy iron can you show a example or code?

[foggy iron]

ofc

1sec

[sick perch]

@dreamy flint im not familiar with the tools (i intensely avoid mysql due to its quirks that enable data loss accidents)

[novel oak]

Can any one answere?

Is the same sql and mysql? and can use sql in python or only mysql??

[dreamy flint]

What should I do instead?

What Database should I do? I have a server farm

So I am able to host my own database

But if I should use a VPS with included database please tell

[sick perch]

in general i have made very good experiences with postgresql on performance and correctness

@novel oak i cant understand your question, it doesnt make sense from my point of view

[dreamy flint]

hmm

[haughty wigeon]

Can this server be used as a replacement for stack over flow

like what's the protocol man

[novel oak]

you make a question or expose your problem an othe people try to help you

[haughty wigeon]

exposé

[novel oak]

here you can interact and they can explain you, in S.O. only can try to undestand and copy paste code

[sick perch]

@haughty wigeon the format is different

[novel oak]

ile i mean

i am new

[sick perch]

im starting to wonder iif some of the convos here should move to a help channel

[novel oak]

and want to know the differences between the different sql's

[haughty wigeon]

i aint paying you to wonder

get pack to work

[novel oak]

and what is better for discord.py

[sick perch]

@novel oak its entirely possible to use sql from python - python even has tools to abstract different sql databases, so its absolutely not clear what you ask for

[novel oak]

or for python in general

[novel oak]

ok

knwo i have understand

[foggy iron]

df = pd.read_csv('datinha.txt', error_bad_lines=False)

busca = numpy.where(df[select_language[6]].str.find(item) > -1)
busca_lista = []
for subl in busca:
    for i in subl:
        busca_lista.append(i)

id_name = [df.at[i, 'UniqueName'][3:] for i in busca_lista]

thats the query @sick perch
and the print bellow is the data called 'datinha.txt'

[novel oak]

last question, if I host a discord.py bot, and use a sql DB instead of a json file the host should include at least a DB or i can created when hired? @sick perch

[sick perch]

@novel oak typically databases are created, managed, maintained and migrated (as you will add new fields/tables when adding new features)

@foggy iron so you have a table of something like IDNR,CODENAME,LANGUAGE_A,LAnguage..

the end result data format is not clear as im unfamiliar with numpy/pandas

[foggy iron]

ye

its a table where i check a column for a string, if he find it so he take the index of the column and with the index he check the column who say the id string of the index

[sick perch]

what data, what column, whats the specifics, i have no idea whats the goal of the code in detail, i just see some data collecting

is it something like given a unique name and a language code, return the matching translation?

and is there any update/change functionality

[foggy iron]

its jsut a data collecting, dont has any update about this data
the server select a language, for example: EN-US, so when he try to find a item name like "Carrots" he try to find it on the EN-US column, if he find it so he say he take the index he found it and look at the id_name column with the same index, thats how he find the id_name of the carrots, the id gonna be unique for all languages and use in a API

but the other data i'll create soon i'll need to update it some times, cause its where i'll put the info like: Server Python language=EN-US

[sick perch]

so you want to look for a general id given a specific language and a translation in that language?

[foggy iron]

so the bot will take info from 2 datas, he will check 1 first to know what language he will select to do the query and after it he gonna query it on the datinha.txt

[sick perch]

do you load the text file fresh every time you query?

[foggy iron]

ye, but i dont translate anything

[foggy iron]

idk if he load the txt file, so idk if it take a time or how much it take

[sick perch]

a database will likely be faster, in particular if the querying is done a bit smart

will the number of rows growß

[foggy iron]

from the datinnha.txt gonna be almost the same all the time, the only data who ganna change is the info of the servers about what selected languages they have, cause it gonna be like a user database

how much faster u think it can be? in %

[sick perch]

id be pulling numbers out of thin air if i made guesses

[foggy iron]

cause if only be 10% faster i dont think it gonna be worth for me start to learn it

[sick perch]

@foggy iron is it fast enough right now?

@foggy iron and do you expect much data addition?

[foggy iron]

i dont expect more data addition, the speed is not bad but i was thinking if could be 40% faster

[tender jasper]

Hey guys, can someone tell my why this is not working? im getting the following error -
IndexError: list assignment index out of range
Code -

        conn = sqlite3.connect('attendance_mgmt.db')
        c = conn.cursor()
        att = tk.Tk()
        c.execute('SELECT student_name FROM student')
        studentnames = c.fetchall()
        i = 0
        j = 0
        print(studentnames)
        att_values = []
        l = len(att_values)
        for students in studentnames:
                for j in range(len(students)):
                        tk.Label(att, text= students).grid(row = i, column = 0)
                        att_values[i] = tk.Entry(att)
                        att_values[i].grid(row = i, column = 1)
                i = i+1
        tk.Button(att, text = "Submit", command = lambda: SubmitAtt()).grid(row = 0, column = 3)
        conn.commit()
        conn.close()```
Im trying to store the user input from `tk.Entry`into `att_values`, however the number of names can keep increasing which is why i need to use a list
Am i implementing this correctly?
I basically need to have a variable number of variables, and i thought using a list would work best, but idk why its not working

[sick perch]

@foggy iron you may be able to get it much faster by processing it once to a set of dictionaries,

@tender jasper lists donr magically make place for new items, use add_values.append instead of att_values[i] =

[tender jasper]

Will that work with a tk.Entry widget? As It doesn't have a value until the user inputs something. If i were to implement it, would it look like this att_values.append(tk.Entry(att)) . I am a beginner to python and still not as familiar with the syntax. Thanks for the help!

[sick perch]

@tender jasper you cannot put a item into a "list" when there is no spot for the item

you start with add_values having zero lenght

so putting in elements at any place is going to complain

[foggy iron]

well... i'll try it later

[tender jasper]

Okay, so once i append the value it should work right? As to my knowledge, appending a value just adds it to the end of a list

[foggy iron]

but how i'll format from json to dictionaries?

and it really gonna be faster? looks the opposite for me, idk

[sick perch]

@foggy iron switching from a value based search that cant optimize to a O(1) key based lookup is a change thats typically notable

one thing to keep in mind however is that if you put the cost for the prepare in for every request, its going to backfire

the idea is to prepare the data beforehand in a way that makes lookup fast

the lookup would turn into something like uid = TRANSLATIONS_TO_UID[CURRENT_LANGUAGE].get(input_text)

[foggy iron]

so are u talking about i do a dic like: id : [EN-USstring, PT-BRstring, ES-ESstring...] so he will look at only 1 column and check the id, like it?

i got the part of change the search to a binary key, but i dont got the part of prepare the data, cause for me the data look already prepared and the code only do a search about strings, so i'm trying to understand how do u think the dic format should be, i'm a begginer so i dont have too much experience about dic, i used some but not on a 8k lines of data

[sick perch]

Unfortunately on mobile now, the nested dictionary stuff would indeed need a coded up example to make sense

But what it would do is replace linear search with hash table lookup

[robust lion]

Hey does anyone have any idea where to start setting up a database with python? I've used MS access, but I'd like to try making a python database with a nice GUI for checking out books, and an email notifyer

[sick perch]

As Web App or as native app

[robust lion]

idk the difference, but I only need it to run on one windows device

[river field]

Can anyone familiar with SQLAlchemy have a look at an issue regarding SQLite and the asyncio extension please?

https://stackoverflow.com/questions/66431083/using-asyncio-extension-with-sqlite-backend-broken-by-version-upgrade

[toxic flower]

Hello, I have a couple questions on panda dataframes
Can anyone help me with that?

[harsh pulsar]

@toxic flower don't "ask to ask", just ask your question. but maybe you want to ask in #data-science-and-ml instead, or a help channel.

nvm, i see you already asked in data science

[foggy iron]

i got it, idk how to format a 8k lines data to be a single dict but i'll do some searchs and see what i find

gonna try it and compare the difference of speeds

[foggy iron]

ok now i have the entire data in a dict with 1 line . _.

[torn sphinx]

i got prob with colon any fix ?

cant figure it out so any suggest ?

[foggy iron]

it should be a dict?

[torn sphinx]

im taking lesson from yt

[foggy iron]

well the {} looks wrong for me on my oppinion it should be the opposite

[opaque rose]

I have a website that returns a dictionary, what should I use to get information from that website into my code?

[foggy iron]

u should have something like post = {[author....], [author....]}

[foggy iron]

request, import request and google about how to use it to parse data from a website

[torn sphinx]

oh

[opaque rose]

Okay. I appreciate the help

[foggy iron]

anyway u guys should use the help channels for these things, here is for databases discussions . -.

[torn sphinx]

i cant find it

[torn sphinx]

its the first colon problem the second one is okay

so its the {} i think

oh nvm it was the , on first content

i didnt put it silly me

@foggy iron sorry to bother

[foggy iron]

dont worrie xD its all working now?

[torn sphinx]

yeah

[safe sun]

yall know blockchain?

[torn sphinx]

hey i got problem with jinja2.exceptions.TemplateSyntaxError

any help i think its my files

but idk how to rearrange files

[solid sierra]

Help

[slender atlas]

you forgot a comma on line ten

[torn sphinx]

Dw i solved it lad

[torn sphinx]

I have a row with the a column who has value 2021-03-02 09:00:00
and i did SELECT * from sales where ydate1 > date('now');
for me the current time is 12:53pm but above query is still showing the row which is in the past 9am.

[pulsar kestrel]

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

can someone help me

[sick perch]

@pulsar kestrel yikes, that looks like a really broken db abstraction, it should be using bind parameters instead of string formatting/escaping

[pulsar kestrel]

what do you mean @sick perch

[sick perch]

the code you posted uses a number of known worst practices (one of which is using string escaping for sql parameters)

[pulsar kestrel]

oh ok

is string escaping bad for mysql? @sick perch

[sick perch]

string escaping should never be used for non-trusted data, instead sql bind parameters should be, any normal db abstraction has support for those - did you self-roll the db management?

[pulsar kestrel]

yeah

[tacit marsh]

Hi sorry to break into this conversation , but please what are your reasons for that? escaping a string special characters is another way to prevent SQL injections or one can use bind parameters, but even then , I still use string escape along side binding parameters one cannot be too careful for security. But I will love to learn more about this?

[sick perch]

simple - if you move parameter management structurally to a database api, then it can prevent you from forgetting that one esscape thats the next injection

[torn sphinx]

but then you have to worry about different character sets when you escape it @tacit marsh

[sick perch]

bascially - manual escape is just asking for the next human error to happen, and those are hard to spot and have many different vectors of attack

[torn sphinx]

better to just use parameter binding because in most libraries it willl also handle conversion of type for you as well

[torn sphinx]

anyone know why this happens

[sick perch]

what time is it on the db server?

[torn sphinx]

same time as my pc

[sick perch]

then at first glance it doesnt make senes, what does selecting of now return?

[torn sphinx]

the date

do you think its because it doesnt return the time?

[sick perch]

thats possible, check out how your db compares dates and datetimes, i havent dealt with the details of that in ages (i always only use utc timestamps these days for structural reasons)

[torn sphinx]

ye i should go to check this then

it was datetime i had to use

instead of date

[past aspen]

Windows PowerShell
Copyright (C) Microsoft Corporation. All rights reserved.
Try the new cross-platform PowerShell https://aka.ms/pscore6

PS C:\Users\DELL\Desktop\Mage>  & 'C:\Users\DELL\AppData\Local\Programs\Python\Python39\python.exe' 'c:\Users\DELL\.vscode\extensions\ms-python.python-2021.2.582707922\pythonFiles\lib\python\debugpy\launcher' '54044' '--' 'c:\Users\DELL\Desktop\Mage\bot\_main.py' 
Traceback (most recent call last):
  File "C:\Users\DELL\AppData\Local\Programs\Python\Python39\lib\runpy.py", line 197, in _run_module_as_main
    return _run_code(code, main_globals, None,
  File "C:\Users\DELL\AppData\Local\Programs\Python\Python39\lib\runpy.py", line 87, in _run_code
    exec(code, run_globals)
  File "c:\Users\DELL\.vscode\extensions\ms-python.python-2021.2.582707922\pythonFiles\lib\python\debugpy\__main__.py", line 45, in <module>
    cli.main()
  File "c:\Users\DELL\.vscode\extensions\ms-python.python-2021.2.582707922\pythonFiles\lib\python\debugpy/..\debugpy\server\cli.py", line 444, in main
    run()
  File "c:\Users\DELL\.vscode\extensions\ms-python.python-2021.2.582707922\pythonFiles\lib\python\debugpy/..\debugpy\server\cli.py", line 285, in run_file
    runpy.run_path(target_as_str, run_name=compat.force_str("__main__"))
  File "C:\Users\DELL\AppData\Local\Programs\Python\Python39\lib\runpy.py", line 267, in run_path
    code, fname = _get_code_from_file(run_name, path_name)
  File "C:\Users\DELL\AppData\Local\Programs\Python\Python39\lib\runpy.py", line 242, in _get_code_from_file
    code = compile(f.read(), fname, 'exec')
  File "c:\Users\DELL\Desktop\Mage\bot\_main.py", line 23
    CREATE TABLE IF NOT EXISTS  main(
           ^
SyntaxError: invalid syntax
PS C:\Users\DELL\Desktop\Mage> ```

hmmm

i have built a SQLITE3 table

    CREATE TABLE IF NOT EXISTS  main(
        guild_id TEXT,
        msg TEXT,
        channel_id TEXT
        )```

[pulsar kestrel]

@sick perch i found the problem. this PASSWORD() doesn't exists in my mysql version

[sick perch]

That is not valid python, put it in the multiline string

[past aspen]

i figured that out you were a little lat ebut any way

thx

:>

[torn sphinx]

anyone know a good tutorial for mongodb databases for discord bot?

[rapid swift]

I was wondering is DB browser for SQLITE safe? https://github.com/sqlitebrowser/sqlitebrowser/releases/tag/v3.11.0-beta3
My computer is warning me about it so I really don't know

[hazy smelt]

You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ''Guess' set 'Wins'='Wins'+162 where User_ID=682134271858573329' at line 1

async def update(ctx, table, column, new_val, member:discord.Member):
    if ctx.author.id==571299307052072980:
        upd=[]
        upd.append(str(table))
        upd.append(str(column))
        upd.append(str(column))
        upd.append(int(new_val))
        upd.append(member.id)
        lol=tuple(upd,)
        mycursor.execute("Update %s set %s=%s+%s where User_ID=%s", lol)
        mydb.commit()

anyone knows the problem?

[burnt turret]

the table name isn't meant to be passed in that way

because you're using parametrized queries, the driver module automatically quotes the things you're passing in, which means even the table name is being quoted

[hazy smelt]

Ok

I c thx

[torn sphinx]

Easy way to prevent sql input in a python file that uses raw userinput?

Please ping me if you answer (im coding and dont always check the channel)

[burnt turret]

@torn sphinx are you thinking of parametrized queries?

[torn sphinx]

i am talking user input (from a discord bot) and using that in my sql query. However there is no sql prevention, so doing !member "; * will brake it

[burnt turret]

yes you need parametrized queries in this case

what database driver module are you using? @torn sphinx

[torn sphinx]

Um

[torn sphinx]

I did not write anything yet. I first wanma know how i prevent someone from deleteing my db before i start coding

[burnt turret]

well, then "parametrized queries" are what you should be looking up. the third pinned message in this channel explains the idea.

the process is slightly different for different db driver modules.

[torn sphinx]

okay i will look into it

thank you

[broken chasm]

Hey guys, coming from about 5 years of dev mixed with (Node, Ruby/Rails) exposure. Been learning Python for about a week. Any suggestions on some of the more common ORM's used in the Python ecosystem? I've played w/ & have been using SQLAlchemy . Also, any suggestions on what libraries are used for managing DB Migrations?

[burnt turret]

SQLAlchemy is probably the most popular python ORM out there

alembic is what people use for migrations i think

!pypi alembic

[delicate field]

alembic v1.5.5

Author

Mike Bayer

Requires Python

!=3.0.*,!=3.1.*,!=3.2.*,!=3.3.*,!=3.4.*,!=3.5.*,>=2.7

Summary

A database migration tool for SQLAlchemy.

License

MIT

[broken chasm]

Cool. These are the two exact libraries I was planning on using for my first project. Seems to be good choices.

[signal oxide]

Hi, I have the postgres table:

id int primary key,
character_id int references characters(id),
skill_one_name text,
skill_one_level int, 
skill_two_name text,
skill_two_level int, 
skill_three_name text,
skill_three_level int

How would I get the count of the skill names from all three columns? If it was one I could just group by. How do I do it with three?

Or is there a better way to do it? Each will always have three skills

[torn sphinx]

hi i have a question on databases specifally postgresql

so i want to install postgis to my database in postgresql but when it try :

CREATE EXTENSION postgis;

it gives an error of

ERROR:  could not open extension control file "/Library/PostgreSQL/13/share/postgresql/extension/postgis.control": No such file or directory

the account i was on was the default postgres one
i had tried to reinstall postgres after i deleted the app and postgresql
then reinstalled it

how can i install postgis without it giving an error?

[waxen lagoon]

Hello, had a SQL question:

    sql_create = text('''CREATE TABLE temp_table AS
                         SELECT station_id, AVG(in_communication) as in_communication_pct
                         FROM timechecks
                         GROUP BY station_id;''')

    sql_update = text('''UPDATE 
                               timechecks
                         SET in_communication_pct = (SELECT in_communication_pct
                                                        FROM temp_table
                                                        WHERE station_id = timechecks.station_id) 
                         WHERE EXISTS (SELECT in_communication_pct
                                        FROM timechecks
                                        WHERE in_communication_pct IS NULL)
                         AND EXISTS (SELECT in_communication_pct
                                       FROM temp_table
                                       WHERE station_id = timechecks.station_id);''')

So I have a temp table (temp_table) where I'm calculating the average based on the station_id, then I want to insert that value in a perm table (timechecks). The timechecks table has many rows per station_id to calculate the average, but I want to insert the newly calculated in_communication_pct into only the newest rows which should be NULL and then run this update after they are inserted.

Unfortunately this is still updating the entire column of in_communication_pct, it is still based on the station_id it is just overwriting all of them. Any suggestions? No errors are being thrown.

[torn sphinx]

hi

The use of pools could improve the performance of the database?

[broken chasm]

Anyone can feel free to correct me if I'm wrong, but the short answer to your question is yes, pools can improve your performance. It is important to understand why though, and that is simply because connection pooling means that connections are reused rather than created each time a connection is requested.

Here is a video I recently watched where she goes over how/why to use connection pools with SQLAlchemy: https://youtu.be/36yw8VC3KU8?t=839

Also, another article explaining the how/why connection pools could benefit your application: https://pynative.com/python-database-connection-pooling-with-mysql/

[manic light]

working with sqlite3/python while making a practice db program . does it make sense to have class instances representing people and their attributes when the data gets saved in a sql DB at the end? is it just extra work or is this common practice?

[sick perch]

Depends on the use case, these days people tend to use orms to map rows to objects

[manic light]

Interesting! I am currently working out the schema in sql and was going to mirror that in my python program as well, the long term goal was to add a gui. So i guess having objects in python would help with that. I'll keep reading up on that for now. Some people said to avoid orms and just learn sql instead as its more versatile, any thoughts on that?

[foggy juniper]

Ignoring exception in command give:
Traceback (most recent call last):
  File "/home/pi/.local/lib/python3.8/site-packages/discord/ext/commands/core.py", line 85, in wrapped
    ret = await coro(*args, **kwargs)
  File "/home/pi/Documents/Central_Communications/cogs/central-economy.py", line 87, in _give
    await db.execute(f"UPDATE economy SET money = {currentbalance + amount} WHERE user_id = {member.id}")
TypeError: can only concatenate tuple (not "str") to tuple```
```py
@commands.command(name='give')
@commands.has_permissions(manage_guild=True)
async def _give(self, ctx, amount, member: discord.Member = None):
    if not member:  # if member is no mentioned
        member = ctx.author  # set member as the author
        
    async with aiosqlite.connect('/home/pi/Documents/Central_Communications/database/economy.db') as db:
        cursor = await db.execute(f"SELECT money FROM economy WHERE user_id = {ctx.author.id}")
        currentbalance = await cursor.fetchone()
        await db.execute(f"UPDATE economy SET money = {currentbalance + amount} WHERE user_id = {member.id}")
        print(f'Gave {member} {amount} K-Bucks')
        await db.commit()
        print('Saved!')
        pass
    embed=discord.Embed(
            type='rich',
            colour=discord.Color.dark_green(),
            description=f'{ctx.author.mention}: Gave {amount} K-Bucks to {member.mention}!'
            )
    embed.set_author(name=ctx.author, icon_url=str(ctx.author.avatar_url))
    embed.set_footer(text="Developed by LIPD Productions Inc.#1205", icon_url=str(ctx.guild.icon_url))
    embed.timestamp=datetime.datetime.utcnow()
    await ctx.send(embed=embed)
    pass```
How do I get the new balance into a tuple so SQL will be happy with it?

[pure mortar]

use tuple() ?

db.execute(f"UPDATE economy SET money = {currentbalance + amount} WHERE user_id = {member.id}")

looks like its this line

or maybe you can split them up and just do string concatenation

that seems simpler than changing data types since it looks like db.execute only accepts str types anyways

i actually dont know if thats true since i havent used that db

[foggy juniper]

Still sees it as a str

[upper juniper]

Dont use fstrings in sql lines

Use the execute api properly, pass the values as a tuple

[pure mortar]

that sounds more reasonable

[raven coral]

Hlo can anyone help?

Insert and Ignore not work in MySql

[torn sphinx]

help me in #

help carbon channel

cant tag it idk why

[burnt turret]

show your code and the result

[raven coral]

This is

    @commands.Cog.listener()
    async def on_message(self, message):
        if message.author.bot is False and message.guild is None:
            return
        cursor = db.cursor()
        cursor.execute("INSERT OR IGNORE INTO levelling (user_id, guild_id, xp) VALUES (%s,%s,%s)", (message.author.id, message.guild.id, 1))

        if cursor.rowcount == 0:
            cursor.execute("UPDATE levelling SET xp = xp + 5 WHERE user_id=%s AND guild_id=%s", (message.author.id, message.guild.id))
            cur = await cursor.execute("SELECT xp FROM levelling WHERE user_id=%s AND guild_id=%s", (message.author.id, message.guild.id))
            data = cur.fetchone()
            xp = data[0]
            level = math.sqrt(xp) / self.client.multiplier

            if level.is_integer(): 
                await message.channel.send(f"Congragulations! {message.author.mention} You are now at level: {int(level)}")

        db.commit()

[burnt turret]

https://www.mysqltutorial.org/mysql-insert-ignore/

it's INSERT IGNORE ...

there's no OR in between it by the looks of it

[raven coral]

Oih

Ooh

[raven coral]

Not working

Stored same data multiple times

Is i have to set to Primary key?

Or unique

[burnt turret]

you need to set constraints on the columns then obviously

in INSERT IGNORE, the IGNORE is just ignoring whatever error will be raised by the constraint violation

[raven coral]

Now it throws error none type of object has attribute of fetchone()

[burnt turret]

that's not related to your initial question in any way

cur is None there

your code seems very....inconsistent

you're awaiting some executes but not others

[raven coral]

Is it ok now?

    @commands.Cog.listener()
    async def on_message(self, message):
        if message.author.bot is False and message.guild is None:
            return
        cursor = db.cursor()
        cursor.execute("INSERT IGNORE INTO levelling (user_id, guild_id, xp) VALUES (%s,%s,%s)", (message.author.id, message.guild.id, 1))

        if cursor.rowcount == 0:
            cursor.execute("UPDATE levelling SET xp = xp + 5 WHERE user_id=%s AND guild_id=%s", (message.author.id, message.guild.id))
            cur = cursor.execute("SELECT xp FROM levelling WHERE user_id=%s AND guild_id=%s", (message.author.id, message.guild.id))
            data = cur.fetchone()
            xp = data[0]
            level = math.sqrt(xp) / self.client.multiplier

            if level.is_integer(): 
                await message.channel.send(f"Congragulations! {message.author.mention} You are now at level: {int(level)}")

        db.commit()

[vocal flint]

ok

[solemn root]

You would use dot notation and call it by creating a connection to the database.
db = sqlite3.connect("mydata.db")

[vocal flint]

oh so you use a File?

[solemn root]

yes, it creates a file

[vocal flint]

Oh

So

if I want to make a stored backup

How would it be done?

my backupdata.db?

[solemn root]

I myself am still learning how to create a backup file and auto-push data to the backup. But I believe it isn't much more than opening the backup after closing the main database file, then updating the backup with the contents of the main database file.

[vocal flint]

oh ok]

[sick perch]

The answer is "depends" these days orms, in particular sqlalchemy are pretty good + have neat lower level tools that protect you from quite a lot of classical mistakes, so I think it's fair to say that using them typically is a good idea unless you have a surrounding toolset/required that makes it difficult

[polar osprey]

Hey guys
I want to populate a Treeview with Information from a Database. I have two MySQL Tables: Materials and Lineage. In Materials I have all the material and in Lineage the hierarchy is stored (Parent, Child and Generation). For simplicity you can also think of folder structure with subfolders.

So I see basically two ways of going about this:
a.) I can SELECT all entries from Lineage and then iterate trough the generations and for each item, I'll do a select from the Materials table for the information regarding the item (name, id, etc.)
b.) I can do one SELECT for everything from Lineage and another SELECT for everything from Materials. Then I'll iterate through the generations again but I'll look for the item information (name, id etc.) in the array containing the Materials information.

What's better?

[sick perch]

depending on the data, a join may be a good idea - how many elements are there to begin with tho?

[polar osprey]

at the start not so many but eventually there will be a few hundred

I was thinking about a join but I haven't worked with MySQL in a while and I wasn't sure how that would work since I have way more entries in the Lineage table. Because one Material can have multiple children and something can be a child of multiple parents.

[sick perch]

i see, don't think i have enough information to give a good hint (in particular if the data is more like a graph than a tree)

[pale oriole]

hello hello
does anyone know a tool that can help with finding a path from a table to another table in the database?
What is mean is, i have a database with more than 50 tables. and i need to find how to get from one table to another with joins. and even though i have an erd with 50 tables its really hard to find a path.

[torn sphinx]

What you mean find path? The erd tells you this no? What easier way do you want?

Even without the erd the foreign keys should tell you

[pale oriole]

lets say you need to do 6 joins to get from one table to another
the erd i have is extremly complicated and an erd shows u the connection from one table to whatever connect to it in one join (have a foreign key of his primary one usually)
i am asking if there's some kind of a tool/software that shows all the tables i need to go through in order to get to the other table.

[torn sphinx]

yeah for that i am not sure which software does this

but i dont see why its so much extra effort to just look at the erd diagram and see where the lines go?

just follow the line in the diagram and it tells you

[sick perch]

i beleive there is tools that show you uml diagrams of database tables, one of those should be a starting point

[novel oak]

Wheres the problem here?

UserXp = SELECT XP FROM UsersDiscordIceEmpire WHERE ID = 1;

the error

File "d:\Python\Scripts\DiscordBots\IceBot\Pruebas.py", line 31
    UserXp = SELECT XP FROM UsersDiscordIceEmpire WHERE ID = 1;
                    ^
SyntaxError: invalid syntax

[burnt turret]

the sql query needs to be a string in your python code

wrap it in quotes

[novel oak]

ok thx

[novel oak]

if i do this

UserXp = c.execute('SELECT XP FROM UsersDiscordIceEmpire WHERE ID = 1;')

the when i try to print it, it returns this

<sqlite3.Cursor object at 0x00000255697AF7A0>

[novel oak]

and if I symply wrap it in quotes then qhen i try to print it returns the str no the value extracted form the DB

UserXp = 'SELECT XP FROM UsersDiscordIceEmpire WHERE ID = 1;'

[torn sphinx]

you have to do fetch

which are you using module? sqlite?

[novel oak]

sqlite3

[novel oak]

i am learning whats fetch?

[torn sphinx]

ok so can do results = UserXP.fetchone()
it will fetch the data

there is also fetchall()

[novel oak]

ok

[torn sphinx]

fetchone() - get one row from query
fetchall() - get all rows from query

[novel oak]

returns error

<sqlite3.Cursor object at 0x000001CF59EEF7A0>
Traceback (most recent call last):
  File "d:\Python\Scripts\DiscordBots\IceBot\Pruebas.py", line 34, in <module>
    results = UserXP.fetchone()
NameError: name 'UserXP' is not defined

what am i doing wrong?

[torn sphinx]

you can show your code

for that function

[novel oak]

DB = sqlite3.connect('D:\\Python\\Scripts\\DiscordBots\\IceBot\\UsersIceEmpire.db')

c = DB.cursor()

UserXp = c.execute('SELECT XP FROM UsersDiscordIceEmpire WHERE ID = 1;')

print(UserXp)
results = UserXP.fetchone()```

simply that

i am only trying to do what what I learned

[torn sphinx]

UserXp != UserXP

You have capital P in second

[novel oak]

ok yes, i am stupid

@torn sphinx now returns None

[torn sphinx]

then the meaning of this is that there is no match found in the database for this query

[torn sphinx]

hey,
I'm using matplolib for my program
I have no error but the graph is not displayed

import numpy as np
import matplotlib.pyplot as plt
x = np.linspace(0, 2, 10)
y = x **2
print(x)

plt.plot(x, y)```

please
what is the command to open a help lounge please

[pale oriole]

if i could i would've send u a picture of the diagram but i can't cause funny enough its classified lol
but imagine a maze. and not the kind u can see from above but you're in the maze. that's the erd

[novel oak]

why the DB insterprets that a 0 is a tuple?

File "C:\Users\Jose Manuel\AppData\Local\Programs\Python\Python39\lib\site-packages\discord\client.py", line 343, in _run_event
    await coro(*args, **kwargs)
  File "d:\Python\Scripts\DiscordBots\IceBot\main.py", line 165, in ON_MESSAGE
    UpdatedUsersJS = int(UserXp + 0.1)
TypeError: can only concatenate tuple (not "float") to tuple

I printed UsersXp And returnes ('0',)

why didn't just returned 0?

this is the code

DB = sqlite3.connect('D:\\Python\\Scripts\\DiscordBots\\IceBot\\UsersIceEmpire.db')
            c = DB.cursor()
            c.execute(f"SELECT XP FROM UsersDiscordIceEmpire WHERE ID = '{member.id}';")
            UserXp = c.fetchone()
            print(UserXp)
            UpdatedUsersJS = int(UserXp + 0.1)
            c.execute(f"UPDATE UsersDiscordIceEmpire SET XP = '{UpdatedUserXp}' WHERE ID = '{member.id}';")

            DB.commit()
            DB.close()

[torn sphinx]

@novel oak you have to do UserXp[0] to get the value and then you can do add 0.1

but is pointless to do int() and add 0.1 because int will convert decimal to integer

[autumn epoch]

@burnt turret How would I get the top 5 top data in a column?

[burnt turret]

what database is this

[autumn epoch]

Postgre

[burnt turret]

you add a FETCH clause to your query after ordering the data by descending order with respect to that column -

SELECT * FROM tablename ORDER BY column DESC FETCH FIRST 5 ROW ONLY;

[autumn epoch]

Ok

[autumn epoch]

Also how would I add a WHERE to that

[burnt turret]

the way you do it usually

WHERE comes before ORDER BY

[autumn epoch]

Where would it go?

Ok

[modest pulsar]

hi, quick question, how can i delete a column from a table with sqlite3?

[burnt turret]

ALTER TABLE

refer sqlite documentation for the exact syntax, but i think it should be something along the lines of

ALTER TABLE tablename DROP COLUMN columname

[modest pulsar]

ok thx

[jagged cove]

quick question guys

if i use SELECT * FROM table LIMIT 10 but there are less than 10 records in the table, what happens?

does it just return a list with as many members as are in the table?

[prisma girder]

Yes

[jagged cove]

alright, thanks

[novel oak]

the thing is that i don't know why says that is a tuple

instead of an int or a float

[slender rose]

How do we store python datetime object in postgresql

Also how do we fetch using a condition like a column has a datetime in it and i wanna fetch only those rows where the time difference between current time and that time is 12 hours

[burnt turret]

postgres has datetime data types, pick one which matches your needs https://www.postgresql.org/docs/9.1/datatype-datetime.html

[slender rose]

Thanks

Any idea about my second problem?

[burnt turret]

i'm thinking

you should be able to just subtract the datetime objects, but i'm not very sure myself about the "checking whether it is greater than 12 hours" part

[slender rose]

Oh

[burnt turret]

i think subtracting two DATE objects will give you an INTERVAL object (refer the link I sent to see what these types are)
and then you can check if the resulting INTERVAL is equal to an INTERVAL(H=12) or something

https://www.postgresql.org/docs/9.1/functions-datetime.html this describes how adding/subtracting different datetime objects behave
and the earlier link will tell you about the types

[jagged cove]

how do i get someone's position in an ordered table? I need a specific entry by id's position if the table was ordered by points for leaderboard functions

[proven arrow]

You can use the ROW_NUMBER() window function

[proven arrow]

The python module represents the database Rows as a tuple, where each element of the tuple is a column in your row.

So you would have to extract the value from this tuple as they showed you.

[novel oak]

ahhh ok now i undestanded

[white steppe]

emmmwhy I get the error ?

it worked like 2 days before :/

[left scaffold]

Hi so this query SELECT * FROM leave WHERE userid = $1, ctx.author.id is then used in a if statement but I need to do (fetch[6]['message']) as it returns a list but it says 6 is out of range throwing an index error even tho i'm sure it isnt

[white steppe]

mydb = mysql.connector.connect(
  host="nope",
  user="nope",
  password="nope",
  database="nope"
)
mycursor = mydb.cursor()

mycursor.execute("SELECT * FROM datatable")

myresult = mycursor.fetchone()

print(myresult)```

[burnt turret]

print the result and see if that many rows actually are being fetched

[left scaffold]

ok

[burnt turret]

do you have a question?

[white steppe]

kindof, this is code for this

error

I just dont' know why this happened

[burnt turret]

don't name your file mysql.py

it's conflicting with the actual package

[white steppe]

sriously

thats why

LOL

xD

oh man

thx

🙂

[burnt turret]

👍

[left scaffold]

this is what it returns [<Record userid=691994304885030972 start=datetime.datetime(2021, 3, 3, 17, 55, 53, 136344) end=datetime.datetime(1900, 4, 4, 0, 0) reason='testidk' pending=False approved=True message=816730618879672370>] which is 6 values

[burnt turret]

that's a single row

[left scaffold]

yea

[burnt turret]

that row may have 6 values, but when you do fetch[6] it's trying to get the 7th row

[left scaffold]

yea

oh

dont you start from 0 tho

[burnt turret]

in this case you can do fetch[0]["message"]

[left scaffold]

ok

[burnt turret]

otherwise you could use fetchrow which will directly give you that record object, and then you could do fetch["message"]

[left scaffold]

ok

also why is it when I combine these 2 commands

UPDATE leave SET approved = True WHERE userid = $1, member.id
UPDATE leave SET pending = False WHERE userid = $1, member.id

UPDATE leave SET pending = False AND approved = True WHERE userid = $1, member.id

it doesn't update

[torn sphinx]

I tried setting up mongo database with local and with the url, but both times it doesn't seem to do anything

[burnt turret]

to update multiple columns, you separate them with commas

UPDATE leave SET pending = False, approved = True WHERE userid = $1

[left scaffold]

oh

thx

[sick perch]

@left scaffold also if you have a number of mutually exclusive boolean fields, chances are you want a enum field

[novel oak]

and why returns this now?

Ignoring exception in on_message
Traceback (most recent call last):
  File "C:\Users\Jose Manuel\AppData\Local\Programs\Python\Python39\lib\site-packages\discord\client.py", line 343, in _run_event
    await coro(*args, **kwargs)
  File "d:\Python\Scripts\DiscordBots\IceBot\main.py", line 164, in ON_MESSAGE
    UserXp = int(UserXP[0])
TypeError: 'NoneType' object is not subscriptable

this is the code

DB = sqlite3.connect('D:\\Python\\Scripts\\DiscordBots\\IceBot\\UsersIceEmpire.db')
            c = DB.cursor()
            c.execute(f"SELECT XP FROM UsersDiscordIceEmpire WHERE ID = '{member.id}';")
            UserXP = c.fetchone()
            UserXp = int(UserXP[0])
            UpdatedUsersJS = UserXp + 0.1
            c.execute(f"UPDATE UsersDiscordIceEmpire SET XP = '{UpdatedUserXp}' WHERE ID = '{member.id}';")

            DB.commit()
            DB.close()
    else:

[lone adder]

hey does anyone have an idea why the data is not getting updatedpy c.execute("UPDATE user SET exp = :exp WHERE guildid = :guildid AND userid = :authorid", {"exp": exp, "guildid": guild.id, "authorid": author.id}) conn.commit()

[proven arrow]

Your code is fine. If it doesn’t update then probably the values you pass don’t match any row in the database for it to update.

[proven arrow]

Your error is to do with basics of python. Your trying to index and get a value from a None type (which isn’t possible). You should have proper checks/error handling to see if the database returned any rows before trying to index it.

[lone adder]

but there is

[empty haven]

why am I getting this error? my code will be below ```py
Ignoring exception in command notes:
Traceback (most recent call last):
File "/Library/Frameworks/Python.framework/Versions/3.9/lib/python3.9/site-packages/discord/ext/commands/core.py", line 85, in wrapped
ret = await coro(*args, **kwargs)
File "/Users/8586/PycharmProjects/Magoji-Chili-Branch/cogs/moderation/moderation.py", line 240, in notes
rows = await self.bot.db.execute(
File "/Users/8586/PycharmProjects/Magoji-Chili-Branch/utilities/database.py", line 21, in execute
async with self.pool.acquire() as conn:
AttributeError: 'Database' object has no attribute 'pool'

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

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

[fallen solar]

.battleship

[empty haven]

https://paste.pythondiscord.com/pawarikaka.py

[torn sphinx]

class Database():
    client: MongoClient = None
    database: MongoDatabase = None
    users: MongoCollection = None
    general: MongoCollection = None
    messages: MongoCollection = None
    voice_time: MongoCollection = None
    counters: MongoCollection = None

    @staticmethod
    def connect(config):
        Database.client = MongoClient()
        Database.database = Database.client[config["database_name"]]

        Database.users = Database.database.users
        Database.general = Database.database.general
        Database.messages = Database.database.messages
        Database.voice_time = Database.database.voice_time
        Database.counters = Database.database.counters

can anyone explain waht the users: mongocollection = none does

[icy stream]

you're calling one of the functions before the db has been initialised

[waxen temple]

vcokltfre, ur cool

[icy stream]

[waxen temple]

ikr, what a stupid head

jk

[torn sphinx]

Wise head

[raw blade]

does a good strategy for implementing a db connector with sqlalchemy change when going from gunicorn/flask to uvicorn/fastapi? like do i need to do scoped sessions and make an async connector for my app to take full advantage?

[torn sphinx]

do I need to commit when do asyncpg.execute?

[burnt turret]

asyncpg auto-commits

so you don't have to

[torn sphinx]

ok

[dreamy flint]

I'm using PostgreSQL.
This is for discord servers. I don't know how to figure this out.
I have the server id, and this server id contains a dictionary of the role IDs with some other id as value.
What's the best way to store this JSON/Dictionary in PostgreSQL?

[burnt turret]

you'll want to convert that data into tables

can you describe the data in a little more detail?

[sick perch]

Without the models it's tricky to get a good idea

And the sort by distance function?

[sick perch]

At first glance that doesn't look like it integrates with sqlalchemy

What goes to order by needs to be something that tells sqlalchemy how to make /use the distance

What database is the backend?

With postgresql you can potentially use its geo type stuff /optimization to make it neat and effective, wit Sqlite no idea

[short flicker]

Anyone had to work through data validation/testing in python.

JSON : I've used JSONSchema. It has worked well for my purposes.

What is a good option for columnar data?

• Trying to stay away from Pandas, but not a hard No
  -- Reasoning is I have millions of records to process through and I'd either crash my machine or I'd need to paginate through it all and I'm concerned of the extra time to deal with creating a DF for each grouping

Any thoughts / best practices / articles?

[torn sphinx]

  @set.command(name="theme")
    @has_permissions(administrator=True)
    async def set_theme(self,ctx: Context, theme: str):
        if theme not in self.gifs.keys():
            await ctx.send("this theme doesn't exist")
        if theme in self.gifs.keys():
            self.slap_gifs = self.gifs[theme]["slap"]
            db_themes.find_one({"guild_id": ctx.guild.id}, {"$set": {"Theme": theme}})
        await ctx.send("theme has been set to " + theme)






    @command()
    async def slap(self, ctx: Context, member : Member ):
        themes = db_themes.find_one_id(ctx.guild.id)
        print(db_themes.find_one_id(ctx.guild.id))
        if themes is None:
            self.slap_gifs = self.gifs["animals"]["slap"]
        else:
            self.slap_gifs = self.gifs[themes]["slap"]

I wanna make it so that when they set a theme it's gonna store the theme for that guild and the theme of the gifs change to the theme they set it to, I'm pretty sure I've done smth wrong but im not good with databases

anyone know how to do it right?

[burnt turret]

this is mongodb/pymongo right?

[torn sphinx]

yhh

[burnt turret]

Alright so which part doesn't work?

the queries in your first command is syntactically correct, but in your second command, find_one_id method doesn't exist

[torn sphinx]

yeah I realized

[torn sphinx]

okay so ppl can chooce a theme right so I want it to store in the database the theme they set per guild ID, and then if they set a new theme that would overwrite the old theme. and then in the 2nd I want the themes value to be the theme they set it to from the database

[burnt turret]

Okay so, your collection looks something like this:

{guild_id: 1234, theme: theme},
{guild_id: 4321, theme: another theme}

(I'm gonna answer the second question first)
To retrieve a document from the database, you'll use the find_one method, and pass it a key: value pair which can identify a document (a document refers to a dictionary from ^ the example)

a query like db_themes.find_one({"guild_id": ctx.guild.id}) will return the corresponding dictionary