also what's the exact problem you're trying to solve with your query?
#databases
runic pilot
torn sphinx
buses has : id, origin and destination, and departure time
passengers has their own id (not useful) and time along with origin and destination they need to go to
the passenger time is when they arrive at the bus station.. so next bus they need to take I take in to account to find hte count of passengers on that bus
so in my query I made pd, which is possible departure times.. and I joined that back to b and p to arrive at counts
runic pilot
so you're trying to count the passengers that will take a given bus?
torn sphinx
yes
runic pilot
and what's the schema of the tables?
torn sphinx
buses: id, origin, destination, time
pass: id, origin, destination, time
pass id is not related to bus id
time is varchar.. so I cast as time
runic pilot
and for a passenger to take a bus, it needs to be pass.origin = buses.origin AND pass.time <= buses.time ?
torn sphinx
yes
runic pilot
(and they need the same destination?)
runic pilot
sorry I didn't actually read the query you posted, it's a bit of a monster
but yeah it sounds like you have the general idea
torn sphinx
I think
I'm only missing one part
the 0 for passenger count
when there's no buses
runic pilot
you want to join them on those conditions and GROUP BY bus ID and count the distinct passenger IDs
oh
torn sphinx
is there a way I can add like an else or something
runic pilot
you can use case statements
runic pilot
which flavor of sql are you using?
torn sphinx
postgresql
runic pilot
this should help I think, there's a few examples here https://www.postgresql.org/docs/11/functions-conditional.html#FUNCTIONS-CASE
but it does seem strange to treat SQL like a programming language like this, normally you'd pull the results into python and format them base on the queried results
torn sphinx
im practising
runic pilot
I have before, I'm not a pro, but I'm decent at it
tawny thicket
great man, have you ever configure the ip & hostname? Trying to find a way to do it
runic pilot
of the client or the server?
runic pilot
so you're running the server locally?
tawny thicket
on a vps
runic pilot
ok, can you tell me what you're trying to accomplish? like are you trying to end up connecting with a mydb.domain.com:2345 from a client?
tawny thicket
not connecting, i just want to change my mongodb ip address.
runic pilot
gotcha
so a server is automatically assigned an IP based on the CIDR block given to your account from your hosting provider
some hosting providers allow you to create a "static IP" that you can point to other IP addresses, though
so you can always rely on that IP even if the server goes down and comes back up or you switch the server that the DB is on
otherwise, it's common practice, so assign a CNAME domain to the server to allow connection via a domain instead of an IP address
runic pilot
happy to help!
vagrant hearth
Hi everyone, this is my first message in this server and I'm kinda new to Python. I have a question regarding whether holding instances of an object in a dictionary is a good thing. For example, I created an online game where multiple users will come up with a creative caption for a non-caption meme. Let say I have a server which holds a dictionary of game objects with its game'id as the key. Every time a user submits a caption, the server will identify the request's game ID (the server will store the request and the room's code associated with it when first time connected) and pull out the game with that ID and update the game's state and send back new updates to other connected users in the game. So here is my questions, Is there a way where I can separate this dictionary containing instances of game object from the server?
and is there a better way to hold instances of python objects other than dictionary?
pseudo summit
Use pickle and store it in a binary field.
nocturne dock
Is it considered good practice to write SQL inline, or should you be factoring it out into templated .sql files?
pseudo summit
So with Postgres thatβd Data type bytea
vagrant hearth
@pseudo summit while running ? This is not a very fast way to pull and update
Please clarify why you would think by using pickle will help separate the dictionary from the server
runic pilot
It sounds like you want a database
Which holds data separate from you application servers
vagrant hearth
yes a kind of database that is fast in queries
If I may ask, would dictionary is good enough to hold instances of game object?
maiden heart
@lilac bane then , I connected to the db easily
but
I have troubles in understanding should I close() the conn or no, do I have to do .commit() or .cursor like psycopg2 or no
lilac bane
it opens a connection
so you have to close it
i dont know if it works with ctx managers tho
maiden heart
and sth else
maiden heart
when I execute code, I have to fetch() or fetchall()
maiden heart
I am so confused
lilac bane
or fetchrow()
maiden heart
I was trying to do so, but when I stuck I can't find anyone to help me in π I ll try some code
eternal raptor
@commands.command(pass_context=True)
async def utworzprof(self, ctx, member: discord.Member, ID = None):
conn = sqlite3.connect('bazaNinjaSerwer.db')
c = conn.cursor()
utworz = """INSERT INTO ekonomia VALUES (? INT, ? TEXT, 0);""" (ID, member)
c.execute(utworz)
result = c.fetchone()
async for i in result:
await ctx.send(i)
print(i)
await ctx.send("Pomyslnie utworzono profil!")
conn.commit()
conn.close()
When I use command: n/utworzprof 1 @eternal raptor
File "C:\Users\user\AppData\Local\Programs\Python\Python37-32\lib\site-packages\discord\ext\commands\converter.py", line 134, in convert
raise BadArgument('Member "{}" not found'.format(argument))
discord.ext.commands.errors.BadArgument: Member "1" not found
modest matrix
this isn't a db issue
you're using the wrong order for your command.
maiden heart
@modest matrix hey ! can u help me in sth
I can't understand when I should do connection.close() in asyncpg
every query ?
or what ?
torn sphinx
in pgAdmin 4 it says read only for column, how can i open write to the column?
modest matrix
not exactly an expert on this
afaik, unless you use transactions, changes are applied immediately
you using it for a discord bot? you're better off using a connection pool, rather than opening a connection with every command
maiden heart
not exactly an expert on this
afaik, unless you use transactions, changes are applied immediately
you using it for a discord bot? you're better off using a connection pool, rather than opening a connection with every command
@modest matrix wdym ? can u simplify pls ? I just know the basics of postgres I am beginner
modest matrix
read the asyncpg docs for connection pools
I don't feel comfortable explaining something I don't really have knowledge on myself
maiden heart
the problem is that I can't find someone who has knowledge
compact dove
@eternal raptor i just wanted to ask if is it a discord bot? and what it does and why it needs a database?
modest matrix
read the docs and ask specific questions
a connection pool is basically, well, a pool of reusable connections that are being kept connected
so you don't have to connect for every command, but instead you retrieve a connection from the pool and give it back once you're done
you can have this as a bot var, like if you subclass bot, but this is more discord.py territory rather than databases
toxic rune
You shouldn't close connection every query. You close it when you are done. @maiden heart
Opening connections is an expensive task.
maiden heart
You shouldn't close connection every query. You close it when you are done. @maiden heart
@toxic rune aha
ok
then I should do .close() at the end of the code
but, now I am not using cogs , if I used how could I do this
?
toxic rune
Are you doing a discord bot? If so, you should close it when the bot is shutting down.
That's what most of the bots usually do
They use pools (basically multiple connections on standby) but concept applies here too.
modest matrix
use ?tag botvar in the testing channel on the discord.py server
gives you an explanation on how to have the pool be part of your bot instance, which you can use everywhere.
maiden heart
@modest matrix then pools is a variable in my bot so I can connect whith in every command
but
as @toxic rune said , connecting to db is expensive
so I should do just one connection when the bot is ready
and
close the connection
when the bot restarts, close , etc
am I right or am I idiot ? π
toxic rune
Most of bots have a subclass for commands.Bot where they initialize stuff like aiohttp ClientSession or in this case, the connection pool.
Generally, this on most cases will work fine.
Except that you have to do it within a coroutine since create_pool needs to be awaited.
Then on any cog, or any module that you have access to "bot", you can use it fine.
maiden heart
@toxic rune sry , But I can not understand it well :\ its 80%
toxic rune
Which part?
maiden heart
can you just simplify the mechanism it works a little bit ? explain on the code the img
pls
toxic rune
Not really good at explaining but:
Well you're just creating a commands.Bot instance and then giving it a variable called "my_variable" and assigning 0 to it.
Now any module that has access to bot (i.e. a cog, since you usually pass the bot instance to it) can access to "my_variable."
Can apply the same mechanism but for the asyncpg connection instead.
modest matrix
What Dimbreath said
Additionally, an explanation on pools:
You don't really connect if you have a pool
The connecting is done when you create the pool.
So whenever you use pool.aquire, you retrieve an already active connection, and give it back when you're done with the command, so other commands can use it
maiden heart
oh ok
toxic rune
That's just increasing the value of my_variable by 1. In this case you don't need that, mostly for example purpose
maiden heart
then The connection is assigned to a variable assigned to commands.Bot instance
I can retrieve it every command
toxic rune
Yes, (or the pool, whichever you're using.)
And any command or anything that can access "bot" will be able to use that.
I.e. cogs way would be:
class MyCog(commands.Cog):
def __init__(self, bot):
self.bot = bot
def setup(bot):
bot.add_cog(MyCog(bot))
So within that cog/class you'll be able to use that variable.
And execute queries
maiden heart
but now , I ll assign the var with asyncpg.connect()
when I retrieve the var , it will do sth like new connection
?
or what ?
but no, I can assign the botvar with
.execute
and the connection is done earlier
so no worries
anyway , TY
toxic rune
The connection is created only when you assign it to the var, after that it will stay open until you close it (in most cases, when the bot is shutting down.)
modest matrix
Read the docs on it 
.connect() doesn't create a pool
Once you have the pool assigned to your botvar, you can get an actual connection using pool.acquire()
maiden heart
and I ll assign the bot.var with the .execute()
Read the docs on it :notlikeduck:
.connect() doesn't create a pool
Once you have the pool assigned to your botvar, you can get an actual connection using pool.acquire()
@modest matrix you mean .execute() by the pool in this case right ?
modest matrix
but now , I ll assign the var with asyncpg.connect()
Was referring to this
maiden heart
aha ! I know this is not the pool
I mean the pool you are talking about in asyncpg case is .execute() method
modest matrix
I don't understand what you mean 
maiden heart
@modest matrix see
import asyncio
import asyncpg
import datetime
async def main():
# Establish a connection to an existing database named "test"
# as a "postgres" user.
conn = await asyncpg.connect('postgresql://postgres@localhost/test')
# Execute a statement to create a new table.
await conn.execute('''
CREATE TABLE users(
id serial PRIMARY KEY,
name text,
dob date
)
''')
# Insert a record into the created table.
await conn.execute('''
INSERT INTO users(name, dob) VALUES($1, $2)
''', 'Bob', datetime.date(1984, 3, 1))
# Select a row from the table.
row = await conn.fetchrow(
'SELECT * FROM users WHERE name = $1', 'Bob')
# *row* now contains
# asyncpg.Record(id=1, name='Bob', dob=datetime.date(1984, 3, 1))
# Close the connection.
await conn.close()
asyncio.get_event_loop().run_until_complete(main())```I ll firstly do sth like this :
then
I ll do sth like that :
hushed forum
language
maiden heart
AND I THINK I AM COMPLETELY FUCKING WRONG
maiden heart
@hushed forum what do u want ?
modest matrix
Honestly, it's just very difficult to help you because you don't seem to understand what were saying whatsoever
hushed forum
^
modest matrix
And I dont really understand you either most of the time
maiden heart
Honestly, it's just very difficult to help you because you don't seem to understand what were saying whatsoever
@modest matrix I understand , but I did not do that before
hmm ok can I do the last try to make you understand what I mean ?
if you don't ok :(
maiden heart
@hushed forum r u speaking to me ?
maiden heart
if you don't ok :(
whould u do ?
modest matrix
I mean, we can try at least
I think the issue is kinda that you want to learn too many things at the same time, and it just confuses you
maiden heart
i learnt discord.py well , but I need to learn the best way to execute my code when I connect to a db
you told me about pools
but I can not find anything in the docs about except 1 line
,so can you give code example about making pool ? I learnt how to make the bot var @modest matrix
modest matrix
There's an example here
https://magicstack.github.io/asyncpg/current/api/index.html#connection-pools
It's pretty straightforward
modest matrix
it's very similar to connect
Yea idk, things are a bit hard to find in the docs
So then in your commands, you just use your botvar to acquire and release the connection
maiden heart
I ll replace the connect() by pool ,, I think I 've fully understood
I ll do the code
modest matrix
yeah, should work
maiden heart
@modest matrix should I do command_timeout ?
torn sphinx
how can i delete row on pgAdmin 4 ? not in code, on panel
modest matrix
You don't have to
maiden heart
@modest matrix that is right ?
torn sphinx
how can i delete row on pgAdmin 4 ? not in code, on panel
@torn sphinx plsssss anyone answeeerrr
torn sphinx
@maiden heart bro i googled first but this not worked i cant click delete button
maiden heart
ok , then when I want to retrieve I ll do for ex : Ahmedinoo_db_con.execute("")
that's it ?
modest matrix
Ahmedinoo is your bot instance, I'm assuming?
if you want to do execute you do:
connection = await <your bot>.dpconnect.acquire()
await connection.execute('')
await <your bot>.dpconnect.release(connection)```yes it is Ahmedinoo
modest matrix
ah yeah
maiden heart
but why I should await the last line ?
modest matrix
but you do the acquiring in the commands
maiden heart
and I must do this every command right ?
modest matrix
because it's a coroutine 
yea, for every command
at least for the ones where you do db stuff
maiden heart
I know, I mean why should I write it not await it
modest matrix
ah
see, you get a connection from the pool with acquire
and with release, you tell the pool: "hey, I don't need this connection anymore, I'm giving it back, so it can be used somewhere else"
maiden heart
oh ! ok π
then , this way many users can access the db through command per time right ?
modest matrix
yea, and most importantly, you don't have to do entirely new connections each time someone runs a command
modest matrix
π
hushed forum
but the IED is giving error that var is not defined
@maiden heart IED GO BOOM BOOM
BOOM BOOM
BOOM
I LOVE BOOM
maiden heart
he is bothering me
hushed forum
doon doon
i love when we together dundondin do doo
aye fuck off
ur mum gay
i am a god
maiden heart
oh ! you are rude
pale crest
!tempban 688301469291642891 7d Take some time off and review our rules and code of conduct if you want to be part of this community.
delicate fieldBOT
:ok_hand: applied ban to @hushed forum until 2020-06-12 12:24 (6 days and 23 hours).
pale crest
No problem.
maiden heart
I don't do anything just I missed IDE and I wrote IED
pale crest
Don't worry about it.
maiden heart
anyway, @modest matrix can you tell me should I do await the pool inside the function or what ?
pale crest
Some people just have no lives and feel the need to bother others to make themselves feel better.
maiden heart
Some just have no lives and feel the need to bother others to make themselves feel better.
@pale crest aha ! your community is great
torn sphinx
how can i save changes i do on my db with asyncpg?
modest matrix
you mean pool.acquire? yea that has to be awaited
torn sphinx
i am writing conn.commit() but it says pool object has no attribute commit
@modest matrix can u help plzz
modest matrix
idk what db you have, what library you use, your code, or even what you're trying to do
modest matrix
"pls help no work" isn't sufficient
you're most likely using commit on a pool
the error literally tells you
torn sphinx
lmaoo @modest matrix im using postgresql and my module is asyncpg and im trying to save what im changed on db
maiden heart
#-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=Importing-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=>
import asyncpg
import asyncio
import discord
from discord.ext import commands
#-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=Constant Vars-=-=-=-=-=-=-=-=-=-=-=-=-=>
dpPort = "5432"
dpUser ="xpceajcl"
dpPassword =""
dpName = "xpceajcl"
dpHost = "kandula.db.elephantsql.com"
#-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=Bot Customizables-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=>
Ahmedinoo = commands.Bot(command_prefix = "p")
Ahmedinoo.remove_command('help')
Ahmedinoo.dpconnect = await asyncpg.create_pool(
database = dpName,
user = dpUser,
password= dpPassword,
host= dpHost,
port = dpPort)
#-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=Main=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=>
@Ahmedinoo.event
async def on_ready():
print ("Hi Ahmedinoo")
print ("Your Bot is Working !")
@Ahmedinoo.command()
async def minecraft(ctx, **accounts):
aquire = await dpconnect.aquire()
credits = aquire.fetchall("SELECT * FROM CREDITS")
await Ahmedinoo.dpconnect.release(aquire)
for rows in credits:
print(rows)
token = ""
Ahmedinoo.run(token) ```okay but how can i save changes @modest matrix
modest matrix
you don't have to, unless you're using transactions
and if you're using a pool, you have to acquire a connection firs
maiden heart
@modest matrixi did
modest matrix
(ozamed)
your issue is that you're, well, using create_pool outside of a function
just one sec
maiden heart
ok
modest matrix
you can use run_until_complete
so ```py
loop = asyncio.get_event_loop()
Ahmedinoo.dpconnect = loop.run_until_complete(create_pool(
database = dpName,
user = dpUser,
password= dpPassword,
host= dpHost,
port = dpPort))
should at least work, there's most likely better ways, but I'm not exactly an expert @modest matrix you sure it is asyncio ?
I think my code is all wrong now because of asyncio
the events, commands are not defined
modest matrix
show what you did
maiden heart
@modest matrix
modest matrix
forgot a bracket
modest matrix
asyncpg.create_pool
this is getting kinda offtopic tho, this is very basic
noble oak
Ignoring exception in on_message
Traceback (most recent call last):
File "E:\Program Files (x86)\Python\lib\site-packages\discord\client.py", line 312, in _run_event
await coro(*args, **kwargs)
File "e:/Games and Apps/Visual Studio Code/Visual Code Projects/Discord Bot/main.py", line 79, in on_message
await client.process_commands(message)
File "E:\Program Files (x86)\Python\lib\site-packages\discord\ext\commands\bot.py", line 926, in process_commands
ctx = await self.get_context(message)
File "E:\Program Files (x86)\Python\lib\site-packages\discord\ext\commands\bot.py", line 842, in get_context
prefix = await self.get_prefix(message)
File "E:\Program Files (x86)\Python\lib\site-packages\discord\ext\commands\bot.py", line 787, in get_prefix
ret = await discord.utils.maybe_coroutine(prefix, self, message)
File "E:\Program Files (x86)\Python\lib\site-packages\discord\utils.py", line 319, in maybe_coroutine
return await value
File "e:/Games and Apps/Visual Studio Code/Visual Code Projects/Discord Bot/main.py", line 23, in get_prefix
cursor = db.cursor()
psycopg2.InterfaceError: connection already closed```
```py
async def get_prefix(client, message):
if not message.guild:
return commands.when_mentioned_or(".")(client, message)
cursor = db.cursor()
cursor.execute("SELECT prefix FROM prefixes WHERE guild_id = %s", (message.guild.id,))
print(cursor.query)
result = cursor.fetchone()
if result is None:
return "."
else:
return result
cursor.close()
db.close() ```
Why is it telling me that the db is already closed@modest matrix too many connections error π I think this code would not work today :(
modest matrix
try giving it a min_size
you can define how many connections it shjould always have, by giving create_pool the kwarg min_size
(int, obv)
maiden heart
also, when aquiring I should do Ahmedinoo.dpconnect.aquire()
modest matrix
yes
modest matrix
then make min_size 1 and max_size 5
maiden heart
I don't do any connections yet
ok
@modest matrix it is saying that pool has not aquire
:((
modest matrix
because it's spelled acquire
maiden heart
oh ! sorry man
modest matrix
you could also try reading the docs for once, instead of pinging me all the time :))
maiden heart
ok , sorry
I am bothering u
:(
@modest matrix I DID IT , i ve got stucked but used docs ! any way TYSM and SRY FOR bothering you by pinging
π
somber hatch
so im trying to make an api for mongodb but i get this
TypeError: Object of type ObjectId is not JSON serializable
@app.route('/attack', methods=['GET'])
def get_all_stars():
uinfo = mongo.db.attack
output = []
for s in uinfo.find():
output.append(
{'_id': s['_id'], 'guild id': s['guild id'], 'guild name': s['guild name'], 'Boss level': s['Boss level']})
return jsonify({'result': output})
zinc maple
seems like you wanna do s['_id'].toString()
no wait that's not python
try {'_id': str(s['_id']), 'guild id...
somber hatch
oh yeah that worked tysm
zinc maple
supposedly gives you hex encoded string representation and you can even compare it without problems (string_from_objectid == objectid for example)
maiden heart
guys, the $ sign and the digit means insert data in that specific row , right ?
torn sphinx
Hello, im making a club system for my bot, for that im using aiosqlite, the problem is even though the fetch is None it returns the elif statement
eternal raptor
Code is above
eternal raptor
@commands.command()
async def utworzprof(self, ctx, IDD : str):
conn = sqlite3.connect('bazaNinjaSerwer.db')
c = conn.cursor()
IDD = discord.ClientUser
sql = "INSERT INTO ekonomia (ID, User, NinjaGold) VALUES ('?', '1', '0')"
c.execute(sql, (IDD,))
await ctx.send("Pomyslnie utworzono profil!")
conn.commit()
conn.close()
Ignoring exception in command utworzprof:
Traceback (most recent call last):
File "C:\Users\user\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\user\Desktop~BOT~\cogs\ekonomia.py", line 31, in utworzprof
c.execute(sql, (IDD,))
sqlite3.ProgrammingError: Incorrect number of bindings supplied. The current statement uses 0, and there are 1 supplied.
The above exception was the direct cause of the following exception:
Traceback (most recent call last):
File "C:\Users\user\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\user\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\user\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: ProgrammingError: Incorrect number of bindings supplied. The current statement uses 0, and there are 1 supplied.
@commands.command()
async def utworzprof(self, ctx, ID : discord.Member):
conn = sqlite3.connect('bazaNinjaSerwer.db')
c = conn.cursor()
c.execute("INSERT INTO ekonomia (ID, User, NinjaGold) VALUES (?, 1, 0)", ID)
await ctx.send("Pomyslnie utworzono profil!")
conn.commit()
conn.close()
Ignoring exception in command utworzprof:
Traceback (most recent call last):
File "C:\Users\user\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\user\Desktop~BOT~\cogs\ekonomia.py", line 29, in utworzprof
c.execute("INSERT INTO ekonomia (ID, User, NinjaGold) VALUES (?, 1, 0)", ID)
ValueError: parameters are of unsupported type
The above exception was the direct cause of the following exception:
Traceback (most recent call last):
File "C:\Users\user\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\user\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\user\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: ValueError: parameters are of unsupported type
brittle pulsar
Hi guys I want to learn sql language, What's a good IDE to use?
Can I use Python's pycharm IDE for SQL language?
pure cypress
For pure SQL there's DataGrip
I don't know if the free version of PyCharm has sql support, but the pro version does
brittle pulsar
@pure cypress Is DataGrip what most ppl use?
pure cypress
I don't know
brittle pulsar
@languid merlin What you mean used to use DBeaver? why did you stop?
languid merlin
For SQL
- For SQLite, I use https://sqlitestudio.pl/features/
- For PostGRES, pgAdmin should be OK. For MySQL, should be phpMyAdmin, or something.
@brittle pulsar I haven't touch SQL, except for SQLite for a while.
But DBeaver is powered by JDBC, and is quite handy.
brittle pulsar
Why? SQL seems fun
languid merlin
SQL hostings are not generous, and I do go over 10k rows (if you mean Heroku) sometimes.
https://dev.to/patarapolw/generous-sql-or-nosql-hosting-similar-to-mongodb-atlas-1g3h
By even if I use MongoDB, I still do care about schemas (which is covered by Node.js's Mongoose, anyway.)
Mongo JOINs are slow, and PostGRES tends to be performant, BTW.
somber hatch
i have this ```py
@app.route('/hackers/', methods=['GET'])
def get_one_hacker(Name):
hacker = mongo.db.hackers
s = hacker.find_one({'Name': f"{Name}"})
if s:
output = {'Name': s['Name'], 'Reports': s['Reports'], 'First report': s['First report'],
'Last report': s['Last report']}
else:
output = "No such name"
return jsonify({'result': output})
but whenever i do http: // localhost:5000 / hackers / pinkulu
i get
```html
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2 Final//EN">
<title>404 Not Found</title>
<h1>Not Found</h1>
<p>The requested URL was not found on the server. If you entered the URL manually please check your spelling and try
again.</p>
in postman
i know i am i the database, os idk why it dosnt work
surreal forge
im using tinydb for a small database that's storing a list of numbers and strings
and im trying to cache the database in memory
to increase speed because
what im doing is a discord bot and it's storing prefixes
if i don't cache it it'll have to go reading disk for every single message sent
to try to find a prefix
pseudo summit
@somber hatch I think it is your route.
/hackers/ and /hackers/<Name>/ are different.
So hi here getting a 404
Cause notice your else didnβt even fire
pseudo summit
So what I said? You had /hackers/ but were trying to call /hackers/<Name>/
So you called a route that didnβt exist hence 404
somber hatch
yeah
pseudo summit
π
minor ruin
Speaking of NoSQL stuff, Azure has CosmosDB which JSON documents you can query in SQL like interface. THey made it free for ~100 requests a second and 1GB of storage. There also have emulator if you want to run it locally for testing. They just released a new Python SDK which is much more improved their old setup
torn sphinx
damn that's pretty cool
and it's all types of nosql.. I wonder what their internal architecture is
minor ruin
not sure
pseudo summit
Itβs proprietary not just a reskin of something else as far as I can tell.
minor ruin
oh yea, it's completely 100% Azure only
it came from needing backend storage system for Azure and grew out of that
nocturne dock
Are the APIs between Azure/AWS/GCP consistent? Or do you have to learn different things for each system?
hard harbor
What are some solutions you guys would recommend for multitenant database system?
I have an approach using docker in mind so I will deploy these docker images by just changing the config but I don't have any experience in it and would like to know if there are some good resources to help manage that
round turtle
MongoEngine is good for defining a document for data validation. Raw pymongo is lacking in this area. Can I use MongoEngine to define a document first, then use pymongo to insertMany documents into an empty collection? If yes, will pymongo's insertMany() do data validation based on the document definition set by mongoengine?
torn sphinx
in the create commands checks are working but its not inserting
no error in console though
code: https://hatebin.com/rodorhytqa
rich trout
I feel like it should be self.cursor.commit()
it should be
not self.db.commit()
That should error.
torn sphinx
there is no method like that
rich trout
!e ```py
import sqlite3
conn = sqlite3.connect(":memory:")
c = conn.cursor()
c.execute('''CREATE TABLE stocks
(date text, trans text, symbol text, qty real, price real)''')
c.execute("INSERT INTO stocks VALUES ('2006-01-05','BUY','RHAT',100,35.14)")
conn.commit()
c.execute("SELECT * FROM stocks")
print(c.fetchall())
conn.close()
@rich trout :white_check_mark: Your eval job has completed with return code 0.
[('2006-01-05', 'BUY', 'RHAT', 100.0, 35.14)]
How do you know its not inserting?
torn sphinx
its not in the db
rich trout
how are you checking the db?
rich trout
what needed changing?
torn sphinx
having error in db browser
and another help
on the same command im just trying to fetch all the names and check if the given name is in the db. Even though the fetch is None, its just return the else statement
in the create command
toxic rune
A simplish design question. So I have a bot that displays data from a database. Some of that data has to be joined with another table that holds the names to avoid repetition (1:M relationship).
No issue until there, the issue is what I want to show an emoji related to that data that I have uploaded on my Discord server.
My question is what would be better:
- Store the emoji IDs on the database.
- Return the ID related to that data and have it checked on the bot-side.
toxic rune
For reference on what I said earlier:
CREATE TABLE Nation(
id SMALLINT NOT NULL,
name TEXT NOT NULL,
PRIMARY KEY (id)
);
CREATE TABLE Ship(
id SMALLINT NOT NULL,
nation_id SMALLINT NOT NULL,
PRIMARY KEY (id),
FOREIGN KEY (nation_id) REFERENCES Nation (id)
);
-- Query...
SELECT ship.id, nation.name FROM Ship ship JOIN (SELECT id, name FROM Nation) nation ON ship.nation_id = nation.id;
Basically each nation has an emoji I have on a server, not sure if I should store the emoji ID on the database too, or also return the nation.id and then fetch the emoji ID on the bot itself. Using name is not a good idea since I could be translating the string eventually and so on.
ancient fiber
Can I use fetchall in sqlite3 but get the results as result not as ('result',)?
zinc maple
https://docs.python.org/3/library/sqlite3.html#sqlite3.Connection.row_factory will help you give some changes to how you can parse the results @ancient fiber
zinc maple
yeah this works like a charm
def dict_factory(cursor, row): return {col[0]: row[idx] for idx, col in enumerate(cursor.description)}
noble oak
@commands.command(hidden = True)
@commands.is_owner()
async def blacklist(self, ctx, member: discord.Member = None):
if member is None:
return await ctx.send("Give me a user to blacklist next time")
db = psycopg2.connect(
database = "welcome",
user = "postgres",
password = PASSWORD,
host = "localhost",
port = "5432"
)
cursor = db.cursor()
cursor.execute(f"SELECT user_id FROM blacklists")
result = cursor.fetchone()
if result is None:
sql = ("INSERT INTO blacklists(user_id) VALUES(%s)")
val = (member.id)
await ctx.send(f"{member.id} ({member}) was blacklisted from using the bot")
elif result is not None:
await ctx.channel.send(f"{member.id} ({member}) is already blacklisted. Unblacklist him with the `unblacklist` command.")
cursor.execute(sql, val)
db.commit()
cursor.close()
db.close()```I'm trying to make a blacklist command so basically if I do `.blacklist <user>` it will not let them use the bot but for some reason this isn't inserting the member I chose into the database. But I think it's the `db.commit()` that isn't workingI stuck loads of prints in it and it stopped right after the cursor.execute()
dawn pulsar
I'm currently using mysql-connector, what should I use in an asnyc setting? Because my current connector blocks right?
minor ruin
latent comet
Hello.
In my PostgreSQL database I have a table named "guilds" which has one column named 'guilds', which is of value json. I'm trying to insert a json data into that column, but it doesn't seem to work, as when I try to fetch the result after performing the insert, I receive no results. Here is some code.
x = {'hi': 'test'}
data = Json(x) # (from psycopg2.extras import Json)
sql = ("INSERT INTO guilds (guilds) VALUES (%s)" % data)
cur.execute(sql)
rows = cur.fetchall() # psycopg2.ProgrammingError: no results to fetch
print(rows) # error.....^
I also tried doing json.dumps(x) as a replacement for Json(x), but then I receive this error:
psycopg2.errors.SyntaxError: syntax error at or near "{" LINE 1: INSERT INTO guilds (guilds) VALUES ({"hi": "test"})
regal snow
??
latent comet
@regal snow you are using two '?'s in your sql statement but you are passing 3 values to the 'val', which is one more
regal snow
so I should add 1 more ?
latent comet
no you should remove 1 value.
or you could also add but then you would need to add one more '?' to the sql variable
the val variable has 3 things, the sql variable has two placeholders. One less than the val.
regal snow
which value do I remove
latent comet
I think the second one, message.guild.id, but that's your code and you decide what to remove and add.
regal snow
k
@latent comet I looked at the tutorial I was following and saw the mistake. I fixed it but now it shows this https://hatebin.com/ypdzktaywq . Heres the new codehttps://hatebin.com/ssrladavvc
regal snow
sqlite
valid cobalt
or is it psycopg2?
valid cobalt
Hm okay
db = sqlite3.connect('database.sqlite')
cursor = db.cursor()```@regal snow I'm kinda new to sqlite and stuff
mild haven
@valid cobalt sqlite works fine, but I'd recommend PyMySQL or PostgreSQL
spark stone
hello there, im trying to create sqlite table where i have a unique id at the first column and index by that column, i tried auto_increment but its not incrementing anything, i get row like (None, ...).
and can index*
bold pelican
How to get a list of values in fetch val?
valid cobalt
@mild haven i'm using asyncpg
molten echo
bold pelican
How to use emojis in database?
ancient fiber
c.execute("INSERT INTO credits(member_id, credits, member_name) VALUES(?,?,?)", (id, str(0), message.author))```
Command raised an exception: InterfaceError: Error binding parameter 2 - probably unsupported type.
how to fix this error?message.author is a discord.Member, and your db lib doesnt know how to convert it to a string
so use message.author.name, str(message.author), message.author.id, etc
depending on what you need
versed robin
How hard would it be to convert the current programme that connect to a Mysql database to a SQLITE3 db as I have some current issues with the pyodbc connection??
brazen charm
Just use postgres
lost quarry
how can i search if x object is in a table and return the value?
toxic rune
@versed robin It all depends whether you're using specific stuff related to that engine/database. In most cases I assume that's what would hold you back. For example if I ever wanted to port one of my databases to SQLite3 I would need to revamp them completely since as far as I'm concerned SQLite3 doesn't support JSONB, etc.
versed robin
@toxic rune - Thanks for the response, but it it is just normal SQL statements the job would not be so huge
toxic rune
Then it probably wouldn't be much work then.
dawn pulsar
This is a long shot, but I tried something like this, anyway I could make it work?
Table is made of staff, name, time, duration
"SELECT * FROM (SELECT name, time, duration FROM support_sessions WHERE staff = %s ORDER BY time DESC LIMIT 1) AS session, \
(SELECT COUNT(*) FROM support_sessions WHERE time > session.time AND staff = %s) AS nth_session, \
(SELECT COUNT(*) FROM support_sessions WHERE duration > session.duration AND staff = %s) AS nth_longest, \
(SELECT COUNT(DISTINCT name) FROM support_sessions WHERE time < session.time) AS distinct_helped, \
(SELECT AVG(time)-time FROM support_sessions WHERE time=session.time) AS average_change", (staff, staff, staff))
I'm trying to get their longest session, the row that the longest session was in the list, the rank of how long it was (e.g. 24th longest), the number of new people before that, and the change from their average time
But I'm not sure if I can just mush it all in one query
Is this a complete no go?
pure cypress
You could mush them into one query as subqueries, but I don't think there's much benefit in that
Or using joins
dawn pulsar
Could you help direct me in how I would do that? I thought that was what I was doing?
Or joins?
@pure cypress
pure cypress
Ah you're right, you are using subqueries
It's just hard to read
Err maybe
The first one is a subquery
The rest look like they're just, separated by commas, which isn't correct afaik
pure cypress
select ... from (select ... from (select ... from (and so on...)))
dawn pulsar
Hmm
pure cypress
Joins are probably better
dawn pulsar
But then
Surely
I'd need to get narrower and narrower
Because if I select * from x WHERE conditon, all the ones before that will have that condition right?
dawn pulsar
So I can't do that in this case?
I thought joins where 2 different tables?
And what do I join ON
pure cypress
No, you can do a self join
dawn pulsar
Soooo
pure cypress
I was only familiar with JOIN syntax
pure cypress
Well what's even wrong with your query
dawn pulsar
Well
I can either exclude the first * FROM
And get
pymysql.err.InternalError: (1241, 'Operand should contain 1 column(s)')
Or include it and get
pymysql.err.InternalError: (1054, "Unknown column 'session.time' in 'where clause'")
"SELECT * FROM (SELECT name, time, duration FROM support_sessions WHERE staff = %s ORDER BY time DESC LIMIT 1) AS session, \
(SELECT COUNT(*) FROM support_sessions WHERE time > session.time AND staff = %s) AS nth_session, \
(SELECT COUNT(*) FROM support_sessions WHERE duration > session.duration AND staff = %s) AS nth_longest, \
(SELECT COUNT(DISTINCT name) FROM support_sessions WHERE time < session.time) AS distinct_helped, \
(SELECT AVG(time)-time FROM support_sessions WHERE time=session.time) AS average_change", (staff, staff, staff))
Seems like it doesn't support referencing previous results
dawn pulsar
Right
I thought I've seen that before, but if that doesn't work, what do I do now? have even more subqueries
WHERE duration > (SELECT duration FROM ...
I mean, theoretically, this works?
a = get_data("SELECT * FROM (SELECT name, time, duration FROM support_sessions WHERE staff = %s ORDER BY time DESC LIMIT 1) AS session, \
(SELECT COUNT(*)+1 FROM support_sessions WHERE time > (SELECT time FROM support_sessions WHERE staff = %s ORDER BY time DESC LIMIT 1) AND staff = %s) AS nth_session, \
(SELECT COUNT(*)+1 FROM support_sessions WHERE duration > (SELECT duration FROM support_sessions WHERE staff = %s ORDER BY time DESC LIMIT 1) AND staff = %s) AS nth_longest, \
(SELECT COUNT(DISTINCT name)+1 FROM support_sessions WHERE time < (SELECT time FROM support_sessions WHERE staff = %s ORDER BY time DESC LIMIT 1)) AS distinct_helped, \
(SELECT AVG(time)-time FROM support_sessions WHERE time=(SELECT time FROM support_sessions WHERE staff = %s ORDER BY time DESC LIMIT 1)) AS average_change", (staff, staff, staff, staff, staff, staff, staff))
But that's like 1504 requests in one
And horrible af
@pure cypress
nocturne dock
Is it good practice to write inline SQL?
Asking a question:
Is it good practice to write inline SQL vs. working with an ORM?
Queries can easily get super complex. Is it a good idea to mix in SQL to your .py files?
celest blaze
if it makes the code easier to understand, then yes. If not, then no.
pure cypress
@dawn pulsar I have to be afk, sorry I'll get back to this later
dawn pulsar
Ok sure, just ping me
pure cypress
Sure thing
opal dawn
So, i'm using mysql database and want to enable full text search on fields in certain tables.
My guess is that sqlachemy/flasksqlalchemy doesn't support that out of the box.
Wondering if I can implement FTS using raw SQL tables, and still use models for them.
pure cypress
@dawn pulsar here's an idea of how it would look with joins
SELECT
COUNT(nth_session.id) as "nth_session"
FROM support_sessions session
JOIN support_sessions nth_session
ON nth_session.time > session.time AND nth_session.staff = 'bob'
WHERE session.staff = 'bob'
ORDER BY session.time DESC;
And you just keep adding joins for the other queries you have
formal shell
Why I get this error and how can I fix that? I use discord.py library.
dawn pulsar
@pure cypress Thanks for the help so far!
It's going well
I have a small problem
This returns null values for some reason, know why?
Oops, I made some small changes
SELECT
session.name, session.time, session.duration, COUNT(nth_session.staff) AS "nth_session", COUNT(nth_length.duration) AS "nth_length",
COUNT(DISTINCT distinct_helped.name) AS "distinct_helped", AVG(average_change.duration)-session.duration
FROM support_sessions AS session
JOIN support_sessions AS nth_session
ON nth_session.time < session.time AND nth_session.staff = 'Generic Staff 1'
JOIN support_sessions AS nth_length
ON nth_length.duration < session.duration AND nth_length.staff = 'Generic Staff 1'
JOIN support_sessions AS distinct_helped
ON distinct_helped.time < session.time AND distinct_helped.staff = 'Generic Staff 1'
JOIN support_sessions AS average_change
ON average_change.staff = 'Generic Staff 1'
WHERE session.staff = 'Generic Staff 1'
ORDER BY session.time DESC;
Did you figure it out then?
lost quarry
how can i store data like the people that has permissions in my discord bot's sql?
pure cypress
And presumably 0 is incorrect too, right?
I can replicate this if I purposely sabotage one of the ON clauses to something which I know will never match
dawn pulsar
The zeros might be fine since itβs test data
But bull shouldnβt be a thing
Null
pure cypress
Well, I don't know. I'm sure this is possible I am just not skilled enough at SQL. I haven't really used it in 3 years
If you find a solution, I'd be interested in it. I got a little invested in the problem. SQL can be challenging π
stable pilot
i am trying to transfer my old database to my new dedicated serverr
this is the old server
error message
https://i.imgur.com/JqCEFLy.png
@here
robust quest
if i connected my sql server to mysql workbench, can i use python to connect to that sql server now
the sql server is not local
for python im using import mysql.connector
celest blaze
yes
databases in general can "talk to" many clients at once; mysql connector is a client, and your python program is also a client
also: try it and see π
robust quest
ive been trying
but i can't get it to work haha
so i used mysql workbench to connect to my sql server via ssh
which worked
now in python i don't know which info to put in
mydb = mysql.connector.connect(
host = "",
user = "",
passwd = "",
database = "",
)
i don't know what the host and user should be
@celest blaze
celest blaze
nor do I
lofty kelp
would questions about pyyaml fall into this channel?
raw island
anybody have a decent sql library?
raw island
ty
chrome vault
@commands.command()
@commands.has_permissions(manage_channels=True)
async def bind(self, ctx, channel: discord.TextChannel):
guild_id = str(ctx.guild.id)
channel_id = str(channel.id)
db = self.mydb()
try:
print(db)
mycursor = db.connect()
mycursor.execute("SELECT channel_id from channel WHERE guild_id ='" + guild_id + "'")
result = mycursor.fetchall()
print(len(result))
if len(result) == 0:
mycursor.execute("INSERT INTO channel(guild_id, channel_id) VALUES(%s, %s)", (guild_id, channel_id))
db.commit()
await ctx.send(f"Chat bot is bind to <#{str(channel.id)}>")
else:
mycursor.execute("UPDATE channel SET channel_id=%s WHERE guild_id=%s ", (channel_id, guild_id))
db.commit()
await ctx.send(f"Chat bot is bind to <#{str(channel.id)}>")
except Error as e:
print("Error while connecting to MySQL", e)
finally:
if (db.is_connected()):
mycursor.close()
db.close()
print("MySQL connection is closed")
why my values are not inserting or updating in my db
formal shell
Can enyone help me?
eternal raptor
@commands.command()
async def utworzprof(self, ctx, ID : discord.Member):
conn = sqlite3.connect('bazaNinjaSerwer.db')
c = conn.cursor()
sqlcommand = ("INSERT INTO ekonomia (ID, User, NinjaGold) VALUES (?, ?, 0)")
val = (ID.id, ID)
c.execute(sqlcommand, val)
await ctx.send("Utworzono profil!")
conn.commit()
conn.close()
Hi, i will try create a mini economy (private) bot.
How to repair??? When I Use command n/utworzprof @eternal raptor @1231231312:
above exception was the direct cause of the following exception:
Traceback (most recent call last):
File "C:\Users\user\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\user\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\user\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: InterfaceError: Error binding parameter 1 - probably unsupported type.
harsh pulsar
@eternal raptor the error message tells you what to do:
Error binding parameter 1 - probably unsupported type.
That means the 1th parameter has the wrong type
remember in python we start at 0
so "parameter 1" is the 2nd parameter
val = (ID.id, ID)
ID is an instance of discord.Member
it looks like you're trying to save the discord.Member object directly to the database -- since discord.Member is not a supported type, the operation fails
eternal raptor
error repaired
mossy blaze
Hello. I have
UPDATE money SET money = {amount} WHERE user_id = ("{member_id}")
How can I update the money variable to amount + money?
Sorry for this noobish question but I just started working in SQL.
harsh pulsar
@mossy blaze 1) use parameterized queries, 2) you can use math operations like +
db.execute('UPDATE money SET money = money + ? WHERE user_id = ?', (amount, member_id))
note that the ? placeholder syntax might be different depending on the database library you use
sly quail
Hello, im using Flask for a web app with sqlalchemy and sqlite for the database. I have established a many to many relationship between user and houses so i can store what houses a user has rented and who has stayed in a certain house. My question is if i can also establish a one to many relationship between user and houses to store what houses a user owns
harsh pulsar
@sly quail you can make a 2nd table for that purpose, or make a single table with a 3rd column stating whether the relationship is "own" or "rent"
there are probably other options as well
sly quail
right, but is it possible for 2 tables to have both relationships at once?
one to many and many to many
harsh pulsar
yes of course
however it could potentially be confusing
depending on how it's implemented and documented
noble oak
async def create_db_pool():
client.pg_con = await asyncpg.create_pool(database = "welcome", user = "postgres", password = PASSWORD)
async def get_prefix(client, message):
conn = client.pg_con
result = await conn.execute("SELECT prefix FROM prefixes WHERE guild_id = $1", message.guild.id)
if not message.guild:
return commands.when_mentioned_or(".")(client, message)
#result = cursor.fetchone()
if result is None:
return "."
else:
return result```I'm new to asyncpg and I am trying to fetch a prefix but it's not returning the prefix or any errors@pure cypress#3318
I tried it, the COUNT seemed to count on all joins, so even though thereβs now only 100 rows
The nth session returned 1914
But I have to go, please ping and Iβll respond when I see it or when Iβm done :)
harsh pulsar
@noble oak don't you need to .fetchall() the result?
harsh pulsar
ok asyncpg has nicer methods
noble oak
?
harsh pulsar
one moment
noble oak
Oh sorry
harsh pulsar
result = await conn.fetchval("SELECT prefix FROM prefixes WHERE guild_id = $1", message.guild.id, column=0)
@noble oak
note that fetchval is nonstandard and specific to asyncpg
noble oak
Oh thank you so much, it worked
gloomy wyvern
"A company wants to achieve a start up which referring to medical offices of a city, it provides document and meetings archiving."
guys can you help me to find all the entity for the database of this exercise? i found patient, document, and meeting but i think there are others hidden since it seems too easy in that way
keen sundial
hi friends. anyone know of a tool to read json and generate mongo engine classes?
dawn pulsar
Hey all, I'm trying to merge a few seperate requests into one, since latency is an issue but database speed is not, anyone know how I can do this, I tried join but COUNT returned the number for the same table 4 times, since I joined it with itself 3 times, my table is called support_sessions and has the columns staff, name, duration and time, I'd like to merge these fetches:
SELECT name, duration, time FROM support_sessions WHERE staff = %s ORDER BY time DESC LIMIT 1 AS THIS_SESSION
-- This is the base fetch, all of the rest of the fetches should use this fetches data, e.g. to get the n_th session done, it'll reference this and order it, this is the nth_session, I'm trying to find what n is, e.g. it's the 15th session by time
SELECT AVG(duration)-duration FROM support_session WHERE --Session = Above--
SELECT COUNT(*) FROM support_session WHERE time < THIS_SESSION.time AND staff = %s
SELECT COUNT(*) FROM support_session WHERE duration < THIS_SESSION.duration AND staff = %s
SELECT COUNT(DISTINCT name) FROM support_session WHERE time < THIS_SESSION.time AND staff = %s
oof, I always read %s as string formatting and my mental alarm bells fire.
@dawn pulsar is this pg, mysql?
You can create a server side function to do this stuff. It's often not done since managing queries is not the same flow as managing client code, but create or replace function getSessionCounts... and then do the work on the server.
a nice thing about it though is that you can jiggle the tables on the server and anyone using the function wont notice. where if you have multiple applications using the same db, then you have a difficult migration for clients to use refactored data model
formal shell
I can't do INSERT and I don't know why. if not res: working, because bot put reaction under command. Can enyone help me?
Code:
cur.execute(f"SELECT * FROM automod_ignore_channels WHERE guild_id={ctx.guild.id} and channel_id={channel.id}")
res = cur.fetchall()
if not res:
cur.execute(f"INSERT INTO automod_ignore_channels VALUES ({ctx.guild.id}, {channel.id})")
conn.commit()
await ctx.message.add_reaction('β
')
@keen sundial I'm using PyMySQl on a Maria Database
frozen fossil
i am trying to store the discord id in a mysql database, https://jelbrek.icu/kb6okqkz.png but im getting this
i already tried using int/bigint
carmine falcon
@frozen fossil double check that the python value you're putting in is an int, and you're formatting the query correctly?
frozen fossil
yes the discord userid is always a integer
carmine falcon
this?
atomic coral
Mongo database:
{"_id": id, "list": [1, 2, 3]}
collection.update("list": 4)```
How could I append "list"?
fringe tiger
Not experienced in what's the best aproach.
Should I directly iteract with models from my code and write statemets like await Guild.get(id=guild_id).update(custom_prefix=prefix) where I need them or should I make some kind of middle man file that would have function change_guild_prefix and then call that?
Django/Tortoise ORM in question.
noble oak
await conn.execute("DELETE channel_id, msg FROM welcome WHERE guild_id = $1", ctx.guild.id)```How do you delete channel_id AND msg from a table
hazy mango
async def execute_sql(self, *statement, index=None):
async with self.database_pool.acquire() as connection:
tr = connection.transaction()
await tr.start()
try:
cur = await connection.cursor(*statement) # execute statement
except Exception as e:
await tr.rollback() # undo any changes made
raise e
else:
if statement[0].upper().startswith("SELECT "): # statement is a SELECT query so we want to return values
if index is not None:
return (await cur.fetchrow())[index] # return specified index of the first record
return await cur.fetchrow() # return the first record
## statement isn't a SELECT query so we need to commit
print("Committing..")
await tr.commit()
@commands.Cog.listener()
async def on_message(self, message):
if message.author.bot or message.is_system() or not message.guild:
return
await self.bot.execute_sql("INSERT INTO messages VALUES ($1, $2, $3, false, $4)", message.id, message.author.id, message.channel.id, [message.content])
print("Stored message info")
```Why doesn't this update the postgreSQL database? No errors and I get the message saying "committing" and "Stored message info" but when looking at the table it remains emptymaybe use a context manager>
hazy mango
If I do py async with connection.transaction():then I can't commit
brazen charm
it auto commits
async with c:
start and commit/rollback the transaction or savepoint block automatically when entering and exiting the code inside the context manager block.
It didn't tho, I've already tried that
hazy mango
async def execute_sql(self, *statement, index=None):
async with self.database_pool.acquire() as connection:
async with connection.transaction():
cur = await connection.cursor(*statement) # execute statement
if statement[0].upper().startswith("SELECT "): # statement is a SELECT query so we want to return values
if index is not None:
return (await cur.fetchrow())[index] # return specified index of the first record
return await cur.fetchrow() # return the first record```If you commit and then immediately fetch the contents does it still remain unchanged?
brazen charm
ig try select just after commit
and see
if it isnt updated something is really borked
looking at sources you could always try manually sending the commit query
hazy mango
async def execute_sql(self, *statement, index=None):
async with self.database_pool.acquire() as connection:
tr = connection.transaction()
await tr.start()
try:
cur = await connection.cursor(*statement) # execute statement
except Exception as e:
await tr.rollback() # undo any changes made
raise e
else:
if statement[0].upper().startswith("SELECT "): # statement is a SELECT query so we want to return values
if index is not None:
return (await cur.fetchrow())[index] # return specified index of the first record
return await cur.fetchrow() # return the first record
## statement isn't a SELECT query so we need to commit
print("Committing..")
await tr.commit()
tr = connection.transaction()
await tr.start()
cur = await connection.cursor("SELECT * FROM messages")
print(await cur.fetchrow())
await tr.commit()
the print(await cur.fetchrow()) outputsNone
So I'm guessing that means it really isn't committing?
@brazen charm
hazy mango
π€
hazy mango
So await connection.cursor("COMMIT;")?
Nope, that also gave None
And the table remains empty
@brazen charm
brazen charm
Thats really fucking confusing
hazy mango
Yea lol
brazen charm
just as a test
maybe try create a brand new connection rather than getting it from the pool
harsh pulsar
don't you need to async with conn.transaction():?
oh i see you're trying to manually control the rollback behavior
with start/stop
well yeah it looks like you never stop() the transaction
hazy mango
Honestly I don't care about the rollback
harsh pulsar
and you have nested transactions?
in that case just use the async context manager and save yourself the trouble
hazy mango
The function only ever executes one query
So rollback isn't an issue
But the async manager doesn't auto-commit
It should, but it doesn't
harsh pulsar
what is statement?
hazy mango
That's what I used first of all, realised it wasn't committing so tried the other way
"INSERT INTO settings (guild, prefix) VALUES ($1, $2)", guild.id, ['~']
Wait that's the wrong one
harsh pulsar
this is asyncpg right?
hazy mango
"INSERT INTO messages VALUES ($1, $2, $3, false, $4)", message.id, message.author.id, message.channel.id, [message.content] is my statement here
and yes, asyncpg
harsh pulsar
just want to triple check
ok great
async def execute_sql(self, *statement, index=None):
async with self.database_pool.acquire() as connection:
async with connection.transaction():
cur = await connection.cursor(*statement)
if statement[0].upper().startswith('SELECT'):
output = cur.fetchrow()
if index is not None:
output = output[index]
else:
output = connection.fetchrow('SELECT * FROM messages')
print(output)
how about something like this @hazy mango
hazy mango
It doesn't commit
When I do async with ... it doesn't commit
I know it's supposed to auto-commit but it doesn't
https://discordapp.com/channels/267624335836053506/342318764227821568/719630140870754366 is what I had when using context manager to be specific
I don't see how doing what you put would be any different
brazen charm
I havent used pgadmin for a while but can you log incomming querys
and see if the db is even picking up the querys
harsh pulsar
ah i see
i wonder if it has to do with re-using the cursor
i think this whole "one size fits all" approach to execution is a little weird
you have fundamentally different semantics between DDL stuff (eg INSERT) and SELECTs
hazy mango
Only thing different is SELECT I want to return the values and others I don't (since there's nothing to really return)
hazy mango
I'm probs gonna do something for allowing multiple rows but rn I only ever get one row
Cause I'm doing queries like WHERE guild=guild_id or WHERE message=message_id where there's only ever one match
Only one match so only return first row (aka the match)
harsh pulsar
what about something like this
def _sql_is_select(query):
return query[0].lstrip().upper().startswith('SELECT')
class MyThing:
async def _execute_sql_select(self, query, *parameters, index=None):
async with self.database_pool.acquire() as connection:
if index is not None:
output = connection.fetchval(query, *parameters, column=index)
else:
output = connection.fetchrow(query, *parameters)
return output
async def _execute_sql_ddl(self, query, *parameters):
async with self.database_pool.acquire() as connection:
async with connection.transaction():
await connection.execute(query, *parameters)
async def execute_sql(self, query, *parameters, index=None):
if _sql_is_select(query):
output = await self._execute_sql_select(query, *parameters, index=index)
else:
output = await self._execute_sql_ddl(query, *parameters)
print(output)
this way you aren't messing around with cursors
and you have 2 different functions for your 2 unrelated code paths
rather than trying to shoehorn 2 different ideas into 1 code path
at least now you can narrow it down to "asyncpg is behaving unexpectedly" or "i made a mistake"
hazy mango
Yea I see what you want
Thanks
That appears to working perfectly, tyvm @harsh pulsar :~)
frozen fossil
how can i dump the schema file from a .db file?
torn sphinx
data = (message.id,
message.author.id,
message.clean_content,
message.created_at.strftime('%y/%m/%d'))
c.execute("INSERT INTO List VALUES(?,?,'?','?')", data)``` Am I doing it wrong?@torn sphinx remove the quotes around the ?s
the point of using ? and not an f-string or .format or % is that the library does the quoting for you
also what is the intended data type of the created at field?
torn sphinx
Even without the quotes it doesn't work. created_at returns a datetime object.
In this case it's a string.
round turtle
can pymongo and mongoengine code be mixed together in the same python script?
torn sphinx
what is partitioning and what is indexing
carmine heart
Say that you have a really long unordered list of names and you need find all records with the name "John"; you'd have to scan the entire list of names to see where the Johns are. Without a clever solution, like an index, that's what you're database application needs to do each time you try to select a specific piece of data from the table. To speed that up, there's something called on index which indexes in some way (a common way are b-trees) which data is where in the table. (It's typically compared to an index in a book; if you're looking for a specific term in a book, you look up the term in the index and just go to the page instead of reading the entire book until you find it. The actual method used obviously differs internally, but that's what it is: A quicker way to locate information.)
Obviously it takes a bit of time to maintain such an index and each time you add a new entry, the index needs to be kept up to date. That means there are some overhead costs associated with having an index, but it helps you with look-ups.
Partitioning is taking your database and dividing it up in into smaller parts/databases. I don't have a lot of experience with that, so I can't give you a better story than wikipedia.
torn sphinx
hmm partitioning sounds like sharding, I'll look it up to see how it's different
if indexes speed up look ups, does it mean everything under an index has to be unique? like can there be multiple "John"s
carmine heart
There's no uniqueness requirement; it's not actually an index like in a book. That's just an analogue often used to describe what's going on.
In reality, you get search trees that essentially allow you to make easy decisions on which path to follow to get to the actual record information
Think of structuring your data in a binary tree, ordered on the first name. The first layer of the tree is "Are you looking for someone with a first name after of before this comparison node?". Then you do the same at the second layer, third layer, until your binary search completes.
I think the commonly used b-trees are a bit more complex than just a simple binary tree search (haven't looked at the implementation too much), but that's about what's going on
torn sphinx
wow.. that's a nice analogy
and that's how I'll remember it.. index of a book
oh,. wait
I just read that again, you mean it's not like an index of a book
dang
ok I need to understand b trees
polar radish
this is crapping itself after like
3 iterations for some reason
any ideas why?
nevermind
i figured out why
restive linden
Anyone here know of a good video where i can learn about SQL databases? (Im new to databases)
torn sphinx
restive linden
thx @torn sphinx
regal dagger
hello i have a few doubts
suppose im planning on hosting a bot online 24/7 and i have a postgres db
currently i am hosting my bot locally from my pc and connect to my postgres db with this line```py
async def create_db_pool():
bot.pg_con = await asyncpg.create_pool(database='',user= '', password='')
bot.loop.run_until_complete(create_db_pool())```
will i still be able to connect to the db if i put my bot files in a hosting platform in the same way?
atomic coral
Mongodb database:
{"_id": id, events: {"10/06": "some event"}}```
How can I add something to the dict `events`?
strong compass
guys is there anyway i can host mysql db on my own pc and use it on another pc that connected to the same lan as me ?
gleaming quest
Why my code:py with connect(db_url, sslmode='require') as conn: cur = conn.cursor() result = cur.execute('SELECT * FROM warns WHERE uid = %s', [uid]) cur.close() return result Returns always None?
When I'm doing it by myself, with same argument - it's OK
frozen fossil
guys is there anyway i can host mysql db on my own pc and use it on another pc that connected to the same lan as me ?
@strong compass run ipconfig if youre on windows and instead of using localhost as your host use your ip
@gleaming quest are you sure its [uid] and not (uid,)
strong compass
@frozen fossil
i get this error
mysql.connector.errors.InterfaceError: 2003: Can't connect to MySQL server on 'leaguebots.ddns.net:3306' (10061 No connection could be made because the target machine actively refused it)
frozen fossil
@strong compass maybe the port isnt open?
frozen fossil
im not familiar with windows and their firewall
torn sphinx
Hello, I have the following query:
CREATE TABLE {guild} (guild_id TEXT(20), user_id TEXT(20), user_name TEXT(40))
This works just fine, but after the bot has joined one server and then if it joins another, the query returns a syntax error.
Can someone tell me why?
don't use string operations
c.execute("SELECT * FROM stocks WHERE symbol = '%s'" % symbol)
use this format
or better yet use a second parameter that .execute() takes
cursor.execute("SELECT * from ? WHERE name = ?", [table_name, name])
as another example
this time question marks
yea, I think I got it. thanks
I'll give it a try
np
eternal raptor
@commands.command(pass_context=True)
@commands.has_role('ADMIN')
async def ukaraj(self, ctx, member: discord.Member, reason = None):
memberrr = str(member)
if reason == None or memberrr == None:
await ctx.send("``` Podaj powΓ³d lub uΕΌytkownika i sprΓ³buj ponownie! ```")
else:
memberrid = member.id
embed = discord.Embed(title='Sukces!', description=f'Ukarano uΕΌytkownika {memberrid}, powΓ³d: {reason}')
await ctx.send(embed=embed)
conn = sqlite3.connect('bazaNinjaSerwer.db')
c = conn.cursor()
c.execute("INSERT INTO baniki (ID, User, reason) VALUES (?, ?, ?)", (memberrid, memberrr, reason))
conn.commit()
conn.close()
await ctx.member.ban(memberrid)
Ignoring exception in command ukaraj:
Traceback (most recent call last):
File "C:\Users\user\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\user\Desktop~BOT~\cogs\warns.py", line 23, in ukaraj
c.execute("INSERT INTO baniki (ID, User, reason) VALUES (?, ?, ?)", (memberrid, memberrr, reason))
sqlite3.IntegrityError: UNIQUE constraint failed: baniki.ID
The above exception was the direct cause of the following exception:
[...]
File "C:\Users\user\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: IntegrityError: UNIQUE constraint failed: baniki.ID
torn sphinx
kinguard, I did
'CREATE TABLE %g (guild_id TEXT(20), user_id TEXT(20), user_name TEXT(40))' % guild
but now it returns a TypeError: must be real number, not Guild
harsh pulsar
@torn sphinx you are trying to pass a sophisticated object, but it should be a string or a tuple of strings
%g is also not what you want
eternal raptor
@harsh pulsar could you help me?
atomic coral
{"_id": id, "events": {"12/05": ["some event", "some other event"], "15/04": ["birthday"]}}
If this is my mongo db for example, could anybody show me how I can check if a certain date is already in events and if it is I add something to that list, but if it isn't I create a new date with that event.
harsh pulsar
@eternal raptor please don't ping people like that. look at the error message - can you guess the problem?
eternal raptor
@harsh pulsar i have a problem with this problem and i cannot guess the problem :/
eternal raptor
something went wrong
craggy holly
Would it be a bad idea to use collision resistant hashes as a primary key? I am trying to get a "reproduceable ID" so that if an input string is the same, the output ID is the same.
runic pilot
primary keys are made to denote the identity of a row, and if you have a way to get a "reproducible ID", you should just use that as the PK IMO
alternatively, you could put a unique constraint on the hash column
I have a table with no PK and just a unique constraint on a column that represents the "unique fingerprint" of the data in that row
craggy holly
Right, I see. That would make more sense for my use which would be to identify what type of item a record is dealing with for a school project
torn sphinx
@harsh pulsar Nothing gets inserted.
torn sphinx
Transactions are record-wise operations, doing a look up on a record for some info.. usually the entire row. Analytical is several rows but limited number of columns..
viscid ridge
yall all super smart im learning base
torn sphinx
ACID, BASE you mean?
hazy mango
await self.bot.execute_sql("UPDATE messages SET contents=contents||'{$1}' WHERE message_id=$2", content, message_id)
```This gives me an error stating that postgresql `could not determine data type of parameter $1`. `content` is a string, so how would I declare it as such?is it because of the ''
hazy mango
The reason I have '' in the '{$1}' is so postgresql realises it's an array
If I do just {$1} it says syntax error at or near "{"
torn sphinx
would '{'||'$1'||'}' work
hazy mango
So contents=contents||'{'||'$1'||'}'?
@torn sphinx
That gives asyncpg.exceptions.InvalidTextRepresentationError: malformed array literal: "{" DETAIL: Unexpected end of input.
hazy mango
Doing contents=contents||ARRAY[$1] seems to have worked
Not sure why that works and '{$1}' doesn't but π€·
torn sphinx
nicee
bold pelican
I need urgent help
Any one help me?
Hello {member.mention}, welcome to {server.name} server. There are {server.member_count} members. You're {member.join_count}
This is my welcome message
dawn pulsar
Right?
bold pelican
my table has
dawn pulsar
You need to show more of your code
bold pelican
welcome_message text[]
How can I save the string with those curly brackets?
Without escaping those characters
No code
I am using PyCharm connected Postgres
@dawn pulsar
eternal raptor
@harsh pulsar
pliant cliff
What's a good database to use for like... general purpose?
wanton grove
im getting this error:
discord.ext.commands.errors.CommandInvokeError: Command raised an exception: ProgrammingError: 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version
for the right syntax to use near '576862390629695518 (guildname VARCHAR(255)' at line 1
```when i try running this code:
cursor.execute(f"CREATE TABLE {guild.id} (guildid VARCHAR(255))")
cursor.execute(f"CREATE TABLE {name} (temp VARCHAR(255))")
@pliant cliff storing data
pliant cliff
???????
wanton grove
mysql
pliant cliff
ah... gotcha
wanton grove
or mariaDB or smth
raven cargo
Is there anyone that is decently experienced with Redis Here?
I'm working on a discord bot, where I want my bot to run different configurations depending on the server that is executing the commands (server id works as key).
So, I'm creating a web application where you should be able to configure said settings for your server, and these server changes will be communicated over Websockets to my bot.
So, this data will be placed in a postgres database, but I also want the data to be even faster to access (Redis), so I don't have to make a database call for every message that is typed, and it can easily do it through Redis instead.
So my question I guess is, if there is a good method to make some sort of "relational" system in redis. I've seen people use they keys such as like guild:{id}:commands for instance, to get all the commands for a certain guild (server). Is there any other better practice when it comes to this?
Or is it better to just keep like, 1 key for each server, and instead use some JSON format and serialize / de-serialize all the settings for said server
That way since I store it in postgres too, all I have to do is load the data from postgres into redis on boot, and then it should sail smoothly from there
wanton grove
since {guild.id} returns numbers, i changed it to
cursor.execute(f"CREATE TABLE `{guild.id}` (guildid VARCHAR(255))")
```and that fixed my issue @commands.command(pass_context=True)
@commands.has_role('ADMIN')
async def ukaraj(self, ctx, member: discord.Member, reason = None):
memberrr = str(member.name)
if reason == None or memberrr == None:
await ctx.send("``` Podaj powΓ³d lub uΕΌytkownika i sprΓ³buj ponownie! ```")
else:
memberrr = str(member.name)
conn = sqlite3.connect('bazaNinjaSerwer.db')
c = conn.cursor()
c.execute("INSERT INTO baniki (ID, User, reason) VALUES (?, ?, ?)", (member.id, memberrr, reason))
conn.commit()
conn.close()
ctx.guild.ban(member)
embed = discord.Embed(title='Sukces!', description=f'Ukarano uΕΌytkownika {member}, powΓ³d: {reason}')
await ctx.send(embed=embed)
when I this ban command: n/ukaraj @example example_reason:
Ignoring exception in command ukaraj:
Traceback (most recent call last):
File "C:\Users\user\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\user\Desktop~BOT~\cogs\warns.py", line 21, in ukaraj
c.execute("INSERT INTO baniki (ID, User, reason) VALUES (?, ?, ?)", (member.id, memberrr, reason))
sqlite3.IntegrityError: UNIQUE constraint failed: baniki.ID
await injected(*ctx.args, **ctx.kwargs)
File "C:\Users\user\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: IntegrityError: UNIQUE constraint failed: baniki.ID
@harsh pulsar
zinc maple
the ID ur trying to insert already exists in there
if the ID already exist what would you like to do in this case? just update the existing user with new info @eternal raptor ?
you probably want to figure out why you're trying to ban a user that's already in your ban table
zinc maple
@bold pelican is it fstring ur looking for?
bold pelican
nope
zinc maple
if you put f before "string content" it lets you slip in local vars like print(f"hello {name}")
zinc maple
ah
bold pelican
I use str.format
zinc maple
I believe format uses like %s for its replacements right?
zinc maple
ooh right
I'm gonna make a guess that it would have to be something like {member_joined_at}".format(member_joined_at=member.joined_at)
maybe?
zinc maple
just woke up + bit of a guess but
bold pelican
But
zinc maple
oh it works with the .var like that? good to know
zinc maple
what db is this postgres?
I haven't had a chance to work with postgres yet, I'm trying to do some search, it seems to use curly braces internally a lot so maybe you need to escape them somehow (?)
a quote from the docs I found >You can put double quotes around any element value, and must do so if it contains commas or curly braces.
zinc maple
π
zinc maple
all good
yeah I figured, took me some time to find anything
I also saw someone do double curly braces
bold pelican
oh
zinc maple
to escape it like {{Hello ...
zinc maple
okok
bold pelican
anyway it is workin' now
zinc maple
π
bold pelican
π
torn sphinx
output = '{ "suggestions":None, "logs":None }'
out = json.loads(output)
why does this give me the error: json.decoder.JSONDecodeError: Expecting value: line 1 column 17 (char 16)
rich trout
!e ```py
print('{ "suggestions":None, "logs":None }'[15:18])
@rich trout :white_check_mark: Your eval job has completed with return code 0.
:No
None is not valid json
null is the correct replacement I believe
!e ```py
import json
print(json.loads('{ "suggestions":null, "logs":null }'))
@rich trout :white_check_mark: Your eval job has completed with return code 0.
{'suggestions': None, 'logs': None}
seems like it serializes* None to null and back to None except for key names which become string 'null'
mossy blaze
Hello, I'm using MySQL connector in Python and I was wondering how I can check if a column is empty?
atomic coral
does Mongodb works with Heroku?
atomic coral
oh so I can't just use the same code
runic pilot
what do you mean?
runic pilot
ok, here's something that took me a while to learn: your application code (likely python) will run in a different place than your database (mongodb, for example)
so all that needs to happen is those two things talk to each other
atomic coral
oh ok
runic pilot
they can run on the same server, but that's generally not how it works for any sizable use case
atomic coral
So I don't just push my python file in Heroku?
runic pilot
you can, and that will work
atomic coral
Like I did before using mongodb?
runic pilot
where is the mongo server your python code is connecting to?
atomic coral
what do you mean?
runic pilot
show me a couple lines of your code that instantiates the mongo connection and I'll explain
atomic coral
cluster = MongoClient("link to my cluster")
db = cluster["Discord_bot"]
collection = db["botdb"]```
this?pymongo
runic pilot
runic pilot
so by default the client tries to connect to the mongodb server running at localhost:27017
yes, everything you have is right so far
atomic coral
ok
runic pilot
so if you're running this bot on your computer, you should also be running a mongodb server running that it can connect to
is that the case?
runic pilot
ok, let's back up a step
atomic coral
yeah sorry I don't know anything about this
runic pilot
when you asked if heroku could connect to mongo were you asking because you have something working with mongo already and were asking if it would work on heroku?
atomic coral
Yes, if I just run my code on PyCharm, the bot works
runic pilot
and it connects to mongo and everything?
atomic coral
yes, I can add/delete things in my cluster using commands on discord
runic pilot
ok so you just need a remote server and to connect to it then
since I'm assuming you don't want to run this bot on your computer forever, you'd rather have a remote server (heroku) do it
atomic coral
yes that was the reason I used heroku
runic pilot
ok perfect, we're on the same page
atomic coral
I used to store my data in a JSON file but aparently that doesn't work on heroku
runic pilot
but you probably hit a snag: heroku doesn't have a way to connect to your machine to contact the database
atomic coral
ok
runic pilot
eh, yeah it's also not great practice to use a JSON file as your database
and it's good that heroku can't connect to the mongo server you're running on your computer- we don't want to expose that to the public internet
atomic coral
k
runic pilot
so instead you need to create a remote mongo db, in the same way heroku is a remote app server for your code
atomic coral
alright
runic pilot
I personally don't have experience with rolling a mongo server in production, but this heorku add-on is that the top of the list for mongo https://elements.heroku.com/addons/mongolab
and it looks like it's free for up to half a gig worth of data
runic pilot
yeah I think that should do it for you
I do a similar thing with the postgresql extension
atomic coral
App to provision to what do I put here?
runic pilot
you need to create a heroku app first
atomic coral
I already have one
runic pilot
ok, select that app
torn sphinx
Quick question to SQL pros: is it actually safe to use Enums, so I can use some variable as table name? For example, something like this ```py
class MyType(Enum):
todos = 'todos'
def some_io_function(argument: MyType, id: int):
query = 'SELECT * FROM {} WHERE ID = $1'.format(argument.value)
return database.fetch(query, id)
ohnvm
runic pilot
just type in the name of the heroku app like this https://cl.ly/605b2c233107
runic pilot
@torn sphinx it's totally safe, but you should override the __str__ and __repr__ methods to make sure they return what you want
atomic coral
zinc maple
I do this a fair bit tho, just saving the .value and then casting it back to MyEnum(int)
bit*
runic pilot
ah yeah, I hear they made that blocker for a lot of things now
torn sphinx
@runic pilot nah I actually dont really need the returned value, just making sure it's sql-injections safe :D
atomic coral
I can't verify a credit card
zinc maple
you can check if value in myenum.value2member_map for some sanity
runic pilot
heroku takes the credit card, looks like these guys offer the 512mb db for free
zinc maple
or any(v in (val.value for val in myenum.members.values()) I read its better practice* but its a bit less nice looking
atomic coral
https://www.mongodb.com/atlas-signup-from-mlab
@runic pilot I already have an account on mongodb
runic pilot
oh great, then you can probably create a free database from there
atomic coral
but I'm on heroku if I do Provision add-on?
runic pilot
if you create the db through heroku they just do a couple things for you that you can easily do manually
atomic coral
so my database exists already right?
runic pilot
Β―_(γ)_/Β―
atomic coral
I used it before I tried connecting my bot with heroku
runic pilot
you'll have to tell me, I don't know what's in your mongo account
atomic coral
yeah I already have a cluster
runic pilot
great, then yeah you should be good to go, just use that db uri
atomic coral
uri?