alright
#databases
torn sphinx
torn sphinx
May I have a bit of help.
@client.command(pass_context=True)
async def leaderboard(ctx):
res1 = c.execute("SELECT Coins FROM Users ORDER BY Coins DESC;")
res1 = res1.fetchone()
embed = discord.Embed(title="Most coins", description="The top users with the most Coins", color=0xfffafa)
res2 = c.execute("SELECT UserID FROM Users ORDER BY Coins DESC;")
res2 = res2.fetchone()
for counter in range(0, 1):
for member in ctx.message.server.members:
counter_id = str(res2[counter])
if counter_id == member.id:
print(str(res1[counter]))
embed.add_field(name=member.display_name, value=str(res1[counter]), inline=False)
break
await client.say(embed=embed)
When I put for counter in range(0, 2): it gives me a error..
wispy fable
You should post your error so that we know what's wrong
torn sphinx
ok
`
Ignoring exception in command leaderboard
Traceback (most recent call last):
File "C:\Users\BODZIO\AppData\Local\Programs\Python\Python36-32\lib\site-packages\discord\ext\commands\core.py", line 50, in wrapped
ret = yield from coro(*args, **kwargs)
File "C:\Users\BODZIO\Desktop\Discord Bot\DiscordBot.py", line 195, in leaderboard
counter_id = str(res2[counter])
IndexError: tuple index out of range
The above exception was the direct cause of the following exception:
`
I gtg
for a bit
torn sphinx
ok..
hearty iron
Hey, what is True in json?
"true" or true
pseudo compass
second
hearty iron
Ohhk thanks @pseudo compass
torn sphinx
Anyone that can help?
dull scarab
you're selecting 1 column so your record, and only requesting 1 row. so you would naturally only have 1 value to index.
Range is non inclusive on on the end number, so when you do for x in range(0, 1) it only iterates once, with the value 0
When you do range(0, 2) it iterates twice, with the values 0 and 1, but since you only queried one column in your query, there is only 1 value in your res2
@torn sphinx
No idea which database you're using, and lib. But you can often iterate over every entry with a for loop
torn sphinx
SQL @dull scarab
dull scarab
Alright, well you should be able to iterate over a record/row, if my memory is with me
dull scarab
When you do for x in range(0, 1) you're doing this; (let's see if it works here)
!eval for x in range(1): print(x)
delicate fieldBOT
@dull scarab Your eval job has completed.
0
^ - Only runs once, with the first value
!eval #py mylist = [0] # your current row for x in range(2): print(mylist[x]) #
delicate fieldBOT
@dull scarab Your eval job has completed.
001 | 0
002 | list index out of range
slate bone
Guys how to make a column that can hold only two choices
I mean like it has to be either yes or no ?
pure cypress
if your db supports a bit or boolean type, use that
otherwise you can use an integer type with a check constraint for 0 to 1
pure cypress
you can look at the documentation
but I can save you time
I know it doesnt support a boolean type
slate bone
Thanks lol
pure cypress
so you have to go with the check constraint
pure cypress
plus 0 is falsy and 1 is truthy
I think others could figure it out. It's not that far fetched to use 0 or 1 for booleans
even the sqlite docs say that's how it should be done if you need booleans
slate bone
I didn't read the docs yet just watched Corey video sry
Should've searched more before asking
Sorry for asking too many
But is it possible to make a cooldown in the database itself ? I mean add a timer !?
pure cypress
can you elaborate?
slate bone
I want to make a cooldown database
So I want to add a timer to every single name
pure cypress
cooldown for what?
slate bone
Cooldown to set the name to (true or false) Like the previous question
Like it goes true for two days then automatically sets to false
pure cypress
not in pure sql no
slate bone
With SQLite3
pure cypress
you'd need to write a program like in python
torn sphinx
@container.command(pass_context=True, aliases=['открыть'])
async def open(self, ctx):
c.execute("SELECT * FROM servers WHERE id=:id", {'id': ctx.message.server.id})
language = c.fetchone()
server_id = ctx.message.server.id
status = language["russian"]
if status == "on":
reply = "Вы нашли {} предмет: {}"
reply1 = "| {} вы открыли всего {} контейнеров."
item_list = item_listru
colors = colorsru
else:
reply = "You found {} item: {}"
reply1 = "| {} you have opened {} containers in total."
item_list = item_listen
colors = colorsen ## Doulevei mono me ta 2 teleftea lines
probabilities = [0.40,0.27,0.17,0.11,0.04,0.01]
rarity = numpy.random.choice(list(item_list.keys()), p=probabilities)
item = random.choice(list(item_list[rarity].keys()))
c.execute("SELECT * FROM containers WHERE id=:id", {'id': ctx.message.author.id})
data = c.fetchone()
if data is None:
with conn:
c.execute("INSERT INTO containers VALUES (:id, :name, :amount, :common, :uncommon, :rare, :epic, :legendary, :exotic)", {'id': ctx.message.author.id, 'name': ctx.message.author.display_name, 'amount': 0, 'common': 0, 'uncommon': 0, 'rare': 0, 'epic': 0, 'legendary': 0, 'exotic': 0})
conn.commit()
c.execute("SELECT * FROM containers WHERE id=:id", {'id': ctx.message.author.id})
data = c.fetchone()
common_item = data["common"]```
This locks the databaseits because I am using the cursor 2 times I guess, but how can I prevent it from locking it
I want to check for data in "servers" and "containers" tables at the same time
ionic pecan
I sorta feel like the „with conn“ is messing this up because it opens a new cursor, doesn‘t it?
torn sphinx
I though that everytime I want to insert something I have to use that
vestal apex
shouldn't you be using an asyncio based database library here? this doesn't look like it's designed to play nice with async
torn sphinx
@gusty spindle I am storing xp on each message and at the same time storing user data in an other table
gusty spindle
could you show how you're doing that?
torn sphinx
Sec
This is how I store xp on message https://hastebin.com/gojubugagu.py
The command works fine without these lines though
I've been runing the bot for months and it didn't have any problems while storying xp. So I dont think the problem is there. As I said it locks the database with the lines on the second link.
gusty spindle
hmm
gusty spindle
as gdude said, it looks like a problem with having multiple connections to your database at the same time.
close all running instances of your bot and try again, perhaps.
torn sphinx
Theres only one instance
How can I handle multiple connections?
Or doing the first one then the second one
not at the same time
vestal apex
(this still looks a situation highly suitable for aiosqlite)
gusty spindle
torn sphinx
Can you help me with that? I mean how to set it up
Can you make an example with tihs? Then I will understand
@container.command(pass_context=True)
async def stats(self, ctx, user: discord.Member = None):
print("1")
c.execute("SELECT * FROM servers WHERE id=:id", {'id': ctx.message.server.id})
language = c.fetchone()
status = language["russian"]```async with aiosqlite.connect('database.db') as c:
data = await c.execute("SELECT * FROM servers WHERE id=:id", {'id': ctx.message.server.id})
language = data.fetchone()
server_id = ctx.message.server.id
status = language["russian"]```
Did I do it right?i dunno, did you? you're the one with the code, why don't you test it out and tell us if it works? ^-^
torn sphinx
ionic pecan
<coroutine object bla bla bla at 0xDEADBEEF> means you need to await it
torn sphinx
I do data = await db.execute("SELECT * FROM servers WHERE id=:id", {'id': ctx.message.server.id})
ionic pecan
c.execute doesn't return much meaningful
no, that's the execute call
you wanna await the fetchone
ionic pecan
that error's fairly straightforward and you should sorta be familiar with it if you're writing a discord bot
c.fetchone returns a tuple
you can't access a tuple by key notation, only index notation
torn sphinx
Well I am using the same method in an other command and it works like that
Shouldnt it be same with taht one?
pseudo compass
does that actually work tho
torn sphinx
Yup
ionic pecan
not sure how any of that is supposed to work
c.fetchone returns a coro
probably same for c.execute
viral crag
!ban @torn sphinx Spammer
delicate fieldBOT
:ok_hand: permanently banned @rocky aspen (Spammer).
torn sphinx
@ionic pecan
I found that I need
def dict_factory(cursor, row):
d = {}
for idx, col in enumerate(cursor.description):
d[col[0]] = row[idx]
return d``` How to use itI don't understand why accessing by key is so important to you
You have three columns, just accesss whichever you care about
torn sphinx
pls man
sturdy spindle
hi - is this the right place to ask about pandas talking to json, or should i try DataScience?
pseudo compass
Considering JSON is not a database, yea #data-science-and-ml would be your best bet
spiral burrow
pandas also isn't a database 😃
azure heron
im trying to figure out a SQL query that is well beyond my abilities .. posted about it here: https://stackoverflow.com/questions/51975282/given-players-participating-in-games-rank-player-combination-based-on-total-sco
broken linden
If anyone is using redis, apparently they changed their licensing stuff for several modules adding the so called Commons Clause https://redislabs.com/community/licenses/ in order to limit commercial sale of that software
dusky owl
Which database is the best for storing a large amount of data -> 20-30k entries and then their neighbors. So for instance I may have object A that connects to B,C, and D. So I could draw out like A connects to B C and D then B connects to a couple other nodes and so forth
ionic pecan
"connects" as in graphs?
dusky owl
Yeah
Basically I’m gonna be taking all the entries and graphing their relationships between one another
keen cedar
So, I'm using asyncpg to connect to the PostreSQL server on heroku, and I'm getting the no pg_hba.conf entry for host blablahblah, SSL off error message. I'm guessing I need to enable the SSL mode, so I pass True for the ssl argument and now I'm getting this error
File "D:\Python37\lib\asyncio\sslproto.py", line 117, in do
_handshake
self._sslobj = self._context.wrap_bio(
AttributeError: 'str' object has no attribute 'wrap_bio'
silver pawn
sqldb = mysql.connector.connect(
host="localhost",
user="root",
passwd="****************",
database="antifortnite"
)
cursor = sqldb.cursor()
cursor.execute("SELECT id FROM hasplayed WHERE id="+ str(userid) + ";")
if cursor.rowcount: InTable=True; print("Already in Database"); return```
gives me :
```shell
2018-08-23T20:39:05.029087Z 3 [Note] Access denied for user 'root'@'localhost' (using password: NO)```can you connect to the database from command line?
oddly enough I was just helping someone with a similar issue last night in PHP
he just reinstalled mysql and it fixed itself
pseudo compass
either passwd is not the right kwarg, or your database permission aren't configured to allow logins that way
silver pawn
@pure cypress we can connect to the database in command line
pure cypress
I thing AnonymousDapper was right about passwd being wrong
according to this it's password https://dev.mysql.com/doc/connector-python/en/connector-python-example-connecting.html
pseudo compass
looks like it
(it kinda also says you aren't using a password, so i figured it didn't detect it)
glass bough
When I run this: python info = sql.execute("SELECT turn, other_turn FROM sessions WHERE IP = ?", (request.remote_addr,)) print('info:', list(info)) print(list(info))
why do I get this?python info: [('player1', 'player2')] []
in the output
vestal apex
info is an exhaustable iterator. the first time you feed it into list, it goes through all its items and they're turned into elements. the second time you feed it into list, it has nothing left to give, so you end up with an empty one
vestal apex
yup
glass bough
Thanks, I might have known that at some point, but it hasn't come across in a while
versed coyote
@dusky owl Neo4j definitely
It can store a hell of a lot more than 20-30k without any issues
And it's pretty easy to use
Also has a really neat query syntax
dusky owl
👌🏾
versed coyote
Also has a pretty good Python library, and a excellent REST API
If you ever need help let me know, I used it for a while ~ a year ago
silver pawn
What GDPR rules does data collection from bots affect. Like is storing discord ID, displayname, Etc ok without asking for permission since it’s public info?
Didn’t know where to post that. So if it’s in wrong place tell me and I’ll move it
vestal apex
as far as the Discord Terms of Service go (I'm assuming we're talking Discord), which may or may not line up fully with GDPR requirements, they do stipulate that you get explicit end user consent prior to storing such data 2.4 End User Data. “End User Data” means all data associated with the content within the functionality enabled by the API or SDK, including but not limited to message content, message metadata, voice data and voice metadata. You acknowledge and agree that Discord and its end users, as applicable, retain all worldwide right, title and interest in and to the End User Data. If you have access to End User Data through the API or the SDK, you shall ensure that your Applications do not collect, use and/or disclose End User Data except (a) as necessary to exercise your rights under this Agreement, (b) in accordance with Discord’s Privacy Policy. If you have access to End User Data through the SDK, you additionally agree to get express permission from the End Users.
that's part of the reason we have people accept our privacy policy prior to providing them access to the rest of the server
versed coyote
@viral crag Is our resident expert on this issue, especially since he handled it for this server IIRC
vestal apex
( the entirety of the Developers' Terms of Service are available here https://discordapp.com/developers/docs/legal )
silver pawn
What checkpoint?
vestal apex
yup
silver pawn
?
vestal apex
oh I thought there was an implied comma. did someone say checkpoint?
that is the name of the channel where we ask for agreement
low lion
Here's where I meant to post it: Snowflakes are Discord's property, so they're kosher for storage.
silver pawn
Ahh. So do you need specific user agreement or can you just Tell them that the data is being collected
Can we just say that we are and they can leave if they want, or do I need to get them to specifically agree like the checkpoint
pure cypress
Maybe something like "By being on this server you accept these terms" is enough? I've seen that kind of stuff with other services
pure cypress
Idk with GDPR if you need explicit agreement
always do your own research when it comes to stuff like this
vestal apex
Without limiting the foregoing, you shall ensure that each End User expressly agree to the collection, use and disclosure of all information made available by such End User in accordance with the terms of this Agreement and the TOS, which includes Discord’s Privacy Policy.```also via the Developers' ToS
silver pawn
So just as mark said, saying something like “by using this server you agree to your data being collected” isn’t enough?
Damn
pure cypress
Apparently not
silver pawn
Welp
low lion
It shouldn't be too tricky to add a checkpoint.
low lion
You can block a user from using any commands before they use a terms command, and then agree to it. (And don't collect user data from the terms command.)
pure cypress
That's not ideal
silver pawn
I mean, currently the public one didn’t have the databases linked, only the test one running on just a server with me and a friend
Yeah
vestal apex
I know a user that only stored hashes of user snowflakes and only associated limited information with them unless the user opted in explicitly. I'm no lawyer but that does seem to resolve a few issues
silver pawn
I don’t wanna close a users discord if someone doesn’t agree to allowing a bot to see and track your ID
Ah. Hashing? I mean all we collect and track is userid for now so that could work
low lion
The ids are kosher. They've specifically said somewhere that Snowflakes aren't EUD.
I'm having trouble re-sourcing the quote, but I know it's somewhere.
vestal apex
hmm, given the ToS terminology it sounds like they'd be included, but that's entirely possible
pure cypress
Curious how to approach that. I wonder if it is permissible to defer the responsibility of ensuring express agreement to the guild owners which use your bot
silver pawn
Like use a website or something before you can add the bot to ensure that the owner explicitly agrees to add such protection like a checkpoint etc
pure cypress
I would resort to asking Discord themselves about this, but they don't seem to have good communication channels
only Twitter...
vestal apex
it looks like there's some other relevant comments in there, too
Q. How explicit do we need to be about the fact that our bot logs stuff?
A. It's explicit enough if people know when they add it, e. g. in the description of the bot, or where they get the invite link from. You don't have to notify every user; adding your bot is consent.
assuming the information is up to date that's promising
but last August was prior to a number of GDPR restrictions, so I'm not sure
silver pawn
I’ll do more digging around and possibly contact the discord support about it. Don’t wanna get on wrong side of the TOS.
- it’s 5:35 so imma go sleep. Night and thanks people
vestal apex
good luck
low lion
It was shortly prior, so I believe the update in question was them making sure to be ahead of the legislation.
So you have something to work off of in the morning, @silver pawn, you can also ask the discord.py server specifically. They kinda have to work in those rules a bit. 😛
vestal apex
Discord API server might be better, even
severe bridge
i have a project where i have to come up with something to use a database for, any ideas?
most of the common ones like restaurant bookings are already taken by others, so i cant use them
low lion
Discord bot? 😛
severe bridge
and im at beginner level so nothing too complicated
discord bot maybe
how would a database help for making a discord bot though
low lion
I use it for a few things in my bot. Command statistics, static information storage, storing things for end users to recall later, etc.
severe bridge
oo
dull scarab
Anything that requires storing of data tbh
Could make a text based rpg and store player stats, inventory etc
azure heron
in the context of a many-to-many relationship, how do i find all Classes that contain exactly Student1, S2, and S3, but exclude any others? (Such as a class that includes S1, S2, S3, S4)
pure cypress
in a many to many you have a junction table
so do a join on the junction table for all records where the student id matches
azure heron
wouldnt that still pick up classes with additional members? here's the example db: https://stackoverflow.com/questions/52010522/select-record-that-has-specific-records-related-but-no-additional-records-relat
glossy ermine
@torn sphinx Been away, i still don't understand what you're asking
torn sphinx
@glossy ermine dm me
wispy fable
@torn sphinx @glossy ermine
We do not DM people on this server for help related topics
!t no-dm
delicate fieldBOT
torn sphinx
@wispy fable ok chill
upbeat rivet
Can Somebody please help me on this :/
amount = int(splitted[1])
item = splitted[0]
itemwert, = c.execute("SELECT wertAlt From stock WHERE LOWER(Item) = LOWER(:Item)", {'Item': item}).fetchone()
math = int(amount * itemwert)
if math > cash:
e = discord.Embed(title='Error', colour=discord.Colour(0xfd0000),
description='Hoppla.. Du hast keine `{0:,.0f}` € in Bar.'.format(math))
return await ctx.send(embed=e, delete_after=5.0)
else:
print(item)
c.execute("""UPDATE economy SET Cash = Cash - (:Cash), (:Item) = (:Item) + (:Amount) WHERE ID = (:ID)""", {'Cash': math, 'Amount': amount, 'Item': item, 'ID': str(ctx.author.id)})
conn.commit()```
It tells me that on (:Item) there is an syntax error..
Command is used like that: `!buy Holz 1000`
It prints `Holz` and it looks like that:Can u ping me if you know what is wrong.. I guess I can't use (:Item) as a placeholder. But how else can I do it than without making for every Item an if statement?
ionic pecan
why do you need the parantheses?
ionic pecan
because you‘re passing your ID as a string
torn sphinx
Fixed it but still gives none
ionic pecan
show ze code
torn sphinx
def select_all_tasks(conn, id):
c = conn.cursor()
c.execute("SELECT * FROM users WHERE id=:id", {'id': id})
data = c.fetchone()
print(data) #Print data here
return data
@commands.command(pass_context = True)
async def level(self, ctx, user: discord.Member = None):
id = ctx.message.author.id
select_all_tasks(conn, id)
print(data) #but prints None here.```Data not defined actually
azure snow
broken linden
!t ask
delicate fieldBOT
slate bone
Guys I want to make clans database.....
Is it ok to make for each clan a table ? btw I'm using SQLite3
I mean will that be ok? or will affect the DB
wispy fable
@slate bone sounds fine to me
Why not make each clan a row on a single table though?
what will a "clan" contain?
ionic pecan
You really don‘t want to be programmatically creating tables
slate bone
Thanks I found another way
Creating a members table and there will be a row where you can define each clan using id or name
I mean the clan of the member
calm sky
hm another question. i have this: await tipsdb.execute('CREATE TABLE IF NOT EXISTS TIPS (ENTRYID INTEGER PRIMARY KEY AUTOINCREMENT, GUILDID INT UNIQUE NOT NULL, CATEGORY TEXT NOT NULL, CREATORID INT NOT NULL, CREATED TIMESTAMP DEFAULT CURRENT_TIMESTAMP, EDITORID INT DEFAULT NULL, LASTEDITED TIMESTAMP DEFAULT NULL, TIP TEXT NOT NULL)') but if i wanna add somethign it says table TIPS has no column CREATORID ???
https://gitlab.nextcoders.eu/weStart/DisBot/blob/master/cogs/Tips.py#L20 line 20 and line 54
CREATORID INT NOT NULL <-- is there
but insert into says no such column
iv eoduble checked spelling mistakes but cant find any?
also creating the db gives no syntax error
i mean the table
vestal apex
does the table already exist with a different schema?
calm sky
i dont think so i have deleted th e database
and recreated it
sqlite3.OperationalError: table TIPS has no column named CREATORID
thi sis the error
lemem check
ill delete again
oh im dumb, it wanted to refactor and i just ignored it - so it didnt delete the file
thanks
calm sky
question: how can i db.commit() and get back the rows affected with the code like: ```python
async with aiosqlite.connect('tips.db') as db:
for eid in args:
await db.execute('DELETE FROM TIPS WHERE ENTRYID = ?', (eid,))
c = await db.commit()
await ctx.send(f"{ctx.command.name}: Deleted {c.rowcount} tips.")
return
ok so, is there any way i can db.execute('INSERT ...) and get the entryid in return?
dusky owl
is this the right download for neo4j? so i just need this and the driver? https://neo4j.com/download-center/#releases
ionic pecan
I'd install from the repos
dusky owl
windows machine
dusky owl
our *nix box is offline rn for some ML stuff
slate bone
Could anyone help why does it think of the clan_name as a column
def create_clan(name, owner, admins, role, members_count, maximum_rooms):
# Creating Clans Table
c.execute("""INSERT INTO clans(name, owner, admins, role, members_count, maximum_rooms) VALUES(
{0},
{1},
{2},
{3},
{4},
{5}
)""".format(name, owner, admins, role, members_count, maximum_rooms))
##################
clansbotconfig.create_clan(str(clan_name), ctx.message.author.id, "null", clan_role.id, len(clan_members), 4)
clan name is a string
It shows that error
Traceback (most recent call last):
File "C:\Users\User\AppData\Roaming\Python\Python36\site-packages\discord\ext\commands\core.py", line 60, in wrapped
ret = await coro(*args, **kwargs)
File "ClansBot.py", line 84, in انشاء
clansbotconfig.create_clan(str(clan_name), ctx.message.author.id, "null", clan_role.id, len(clan_members), 4)
File "C:\Users\User\Python Bots\clansbotconfig.py", line 59, in create_clan
)""".format(name, owner, admins, role, members_count, maximum_rooms))
sqlite3.OperationalError: no such column: Hello
The above exception was the direct cause of the following exception:
Traceback (most recent call last):
File "C:\Users\User\AppData\Roaming\Python\Python36\site-packages\discord\ext\commands\bot.py", line 896, in invoke
await ctx.command.invoke(ctx)
File "C:\Users\User\AppData\Roaming\Python\Python36\site-packages\discord\ext\commands\core.py", line 506, in invoke
await injected(*ctx.args, **ctx.kwargs)
File "C:\Users\User\AppData\Roaming\Python\Python36\site-packages\discord\ext\commands\core.py", line 69, in wrapped
raise CommandInvokeError(e) from e
discord.ext.commands.errors.CommandInvokeError: Command raised an exception: OperationalError: no such column: Hello```in sqlite how do i append or somehow set the timezone of CURRENT_TIMESTAMP ?
pure cypress
IIRC it uses the system clock
calm sky
hm ok thanks ;D
well on my windows laptop the time is utc, and it always calculates CET time from taht
so its 2 hours behidn me
what teh bot actually adds
as timestamp
torn sphinx
new to databases things
BUT is there a way to upload sqlite3.db information to online source? so like for example this
User logins > checks sqlite database online > checks info > return
spiral burrow
Sure, when you're opening the connection it doesn't matter if it's local or remote
torn sphinx
@spiral burrow well how could i use it in remote how do i make it go online and connect to it in code
spiral burrow
oh wait, I don't think that sqlite has remote access
spiral burrow
You'd have to use another flavor of SQL, like Postgres or MySQL
or another non-SQL db with network capabilities
I'm sure a remote sqlite connection can be done, but I don't believe it's trivial
craggy otter
I wanna start database, what should i include before i start it. Like safety hazards ect...
Database developing*
versed coyote
Like, your own database?
Instead of one of the hundreds that exist
Is it relational?
Object-based?
craggy otter
I mean i am going to buy a server or database of some sort
versed coyote
Oh ok
One of the web developers here might be able to help, but they're asleep right now
craggy otter
Because my father has company and his computer is always full.
My questions:
Should i buy NAS or use old computer?
What language i should use?
What linux os is best for database?
What about backup?
vestal apex
servers, databases, and network storage are all related but distinct concepts
which one are you asking about, exactly?
calm sky
whats the prefered way to use sqlite? aiosqlite?
dull scarab
With an async environment, yeah sure
calm sky
ok thanks ;D
craggy otter
@lofty ginkgo My friend from fingersoft said he has done that, but he said it overheats so you need watercooler
gusty spindle
🤔 no, you just need better airflow. i'm using my old computer as a NAS no problem.
gleaming frost
@calm sky if your using a non-async project.... Maybe use sqilite.
slate bone
I'm using an async project and using sqlite what's wrong with it ?
dull scarab
aiosqlite is designed for an async environment
That's the gist of it
Sqlite works, but is not optimal
And may freeze, have other stops
slate bone
Thanks I will read about it
tender badger
Hi, how can i close my database at the end of my program ?
ionic pecan
can you be a bit more specific? what do you mean with "close my database"?
tender badger
close connexions
i'm creating a discord bot, i open connexions when i run the bot but if he get an error, i want to close connexions
torn sphinx
hey
self.c.execute('SELECT id FROM votes WHERE user_id = ? AND option_id = ?', (user_id, option_id))
this throws me an error: Error binding parameter 1: probably unsupported type
tender badger
Try self.c.execute('SELECT id FROM votes WHERE user_id = {0} AND option_id = {1}'.format (user_id, option_id))
torn sphinx
sqlite docs very clearly discourage string formats
and it throws some other weird error, sqlite.OperationalError: near ")": syntax error
oh god
vestal apex
what's user_id?
brittle spear
Hey ! Someone have python postgresql doc pls ?
wispy fable
for?
tender badger
So nobody have a solution for my problem ?
dull scarab
Wrap run in a try except and close th db on a keyboard interrupt exception?
patent glen
sqlite doesn't care much about column types ftr. And "parameter 1" means the second parameter. Double check the actual type of option_id @torn sphinx
tender badger
When i want to create a nex server with PgAdmin, i have an error Unable to connect to server:
slate bone
Guys someone told me not to use .format while inserting in SQLite3 to avoid sql injection
What is the right way to insert in SQLite3 ?
lofty ginkgo
@slate bone have no idea with SQLite, but usually use prepared statement and sanitize all the input
gusty spindle
taken from the python docs:
Put ? as a placeholder wherever you want to use a value, and then provide a tuple of values as the second argument to the cursor’s execute() method. (Other database modules may use a different placeholder, such as %s or :1.) For example:
# Never do this -- insecure!
symbol = 'RHAT'
c.execute("SELECT * FROM stocks WHERE symbol = '%s'" % symbol)
# Do this instead
t = ('RHAT',)
c.execute('SELECT * FROM stocks WHERE symbol=?', t)
print(c.fetchone())
# Larger example that inserts many records at a time
purchases = [('2006-03-28', 'BUY', 'IBM', 1000, 45.00),
('2006-04-05', 'BUY', 'MSFT', 1000, 72.00),
('2006-04-06', 'SELL', 'IBM', 500, 53.00),
]
c.executemany('INSERT INTO stocks VALUES (?,?,?,?,?)', purchases)```
you can read more here: https://docs.python.org/3/library/sqlite3.html
primal olive
Yo, could someone help me with identifying different types of normalisation
It's confusing the crap out of me
There is a table called "Mentors"
Inside it has "UserID, MentorID and MentorName"
Each row / column only has one value in it (so it is at least 1NF)
However, I am unsure if it is only 1NF or if it is 2NF
Please @ me if you can help, thanks
torn sphinx
Sometimes I get "Database is locked" How can I prevent that? I am doinging 2 operations at the same time. Updating suer data on message ( adding Xp) and updating his stats when opening a crate. What can I do
gusty spindle
what sort of database are you using?
torn sphinx
Sqlite3
gusty spindle
yeah, you can't have multiple connections to your database open at the same time. not with sqlite, anyways.
torn sphinx
On message
c.execute("SELECT * FROM users WHERE id=:id", {'id': message.author.id})
data = c.fetchone()
if data is None:
return
xp = data['xp']
premium = data['premium']
if premium =="Yes":
c.execute("""UPDATE users SET xp = :xp WHERE id = :id""",
{'xp': xp + 2, 'id': message.author.id})
conn.commit()
else:
c.execute("""UPDATE users SET xp = :xp WHERE id = :id""",
{'xp': xp + 1, 'id': message.author.id})
conn.commit()```Command
c.execute("SELECT * FROM containers WHERE id=:id", {'id': ctx.message.author.id})
data = c.fetchone()
amount = data["amount"]
c.execute("""UPDATE containers SET amount = :amount WHERE id = :id""",
{'amount': amount + 1, 'id': ctx.message.author.id})```are you using the sqlite module, or are you using aiosqlite?
gusty spindle
you should look into using aiosqlite instead, as it doesn't block async stuff and is therefore better for discord bots.
how do you mean?
torn sphinx
I mean when ever I get "Database is locked" it gives me the line829 where I save xp on message
so I gues the problem is there
gusty spindle
that's just cause you can't have multiple connections open to your database at the same time, as i've explained. that error is inevitably going to happen if you use a non-async database module within an async project.
torn sphinx
So I replace all with aiosqilite
gusty spindle
yup
torn sphinx
Alright
gusty spindle
that's the ideal solution
another way would somehow to use a queue, but that's really not optimal in this case
just start using aiosqlite in async code, it'll also stop the bot's event loop from getting blocked by your database interactions
torn sphinx
Cool, thanks a lot
gusty spindle
👌 if you need any help migrating your current code, feel free to ask here again.
torn sphinx
So
c.execute("SELECT * FROM users WHERE id=:id", {'id': id})
data = c.fetchone()```Will be
async with aiosqlite.connect('database.db') as db:
cursor = await db.execute("SELECT * FROM users WHERE id=:id", {'id': id})
data = await cursor.fetchone()```looks right to me
torn sphinx
Alright
But I need to add
async with aiosqlite.connect('database.db') as db:``` on each command? With sqlite3 I did
```py
conn = sqlite3.connect('database.db')``` Outsite of the commandsSo I guess py conn = async with aiosqlite.connect('database.db') as db:
torn sphinx
So I can just do
conn = async aiosqlite.connect('database.db')```i think it would be conn = await ...
torn sphinx
pure cypress
It doesn't need to be awaited
torn sphinx
okey
pure cypress
I am not sure if you can even use it like that
it seems to be designed as an async context manager
which goes hand in hand with async with
torn sphinx
I will just create a fuction
Something like thispy def select_test(id): async with aiosqlite.connect('database.db') as db: cursor = await db.execute('SELECT * FROM users WHERE id=:id', {'id': id}) row = await cursor.fetchone() rows = await cursor.fetchall() await cursor.close()
pure cypress
you need to indent the code so it's inside the async with
torn sphinx
Yeah just noticed
Hmm
def select_test(id):
async with aiosqlite.connect('database.db') as db:
cursor = await db.execute('SELECT * FROM users WHERE id=:id', {'id': id})
data = await cursor.fetchall()
print(data)
await cursor.close()``` looks fineyou can only use async with inside a function defined as async def
torn sphinx
pure cypress
Have you tried to solve these errors yourself?
pure cypress
lol np
hard swan
i was looking at the problem for solid 5 minutes
slate bone
is it ok to do that? I can't really find any good explination for aiomysql and only thought of that
I know bad idea but if working I'm going for it
import asyncio
import aiomysql
loop = asyncio.get_event_loop()
async def mysql(loop):
pool = await aiomysql.create_pool(host='127.0.0.1', port=3306,
user='root', password='',
db='mysql', loop=loop)
async with pool.acquire() as conn:
async with conn.cursor() as cur:
# ALL OF MY DB WORK HERE
pool.close()
await pool.wait_closed()
loop = asyncio.get_event_loop()
loop.run_until_complete(mysql(loop))```@Me please
ionic pecan
well, did you try it?
dull scarab
But he knows its a bad idea
ionic pecan
I don‘t see how
crisp robin
PyMongo:
How do I check if a document exists?
If it does, don't do anything, if it doesn't add the user onto the database.
torn sphinx
You can check with an if statement.
data = c.fetchone()
if data is None:
#do stuff
else:
return``` Thats a python example@crisp robin
Ignoring exception in message
Traceback (most recent call last):
File "C:\Users\ALEX\AppData\Local\Programs\Python\Python36-32\lib\site-package
s\discord\ext\commands\bot.py", line 252, in _run_extra
yield from coro(*args, **kwargs)
File "cogs\economy.py", line 786, in on_message
cursor = await db.execute("UPDATE users SET xp = :xp WHERE id = :id", {'xp':
xp + 2, 'id': message.author.id})
File "C:\Users\ALEX\AppData\Local\Programs\Python\Python36-32\lib\site-package
s\aiosqlite\core.py", line 200, in execute
cursor = await self._execute(self._conn.execute, sql, parameters)
AttributeError: 'NoneType' object has no attribute 'execute'```Why I get this error?
Wors fine with SELECT
but not with UPDATE
dull scarab
db is None?
dull scarab
May i see the entirety of the command?
torn sphinx
Sure
I was using sqlite but they told me to use aiosqlite its better with discord bots
dull scarab
It is indeed
dull scarab
db is defined here py async with aiosqlite.connect('database.db') as db: cursor = await db.execute('SELECT * FROM users WHERE id=:id', {'id': id})
dull scarab
But it's scope does not continue
torn sphinx
XD
dull scarab
to your next db.execute
dull scarab
👍🏾
torn sphinx
I though I didnt have it it in the other command
but I have it there too
So whats wrong
dull scarab
Also, on_message is not a command per se
What do you mean what's wrong? I just told you?
torn sphinx
I dont understand it
dull scarab
async with aiosqlite.connect('database.db') as db:``` connect here is a context manager that handles the connection and references it with `db` for all the code **within** it's scope.So when it exits that scope, the context manager handles the clean up for connect
torn sphinx
So I dont have to have this in each command?
dull scarab
so after that block, db is not available
You gotta get the connection if you want to do a query
dull scarab
async with aiosqlite.connect('database.db') as db:
cursor = await db.execute('SELECT * FROM users WHERE id=:id', {'id': id})
data = await cursor.fetchone()
data = data
...
else:
...
if premium:
async with aiosqlite.connect('database.db') as db:
...```Yeah i just did this
Idont get error
but it doesnt update my xp by on
maybe because I close the database without saving?
I have this line
await cursor.close()``` after updateThe context manager should handle that
Also, believe sqlite supports value increments
torn sphinx
It doesnt update xp Idk
dull scarab
async def on_message(msg):
async with aiosqlite.connect("database.db") as db:
c = await db.execute("select premium from users where id=:id", {"id": msg.author.id})
premium = await c.fetchone()[0]
if premium == "Yes":
amt = 2
else:
amt = 1
async with aiosqlite.connect("database.db") as db:
db.execute("UPDATE users SET xp = xp + :amt WHERE id=:id", {"amt": amt, "id": msg.author.id})```or so
sick swallow
in some sql libraries you have to call commit() or open the connection with autocommit==True, else nothing gets saved
torn sphinx
I use aiosqlite
dull scarab
Oh right yeah
torn sphinx
Same with aioslqite? I can just do commit()?
dull scarab
youd have t o close a cursor, and commit to db if you're executing a statement
sick swallow
i think so, yes
torn sphinx
Let me give it a try
dull scarab
async with aiosqlite.connect(db_path) as db:
async with db.execute(query, *args) as cursor:
row = await cursor.fetchone()
await cursor.close()```
```py
async with aiosqlite.connect(db_path) as db:
await db.execute(query, *args)
await db.commit()```i believe is the way to go for either-or
fetch_anything -> cursor.close
execute statement -> db.commit
torn sphinx
Thanks a lot guys. Works perfect
faint blade
I kinda have a problem with my database
I have this function:py async def get_coins(self, user_id): async with pool.acquire() as conn: await self.insert_user(user_id) coins = await conn.fetchval("SELECT Coins FROM Users WHERE UserID=$1", user_id) return int(coins)
But i get a error when i use it as a int:```py
Ignoring exception in command pay
Traceback (most recent call last):
File "/home/gabriel/.local/lib/python3.6/site-packages/discord/ext/commands/core.py", line 50, in wrapped
ret = yield from coro(*args, **kwargs)
File "/home/gabriel/Projects/Python/Gamble.IO/transactions.py", line 19, in pay
elif Coins.get_coins(ctx.message.author.id) < amount:
TypeError: '<' not supported between instances of 'coroutine' and 'int'
The above exception was the direct cause of the following exception:
Traceback (most recent call last):
File "/home/gabriel/.local/lib/python3.6/site-packages/discord/ext/commands/bot.py", line 846, in process_commands
yield from command.invoke(ctx)
File "/home/gabriel/.local/lib/python3.6/site-packages/discord/ext/commands/core.py", line 374, in invoke
yield from injected(*ctx.args, **ctx.kwargs)
File "/home/gabriel/.local/lib/python3.6/site-packages/discord/ext/commands/core.py", line 54, in wrapped
raise CommandInvokeError(e) from e
discord.ext.commands.errors.CommandInvokeError: Command raised an exception: TypeError: '<' not supported between instances of 'coroutine' and 'int'
I dont know how to fix this as i dont really understand the error
ionic pecan
Coins.get_coins is a coroutine
faint blade
Yes
slate bone
Guys in aiomysql if I want to put a function inside a connection pool should I make it a async func ?
Sorry I just learned aiomysql and can't find a good explination
import asyncio
import aiomysql
loop = asyncio.get_event_loop()
async def mysql(loop):
pool = await aiomysql.create_pool(host='127.0.0.1', port=3306,
user='root', password='',
db='mysql', loop=loop)
async with pool.acquire() as conn:
async with conn.cursor() as cur:
async def check_name(name):
fetch = cur.execute("SELECT name FROM clans WHERE name = '{0}'".format(name))
if fetch is None:
return True # Name is Availabe
else:
return False # Name isn't Availabe
pool.close()
await pool.wait_closed()
loop = asyncio.get_event_loop()
loop.run_until_complete(mysql(loop))```and when I import that file how can I import that check_name function ?
should I do like filename.mysql.check_name ?
slate bone
Then what is the right way to do it ?
I want to call check_name in another file
This is in config file by the way
gleaming frost
Don't make it nested?
faint blade
Also, it fixed the error TY Volcyy
slate bone
How to do that can you refer it in the docs please
The docs are so bad and I'm having a hard time dealing with it
slate bone
Ok sorry, Last question if I did that do I have to connect it in each func ?
import asyncio
import aiomysql
loop = asyncio.get_event_loop()
@asyncio.coroutine
def test_example():
conn = yield from aiomysql.connect(host='127.0.0.1', port=3306,
user='root', password='', db='mysql',
loop=loop)
cur = yield from conn.cursor()
yield from cur.execute("SELECT Host,User FROM user")
print(cur.description)
r = yield from cur.fetchall()
print(r)
yield from cur.close()
conn.close()```Have been programming for many hours so can't concetrate anymore just want to figure that and go to sleep
gleaming frost
import asyncio
import aiomysql
loop = asyncio.get_event_loop()
async def mysql(loop):
pool = await aiomysql.create_pool(host='127.0.0.1', port=3306,
user='root', password='',
db='mysql', loop=loop)
async with pool.acquire() as conn:
async with conn.cursor() as cur:
await check_name(cur, "hello")
pool.close()
await pool.wait_closed()
async def check_name(cur, name):
fetch = cur.execute("SELECT name FROM clans WHERE name = '{0}'".format(name))
if fetch is None:
return True # Name is Availabe
else:
return False # Name isn't Availabe
loop = asyncio.get_event_loop()
loop.run_until_complete(mysql(loop))
Thankss man I finally got it really apprecaite your help
I didn't think of it like that
torn sphinx
Do I need to close the database and do commit() when ever I also SELECT from the database? or only when I UPDATE or INSERT?
tender hollow
Closing is always a good idea (whether or not it's strictly necessary depends on several factors)
Commit - no, as commit is to apply changes, a SELECT query doesn't change the database
torn sphinx
Alright
thanks
So I close the database after I do SELECT of after I put the data in a variable then close
Wait I give example
This
async with aiosqlite.connect('database.db') as db:
cursor = await db.execute('SELECT * FROM servers WHERE id=:id', {'id': ctx.message.server.id})
language = await cursor.fetchone()
await cursor.close()```
or
```py
async with aiosqlite.connect('database.db') as db:
cursor = await db.execute('SELECT * FROM servers WHERE id=:id', {'id': ctx.message.server.id})
await cursor.close()
language = await cursor.fetchone()```You're going to want to close after you're finished with the cursor (I'm not 100% sure of the specifics of the aiosqlite module, but regardless, if you're still using cursor you probably should close it only after)
torn sphinx
Alright
torn sphinx
@tender hollow One mor equestion. When I update/instert in the database. I close the database and then commit or I first commit then close?
torn sphinx
Oh alright
torn sphinx
.get gets aprimary key, index_lists lists secondary index according to the docs
that's the only thing I could think of
(I have no idea what a secondary ndex is)
hollow tiger
🤔
how do i add a primary key?
table.insert(object | [object1, object2, ...][, durability="hard", return_changes=False, conflict="error"]) → object
vestal apex
everything already has a primary key
hasty hinge
Is there a way to install Microsoft Visual C++ 14.0 without installing Visual Studio? I need it to install mysqldb
torn sphinx
I have a command where you open crates and then it stores them but I have a problem. On first time you do the command it doesnt work and gives this error.
returning exception You did not supply a value for binding 1.
Traceback (most recent call last):
File "C:\Users\ALEX\AppData\Local\Programs\Python\Python36-32\lib\site-package
s\aiosqlite\core.py", line 136, in run
result = fn()
sqlite3.ProgrammingError: You did not supply a value for binding 1.
Exception in command 'container open', You did not supply a value for binding 1.```
On second try it works fine, on the third time it gives again error. On fourth try it works fine. on fifth time it gives error again and it goes on. One time it works and one doesnt.What's the code?
Which line corresponds to 136
(and don't name your command for open, it probably is shadowing the build in open function)
torn sphinx
It doesnt matter I think because it was working fin with sqlite
That line has nothing to do with my code. its from python
dull scarab
I'd still name it open_ or something, and add open as it's name parameter to be safe
torn sphinx
Willl keep that in mind
torn sphinx
XD
Wait I have a possible fix
give me a sec
nope.
I think the problem is where I add up 1 number. amount + 1 it thinks that "amount" is empty so it says "You didn't not supply a value for binding 1" But it works on the second time because on the first time it did add amount on the variable thats why it works on second time
idk
then it doesnt work on third time again because its empty again. but works on fourth
complicated
dull scarab
There's quite a bit of refactoring you could do for sure.
Are you sure it's every other use, and not every occurrence of x rarity etc?
dull scarab
Like the part where all these commands use teh common key
Instead of :excotic / :legendary etc
Yeah, you use the common key in the parameter dict for all your queries
But the sql itself uses the actual rarity
torn sphinx
oh damn copy pasting
you are right
It only shows common
but when I get for example rare it doesnt show mesage
Thanks
torn sphinx
Floppy I moved the cog from my testing bot to the main bot and now it spams canno operate in a closed database. I tried to see if my xp is increasing and it doesn increase I also opened containers to see if my amount of containers increase and they do. I dont know why i get tat error
torn sphinx
Also I have 3 process named python.exe because i run 3 bots on that pc but I alos have 3 process named py.exe. I dont know if they were there before though
I am trying to use all commands 1 by one to see in which command I get that error but it will be hard since theres so mnay servers with the bot lol
torn sphinx
oh it give me the line where to problem is lol
hold on
it says on line 938 I will give you a screenshot
Its where it updates xp on message
The same issue that I had when I was using sqlite. I switched to aiosqlite for this reason. But same thing.
dull scarab
I dont think you need a cursor when updating
dull scarab
async with aiosqlite.connect(...) as db:
await db.execute(query, args)
await db.commit()```should do it
torn sphinx
So I should replace all await cursor.commit(), await cursor.close() with await db....()
But then how it saves data if tahtswrong
oh nvm
You are right
torn sphinx
so I close with db when selecting only?
dull scarab
You close the cursor
torn sphinx
oh now i understand
dull scarab
The context manager should handle the db related instances
crisp robin
Please Could I get some help: MongoDB, Discord.py
Error:
TypeError: '_asyncio.Future' object is not subscriptable```
Code:
```py
xpuser = client.db.xp.find_one({ "_id": id })
currentxp = xpuser['xp']
client.db.xp.update_one( { "_id": id }, { "$set": { "xp": currentxp + 1 } }, upsert=True )```I've tried searching it up, but didn't get any useful results.
Please help.
ionic pecan
show the full error
although I guess it happens on line 2
is client.db.xp.find_one a coroutine?
crisp robin
straight from heroku
2018-09-01T14:21:08.031515+00:00 app[worker.1]: Exception in thread Thread-21:
2018-09-01T14:21:08.031525+00:00 app[worker.1]: Traceback (most recent call last):
2018-09-01T14:21:08.031527+00:00 app[worker.1]: File "/app/.heroku/python/lib/python3.6/threading.py", line 916, in _bootstrap_inner
2018-09-01T14:21:08.031529+00:00 app[worker.1]: self.run()
2018-09-01T14:21:08.031530+00:00 app[worker.1]: File "/app/.heroku/python/lib/python3.6/threading.py", line 1182, in run
2018-09-01T14:21:08.031532+00:00 app[worker.1]: self.function(*self.args, **self.kwargs)
2018-09-01T14:21:08.031533+00:00 app[worker.1]: File "avibot.py", line 74, in update_xp
2018-09-01T14:21:08.031534+00:00 app[worker.1]: currentxp = xpuser['xp']
2018-09-01T14:21:08.031536+00:00 app[worker.1]: TypeError: '_asyncio.Future' object is not subscriptable```@ionic pecan
ionic pecan
is client.db.xp.find_one a coroutine?
crisp robin
no
crisp robin
def update_xp():
global calls
t = threading.Timer(60.0, update_xp)
t.start()
for id in typed:
if typed[id] == True:
xpuser = client.db.xp.find_one({ "_id": id })
currentxp = xpuser['xp']
client.db.xp.update_one( { "_id": id }, { "$set": { "xp": currentxp + 1 } }, upsert=True )
typed[id] = False
calls += 1
if (calls == 10):
calls = 0
typed.clear()```@ionic pecan
ionic pecan
no, I meant client.db.xp.find_one
crisp robin
sorry. what about it
ionic pecan
can you post it here?
ionic pecan
did you define it yourself or is it of the mongodb module you're using?
crisp robin
mongodb module
ionic pecan
which one are you using?
pseudo compass
if it returns a Future, its probably a coroutine
crisp robin
oke thanks
hollow tiger
File "<string>", line 3, in func
File "/usr/local/lib/python3.6/dist-packages/rethinkdb/ast.py", line 123, in run
return c._start(self, **global_optargs)
File "/usr/local/lib/python3.6/dist-packages/rethinkdb/net.py", line 621, in _start
self.check_open()
File "/usr/local/lib/python3.6/dist-packages/rethinkdb/net.py", line 595, in check_open
raise ReqlDriverError('Connection is closed.')
rethinkdb.errors.ReqlDriverError: Connection is closed.```what is happening?
i just ran py import rethinkdb as r print(r.db("appbot").table("guilds").run())
ionic pecan
do you not have to .. connect?
slate bone
Guys how can I put function inside a connection pool and call them from outside ?
I mean like make a connection pool and put inside a function called check_name() for example
then call it from another file
is that possible ?
I mean in aiomysql
dull scarab
Do you have any sample code, or something to explain your question with?
slate bone
I want to call check_name from outside I mean from another file as that file is supposed to be a config
# DB Connection Pool
async def mysql(loop):
pool = await aiomysql.create_pool(host='127.0.0.1', port=3306,
user='root', password='',
db='clans', loop=loop)
async with pool.acquire() as conn:
async with conn.cursor() as cur:
def check_name(name):
fetch = cur.execute(f"SELECT name FROM clans WHERE name = '{name}'")
if fetch is None:
return True # Name is Availabe
else:
return False # Name isn't Availabe
pool.close()
await pool.wait_closed()```Move the method out of mysql and pass pool to check?
async def check_name(pool, name):
async with pool.acquire() as conn:
async with conn.cursor() as cur:
fetch = cur.execute(...)
if ...
else ...
async def mysql(loop):
pool = await ...
await check_name(pool, "bob")```Also, avoid string formatting your queries as it's a pretty big security risk
use placeholders
I believe the placeholder is %s
craggy otter
This seems interesting, any docs to read or how i get started with databases?
dull scarab
Which one, and for what purpose?
craggy otter
Purpose is to collect data into a server
craggy otter
Yeah
dull scarab
postgresql is among the best alternatives, but something as simple as sqlite works for small scale bot
Just use the async versions of the libs
craggy otter
From postgresql.org?
dull scarab
You'll probably need to setup a postgres db for it, and use a lib like
https://magicstack.github.io/asyncpg/current/usage.html
Sqlite is more plug and play since it creates the db for u
slate bone
Floppy is sqlite3 ok for discord bots?
dull scarab
It's ok for smaller bots
If you plan on ever going big with ,i'd plan ahead and use a more suited db
slate bone
Well I think sqlite3 is good for my bot but some people told me that it doesn't go on good with asyncio
dull scarab
well, you'd probably have to use the async lib for it
craggy otter
What about database what collects company's files to a server?
dull scarab
like aiosqlite
Uh, need a bit more context. Is it high traffic, large queries, many users etc
craggy otter
Yeah, but can you give me little knowledge how yo get started?
slate bone
Eagle I think sqlite3 is good for small to medium DBs
slate bone
Yeah that's what I'm gonna do
dull scarab
Eventhough it seems to handle it ok without, but cant guarantee it will forever
craggy otter
Can you pin those sqlite3 ect?
dull scarab
those sqlite what?
craggy otter
nvm
dull scarab
Anywho, if you wanna get started with postgres I believe you can find some guides on how to setup a db on their site you linked earlier
https://magicstack.github.io/asyncpg/current/usage.html with a few examples here etc
for aiosqlite there's a small sample code here, but i'd suggest reading a bit up on general SQL stuff
https://github.com/jreese/aiosqlite
like queries
hasty hinge
I am new with SQL, I uploaded all my csv-like file with values of the users with MySQLdb, but I don't know how to get an specific user with the first row (User ID)
dull scarab
"SELECT columns FROM users WHERE userid = %s", userid or something in that line
Depending on what columns you want, and what the name of the column your querying on is named
etc
hollow tiger
so uh
i'm looking at https://rethinkdb.com/api/python/get/ but i don't quite get it
in the example py r.table('heroes').get(3).merge( { 'powers': ['invisibility', 'speed'] } ).run(conn)
where does 3 from .get(3) come from?
is it just the third row of the table?
pseudo compass
it must be
hasty hinge
It is better to connect the DB and close it when you turn down your bot, or connect it and close it for every function?
I am using MySQL for my discord bot
slate bone
I prefer using aiomysql
why is It showing an error?
loop = asyncio.get_event_loop()
conn = aiosqlite3.connect('clans.db', loop=loop)
c = conn.cursor()```
```File "clansbot.py", line 3, in <module>
import clansbotconfig
File "C:\Users\User\Python Bots\clansbotconfig.py", line 11, in <module>
c = conn.cursor()
AttributeError: '_ContextManager' object has no attribute 'cursor'```
wispy pelican
Does your class have cursor attribute?
slate bone
I'm not even using a class should I ?
wispy pelican
Where is the function cursor() located?
slate bone
I just put that code exactly under the import
should I use a class or something ?
I figured it thanks
wispy pelican
what did it end up being?
slate bone
I should've awaited the cursor
hasty hinge
How can I get the first 10 rows descending from a table?
slate bone
@hasty hinge I think it's something like that
SELECT * FROM table
LIMIT 10
ORDER BY column1 DESC;```thanks
slate bone
@hasty hinge Limit without =
gusty spindle
i don't think there's = on ORDER BY either.
slate bone
lol sorry didn't notice it
gusty spindle
no worries :D
hasty hinge
pymysql.err.ProgrammingError: (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ORDER BY XP DESC' at line 1")
I am using "SELECT * FROM USUARIOS LIMIT 10 ORDER BY {} DESC;".format(var)
gusty spindle
.format 
gusty spindle
try putting the LIMIT after ORDER BY
hasty hinge
Using SELECT * FROM USUARIOS ORDER BY {} DESC LIMIT 10;
gusty spindle
yup :D
hasty hinge
thx
gusty spindle
still..
not sure how i feel on the use of .format(...) there
that's very vulnerable to SQL injections, and it seems like pymysql has a way to prevent that,
here's an example of how i believe it should be done:
cursor.execute("SELECT id, password FROM users WHERE email=%s", ("email@example.com",))```the second argument is a tuple, and pymysql will automatically format the elements of that tuple into your query at occurrences of %s after applying input sanitisation for you.
that's how it's shown on https://pymysql.readthedocs.io/en/latest/user/examples.html at least
i've never used pymysql so i'm just referring to that
@hasty hinge
gusty spindle
nope
not at all
what if you asked a user for their email for this query:
SELECT id, password FROM users WHERE email={}
and they input "inject" OR 1=1
that would give you sql SELECT id, password FROM users WHERE email="inject" OR 1=1
and 1=1 will evaluate to true every time so..
i hope you can see the issue there :)
honest jolt
anybody got any experience using sqlite3 for python?
dull scarab
!t ask
delicate fieldBOT
honest jolt
import sqlite3
conn = sqlite3.connect('test.db')
c = conn.cursor()
import openpyxl as ol
#Open excel
ins = ol.load_workbook("inspections.xlsx")
#Get excel page
inspect = ins.get_sheet_by_name('inspections')
val = [row[1].values, row[2].values, row[3].values]
def create_table_inspection():
c.execute('CREATE TABLE IF NOT EXISTS excelDataIns(actDate TEXT, employeeId TEXT, facAdd TEXT')
def data_entry():
c.executemany("INSERT INTO excelDataIns(actDate , employeeId , facAdd) VALUES (?,?,?)", val)
conn.commit()
c.close()
conn.close()
#create_table_inspection()
#data_entry()```
so im trying to read an excel file and insert the values 9/05/2017 EE0000593 17660 CHATSWORTH ST
10/04/2017 EE0000126 3615 PACIFIC COAST HWY
4/04/2017 EE0000593 17515 CHATSWORTH ST
15/08/2017 EE0000971 44455 VALLEY CENTRAL WAY```
short ver. of my exceland, does it show any errors?
dull scarab
val = [row[1].values, row[2].values, row[3].values] here?
honest jolt
yea
dull scarab
Where are you getting row from then
honest jolt
uh
dull scarab
Seems to be a step missing there
honest jolt
#Open excel
wb = ol.load_workbook("Stocks.xlsx")
#Get excel page
IBM_sheet = wb.get_sheet_by_name('IBM')
# Data for 2010
data10 = [[row[2].value, row[3].value, row[4].value, row[5].value] for row in IBM_sheet if row[0].value.year == 2010]```there is my teachers example
honest jolt
o my bad
dull scarab
!t listcomps
delicate fieldBOT
honest jolt
so how woul di make that work cause I dont want that if statement
dull scarab
You'd remove it
the if statement is just a conditional for which of the rows should be used
if you remove it, it will use all
honest jolt
aight lemme try it
dull scarab
just like this
even_numbers = [n for n in range(20) if n % 2 == 0] only gets even numbers
this will get all numbers
all_numbers = [n for n in range(20)]
dull scarab
Doesn't change anything tbf
you're iterating over an iterable, and assigning each element to row
if that element is an int, or not
Is up to how you make stuff
honest jolt
i see
val = [[row[1].values, row[2].values, row[3].values]for row in inspect]```val = [[row[1].values, row[2].values, row[3].values]for row in inspect]
AttributeError: 'Cell' object has no attribute 'values'```did I place the for loop wrong?
honest jolt
hmm. does it matter?
dull scarab
values and value are 2 completely different attribute / properties of the cells you're using
one doesnt exist, one probably does
as it seems to be a cell in an excel sheet, it would make sense that value works and not values
Since I can't imagine a cell in excel holding multiple values
honest jolt
oh i see
hazy peak
In mysql.connector, the max value for "connection_timeout" is 365 days. Is it ok to keep the connection up for like 7 days before it goes away?
hollow tiger
something weird...
i ran this:
import rethinkdb as r
import hastebin
conn = r.connect(db="appbot")
data = r.table("guilds").get("423374132873527297").run(conn) #original data
print(hastebin.post(str(data).encode('utf-8'))) #printing original data in a hastebin
del data["apps"]["Test"] # deleting a dict item
r.table("guilds").insert(data, conflict="update").run(conn) # supposedly updating the document
print(hastebin.post(str(r.table("guilds").get("423374132873527297").run(conn)).encode('utf-8')))
# ^^^ printing the updated data, which is the same as the original data... ^^^
(edited)
the results: https://hastebin.com/asimatoter https://hastebin.com/qolebejumi
but weird part is that both of the results are the same
what's going on?
dull scarab
Not familiar with rethink, but it seems like you're just trying to update values. So if a value is not present, it wont update it but it wont delete it unless specifically told so
hazy peak
If I use "MySQLConnection.is_connected()" method, will that reset current connection_timeout to 8h again?
hollow tiger
what
i am trying to update a document by doing: if i couldn't insert data on error then it'd update it
so basically it updates the data in a document
but for some reason it's not actually doing the updating part
dull scarab
But you're deleting it from data which is not connected to your db directly
It's just a container of data
So passing data to insert will just try to insert whats in data
It wont match the db to data from what I can tell
dull scarab
# pseudo code
data = db.get_entries()
print(data)
>>>[{"id": 1, "name": "bob"}, {"id": 2, "name": "carl"}]
del data[0] # delete the bob entry
# tries to *insert* the one entry that's left for carls, carl exists so it tries to update that entry with .. the same values it already had
db.insert(data, on_conflict=update)```That's about how I understand what's going on
and it doesn't anything about the bob entry in the db, as it doesn't have any conflicts
honest jolt
import sqlite3
conn = sqlite3.connect('Data.db')
c = conn.cursor()
#List the distinctive businesses that have had at least 1 violation ordered alphabetically to the console.
c.execute("SELECT excelDataIns.facName, excelDataVio.serialNo, excelDataVio.vioStat FROM excelDataVio, excelDataIns WHERE vioStat = 'OUT OF COMPLIANCE' ORDER BY excelDataIns.facName ASC")
for row in c:
print(row)
was wondering if i did it right because when I run the code it displays a operational error of full database/disk space, and when i checked how much space i had left it was 32gb on my ssd so perhaps im looping it wrong
my database file is 135mb
ionic pecan
can you post the full error?
IIRC, for row in c fetches one row at a time on DBAPI2 cursors
honest jolt
uh do u want a screenshot?
ionic pecan
screenshot or paste it in a codeblock, whatever works for you
honest jolt
aiht gimme 1 sec
File "C:/Users/Hoytein/Desktop/Assignment 2/Task 2/sql_food.py", line 6, in <module>
c.execute("SELECT excelDataIns.facName, excelDataVio.serialNo, excelDataVio.vioStat FROM excelDataVio, excelDataIns WHERE vioStat = 'OUT OF COMPLIANCE' ORDER BY excelDataIns.facName ASC")
OperationalError: database or disk is full```https://stackoverflow.com/questions/16685016/sqlite3-operationalerror-database-or-disk-is-full-on-lustre
Try raising max_page_count, although I'm not sure how that would affect a SELECT, maybe cache
honest jolt
hm.. interesting
so i read up on pragma and im still a bit confused on how to implement it in my code
import sqlite3, pragma
conn = sqlite3.connect('Data.db')
c = conn.cursor()
#List the distinctive businesses that have had at least 1 violation ordered alphabetically to the console.
c.execute("SELECT excelDataIns.facName, excelDataVio.serialNo, excelDataVio.vioStat FROM excelDataVio, excelDataIns WHERE vioStat = 'OUT OF COMPLIANCE' ORDER BY excelDataIns.facName ASC")
PRAGMA schema.max_page_count = 2147483646;
for row in c:
print(row)```
was wondering if this was how to use itWhat is the equivalent to "list.index()" in SQL?
I need to find in what row is one specific key
ionic pecan
what's index()?
hasty hinge
In python, it returns you an int with the number of the key
https://www.tutorialspoint.com/python/list_index.htm @ionic pecan
ionic pecan
ah, you edited that list. part in 😛
tables don't really have a concept of indexes, as far as I know
they are more like sets than lists
Can you explain your use case a bit?
hasty hinge
I want to make a "rank command" in discord.py, I already used ```python
await cur.execute("SELECT * FROM USUARIOS ORDER BY {} DESC LIMIT 50;".format(order))
to get the top 50, but I want a way to get an specific user without getting the full databasedo you have a primary key?
hasty hinge
yes, the user ID
pseudo compass
use a where clause
you also SHOULD NOT use .format or any other string formatting methods in a query
pseudo compass
sql injection
hasty hinge
I started yesterday
pseudo compass
if you are using data that does not come from you 100% (say, a string a user types), they can type a string that will execute sql statements
hasty hinge
nono, I am using it only for me.
pseudo compass
im not sure what db you are using, but most have some easy way to insert values into the query
something like execute("SELECT * FROM table WHERE id = ?", id)
dunno how your's does it
hasty hinge
I mean, that is in a function that I use in other of my code files, and to call it in the other files I use function("XP")
or function("GC")
That string is the "order" var
pseudo compass
look up how to insert a value in a query for your database library
but, if it's just for that, it's not a huge issue
hasty hinge
I am using aiomysql
pseudo compass
but its still good practice
hasty hinge
Yeah, I only can call that function from my code
pseudo compass
shouldn't be an issue, but it's up to you ¯_(ツ)_/¯
ionic pecan
a huge part of sql databases is indexing
"indexes" as inlist.indexaren't a thing in SQL databases. databases support indexes for fast lookup of certain keys, which has just about nothing to do with "at which index in my list is this element"
pseudo compass
oh i see what you meant
i mean, you can lookup a primary key by the value of another key in that same row
but they aren't the same as list indices
hasty hinge
So, need I to load the full db into python, and then get the index of the generated list?
ionic pecan
Just base your rank off the position in the returned list from the database, like you said, yes
craggy otter
I am looking solution in bugs.python.org
craggy otter
Yeah, i know
ionic pecan
If you can't import sqlite3, then your Python was compiled without it
How did you install it?
craggy otter
me?
pseudo compass
yes, did you compile python from source?
craggy otter
I think so
pseudo compass
sudo apt install ca-certificates libexpat1 libffi6 libgdbm3 libreadline7 libsqlite3-0 libssl1.1 dpkg-dev gcc libbz2-dev libc6-dev libexpat1-dev libffi-dev libgdbm-dev liblzma-dev libncursesw5-dev libreadline-dev libsqlite3-dev libssl-dev make tcl-dev tk-dev wget xz-utils zlib1g-dev gnupg dirmngr
run that and recompile python
that'll get you all the libraries you need for sqlite3 and whatever other extensions you may have missed
craggy otter
Still not found
pseudo compass
dont relaunch python, recompile it
craggy otter
with?
ionic pecan
which platform are you on?
craggy otter
ubuntu
pseudo compass
@craggy otter if you compiled it from source, install those and recompile it
if you have no idea what i'm talking about, you didn't compile it
craggy otter
I didnt i am trying to find how to do it from google
pseudo compass
#!/usr/bin/env bash
VERSION=3.7.0
sudo apt update
sudo apt install ca-certificates libexpat1 libffi6 libgdbm3 libreadline7 libsqlite3-0 libssl1.1 dpkg-dev gcc libbz2-dev libc6-dev libexpat1-dev libffi-dev libgdbm-dev liblzma-dev libncursesw5-dev libreadline-dev libsqlite3-dev libssl-dev make tcl-dev tk-dev wget xz-utils zlib1g-dev gnupg dirmngr
wget https://www.python.org/ftp/python/${VERSION}/Python-${VERSION}.tar.xz
wget https://www.python.org/ftp/python/${VERSION}/Python-${VERSION}.tar.xz.asc
gpg --keyserver ha.pool.sks-keyservers.net --recv-keys 0D96DF4D4110E5C43FBFB17F2D347EA6AA65421D
gpg --batch --verify Python-${VERSION}.tar.xz.asc Python-${VERSION}.tar.xz
tar -xf Python-${VERSION}.tar.xz
cd Python-${VERSION}
./configure --enable-optimizations --enable-loadable-sqlite-extensions --enable-shared --with-system-expat --with-system-ffi
make -j "$(nproc)"
sudo make altinstall
replace 3.7.0 with your version
that's the easiest way
ionic pecan
why wouldn't you just install from the repos though?
pseudo compass
this enables optimizations and if doesn't have sqlite3 installed from the repo, this is a surefire way to get it
slate bone
Guys we only use commit for changes like create or delete right ?
But do we have to use it for things like SELECT which doesn't cause any change ?
I use aiosqlite3 by the way
tender hollow
commit writes changes to the database, so no
slate bone
Thanks mate
obsidian leaf
hiya - Im trying to change a password in a database and the program runs - but the password never changes - anyone see anything wrong with the code?
pseudo compass
are you committing to the db?
obsidian leaf
i did change the variable
sick swallow
maybe currentuser.password is the old password instead of the new one
hollow tiger
hey
i'm using rethinkdb and i'm trying to update a document but it won't let me
>>> r.table("guilds").insert(data, conflict="update").run(conn)
{'deleted': 0, 'errors': 0, 'inserted': 0, 'replaced': 0, 'skipped': 0, 'unchanged': 1}```btw removing removing , conflict="update" just gives this huge error saying {'deleted': 0, 'errors': 1, 'first_error': data, 'inserted': 0, 'replaced': 0, 'skipped': 0, 'unchanged': 0}
vestal apex
are you updating it with the same information it already has?
hollow tiger
no
>>> import rethinkdb as r
>>> conn = r.connect(db="appbot")
>>> data = r.table("guilds").get("423374132873527297").run(conn)
>>> del data["apps"]["Test"]
>>> r.table("guilds").insert(data, conflict="update").run(conn)```i compared data before and after that 5th line and it didn't change
vestal apex
updating on conflicts reads to me like dictionary updating ```py
x = {0:0, 1:1}
y = x.copy()
del y[0]
x.update(y)
y
{1: 1}
x
{0: 0, 1: 1}```
which does not remove keys that don't exist in the update dict
vestal apex
hollow tiger
hmm... lemme check it out
slate bone
loop = asyncio.get_event_loop()
conn = aiosqlite3.connect('clans.db', loop=loop)
# FIXME : Checking if a member in cooldown
async def check_member(member_id):
global conn
fetch = await conn.cursor().execute("SELECT id FROM cooldown WHERE id = '{0}'".format(member_id)).fetchone()
if fetch is None:
return True # User Not In Cool Down
else:
return False # User In Cool Down```
it shows that error
```Command raised an exception: AttributeError: '_ContextManager' object has no attribute 'cursor'```Also I will change the .format() later no need to tell me lol
ionic pecan
check what aiosqlite3.connect returns
slate bone
That's weird when i tried to return the aiosqlite3.connect it sent the error the ran the command then sent the error again
# What was supposed to be sent to the channel in discord
Command raised an exception: AttributeError: '_ContextManager' object has no attribute 'cursor'```I tried to remove global conn and rewrote the conn variable and the loop variable and same error
By the way there is other functions that worked just fine like that ....
# That worked though it's similar
Check Clan Name
async def check_name(name):
global conn
return conn
fetch = await conn.cursor().execute("SELECT name FROM clans WHERE name = '{0}'".format(name)).fetchone()
if fetch is None:
return True # Name is Availabe
else:
return False # Name isn't Availabe```when someone answers please @ Me
ionic pecan
yes, it "works fine" because you just return instantly
slate bone
Sorry I switched them and returned the conn in the wrong function lol
When I add it to the right function it doesn't return anything
and sends the error once
The code again shows an error Command raised an exception: AttributeError: '_ContextManager' object has no attribute 'cursor'
# FIXME : Checking if a member in cooldown
async def check_member(member_id):
global conn
fetch = await conn.cursor().execute("SELECT id FROM cooldown WHERE id = '{0}'".format(member_id)).fetchone()
if fetch is None:
return True # User Not In Cool Down
else:
return False # User In Cool Down```By the way it doesn't print it but I put on my error handler to send an error if happens in the chat and it sends that
glossy ermine
async def check_member(member_id):
cursor = await conn.cursor()
await cursor.execute("SELECT id FROM cooldown WHERE id = ?", (member_id,))
return await cursor.fetchone() is None
@slate bone
slate bone
@glossy ermine Same error sorry bro
By the way that's a config file and the error is showing in check_member
slate bone
[Solved] : Should've awaited the connection thanks
hollow tiger
hey, i'm trying to get something here
Quoting from https://www.rethinkdb.com/docs/cookbook/python/:
Removing a field from a document
The update command lets you to overwrite fields, but not delete them. If you want to delete a field, use the replace command. The replace command replaces your entire document with the new document you pass as an argument. For example, if you want to delete the field author of the blog post with the id 1, you would use:
r.table("posts").get("1").replace(r.row.without('author')).run()```how would i go about deleting a field that's within a field in the r.row.without()?
what i mean is like {"i want":{"to delete":"this"}}
please ping me if you are responding
dull scarab
Can't you just get("1").delete().run() or something to delete it?
hollow tiger
i wanted to remove a field from a document, not delete the whole document
dull scarab
I mean, it's just a database without sql, cols and rows. If you fetch the row, you can delete the row
From the link you posted ```py
Or, let’s try to delete a single user:
r.table("posts").get("7644aaf2-9928-4231-aa68-4e65e31bf219").delete().run()```
hollow tiger
i'm not trying to delete a row - i'm trying to delete a field from a row
dull scarab
I suppose what your wrote is correct then, I'm just giving input. Why don't you try it and see if it does what you want it to?
hollow tiger
it doesn't though - i want to delete a field that's inside of a field if u know what i mean
{"deleting":{"this":"dict field"},"not":"this"}
glossy ermine
@slate bone You don't need the global conn in each function
hollow tiger
anyone? D:
Does anyone know the answer to https://discordapp.com/channels/267624335836053506/342318764227821568/486098346671603723?
crisp robin
Hello. MongoDB: (Motor)
return await client.db.xp.update_one( { "_id": message.author.id }, { "$set": { "name": message.author.name } } ,{ "$set": {"xp": 0} }, { "$set":{"level": 1} },{ "$set": {"points": 0} },{ "$set": {"time": time.time()} }, upsert = True)``` Error:
```py
update_one() got multiple values for argument 'upsert'```
Please help. I've tried many different things but none of them worked.I have no idea how it works but
why do you have many different $sets
why isnt there just one $set with a dictionary with all the values
crisp robin
If I do one set, it gives me errors. I asked a friend and he said try doing many different sets.
I'll try again and post the error.
tender hollow
what happens if you remove upsert = True
crisp robin
Another error, I think saying i can use update_one.
tender hollow
you think? What is the actual error, copy paste it
hasty hinge
I see that I wrote by error some IDs in my database, how can I delete all the rows with exactly IDs that others except the ones with most big value?