so in this case the columns will always be user_id, active_skill, and the active skill xp
#databases
harsh pulsar
in that order
frozen ocean
what i'm doing now is
cursor.execute("SELECT * FROM users")
result = cursor.fetchone()
while result is not None:
...
result = cursor.fetchone()```
And I'm wondering if i know the column header and the index of the tuple (i'm using `cursor.description.index(result[1]+'_xp', None, None, None, None, None, None)`), is there anyway i can use the cursor where it is to modify that row only? That way I don't have to use WHEREah, yes
try the cursor.description attribute
i don't know how standard it is across databases, i know it works with sqlite
colnames = [desc[0] for desc in cursor.description]
result = dict(zip(colnames, cursor.fetchone()))
something like that
the .description is part of the dbabpi spec so most sql libraries should support it
torn sphinx
Do I have to commit when UPDATING records?
runic pilot
only if you're using a transaction or session
torn sphinx
Oh so I don't, ok then thank you!
runic pilot
are you using a library to work with your db? usually they start a transaction for you
torn sphinx
Hi, I have a question about mongodb transaction. If I do multiples delete and after that make one agregation, the aggregation will consider the delete in the same transaction?
runic pilot
never checked, but I would assume so based on it following ACID principles as stated here https://www.mongodb.com/transactions
but since it's mongo it's super easy to create a new collection with a document or two and try it out
frozen ocean
in sqlite3, will a cursor execute at the location it is at if not given a where argument? for example:
users_db.row_factory = sqlite3.Row
cursor = users_db.cursor()
cursor.execute("SELECT * FROM users")
result = cursor.fetchone()
cursor.execute("UPDATE users SET active_skill = ?", (new_skill, ))```not sure on the answer but generally you shouldn't re-use a cursor, especially for a different operation
also "select *" will scan all the way through the whole table and put the cursor at the end
frozen ocean
ah i see, but doesn't fetchone() move it to the top of the table? and everytime its called move it down one
runic pilot
again- not sure on the answer, but you can experiment and see for yourself
though I would expect "UPDATE users SET active_skill = ?" to update every row in the table
frozen ocean
it didn't work either way haha, well i ended up just creating another cursor to UPDATE users SET active_skill = ? WHERE user_id =
thanks for the help guys! i learned a lot in the process, i find the docs very scarce in supplying all the information
tall spoke
can someone help me trouble shoot this code for asyncpg:
async def transaction_postgresDatabase(query, *args, timeout=None):
con, pool = await connect_postgresDatabase(mainPool)
tr = con.transaction()
await tr.start()
try:
await pool.execute(query, *args)
except Exception as e:
print(e)
pass
else:
await tr.commit()
await con.close()
when i send: UPDATE discord_data.users SET experience = $1 WHERE id = $2 and servID = $3
it doesn't update the value in the database, although this works in the postgres CLI
It doesn't throw ANY ERRORS
harsh pulsar
@tall spoke what if you just print something silly in the except like "asdfasdf"
also is this asyncpg?
tall spoke
yeah
harsh pulsar
also instead of all tr.start() stuff you can just write:
with con.transaction():
await pool.execute(query, *args)
generally it's bad practice to have catch-all exception handling
oh wait
hold on
you're writing pool.execute
can you provide the source for connect_postgresDatabase?
tall spoke
async def connect_postgresDatabase(pool):
con = await pool.acquire()
try:
return con, pool
except Exception as e:
print(e)
yeah im aware of the Exception thing. I do this when starting new code to see which errors if any i may have to account for
btw i added some extra print statements and it doesn't go to the exception
it goes to the else
harsh pulsar
right
its cause youre calling execute on the pool, not the connection
im not sure what that does, but you just are not using the connection you think you're using
async def transaction_postgresDatabase(query, *args, timeout=None):
con, _ = await connect_postgresDatabase(mainPool)
with con.transaction():
await con.execute(query, *args)
that's all you need
pliant spire
Guys, im a little confused about aws ec2's, we get charged for streaming data out? Like, i make a request to a server, save a chunk of the request in a variable, then write that to a different server...ill get charged extra for doing that?
torn sphinx
Hello I need some help. I am using the aiomysql library for my discord bot but the connection keeps getting disconnected after a few hours.
My main file where the DB connection is made: https://paste.ofcode.org/KNiYufzaE88F5PG9LrSrwi
Where i am using this conenction: https://paste.ofcode.org/4RBxV4whJdabRWFxa3ThkS
I don't know if its because something is blocking or what the reason is for this issue. But it works until after like maybe 10 hours the connection is lost to mysql.
I also have this in my on_message() which uses a function that makes a DB query: https://paste.ofcode.org/YmHPL4h8r2qxMhRMmuf7Ws
modest matrix
I was thinking "huh, this question sounds familiar" but you're the same person lmao
I believe last time we told you to use a connection pool, so why did you not end up doing it
@torn sphinx
pearl heath
@harsh pulsar I don't know what to say, it doesn't show a value for anything like its doing something but not returning anything apparently even though an unlabled function variable is a NoneType and is the return value of internal_local_database_lookup and the following line in the control loop validate_user_input doesn't catch that NoneType, or False, or None, and everything skips to the exception . Removing the try/except doesn't change the behavior, it goes to the else.
if internal_lookup == None:```any call to either Compound or Composition tables in the DB using query just doesn't work past the test stuff on lines immediatley following redprint("made it this far") and if you run the script, you will see that test query works there, but not elsewhere. It worked before, the expected behavior is that a new lookup is not found in the DB and that function is supposed to return a None in that case, returns even less than that apparently
coarse coral
class Services(db.Model):
id = db.Column(db.Integer, primary_key=True)
seller_id = db.Column(db.String(50), default=User().get_id(), nullable=False)
p_img = db.Column(db.String(100), nullable=False)
p_img_two = db.Column(db.String(100), nullable=False)
p_img_three = db.Column(db.String(100), nullable=False)
p_img_four_five = db.Column(db.String(100), nullable=False)
service_name = db.Column(db.String(100), nullable=False)
service_price = db.Column(db.String(50), nullable=False)
service_category = db.Column(db.String(50), nullable=False)
multi_service = db.Column(db.Boolean, nullable=False)
Do you think I should have a table for images? or do you think the way I currently have the services table is fine ?
torn sphinx
I was thinking "huh, this question sounds familiar" but you're the same person lmao
I believe last time we told you to use a connection pool, so why did you not end up doing it
@torn sphinx
@modest matrix Yeah error from last time i fixed. I copied same code over but this was different error with the request library
modest matrix
if you have an error, it might help if you send it here
torn sphinx
someone told me earlier it maybe the request library blocking
so i use now aiohttp
i will now monitor and watch for error
modest matrix
and again, use a pool, because according to your code, you arent
torn sphinx
i dont know how to use pool 😭
But it works fine without pool in my other bot with 0 issue. The problem last time was not using the async library of mysql and something was blocking. After this no problem
modest matrix
well lets just wait and see i guess
torn sphinx
i see example online but they are confusing, they use like loop or something
modest matrix
if it disconnects again, ping me and Ill try to explain it
torn sphinx
Ok thanks 🙂
neat umbra
Ugh
How to return a list or random document from pymongo's collection.find()
I get a typeerror saying "MotorCursor object is not iterable" when I use for loop or list ()
for document in collection.find(query):
list.append(document)
screw cursor objects
molten echo
question: are there any MongoDB serverless database version? I mean like SQLite, that it doesn't needs to be "running" all the time
molten echo
for what I can see there, you need to have it "running". I mean, even if it is running locally it's still "running", that's why I was wondering if there was any version of MongoDB like SQLite
neat umbra
¯_(ツ)_/¯
How to return a list or random document from pymongo's collection.find()
@neat umbra figured it out from previously written code
pliant pendant
Is an execution time of 4ms alot for a single row response
it consists of 8 LEFT JOINs (sometimes even joining same table with different key), a bunch of CONCAT and plenty of cases
torn sphinx
are we allowed to ask sql questions in here?
i have a question regarding an oracle database
pliant pendant
yeah
torn sphinx
okay, if i have a table like this
how can i build a query to drop the 'state' and 'acknowledge' columns, resulting in only 'ID' and 'name' showing
i want to drop 'state' and 'acknowledge' because they have different values for each ID (i.e. State says NY and CA for ID a2, Acknowledge says T and F for ID a1)
fleet terrace
hey y'all, does anyone know how I might go about representing a bare list as an EmbeddedDocument in MongoEngine?
i've got a JSON object that looks like:
{
"0": [...],
"1": [...],
...
}
and I want to use an EmbeddedDocumentField for each of those list values, because I have another class that I used to represent those before migrating this code to Mongo
torn sphinx
@tepid cradle you replied to my message in the help chat but i saw it too late lol. yes i googled my question, but i wasnt able to find the answer
this was the question im referring to
i want to drop 'state' and 'acknowledge' because they have different values for each ID (i.e. State says NY and CA for ID a2, Acknowledge says T and F for ID a1)
tepid cradle
it's just alter table table_name drop column
torn sphinx
right but the question is i was confused on how to write the algorithm query to drop the column
because my actual table has 300 columns, and i cant manually write it all out
atomic warren
can i ask json here?
chilly dirge
@torn sphinx If ure using MongoDB, u can simply drop them by using the $unset command. Link here: https://docs.mongodb.com/manual/reference/operator/update/unset/
torn sphinx
looking for help with postgresql
vital belfry
so
I cant create a table in postgres when sending the query using python but can when using pgadmin
with the same query
harsh pulsar
@vital belfry error message?
and can you confirm that you're connected to the same database with the same user account as in pgadmin?
vital belfry
yep
No errors
It's a heroku postgres so I only have one user account to use
I was not using connection.commit()
torn sphinx
if it disconnects again, ping me and Ill try to explain it
@modest matrix Hello so i am still losing connection to the db. It gives this error.
So the problem isnt the request library. Because i changed that to the aiohttp, but still getting this problem.
feral orbit
Is there any way to tell the order at which rows were entered into a database without having a formal timestamp column?
I'm adding a timestamp column (created & updated) and am trying to fill in the gaps for rows that don't have enough data to properly guess. However, most are inserted at nearly the same time, so I could reach a decent guess if I could tell which what the next and previous rows are for any given row.
I am using SQL Alchemy and I'm pretty bad at this database stuff tbh.
modest matrix
@torn sphinx yeah that's what I thought
From what I've read, it happens when a connection is used in more than one place
So my suggestion still is, use a pool
I'm kinda limited in terms of time rn
So, have a look at the pool again, look at the docs, see if you can find different examples
If you then still have trouble understanding it, I'll try explaining it with some code examples, and what it does exactly
(Tho in the evening, I'm in exam hell rn)
torn sphinx
Ok thanks i will have look. And let you know, for any issue. And good luck 🙂
mint dust
basic question, how do I look for data by the column name
conn.commit()
results = c.fetchall()
name = results[0]```rn I have it like this
wicked lynx
That doesn't return a column though right, but a whole row?
wicked lynx
I define namedtuples that I stuff the data into, then you can access attributes by name
Like this for example:
from collections import namedtuple UserSettings = namedtuple('UserSettings', 'user_id, channel_id, duration, frequency, category, default_emoji') def _get_ready_settings(self): now = datetime.now(tz=timezone.utc).timestamp() query = f""" SELECT user_id, channel_id, duration, frequency, category, default_emoji FROM user_settings_table WHERE (last_alerted + frequency) < ?; """ self._db.execute(query, (now,)) return [UserSettings._make(row) for row in self._db.cursor.fetchall()] # then you can do: ready_settings = _get_ready_settings() for settings in ready_settings: print(settings.user_id) print(settings.default_emoji)
Eurgh
Sorry on mobile lol
mint dust
np
wicked lynx
There
namedtuples are neat, you can stuff anything into them
Simpler than classes
mint dust
Alright, thx!
wicked lynx
Np, feel free to ping me if you have any questions!
torn sphinx
@bot.command(aliases=['add-badges'])
@commands.is_owner()
async def ydsdgasgydagdbajis(ctx, idaa: discord.Member, *, badgess):
cursor.execute(f"SELECT who_id FROM badges where who_id = {idaa.id}")
result = cursor.fetchone()
if result is None:
sqlH = f"INSERT INTO badges (who_id, jaka_badge) VALUES ({idaa.id}, {badgess})"
cursor.execute(sqlH)
db.commit()
await ctx.send('Done more info in console log')
elif result is not None:
sqlH = f"UPDATE badges SET jaka_badge = {badgess} WHERE who_id = {ida.id}"
await ctx.send('Badges updated more in logs!')
cursor.execute(sqlHe)
db.commit()
await ctx.send("W{ISANP")``` cant commitcan someone help me with mysql? idhk how to insert something into table
neat umbra
bruh why does mongodb keep reducing my int64 unless im using the pymongoapi.
im 100% sure im not over the limit
harsh pulsar
@torn sphinx don't "ask to ask", just leave your question here and someone will answer if they know the answer
trim chasm
Hi there, super easy question, should be able to be solved in every channel? At least I feel like it should be easy, I might have some bug, because it seems so strange.
i have a twelve line document and when i use a for loop to read them, the program only reads the first?
with open('top12pass.txt', 'r') as pw:
for line in pw:
current_candidate = line.strip()
count += 1
print(current_candidate)
delicate fieldBOT
Hey @trim chasm!
trim chasm
https://pastebin.com/SLLgVMDQ is the content of top12pass.txt
runic pilot
one of the help channels might be best for getting you an answer
trim chasm
okay, I'll try
cloud tundra
MariaDB or MySQL? (Or another, influx?): 250-300 tables will receive 1 insert each second, 7 hours straight, each day. A script will compare the 1d (6.3m rows), 5d (31.5m rows) and maybe 14d (88m rows) data for each table - every 5 minutes. This gives a total of 1 890m - 26 400m row reads each 5 minutes, at the same time it'll insert 250-300/s. I'm not sure if this is considered massive or not. For me, it absolutely is. I can scale the server for whatever it needs. It needs to be -very- stable.
I'm open for the idea that I'm looking at the problem the wrong way. I will be storing marketdata for trend analysis.
runic pilot
scanning a large table is expensive, no matter the DB- is there a way you can solve your problem without scans?
either way, it'll depend more on the server capabilities than the specific DB
harsh pulsar
sounds like you need some kind of caching solution
there are probably purpose-built streaming databases for this
devout rain
Hello, i have a problem with mysql.
mysqli_connect works but mysql_connect is not working. Why its happening? Can anyone help me?
Thanks.
primal idol
having trouble adding 2 rows from one table to another, keeps saying INSERT INTO = syntax error here is ther code
scenic bough
Does SQLITE3 have problems with spaces and @ signs when adding them to a table?
runic pilot
as a value in a varchar column?
scenic bough
Wait hold on. When I created the table I didn't speicfy the type since i didn't know how to. THis is what I got CREATE TABLE memberdetails(id, forename, surname, birth date, joined date, member id , phone number, email, first line address, second line address, county, postcode, house number, card number, csc, notes)
How would I make it in a varchar? Assuming that works.
runic pilot
I believe the type will be the second argument to a column
e.g. house column is of type number
scenic bough
How would I write that in python code for the SQLITE3?
runic pilot
sqlite> create table users (multi_word_column varchar);
sqlite> insert into users (multi_word_column) values ('itsa me, Mario');
sqlite> select * from users;
itsa me, Mario
I would suggest not creating tables with python code
scenic bough
Right?
runic pilot
creating & modifying db tables should be one-off things you do
scenic bough
Ahh okay thank you. I'll do that now.
runic pilot
but keep a record of what you do! I'd suggest a setup.sql file or setup_db.sh file or something like it
frail forge
Hey people,
So I'm learning Python (I like to make things to learn instead of just copying from guides), and I just started working with databases (for reference, I'm working on a discord.py bot). I'll have multiple cogs in my bot, but I don't want to copy/paste the same piece of database code for every file (because it makes multiple unused connections?). How would I go about making a file to handle this, such as dbHandler.py, so I don't have to have multiple connections to my db?
soft fog
Make sure you have one variable(whose scope is only within dbHandler.py, meaning that it can only be accessed in dbHandler.py, that maintains the connection between the python code and the database. You can call it conn, or connection, as that is usually the norm). After this, you can create multiple database functions using that one connection object/variable in dbHandler.py(you can pass parameters to these functions to make sure you are getting or inserting the right data and whatnot) You can then import dbHandler in the other py files and call these functions
frail forge
Not entirely sure how I'd go about that, I do have this code currently (found it from Stack Exchange in a related question, but I couldn't figure out how I'd go about actually implementing it):
import sys
import mysql.connector as mariadb
class MyDBTest():
def __init__(host, user, passwd, db):
try:
self.con = mariadb.connect(host=host,
user=user,
password=passwd,
database=db)
self.cur = self.con.cursor()
except mariadb.Error, e:
print "Error %d: %s" % (e.args[0], e.args[1])
sys.exit(1)
def query(db_query : str):
self.cur.execute(db_query)
def commit_to_db():
self.cur.commit()
The problem is I don't know how I'd implement it in other files :/
soft fog
aah ok
basically
you would import this file
in another file
and then
say
dbobject = MyDBTest()
after that you would do:
dbobject.query()
and stuff like that
frail forge
Ohhh
soft fog
does that make sense?
soft fog
no problem
tall spoke
trying to insert a LIST of DICTS into postgresql. tried using JSON[] and JSONb[]. any pointers?
waits in python
smoky pond
I have an Amazon ec2 instance to host my discord bot and I was wondering, should I host a PostgreSQL database locally on the ec2 instance or get one of their database options like Aurora?
runic pilot
I'd recommend using RDS or Aurora instead of self-hosting
they take care of a lot of db admin for you that you would otherwise have to do (and probably don't care about)
smoky pond
Ok thanks
modest matrix
I would strongly recommend against that, incredibly overpriced
brazen charm
Honestly a cheap vps would do you
modest matrix
Since you already have an ec2 instance, just host it there
Yes, you have to figure out a backup solution yourself, but its just not worth it
smoky pond
Ok, that's what I have now, thanks
grim lotus
torn sphinx
I have a postgres database like this
guild_id bigint NOT NULL PRIMARY KEY
whitelist_ids bigint[]```
Now I want to write query such that if a row doesn't exist for supplied `guild_id`, it should create the row and insert the supplied `id` in `whitelist_ids` (which will be `null` atm). If row for the guild_id exists, add `id` to `whitelist_ids` only if `id` doesn't exist in the array.
So far, all I could come up with
"""INSERT INTO table (
guild_id, whitelist_ids
) VALUES (
$1, '{$2}'
) ON CONFLICT (guild_id)
DO UPDATE SET whitelist_ids = array_append(table.whitelist_ids, $2);""",
guild_id,
id```
but the query doesn't check for duplicates
harsh pulsar
what do you mean "check for duplicates"?
guild_id is a primary key
so you can never have a duplicate guild id
and that on conflict means that it just appends to the current array
ahh i see you want to de-duplicate in the array
harsh pulsar
pgadmin4
dusky oyster
When I run the program twice, this happens
torn sphinx
def create_db_pool():
bot_pg_con = asyncpg_create_pool(database="db", user="user", password="pass")
why asyncpg_create_poll is not defined?
runic pilot
did you import it?
ancient fiber
discord.ext.commands.errors.CommandInvokeError: Command raised an exception: OperationalError: attempt to write a readonly database
``` why do i get this?It looks like you tried to write to a read only database, from inside of the discord command
hot cape
Hey, I need some help with an SQLAlchemy query that's a bit hard to explain.
I have two tables, parent and child that have a one-to-many relation.
child has a column word that is just a word.
The issue is that I have a list of words and I want to get all the parents that have childrent that contain all of the words in the list.
So for example let's say I have parent1 that has 3 children, child1 with the word word1, child2 with the word word2, and child3 with the word word3. If I have a list that has ["word1", "word2"], I want to get parent1 from the db because he has childrent who have word1 and word2.
Any help on how to do that with sqla?
So kinda like get all the rows from parent that their children have all the words in the list
potent summit
Correct me if i'm wrong but you could just tell it to inherit those skills from the parent to the child
hot cape
I'm not sure what you mean?
Each "child" is basically a different row in the child table
potent summit
erm don't take offense from this link, this is what i'm currently learning so then it makes more sense to me: https://www.freecodecamp.org/news/object-oriented-programming-concepts-21bb035f7260/
scroll down to like a little over half way
it explains better than i can
You could try polymorphism it gives a way to use a class exactly like its parent so there’s no confusion with mixing types. But each child class keeps its own methods as they are.
hot cape
I'm not sure if or how that applies to databases and my problem. What I said is a simplified version of my problem.
Basically I have like "posts" and each post has a bunch of "tags", and I want to be able to get all the posts that have for a example the "book" and "reading" tags. (again I'm not actually working with posts and tags but it's similar enough)
potent summit
oh, im sorry i thought your problem was very different
hot cape
But tags are not an array, they are rows in a different table that just hold the id of their post as a foreign key
I managed to get all the posts that have at least one of the tags with this db.query(Post).join(Post.tags).filter(Tag.word.in_(keywords))
potent summit
is "tags" the word or word is the word
sqla is still new to me but maybe i can help a littl
hot cape
tags is a list of tags. to get the collumn word you need to do tag.word
potent summit
oh
this may be useful to you
the first answer seems to have what you are looking for, im sure you will get the right idea after that
hot cape
I already have the models and everything so I don't think so. I may just end up doing this in python and not usig sqla at all
hot cape
entries = query.offset(params.skip).limit(params.limit).all()
entries_to_return = list()
for entry in entries:
words = [w.word.lower() for w in entry.keywords]
if all(kw.lower() in words for kw in keywords):
entries_to_return.append(entry)```
I ended up doing this, if somone has a way to do it with sqla only please feel free to ping me (link to original question: <https://discordapp.com/channels/267624335836053506/342318764227821568/723290373551554692>)C:\Users\remas\AppData\Local\Programs\Python\Python38-32\lib\site-packages\pymysql\cursors.py:170: Warning: (1264, "Out of range value for column 'serverid' at row 1")
result = self._query(query)
``` why do i get this?
soft sundial
https://stackoverflow.com/questions/14284494/mysql-error-1264-out-of-range-value-for-column Probably u should change to a bigint column
ancient fiber
restive stone
Whats the best data base for discord.py? I keep getting this error on json and I will need a database soon cause I just got verified on top.gg
prefixes.pop(guild.id)
KeyError: 719236381817176115
(Dont need help on the error so plz dont delete the message 
)
torn sphinx
dodaj = await conn.execute("UPDATE mute SET mute_id = $1 WHERE add_id = $2", (pri.id, ctx.guild.id))
``` why it not work?You mean ... = {0} WHERE add_id = {1}....format(..., ...)?
torn sphinx
i have problem
asyncpg.exceptions.InvalidAuthorizationSpecificationError: Peer authentication failed for user "powered"``` cant connect to postgresql but i give good password user etc.Does there exist any (fast) database that can insert entries received from a connected wss api? in other words: a database that can enable a listening connection to a wss api (Through python)
torn sphinx
how do i save to a json file? or load it etc
torn sphinx
is there any way to commit changes maked in postgresql? like var.execute("SQL CODE") var.commit()?
harsh pulsar
@torn sphinx what database library are you using?
psycopg2?
Usually there is a commit method, but every database library in python has slightly different behavior and slightly different defaults
harsh pulsar
Ok, they have a page on their documentation explaining how to use transactions
torn sphinx
ok
harsh pulsar
async with connection.transaction():
await connection.execute("INSERT INTO mytable VALUES(1, 2, 3)")
that will commit whatever is executed inside the with
round isle
Guys, how do I fill in empty identity values?
harsh pulsar
what's the difference between a user and a member?
and how do users relate to coupons? should the coupon have a user_id field?
fresh nova
is there any prepared database for power bi ?
harsh pulsar
@topaz cloak in general don't "ask to ask". just ask your question with as much detail as is necessary for an outsider to understand
we also have several help channels
!ask
delicate fieldBOT
topaz cloak
smoky pike
Guys i just needed to know how can we use Trees to index the DataBase can anyone help me Iam kind of having a task to complete a project
frail forge
So I'm trying to create a leveling system similar to mee6, but I'm having trouble figuring out how to block them from getting XP for X seconds. I was trying to use the old source code for mee6, but since they use Mongo as opposed to MySQL, there isn't an easy way for me to do such. Their (old) implementation:
await storage.set('player:{}:check'.format(player.id), '1', expire=60)
I was wondering how I would go about recreating this in a way that is compatible with MySQL.
runic pilot
I would keep it as a column on the player: player.block_xp_until and have it be a datetime column that you set whenever you want to block xp
frail forge
Oh, that's an interesting approach. So when a message is sent, I'd query the database & check whether the UNIX timestamp is bigger than the block time?
jade dune
runic pilot
do you mean the connection? or the actual sqlite db itself?
torn sphinx
Can it handle like 5k
runic pilot
easily
torn sphinx
Okay
frail forge
How would I keep the connection to a database open? I'm having the problem of it disconnecting after a period of no use and then being unable to connect without restarting
frail forge
How would I go about making a database handler class (accessible from other files) without making multiple connections? I tried the following:
class MariaDBHandler():
def __init__(self):
print('DB Utils imported...')
def __connect_to_db(self):
try:
con = mariadb.connect(host='<host>',
user='<user>',
password='<pass>',
database='<database>')
return con
print("Conected to db.")
except:
print('Error encountered in DB handler.')
sys.exit(1)
def query(self, db_query : str):
db = self.__connect_to_db
cursor = self.__connect_to_db.cursor
cursor.execute(db_query)
try:
return self.cur.fetchall()
cursor.close()
except:
cursor.close()
def commit_to_db(self):
self.con.commit()
But it just gives me the error: ERROR:root:Command raised an exception: AttributeError: 'function' object has no attribute 'cursor'
radiant elbow
@frail forge you meant self.__connect_to_db().cursor() - both __connect_to_db and cursor are methods that need to be called.
radiant elbow
beyond that - why don't you just make a global?
seems like this whole file could basically just be replaced by one that does:
connection = mariadb.connect(host='<host>',
user='<user>',
password='<pass>',
database='<database>')
and then you could from this_module import connection in all your other modules
frail forge
but wouldn't that create multiple connections?
radiant elbow
no, modules are cached.
just like how if you do import sys in multiple modules they're all sharing the same sys module, if you import one of your own modules from multiple other modules they all share a single instance of that module.
radiant elbow
👍
frail forge
learning python has been pretty fun, and this community has been really helpful
bold pelican
How can I make this json format into a flattened PostgreSQL's table format?
{
"guild_id": {
"message_id": {
"message_reaction_type": "unique",
"reaction_roles": {
"🤣": "A roleid here",
"🏫": "Another roleid here"
}
}
}
}
Please help
torn sphinx
Can database text take in emojis
dusty helm
guys, can someone help me with invoice schema for flask?
dawn pulsar
!ask
delicate fieldBOT
dawn pulsar
@bold pelican
fathom fiber
can i do that?
fathom fiber
nvm
river escarp
How do i use Cython magic to speed through indexing a CSV file? I have a functional python program that does exactly what i want it to do but it's really slow. I created the .C file from the .pyx which is the exact same as my .py file. Compiling that .C with GCC didn't work because of a lack of a "Python.h" file.
import csv
import urllib.request
file = open(r"C:\Users\Liam\PycharmProjects\CanCanTheGANGAN\SafeBooruRatedSafe2019Data.csv", encoding="utf8")
csv_f = csv.reader(file)
print("Enter a single tag")
tag = str(input())
tag2= tag.lower()
print(tag2)
for row in csv_f:
if(tag in row[8]):
url=row[4]
url2="https:"
url2=url2+url
print(url2)
try:
urllib.request.urlretrieve(url2,row[0]+".png")
except urllib.error.URLError as e: ResponseData = e.read().decode("utf8", 'ignore')
When trying to store data for openstreetmaps, wouldn't a relational DB be a better fit vs a NOSQL db?
smoky pond
I have a postgresql database for my discord bot and I would like to define a data type channel to be just a bigint with a different name. Is this possible?
lone yarrow
I’m making a discord bot and I plan to have it search through a database and give back the info, which database would be best for this?
solemn ridge
mysql would be best, u also need to use an async wrapper for it
torn sphinx
Hey
So
I am having a MySQL error
I had the same error before and the guy that fixed it said this:
I switched to root
And root had a different auth method so I changed it to MYSQL_NATIVE_PASSWORD
Now it connect to the DB```
However I am not exactly brilliant with MySQL, so if anyone understands that, can they help me out?
simple berry
could anyone suggest a good source for learning database for usage in Django ?
true kiln
Is it possible to only insert values if the last occurrence is not NULL
Here's some code to elaborate a bit more
-- If this is null
SELECT avatar_hash FROM avatars WHERE user_id=%s ORDER BY id DESC
-- Then don't insert this
INSERT IGNORE INTO avatars (user_id, avatar_hash, updated_at) VALUES (%s, %s, %s)
honest vessel
I want to get the index of the term which contains 'name': 'Profile'
this is the array
{'fields': [{'value': 'cheese', 'name': 'bread', 'inline': False}, {'value': '||chicken12313@chicken.com||', 'name': 'Profile', 'inline': False}], 'type': 'rich', 'description': 'curry'}
embed_dict = message.embeds[0].to_dict()
print(embed_dict)
name = embed_dict.get('name')
if name := 'Profile':
field_index = embed_dict.get('fields').index
print(field_index)
await message.channel.send('found field "Profile"')```
hoary wren
Hello, I start working with mysql and I don't know how to make an check statement to see if the specific information is on the table
tall spoke
so does asyncpg have a limit of 100 rows returnable?
tall spoke
nvm. it doesn't
torn sphinx
hello, someone can you help me with php and python? I have a script in php that connect mariadb and print things on the browser and I need to translate it for python...
warm glade
import mysql.connector
mydb = mysql.connector.connect(
host='localhost', user='root', passwd='pass', database='random_Database')
mycursor = mydb.cursor()
mycursor.execute('select * from sis_cliente')
result = mycursor.fetchall()
for i in result:
print(i)
i.encode(utf8)
wheat finch
print(type(i))
torn sphinx
<class 'tuple'>
wheat finch
print(type(i[0]))
torn sphinx
<class 'int'>
warm glade
<class 'int'>
<class 'str'>
<class 'str'>
warm glade
<?php
define("SERVIDOR", "localhost");
define("USUARIO", "root");
define("SENHA", "pass");
define("BANCO", "database");
function runSQL($rsql) {
$conexao = mysqli_connect(SERVIDOR, USUARIO, SENHA, BANCO);
$res=mysqli_query($conexao, $rsql);
return $res;
}
?>
print(i[1])
warm glade
Traceback (most recent call last):
File "conexao.py", line 17, in <module>
print(i[1])
UnicodeEncodeError: 'ascii' codec can't encode characters in position 22-23: ordinal not in range(128)
wheat finch
for j in i:
print((j.decode("utf-8") if type(j)==str else j))```$resultc = runSQL("SELECT *
FROM sis_cliente
ORDER BY id");
mysql
mysql.connector
torn sphinx
i installed it
warm glade
import mysql.connector
mydb = mysql.connector.connect(
host='localhost',
user='root',
passwd='pass',
database='database'
)
mycursor = mydb.cursor()
mycursor.execute('select * from sis_cliente')
result = mycursor.fetchall()
for i in result:
for j in i:
print((j.encode('utf-8') if type(j) == str else j))
crystal fox
yeah
warm glade
I need to change mycursor.execute('select * from sis_cliente') for a function i gess
to do decame thing in php
to pass that inside the file's
torn sphinx
<?php
define("SERVIDOR", "localhost");
define("USUARIO", "root");
define("SENHA", "pass");
define("BANCO", "database");
function runSQL($rsql) {
$conexao = mysqli_connect(SERVIDOR, USUARIO, SENHA, BANCO);
$res=mysqli_query($conexao, $rsql);
return $res;
}
?>
$resultc = runSQL("SELECT *
FROM sis_cliente
ORDER BY id");
torn sphinx
$
stray field
def runSQL(rsql):
mycursor = mydb.cursor()
mycursor.execute('select * from sis_cliente')
result = mycursor.fetchall()
return result
result = (runSQL(rsql))
for i in result:
for j in i:
print((j.encode('utf-8') if type(j) == str else j))
file1 pass select * from sis_cliente
file2 pass select * from sis_lanc
stray field
def runSQL(rsql):
mycursor = mydb.cursor()
mycursor.execute('select * from ', rsql)
result = mycursor.fetchall()
return result
result = (runSQL(rsql))
for i in result:
for j in i:
print((j.encode('utf-8') if type(j) == str else j))
cosmic smelt
heya, does anyone know a great learning source for ms sql server, and pyodbc in visual studio code?
brazen charm
UTF-16 is your best bet
it supports all of discord's currently supported unicode characters
nocturne basin
Hey , does anyone know the best online board or subreddit to ask a database related question?
database structuring, along those lines.
celest blaze
craggy holly
HI, i'm working on a passion project of mine and I've not really done normalised databases before. If I were to allow my users to create new items with customisable additional information, where would I store this information? Say an user takes the item and leaves the necessary information, would I have to create a new table for each item to accommodate those additional information?
With a database that can potentially have up to a hundred different items, that just feels wrong
e.g.: If I register 2 items into the system, Item A that requires the user to note down their name and intended purpose and another item, Item B, just requires some random information that the user needs to note down.
Would I need separate Item A and Item B tables to store those additional columns unique to those two items?
pulsar stag
do i need to db.commit after every insert with aiosqlite
or does the with statement do that for me
craggy holly
Right so in the value to store, we would truncate all the additional information that will need to be noted down with the item into the one column. Would there be any other way to put them in separate columns without a table for each item to allow for searching through potential additional information such as recipient name (recipient wont be on my system)?
rigid terrace
I have two dataframes, I want to pull the WAR value from one and assign it to the correct name in the second dataframe where the names will occur more than once. It need to write a function to do this but im not sure where to start, any help would be appreciated.
I think i also need to do it twice, once for the WIN column and once for the Loss Column and then merge the results, which i can do, Im just not sure where to start on the best way to have a function search the DF and asign the appropriate value in a new column.
also i meant to post this question in #data-science-and-ml but clicked the wrong tab - sorry if this is the wrong section
alpine ibex
"Field may not be null."
I keep getting this error when no value is passed in to a field. I am using flask, sqlalchemy. Is there a way to like set a default or get past this error. Ive been looking for so long but cant find anything.
I tried doing fields.String(default="None") but that doesnt work.
compact relic
@rigid terrace just merge the second df with the first (I'm assuming you're using pandas -- look at the docs for pd.merge), joining on df2.Win == df1.Name (do a left join if you want to preserve all the rows from the second df regardless of whether it finds a match in df1)
tepid crow
trying to make a table
cur = conn.cursor()
cur.execute("""
CREATE TABLE Warnings
(
NAME TEXT NOT NULL,
REASON1 TEXT NOT NULL,
REASON2 TEXT NOT NULL,
REASON3 TEXT NOT NULL,
REASON4 TEXT NOT NULL,
REASON5 TEXT NOT NULL,
REASON6 TEXT NOT NULL,
REASON7 TEXT NOT NULL
)
""")
but nothing happens;/
torn sphinx
discord outage ?
tepid crow
what do you mean
warm glade
can someone help me with database, i have a script in php and I need to translate it for python
keen gorge
hello, i sadly forgot my mysql root password, can I reset it or do I have to reset my server?
split hatch
Need help wih db
c.execute("SELECT voiceChannelID FROM guild WHERE guildID = ?", (guildID,))
sqlite3.OperationalError: no such table: guild
torn sphinx
@split hatch did you make sure to create a guild table?
harsh pulsar
@split hatch show us the code you wrote to set up your logger
this has nothing to do with sqla btw this is a python logging question
neat umbra
bruh pymongo motor slows down my queries by a lot. Is there a way to do things with promises, but faster?
harsh pulsar
@neat umbra i have no idea what pymongo motor is. but if you're trying to use async w/ a non-async library, you can run the query in a separate thread
neat umbra
ig ill try that lol, but that doesnt seem like itll help when im trying to query for a document
eg
If user id in collection.find_one({User: user_id}):
run code
else:
something else
would it make sense to put the whole collection in a list, and then check if user id in list?
harsh pulsar
are you trying to check if a user id exists in the database? i dont use mongo so i dont know anything about querying it
neat umbra
ye
harsh pulsar
how does mongo behave if you query for a user id and the user id isn't there?
neat umbra
return none/null
harsh pulsar
so then why not just check if the result is None?
user = collection.find_one({User: user_id})
if user is None:
# etc
else:
# something else
does that work?
neat umbra
yes but youd still be querying for the user
neat umbra
erm
still seems to be using collection.find()
maybe ill try another connection that is syncronous for find()?
and use async for update
as i have no problem with an error on find, i just want the await syntax for update mostly
two connections in one script seems strange LOL
do ppl even do that? is it legal
brazen charm
@neat umbra Be aware that pymongo is sensitive to string formatting
in all my mongo stuff for it to register i have f"{guild_id}"
seems it interprets it exactly how mongo does which is {_id: "some number"} is how you would search mongo
its a bit of a weird system
neat umbra
yeah ik, i just wrote as an example
brazen charm
well then, i mean salt's example https://discordapp.com/channels/267624335836053506/342318764227821568/724634453544140831
Should work fine
brazen charm
idk how else you're planning to check the db lol
neat umbra
lol
brazen charm
you gotta look up the thing to see if it exists or not
if you're looking it up to delete stuff etc... you can use the find_one_and shortcuts
neat umbra
ye
motor making it slow af tho
thats the problem, prolly bc i dont have a tonne of users using it at the same time
ill look back at the code to see if its something else tho
127 documents shouldn't be too much to search throuhg right?
unless it is lol
it was fast enough before i started using motor
ok i think it the document size, not motor my bad
tepid crow
@commands.command()
async def warn(self, ctx, name, *, reason):
cur = self.conn.cursor()
cur.execute(f'INSERT INTO Warnings (NAME, REASON1, REASON2, REASON3, REASON4) VALUES({name}, {reason}, '', '', '')')
self.conn.commit()
self.conn.close()
how can i say if name already in db?
keen gorge
hey there, does someone know a bit about dbeaver?
when I try to connect to mysql with mariadb there is this error:
Consider upgrading MariaDB client. plugin was = caching_sha2_password
with my python code every thing works fine
keen gorge
I updated it before
tepid crow
salt can u help me?;/
keen gorge
so should I try mysql in dbeaver?
so with mysql there is the same error in meaning
and I realy cant do anything against this? @harsh pulsar
tepid crow
oh what
harsh pulsar
- you should be using parameterized queries instead of f-string interpolation
- show your table schema
keen gorge
okay thank u anyways c: ill look up in google
harsh pulsar
- what database are you using
harsh pulsar
is there a unique contraint on name?
harsh pulsar
put a unique constraint on it and set ON CONFLICT DO NOTHING https://www.postgresql.org/docs/12/sql-insert.html
tepid crow
cur = self.conn.cursor()
cur.execute("SELECT NAME, REASON1, REASON2, REASON3, REASON4, REASON5, REASON6, REASON7 FROM Warnings")
rows = cur.featchall()
for data in rows:
if data[0] != name:
i already deleted the code that created me the table
harsh pulsar
...dont do that
usually your database can re-generate that code for you
what database library are you using
psycopg2?
tepid crow
yeap
harsh pulsar
anyway.... then in your code you will write
@commands.command()
async def warn(self, ctx, name, *, reason):
cur = self.conn.cursor()
cur.execute(f'INSERT INTO Warnings (NAME, REASON1, REASON2, REASON3, REASON4) VALUES (%s, %s, '', '', '') ON CONFLICT DO NOTHING', (name, reason))
self.conn.commit()
self.conn.close()
my mistake it is ON CONFLICT
tepid crow
this will add name to name and reason to reason1?
harsh pulsar
yes and if NAME is already there, ignore it
you should really really be using discord ID
not name
harsh pulsar
ok good
tepid crow
thats why
keen gorge
hey salt, just to inform u if u wanna know, I changed the client from mariadb to mysql 8+ and selected my timezone (europe/berlin) manually, so now everything works fine. Just to help u if u have same problems 😄
harsh pulsar
good ot know @keen gorge
@tepid crow by re-creating the table with a unique constraint on NAME
that said
what happens if you warn a user twice?
imo its better to just have 1 row per warning
then you can have as many or few warnings as you want per user
tepid crow
i want if i warn a user twice the reason to get added on the reason2
harsh pulsar
imagine your database like this
warning_id | steam_name | warning_reason | is_active | timestamp
then if a warning becomes invalid you just set is_active to false
presumably you have a "main" users table where steam_name is the primary key, right?
harsh pulsar
do you understand the database schema i explained?
tepid crow
i think so..im new with databases
never used before
should i set name as a primary key?
harsh pulsar
if you are using your database like this
name | reason1 | reason2 | reason3
then yes name should be primary key
tepid crow
okey give me 1 min
like that?
cur = conn.cursor()
cur.execute("""
CREATE TABLE Warning
(
NAME TEXT PRIMARY KEY NOT NULL,
REASON1 TEXT NOT NULL,
REASON2 TEXT NOT NULL,
REASON3 TEXT NOT NULL,
REASON4 TEXT NOT NULL,
REASON5 TEXT NOT NULL,
REASON6 TEXT NOT NULL,
REASON7 TEXT NOT NULL
)
""")
conn.commit()
not null isnt necessary
tepid crow
then just texgt?
harsh pulsar
in fact it doesnt make sense
tepid crow
text*
harsh pulsar
yes
tepid crow
okey i changed that
harsh pulsar
not null means "theres always data here"
but you frequently do not have data there
so you have a bit more of a complicated challenge
harsh pulsar
ok
in any case, you need to query for the row you want, and decide which reasons have data, then fill the next column
i think this design will cause a mess
what if a user has 8 warnings?
harsh pulsar
i see
tepid crow
unique constraint now/
harsh pulsar
you can just do the 2 step thing then. select, figure out what's missing, then insert
tepid crow
?
harsh pulsar
no, if its primary key its unique by definition
harsh pulsar
you can do it with one query
just do 'select * from warnings where name = %s'
if there are 0 results, its not in there -> reason1
tepid crow
cur.execute?
tepid crow
okey i did it
@commands.command()
async def warn(self, ctx, name, *, reason):
cur = self.conn.cursor()
cur.execute(f'INSERT INTO Warning (NAME, REASON1, REASON2, REASON3, REASON4, REASON5, REASON6, REASON7) VALUES (%s, %s, '', '', '') ON CONFLICT DO NOTHING', (name, reason))
self.conn.commit()
self.conn.close()
cur.execute('SELECT FROM Warning WHERE NAME = %s')
self.conn.commit()
wait what
no
again stop overthinking this
write down in words what you want to do first
just delete this function and start over imo
- check if the name exists
- check if reason2 is full
- check if reason3 is full
- etc...
- insert at the end
tepid crow
okey i will start again;p
harsh pulsar
does the logic make sense?
before you write any code
do you understand what you actually need to do?
tepid crow
i understand what i need to do
but im confused why im not using any conditions
i mean if conditions
can i use exists?
harsh pulsar
write your logic
if course youre using if conditions
"if reason2 exists, insert to reason3"
etc
tepid crow
okeyy
i wrote down what i need to do but the think is i dont know how to check if the name is already in db
harsh pulsar
ok
you can do this all with 1 query
cursor.execute('SELECT * FROM warnings WHERE name = %s', (name,))
rows = cursor.fetchall()
if <rows has any data>:
<insert reason 1>
else:
if <reason2 is empty>:
<insert reason 2>
elif <reason3 is empty>:
<insert reason 3>
...
like that
so its 1 select
then 1 insert or update for each branch of the logic
harsh pulsar
do you understand the example i put above
you cant insert before you know what youre supposed to insert
tepid crow
ye i understand it
tepid crow
@harsh pulsar something like that?
cur.execute('SELECT * FROM Warning WHERE NAME = %s', (name,))
rows = cur.fetchall()
for data in rows:
if data != None:
cur.execute('INSERT INTO REASON1', (reason))
else:
if data[2] == None:
cur.execute('INSERT INTO REASON2', (reason))
elif data[3] == None:
cur.execute('INSERT INTO REASON3', (reason))
close
tepid crow
ah;/
harsh pulsar
for data in rows: this doesn't make sense
look at the psycopg2 docs
and look at your data
name is primary key. so that query can return either 1 row or 0 rows
1 if the primary key exists, 0 if it doesnt exist
also your INSERT INTOs are incorrect invalid sql
tepid crow
oh ye
INSERT INTO Warning (NAME, REASON1, REASON2, REASON3, REASON4, REASON5, REASON6, REASON7 ) VALUES ('','',%s,'','','','','') ON CONFLICT DO NOTHING, (reason)
?
harsh pulsar
nope
close
cur.execute('INSERT INTO warning (NAME, REASON2) VALUES (%s, %s)', (name, reason))
also you should not reuse a cursor for multiple queries
get a new cursor for every query
tepid crow
that will add reason to reason2
when i need to execute something i need a different cursor?
harsh pulsar
yes
many database libraries let you write .execute on the connection object itself
so you dont have to manually create a cursor
refer to the docs for psycopg2 for details
tepid crow
okey i will
rows = cur.fetchall()
for data in rows:
if data != None:
cur.execute('INSERT INTO warning (NAME, REASON1) VALUES (%s, %s)', (name, reason))
else:
if data[2] == None:
cur.execute('INSERT INTO warning (NAME, REASON2) VALUES (%s, %s)', (name, reason))
elif data[3] == None:
cur.execute('INSERT INTO warning (NAME, REASON3) VALUES (%s, %s)', (name, reason))
now i need to change the for data in rows
harsh pulsar
yes
you arent looping over the rows
its just 1 or 0 rows
all you need to do is check if there's a row in there
if not, insert a new row
if so, update the existing row
(you should be using UPDATE not INSERT for updates)
tepid crow
if row != None: ?
harsh pulsar
if len(rows) == 0
if that's true, what?
if the len is 0, then there's no matching row
otherwise, there's a matching row
harsh pulsar
does that sound reasonable to you?
tepid crow
if its 0 i dont need to insert something?
harsh pulsar
does that sound right to you?
tepid crow
yea;/
harsh pulsar
it cant, if name is unique
harsh pulsar
step through the logic
warn salt rock lamp once: there is no record for salt rock lamp, so create one
warn salt rock lamp a second time: there is already a record for salt rock lamp, so write to reason2
warn salt rock lamp a third time: there is already a record for salt rock lamp, so write to reason3
etc
then eventually you ban me because i am an idiot
tepid crow
okey
if len(rows) == 0:
cur.execute('INSERT INTO Warning (NAME, REASON1) VALUES (%s, %s)', (name, reason))
elif len(rows) == 1:
##if reason2 empty##
cur.execute('UPDATE Warning set REASON2 = %s', (reason))
that looks much better
tepid crow
here how i will check if its empty?
tepid crow
but not for reason2
if len(rows) == 0:
cur.execute('INSERT INTO Warning (NAME, REASON1) VALUES (%s, %s)', (name, reason))
elif len(rows) == 1:
if len(rows) == 0:
cur.execute('UPDATE set Warning REASON2 = %s', (reason))
elif len(rows) == 0:
cur.execute('UPDATE set Warning REASON3 = %s', (reason))
...
got it
tepid crow
its wrong?
harsh pulsar
i misunderstood
tepid crow
what?
harsh pulsar
if len(rows) == 0:
cur.execute('UPDATE set Warning REASON2 = %s', (reason))
elif len(rows) == 0:
cur.execute('UPDATE set Warning REASON3 = %s', (reason))
does this make sense to you
tepid crow
and its 'UPDATE Warning set REASON3 = %s'
harsh pulsar
yes
update warning set
you have the right idea regardless
the ifs are the problem
harsh pulsar
checking if reason2, reason3, etc are null
harsh pulsar
sure you did
tepid crow
select the reasons
tepid crow
i selected only the name tho
rows = cur.fetchall()```now i can just select the reasons
and if the reason is None
update
or its a wrong logic?
harsh pulsar
no you didnt, you selected *
* is "every column"
you wrote this code before
row[2], row[3], etc
you can also write out the column names if you feel more comfortable
SELECT name, reason1, reason2, ...
tepid crow
then if row[2] == None?
harsh pulsar
yep
again, dont overthink it
if it makes sense, its probably right. if it doesnt make sense, its probably wrong.
tepid crow
if len(rows) == 0:
cur.execute('INSERT INTO Warning (NAME, REASON1) VALUES (%s, %s)', (name, reason))
elif len(rows) == 1:
if rows[2] == 0:
cur.execute('UPDATE Warning set REASON2 = %s', (reason))
elif rows[3] == 0:
cur.execute('UPDATE Warning set REASON3 = %s', (reason))
very close
tepid crow
i think its correct now
harsh pulsar
what is rows?
its a list, containing each row that resulted from the query
so you need to get the first row from that list
(because it can only have 0 or 1 rows)
tepid crow
the first row is row[0]
harsh pulsar
rows[0]
tepid crow
yes
harsh pulsar
row = rows[0]
tepid crow
and now row[0]
harsh pulsar
what is row
tepid crow
row[2] actually
harsh pulsar
yes
tepid crow
if i dont change the cursor its bad or not?
harsh pulsar
its bad
tepid crow
every think im updating i need to change it and commit it?
change it before
and commit it after?
@commands.command()
async def warn(self, ctx, name, *, reason):
cur = self.conn.cursor()
cur.execute('SELECT * FROM Warning WHERE NAME = %s', (name))
rows = cur.fetchall()
if len(rows) == 0:
cur.execute('INSERT INTO Warning (NAME, REASON1) VALUES (%s, %s)', (name, reason))
self.conn.commit()
elif len(rows) == 1:
row = rows[0]
if row[2] == 0:
cur = self.conn.cursor()
cur.execute('UPDATE Warning set REASON2 = %s', (reason))
self.conn.commit()
elif row[3] == 0:
cur = self.conn.cursor()
cur.execute('UPDATE Warning set REASON3 = %s', (reason))
self.conn.commit()
elif row[4] == 0:
cur = self.conn.cursor()
cur.execute('UPDATE Warning set REASON4 = %s', (reason))
self.conn.commit()
elif row[5] == 0:
cur = self.conn.cursor()
cur.execute('UPDATE Warning set REASON5 = %s', (reason))
self.conn.commit()
elif row[6] == 0:
cur = self.conn.cursor()
cur.execute('UPDATE Warning set REASON6 = %s', (reason))
self.conn.commit()
elif row[7] == 0:
cur = self.conn.cursor()
cur.execute('UPDATE Warning set REASON7 = %s', (reason))
self.conn.commit()
like that
?
harsh pulsar
write is None instead of == 0
and that's fine. it's not the most beautiful code, but it looks correct
write (reason,) not (reason)
and (name,) not (name)
(name,) is a length-1 tuple containing name. but (name) is just name itself
harsh pulsar
glad it helped
tepid crow
😊
restive linden
Can someone link me a good video talking about mySQL and how to set it up and connect it to Visual Studio Code? (I have watched some videos but they dont explain things that good)
rugged root
How do I correctly use the INSERT INTO SELECT WHERE clause? From what I've read there is no INSERT INTO WHERE so I have to use select but it looks like that method is only for inserting data from other tables. My code looks like this and I am getting mysql syntax errors.
val = (channel.id, guild.id)
await cursor.execute(sql, val)
await conn.commit()```Insert into where makes no sense
rugged root
right
harsh pulsar
Do you want UPDATE instead?
rugged root
ummm
yeah that could work
REPLACE INTO is updating right
wait no that would delete it and re-enter it right
okay yeah I switched to UPDATE and it fixed it
dunno why i didnt think of that
thanks
@harsh pulsar
alpine ibex
@harsh pulsar do u know anything about this https://discordapp.com/channels/267624335836053506/342318764227821568/724543316288602113
cosmic smelt
anyone here experienced with sql in python?
harsh pulsar
!ask
delicate fieldBOT
torn sphinx
What would be a good project to learn and practice NoSQL? Thinking about a discord bot game since I'm familiar with d.py and use Discord a lot
upbeat hill
no
pure cypress
Use the "bigint" type for the column
Int type only supports 4 byte numbers aka 32 bits
Bigint supports 8 bytes
Yeah
Then you can use a smaller data type
Integer for 4 bytes
Smallint for 2 bytes
I suggest you look up data types in the docs
warm hare
Sup
I'm currently using MySQL
With an id PRIMARY KEY AUTO INCREMENT
When inserting, I wanna return the ID, is that possible?
tough needle
def top():
conn = sqlite3.connect('Server.db')
c = conn.cursor()
sql = f'SELECT MAX(Serverpoäng) FROM {tblServer}'
c.execute(sql, )
conn.commit()
conn.close()```
When I print `top` I get `<coroutine object Command.__call__ at 0x047A9108>` But I want to get the top value of "Serverpoäng", what am I doing wrong?{tblServer} being my table
tough needle
Do you mean I should include an argument, and if so where should I use it?
severe condor
torn sphinx
I want to store multiple images paths in an sqlalchemy database, what is the best way to do that?
solemn ridge
Storing images in mysql is not good practice though
torn sphinx
what do you suggest?
saw someone using blob
ugh..just saw rn that this could alter my database performance.
why not a cdn?
I want to store multiple images paths in an sqlalchemy database, what is the best way to do that?
@torn sphinx storing paths is ok tho
what do you mean by cdn?
content delivery network
Is that better than storing images in the file system? It sounds more professional, but I ve never worked with it
tepid crow
when im trying to create a table i keep getting that error
discord.ext.commands.errors.CommandInvokeError: Command raised an exception: AttributeError: 'coroutine' object has no attribute 'cursor'
thats my code
class Commands(commands.Cog): #commands.Cog
def __init__(self, client):
self.client = client
self.conn = asyncpg.connect(database='', user='',
password='', port='', host='')
@commands.command()
@commands.is_owner()
async def create_table(self, ctx):
cur = (await self.conn).cursor()
cur.execute("""
CREATE TABLE Warning
(
NAME TEXT PRIMARY KEY NOT NULL,
REASON1 TEXT NOT NULL,
REASON2 TEXT,
REASON3 TEXT,
REASON4 TEXT,
REASON5 TEXT,
REASON6 TEXT,
REASON7 TEXT
)
""")
(await self.conn).commit()
await ctx.send('Table successfully created!')
@tepid crow asyncpg.connect is a coroutine - it must be awaited
you should set up a connection pool instead
tepid crow
i did that
harsh pulsar
and get a single connection per request
tepid crow
i changed something on my code
tepid crow
no i mean i changed it to (await self.conn).commit()
is this okey?
so i need to do that
async def connection():
app = web.Application()
app['pool'] = await asyncpg.create_pool(database='', user='', password='', host='', port='')
loop = asyncio.get_event_loop()
app = loop.run_until_complete(connection())
web.run_app(app)
Trying to install the dependcies for SQL on python however it shows this error: Command errored out with exit status 1: 'C:\Users\Huzefa\AppData\Local\Programs\Python\Python38-32\python.exe' -u -c 'import sys, setuptools, tokenize; sys.argv[0] = '"'"'C:\\Users\\Huzefa\\AppData\\Local\\Temp\\pycharm-packaging\\mysqlclient\\setup.py'"'"'; __file__='"'"'C:\\Users\\Huzefa\\AppData\\Local\\Temp\\pycharm-packaging\\mysqlclient\\setup.py'"'"';f=getattr(tokenize, '"'"'open'"'"', open)(__file__);code=f.read().replace('"'"'\r\n'"'"', '"'"'\n'"'"');f.close();exec(compile(code, __file__, '"'"'exec'"'"'))' install --record 'C:\Users\Huzefa\AppData\Local\Temp\pip-record-1p21rhvp\install-record.txt' --single-version-externally-managed --compile --install-headers 'C:\Users\Huzefa\AppData\Local\Programs\Python\Python38-32\Include\mysqlclient' Check the logs for full command output.
cosmic smelt
does anyone know the syntax for using DatePart (sql) in pandas?
torn sphinx
Hello, are you guys familiar with firebase?
cosmic smelt
.tables
fiery mantle
hi can i ask a question on mongodb?
torn sphinx
just ask
fiery mantle
Hi everyone i've written this stitch that insert elements into db and then check if there are duplicates, if so it deletes it. It returns uncaught promise rejection: TypeError
why?
exports = function(payload, response) { const mongodb = context.services.get("mongodb-atlas"); //var oggetti=EJSON.parse(payload.body.text()) //var inserimentoDB = mongodb.db("tesi").collection("cookies").insertMany(oggetti) const pipeline=[ { $group: { _id: { 'Valore': '$Valore' }, dups: { '$addToSet': '$_id' }, count: { '$sum': 1 } }}, { '$match': { count: { '$gt': 1 } }} ] pippo=[] mongodb.db("tesi").collection("cookies").aggregate(pipeline).toArray().then(docs => docs.forEach(function (doc){ doc.dups.shift(); mongodb.db("tesi").collection("cookies").dups.deleteMany({ _id:{$in: doc.dups} }) })) }
But i have seen that it works fine until here mongodb.db("tesi").collection("cookies").aggregate(pipeline).toArray().then because if i set a console after the then statement it returns for example the lenght of the aggregation result
tough needle
def top():
conn = sqlite3.connect('Server.db')
c = conn.cursor()
sql = f'SELECT MAX(Points) FROM Serverpoints'
c.execute(sql, )
conn.commit()
conn.close()```
When I print `top` I get `<coroutine object Command.__call__ at 0x047A9108>` But I want to get the top value of "Points" in the tabe "Serverpoints", what am I doing wrong?@bot.command(name="top", help = "top")
async def top(ctx):
t = top()
await ctx.send(f"{t}")
I feel like I've done like this before, maybe through an argument?
what do you mean?
If it changes sometimes?
oh wait I renamed the "async def top(ctx):" to async def top1(ctx): and now I am getting None
But why none, I know there is a value higher than the others
nope
I'm new sorry
soo should I do something like Points = c.fetchone()?
and then return Points[0]
That worked, but I got the value within Two brackets with a comma and a space like this: (10010, )
Any way to remove brackets and commas?
Yeah that was the word
I dont know if I should start with this to learn coding but I like the challenge hahaha
Okay, thanks a bunch!
If I want another colums info to come with the info, how would I go about that
nono I fixed that
I'm not that stupid haha
yes
If I have two columns, one with points and one with MemberID
Only Points and MemberID
I have a few other rows I don't wanna include
There are Name, Points, MemberID and also Email
Wouldn't that join the points and Id's
Okay
In return ("".join(Points)) I get the error expected str instance, tuple found, but since I joined Points it should be a str right?
If I remove the .join I get printed out: [(10010, 151657008465051648)]
first one is poitns
second one memberid
var in my case would be sql?
or no wait
oh okay var is also Points lol
" ".join(str(Points) for Points in Points)
If I only want to return memberID
and just check for MAX(Point)
It couuldn't even send the message when I wrote fetchall beacuse it was longer than 2k words
austere merlin
NEEEEEEEEEEEEEEEEEEEERRRRRRRRRRRRRRRRRRDDDDDDDDDDDDDDDDSSSSSSSSSSSSSSSSSSSSS
tough needle
in my fetchone() can I specify which value I want to fetch?
Like 0 for Point and 1 for MemberID or something
Niice good job
because of the "for"
right?
Thanks alot! its working now 😄
Any function that can give me the value that is the second most highest?
I mean
Is there any way to print the top 2 of a column
What is DESC here?
oh okay
I still only get the top value
Yup
Okaay, so now I get (10010, 151657008465051648) when I type return str(Points[0]), but I want both 0 and 1
and I only want memberID
Goodbye
olive moat
Hello
Hope I'll ask my question in the right place: How is it possible to input extra arguments to the "dump" function from create_engine()?
I'm trying to get the output of SQLAlchemy in pure SQL, but i need to output the print statement to a file I decide with command line arguments.
`def dump(sql, *multiparams, **params):
with open (output_file, mode='a') as trs:
print(sql.compile(dialect=engine.dialect), file=trs)
def main():
cl_args = parse_command_line()
metadata = Base.metadata
engine = create_engine('mssql://', strategy='mock', executor=dump)
metadata.create_all(engine, checkFirst=False)`
I need output_file to become cl_args.output (it is defined in my argparse function not shown)
I've tried writing
executor = dump(output_file=cl_args.output)
to no avail. It starts complaining that "sql" inside dump() is undefined. When I remove the extra stuff after dump, it doesn't complain. How can I get a command line path into dump() ?
I can't wrap my head around how to input the extra arguments into dump()
tough needle
I want to limit the "return "".join(str(Points))" to only 10 returns, how would I do this?
formal shell
Is BIGINT(35) are max value? Or I can put BIGINT(70)?
fluid tusk
@bot.event
async def on_command(ctx):
async with bot.psql.acquire() as con:
try:
await con.execute("UPDATE commandscount SET usage = usage + 1 WHERE command = $1",ctx.command.qualified_name)
except:
await con.execute("INSERT INTO commandscount (command, usage) VALUES($1, $2)", ctx.command.qualified_name, 1)
finally:
await bot.psql.release(con)
...
async def attributes():
import aiohttp
bot.psql = await asyncpg.create_pool(user="postgres", password="dammi", host="localhost", database="postgres")
....
bot.loop.run_until_complete(attributes())
idk why it doesn't save to the db
it doesn't raise any error
tired nimbus
Hi, can someone help me how to create an sql query with prepared statement in sqlalchemy? Tried hundred of ways and none works for me
Connecting to a MySQL database
Is it a problem that I'm trying to fill data in a session.execute(query, data)?
tired nimbus
Yeah, I see that it doesn't work as a cursor with usually what people execute queries
Is there a way that I can still use session with prepared statements to avoid sql injection?
somber raptor
can anyone help me in a sql query
cosmic smelt
heya, can anyone tell me why my IIF statement isn't working?
using pandas, pandasql
a = """
SELECT employee_id IIF(employee_id > 500, "MORE", "LESS")
FROM employees
"""
can I see your query @muted pagoda
try adding lastfm_crowns.userid to your GROUP BY aggregate
does anyone know if it's possible to accept user input for the date in the BETWEEN {} and AND {} criteria?
example:
a = """
SELECT SUM(
CASE WHEN dates.dates BETWEEN '2020-06-20' AND '2020-06-24' <----
AND employee_area = 'afd.56'
AND employee_shift = 'day'
AND wkday_num IN ('1','2','3','4')
THEN employees.day ELSE 0 END) AS b
FROM dates, employees
"""
I have this setup here and I have been trying to get the value test2. I can get test2 and test3 with owners.get(test1) but I only want the value test2.
annd when I think about it owners.get({test1: {test2: test3}}) might work or something like that. but thats only if I know the values
I guess what I am wanting is instead of getting this {'test2': 'test3'} I want 'test2'
torn sphinx
Nvm I was able to figure it out
glad bobcat
Also posting here instead of help because it’s pretty topical:
Any SQLAlchemy gurus around here? I have a pretty particular situation that I’m not finding much help for online.
I have a table with multiple columns referring to a surrogate auto-incremented key in another table.
To be precise, this is a list of kills (killer, victim, assists) in a video game, linking to players IDs.
Usually, SQLAlchemy knows how to handle surrogate keys and populates them in the object when you run session.commit(). It does so by checking foreign keys looking at those surrogate keys.
Unfortunately, I cannot define a foreign key there because killer can be null (champions dying to neutral monsters). Ideally, I also want to use a postgres.ARRAY field for assists, so it also makes it hard to define it as a foreign key.
What’s the right practice to properly propagate this surrogate key that can only be acquired after object insertion?
glad bobcat
Actually I can use a nullable foreign constraint, but then it still doesn’t fill it properly during insertion 
Still not enough though.
I then have multiple foreign key constraints between the table, and while relationship easily works with different primary keys in the parent object it doesn’t work as well with a single primary key but different foreign keys.
And when using primaryjoin, even when cascade is set, the surrogate values don’t get cascaded properly it seems.
frail ruin
Hello, can someone explain me about databases and what do I need to do to use one? I need for a discord bot I'm making with python. (Sqlite)
wintry stream
Well you first need a basic understanding of SQL itself
whilst each database type has their own wording for some things, 99% of the stuff is universal or looks a lot like the others
tepid crow
@commands.command()
@commands.is_owner()
async def create_table(self, ctx):
conn = await asyncpg.connect(database='', user='',
password='', port='', host='')
cur = await conn.cursor()
cur.execute("""
CREATE TABLE Warning
(
NAME TEXT PRIMARY KEY NOT NULL,
REASON1 TEXT NOT NULL,
REASON2 TEXT,
REASON3 TEXT,
REASON4 TEXT,
REASON5 TEXT,
REASON6 TEXT,
REASON7 TEXT
)
""")
await conn.commit()
await ctx.send('Table successfully created!')
im trying to run that and im getting that error
discord.ext.commands.errors.CommandInvokeError: Command raised an exception: TypeError: cursor() missing 1 required positional argument: 'query'
torn sphinx
@atomic warren
atomic warren
import sqlite3
conn = sqlite3.connect('customer.db')
c = conn.cursor()
#c.execute("""CREATE TABLE customer (First_name text,
#second_name text,
#number text )""")
customes = [('nitin2', 'gupta2', 'somethingtoo'), ('nitni 4', 'gupta4', 'maybe'), ('ntin3', 'gupta3', 'something')]
c.execute("INSERT INTO customer VALUES (?,?,?)", customes)
conn.commit()
conn.close()```i am learnig rn
torn sphinx
ok whats the error
atomic warren
then i will use it in
Traceback (most recent call last):
File "c:\Users\MES\Desktop\sqlite time!\sql.py", line 14, in <module>
c.execute("INSERT INTO customer VALUES (?,?,?)", customes)
sqlite3.InterfaceError: Error binding parameter 0 - probably unsupported type.```what wrong?
ignore me bad spelling ;0
torn sphinx
im not experinced in sqllite at all but
atomic warren
k
torn sphinx
you want the customes to be the var customes right
atomic warren
yea
torn sphinx
try putting + customes + instead of customes
somber falcon
hey can you explain what might be the reason for my issue above? @torn sphinx i've defined everything and idk what im doing wrong
torn sphinx
or just + customes
atomic warren
k
torn sphinx
can u take more screenshots @somber falcon
torn sphinx
did you take it from https://github.com/MagicStack/asyncpg
torn sphinx
not what i meant
somber falcon
what lines?
torn sphinx
of the errors
torn sphinx
where its comming from
somber falcon
@commands.Cog.listener()
async def on_message(self, message):
if message.author == self.client.user:
return
if message.author.bot == True:
return
author_id = str(message.author.id)
guild_id = str(message.guild.id)
user = await self.client.pg_con.fetch("SELECT * FROM Users WHERE user_id = $1 AND guild_id = $2", author_id, guild_id)
if not user:
await self.client.pg_con.execute("INSERT INTO Users (user.id, guild_id, lvl, exp) VALUES ($1, $2, 1, 0)",
author_id, guild_id)
user = await self.client.pg_con.fetchrow("SELECT * FROM Users WHERE user_id = $1 AND guild_id = $2", author_id, guild_id)
await self.client.pg_con.execute("UPDATE Users SET exp = $1 WHERE user_id = $2 and guild_id = $3", user['exp'] + 1,
author_id, guild_id)
if await self.lvl_up(user):
await message.channel.send(f"Congrats {message.author.mention}! You leveled up to {user['lvl'] + 1}")
it says line 41
and thats the whole part
atomic warren
oh ok
torn sphinx
idk man i dont know shit ab sqllite ima have to guess you did some typo or put a letter too early python is pretty strict ab the placing
or try to put the defined user thing
at the top of the script
torn sphinx
if its discord.py ask in #discord-bots
somber falcon
well its just database related
wraith trail
@atomic warren Should 'customes' be 'customers'?
c.execute("INSERT INTO customer VALUES (?,?,?)", customes)
atomic warren
eya
yea
import sqlite3
conn = sqlite3.connect('customer.db')
c = conn.cursor()
c.execute("SELECT * FROM customer")
#print(c.fetchone())
#print(c.fetchall())
#c.execute("""CREATE TABLE customer (First_name text,
#second_name text,
#number text )""")
customes = [('nitin2', 'gupta2', 'somethingtoo'), ('nitni 4', 'gupta4', 'maybe'), ('ntin3', 'gupta3', 'something')]
c.execute("INSERT INTO customer VALUES (?,?,?)" , customes)
conn.commit()
conn.close()``` File "c:\Users\MES\Desktop\sqlite time!\sql.py", line 18, in <module>
c.execute("INSERT INTO customer VALUES (?,?,?)" , customes)
sqlite3.InterfaceError: Error binding parameter 0 - probably unsupported type.```@torn sphinx can u help me a bit more i renamed my table and it works now but i'm getting another error its now column related
atomic warren
help me pls too ;-;
atomic warren
yea..
tepid crow
im creating a pool inside a command and i keep getting that error
discord.ext.commands.errors.CommandInvokeError: Command raised an exception: TooManyConnectionsError: too many connections for role "owcwupwi"
@somber falcon it seems you dont have a column named user_id
atomic warren
help me also fi you can
somber falcon
i do tho
tepid crow
can you saw me schema?
wraith trail
@atomic warren You are passing multiple lists, I think you would need to loop through the lists or use executmany()
somber falcon
ok somehow fixed by renaming everything and now im getting this error
atomic warren
oh ok
wraith trail
@atomic warren
Here:
c.execute("INSERT INTO customer VALUES (?,?,?)" , customes)
customes is not a single list