Can anyone help me with getting a db setup with flask and postgres?
#databases
quiet ermine
I am having no luck with this
I have setup flask and postgres for production but cannot link them for the life of me
unreal tartan
well, do you use sqlalchemy, flask-sqlalchemy, or psycopg?
ionic pecan
@quiet ermine what do you mean with "link"?
quiet ermine
psycopg and sqlalchemy connecting to the db
I may have set the db up wrong also but it looks right
ionic pecan
what about it is not working?
quiet ermine
It isn't accepting env vars
temporary faliure in name resulution
Or diffrent errors for diffrent things
ionic pecan
are you using docker?
quiet ermine
No, not right now
No idea how to use volumes
But my dockerised test ci has no errors
Should I use volumes then?
ionic pecan
which host is your postgres listening on?
quiet ermine
127.0.0.1:5432?
ionic pecan
which connection parameters did you give to sqlalchemy?
quiet ermine
postgresql+psycopg2://{user}:{pw}@{url}/{db}
ionic pecan
which value did you use for url?
quiet ermine
user = something-db
pw = dkfgrk
url = 127.0.0.1:5432/localhost/localhost:5432
db = something-db
Tried 3 diffrent urls
None worked
ionic pecan
can you send the full traceback?
quiet ermine
I used env vars called POSTGRES_USER, POSTGRES_PW
Need to recreate it, closed session now
ionic pecan
are you reading env vars yourself?
ionic pecan
you didnt go from env var to its value
it's trying to access postgres running at the host POSTGRES_URL
ionic pecan
no, what
your script is trying to connect to a host named POSTGRES_HOST
docker only solves as many problems as it creates
docker won't fix bugs in your program
ionic pecan
a video won't help you with it either
can you share your script
the database connecting part specifically
quiet ermine
Never worked with postgres before, pretty sure I done a few things wrong
ionic pecan
your postgres is working fine
quiet ermine
from config import user, pw, url, db
db_url = f'postgresql+psycopg2://{user}:{pw}@{url}/{db}'
app.config['SQLALCHEMY_DATABASE_URI'] = db_url```all the user, pw, url, db are them env vars
ionic pecan
show the contents of config
ionic pecan
thats okay
quiet ermine
I'll just screenshot
ionic pecan
def get_env_var(env_var):
if env_var in os.environ:
return env_var
return
im not sure why you're using this function compared to just os.environ.get('POSTGRES_XYZ'), but re-read the function you wrote
you're returning the argument you give it when it's in the environment
so if you run get_env_var('POSTGRES_FOOBAR') and POSTGRES_FOOBAR is set then it just returns POSTGRES_FOOBAR
quiet ermine
Well I am just stupid
Fixed
Hmm password auth failed
Intreguing
That should 100% work
ionic pecan
is POSTGRES_PW set in the environment
ionic pecan
how did you give the postgres user his password
polar osprey
Guys I've got a stupid question for you: On my webserver I have MySQL 5.5.6 installed and I just found out that MySQL 8 ist the current version.
However if I run apt-get install mysql-server I get
mysql-server is already the newest version.
Can anyone explaint to me why?
ionic pecan
sudo -u postges psql
then \password <username> and copypaste the one you have in the env var
quiet ermine
π
ionic pecan
@polar osprey debian doesn't do "current version", debian does "stable version"
for postgresql I use their apt repository, for mysql you should probably use https://dev.mysql.com/doc/mysql-apt-repo-quick-guide/en/
i think debian distributes mariadb instead of mysql now but I'm not sure
quiet ermine
Ah
I messed up pipenv somehow
Restarting instance
@ionic pecan It works!
Weird thing is that I thought I done that command?
I definatly set the password for it, prehaps I done it for the user rather than the other one (brain fart)
torn sphinx
hi, i need some help with firebase
ionic pecan
glad to hear π
torn sphinx
i'm really new to it, and i want to make a score database. however, whenever i add stuff to it, it always puts the data under the current token or whatever
so it looks something like this:
{"-LcljmHLim1-JYwviKq5":{"score":25},"-LclnClwLmrBffVYA84o":{"score":100},"-LclnpOwUk30AftnCpez":{"score":100}}
so how would i be able to change the tokens to user ids?
i want to use it for my discord bot
ionic pecan
what are you storing
ionic pecan
what did you use to create the tables?
ionic pecan
no, i mean, how did you create the tables in the first place
quiet ermine
Just with the python shell
> from x import db
> db.create_all()```Will find out migrations tomorrow and proper ways
ionic pecan
you should look into alembic
quiet ermine
Until then, thanks for helping :)
unreal tartan
flask migrate actually uses alembic, so it's gonna be fine.
unreal tartan
how did you do the migration?
quiet ermine
flask db init flask db migrate flask db upgrade
All with no errors
But no changes detected?
(Brb)
unreal tartan
You must have model classes somewhere right? Representing your tables. like
class Model(Base):
__tablename__ = 'tablename'
column1 = Column(....)
Did you change the type of the column there?
unreal tartan
in the alembic docs it reads
Autogenerate can optionally detect:
Change of column type. This will occur if you set the EnvironmentContext.configure.compare_type parameter to True, or to a custom callable function. The feature works well in most cases, but is off by default so that it can be tested on the target schema first. It can also be customized by passing a callable here; see the section Comparing Types for details.
So I think it won't see column type changes by default.
Did flask db migrate create a migration file (usually in a folder called 'versions' or so)
quiet ermine
Shouldn't that be pretty important if you are migrating the db?
Β―_(γ)_/Β―
Yup, everything normal
unreal tartan
So, it made a new file?
quiet ermine
Could not determine argument, please use a 0 or 1!
Creating directory /home/node-admin/jilkpw/migrations ... done
Creating directory /home/node-admin/jilkpw/migrations/versions ... done
Generating /home/node-admin/jilkpw/migrations/README ... done
Generating /home/node-admin/jilkpw/migrations/script.py.mako ... done
Generating /home/node-admin/jilkpw/migrations/env.py ... done
Generating /home/node-admin/jilkpw/migrations/alembic.ini ... done
Please edit configuration/connection/logging settings in '/home/node-admin/jilkpw/migrations/alembic.ini' before proceeding.
Could not determine argument, please use a 0 or 1!
INFO [alembic.runtime.migration] Context impl PostgresqlImpl.
INFO [alembic.runtime.migration] Will assume transactional DDL.
INFO [alembic.ddl.postgresql] Detected sequence named 'boosted_id_seq' as owned by integer column 'boosted(id)', assuming SERIAL and omitting
INFO [alembic.ddl.postgresql] Detected sequence named 'servers_guild_id_seq' as owned by integer column 'servers(guild_id)', assuming SERIAL and omitting
INFO [alembic.env] No changes in schema detected.
Could not determine argument, please use a 0 or 1!
INFO [alembic.runtime.migration] Context impl PostgresqlImpl.
INFO [alembic.runtime.migration] Will assume transactional DDL.```I can't really understand the error
Well, warning
'servers_guild_id_seq' as owned by integer column 'servers(guild_id)', assuming SERIAL and omitting is the one, I think it omitted the change
unreal tartan
can you type 'alembic' on the console, does it recognize that command?
unreal tartan
okay, do alembic revision -m "change integer to biginteger"
It'll create a file. it should say that.
quiet ermine
FAILED: No config file 'alembic.ini' found, or file has no '[alembic]' section
Should I touch alembic.ini
unreal tartan
okay, cd into /home/node-admin/jilkpw/migrations/
quiet ermine
Ah
unreal tartan
That's where the alembic.ini is
(and that file will probably hold your database connection data, normally)
I think
unreal tartan
on top under [alembic] add script_location = versions I think versions is a folder there.
unreal tartan
And also sqlalchemy.url = .... that's the whole user:passw IP string
including sql+postgres in the front, it's exactly the same string as you had issues with in the beginning.
quiet ermine
Huh
unreal tartan
It's that string "f'postgresql+psycopg2://{user}:{pw}@{url}/{db}"
quiet ermine
Hmm weird
FileNotFoundError: [Errno 2] No such file or directory: '/home/node-admin/jilkpw/migrations/versions/versions'
Seems it was already operating in it
unreal tartan
hmm, probably because flask is doing some configuration for you π€
Maybe undo the configuration changes, take away the 2 lines again.
quiet ermine
π
unreal tartan
I don't understand though. if you do flask db migrate it should make a new file in the versions dir.
quiet ermine
I will continue it tomorrow where I am a little more awake π
Nothing is in there
Strange
Cya π
unreal tartan
π
quiet ermine
Also thanks for helping :) bit late
maiden halo
Need a little help here... For some reason this
sql = f"SELECT point_progress FROM all_users WHERE discord_id = '{ctx.author.id}'"
cursor.execute(sql)
result=cursor.fetchall()
print(sql)```prints everything in point_progress from all_users
completely ignoring the WHERE
any ideas on that?
wind pelican
could be an issue with how you inserted them. as in, perhaps they all have the same id.
also you should NOT use string formatting to put variables in to your sql statements. it is SUPER dangerous
subtle galleon
c.execute('SELECT * FROM stocks WHERE symbol=?', t)
print(c.fetchone())```
I know that in sqlite3 it's done like that.^Given as two arguments with the second argument being a tuple.
Even if it's one variable. The ?s and the items in the tuple will match up.
https://docs.python.org/3.7/library/sqlite3.html
From the documentation tutorial. : )
wind pelican
yep. also as far as your where clause the only thing i can think of is that either: you didnt select the column you are checking, but i would have expected an error if thats the issue. or your data in your database all has the same discord_id @maiden halo
but you def need to move away from using string formatting either way
subtle galleon
I think discord_id is the column name.
wind pelican
yes
maiden halo
Not sure how else to format
wind pelican
drhorrible showed you some examples and documentation
maiden halo
subtle galleon
Insert the variables into a tuple and use question marks.
wind pelican
what type is the discord_id column? perhaps your '' are messing it up
maiden halo
it's long
wind pelican
'' makes it a string iirq
subtle galleon
^Yeah.
wind pelican
so switch over to the format drhorrible showed you and you wont have to worry about it
maiden halo
Thing is if I do a command for returning something from the db
it usually looks like ('data',)
so I make that into a string and match it with f"('{data},') "
subtle galleon
I'd personally use the str() method to convert things to strings if I were you. Using single quotes is a bit confusing. Right now, in your f-string, you are converting that comma into a string too.
I'm also not sure what you mean by returning something from the database. Like, selecting and printing it? Why would you have to match that?
maiden halo
I do str(fetchresult) and then compare it to f"('{data},')"
so I can see if stuff matches? π€
How else can I check for matching ID for example?
subtle galleon
Okay, so str(fetchresult) returns ('data',)?
maiden halo
yes
maiden halo
So what is? ^^'
wind pelican
if you have ('data',) then you have a tuple, aka a kind of array
to get 'data' you just do ('data',)[0]
which means if fetchresult is ('data',) then fetchresult[0] is 'data'
subtle galleon
print("Row ID | Name | ID | Color")
data = await cursor.fetchall()
for item in data:
print(f"{item[0]}.) | {item[1]} | {item[2]} | {item[3]}")```
This is a snippet from my bot.I can index, so I can only assume you can too.
wind pelican
yeah here drhorrible uses indexing to pull each item out of a tuple with 4 entries
subtle galleon
(That's using aiosqlite, which is the asynchronous version of sqlite3.)
wind pelican
thats item[0] to item[3]
maiden halo
cursor.execute(f"SELECT discord_id FROM all_users")
results = cursor.fetchall()
print(f"('{ctx.author.id}',)")
print(results[0])
if str(f"('{ctx.author.id}',)") in str(results):
sql = f"SELECT point_progress FROM all_users WHERE discord_id = {ctx.author.id}"
cursor.execute(sql)
result=cursor.fetchone()
print(sql)
result=int(result)[0]
if result > 5:
sql=f"UPDATE 'all_users` SET `point_progress` = '0' WHERE ID={ctx.author.id}"
cursor.execute(sql)
sql=f"SELECT points FROM all_users WHERE discord_id={ctx.author.id}"
cursor.execute(sql)
result=cursor.fetchone()
sql=f"UPDATE 'all_users` SET `points` = '{int(result)+1}' WHERE discord_id={ctx.author.id}"```heres my full code
it's a bit messed up now cuz I'm trying stuff
these return the same value btw
wind pelican
i feel like you have a couple of issues here.
the first one is that you are trying to ignore the type of all your data.
fetchall returns a tuple of rows with columns of data in it
if the discord_id or any other column is a long then you should not be passing it a string, which you are doing when you put '' around a value in your sql statement
str(results) is something you would basically never want to do
it takes real python objects that you can manipulate and index, and lumps them all in to one string
since you only select one column, i believe it is returning an array (in the form of a tuple) with one entry for each result
if you want to know if ctx.author.id is one of those options you can just do if ctx.author.id in results:
because if results is (50,23,14) then 23 in (50,23,14) is True
wind pelican
oh i see the issue
print(results[0])
this shows (data,)
but results is
( (data,), (otherdata,))
subtle galleon
Isn't it a list of tuples?
subtle galleon
Mhm, like in my example.
wind pelican
then for each item in results, you should compare the first entry to ctx.author.id
cursor.execute(f"SELECT discord_id FROM all_users")
results = cursor.fetchall()
for row in results:
if row[0] == ctx.author.id:
pass # here you know the current row has that id```however, i dont think you need to bother even checking separately like this
it looks like your goal is to take a specific users current points and increase it by one
maiden halo
yes
Well and based off how many progression points they have increase another value and set the progression back to 0
wind pelican
so the easiest way to do this is just do select point_progress, points from all_users where discord_id = ?
check if this returned any results, if so:
then you can check their points and point_progress
if they have the required values you then just need to do a single update to change both of those values at the same time
subtle galleon
That's a slick solution and probably the best.^
wind pelican
you also dont need to do fetchall, because if you have more than one entry for a single discord_id your database is messed up
you can just fetchone
maiden halo
Yeah I figured that just now π
subtle galleon
Fetchall is just overkill.
maiden halo
I'll try your solution
wind pelican
fetchall returns a list of rows, each with all of the columns you selected
fetchone returns just a single list with your columns
makes it a bit easier to code
subtle galleon
Easier, yes, but fetchall is possible if you really love it for whatever reason.
wind pelican
yep
to know if your query had any results, compare it to None: if results is not None:
this is for fetchone of course
subtle galleon
if results: if you like implict.
wind pelican
tru
quiet ermine
Getting role "x" does not exist when trying to migrate db with flask-migrate
All roles are setup fine
maiden halo
for row in results:
print(row[0])
print(ctx.author.id)
ok this prints way more than necessary
wind pelican
what is your query code?
subtle galleon
What's the output?
quiet ermine
Nevermind
maiden halo
cursor.execute(f"SELECT discord_id FROM all_users")
results = cursor.fetchall()
for row in results:
if row[0] == ctx.author.id:
pass # here you know the current row has that id```the output is 3.6k lines from a DB that has 38 entries
xD
subtle galleon
How many columns?
wind pelican
whats your fullcode from the execute statement to the end of the loop?
maiden halo
cursor.execute(f"SELECT discord_id FROM all_users")
results = cursor.fetchall()
for row in results:
print(row[0])
print(ctx.author.id)
if row[0] == ctx.author.id:
#print(ctx.author.id)```hm that commented out line means either an indentation error or its executing other code in your loop
maiden halo
that commented line is just there because it didn't return anything from there
wind pelican
but remember in order to do what you wanted you dont need to select all of the users
maiden halo
so I wanted to make sure it doesn't
wind pelican
however are you sure there are only 38 entries?
i feel like unless there is other code in your loop you might have more than 38 entries
did you set the discord_id to unique in your schema?
maiden halo
omfg it actually spammed my db like crazy xD
guess there's more
sec
gonna clean em up
wind pelican
yeah you prob inserted a ton of copies
you need to make sure your 'unique ids' are actually set to unique so you dont get duplicates
wind pelican
i assume the insert code is someplace else
subtle galleon
More.
subtle galleon
Yeah, I'm dumb. You weren't selecting all columns from the table. Well over 1000.
wind pelican
so two things fix this,
one set discord_id to be a UNIQUE INDEX in your schema and reapply it to the db
and two check if the discord_id is already in the table before inserting the user
maiden halo
Lemme do it on a clean db xD
wind pelican
that way sql throws an error or something if you try to add the same user twice
and that way you can update instead of insert if they are already there
maiden halo
well the code that inserts them isn't here.
wind pelican
yeah but i expect its why you have so many rows
maiden halo
They're inserted on_ready and updated on_message
wind pelican
when you thought you would have 38
subtle galleon
Entries are inserted on_ready?
wind pelican
cause you should only insert a new row when a user that has never been seen before tries to use the system
hm yeah seems like you should be inserting via either watching for some sort of !playgame message or for the first time the bot sees that user join the server/send a message
and then never insert that user again, just update
subtle galleon
Yeah, you can probably use the on_member_join join event listener, and then check if the member.id is in the db.
maiden halo
Ah now for some weird reason it doesn't check if the IDs are the same and keeps shoving users into the DB
Worked before, didn't touch the code, broke now
I really appreciate the help, but I'm way too tired right now to try and think of a solution now. I'll try in the morning. Thanks for the help and goodnight ^^
raw onyx
i have a question in terms of speed
would it be better to reset the cursor every time a function is called?
or would it be better to create 1 instance of the cursor that is used for every function that needs it?
wind pelican
i believe you should use a single cursor as long as you can perform operations on it sequentially, eg dont do any async/other process stuff with that cursor until you commit your transaction
raw onyx
if that's the case, i'll have to re-define it every time
wind pelican
also it looks like multiple cursors might be unsafe in general, perhaps due to the fact that you might use data from the database before another cursor commits which will result in incorrect behavior
raw onyx
well, im making a discord bot, and currently i have 1 global connection to the database and the cursor gets re-defined every time in async functions
wind pelican
i think the ideal method is to create a single cursor that you can access from any function and then complete your entire transaction without calling any async stuff
eg if you need to select and then update, collect every thing you need to perform both before you start your operation and dont await until you have commited it
raw onyx
the thing is, im using an asynchronous database library
wind pelican
hmhm
raw onyx
so avoiding async isn't really an option
wind pelican
well then it prob depends on your library
i was assuming the normal sqlite db
youll have to check the specifics of your library
raw onyx
it's just aiosqlite3
wind pelican
i think, looking that this, you prob will want to make multiple cursors
but i dont know what the results of writing to the same entries will be, as long as a row only relies on its own data to be considered valid its prob ok
but like trying to maintain a tree or other structure in the db might be complicated
tawny sail
is it possible to make a query to a database in an async maner?
and is it recommended?
patent glen
what kind of database?
there are async libraries for some databases, and it can be useful
with sqlite specifically there's not much benefit (it can't save you from deadlocks, so you still have to be careful not to have more than one thing working with the database at a time, and since it's a local file you don't have to deal with network delays)
tawny sail
mysql
polar osprey
Guys I'm unable to connect to my MySQL Server via my Python script after I upgraded it from 5.5 to 5.7
mysql.connector.errors.InterfaceError: 2003: Can't connect to MySQL server on 'host:port' 10061
Any Ideas?
ionic pecan
is mysql running?
polar osprey
yes
I'm using plesk and via the plesk site I can access the database using phpMyAdmin. Also the website which uses the same database is running
ionic pecan
is mysql listening on the ip you try to contact it on?
polar osprey
I found the error
The upgrade generated a new my.cnf file which only had two lines in it:
!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mysql.conf.d/
I had to remove the second line
I had to do this because under /etc/msql/mysql.conf.d/ there is a file "mysqld.cnf" which contains bind-address = 127.0.0.1
glossy dock
Hello, I've a more general question about RSS feeds and databases:
does it really make sense to store the ids of all the new items you fetch with your feeds and then always check the db for a new id being in it to check if it's new?
I am dealing with some feeds that don't have much information to go off and read up that the db check is the standard practice, but what if I'm getting enough entries to reach tens of thusands per month? Wouldnt this cause issues later down the line
errant phoenix
guys
anyone familiar with postgres jsonb?
setJSON: () => {
return db.query(
`with poll_answer as (
select ('{'||index-1||',value}')::text[] as path
from "Polls"
,jsonb_array_elements(answers) with ordinality arr(answer, index)
where answer->>'answer' = 'dick'
)
update "Polls"
set answers = jsonb_set(answers, poll_answer.path, '"jimi.hendrix@gmail.com"', false)
from poll_answer
where answer = 'dick';`
).then(res => res)
},```I'm trying to update value of object in an array
through jsonb
the first part with poll_answer that responsible to find the index where the object is located
and jsonb_set is the update operation
gleaming parcel
hi
i need osme help with databases
im creating a discord bot
im trying to create a leaderboard based on points
points are given by an admin
im using pgadmin4
how would i get users into the database and the points
rain field
Create a table
carmine heart
You can use their id, since they are guaranteed to be unique for each player
rain field
With discord id/ points
gleaming parcel
rain field
Then in your bot file create a cursor
gleaming parcel
this is hwat i have so far based on a lvl tutorial i watched
rain field
Wait what are you using?
rain field
Never used that I used MySQL but yeah you need a Library I think
rain field
Iβm not at home for now
merry garden
You need an async postgresql lib
gleaming parcel
i got asyncpg
rain field
Okay then create a cursor
merry garden
Search for some basic asyncpg examples and start from there
merry garden
So start with basics π Doesnt need to be discord related directly
gleaming parcel
only reason im using it though
merry garden
But you still need the knowledge, no? π€
merry garden
Guess youre done already then, gl
merry garden
I am
gleaming parcel
huh
merry garden
But you could learn something
gleaming parcel
only if u told me
gleaming parcel
absolute pain in the ass
merry garden
Whatever you prefer, in your ass
rain field
Trust me once you know how it works itβs ez pz
Took me 8 hours of practice to become a SQL pro
Everything from inserting updating to deleting checking selecting
indigo mason
@gleaming parcel Please do not
You need to learn SQL
Then read the asyncpg docs
This has been explained to you
gleaming parcel
ur following me
gleaming parcel
trying to ruin this or me
seem very dedicated
i cant even learn
like fuck off
blocked
indigo mason
I'm trying to help you and you're refusing everything we try
Goodness
At least we tried
I guess some people just can't be helped
carmine heart
Okay, what exactly is going on in here?
gleaming parcel
hes chasing me from another server
bringing drama here for no reason
i blocked him
indigo mason
@carmine heart A case of someone asking bad/broad questions and when being told to ask something specific, calling people out for trying to start drama etc.
carmine heart
Okay, I don't care about stuff that happens on other servers, so I'm not going to comment on that
gleaming parcel
he wont stop trust me
indigo mason
We've given him resources to learn SQL
We've sent the asyncpg docs
We've sent tutorials
carmine heart
@gleaming parcel Okay, that's enough with the attacks.
@indigo mason It's okay, let's stop it for now
gleaming parcel
literally all people did was tell me things i told them i know and they got angry that i did and when i didnt know something they got even more angry
simple as that
im done
carmine heart
@gleaming parcel So, the problem is that we're not going to write the code for you. That means that you need to be willing to put in some effort when it comes to writing your code. That doesn't mean we can't guide you, but you need to put in some effort as well.
gleaming parcel
bye bye
ionic pecan
bye bye
indigo mason
I really do have a hard time with people like this
If they refuse help should I keep trying? Will they ever see reason?
I don't like to think someone people can't be help
unborn sentinel
It's not worth continuing, All
gleaming parcel
.
rain field
Now you need to execute
So await conn.execute(β your sql stuff in hereβ)
And when you finish always close the connection
await conn.close()
gleaming parcel
what does it do
rain field
Wdym?
gleaming parcel
what does await conn.execute do
rain field
It executes your sql code thatβs in the string
gleaming parcel
if i dont ask ppl will say im getting spoon fed
so whats after await conn.execute
rain field
You just close the connection for safety mesures
gleaming parcel
whats in the brackets for conn.execute
rain field
Your sql code
gleaming parcel
idk where that is
rain field
You gotta learn SQL then
gleaming parcel
ok
gleaming parcel
what should i read to learn it
gleaming parcel
cant find anything about an sql code
carmine heart
true, this is more for setting up your server and how to approach it from python
The main page has more info on the SQL queries itself: http://www.postgresqltutorial.com/
gleaming parcel
so much to read for somethings thats probably like 4 lines
carmine heart
Well, you've picked a fairly complicated project for someone who's just starting with Python and programming in general. That means that it will take you some effort. The discord.py library in itself already uses a lot of intermediate Python concepts, like asynchronous programming, decorators, and classes; in addition, you've also added a database part to your project. It's not impossible to do, some of our staff members have done just that as their first project, but it will take a lot of effort and determination to complete.
gleaming parcel
im fairly good with python and discord py
databases are just complicated to understabd
molten kraken
sorry i have a nub question, trying to write a command for my discord bot users to register themselves to db, it worked when i was just saving the discord ID but when i tried to add in their discord name I started getting an error 'not enough parameters for the sql statement' the non working code looks like this:
@bot.command()
async def register(ctx):
sql = "INSERT INTO users (discord_id) VALUES (%s, %s)"
discord_id = str(ctx.message.author.id)
name = str(ctx.message.author)
mycursor.execute(sql, (discord_id,), (name,))
await ctx.send('You are now registered.')
db.commit()
carmine heart
You need to pack all the parameters in one iterable, so not mycursor.execute(sql, (discord_id,), (name,)), but mycursor.execute(sql, (discord_id, name))
gleaming parcel
should i just use a json file instead
molten kraken
thanks, working now - well once I remember to add name back into the insert statement as well haha
but yeah that was my issue thanks
carmine heart
@gleaming parcel That's possible as well, depending on the number of load/store actions you do. Just make sure not to await anything in the middle of having opened a file
copper echo
Hey so i have litlle problem
when im trying to import my sql file(exported) to the ovh database its giving me error
Dump file invalid : First 0 errors are: Line 35: ERROR: must be member of role \"style\". Line 49: ERROR: must be member of role \"style\". Line 73: ERROR: must be member of role \"style\". ```style is my user i created
before that it was giving me the same error but with other user - postgres
unreal tartan
Does it create a database with OWNER = style?
In which case, the user that is executing the script, and thus creating this database needs to also be a 'style' member.
If you execute this script as user postgres you will have to GRANT style to postgres
Or you could execute this script as the style user
copper echo
i dont have idea
likee
in ovh
i created member style
and im loggining with it to db
but i dont know if its user of db
im executing it as style
php lol
unreal tartan
hmm not sure if you execute it as style already π€
It doesn't create the database itself, so that must already exist? Maybe verify if 'style' is the owner of that as well?
copper echo
hm
its weird
because
and in settings
style has administrator permissions
so i dont know if its owner or its just user
unreal tartan
can you execute sql statement on it?
SELECT d.datname as "Name",
pg_catalog.pg_get_userbyid(d.datdba) as "Owner"
FROM pg_catalog.pg_database d
WHERE d.datname = 'databasename'
ORDER BY 1;
I'm not sure what your DB name is you can probably read that language better than I π
unreal tartan
okay, try ALTER DATABASE mt2bot OWNER style
copper echo
Error: ERROR: option "owner" not recognized
Pozycja: 23
SQLState: 42601
ErrorCode: 0
"Pozycja" is "position" in english
unreal tartan
ah, it's OWNER TO apparently.
copper echo
Error: ERROR: must be owner of database mt2bot
SQLState: 42501
ErrorCode: 0
oh
i logged as style
to db
so
and i dont know password of postgres user
unreal tartan
it's often either nothing or 'postgres' as well
copper echo
oke gonna try
nope
`
PgSQL: FATAL: password authentication failed for user "postgres" class org.postgresql.util.PSQLException: FATAL: password authentication failed for user "postgres
unreal tartan
Damn
I assume the database is created via that website interface, and the website uses postgres user then?
unreal tartan
Maybe try deleting it, and create the dtabase via your sql program where you're logged in as style.
If that works --- and you have no data in the database Β―_(γ)_/Β―
copper echo
i have to login with database name
unreal tartan
the database 'postgres' should exist by default I think
copper echo
jdbc:postgresql://ip:port/mt2bot
hm
oke
yup im in
so now
CREATE DATABASE mt2bot2 OWNER = style?
unreal tartan
without the =
unreal tartan
welp, great
copper echo
i dont think so
unreal tartan
nope
unreal tartan
yea you can try 'grant postgres to style', but I'm afraid you will lack permissions for that as well.
copper echo
Error: ERROR: must be superuser to alter superusers SQLState: 42501 ErrorCode: 0
unreal tartan
yep
unreal tartan
I think you'll have to find a way via the website to either have style have more permissions, or to have that database owner changed via the website.
unreal tartan
try creating the database in the website again.
See if you can edit settings there to grant permissions to style.
Otherwise, try executing GRANT ALL PRIVILEGES ON mt2bot2 TO style
copper echo
my grammar is horrible sorry xd
unreal tartan
well your sql program is doing psql, no?
copper echo
nope, in ovh you are just giving name of db
and its creating
but
in sql program im using my user that i created
in cmd im using root
unreal tartan
uhm
try exeucting psql if that doesnt work psql -U postgres -- If neither of those work then you'll still miss the passwords.
copper echo
im in
so now
GRANT ALL PRIVILEGES ON mt2bot2 TO style?
done
and nothing appears
idk if its good
unreal tartan
you have to end the statements with ;
unreal tartan
It's because you entered something on the previous line already, the postgres-# means you're continueing from a previous line.
postgres=# is good
copper echo
oh
postgres=# GRANT ALL PRIVILEGES ON mt2bot TO style;
ERROR: relation "mt2bot" does not exist
lol?
unreal tartan
does the database not exist anymore? did you delete it for the previous attempt?
unreal tartan
you can type \l to see them (that's a letter L)
unreal tartan
Try putting the database between quotes maybe
copper echo
' or "
unreal tartan
I like single ones :P
copper echo
syntax error
postgres=# GRANT ALL PRIVILEGES ON 'mt2bot' TO style;
ERROR: syntax error at or near "'mt2bot'"
LINE 1: GRANT ALL PRIVILEGES ON 'mt2bot' TO style;
postgres=# ALTER TABLE mt2bot OWNER style;
ERROR: syntax error at or near "style"
LINE 1: ALTER TABLE mt2bot OWNER style;
em btw
unreal tartan
oh yea, I forgot the TO again, OWNER TO
unreal tartan
it's alter database, not alter table. I typed that wrong, my bad.
unreal tartan
yep, good luck π
copper echo
thank you soooo much <3
i was trying to dfo this from yesterday
ehh
still
Dump file invalid : First 0 errors are: Line 48: ERROR: must be member of role \"style\". Line 63: ERROR: must be member of role \"style\". Line 89: ERROR: must be member of role \"style\".
those are strange line numbers, is that the same file from the "php" link above?
copper echo
yup
unreal tartan
so it's actually the create table which fails, which is so weird, because the script is ran as user 'style' you said, yet it doesn't look that way
copper echo
ugh
unreal tartan
Assuming the script maybe runs as postgres user. you could
ALTER DATABASE mt2bot OWNER TO postgres;
GRANT style TO postgres;
Then the postgres user shouldn't have issues giving owner to style, I hope.
copper echo
so
i did it two times
(to be sure)
and i imported db
but
still
the
same
error
Dump file invalid : First 0 errors are: Line 48: ERROR: must be member of role \"style\". Line 63: ERROR: must be member of role \"style\". Line 89: ERROR: must be member of role \"style\".
In that case I'm just totally out of ideas :/
torn sphinx
As a beginner to databases, which db should I consider learning?
copper echo
ugh
torn sphinx
I heard MongoDB is good?
neat bolt
Hi all, I'm starting to understand how relational databases work, and how to query with SQL. I'm stuck on how to actually design my own schema. I have a lot of data that is contained in json files that all relate to each other. What is the best way to go about parsing through the data and figuring out where there should and shouldn't be relationships?
ionic pecan
@torn sphinx not sure where you heard that, the standard recommendation is postgresql
the postgres docs have great intro docs including an SQL tutorial
@neat bolt that's a bit hard to say like that because it's mostly a skin that you gain whilst you design your schema. can you show us yours and tell us what you had in mind so far?
gleaming frost
@torn sphinx If you want a non-SQL database
MongoDB is a great start
But go for a SQL database. Don't be like me xd
gleaming frost
Ok so
SQL is by far the biggest most mainstream database service
There are lots and lots of different flavours
Non-SQL is ever other database structur
It's a bit like people saying there's Windows OS and the non windows OS's
SQL is so big it gets its own catagory
@ionic pecan Do you know who did most of the work converting from rethinkdb to SQL?
I'm looking to do the same thing
gilded narwhal
sql is a language
gleaming frost
cough I meant that
gleaming parcel
how to use a database with discord.py to create a currency system
i got a database setup with pgAdmin4
and i have it connected to my discord bot
I have a user_id and points table in my database
i dont know what to do now
gleaming parcel
still no response
molten kraken
with the caveat that i'm also still a beginner... figure out how you want your users to acquire and/or spend points as a first step, cos i don't think there's much that the more experienced people here will be able to do to help without knowing what you're wanting to achieve
then you can likely look at creating a command structure or use message events or whatever to update points in your table
depending on what you want to do
gleaming parcel
im making a command that allows someone to give users points
and users with the most points are on a top 5 leaderboard
molten kraken
not sure if its the sql statements you need help with or writing bot commands to execute them
if the former, that should help i think
gleaming parcel
already read it
molten kraken
ah ok, is there a particular point of confusion?
vestal gate
Hello there
Can ssomeone help me with an update on a table?
I have a table named "T" and a query named "Q" . I want to update the column "T"."C2" with values located in "Q"."C2"
based upon "Q"."C1" = "T"."C1"
SQL```
update "T"
set "T"."C2" = "Q"."C2"
from "T" inner join "Q"
on "T"."C1" = "Q"."C1"
I am trying this
but I get 1: firebird_sdbc error:
*Dynamic SQL Error
*SQL error code = -104
*Token unknown - line 3, column 1
*from
caused by
'isc_dsql_prepare'
ionic pecan
never seen a from in an update
nimble arch
So I want to execute some sql like
SELECT ID
FROM tablename
WHERE last_digit_of_id IS x
Solution:
SELECT ID
FROM tablename
WHERE ID LIKE '%x'
ORDER BY ID DESC;
unique wraith
So doing this in PHP and not Python, but this is an SQL-specific chat so I think it'll be ok.
So I'm trying to select two columns from two separate tables with a user's userid in MySQL.
What I have so far:
SELECT main.email, prof.pfpURI FROM `blogmain` main RIGHT JOIN `userprofiles` prof ON main.userid=prof.userid WHERE userid=?```
I keep getting `#1052 - Column 'userid' in where clause is ambiguous`
Anyone know how to fix this and what I'm doing wrong?
proper copper
Need to specify which table is userId associated to
gleaming parcel
Anyone on?
I got a currency system using PostgreSQL and Asyncpg for my discord.py bot but I want to be able to create a leaderboard for who has the most points
how would I go about doing this
torn sphinx
Create a table to store points you already did this
Create some functions in your bot that reads the leaderboard
Create some functions that add points
gleaming parcel
i can give users points
ionic pecan
Use order by in your select queries
e.g. SELECT username, amount FROM wallets ORDER BY amount DESC
gleaming parcel
how do i remove <Record points=number>
i put the points into a list
and when i print the list
i get <record user_id=>
ionic pecan
you'll need to show us your code
gleaming parcel
@commands.command()
async def leaderboard(self, ctx):
users=await self.bot.pg_con.fetch("SELECT points FROM users")
user=await self.bot.pg_con.fetch("SELECT user_id FROM users")
users.sort(reverse=True)
embed=discord.Embed(color=0xf442df)
embed.set_author(name=f"Points Leaderboard - Top 10", icon_url=self.bot.user.avatar_url)
embed.add_field(name=f"<@{user[0]}>", value=users[0]['points'])
embed.add_field(name=f"<@{user[1]}>", value=users[1]['points'])
embed.add_field(name=f"<@{user[2]}>", value=users[2]['points'])
await ctx.send(embed=embed)
you'll be looking at the users part
user*
errant phoenix
guys need help with jsonb syntax for sql
return db.query(
//Find the index where the target value located, then update
`with poll_answer as (
select ('{'||index-1||'}')::text[] as path
from "Polls"
,jsonb_array_elements(answers) with ordinality arr(answer, index)
where answer->>'answer' = '${answer}'
)
update "Polls"
set answers = jsonb_set(answers, poll_answer.path, '{"votes":${numVotes},"voters":["sadf"]}', false)
from poll_answer
where permlink = '${permlink}'`
).then(res =>
res)```
this somehow updated the whole object in array
how can i still keep a particular object while adding new one
i hope someone can understand this code@gleaming parcel that entire code makes a few wrong assumptions about SQL
gleaming parcel
dw i fixed it
ionic pecan
show
ionic pecan
not sure where i was addressing you
ionic pecan
I meant @gleaming parcel, can you show your updated code
errant phoenix
hmm no one cares about jsonb π¦
inner pecan
Can anyone help me write a select statement to do what I want in sqlite3 python?
This is my main table
I want to do a SELECT statement with the exchange code column and how many times it appears (but no duplicates in the symbol column)
I had this
df = pd.read_sql_query("SELECT ExchangeCode,count(*) as TotalErrors from MainTable group by ExchangeCode ORDER BY TotalErrors DESC", conn)```
but I realised this isn't doing what I want as its counting multiple times for symbol duplicatese.g
USQ should be 1 for example.
dull scarab
You could count them in python itself?
inner pecan
but its easier to just do a select no?
dull scarab
If one knows how I guess.
late palm
select and distinct and join @inner pecan
dull scarab
subquerry for uniques with something like SELECT DISTINCT from table?
late palm
SELECT DISTINCT <TABLENAME.COLUM> <TABLENAME.COLUM> FROM <TABLENAME> TABLENAME> WHERE <TABLENAME.COLUM> ORDER BY <TABLENAME.COLUM>
torn sphinx
I want to make a script that inserts data from multiple json files into separate tables. The datatypes are the same but I wan't to use multiprocesing/threads to accomplish this since the number of files is over 100. I'm guessing it's not possible for multiple processes to write to a db at the same time even if it's to different tables. What are my options in such a case? Only thing I can think of is having a sepparate db for each file but that seems really messy.
I have to add that the json files are pretty large aswell about 10mb each, which is why I can't just do them one at a time.
copper echo
Hey
so i want to strone in my column number like 1.2 and in future get it and add something to it
what type of column i should take?
i was thinking about float, but im not sure what that (n) means
ALTER COLUMN level_multiplier float(n) NOT NULL
DEFAULT (1)```btw i want to update existing table
kind fox
What is an OperationalError with sqlite3?
hazy mango
https://stackoverflow.com/questions/25387537/inserting-a-table-name-into-a-query-gives-sqlite3-operationalerror-near-sy that what youβre after?
copper echo
okey i found a way to do thing with float
but now
how can i make the default value to empty list?
[]
ADD active_items text[] NOT NULL
DEFAULT ('[]')```ok thank youu
lilac mirage
Is it faster to do "insert or ignore" on a list of users that might be in a table or filter them out via a select statement beforehand?
void otter
i am having trouble using postgres
https://www.github.com/isakal/Tiwwter
i am trying to set up postgres in production instead of sqlite (hosting on heroku)
please ping me or send a pull request
delicate fieldBOT
void otter
i don't quite understand how to connect a postgres db to my app
thick iron
when using the following query in sqlalchemy with postgresql I get an error:
q = sess.query(Messdaten.uhrzeit, func.avg(Messdaten.wechselstrom_leistung))
.filter(Messdaten.uhrzeit > time_threshold)
.group_by(func.date_trunc("hour",Messdaten.uhrzeit))
LINE 1: SELECT messdaten.uhrzeit AS messdaten_uhrzeit, avg(messdaten...```so uhrzeit is in the group_by clause, but as part of a function is there a fix for this problem
or do I need to do it using some kind of join?
brisk harbor
has anyone worked with sqlite3?
I keep getting the error:
c.execute("VACUUM")
sqlite3.OperationalError: database or disk is full
regal edge
What for a package your using for the connection?
Is your file system full?
brisk harbor
Just import sqlite3
regal edge
ok but if more than 1gb free its the page limit from sqllite, not the file system
brisk harbor
yea there is about 9gb free on my hard drive
and im using the database on my other drive
so I should increase the page limit
regal edge
not right away. Should it google too
brisk harbor
yea I am
trying to haha
This is it
cur.execute("PRAGMA max_page_count = 195313")
cur.execute("PRAGMA page_size = 512")
So, for PostgreSQL, how can I check to see if a value is in a bigint[] table?
I thought it would be (for discord.py)
await self.bot.pg_con.fetchrow(f"SELECT * FROM table WHERE column = ANY {ctx.author.id}")
When I try this I get:
Postgressyntaxerror: syntax error at or near 493659821007175680"
The numbers are my my user.id
I don't think you need any
proper vine
Total beginner here. Is it hard to move an existing MYSQL db scheme to a django project in pycharm?
where do I start? googled but haven't found any answers
abstract herald
self.pool = await aiomysql.create_pool(
host = CONFIG["DATABASE"]["DB_HOST"],
port = 3306,
user = CONFIG["DATABASE"]["DB_USER"],
password = CONFIG["DATABASE"]["DB_PW"],
db = CONFIG["DATABASE"]["DB"],
ssl = {'ssl' : { 'ca': CONFIG["DATABASE"]["CA"]}},
)
self._sslobj = self._context.wrap_bio(
AttributeError: 'dict' object has no attribute 'wrap_bio'
What is causing this? 
serene thicket
hey everyone, was hoping someone might be able to help me with or point me in the right direction around a few things.
- On line 43 I have a sql statement to validate if the record exists, but it doesnt work
- It is very very inefficient (slow) - should i maybe make it async?
- Am I taking the right approach in general
Code: https://mystb.in/lonajabaco.py
sidenote: working with MSSQL Server
@plain radish save me please sir!
alpine hawk
Can someone let me know if this code for SQLite is safe from SQL injection?
'WHERE id = ?',
(old_number_of_wins + 1, wins_id,))```no
gilded narwhal
looks ok to me
as long as you're letting sqlite itself sanitize the params you should be fine
mortal crystal
Needing a life saving help plz :)
Getting error in my login system when i try to compare input to database
ionic pecan
@mortal crystal Youβre missing the columns you want to select in the query
mortal crystal
All fixed now thanks
torn sphinx
whats the best postgresql lib for python?
GNU nano 2.7.4 File: pg_hba.conf
host all all trust
host all all 0.0.0.0/0 md5
host all all ::/0 md5
host all all 0.0.0.0/0 md5
is there something wrong with this that would only accept local connections
I have this set in postgresql.conf ```
listen_addresses = '' # what IP address(es) to listen on;
# comma-separated list of addresses;
# defaults to 'localhost'; use '' for $
# (change requires restart)
port = 5432 # (change requires restart)
max_connections = 50 ```
port whitelisted in firewall, but using python im getting ```python
ConnectionRefusedError: [Errno 111] Connect call failed ('xx.xx.xx.xx', 5432)
im able to log into db and view table locally
actually, whenever I changed local all all trust to ```
host all all trust
I removed first line, got it working.
has anyone here worked with timescaleDB?
do I need a hypertable for faster inserting?
I don't need to work with any actual time records
river barn
Does a column with auto incr in postgresql increment also when I manually insert it?
torn sphinx
uuids=# INSERT INTO tbl1 (col1, col2) VALUES ("value for col1", "value for col2");
ERROR: column "value for col 1" does not exist
finite hatch
Hi im new to python. I have created an array of functions and i tried random.shuffle(a). I dont know if it worked. If it did i want to know a way to call all the functions in the array according to their new indexes.
pale crest
@finite hatch This is the database channel, you would be better off asking in one of the help channels.
finite hatch
sorry i post this channel by mistake
pale crest
Ok, no worries.
torn sphinx
I figured it out, postgres is weird
nimble arch
Is BETWEEN in sql including, excluding or partly?
patent glen
@nimble arch inclusive
nimble arch
for both arguments?
patent glen
yes
nimble arch
nice
torn sphinx
Hey I had a question about doing something in SQLite if anyone would be able to help me answer it?
I'm trying to figure out a way to search the entire SQLite database (all columns and rows) for a certain user defined keyword is there any way to do this? I have found the WHERE function however it seems that it is limited to one column
gritty belfry
Cross posting from #help-chestnut
Can anyone help out with comparing datetimes in flask-sqlalchemy
entry = Entry.query.filter_by(user_id=current_user.id, timestamp='2019-04-13').all()
Its a sqlite database so I know the timestamp field is stored as a string but I'm not sure if sqlalchemy turns them back into datetimes when filtering
Also the timestamp field has seconds, 2019-04-13 16:36:15.592823
soft pollen
is there a way to view an in memory database from pycharm while the script is running?
ionic pecan
@torn sphinx you should AND-together your WHERE conditions
or with OR:
WHERE xy = ? OR ab = ?
@soft pollen sqlite?
soft pollen
@ionic pecan yes
ionic pecan
i believe you need to go via the database connection of your script and use that
soft pollen
ok another question. I am writing tests for a project I am working on and I am using SQLAlchemy. If I define all of my tables in schema.py , how do I make sure that all of the classes defining tables get executed
SqlAlchemy does a lot of magic whenever you are defining classes
do I just have to import ANYTHING from the schema.py file? or do I have to import all of my classes to the tables setup
warped shuttle
is it considered bad practice to not capitalize sql keywords?
patent glen
nah
serious answer https://www.sqlstyle.guide/ does recommend capitalizing them
as long as your code uses a convention consistently though i wouldn't call it bad practice
i tend to lowercase keywords and capitalize identifiers
warped shuttle
cool. ty!!
fringe tiger
Oof, so what will happen if I have 2 execute/commit statements almost in the same time? Will the other one be queued or will both be ignored? Sqlite3
Same question but if the database is currently being read? Will it be able to commit/execute?
I read about states and it should work but asking just to be sure
torn sphinx
I got PostgreSQL on my PC with my database and i've got my python discord bot on my raspberry pi. When I try run the bot it wont connect to my database
asyncpg.exceptions.InvalidAuthorizationSpecificationError: Peer authentication failed for user "postgres"
torn sphinx
Quick question so I am using sqlite and have implemented the WHERE var OR var =?, (function) however it says that I have the incorrect number of bindings supplied but I only want to implement one function to all these columns. How can I do this?
Nevermind I figured it out was missing a comma
torn sphinx
I have another problem which is that I've implemented the WHERE var OR var =?, (function,) and it runs however it seems to me as though is simply returns all the rows in the database. I am using cursor.fetchall() so I don't know if that might be the problem
If anyone could help that would be great
quasi holly
Am I the only one who has a problem with exporting/importing in Postgre? I use pgadmin and I can export files but when I import there's always an error and also when I import it never detects the file unless I set the search to all files.
@chilly ravine Yes postgres has a password.
chilly ravine
i know it does, i'm asking whether that person's database login is password protected
quasi holly
oooh soz
torn sphinx
How do I move my database from my windows pc to my raspberry pi
Iβm running raspbian
proper copper
How do you mean @torn sphinx ? As in you want to connect to the database from your Pi or migrate your data to a database on the pi?
proper copper
You would first need to open up a port on your windows computer to allow outside connection (or at least to your local network). Then, you find the IP addr of your windows, say 192.168.1.XXX:<Database Port Number>
Your Pi would use that destination to connect to your database
torn sphinx
i cant dump my database on my windows pc
when i do pg_dump -h localhost -U postgres -p 5432 strykrrbot leaderboard > strykrrbot leaderboard_Latest.dump
i get
'pg_dump' is not recognized as an internal or external command,
operable program or batch file.
nvm i got it
patent glen
@copper echo you left out set data type
hazy mango
Hey, I'm using SQLite3 and I'm trying to make it so that when a name is inserted to the table, if it's already in there, it gets the gender from where it's already in the table and assigns it, e.gpy NAME : GENDER "Athlete 1" : "MALE" "Athlete 1" : #Will become "MALE"(@me upon response please)
ionic pecan
you need to use a separate query for that
IIRC upserts cannot access other rows than themselves
hazy mango
But what would that separate query be?
I mean I've got another way I can do if need be but would be nice to keep it all SQL
torn sphinx
pi@raspberrypi:~/Desktop $ python3 bot.py Traceback (most recent call last): File "bot.py", line 277, in <module> bot.loop.run_until_complete(create_db_pool()) File "/usr/local/lib/python3.6/asyncio/base_events.py", line 466, in run_until_complete return future.result() File "bot.py", line 16, in create_db_pool bot.pg_con = await asyncpg.create_pool(database='strykrrbot', user='postgres', password='321') File "/usr/local/lib/python3.6/site-packages/asyncpg/pool.py", line 400, in _async__init__ await self._initialize() File "/usr/local/lib/python3.6/site-packages/asyncpg/pool.py", line 417, in _initialize await first_ch.connect() File "/usr/local/lib/python3.6/site-packages/asyncpg/pool.py", line 125, in connect self._con = await self._pool._get_new_connection() File "/usr/local/lib/python3.6/site-packages/asyncpg/pool.py", line 463, in _get_new_connection **self._connect_kwargs) File "/usr/local/lib/python3.6/site-packages/asyncpg/connection.py", line 1688, in connect max_cacheable_statement_size=max_cacheable_statement_size) File "/usr/local/lib/python3.6/site-packages/asyncpg/connect_utils.py", line 543, in _connect connection_class=connection_class) File "/usr/local/lib/python3.6/site-packages/asyncpg/connect_utils.py", line 519, in _connect_addr await asyncio.wait_for(connected, loop=loop, timeout=timeout) File "/usr/local/lib/python3.6/asyncio/tasks.py", line 352, in wait_for return fut.result() asyncpg.exceptions.InvalidAuthorizationSpecificationError: Peer authentication failed for user "postgres"
sorry for big wall
but i keep getting this when i run my db on my raspberry pi
river barn
Do not login with the postgres user
make one yourself
https://www.digitalocean.com/community/tutorials/how-to-install-and-use-postgresql-on-ubuntu-18-04 this is a decent introduction
plain dune
am i allowed to send links? im stuck on assignment with little experience and was wondering if i could send my assignment brief and hoping hopefully someone can help me
river barn
Explain the problem and we will help you if we can
fluid root
anyone using postgre and pgadmin?
how to actually close this detached panel π
it has no X button
maiden wigeon
Is there a way to format numbers in a mariaDB view? when using FORMAT() all numbers gets converted to strings which messes up calculations
onyx seal
I'm not sure if this is specifically a database question, but it relates to the pgAdmin tool. I noticed there's a way to install it as a python wheel, but there are no instructions provided by their docs on how to install that. Has anyone had experience installing pgAdmin into a CentOS 7.6 server which has a running postgres installation already? Just trying to weigh up the options as to whether it's better to replace phpPgAdmin with pgAdmin 4 or just leave it as is (my server also has cpanel on it). TIA for responses π
mortal crystal
Hi guys plz help, how do i pass value from sqlite3 to a textinput in Kivy, thank you
torn sphinx
Hello, What is the best way to store both a list of strings and a dict(Str-Str)?
bright dragon
Hi, I am using aiosqlite, and I need to use something like LIMIT, I dont have it enabled, is there anything similar? I want to delete a row from a table, It could happend that this table is more than once, so I want to only delete one of those rows
plain radish
Are there not any differences between the two rows?
If there is, just use the identifiable column data to narrow it down
if there isn't, and you're not intending to add multiple of the same rows, you should fix your schema so you have a unique constraint
bright dragon
I have an Id column, I was trying to indentify the row by that, but couldn't make it work, the row it is not being deleted
async with aiosqlite.connect("barizza_trigger_cartas") as db:
to_be_deleted = await db.execute("SELECT id FROM barizza_trigger_cartas WHERE user_id = '{}' AND carta_desbloqueada = '{}'".format(user_id, carta))
id_to_be_deleted = await to_be_deleted.fetchone()
if id_to_be_deleted is None:
return await ctx.send("You don't have that object")
await db.execute("DELETE FROM barizza_trigger_cartas WHERE id = '{}'".format(id_to_be_deleted ))
await db.commit()
sorry for the spanglish
@plain radish please help me I can't solve it
plain radish
I don't know what you mean by it not working
Are you getting any output
Have you made sure you're getting what you expect when you fetch the original row
and are you sure you're accessing the id correct from the fetched record
maybe log each stage to make sure you're getting what you're expecting
bright dragon
The problem is that the record keeps in the db, it is not beeing deleted
aparantly beacause of: error changing data unique constraint failed
plain radish
if you have an error, please share the whole traceback
bright dragon
The thing is that I could get that error throught a program called "DB Browser for SQLite"
I browsed the data and tried to erase some rows, if I do that I get that error, I think the same is happening with my code
but my program, commits the delete and dosen't show me any error
it's weird
trim birch
hello there, im currently using sqlalchemy and I would like to know how I can query up to a certain number of rows
Paste.query.filter_by(type=0)
this is my current code, it retrieves all snippets with the public type
thanks π
ionic pecan
.limit(n)?
trim birch
thank you π
ionic pecan
sure thing
distant epoch
Hello!
I'm trying to switch from json to sqlite for the data of my bots as nested dicts aren't adapted for my current project.
I've only used sql a few times but it should be enough to understand what I'm doing.
I'm using simple tables like this:
I'm trying to do a function with the server_id, column and value as arguments to update a cell
For example I would like to modify the setting_2 of the server_1.
I would like to update the values independantely from the rest of the raw.
The problem is that I can't manage to do this with UPDATE and REPLACE, I lose the other settings of the raw.
I've seen that you can do an INSERT OR IGNORE then an UPDATE but I need to specify something for each setting of the raw so it doesn't work as a function.
Maybe it isn't the right way to store my data, or I'm missing something.
Does someone has any ideas/tips?
ionic pecan
you should use one row per setting per server instead
distant epoch
you mean invert the settings and servers?
quiet ermine
If something in a db made by sqlalchemy is None/NULL and then get's updated to something like hello, would it throw any errors?
distant epoch
@ionic pecan ohh ok thanks
that's not really intuitive but should solve the problems
torn sphinx
I like postgre
mellow wind
lets say I'm making a stats bot, and it tracks certain metrics of my server
What would be the database best suited for quick read and writes
patent glen
keep it in memory and only write it out occasionally?
olive delta
@river barn The only way I know at this moment is simply create a wrapper for a list and create add, remove, get operations
In the add you'll simply check if the list contains such a value
But still I'm still not familiar with everything related to python so I may be missing something
wintry cove
Hey, anyone who knows about real time database (like Firebase is offering) with JavaScript SDK for client (browser)? Need it for IoT devices with real time updates for analytics (charts, alarms, etc.). Can be paid but we need it locally.
olive delta
This is a python server
not JS
Why do you need it?
Just create a server that will handle connections to the database
or data storage service
wintry cove
@olive delta this is database channel and databases are language agnostic (most of them if they provide connector for language of your choice) and yeah, just create a server with REST API and web socket communication, that's easy to maintain and development is fast. /s
As I said before, a lot of writes from a lot of IoT devices (data like temperature, speed, distance between other IoT devices) needs to be monitored in real time (could be web sockets or MQTT) from other platforms we have in our infrastructure (admin dashboards, LCD panels in factory, ...). We would use Firebase but we need to have it deployed locally. I am already testing few (PubNub and Parse platform) and was just curious if someone has some experience.
torn sphinx
What would be the best way of storing both a list of strings and a dict?
wintry cove
@torn sphinx do you have existing database that you are using? or you are at the beggining and just picking stack?
willow sentinel
Why is money = None even if the user has money in the database?
willow sentinel
Iβm checking if their Iβd is in the database with he currently of the meage that triggered this
Message*
@torn sphinx
Hereβs where the error occurs
torn sphinx
@willow sentinel next question why are you using ORDER BY if you only want money
willow sentinel
Idk i just did
torn sphinx
What is the error?
willow sentinel
Basically it says you cant add an integer and a nonetaype
Unsupported operation type(s) for +: βnonetypeβ and βIntegerβ
torn sphinx
Are you sure money is None?
torn sphinx
Well did you try printing money just to make sure?
torn sphinx
Well did you check if the user actually had money?