if I manually insert straight into the db using compass community (not code)
#databases
hollow stream
it depends, the mongo shell rounds your number in some case and you have to wrap it in a string
old scarab
why would it round my number
old scarab
WILD
that is SO WEIRD
thanks for the article I tried searching it and couldn't find anything probably because it was a hard issue to describe
hollow stream
i’m not huge fan of Document Storage
this might make me never use it in the future ever
old scarab
LOL
yeah this is super inconvenient wtf
I want to save my specific number not a rounded number this is so wild
toxic rune
Hi I made a post on Code Review (since it's too long to post here) about some postgresql stuff if anyone can check it out (i don't have high hopes of it being answered there due to the really low amount of sql questions in there) https://codereview.stackexchange.com/questions/232537/storing-multiple-translations-for-retrieval
serene slate
quick question:
I am going to scrape several website (20-40) and extract the same kind of information from all of them.
There will be about 10 columns of data saved in the MySQL database.
Would it be best to create a different table for each site or would you just have 'site name' as a column in one large table?
hollow stream
how about sites table and content with site_id column
serene slate
so it is used as a sort of look up
?
get the ID from one table and look for it in the data table?
thinking about it, that is a good idea as I want to display the data on a website so having multiple tables will complicate queries
upbeat lily
This is quite a theoretical question, but I have an (enhanced) entity relationship diagram with like a 3-way relatiionship. A river connects to a lake at a county border, where river, lake, and county are all entities. Is a 3-way relationship like that allowed, or do I need to resolve it in some way?
rich trout
You can do that with an intermediate table, which is also a recommended way to do a two-way relationship
That is, table1 is Country, table2 is River, table3 is Lake, Table4 has three columns: country, river, lake
bright dragon
Hi
I am making a psw manager app
with qt and pyside2
should I have any consideration when choosing what db to use?
everything I have already made in my backend I made it with sqlite3, and I want to store the passwords as a string that has been encrypted with fernet
and in live time in the ui, when the user has pressed a button decrypt them all
floral mason
SQLite stores data as raw text, but has basic security features in that only authorized users may access the database. That basically means it’s as strong as the security level enforced by the user.
bright dragon
So it will be the same that storing the passwords in a word file
I think I couldn't hope for anything else
gilded narwhal
i know there's at least one sqlite extension for encrypting the database
dunno how easy it would be to get working with python though
noble kite
Hello. I'm looking to store product information in a database and I figure the best way is to store each product's set of info in a JSON object. Is there a best practice for this?
full jungle
@noble kite
... store each product's set of info in a JSON object. Is there a best practice for this?
I would recommend treating the top level (0th) of the JSON object as the tables, the 1st level as the attributes, and the 2nd level as the values. For example: ```json
{
"Products": {
"ProductID": [0, 1, 2, 3, ...],
"ProductName": ["Product1", "Product2", ...],
...
},
"Vendors": {
...
},
"Other_Table": {
...
},
...
}
Ah excellent!
torn sphinx
Hey guys, I'm facing a weird issue where when I pass an array of Object IDs arr = [3, 4, 1] and then query them in SQLAlchemy using Object.query.filter(Object.id.in_(arr)).all() it returns the queries but in the [1, 3, 4] order
Is there a way to mitigate this?
frozen fossil
function getuserinfo($connection, $db, $uid, $row)
{
$stmt = $connection->prepare("SELECT * FROM $db WHERE UID=$uid");
$stmt->execute();
$row = $stmt->fetch(PDO::FETCH_ASSOC);
return $row["amount"] > 0;
}
``` im trying to port this function to python does the fetch work the same as the mysql connector fetchall ?fetchall would return an iterable of rows
rich trout
You're looking for fetchone(), perhaps?
bleak shoal
Hi like to ask a question on recommendation on how and which program is good for creating blockchain
gritty pine
hey with sqlalchemy automap is there a way to limit the reflection to specific tables to speed things up?
robust tundra
hello
can someone help me add tables for this code
async def score(ctx, s1=None, s2=None):
try:
s1 = int(s1)
s2 = int(s2)
except:
return
if s1 > s2:
await ctx.send("Team 1 wins!")
elif s2 > s1:
await cts.send("Team 2 wins!")
else:
await ctx.send("Its a draw!")```
tawny sail
Can you be a little more specific plz
robust tundra
yes
1sec
@tawny sail
i want to create a table to go into the database
select current games
update the wins/losses
and delete the current games
tawny sail
So u probably wanna have a table called games, columns can be players, wins, loses and anything else u need?
tawny sail
Oh
tawny sail
Which db
robust tundra
sqlite3
robust tundra
whats what?
tawny sail
tawny sail
It's a library which let's you connect to a database
robust tundra
why would i use that when i already have sqlite3?
tawny sail
Then what's the issue
tawny sail
Have u learnt how to connect to a dB and run sql commands via python?
tawny sail
Then u wanna do that first
robust tundra
i mean my friend helped me
do the other cmd
!start
it reaches into the DB gets players and adds them
to games
tawny sail
Watch the tutorial and learn how to connect to dB and do stuff with python
robust tundra
alright well, ill try again i guess i just dont understand it
tawny sail
Well u don't wanna rely on ur friend teaching and keeping u with just half knowledge. It better to learn the basics of sql and also sqlite3 lib, the video above
robust tundra
wanted 2 see if someone could help me create it
tbh its the last cmd on the bot that i need to work on
and its finished
tawny sail
Yea I get it but ur gonna benefit a lot more if u can learn stuff too
Rather then me giving solution :)
robust tundra
you're right I know. id rather have the solution though if you wanna help me on your free time let me know cuz i know i wont understand it thanks anyways
tawny sail
Well the thing is ur not ready to learn, if u say u won't understand then it means u don't want to, if that's the case....
plain radish
!mute 294308151749246983 5d This community is here to provide help to people who want to learn python. Our members are not to be used as someone to do your bidding. Nor do we allow recruitment of any kind here for the purpose of you getting people to do things for you, with or without pay. I suggest you seriously reconsider why you're here.
delicate fieldBOT
:incoming_envelope: :ok_hand: applied mute to @robust tundra until 2019-11-25 07:00.
plain radish
@regal rapids commentary on something that's already had been solved through moderative action isn't constructive or on topic to the channel. please avoid it.
thank you
torn sphinx
How exactly foreign key usage impacts a database except binding entries together? Does it save space or make it process queries faster?
patent glen
it prevents you from having data that falls out of sync (i.e. if you deleted the entry in the parent table it can automatically delete it, and it can prevent you from adding an entry that doesn't have a valid parent id)
for query performance it may be a good idea to create an index
torn sphinx
Okay, I'll read about database indices. Thanks
torn sphinx
I'm wondering if there's a way to dynamically input data into columns in sqlite3. Specifically, I want my update function to take in kwargs and update the db only for ones that are passed in. Is there a way to do this without just using a list of conditionals?
sinful stratus
What exactly is your kwargs
torn sphinx
Im writing a discord bot and I'm saving muterole id, modrole id, log channel id, and modlog channel id in the guild table. So I want to be able to update as many values are passed in.
def update_guild(self, guild_id, **kwargs):
self.c.execute('UPDATE Guild SET muterole_id=?, modrole_id=?, log_channel_id=?, modlog_channel_id=? WHERE guild_id=?', (guild_id))
self.conn.commit()
self.logger.log(level=logging.INFO, msg=f'Updated guild {guild_id} in database')```dont use **kwargs
also this is blocking
use async
use optional args for your function
I suggest postgres via asyncpg library
grim iris
Hi guys im working on a knowledge data base which has many documents about machine information and also information about how to fix troubleshoots which are connected with machine information. The Sources come from help desk and engineers. So a lot of unstructred data and structured data which i need to combine at the end for some ai models.
torn sphinx
Midorina I'm a little confused. Aren't kwargs optional? Also not sure what you mean by blocking.
patent glen
er, kwargs or not, you still need to detect the presence or absence of the arguments
torn sphinx
I made a way to do it but I'm not sure if it protects against injection
patent glen
something like this
def update_guild(self, guild_id, **kwargs):
dynamic_update = ','.join(f'{name}=?' for name in kwargs.keys())
query = f'update guild set {dynamic_update} where guild_id=?'
self.c.execute(query, (*kwargs.values(), guild_id))```...how is your function being used that you need to worry about injection
are you constructing kwargs directly from user input in commands?
torn sphinx
nah
patent glen
or are you just doing update_guild(guild_id, muterole_id=whatever) one place, different field another place, etc
you don't really have to worry about injection unless it's from user input, though you could make the function more robust by checking to make sure all of the keys are valid column names
torn sphinx
functions that call this will have static kws specific to that function
patent glen
i will say the usual way to do this kind of thing
is to use an ORM and fetch the whole row as an object, make the changes locally, then save the whole row back
like there's a reason you don't see this kind of thing often
torn sphinx
why is it that people do that more?
patent glen
mainly because it's considered better to leave dynamic sql building to libraries designed to do it
torn sphinx
ah
fringe tiger
async with ORM, how and what libs to use? Preference sqlalchemy. Tried Databases but I just keep going in circles.
hollow wadi
celest zodiac
@hollow wadi Are you saying, "how do I query my database from my code"?
torn sphinx
With flask-sqlalchemy, can I make it so whenever I try to create a child foreign key entry I also create the parent entry if it doesn't exist?
Right now I catch the integrity error and create parent in there, but maybe there's more elegant solution
Like this
@app.route('/react/', methods=['POST'])
def create_react():
data = json.loads(request.data)
if not data or not 'message_id' in data:
abort(400)
try:
react = Reaction(data)
db.session.add(react)
db.session.commit()
return 'OK', 201
except IntegrityError as e: # Foreign key violation?
db.session.rollback()
error = str(e.orig)
if 'guild' in error and 'is not present' in error:
guild = Guild(data['guild_id'])
db.session.add(guild)
db.session.commit()
create_msg()
return 'OK', 201```
pure scroll
which database engine you are using
torn sphinx
Postgres
pure scroll
well you can do something like first insert a guild with with on conflict do nothing statement
and then insert reaction
torn sphinx
Thought about that too, but couldn't figure out what would be better. I think it's less likely for parent not to be there, so I decided to catch the exception instead of trying to insert parent each time I'm inserting child. But since I'm not really experienced with this yet, I thought that there could be some relationship magic that can create a parent entry automatically
torn sphinx
I have 2 servers with different hardware, 1 query with same execution plan, no disk reads and two different execution times
I'm interested in seeing what exactly is the bottleneck on the server that has slower time for this query
I'm guessing it's either cpu/ram
any idea how to find out which it is?
minor ruin
Might help if you list database software, OS and overview of hardware
rain wagon
@torn sphinx run vmstat -n -w -S m 1 | tee ~/$HOSTNAME.vmstat.dat when doing queries and analyse the output. I have written a python script to make graphs out of such data, if you search around you can find it
cpu is sy + us and ram is swpd + buff + free. But don't forget to monitor si so and bi bo as well, which is swap and general i/o.
assuming linux of course, but you didn't specify.
muted badger
Hi there! Is anyone familiar with mongoDB?
dire cedar
i'm using mysql.connector, from python
should i do cursor = db.cursor() each time i try to read or write sth, or i can do it only once?
or could i initialise it only in my data parser class in the __init__ and each time you try to obj.read or obj.write it just uses self.cursor without redifining it
grim iris
Doing a web application which should save documents, photos, audio, text (unstructured data) and also structured data. I thought that i save the videos and audio in a blob storage (azure) others should be raw. My approach is to implement this on mariaDB, anybody got some exp regarding this?
jade willow
hi I was working with flask sqlalchemy on heroku...can u give me a hint on how to create tables in webapp......I currently have to create table manually before deploying app or it creashes
ionic pecan
what do you mean with "in webapp"? you want to run migrations from your application itself?
while it's running?
rose parrot
I'm having trouble to set up my DB with Flask-SQLAlchemy, I'm messing up implementing my design.
I'm basically trying to link the classes User (which is also linked to Flask-Login through UserMixin) to Student, Teacher and Class.
Both Student and Teacher inherit from Users, a User can be a Teacher and/or a Student (but not both when associated with a single Class), each Class has Class.students and Class.teachers, it should be possible to access User.classes (which joins every class the User teaches and/or studies) and it should be possible to retrieve a list of Class objects separately using User.teaches and User.studies.
It seems like I have to use both Many-to-Many relationships, Joined Tables Inheritance + Polymorphic and Association Tables. Is anybody in here up for a help or can recommend a good guide for such a complicated (at least for me) design?
rose parrot
I'm trying to get each bit right, but I'm messing it in the very beginning.
class StudentAssociation(db.Model):
__tablename__ = 'students_x_classes_association_table'
user_id = db.Column(db.Integer, db.ForeignKey('user.id'), primary_key=True)
class_id = db.Column(db.Integer, db.ForeignKey('class.id'), primary_key=True)
class_ = db.relationship('Class', back_populates='students')
student = db.relationship('User', back_populates='studies')
class TeacherAssociation(db.Model):
__tablename__ = 'teacher_x_classes_association_table'
user_id = db.Column(db.Integer, db.ForeignKey('user.id'), primary_key=True)
class_id = db.Column(db.Integer, db.ForeignKey('class.id'), primary_key=True)
class_ = db.relationship('Class', back_populates='teachers')
teacher = db.relationship('User', back_populates='teaches')
# Clas definition
class User(db.Model, UserMixin):
# Table information
__tablename__ = 'user'
# Mappers
id = db.Column(db.Integer, primary_key=True)
username = db.Column(db.String(250), unique=True, index=True)
# Polymorfic
role = db.Column(db.String(240))
# Relationship
studies = db.relationship(StudentAssociation, back_populates='student')
teaches = db.relationship(TeacherAssociation, back_populates='teacher')
__mapper_args__ = {
'polymorphic_identity':'user',
'polymorphic_on':role
}
class Student(User):
__tablename__ = 'student'
__mapper_args__ = {
'polymorphic_identity':'student',
}
class Teacher(User):
__tablename__ = 'teacher'
__mapper_args__ = {
'polymorphic_identity':'teacher',
}
class Class(db.Model):
# Table information
__tablename__ = 'class'
# Mappers
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(20))
description = db.Column(db.String(240))
students = db.relationship(StudentAssociation, back_populates="class_")
teachers = db.relationship(TeacherAssociation, back_populates="class_")
if the first line of the codeblock is ```python it'll also highlight the code for you making it better to read
rose parrot
I'm also not quite sure how to implement the role to vary between [], ['teacher','student'], ['teacher'] and ['student'] depending on the user :P
Thank you for the tips about formatting here :)
plain radish
you're welcome.
i'll have to be clear, i don't use sqlalchemy personally, so im unsure on syntax for building the models you require. however i can at least give a brief idea as to how i'd approach it and explain kinda why
rose parrot
It'd be awesome if you could share your thoughts on it
plain radish
are your association tables to handle the cases of many-to-many entries?
rose parrot
Yes, I'm actually using an Association Object, but using an Association table is an option according to https://docs.sqlalchemy.org/en/13/orm/basic_relationships.html#association-object
plain radish
okay, so it's an orm construct
rather than a real table
the idea though plays similar to my own thoughts
to give you an idea
your structure overall is heading in the right direction
as long as the schema supports the relationships without much hassle, your models should be able to retrieve related data through it's relationships
rose parrot
Hm, what's the name of the topic which covers what each of those different types of links mean?
I'm actually a civil engineer, so I'm not used to this diagram notation
plain radish
they're just generalised relationship indicators in that model view
a line is coming from one side and goes towards the end of the line with the dot
and a solid line defines an identifying relationship, which means the foreign key that's related to the other table is also the primary key of the current table
so you'll see user has two relationships
one with teacher and one with student
both are identifying, which means you're limited to creating a PK that is the same value of an existing user ID and relates to an existing user
so there's a clear direct relationship to query student or teacher related tables through it
the dotted lines are just normal non-identifying foreign keys
so user and teacher table have a one-to-one relationship since it's identifying
and teacher to assigned class doesn't have that limit, so you can have multiple teachers in the assigned class
overall this subject would just be called something like "sql relationships"
i'm sure you'll be able to find a lot of resources on the subject
torn sphinx
hey
i have a website here
with text that you can edit
but i want the text a user will edit in there to turn into a different color or will be underlined
for example
when the user adds additional letters, those letter need to turn into different color or need to be underlined.
any ideas on how i can do that?
to make this possible (Making the text on my webpage editable), i came here in the first place.
so i'l be able to pull this of too if its possible. I just need some advice and steps
rose parrot
@plain radish I'll keep digging how to implement it using SQLAlchemy, seems like there's a lot of constructs to do it that I'm not quite certain how to properly use. I appreciate the explanation, your time and effort
plain radish
no probs
it helps to get a ensure you establish the sql-side of things first anyhow
once that's done you can find out later how to do equivalents with an ORM
makes searching a lot easier
also helps with connecting what a construct in an ORM is doing on the SQL side
since sometimes it can be super confusing dealing with some abstraction of a something unknown
torn sphinx
yes, and when i submit the changes, the color needs to stay changed
oof
got a document or tutorial which cover those like stuff
plain radish
uh
@torn sphinx did you mix up the channels
#web-development might be what you were after?
torn sphinx
it has to do with both
plain radish
i'm not sure how it's related to databases
torn sphinx
i want to store the submitted data with the colors in the databse
rose parrot
You should ask in the webdev the formatting part and then when it comes to storing it, you should ask that part here
It's something that I've learned recently called separation of concerns
I think
torn sphinx
ohkay
delicate fieldBOT
runic badger
hey im working with sqlite3. what im doing is selecting a collumb from the database, and using a try except if that collumn does not exist. I need to have an exception raised if the collumn doesnt exist. although the way im doing this is an injection vulnerability. i could pass it in as a proper parameter but the exception is not raised if i do it that way. anybody know a way around this?
# this is insecure but it works and is what im doing rn
c.execute(f'SELECT {"asdfasdfsadf"} FROM clans WHERE user_id = ?;', (id,))
Traceback (most recent call last):
File "<input>", line 1, in <module>
sqlite3.OperationalError: no such column: asdfasdfsadf
# this is secure but doesnt raise a "sqlite3.OperationalError: no such column" exception
c.execute('SELECT ? FROM clans WHERE user_id = ?;', ('asdfasdfsadf', id,))```i need the exception raised somehow. or some way to know if the collumn exists or not. i also dont want some long voo doo check magic, this needs to be increadably quick.
humble pike
Hi guys,
Anyone here use SQLalchemy?, I have a situation where I want to update 300 records at once, and if that record doesn't exist, insert it into the db.
I'm sure there must be a way of doing this, maybe i'm over complicating it. Thought merge might be the answer.
patent glen
@runic badger your second version returns the literal string, not the value of the column
where does your column name come from? there's not really a way to do what you're asking
if you want to find out what columns a table contains you can do select * from [table name] then keep the list of columns around and just use that
anyway most database designs just use a fixed set of columns, the whole thing you're doing is very unusual
runic badger
ik it is, essentialy what its actualy supposed to be for is for a chat bot with gameifacation. the collumns are supposed to be the clans. but a user may have a custom clan that is not in the database, and so it needs to be treated differently
and im gonna add an update soon for users to set there custom klans themselves so i need to make sure its secure
and yes, ik that if they make their clan "user_id" it will brake. ill remembet to filter for that
hmm i think ill just skip the exception loop and just use the hardcoded list of real clans to see if its custom or not. if not then i can assume its safe and not a rhat (becayse why would by own clans be rhats) if it is not found in the list and is custom it skips the sql and does what i want it to do.
patent glen
you should just have a row for each clan
with a table joining rows and clans together
you're gonna have to do that anyway for the custom clans, so why treat real clans specially
runic badger
there are 24 rows. 1st is the user id and the others are how much xp they have contributed to each clan. i need to keep a history of all xp commited per clan per user. so each new inserted row is a users record of there xp added to that clan
thats why i have it setup that way
and just tested it, checking if there clan is in the known safe and non custom clan list works great. idk why i didnt think about this before
frozen fossil
import mysql.connector
config = {
'user': 'root',
'host': 'localhost',
'database': 'shoppy',
'raise_on_warnings': True
}
cnx = mysql.connector.connect(**config)
cursor = cnx.cursor(buffered=True)
query = ("SELECT * FROM `items`")
print(cursor.execute(query))
cursor.close()
cnx.close()
``` I am trying to run this but it always returns None U missed a line dude
Results = cursor.fetchall()```
After
`cursor.execute()`And then print results
@frozen fossil
frozen fossil
ooh yeah
fossil leaf
@tawny sail can u help me plz?
i have this code
sql1 = (f"INSERT INTO mons(user_id, Level, Name, HPiv, CCiv, CCDEFiv, FRiv, FRDEFiv, MGC, SPD, num) VALUES(?,?,?,?,?,?,?,?,?,?,?)")
val1 = (str(ctx.author.id), 5, arg, hpiv, CCiv, CCDEFiv, FRiv, FRDEFiv, MGC, SPD, result[1]+1)
conn.execute(sql1, val1)
sql = (f"UPDATE usr(user_id, total)")
val = (str(ctx.author.id), result[1] + 1)
c.execute(sql, val)```i keep getting val1 = (str(ctx.author.id), 5, arg, hpiv, CCiv, CCDEFiv, FRiv, FRDEFiv, MGC, SPD, result[1]+1) TypeError: 'builtin_function_or_method' object is not subscriptable
primal valve
whats result?
fossil leaf
o
ty i forgot the parenthesis
im blind lol
fixed but i got another error
Command raised an exception: TypeError: can only concatenate str (not "int") to str```@primal valve do u know why im getting this?
c.execute(f"SELECT user_id, total FROM usr WHERE user_id = '{ctx.author.id}'")
result = c.fetchone()
if result is None:
await ctx.send("You Haven't Started yet!")
ivs = 0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31
hpiv = random.choice(ivs)
CCiv = random.choice(ivs)
CCDEFiv = random.choice(ivs)
FRiv = random.choice(ivs)
FRDEFiv = random.choice(ivs)
MGC = random.choice(ivs)
SPD = random.choice(ivs)
sql1 = (f"INSERT INTO mons(user_id, Level, Name, HPiv, CCiv, CCDEFiv, FRiv, FRDEFiv, MGC, SPD, num) VALUES(?,?,?,?,?,?,?,?,?,?,?)")
val1 = (str(ctx.author.id), 5, str(arg), int(hpiv), int(CCiv), int(CCDEFiv), int(FRiv), int(FRDEFiv), int(MGC), int(SPD), int(result[1]+1))
conn.execute(sql1, val1)
sql = (f"UPDATE usr(user_id, total)")
val = (str(ctx.author.id), result[1] + 1)
c.execute(sql, val)```thats the full block
primal valve
where exactly?
fossil leaf
val1 = (str(ctx.author.id), 5, str(arg), int(hpiv), int(CCiv), int(CCDEFiv), int(FRiv), int(FRDEFiv), int(MGC), int(SPD), int(result[1]+1))
TypeError: can only concatenate str (not "int") to str```yea
primal valve
but youre doing result[1]+1
fossil leaf
oof
primal valve
that wont work if result[i] is a string
fossil leaf
oooooof
ty im so stupid
and
yet another error
🤦
c.execute(sql, val)
sqlite3.OperationalError: near "(": syntax error```???????
my syntax looks gud to me
@primal valve sry for the troubleeeeeeeeeeeeeeeee ik im getting annoying
primal valve
yes
fossil leaf
sql1 = (f"INSERT INTO mons(user_id, Level, Name, HPiv, CCiv, CCDEFiv, FRiv, FRDEFiv, MGC, SPD, num) VALUES(?,?,?,?,?,?,?,?,?,?,?)")
val1 = (str(ctx.author.id), 5, str(arg), int(hpiv), int(CCiv), int(CCDEFiv), int(FRiv), int(FRDEFiv), int(MGC), int(SPD), int(total+1))
conn.execute(sql1, val1)
sql = (f"UPDATE usr(user_id, total)")
val = (str(ctx.author.id), total + 1)
c.execute(sql, val)
culorz = [0x9750C7, 0x000066, 0xA200FF, 0x0008FF]
embed = discord.Embed(color=random.choice(culorz), timestamp=message.created_at, title=f"{ctx.author[:-5]} Has Tammed A:", description=f"{arg}!")
await ctx.send(embed=embed)```?????????????
fossil leaf
;;;;;;;;;------;;;;;;;;;
frozen fossil
my mysql query returns [(2,)] how would i just get the 2 as a int and not as a list
fossil leaf
oof
primal valve
is there any way to prevent stuff like this?
same id but different word should be okay, but same id and same word shouldnt
primal valve
but same word and different id should be okay too
pale knoll
hello, maybe someone can provide me best database for big projects?
i was using mysql, but i think its old nowadays
rich trout
if you want to have two columns in combination to be unique, you should create a multi column unique constraint
IMO it's postgresql, but if you want to use the exact same interface, MariaDB is the modern equivalent to mysql
storm hawk
@pale knoll mysql is great.
void otter
@pale knoll
Postgresql is excellent for big projects, it has a lot of features and is blazing fast
torn sphinx
Anybody know PHP/MySQL ?
frozen fossil
https://raizo.de/p/6YOCp.png i want to check if the item exists (with a id as input) and if it exists it should give me the whole row(name price stock etc) 
rich trout
You can do a select() filtering by id, and see if the result set is empty
rich trout
Depends on how you're accessing the DB
frozen fossil
umm with mysql.connector
rich trout
If youre using fetchall() for example, it would return an empty list
frozen fossil
so if empty it returns False ?
rich trout
yes
frozen fossil
hmm thats great
rich trout
There's a pitfall which is if you don't convert to list (which fetchall() does), you could be checking against a generator and that wouldn't behave as expected, but I'm sure you're fine in that regard
frozen fossil
rich trout
for result in conn.execute(SQL):
print(result)
a = conn.execute(sql)
if a: # always true
pass
vs
a = conn.execute(SQL).fetchall()
if a: # True if there were results
dire cedar
(working with mysql.connector)
could someone explain me what's the point of cnx.cursor ? I know that basically, we'll do all the operations from it, to fetch and write data. But when should I initialise a new cursor? Only at the beginning of my script? Or each time i interact with db i re initialise the cursor?
Or only when i change the table i'm working with?
frozen fossil
# select if item exists
query = "SELECT * FROM `items` WHERE ID = %s"
val = (id)
exists = cursor.execute(query, val).fetchall()
if exists:
# get whole row
else:
await ctx.send("Item could not be found please contact the database administrator.")
``` alright i think this is itYes, but keep in mind exists would be [ .the whole row. ] (a one item list with the whole row inside
rich trout
SELECT id FROM ...
dire cedar
i'd be greatful if someone helps me out on https://discordapp.com/channels/267624335836053506/342318764227821568/647875685175525376
frozen fossil
@dire cedar in my expierence i only use one cursor all the time
even if switching tables
but i dont know if thats a good idea
rich trout
A cursor is a single "View" into queries and results
If you have multiple cursors, you can perform multiple queries "at the same time", although multiple connections is more common and more performant
frozen fossil
SELECT id FROM `items` WHERE ID = %s
```?yes
frozen fossil
ah ok
rich trout
presumably your items table has an id column
rich trout
You can do SELECT id, name, address FROM items to pick which columns are returned
dire cedar
well i use sql for my discord bot in my bot commands, and my bot events. My bot being asyncio based.
Do you advise me to use several cursors, one new each time i'm on another command/event, or only at the script beginning?
rich trout
I believe it returns a row inside a list--so you can do things like row.id or row.name
I would suggest a connection pool
dire cedar
what is that?
rich trout
pool = mysql.connector.pooling.MySQLConnectionPool(pool_name = "mypool",
pool_size = 3,
**dbconfig)
connection = pool.get_connection()
This allows you to reuse connections, but you will need to get a cursor every time as well
You'd initialize the pool when your bot starts, and whenever you need a connection grab one, and create a cursor and do querying
That way you can have multiple simultaneous connections for async usage
You might also want to look into https://github.com/aio-libs/aiomysql
frozen fossil
oh i may need that too probaly
dire cedar
oh my god, thanks so much
frozen fossil
@rich trout ```
SELECT itemname, price, stock FROM `items´ WHERE ID = 1
mysql.connector.errors.ProgrammingError: 1146 (42S02): Table 'shoppy.items´ where id = 1' doesn't exist
i am an idiot
sorry for the ping
frozen fossil
i should use 1% of my brainpower to atleast read my querys
oh that too
[('golden_apples', 4.0, 20)]
hmm
dire cedar
@rich trout with the async module u linked me, would i have to initialise the cursor each coroutine?
rich trout
yes
dire cedar
and using pool?
rich trout
You would call this every time:
async with pool.acquire() as conn:
async with conn.cursor() as cur:
await cur.execute("SELECT 42;")
print(cur.description)
(r,) = await cur.fetchone()
assert r == 42
Although I think that can inline to
async with pool.acquire() as conn, conn.cursor() as cur:
or just async with pool.acquire().cursor() as cur:
frozen fossil
also how would i sanitize input since any user can use that command
rich trout
pass things in as parameters
dire cedar
are pool.acquire and conn.cursor coroutines?
rich trout
cursor.execute("INSERT INTO employees "
"(first_name, last_name, hire_date, gender, birth_date) "
"VALUES (%s, %s, %s, %s, %s)", ('Geert', 'Vanderkelen', tomorrow, 'M', date(1977, 6, 14)))
They are async context managers
rich trout
Should be where id = %s, no?
frozen fossil
yes
dire cedar
but could i do sth like:
class Data:
def __init__(self):
self.cursor = pool.acquire().cursor()
async def read(self, params):
await self.cursor.execute(...)
...
async def write(self, params):
await self.cursor.execute(...)
...
You could, yes, but that would cause your cursors to potentially interweave
frozen fossil
but that dosent sanitize input, no?
rich trout
result sets are stored in the cursor
dire cedar
well, i create one Data object per coroutine ONLY
rich trout
The %s syntax does sanitize input
In that case maybe? but it's really best practice to use the context managers
frozen fossil
oooh thats awesome
dire cedar
In that case maybe?
Wdym?
but it's really best practice to use the context managers
yeah i understand that context managers with aenter property or sth are best practise. But one thing I absolutly want is being able to control the data of a same function with my custom class. Else it's an absolute pain to redifine everything such as cursors, the pre defined arguments in "WHERE" clause (that i would parse as initiliasing the Data obj), etc...
==============
Altho i wanna be sure that doing this class won't cause me issues such as interferences with results or stuff like that, IF i initialise a new Data object per coroutine / code block of a same goal.
@rich trout
rich trout
Cursors maintain their result sets until you clean them up manually, and don't always last too long (since they're specific to a connection, and if the connection dies they break IIRC), so it's usually best to create new cursors whenever you do "a new task"
If you're comfortable maintaining the proper workflow so that they work, then they will work, but it's unneccessarily risky
dire cedar
so ur saying that if i keep my initial intention and don't start making exception of its usage here and there, then it is optimised.
But as a whole its risky cuz i might do it at some point.
rich trout
right, and there is a chance that you do something that causes an error to appear inconsistently, and there's nothing so painful to debug as something like that
dire cedar
@rich trout i see, could ya give me an example of somethign i do that would cause an error to appear inconsistently, besides using my class out if its function.
knowing that my object can only .write and .read in a same table
rich trout
d = Data()
await d.read()
sleep(200)
# Put laptop to sleep and move it here
await d.write() # Exception happens here
or:
d = Data()
read, written = asyncio.gather(d.read(), d.write("data")) # exception: data.write() call results showed up inside d.read()'s execute call
this can happen because when d.read() await's into cursor.execute(), it's possible for asyncio to schedule the d.write() next, before getting the results, and then d.write()'s result-get, of d.reads() query result inside d.write() next
dire cedar
i seee
well, technically i can't really do what i said, cuz http://prntscr.com/q145vm
coroutine
unless i make the object asynchronously initiliased but at this point it doesn't have any sense any more
frozen fossil
@dire cedar im having a hard time running mysql with discord.py can you show me a snippet of your class?
rose parrot
I'm working with Flask + SQLAlchemy
I have two relationships on my models.py looking like this:
class User(db.Model, UserMixin):
#....
# Relationship
studies = db.relationship("Class", secondary=students_association, back_populates='students')
teaches = db.relationship("Class", secondary=teachers_association, back_populates='teachers')
Then, I wanted to create a user.classes retrieving the result of the joined user.studies and user.teaches.
Since I currently couldn't find my way through its implementation using relationships, my workaround was just like that:
class User(db.Model, UserMixin):
#....
@hybrid_property
def classes(self):
return(self.studies + self.teaches)
Is that a good workaround or should I try something else? Obviously I can't assign or delete user.classes values by doing this.
P.S: I also added a similar @hybrid_property on my Class object such as
class Class(db.Model):
#....
@hybrid_property
def members(self):
return(self.students + self.teachers)
So I can access class.members
frozen fossil
@rich trout are you still online?
rich trout
Still about, yes
frozen fossil
@rich trout can you help me with this class https://gist.github.com/7ada27d70bddea211823a69ac483e222
im getting a func error
and im pretty sure its not discord
rich trout
What's the full error message?
frozen fossil
let me get it real quick
rich trout
embed.set_footer(text= {id})
looks rather suspicious as well
rich trout
I believe this should be @commands.Command without the ()
But note how that's in python, so you'd probably want it to be text=id or similar
frozen fossil
oh yeah im a dumbo
https://raizo.de/p/9U0R5.png ayy im a dumbo ig,btw what was suspicious ?
rich trout
the text={id}
That creates a set from the contents of ID, but that function parameter takes a string
frozen fossil
well okay
sql = "INSERT INTO `items` (`itemname`, `price`, `stock`, `id`) VALUES (%s, %s, %s, %s)"
val = (itemname, price, stock, id)
cursor.execute(sql, val)
``` this executes but dosent actually insert into the table why not use f strings
frozen fossil
because it dosent help me ¯_(ツ)_/¯
copper echo
query_1 = "UPDATE cats SET money = money - $2 WHERE owner_id = $1"
query_2 = "UPDATE cats SET money = money + $2 WHERE owner_id = $1"
await self.bot.db.execute(query_1, ctx.author.id, amount)
await self.bot.db.execute(query_2, member.id, amount)
how can i make this in one line?
i was thinking about variables or something
rich trout
did you forget to .commit()
copper echo
i dont have to do this, if its to me
asyncpg is commiting everything after functions
rich trout
You might want to look into executemany(), but I was actually responding to raizo up there, my discord may have missed a few messages
copper echo
ye discord is having troubles atm
frozen fossil
@rich trout Do i need to commit if i have buffer on?
rich trout
I don't see why not
frozen fossil
@rich trout do i commit after every excute
rich trout
every time you want your current transaction to be saved to the database
frozen fossil
so if i'd fetch or select i wouldnt need that?
fossil leaf
discord.ext.commands.errors.CommandInvokeError: Command raised an exception: OperationalError: near "FROM": syntax error```
and
```css
c.execute(f"SELECT selected WHERE user_id = '{ctx.author.id}' FROM usr")
sqlite3.OperationalError: near "FROM": syntax error```db = sqlite3.connect('mons.sqlite')
main = sqlite3.connect('users.sqlite')
conn = main.cursor()
c = main.cursor()
c.execute(f"SELECT selected WHERE user_id = '{ctx.author.id}' FROM usr")
c_result = c.fetchone()
if c_result == None:
await ctx.send("You Have to Start First!")
select = str(c_result[0])
conn.execute(f"SELECT Level, Name, HPiv, CCiv, CCDEFiv, FRiv, FRDEFiv, MGC, SPD, Total WHERE user_id = '{ctx.author.id}' and num = '{select}' FROM usr")
result = conn.fetchone()
cursor = db.cursor()
cursor.execute(f"SELECT image, hp, CC, CC_DEF, FR, FR_DEF, MGC, SPD FROM mons WHERE name = '{select}' FROM mons")
cur_result = cursor.fetchone()```@frozen fossil help?? maybe~~ and i die for a ping~~
frozen fossil
what is selected
frozen fossil
hmm ok
fringe tiger
@frozen fossil nope, not for that. Only for changes
fossil leaf
well in the db its registered as text
frozen fossil
ayy ty
fossil leaf
but im using it in the code as a int
frozen fossil
@fossil leaf well comment some lines and check what line it is
fringe tiger
what's the problem?
frozen fossil
discord.ext.commands.errors.CommandInvokeError: Command raised an exception: OperationalError: near "FROM": syntax error
thats his problem
fringe tiger
ew f strings
fossil leaf
1 sec
discord.ext.commands.errors.CommandInvokeError: Command raised an exception: OperationalError: near "FROM": syntax error
and
c.execute(f"SELECT selected WHERE user_id = '{ctx.author.id}' FROM usr") sqlite3.OperationalError: near "FROM": syntax error
db = sqlite3.connect('mons.sqlite')
main = sqlite3.connect('users.sqlite')
conn = main.cursor()
c = main.cursor()
c.execute(f"SELECT selected WHERE user_id = '{ctx.author.id}' FROM usr")
c_result = c.fetchone()
if c_result == None:
await ctx.send("You Have to Start First!")
select = str(c_result[0])
conn.execute(f"SELECT Level, Name, HPiv, CCiv, CCDEFiv, FRiv, FRDEFiv, MGC, SPD, Total WHERE user_id = '{ctx.author.id}' and num = '{select}' FROM usr")
result = conn.fetchone()
cursor = db.cursor()
cursor.execute(f"SELECT image, hp, CC, CC_DEF, FR, FR_DEF, MGC, SPD FROM mons WHERE name = '{select}' FROM mons")
cur_result = cursor.fetchone()```thats my errors and cde
@fringe tiger do you know what im doing wrong?
fringe tiger
using f strings instead of placeholders..but that's not directly related to your error
lemme see
try to reverse it
first FROM then WHERE
fossil leaf
ok
frozen fossil
equery = "SELECT * FROM `items` WHERE ID = %s"
row = cursor.execute(query, id).fetchall()
``` this runs fine in mysql itself when replacing %s with a int but not in py same error
frozen fossil
mysql.connector.errors.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 '%s' at line 1
fossil leaf
im not even inputting info im just trying to get info ;-;
WAHHHHHHHHHHHHHHHHHHHHHHHH
I THOUGHT MOVINGFROM JSON SO SQLITE WAS A GUD IDEA
😫
frozen fossil
y
fringe tiger
maybe this?
c.execute("SELECT selected FROM usr WHERE user_id = ?", (ctx.author.id,))
fringe tiger
(ctx.author.id,)
fossil leaf
c.execute("SELECT selected FROM usr WHERE user_id = ?", (ctx.author.id))
^```it needs to be tuple
fossil leaf
IndentationError: unindent does not match any outer indentation level```you forgot ,
fossil leaf
o
same error
o nvm
my indent was wrong
discord.ext.commands.errors.CommandInvokeError: Command raised an exception: OperationalError: near "FROM": syntax error
same fucked uperror
fringe tiger
usr is your table name?
fossil leaf
ohwait
its from the other oe this time
conn.execute(f"SELECT Level, Name, HPiv, CCiv, CCDEFiv, FRiv, FRDEFiv, MGC, SPD, Total WHERE user_id = '{ctx.author.id}' and num = '{select}' FROM usr")```ty
fossil leaf
hmmmmmm im sorry im mega dumb how do i addmultiple/
fringe tiger
(ctx.author.id1, ctx.author.id2, ctx.author.id3)
fossil leaf
oof
fringe tiger
and for each argument there needs to be a ? placeholder
inside the string
it goes in order
fossil leaf
ty
frozen fossil
@fringe tiger you have a clue whats going on at my snippet?
fringe tiger
where
frozen fossil
equery = "SELECT * FROM `items` WHERE ID = %s"
row = cursor.execute(query, id).fetchall()
Hm I don't think you need ` it's just items but that doesn't seem to be problem. This is mysql? I used %s too for placeholder, now depending on what lib/db you're using it might be different
frozen fossil
mysql.connector
fringe tiger
id is just a int?
try row = cursor.execute(query, (id,)).fetchall()
I'm just randomly guessing
frozen fossil
yes
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 '%s' at line 1
oh i think i know
i tried query = ("SELECT id FROM items WHERE id = %s", id) but that gives me a tuple rror
fringe tiger
hm I'm bad at this stuff idk why I'm helping gonna make it even worse
but query is just a string
you made it a tuple
frozen fossil
how the hell am i making this a tuple
fringe tiger
("SELECT id FROM items WHERE id = %s", id)
frozen fossil
fringe tiger
depends how you pass that querry
frozen fossil
i fetchall
fringe tiger
no what I meant
query = "SELECT PREFIX FROM GUILDS WHERE GUILD_ID=%s"
row = fetch_helper(query, guild_id)
is not the same as
query = ("SELECT PREFIX FROM GUILDS WHERE GUILD_ID=%s", guild_id)
row = fetch_helper(query, guild_id)
if i pass it like ```py
query = "SELECT id FROM items WHERE id = %s"
exists = cursor.execute(query, id).fetchall
exactly like that?
frozen fossil
yes
fringe tiger
what about ()
fringe tiger
prob won't help but what about
exists = cursor.execute(query, (id,)).fetchall()
fringe tiger
oke then your sql definitelly doens't like that %s
frozen fossil
it worked for insert
fringe tiger
frozen fossil
# insertion into database
sql = "INSERT INTO items (itemname, price, stock, id) VALUES (%s, %s, %s, %s)"
val = (itemname, price, stock, id)
cursor.execute(sql, val)
cnx.commit()
await ctx.send(f"Record inserted.")
``` that workswill do 
rich trout
@frozen fossil
exists = cursor.execute(query, (id,)).fetchall()
note the trailing comma in (id,), that must be present
frozen fossil
Pretty sure I tried that but I’ll do that when I’m going to wake up
fringe tiger
yep
frozen fossil
dire cedar
@rich trout thanks a whole bunch for ur help yesterday
fringe tiger
@frozen fossil so it works?
frozen fossil
indeed
frozen fossil
lol
frozen fossil
if a select fails does it return None ?
fringe tiger
what? @torn sphinx
torn sphinx
@client.event
async def on_member_join(member):
with open ('users.json', 'r') as f:
users = json.load(f)
await update_data(users, member):
with open ('users.json', 'w') as f:
json.dump(users, f)
@client.event
async def on_message(message):
with open ('users.json', 'r') as f:
users = json.load(f)
await update_data(users, message.author):
await add_experience(users, message.author, 5):
await level_up(users, message.author, message.channel):
with open ('users.json', 'w') as f:
json.dump(users, f)
async def update_data(users, user):
if not user.id in users:
users[user.id] = {}
users[user.id]['experience'] = 0
users[user.id]['level'] = 1
async def add_experience(users, user, exp):
users[user.id]['experience'] += exp
async def level_up(users, user, channel):
experience = users[user.id]['experience']
lvl_start = [user.id]['level']
lvl_end = int(experience ** (1/4))
if lvl_start < lvl_end:
await channel.send('{} has Leveled to {}'.format(user.mention, lvl_end))
users[user.id]['level'] = lvl_end
could anyone help me
minor ruin
you are not following python formating rules
your problem is not at line 16, it's before that
go look up python pass
also using json to store data like that is not database problem and asking for issues
also, why is your indent level sooo off? What IDE are you using?
rose parrot
#....
with open('users.json', 'w') as f:
#...
@torn sphinx You added a whitespace after open
I think he copied pasted chunks of code @minor ruin , that's why the identation is odd
minor ruin
edug
await add_experience(users, message.author, 5):
await level_up(users, message.author, message.channel):```do you see any issues because I do
frozen fossil
@rich trout sorry to bother but could u explain me again why (id,) works and not id
cinder sierra
i do believe it'd be similar as to how sqlite works with prepping its statements, it requires a tuple
eg. a sqlite3 statement cursor.execute('SELECT id FROM items WHERE id = ?', (id,))
i don't know the technicality behind why a tuple is required, but the docs i do believe stated it's for security reasons
@frozen fossil
frozen fossil
@cinder sierra so not only id should be a tuple but every of my values?
cinder sierra
yes
frozen fossil
alright
cinder sierra
say you wanted another parameter with the WHERE, like uhh, a name
exists = cursor.execute(query, (id, name)).fetchall()
that'd be all, just adding it to the tuple
if that's what you mean that is
frozen fossil
oh yea
frozen fossil
can somebody help me with foreign keys i dont get them
lofty summit
A foreign key is basically a primary key from another table
void otter
yes
lofty summit
It's used to ensure data integrity
void otter
it just says okay connect model1 with id of something with a model2 with id that is equal to the foreign key of the model1
echo turret
so like inner joining databases right? @void otter
echo turret
yh
echo turret
sure
void otter
so user with id e.g. 4 is in a relationship with post with id7
frozen fossil
@lofty summit Yeah i got that but would i reference the primary key
void otter
yes
torn sphinx
What's the point of a managed database if you can just use is in your code directly and not pay a bunch of money?
rich trout
Just copying from digitaloceans ad:
- Automated failover
- Automated and flexible scaling
- Backups
- Experienced and prompt maintenance
No chances of "I oopsed my database and lost all my clients data" or "I ran across a bug in the database server code and the database crashed, and now the app doesn't work"
These bugs do exist--some enterprise databases have issues with very high read/write combined loads, which can lead to timeouts halfway through an insertion query. If that timeout happens, the database shuts off. With a managed database this isn't an issue. Generally, if you need a managed database you should already be looking to pay for one; the idea being that managing your own database is difficult and expensive and paying someone else to do it is worth the money.
delicate fieldBOT
tawny sail
Remove the try except block or print error to get a better idea
Not sure about the 15th line, need some education on that 😅
shadow scaffold
Hi there! I have a question regarding an SQLalchemy issue. Someone could please help me?
This is a many-to-many relation issue. I have difficulties to add data in it.
torn sphinx
Hey everyone! Was wondering if anyone knows any good async MongoDB library, motor seems bit outdated tho :<
torn sphinx
I keep getting Authentication errors when I try to load something on Heroku, I know it's bad but it's the only thing I have, so it keeps saying that it can't connect for this one file when it's looking for a col, but it works for my other code when i host it myself.
Ping me if someone responds, thanks
dire cedar
I get this error:
await cursor.execute(command,variables)
File "C:\Users\alexis\AppData\Local\Programs\Python\Python37-32\lib\site-packages\aiomysql\cursors.py", line 239, in execute
await self._query(query)
File "C:\Users\alexis\AppData\Local\Programs\Python\Python37-32\lib\site-packages\aiomysql\cursors.py", line 457, in _query
await conn.query(q)
File "C:\Users\alexis\AppData\Local\Programs\Python\Python37-32\lib\site-packages\aiomysql\connection.py", line 428, in query
await self._read_query_result(unbuffered=unbuffered)
File "C:\Users\alexis\AppData\Local\Programs\Python\Python37-32\lib\site-packages\aiomysql\connection.py", line 622, in _read_query_result
await result.read()
File "C:\Users\alexis\AppData\Local\Programs\Python\Python37-32\lib\site-packages\aiomysql\connection.py", line 1105, in read
first_packet = await self.connection._read_packet()
File "C:\Users\alexis\AppData\Local\Programs\Python\Python37-32\lib\site-packages\aiomysql\connection.py", line 593, in _read_packet
packet.check_error()
File "C:\Users\alexis\AppData\Local\Programs\Python\Python37-32\lib\site-packages\pymysql\protocol.py", line 220, in check_error
err.raise_mysql_exception(self._data)
File "C:\Users\alexis\AppData\Local\Programs\Python\Python37-32\lib\site-packages\pymysql\err.py", line 109, in raise_mysql_exception
raise errorclass(errno, errval)
pymysql.err.InternalError: (1205, 'Lock wait timeout exceeded; try restarting transaction')```as i just try to delete an entry in the table
as I "DELETE FROM table_name WHERE column=value AND column2=value2"
but it happens with deletes only
not updates, not inserts
any clue?
fallow elm
i'd assume something is holding a lock open. you can try running the delete directly from the mysql client to ensure it's not related to your code
serene slate
I have a python script that keeps submitting data to a MySQL database.
I have a TIMESTAMP column at the end I want to update with the current timestamp ONLY if a specific value in the row has changed.
Do I need to use a trigger for that? How do I detect if one value has changed?
I think I just figured it out
Now this probably isn't right but I think I'm on the right track:
BEGIN
IF NEW.price <> OLD.price
SET NEW.LastChanged = TIMESTAMP()
END```BEGIN
IF (NEW.price <> OLD.price, NEW.LastChanged = TIMESTAMP(), NEW.LastChanged = OLD.LastChanged)
END```is that better?
BEGIN
IF NEW.Price <> OLD.Price THEN
SET NEW.LastPriceChange = TIMESTAMP;
ELSE
SET NEW.LastPriceChange = OLD.LastPriceChange;
END IF;
END```
This didn't give me any errors, going to testHay, i have 2 tables that have both the same columns (id) and in one table i want to get the row based on the id of the other table could somebody please tell me how to do that
serene slate
I am totally shit with databases and I think a JOIN of some soft would be best but the way I'd do it would be something like:
SELECT * FROM table1 WHERE table1.id in (SELECT id FROM table2 WHERE some conditions)```Does this work?
return databaseCursor.fetchall(databaseCursor.execute(statement))
unrelated to the previous discussion
frozen fossil
um you could just return the fetchall after executing..
torn sphinx
so i cant keep it in one line?
frozen fossil
i dont think so
torn sphinx
okk thx
frozen fossil
you can try
frozen fossil
i think thats when you return a object
torn sphinx
for i in getStatement("SELECT name FROM data WHERE x ='x'"):
r.append(i)
and get statement is
global databaseCursor
statementReturn = databaseCursor.execute(statement)
return databaseCursor.fetchall(statementReturn)
🤦♂️
serene slate
I often do things like:
query = "SELECT blah blah blah"
cursor.execute(query)
data = cursor.fetchall()
return data```I use the MySQLdb lib btw
torn sphinx
databaseCursor.execute(statement)
data = databaseCursor.fetchall()
return data
still doesnt work
serene slate
can you post your full code that creates the cursor and connects to thedata base etc
(blank out passwords etc obvs.)
torn sphinx
I use sqlite so no pass lol
databaseConnection = sqlite.connect('Data.db')
databaseCursor = databaseConnection.cursor()
def getStatement(statement):
global databaseCursor
databaseCursor.execute(statement)
data = databaseCursor.fetchall()
return data
for i in getStatement("SELECT smth FROM dataInDB WHERE x = 'f'"):
roles.append(i)
not error
but when I print, it says <function x.<locals>.x at 0x0121E610>
it gives me a pointer
serene slate
I'd go along the lines of
def getStatement(statement):
conn = sqlite3.connect(db_file)
cursor = conn.cursor()
cursor.execute(statement)
data = cursor.fetchall()
conn.close()
return data
data = getStatement(statement)
for i in data:
print(i)```so I cant keep everything in one line huh
serene slate
I am not a Python officionado or anything but I think trying to do that is unpythonic
and softa fround upon, and makes your code look weird
but honestly I'm just some noob guy
ooo I just noticed I never closed my database connection up there
serene slate
not every time, no
not as far as I'm aware
but in my example every time you called getStatement a new connection was opened and never closed
after a while I'm sure that would cause an issue lol
torn sphinx
its not a connextion issue. Its insead of giving me the value, it spits out the pointer
thats my prob
serene slate
what does your print command look like?
serene slate
<function x.<locals>.x at 0x0121E610>
if that is printed it is like that looks like you are just printing a function
not what it returns
torn sphinx
datas = []
global databaseCursor
databaseCursor.execute(statement)
data = databaseCursor.fetchall()
for i in data:
datas.append(i)
return datas
the indentation is messed up
serene slate
sorry, I no longer follow you. If you're not printing it and you get that message I'm not sure what it is you're trying to do
torn sphinx
Ok here is in what I try to use this
from discord.ext import commands
from discord.ext.commands.core import Command
import sqlite3 as sqlite
databaseConnection = sqlite.connect('Data.db')
databaseCursor = databaseConnection.cursor()
def getStatement(statement):
datas = []
global databaseCursor
databaseCursor.execute(statement)
data = databaseCursor.fetchall()
for i in data:
datas.append(i)
return datas
def hasAdminRole():
async def predicate():
roles = []
dataStatement = getStatement("SELECT adminRoles FROM serverData WHERE server='server'")
for i in dataStatement:
roles.append(i)
return roles
return commands.has_role(predicate)
@commands.command()
@hasAdminRole()
async def a(ctx, arg):
await ctx.send(arg)
violet comet
your predicate is supposed to return True or False, depending on whether your check passes
not return a list of roles
torn sphinx
Ok got it thx
torn sphinx
Hello! I have a big problem! I can not access my server database in the pgadmin4 postgresql!!
I have changed from windows 7 to Ubuntu,I can still take/write to the database,but I can not log in from browser,I can not even restart my password in postgresql website because it is broken..can someone help me?
torn sphinx
Anyone? ;-;
frozen fossil
Hay, i have 2 tables that have both the same columns (id) and in one table i want to get the row based on the id of the other table could somebody please tell me how to do that
rich trout
SELECT FROM table_2 WHERE ID = ( SELECT id FROM table_1 WHERE criteria=search)
You can also do a JOIN WHERE table1.column1 = table2.column_whatever
depends on your exact goal
replace = with IN in the first option to match many
frozen fossil
@rich trout well hard to explain but i get a input that is a id and table 1 has the id in a column and i wanna get the row with the exact id so i can get the item name
rich trout
@frozen fossil that would be select from table1 where id = ?, and then pass in the id
frozen fossil
@rich trout problem is i have many of the ids
fallow elm
i can't tell by the way you're describing it buy you might want a join. if you can give two example tables it'd be easier to help
rich trout
Ah, this can be done but looks slightly awkward
frozen fossil
lemme open my project
rich trout
cursor.execute("SELECT * FROM table1 WHERE id IN (?)", ([1, 2, 3],))
There is a possibility this doesn't work--it appears that back in 2012 the sqlite bindings did not support passing lists. I think it does now but the documentation is not specific. If that's the case, you can use executemany() across your ids
Meh, worse case is doing this:
cursor.execute(f"SELECT * FROM table1 WHERE id IN ({', '.join('?'*len(ids))})", ids)
frozen fossil
@rich trout so wait cursor.execute("SELECT item_name FROM table1 WHERE id IN (?)", ([1, 2, 3],)) would only get me the name of all the items right?
in a
tuple?
rich trout
That's a good question, I don't remember. You might end up with a list of rows that only have name, but I think you'll get a tuple of names only
You'd need to do fetchall() of course
dire cedar
@rich trout heyo buddy, i'm pinging u cuz you basically introduced me to aiomysql, i've been using your snippet of code for each interaction with db http://prntscr.com/q3jr1r and sometimes, espcailly when i do a lot of operations on the db on a short amount of time, i get this error
pymysql.err.InternalError: (1205, 'Lock wait timeout exceeded; try restarting transaction')
Could ya help me out on this?
https://discordapp.com/channels/267624335836053506/342318764227821568/649499152731865108
i know i could increase a variable about this lock wait timeout, but i prefer avoid ALL the errors that could follow, even given a higher timeout
i've been debugging a lot, and errors have been occuring on await cursor.commit() or await cursor.execute()
rich trout
The issue is that you're performing too many operations in too short a time on the database. depending on your serialization level, each call to execute() will reserve the items in the database it needs to operate on
If it goes too long and things are still locked, it throws that error
I'm curious as to what exactly you're doing to do so many operations in a short amount of time. Perhaps executemany() could be of use? It's designed to avoid these kinds of issues
dire cedar
yeah i have thought of this and i guess it can fix the issue. But i'm concerned about the future proof aspect. Basically if they spam commands, i mean send a lot at the same time, many events trigger etcetra... when i'll release this to public how will the interaction with db go
it will be exactly the same as what i'm experiencing without executemany right
knowing that its for a guild with more than 7k members and they all are eager to run many commands storing data
@rich trout
well and its not a question of putting ratelimits to commands
rich trout
Probably, although I do suspect it will be less intensive. Regardless, that is where you start to get into dealing with how to optimize your database queries and usage--are they accessing more rows than they need to? Should some of these commands come with a builtin ratelimit so if they're spammed they don't work too fast, etc
dire cedar
its just as a whole that i'm concerned
rich trout
With 7k members I don't think you could expect anything more than a few hundred commands a second, even if they're all going at it
Without executemany I suspect you're over that threshhold
Perhaps looking into serialization level could help if things get quite hairy, but beyond that I'm not so familiar--too many read/writes in a second to the point you get lock errors is first a matter of cleaning up your code, then implementing optimized queries and local ratelimits, and then it's time to look into database config, optimization, and sharding, and I have very little experience with the last three. Regardless, I think you're fine as long as your code is clean and doesn't do too many queries--especially ones that hit a lot of rows
minor ruin
Alexis, you may consider decoupling command handler from listener
have listener shove something into RabbitMQ or like system and handler read off of it
dire cedar
@minor ruin i'm not entirely sure of having understood what u just said, but could ya perhaos re explain that seems interesting
minor ruin
Alexis, microservice it
instead of one single discord process where you are constantly worrying about async, split it out
minor ruin
to use bank teller example, you have a line with slow grandma, teenager, normal people, grandma gets to bank teller and lo and behold, she doesn't have required materials so instead of waiting, you set her to side and move on to next person in line.
I'm not sure due to Python GIL if you can multi thread it
well, multi thread in a way that's useful
what I'm talking about is multiple processes that are working in unison
minor ruin
do you have one or two commands that have a ton of SQL calls and crunching?
see about moving that, make you make flask calls or something else
dire cedar
not really
its just that there are some events being constantly called
like on_message
minor ruin
eventing still might help depending
rich trout
You may consider batching those, adding them to a queue until there's enough and then executing queries that do many of them at once
icy shore
anyone using Python and MongoDB? I am looking to switch from MySQL to MongoDB
minor ruin
Quicken, I looked into it, how are you going to run the MongoDB servers? They can be much more squirrelly then MySQL/Postgres
icy shore
So we use MongoDB at work and it runs in Azure. I want to load it on Windows so I can ramp up with my skills.
torn sphinx
i'm using pyrebase(package for firebase) and the authentication of a private key works perfectly fine on windows but when i try to do the same on linux, it gives me a RSA key format is not supported error
minor ruin
Quicken, we use Azure as well, have you looked at Cosmos with Mongo?
torn sphinx
cursor.execute(f"SELECT user_id, exp, level FROM glevel WHERE guild_id = '{ctx.guild.id}' ORDER BY level DESC, exp DESC")
result = cursor.fetchall()```
I'm not sure what I'm doing wrong but it stops getting results when `level` is over `10` it will get results and order them all right but if the `level` is over `10` it just ignores themAny help would be appreciated
ping when responded
rich trout
@torn sphinx is it possible you're saving the level as a String, and therefore '10' comes after '1' in the results?
you should be using parameter-passing, not string formatting, for these things. It'll help avoid that problem
rich trout
👍
torn sphinx
Yeah it was a str making it an int fixed it
inner pecan
Hi I want to join two tables and select only the columns in BOTH, I thought this is what INNER JOIN did but it doesn't work.
tableA has 40 columns, tableB has 39 columns which are also in A. A has an extra column.
I want a join but only the 39 columns in common to show.
SELECT * FROM 'tableA' A INNER JOIN tableB' B USING(Symbol);
doesn't work?
rich trout
Joins operate on the USING column, so an INNER JOIN selects the columns you specify, appending them together and returning all matching columns
inner pecan
ok so is there a way for me to do what I want?
manually selecting them like "Select A.c1, A.c2 .... A.c39, B.c1, B.c2 ... B.39" is not good enough
my other option was to just delete the column I don't care about but sqlite doesn't support removing a column lol
rich trout
There is a procedure for deleting columns, it's just awkward
inner pecan
is it recreating the table?>
rich trout
1. Create new table
2. Copy data
3. Drop old table
4. Rename new into old
I can not access my postgresql admin4 browser databases - I can not see the server list!
Help! All I did is change operating systems,though I can still take data and put it in the database
Sqlite3 sucks! It gets locked no matter what table or file,I want to switch all to postgresql
torn sphinx
Really,nobody has had this problem? ;-;
smoky radish
maybe you havent described it well enough
smoky radish
where is you postgres running
how are you accessing it successfully, how are you accessing it when it fails
the list of what type of servers is missing? did you mean table?
torn sphinx
All I can say is I have it downloaded on Ubuntu, I run the application,it opens and it asks me my master password (which means I am logged in in some way) and I enter it, there is a 'Server' to the left,I press on the little arrow and there should be thousands of databases,but I can see nothing,it is ALL empty,but somehow I can still access the databases through scripts..
smoky radish
what application
smoky radish
so you can access by running queries directly?
torn sphinx
Yeah,I think I understand what you are asking 😂
Yes
I can access the information from the database
Is the app broken or something ,I do not know..
smoky radish
hm
torn sphinx
😦
smoky radish
how important is using pgadmin4 for you? would you be willing to try another app?
torn sphinx
You mean another database type?
smoky radish
ive never used pgadmin4 so i cant really help much
no
another way to access the database
torn sphinx
You mean another app to a
oh
Sure
Why not?
I mean I kinda got used to pgAdmin4 already,BUT better anything than nothing
torn sphinx
I am trying to install java,wait ;-;
torn sphinx
YES,IT WORKS,THANKS
Except 1 problem 😆
why are BIGINTs weird? there are like 6 pairs of 3 numbers like
111,222,333,444,555,666 (ofc not literally like that)
Oh nevermind
When you double-click on it,it becomes a normal number
thaaaanks @smoky radish ❤️
Have a nice day
smoky radish
@torn sphinx thats just the thousand seperator, its so you can tell the magnitude of the number
torn sphinx
it is a member id
smoky radish
one million and thirty three thousand
1,033,000
frozen fossil
@rich trout on last thursday you send me a piece of code (cursor.execute("SELECT item_name FROM table1 WHERE id IN (?)", ([1, 2, 3],)) ) , tho lists cant be converted to a mysql type right now i am trying to make this work:
#get all ids
cursor.execute("SELECT id FROM orders WHERE userid=(%s)", (userid,))
ids = cursor.fetchall()
# iterate trough item table
cursor.execute("SELECT itemname FROM items WHERE id IN (%s)", (ids,))
await userobj.send()
You would want to go back and look at my second solution then--the one that includes a short format string within the query string
frozen fossil
alright i will try that
ayy tuple cant be converted
is ids supposed to be a list?
rich trout
List is probably not convertable either
Try something like this:
cursor.execute(f"SELECT * FROM table1 WHERE id IN ({', '.join('?'*len(ids))})", ids)
same thing :/
rich trout
are you passing in ids as I mentioned there? It's different than the others for a reason
frozen fossil
yeah i c+p'd it
cursor.execute(f"SELECT itemname FROM items WHERE id IN ({', '.join('?'*len(ids))})", ids)
frozen fossil
lemme check
rich trout
What that should do is expand into the following:
rich trout
"SELECT itemname FROM items WHERE id IN (?, ?, ?, ?, ?)", (1, 2, 3, 4)
oh, that's definitely wrong
frozen fossil
so what the hell is that
rich trout
That's a list of tuples, it should be a tuple of ints
rich trout
You're probably taking a result and appending it, you should be appending result.id or similar
frozen fossil
cursor.execute("SELECT id FROM orders WHERE userid = %s", (userid,))
ids = cursor.fetchall()
print(ids)
thats what i am doing
rich trout
Yeah, ids' is a list of results, and each result has one item (the id)
You'd need to rearrange that so that you have a tuple of id's
dire cedar
@rich trout actually i've been able to fix my stuff a whole bunch.
Indeed i'm sending A LOOOOOT of delete requests to the db in a short amount of time.
So far i've been getting some lock error timeouts...
But the fix to this is actually to make the key (from which i read, update, delete the whole data set) a primary key
cuz really i couldn't optimise more in terms of grouping into executemany, etc
rich trout
...I had simply assumed it was a primary key. Whoops.
What you're seeing there is indexing
that is, all primary keys have an "index" created for their column. If you do a search for a primary key (SELECT .. WHERE key = thing) it search the index, which is really really fast. Otherwise it needs to loop through the table
You can create manual indexes too, but for your case I suspect making it a primary key was smarter :P
dire cedar
well i didn't know the point of indexes on sql before, since i'm really a beginner in DBs, i've only been using json so far xd
thanks for explanation mate ^^
so basically i'll make my identifiers key primary
frozen fossil
@rich trout ((1,), (1,), (1,), (1,), (1,), (1,), (1,), (1,), (1,), (1,), (1,)) now i got this
tuples inside a tuple
rich trout
Are you familiar with list comprehensions?
frozen fossil
no i never had to
rich trout
one of those would be the perfect solution here
something like, tuple(item.id for item in ids)
!listcomps
delicate fieldBOT
rich trout
Yes, one tuple containing only the ids you want
why does god want me to suffer
frozen fossil
@rich trout wait wait is that a list with tuples and in the tuples is a empty tuple?
or what the hell am i looking at
rich trout
You want this format:
ids = (1, 2, 3, 4, 5, 6, 7)
You can use tuple() and a comprehension to do something like this:
!e ```py
a = [(0,)]*6
print(a)
print(tuple(i[0] for i in a))
@rich trout :white_check_mark: Your eval job has completed with return code 0.
001 | [(0,), (0,), (0,), (0,), (0,), (0,)]
002 | (0, 0, 0, 0, 0, 0)
well that was more than enough
@rich trout ? is a placeholer, but dont i need infinite placeholders for that?
since there could be infinite ids
rich trout
That's what this part is for:
({', '.join('?'*len(ids))})
It inserts N placeholders, depending on the length of ids
however in your case we might just want a join
cursor.execute("SELECT id FROM orders WHERE userid = %s", (userid,))
cursor.execute("SELECT item FROM items WHERE order IN (ids)")
becomes
cursor.execute("SELECT item FROM items WHERE order IN ( SELECT id FROM orders WHERE userid = %s)", (id,))
rich trout
Nothing, then
frozen fossil
ugh
@rich trout wait so just use the command below?
because the last command is not working
rich trout
What are you trying?
frozen fossil
cursor.execute("SELECT itemname FROM items WHERE order IN ( SELECT id FROM orders WHERE userid = %s)", (userid,)) @rich trout
rich trout
And what's wrong with it?
frozen fossil
@rich trout syntax errors
raise errors.get_exception(packet)
mysql.connector.errors.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 'order IN ( SELECT id FROM orders
WHERE userid = 452088717008437249)' at line 1
frozen fossil
i am