databases

[torn sphinx]

https://en.wikipedia.org/wiki/Prepared_statement

[wet ravine]

oh i see what i did is prone to sql injection

risky af

[torn sphinx]

I'm using python to write to my sqlite database but I'm having trouble altering one of the columns. I've googled "How to edit sqlite column datatype in python" and I'm only getting things written to execute in Sqlite, not python. So I changed my search and started looking up how to delete a column so that I could remake it afterwards but I don't have access to my database through the browser so I'm not sure what to do.

(I'm trying to edit my column so that it can take a value like 1234-1245-1256 and not write it in as -1267)

[small gust]

In sqlalchemy, is there a way to limit the number of rows created? I have a caching table I'm making and I only want N number of keys, and I want new keys to bump off the old keys

So max 5 of key “abc” and max 5 of key “xyz” etc

[torn sphinx]

is there a way to set access control at column level

[rough hearth]

Looks like I'm going to be undertaking a project that will require a lot of one-to-one or one-to-many relationships between strings, so I presumably won't need an ORM or anything fancy like that. But sqlalchemy is the only database tool I've used.

I'll also want to be able to cache recent lookups, but maybe some databases or database libraries do this internally?

[small gust]

If it's all strings, I'd recommend Elasticsearch

[rough hearth]

Looks like there's a paid component. I'm required to make this as an open-source project.

[vivid wagon]

in my database, column messageID contains integers. Assuming that p.message_id always returns an integer, will this line of code always delete all rows from the database where messageID is equal to p.message_id

cursor.execute("DELETE FROM reactionroles WHERE messageID = :id", {'id': p.message_id})

this is using sqlite btw

[grave carbon]

wait I saw a video that said if you do %s %s %s for your vars etc that it prevents SQL injections

[brazen charm]

no

well

yes and no

it depends on your connector

SQLite is ?

[grave carbon]

I'm using postgresql psycopg2

[brazen charm]

psycopg2 is some weird fuckery of a system using %2 but also {value} string formatting and doing weirdness with that

[grave carbon]

is there a better library

[brazen charm]

actual postgres uses $1, $2 etc... for placements

asyncpg also uses the $1, $2 system for formatting

[grave carbon]

https://www.youtube.com/watch?v=2PDkXviEMD0&t=792s

Thats what this guy said to do to prevent sql injections

I also cant find many good tutorials on using psycopg2

why it is so hard to use python with postgresql I was expecting pythonic interface but its just passing direct Strings as SQL code which feels wrong to me 😄

i.e. a function like
cursor.insert(tablename, ([list, of, columns]), [(list), (of), (tuples), (of), (values)]) to insert values etc

dont understand why need to do cursor.execute(""" DIRECT SQL CODE WRITTEN IN STRING FORMAT """)

[boreal kernel]

You can do that sort of thing with SQLAlchemy, has a bit of a learning curve though

[grave carbon]

What libraries would you recommend to interface with Postgresql in python? Is SQLAlchemy stand-alone or does it complement another interface?

[boreal kernel]

SQLAlchemy is actually an ORM that uses psycopg2

[brazen charm]

psycopg2 is pretty much it

unless youre using async then asyncpg

[grave carbon]

Is asyncpg more efficient than psycopg2

[brazen charm]

i mean only if its for a async system lol

psycopg2's system is a bit odd tbh

it uses normal string formatting for your place holders but then converts them to normal postgres place holders

[grave carbon]

Just feels like psycopg2 is so resistant to usability that I may as well use json stored in a file for anything but huge databases

[boreal kernel]

https://docs.sqlalchemy.org/en/13/core/connections.html#using-transactions

This the kind of thing you're looking for?

If you're really worried about efficiency then SQLAlchemy's ORM isn't going to help much, but I feel like it's not worth worrying about until it's actually a problem

[grave carbon]

I'm less worried about efficiency than I am needless complicating of altering the database 😄

Like if a string value gets entered with " string " instead of ' string ' then the code fails even so I dont like that I have to do all these stupid string formatting measures to enter a simple value into a field

I'd have hoped that the leading library to do these queries would handle these kinds of things

[boreal kernel]

I mean, there's only so much you can do to make SQL* languages fun to work with

[grave carbon]

Like I tried creating a function to create my query string for me it just seems fraught with needless complication

    insertQueryString = "INSERT INTO accrued_balances (ticker, balance) VALUES "
    updateCount = 0
    for update in updatesList:
        if updateCount == len(updatesList) - 1:
            insertQueryString = insertQueryString + "(\'" + str(update[0]) + "\', \'" + str(update[1]) + "\');"
        else:
            insertQueryString = insertQueryString + "(\'" + str(update[0]) + "\', \'" + str(update[1]) + "\'),"
        updateCount += 1```
and even this seems to be vulnerable to sql injection depending on who controls the data going into this func

Thats just so I can dynamically insert a variable amount of data rows

It gets a lot more complicated if I want to check if key exists, if it does, UPDATE the row instead

and to do that I cant even concatenate the inserts together I have to try: except: every insert query individually and then update if failed

But at this point I'm just whining 😄

I'll checkout SQLAlchemy and see if I can get what I want there and hope I dont leave myself vulnerable to injections as well 😛

[brazen charm]

Tbf

By that point

I'd just make use o postgres' function system

And do a function in pgsql that does all of that

[boreal kernel]

When using SQLAlchemy's Enum type with native_enum and create_constraint set to False, will SQLA still raise an exception on the ORM side for passing a value that is not in the provided Enum class?

[narrow niche]

@maiden dew or anyone else who's worked with snowflake, think you can offer some advice?

i condensed my question into an SO post

https://stackoverflow.com/questions/62076203/snowflake-sql-udf-returns-unknown-user-defined-function-error

[vivid wagon]

in my database made using SQLite, column messageID contains integers. Assuming that p.message_id always returns an integer, will this line of code always delete all rows from the database where messageID is equal to p.message_id?

cursor.execute("DELETE FROM reactionroles WHERE messageID = :id", {'id': p.message_id})

[vivid wagon]

jesus didn't realize this channel is so empty

[runic pilot]

yes, there's no limit on the number it deletes

it deletes all that match

[vivid wagon]

ok tysm

[past widget]

    db = sqlite3.connect('main.sqlite')
    cursor = db.cursor()
    cursor.execute(f"SELECT status FROM main")
    result = cursor.fetchone()
    sql = ("UPDATE main SET status = True")
    cursor.execute(sql)
    db.commit()
    cursor.close()

Guys, am I even doing this right? Because nothing is happening

And this all what I have in the db

a TXT row called status

in a table called main

[brazen charm]

'True' perhaps

[past widget]

well

db = sqlite3.connect('main.sqlite')
cursor = db.cursor()
rows = [('True'),]
cursor.execute('insert into main (status) values (?)', (rows))
db.commit()
cursor.close()

I did this

but nothing would happpen

[scarlet hull]

@past widget INSERT INTO and VALUES

[toxic rune]

Have you tried passing rows without the ()?

[candid niche]

Hello! is there a way that I can open up the db browser and have my databse already in it? I'm looking to make a way that you can view it without it printing in the terminal so you can see it more clearly

[grim lotus]

Is postresql gud ?

I m just starting DBs

[upbeat lily]

yes

[grim lotus]

How do i start then ?

I have no idea

Later i want to impliment it into

Discord bot

[celest blaze]

I'd start with sqlite since it's trivial to set up

you can learn the basics of tables, queries, how to interact with a db via python

once you're comfortable with that, then try out postgresql

there are of course differences between the two, but I think sqlite is valuable because it's so easy to start with

[grim lotus]

Ok

Thanks

[rose plank]

trying to upload a file into firebase with pyrebase in flask through a form but it's not working
html

            <input type="file" name="cover">

goes to a route with this:

        file = request.form['cover']
        newFile = FileContents(name=file.filename, data=file.read())
        name = (f'/{book}_{author.split()[0]}_{str(book_id)}.jpeg').lower()
        storage.child(name).put(file)

and i get this error for file = request.form['cover']

KeyError: 'cover'

could anyone help with this

[pearl heath]

hey I am getting id already exists and I have no db file, for testing it is all in memory. This is for SQLAlchemy

I have database.metadata.clear() before the model declarations and __table_args__ = {'extend_existing': True} in the models just in case and it is still saying the entries exist
The error is :

  File "/home/moop/.local/lib/python3.6/site-packages/sqlalchemy/engine/default.py", line 590, in do_execute
    cursor.execute(statement, parameters)
sqlite3.OperationalError: index ix_Compound_id already exists

[chrome cloak]

Hi, just a quick question. I was having a problem earlier with making two scripts "talk" to each other and my first hunch was to have script1 write to a txt and script2 would read that txt. But two scripts accessing the same file simultaneously would not work, so I'll solve it later with threading. But I just had an idea: Could the use of a data base solve that problem by having script1 update the database and script2 just sending constant queries?

[celest blaze]

yes.

however: with skill and luck you can get a text file to work, too

[grand estuary]

Hi, im mainly looking for advice; I have created an object property bag like system where i can define an object dynamically but it is intended for eg. products that have a large variety of properties. But should I use it for the likes of roles or would it be better to define it as I usually would.

[chrome cloak]

however: with skill and luck you can get a text file to work, too
@celest blaze Teach me!

just kidding XD, but if you have the time though, I would love to know how

[celest blaze]

well it depends on what you want to communicate and how often.

But you can have one script write out the file in one go

and the other script read the file in one go

as long as they don't need to communicate too often, this should work fine

[chrome cloak]

I'm doing a project that gets data from an mqtt broker (script1) and then I want to send it to the GUI (script2)

[celest blaze]

that means nothing to me

I mean I have a dim idea of what mqtt is

[chrome cloak]

ah yeah, but that would require one script waiting for the other to finish

[celest blaze]

but it doesn't tell me how much data you're communicating, nor how often

[chrome cloak]

well the server sends me data every second (more or less) with as many points as possible (more than a 1000 probably)

[celest blaze]

that might be a tad frequent for this technique, but I'd try it anyway: ```py
import json
import os
import tempfile
import time

def atomically_write_data(data, destination_file_name):
with tempfile.NamedTemporaryFile(mode="w", delete=False) as outf:
json.dump(data, outf)
os.rename(outf.name, destination_file_name)
print(f"Wrote {destination_file_name}")

while True:
some_example_data = {
"this": ["that", "the other"],
"when": time.time()
}
atomically_write_data(some_example_data, "/tmp/shared-data-file")
time.sleep(1)

reader.py

import json

import time

while True:

with open("/tmp/shared-data-file") as inf:

print(json.load(inf))

time.sleep(3)

the first bit is the writer; the second, commented-out, bit is the reader

if the data were huge, and your disk was slow, so that the writing took more than a second, you'd be screwed. But it'll probably be hundreds or thousands of times faster than that, so it'll probably work

you may have to fiddle the call to NamedTemporaryFile to ensure that the temp file is on the same "file system" as the destination, or else the rename will fail.

It'd be nice if there were a built-in way to do this, but I don't know of one

[chrome cloak]

wow, thank you! I'll try it as soon as possible

[finite lynx]

Using an sqlite database, if I have an Integer type row, do decimal nunbers work with it?

[celest blaze]

try it and see

I dimly recall that sqlite doesn't actually apply types to columns

like, everything is a string, or something

[finite lynx]

Well I dont know if it because how the form sent it or

As this is the error, sorry for the way I took the photo lol

[celest blaze]

https://sqlite.org/quirks.html#flexible_typing

that error seems as likely to be about sqlalchemy as to be about sqlite

have you followed the link in the error?

[finite lynx]

Not yet gonna look

InterfaceError

Is in the top

[grim lotus]

@celest blaze shouldn't i use json ?

As it is more begginer friendly

[finite lynx]

Sqlachemy seems to have a Decimal type, so I guess it could be it denying the ability

Wait hmmm if I use migrations, is it possible for me to chnage out the type in SQLAlchemy of the row?

[celest blaze]

@grim lotus JSON is good but I don't know what you want to use it for. If you scroll back a few messages, you'll see an example where I used it

[grim lotus]

Ohh

Do you anything about sql lang or something i wrote a dummy or skeleton code

[celest blaze]

that wasn't English so I didn't understand it 🙂

[grim lotus]

Which ,?

[celest blaze]

Do you anything about sql lang or something i wrote a dummy or skeleton code
@grim lotus

[grim lotus]

I wrote a dummy code ..... For postresql

[celest blaze]

it had some English words in it though

ok

[grim lotus]

Dummy in the skeleton code

Which clears basic concept

[celest blaze]

ok

"clears"? Do you mean, you've written something simple that uses postgresql, so you're confident you understand the basics?

[grim lotus]

Yes

[celest blaze]

if so, then great -- keep using it

postgresql works well and has good documentation.

[grim lotus]

No i wanted to ask you do you have knowledge about that ?

[celest blaze]

administering the database can be a pain, but that's true for any "real" database

oh not really

I've played with postgres but it was long ago; you might know more than I do at this point

[grim lotus]

ouh

Ohh

Thanks

[dawn pulsar]

What’s the most cpu minimal database for a few small tables?

Like base cpu usage being low, MariaDB was a bit much

[minor ruin]

few small tables should be CPU low in all database servers

if you were having serious CPU issues, sounds like SQL queries that were terribly optimized

[unreal oriole]

SPACEX LAUNCHES ROCKET FIRST MANNED MISSION FROM A PRIVATE COMPANY

[minor ruin]

try one of Off topic rooms

[dawn pulsar]

@minor ruin I had a GCP that was at like 90% coy usage after I started a MariaDB

[minor ruin]

Ok, try SQLite then

[noble lava]

So I was learning sqlite3, and this is my code and the error:

data = sqlite3.connect("/storage/emulated/0/Documents/Discord Bot/data.db")
c = data.cursor()
print(c.execute("SELECT * FROM test"))

Error: says that file is not database or encrypted

---

Mention me when you can tell me my problem.

[sick nacelle]

why is this happening]

[obtuse stump]

is there an easy way to migrate data from mysql to postgresql
just found out i shoudnt be using mysql

[carmine falcon]

pgloader maybe

[neon marten]

@obtuse stump I think there is some code on github. I have the code for mongodb to postgresql

but I have seen some python code on the internet for that.

[grave carbon]

I'd just make use o postgres' function system
@brazen charm
Where can I learn about that and is the func stored in the postgres server or is it it's own script?

[brazen charm]

@grave carbon https://www.tutorialspoint.com/postgresql/postgresql_functions.htm

[noble lava]

So I was learning sqlite3, and this is my code and the error:

data = sqlite3.connect("/storage/emulated/0/Documents/Discord Bot/data.db")
c = data.cursor()
print(c.execute("SELECT * FROM test"))```

Error: says that file is not database or encrypted

---

Mention me when you can tell me my problem.

[torn sphinx]

@noble lava you need to fetch it using fetchone() / fetchmany() / fetchall() using your c there, and then you can print it

!d sqlite3.Cursor.fetchone

[delicate field]

`sqlite3.Cursor.fetchone`

fetchone()```
Fetches the next row of a query result set, returning a single sequence, or [`None`](constants.html#None "None") when no more data is available.

[torn sphinx]

@noble lava i suggest learn basic sqlite from tutorials first

at first, your .db file and .py file should in same dir for ease.

then, you must write down it like that:

import sqlite3 as sql
con = sql.Connect("test.db")
cur = con.Cursor()
data = cur.fetchall()

query = ("""SELECT * FROM yourtablename)
ans = cur.execute(query)
for i in ans:
print(i)

[noble lava]

@torn sphinx error: ```Traceback (most recent call last):
File "/data/user/0/ru.iiec.pydroid3/files/accomp_files/iiec_run/iiec_run.py", line 31, in <module>
start(fakepyfile,mainpyfile)
File "/data/user/0/ru.iiec.pydroid3/files/accomp_files/iiec_run/iiec_run.py", line 30, in start
exec(open(mainpyfile).read(), main.dict)
File "<string>", line 8, in <module>
sqlite3.OperationalError: table test already exists

[Program finished]```

And new code: ```import sqlite3
conn = sqlite3.connect("data.db")
c = conn.cursor()
data = c.fetchall
c.execute("""CREATE TABLE test (
UserId varchar(255),
Balance int,
Wallet int);""")
c.execute("""INSERT INTO test (UserId, Balance, Wallet)
VALUES ("716445484901859338",0,0);""")
ans = c.execute("SELECT UserId FROM test;")
for i in ans:
print(i)

[torn sphinx]

yes 😄

delete your test.db and run program again 😄 it is so clichee error about sql :d

[noble lava]

Oh

Lol

[torn sphinx]

that s why i am saying python's db access is weak

[noble lava]

Ah

How will I use this for a bot if I have to delete the file after edit?

[torn sphinx]

my motto is : what you want to do, use sth for it which fits the best 😄

you want speed: learn C
you want hardware access: C
you want db access: C
you want develop web thing: JS
you playing with data, ai : Python 😄

[noble lava]

Wait. I see my problem.

[torn sphinx]

@noble lava i dont know so much about BOT systems 😄 it is real time app

[noble lava]

It's trying to create a new table every time I run it.

[torn sphinx]

• ATTENTION : ""Use CREATE TABLE AT ONCE EVERY .PY FİLE"

[noble lava]

Um

Ok

Can I write discord bots using C?

[torn sphinx]

i want to research about it 😄

[noble lava]

Cuz I don't understand how to access data using json

So I went to SQL

And sqlite3

[torn sphinx]

but i want to say that for realtime apps like chat, or ai or web apps u should use a suitable frameworks like .js links

at first, if you want to access a SQL db with py you can use sqlite

but you want to access a NOSQL DB like (Mongo) you can user .json file format

[noble lava]

I was making global currency for my bot and they suggested postgres or sqlite3.

[torn sphinx]

yes that is best for it

[noble lava]

But I can learn JSON too.

[torn sphinx]

unf, yu shuld 😄

[noble lava]

I already understand JS.

I know the fundamentals.

And some random stuff from an app.

[torn sphinx]

you want to develop app about real time stuff like chatbot, or instagram

?

[noble lava]

No

Discord bot

[torn sphinx]

which purpose ?

what is your goal ?

[noble lava]

Fun and management of one single server

Should I use discord.js or discord.py?

[shrewd merlin]

ı have a json file like this

{
"682670645653667923": "",
"268319011845046272": "12"
}

and ı want to take 12 how can ı do ?

[noble lava]

I don't think that's a database question.

Oof

That's more of a json library question.

[shrewd merlin]

ı cant look json channel

[torn sphinx]

@noble lava i dont know anything about that modules or libs

@shrewd merlin look here 😄

json works like arrays

"key":"value"

if you want to write a file named a.json like :

a.json ------

obj = {"key": "value", "key1": "value1"} ...
ans = obj[0]
print (ans)

value

it shows value if you want to access "12" you use obj[1]

k ?

😄

[shrewd merlin]

ok thanks ı will try

[torn sphinx]

it does not works search about "json access key value"

[shrewd merlin]

hikmet türkçe yazabilirsin kanka 😄

[torn sphinx]

alışkanlık :d

[shrewd merlin]

bi özele gelebilirmisin *

[torn sphinx]

geliyim

[torn sphinx]

where are the sqlite3 databases stored?
tried deleting the .db file in the same directory but it still says theres a db

[outer vault]

@shrewd merlin @torn sphinx could we keep this server in english?

[torn sphinx]

k, sry

he was my citizen 😄

cuz that

but wont be again

[vocal moon]

OSError: Multiple exceptions: [Errno 111] Connect call failed ('::1', 5432, 0, 0), [Errno 111] Connect call failed (' 127.0.0.1', 5432) Why do I get this when trying to connect to my psql db?

I'm trying to connect from my server

[torn sphinx]

@vocal moon can i see your code snippet dude

[vocal moon]

wait would it be because i'm not hosting the db on the same machine?

async def create_db_pool():
    bot.pg_con = await asyncpg.create_pool(database="SCPF: REVIVAL", user="postgres", password="nosir")

[torn sphinx]

bugs generally raises from syntax errors

[vocal moon]

it works fine when i run it on my local machine

when i run the code on my debian server it raises the error

[torn sphinx]

hmm

did you import os lib

[vocal moon]

uh no

shall I?

[torn sphinx]

maybe it helps

it was like os = unix that

maybe it occurs from there

[vocal moon]

after I import it do I need to do anything else

[torn sphinx]

yes

import os
if os == win:

stuff

else:

stuff

you should write it at first line

[vocal moon]

my os is not windows rn

tho

[torn sphinx]

not same like it, but it is similar to this. just for telling method (how can i do it)

for more: search "python os module"

[vocal moon]

i dont think its because of that I think it's because 127.0.0.1 is a local ip and im not hosting on a local machine

[regal dagger]

hello i have a doub t

when is fetch and fetchrow used?

[regal dagger]

i need help with pgadmin4

how do i edit data in a row

[torn sphinx]

@vocal moon accessing a remote db is requires another libs

[vocal moon]

Which ones?

[torn sphinx]

for all, using a local file instead of remote server or site

[vocal moon]

I created a local dB but it says Auth didn't work

[torn sphinx]

i heard aiohttp, it not that urllib like libs

[regal dagger]

hello

how do i edit data with pgadmin

[torn sphinx]

are your code is like that : con = sql.connect(yourdir/"test.db")

@regal dagger idk about pgadmin

[regal dagger]

hmm

[torn sphinx]

for playing db with web i suggest js or php

[somber hatch]

how can i see if something isnt there in mongodb, like im lookinf for "Thing" but thing dosnt exist

[pale elbow]

am i doing something wrong with asyncpg?
fetch(query, *args, timeout=None) is the func def for asyncpg.fetch, i have

async def fetch(self, conn, query, *values):
        if len(values) > 0:
            data = await conn.fetch(query, values)
``` but i get the error: `invalid input for query argument $1: (int,) (an integer is required) (got type tuple)`

and the query is

            SELECT words 
            FROM demon_words
            WHERE guild_id = $1
        '''
    data = await self.db_handler.fetchval_aux(self.db, query, ctx.guild.id)

sorry my code was a bit confusing

my async def function is in my db handler class

and the conn.fetch is on the db connection

just very confused as to why it cant handle *args being passed when api docs say so

[torn sphinx]

you cant query nosql db with sql query

[pale elbow]

(i know i could skip the db handler step and just pass arguments normally, which i did before, but my ogal was a general function for everything my discord bot does to log etc)

wym? the queries work fine

[torn sphinx]

yu r getting error input arg

[pale elbow]

yes, with the arguments, not the query

if i change self.db_handler.fetchval to self.db.fetchval it works

[torn sphinx]

if yu want to access your mongodb element (it works as json)

[pale elbow]

its postgres

[torn sphinx]

sry, i confused users 😄

[pale elbow]

ah

[torn sphinx]

wish help 😦

[pale elbow]

i solved it: my issue was that i tried to pass the tuple into an *args that converted it into a tuple(tuple,)

had to unpack

[halcyon patio]

Anyone have an idea about an ideal way to store a trie into a database

[lucid wharf]

pymongo.errors.InvalidOperation: cannot set options after executing query

helppu

[dawn pulsar]

If I order by time, for example, how would I get a rows position? E.g. select column1, column2, time, POSITION_OF_ROW from table order by time

John, Smith, 10/10/2020, 55

55th row in the list

[cinder yew]

Hey guys id really appreciate if someone could help me with my SQLAlchemy project.

I have two tables, one with employees and one with machines (Drill, Hammer, ...). For every employee I want to have a list with machines that he can handle (employee with id 32 can handle a hammer and a saw).
I was wondering what was the best way to realize this since the way I thought of (creating a column in the employee table for a list of machines) seems to be not right.

[minor ruin]

third table

authorization which has employeeid, machineid

[cinder yew]

okay and then for every relation between employee and machine one entry in the table? so for one employee with six machines i would have sic entries in the table?

[minor ruin]

yep

[cinder yew]

alright thanks a lot 👍

[minor ruin]

reason for this is lets you answer both questions easy

which machines can employee handle but which employees can handle a machine

[runic pilot]

This pattern is called a many to many or a join table if you want to look it up

[vocal moon]

pythonbotsgig@lazz-scp:~$ su - postgres
Password: 
su: Authentication failure

THe password is correct tho

The*

any ideas?

[runic pilot]

#unix might be able to help better

[vocal moon]

ok

[neon marten]

@vocal moon try sudo su - postgres

[torn sphinx]

i have a classmate named naveen

[neon marten]

well that's not me though, I teach the class not a student anymore 🙂

[torn sphinx]

cool! 🙂

[vital belfry]

What is the best / cheapest postgres SQL you can get online?

[brazen charm]

postgres

lol

[vital belfry]

I meant like hosting for it

lol

[brazen charm]

just buy a cheap vps and setup postgres on that

[vital belfry]

thanks

[torn sphinx]

Anyone know about Postgre?

And the connection with Python?

[ionic tapir]

!ask

[delicate field]

Asking good questions will yield a much higher chance of a quick response:

• Don't ask to ask your question, just go ahead and tell us your problem.
• Don't ask if anyone is knowledgeable in some area, filtering serves no purpose.
• Try to solve the problem on your own first, we're not going to write code for you.
• Show us the code you've tried and any errors or unexpected results it's giving.
• Be patient while we're helping you.

You can find a much more detailed explanation on our website.

[raw geyser]

@torn sphinx hey , ya whats up

i personally prefer using sqlite and db browser ui

pretty easy to connect using sqlite3 pip

[kindred python]

I use postgres because I like elephants

I'm a simple man

[celest blaze]

I see an elephant, I upvote

[last yew]

I'm having a hell of a time connecting flask-mongoengine to mongodb atlas. I keep getting this:

Any advice

[halcyon patio]

make the cluster closer to where your app runs

[last yew]

config.py ```MONGODB_HOST = "mongodb+srv://<user>:<password>@cluster0-9xx0c.mongodb.net/<db><redacted>"

@halcyon patio Wdym?

[halcyon patio]

this might be bad advice but you should choose a cluster location closed to where you are running your web app

[last yew]

you mean on the machine?

[halcyon patio]

yeah

the other reason might be

that you are querying a large amount of data and the database is taking a long time to respond

[last yew]

I don't think I'm querying any data 🤣 FIrst time I'm connecting

[rich trout]

@halcyon patio why do you want to store a trie in a database? what's the goal?

[halcyon patio]

there's not much of a goal, I'm just curious to how others would approach the problem

[raw geyser]

anyone know what the sql is for replacing newly loaded records over already existing records based on 3 PK

[merry mirage]

Can anyone connect to mongodb via mongodb+srv URI with pymongo? I don't understand why this is still an issue. I get pymongo.errors.ConfigurationError: The DNS response does not contain an answer to the question: _mongodb._tcp.XXX.mongodb.net. IN SRV

[dawn pulsar]

How would I find the position when I sort data?

E.g. "SELECT name, duration, time, ROW_POSITION FROM table WHERE name = %s ORDER BY time DESC LIMIT 1"
Or "SELECT name, duration, time, ROW_POSITION FROM table WHERE name = %s ORDER BY duration DESC LIMIT 1"

John, 15150, 01/06/2020 5(th)
Steve, 65122, 01/06/2020 51(st)

(Please ping)

[pale elbow]

is there any way to handle duplicates with asyncpgs copy_records_to_table?

that does not involve fetching all the primary key ids from table beforehand

[split wing]

I have a rather bullshit homework regarding some easy SQL Queries, that anyone with base knowledge could achieve. However, the stupid restrictions are: projection, selection, cartesian product / join & set operators; so I may not use count() or any other function that makes it easier.
Does anyone know how I can easily select certain categorical ID's that are two times in a table with these restrictions? I did not really work with set operators but I think these are the solution?

The task is to select all PIDs that occur multiple times (so everything but 1)

[torn sphinx]

can someone help me with postgreSQL??

[vocal moon]

I'm running ALTER TABLE pubic.warnings DROP warning_id in my psql command line but it is doing nothing

[merry mirage]

Pymongo issue here
count = mongo.db.find(query).sort([('date', -1)]).limit(1000).count() this statement returns 25200 as count. How could this return 25k even there is .limit(1000)?

[clever topaz]

@vocal moon Check your spelling. It's incorrect.

@merry mirage Try limiting after find rather than sort.

[vocal moon]

oh god pubic

sorry

[clever topaz]

@vocal moon Wasn't 100% it wasn't a prank.

[merry mirage]

@clever topaz Thanks, but it does the same thing I suppose. https://stackoverflow.com/a/36311154/6402099

[clever topaz]

Did you try it?

[merry mirage]

Yep

[clever topaz]

@merry mirage https://stackoverflow.com/a/29605046 ?

Bit weird of (py)mongodb to work like that.

I guess they apply as separate queries.

[merry mirage]

🤷‍♂️ I think so. I expect it should be <= limit amount. But im happy it just cost me some time, not damaged anything by misusing.

[clever topaz]

Worked?

[lucid haven]

hey im trying to make an id column which auto increments by 1

whats wrong with this

ALTER TABLE info ADD id INT IDENTITY(1,1);

Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IDENTITY(1,1)' at line 1

[merry mirage]

I didn't try it, but I wonder too.

[clever topaz]

@merry mirage Wonder what?

[lucid haven]

anyone know whats wrong with mine? :B

[rocky edge]

Hey, guys. If you ever wanted to try out InterSystems DB you can try using my app 🙂
https://community.intersystems.com/post/how-i-added-objectscript-jupyter-notebooks

[lucid haven]

Hi

i added this to my table

ALTER TABLE info ADD id INT AUTO_INCREMENT PRIMARY KEY

but how do i get it to actually increment

im inserting other data into the other columns correctly but it keeps giving me an error

Operand should contain 1 column(s)

[pearl heath]

with flask

lookup_result  = Compound.query.filter_by(cid=entity).first()

Will return None if nothing is found?

[tacit pike]

Бля пидорасы

[chilly lintel]

я нет я тут случайно

[tacit pike]

Сука блть

[deft badge]

@chilly lintel @tacit pike please keep things in English, on-topic and drop the cussing.

[outer epoch]

final_channel = self.collection.find_one({"guild_id": member.guild.id})
    for x in final_channel:
      data = x["ch_id"]```


error is : ``data = x["ch_id"]
TypeError: string indices must be integers``

[cinder yew]

Hi guys I've got a rather logical problem:
I have a database in SQLAlchemy with the tables Workshop, Machine, Employee. An employee can work on a machine and the machine stands in a specific workshop.
Now I want to create some kind of schedule for every calenderday where I assign (for that specific day) every machine to a workshop (they are portable) and for every machine one or more employees that operate the machine.

My plan was to create a table where I have one column for the date, one for the workshop, a relation (one to many) to the machines and then something like a nested relation (?) from every single machine to an employee (but only for that specific day).

My question is: Is there a better/more elegant way to do it and if not how do i create such a "nested relation"?

[runic pilot]

a lot of database design questions depend on the queries you'll be running to answer questions about your data (the access pattern)- what kinds of questions will you be asking your database?

[cinder yew]

when planned I want to know:

• is every employee and machine planned
• which machines/ employees are in workshop_xy
• which employee operates machine_xy

and i want to ask what employee_2 is doing tomorrow

[runic pilot]

ok, and do these tables have Big Data ™️ ?

[cinder yew]

not really, there are like 5 workshops, 200 employees and 50 machines

[runic pilot]

ok so ultimately, that tells me that the db structure won't have much impact on query performance

so I might suggest a "machine_assignments" table

date, machine_id, workshop_id, employee_id

similar to your suggestion, but taking it one step further, since it's almost like a 3-way join

[cinder yew]

wow that's a great suggestion, didnt thought about that

i guess for every machine there has to be a single entry in the table but if there were multiple employees per machine a one-to-many relation would be possible am i right?

[runic pilot]

well we didn't define a unique constraint anywhere, so that'd be allowed with our current suggested schema

for example, if we wanted to make sure the machine didn't move throughout the day we might want a constraint on (date, machine_id, workshop_id)

er, sorry that's not right 🤔

[cinder yew]

alright that already helps me a lot, just a last question: does the querying get any faster, when i kind of "disable" (but not delete) all entries with dates that are in the past or would you say it doesnt matter for a time of like 5 years?

[runic pilot]

5 workshops * 200 employees * 50 machines * 365 days * 5 years = 91,250,000

yeah, you'd probably want some indices on your table there to speed up queries on that

on a join table, it's very common to index all columns that refer to another table's ID

and on an event table (which is sort of what this is), it's very common to index the date column

of course, you can speed up queries even more by creating "tuple" indices, which are used when querying with multiple parameters- e.g. an (date, employee_id) index would be able to quickly answer the question "What machines and workshops was X employee using from D1 to D2 dates?"

though I'm not sure that's practically any different than using 2 indices when querying ¯_(ツ)_/¯

[cinder yew]

okay, that really made my day, i couldnt stop thinking about it but didnt found a solution. Thank you so much for your help, sir. I really appreciate it!

[runic pilot]

happy coding!

[raw stag]

hey guys. im using pymongo and im relatively new to it. i have a question that seems kinda simple but i cant find a straight answer for it.

is there a way to safeguard a collection from having null values inserted in it???
like maybe something involving validate? like, if the value is null, i dont want the whole document dropped, just that particular property

any help or advice would be super appreciated 😅
suuuuuper exhausted after a full day of chasing this problem

Any responders, if you can, please @ me so i dont miss you! thank you!

[ripe helm]

How do i connect to a database with a different file path with sqlite3?

say folder1 has folder2 and the db

and folder2 has the py project

would it be like sqlite3.connect('./hierarchy.db')?

[cosmic stump]

Use os chdir

[ripe helm]

do i have to?

[celest blaze]

no

you could spell out the "complete" name of the file, like sqlite3.connect('/home/fred/stuff/hierarchy.db')

[ripe helm]

@celest blaze how would i use a relative path?

[celest blaze]

kinda how you're already doing it, except accurately 🙂 Since I don't know your directory layout, I can't tell you any more

you can also do import pathlib; here = pathlib.Path(__file__).resolve() to find the directory where your code is, and then navigate from there (with ../ and stuff) to where the database is

maybe it's in the same directory

[slender seal]

Is there a way to re-initialize a dropped Postgresql table? Using flask and doing flask db upgrade complains that the other tables are (rightfully) already there

[runic pilot]

not with migration commands, no. The migrator usually keeps a table that shows which migrations have been ran and your dropped table is already created from the perspective of the migration tool

[pseudo summit]

I have to admit, I still can't get used to ORMs like SQLAlchemy. I know too much SQL so they just ... muddy my brain.

[slender seal]

Yeah lol i recreated my db instead 😆

[pseudo summit]

Let me tell you, trying to find a Flask tutorial that DOESN'T use SQLAlchemy is tough. Not sure I ever did. I should probably do a write-up on my site and how to do it without an ORM. I do plan to turn my most recent site into a cookiecutter for exactly this reason and I'll OS it of course.

[chrome vault]

why mysql not working with python 32 bit but working fine with 64 bit

?

[brazen charm]

why are you using 32 bit

[pale elbow]

so with my bot im using asyncpg, and a connection pool. Does anyone know if theres a way to make the connections in the pool close when i shut down the bot? (controlled, or not)

[eternal raptor]

    @commands.command(pass_context=True)
    async def pokaz(self, ctx, table):
        conn = sqlite3.connect('bazaNinjaSerwer.db')
        c = conn.cursor()
        this = c.execute("SELECT rowid, * FROM ?", (table))
        c.execute("SELECT rowid, * FROM ?", (table))
        conn.commit()
        await ctx.send(this)
        conn.close()

discord.ext.commands.errors.CommandInvokeError: Command raised an exception: OperationalError: near "?": syntax error

[zinc maple]

the built in sqlite3 module if I have an on-disk database.sqlite file is the entire thing loaded into memory when I "connect" to it?

@eternal raptor I'm not 100% sure but sometimes you need to go (table,) to make it understand that it's a tuple

[torn sphinx]

Using mysql.connector - Can someone tell me how to run this command(UPDATE <some table name> SET USED = 0) through the cursor.execute()? If i run this in the sql of phpmyadmin it updates everything like it should but if I run it with cursor.execute() it does nothing. Also my cursor.execute() works just fine with something like DROP DATABASE <whatever> for example.

[rich trout]

did you forget cursor.commit()?

[torn sphinx]

Didn't even know that thing existed lol

[deep maple]

hello, I have a problem with my database:

   cursor.execute(f"UPDATE info SET time={check} WHERE guild_id={ctx.guild.id} AND user_id={member.id}")
sqlite3.OperationalError: near "<": syntax error```
in
```python
@bot.command()
async def rep(ctx, member: discord.Member):
   temps = time.time()
   bot.result = 0
   try:
       check = cursor.execute(f"SELECT time FROM info WHERE guild_id={ctx.guild.id} AND user_id={member.id}")
       check = int(check.fetchone()[0])
       bot.result = check
       check = time.time
       cursor.execute(f"UPDATE info SET time={check} WHERE guild_id={ctx.guild.id} AND user_id={member.id}")
       conn.commit()
       
   except:
       print("erreur")
       check = time.time
       bot.result = check
       cursor.execute(f"UPDATE info SET time={check} WHERE guild_id={ctx.guild.id} AND user_id={member.id}")
       conn.commit()```

[fresh nova]

Cam some one help me

Can some one help me ?

I can’t selete the query

Ok

@torn sphinx this is an error

[harsh pulsar]

it's your 2nd column.

quantity_report

that's using the un-aggregated value of quantity

do you mean to be applying that to sum(quantity)?

you can't use an alias to "override" a column, and you can't refer to aliases from other expressions in SELECT

you'd have to do this in a subquery

[fresh nova]

what should i fo?

[harsh pulsar]

nested queries

[fresh nova]

i want to show the store entity for each store

[harsh pulsar]

yes

write your groupby with sum(quantity) in the inner query

then do your case ... when for quantity_report in the outer query

select
  store_name,
  total_quantity,
  case when total_quantity > 20 then 'Big' else 'Small' end quantity_report
from (
  select store_name, sum(quantity) total_quantity
  from inventory natural join stores
  group by store_name
) t

something like that

(pardon my lazy formatting)

[fresh nova]

thanks

let me try that approuch

[harsh pulsar]

itd be really nice if sql let you refer to aliased columns... at least hopefully your query engine will optimize that

[fresh nova]

what is 't' letter at the end of query?

[harsh pulsar]

in most sql dialects, table results from subqueries must be aliased

so i just called it t

you can write AS t or AS subtable or something, if that's clearer to you

[fresh nova]

can you explain where is the total_quantity field in table?

@harsh pulsar please?

[harsh pulsar]

@fresh nova look in the inner query

in select

[quick helm]

I have an issue, basically I have 2 scripts logging in to the same database, and for some reason when one is connected the other times out

mySQL ^

[fresh nova]

@harsh pulsar the query works fine, thanks for that

[harsh pulsar]

@torn sphinx what do you mean "limited amount of columns"? don't store one cookie per column

denormalize your data

have a table of cookies

user_id | cookie_id

primary key being cookie_id, user_id being a foreign key to your user table

precisely

have the table represent login sessions

or something

there are probably libraries that take care of this for you

[fresh nova]

@harsh pulsar can you explain how the inner quey works?

[harsh pulsar]

@quick helm maybe mysql has some kind of concurrency/locking configuration you can investigate

@fresh nova what do you mean? its literally just a select from with groupby

[quick helm]

@harsh pulsar it's weird because sometimes it works

[harsh pulsar]

yeah i dont have much experience provisioning databases :/

just querying

[quick helm]

I might move to MongoDB

I hate SQL

it's so annoying to work with

[harsh pulsar]

mysql yes

[quick helm]

hey discord uses a noSQL db

[harsh pulsar]

postgres is a dream by comparison

[fresh nova]

@harsh pulsar i mean what happend if we write 2 select ?

[harsh pulsar]

@fresh nova what do you mean

you have the inner query

[quick helm]

mysql is pretty bad, I've just used it for a while

[harsh pulsar]

then you're querying from the result of that query

[fresh nova]

@harsh pulsar which one of them run first ?

[harsh pulsar]

the query engine usually optimizes it so that they are flattened

but conceptually its like programming

the stuff inside the parentheses always runs first

obviously, to query from the result of the inner query, the inner query must run first 🙂

[fresh nova]

@harsh pulsar

[harsh pulsar]

i would do ORDER BY in the outer query for clarity

but yeah

[fresh nova]

which one is outputting the select?

[harsh pulsar]

feel free to reformat that inner SELECT as well

there are 2 selects

stop thinking so hard and look at the code

there's an inner query

then there's an outer query, that queries from the result of the inner query

[fresh nova]

ok thanks

how can i see the view in output?

@harsh pulsar thanks vey much for helping

[harsh pulsar]

what do you mean "how can i see the view"? use whatever you were using normally

i didnt conjure up a sql wizard and add some magic functionality to your computer

this is just sql

[fresh nova]

i have create view from this query and i want to run and see it but i don't know how to do that

[harsh pulsar]

what do you mean? create the view, then query from the view

[fresh nova]

how to see what the view is outputing?

[harsh pulsar]

a view acts just like a table

you can query from it like a table

thats the point of a view

[fresh nova]

how to output the view

[harsh pulsar]

i dont understand your question

either you write a query from it, or you use your IDE (which could be one of a hundred programs) to show it some other way

[fresh nova]

how to see the result of view that i have just created

[harsh pulsar]

ive answered the same question 3 times

youve just changed the phrasing slightly

[regal dagger]

how can i link pgadmin4 to heroku?

btw i need help with pgadmin4 too

how can i clear the data in columns?

do you mean can you link a post gres data base to pg admin or can you link a postgres database with pgadmin
@torn sphinx nvm..... i want help with this how can i clear the data in columns?

i wanted to reset my db actually

to clear the data thats stored

umm im kinda new to pgadmin4 and postgres

it will delete ALL data in the table
@torn sphinx it will keep the colum name right

TRUNCATE table_name;
@torn sphinx ok so how do i do this with pgadmin4

yes

actually i use python to modify data in db

um no?

yes

cant seem to delete or edit data

@torn sphinx

oh

@torn sphinx got it

TRUNCATE table_name; so i just do this?

thanks

i just found out that theres an option to truncate

[fresh nova]

@harsh pulsar when we usually use the 2 inner query like that ?

[lavish ferry]

how can I update the pg_hba.conf file in heroku?
I need to put my IP there, like:
host all all xxx.xxx.xxx.xxx md5
its because i'm getting this error: asyncpg.exceptions.InvalidAuthorizationSpecificationError: no pg_hba.conf entry for host "123.456.789.10", user "xxx", database "yyy", SSL off

source:
https://confluence.atlassian.com/jirakb/error-connecting-to-database-fatal-no-pg_hba-conf-entry-for-host-x-x-x-x-user-jiradbuser-database-jiradb-ssl-off-950801726.html
https://www.postgresql.org/docs/12/auth-pg-hba-conf.html

[queen galleon]

how can i check if a specific word is in a column. The column is a list

[gloomy pike]

json list?

[torn sphinx]

Is this not how you check multiple conditions in sqlite?

cursor.execute(f'DELETE FROM reacted WHERE messageid = {payload.message_id} AND userid = {payload.user_id}')```
It's not deleting the record lol

[harsh pulsar]

that's the correct sql but the wrong way to put data into the query

use query parameters

cursor.execute('DELETE FROM reacted WHERE messageid = ? AND  userid = ?', (payload.message_id, payload.user_id))

this handles all the correct sql escaping and type conversions for you

otherwise you will get it wrong, not to mention open yourself up to sql injection attacks

[torn sphinx]

Ok, thank you!

[pearl ether]

hello 🙋‍♂️, i'm new here and i know nothing about pyton, skripts and stuff like this. Can anyone help me with the download 😂 (ps: i'm german so.. my english isn't that good)

[latent comet]

would something like this work in Postgre? group.execute("""SELECT * from %s""", (group_id,))
I just tried and received an error, is there any way around this?

"psycopg2.errors.SyntaxError: syntax error at or near"

since my db don't necessarily always now the table name, its actually getting it from its parameters so...

[jovial jewel]

with postgres how do you find the local url of the db?
i'm trying to access it with sql alchemy but dunno how to find it out for my db i just made or what i should search to find it out

[latent comet]

if you downloaded it, it should be accesed in the localhost and by default the port is 5432, unless you changed it

[jovial jewel]

arg, i need to do some research on how dbs work i don't even know what to do with the port. I've only ever just followed django tutorials 😛

I thought I could just use postgresql:///db_name 😆

[runic pilot]

no matter what database, the url to connect with it will almost always be the same pattern: scheme[+driver]://[username[:password]@]hostname:port/dbname

for a local postgresql server it's usually postgresql://localhost:5432/mydb

[reef hawk]

Question with asyncpg - is there a way to insert a list/extract a list from Postgres similar to how it would be in Python?

[latent comet]

In Postgres is it possible to access a table from an argument passed into the function?
def func(i, values): cur.execute("INSERT INTO %s VALUES(%s)", (i, values))
I tried accessing the table using a variable but that didn't work..

"psycopg2.errors.SyntaxError: syntax error at or near"

[leaden orbit]

i want to use a snowflake as the id of my table, is there a way to call it by default

could i do id = db.Column(db.BigInteger, default=snowflake, primary_key=True)

or should i be using server_default

or should i manually call it

[noble oak]

Do all the different databases work the same

As in do they all have lke the same docs

[minor ruin]

no

SQL Syntax tends a very slightly between Database applications

and some databases have data analysis tools available like MSSQL

[celest blaze]

and then there's all the NoSQL databases

[neat umbra]

Definitely different

Some are similar tho

[minor ruin]

Yea, NoSQL is whole another meal of worms

there is one NoSQL application I know of that uses SQL syntax for retrieval

[quartz hound]

Which DB is the best one to learn?

I am a beginner with around 6 full months of experience with Python.

[harsh pulsar]

sqlite and postgres imo

[molten echo]

Hi, I am in need of asyncio but I use peewee ORM right now, what library should I use? I've seen peewee-async exists but I am not sure if it's good enough for production code.
Edit: This is why I ask https://github.com/05bit/peewee-async/issues/135

[zinc maple]

From what I can find the built-in sqlite3 module does not support seeded random for orderby, I'm reading some information about custom functions, from what I can tell the type of function I need to write is a collation but I can only find information about it for php not python.
Anyone been down this road?

[regal dagger]

can someone pls help me

  File "C:\Python\Python38\lib\site-packages\discord\client.py", line 312, in _run_event
    await coro(*args, **kwargs)
  File "h:\HRISHI\fiverrbot\banishedbot\cogs\level.py", line 26, in on_message
    await self.bot.pg_con.execute("INSERT INTO LevelSystem(user_id, guild_id, level, xp, rank,banishedbucks) VALUES($1,$2,1,0,Story Teller,0)",author_id,guild_id)
  File "C:\Python\Python38\lib\site-packages\asyncpg\pool.py", line 518, in execute
    return await con.execute(query, *args, timeout=timeout)
  File "C:\Python\Python38\lib\site-packages\asyncpg\connection.py", line 274, in execute
    _, status, _ = await self._execute(query, args, 0, timeout, True)
  File "C:\Python\Python38\lib\site-packages\asyncpg\connection.py", line 1402, in _execute
    result, _ = await self.__execute(
  File "C:\Python\Python38\lib\site-packages\asyncpg\connection.py", line 1411, in __execute
    return await self._do_execute(query, executor, timeout)
  File "C:\Python\Python38\lib\site-packages\asyncpg\connection.py", line 1423, in _do_execute
    stmt = await self._get_statement(query, None)
  File "C:\Python\Python38\lib\site-packages\asyncpg\connection.py", line 328, in _get_statement
    statement = await self._protocol.prepare(stmt_name, query, timeout)
  File "asyncpg\protocol\protocol.pyx", line 163, in prepare
asyncpg.exceptions.PostgresSyntaxError: syntax error at or near "Teller"```

[torn sphinx]

Hi, i need an sql for my bot but every sql needs to download something. Do you guys know any sql that no need to download anything and control on website?

[noble oak]

Are you able to take two values from a database table?

cursor.execute(f"SELECT channel_id FROM main WHERE guild_id = {ctx.guild.id}")```

So would I be able to take channel_id and msg?

This is what the table looks like

[zinc maple]

yepp, just SELECT channel_id, msg FROM

if that's what you mean

[noble oak]

Oh thank you

Traceback (most recent call last):
  File "E:\Program Files (x86)\Python\lib\site-packages\discord\ext\commands\core.py", line 83, in wrapped
    ret = await coro(*args, **kwargs)
  File "e:\Games and Apps\Visual Studio Code\Visual Code Projects\Discord Bot\Cogs\config.py", line 91, in channel
    cursor.execute(sql, val)
sqlite3.OperationalError: database is locked

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

Traceback (most recent call last):
  File "E:\Program Files (x86)\Python\lib\site-packages\discord\client.py", line 312, in _run_event
    await coro(*args, **kwargs)
  File "e:\Games and Apps\Visual Studio Code\Visual Code Projects\Discord Bot\Cogs\error.py", line 17, in on_command_error
    raise error
  File "E:\Program Files (x86)\Python\lib\site-packages\discord\ext\commands\bot.py", line 892, in invoke
    await ctx.command.invoke(ctx)
  File "E:\Program Files (x86)\Python\lib\site-packages\discord\ext\commands\core.py", line 1234, in invoke
    await ctx.invoked_subcommand.invoke(ctx)
  File "E:\Program Files (x86)\Python\lib\site-packages\discord\ext\commands\core.py", line 797, in invoke
    await injected(*ctx.args, **ctx.kwargs)
  File "E:\Program Files (x86)\Python\lib\site-packages\discord\ext\commands\core.py", line 92, in wrapped
    raise CommandInvokeError(exc) from exc
discord.ext.commands.errors.CommandInvokeError: Command raised an exception: OperationalError: database is locked```

I got this error when trying to add something to the database

    @welcome.command()
    @commands.is_owner()
    async def channel(self, ctx, channel: discord.TextChannel):
        #if ctx.author.guild_permissions.manage_messages:

        db = sqlite3.connect('main.sqlite')
        cursor = db.cursor()
        cursor.execute(f"SELECT channel_id FROM main WHERE guild_id = {ctx.guild.id}")
        result = cursor.fetchone()

        if result is None:
            sql = ("INSERT INTO main(guild_id, channel_id) VALUES(?,?)")
            val = (ctx.guild.id, channel.id)
            await ctx.send(f"Welcome channel has been successfully set to {channel.mention}")

        elif result is not None:
            sql = ("UPDATE main SET channel_id = ? WHERE guild_id = ?")
            val = (channel.id, ctx.guild.id)
            await ctx.send(f"Welcome channel has been successfully updated to {channel.mention}")

        cursor.execute(sql, val)
        cursor.execute(sql1, val1)
        db.commit()
        cursor.close()
        db.close()

[noble oak]

Anyone know what to do when it says that the database is locked?

[runic pilot]

probably upgrade to a database that supports concurrent access

[noble oak]

Does postgreSQL support that?

[runic pilot]

everything that isn't sqlite supports it

[noble oak]

oh fair

So basically I chose the only bad one

[runic pilot]

lol no, it's not bad, it's just made for a specific purpose

[noble oak]

And would the code above work with psql?

[runic pilot]

not exactly as-is, but pretty similar, I'd recommend taking a look at the sqlalchemy docs to see what needs to change

I think it's just the connection creation

[noble oak]

What type of file does a postgresql save as

[eternal raptor]

        @commands.command(pass_context=True)
        @commands.has_role('ADMIN')
        async def ukaraj(self, ctx, member: discord.Member , reason = None, table_name, user, userid):
                if reason == None or member == None:
                        await ctx.send("Podaj powód lub użytkownika i spróbuj ponownie!")

                embed=discord.Embed(title='Sukces!', description=f'Ukarano użytkownika {member}, powód: {reason}')
                await ctx.send(embed=embed)
                await ctx.guild.ban(member)
                conn = sqlite3.connect('bazaNinjaSerwer.db')
                c = conn.cursor()
                table_name = 'baniki'
                dodaj = (f"INSERT INTO {table_name} (User, UserID, reason) VALUES ('{user}', '{userid}', '{reason}')")
                print(c.execute(dodaj))
                await ctx.send(c.fetchone())
                conn.commit()
                conn.close()

cogs.warns nie mo�e by� za�adowany. [Extension 'cogs.warns' raised an error: SyntaxError: non-default argument follows default argument (warns.py, line 13)]

unban command

[latent comet]

Is there any way to insert a json into a Postgresql database?
I'm currently doing something like this:

        group.execute(
            sql.SQL("CREATE TABLE if not exists {} 'info' json DEFAULT (%s::jsonb[])")
                .format(sql.Identifier([group_id])),
            [self.get_default(group_choice)])

        group.commit()

(group_id) is a string, whereas (group_choice) is a Json.
I'm receving this error: TypeError: SQL identifier parts must be strings

[eternal raptor]

    @commands.command()
    async def tablicanowa(self, ctx, table):
        conn = sqlite3.connect('bazaNinjaSerwer.db')
        c = conn.cursor()
        this = f"CREATE TABLE {table}"
        await ctx.send("utworzono tablice!")
        await ctx.send(c.execute(this))
        conn.commit()
        conn.close()

when i use this command: discord.ext.commands.errors.CommandInvokeError: Command raised an exception: OperationalError: near "'baniki'": syntax error

help

plz

[regal dagger]

await self.bot.pg_con.execute("UPDATE LevelSystem SET rank = Bearer Tier I " )

is the syntax for postgresql correct?

do i need to put the words in quotes?

[quick helm]

if collection.count_documents({'gID': 715239890635194500, 'inServer': True}, limit=1) != 0:

I'm having a hard time with integers and mongoDB

Why doesn't it find the document?

the gID is there

and it's the exact same one

[carmine falcon]

pymongo?

@quick helm

[quick helm]

yeah

pymongo

[carmine falcon]

hm, and removing inserver doesn't give you anything either?

[quick helm]

i think it's because it's converting the numbers to scientific

[carmine falcon]

uh hmn

[quick helm]

or something along those lines

[carmine falcon]

you're sure that gID is an integer and not a string in mongo?

[carmine falcon]

what's producing the first one?

[quick helm]

find_one

[carmine falcon]

hmn

maybe inputting it as a string....

in theory pymongo should handle this kind of thing

[quick helm]

it should

but it's an ugly solution

in my python code I process them as ints, so having to convert and convert back is ugly

it's like reading a book written in 2 languages

[carmine falcon]

it works when provided as an int?

er

string?

[quick helm]

It should, I can try

[carmine falcon]

hmm... i wonder what's going on

[quick helm]

works fine

there's no reason for it not to anyway

[carmine falcon]

wacc

do you have access to a mongo shell with this data?

[quick helm]

I have full access

[carmine falcon]

you could try just grabbing the item and seeing what the data type in mongo is

[quick helm]

yeah I was looking at how to do that

but idk how

this is my first day with mongodb after 6 years with mysql

[carmine falcon]

typeof db.test.findOne().gID

[quick helm]

you mean type() right?

[carmine falcon]

uhh

in the mongo console i mean

[quick helm]

ah

[carmine falcon]

presumably pymongo can do that too but i dont remember how tbh

[quick helm]

type() would probably work

but ill do console

[carmine falcon]

wouldn't that give you the converted-to-python-object data type

[quick helm]

yeah but when I enter the data in python pymongo will convert it

and also I get object

for the typeof

[carmine falcon]

uhh

hmn

it's possible that it's stored in mongo as a string

[quick helm]

I tried already

[carmine falcon]

and then when you query it with python, it's converting it into an integer

oh, no dice?

hmmmmmmmmmmmm

[quick helm]

yeah to put '' in python

is there a difference between "" and ''?

[carmine falcon]

not really

[quick helm]

that's what I thought too

it's weird. I think it's because the number is over 16 digits

which is usually the maximum

[carmine falcon]

weird

too much magic.,.

[quick helm]

there must be a way to fix this

I'm sure someone has stored ints above 16 digits in mongoDB before

if I put the value as
NumberLong(636002954392732556)

will it return an int in python?

[noble oak]

I got this error (I'm not able to type it here because it's too long) and I was trying to connect to my database

        db = psycopg2.connect(
            database = "welcome",
            user = "postgres",
            password = PASSWORD,
            host = "localhost",
            port = "5432"
        )
        cursor = db.cursor()
        cursor.execute(f"SELECT channel_id FROM welcome WHERE guild_id = {ctx.guild.id}")
        result = cursor.fetchone()```

[carmine falcon]

theoretically mongo should be happy with bigger integers... i think?

[noble oak]

Anyone know why I'm getting that error?

[carmine falcon]

uh hmn

"character varying = bigint"

what's the type of the guild_id column?

[noble oak]

Sorry?

[carmine falcon]

uh, like

[noble oak]

Data type?

[quick helm]

yeah, fixed

[carmine falcon]

yeah

[quick helm]

NumberLong works

[noble oak]

character varying

[carmine falcon]

ah yeah

that's probably it

you should probably change that to like, bigint

or something

[noble oak]

Ok thanks

Traceback (most recent call last):
  File "E:\Program Files (x86)\Python\lib\site-packages\discord\ext\commands\core.py", line 83, in wrapped
    ret = await coro(*args, **kwargs)
  File "e:\Games and Apps\Visual Studio Code\Visual Code Projects\Discord Bot\Cogs\config.py", line 108, in channel
    cursor.execute(sql, val)
psycopg2.errors.SyntaxError: syntax error at or near ","
LINE 1: INSERT INTO welcome(guild_id, channel_id) VALUES(?,?)
                                                          ^


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

Traceback (most recent call last):
  File "E:\Program Files (x86)\Python\lib\site-packages\discord\client.py", line 312, in _run_event
    await coro(*args, **kwargs)
  File "e:\Games and Apps\Visual Studio Code\Visual Code Projects\Discord Bot\Cogs\error.py", line 17, in on_command_error
    raise error
  File "E:\Program Files (x86)\Python\lib\site-packages\discord\ext\commands\bot.py", line 892, in invoke
    await ctx.command.invoke(ctx)
  File "E:\Program Files (x86)\Python\lib\site-packages\discord\ext\commands\core.py", line 1234, in invoke
    await ctx.invoked_subcommand.invoke(ctx)
  File "E:\Program Files (x86)\Python\lib\site-packages\discord\ext\commands\core.py", line 797, in invoke
    await injected(*ctx.args, **ctx.kwargs)
  File "E:\Program Files (x86)\Python\lib\site-packages\discord\ext\commands\core.py", line 92, in wrapped
    raise CommandInvokeError(exc) from exc
discord.ext.commands.errors.CommandInvokeError: Command raised an exception: SyntaxError: syntax error at or near ","
LINE 1: INSERT INTO welcome(guild_id, channel_id) VALUES(?,?)
                                                          ^```

    @welcome.command()
    @commands.is_owner()
    async def channel(self, ctx, channel: discord.TextChannel):
        #if ctx.author.guild_permissions.manage_messages:

        db = psycopg2.connect(
            database = "welcome",
            user = "postgres",
            password = PASSWORD,
            host = "localhost",
            port = "5432"
        )
        cursor = db.cursor()
        cursor.execute(f"SELECT channel_id FROM welcome WHERE guild_id = {ctx.guild.id}")
        result = cursor.fetchone()

        if result is None:
            sql = ("INSERT INTO welcome(guild_id, channel_id) VALUES(?,?)")
            val = (ctx.guild.id, channel.id)
            await ctx.send(f"Welcome channel has been successfully set to {channel.mention}")

        elif result is not None:
            sql = ("UPDATE welcome SET channel_id = ? WHERE guild_id = ?")
            val = (channel.id, ctx.guild.id)
            await ctx.send(f"Welcome channel has been successfully updated to {channel.mention}")

        cursor.execute(sql, val)
        db.commit()

        cursor.execute(f"SELECT msg FROM main WHERE guild_id = {ctx.guild.id}")
        result1 = cursor.fetchone()

        if result1 is None:
            sql1 = ("INSERT INTO main(guild_id, msg) VALUES(?,?)")
            val1 = (ctx.guild.id, "Welcome {user} to the {guild} server. There are now {members} members in this server.")


        
        cursor.execute(sql1, val1)
        db.commit()
        cursor.close()
        db.close()```

Sorry for the massive blocks but why is it saying there's a syntax error, anyone know?

[carmine falcon]

uh hmn

are you sure that it's ?

i dont remember clearly but isnt psycopg2's placeholder %s

so "INSERT INTO... VALUES(%s, %s)" or something

[noble oak]

Ok I'll try it out

Appreciate it

[latent comet]

Is there any way to insert a json into a Postgresql database?
I'm currently doing something like this:

        group.execute(
            sql.SQL("CREATE TABLE if not exists {} 'info' json DEFAULT (%s::jsonb[])")
                .format(sql.Identifier([group_id])),
            [self.get_default(group_choice)])

        group.commit()

(group_id) is a string, whereas (group_choice) is a Json.
I'm receving this error: TypeError: SQL identifier parts must be strings

[torn sphinx]

why i can't create table?

[torn sphinx]

pls need help

[delicate field]

Hey @torn sphinx!

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

[fluid tusk]

can eval run codes imported fo sqlite db

?

[harsh pulsar]

technically yes, but you probably should not do that

show the full error traceback @torn sphinx

[noble oak]

@client.event
async def on_command(ctx):
    cursor = db.cursor()
    cursor.execute(f"SELECT commands FROM commands_run WHERE user_id = {ctx.author.id}")
    result = cursor.fetchone()

    result += 1

    cursor.execute(result)
    db.commit()
    cursor.close()
    db.close()```

I'm trying to make it so that when a command is used, the commands_run will go up by one but I am getting the error:

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

That's the table

[eternal raptor]

    @commands.command()
    async def tablicausun(self, ctx, table):
        conn = sqlite3.connect('bazaNinjaSerwer.db')
        c = conn.cursor()
        this = f"DROP TABLE {table}"
        await ctx.send(c.execute(this))
        await ctx.send(f"Zlikwidowano tablice {table}!")
        conn.commit()
        conn.close()

how to replace {} to '?'

[sly wren]

Does someone have a good tutorial on how to use sqlite3 with python?

[harsh pulsar]

@eternal raptor unfortunately you can't parameterize a table name orc olumn name

[compact dove]

oooh databases?

I should try that

can i work with an access database with python?

[zinc maple]

only thing I find about that is 10 year old threads where the brightest solution seems to be to export it to a sqlite db <.<

oh pypyodbc might be able to

[fair stump]

Hi, did u know any library for firabase? Its for a discord bot project, so idk where send this question xd
Pd: @ me if u know the answer(?

[noble oak]

@client.event
async def on_command(ctx):

    db = psycopg2.connect(
    database = "welcome",
    user = "postgres",
    password = idk if it matters if you see this or not,
    host = "localhost",
    port = "5432"
    )
    
    cursor = db.cursor()
    cursor.execute(f"SELECT commands FROM commands_run WHERE user_id = {ctx.author.id}")
    result = cursor.fetchone()

    if result == None:
        result = 0
        results = result
        results += 1
        sql = ("INSERT INTO commands_run(user_id, commands) VALUES(%s,%s)")
        val = (ctx.author.id, results)

    if result != None:
        results = result
        results += 1
        sql = ("UPDATE commands_run SET commands = %s WHERE user_id = %s")
        val = (results, ctx.author.id)



    cursor.execute(sql, val)
    db.commit()
    cursor.close()
    db.close()

For some reason this won't add anything to my database

[tribal dome]

@fair stump im currently using mongodb on my discord bot proj

[fair stump]

Its compatible with firebase?

[noble oak]

for the database arg in psycipg2.connect() is that supposed to be the name of the table or the database

[gloomy pike]

cursor = await self.db.execute("Select MemberID from Tags where Tag = ? and GuildID = ?", (tag, ctx.guild.id))
result = await cursor.fetchone()

if not result:
    return await ctx.send(embed=to_embed("Tag does not exist.", negative=True))```

i triple checked and the entry for cursor exists

why does result still return false

[rich trout]

@noble oak that's supposed to be the database name, not the table name

@gloomy pike I don't see anything wrong with what you have there, what does an unconditional select give you?

its possible that capitalization or something else is at fault here, depending on what "tag" is

[gloomy pike]

nvm

[gloomy pike]

ID = str(10**30 + random.randint(0, 10**31-10**30-1))```

[uncut egret]

Hey I was wondering if someone can point me into the right direction, I have a database and I want to make my own API in json format with all of my info from a table where do i starT?

[gloomy pike]

why does ID save to sqlite3 db as round number?

like 9e+30 ...

instead of the number itself

why would it round if it's a string

it saves as 9.15879410636764e+30

[carmine falcon]

@gloomy pike what's your database schema like?

the engine will normally do conversion if the column is declared as a number

[gloomy pike]

create table if not exists Reminders(MemberID int not null, Reminder str not null, Time int not null, ID string not null)

[carmine falcon]

hmn

[gloomy pike]

that was the statement

but the type of data shows as "text/numeric"

[carmine falcon]

maybe uh, change the column type to TEXT or something

hmn

[gloomy pike]

oh

yeah

i did

it worked now

[carmine falcon]

ah nice

[alpine patio]

more info @torn sphinx

[gloomy pike]

is it bad practice to name columns the same as the table name?

[nocturne basin]

Does anyone know a good guide to stream twitter api tweets into a sql database?

streamed + historical tweets

[uncut egret]

im using a restful api, im adding users manually to the api and i want to grab everyone from the database to auto put them on the api can someone guide me into the right direction

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

[olive pivot]

do you mean replacing the users list at the top of your script with a DB?

@uncut egret

[uncut egret]

yea

i want to search

through my database

@olive pivot

not the manual list i created

[olive pivot]

as you are building a Flask app, I would recommend using Flask-SQLAlchemy to connect to the DB. https://flask-sqlalchemy.palletsprojects.com/en/2.x/
If you are new to DBs, and this is just a pet project to learn, you probably want to create a SQLite local DB and play with it

[uncut egret]

I have a local SQlite DB

i play around with it all the tim

time*

i just want to connect my DB so when im doing users/<username> it checks from the database

[olive pivot]

then go with Flask-SQLAlchemy.
I've only use vanilla SQLAlchemy, and if you already know SQL it's very easy to grasp.

[uncut egret]

mmk

how do i select things in tuples

conn = sqlite3.connect(db_path)
c = conn.cursor()
c.execute("SELECT name FROM players")
players = c.fetchone()
player = players[0]
print(player)
c.execute("SELECT elo FROM players")
elos = c.fetchone()
elo = elos[0]
c.execute("SELECT record FROM players WHERE ID")
records = c.fetchone()
record = records[0]
c.execute("SELECT loss FROM players WHERE ID")
losses = c.fetchone()
loss = losses[0]
c.execute("SELECT win FROM players WHERE ID")
wins = c.fetchone()
win = wins[0]

users = [
    {
        "username":f"{player}",
        "elo": f"{elo}",
        "highestElo": f"{record}",
        "loss": f"{loss}",
        "win": f"{win}"
    }
]

class User(Resource):
    def get(self, name):
        for user in users:
            if(name == user["username"]):
                return user, 200
        return "User not found", 404```

so now whenever i type user/(playername)

it shows the first DB selection in the players table

how do i make it so i can type anyones name

doing user/PoweR gives me

{
    "username": "PoweR",
    "elo": "1271",
    "highestElo": "1339",
    "loss": "26",
    "win": "29"
}```

[deep maple]

Hello

il have

a error

    cursor.execute(f"UPDATE info SET profil={arg} WHERE guild_id={ctx.guild.id} AND user_id={ctx.author.id}")
sqlite3.OperationalError: no such column: test```

in

@bot.command()
async def setbio(ctx, *, arg):
    check = cursor.execute(f"SELECT profil FROM info WHERE guild_id={ctx.guild.id} AND user_id={ctx.author.id}")
    check = check.fetchone()
    print(check)
    cursor.execute(f"UPDATE info SET profil={arg} WHERE guild_id={ctx.guild.id} AND user_id={ctx.author.id}")
    conn.commit()```

my table

cursor.execute("""
CREATE TABLE IF NOT EXISTS info(
     guild_id TEXT,
     user_id TEXT,
     argent INTERGER,
     rep INTERGER,
     profil TEXT
)
""")
conn.commit()```

[torn sphinx]

hm

[deep maple]

can you help me please ?

💨

please ....

(please)

[quick helm]

I'm confused

I do
"uID": NumberLong(518187202891612182), in MongoDB when I insert the row

but the row gives me

what?

[torn sphinx]

what do you mean, what's the question here

[quick helm]

look at the 2 values

they're not the same

when I do NumberLong("518187202891612182") it works out fine

[torn sphinx]

share the complete command

[quick helm]

db.tokens.insert({"uID": NumberLong(518187202891612182)})

I know there's nothing wrong with the command itself, it's something to do with how NumberLong works

[torn sphinx]

you're right, it should be ""

for insert with NumberLong

[quick helm]

yeah I'm wondering why though

I thought it accepted both ints and strings

[torn sphinx]

what version are you running

[quick helm]

latest

[chrome vault]

    @commands.command()
    @commands.has_permissions(manage_channels=True)
    async def bind(self, ctx, channel: discord.TextChannel):
        db = self.mydb()
        print(db)
        mycursor = db.connect()
        sql = "SELECT channel_id FROM channel WHERE guild_id = '74185'"
        mycursor.execute(sql)
        result = mycursor.fetchone()
        print(result)

idk why my query is not executing

pls help

[upbeat lily]

what database? @chrome vault

My guess is you're mixing up types

and guild_id shouldn't be in quotes

but I'm not entirely sure

[noble oak]

  File "E:\Program Files (x86)\Python\lib\site-packages\discord\client.py", line 312, in _run_event
    await coro(*args, **kwargs)
  File "e:/Games and Apps/Visual Studio Code/Visual Code Projects/Discord Bot/main.py", line 109, in on_command
    results += 1
TypeError: can only concatenate tuple (not "int") to tuple```
```py
@client.event
async def on_command(ctx):


    db = psycopg2.connect(
    database = "welcome",
    user = "postgres",
    password = password,
    host = "localhost",
    port = "5432"
    )
    
    cursor = db.cursor()
    cursor.execute(f"SELECT commands FROM commands_run WHERE user_id = {ctx.author.id}")
    result = cursor.fetchone()

    if result == None:
        result = 0
        results = result
        results += 1
        sql = ("INSERT INTO commands_run(user_id, commands) VALUES(%s,%s)")
        val = (ctx.author.id, results)

    elif result != None:
        results = result
        results += 1
        sql = ("UPDATE commands_run SET commands = %s WHERE user_id = %s")
        val = (results, ctx.author.id)



    cursor.execute(sql, val)
    db.commit()
    cursor.close()
    db.close()```

The datatype is integer so how do I add a number to it

[pliant lava]

HI I'm having an issue with executing an sql command

so I'm trying to run this sql command when I run my app

class MyMainApp(App):
    
    def build(self):
        conn = sqlite3.connect("expenditure.db")
        cur = conn.cursor()
        sql = """
        create table if not exists expenses (
            id integer PRIMARY KEY AUTOINCREMENT NOT NULL,
            amount number,
            category string,
            message string,
            date string
            );
        """
        cur.execute(sql)
        conn.commit()
        return kv
    
    


if __name__ == "__main__":
    MyMainApp().run()

unfortunately it gives me an error of

     cur.execute(sql)
 sqlite3.OperationalError: no such table: expenses

does anyone knows what's the proper way of executing it?

I've also tried putting the block of sql command outside of the build function but it doesn't work

[sage charm]

Hi @pliant lava, I ported your code to work on my side, but I couldn't find the same error on my side.

Code :

import sqlite3

class MyMainApp():
    
    def build(self):
        conn = sqlite3.connect("expenditure.db")
        cur = conn.cursor()
        sql = """
        create table if not exists expenses (
            id integer PRIMARY KEY AUTOINCREMENT NOT NULL,
            amount number,
            category string,
            message string,
            date string
            );
        """
        cur.execute(sql)
        conn.commit()
        kv = 'true'
        return kv


if __name__ == "__main__":
    kv = MyMainApp().build()
    print(kv)

Console : ```console
H:\data\sqlite3>py test.py
true

is the error in that code block right ?

[pliant lava]

sorry I found the issue already! it seems I built kv outside

which caused the error

I put kv = (something) outside

thank you for helping!

[sage charm]

All right, you're welcome.

[eternal raptor]

    @commands.command(pass_context=True)
    async def utworzprof(self, ctx, idd, member: discord.Member):
        conn = sqlite3.connect('bazaNinjaSerwer.db')
        c = conn.cursor()
        utworz = ("INSERT INTO ekonomia VALUES ('%s', '%s' , '0')" % (str(idd) % (str(member)) ) )
        await ctx.send(c.execute(utworz).fetchall())
        conn.commit()
        await ctx.send("Pomyslnie utworzono profil!")
        conn.close()

discord.ext.commands.errors.CommandInvokeError: Command raised an exception: TypeError: not all arguments converted during string formatting

@sage charm could you help me plz?

@ionic tapir

[sage charm]

I'm looking at

[eternal raptor]

@sage charm what is wrong?

[sage charm]

what exactly does she line up that's wrong?

I don't use discord.py unfortunately, but I would say that the error comes from the variables of your function.

[opal dawn]

I have a mysql table w. data like title, date, description, ect. I want the user to be able to view all records containing a keyword they type in search.

Full text search, no?

This is how i did it using an ORM in a PHP framework: https://hastebin.com/mipesupupu.sql

Wondering if sqlalchemy has the same tasting magic sauce

[ionic tapir]

@eternal raptor i can't tell what this is doing

that second % should be a comma i think

but please please remove the command injection vulnerability

[dusty helm]

guys, any good guide on invoice schema for databases?

[zinc maple]

@ionic tapir just out of curiosity what is the vulnerability here? str(member) or?

like if you have a member named '); DROP TABLE .. ?

[pliant lava]

hey guys quick question, Im trying to select data from the date column

Im not sure if my syntax is right in here

class FourthWindow(Screen):
        def search_date(self):
            if str(self.ids.day_input.text) == "Day" or str(self.ids.month_input.text) == "Month" or str(self.ids.year_input.text) == "Year":
                self.error("Please select all fields!")
            else:
                day = str(self.ids.day_input.text)
                month = str(self.ids.month_input.text)
                year = str(self.ids.year_input.text)
                daytotal = None
                monthtotal = None
                yeartotal = None
                conn = sqlite3.connect("expenditure.db")
                cur = conn.cursor()
                cur.execute("""SELECT sum(amount) FROM expenses where strftime('%m', date) = ? and strftime('%d',date) = ? and strftime('%y', date) = ?""",(day,month,year,))
                daytotal = cur.fetchone()[0]
                print(daytotal)
                self.ids.daily.text = daytotal

daytotal returned me empty

[zinc maple]

aren't those % replacements entierly positional?

so it should be (month, day, year)

[pliant lava]

it still gave me the error after fixing

this is sqlite3 for context

[zinc maple]

so where does the error land on

is it the fetchone()[0]?

I can't really tell if there's another issue tbh

[pliant lava]

and I used datetime.now() function when inputting but I removed the back leaving it just year-month-day

the daytotal returns none

and I used datetime.now() function when inputting but I removed the back leaving it just year-month-day
@pliant lava not sure if this would cause issue with the strftime function

[zinc maple]

before cur.execute put a print

sec

[pliant lava]

no rush, thank you for helping

[zinc maple]

oh wait hmm

see I haven't used strftime before

so I might be just getting confused

[pliant lava]

do you think the issue might be because I chopped off a part of datetime?

[zinc maple]

what I would do if I were you is try to first of all print out the sql statement you're executing to make sure it looks good, then you probably want to make sure the select statement actually returns something...

[pliant lava]

what I would do if I were you is try to first of all print out the sql statement you're executing to make sure it looks good, then you probably want to make sure the select statement actually returns something...
@zinc maple I'm not sure on how to do the first one

[zinc maple]

in regards to datetime as I said I haven't worked with strftime before so I'm honestly not sure

[pliant lava]

I'm not sure on what you mean with the first one could u elbaorte more?

[zinc maple]

you know print() right

[pliant lava]

yes

so I just print the sql statement?

[zinc maple]

yes but also try to fill in the placeholders for the print

you might have to swap out the ?'s for something that works for print

[pliant lava]

a I see ok got it ty

[zinc maple]

worst case you might be better off grabbing a help channel, I think the fact that I haven't personally worked with neither sum nor strftime is not helping as much as you need

[pliant lava]

thank you!

[ionic tapir]

@zinc maple yeah, command injection

It's tricky because string formatting looks very similar to safe parametrisation

[pliant lava]

@zinc maple I seem to found the issue here but I'm not sure on how to fix it

when I try running this it says incorrect number of bindings supplied, statement uses 1 but 2 is supplied

actually nvm that I found the mistake from that one

I've tried one by one checking it seems the '%y' is the issue

[zinc maple]

@ionic tapir only way to get around that if you want to include a user given string like member name is a prepared statement right?

@pliant lava you mean in the strftime part?

[pliant lava]

yes

I found the error

the y needs to be capital

[zinc maple]

oh yeah that's right

ok cool, yeah I had a feeling because the rest looks normal

[pliant lava]

oh wait nvm it still gives me an issue

[zinc maple]

f

[pliant lava]

oof

somehow they won't search up the year properly

i've checked the year variable content and the variable type there's no issue in there

weird

[zinc maple]

what you had earlier they were in the wrong order

I believe they need to be in the order of the ?'s in the string

[pliant lava]

yea fixed that issue already

[zinc maple]

okokok

[hot cape]

Hey guys, I am a little confused with SQLA and sqlite
I have this

class User(Base):
    __tablename__ = "users"

    id = Column(Integer, primary_key=True, index=True)
    public_id = Column(String, primary_key=True, index=True)

    username = Column(String, unique=True)
    hashed_password = Column(String, nullable=False)```
The issue is when I try to create a new user (`new_user = models.User(username=user.username, hashed_password=fake_hashed_password, public_id=pub_id)`) I get a `NUT NULL constraint failed: users.id`

But when working with  sqlite directly without sqla, setting a column as an `INTEGER PRIMARY KEY`  makes it auto increment on its own, so why doesn't it work here? Also from this: https://docs.sqlalchemy.org/en/13/dialects/sqlite.html#sqlite-auto-incrementing-behavior I understand that it should work as is

[pliant lava]

its probably due to diff type of db

[hot cape]

What do you mean?

[pliant lava]

like one is sqla and another is sqlite

[hot cape]

Oh and I tried to delete the .db so it gets generated again when I run the project in case the d had an older version of the table (though I am pretty sure I didn't change that table at all)

[pliant lava]

so like one will do auto increment without you needing to specify for sqlite but in sqla you need to specify

I'm not sure myself but I think that's a possibility

[hot cape]

I could try to use the explicit sqlite_autoincrement=True param but even in sqla docs (the link I sent) it says that it is not recommended so that's why I figured it should work as is now

[pliant lava]

sqlite should auto increment from what i know , but I think last time when I tried sqlalchemy I actually had to specify

I think it's called serial in sqla

[hot cape]

adding sqlite_autoincrement=True gives me a different error when I start the project and it doesn't create the db 🤔
I'll try serial though I think I have seen this as a datatype in postgresql

Argument 'sqlite_autoincrement' is not accepted by dialect 'sqlite' on behalf of <class 'sqlalchemy.sql.schema.Column'> The error it gives now, weird

[pliant lava]

quick question though why're u using 2 diff db?

[hot cape]

I am not sure what you mean by that so if I am doing it then I didn't do it on purpose

[pliant lava]

I must be drunk

[hot cape]

like one is sqla and another is sqlite
I assume you are talking about this but yeah, not sure what an sqla database would be?

[pliant lava]

no no mb I misunderstood

[hot cape]

Ah okay

[pliant lava]

ignore what I just said lol

[hot cape]

If someone else has any idea please ping me (link to og message for convenience: https://discordapp.com/channels/267624335836053506/342318764227821568/718142996914372659)
Update: nvm, it was because I had two PKs, which is really not needed but that's what you get from late night coding

[noble oak]

How do I add a number to a table

The datatype for commands is integer

[eternal raptor]

    @commands.command(pass_context=True)
    async def utworzbazeprofili(self,ctx):
        conn = sqlite3.connect('bazaNinjaSerwer.db')
        c = conn.cursor()
        c.execute("CREATE TABLE ekonomia (ID int NOT NULL UNIQUE, User varchar(100) NOT NULL, NinjaGold varchar(100) UNIQUE NOT NULL, PRIMARY KEY(User))")
        conn.commit()
        conn.close()

    @commands.command(pass_context=True)
    async def utworzprof(self, ctx, idd = None, member: discord.Member.id):
        conn = sqlite3.connect('bazaNinjaSerwer.db')
        c = conn.cursor()
        t = client.get_user()
        utworz = "INSERT INTO ekonomia VALUES ('%s', '%s', '0')" % (str(idd, member))
        await ctx.send(c.execute(utworz).fetchall())
        conn.commit()
        await ctx.send("Pomyslnie utworzono profil!")
        conn.close()

When I use command:
TypeError: 'property' object is not callable
File "C:\Users\user\AppData\Local\Programs\Python\Python37-32\lib\site-packages\discord\ext\commands\core.py", line 450, in _actual_conversion
raise BadArgument('Converting to "{}" failed for parameter "{}".'.format(name, param.name)) from exc
discord.ext.commands.errors.BadArgument: Converting to "property" failed for parameter "member".

[cerulean dagger]

How do you make a database file

like this: file.db?

[torn sphinx]

@cerulean dagger https://docs.python.org/3/library/sqlite3.html

[torn sphinx]

I'm using this code:

cursor.execute('SELECT infractions FROM reputation WHERE userid = ?', (str(user.id)))
infractions = cursor.fetchone()[0]```
But I'm getting this error:
`Incorrect number of bindings supplied. The current statement uses 1, and there are 18 supplied.`
What should I change about my query to get the data I'm looking for? 18 is the length of the id if that helps. I tried putting the question mark in parentheses as well.

[torn sphinx]

hi

I'm trying to join two tables on nearby times, they are in varchar, but not sure what the problem is

SELECT * from buses as b
left join pass (select * 
from passengers as p
where p.time >= b.time
order by p.time
limit 1)
on origin, destination

[runic pilot]

what's an example of a few entries from the time column?

[torn sphinx]

I rewrote my query

but I'm not getting the right answers

SELECT b.id as id, 
count(bus_departure_time) as passengers_on_board
FROM 
(select p.id, min(cast(b.time as time) as bus_departure_time
FROM passengers p
left join buses b on b.origin = p.origin AND b.destination = p.destination
AND cast(b.time as time) >= cast(p.time As time) 
group by p.id) pd

left join passengers p on pd.id = p.id
left join buses b on b.origin = p.origin AND b.destination = p.destination
and pd.bus_departure_time = cast(b.time as time)
group by b.id
having b.id is not null

@runic pilot

[runic pilot]

again, please give examples of some rows

and the table schema

[torn sphinx]

ok