discord.ext.commands.errors.CommandInvokeError: Command raised an exception: NameError: name 'database' is not defined
#databases
tough needle
and in your main: from database import database
tough needle
ogey
tough needle
no idea, google it
sharp magnet
Not even google knows
¯_(ツ)_/¯
from cogs.database import database heh it was just this
found it on stack overflow
@tough needle I've got somewhere
Now a whole new
issue
yay
Traceback (most recent call last):
File "C:\UsersAppData\Local\Programs\Python\Python38-32\lib\site-packages\discord\ext\commands\core.py", line 83, in wrapped
ret = await coro(*args, **kwargs)
File ", line 191, in add_coins
database.add_coins_db(user_id, added_coins)
File , line 17, in add_coins_db
cursor.execute("""
sqlite3.OperationalError: no such table: users
The above exception was the direct cause of the following exception:
Traceback (most recent call last):
File "C:\UserppData\Local\Programs\Python\Python38-32\lib\site-packages\discord\ext\commands\bot.py", line 892, in invoke
await ctx.command.invoke(ctx)
File "C:\UsersAppData\Local\Programs\Python\Python38-32\lib\site-packages\discord\ext\commands\core.py", line 797, in invoke
await injected(*ctx.args, **ctx.kwargs)
File "C:\UsersppData\Local\Programs\Python\Python38-32\lib\site-packages\discord\ext\commands\core.py", line 92, in wrapped
raise CommandInvokeError(exc) from exc
discord.ext.commands.errors.CommandInvokeError: Command raised an exception: OperationalError: no such table: users```Read the error
sharp magnet
I did
it cant find the table
but there is a table
heh
the other things are going to be other shit im going to work on once this is done
@tough needle Is there something really obvious that im missing here or
I'm gonna copy a message link here because its 4 am where I am so im gonna go to bed if you've got any idea to the stuff that happened above then just @ me and i'll take a read
tribal dome
hi i was wondering if someone would be able to assist with mongodb update query
tepid cradle
ask away
tribal dome
im trying to update my dictionary nested inside an array in my mongo Collection for a rest api. I'm tryin gto query and evaluate if the name matches, then update the collection fields with the new info. I can't seem to get it to work, it just returnss True without modifying.
-Tried dot notation, commented out, same result
-Tried using array_filter, kept claiming it's undefined
def put(self, user_name):
#sample incoming object
item = {"name": "oranges", "category": "TEST", "purchase_date": "11/21/20", "expiration_date": "12/04/20", "count": 5}
item_name = item["name"]
item_category = item['category']
item_purchase_date = item["purchase_date"]
item_expiration_date = item["expiration_date"]
lookup = collection.find_one({"user_name": user_name})
db_item = lookup.get('user_items')
#Try just updating item_category first
for index, entry in enumerate(db_item):
name = json.loads(entry).get("name")
if name == item_name:
# collection.update_one({"user_name.user_items":item_name},
# {"$set": {"user_name.$.category": item_category})
collection.update_one({"user_name.user_items": item_name},
{"$set": {"category": item_category}})
return True```I'm not very familiar with MongoDb, so I might be wrong here, but you've used user_names.user_items, but user_items doesn't seem to be nested inside user_names
They seem to be on the same level
tribal dome
does it have to be nested to use the dot notation?
tepid cradle
yes, that's what the dot notation indicates, it is used to access child elements
stable violet
working in postgresql/python, I've written this statement which successfully runs to update many rows at once using psycopg2,
"UPDATE daily_data SET "
"active_seconds=data.active_seconds + daily_data.active_seconds, "
"inactive_seconds=data.inactive_seconds + daily_data.inactive_seconds "
"FROM (VALUES %s) "
"AS data (active_seconds, inactive_seconds, streamer_id, viewer_id, date, game)"
"WHERE "
"streamer_id=data.streamer_id AND "
"viewer_id=data.viewer_id AND "
"date=data.date AND "
"game=data.game"```
I've got a second statement
"UPDATE viewer_data SET "
"honor=data.honor + viewer_data.honor "
"FROM (VALUES %s) "
"AS data (honor, streamer_id, viewer_id)"
"WHERE "
"streamer_id=data.streamer_id AND "
"viewer_id=data.viewer_id"```
which is throwing an error
psycopg2.errors.AmbiguousColumn: column reference "streamer_id" is ambiguous
LINE 1: ...37)) AS data (honor, streamer_id, viewer_id)WHERE streamer_i...
I've asked a couple other people and have been looking at this for a while now and both I and others (in SWE) are not seeing the issue, any help?
@ me if you respond please in case I don't see immediately
harsh pulsar
Always specify the table you're using
In your WHEREs you dont
Hence ambiguity hence error
torn sphinx
I've changed the query I was working on to just get the results from those two tables separately. Now the question - is it more efficient to filter the subqueries separately, or apply WHERE to the resulting table t?
SELECT
emote,
sum(msg_count) as message_count,
sum(rct_count) as reaction_count,
sum(msg_count) + sum(rct_count) as total_count
FROM (
SELECT
emote,
period,
guild_id,
count AS msg_count,
0 AS rct_count
FROM emotes
> WHERE guild_id = 1 AND
> period > '2020-07-01'
UNION ALL
SELECT
emote,
period,
guild_id,
0 AS msg_count,
count AS rct_count
FROM reactions
> WHERE guild_id = 1 AND
> period > '2020-07-01'
) AS t
>WHERE guild_id = 1 AND
>period > '2020-07-01'
GROUP BY emote
ORDER BY total_count DESC```thank you, got it down now
humble pike
Hey guys, Using SQLalchemy relationships
something like,
session.query(User). \
options(sqlalchemy.orm.joinedload(User.Addresses)). \
order_by(User.firstseen.desc()). \
limit(limit). \
all()
How do i specify how many addresses to return?
jade sphinx
I'm trying to send a simple search request to ES but I'm getting all these errors: https://bpa.st/Z52Q
and each block of errors ends with "ssl.SSLError: [SSL: WRONG_VERSION_NUMBER] wrong version number (_ssl.c:1123)"
humble pike
@blazing void That returns 10 users, but if there was 20 addresses it would still return 20 of them all as address objects.
blazing void
oh I see, you have a joinedload
have you tried doing a regular join?
is Addresses a table? if your relations are set up correctly, you should be able to query(User, Address) or query(User).join(Address)
or in fact... if your Addresses are relations, do you even need the join?
humble pike
Ths works but its limited the amount of users it returns, but doesn't limit the amount of rows returns from host_snapshots.
I get 24 snapshots a day, so for each host if I just wanted to display 24 hours worth of data, I would only need the last 24 snapshots
As you can see, 13 snapshot objects, when i'm trying to limit it 🙂
humble pike
Um, ultimately could be looking at 200-300 hosts. if not more 😂
future proof would be 10k.
but would limit queries to 500 ish
blazing void
I'm not sure there's an easy way to do the joinedLoad, but I think doing the join with query(Host, Whatever) or query(Host).join(Whatever) should get you your data without lazy loading
but also 10k entries in a database isn't much, but I do agree resolving 500 relations lazily would be slow
humble pike
especially if I have a years worth of host_snapshots it's trying to load per host!
Brains not working
humble pike
@blazing void found https://docs.sqlalchemy.org/en/13/orm/join_conditions.html#row-limited-relationships-with-window-functions in the docs.. too tired to know how to use lol
torn sphinx
I need help on setting up aiosqlite
torn sphinx
Nvm
So, I am trying to delete data from a sqlite database, using DELETE FROM <table> WHERE <condition> does not work, but DELETE FROM <table> works, could you please help me figure this out?
A mre with pytest test and output can be found here https://hatebin.com/zptehnikcg, please let me know if I forgot anything for the mre.
I am aware I should be using the ? notation to pass arguments to the queries, I will fix that as soon as it is apropriate
blazing arch
Your condition is probably wrong
pulsar stag
^
blazing arch
Try the exact same condition with a select and see what it returns
torn sphinx
let me recheck, I checked it before some times, but I will try that select method
Hey, I want to use aiosqlite to make a operation system for my discord bot. When someone does the command !create_op OperationNameHere it's going to create a text channel (already coded that) and then log the operation in the database. E.x that it logs what they named the channel to.
I will jump back to #discord-bots to make another command and then I guess come back here to log that thing too.
Anywho, my question is how do I connect to the aiosqlite database, how do I make a table, and how do I insert & print out the data?
glad spoke
async def addreason(self, ctx, punishment_id: str, *, reason: str):
query = "UPDATE moderation SET warning=" + reason + " WHERE punishmentID=" + punishment_id
cursor.execute(query)
db.commit()
For some reason this SQL query won't execute, nothing after it will run so thats what I'm assuming, no errors are returned either.
torn sphinx
yep it is wrong, thank you for making me see it 😂
blazing arch
My guess would be that id is an int, not a string
torn sphinx
indeed it is, but taking the '' does not change the output
glad spoke
how would I change it to an ID?
pulsar stag
int(punishment_id)
glad spoke
Ah so an int
but the SQL query would fail if I parsed and int, surely?
Yeah it expects a string.
by default it is a string
both are
torn sphinx
ok I cannot figure this out...
blazing arch
Are you sure you have something in there with an id of 0 ? IDs usually start at 1 in databases @torn sphinx
torn sphinx
1s, I will send prints.
these are the tables
And here's the relevant part of the test
db_man = DBManager(':memory:', schema=schema)
c = Class(db_man, "a", 8, 1)
e = Evaluation(db_man, class_id=c.get_primary_key()[1], weight=0.30, eval_id=-1)
print(db_man.fetch_all_table(e))
print(db_man.fetch_all_table(c))
assert db_man.get_table_size('evaluations') == 1
assert db_man.get_table_size('classes') == 1
print("------------------------")
e.remove()
print(db_man.fetch_all_table(e))
print(db_man.fetch_all_table(c))
assert db_man.get_table_size('evaluations') == 0 # ERROR
assert db_man.get_table_size('classes') == 1
oh wait
nah nvm, could be some error on the schema but I don't think so. here it is: https://hatebin.com/jmcijpores
blazing arch
Can you print a SELECT id FROM 'evaluations' ?
blazing arch
Well that's weird
torn sphinx
any clue on anything I should look for?
I placed that select both before and after the deletion, same result
blazing arch
I have no idea sorry, especially since SQL is my weak spot :/
torn sphinx
understandable, thank you for the help
torn sphinx
Does anyone have any suggestions for db's other than sqlite3 to be used for a discord bot
Im not really liking sqlite3
lucid needle
soo
torn sphinx
mysql maybe?
lucid needle
something is wrong with my db browser sqlite
so when i edit the table data in manual it gives me database is locked error
misty zenith
i have an issue, i'm trying to get a price of a certain product and do a calculation with that price. but when i try to do a calculation with the result, it says builtins.TypeError: 'int' object is not subscriptable. Does anyone know what i'm doing wrong?
if price:
amount = int(input('hoe veel wil je? (stenen zijn gerekend in m2 ) \n'))
for i in price: #error happens here
total = amount * i[1]
return product, total #till here
that's my code^. PC stands for product code
blazing void
it's telling you i is not a string, list, or dictionary, or anything else that would allow you to do i[1]
also your code appears to be overwriting total for every thing inside the structure price, there is something wrong with your code beyond just syntax
misty zenith
oh ok.... sorry, i'm still new at programming
blazing void
oh yeah, no worries, just letting you know once you fix i[1] problem you'll need to look at hat's going on with total = inside a for loop
misty zenith
you were right! solved it, thanks!!
grim lotus
k = await _bot.pg_con.fetchrow("""SELECT im FROM test WHERE name = 'kek'""")
with open('test.gif', 'wb') as f:
f.write(k)```if you open test.gif as binary, then f.write() expects bytes-like
what you're passing it is an asyncpg.Record, you need to read the field from it
presumably im
grim lotus
I have the bytes in im and im is a bytea typw colums
normal igloo
you need to get the image from the record object
grim lotus
Okay
How to get the image from record object ?
y = k["im"] ? Amd write y ?
It worked but i m struggling to send test.gif
normal igloo
wdym
grim lotus
k = await _bot.pg_con.fetchrow("""SELECT im FROM test WHERE name = 'kek'""")
with open('test.gif', 'wb') as f:
f.write(k["im"])
How do i send the test.gif file now ?
woeful tinsel
could anyone teach me how to use PostgreSQL? i've tried reading the toutorials and dont really understand. I'm just trying to basicly have a dictionary where i can retrieve a large list given an integer
ping me if you can help ty in advance
normal igloo
Wdym, why are you writing to a file if you just want to send the data in that file somewhere
use sockets probably
woeful tinsel
how would i use sockets?
grim lotus
.ojsk py
k = await _bot.pg_con.fetchrow("""SELECT im FROM test WHERE name = 'kek'""")
#getting the bytes data
file = discord.File (k["im"])
#bytes data ^
await ctx.send(file = file)
#sending it```was test.gif a valid image when you wrote it
lime echo
normal igloo
the error is on the line file gets declared so
normal igloo
was test.gif a valid image when you wrote it
@normal igloo
normal igloo
Yes, but when you did write it was it a valid image
grim lotus
was getting the same error
normal igloo
do print(type(k["im"]))
grim lotus
This is working fine
<class 'bytes'>
do
print(type(k["im"]))
^^
It does takes it as a bytes object
normal igloo
No it doesn't
"fp
A file-like object opened in binary mode and read mode or a filename representing a file in the hard drive to open."
"Note If the file-like object passed is opened via open then the modes ‘rb’ should be used.
To pass binary data, consider usage of io.BytesIO."
grim lotus
Oh
So how do i ? Send the text.gif ?
k = await _bot.pg_con.fetchrow("""SELECT im FROM test WHERE name = 'kek'""")
with open('test.gif', 'wb') as f:
f.write(k["im"])
type(k["im"])```
> <class 'bytes'>
k = await _bot.pg_con.fetchrow("""SELECT im FROM test WHERE name = 'kek'""")
#getting the bytes data
file = discord.File (k["im"])
#bytes data ^
await ctx.send(file = file)
#sending it
> Null bytes errorHow do i send that gif
gentle silo
"To pass binary data, consider usage of io.BytesIO.""
cobalt owl
if I have a massive basically undownloadable table in Access (can't import to Excel), can I use pyodbc to pull that data into Python to be manipulated or printed out?
I'm trying to find use cases for Python in my workplace and I feel like manipulating data from our HUGE access tables would be a good start
blazing void
depending on the type of data, maybe even pandas would be more appropriate
pandas' read_sql_query will accept a connection via pyodbc
opal flint
You can use pyodbc for that. I've found it useful for pulling out data from old access tables and then using pandas to manipulate it for data analysis. I usually also end up re-storing it into something like sqlite3 since it's a flat file db.
cobalt owl
So my data updates every night at 3 AM, and I'd like to more pull information based on certain filters... So, it's not really 'data analysis' so much as it's like "If we have less than a month of inventory on hand on the refresh today, pull it out and send to the purchasing team' and things like that
I guess realistically I can just create an Access report that does that work?
blazing void
so you want ETL to run at shortly after 3 AM to do some stuff
cobalt owl
More or less yes (after googling what ETL is :))
blazing void
I use Apache Airflow for ETL, it's written in Python, but I don't recommend that for starting with, it's a bit of a pain to set up and doesn't feel "modern"
but anyway, sounds like if your data is in Access, try fetching the data via pyodbc first; then try getting the data into Pandas next, which'll let you easily modify it
cobalt owl
OK, I wanted to make sure I wasn't using a $10,000 hammer to slam in a $.01 nail by using Python/Pandas if you know what I mean
Thanks for the help guys!
blazing void
sure sure, Pandas is very good for this, you can basically treat it like a programmer's excel
(and good investment in skills if you need to do more data-sciency things later)
cobalt owl
Yes! That's what I'm hoping for. Using it to get comfortable with Pandas while doing more 'simple' tasks and then move forward from there
nocturne lance
hey! how do I create a database for my game?
if anyone answers this please ping me
quartz storm
im trying to learn how to do database
can someone help me or refer me to a good video series that explains how to do get started?
lucid needle
Ignoring exception in command game start:
Traceback (most recent call last):
File "C:\Users\csuser\AppData\Local\Programs\Python\Python37-32\lib\site-packages\discord\ext\commands\core.py", line 83, in wrapped
ret = await coro(*args, **kwargs)
File "c:\Users\csuser\Desktop\KAITOU JOKER\start_game.py", line 87, in start
await cursor.execute(sql, (ctx.author.id, str(greentick)))
AttributeError: '_LazyloadContextManager' object has no attribute 'execute'
The above exception was the direct cause of the following exception:
Traceback (most recent call last):
File "C:\Users\csuser\AppData\Local\Programs\Python\Python37-32\lib\site-packages\discord\ext\commands\bot.py", line 892, in invoke
await ctx.command.invoke(ctx)
File "C:\Users\csuser\AppData\Local\Programs\Python\Python37-32\lib\site-packages\discord\ext\commands\core.py", line 1234, in invoke
await ctx.invoked_subcommand.invoke(ctx)
File "C:\Users\csuser\AppData\Local\Programs\Python\Python37-32\lib\site-packages\discord\ext\commands\core.py", line 797, in invoke
await injected(*ctx.args, **ctx.kwargs)
File "C:\Users\csuser\AppData\Local\Programs\Python\Python37-32\lib\site-packages\discord\ext\commands\core.py", line 92, in wrapped
raise CommandInvokeError(exc) from exc
discord.ext.commands.errors.CommandInvokeError: Command raised an exception: AttributeError: '_LazyloadContextManager' object has no attribute 'execute'
anyone helping me?
PLS ANOYONE?
delicate fieldBOT
tender coral
Here click this link copy paste code press save and send us the link
hasty juniper
An example of an asynchronous database of this sqlite3 on Github or something else
lucid needle
@tender coral ?
tender coral
I asked if you can send full code
Because just the error isnt enough
I sent a website where you can paste the cose
lucid needle
@tender coral full code of the command?
@game.command()
async def start(self, ctx):
if await is_unlocked(ctx.author, self.bot):
pass
if not await is_unlocked(ctx.author, self.bot):
if not await in_game(ctx.author, self.bot):
greentick = self.bot.get_emoji(731565652250460181)
embed = discord.Embed(description=f'Please watch the gif and when you are ready react to {greentick}', color=0xffffe3)
embed.set_image(url='https://images-ext-1.discordapp.net/external/2MuBkRl-BOKfTZtJ0qLRm1IioNxQm3c3lRRmnfrKFLI/https/im2.ezgif.com/tmp/ezgif-2-63d1e669fcfd.gif')
introduction = await ctx.send(embed=embed)
await introduction.add_reaction(greentick)
cursor = self.bot.db.cursor()
sql = 'INSERT INTO wait_for_emoji (user_id, emoji) VALUES (?, ?)'
await cursor.execute(sql, (ctx.author.id, str(greentick)))
sql = 'INSERT INTO in_the_game (user_id) VALUES (?)'
await cursor.execute(sql, (ctx.author.id,))
def check(r, u):
return r.message.channel == ctx.channel and r.emoji == greentick
await self.bot.wait_for('reaction_add', check=check)
await ctx.send('WORKED')
this isthe full code
tender coral
Thanks
lucid needle
so will u help me
thats in cog
and the command is the sub command
cursor = self.bot.db.cursor()
sql = 'INSERT INTO wait_for_emoji (user_id, emoji) VALUES (?, ?)'
await cursor.execute(sql, (ctx.author.id, str(greentick)))
``` is the main problem i thinkhe says that cursor has no attribute execute()
lucid needle
WHAHAAAt
tender coral
So youre trying to run a function that doesnt exist
lucid needle
@tender coral but
async def in_game(user, bot):
cursor = await bot.db.cursor()
sql = 'SELECT * FROM in_the_game WHERE user_id = ?'
await cursor.execute(sql, (user.id,))
row = await cursor.fetchone()
if row is None:
game = False
else:
game = True
return game
async def waiting_for_emoji(user, bot):
cursor = await bot.db.cursor()
sql = 'SELECT * FROM waiting_for_emoji WHERE user_id = ?'
await cursor.execute(sql, (user.id,))
row = await cursor.fetchone()
if row is None:
waiting = False
else:
waiting = True
return waiting
these works
hmmm
idk self.bot.db.cursor works in other cogs @tender coral
torn sphinx
ok, funny question could i have a database in json 😆
for my py bot
proven arrow
Yes you can, but it can also get messy and complicated at times with JSON. Personally I would not recommend it, although you can probably can get away with it for small bots, however for growing/medium to larger bots you should consider using a database since its much cleaner, and will be more efficient on your bot in terms of async as well. @torn sphinx
lucid needle
@tender coral i know how to fix it
that was aiosqlite3 but i forgot to put await from of self.bot.db.cursor() !
damn
humble pike
def test(limit=10) -> List[Host]:
partition = select([
HostSnapshot,
func.row_number().over(
order_by=HostSnapshot.height, partition_by=HostSnapshot.publickeystring
).label('index')
]).alias()
partitioned_b = aliased(HostSnapshot, partition)
Host.partitioned_bs = sqlalchemy.orm.relationship(
partitioned_b,
primaryjoin=and_(partitioned_b.publickeystring == Host.publickeystring)
)
session = db_session.create_session()
hosts = session.query(Host).options(sqlalchemy.orm.selectinload(Host.partitioned_bs))
return hosts
Hey guys, trying to do a Row-Limited Relationships with Window Functions, but just cant seem to get it to work. can anyone help?
sterile raft
Hello! I'm trying to make a leveling system in my bot and using I'm using postgresql, it seems to give me a random error which is so weird. Basically let me explain how it works and how it doesn't. So when I send a normal message it works perfectly, and when I send a command I get this py Ignoring exception in on_message Traceback (most recent call last): File "C:\Users\Dell\AppData\Local\Programs\Python\Python37\lib\site-packages\discord\client.py", line 312, in _run_event await coro(*args, **kwargs) File "c:\Users\Dell\Desktop\Aidas\Rod\cogs\listeners.py", line 31, in on_message db.xp(message.author.id, "+", server_data[2]) File "c:\Users\Dell\Desktop\Aidas\Rod\utils\essentials\sql.py", line 63, in xp xp = f_data[6] TypeError: 'NoneType' object is not subscriptable I've talked to people in d.py and one of them said it could be a what's the database giving me.
(567321250696003606, 1000, 0, 'none', 0, 0, 29, 100) this is how my table looks like, but it gets to None when ever the message is a command...
torn sphinx
so if i have a one to many relationship i need two tables right
the second one has the id of the values in the first
fallow elm
one to many is usually talking about the relationship between two different resources
torn sphinx
like if u have a class with a parameter thats a list
fallow elm
yeah, usually you'd store that in a separate table
since relational databases don't traditionally support arrays
thought that's not really true anymore 
but if you're talking about a normalized relational design, yes
but it's easier to talk concretely if you have a use case in mind
torn sphinx
how do i check if a record exists in a databse?
using sqlite3
if it is not possible in sqlite3 then where is it possible
pulsar stag
SELECT * FROM ______ WHERE condition;
or if you literally just wanna know if it exists
SELECT 1 FROM some_table WHERE condition;
ok thanks
real spoke
Hey lads, I have a very weird issue
Basically whenever I am trying to connect to the database, the connection just "hangs"
It even happens when I am trying to connect locally
I am using Postgres if it helps
Is there any way to run some diagnostics as to why this might be happening
fallow elm
not really a python question but it sounds like you don't have proper network access
is the database local to where you're connecting from?
real spoke
Yep, it is on my PC
but it just hangs/freezes when I am entering my credentials and wanting to connect
fallow elm
and you can't connect with psql on the command line either?
real spoke
Nope, I was using psql to connect
but after entering password and pressing Enter, nothing happens
no error, nothing
fallow elm
what operating system
real spoke
Windows Server 2016
Weirdest part is that it was working fine like 12 hours ago
but then it just stopped
Basically I can still connect to the DB if I reset my PC, but after like a few mins it stops responding
I tried using PGAdmin, and it gave Connection to the server has been lost error when trying to connect to DB locally
fallow elm
wonder if it's some kind of firewall blocking it
or if the database is being stopped somehow
i'm not too familiar with windows administration though, sorry
real spoke
Firewall is off, but not sure how it can be stopped either 🤔
fallow elm
definitely you need to solve the local database connection issues before trying python stuff though
real spoke
Yepp
I looked up PGAdmin help page about that error, and it says the following:
"This error message indicates that the connection attempt has taken longer than the specified threshold; there may be a problem with the connection properties provided on the Server dialog, network connectivity issues, or the server may not be running."
Basically it makes sense because connection just hangs, aka takes longer than supposed to
fallow elm
are there logs you can look at for postgres
real spoke
not sure, I might google how to
ancient abyss
How would I insert datetime.now() into postgresql?
stamp = datetime.now()
name = input('Enter your name: ')
cursor.execute('INSERT INTO Testing (stamp, name) VALUES (%s, %s)',
(stamp, name))
conn.commit()
Because, right now, it pushes me down and spits errors at my face
muted ingot
i need some help with aiopg
ive been working with asyncpg and moved because i was getting infinite hangs
but im not sure how to do .execute equivalent in aiopg
ornate ferry
How would I insert datetime.now() into postgresql?
@ancient abyss use current_timestamp
ancient abyss
Ah, so just replace datetime.now() with that?
ornate ferry
try ```python
name = input('Enter your name: ')
cursor.execute('INSERT INTO Testing (stamp, name) VALUES (%s, %s)',
(current_timestamp, name))
conn.commit()```
or maybe with GETDATE() @ancient abyss
muted ingot
do you have to reconnect to aiopg every time or can you just make one pool and use that?
ornate ferry
sry try like this : ```python name = input('Enter your name: ')
cursor.execute('INSERT INTO Testing (stamp, name) VALUES (current_timestamp, {})')
.format(name)
conn.commit()```
@ancient abyss
ancient abyss
Oh, never seen it done like that. Alrighty
muted ingot
@client.command()
async def welcome(ctx: commands.Context, channel:discord.TextChannel):
"""Use this to set the custom welcome channel"""
await ctx.send('Registered.')
channel_id = channel.id
guild_id = ctx.guild.id
await ctx.send('Registered 2.')
async with client.pool.acquire() as conn:
await conn.execute("INSERT INTO welcomechannels VALUES ($1, $2);", channel_id, guild_id)
await ctx.send('Registered 3.')|
Registered 3 never gets sent.
And there aren't any errors
So the code gets hung on a specific line.
coral atlas
Hi all - Anyone able to help me find a sample working (simple) program for Influx ?
I've got their sample program but keep hitting errors - just looking for one that isn't 4 years old and that forms a working basis that I can learn from....
muted ingot
@client.command()
async def welcome(ctx: commands.Context, channel:discord.TextChannel):
"""Use this to set the custom welcome channel"""
await ctx.send('Registered.')
channel_id = channel.id
guild_id = ctx.guild.id
data = {}
data['welcomechannels'] = []
data['welcome channels'].append({
'guild_id': guild_id,
'channel_id': channel_id
})
with open('data.json', 'w') as f:
json.dump(data, f)
im trying write a json file using this but none of the data ever gets written
I tried making a data.json file
but it still doesn't work
it seems to hang before it reaches the end
rich trout
It's crashing but you're probably suppressing the error message
you really shouldn't do that
data['welcomechannels'] = []
data['welcome channels'].append({
space doesn't match, so it's throwing a keyerror
muted ingot
oh
i dont remember supressing errors
ill try this
thank you
damn that was so obvious
i appreciate the help :P
@rich trout I so the bot reached the end of the command sequence
however, I dont see the data in the json file
I have the file in the same folder
rich trout
well, how are you running the bot?
muted ingot
it runs it through the same folder I think?
rich trout
you can print(os.getcwd()) to find out where it's putting it for sure
rich trout
Yep
muted ingot
thats the same directory?
rich trout
your file will be at C:\users\adi\data.json
rich trout
the "current working directory" is where file operations happen if you don't specify more exactly
since you're calling python from that directory, that's the "current working directory" even if you're running something from far away
muted ingot
is there a way to specify where to get json and to save json from
like in terms of a more specific directory
rich trout
You could use the whole path, or you could use cd to change the current directory to make it easier on you
muted ingot
wait so there isnt a way to change where the json file is saved?
rich trout
I just gave two options for how to do that?
rich trout
Are you clicking a button to get that terminal, or are you typing python e:\...
rich trout
yeah
muted ingot
now i see what you mean by working directory
rich trout
For example, if you want to use rm
it would be weird for rm to look for files in C:\windows\system32 if you typed rm text.txt
muted ingot
yea
rich trout
cd folder or cd .. are pretty much all you need.. on windows, in powershell (PS C:\ gives that away) probably just fine with cd E:\ to change drives, but the regular terminal wants you to give it \D too, for cd /D E:\
rich trout
That should work 👍
muted ingot
if i just want to run the main.py file, do i have to do anything
or does the python file automatically navigate the folder it's in
rich trout
python checks the current directory just like all other well-behaved tools
so, if main.py is under Katchi-bot, python main.py should suffice
muted ingot
yep
i just double click to run it lol
@rich trout this is really weird it still saves to C:\users\adi\data.json
rich trout
if you're clicking a button labeled "run" or "testing" in vscode, it may be setting the CWD to something different
rich trout
the print(os.getcwd()) bit I sent will tell you what python thinks the CWD is
rich trout
lol, it's doing it again
rich trout
I never got the hang of configuring vscode, so I can't really help with that, but I know it's possible
muted ingot
yea im just gonna double click screw it
rich trout
<
muted ingot
POG
rich trout
muted ingot
rich trout
happy coding
rich trout
I am gonna go sleeps now
rich trout
lol
muted ingot
wait
rich trout
should be straightforwards
muted ingot
b4 u go
i wrote this
with open('data.json') as json_file:
data = json.load(json_file)
for p in data['welcomechannels']:
print('guild_id: ' + p['guild_id'])
print('channel_id: ' + p['channel_id'])
it runs in the same command
but it doesnt print anything
i think there might be an error?
rich trout
I don't see anything obviously wrong
so I'd look into your command error handler to make sure it prints exceptions
rich trout
By default they just show up in the terminal, I think there's a beginning step most people do that ends up hiding them
It'd be named on_command_error
muted ingot
OH
@client.event
async def on_command_error(ctx, error):
if isinstance(error, commands.BadArgument):
await ctx.send('I could not find that member...')
if isinstance(error, commands.MissingPermissions):
await ctx.send('You are missing permissions to access that command.')
Bruh moment
i made a custom error handler
imma deleet
concatenation
i figured it out
rich trout
oh, guild_id was an int wasn't it
derp
I shoulda seen that
but, glad you've got a handle on both of them now
muted ingot
well thanks for all the help @rich trout i appreciate it all
ive been trying database stuff but apparently theres a bug in asyncio
rich trout
oh?
rich trout
what bug?
rich trout
oh, gross that
muted ingot
apparently its a bug that needs to be fixed in the next version of asyncio
so i wasted 2 days trying database stuff when the lib was broken :P
muted ingot
not yet
i just gave up
i was like not worth it
might was well do it in a json file
its easier
muted ingot
is there a way to append to an existing json folder?
"Expense": {
"Name": "OneTel Mobile Bill",
"Amount": "39.90",
"Due": "28/12/2011",
"Recurrence": "1 Months",
"Paid": "0",
"LastPaid": "01/01/2002"
}
}```^ init
"Expense": {
"Name": "OneTel Mobile Bill",
"Amount": "39.90",
"Due": "28/12/2011",
"Recurrence": "1 Months",
"Paid": "0",
"LastPaid": "01/01/2002"
},
"Expense": {
"Name": "Loan Repayment",
"Amount": "50.00",
"Due": "08/03/2012",
"Recurrence": "3 Months",
"Paid": "0",
"LastPaid": "08/12/2011"
}
}```
^^ end resultthis is just an example
please @ me if you have a solution
upbeat lily
It looks like Java (or at least not like python) - so probably not in here, no
brittle pulsar
It's related to SQL but I don't understand it
database
I don't understand what it means, but I'm really interested to know what it's talking about
like the general idea.
torn sphinx
Hi, I need to setup aiosqlite for my discord bot.
I've done the pip install command and yeah. But I know nothing about except that it's supposed to be good for bots. (Been told in #discord-bots).
Anywho, is there anyone in here that can help me setup so I can log everytime somebody get's banned? I already have a ban command. I don't have any line of code about it in my script yet, that is why I am here.
Let me know if you can help me, thanks alot.
dull scarab
Have you looked at their documentation?
dull scarab
aiosqlite is just a async version of sqlite, which is basically a .txt file on steroids
dull scarab
dull scarab
Should be fairly similar to using sqlite, but with a bit more contextmanagers
torn sphinx
I'm not very good at sqlite either.
Kinda new to databases, and python overall ngl
I can see the examples in the docs
dull scarab
async with aiosqlite.connect(...) as db:
await db.execute("INSERT INTO some_table ...")
await db.commit()
async with db.execute("SELECT * FROM some_table") as cursor:
async for row in cursor:
...``` This is probably the 2 easiest usecasesWhere do I put it? That is what I can't figure out.
dull scarab
first being a normal insert, 2nd part being a select
Whereever you're doing the sql operation
torn sphinx
So whenever I am logging it?
torn sphinx
@bot.command()
async def ban(ctx):
embed=discord.Embed(
title="User Banned!",
color=0xff9100,
description=f"**User:** {member.mention}\n**Reason:** {reason}\n**Bot's Note:** {member} has been banned from {ctx.guild}!"
)
embed.set_footer(text=f"Banned by {ctx.message.author}")
await ctx.send(embed=embed)
async with aiosqlite.connect(...) as db: <--------
await db.execute("INSERT INTO some_table ...")
await db.commit() <-------
await member.ban(reason=reason)
``` Like that?async def somecommand(ctx, store_this):
async with aiosqlite.connect(...) as db:
await db.execute("INSERT INTO memos", (store_this, ctx.author.id))
await db.commit()``` etcgiven memos is a table with str | author
dull scarab
You need to make sure the execute and commit is inside the db context
preferably
and why wouldn't you have quotes around a string?
dull scarab
uh
dull scarab
Why would you keep those?
torn sphinx
Bro
dull scarab
You still need to name it something
dull scarab
have you defined bans?
torn sphinx
Nope.
dull scarab
Then no
torn sphinx
How do I define it?
dull scarab
well, it's the name of the file you want to be your database
torn sphinx
Yeah.
dull scarab
so you define it as a string
torn sphinx
That was what I asked before
Oh, right.
async with aiosqlite.connect("bans") as db:?
dull scarab
sure
torn sphinx
Sweet.
dull scarab
I'd still call it bans.db though
torn sphinx
Will do.
dull scarab
so you know what it is
dull scarab
Now don't forget to create the table before inserting.
dull scarab
Something like that
You'll have to dig around if the syntax is correct for sqlite
I don't remember fully
Not every db supports IF NOT EXISTS
torn sphinx
Lemme google real quick
Brb
Let's just do it without IF NTO EXISTS
I can just remove the code line after I ran it the first time
torn sphinx
Oh, alright.
So
How do I create it?
CREATE TABLE ban_logs(user_id, INT)
Like this?
dull scarab
I'm sure you can experiment with that syntax and create what you're after.
Imma get back to work
torn sphinx
I use aiosqlite, and I am trying to insert a variable into the database. I get this as an error: discord.ext.commands.errors.CommandInvokeError: Command raised an exception: OperationalError: 4 values for 3 columns. And I understand the error. But is there any way to fix it?
This is my code:
await db.execute("INSERT INTO ban_logs (user_id, guild_id, reason) values (?, ?, ?, ?)",
(id_on_member, staff_who_banned, id_on_guild, reason))
(This is for my discord bot)
Hi, client.
(user_id, guild_id, reason) 3 columns
(?, ?, ?, ?) 4 values
cant insert 4 into 3
OHHHH
I forgot to add it there.
Thanks alot, bro.
check = conn.execute("SELECT userid, guildid FROM users WHERE guildid=$1 AND userid=$2", (ctx.author.id, ctx.guild.id,)).fetchone()
print(check)
if check == None:
print("I got here")
try:
with conn:
conn.execute("INSERT INTO users (guildid, userid) VALUES ($1, $2)", (ctx.guild.id, ctx.author.id,))
conn.commit()
except Exception as error:
print(error)
else:
print("Nope it exists")```
it just returns None even though it existsI am using SQLite3
timber jasper
How can i fix that ?
harsh pulsar
as a workaround, you can set guildid and userid as a composite primary key in your table @torn sphinx
then you dont even need to check
you can just try to insert and catch the failure if it fails
or insert or ignore into
timber jasper
--
torn sphinx
With aiosqlite, can I add 1 number each time the info is stored?
or
insert or ignore into
@harsh pulsar i get an error when usingignore
harsh pulsar
@timber jasper the error message is pretty clear on what the problem is...
timber jasper
i did avatar = await self.bot.db.fetchval("SELECT discordicon FROM discordguildssql.intro WHERE discordicon = $1", str(message.author.avatar_url))
torn sphinx
So it looks something like this:
| Number | Reason |
-------------------
0 | NSFW |
1 | Test |
2 | Hacking |
?
timber jasper
yeah but if i execute the command again it's works
harsh pulsar
@torn sphinx you can use an autoincrement column
torn sphinx
How?
harsh pulsar
autoincrement per user is not easy though
timber jasper
but if i delete the row in db and execute again and write that then it doesn't work
harsh pulsar
you will have to do that in the query, not when you insert @torn sphinx
torn sphinx
Kinda new to databases, try to explain it as easy as possible please.
Also, how do I delete a table using DB Browser for SQLite?
Found it.
timber jasper
Do you know how can i fix that ? @harsh pulsar
harsh pulsar
@timber jasper look at the error message, do you think you can explain it to me?
timber jasper
it's only say that the url not formed...
harsh pulsar
@torn sphinx your table is like this, right?
create table ban_log (
user_id integer,
guild_id integer,
reason text,
)
so when you query, you can do this
select
user_id,
guild_id,
reason,
row_number over (user_id, guild_id) AS ban_number
from ban_log
Wait
Query is like get it?
Or what does query mean?
@harsh pulsar 
What I am doing now is for join logs, not ban logs.
torn sphinx
@bot.event
async def on_guild_join(guild):
id_on_guild = guild.id
async with aiosqlite.connect("C:/Yoha/db/info_logs.db") as db:
await db.execute("CREATE TABLE join_logs (number int, guild_id int)")
await db.execute("INSERT INTO join_logs (number, guild_id) values (?, ?)",
(number, id_on_guild))
await db.commit()
This is was I have rn
harsh pulsar
https://www.db-fiddle.com/f/jPwcKcU2eHAEnj9bd8T1xw/0 @torn sphinx
torn sphinx
What is all this code? 😳
harsh pulsar
the left side is creating the example database
the right side is the query to get the data
dont panic
read each line
you know all of this syntax, you have seen it before
torn sphinx
And this?
harsh pulsar
you have seen 'insert' before, yes?
it is example data
press "Run" at the top
to see the output from the query on the right side
torn sphinx
Oh
I mean
This is what I want I guess
It's just that
For the join logs, I want it to count every server it joins kinda.
harsh pulsar
@timber jasper well i don't know the library you are using. but it is clearly expecting a url, but you passed a filename
timber jasper
discordpy and for database asyncpg (Postgresql)
torn sphinx
So it'll put a column to the left, the number. And one to the right, the guild id.
And the number one
Will just do +1 everytime it joins a new server
harsh pulsar
@timber jasper are you just trying to embed from a filename? you might want to ask about that in #discord-bots
timber jasper
and i did: str(message.author.avatar_icon)
torn sphinx
| Number | Guild_ID |
-------------------
0 | 1234 |
1 | 18445 |
2 | 123871 |
Like that
harsh pulsar
ok let me show you how
torn sphinx
Thank you.
harsh pulsar
the problem with that
torn sphinx
I don't need to be able to print it out
harsh pulsar
wait
torn sphinx
I just wanna store it
harsh pulsar
hold on
torn sphinx
Ait.
harsh pulsar
you dont even need the number in the table
torn sphinx
?
harsh pulsar
there are problems with trying to track the number in the database
torn sphinx
You are right
harsh pulsar
what happens if you leave a guild?
do you then update every number that's farther down in the table?
this is best not stored in the database
torn sphinx
No.
It's not like that.
I just want to store as many servers as possible
Lol
It shouldn't delete any of it
harsh pulsar
so the 1st guild you joined will always be 1
and the 13th guild will always be 13
even if you leave the 13th guild later
torn sphinx
Yeah.
harsh pulsar
ok
torn sphinx
It will stay in the db
Once the bot joins a server
It's in the db
Forever.
That is what I am thinking
harsh pulsar
ok
torn sphinx
| Number | Guild_ID | ------------------- 0 | 1234 | 1 | 18445 | 2 | 123871 |
@torn sphinx
And this is how the db should look like.
tame ore
what's your db?
torn sphinx
aiosqlite.
harsh pulsar
making 1000% sure the increment is exactly 1 is actually quite difficult
tame ore
not sure if sqlite supports unsigned 64bit ints
torn sphinx
@harsh pulsar If we can reach 99% that's good for me.
tame ore
well, it does
harsh pulsar
@tame ore integer is auto-scaling up to 8 bytes
torn sphinx
It's not a super big deal if there is 1 or 2 gaps.
I just don't want there to be gaps everywhere.
harsh pulsar
https://www.db-fiddle.com/f/dE2GjZkMEp1Wuk9jWLW25N/1 @torn sphinx
this should work, gaps will appear if you try to insert the same guild twice for example
harsh pulsar
if it's ok to join the same guild twice, you can remove the unique
torn sphinx
What's unique?
torn sphinx
oh ok
harsh pulsar
so if you say guild_id integer unique you can never have 1003 appear twice
see my updated example
@tame ore im not sure either
they just want to have it 🤷♂️
torn sphinx
harsh pulsar
sometimes there's no accounting for taste
and it's more "portable" than relying on rowid which is a very sqlite specific thing
torn sphinx
@harsh pulsar So wait. Where did you type unique in this code?
harsh pulsar
look at the previous version
craggy holly
On Sqlite3 does limiting text length on a column or something similar help reduce size on disk? Or does it not really matter?
If I have a column that defines a type that my application uses, would it be better to just use an integer 0, 1, 2, 3 or have a longer, text column that would have "PERCENTAGE", "FLAT RATE" etc?
torn sphinx
Oh there.
harsh pulsar
torn sphinx
So, why can't I just use unique and make it impossible for it to dublicate?
duplicate?*
torn sphinx
It shouldn't log it then
harsh pulsar
if you try to insert and it fails due to uniqueness constraint
you will have a non-sequential jump in the guild number
as per the sqlite3 docs
torn sphinx
AKA a gap?
harsh pulsar
yes
torn sphinx
I'll take the gap then.
Because I doubt someone will leave and rejoin the bot 1k times
harsh pulsar
@craggy holly as far as i know in sqlite it doesn't matter. in other databases it might matter. you can use an integer and then use an IntEnum in your python code, or you can just use a string. whichever is more comfortable to you
torn sphinx
And it's easier to spot a gap then it is to spot a different guild id
harsh pulsar
yeah
torn sphinx
So I can just delete it manually.
harsh pulsar
go with the unique constraint then
torn sphinx
So I use version 1?
torn sphinx
Ait.
craggy holly
Right, Thanks
harsh pulsar
ah nvm
you can't have autoincrement that isn't primary key
that's a postgres thing
nvm
go with option 1
harsh pulsar
@craggy holly for ref https://www.sqlite.org/datatype3.html
@torn sphinx and for your ref https://sqlite.org/autoinc.html
torn sphinx
@harsh pulsar 1 more thing
@bot.event
async def on_guild_join(guild):
id_on_guild = guild.id
async with aiosqlite.connect("C:/Yoha/db/info_logs.db") as db:
await db.execute("CREATE TABLE join_logs (number integer primary key autoincrement, guild_id integer unique)")
await db.execute("INSERT INTO join_logs (number, guild_id) values (?, ?)", <---
(number, id_on_guild))
await db.commit()
This is my code
What do I do, lemme make a arrow
At that arrow
What do I put at the "number" part?
@torn sphinx what does the number column signify?
It's just
Number of the line
| Number | Guild_ID |
0 | 1234 |
1 | 18445 |
2 | 123871 |
Like that
@torn sphinx
harsh pulsar
don't insert the number @torn sphinx
torn sphinx
Alright.
harsh pulsar
don't create the table each time...
you should write a separate script to create the tables for your application
torn sphinx
Sounds like alot of work when I can just do a #
But I got an error
@torn sphinx and I dont think you would want to create the table evertyime you ran on_guild_join?
harsh pulsar
😐
torn sphinx
parameters are of unsupported type
harsh pulsar
your setup script should have this:
import sqlite3
db = sqlite3.connect("C:/Yoha/db/info_logs.db")
with db:
db.execute("CREATE TABLE join_logs (number integer primary key autoincrement, guild_id integer unique)")
and your bot code should have this:
@bot.event
async def on_guild_join(guild):
async with aiosqlite.connect("C:/Yoha/db/info_logs.db") as db:
await db.execute("INSERT INTO join_logs (guild_id) values (?)", (guild.id,))
await db.commit()
or better yet...
your setup script should have this:
import sqlite3
DB_FILE = "C:/Yoha/db/info_logs.db"
db = sqlite3.connect(DB_FILE)
with db:
db.execute("CREATE TABLE join_logs (number integer primary key autoincrement, guild_id integer unique)")
and your bot code should have this:
DB_FILE = "C:/Yoha/db/info_logs.db"
@bot.event
async def on_guild_join(guild):
async with aiosqlite.connect(DB_FILE) as db:
await db.execute("INSERT INTO join_logs (guild_id) values (?)", (guild.id,))
await db.commit()
also could do this CREATE TABLE [IF NOT EXISTS] but useless
That doesn't work for aiosqlite
I've tried.
torn sphinx
👍
harsh pulsar
use a one-time setup script
torn sphinx
How?
harsh pulsar
i just showed you
write a script that creates all your tables
run that once
then just use your bot
torn sphinx
Nah
Seems like alot of work when just removing the line after is easier
Also
It works
But I see now how useless the number thing was
It's literally there.
Anywho, it works
Thanks for the help.
You're the man.
harsh pulsar
Seems like alot of work when just removing the line after is easier
it's not easier if you have a lot of code
you're welcome. just keep my advice in mind when you have 30 queries not 1 🙂
thick tusk
brazen charm
its pretty self explanitory
there is no column called namescam
your use of string formatting doesnt help
harsh pulsar
@torn sphinx print where? to the console? to discord?
torn sphinx
Discord
harsh pulsar
in general you just query the data
torn sphinx
1 sec.
harsh pulsar
you got the data from your database
now format it however you want
theres no special magic "format my data for me" function
torn sphinx
I want it to look like that
But it should say
Like
What the warning is
Like you said, I need to format it.
But how?
@harsh pulsar
harsh pulsar
you are just returning the cursor
torn sphinx
How do I do it the correct way then?
harsh pulsar
cursor = conn.execute('select blah blah')
data = cursor.fetchall()
that gives you a list of rows. each row is a tuple
torn sphinx
...
harsh pulsar
?
torn sphinx
I don't understand shit here.
harsh pulsar
what part don't you understand
torn sphinx
list of rows, each row is as tuple
harsh pulsar
cursor = conn.execute('select a, b from mytable')
data = cursor.fetchall()
data will be like
[
('a1', 'b1'),
('a2', 'b2'),
...
]
Uhhhh.
Let me give you my code
@bot.command()
async def warn(ctx, member : discord.Member, *, reason=None):
embed=discord.Embed(
title="User Warned!",
color=0xff9100,
description=f"{member} has been warned!"
)
embed.set_footer(text=f"Warned by {ctx.message.author}")
await ctx.send(embed=embed)
async with aiosqlite.connect("C:/Yoha/db/mod_logs.db") as db:
#await db.execute("CREATE TABLE warn_logs(user_id int, reason str)")
await db.execute("INSERT INTO warn_logs (user_id, reason) values (?, ?)",
(member.id, reason))
await db.commit()
This is the warn command
And there's nothing wrong with it, right?
harsh pulsar
where is the query
torn sphinx
But let's start at the beginning.
This is my warn command
And I want a warnings command
That shows the warnings
harsh pulsar
ok
@bot.command()
async def warnings(ctx, member: discord.Member):
warning_data = # query for warning data
embed_val = # create embed
# send embed
Also, this is more of a discord.py question. But do you know how I make it server side? So it won't show the warnings from server x in server y.
harsh pulsar
just use the guild where the warning was issued
or where the command was sent from
use ctx, it should have that info
torn sphinx
Do I have to add guild id or not?
harsh pulsar
in your database, yes
torn sphinx
Brb then
harsh pulsar
but you should just get it from the ctx,the user should not have to specify
harsh pulsar
you tell me
im not going to spoon feed you answers, you have all the knowledge and tools you need to do tihs
torn sphinx
Less take warnings then
So wait
The query thingy
It's uhhhh
await db.execute("SELECT user_id FROM warn_logs")
?
@harsh pulsar Quick thing
I can't understand how I am supposed to specify what guild it is and what user it is.
So it only shows their warnings
And not everyone's.
harsh pulsar
await db.execute("SELECT user_id FROM warn_logs WHERE guild_id = ?", (guild.id,))
i dont know if aiosqlite uses ? or $1 or %s for placeholders
look it up in their docs
torn sphinx
It uses ?
Alright so.
Now I have what guild it is
Now I have to pinpoint the id to a specific user
@harsh pulsar
I got this when I ran the cmd
harsh pulsar
what do you think is the cause of that
instead of asking me
stop and think about it
what do you think you are seeing here?
torn sphinx
Uhhhhh.
No idea fr.
According to the code.
It should print my user id.
But that's not a user id.
So idk, I literally have no idea what that is.
harsh pulsar
should it?
torn sphinx
warning_data = db.execute("SELECT user_id FROM warn_logs WHERE guild_id = ?", (ctx.guild.id,))
I think so
harsh pulsar
i'm not a hardass like this all the time, but from what i can tell you have more than enough programming skill
now you need to learn a new skill, that is reading docs and problem solving
so i don't want to just give you the answers
yes. there should be a "homepage" or "documentation" link in there
harsh pulsar
sometimes the documentation is just bad
harsh pulsar
if you dont see what you need in the docs, then ask for help
but usually the next step is, if its not in the docs, read the source code
torn sphinx
Got it.
Source code?
How did you learn so much? Is it the docs that learns you the most?
torn sphinx
Bruh, these docs are one of the bad ones
harsh pulsar
"API Reference" on the left
torn sphinx
Ait.
torn sphinx
Roger that.
I'll just close everything
Put on some music
And read
See ya, thanks for all the help. You're the man. 👊
harsh pulsar
good luck, you can definitely do it
dont be afraid to ask for clarification if something in the docs is truly baffling
like a term that you can't find a definition for
etc.
floral thistle
Having some serious problems importing a module for some reason
I have a django project. Imagine a structure like this
Database
I-models
I---loads.py
I---of.py
|---files.py
I-utils
I---importantFile.py
I---someothershit.py
I am trying to import stuff from models
Current way that it is importing is like this
from Database.models import CanonicalModel, CanonicalDataPoint, Device, \
Device2, Manufacturer, Product, Profile, Unit
This is working for all of my colleagues, but not for me. For some weird reason
Instead, this gives me this error
ModuleNotFoundError: No module named 'omnio_db'
check = conn.execute("SELECT userid, guildid FROM users WHERE EXISTS (SELECT guildid, userid FROM users WHERE guildid=$1 AND userid=$2)", (ctx.author.id, ctx.guild.id,))
res = check.fetchone()
print(res)
if res == None:
print("I got here")
try:
with conn:
conn.execute("INSERT OR IGNORE INTO users (guildid, userid) VALUES ($1, $2)", (ctx.guild.id, ctx.author.id,))
conn.commit()
except Exception as error:
print(error)
else:
print("Nope it exists")```
It returns None even though the record exists. Can someone please help me understand this issue.I'm not sure why your first query is doubled like that
torn sphinx
what do you mean?
rich trout
Why use EXISTS?
torn sphinx
to return a value?
rich trout
You can just use the (SELECT..) part to do that
torn sphinx
SELECT guildid, userid FROM users WHERE guildid=$1 AND userid=$2```so this?yes
torn sphinx
ok
check = conn.execute("SELECT guildid, userid FROM users WHERE guildid=$1 AND userid=$2", (ctx.author.id, ctx.guild.id,)).fetchone()
print(check)
if check:
print("I got here")
try:
with conn:
conn.execute("INSERT OR IGNORE INTO users (guildid, userid) VALUES ($1, $2)", (ctx.guild.id, ctx.author.id,))
conn.commit()
except Exception as error:
print(error)
else:
print("Nope it exists")```
here is the updated versionI cleared all the rows in table users
and it wont create a new one
(I am new to databsases)
rich trout
Right
you're probably going to want to "if not check"
because right now, it's checking if the rows exists, then trying to insert if it does
rather than what your "nope it exists" message seems to imply
rich trout
Hm.. have you tried marking userid and guildid as unique?
torn sphinx
but when I do that I cant have the userid appear in another record
as well as the guild id
rich trout
Oh right, sorry. unique together
torn sphinx
yeah 😦
rich trout
Then you shouldn't be able to have dupes
unless it's throwing an error and thats why you say you're getting them?
torn sphinx
how do I do that
rich trout
What database are you using?
rich trout
unique(column1, column2) in the table definition
EG:
CREATE TABLE testing (
name TEXT,
id1 INTEGER PRIMARY KEY,
id2 INTEGER,
unique(id1, id2)
);
sqlite3 uses ? as placeholders, not $1
im surprised you arent getting syntax errors from sqlite
harsh pulsar
maybe python 3.8 allows $1 as well?
torn sphinx
CREATE TABLE IF NOT EXISTS users (userid INTEGER NOT NULL, team TEXT DEFAULT 'NONE',guildid INTEGER NOT NULL, isteamleader BOOLEAN DEFAULT false) UNIQUE (userid, guildid)```So like this?dunno
harsh pulsar
why not make that primary key
harsh pulsar
CREATE TABLE IF NOT EXISTS users (
userid INTEGER,
guildid INTEGER,
team TEXT DEFAULT 'NONE',
isteamleader BOOLEAN DEFAULT false,
PRIMARY KEY (userid, guildid)
)
like this
torn sphinx
same as Unique key
harsh pulsar
it allows you to have duplicate user id's
just not duplicate userid,guildid pairs
or do you want to allow duplicate pairs too?
harsh pulsar
in sqlite3 they're "almost" the same yes
torn sphinx
ok nvm I need to use PRIMARY KEY haha
harsh pulsar
so why don't you try it with either PRIMARY KEY (userid, guildid) or UNIQUE (userid, guildid). i prefer PRIMARY KEY because in this case it is semantically the primary key
in which case you can skip the entire if-check-else thing
and your entire code becomes
with conn:
conn.execute("INSERT OR IGNORE INTO users (guildid, userid) VALUES ($1, $2)", (ctx.guild.id, ctx.author.id,))
also if you use with conn: you don't need to also write conn.commit()
harsh pulsar
up to you.. it's more "wrong" than "unnecessary"
torn sphinx
i know
harsh pulsar
if you use both you are committing twice
rich trout
Ah drat, that may have been the big issue
harsh pulsar
maybe sqlite3 is smart enough to optimize that away