like, usually spark is used to distribute work across workers, but in standalone mode it's on one core
#databases
torn sphinx
shell ocean
ah, you mean that wa
y
well, I think it should be fine
by the way, you can have Spark use all your cores
torn sphinx
I know, but I want to restrict it when I want to offer it as a service
also, how do you do indexing when creating a table
shell ocean
what do you mean?
torn sphinx
like, you make some column as the index and that enables faster querying right?
so an index is like.. a primary key I guess
so how do you specify which column to index by, when you create the table
just mention it as primary key?
or is it something else
shell ocean
are you talking about SQL?
shell ocean
depends on which
but anyway you don’t have to create indices @ the start
primary key is always indexed IIRC for performance
steady epoch
Which db should I learn I am a beginner
calm charm
guys I have a question regarding SQlite.
steady epoch
Ok can I get guidance
calm charm
nvm
void otter
learn basic queries and table manipulations
calm charm
hello
How do I insert a value to all existing members?
Ik how to insert
but how do i do it to tall existing members
steady epoch
U can do if value of your member is not null then insert or something like that
calm charm
.test
wait mb
questiooon
anyone know why: py @client.command() async def give(ctx, member: discord.Member, amount: int): connection = sqlite3.connect("testdatabase.db") cur = connection.cursor() cur.execute(f"SELECT currency FROM currency_value_table WHERE member_id = {ctx.author.id}") cur.execute(f"UPDATE currency_value_table SET currency = currency - {amount} WHERE member_id = {ctx.author.id}") record = cur.fetchone()[0] results in File "discord.bot.py", line 363, in give record = cur.fetchone()[0] TypeError: 'NoneType' object is not subscriptable
@steady epoch
also would it be something like this: ``pu
connection = sqlite3.connect("testdatabase.db")
cur = connection.cursor()
purchases = [(member.id),'100']
for guild in bot.guilds:
for member in guild.members:
cur.executemany('INSERT OR IGNORE INTO currency_value_table (?,?)', purchases)
connection.commit()
connection.close()
cerulean pendant
@calm charm you can't use fetchone in a UPDATE command
it doesn't return anything
calm charm
then would i put the SELECT BEFORE UPDATE
cerulean pendant
in your second code you need a list of tuples with two elements to use executemany so:
purchases = [(member.id, '100')]```so only execute?
cerulean pendant
@calm charm you can put in any way you want, but if you use execute with UPDATE then you can't do fetchone(). you can do fetchone() only after SELECT
cerulean pendant
for the second code, if you change the list to what I provided it will work with execute many
but if you want ot insert only one row then why use executemany
it is for when you want to do multiple rows at the same time
rain wagon
walkda, you need to understand something first
there are statements in SQL, called DML
Data Manipulation Language
Those are commands that change data, such as UPDATE, INSERT
when you do those, you need to call commit() and you don't get a result
if you want a response, as in, data, you need to call result = cursor.execute("SELECT ..") and then evaluate the data first
calm charm
you call commit() amd you dont get a resukt?
rain wagon
commit writes the data to the db
the only thing you get is the number of rows affected
Also, when you call execute, you need to get the new cursor
calm charm
yeah so in my code, I want to give all existing members in the guild a currency of 100
cerulean pendant
(if it is a SELECT)
calm charm
ic
cerulean pendant
otherwise you can't fetch
rain wagon
yes
btw, you don't need to walk all members if you want to give it to all
UPDATE t_members SET currency=100 does the trick for ALL members
because it does not have a condition set
calm charm
so i dont need the for loops?
rain wagon
No
the table and column I used was entirely fictional though
you need to adapt it to your db
calm charm
so:
connection = sqlite3.connect("testdatabase.db")
cur = connection.cursor()
purchases = [(member.id),'100']
cur.execute('UPDATE t_members SET currency=100')
connection.commit()
connection.close()
lime cobalt
https://i.imgur.com/awlc7Yf.png
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 MySQL server version for the right syntax to use near 'item' at line 1
Dunno why this sql statement throws this weird error i tried running the same sql on mysql workbench and it threw an error saying that column test doesn't exist (i replaced the %s with 'test') so i am guessing something is screwing up because name is an sql keyword but i dunno how to counter it
It says check for the right syntax near 'item' but i dont have anything like that in my sql
it couldnt magically convert items to item
calm charm
hello
oh @lime cobalt dont use that type of formatting
you're supposed to do it something like this::
c.execute('SELECT * FROM stocks WHERE symbol=?', t)
and replace the t with whatever variable you're using
rigid vapor
I have a (hopefully?) basic database design question -- I have a relationship where I have a composite key - a 3-tuple. (locale, version, name).
Now, creating a table with this kind of thing isn't really a problem, SQLAlchemy et al can accommodate composite primary keys just fine. (Though declaring a foreign key constraint on a composite key looks ... messy, to be generous.)
So I feel like a natural thing to want to do is create an ID table where you establish a relationship between id and the (locale, version, name) 3-tuple. At this point, you'd need to tell SQLAlchemy that this table has:
- A Primary key (id)
- A UniqueConstraint('name', 'locale', 'version', ...) to establish that this 3-tuple should be unique.
My only question here is actually just: Is that the normal way to design something like this? Some of these syntactical features for SQLA to accomplish this look like they've been hidden in the basement and weren't meant to be used ... have I wandered off the beaten path?
lime cobalt
@calm charm but %s works tho
rain wagon
Is there a way to upsert in SQLAlchemy?
steady epoch
"""Method is used to register a user's clan by taking a tuple of data to commit"""
sql = "INSERT INTO register (clan_tag,discord_id ,guild_id ) VALUES (?,?,?)"
self.conn.cursor().execute(sql, tuple_data)
self.conn.commit()
def register_player(self, tuple_data):
"""Method is used to register a user's clan by taking a tuple of data to commit"""
sql = "INSERT INTO register (player_tag,discord_id) VALUES (?,?) "
self.conn.cursor().execute(sql, tuple_data)
self.conn.commit()```i want to check if discord id is already there then skip insertion of discord.id
rain wagon
SELECT discord_id FROM register WHERE discord_id=?
steady epoch
@rain wagon actually i want if user id is present i want to add player tag and clan tag if user id is not present then i want to insert discord id ,player tag and clan tag
rain wagon
same syntax
adapt it to your database
SELECT columns FROM table WHERE CONDITION is the sql syntax
This returns either a result or None
if none, there is no entry
steady epoch
i want to check first if the user is registered or not
if not then i want to register
rain wagon
it's better to do two statements in this case, because I am sure you need some code logic there as well
steady epoch
actually i am new to db can u hint me how to write that in two line no i dont need logic just want to know the syntax of two liner sql qurey
rain wagon
I told you the syntax
steady epoch
ok
rain wagon
now go and figure out how to test if the statement is true or false
hint: The returned object has a state true or false
steady epoch
hmmm ok
rain wagon
stmt = connection.cursor().execute("SELECT discord_id from register WHERE discord_id=?", (discord_id,))
result = stmt.fetchone()
if not result:
register()
else:
pass
```` @steady epochif you still need a hint
steady epoch
hmmm
rain wagon
Can someone explain the rationale why I can't just say db.session().query(Databasemodel.id == id).update(Databasemodel) in SQLAlchemy when I want to update all columns in a row? Why do I have write each row into a statement?
lime cobalt
@calm charm tried using '?'
Command raised an exception: ProgrammingError: 1064 (42000): 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 '?' at line 1
cursor.execute('''SELECT * FROM items WHERE name = ?''', name)
oh wait it needs to be a tuple doesnt it
Command raised an exception: ProgrammingError: Not all parameters were used in the SQL statement
what....
lime cobalt
so i figured out that i was missing quotes
i was supposed to do WHERE name = '%s'
needed to wrap it around quotes
but
but now
it still doesnt work
ran the same sql query in mysql workbench
but
ProgrammingError: 1064 (42000): 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 'test item''' at line 1
wait what
i just noticed the 3 quotes lmao
alright fixed
alright uh
i dont get it
it says i have 3 quotes
cursor.execute('INSERT INTO items(guild_id,name,price,info) VALUES (%s, %s, %s, %s)', (ctx.guild.id, name, price, desc))
cursor.execute('SELECT * FROM items WHERE name = \'%s\'', (name,))
i removed all 3 quotes...
no other sql statement has 3 quotes
fuck it
double quotes
i dont get this error
the right syntax to use near 'test item''' at line 1
where are these 3 quotes coming from
rich trout
You don't need to quote that %s
('SELECT * FROM items WHERE name = %s', (name,))
The error message is misleading, the actual syntax it's choking on is (close enough to)
WHERE name = '''test name'''
The doubling of quotes indicates an escaped single quote, that doesn't behave like the start of a string but a value
anyway, just remove the quotes
solid void
hm, so at work, i'm wondering about which database to use, i have a consequent amount of data to process (millions of rows), that can be flat or lightly normalized, that gets updated frequently, the aim is to be able to quickly compute aggregates on the data, (group by/sum) , the data doesn't persist long, and can be reimported from a source of truth, though considering the amount of data, this could be costly, but fast insert is probably more important than consistency checks. I though about using redis and just keep it all in memory, but the mere insertion of the millions of rows takes something like 20mn even on a local docker instance, using redis-py, probably due to serialization stuff (tried with hmset to keep the values directly, but maybe serializing to some bytes representation and keeping redis blind to it would work better), i could use mongo, postgres, mysql, whatever, if i can delegate the aggregation to the db, that's great, but if not, selecting/fetching/summing in python should be manageable, if the fetching is not too slow (would depend on the amount of data fetched, certainly, but in redis, we could certainly cache dynamicaly). Does anyone have experience with something similar and some feedback about what works best?
gleaming quest
Will: ```py
with mysql.connector.connect(...):
why does cur.execute(f"INSERT OR IGNORE INTO currency_value_table ({ctx.author.id}, 100)" ) result in ```ile "discord.bot.py", line 374, in register
cur.execute(f"INSERT OR IGNORE INTO currency_value_table ({id}, 100)")
sqlite3.OperationalError: near "629120968425472010": syntax error
mild haven
I'm using sqlite, and am trying to select only the rows that contain text that is in foo. My code is:
db.execute("select * from items where item1 in ?", (foo))
@calm charm is that sqlite?
mild haven
you pass variables to a SQL script by using ? in place of the variable, then putting the variable in parentheses after the script, like cur.execute("insert or ignore into currency_value_table (?, 100)", (id))
ok
afaik SQL keywords aren't case sensitive
@calm charm why is id a dict
calm charm
wb me
small pendant
well, you're not using proper escaping, tk already gave you an answer
calm charm
@small pendant ```py
cur.execute("INSERT OR IGNORE INTO currency_value_table (?, 100)", (ctx.author.id))
sqlite3.OperationalError: near "?": syntax error
I did that. Know what to do.
mild haven
you need to have values between currency_value_table and (?, 100)
calm charm
cur.execute("INSERT OR IGNORE INTO currency_value_table value (?, 100)", (ctx.author.id))
sqlite3.OperationalError: near "?": syntax error
so this ^^
mild haven
cur.execute("INSERT OR IGNORE INTO currency_value_table VALUES (?, 100)", (ctx.author.id))
you forgot the s
calm charm
oh
File "discord.bot.py", line 373, in register
cur.execute("INSERT OR IGNORE INTO currency_value_table VALUES (?, 100)", (ctx.author.id))
ValueError: parameters are of unsupported type
who
wha
mild haven
was the first value an int
mild haven
no
calm charm
cur.execute("INSERT OR IGNORE INTO currency_value_table VALUES (?, 100)", (int(ctx.author.id)))
the problem is that it's too big
calm charm
wdym too big
mild haven
use bigint
calm charm
cur.execute("INSERT OR IGNORE INTO currency_value_table VALUES (?, 100)", (bigint(ctx.author.id)))
NameError: name 'bigint' is not defined
it's not a python data type
calm charm
how do i find a workaround in python
mild haven
when creating the table, specify that the first value is a bigint
mild haven
just change the data type of member_id to bigint
calm charm
INTEGER. The value is a signed integer, stored in 1, 2, 3, 4, 6, or 8 bytes depending on the magnitude of the value.
from a sqlite datatypes
mild haven
ah i think i know what's wrong
the value isn't too big
i was thinking of int when it was integer
calm charm
so.?
mild haven
There should be a comma after the id. SQLite expects a tuple, and ctx.author.id by itself is an integer. With a comma afterwards, it becomes a tuple.cur.execute("INSERT OR IGNORE INTO currency_value_table VALUES (?, 100)", (ctx.author.id, )
rain wagon
Or just the list indicator
cur.execute("INSERT OR IGNORE INTO currency_value_table VALUES (?, 100)", [ctx.author.id])
calm charm
ill test both
wait first tell me
how is this invalid syntax: ```py
File "discord.bot.py", line 374
embed = discord.Embed(title='Currency Information', description=f'Hello {ctx.author}. This is a message to confirm that you have registered a bank. 100 coins have been added to your wallet as of right now. To see what you can do with your money, please type .help currency. Good luck with your spendings!', timestamp=datetime.datetime.utcnow(), color=discord.Color.green())
^
SyntaxError: invalid syntax
??
mild haven
maybe datetime.datetime?
rain wagon
that depends how you import it
with import datetime you need two datetime
from datetime import datetime eliminates one
mild haven
@calm charm is that in on_message? if so, you can use message.created_at to get the message's timestamp
calm charm
no like
shi- nvm found it
was missing a bracket line before
yay it works
how do i test if a member_id is in a database?
if member.id in testdatabase.db:
you call db.execute with the select query
replace db with the cursor object
@calm charm example: ```python
#selects anything that matches the value of member.id
db.execute("select * from members where member_id = ? ", (member.id, )
#fetches one row that matches and assigns the returned list to a variable
row = db.fetchone()
#if nothing matches, db.fetchone returns "None", so we want to make sure that it matches
if row != "None":
print(row.member_id)
else:
print("Member ID not found.")
Can someone help me switch from sqlite to aiosqlite?
so far i have this code for sqlite: ```py
import sqlite3
from sqlite3 import Error
def create_connection(db_file):
conn = None
try:
conn = sqlite3.connect(db_file)
return conn
except Error as e:
print(e)
return conn
def create_table(conn, create_table_sql):
try:
c = conn.cursor()
c.execute(create_table_sql)
except Error as e:
print(e)
def main():
database = r"C:\Users\FAMILY\Desktop\Discord Bot\testdatabase.db"
sql_create_currency_value_table = "CREATE TABLE currency_value_table (member_id bigint UNIQUE, currency integer);"
# create a database connection
conn = create_connection(database)
# create tables
if conn is not None:
# create projects table
create_table(conn, sql_create_currency_value_table)
else:
print("Error! cannot create the database connection.")
if name == 'main':
main()
ornate isle
@calm charm usage between the 2 libs is almost identical, aside from adding some awaits and such. Check the GitHub page for aiosqlite and it demos usage
mild haven
ah my bad
calm charm
this is really
hard
Can someone just help me get the connection with the databse?
like create the tables
ornate isle
@calm charm ```python
import asyncio
import aiosqlite
DATABASE = 'testdatabase.db'
CREATE_TABLE_SQL = 'CREATE TABLE currency_value_table (member_id bigint UNIQUE, currency integer);'
async def create_table():
async with aiosqlite.connect(DATABASE) as db: # create connection to db
await db.execute(CREATE_TABLE_SQL) # execute sql
def main():
asyncio.run(create_table()) # execute in asyncio event loop
if name == 'main':
main()
that does the exact same thing as your original non-async code
except with aiosqlite
gleaming quest
@calm charm in my opinion it's better to store user ID as varchar(18)
Not number but text
Because you don't want to make operations on theese
And text is lighter than integers
calm charm
Also this is kinda a mix, but how do I fetch the highest data in terms of value from a database and display it in a rich embed
vital belfry
postgres check if variable in table?
calm charm
Aiosqlite?
calm charm
Ty
hello
was wondering one thing.
I already set up a database in sqlite that works as a currency system. As of right now, I only have three commands set up. I was wondering how to add a system that makes a user have an inventory. I get that I have to record member_id down in a database, however I was wondering how do I check if they have certain amounts, (like 10 cookies). Do I create a different value for each item, or what?
solemn ridge
Hey does anyone know where this error can come from?
getter = self._metadata._getter
AttributeError: 'NoneType' object has no attribute '_getter'
I literally changed nothing in my code and it started failing
to request things on my MySQL db
solemn ridge
I know as a matter of fact
rain wagon
so check if the query was successful
solemn ridge
it isnt
rain wagon
And yet python says it is
Or the object has no property with that name
or only a setter and not a getter
solemn ridge
For example
user = session.query(User).filter_by(username=dt['username']).first()
print(user.username)
this is the part where I query
user.username
works
it returns the username
rain wagon
Show the full traceback
rain wagon
solemn ridge
well i found this
I see it says this object does not return any rows
but its weird
because it does
rain wagon
first of all
you have been asking the wrong question
a traceback must always be read to the bottom
rain wagon
What are you trying to tell me?
solemn ridge
It is red haha
solemn ridge
ye
rain wagon
How do you store database connection/engine?
solemn ridge
sqlalchemy
rain wagon
Because it basically means that the db connection is closed
solemn ridge
wdym how?
rain wagon
no in flask
solemn ridge
Hmm
rain wagon
Are you using g?
solemn ridge
g?
rain wagon
Flask.g
Make a function that connects to mysql, store the result in g and then you can access it during a request in all functions
solemn ridge
Bro
rain wagon
you need to call that function in before_request
solemn ridge
I am not going to rewrite
rain wagon
and close it on teardown
calm charm
can someone help me turn some sqlite 3 commands to aiosqlite?
toxic rune
So I have this small schema for what would be a command permission system for a Discord bot (to upgrade my current one that uses multiple tables, one for ignored categories, other for ignored channels and other for whitelisted/blacklisted commands on a channel)
CREATE TABLE GuildPermission (
setting_id BIGSERIAL,
guild_id BIGINT NOT NULL,
whitelist BOOLEAN,
category_id BIGINT,
channel_id BIGINT,
command_name TEXT,
PRIMARY KEY (setting_id)
);
Would it be a proper approach to do this or should I keep the multiple table design? In this one I could check if whitelist is False, category_id and channel_id is NULL but there's a command name then that command is disabled on the guild, and such.
rain wagon
NF1! @toxic rune
toxic rune
Well... True. Just having trouble coming up with a proper design for this lol
calm charm
can someone turn this```py
connection = sqlite3.connect("testdatabase.db")
cur = connection.cursor()
cur.execute('SELECT * FROM currency_value_table WHERE member_id = ?', (ctx.author.id,))
row = cur.fetchone()
n:m relationships need to be resolved in a seperate table
calm charm
@ornate isle
rain wagon
Best practice would be table with all possible commands and a relatonship resolver table
toxic rune
Hmm I generally don't keep track of what commands are in the bot on the database. But assuming I do it that way, how would I go for the permission table? I can assume that you can override other permissions (i.e. you have a command disabled in X category, but you want that command to work in Y channel of X category) and such.
rain wagon
does each command need a permission level?
or have
I don't know your design but NF1 says, only 1 value per column (atomic data)
so multiple values per column violate NF1
and in that case you need a n:m resolver
AS for permissions, define a number for each permission and add it to the commands table
then you can check if the guild has that permission
toxic rune
Well I was planning the ability to: 1) Disable a command globally / per category / per channel 2) Ignore all commands globally / per category with the ability to override.
Right now what I have is a quick system since I didn't have time to write a better one, one table that holds what categories are ignored globally, another for globally ignored channels and then a whitelist table for commands https://mystb.in/vurakinafa.sql
Seems fairly complicated tbh.
rain wagon
but you get good speed and an atomic, manageable database
that can easily be extended
resolve your n:m relations
toxic rune
That's for sure, just not sure how I'd design the second table for this to work. 
rain wagon
Enter the id of both things into one table
that is all there is
this way you connect the command with the guild id
and you can query that table for which commands a guild has
I mean, you can leave the command table out, if you don't want it and do it this way:
This does take more space though
calm charm
discord.bot.py:409: RuntimeWarning: coroutine 'Cursor.fetchone' was never awaited
record = await cur.fetchone()[0]
RuntimeWarning: Enable tracemalloc to get the object allocation traceback
``` even though i have ```py
connection = await aiosqlite.connect("testdatabase.db")
cur = await connection.cursor()
await cur.execute("SELECT currency FROM currency_value_table WHERE member_id = ?" ,(member.id,))
record = await cur.fetchone()[0]
await connection.commit()
await connection.close()
``` (3rd last line)y
ornate isle
@calm charm get rid of the [0]. you can't get by index until after it has been awaited
calm charm
nah, i got it. However another question
Is there a way to sore multiple values in a cloum
ornate isle
do it on a separate line or something. also take a look at the code sample i sent you last night with the conversion of your previous code to async -- you'll notice it's way simpler because i removed all the stuff you don't need. this latest sample is about twice as long as it needs to be
calm charm
Like if I had items text Can I do items, bag, clothes?
ornate isle
i'm not sure what you're asking. you want to store bag and clothes as 2 values in one 'text' column?
ornate isle
haha. yeah, so this sounds to me like you just need to start learning the fundamentals of sql schema design in general now
calm charm
yeS
toxic rune
@rain wagon Oh no I get that. It's more so how to handle the multiple permissions with their overrides, organizing their columns and such.
ornate isle
the "correct" way to store multiple items belonging to a single row is to use a separate table. the "quick but silly" way would be to just store them as strings in the db separated by a comma like you just did, and just always make sure you split the string by commas whenever you query the db so you'll end up with the correct number of items
calm charm
._.
ornate isle
i don't know what to infer from that response
calm charm
can i see an example using this :```py
await cur.execute("INSERT OR IGNORE INTO items_table VALUES (?, ?)", ('bag', 1))
you can only insert one item into one field
calm charm
what do i say
rain wagon
The correct way is INSERT INTO tablename(column1, column2) VALUES(?,?)
for this case
column1 and 2 needs to be replaced with the actual names
calm charm
now how would i insert multiple values into a colum
calm charm
can i do one column, 2 values?
calm charm
Then how would I make like an inventory system
where an item gets stored to the db, and with amount
rain wagon
First, make a table with all possible items
calm charm
but its gonna store multiple items
rain wagon
Each gets an id
ornate isle
@calm charm you may not like this answer but you absolutely now need to go read up a basic tutorial on sql db design now
calm charm
ok
ornate isle
youll use a separate table
calm charm
do i assign my own id to an item?
calm charm
ok
ornate isle
i'm not telling him to leave, i'm suggesting that beyond solving this one specific issue, it would be very wise to get an understanding of how most db schemas as designed
calm charm
ye he's right
rain wagon
Look at this
The table player has the player, the table items all items
The ownership get's resolved in the table inventory, holding the id of the player and the id of the item as well as the amount of it
That is how you do that
calm charm
what does [PK] mean
ornate isle
primary key. id generally
rain wagon
Primary Key, but in this case it is also a foreign key
SHown in the Ref part
2 PK are one combined primary key
calm charm
who would Ref{ inventory.idPlayer > player.idPlayer} work in discord. Like how do I tell the idPlayer in inventory to get information from idPLayer in player
ornate isle
if you have a user with id=1 and want them to be assigned an item that has id=4, you would add a row to inventory where playerid=1, and itemid=4
ornate isle
when you create your db table and schema, generally you create an "id" column as something like integer PRIMARY KEY AUTOINCREMENT
new rows will then automatically be given the next available id. 1, 2, 3, etc
rain wagon
@calm charm you cannot copy that directly, it is the syntax of the tool
ornate isle
then you never insert an id manually, you just insert the other data and it'll assign it the next id
rain wagon
I just wanted to visualize it for you
calm charm
so i would Insert a row In inventory Where playid=1, itemid=4
ornate isle
do you know how to insert a row into a table?
rain wagon
In this case assigning the id's yourself is more benfitial
and there is nothing wrong with it
at least for the items
calm charm
INSERT
ornate isle
@rain wagon why would you manually assign an id to items
calm charm
So would I just enter the names, and it will automatically create an id for it?
rain wagon
because it can be benefitial, if you have bigger systems. ID do not need to start at 1, they can start at any number. And having let's say each group of items belonging to "Weapons" between 10.000 and 20.000 would be a good way of doing it
ornate isle
to give breathing room for keeping them ordered?
calm charm
so wait: ```py
Table items{
idItem INTEGER [PK]
name TEXT
or do i fill in name and idItem?
rich trout
well, only name, if it's an autoincrement
ornate isle
if a table references an item in another table, it does so by referencing the primary key
rich trout
It's wiser to not choose standard numbers for ID's because then you never "run out"
calm charm
Ill probs do autoincrement then
do u do ref{inventory.idItem >item.idItem}
to reference?
ornate isle
yeah this is getting into performance/optimization stuff that i feel probably adds more complexity than necessary for someone getting started
rich trout
!e ```py
import sqlite3
conn = sqlite3.connect(":memory:")
c = conn.cursor()
c.execute("""
CREATE TABLE items (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT)
""")
c.execute("INSERT INTO items VALUES (NULL, 'test item')")
print(list(c.execute("SELECT * FROM items").fetchall()))
delicate fieldBOT
@rich trout :white_check_mark: Your eval job has completed with return code 0.
[(1, 'test item')]
wait keep it
can u explain the last 4 lines
like y r u inseting NULL
and getting a value of 1
rich trout
When you insert into a column labeled AUTOINCREMENT, NULL indicates it should automatically generate a number
rich trout
The SELECT * FROM selects every row, the fetchall() get's all results
calm charm
it said
rich trout
Yeah I mixed up a symbol, 1s
!e ```py
import sqlite3
conn = sqlite3.connect(":memory:")
c = conn.cursor()
c.execute("""
CREATE TABLE items (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT)
""")
for item_name in ["test 1", "test 2", "sword", "shield"]:
c.execute("INSERT INTO items VALUES (NULL, ?)", (item_name,))
print(list(c.execute("SELECT * FROM items").fetchall()))
@rich trout :white_check_mark: Your eval job has completed with return code 0.
[(1, 'test 1'), (2, 'test 2'), (3, 'sword'), (4, 'shield')]
and now, how do i associate these values with a playerid
ornate isle
well you'd also have to create the other tables. this just creates the items table
calm charm
ik but im saying
rich trout
You use the foreign key format, here's an example
!e ```py
import sqlite3
conn = sqlite3.connect(":memory:")
c = conn.cursor()
c.execute("""CREATE TABLE players (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT)""")
c.execute("""
CREATE TABLE items (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT,
owning_player INTEGER,
FOREIGN KEY (owning_player) REFERENCES players (id))
""")
c.execute("INSERT INTO players VALUES (NULL, 'bob')")
bob_id = c.execute("SELECT id FROM players WHERE name = 'bob'").fetchone()[0]
for item_name in ["test 1", "test 2", "sword", "shield"]:
c.execute("INSERT INTO items VALUES (NULL, ?, ?)", (item_name, bob_id))
print(list(c.execute("SELECT * FROM items").fetchall()))
@rich trout :white_check_mark: Your eval job has completed with return code 0.
[(1, 'test 1', 1), (2, 'test 2', 1), (3, 'sword', 1), (4, 'shield', 1)]
Here, we change it so that the items table contains a "foreign key" reference to another table--the players table
The last command no longer really fits, since we've got foreign key references, and usually you want, say, all the items a given player has
calm charm
i see.
i see.
anyway thanks guys. This helped a lot.
I'll ask for help if I need clarification with anything else
rich trout
!e ```py
import sqlite3
conn = sqlite3.connect(":memory:")
c = conn.cursor()
c.execute("""CREATE TABLE players (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT)""")
c.execute("""
CREATE TABLE items (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT,
owning_player INTEGER,
FOREIGN KEY (owning_player) REFERENCES players (id))
""")
c.execute("INSERT INTO players VALUES (NULL, 'bob')")
bob_id = c.execute("SELECT id FROM players WHERE name = 'bob'").fetchone()[0]
for item_name in ["test 1", "test 2", "sword", "shield"]:
c.execute("INSERT INTO items VALUES (NULL, ?, ?)", (item_name, bob_id))
print(list(c.execute("SELECT players.name, items.name FROM items JOIN players").fetchall()))
@rich trout :white_check_mark: Your eval job has completed with return code 0.
[('bob', 'test 1'), ('bob', 'test 2'), ('bob', 'sword'), ('bob', 'shield')]
👍
calm charm
also, how do we add quantities to let's say 'sword' in this case. Like bob has 3 swords?
rich trout
In that case, either you have several rows for "sword", or you use an "intermediate table"
calm charm
what is an intermediate tablr?
rich trout
Where the table contains a link to an "item kind", a number column, and another reference to the player table
calm charm
so like if bob had 2 swords, how would it look like:
rich trout
Well, teh items table would be id, and name. The players table would be id, name, and the new "inventory" table would be player_id, count, item_id
so, (1, "bob"), (1, 2, 1), (1, "sword"), for example
calm charm
ok ty. In the future, i'll probably ask both of you for help, since you guys are the best help
rain wagon
I've got a question regarding SQLAlchemy
query = db.session.query(TCharacterInformation, TBloodline, TRace, TCorporation, TAncestry, TAttributes)\
.join(TBloodline, TRace, TCorporation, TAncestry)\
.join(TAttributes, TAttributes.character_id == TCharacterInformation.character_id)\
.filter(TCharacterInformation.character_id == characterid,
TAttributes.character_id == TCharacterInformation.character_id)\
.first()```This is my inner join query. When I now add TAlliance, it returns `None` for the whole query, probably because the result for a query on TAlliance is empty (no data in the table). How would I write this so it does not invalidate the whole query as None?This is the standalone TAlliance query: ```python
alliance = db.session.query(TAlliance) \
.filter(TAlliance.alliance_id == character.alliance_id) \
.first()```
Okay, now I get a idfferent error: python query = db.session.query(TCharacterInformation, TBloodline, TRace, TCorporation, TAncestry, TAttributes, TAlliance)\ .join(TBloodline, TRace, TCorporation, TAncestry, TAlliance)\ .join(TAttributes, TAttributes.character_id == TCharacterInformation.character_id)\ .join(TAlliance, TAlliance.alliance_id == TCharacterInformation.alliance_id)\ .filter(TCharacterInformation.character_id == characterid, TAttributes.character_id == TCharacterInformation.character_id, TAlliance.alliance_id == TCharacterInformation.alliance_id)\ .first()
sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) ambiguous column name: t_alliance.idAlliance
There is no other table with that name though
or column
rain wagon
I've renamed the table to t_alliance.idAlliances to avoid ambiguity in the generated sql, but still same error
sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) ambiguous column name: t_alliance.idAlliances
rain wagon
This is the basic principle behind all relational databases, @torn sphinx
You should always aim to at least implement NF1+2+3
carmine mortar
is it possible to access the same DB between my raspberry pi and my PC?
programmatically, that is
ornate isle
@carmine mortar what db?
if it's something like postgres, then sure, one of those machines (probably the rpi) can host the server and the other can connect to it.
(or have both of them connect to a remote db)
if you were hoping to use sqlite, then that is not as easy to handle remotely
torn sphinx
@rain wagon I'm glad that I read that before I started trying to design more than my practice project ha.
gleaming quest
Hi, I want to use some data caching system, I'm using of course memcached but... Maybe cache could be just variable? Just simple dict
gleaming quest
If someone will reply... Please @ping me
calm charm
tbh, i dont know much and this channel is not that alive. Try asking #python-discussion
charred fractal
File "c:/Users/User/Desktop/FlippingFlamingo/discordlevel.py", line 2, in <module>
import mysql.connector
File "C:\Users\User\AppData\Local\Programs\Python\Python36\lib\site-packages\mysql\connector\__init__.py", line 41, in <module>
import dns.resolver
ModuleNotFoundError: No module named 'dns'```
How to install module `dns`?
charred fractal
~~python cursor.execute("SELECT user_xp * FROM users WHERE client_id = " + str(message.author.id))
Error:
Traceback (most recent call last):
File "C:\Users\User\AppData\Local\Programs\Python\Python36\lib\site-packages\mysql\connector\connection_cext.py", line 489, in cmd_query
raw_as_string=raw_as_string)
_mysql_connector.MySQLInterfaceError: 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 'FROM users WHERE client_id = 205088813503086594' at line 1
During handling of the above exception, another exception occurred:
Traceback (most recent call last):
File "C:\Users\User\AppData\Local\Programs\Python\Python36\lib\site-packages\discord\client.py", line 312, in _run_event
await coro(*args, **kwargs)
File "c:/Users/User/Desktop/FlippingFlamingo/discordlevel.py", line 35, in on_message
cursor.execute("SELECT user_xp * FROM users WHERE client_id = " + str(message.author.id))
File "C:\Users\User\AppData\Local\Programs\Python\Python36\lib\site-packages\mysql\connector\cursor_cext.py", line 266, in execute
raw_as_string=self._raw_as_string)
File "C:\Users\User\AppData\Local\Programs\Python\Python36\lib\site-packages\mysql\connector\connection_cext.py", line 492, in cmd_query
sqlstate=exc.sqlstate)
mysql.connector.errors.ProgrammingError: 1064 (42000): 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 'FROM users WHERE client_id = 205088813503086594' at line 1
```~~ ANSWER RIGHT IN FRONT OF MY EYESWow, it is pretty easy to create a currency command with databases.
torn sphinx
Currently using sqlite3, and I made a function that updates or creates entrys if they don't exist.
The whole thing works perfect, but only for one row..its for a discord bot, that makes new rows for each server. On server has one row. To create the first entry for the server I use
sql = f"INSERT INTO {table_to_update}(guild_id, {to_select_or_update}) VALUES(?, ?)"
and it works fine, but only for the first server that runs a command or anything. That server gets a entry, and works fine. Any other discord server that comes after it does not get a entry tho. The database keeps 1 row, and only edits it for the first server. Every other servers simply get..ignored. Why is it not creating new rows?
I am already doing pretty much the same for my leveling system, and that one works perfectly without problems, across different servers.
if result is None:
sql = ("INSERT INTO levels(guild_id, user_id, exp, lvl) VALUES(?,?,?,?)")
val = (message.guild.id, message.author.id, 2, 0)
cursor.execute(sql, val)
db_commit()
And this is the code for my other stuff, which does not work as of right now.
sql = f"INSERT INTO {table_to_update}(guild_id, {to_select_or_update}) VALUES(?, ?)"
val = (guild_id, update_value)
cursor.execute(sql, val)
Has anyone used Automap from SQLAlchemy? I'm trying to map an existing database
calm charm
does anybody know how to why mySQL server's arent showing up?
torn sphinx
@calm charm more info?
calm charm
nvm
but
I have this code which looks through a table and get's the highest values in terms of currency: py connection = await aiosqlite.connect('currency.db') cur = await connection.cursor() await cur.execute('SELECT * FROM currency_value_table ORDER BY currency') record = (await cur.fetchall()) await connection.commit() await connection.close() await ctx.send(f"Wow look cool {record}") It does it so that it prints `(member.id) (currency amount) However I wanna make it so that member.id is their display name or nick name. Does anyone know how to make this possible
torn sphinx
May I ask what you are using for DB interaction?
charred fractal
@calm charm what is an Inventory system?
calm charm
like where the user can can certain items and it's held in their inventory @charred fractal
@torn sphinx Im using aiosqlite
charred fractal
Oh, Well that seems complicated.
calm charm
ye
calm charm
dk, really havent worked with json columns
charred fractal
I just want a basic currency command. that doesn't really do much.
charred fractal
I need help with sending messages each time a user's current level changes, python if newXP < 100: currentLevel = 0 elif newXP > 99 and newXP < 200: currentLevel = 1 elif newXP > 199 and newXP < 500: currentLevel = 2 elif newXP > 499 and newXP < 900: currentLevel = 3 elif newXP > 899 and newXP < 1000: currentLevel = 4 elif newXP > 999 and newXP < 1500: currentLevel = 5 else: currentLevel = 6
proven grove
I need urgent help. My Raspbian installation has become corrupt and I'm going to do a fresh install, but I don't have a backup of my MySQL database, where's it stored?
Okay, found it under \var\lib\mysql\
Even a more important question, can I copy the files there and replace them on a new installation without losing data?
charred fractal
How is this not telling me how many coins I have python @bot.command() async def coins(ctx): cursor = mydb.cursor() cursor.execute("SELECT user_coins FROM users WHERE client_id = " + str(ctx.author.id)) result = cursor.fetchall() currentXP = result[0][0] currentLevel = result[0][1] currentCoins = result[0][2] print(result) if(len(result)) == 0: print("User is not in db .. add them.") cursor.execute("INSERT INTO users VALUES(" + str(ctx.author.id) + "," + str(currentXP) + "," + str(currentLevel) + ", 0") mydb.commit() print("Inserted...") else: await ctx.send("Coins: " + str(currentCoins))
charred fractal
config = ConfigParser()
config.read(r'C:\Users\User\Desktop\MySQL\example_file.ini')
conf = ['credentials']
mydb = mysql.connector.connect(
host = "localhost",
user = conf['user'],
password = conf['password'],
database = "userlevels",
auth_plugin="mysql_native_password"
)``` ``` File "c:/Users/User/Desktop/FlippingFlamingo/discordlevel.py", line 20, in <module>
user = conf['user'],
TypeError: list indices must be integers or slices, not str```This is just a general question, but does anybody know ti create a bank account in which you can only hold a certain amount, whereas you have your regular balance, and that can be anything
charred fractal
Uh that is like the same thing as coins.
Except you name your other column user_bank
glad bobcat
Do sessionmaker objects in sqlalchemy timeout?
glad bobcat
I guess not, they’re just configurations after all
What’s the smart way of using a session in a single stupid class but that might be rarely called, like a Discord bot?
Should I just use sessionmaker and make/destroy session for each call, or use a scopedsession as a way to share sessions?
rain wagon
Is there no way to do a cross join with sqlalchemy?
An ANSI join does not quite work for me
patent glen
what's the difference?
anyway i'm not familiar with sqlalchemy specifically but look at https://stackoverflow.com/questions/14813516/is-explicit-cross-join-possible-with-sqlalchemy and https://docs.sqlalchemy.org/en/13/core/tutorial.html [look for 'cartesian product']
charred fractal
@bot.command()
async def coins(ctx, user: discord.Member = None):
if user is None:
user = ctx.author
cursor = mydb.cursor()
cursor.execute("SELECT user_coins FROM users WHERE client_id = " + str(ctx.author.id))
result = cursor.fetchall()
currentCoins = result[0][0]
print("Coins: " + str(currentCoins))
await ctx.send("Coins: " + str(currentCoins))
else:
cursor = mydb.cursor()
cursor.execute("SELECT user_xp, user_level, user_coins FROM users WHERE client_id = " + str(ctx.author.id))
result = cursor.fetchall()
print(result)
if(len(result)) == 0:
print("User is not in db .. add them.")
cursor.execute("INSERT INTO users VALUES(" + str(user.id) + ", 0" + ", 0" + ", 0)")
mydb.commit()
print("Inserted...")
cursor = mydb.cursor()
cursor.execute("SELECT user_coins FROM users WHERE client_id = " + str(user.id))
result = cursor.fetchall()
currentCoins = result[0][0]
await ctx.send("Coins of " + str(user.name) + ": " + str(currentCoins))
else:
cursor = mydb.cursor()
cursor.execute("SELECT user_coins FROM users WHERE client_id = " + str(user.id))
result = cursor.fetchall()
currentCoins = result[0][0]
await ctx.send("Coins of " + str(user.name) + ": " + str(currentCoins))```
Error:
```discord.ext.commands.errors.CommandInvokeError: Command raised an exception: IntegrityError: 1062 (23000): Duplicate entry '398802567972257793' for key 'users.PRIMARY'
Hello
How do I make it so that I can turn select bank from currency_value_table where member_id =ctx.author.id' into a usable variable in python?
calm charm
nvm i got it
final shale
something like:
cur = conn.cursor()
cur.execute("select bank from currency_value_table where member_id =ctx.author.id")
conn.commit()```they said they already got it.
fringe tiger
Question about design, let's say I have bool columns like
send_to_dm, send_to_channel, enable sending
So it's pretty self exlanatory what does it to without going further into it.
So anyway it would be easier for me to to just have one a integer column representing type like reminder_type then if it's 0 it's disabled, if it's 1 it will send to dm, if it's 2 it will send to channel and if it's 3 it will send both to dm and channel.
Now this would be easier for me to code (I can code both) but I'm thinking would this be some kind of antipattern? Because column reminder_type would be documented in the code, if someone opens the database without looking at the documentation in the code they would scratch their head thinking what does each integer do, but if I name each column individually they would have a general idea.
Am I overthinking here 
Also I feel like in the future it would be easier to just add another type in reminder_type than creating a new column
fringe tiger
solved, googled and it was one of first antipatterns. I'll study it more
median night
guys, if i need to save something like a description of an item which could be very long, would it be better to save it in the database as plaintext or encode it somehow? like base64 or something else
I am using mongodb btw if this makes any difference
gleaming quest
How can I send "keep-alive query" to PostgreSQL?
potent yarrow
Is there an effiecient method in asyncpg to delete many rows from a table with conditions, im using executemany and its taking like 15s+ for 300 rows
calm charm
Does anybody know how to create a system where you can have a bank limit. As of right now, I have a wallet and bank. They both can hold unlimited values. You can also deposit/withdraw out of your bank. But I wanna make it so that you can only hold a certain amount in ur bank
charred fractal
Well...
you just have to set a thing for user_bank
although setting a limit is bit tricky
I would probably have to do it for myself to see.
sleek rose
Is there a resource that show how I can add pics to my SQLite 3 database by reference to a folder? I have a folder with .jpg named after the movie in the database. How can I reference them and have them show up in the database.?
rain wagon
Don't store pictures in a db @sleek rose
Store the path to them
which should be a simple string/text
torn sphinx
is anyone here good at ERD databases?
sleek rose
Thank you scorcher24.
fringe tiger
So using asyncpg and discord.py, do I need to use a connection pool if I'm just gonna connect once and pass that connection around
Someone said I should a while back but I don't understand why
pearl heath
hey I'm using flask-sqlalchemy and I'm trying to understand the finer workings of the way the objects work. I've got some test code that just seems to work differently than the tutorials and I am getting confused. I think this is a case of the tensorflow 1 vs 2 docs confusion happening
I'm trying to make a function that returns the user object based on its user_id and then change a variable in that user object using object.field = "blorp".
I can make an object and access it like user.user_id and assign stuff like user.user_id = 2 and commit it to the database... but then how do I get a function that returns all the created User databases as a list of those objects I can do a for loop on? database.session?
this object returns ALL of the users right?
>>> asdf = database.session.query(User)
>>> asdf
<flask_sqlalchemy.BaseQuery object at 0x7f49aacff780>
how do I get this as a list of user id's?
#>>> dbquery = database.session.query('user_id')
#>>> dbquery.
add_column add_columns add_entity all as_scalar autoflush column_descriptions correlate countcte delete dispatch```
how do I get to :
for user in user_id_list:
if user.id == watever:
user.name = "mud"
When creating a schema, what would i put a column as that will involve a list
strange fox
Pymssql or pyodbc ?????
Which one should I go for
proven wagon
Hey, I need to move all tables and rows from a database on one VPS to a database on a different VPS.
Is there a easy way to do this?
upbeat lily
Depending on which specific database you're running, Google should probably have a guide for you. I haven't done it myself, but I believe MySQL at least is pretty easy (and I assume Postgres is as well)
proven wagon
postgres
charred fractal
@bot.command()
async def coins(ctx, user: discord.Member = None):
if user is None:
user = ctx.author
cursor = mydb.cursor()
cursor.execute("SELECT user_coins FROM users WHERE client_id = " + str(user.id))
result = cursor.fetchall()
print(result)
if(len(result)) == 0:
print("User is not in db .. add them.")
cursor.execute("INSERT INTO users VALUES(" + str(user.id) + ", 0" + ", 0" + ", 0)")
mydb.commit()
print("Inserted...")
await ctx.send("Please run this command again!")
else:
currentCoins = result[0][0]
await ctx.send(f"You have " + str(currentCoins) + " Coins.")
else:
cursor = mydb.cursor()
cursor.execute("SELECT user_coins FROM users WHERE client_id = " + str(user.id))
result = cursor.fetchall()
print(result)
if(len(result)) == 0:
print("User is not in db .. add them.")
cursor.execute("INSERT INTO users VALUES(" + str(user.id) + ", 0" + ", 0" + ", 0)")
mydb.commit()
print("Inserted...")
await ctx.send("Please run this command again!")
else:
currentCoins = result[0][0]
await ctx.send(f"{user.mention} has " + str(currentCoins) + " Coins.")```Error: ```discord.ext.commands.errors.CommandInvokeError: Command raised an exception: DataError: 1136 (21S01): Column count doesn't match value count at row 1
When I was just using this 5 minutes ago.
and i didn't even touch anything.
oh nvm.
forgot
that you had to put another value after the insert command.
if you add a new column
flint fractal
Im trying to do this insert query
db.execute("INSERT INTO bots (name, id, main_owner, owners, library, website, github, short_description, long_description, prefix, invite_url, support_server, tags, monthly_votes, total_votes, certified, vanity_url, server_count, shard_count, approved) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)", name, id, mainowner, owners, library, website, github, shortdesc, longdesc, prefix, inviteurl, supporturl, tags, 0, 0, False, "None", 0, 0, False) but i get a syntax error
i've tried using $1, $2 ,etc. instead and it doesn work
flint fractal
well im using a Postgres Database
and i usually use $1, $2, etc.
db.execute("INSERT INTO bots VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16, $17, $18, $19, $20)", name, id, mainowner, owners, library, website, github, shortdesc, longdesc, prefix, inviteurl, supporturl, tags, 0, 0, False, "None", 0, 0, False)
sqlalchemy.exc.ProgrammingError: (psycopg2.errors.UndefinedParameter) there is no parameter $1
LINE 1: INSERT INTO bots VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9,...```Try %s 😉
gleaming quest
SELECT * FROM `users` WHERE `user_id` LIKE %s
``` I'm using queries like this oneI just dont understand how its the same server but requires different parameters
flint fractal
well im using SQLAlchemy on both apps and doesnt require it on the other
it just confuses me
charred fractal
when I use a conversion command for my coin system
it doesn't update when I do select * from users;
although it says it changed the amount
it updates properly when I use a give command
but this is the code I have: python @bot.command() async def convert(ctx): cursor = mydb.cursor() cursor.execute("SELECT user_coins FROM users WHERE client_id = " + str(ctx.author.id)) result = cursor.fetchall() print(result) if(len(result)) == 0: print("User is not in db .. add them.") cursor.execute("INSERT INTO users VALUES(" + str(ctx.author.id) + ", 0" + ", 0" + ", 0" + ", 0)") mydb.commit() print("Inserted...") await ctx.send("Please run this command again!") else: currentCoins = result[0][0] if currentCoins >= 1000: newCoins = currentCoins - 1000 cursor.execute("UPDATE users SET user_coins = " + str(newCoins) + " WHERE client_id = " + str(ctx.author.id)) cursor = mydb.cursor() cursor.execute("SELECT user_bag FROM users WHERE client_id = " + str(ctx.author.id)) result = cursor.fetchall() currentBag = result[0][0] newBag = currentBag + 1 newCoins = newCoins + 1000 cursor.execute("UPDATE users SET user_bag = " + str(newBag) + " WHERE client_id = " + str(ctx.author.id)) embed1 = discord.Embed(title="Flipping Flamingos:", description=f"{ctx.author.mention}", color=0xff00df) embed1.add_field(name="Converted: ", value="I have converted " + str(newCoins) + " Coins :blue_circle: to 1 Bear Bag :moneybag:", inline=False) #await ctx.send("I have converted " + str(newCoins) + " Coins to 1 Bear Bag") await ctx.send(embed=embed1) else: await ctx.send("Sorry you don't have enough coins to convert!")
i just don't get why it isn't displaying when I do select * from users; in my mysql commandline.
rain wagon
cursor.execute("UPDATE users SET user_coins = " + str(newCoins) + " WHERE client_id = " + str(ctx.author.id))
oh boy
don't do that
@charred fractal
rain wagon
charred fractal
how do I fix it?
rain wagon
Use bound parameters
charred fractal
huh?
rain wagon
Inputs into databases need to be escaped
so that a Loginname;)drop table wp_users;-- does not delete all your users
rain wagon
You need to do that for ALL your statements
charred fractal
not all of them.
rain wagon
I talking about escaping the inputs
charred fractal
but especially after my update statements
rain wagon
Hmm, I need to convert an existing sqlite db into my own format. DBeaver seems to be unable to handle a statement like INSERT INTO (db1.colum) SELECT db.2value
Does anyone know a good tool for that, save the cli?
Or know how to do that with DBeaver?
nvm I use python, it's probably the best.
gleaming quest
My data is not saved to PostgreSQL databse and yes, I;m using db.commit()
I don't know what could be wrong
gleaming quest
db = postgre_connect()
c = db.cursor()
c.execute(
'UPDATE guilds SET mute_role_id = %s WHERE guild_id = %s;',
(
str(new_mute_role_id),
str(ctx.guild.id)
)
)
db.commit()
c.close()
db.close()
``` Some codeis the connection successful?
gleaming quest
Yup
Beacuse I;m taking (adn saving) data in other function
That one works
But only whis one not
anything in
/var/log/postgresql/?.log?
@rain wagon I'm on Windows (I guess It's Linux directory)
rain wagon
Just a side note: Why do you save an int as string?
that is just not feasible
it slows down the database
gleaming quest
Because it's 18 pos int
rain wagon
it's what?
gleaming quest
¯_(ツ)_/¯
rain wagon
In %PROGRAMFILES%\PostgreSQL should be the logs
gleaming quest
"No directory"
rain wagon
uff then you gotta look
gleaming quest
One thing
rain wagon
not using Windows for anything server related
gleaming quest
I'm using virtual env
rain wagon
congrats
gleaming quest
?
rain wagon
Another source of error here is the input
If the id does not exist, nothing is updated
so make sure you try to update on the correct id
gleaming quest
Nvm guild_id column was empty...
Just a side note: Why do you save an int as string?
that is just not feasible
it slows down the database
...really?
rain wagon
Any CPU handles numbers a lot better than strings
and db have an easier time handling large numbers than text
especially for PK and FK
gleaming quest
Oh...
That's good to know
What format should I use to handle 10e+18 numbers?
In PostgreSQL
charred fractal
I'm stuck on trying to make a conversion system into a loop although I tried while currentCoins <= 1000 it just kept doing that even though it wasn't.
glad bobcat
Heya everybody, important question I never found the answer too: how do you sum on a column that’s defined as a Boolean in SQLAlchemy?
MySQL properly sums it if you call SUM() on the column, but SQLAlchemy just returns True as long as one of the members was true...
The way I made it work was to do:
(func.count(
case(
[((ld.EsportsGameParticipant.win == 1), 1)],
else_=literal_column("NULL")
)
) / func.count()).label('winrate'),
func.count().label('games')
But it seems really really really dirty, and I’m pretty sure it’s very inefficient in terms of performance
charred fractal
What is SQLAlchemy?
glad bobcat
The most popular Python ORM for SQL databases.
charred fractal
What is the difference between MySQL and SQLAlchemy?
glad bobcat
They are entirely different things. MySQL is an SQL implementation (so, the database in itself), SQLAlchemy is a python package that helps you communicate with it.
SQLAlchemy works with any SQL database, from MySQL to PostgreSQL including SQL Server and Amazon Aurora.
But my issue is that calling sqlalchemy.func(sum) on a Column(sqlalchemy.Boolean) returns a Boolean instead of an int like it does in the MySQL dialect...
I think SQLAlchemy casts the sum as the original type and I don’t see how to bypass that
charred fractal
why can you not just use MySQL to do that?
glad bobcat
I’d rather use the Python ORM than write out SQL statements by hand for forward compatibility purposes.
charred fractal
ah.
glad bobcat
(func.sum(ld.EsportsGameParticipant.win.cast(sqlalchemy.Integer)) / func.count()).label('winrate'),
This worked but it still generates a cast on the MySQL side which I dislike 🤔
I found that but I haven’t found where to apply it
charred fractal
I dislike each time you want to convert coins that you have to type the command each time instead of doing >convert coins #oftimes
glad bobcat
(sqlalchemy.type_coerce(func.sum(ld.EsportsGameParticipant.win), sqlalchemy.Integer)
/ func.count())
.label('winrate'),
Found it, not generating an SQL cast in the query as well \o/
charred fractal
so... sometimes you have to deal with stuff you don't like.
glad bobcat
https://stackoverflow.com/questions/37328779/sqlalchemy-func-count-on-boolean-column/60647153#60647153
Went and corrected the StackOverflow that misled me into using case() lol
torpid crater
In case anyone ever wondered how to use just one connection for all of your Mongo stuff, I made a demonstration Repo on Github here just a few minutes ago: https://github.com/wanderrful/request-demo
cerulean harbor
void otter
what database/orm are you using?
mild haven
is MySQL async?
lofty summit
Np
charred fractal
why does this not work @commands.check(is_owner) compared to this: @commands.check(is_owner) that does work?
Error: ```
Ignoring exception in command startlot:
Traceback (most recent call last):
File "C:\Users\User\AppData\Local\Programs\Python\Python36\lib\site-packages\mysql\connector\connection_cext.py", line 489, in cmd_query
raw_as_string=raw_as_string)
_mysql_connector.MySQLInterfaceError: Operand should contain 1 column(s)
During handling of the above exception, another exception occurred:
Traceback (most recent call last):
File "C:\Users\User\AppData\Local\Programs\Python\Python36\lib\site-packages\discord\ext\commands\core.py", line 83, in wrapped
ret = await coro(*args, **kwargs)
File "c:/Users/User/Desktop/FlippingFlamingo/discordlevel.py", line 752, in startlot
cursor.execute("SELECT user_coins FROM users WHERE client_id = " + str(winner))
File "C:\Users\User\AppData\Local\Programs\Python\Python36\lib\site-packages\mysql\connector\cursor_cext.py", line 266, in execute
raw_as_string=self._raw_as_string)
File "C:\Users\User\AppData\Local\Programs\Python\Python36\lib\site-packages\mysql\connector\connection_cext.py", line 492, in cmd_query
sqlstate=exc.sqlstate)
mysql.connector.errors.DataError: 1241 (21000): Operand should contain 1 column(s)
The above exception was the direct cause of the following exception:
Traceback (most recent call last):
File "C:\Users\User\AppData\Local\Programs\Python\Python36\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\Python36\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\Python36\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: DataError: 1241 (21000): Operand should contain 1 column(s)
@bot.command()
@commands.check(is_owner)
async def startlot(ctx):
cursor = mydb.cursor()
query = "SELECT client_id, user_coins FROM lottery"
cursor.execute(query)
entries = cursor.fetchall()
winner = random.choice(entries)
reward = sum([entry[1] for entry in entries])
cursor = mydb.cursor()
cursor.execute("SELECT user_coins FROM users WHERE client_id = " + str(winner))
result = cursor.fetchall()
currentCoins = result[0][0]
newCoins = currentCoins + reward
cursor.execute("UPDATE users SET user_coins = " + str(newCoins) + " WHERE client_id = " + str(winner))
mydb.commit()
await ctx.send("I have gave " + str(winner) + " " + str(coins) + " Coins :blue_circle:")
```Have you opened mydb connection before for sure?
charred fractal
yes
gleaming quest
Google says, that DataError: 1241 is being raised when you're trying to assing array to one field, or trying to use unexpected ( ) in SQL query
charred fractal
I fixed that lol.
gleaming quest
Replace py "SELECT user_coins FROM users WHERE client_id = " + str(winner) With ```py
"SELECT user_coins FROM users WHERE client_id = %s", (str(winner),)
New Error ```Ignoring exception in command startlot:
Traceback (most recent call last):
File "C:\Users\User\AppData\Local\Programs\Python\Python36\lib\site-packages\mysql\connector\connection_cext.py", line 489, in cmd_query
raw_as_string=raw_as_string)
_mysql_connector.MySQLInterfaceError: Operand should contain 1 column(s)
During handling of the above exception, another exception occurred:
Traceback (most recent call last):
File "C:\Users\User\AppData\Local\Programs\Python\Python36\lib\site-packages\discord\ext\commands\core.py", line 83, in wrapped
ret = await coro(*args, **kwargs)
File "c:/Users/User/Desktop/FlippingFlamingo/discordlevel.py", line 751, in startlot
cursor.execute("SELECT user_coins FROM users WHERE client_id = " + str(winner))
File "C:\Users\User\AppData\Local\Programs\Python\Python36\lib\site-packages\mysql\connector\cursor_cext.py", line 266, in execute
raw_as_string=self._raw_as_string)
File "C:\Users\User\AppData\Local\Programs\Python\Python36\lib\site-packages\mysql\connector\connection_cext.py", line 492, in cmd_query
sqlstate=exc.sqlstate)
mysql.connector.errors.DataError: 1241 (21000): Operand should contain 1 column(s)
The above exception was the direct cause of the following exception:
Traceback (most recent call last):
File "C:\Users\User\AppData\Local\Programs\Python\Python36\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\Python36\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\Python36\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: DataError: 1241 (21000): Operand should contain 1 column(s)
New Code: python @bot.command() @commands.check(is_owner) async def startlot(ctx): cursor = mydb.cursor() cursor.execute("SELECT client_id, user_coins FROM lottery") entries = cursor.fetchall() winner = random.choice(entries) reward = sum([entry[1] for entry in entries]) cursor = mydb.cursor() cursor.execute("SELECT user_coins FROM users WHERE client_id = " + str(winner)) result = cursor.fetchall() currentCoins = result[0][0] newCoins = currentCoins + reward cursor.execute("UPDATE users SET user_coins = " + str(newCoins) + " WHERE client_id = " + str(winner)) mydb.commit() await ctx.send("I have gave " + str(winner) + " " + str(coins) + " Coins :blue_circle:")
gleaming quest
File "C:\Users\User\AppData\Local\Programs\Python\Python36\lib\site-packages\mysql\connector\connection_cext.py", line 489
``` What's in line 489?See into error
gleaming quest
Solved? ¯_(ツ)_/¯
charred fractal
No... this is the actual error: discord.ext.commands.errors.CommandInvokeError: Command raised an exception: DataError: 1241 (21000): Operand should contain 1 column(s)
gleaming quest
Okay, once more
File "C:\Users\User\AppData\Local\Programs\Python\Python36\lib\site-packages\mysql\connector\connection_cext.py", line 489
Old code: py cursor.execute("SELECT user_coins FROM users WHERE client_id = " + str(winner)) New code: ```py
cursor.execute("SELECT user_coins FROM users WHERE client_id = %s", (str(winner),))
It was aout *out of pased code
gleaming quest
I mean it wasn't in code you pasted, so you solved it at yourself
charred fractal
what?
but winner is suppose to = the random id that was picked.
Okay here we go: ```discord.ext.commands.errors.CommandInvokeError: Command raised an exception: IndexError: list index out of range
Code: python @bot.command() @commands.check(is_owner) async def startlot(ctx): cursor = mydb.cursor() cursor.execute("SELECT client_id, user_coins FROM lottery") entries = cursor.fetchall() winner = random.choice(entries) reward = sum([entry[1] for entry in entries]) cursor = mydb.cursor() cursor.execute("SELECT user_coins FROM users WHERE client_id = %s", (str(winner),)) result = cursor.fetchall() currentCoins = result[0][0] newCoins = currentCoins + reward cursor.execute("UPDATE users SET user_coins = " + str(newCoins) + " WHERE client_id = " + str(winner)) mydb.commit() await ctx.send("I have gave " + str(winner) + " " + str(coins) + " Coins :blue_circle:")
rain wagon
cursor.execute("SELECT user_coins FROM users WHERE client_id = %s", (str(winner),))
result = cursor.fetchall()
currentCoins = result[0][0]
newCoins = currentCoins + reward
cursor.execute("UPDATE users SET user_coins = " + str(newCoins) + " WHERE client_id = " + str(winner))
# You can shorten that:
cursor.execute("UPDATE users SET user_coins = user_coins + ? WHERE client_id=?", (newCoins, client_id))```@charred fractal
Also, when do you start using bound statements?
rain wagon
sql
charred fractal
the last statement?
charred fractal
hmm?
rain wagon
That line is just a shorter version of your 5 lines
And a better way of doing it, because your db is still susceptible to sql injections.
charred fractal
so?
cursor = mydb.cursor()
cursor.execute("SELECT client_id, user_coins FROM lottery")
entries = cursor.fetchall()
winner = random.choice(entries)
reward = sum([entry[1] for entry in entries])
cursor = mydb.cursor()
cursor.execute("SELECT user_coins FROM users WHERE client_id = %s", (str(winner),))
result = cursor.fetchall()
currentCoins = result[0][0]
newCoins = currentCoins + reward
cursor.execute("UPDATE users SET user_coins = " + str(newCoins) + " WHERE client_id = " + str(winner))```absolutely not
charred fractal
obviously was thinking the same answer: discord.ext.commands.errors.CommandInvokeError: Command raised an exception: IndexError: list index out of range
*error
lol
rain wagon
2 things
I have given you a shorter way of writing 5 lines
second, why do you take good code and turn it back into code vulnerable to sql injection?
Give me a link to your bot and I show you how fast a database is gone....
gleaming quest
Error: psycopg2.errors.UndefinedFunction: operator does not exist: bigint ~~ bigint Code: py c.execute( 'SELECT mute_role_id FROM guilds WHERE guild_id LIKE %s::bigint;', ( int(ctx.guild.id), ) ) Why it doesn't work?
Connecting to PostgreSQL
charred fractal
No.
and do you mean just this: cursor.execute("UPDATE users SET user_coins = user_coins + ? WHERE client_id=?", (newCoins, client_id))
rain wagon
LIKE %s::bigint that doesn't seem right @gleaming quest
charred fractal
and isn''t there suppose to be quotes around "%s"
I may be wrong as your using a different sql.
gleaming quest
"" is a string
rain wagon
@charred fractal If you have to use %s or ? has nothing to do with SQL
It is the database connectors in python having different ways of doing it
charred fractal
okay.
gleaming quest
charred fractal
oh and btw ctx.guild.id should already be an integer.
rain wagon
Use = not LIKE
charred fractal
I still don't understand what you are trying to say @rain wagon
rain wagon
Your input values are going straight to the database. There is no escaping. So, if I were to sneak a sql command into your bot, it would execute it.
If you use bound parameters, it will escape them, so the db will not escape any malicious sql command
charred fractal
this is the code: reward = sum([entry[1] for entry in entries])
Error: ```Ignoring exception in command startlot:
Traceback (most recent call last):
File "C:\Users\User\AppData\Local\Programs\Python\Python36\lib\site-packages\discord\ext\commands\core.py", line 83, in wrapped
ret = await coro(*args, **kwargs)
File "c:/Users/User/Desktop/FlippingFlamingo/discordlevel.py", line 753, in startlot
currentCoins = result[0][0]
IndexError: list index out of range
The above exception was the direct cause of the following exception:
Traceback (most recent call last):
File "C:\Users\User\AppData\Local\Programs\Python\Python36\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\Python36\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\Python36\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: IndexError: list index out of range```
gleaming quest
Thanks @rain wagon
charred fractal
hmm.
rain wagon
For example, if I'd change my username to scorcher24); UPDATE users SET coins =100000000000;-- all users would get a billion coins if your bot runs my name
charred fractal
it uses client ids.
rain wagon
it's an example that may or may not hold up
but it's the principle of the matter
I end the current command after my name, sql fails the command
then I inject a new command and turn the rest of the commands into a comment
-- = comment in sql
gleaming quest
@charred fractal https://www.w3schools.com/sql/sql_injection.asp
That's common hacker attack method
And that's why you shouldn't concatenated queries, but use %s or ?
rain wagon
go back and change it
charred fractal
and how could they set user id 105 or 1=1
gleaming quest
This time you should change it, but later you will know, that you have to protect your projects of injections
rain wagon
cursor.execute("UPDATE table SET column = %s"), [value_to_set])
like this
you give it a list after the query
in the order of the %s
gleaming quest
I think values must be a tuple 🤔
rain wagon
no
charred fractal
yeah.
rain wagon
just an iterable
gleaming quest
Okay, maybe, not sure
rain wagon
when you have only one value, you always need to add a second ,
with a list, that isn't necessary
charred fractal
aghh
I can't change other stuff
else it will break my code
as of I have >coins @user
gleaming quest
It's better to rewrite code, than have a spaghetti
charred fractal
it is impossible to have a user id of 105 or 1=1.
as of discord's ids are very long
gleaming quest
18 chars exacly
rain wagon
Anyway, for this issue you are having @charred fractal : result = cursor.fetchall() currentCoins = result[0][0]
If you only need the first value, use fetchone()
but I have shown you how to write that query without that line
charred fractal
ooh
rain wagon
cursor = mydb.cursor()
cursor.execute("SELECT client_id, user_coins FROM lottery")
entries = cursor.fetchall()
winner = random.choice(entries)
reward = sum([entry[1] for entry in entries])
cursor = mydb.cursor()
cursor.execute("UPDATE users SET user_coins = user_coins + %s WHERE client_id = %s"), [reward, str(winner)])```@charred fractalI see now.
gleaming quest
Using :: is okay in Postgres, but PyCharm shows that it doesn't
rain wagon
go to the settings and set postgres dialect
charred fractal
but it should stop them anyways as I define coins as an int
gleaming quest
It is set
charred fractal
and not an arg.
rain wagon
@gleaming quest
gleaming quest
Yup
rain wagon
You can also set project dialect
gleaming quest
I'm doing this
charred fractal
great, ```Error: discord.ext.commands.errors.CommandInvokeError: Command raised an exception: DataError: 1241 (21000): Operand should contain 1 column(s)
async def coins(ctx, user: discord.Member = None):``` I think someone could possibly try to enter something wrong here.of course ```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 MySQL server version for the right syntax to use near '%s WHERE client_id = %s' at line 1
I used currentCoins = result[0][0] multiple times
and now why is it giving me trouble here?
anyone?
charred fractal
Finally fixed it.
patent glen
@charred fractal while it is true that if you're getting it from an api field that is an int the user can't control it to be an arbitrary string, it's still a bad habit to get into
oops didn't see that that was all from yesterday
wooden roost
hihi!
torn bane
You know what i am trying to do
Assuming you don’t know the column name
All you know is *
So how to approch this?
blazing arch
SHOW COLUMNS FROM table_name;
This will give you a list of the columns that you can then use inside your concat
gleaming quest
How can I UPDATE values to columns whick names are stored in Python tuple? ```py
names = (
'Total members: ',
'Now online: ',
'All time max: ',
'Humans: ',
'Bots: ',
'Bans: '
)
channels_columns = (
'total',
'online',
'max',
'users',
'bots',
'bans'
)
Creating channels and saving them to database
for i in range(6):
if result[i] is True:
new_channel = await ctx.guild.create_voice_channel(name=str(names[i] + '0'), category=new_category)
c.execute(
'''
UPDATE settings_stats
SET %s = %s::bigint
WHERE guild_id = %s::bigint
''',
(
str('channel_id_' + channels_columns[i]),
int(new_channel.id),
int(ctx.guild.id)
)
)
Output says, that this str('channel_id_' + channels_columns[i]), is evaluraing to 'string', not column name
sick nacelle
Anybody know how to implement a many to many relationship with sqlite in python? What Im trying to figure out how to create a many to many relationship between my Train table, and my Station table. I know I need a join table, which I created. But what I'm trying to figure out is how can I reference one train having many stations and one station having many trains. I'm not sure if I'm overcomplicating it or not. Heres my schema.
cur.execute("""DROP TABLE IF EXISTS line""")
cur.execute(
""" CREATE TABLE line(
pk INTEGER PRIMARY KEY AUTOINCREMENT,
line_name VARCHAR
);""")
cur.execute("""DROP TABLE IF EXISTS station""")
cur.execute(
""" CREATE TABLE station(
pk INTEGER PRIMARY KEY AUTOINCREMENT,
stop_id VARCHAR,
station_name VARCHAR
);""")
cur.execute("""DROP TABLE IF EXISTS line_station""")
cur.execute(
""" CREATE TABLE line_station(
line_pk INTEGER,
station_pk INTEGER,
FOREIGN KEY (station_pk) REFERENCES station(pk)
FOREIGN KEY (line_pk) REFERENCES line(pk)
);"""
)
that seems reasonably standard
you do need a third table for many-to-many relationships in sql, so that's not overcomplicated at all
@sick nacelle
simple fox
OKay I have a basic question. Idk where to ask this. (I am using .net) I made some changes to a program and saved it, which I know it got sent to our database(i am in a dev environment so not a big deal) But i was wondering how do I find what table it was sent to??
maiden sonnet
Hey everyone, is there anyone here who knows a bit about Flask-SQLAlchemy and relationships?
upbeat lily
People probably do. Its best to just ask
maiden sonnet
Right, makes sense. I'll first provide a brief description and will then see to upload code.
So, I basically I have an app using Flask-SQLAlchemy. It's a trivia database, and I have 3 tables (that are relevant here). trivia for the questions, users for users who can post new questions and categories .So trivia has one column author_id pointing to the users table (sry, confusing that I use author and user like that), and category_id to a category.
When a new question is inserted, I add the user object to the question object. Then I check if the desired category already exists (categories basically only has an id column as primary key and a category column which has a unique constraint). I do this by querying the db. If I get a result back, I simply attach this to the new question, otherwise I first create and commit the new category to the db, and then attach it to the question.
Finally I commit the question.
My problem: the saving always fails if the category already exists. I get an IntegrityError because the uniqueness constraint is violated. However, it should not create a new category entry in the first place! It's as if SQLAlchemy always wants to create a new category entry no matter if it already exists and regardless of whether I have already queried the db for this.
Interestingly, with the author (user) everything is fine - it's not trying to create new users!
I'll be super grateful if anyone has any ideas or insights. I've been googling like a madman and I can't find a solution
I'll try to upload relevant code snippets.
rich trout
@maiden sonnet have you tried passing echo="debug" to your engine constructor? That should let you see all SQL called and narrow down the issue
I can't figure out why it might be behaving like that without running it
delicate fieldBOT
Hey @cerulean harbor!
maiden sonnet
@rich trout Thanks for your answer
Yes I have the logs, I will add them to the gist
Will be at the computer in +-20min to do it
rich trout
@ me when you do
maiden sonnet
at it right now 😉
@rich trout added it as comment to the gist 🙂 https://gist.github.com/fedus/dafe47a3f95a2a49e6d824052369a809
thanks for your time!
rich trout
this is interesting:
the error is actually happening during your query to find out if theres a dupe
Category.query.filter(Category.category == raw_category)
is the erroring line
maiden sonnet
how did you pinpoint the line? I feel this is a silly question xD
maiden sonnet
ahh indeed
rich trout
- Theres a query for trivia by userid, then an immediate insert into the db
- The exception occurs while querying to determine if there's a dupe in the DB, and it notes it's a "previously caused error"
Therefore, somewhere between the trivia query and the Category name query an insert is being run
maiden sonnet
yes, that's what I also read from it, it tries to insert too early, but I don't know why it does that?
rich trout
There's only one line that actually does anything between them
new_trivia = trivia_schema.load(trivia, session=db.session)
rich trout
So I suspect that line is creating a category object, which causes an SQLAlchemy insert for some reason
really not familiar with marshmallow though
maiden sonnet
that's indeed what it does, it's marshmallow taking the raw data from the API call and parsing it into a category object
that's precisely what it's doing
now, I suspect because the raw data already has "category" information, it tries to add it to the category table before my check if it already exists even gets a chance to run
rich trout
right
I think it would be appropriate to put that category check inside the category schema somewhere
maiden sonnet
the only thing is I tried to base what I did on an example from sql alchemy (if you want, you can check the Author/Quotes example at https://marshmallow.readthedocs.io/en/stable/examples.html#quotes-api-flask-sqlalchemy, it's luckily quite small, you can check the "add quote" route)
def new_quote():
json_data = request.get_json()
if not json_data:
return {"message": "No input data provided"}, 400
# Validate and deserialize input
try:
data = quote_schema.load(json_data)
except ValidationError as err:
return err.messages, 422
first, last = data["author"]["first"], data["author"]["last"]
author = Author.query.filter_by(first=first, last=last).first()
if author is None:
# Create a new author
author = Author(first=first, last=last)
db.session.add(author)
# Create new quote
quote = Quote(
content=data["content"], author=author, posted_at=datetime.datetime.utcnow()
)
db.session.add(quote)
db.session.commit()
result = quote_schema.dump(Quote.query.get(quote.id))
return {"message": "Created new quote.", "quote": result}```That's from their example.
I agree with you it would feel cleaner to put this check to the category schema in any case - I admit this is my first project with flask and sqlalchemy and I'll have to do more research in how to move it there. I guess that's why I probably will want to get it working first and then move it in a second step, if that makes sense?
rich trout
The issue with that is marshmallow seems to be creating an object in all cases, so there's no way to "get it working first" without somehow intercepting that step
I'm not familiar enough with marshmallow to know how to do that, I'll take a look at the docs
But IMO it would be fairly easy to override the Category model type to "Fetch and create if not found"
since you've got an interesting scenario--while your primary key is an integer, you effectively have a second primary key in your unique "label" value
maiden sonnet
(I wrapped the controller code in with db.session.no_autoflush: , interestingly now from the sql you can see it's not inserting at the beginning, but then when I finally flush, it still wants to create a new category object right before writing the new trivia question ... I added a comment to the gist with log output)
(Also, THANKS so much for your time and help already)
rich trout
There's a https://marshmallow-sqlalchemy.readthedocs.io/en/latest/ library (that I've never seen before now), and it provides a "load instance" interface, so this is clearly a known detail
maiden sonnet
yes probably I wouldn't have needed an extra category table at all ... but I thought maybe i'll add meta data to categories later like descriptions etc so that's why it's there xD
rich trout
and the demo on that page indicates using it exactly how you're expecting things to work
author = Author(name="Chuck Paluhniuk")
author_schema = AuthorSchema()
book = Book(title="Fight Club", author=author)
session.add(author)
session.add(book)
session.commit()
dump_data = author_schema.dump(author)
print(dump_data)
# {'id': 1, 'name': 'Chuck Paluhniuk', 'books': [1]}
load_data = author_schema.load(dump_data, session=session)
print(load_data)
# <Author(name='Chuck Paluhniuk')>
It's also entirely possible to adjust the marshmallow schema so that you can load a category in your own code
maiden sonnet
(just having a re-read of some things on that page)
maiden sonnet
I think I'm simply a bit confused because of the nuances between the "barebones" marshmallow and sql-alchemy-marshmallow
could I ask how you would approach this check for add-category-only-if-it-doesnt-exist-yet ?
rich trout
If you've got an environment set up
try their barebones example with two quotes by the same author
or
pearl heath
can someone tell me why my flask-sqlalchemy project is adding tables to the DB but not populating anything else? I am seeing something about a connection error in the VSCode debugger. this is a brand new debian stable install, this shouldn't be happening right? am I missing database setup code ? can someone give an example of a start to finish flask-sqlalchemy basic, one model, database app with sqlite? I am being told the test code I have works for other people but I think there might be a package I'm missing or maybe they have setups configured perfectly already
rich trout
The difference between yours and the example is they do not bind their author schema to a sqlalchemy model
In fact, they're only using the schema for validation, not for object creation
To do the check, override the .load() on your schema for category, and have it do the query you have now
pearl heath
from flask.config import Config
from flask import Flask, render_template, Response, Request ,Config
from flask_sqlalchemy import SQLAlchemy
HTTP_HOST = "gamebiscuits"
ADMIN_NAME = "Emperor of Sol"
ADMIN_PASSWORD = "password"
ADMIN_EMAIL = "game_admin"
DANGER_STRING = "TACOCAT"
class Config(object):
SQLALCHEMY_DATABASE_URI = 'sqlite:///' + HTTP_HOST + '.db'
SQLALCHEMY_TRACK_MODIFICATIONS = True
server = Flask(__name__ , template_folder="templates" )
server.config.from_object(Config)
database = SQLAlchemy(server)
database.init_app(server)
class User(database.Model):
user_id = database.Column(database.Integer, default = 0, primary_key = True)
username = database.Column(database.String(64), default = "tourist", index=True, unique=True)
email = database.Column(database.String(120), default = DANGER_STRING , index=True, unique=True)
password_hash = database.Column(database.String(128), default = DANGER_STRING)
turns_run = database.Column(database.Integer, default = 0)
cash = database.Column(database.Integer, default = 1000)
def __repr__(self):
return '<User id:{} name: {} >'.format(self.user_id , self.username)
admin = User(username=ADMIN_NAME, user_id = 1, email=ADMIN_EMAIL , password_hash = ADMIN_PASSWORD)
guest = User(username='guest', user_id = 2, email='test@game.net' , password_hash = 'password')
user = User()
database.create_all()
database.session.add(admin)
database.session.add(user)
database.session.commit()
Make sure to call super() if you don't find one, and if you want to be future proof handle a validationerror by querying again
pearl heath
it fails at commit()
maiden sonnet
@rich trout thanks. I think the reason I use it for object creation is because I get raw json data from the api call, and they just instantiate an example object on the spot - or am I reading this wrong?
rich trout
They have to do this:
quote = Quote(
content=data["content"], author=author, posted_at=datetime.datetime.utcnow()
)
You don't
They load into a model-less schema, and then use the schema attributes to do their logic
That is, they load the schema into data, then use data['first_name'] to check the author object
I assume the default return from an object without a model is a dictionary or similar
@pearl heath what's the error?
maiden sonnet
ahhhhhhh now I see (I think) ... oh dear, yes, the example on the normal Marshmallow page has a Schema and a Model but they are not linked, whereas mine are
rich trout
yep
maiden sonnet
baby steps xD
rich trout
So now the three options are clear--override Category.__init__ so you can't accidentally dupe one, override CategorySchema.load so you load without creating dupes, or handle the models manually
maiden sonnet
thanks a lot ... I feel like at least now I conceptually see better what's going wrong, I'll have to try a few things now. Also thanks for listing the options. I'll probably just try to get it working manually now and clean up later 😉
rich trout
👍
maiden sonnet
@pearl heath as you can see I'm not a pro myself but maybe your sqlite URI has an error? It has three slashes /, afaik it should have 2?!
pearl heath
for a network URI it will be two slashes, local folder files are three, absolute paths are four slashes
maiden sonnet
ah, good to know ... sorry that wasn't helpful then ...
pearl heath
nah you cool
maiden sonnet
what the exact error then?
pearl heath
well right now Im getting unique constrint failed for user.userid but the code I plan on putting in the game im translating to python gets a column userid not found for table user and sqlite3 shell shows the tables created but empty
and no matter the number of entries i make, the file is always 16.4 kb
im working on trying a different way of mapping these two classes but nothing is working right for just normal looking code leading me to think its a deeper issue like a package missing or some "everybody knows this so we don't bother telling you ,you should be born with this knowledge" type dealio
rich trout
You need to provide autoincrement=True to your user id
rich trout
probably
maiden sonnet
i'm also not sure providing a default value for a primary key makes much sense, but that's not what makes it fail
pearl heath
nope 😦
this is so annoying, Ive been stuck on this for days now and I cant make any progress translating until I can start using the functions I've translated already
rich trout
Are you sure there isn't a second sqlite database hiding somewhere?
pearl heath
PHP --> python is super easy to do text replacments lol
rich trout
Try doing a query before adding any *Users in your script that's failing with a constraint error
pearl heath
@rich trout the code I provided is the simplest code to accomplish database work in flask-sqlalchemy?
rich trout
close enough
pearl heath
hmm new instance user conflicts, lets delete some files lol
omfg has my problem the past two days been that I havent been del;eting the db file after every test?
but I tried this in bpython with random db names!
rich trout
lmao >.<
pearl heath
it CANT be that
rich trout
ooo how were you doing random
pearl heath
just stupid funny names
rich trout
ah
pearl heath
but yeah it seems that was the problem
rich trout
if urandom isn't available, there's a good chance the seed stays the same between runs
I know this from experience ;-;
pearl heath
oh ive had that issue before when i first started a long time ago
I usually take a bit of random garbage from places besides urandom also
slaps number multiply it by a million digit prime number and she runs like a hippo! number falls apart
rich trout
lol
pearl heath
I thank you! you have helped me this day and I shall leave fulfilled!
how would you handle a database request intended to be sent via request interception in burpsuite? I plan on having a hacker mode for this game wherein you modify requests to add an extra POST field and put the expected data in it and you'll be authorized to access a network view of local ships in other systems thats just data sent and laid over a template... but it's like... intentional hacking... how would you do that safely?
rich trout
Intentionally poor deserialization maybe?
like, say, a post request like this:
{
"method": "INSERT",
"table": "ships",
}
And then validate it internally with the appropriate missing checks for "secret" hackable access?
I guess, the answer is "same as any other api just not publishing the trick to get the data out"
an interesting option could be leaving SQL injection open, but restricting permissions so that "bad things" can't be easily done, but idk what your expected UX is