databases

[proven arrow]

NoSQL databases are for when your general relational database is not suitable for your problem.

[rain plank]

I mean relational databases are easy to learn too

[burnt turret]

you're trying to use indexing on a Cursor

[torn sphinx]

So how do I do this now?

[burnt turret]

https://gist.github.com/anand2312/840aeb3e98c3d7dbb3db8b757c1a7ace#sidenote-why-arent-finds-awaited here are examples of working with Cursor objects

[torn sphinx]

I want to if bank_authorisation.find({ "_id": receiver }) == 0:...

[burnt turret]

That'll never be equal to zero

[torn sphinx]

Then what happens when there is no results?

None?

[burnt turret]

Refer the example I linked, there's a to_list method - when you use that, and there are no records, it should be giving you an empty list

[burnt turret]

iirc find_one will give you None

[torn sphinx]

Uh-huh...

[burnt turret]

find isn't find_one

[torn sphinx]

I know.

[burnt turret]

find always gives you a Cursor object, regardless of whether there were matching documents

[torn sphinx]

uh huh...

So, let me try to do this with await collection.find_one().

[burnt turret]

If you only need one document that's the way to go 🤷‍♂️

[torn sphinx]

uh huh...

@burnt turret Will if recipient["_id"] == id: work?

sorry if I'm bothering you...

[atomic talon]

i want to get "281"

[torn sphinx]

!paste

[delicate field]

Pasting large amounts of code

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

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

[jolly rivet]

hey I am new to databases ,

I want to create a database for my app

[burnt turret]

show the code before it, can't answer without seeing

[jolly rivet]

how can I do it

in what frame work

[burnt turret]

I don't mind, just ping me to get my attention and I'll reply eventually

[jolly rivet]

pls tell

anybody

[burnt turret]

Depends on what kind of data you're storing but generally a relational database will work fine for almost anything

[jolly rivet]

I just want to go with the basics

[burnt turret]

examples of popular relational databases are MySQL, Postgres

[jolly rivet]

just to learn it

[jolly rivet]

oh

[burnt turret]

SQLite is probably the easiest to set up then

[jolly rivet]

ok thanks

[burnt turret]

https://sqlbolt.com is a good resource to learn SQL

[wraith shell]

Where do people prefer to keep their “select” sql statements.
If the script is lengthy would you prefer to create a view in the database and call the view or keep the lengthy sql script in your python file or read it from your static folder.
I am concerned about the readability and managing the code base.

[proven arrow]

It’s a matter of preference for what you say.

But even if you have lengthy sql there are design patterns you can follow to abstract it further and still have the code readable/maintainable

In my case, I keep a data access layer and use a service/repository pattern to separate it

Or when I use ORMs sometimes I might add some parts directly to the model.

[wraith shell]

I was thinking of creating a single ugly py file with a dictionary to keep all the lengthy SQLs at one place. It would keep my execute functions clean with key value instead of the whole sql. Readability counts.

[wraith shell]

This is my case actually

[wraith shell]

Thanks for this. I will explore this more.

[proven arrow]

It also depends on your app as well or what your doing. For ex. if you have many applications making the same select query then sure a view/stored procedure might make better sense, to handle migrations or changes in the schema. Or using views for security reasons.

[wraith shell]

Correct. My app is actually dependent upon raw data coming from some other system as materialised views. I am creating complex joins over those MVs to make the business logic and feed it to my application.
That business logic joins and where clause conditions are in those SQLs.

I plan to feed that data to my app with a stage table in the middle. So all I have to do is

truncate table stage
insert into stg select * from view

which is much cleaner than writing the whole query there.

[torn sphinx]

any chance anyone can discord call me to help me fix an issue ive been stuck on for a week , its simple but im really stuck

[proven arrow]

What’s the question

[median swift]

Hi guys, came across the following problem with sqlite3 - My other statements seem to work except this.

ValueError: parameters are of unsupported type```

[burnt turret]

You need to put a trailing comma in the second argument to make it a tuple - (serverid,)

without this, python doesn't make it a tuple

[median swift]

all works! Thank you very much! Very new to working with databases in python 🙂

[burnt turret]

SELECT COUNT(column) FROM tablename

[weary fulcrum]

Hi

[modern flower]

I'm trying to go start a new DB but when i try to make migrations from scratch on it it keeps saying django.db.utils.ProgrammingError: relation "fighters_fighter" does not exist

[plucky mantle]

Hi, have been struggling with getting test data into Django from csvs, for weeks now 😄 I am trying to import test data into the car inspection django model, I attempted via django admin and csv import, and also via python scripts but since tables are all related, one to many, many to many, I struggle to get the test data that is also been related imported to the django model (in sqllite). Doesn't make sense!
help would be awesome and welcome! I can share details and my screen if someone will have time.

If there is a recommended way how to do such imports please do share. Am out of ideas.

[wind field]

cluster = MongoClient["my private direction"] Why is it giving me an error in MongoClient?
I have imported it
from pymongo import MongoClient

[brave bridge]

Can you show the error?

[south cobalt]

i am appending a series to an empty pandas data frame but when i print the result i find its still an empty data frame

anyone know why this might be happening

self.pv_DFS[1].append(de_pv, ignore_index=True)

self.pv_DFS is a python list of empty dataframes but i cant seem to edit any of these data frames

[mellow verge]

is pymongo blocking?

[south cobalt]

this to me or something else?

[mellow verge]

nonon something else

[south cobalt]

ok good lol cause idk what pymongo is

[brave bridge]

If it does I/O and it doesn't use async/await, it's blocking 🙂

Non-blocking I/O is only non-blocking because it allows you give control back with await when it's waiting for I/O to respond.

[mellow verge]

okay thanks

[burnt turret]

https://gist.github.com/anand2312/840aeb3e98c3d7dbb3db8b757c1a7ace I've written a gist about the non-blocking mongodb driver and how you'll switch to it from pymongo

[calm prawn]

How can I do a sqlite search where if I input Bartick it will give me data about Bartick Maiti
OR
I input Maiti it will give me data about Bartick Maiti

[proven arrow]

Use LIKE for pattern matching

https://www.sqlitetutorial.net/sqlite-like/

[calm prawn]

thanks

Though I randomly searched like this SELECT * FROM cards WHERE NAME CONTAINS ? sqlite

and got the same link 😛

[proven arrow]

But SQLite doesn’t support contains

Oh never mind I misread what you said

[calm prawn]

Nha nha I didn't knew so I was just testing

[calm prawn]

it is not even matching spelling

Like if name is Bartick if someone puts B

it is saying true name found

How can I also ensure that it matches spelling?

[proven arrow]

You can’t because it matches the pattern

Although you can set a minimum character limit so it tries to find those with a minimum number of characters

[calm prawn]

okay

I will just split and check

[proven arrow]

You can also do it so it matches anything between the first and last letter, and I’m sure this is covered in the link I sent

[cedar totem]

hlw anyone can help

hlw anycan help

is database is safe on repl.it

[sharp kindle]

we have a directory full of mostly similarly structured json files that i wan't to setup as a databse, should i go towrads sql or nosql^

the json structure changes semi regularly though

its also less than a gig and doesnt need fast access so performance is not that important

just need to be able to query for specific properties easily

[burnt turret]

MongoDB would probably be the easiest to set up for this case 🤔

[signal cloak]

    raise ServerSelectionTimeoutError(
pymongo.errors.ServerSelectionTimeoutError: cluster0-shard-00-00.8k05l.mongodb.net:27017: ,cluster0-shard-00-02.8k05l.mongodb.net:27017: (10054, 'WSAECONNRESET'),cluster0-shard-00-01.8k05l.mongodb.net:27017: , Timeout: 30s, Topology Description: <TopologyDescription id: 6054a812480b5326016a69fb, topology_type: ReplicaSetNoPrimary, servers: [<ServerDescription ('cluster0-shard-00-00.8k05l.mongodb.net', 27017) server_type: Unknown, rtt: None, error=AutoReconnect('cluster0-shard-00-00.8k05l.mongodb.net:27017: ')>, <ServerDescription ('cluster0-shard-00-01.8k05l.mongodb.net', 27017) server_type: Unknown, rtt: None, error=AutoReconnect('cluster0-shard-00-01.8k05l.mongodb.net:27017: ')>, <ServerDescription ('cluster0-shard-00-02.8k05l.mongodb.net', 27017) server_type: Unknown, rtt: None, error=AutoReconnect("cluster0-shard-00-02.8k05l.mongodb.net:27017: (10054, 'WSAECONNRESET')")>]>```

y do u get this error working on mongo DB?

[signal cloak]

also

how do i get adminDB

[proven arrow]

UPDATE table SET column = ? WHERE condition

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

[proven arrow]

? Is a placeholder and only works for values. So you use it wherever a value will go.

It won’t work for things like table names or column names, because those must be hardcoded into the string

No

First write a working Sql statement by following the link I sent, and then replace the values with the ?

[rain plank]

You need to make (Update) a tuple (Update,)

[proven arrow]

What is wallet?

So then read your statement back and tell me if it makes sense

Where wallet = user id is what you have

Yayy 😃👏

Yeah but you should pick better easier to understand names for variables

[green sorrel]

sqlalchemy.exc.OperationalError
sqlalchemy.exc.OperationalError: (MySQLdb._exceptions.OperationalError) (1045, "Access denied for user '@root'@'localhost' (using password: NO)")
(Background on this error at: http://sqlalche.me/e/13/e3q8)

help plzz

[proven arrow]

Check your credentials

You have an @ at the beginning

[sleek epoch]

how do i make a boolean within a postgres expression?

do i just use $x then pass True as the arg?

[rain plank]

yes

[sleek epoch]

is there a gui for postgres?

[rain plank]

pgadmin

[sleek epoch]

thx

[rain plank]

np

[sleek epoch]

would a many to many relationship be the best way to do members?

discord users to guilds

would this be an appropriate table?

    await db.execute("CREATE TABLE IF NOT EXISTS members(userid BIGINT REFERENCES users(userid), guildid BIGINT REFERENCES guilds(guildid), announcements BOOLEAN, PRIMARY KEY(userid, guildid))")

ive never done many-many before but i presume its done like this

[proven arrow]

Yes, if that’s your pivot/junction table

Just add to the current column
UPDATE ... SET column = column + new_value WHERE ...

[vestal shoal]

    cursor_data.execute("SELECT bass FROM userfishdata WHERE memberid = ?",(author,))
    print(cursor_data.fetchone())
    cursor_data.execute("SELECT common_carp FROM userfishdata WHERE memberid = ?",(author,))
    print(cursor_data.fetchone())```

is there  a better way than doing this to get individual parts of my database?

[warped trout]

I'm doing the django tutorial and it mentioned how SQLite is serverless - could someone explain what that means?

[proven arrow]

You can select multiple columns in a single statement

Just separate each column with a comma ,

[vestal shoal]

yeah but it errors

[proven arrow]

Then show the error

[vestal shoal]

    cursor_data.execute("SELECT * FROM userfishdata WHERE memberid = ?",(author,))
    bass = cursor_data.fetchall()[0]
    salmon = cursor_data.fetchall()[1]```

this errors

with

[proven arrow]

It means it’s a file based database, so you don’t connect to it over a network but instead the file system

[vestal shoal]

discord.ext.commands.errors.CommandInvokeError: Command raised an exception: IndexError: list index out of range

[ebon skiff]

Any way to write this a bit better? ```MYSQL
SELECT customerName, country, creditLimit
FROM customers
WHERE country = 'USA' AND creditLimit > 100000
OR country = 'Australia' AND creditLimit > 100000
OR country = 'Japan' AND creditLimit > 100000

[vestal shoal]

but its not out of range\

[proven arrow]

Indexing starts at 0

[vestal shoal]

i know

this is my db:

[('11', '3', '0', '0', '0', '819798877125214238', '0')]

[proven arrow]

You do fetchall once and then that’s it. There’s nothing else to fetch

[vestal shoal]

bass text,
common_carp text,
common_herring text,
red_herring text,
blue_herring text,
memberid text,
golden_herring text

[proven arrow]

Whatever you fetch the first time, use that as your result

[vestal shoal]

yeah but sometimes it comes up with different errors

oh wait nvm u fixed it ty

[proven arrow]

If this is the db then why are you indexing with 1?

[vestal shoal]

wym

i start the index with 0

[proven arrow]

There’s only 1 element in that list, and clearly you were indexing with 1 hence the error

Maybe lookup how indexing works with lists.

[vestal shoal]

@proven arrow

doesnt indexing with lists work like this

like for that i would do

print(bass[0][1])

idk

[proven arrow]

That’s fine

[vestal shoal]

yeah i know bro im just tired

ive been up for like 30 hours

i dont know why i indexed with 1

[proven arrow]

@ebon skiff you can do WHERE country IN (‘USA’, ‘Australia’, ‘Japan’) AND creditlimit = 100000

[ebon skiff]

SELECT customerName, country, creditLimit 
FROM `customers` 
WHERE country IN (‘USA’, ‘Australia’, ‘Japan’) AND creditlimit = 100000 LIMIT 0, 25
MySQL said: Documentation

#1054 - Unknown column '‘USA’' in 'where clause'

[proven arrow]

Don’t copy what I sent but use the proper quotations of your keyboard

My phone doesn’t send it properly over discord

[ebon skiff]

i know the `

It still says the same 😅

[proven arrow]

Show screenshot

Of code and error

[ebon skiff]

Here it is.

[proven arrow]

That doesn’t tell me how you wrote in on your computer

Show a screenshot

[ebon skiff]

I did it wrong mb

[sleek epoch]

more sql syntax

await db.execute("""UPDATE members
        SET announcements = $1 
        WHERE userid = $2 and guildid = $3""", False, ctx.author.id, ctx.guild.id)

what did i do wrong

no changes happen, but no errors

[ebon skiff]

Does db not use %s?

[sleek epoch]

%s doesnt work for me

im using postgres

[proven arrow]

Syntax looks fine

Does a row exist with those values?

[sleek epoch]

yes

checked it myself

these are my tables

    await db.execute("CREATE TABLE IF NOT EXISTS guilds(guildid BIGINT PRIMARY KEY, prefix TEXT)")
    await db.execute("CREATE TABLE IF NOT EXISTS users(userid BIGINT PRIMARY KEY, announcements BOOLEAN)")
    await db.execute("CREATE TABLE IF NOT EXISTS members(userid BIGINT REFERENCES users(userid), guildid BIGINT REFERENCES guilds(guildid), announcements BOOLEAN, PRIMARY KEY(userid, guildid))")

the third one is the one im editing

[proven arrow]

Show the output of:

await db.execute("""SELECT COUNT(*) FROM members
WHERE userid = $1 and guildid = $2""", ctx.author.id, ctx.guild.id)

[sleek epoch]

record count = 1

[proven arrow]

So it does exist

[sleek epoch]

the third one should be false, but it ends up remaining true

oh it is false

it was moved to the bottom

lol

its working now

turns out i had a row[0] instead of row[0][0]

is there a way to select a single row?

[proven arrow]

What module?

I think there is a fetchrow method for asyncpg

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

[wind field]

rankings = levelling.find().sorts("xp",-1)

Instance of 'Cursor' has no 'sorts' member; maybe 'sort'?

[torn sphinx]

How to write query which does pagination in Sql?

[ebon skiff]

What?

[jade swan]

I'm getting to learn MySQL but I cannot seem to understand how to use the CREATE USER statement. Could someone help me with that?

import mysql.connector as mysql

username = "scopes"
password = "hello"

db = mysql.connect(
    host="localhost",
    user="root",
    passwd="dbms"
)

sql = 'CREATE USER "'+username+'"@"%" IDENTIFIED BY "'+password+'"'

mycursor = db.cursor()

mycursor.execute(sql)

print(db)

Why when I run I get :

mysql.connector.errors.InterfaceError: 2003: Can't connect to MySQL server on 'localhost:3306' (10061 No connection could be made because 
the target machine actively refused it)

[torn sphinx]

You know pagination?

Like say I have 100 records and so I make pagination buttons so it shows 10 records for each page

Then I click next and show next 10 items

How to do this with query

[ebon skiff]

Hmm

Best case would be to just fetch all the info and split them into pages.

[torn sphinx]

Yeah but is inefficient

[ebon skiff]

Why?

[torn sphinx]

To fetch thousands or even million records into memory

Especially if the user will only browse through couple page

[ebon skiff]

Making a connection 10 times because 100/10 = 10 or only once a bit longer?

[torn sphinx]

What?

I don’t understand

[ebon skiff]

Instead of the info you could also fetch the row count and fetch the first 10.

[torn sphinx]

Yeah so how do this with the query

[ebon skiff]

SELECT * FROM customers limit 10
``` Fetch first 10 results

[torn sphinx]

Ok then next 10?

[ebon skiff]

SELECT * FROM customers limit 10,10

[serene pivot]

What's the fastest way of doing update statements in postgres?

is there a better way than iterating through the values to insert?

        query = '''UPDATE users SET eyecount = $1 WHERE user_id = $2 AND server_id = $3'''
        for key in self.eyecounts:
            print(key)
            args = key.split(',')
            user_id   = int(args[0])
            server_id = int(args[1])
            eyecount  = self.eyecounts[key]
            await self.cxn.execute(query, eyecount, user_id, server_id)

I have something like that going ^^ but its dreadfully slow even though I tested it on only 200 keys

[sacred vine]

So I'm having trouble with sqlite3 while learning django... Every time I run py manage.py dbshell I get "CommandError: you appear not to have the 'sqlite3' program installed or on your path." I both have sqlite3 installed and added to my user variables and system variables! Since it wasn't working with the sqlite3 directory in path I even went as far as adding the actual .exe file paths to my env variables... At this point I'm completely lost, any help would be GREATLY appreciated!

[tacit sapphire]

Question when it comes to using Flask and MongoDB. Do I want to have a connection created when someone hits an endpoint, or do I want there to be one connection created when the app starts up, and all calls use that?

[chrome gulch]

Is there any other way to not update the same id in json files instead having two of the same id and have different values

[dapper root]

Select s.cname FROM Students as S,Registration as R where s.rollno = r.rollno and r.courseno = 12 and R.percent_ > 90;

Error
ORA-00933: SQL command not properly ended
can anyone tell how?

[dusk sundial]

Hey, I have a question: I'm required to query json data from a website, load it into a json array, and put it into a tinyTB database (tinydb and json modules are imported in python). I did all of this, but I'm supposed to change the values of 2 keys to lists first (for all records). For example, a record from the json array like { "a" : "72", "b" : 11, "c" : 3420} should first become { "a" : "72", "b" : [11], "c" : [3420]} and then insert it into the tiny db. How can I do this? Am I supposed to use the update function & a for loop or something?

[slender atlas]

I need to store banned words for message filtering. Is it better to store multiple records with (guild_id, banned_word) or to have one record like (guild_id, banned_words_blob)?

(using aiosqlite)

[broken bear]

Can someone explain this statement from the fastapi-postgres docs:

*### Migrations

As during local development your app directory is mounted as a volume inside the container, you can also run the migrations with alembic commands inside the container and the migration code will be in your app directory (instead of being only inside the container). So you can add it to your git repository.

Make sure you create a "revision" of your models and that you "upgrade" your database with that revision every time you change them. As this is what will update the tables in your database. Otherwise, your application will have errors.*

When he says 'also run', whats that in contrast to? And

[orchid apex]

With MongoDB, is it any better to store the ObjectID of a document you want to reference in the document that you look at and go dereference that ObjectID, or is it better to embed the contents of that document into the document that you look at?

[torn sphinx]

This is my codecss cursor.execute("INSERT INTO dono_logs (guild_id, user_id, amount) VALUES (?, ?, ?) ON CONFLICT DO UPDATE SET amount = amount + ?;", [guild, user, amount, amount])
And i get this error```css
Ignoring exception in command dono_add:
Traceback (most recent call last):
File "C:\Users\Miste\AppData\Local\Packages\PythonSoftwareFoundation.Python.3.9_qbz5n2kfra8p0\LocalCache\local-packages\Python39\site-packages\discord\ext\commands\core.py", line 85, in wrapped
ret = await coro(*args, **kwargs)
File "C:\Users\Miste\Documents\GitHub\Wiggle-Bot\cogs\dono.py", line 57, in dono_add
cursor.execute("INSERT INTO dono_logs (guild_id, user_id, amount) VALUES (?, ?, ?) ON CONFLICT DO UPDATE SET amount = amount + ?;", [guild, user, amount, amount])
sqlite3.OperationalError: near "UPDATE": syntax error

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

Traceback (most recent call last):
File "C:\Users\Miste\AppData\Local\Packages\PythonSoftwareFoundation.Python.3.9_qbz5n2kfra8p0\LocalCache\local-packages\Python39\site-packages\discord\ext\commands\bot.py", line 902, in invoke
await ctx.command.invoke(ctx)
File "C:\Users\Miste\AppData\Local\Packages\PythonSoftwareFoundation.Python.3.9_qbz5n2kfra8p0\LocalCache\local-packages\Python39\site-packages\discord\ext\commands\core.py", line 864, in invoke
await injected(*ctx.args, **ctx.kwargs)
File "C:\Users\Miste\AppData\Local\Packages\PythonSoftwareFoundation.Python.3.9_qbz5n2kfra8p0\LocalCache\local-packages\Python39\site-packages\discord\ext\commands\core.py", line 94, in wrapped
raise CommandInvokeError(exc) from exc
discord.ext.commands.errors.CommandInvokeError: Command raised an exception: OperationalError: near "UPDATE": syntax error```

[burnt turret]

https://www.sqlite.org/lang_UPSERT.html

Example:

INSERT INTO phonebook(name,phonenumber) VALUES('Alice','704-555-1212')
  ON CONFLICT(name) DO UPDATE SET phonenumber=excluded.phonenumber;

The syntax that occurs in between the "ON CONFLICT" and "DO" keywords is called the "conflict target". The conflict target specifies a specific uniqueness constraint that will trigger the upsert. The conflict target is required for DO UPDATE upserts, but is optional for DO NOTHING.

[torn sphinx]

me?

[burnt turret]

yep

[torn sphinx]

k

[burnt turret]

you seem to be missing that "conflict target"

[torn sphinx]

back a liitle bit

[burnt turret]

Look at the example

CONFLICT(column)

[torn sphinx]

ok

so how do i make it so it puts a new user id and a new guild id?

[merry nymph]

I'm not sure if this is a good way to design my database

I want users to add things to a guild specific shop. Am I heading in the right direction? I also want account balances to be server specific

[burnt turret]

db.execute will give you a Cursor object

the Cursor will contain the data that was retrieved from the database

Right, so you can either loop over cursor itself, or do something like fetchone

...
# looping over Cursor
# each iteration will give you one row
async for row in cursor:
  # do stuff with the row

# using fetch-x methods
row = await cursor.fetchone()  # this retrieves just one row

you don't need to do both

either works

i used fetchone because you'd be getting only one row from your database for that query (ideally)

in cases where you will be expecting more rows to be sent, you'd use something like fetchall

and it'd give you a list of rows

await fetchone

also, remember - fetchone is giving you a row from the database

a row will be in the form of a list (or a tuple, i don't really know aiosqlite)

So if you send row directly, it'll look something like (23,)

you can use indexing to retrieve the actual value (row[0] etc)

Right but the module doesn't know that

it behaves as if you will be getting multiple values

So it will always give you a tuple

yep

indexing would be the more accurate term

to get only 44 from it that's just row[0]

[rain plank]

Yes

[sinful saffron]

Hi, i found this website called sqliteonline.com, does someone knows if i can actually store data in there or it deletes after certain time?

Please mention me when responding, thanks.

If not, can someone recommend me a free online sqlite/sql database?

Some data, but when I see people start using it im probably going to buy one

I'll prefer to not keep my pc on 24/7

[broken bear]

Can someone explain this commnd to me? :

docker run --rm --network=host -v `pwd`:/data postgres \
  psql -h db -p 5432 -U postgres -c \
    "\\copy temp FROM '/csv_kondo/mint_residences.csv' WITH csv"

-v `pwd`:/data postgres \ whats this part?

[brave bridge]

seems alright to me 👍

just make sure that all guild IDs fit into an int

[merry nymph]

Yes, I marked them as integers

Thanks for looking at it!

[deft pasture]

Working with a database but I'm having issues in my script.
https://paste.pythondiscord.com/ominoduyok.py Think its from me referencing the userInfo twice? not sure. Error line starts line 204 here. I also tried passing the cursor object to returnUserInfo method but didn't work? Checked w/ line 125 cursor1.connection == True returns False.

[torn sphinx]

ok

[torn sphinx]

How do I import a db file in a db folder thats in a lib folder

[burnt prism]

Does anyone here use goormide

[mighty mica]

TypeError: The first argument to execute must be a string or unicode query.

Why does this error happen? What is the cause and how do I fix it?

[burnt turret]

The error says that you have passed something that is not a string as the first argument to "execute"

You should be giving it an SQL query in the form of a string

[torn sphinx]

Yeah my init.py has

db.build()```

It says no module named lib

from lib import db

Yes

So I import it on my main file

Yeah I have it in lib folder too

ok

[sinful saffron]

Hey, does someone have a database in infinityfree?

Because for some reason I can't connect to my sql database

Probably it's something with the credentials (password, user and stuff)

[serene pivot]

How can I optimize postgres inserts. I’m trying batch inserts on interval while storing data meanwhile in the cache. But then I’m running into the issue of lack of memory. How could I streamline this to make it as efficient as possible?

[torn sphinx]

can someone help me with this

import tools
import connector
import pyfiglet
connector = connector.connect() 


def login():
    tools.clear_screen()
    mycursor = connector.cursor()
    myresult = mycursor.fetchall()
    result = pyfiglet.figlet_format("Nemo") 
    print(result) 
    username = input(" Enter Your Username: ")
    print(" ")
    password = input(" Enter Your Password: ")
    print(" ")
    correct = input(" Is This Correct Y/N: ")
    if correct == "Y":
        pass
    elif correct == "N":
        tools.clear_screen()
        login()
    mycursor.execute(f"SELECT * FROM nemo_tool WHERE username = '{username}' AND password = '{password}' ")
    if len(myresult) == 1:
        print("You Are In")
    elif len(myresult) == 2:
        print("Wrong Info")





login()```

its not working

[narrow schooner]

I had some trouble connecting to a redis database

[torn sphinx]

any idea

[serene pivot]

dont use f strings in db queries

[serene pivot]

What trouble?

[torn sphinx]

this is my error

[narrow schooner]

Nvm, I figured it out

[torn sphinx]

  File "/Users/namorahimi/Projects/Python-Projects/nemo_tool/functions/login.py", line 35, in <module>
    login()
  File "/Users/namorahimi/Projects/Python-Projects/nemo_tool/functions/login.py", line 12, in login
    myresult = mycursor.fetchall()
  File "/opt/homebrew/lib/python3.9/site-packages/mysql/connector/cursor.py", line 914, in fetchall
    self._check_executed()
  File "/opt/homebrew/lib/python3.9/site-packages/mysql/connector/cursor.py", line 376, in _check_executed
    raise errors.InterfaceError(ERR_NO_RESULT_TO_FETCH)
mysql.connector.errors.InterfaceError: No result set to fetch from
namorahimi@Namos-MacBook-Pro functions % `

[serene pivot]

Don’t use f strings in db queries

[torn sphinx]

but that is not the problem

it works

i have used it before

it select

[torn sphinx]

this is the problem

[serene pivot]

Quit using the invalid syntax then come back.

[torn sphinx]

    mycursor.execute("SELECT * FROM nemo_tool WHERE username = namo AND password = namo ")

better

[serene pivot]

this sqlite?

[torn sphinx]

mysql

[serene pivot]

idk the syntax then

go look it up

i only know sqlite and postgres

[torn sphinx]

ok

[fervent trellis]

Does someone understand parametres in MySQL? Could someone tell me how to Find the names and ids of all the subjects I have to meet in order to study the subject with the id in the variable $ 0 (hence all its prerequisites, prerequisites prerequisites, their prerequisites, etc.). Name the column ‘Name’, the column id ID ’. Sort the result in ascending order by name and remove any duplicates. I'm attaching a pic of the database.

[signal oasis]

Hello, what Database should I use for simple storing of some numbers?

[merry nymph]

You can use a flat file if it's not something big, I guess

[polar osprey]

Hey there have to store material numbers of the following format: 1234.1234

so four digits, a point and then another 4 digits

what data type do I best use for this? I was trying to avoid text and wanted to go with nubers but then I learned that float makes your life miserable by being unprecise

[torn sphinx]

Hmm it’s not unprecise ?

Only sometime when you do calculations on it.

[polar osprey]

you can't do a var1 == var2

and when I read the value out of my database with a prepared statement the results I'm getting are absolutely ridiculous

[torn sphinx]

What is the number representing? And where you will be doing this calculation

[polar osprey]

It's like an identifier for an object

[torn sphinx]

Is it always 4 and 4 digits each side

[polar osprey]

yes

[torn sphinx]

You could store as int.

And then add decimal later when you need

It’s what I did for storing currency in my website

[polar osprey]

I guess that would work but it's a bit dirtier than what pip suggested so I'm gonna look into that

thx for the advice

[mossy socket]

Honestly I'd be surprised if there was one, considering how easy it is to just have a start and end column

[coarse olive]

I don't think there is one either. That's a derived relationship between values.

[weak tinsel]

import mysql.connector as sql


db_con = sql.connect(
    user="root",
    passwd="root",
    host="localhost",
    use_pure=True
)
db_cur = db_con.cursor()


def create_db(db_name):
    db_cur.execute("CREATE DATABASE IF NOT EXISTS %s", (db_name,))
    db_con.commit()

    db_cur.execute("SHOW DATABASES")
    print(*db_cur.fetchall())


if __name__ == "__main__":
    create_db("hmm")```

why do i get a SQL syntax error for this?

Traceback (most recent call last):
  File "/run/media/deep/GAMER/Programming/Python/Rough work/practical_exam/rec1,2,3,4.py", line 22, in <module>
    create_db("hmm")
  File "/run/media/deep/GAMER/Programming/Python/Rough work/practical_exam/rec1,2,3,4.py", line 14, in create_db
    db_cur.execute("CREATE DATABASE IF NOT EXISTS %s", (db_name,))
  File "/home/deep/.local/lib/python3.9/site-packages/mysql/connector/cursor.py", line 551, in execute
    self._handle_result(self._connection.cmd_query(stmt))
  File "/home/deep/.local/lib/python3.9/site-packages/mysql/connector/connection.py", line 490, in cmd_query
    result = self._handle_result(self._send_cmd(ServerCmd.QUERY, query))
  File "/home/deep/.local/lib/python3.9/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 MariaDB server version for the right syntax to use near ''hmm'' at line 1

[burnt turret]

The placeholder %s is only meant for values

so they won't work with stuff like column names/table names

Those are generally hardcodeded into the statement

[vivid torrent]

You could use an f-string to compose that query. Just make sure your parameter is sanitized.

[weak tinsel]

O

What do I do then

O I did not read what cyg... Said

Sorry

[vivid torrent]

db_cur.execute(f"CREATE DATABASE IF NOT EXISTS {db_name}") ... obviously you need to ensure db_name isn't something nefarious.

Ehhh, that just makes me twitchy. I don't think mysqlclient has a way to parameterise literals. The DB API doesn't address it, either.

[rain plank]

just make sure it doesn't have semi colons 😎

[vivid torrent]

It's 2021 and we're still worried about SQL injection attacks. Sheesh.

[burnt turret]

I think there's some method you can use to escape it

Connection.escape_string probably

You could use that on the table name and then put it into the query i think 🤔

[weak tinsel]

damn this is for an exam haha

its fine if i use .format() ig

though i will keep this in mind for future usecases

thanks :D

[white pecan]

hey, I'm new to databases and I'm wondering how exactly they work -

I use PGadmin to access a remote database, for lets say adding a column in a table. Does it take a lot longer to add it from PGAdmin than just doing it on the remote server itself?

And also, lets say I wanted to backup a database every day from RS (remote server) 1 to RS 2, by using something like this:

pg_dump -h [host address] -Fc -o -U [database user] <database name> > [dump file]

My question is, does it take resources from RS 1, the database host, while it's backing up, or from RS 2, the idle server only used for containing the DBs? I want to ideally leave RS 1 on and active, was wondering if it would slow down as I would be backing it up.

Thanks for the help!

[torn sphinx]

I have two "beginners" book for PostgreSQL and SQL:
Practical SQL: a more traditional book of learning SQL by nostarch
A Curious Moon: a novel in which you will learn how to create and use a database to import and make sense of the date from the Cassini mission to Saturn and more specifically the data from the moon Enceladus. Data to be downloaded from NASA.
I have them both, I want to study both of them, but with which one should I start?

[brazen charm]

MMM i'd probably start with SQL first because you can use SQLite in python to get the basic idea

then move onto postgres as it'll help you setup the server and everything

[halcyon fox]

I am having a problem with MySQL connector, I suppose it's a permission issue even though the machine I am trying to connect to is the machine I am using to create the server so that's odd

def create_connection(host_name, user_name, user_password):
    connection = None
    try:

        connection = mysql.connector.connect(host=host_name, user=user_name, passwd=user_password)
        print("Connection to MySQL DB successful")
    except Error as e:
        print(e)
    return connection


connection = create_connection("127.0.0.1", "root", "")

2003: Can't connect to MySQL server on '127.0.0.1:3306' (10061 No connection could be made because the target machine actively refused it)

[torn sphinx]

II have worked with SQL before (lots), but it has been a while (more than decade). So I need a refresher, and the book Practical SQL includes installing PostgreSQL.

[brazen charm]

Mmm probably got with PostgreSQL

SQL hasnt changed much / at all in the last decade or so

[sick perch]

Anyone here familiar with content addressed databases, is like to avoid inventing an own one, but it seems to me beside git there is nothing else

[edgy wolf]

hey how do I do insert and append at the same time? i use asyncpg
this is how I insert:

await db.execute("INSERT INTO my_table (column_1) VALUES($1)","something")

this is how I append to the list after the first query:

await db.execute("UPDATE my_table SET list_1 = array_append(list1,$1) WHERE column1 = $2",1234,"something")

this does fine but I have to make two calls , how do I append the value into list_1 within the first query?

[shy shuttle]

Are you looking for "UPSERT" ?

[edgy wolf]

no i am not

i just want to insert into a column and append to list in the same row with same query instead of two

[shy shuttle]

it looks like those are 2 distinct operations then. The UPDATE requires a WHERE clause that doesn't apply to the INSERT. Even if you manage to cram them into a single db.execute I think it's going to have to be 2 separate statements for the DBMS

[edgy wolf]

you still didn't get it , i don't wanna do insert and update within a query . suppose I have a table named my_table, it has two columns: column1 character varying and cloumn2 [] integer. I want to know how do I insert a value to both these columns. I could do it if the second column wasn't a list.

[shy shuttle]

CREATE TABLE my_table (
  some_number  integer,
  some_text    text,
  an_array     integer[]
);
INSERT INTO my_table
    VALUES (123, 'abc', '{10000, 10000, 10000, 10000}');

[edgy wolf]

thnks!

[devout girder]

ok i need to make a decision
-What database is better (in your opinion)? MongoDB or PostgreSQL (Im choosing between these two, other options are or outdated or ive no interest lookingin)
-Why do you think so?
-What are the differences?

[brazen charm]

PostgreSQL in just about every way other than ease of use for beginners

[devout girder]

oh demn

its hard?
thought postgre was easier

[shy shuttle]

neither is really hard (or easy) to use, but postgres is a far more mature product

[devout girder]

in what way?

[brazen charm]

PostgreSQL vs MongoDB

• PostgreSQL is a SQL database while MongoDB is a unstructured NoSQL database, PostgreSQL follows all the relational concepts as most SQL databases do.
• PostgreSQL offers considerably more performance than MongoDB despite mongo being 'web scale'
• MongoDB gets real slow as document amounts grow.
• Mongo's unstructured format although great for beginners makes it a maintainability nightmare with no ability to control and lock down certain requirements
• Any mutli document dependent data is considerably slower to fetch with Mongo due to the lack of efficient JOINs
• Python specifically has a much more mature PostgreSQL driver ecosystem vs Mongo which although offers both async and sync drivers are far slower and less mature.

Where mongo does offer an advantage is the fact that it offers the very familiar JSON style format which is great for beginners but ultimately costs the database it's performance and robustness of data.

[shy shuttle]

postgres has been around for a long time and the development team has been extremely thoughtful and intentional about which features they add and the overall design of the database. it's very stable an it rarely has "surprising" behavior (the bad kind of surprise, not the "surprise its your birthday" kind)

[devout girder]

alright. im gonna start with postgres. that deservers a pin, thank you ❤️

[shy shuttle]

trying to design a database around JSON's is inherently ugly

[devout girder]

and, is https://www.youtube.com/watch?v=qw--VYLpxG4&ab_channel=freeCodeCamp.org a good video to start with?
i like video tutorials

[brazen charm]

yikes 4 hours

god no

[devout girder]

uff

[brazen charm]

are you windows or linux?

or mac?

[devout girder]

win

[brazen charm]

postgres on windows is very simple to install

https://www.postgresql.org/download/windows/

get the exe via the download

run it, it'll install PgAdmin aswell

[shy shuttle]

i like to do stuff in pieces. try and just get the database running first, then try connecting to it using a simple client and adding some tables and inserting rows. then see i you can get the python connector to work.

[devout girder]

alr will do

thanks

[torn sphinx]

Hello,

i've a stupid problem... i'll try to use python to request mysql database...
if i do : select VERSION(), no problem...

but i'm not able to insert, or just do a stupid select from one database...
my script "freeze" to :
mycursor.execute("SELECT * FROM table limit 10")
print("here")
And in mysql processlist, i see my connection in sleeping....

i do lot of search on web of the strange problem, have you an idea?

(and if i do my simple select in php, for example, no problem too...)

in the connection, i put autocommit=True to try, but no issue 😦
he never "print" my "here"
and i must to kill my script, ctrl+c doesn't work

it try mariadb library and mysql-connector-python

it's very strange

[hoary coyote]

maybe post your full script here? it's hard to imagine what you have done...

[signal oasis]

Why does my text file in Pycharm become read-only and stops having permanent changes to it?

[brave bridge]

what about sqlite? 🙂

[torn sphinx]

import mariadb
try:
mydb = mariadb.connect(
host=db_host,
user=db_user,
password=db_pass,
database="mydatabase",
autocommit=True
)
except mariadb.Error as e:
print(f"Error connecting to MariaDB Platform: {e}")
sys.exit(1)
mydb.auto_reconnect= True

mycursor = mydb.cursor()

mycursor.execute("SELECT * FROM mytable limit 10")
#mycursor.execute("SELECT VERSION()") <=OK
print("here1")

mycursor.close()
mydb.close()

[brazen charm]

Sqlite is \o/

[brave bridge]

?

[torn sphinx]

thanks @hoary coyote

SHOW TABLES works too..
but select no 😦

(and it's the same problem with INSERT, after execute print("here1") don't print...)

in mysql.log, i see the request select.
so, how to go after mycursor.execute("SELECT * FROM mytable limit 10") ?

[hoary coyote]

after mycursor.execute, you need to retrieve the data from the cursor by looping it

example:
for (first_name, last_name) in mycursor:
print(f"First Name: {first_name}, Last Name: {last_name}")

[torn sphinx]

yes, but, i can't (i have not my print("here1");

just a question..
if i just do :
mycursor.execute("SELECT * FROM mytable limit 10")
mycursor.close()
mydb.close()
print("Hello")

program print "Hello"?
or it's necessary to retrieve datas from the select??

[hoary coyote]

it will print "Hello"

[torn sphinx]

ok, so it's not necessary to retreive datas...
i don't understand what is the problem, why he do anything after pass the request to the database.
Version, or list TABLES i'ts ok
but Select, he "wait" after sending the request...

Is there a necessary specification on tables to request tables with python?

[hoary coyote]

not really...i modified your code a bit to connect to my local database and it works fine...

try:
mydb = mariadb.connect(
host=db_host,
user=db_user,
password=db_pass,
database=db_database,
autocommit=True
)
except mariadb.Error as e:
print(f"Error connecting to MariaDB Platform: {e}")
sys.exit(1)
mydb.auto_reconnect= True

mycursor = mydb.cursor()

mycursor.execute("SELECT first_name, last_name FROM users limit 10")
for (first_name, last_name) in mycursor:
print(f"First Name: {first_name}, Last Name: {last_name}")

mycursor.close()
mydb.close()

[torn sphinx]

😦

ok thanks..

[rain plank]

um

!pypi aiomysql I think

[delicate field]

aiomysql v0.0.21

MySQL driver for asyncio.

[torn sphinx]

@hoary coyote there is probably a specificity of the tables, because i create a new table (with phpmyadmin) "test", avec select works fine...
now the questionis, why no problem with php, and can't select or insert with python on theses tables

[sudden thistle]

Whats an sql pool?
Also, where is my data hosted when using Postgres, and will I have to sign up for anything

[torn sphinx]

@hoary coyote is there a limitation about numbers of cols?
(36 for table who have the problem)

[hoary coyote]

@torn sphinx you can always create another simple table to test it out...but i don't think that's the problem...

im suspecting your connection, or something is locking the table..

[torn sphinx]

i've one table with 3 cols, no problem
a table with 9 cols, no problem
a table with 36 cols, can't insert and select into...
request select is sending to mysql server, but no print my "here"
insert request is NOT sending to mysql server

mhh..

[shy shuttle]

postgres is free and open source, the data is hosted where ever you run the server

[torn sphinx]

connexion no, i'ts on the same network (2 VM)

[sudden thistle]

And if I run it on my computer, is it stored on my computer?

[shy shuttle]

its just a program, it runs where you want. if you just start it on your computer, thats where it is going to put the data

[sudden thistle]

I see, so if I begin on my computer, then transfer, it will still be on my computer unless if I move it

Its connected by a network

[torn sphinx]

with SHOW FULL PROCESSLIST; i see no lock

[shy shuttle]

unless you configure it to use some kind of network attached storage, it is local. in general, its unlikely that you'd be able to configure it to use anything but basic local storage without doing it on purpose

[shy shuttle]

big companies and providers might use non-local storage connected over SAN or NAS, but if you're in that situation, you will know it.

[torn sphinx]

@hoary coyote thanks for help!!
the problem was an mtu problem, in my vrack... i forget for theses server to put mtu to 1400...

can someone help me with my mongodb installation in my debian 10 ?
I already installed mongodb in my vps but i want to to connect it to my database

[zealous nova]

hi, so im using mongodb and i have structure like db -> col -> array -> dict -> specific value, how would i increment that value

[torn sphinx]

 Field                     | Type        | Null | Key | Default             | Extra
 My field                    varchar(70) | YES  | MUL | 12. To be defined   |        

It will work If I select a record (Drop down list), only if I set nothing, the default will not show up

I have set NUll as Yes, because I have record already existing and don't want to overwrite it

Any idea why the default would not work ?

[lime bough]

I'm looking for a high quality training course of sql, anyone know of any?

[zealous nova]

so i have this [{key: value}, {key2: value2}] inside MONGODB document, how would i increment value2 by 1

[scenic gale]

Anyone here familiar with mongoexport? I am trying to export all documents older than 120 days but I am struggling with how to format that mongoexport --host localhost --db pagesuccess --collection problem -q='{"date": {"$lt" : ' --type=csv --out text2.csv --fields date,problemDetails,problem,tags,url,language,institution,section,theme

"problemDate": "2020-05-11",

My dates in the db are formatted like that^

I'd like to get my script to delete all entries older than 120 days from current date

I'd appreciate any help!

[thorn geode]

https://sqlbolt.com/ is a good one

[vocal moon]

i'm getting this issue when I try to connect to my postgresql database
https://i.imgur.com/RdF7pkF.png

async def create_db_pool():
    try:
        bot.pg_con = await asyncpg.create_pool(host="192.168.1.156", database="usafdata", user="postgres", password="asd")
    except asyncpg.exceptions.InvalidAuthorizationSpecificationError:
        bot.pg_con = await asyncpg.create_pool(host="localhost", database="usafdata", user="postgres", password="asd")

thats the code im using to run it

[scenic gale]

older120Days=$(gdate --date="120 days ago" +%F)
mongoexport --host localhost --db pagesuccess --collection problem -q='{"date": {"$lt" : {older120Days}}} ' --type=csv --out text2.csv --fields date,problemDetails,problem,tags,url,language,institution,section,theme```

Can someone help me with my mongoexport command

it isnt working because I think the -q query is wrong

but I am not sure why

[devout girder]

im totally new with databases and im just curious if downloading postgresql is needed if i use heroku postgres

[shy shuttle]

you dont need to download the postgres server if you're provisioning on heroku, but you might want to download some type of client so you can connect to the instance and manage it

[pallid locust]

i created a few tuples i need to pull data from
6 6 21
10 8 22
and i want to insert new tuples using bits of info from different rows
for instance i want to do an insert into with (6, 21, 22)
however 21 and 22 are in different rows
is there a way for me to do this?

[devout girder]

mongo or postgresql

[dull shoal]

Ive been looking on the internet as the best way to do a For loop for an sql table. However it seems that one shouldn't do For loops in sql tables, but my script literally requires it. Is the best way to have ID's for each row, and then for row in range(0,len(table)):#do code?

[deft pasture]

Trying to create a DB with the option for one of the values to either be an integer or Null. When creating the table, can I specify one of the values such as create table table(value INTEGER DEFAULT NULL). Putting NULL for integer should be ok right?

[charred fractal]

What does the RESTART function for in the mysql database?

[rain plank]

Why are you specifying NULL as default?

[deft pasture]

doing some prizes, storing in DB. wanting to do NULL to specify that prize quantity is basically unlimited. if a integer specified then it has set quantity type stuff.

[rain plank]

If there isn't a NOT NULL constraint then it will be null by default

[deft pasture]

Wait im bit confused. If I don't specify the value "value" when inserting to db, it stores Null / None for that row, right? If I specify that value though w/ an int then it inserts an integer yea?

[charred fractal]

having not null prevents text from breaking a number column.

[charred fractal]

try looking at this; https://www.w3schools.com/sql/sql_isnull.asp

[rain plank]

Or DEFAULT 0

[deft pasture]

yea some diff number, think i understand the NOT NULL constraint then. cant be 0 if i default to a number cause quantity 0 is removed from db. Thank you both!

[charred fractal]

test?

i'm not getting notifications from here, when all messages are on, and this isn't set to mute.

weird.

oh...

Possibly because I muted category oops

[rain plank]

I can't stand seeing unreads so I mute all big servers

[calm barn]

[{key: value +1}]

did u try that?

you trying to add only once or?

set specific amount

[zealous nova]

I just deleted the element from list and added new one

[calm barn]

no no no

don't do it like that

[zealous nova]

Ok

I just want to increment by one

Always

In mongo

[calm barn]

$inc

use $inc

https://docs.mongodb.com/manual/reference/operator/update/inc/

[zealous nova]

Yes u tryed

But didnt know how to access the element in the dict

[calm barn]

show me your database

send me ss

[zealous nova]

Ok

[calm barn]

you can send me dm

[burnt turret]

why are you passing user=None?

[rain plank]

asyncio.coroutine is deprecated. Use async def instead.

Well since 3.8

[burnt turret]

yeah they aiomysql docs still have some old code examples on there

[scenic gale]

mongoexport --host localhost --db pagesuccess --collection problem -q='{ "problemDate": { "$lte" : "2021-03-22"}} ' --type=csv --out text2.csv --fields date,problemDetails,problem,tags,url,language,institution,section,theme
2021-03-23T08:47:20.340-0400    connected to: mongodb://localhost/
2021-03-23T08:47:20.348-0400    exported 8 records
--------------------------------------------------------------------------------------------------------------------
mongoexport --host localhost --db pagesuccess --collection problem -q='{ "problemDate": { "$lte" : "$older120Days"}} ' --type=csv --out text2.csv --fields date,problemDetails,problem,tags,url,language,institution,section,theme
2021-03-23T08:47:27.884-0400    connected to: mongodb://localhost/
2021-03-23T08:47:27.892-0400    exported 0 records```

Anyone know why the first works fine

but when I use variable in the second it says exported 0

?

Please ping me if you know why

[torn sphinx]

bot.connection_url = DBconnectionURL
bot.mongo = motor.motor_asyncio.AsyncIOMotorClient(
    str(bot.connection_url))
bot.db = bot.mongo["BlackHole"]
bot.MutesDB = Document(bot.db, "Mutes")
bot.TogglesDB = Document(bot.db, "Toggles")
bot.LevelsDB = Document(bot.db, "Levels")
bot.ReactionRolesDB = Document(bot.db, "ReactionRoles")
bot.CountingDB = Document(bot.db, "Counting")
bot.DisMatchDB = Document(bot.db, "DisMatch")
bot.RemindsDB = Document(bot.db, "Reminds")

does this look like a smart idea for the way i set up my databases for my Discord bot, or is there a smarter way to do it?

[vast surge]

how db work?

mention me when someone answers

[sinful condor]

does anyone know why I am getting an invalid syntax on the gt rank1 = collection.find({score: {$gt: userxp}}).count()

[naive adder]

does anyone know how to store multiple things in a column
btw im using sqlite

[weak tinsel]

how do i solve the error - TooManyConnectionsError

im using asyncpg

i defined a asyncpg pool as a bot attribute and whenever i use it , this error pops up

[frigid glen]

@weak tinsel you probably need to reuse your connections, instead of just creating a new one each time.

[river thicket]

Hi, everybody. Have someone worked with cx_Oracle? I can't connect to a remote db and I need some help.

[weak tinsel]

o how do i do that

[burnt turret]

it needs to be in a string

"$gt"

[sinful condor]

oh ok thanks

[weak tinsel]

why do i get __aenter__ error when i access asyncpg pool

[rain plank]

You probably forgot to call acquire()

[weak tinsel]

o

wait so

pool = pool.acquire()

?

[weak tinsel]

i get a new error - pool is closed 🤔

[rain plank]

No lol

[weak tinsel]

😔

i remember doing this but i have forgotten

[charred fractal]

Whenever I do something like SELECT Restart FROM something, Restart turns blue, anyone know why?

[rain plank]

pool = await asyncpg.create_pool(...)
...
#later
async with pool.acquire() as connection:
    # do stuff

[weak tinsel]

thats what i did :/

i name my connections as pool hehe

but yeah , doing that gives pool closed error

[rain plank]

Are you sure you didn't do

async with pool as connection:
   ...

At all in your code?

[weak tinsel]

no

[weak tinsel]

for the relevant code no

does it affect if its present elsewhere

[rain plank]

Yes

[weak tinsel]

o

i see

[rain plank]

Because doing async with pool... automatically closes it

[weak tinsel]

ohh

i see

thanks :)

[torn sphinx]

how would I make a python script that searches through a bunch of databases on my drive if i type in a certain keyword lmao

any libraries would i need?

[true matrix]

Hi guys, I'm looking a few hours to this problem
On Raspberry Pi4, I installed:

• pip install mysql-connector-python
• co2 sensor mh-z19 (works only with python2)
  I need to insert co2-data into mysql
  when I import mysql via python terminal it is OK, but not in a script

pi@raspberrypi:~/mh-z19 $ python
Python 2.7.16 (default, Oct 10 2019, 22:02:15)
[GCC 8.3.0] on linux2
Type "help", "copyright", "credits" or "license" for more information.

import mysql.connector

exit()
pi@raspberrypi:~/mh-z19 $
pi@raspberrypi:~/mh-z19 $ sudo python co2-mysqlconnector.py
Traceback (most recent call last):
File "co2-mysqlconnector.py", line 6, in <module>
import mysql.connector
ImportError: No module named mysql.connector

[rain plank]

Try python -m pip install mysql-connector-python

[true matrix]

Thanks for the quick response!
Problem is still the same: ImportError: No module named mysql.connector

[rain plank]

Why are you using python2 anyway?

[true matrix]

co2 sensor mh-z19 (works only with python2)

https://github.com/UedaTakeyuki/mh-z19

[rain plank]

can you do python -m pip show mysql-connector-python?

[true matrix]

pi@raspberrypi:~/mh-z19 $ python -m pip show mysql-connector-python
Name: mysql-connector-python
Version: 8.0.23
Summary: MySQL driver written in Python
Home-page: http://dev.mysql.com/doc/connector-python/en/index.html
Author: Oracle and/or its affiliates
Author-email: UNKNOWN
License: GNU GPLv2 (with FOSS License Exception)
Location: /home/pi/.local/lib/python2.7/site-packages
Requires: protobuf
Required-by:

[rain plank]

Oh nevermind

Hmm

[halcyon fox]

hey, uhm, how do I enable dark theme for all of MySQL workbench, not just the code editor

because my eyes are turning into crisp

I am asking because I am on windows of course

lucky mac people got dark theme support in MySQL workbench as it follows system wide dark theme on it

[white ravine]

Hello so i started using firebase

and i want to know how i can get all the apikey authdomain

and things can i get help

nvm found it

[weak tinsel]

Or you can make a field of dates

Oops I'm late

[torn sphinx]

That’s wrong

You need to do group by if you use max function there

max is aggregate function so to use it you need to say which column to group on

I don’t know you have to decide how you want to group it

cursor = await db.execute("SELECT userid, max(time) FROM messages group by userid")
lastuser = []
row = cursor.fetchone()
lastuser.append(row[0])

Also what you are trying to do?

There might be better way to do it

That will work if time is always increasing for each row

Doesn’t matter

Also you need to add await before cursor.fetchone()

You pass a list to get member

That’s wrong

[torn sphinx]

Just integer

user_id is a list

How long you have been doing python?

So this is simple and you should know this

Maybe stop guessing and little thinking?

[scenic gale]

Hey guys I am working on a python script to delete entries from my mongodb with 100k entries if they're older than 120 days. This will probably cause close to 50k entries being deleted. Do you know if it would be better to use db.collection.deleteMany() or db.collection.remove on a for loop of collection size

[sick fiber]

   session.begin(subtransactions=True)
   session.add(__request)
   session.flush()
   id = __request.id
   session.commit()```Hello, im using sqlalchemy in my docker flask app and without any reason session.commit() stopped updating table. Requesting id works fine but updating doesnt work, no errors.

[torn sphinx]

Hi

I need a help

[charred fractal]

Help with?

[torn sphinx]

I am working with sqllite. Don't get how to form this query: List the lowest and highest salary per city, the total number of employees per city, the number of employees earning the lowest salary, the number of employees earning the highest salary, the percentage of employees earning the highest salary per city and the percentage of employees earning the lowest salary per city. This information should be shown as 1 row per city containing all the information requested.

I tried this but not works: select max(salary), min(salary), count(), count() from employees e, locations l, departments d where l.location_id=d.location_id and e.department_id=d.DEPARTMENT_ID and salary= (Select max(salary), city from employees group by city)

[charred fractal]

Try looking at this: https://www.w3schools.com/sql/

[torn sphinx]

Yeah I tried but not helped

Pls help me

[mental quiver]

Should I expect aiosqlite to be slower than sqlite? @ me when responding please.

I ask because im testing, executing the same select command 2000 times, with both, I have been getting significantly slower results from aiosqlite than sqlite. Also, sqlite is blocking, right?

[rain plank]

You mean sqlite3?

Yes it is blocking

[stuck bridge]

anyone here good with sql?

converting sql into JSON

[torn sphinx]

if i have a table with a column that's named 'foo' how do I get the name foo with a statment?

[torn sphinx]

I need help too

Some the guys that used to help here have left I think, but what is your question maybe we can help,

[scenic gale]

    client.pagesuccess.problem.deleteMany({ "problemDate": { "$lte" : n_days_ago}})
  File "/usr/local/lib/python3.8/site-packages/pymongo/collection.py", line 3445, in __call__
    raise TypeError("'Collection' object is not callable. If you meant to "
TypeError: 'Collection' object is not callable. If you meant to call the 'deleteMany' method on a 'Collection' object it is failing because no such method exists.```

Does anyone know why this error happens

deleteMany is supposed to be called on collections?

[burnt turret]

the functions in pymongo are named according to PEP8, so it's snake case

delete_many @scenic gale

Although the function is called deleteMany in the mongo shell

[stuck bridge]

hi sorry

had to take a break

just the syntax error

[keen fractal]

hi guys! can we update a view in SQL? if yes is that will affect the mother table?

[winged verge]

guys i have a question

i wanna create a website so i need a database

should i use mysql or something

[tacit acorn]

when i made my flask site i used sqlite for testing, then mysql for production

Hi all, im wondering if a db is right for my use case. I currently have millions of jsons, each with variable lists of stings. There currently in level 9 compressed zips that take up almost 400GB. Im basically wondering if a db would take up less space and/or read faster.

[onyx leaf]

python redis still dont have LPOS

[winged verge]

is blockchain about databases

[royal dagger]

If you have access you can Update view definition. It will not affect mother table. But it will affect all applications which are using the view.

[median swift]

Does anyone know with sqlite if it is possible to create primary keys with 4 digits as opposed to 1. For example:
0001 instead of 1
0012 instead of 12

[charred fractal]

well with a sql database, it is possible to make it with 4 digits, instead of 1. since I store clientids in my database, so I don't think there any reason why it wouldn't.

[median swift]

Yeah just don't know how to do it. I have had a look on the internet with no luck

[charred fractal]

!e

add = 0001 + 0012
print(add)```

[delicate field]

You are not allowed to use that command here. Please use the #bot-commands channel instead.

[charred fractal]

😦

[median swift]

why are you adding them? That is not what I need lol

I may just do them directly in python tbh

And just convert them to 4 digit

[charred fractal]

if len(number) == 4:
  number = number
elif len(number) == 3:
  number = f'0{number}'
elif len(number) == 2:
  number = f'00{number}'
elif len(number) == 1:
  number = f'000{number}'

[median swift]

Great minds think alike! Thanks!

[charred fractal]

there may be an easier way of doing that with a for loop and assigning the value to the number, if you want more numbers than just 4.

[median swift]

Yeah I am going to have fixed to 4 numbers. When it gets to 9999 (if it ever does) then it goes back to 0001

[charred fractal]

mhm

ah, well it can only be 100 in length

[median swift]

Forgive me but is this relating to my numbers lol?

[charred fractal]

well numbers can be infinity, as long as there's no max limit set by anyone.

[icy stream]

assuming number is a string, you can use number.zfill(4) to do this (zfill standing for zero fill)

cc @median swift ^

!e

number = "1"
print(number.zfill(4))

[delicate field]

@icy stream :white_check_mark: Your eval job has completed with return code 0.

0001

[charred fractal]

oh.

I see, you're a mod, lol didn't realize that until you did it now.

[median swift]

Brilliant! Thank you so much

[icy stream]

helper :P, not quite a mod

[icy stream]

happy to help :D

[restive goblet]

why is this not a solution?

[charred fractal]

you selected the first element from the sql, instead of the highest number.

[restive goblet]

but why? im doing offset N

solved it

just did n-1

[charred fractal]

try removing the offset, and see what value that gives you.

[prime nymph]

How can I create a python database

[charred fractal]

you can create a database with python, however, creating a python database is near to impossible.

[prime nymph]

OK

[mighty spindle]

"GitHub - msiemens/tinydb: TinyDB is a lightweight document oriented database optimized for your happiness :)" https://github.com/msiemens/tinydb

Tinydb is a document database written 100% in python

[burnt turret]

it stores data in json files iirc

not really meant for production

this talk about a database written in python led me to http://labs.codernity.com/codernitydb/

anyone ever tried this out before?

[sweet arrow]

How do I get the KEY of data in MongoDb

like

@client.command(aliases=['pl'])
@commands.has_permissions(manage_channels=True)
async def plugins(ctx):
    plugins = pluginData.find_one({})
    plEmbed = discord.Embed(title=f"**__Plugins for {ctx.guild.name}__**", description="A list of all the plugins available")
    for v in plugins:
        if plugins[v] == "disabled":
            plEmbed.add_field(name="`{}`".format(plugins[i]),value=f"Status: 🔴", inline=False)
        elif plugins[v] == "enabled":
            
plEmbed.add_field(name="`{}`".format(plugins[i]),value=f"Status: 🟢", inline=False)
    await ctx.send(embed=plEmbed)

Basically setting the name of the embed fields to the name of the key

[rain plank]

Error?

[charred fractal]

hence why I said it was near to impossible.

[torn sphinx]

Best way to visualise a big database and understand it?

Like 400+ table

[next basalt]

@burnt turret

[burnt turret]

yeah, can you send that code here again

[next basalt]

https://gist.github.com/anand2312/840aeb3e98c3d7dbb3db8b757c1a7ace on this website, they said find doesn't need to be awaited

[burnt turret]

find doesn't need to be awaited, but find_one does

[next basalt]

oh

[burnt turret]

so, what you're trying to do is INSERT if user doesn't exist in the db, else UPDATE

this is called an UPSERT (UPdate INsert)

you do this by passing upsert=True as a kwarg to collection.update_one

[next basalt]

i have this

[burnt turret]

and then instead of getting the previous message count and increasing, then $seting the new value, you can use $inc (increment)

so that entire thing can be condensed into one query -

await levelling.update_one(..., {"$inc": {"messages": 1}}, upsert=True)

[next basalt]

oh

[burnt turret]

if you wanted to decrement, you'd $inc with a negative value

just an fyi

[next basalt]

ok

uhm, so my rank and leaderboard commands stopped working

[burnt turret]

well a database is the best option for you to store data, so if your discord bot needs to store data, sure that's a good combination

[burnt turret]

are there any errors?

[next basalt]

nop

[burnt turret]

send your code

[next basalt]

ok

[burnt turret]

I can't say for sure, but change that bare except there

if any errors are happening, they're getting ignored silently right now

at least print the exception so you know if something goes wrong

[next basalt]

ok

[next basalt]

idk, how but the leaderboard command worked, I changed nothing, just restarted the bot

i have the same error in the rank command

@burnt turret So with motor i can send a lot of requests to MongoDB, right?

btw, everything is now working, thx!

[scenic gale]

has anyone have experience with mongoexport

2021-03-25T16:38:59.196+0000    WARNING: ignoring unsupported URI parameter 'retrywrites'
2021-03-25T16:38:59.196+0000    WARNING: ignoring unsupported URI parameter 'maxidletimems```

i'M NOT SURE WHY THIS HAPPENS

[burnt turret]

the difference between motor and pymongo is just that motor is asynchronous

[next basalt]

oh ok

thx for your help!

[burnt turret]

👍

[dusky iris]

guys

nvm

[slender atlas]

How would I go about deleting duplicate rows?
Like this:

DELETE FROM some_table WHERE some_column = some_value LIMIT COUNT(*)-1

?

I know for sure I have at least 1 row, I want to make sure the table doesn't keep duplicate rows.

Or do I have to put a select there:

DELETE FROM some_table WHERE some_column = some_value LIMIT (SELECT COUNT(*) FROM some_table WHERE some_column = some_value)-1

?

[peak flint]

Hello guys. Question on Regex and wild cards. I usually work on sql server dbs. I am working on a SQLite db now. I am trying to retrieve fields that have special chars in any position. When I input where column_name regexp ‘\b!?@.,\b’ . I tried the usual ‘%[?,.?,]%’ but that doesn’t work either. Any help much appreciated

[halcyon fox]

is it normal for SQL Server to randomly open up terminal in front of you from time to time and automatically install something then close itself?

it's happening with me, every 12~24 hours a random SQL Server terminal window opens in front of me and steals windows' focus, executes something like installing a package, then closes itself automatically, all that happens in the matter of half a second so I don't get a chance to take a screenshot

[serene pivot]

Can anyone help me out with this syntax error I'm getting here?

                query = f"""UPDATE useravatars SET avatars = CONCAT_WS(',', avatars, $1) WHERE user_id = $2"""

                await cxn.execute(query, str(avatar), user_id)

asyncpg.exceptions.IndeterminateDatatypeError: could not determine data type of parameter $1

[brazen charm]

BigInt

Ids are i64 bounds

[serene pivot]

Figured it out. I just had to cast param $1 as a text

what database you using?

oh mysql

Sorry idk anything about mysql. only postgres and sqlite

for postgres you could store it as a text, or a varchar

then split the result on selection

in postgres you could also store it as a json or jsonb datatype. might have something similar in mysql.

[sharp kindle]

does sqlite have a version control type thing

we have a bunch of reference files for integration tests that we store as a bunch of json in their own repo, and im considering switching over to a db

[charred fractal]

you still can store ints in a database just by declaring the defined column as an int, Ex: create table users(clientid bigint primary key not null);

[sharp kindle]

but its kind of important to be able to track changes to the refernece files, preferably tagged with a commit

[gray compass]

hello

I made this test table. I have set the uid col to auto increment

but i don't really know what's wrong

it added 7 instead of 1

I might just have misconfigured it

but idk

[toxic flower]

I have this dataframe with all kinds of columns

I have to index 'trial' and 'user'

which I did with

this outputs a new dataframe

with user and trial in the right place

but all the columns, except 't' 'x' and 'y'

are gone

how do I get these other columns, like 'delay' back?

[blissful knot]

Why not use git?

[sharp kindle]

does sqlite export to a format that git can easily track?

cuz i know tracking binary files with git is a nightmare

is the csv export funcitonality gonna have more or less the same ordering? or am i gonna have a millioon+ deletions and additions because of a change in order

https://stackoverflow.com/questions/33809881/whats-a-good-way-or-tool-to-version-control-a-sqlite-database-schema-only

mrhmmm

a single reference json is 60k lines lol, so it's a problem :/

updates are made in bulk, maybe just take a snap shot everytime it happens

[gray compass]

is there any way I can set up a remote mysql server so I can access the database from any computer?

idfk i'm new to databases

[charred fractal]

Yeah, it probably is possible, however it's not the most recommended, just remember to close the sql window after using the computer, otherwise someone could delete your database data.

[real bough]

alright , so a user will be given option in combobox to either pay with cash or in installments
if the user selects installments then his details will be updated in a separate Table
... but i wonder how do i do it ?

[valid barn]

I don't come with a computer science background, but really love coding in python. Could someone show me the best way to learn/understand posgresql? The docs are amazing, but a little overwhelming.

[sterile pond]

Have you set up a server on your local machine and played around with it?

Reading the docs is good but it remains abstract until you actually practise with the real thing

[valid barn]

@sterile pond Absolutely! I currently learn through an ORM, but eventually I am going to try running the queries directly too

[sterile pond]

Ahh noice

As someone who recently entered the industry, if you have the bandwidth to learn more at once, give some cloud fundamentals certs your consideration

Doing them really opened doors for me

[slender rose]

Unhandled exception in internal background task 'levelup'.
Traceback (most recent call last):
  File "/usr/local/lib/python3.6/dist-packages/discord/ext/tasks/__init__.py", line 101, in _loop
    await self.coro(*args, **kwargs)
  File "/home/palace/ranks.py", line 93, in levelup
    await db.execute("""UPDATE ranks SET experience = $1 AND level = $2 WHERE id = $3""", experience, lvl, user_id)
  File "/usr/local/lib/python3.6/dist-packages/asyncpg/connection.py", line 304, in execute
    return_status=True,
  File "/usr/local/lib/python3.6/dist-packages/asyncpg/connection.py", line 1632, in _execute
    ignore_custom_codec=ignore_custom_codec,
  File "/usr/local/lib/python3.6/dist-packages/asyncpg/connection.py", line 1655, in __execute
    ignore_custom_codec=ignore_custom_codec,
  File "/usr/local/lib/python3.6/dist-packages/asyncpg/connection.py", line 1681, in _do_execute
    ignore_custom_codec=ignore_custom_codec,
  File "/usr/local/lib/python3.6/dist-packages/asyncpg/connection.py", line 380, in _get_statement
    ignore_custom_codec=ignore_custom_codec,
  File "asyncpg/protocol/protocol.pyx", line 166, in prepare
asyncpg.exceptions.DatatypeMismatchError: column "experience" is of type bigint but expression is of type boolean
HINT:  You will need to rewrite or cast the expression.

any idea how to fix this?

[burnt turret]

experience = $1, level = $2

[sterile pond]

Presumably right now you're getting your variable set as false instead of $1

[burnt turret]

It's evaluating that expression

$1 AND level...

So yeah experience is trying to be set to the boolean result of that expression

[tropic apex]

@velvet ridge your missing a parenthesis

cur.execute("INSERT INTO login VALUES(?, ?, ?)", (username, password, current_time)<- missing a parenthesis 

that is the line that has the problem

[torn sphinx]

How to do it so if parent key is updated all child keys are updated in sql?

[burnt turret]

🤔 not very sure, i think postgres has something like CREATE TRIGGER you could use for this

https://www.postgresql.org/docs/9.1/sql-createtrigger.html

or maybe i'm misunderstanding the question

[torn sphinx]

So basically when the primary key updates of any row, it will then update the foreign keys wherever it is used?

[burnt turret]

in theory i suppose you could write a trigger like that

actually that might be a pain to set up

i think with one event in a trigger you'd be able to do stuff to only one target table and not multiple (like in the case of foreign keys), which would mean you'd be setting up multiple triggers...

🤔 maybe this isn't the best solution, sorry

[modest ledge]

Traceback (most recent call last):
  File "votes.py", line 104, in list_checking
    await db.execute("""UPDATE ranks SET experience = $1, level = $2 WHERE id = $3""", experience, lvl, user.id)
  File "/usr/local/lib/python3.6/dist-packages/asyncpg/pool.py", line 58, in call_con_method
    meth_name))
asyncpg.exceptions._base.InterfaceError: cannot call Connection.execute(): connection has been released back to the pool

why does this happen

[prisma girder]

Share more code please

[modest ledge]

async with self.pool.acquire() as db:
  for user_info in self.users.keys():
  user_id = int(user_info)
  touples = await db.fetchrow("""SELECT * FROM ranks WHERE id = $1""", user_id)
  if touples is None:
    experience = self.users[str(user_id)]["exp"]
    lvl = int(experience ** (1 / 3))
    await db.execute("""INSERT INTO ranks(id, experience, level) VALUES($1, $2, $3)""", user_id, experience, lvl)
  else:
    experience = toup["experience"] + self.users[str(user_id)]["exp"]
    lvl = int(experience ** (1 / 3))
    if lvl >= 150:
      lvl = 150
    wait db.execute("""UPDATE ranks SET experience = $1, level = $2 WHERE id = $3""", experience, lvl, user_id)

apparently the problem was in the update line

[prisma girder]

I have no idea what is wrong, never used asyncpg

[green raptor]

i want to add on field NULL values / variables

how to insert with "where"

i am using sqlite

[burnt turret]

as I had said earlier, INSERT doesn't have a WHERE clause

do you want to add a Null in the JackpotBan column of the second row?

you'd use UPDATE in that case

[green raptor]

no i want to add the text "ban" to UserId with that function

[burnt turret]

can you describe that in terms of your table?

you won't be able to add ban to userid

userid looks like it is a numeric field?

you'd want a user's id there

[green raptor]

its text

[burnt turret]

even then, you'd want the user's ID there

naming a column "UserID" and then not storing user IDs there is gonna be very misleading

[green raptor]

here i checked if user is in database with userid and it works

[burnt turret]

do you mean you want to have the text "ban" in the jackpotban column for specific users?

[green raptor]

every userid can have "ban" on column jackpotban

[burnt turret]

only ban?

[green raptor]

yes

[burnt turret]

then why even store anything there

if the user ID exists in the table, it can be implied that it is banned

[green raptor]

ik but i want to know how it works :0
so i can use the database more then only for that

[burnt turret]

alright

can you explain in terms of your table, what you're trying to do now?

do you want the jackpotban column for the second row to have the text "ban"?

making sure the next users you add to it have "ban" is just as simple as inserting the word "ban" as well the next time

you could also have set that column to have a default value of "ban" while creating the table (so if you dont specify a value, "ban" will be used)

[green raptor]

nvm i do it with only userid

but how to get the text from database as string?

this what i get

[burnt turret]

you need to await the coroutine

[green raptor]

can u tell me how to do that

[burnt turret]

await coroutine()

[green raptor]

oh wait found haha

[blissful knot]

Does anyone on here know if I can create linked lists in a Microsoft Access database using python, or they are importable from a text file? (I do not have the option to migrate the database to something that is not Access fyi)

[torn sphinx]

Is there any way to write the json seperated by lines?

[green raptor]

hey i need help to add / insert in database with 4 variables (sqlite)
Error:

Ignoring exception in command addgift:
Traceback (most recent call last):
  File "C:\Users\Jakob\AppData\Local\Packages\PythonSoftwareFoundation.Python.3.9_qbz5n2kfra8p0\LocalCache\local-packages\Python39\site-packages\discord\ext\commands\core.py", line 85, in wrapped
    ret = await coro(*args, **kwargs)
  File "W:\Pybot\QGiveaway\bot.py", line 242, in addgift
    await add_gift(number, key, plattform, howto)
  File "W:\Pybot\QGiveaway\bot.py", line 109, in add_gift
    sql.execute("INSERT INTO tbl_gifts (GiftNumber, GiftCode, Plattform, HowTo) VALUES (" + number + ", " + key + ", " + plattform + ", " + howto + ")")
sqlite3.OperationalError: no such column: STEAMKEY

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

Traceback (most recent call last):
  File "C:\Users\Jakob\AppData\Local\Packages\PythonSoftwareFoundation.Python.3.9_qbz5n2kfra8p0\LocalCache\local-packages\Python39\site-packages\discord\ext\commands\bot.py", line 902, in invoke
    await ctx.command.invoke(ctx)
  File "C:\Users\Jakob\AppData\Local\Packages\PythonSoftwareFoundation.Python.3.9_qbz5n2kfra8p0\LocalCache\local-packages\Python39\site-packages\discord\ext\commands\core.py", line 864, in invoke
    await injected(*ctx.args, **ctx.kwargs)
  File "C:\Users\Jakob\AppData\Local\Packages\PythonSoftwareFoundation.Python.3.9_qbz5n2kfra8p0\LocalCache\local-packages\Python39\site-packages\discord\ext\commands\core.py", line 94, in wrapped
    raise CommandInvokeError(exc) from exc
discord.ext.commands.errors.CommandInvokeError: Command raised an exception: OperationalError: no such column: STEAMKEY

Code:
https://media.discordapp.net/attachments/823228011477860355/825045068808323082/unknown.png

Please mention me.

[burnt turret]

I've told you multiple times now

You do not simply concatenate (add strings) together like that

You have to use the placeholder provided by the library and pass the arguments in separately

in your code that'd be

sql.execute("INSERT INTO tbl_gift (GiftNumber, GiftCode, Plattform, HowTo) VALUES(%s, %s, %s, %s)", (number, key, plattform, howto))```

https://docs.python.org/3/library/sqlite3.html I'd highly recommend you go over this tutorial

@green raptor

[harsh night]

cursor.execute(f"SELECT PlayerID FROM Logs WHERE TempbanTimeRemain < {datetime.datetime.now()}") this give me this error sqlite3.OperationalError: near "16": syntax error

its for a discord bot

[burnt turret]

This error is caused because youre using an f-string

[torn sphinx]

Error is same as what aand said before

[burnt turret]

Yep

[harsh night]

ive used f strings before and they work

[burnt turret]

They work but they're not what you're supposed to use

Third pin in this channel explains why

The doc I linked just a few messages above also does, and also tells you how to do it right

[green raptor]

ty i will check that tutorial

but why i am getting this error for ur sql

 Ignoring exception in command addgift:
Traceback (most recent call last):
  File "C:\Users\Jakob\AppData\Local\Packages\PythonSoftwareFoundation.Python.3.9_qbz5n2kfra8p0\LocalCache\local-packages\Python39\site-packages\discord\ext\commands\core.py", line 85, in wrapped
    ret = await coro(*args, **kwargs)
  File "W:\Pybot\QGiveaway\bot.py", line 242, in addgift
    await add_gift(number, key, plattform, howto)
  File "W:\Pybot\QGiveaway\bot.py", line 109, in add_gift
    sql.execute("INSERT INTO tbl_gift VALUES(GiftNumber, GiftCode, Plattform, HowTo) VALUES (%s, %s, %s, %s)", (number, key, plattform, howto))
sqlite3.OperationalError: near "VALUES": syntax error

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

Traceback (most recent call last):
  File "C:\Users\Jakob\AppData\Local\Packages\PythonSoftwareFoundation.Python.3.9_qbz5n2kfra8p0\LocalCache\local-packages\Python39\site-packages\discord\ext\commands\bot.py", line 902, in invoke
    await ctx.command.invoke(ctx)
  File "C:\Users\Jakob\AppData\Local\Packages\PythonSoftwareFoundation.Python.3.9_qbz5n2kfra8p0\LocalCache\local-packages\Python39\site-packages\discord\ext\commands\core.py", line 864, in invoke
    await injected(*ctx.args, **ctx.kwargs)
  File "C:\Users\Jakob\AppData\Local\Packages\PythonSoftwareFoundation.Python.3.9_qbz5n2kfra8p0\LocalCache\local-packages\Python39\site-packages\discord\ext\commands\core.py", line 94, in wrapped
    raise CommandInvokeError(exc) from exc
discord.ext.commands.errors.CommandInvokeError: Command raised an exception: OperationalError: near "VALUES": syntax error

[burnt turret]

Remove the VALUES from there

I'm on mobile I typed wrong

The point of what I wrote was how I'm passing the arguments in

Instead of adding strings together, I use the %s as a placeholder and then pass the arguments in separately

That is what I wanted you to infer from that

I've edited the message

[green raptor]

yes understood that

[green raptor]

Traceback (most recent call last):
  File "C:\Users\Jakob\AppData\Local\Packages\PythonSoftwareFoundation.Python.3.9_qbz5n2kfra8p0\LocalCache\local-packages\Python39\site-packages\discord\ext\commands\core.py", line 85, in wrapped
    ret = await coro(*args, **kwargs)
  File "W:\Pybot\QGiveaway\bot.py", line 242, in addgift
    await add_gift(number, key, plattform, howto)
  File "W:\Pybot\QGiveaway\bot.py", line 109, in add_gift
    sql.execute("INSERT INTO tbl_gift (GiftNumber, GiftCode, Plattform, HowTo) VALUES (%s, %s, %s, %s)", (number, key, plattform, howto))
sqlite3.OperationalError: near "%": syntax error

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

Traceback (most recent call last):
  File "C:\Users\Jakob\AppData\Local\Packages\PythonSoftwareFoundation.Python.3.9_qbz5n2kfra8p0\LocalCache\local-packages\Python39\site-packages\discord\ext\commands\bot.py", line 902, in invoke
    await ctx.command.invoke(ctx)
  File "C:\Users\Jakob\AppData\Local\Packages\PythonSoftwareFoundation.Python.3.9_qbz5n2kfra8p0\LocalCache\local-packages\Python39\site-packages\discord\ext\commands\core.py", line 864, in invoke
    await injected(*ctx.args, **ctx.kwargs)
  File "C:\Users\Jakob\AppData\Local\Packages\PythonSoftwareFoundation.Python.3.9_qbz5n2kfra8p0\LocalCache\local-packages\Python39\site-packages\discord\ext\commands\core.py", line 94, in wrapped
    raise CommandInvokeError(exc) from exc
discord.ext.commands.errors.CommandInvokeError: Command raised an exception: OperationalError: near "%": syntax error

[burnt turret]

Uhh try ? Instead of %s as the placeholder, I'm not sure which one sqlite uses

@green raptor yeah that's the issue

[green raptor]

its ?

thanks

[burnt turret]

Swap out %s with question marks

Yeah

[green raptor]

now i learned

[burnt turret]

Never use string concatenation for SQL queries

The third pin in this channel explains why that's a bad idea

[green raptor]

ik read docs etc etc, but i like learning by doing xd

[green raptor]

what do u mean

[burnt turret]

adding two strings together using + is string concatenation

String interpolation is using something like f-strings, str.format() to bring variables into your string

BOTH of these are bad ideas to be used with SQL queries

The placeholder thing I just showed you is the right way

[green raptor]

@burnt turret is causing crashes on my pc?

Cause since i did this command pycharm and other stuff crashing

[scenic quiver]

where can i find the documentation for pymongo?

[gray compass]

I wanted to make a desktop auth system

[devout girder]

MongoDB

how would i append to the categoryDisabled

so that becomes

_id: int
disabled: Object
    categoryDisabled: int
    command: "triggered"
    command: "snipe"```

[burnt turret]

https://pymongo.readthedocs.io

[scenic quiver]

oke thanks!

[limpid arch]

why does mysql installation take... forever

is it because of all the configurations that need to be done in the background?

[torn sphinx]

Is there any way to write json data by seperated lines?

json.dump(data, fp)
fp.write("\n")
``` Seems not work

[fervent finch]

@torn sphinx https://docs.python.org/3/library/json.html the indent keyword should be what you're looking for

[torn sphinx]

ok thanks

[torn sphinx]

Hi

I've often heard people recommend using SQLite for development and then switching to something else e.g. PostgresQL for deployment

Because apparently SQLite isn't suitable for a deployed web app

If that is the case then why not start straight away (in development) with your intended db e.g. Postgresql? I'm starting to discover that SQLite has a few limitations when compared to PostgresQL so I don't understand why people even use it in the first place when they are intending to switch.

[tidal field]

i need some help with sqlite

its a little too much to explain so if some1 has the time dm me

[merry nymph]

async with aiosqlite.connect(...) as db: await db.execute("INSERT INTO some_table ...") await db.commit()```

I'm thinking about adding this after my bot's on_ready but it'll just

aync with aiosqlite.connect(...) as db:
    return db```

So when I use a command that needs data persistence I'll just do

await db.execute(stuff here)```

I just wanted to tell someone about it. I'm going to start writing it. I honestly don't think it'll work but

[thorn geode]

Hello! That's using a context manger which will automatically close the connection once the code inside there is done.

I would recommend creating a function which connects to the database and returns that connection before defining it as a bot variable

See: https://github.com/mesub7/pogmas/blob/51a1c4dec78e66327c8b73cebbd4f703186c83c3/Pogmas.py#L33 and https://github.com/mesub7/pogmas/blob/51a1c4dec78e66327c8b73cebbd4f703186c83c3/Pogmas.py#L39

[merry nymph]

I'm going to read it rn, brb

That little guy reminds me of a pet project in python coding projects where you make something like that for a virtual pet

[thorn geode]

Yea it is lol

Good catch!

[autumn pebble]

Hey guys iam not sure if this is the right place to ask....but can anyone give me a good source for me to learn about sqlite or sql in particular since iam kinda lost rn...ty

[charred fractal]

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

[autumn pebble]

Tysm :)

[charred fractal]

Np

[harsh night]

how do i delete a single value from the db instead of a entine row

[charred fractal]

DROP column_name FROM table;

[rain plank]

Does that work

[charred fractal]

oh, hold up, my database is on a different local host... so the password is different?

[rain plank]

I thought it was

ALTER table DROP COLUMN column_name;

[rain plank]

Depends, did you make the password different

[charred fractal]

ah, I don't think there's a DELETE function.

[charred fractal]

no, I didn't change the password, It just sets to the default password for the local host, whatever that is.

[gray compass]

hey how do you set up a remote mysql server I wanna use it for an auth system

idk

lol

[charred fractal]

what is the default password for MySQL?

[rain plank]

Not sure 🤔

[burnt turret]

I don't think there's a default password

It asks you to set a password on installation

If you didn't it's probably just blank

[charred fractal]

Hmmm, well... apparently that's difficult, if you already created a database, and then moved to a different local host.

[burnt turret]

What do you mean by move to a different localhost

[charred fractal]

Well, let's say my database installation was created on TimeTree's Wifi Network, and I set a password there, then I moved to FluffyTree's WifiNetwork, now I use the same password, I used for TimeTree's Wifi Network, and then it says Cannot connect to localhost (10159) on FluffyTree's WifiNetwork.

[burnt turret]

The WiFi network you used while installing a database doesn't change anything really? I don't understand what's going on here

localhost is your computer

[charred fractal]

well, I used the same database password.

not sure why it can't connect.

[burnt turret]

How are you trying to connect?

[charred fractal]

through the MySQL Command Line Client

I had this issue for a little while, and then it worked, so I don't understand.

[burnt turret]

Huh

[burnt turret]

The default port for MySQL is 3306, and that message looks like it is trying to connect on 10159

Did you change the settings or something?

[charred fractal]

no.

maybe by accident.

[burnt turret]

Check if the MySQL server is running

[charred fractal]

Welp,

[stuck pumice]

Hi, can someone help me with a SQLAlchemy question? I am using a decorator to wrap all my database queries in a context manager to handle database sessions, but when I try to decorate classmethods, i can't get it to work, something about the order of the cls and session variables keeps breaking.

[valid shard]

There is something that is using the port

[charred fractal]

ah, of course, there is.

because I remember I got it working by closing every single one of my apps then re-opening mysql

[valid shard]

most probably my apache

[charred fractal]

huh?

[grand lark]

does anyone know a free mysql vendor that gives remote access

?

please ping me with your response

[valid jewel]

WHY DOES MY SQLITE3 DB KEEP LOCKING

Every time I update it and then try read the new update (from a discord bot) it stops for a bit and then says db is locked

Update: I had 3 loops constantly checking and updating the db, I commented them out and it worked, seems to for now, but I need the loops to be running, how can I solve this

[uneven crane]

hello I got a problem with pycharm, cannot install pymysql

pip is not recognized

[cosmic smelt]

using pyodbc, is there a nolock function I should be aware of when querying a sql server (not altering, appending or creating)

[autumn pebble]

Is there any difference between sql,mysql,postgresql,sql server,and sqlite.... iam to new to sql and iam so confuse mind giving me some guide...ty

[burnt turret]

SQL is the language (Structured Query Language) that you use to interact with a relational database.

The rest of the names you listed (MySQL, PostgreSQL, ...) Are all databases

So you'd use SQL to interact with any of them (they all have some quirks, but if you know SQL you can basically use any of them)

https://sqlbolt.com is a good tutorial to learn SQL

After going through that you, sqlite would probably be the easiest to get started with as it won't need any installation

[tidal field]

how do i add +(number) to every integer from a column at the same time

sqlite

[civic lodge]

await cursor.execute("Select CardDatabase.Name, CardDatabase.Rating,CardDatabase.CardType,CardDatabase.Value FROM CardOwnership INNER JOIN CardDatabase ON CardOwnership.CardID = CardDatabase.CardID WHERE CardOwnership.DiscordID = ? ORDER BY CardDatabase.Rating DESC LIMIT ?,?",
                (userID,self.startValue,self.stopValue,))
                data = await cursor.fetchall()

This works for select.

How do I conver this to Delete

?

I'm using asqlite - an sqlite3 wrapper for async

[thorn geode]

You can use DELETE

[civic lodge]

yes i know i can use delete

but that does not work

[thorn geode]

Did you commit?

[tidal field]

how do i add +(number) to every integer from a column at the same time in sqlite