carmine tide
that's cool
#databases
pliant pendant
SELECT image_id, real_image_id, folder_name, extension, image_type
FROM $1_img
WHERE image_id = $2
AND ready = 2;
the part "FROM $1_img", is it possible to make it work?
final lion
unlikely, but why would you need a bunch of related tables with the same schema? sounds like they should be merged into a single table with another column to distinguish
smoky radish
hey, im trying to to use SQLAlchemy ORM to insert into a table with a m:n relationship, but im facing the problem that due the design of my app, there will be duplicate entries inserted to the m:n table. what can i do to ignore the duplicate inserts into the m:n table? in normal (SQL i would use ON DUPLICATE KEY UPDATE or ON CONFLICT IGNORE but i can't figure out how to get the same functionality in SQLAlchemy
upbeat lily
@smoky radish assuming you can't change the design of your app to use unique primary keys, then it seems like this should work https://stackoverflow.com/questions/6611563/sqlalchemy-on-duplicate-key-update
Although, if you're building the app from scratch, it certainly seems better to try and just use a sensible primary key
ah wait - I assuming you aren't using MySQL which that solution necessitates
lusty leaf
So I have a sqlite database constantly like... renewing itself or something. I can't delete it. I HAD a python program messing with it but I've since closed the program, so nothing should be actively messing with the database file. Yet, when I delete it, it acts like I didn't even touch it. Like it doesn't get deleted, but it also doesn't tell me that the file is in use.
How can I force close that connection?
Never mind. I re-opened my file manager and on a HUGE delay, like 5 windows popped up telling me that the db couldn't be modified because it was open in python, but the db is gone now so one of the deletions kicked in after I closed everything out.
proven wagon
Im making a database for my discord bots infractions, how can i make sure one infraction_id (Primary Key) isnt the same as another one saved in my db.
Using PostgreSQL with async driver asyncpg
smoky radish
@upbeat lily i do have unique primary keys, those arent the problem. the problem is that sqlalchemy tries to insert something and gets a error because that unique cell already exists
using postgres btw
placid flicker
Hey guys, I have a table (TABLE 1) with data, but I want that data to also go to another table (TABLE 2). Is it possible that everytime TABLE 1 is filled, TABLE 2 also gets filled automatically?
hushed chasm
hey, looking for help on deciding what db to use for a crud app
my data is in bigquery (and I need to update records here)
but bq is for analytics and not really front-end apps
should I use another db for the app
and then sync them?
or just use BQ directly
looking at maybe 10-20 updates/month from the front-end
weak dagger
from flask import Flask, signals
from config import Config
from flask_sqlalchemy import SQLAlchemy, models_committed
from flask_migrate import Migrate
from flask_login import LoginManager
signals.signals_available
@models_committed.connect_via(app)
def signal_thing(sender):
print('hello first')
sender.print('hello this worked')
models_committed.connect(signal_thing)
signal_thing() does not fire when models are comitted
tiny otter
so im getting this errir sqlite3.OperationalError: no such column: SWC00001
with this code def sendswc(self): c.execute("UPDATE Switches SET Property ="+str(self.swcprop.text)+", DateSentOut= "+str(datetime.date.today())+" WHERE IventoryID ="+str(self.swcid.text)+"") conn.commit()
and there is a entry under IventoryID called SWC00001 in the Switches table
harsh pulsar
don't use string interpolation for querying
tiny otter
becuase of sql injection?
becuase this program sits on a local machine that only i use for a database that isnt local just to keep track of inventory
harsh pulsar
well, also because of the kinds of errors you just got
tiny otter
i see
i ve tried a few other ways too and they just didnt do anything, i entered text, hit the button and...nothing, nothing in the console, no update to the database, nothing
update_swc_query = "UPDATE Switches SET Property =?, DateSentOut= ? WHERE IventoryID = ?"
def sendswc(self):
c.execute(SendSWC.update_swc_query, ("'"+str(self.swcprop.text)+"'", "'"+str(datetime.date.today())+"'", "'"+str(self.swcid.text)+"'"))
conn.commit()
```
update_swc_query = "UPDATE Switches SET Property =?, DateSentOut=? WHERE IventoryID =?"
def sendswc(self):
c.execute(SendSWC.update_swc_query, (str(self.swcprop.text), str(datetime.date.today()), str(self.swcid.text)))
conn.commit()```you have other problems... where is the cursor and connection defined?
tiny otter
update_swc_query = "UPDATE Switches SET Property =?, DateSentOut=? WHERE IventoryID =?"
def sendswc(self):
c.execute(SendSWC.update_swc_query, (str(self.swcprop.text), str(datetime.date.today()), str(self.swcid.text)))
conn.commit()```theyre defined up top as this
conn = sqlite3.connect('C:/Users/jarnold/Desktop/seinv.db')
c = conn.cursor()```i thought so too, that was my first attempt
harsh pulsar
error message?
harsh pulsar
youre probably not supposed to re-use a cursor like that
class SendSWC(Screen, LeftMenu):
update_swc_query = "UPDATE Switches SET Property =?, DateSentOut=? WHERE IventoryID =?"
def sendswc(self):
c = conn.cursor()
c.execute(SendSWC.update_swc_query, (str(self.swcprop.text), str(datetime.date.today()), str(self.swcid.text)))
conn.commit()
it works here oddly ```class NewDVR(Screen, LeftMenu):
insert_dvr_query = "INSERT INTO DVRs(IventoryID, Notes, DateRecieved, DateSentOut, Property) VALUES (?,?,?,?,?)"
def submitdvr(self):
c.execute("SELECT Count(IventoryID) FROM DVRs")
num_len = 5
count = c.fetchone()[0] + 1
count = str(count)
string_output = "0"*(num_len-len(count)) + count
c.execute(NewDVR.insert_dvr_query, ('DVR'+str(string_output),str(self.dvrnotes.text),str(datetime.date.today()),'NA','NA'))
conn.commit()```
or here ```class NewSWC(Screen, LeftMenu):
insert_swc_query = "INSERT INTO Switches(IventoryID, TypeOfSwitch, NumberOfPorts, Notes, DateRecieved, DateSentOut, Property) VALUES (?,?,?,?,?,?,?)"
def submitswc(self):
if self.swcports.text == '':
print ('Form Not Complete')
else:
if self.swctog1.state == 'down':
c.execute("SELECT Count(IventoryID) FROM Switches")
num_len = 5
count = c.fetchone()[0] + 1
count = str(count)
string_output = "0"*(num_len-len(count)) + count
c.execute(NewSWC.insert_swc_query, ('SWC'+str(string_output), str(self.swctog1.text), str(self.swcports.text), str(self.swcnotes.text), str(datetime.date.today()),'NA', 'NA'))
conn.commit()
elif self.swctog2.state == 'down':
c.execute("SELECT Count(IventoryID) FROM Switches")
num_len = 5
count = c.fetchone()[0] + 1
count = str(count)
string_output = "0"*(num_len-len(count)) + count
c.execute(NewSWC.insert_swc_query, ('SWC'+str(string_output), str(self.swctog2.text), str(self.swcports.text), str(self.swcnotes.text), str(datetime.date.today()),'NA', 'NA'))
conn.commit()
else:
print ('Form Not Complete')```
but not for the update
tiny otter
so kep this up top global conn conn = sqlite3.connect('C:/Users/jarnold/Desktop/seinv.db') and just use this as a part of each action c = conn.cursor()
tiny otter
same error of i hit the button and nothing happens, DB doesnt update or anything
but that code where i inject the variables into the query at least reacts when the button is pressed by crashing
harsh pulsar
what happens if you run the query manually using something like DB Browser
harsh pulsar
type in the query in db browser?
charred wedge
Hi guys! I'm trying to migrate some Objects into Arrays containing those objects in my mongoDB collection. Using pymongo is this a safe way of doing so?
for order in orders:
if order.get('broadcast_requests') != None:
for request in order['broadcast_requests']:
if type(request['offers']) != type(list):
order_2_update = db.orders.find_one_and_update(
{'_id': ObjectId(order['_id'])},
{'$set': {
'broadcast_requests': {
'offers': [request['offers']]
}
}})```
spiral iron
idk i was told to post this here
long stream
@spiral iron you don't have your query within the db.update() function call.
stoic kernel
I want to get results from one table only if the id of the row is in another table and yas a positive balance
So something like select data if their balance from the other table is positive
I'm using postgres btw
I feel like I'm explaining this terrible. Say I have a table called points in which I have the ID and their points. I also have another table called videos which has a linker_id and a link to a YouTube video. I want to randomly select from the videos if the linker_id's points are more than 0
Am I making sense?
harsh pulsar
how do the tables relate @stoic kernel ?
stoic kernel
Well the is the same for both @harsh pulsar
harsh pulsar
the id?
stoic kernel
It's a discord id
It's a column in the table
So I want to select a row from one table and then get the id from that table. With that id I then want to check the balance associated with that id in the balance table
harsh pulsar
select
p.id,
p.balance
from
points p
where
exists (
select *
from videos v
where
v.id = p.id
and v.balance > 0
)
maybe
stoic kernel
Does it matter that the id name isn't the same
Like in the he balance table it's called discord id and in the video it's called linker id
harsh pulsar
select
p.id,
p.balance
from
points p
where
exists (
select *
from
videos v
where
v.linker_id = p.id and
v.balance > 0
)
Okay thanks. Im out rn but will check in 30 moms when I get home. Tyvm
weak dagger
object = ObjectModel(name='example')
db.session.add(object)
db.session.commit()
# some error with a signal I'm working on - I debug/fix, then retry.
object2 = ObjectModel(name='second_example')
db.session.add(object)
db.session.commit()
# sqlalchemy.exc.InvalidRequestError: This session is in 'committed' state; no further SQL can be emitted within this transaction.
db.session.rollback()
# sqlalchemy.exc.InvalidRequestError: This session is in 'committed' state; no further SQL can be emitted within this transaction.
Why do I need to restart the shell session again to add another object? I can't seem to do anything after that one commit which throws an error.
pure scroll
because session is abstraction of a database transaction
you can't commit db transaction twice, after you commit it it gets closed
you need to open a new one
serene slate
Using MySQL: is it better/faster to select a full table and then sort/filter it in python or to have a specific query?
The table is about 100k to 300k rows long.
e.g.
query = "SELECT * FROM table1 WHERE ((TIMESTAMPDIFF(MINUTE, UpdateTime, now()) < 10)) AND column1 = 'some value' AND column2 = 'some other value'"```I'll be testing this later ^ so I'll post back to let you know what the results are.
Okay, it seems selecting everything is the faster query but ends up taking longer as the time it takes to send data over the network is longer. I think I have my answser.
Select everything and then sort/filter (script will be running on the same server as DB)
database was currently only 2k rows though so I'll need to test again once it is fully populated
oak frost
knows someone a good alternative to mongodb? They changed the licence and its not longer available in debian 10.
It should be similar, with capped collections. it would be desirable if it had a broker for it (like redis)
harsh pulsar
@serene slate let mysql do as much of that work as possible
not only does it reduce the amount that travels over the network, it also means less data has to be read from disk in the first place
or it means that less has to be kept in memory, reducing memory pressure
unique cape
hey guys I need some help, I stored some information from my database in a list and I need to make functions to add a new entry into database, edit and delete entries, currently a bit stumped on how to go about the last two, is there any way I can retrieve individual words from my list?
so like if I had the second entry selected I would have Dog, 1B, 04-04-2019 stored in variables for me to then make the changes to the database
serene slate
@harsh pulsar Memory is not an issue and the data will not be transferred over a network. Does MySQL perform the time check on every row on disc? If so, would it not be quicker loading everything into memory then performing the time check?
harsh pulsar
@serene slate usually its good practice to let the database do as much work as possible, but in this case it probably doesn't matter much and you should use what you're most comfortable with
@unique cape it sounds like you might benefit from an ORM like Peewee or SQLAlchemy
serene slate
I have like 50+ scripts dumping data into the database and a few scripts taking data out of the data base. I had 300+ connections to the database which I've since reduced and it was grinding to a hault so was trying to take as much work away from the database as possible
(the peak connection count at one point was >1000)
harsh pulsar
then go with what works for your case. there aren't any strict rules about it
@unique cape if you don't use an ORM you'll have to write the INSERT and UPDATE queries yourself, which it sounds like you don't have experience with
serene slate
Thanks for the input. I think I'll need to do what works for now and slowly rearrange everything to have the DB do the legwork eventually
unique cape
hmmmmmmmmmm yeah I don't really have experience with queries, I managed to get the insert to work though and I think I can get the update query to work, I'm just not really sure how to get the information needed to make the changes (outside of the user entering them through a lineEdit widget, but that makes the list a bit redundant)
pliant pendant
async def pool():
if __box.currentpool != None:
return await __box.currentpool.acquire()
else:
__box.currentpool = await asyncpg.create_pool(
user='',
password='',
database='',
host='',
port="")
return await __box.currentpool.acquire()
This code for some reason freezes my whole session after a few runs, why tho... (asyncpg, freezes at approx 5 uses)
harsh pulsar
does the pool not have an async with context manager?
patent glen
it does, it's unclear if he's using it
wait, can you use with (await...) with async context managers?
pliant pendant
@harsh pulsar i tried to do it but it seems that it breaks whenever i do it
@patent glen not sure hmmm
but i may not release the connections back to pool as you said
pliant pendant
I've now successfully made so the pool releases!
however i'll have to do pool.release myself for optimizations
cause otherwise one function could run 10+ connections
due to chaining
limber stone
If you do python async with __box.currentpool.acquire() as con: #do stuff and things
The connection will auto release once you leave the block
proven wagon
How to delete all contents of a table in postgresql?
harsh pulsar
TRUNCATE
ionic pecan
yes
pliant pendant
@limber stone it would, but this not efficient enough for my work
however i may try to find ways to integrate it, if i ever find a way to then i'll definetily return to asynccontext manager
shy sundial
How would I go about taking column from 2 tables (1 from each) and creating a 3rd table with a new column count and auto-updating it as the other 2 main tables update?
harsh pulsar
run a batch job every night or something
shy sundial
but I need it updated right after the main tables have been updated
can I use triggers?
harsh pulsar
ah, i think so. i actually have never used triggers
topaz lantern
Morning all, I have a looming data conversion from a vendor we receive data from coming up. They are changing from a FWF to an XML format. I presently take their FWF's and parse them to update tables and create spreadsheets as needed upon receipt so we can work with the data. I use Pandas heavily in this.
I've seen some snippets of code that show how to parse XML in Python
But their XML is really complex compared to the simple examples.
IE, I'll be pulling data into several dataframes from one XML as there's several root elements I need to collect distinctly
I figured out how to do this with simple XML that is one level with one root level node, but not with the more elaborate versions.
I'm using the XML.etree.cElemenTree module currently.
harsh pulsar
xpath is your friend
or you could use XSLT to flatten the data
oh and use LXML
XSLT example in their docs:
from io import StringIO
from lxml import etree
xslt_root = etree.XML('''
<xsl:styleshversion="1.0"
xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:templmatch="/">
<xsl:messterminate="no">STARTING</xsl:message>
<foo><xsl:valueselect="/a/b/text/></foo>
<xsl:messterminate="no">DONE</xsl:message>
</xsl:template>
</xsl:stylesheet>''')
xml_root = etree.XML('<a><b>This is the content we want!</b></a>')
transformer = etree.XSLT(xslt_root)
result = transformer(xml_root)
print(str(result))
That's pretty great stuff! I have no idea why I hadn't considered xslt. Thank you!
harsh pulsar
yep you could make a "flat" xml structure with XSLT that can be easily pandas-ized
spiral iron
!paste
delicate fieldBOT
torn sphinx
Lets say I have this table:
CREATE TABLE Warnings (
id SERIAL,
warning_id SMALLINT NOT NULL,
revision_no SMALLINT NOT NULL DEFAULT 1,
user_id BIGINT NOT NULL,
server_id BIGINT NOT NULL,
reason TEXT NOT NULL,
PRIMARY KEY (warning_id, revision_no, server_id, user_id)
);```
And I add data to it:
``INSERT INTO Warnings(warning_id, user_id, server_id, reason) VALUES(1, 12345678901234567, 12345678901234567, 'Reason');``
But then I want to add another warning, how I can have the warning_id be one value higher from user_id in server_id? Is it possible to do all in one query?
tall spoke
warning_id = warning_id + 1
I have mine in dictfile[server-dict][user-dict][warn-int-key]: value, style
torn sphinx
Hmm the thing is how can I get the last warning ID for said user inside an INSERT query (easily done if it's a separate query, but I'm wondering if it's possible to do all in one)
plain radish
torn sphinx
@plain radish Yeah it's PG. To explain a bit better I'm doing a revision system to edit previous handed warnings. So not sure if returning would be useful because the editing could be done a few days later, etc
harsh pulsar
@fringe tiger why not, as long as you have an async capable ORM?
fringe tiger
Can you recommend one for mysql @quiet ermine @harsh pulsar
harsh pulsar
i have no idea
fringe tiger
Wasn't at pc yet so looked on mobile, repos I could find on async orm seem incomplete
patent glen
part of the problem is there's no standard api for async database access
harsh pulsar
im actually surprised theres no Async DBAPI PEP
near junco
Had asked this in help, I'd greatly appreciate if someone could help me:
Is there anyone here with experience with SQLite3? I have a question regarding ON CONFLICT clauses.
I'm currently writing a dice roller bot using Discord.py, one of the features is a macro system using a database. The database would consist of two columns: name and command. When the user inputs "/initiative" she would run the command specified under that name.
The issue I am having is that I don't want the user to be able to input the same name with different commands, which means I'd have to use an ON CONFLICT clause, but then I'd like to report to the user "hey, not cool, man, try another name". Is there any way to either:
a) have SQLite return an error to the Python script which would trigger the warning, or, b) have Python itself notice that it already exists and trigger the warning.
Update: I did a placeholder function to solve this issue, but it's woefully slow
It uses a for loop to fetch every value and if the length of the list fetched is higher than 1, it prints the warning.
@patent glen What do you mean by "having a unique index"?
patent glen
ok i guess partially i don't understand what you mean by name and command so it's hard to explain in full detail
but basically you can, in your CREATE TABLE statement, specify that a column (or set of multiple columns) is unique and not allowed to have two rows with the same value
harsh pulsar
they're asking how to handle conflicts on insert @patent glen
patent glen
you have to do that for on conflict to work anyway so i'm not sure where exactly you're getting that you need an on conflict clause
@harsh pulsar yes but the behavior they want is the default behavior, so I am confused
harsh pulsar
maybe they don't realize 🤷
at first i thought they wanted to return the existing value from the db
near junco
I've just started learning SQL, so I am missing a huge part of what is and isn't default behaviour.
patent glen
oh wait this is to store command aliases?
near junco
Yes
patent glen
yeah just make name the primary key
primary key is automatically unique, and if you try to insert, the default behavior (on conflict abort, but you don't need to actually write it) will raise an error that you can handle in python with try/except
harsh pulsar
!e
import sqlite3
conn = sqlite3.connect(':memory:')
conn.execute("CREATE TABLE macros (name PRIMARY KEY, command)")
data = [
('c', 'clone'),
('m', 'merge'),
('c', 'commit')
]
for name, command in data:
print(name, command)
conn.execute("INSERT INTO macros (name, command) VALUES (?, ?)", (name, command))
@harsh pulsar Your eval job has completed.
001 | c clone
002 | m merge
003 | c commit
004 | Traceback (most recent call last):
005 | File "<string>", line 18, in <module>
006 | sqlite3.IntegrityError: UNIQUE constraint failed: macros.name
Is there any harm if a user can see a databases table name? Like, I want to post on github a bot I'm making but I dunno enough about databases to know if seeing a table name is bad or naw
harsh pulsar
not recommended
it just makes it that much easier to potentially attack you somehow
limber stone
Alright. That's what I was thinking. Thanks
patent glen
it's defense in depth, basically
Honestly I wouldn't say that keeping database schemas secret is enough of a reason to avoid making code open source though
ionic pecan
Security through obscurity is not security
Nobody cares about your table name. If someone gains access to your database, you've got a problem anyways
limber stone
Also my reasoning. I'm not totaly dumb when it comes to security, just databases 
ionic pecan
If you run someting like Postgres, just don't expose it to the public internet
limber stone
Yeah I'm not. It's just for a discord bot I'm making. Only way to interact with it is through the bot. But I parametrized (?) everything
fringe tiger
Then there should be no injection danger
atomic plank
Hey if I wish to set a column of BIGINT to None how should I go about it?
like None or?
torn sphinx
found=False
myquery = {"server-id": str(ctx.guild.id)}
mydoc = self.autorules.find({})
for x in mydoc:
found=True
if not found:
return
print("Found data.")
rlist=x["rules"]
if rlist==[]:
return
threshold=None
for rule in rlist:
if int(rule[0])==found_warns:
threshold=rule
punishment=rule[1]```
So this is just getting punishments based on what the user's warning is, it doesn't go to the punishment, i am just wondering why.This is MongoDB btw
atomic plank
I don't know about mongo but best way to trouble shoot something is to add prints to everything and see what's hitting and what isn't and then go from there
torn sphinx
Well when I add print(x) in the for loop
It didn't print anything, so it's probably the connection to the collection
But i don't know what to do
atomic plank
hm perhaps print the whole values of said record? to see if the value is even set
Try reversing your logic at the moment where it's not showing the punishment and see if it prints an error of some kind
quiet ermine
I would sort of like to make my own db
I know there's no point
But it would be cool to do
limber stone
You could try to do "Discord as a Database"
nova hawk
What's in the log?
lusty igloo
Could someone tell me what user and password am I supposed to specify on asyncpg.create_pool if the ones that I use on pgAdmin4 site to see my database and my linux user password don't work ?
harsh pulsar
@lusty igloo whatever you use in pgadmin should work with asyncpg
lusty igloo
I found out it didn't default to localhost and default port and that was the fault but thanks @harsh pulsar
proven wagon
I have multiple discord bots using the same database, multiple bots post Infraction objects that i created. these should all have a unique ID.
Is there a way for me to make sure these have unique IDs without connecting the bots in some way?
harsh pulsar
@proven wagon you can generate a UUID for the infraction, which is unique with like 99.99999999999% probability
!d g uuid.uuid1
delicate fieldBOT
harsh pulsar
that gives you only 10^6 unique IDs
so that's 1/1e6 collision probability
that's too high for my comfort
if you use alphanumerics, it's a lot better
36^6 is a huge number
2176782336 unique ids
proven wagon
Brings me to my next point, i have limited storage space (20MB) so i want to delete the infractions after (time / limit)
OR i need to find a better (free) PostgreSQL host
harsh pulsar
...how many infractions to do you expect to get?
proven wagon
Infractions arent the only things that will be stored
harsh pulsar
what you can do is, once a week, dump the infraction table to csv and save it to dropbox or email it to yourself, then delete all records older than 1 week
you can also create a tablespace in which the entire table is compressed https://www.postgresql.org/docs/current/manage-ag-tablespaces.html
also a UUID1 stored as CHAR(16) is gonna be pretty small on disk overall... 128 bits per ID + database overhead
proven wagon
Its not the storage space im worried about
its the readability of 1fd4ca24-d705-11e9-83e7-704d7b899e6e vs something like 123456
If i use str(uuid.uuid1()).split('-')[0] How much does the collision probability change?
smoky radish
hey, im running into a bit of a problem with SQLAlchemy ORM:
i have the following view (PostgreSQL) that i simply want to query using SQLAlchemy:
class NewestBump(Base):
__tablename__ = 'newest_bumps'
division_id = Column('division_id', Integer, primary_key=True)
bump_id = Column('bump_id', Integer, primary_key=True)
# ... shortened for readability
post_url = Column('post_url', Text)
post_title = Column('post_title', Text)
i can query all the values just fine, using NewestBump.query.filter_by(division_id=1).all() (flask_sqlalchemy)
but no matter what value i put in the database, post_title is always None
ive confirmed multiple times that the equal query executed in my database manager does return a value. If i use SQLAlchemy to execute pure sql i get None tho. ive also confirmed that my table definition is exactly correct (i use sqlacodegen)
Querying the place post_title comes from in the view, does also return none
lusty igloo
Could someone explain me what might make my code not run ?
I try to insert new data into my Postgresql table but it says "Invalid syntax"
Although very same code put in $eval command of d.py bot runs it just fine, is there some sort of limit of setting values at once with PostgreSQL? (I'm setting 17 values at once and I get the invalid syntax in : ... $17)",... the invalid syntax is right under ")
Is it possible that it has issue with code before that?
harsh pulsar
@lusty igloo what's the python code
lusty igloo
def new_account2(self, user:int):
inventory = {"inventory":{"rock": 1}}
inventory = json.dumps(inventory)
await self.bot.pg_con.execute("INSERT INTO rpgdata (member, money, deaths, dev, health, level, xp, damage, defense, dmgavoid, crit, streak, last, armor, primwep, secwep, inventory) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11 , $12, $13, $14, $15, $16, $17)", user,0,0,False,100,1,0,1,1,1,1,0,5,'not equipped','not equipped','not equipped',inventory)```
@harsh pulsarit works just fine on $eval command
harsh pulsar
what is $eval
lusty igloo
evaluates code
harsh pulsar
in discord?
lusty igloo
sort of
lusty igloo
python code
harsh pulsar
show what you're typing into $eval
lusty igloo
Its one that was provided by open source discord bot of d.py creator
$eval userid = 476048684354633729 inventory = {"inventory":{"rock": 1}} inventory = json.dumps(inventory) await bot.pg_con.execute("INSERT INTO rpgdata (member, money, deaths, dev, health, level, xp, damage, defense, dmgavoid, crit, streak, last, armor, primwep, secwep, inventory) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11 , $12, $13, $14, $15, $16, $17)", userid,0,0,False,100,1,0,1,1,1,1,0,5,'not equipped','not equipped','not equipped',inventory)
harsh pulsar
is the syntax error coming from python or postgres?
harsh pulsar
is that all on one line?
lusty igloo
but $eval says there is no error and pgAdmin4 updates correctly
yes
its just my "dashboard" has some limited res
harsh pulsar
that syntax error might be from elsewhere in the cog file
lusty igloo
Uh
harsh pulsar
python isn't very good at identifying where the syntax is bad
harsh pulsar
it's usually somewhere earlier
lusty igloo
which happens to be the syntax error
lusty igloo
it worked just fine before adding that function
I will try to move it even higher ig
harsh pulsar
share the code up through that function
from line 0 through the end of the function
!paste
delicate fieldBOT
lusty igloo
huh?
harsh pulsar
write async def so you can use await inside the function
harsh pulsar
you can't use await in a non-async function
lusty igloo
It just doesn't error as it mostly would so I didn't pick that up
let me see
Yes! it works
harsh pulsar
the fact that $eval lets you put an await at the top level is a special feature
lusty igloo
Thank you very much mate
fringe tiger
what's the difference between %s and ? placeholders
like
query = "SELECT * FROM GUILDS WHERE GUILD_ID=?"
connection.execute(query, (guild_id,))
vs
query = "SELECT * FROM GUILDS WHERE GUILD_ID=%s"
connection.execute(query, (guild_id,))
I used ? in sqlite but now for mariaDB it accepts %s
lusty igloo
It actually is the fact that my $eval (which uses exec) is working on async def function (it compiles all code in async) so thats why I missed it so thanks much
harsh pulsar
@fringe tiger The choice of placeholder is up to the library developer
harsh pulsar
@torn sphinx SELECT student.id AS student_id, class.id AS class_id
lofty quiver
Guys pls mysql3 code with comments
fallow blaze
lmao
proven wagon
What PostgreSQL Data type do i use to store a list
proven wagon
Its a list of uuids
harsh pulsar
array would work
@proven wagon https://www.db-fiddle.com/f/9AqnU63VxtDQ67HKQ286ay/1
proven wagon
Using pgAdmin i cant create a column with data type Array
harsh pulsar
you don't write "Array"
read the docs and my example
https://www.postgresql.org/docs/current/arrays.html#ARRAYS-INPUT
To illustrate the use of array types, we create this table:
CREATE TABLE sal_emp (
name text,
pay_by_quarter integer[],
schedule text[][]
);
sweet jetty
Can anyone help me out with that question? https://dba.stackexchange.com/questions/248823/history-of-updated-rows
tawny sail
before changing the value, u can store the previous one else where
torn sphinx
I have a question:
What is the main difference between databases and handling things with controlling directories and files on your computer?
hope you understand what I am asking
harsh pulsar
databases tend to be much more optimized
hasty hinge
Is it possible to store a python dict as value in a mysql db?
harsh pulsar
@torn sphinx basically using a database will be a lot faster for a large category of operations, and also gives you a query language
the database is like a very specialized filesystem
@hasty hinge dump as json
torn sphinx
Thank you! But since I spent 2 days trying to fix a problem(at the time no one gave me help(not in this server)), but I finally gave up and spent 2 more days coding..and FINALLY making it work - but with the controlling directories and files way :/
hasty hinge
How can I do that? I've never used json @harsh pulsar
harsh pulsar
@torn sphinx depends on the task. sometimes it's easy to work with files
actually @hasty hinge mysql supports a JSON data type that will be better
but you still need to serialize to json
torn sphinx
Yeah! I pretty much use that way to create 1 directory and make 1 text file ...so nothing big,or intense,and it will not be globally used so perfect for me,then! :
🙂
harsh pulsar
!e
import json
data = {"a": 1, "b": 2}
print('Python object: ', repr(data))
print('JSON serialized: ', repr(json.dumps(data)))
@harsh pulsar Your eval job has completed with return code 0.
001 | Python object: {'a': 1, 'b': 2}
002 | JSON serialized: '{"a": 1, "b": 2}'
I apologize,my keyboard is broken so I have to REALLY press the buttons :d
probably from how much I have used it..
on a laptop,that is
harsh pulsar
@torn sphinx if you want to buy a mechanical keyboard one day, i can help. ping me in one of the off topic channels if you want 🙂
torn sphinx
Thank you! maybe some day when I will have a job(but I plan to start saving for my own house really early 😛 )
harsh pulsar
you can skip the 'Installing MySQL 5.7.7 Labs" section
start with "MySQL JSON data type and Python"
torn sphinx
@harsh pulsar I have a question about you,wish to have a conversation,can I direct message You?
harsh pulsar
i would rather not DM
torn sphinx
Oh,I understand..
clever pulsar
I have a situation that i have a list that i want to store in a column of a table, what is the best way to retrieve it back into a list?
harsh pulsar
@clever pulsar what database, and what's in the list?
clever pulsar
The list is my stock portfolio. it contains stock ticker and current stock value and number of of stocks held.
mySQL is the one i am using and i use SQL alchemy to make the connection.
harsh pulsar
@clever pulsar why not make a table like ticker | timestamp | value
clever pulsar
thats a good idea
clever pulsar
I think i really need to think of a better structure for what I am working on.
harsh pulsar
better to realize at the beginning than at the end
celest perch
Hey everyone Iam rookie about database topics
I have postresql and orm with SQLAlchemy
Sample ;
Guid Age Weigh Height
(guid values) 20 60 160
(guid values) 25 60 160
so I want on this example count duplicate values on this example . How can I do that
harsh pulsar
Duplicate GUIDs? Or duplicate age/height/weight
pliant lodge
Has anyone ever encountered a ON CONFLICT DO UPDATE command cannot affect row a second time postgres error? I've tried searching online and none of the so posts seem to help me.
harsh pulsar
this didnt help? https://stackoverflow.com/a/42994480/2954547 @pliant lodge
pliant lodge
I'm having a really 
of a time currently with this:
It works in a unit test perfectly
it works in a pure sql console.
But not in Django.
I feel like its django doing this
harsh pulsar
its possibly/likely django doing it
can you turn on query logging
so you can see what django is actually doing
pliant lodge
its not doing anything unexpected
harsh pulsar
whats the query
pliant lodge
INSERT INTO foo (name, price, url, image_url, item_number, website_id)
SELECT tw.name, tw.price, tw.url, tw.image_url, tw.item_number, tw.website_id
FROM temp_product tw
ON CONFLICT ON CONSTRAINT unique_item DO UPDATE SET (price, url, image_url, item_number) =
(EXCLUDED.price,EXCLUDED.url,EXCLUDED.image_url,EXCLUDED.item_number)
So basically the setup I have rn is: I'm creating a temp table from a CSV and then trying to upsert into the real table
harsh pulsar
i have no idea. you could ask on SO (although you will probably need to provide more info)
pliant lodge
Sure is a mystery, thanks though
celest perch
@harsh pulsar duplicate age/height/weight
harsh pulsar
@celest perch you can GROUP BY those columns and SELECT count(*)
celest perch
thank you
toxic rune
Say I have a table:
+----+----------+----------+
| ID | Column 1 | Column 2 |
+----+----------+----------+
| 1 | 1 | 0 |
+----+----------+----------+
| 2 | 1 | 1 |
+----+----------+----------+
| 3 | 2 | 0 |
+----+----------+----------+
| 4 | 3 | 0 |
+----+----------+----------+
| 5 | 3 | 1 |
+----+----------+----------+
| 6 | 3 | 2 |
+----+----------+----------+
How can I get only the rows with the highest values based on column 1 and then on column 2? For example querying this would return ID #2, ID #3 and ID #6 only, because out of all the rows with Column 1 = 3 the one with highest Column 2 value is ID #6 with 2 as value, and out of all the rows with Column 1 = 1 the one with the highest Column 2 value is ID #2 with 1 as value, etc.
final lion
@toxic rune maybe just SELECT id, max(col2) FROM table GROUP BY col1 ?
torn sphinx
That wouldn't work as you need to use an aggregate function on id
toxic rune
Oh, that works if I instead take the column_1 instead of id (which I don't need in this case)
But in the case I needed the ID too what could I do?
pure scroll
you would apply another query on top of that or go for a window function
I don't think there is any other way
toxic rune
Yeah I checked a bit and seems like these are the only options I have. Thanks!
supple mango
Using mongodb I'm trying to aggregate on a list of dictionaries that contain extra info for the aggregated data. The commented line is where I'm having trouble, currently it adds all created_at datetimes to each aggregated result. Is there a way to have it find the matching id in $$items and get the datetime?
The aggregate:
User.aggregate([
{"$lookup": {
"from": "item",
"let": {"items": "$item_list"},
"pipeline": [
{"$match": {"$expr": {"$in": ["$_id", "$$items.id"]}}},
{"$project": {"name": "$name", 'rate': "$rate", "payout": "$payout"}},
# {"$addFields": {"last_run": "$$items.created_at"}}
],
"as": "inventory"
}},
{"$match": {"_id": doc['_id']}}
])
user document
{
'_id': 1,
'user_id': 0123,
'name': 'Bob Smith',
'item_list':[
{
"id": 1,
"created_at": datetime,
"amount": 3
},
{
"id": 2,
"created_at": datetime,
"amount": 5
}
]
}
item collection
[
{
"_id": 1,
"name": "some_item",
"rate": 60,
"payout": 15
},
{
"_id": 2,
"name": "another_item",
"rate": 30,
"payout": 10
}
]
expected
{
'user_id': 0123,
'name': 'Bob Smith',
'item_list':[
{
"id": 1,
"created_at": datetime,
"amount": 3
},
{
"id": 2,
"created_at": datetime,
"amount": 5
}
],
'inventory':[
{
"name": "some_item",
"rate": 60,
"payout": 15,
"last_run": [datetime from item_list],
"amount": 3
},
{
"name": "another_item",
"rate": 30,
"payout": 10,
"last_run": [datetime from item_list],
"amount": 5
}
]
}
ivory turtle
Heyo!
Does anyone here have some experience with SQLAlchemy?
I'm trying to create a many-to-many relationship between two tables, but I'm running into issues.
Error:
For some reason they seem to not be using the same declarative base/not be initialized at the same time, and this is boggling me.
Code snippets:
from database.dbbase import initialize_sql
import sqlalchemy
SQLALCHEMY_DATABASE_URI = 'mysql+pymysql://' + cfg.mysql['user'] + ':' + cfg.mysql['password'] + '@' + cfg.mysql['host'] + '/' + cfg.mysql['database']
engine = sqlalchemy.create_engine(SQLALCHEMY_DATABASE_URI, echo=False)
initialize_sql(engine)```from sqlalchemy.orm import sessionmaker, scoped_session
from sqlalchemy.ext.declarative import declarative_base
Session = scoped_session(sessionmaker())
Base = declarative_base()
def initialize_sql(engine):
Session.configure(bind=engine)
Base.metadata.bind = engine
Base.metadata.create_all(engine)
from sqlalchemy import Column, String, Integer, Date, Table, ForeignKey
from sqlalchemy.orm import relationship
from database.dbbase import Base
guilds_users_association = Table(
'guilds_users', Base.metadata,
Column('guild_id', Integer, ForeignKey('guilds.id')),
Column('user_id', Integer, ForeignKey('users.id'))
)
class Guild(Base):
__tablename__ = 'guilds'
id = Column(Integer, primary_key=True, autoincrement=True)
name = Column(String)
guild_id = Column(String)
join_date = Column(Date)
prefix = Column(String)
attached = Column(Integer)
users = relationship("User", secondary=guilds_users_association)
def __init__(self, name, guild_id, join_date, prefix, attached):
self.name = name
self.guild_id = guild_id
self.join_date = join_date
self.prefix = prefix
self.attached = attached
Error occurs in guilds.py by the way, on lines 7 to 13;
ergo, this section:
guilds_users_association = Table(
'guilds_users', Base.metadata,
Column('guild_id', Integer, ForeignKey('guilds.id')),
Column('user_id', Integer, ForeignKey('users.id'))
)
Okay, different question now, same subject
session = Session()
guild = session.query(Guild).filter(Guild.guild_id == user.guild.id).first()
user = next((x for x in guild.users if int(x.user_id) == int(user.id)), None)
user.messages_sent += 1
session.commit
session.close
Zero errors, debug shows all is working fine---- but the changes to the user attribute messages_sent aren't being saved to the DB / the DB isn't getting updated
Is the local object simply not deemed the same as the object stored in the DB? do I somehow need to merge/readd it?
rich trout
@ivory turtle You're not calling session.commit, you're just accessing the function
ivory turtle
@rich trout oh my, I can't believe I've been overlooking that for like... the past hour or so
Thanks!
ivory turtle
Amazing, this is the exact moment when you know you've been trying too hard for too long
Thanks for the help there though, and the program is close to completion 😉
harsh pulsar
@torn sphinx use whatever you're comfortable using. also depends on how you're deploying the application
and also the nature of your application
high performance with ajax queries? basic crud stuff? need to store schemaless documents or just relational tables?
but mostly "use whatever you're comfortable using" is the best place to start
most database engines can scale far beyond what a personal project requires
torn sphinx
thank you for the advice
ionic pecan
gentle lark
Any help help with SQLite3?
Command raised an exception: ValueError: parameters are of unsupported type
await ctx.send("The User {0.mention} has been ungulaged.".format(memberg))
c.execute("DELETE FROM muted WHERE discord_id==?", (memberg.id))
conn.commit()
await memberg.remove_roles(role)
await themessage.delete()
return```
wind pelican
@gentle lark (memberg.id) is the same as memberg.id I think you wanted (memberg.id,) that comma makes it a one item tuple. sqlite's methods expect your parameters to be in a sequence like a tuple or list.
gentle lark
oh k thanks :D
real timber
How would one typically go about enabling a user to provide data for use in a database?
if i have some researchers who should be providing data, and my goal is to store that data in Google Cloud and query using BigQuery, I need an intermediary step for the researcher to dump things into... I'm not sure what the most common approach is here though
real timber
@harsh pulsar what, something custom would be written?
harsh pulsar
there is an off the shelf product that builds a REST API out of a postgres database http://postgrest.org/en/v6.0/
but im not aware of something that works on more general databases
you might be able to combine https://flask-sqlalchemy.palletsprojects.com/ and https://flask-restful.readthedocs.io/
real timber
@harsh pulsar hrm... well we're using bigquery, so the data will be hosted in google cloud and queried from there. I'm not sure what the best / generally carried out approach to actually getting the data is though
harsh pulsar
you can also consider using OpenAPI to make things easier https://realpython.com/flask-connexion-rest-api-part-3/
@real timber is there a python dbapi library for bigquery?
or let me back up and ask: how would a remote system normally interact w/ bigquery
real timber
@real timber right - there's an API but a bottleneck is having something that people can actually use
harsh pulsar
http api? some custom protocol like postgres has?
real timber
the usecase is that there are researchers across europe who have data in the form of csv / xlsx files (though just consider csv for now) and I need to centralise this
but apparently none of them can use programming languages 👌
which is making the etl part of it a pig as well... but i'm not there yet
@harsh pulsar but i'm not sure what you're considering here - building some sort of custom site that they could access and dump data into?
or writing a script to send it straight up from the local system
harsh pulsar
so how do you normally interact w/ bigquery? ive only ever used it from the google web GUI
real timber
yeah - that's how I'm interacting with it
but i can't give access to loads of random people I don't think 🤔
i'm still finding my way around bigquery tho
but yes, I just have it on the google cloud platform online
so there are the researchers who have the raw data that we'll get, and they don't need to touch BQ. The analysts can have access to it in order to use the data though
jade horizon
Can anyone help me with my database course?
delicate fieldBOT
harsh pulsar
@real timber yeah i think there is an API for bigquery
real timber
@harsh pulsar there is yeah (although i think it's in alpha...), and that's fine for me. That doesn't help to enable a researcher to upload something though (unless i built a dedicated site for it)
real timber
not seen one yet, unless you mean using the cloud platform.
which might be usable, if i can work out the permissions and stuff... but they wanted to have an intermediary step
jade horizon
!ask
delicate fieldBOT
torn sphinx
anyone knows if I can have multiple databases in 1 python file?
gentle lark
Hello, I'm using SQLite3 and I've came to a problem, when I try to insert into {table} values, this:
`rowid` INTEGER PRIMARY KEY AUTOINCREMENT
is counting like a row, and I have 5 rows:
c.execute("INSERT INTO warns VALUES (?,?,?,?,?)", (warningnum, target.id, discord_name, reason, executioner_id))
shouldn't rowid be autofilling itself?
and this
c.execute("""CREATE TABLE IF NOT EXISTS `warns` (
`rowid` INTEGER PRIMARY KEY AUTOINCREMENT,
`warningnum` INT( 5 ),
`discord_id` INT( 16 ),
`discord_name` VARCHAR( 64 ),
`reason` VARCHAR ( 64 ),
`executioner_id` INT ( 16 )
);""")```
is the whole table if neededfixed it, removed Autoincrement and added (some values) next to warns on ==> INSERT INTO
harsh pulsar
anyone here using an ORM other than sqlalchemy?
curious about Pony
i've used Peewee briefly and it was ok. more beginner-friendly than sqla probably
stable pilot
Hey guys i have a column thats really important and i dont want the data to change in them and the 3 other columns are NULL for now.
i want to update a colomn if the row is empty , so if its empty it can fill it, but if its not then it will find a row thats not empty
basically updating the null field and assigning that row to the unique number generated
and once it is filled whenever again i write something it will assign the next number then repeat
rich trout
I've used Pony quite a bit, and it's worked very well for me
I find it simpler and cleaner, but it does have a lesser amount of users
I switched from SQLAlchemy when I realized that even if you initialized multiple base ORM models, they still appeared to link to each other, resulting in some issues where duplicate table names/duplicate model names needed to be managed globally. I do not recall the exact issue since I hit it a few years ago, but Pony did and does not appear to suffer from the same problem and has worked quite well for me.
stable pilot
hey guys
im trying to do this
userint = ctx.author.id
mycursor = mydb.cursor()
check = "SELECT * FROM playeraccount WHERE playerInfo = userint"
mycursor.execute(check)
myresult = mycursor.fetchone()
for x in myresult:
print(x)
does anyone know what i need for it to work ?
ember sable
I am not that familiar with anything else than ruby SQLite. But check should be something like this as a starter:
check = ’SELECT * FROM playeraccount WHERE playerInfo = ?’ (use a questionmark for variables)
Then do
mycursor.execute(check, userint)
rich trout
Do be careful, as it (usually?) needs the parameters inside a tuple, and providing them raw like that won't work. (userint,), making sure not to forget the trailing comma, will work
torn sphinx
@ionic pecan oh sorry, SQL databases
I had 1 for music and it works perfectly..
I tried to integrate another database for days, and it did not wurk.. :( I even have a SQL browser to easier find problems
deft badge
@torn sphinx could you provide some code or more background, there are a lot of SQL databases and a lot of client libraries, it would be hard to go just off that information
ionic pecan
show your code please
torn sphinx
Thank you for your help
ionic pecan
Thank you for not providing anything to help you with
real timber
torn sphinx
?
real timber
@torn sphinx code is often required when asking questions, read the wiki, it's pretty self explanatory
timber coral
import discord
from io import BytesIO
from discord.ext import commands
import sqlite3
class Ranking(commands.Cog, name='Ranking'):
def __init__(self, bot):
self.bot = bot
self.db = sqlite3.connect("members.db")
self.dbCursor = self.db.cursor()
@commands.command()
async def ct(self, ctx):
self.dbCursor.execute(f"INSERT INTO members(Level) VALUES(1) WHERE Member={ctx.author.id}")
self.db.commit()
...
def setup(bot):
bot.add_cog(Ranking(bot))
members.db is the file, table is members, Level is a collumn i want to edit and set as 1,
and Member is userid
after the query it doesnt execute the code
and i dont know what i did wrong
fringe tiger
Then don't use INSERT but use UPDATE instead
also use placeholders and don't modify strings
also sqlite3 is blocking you should consider aiosqlite
@timber coral
fringe tiger
so you get it now?
fringe tiger
like the word sais
insert is for inserting
update is for updating
ah you mean the value, ye you can update it if it's null. You can set it but you can't increment it. In incrementing case set it to 0 if it's null and then increment it
you should probably just change the default value from Null to 0 if it's a INt
fringe tiger
it's very messy to work with databases that do this kind of thing, it's trigerring me lol
I mean not messy it's just illogical and you as a dev expect a nice database and buum you can't increment it and you loose some time because of that -.-
I did this like 2 days ago with a client and you can use a query like this to increment a field that is a int but has default value of null:
SET Level=IFNULL(Level, 0) + 1
timber coral
ok thanks
alpine axle
Anyone know why this is returning []?
conn = sqlite3.connect('./data/tickets.db')
cursor = conn.cursor()
tables = cursor.execute("SELECT * FROM tickets")
info = cursor.fetchall()
print(info)
and yes, there are records in the db
sharp spear
I don't know anything about sqlite3, but I would assume you want tables.fetchall() not cursor.fetchall().
stable pilot
hey guys i dont know which channel to post this as it is both python and sql
but
async def link(ctx):
userint = ctx.author.id
mycursor = mydb.cursor()
check = "SELECT * FROM playeraccount WHERE playerInfo = %s"
mycursor.execute(check, (userint,))
myresult = mycursor.fetchone()
for x in myresult:
print(x)
#idk what the fuck to do :(
if ctx.author.id == myresult:
await ctx.send(f'sorry you have already done this')
else:
mycursor = mydb.cursor()
randomnumbers = randomPassword()
sql = "INSERT INTO playeraccount (playerInfo, discordName, playerAuth) VALUES (%s, %s, %s)"
val = (ctx.author.id, ctx.author.name, randomPassword())
mycursor.execute(sql, val)
mydb.commit()```
this is my code
i want to fetch my playerInfo aka discord id and if it is equal to the fetched id aka the id that is already in the data base for it not to create a new database, it will only create a data entry if it is not in the data base
what approach should i take now ?@torn sphinx your issue with the second option is in the error message. You are selecting and joining two tables, but then you use COUNT() which in this case presumably would apply to some subset of your join. However, you never actually specify what subset it should apply to. Should it count all the student IDs? Just some of them? The ones per class?
Since there's no aggregate control over your first column, the ordering of your second columns doesn't (to SQL) appear to have any clearly defined choice, and thus it returns that error. You'd want to add a GROUP BY statement, to specify exactly how your counts and students should be grouped.
@stable pilot There's UPSERT, which takes varied names and implementations depending on your database format. If you're using sqlite (which I believe you are doing), UPSERT capabilities were added mid-2018 in version 3.24. Since python 3.7 bundles 3.27, that is probably the solution you would want to use
The details are here https://www.sqlite.org/lang_UPSERT.html
Here is an example query that should give you what you need:
INSERT INTO vocabulary(word) VALUES('jovial')
ON CONFLICT(word) DO UPDATE SET count=count+1
You would need to set your username ID column to UNIQUE, or PRIMARY KEY, but I'm assuming that you'd want that already
wide jolt
@alpine axle you can't use custom clients, deactivate whatever created that embed for you
alpine axle
Rules said nothing about self bots my mistake, i wont use it in here anymore
wide jolt
good, it's not our rule, it's discord's rule btw
alpine axle
I know
I just didnt think you guys cared about discords rules
since your a moderator of python discord
wide jolt
we are a discord partner, discord's rules extend to us
alpine axle
Ahh I see
rich trout
!e ```python
import sqlite3
conn = sqlite3.connect(':memory:')
cursor = conn.cursor()
cursor.execute("CREATE TABLE tickets (id int PRIMARY KEY)")
cursor.execute("INSERT INTO tickets VALUES (12)")
tables = cursor.execute("SELECT * FROM tickets")
info = cursor.fetchall()
print(info)
@rich trout Your eval job has completed with return code 0.
[(12,)]
pure cypress
sqlite3.connect(':memory:') now that's neat
rich trout
It breaks things sometimes ;-;
alpine axle
how come ur works
rich trout
Good question
rich trout
!e ```python
import sqlite3
conn = sqlite3.connect(':memory:')
cursor = conn.cursor()
cursor.execute("CREATE TABLE tickets (id int PRIMARY KEY)")
cursor.execute("INSERT INTO tickets VALUES (12)")
tables = cursor.execute("SELECT name FROM sqlite_master WHERE type='table'")
info = cursor.fetchall()
print(info)
@rich trout Your eval job has completed with return code 0.
[('tickets',)]
Try running that
alpine axle
I did but I got an error lemme try again and get the exact error
cuz I tried that earlier
Oh I see what I did wrong
rich trout
oh?
alpine axle
Earlier when I tried "SELECT name FROM sqlite_master WHERE type='table'"
I thought I was supposed to replace sqlite_master with tickets
rich trout
oooh
alpine axle
like the name of my table
rich trout
yeah, no
rich trout
nw
alpine axle
But if I were to get the values of each record, id have to do something different right
because using
"SELECT * FROM sqlite_master WHERE type='table'"
returns the names of the columns
rich trout
Yes, the reason I asked about running it was to see if the database was actually being connected properly
alpine axle
oh i see
rich trout
If that actually does return your database name, then I'm not sure why your command isn't working
unless you have no records for some reason
alpine axle
that provides me with 0 but if I opened my db in dbbrowser it shows i have records
rich trout
are you certain its the same db?
alpine axle
It should be unless im dumb
rich trout
./ is relative to the running main code's root, not the module's root, if this is in a module
alpine axle
o i figured it out...
I guess the code was trying to access my db while i had my db open in dbbrowser
rich trout
ooh
alpine axle
i forgot, only 1 connection allowed
see this is what i mean, im so stupid sometimes
Thank you Bast ❤
rich trout
i didnt catch that either >.<
dry patio
I keep getting this error
RuntimeError: Task <ClientEventTask state=pending event=on_message coro=<bound method AvailiumBot.on_message of <__main__.AvailiumBot object at 0x7f32061139e8>>> got Future <Future pending cb=[run_on_executor.<locals>._call_check_cancel() at /home/ahd/.local/lib/python3.6/site-packages/motor/frameworks/asyncio/__init__.py:80]> attached to a different loop```
I get that when I do this in my discord bot
neat reef
@dry patio are you running something before setting the loop policy?
Looks like the two event loops are being used at the same time
dry patio
This, but I don't think this has anything to do with it.
dry patio
neat reef
dry patio
I though this was a DB issure cuz its from the motor lib (mongo db drive)
neat reef
It's more related to asyncio
dry patio
Aight
rich trout
@torn sphinx nowhere do you tell it that you're interested in the per-class count
stable geyser
i. f(n) = 13 + 3n^2 – 9n
So i need to find the big O of this right
and show witness values
I can figure out that its O(n^2)
but whats a witness value??
i got no clue
pliant oxide
could you help me with sqlalchemy? https://discordapp.com/channels/267624335836053506/439702951246692352/625282045836394496
^it's the link to help-3 channel
dawn pulsar
What's the least CPU intensive database structure?
void otter
proably mongo
upbeat rivet
I am using PostgresSQL.. Is there a way to get the current time in my time zone?
I am currently using:
EXTRACT(EPOCH FROM localtimestamp) but the server is in america and I am living in europe and want to save the europe time
frozen fossil
CREATE TABLE CRYPTO (id INT, username VARCHAR,money INT); can somebody help me create this 
upbeat swan
CREATE TABLE CRYPTO (id INT(10), username VARCHAR(50),money INT(10));
@frozen fossil
frozen fossil
i need to give the int a limitation too?
gentle lark
@bot.command()
async def warnsby(ctx, target: discord.Member = None):
"""A command to check how many warnings an user has, Stills on DEV :O"""
msgauthor = ctx.message.author
if 477361080574935050 in [role.id for role in msgauthor.roles] or 348174141121101824 == msgauthor.id:
list = []
for row in c.execute("SELECT warningnum FROM warns WHERE discord_id==?", (target.id,)):
list.append(row)
warningnumbers = len(list)
selectme = c.execute("SELECT executioner_id FROM warns WHERE discord_id==?", (target.id,))
selectme = selectme[0]
warner = bot.get_user(selectme)
for reason in c.execute("SELECT reason FROM warns WHERE discord_id==?", (target.id,)):
pass
if warningnumbers == 0:
await ctx.send('This User never was warned.')
return
elif warningnumbers == 1:
await ctx.send('The User {0.name} was Warned **1** Time.\nBy the user: {1}\nWith the reason: {2}'.format(target, warner, reason))```
I get this error (maybe by doing the selectme vars):
``Command raised an exception: TypeError: 'sqlite3.Cursor' object is not subscriptable``you need to show the full traceback
but from a wild guess it happens at ```py
selectme = selectme[0]
cursor.execute doesn't return what you want
you shouldnt assign its result to a variable
instead, you want selectme = cursor.fetchone(), which returns None if nothing was found
ionic pecan
you can access the int field you want then
sage shuttle
Is there something wrong along of the lines of this code? Whenever I purposely input a password less than 5, then put one greater than 5, the print message doesn't show up. Same when I finish the login with a password, the print message doesn't show up and the code repeats
proven wagon
Experienced PostgreSQL users here?
Wondering how i would go about saving objects in a list ( One table for groups, one table for users, one table for warnings, one table for infractions )
A user can be member of a group,
A group or user can recieve a warning or infraction.
harsh pulsar
What do you mean "in a list"
The warnings table would have a group_id and user_id column, which would be foreign keys to the corresponding tables. And you can use a CHECK to make sure that exactly one of those two fields is populated
tall spoke
So how exactly is everyone storing data here. Like for large user counts with multiple fields of information?
Because JSON is not cutting it for me with so many KeyErrors
sand gorge
Hello!
I have a doubt .. try to reach 10/10 using pylint is really necessary?
in my flask application I'm using factory method and in my model.py pylint says:
Instance of 'SQLAlchemy' has no 'String' member (no-member) all the methods I founded just ignore the problem.. but nothing to resolve this..
gilded loom
uh what are you trying to do
i mean i guess it looks like there are no fields in the table with a db.String
so it sounds like a datatype problem
pliant oxide
Guys, do you know sqlalchemy? I need help. I posted here few times, but no reponse.
pure scroll
can you post it again?
pure scroll
I think the problem here is that you have rather an open question answers to all your questions you can find in the documentation.
https://docs.sqlalchemy.org/en/13/core/defaults.html
https://www.w3schools.com/sql/sql_update.asp
http://docs.sqlalchemy.org/en/latest/core/dml.html
pliant oxide
@pure scroll Now I got a problem with mapping tables from database to python objects
Should I reflect those tables with automap base (prepare function) or reflect funtion
OR
Should I use declarative base and define those python objects in code
ivory turtle
Should I use declarative base and define those python objects in code
That one iirc
SQLAlchemy yes?
And plausibly ORM?
@pliant oxide
pliant oxide
yes, SQLAlchemy and orm
pliant oxide
yeah, I read that
ivory turtle
Did it work out for you?
Or give you errors :P?
Because, iirc, this blog should answer your question above
pliant oxide
It helped. The problem is I'm lost. First I read about defining classes for tables, then I read we can reflect tables from database. Then I was reading about differences between engine, connection, session, because I couldn't copy-paste that solutions from link (I got two separated files where I use it, I don't know how to combine them).
@ivory turtle
ivory turtle
Ah
Okay so, did you define the table as a python class?
Do you have a session/connection set up as well?
Cause if you have those
And you set up your table in your DB as well, you can just try inserting
pliant oxide
Can I post a two snippets?
ivory turtle
Yes of course
pliant oxide
hmm, too long, I will use hastebin
ivory turtle
On the move now btw, so I'll be on my phone to help out, just so you know why I might respond slowly 👀
pliant oxide
So I'm using SQL procedures all the time to insert airline/airport/offer
but now I wanted to add support for "searches" table
I don't want to write another procedure on MySQL database (this solution is wrong, but I know how to do it already), so I wanted to use builtin ORM
I wanted autoload first, but then I asked: "is it the right choice?"
That solution with autoload was from here: http://www.blog.pythonlibrary.org/2010/09/10/sqlalchemy-connecting-to-pre-existing-databases/
ivory turtle
I know people like that for ease of use, I personally don't like it, but I haven't found any reasons as to why it would be better/worse (yet)
I'd say if it works for you, and you want it, do it :P
But so far, does the rest of your DB work? Cause that's first priority eh
pliant oxide
Yeah, database is set up already
ivory turtle
And it all works? Since you shared a pic of full null entry
pliant oxide
but I can't do that solution from the snippet
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
engine = create_engine('sqlite:///places.sqlite', echo=True)
Base = declarative_base(engine)
########################################################################
class Bookmarks(Base):
""""""
__tablename__ = 'moz_bookmarks'
__table_args__ = {'autoload':True}
#----------------------------------------------------------------------
def loadSession():
""""""
metadata = Base.metadata
Session = sessionmaker(bind=engine)
session = Session()
return session
if __name__ == "__main__":
session = loadSession()
res = session.query(Bookmarks).all()
print res[1].title
I wanted that, but... look at this:
Base = declarative_base(engine)
I got only declarative_base()
without engine
and I'm creating engine in another file
So I can't do that line:
engine = create_engine('sqlite:///places.sqlite', echo=True)
ivory turtle
Why not centralise it all though?
Since you'd want to use the same base/engine everywhere right?
pliant oxide
In pastebin you could see that I got method to create engine
db_connect()
BTW, there is something like Metadata also
I don't get what metadata do
ivory turtle
Metadata describes tables/columns of a db
Literally holds the meta data of your data holders
Iirc
Lemme grab the docs tho, just to check
pliant oxide
yeah, I read it, but don;t understand ;f
ivory turtle
Okay so it holds the metadata of your DB if am reading it correctly. And tables will parse the metadata object they're connected to
Okay so uhm
DB has a schematic of sorts, tables are attached to it, but tables themselves don't know how exactly they're attached, or maybe even which database, metadata stores all that
And since its parsed thru the tables, they now know
ivory turtle
Boy, hope I didn't botch that explanation.
Metadata also has further functionality btw
Such as the creare_all() function
Which is pretty neat for when you're using foreign keys
ivory turtle
create_all() creates foreign key constraints between tables usually inline with the table definition itself, and for this reason it also generates the tables in order of their dependency. There are options to change this behavior such that ALTER TABLE is used instead.
:P
pliant oxide
yeah, I see, but if I got no constrants?
;f
and still I think we define foreign key constraints inside declarative class, not outside that class
ok, nvm, let's move on to the problem
ivory turtle
yeah, I see, but if I got no constrants?
Still good to do for schematic creation
and still I think we define foreign key constraints inside declarative class, not outside that class
Yes but this also fills your relational tables
ok, nvm, let's move on to the problem,
Yesss, so back to the problem again?
pliant oxide
yes
Simplified problem:
I want to insert new record with "start_timestamp" (let's suppose it's "999" value) to "searches" table in open_spider function.
I got that code in pipelines.py:
class EskyCrawlerPipeline(object):
def __init__(self):
"""
Initializes database connection and sessionmaker.
Creates deals table.
"""
engine = db_connect()
# create_table(engine)
self.Session = sessionmaker(bind = engine)
logging.error("FIRST?")
session = self.Session()
try:
session.execute('SET FOREIGN_KEY_CHECKS=0; TRUNCATE TABLE offers; TRUNCATE TABLE flights; SET FOREIGN_KEY_CHECKS=1;')
session.commit()
except:
session.rollback()
raise
def open_spider(self, spider):
session = self.Session()
# HERE I WANT TO ADD RECORD TO SEARCHES TABLE
logging.error("SECOND?", session.)
pass
and some messy code in models.py:
DeclarativeBase = declarative_base()
def db_connect():
"""
Performs database connection using database settings from settings.py.
Returns sqlalchemy engine instance
"""
return create_engine(get_project_settings().get("CONNECTION_STRING"))
class SearchesDB(DeclarativeBase):
__tablename__ = "searches"
__table_args__ = {'autoload':True}
def loadSession(engine):
metadata = DeclarativeBase.metadata
how to fix it to work ;_;
pliant oxide
No, I don't have errors, because it's not implemented
You see, I wanted to implement that
that's why I got messy code in models.py
ivory turtle
Aight so, I personally define python classes to build objects which I then insert into tables
pliant oxide
But I still not using this
ivory turtle
Yeah, figured as much
ivory turtle
So, you'll need to define some variables as columns and parse their type
pliant oxide
Ok, let me open some tutorial and give 1 minute
ivory turtle
The one I linked at the start showcases this
For the movies.py file for example
They define some variables, set them to columns(var type) and then set them up as object variables
class Movie(Base): __tablename__ = 'movies' id = Column(Integer, primary_key=True) title = Column(String) release_date = Column(Date) def __init__(self, title, release_date): self.title = title self.release_date = release_date
Prime example
Hope it copied all right since I'm on my phone and the codeblock looks positively botched to me
ivory turtle
Aight
pliant oxide
i ping you
pliant oxide
@ivory turtle ok, I back
It took more time I thought
I defined that:
class SearchesDB(DeclarativeBase):
__tablename__ = "searches"
id = Column(Integer, primary_key=True)
start_timestamp = Column(BigInteger)
end_timestamp = Column(BigInteger)
id should be also auto incremented
but I don't know how to add that information
torn sphinx
okay in my program i have 2 classes, one being the peewee model for a sqlite database and the other being the class containing the business logic for my application. how do i integrate the two?
@pliant oxide @ivory turtle sorry for cutting in btw
ivory turtle
@pliant oxide I personally created the tables in my DB and set the I'd column to pkey with auto increment enbled
I am however pretty sure you can just he column auto increment value to true
And also pkey to true
*set the column value
torn sphinx
Okay,I changed my mind,databases are cool,they do not hate you when you get to know them better 😒 ❤
ivory turtle
@pliant oxide did you get it working?
pliant oxide
@ivory turtle Hi again 😄 Yes! Still I think it could be done better. Now I try to figure out how to get and store last inserted record.
ivory turtle
@pliant oxide query for table of type (object), returns a list of table entries, get last thru basic python :P
pliant oxide
I don't want to do in that way, because it could not be last record
I want to store index of inserted record
then to get that record by index
I got something like that:
def open_spider(self, spider):
session = self.Session()
searchDB = SearchesDB()
timeUTCNow = datetime.datetime.utcnow()
timestampNow = timeUTCNow.timestamp() * 1000.0
searchDB.start_timestamp = timestampNow
try:
session.add(searchDB)
session.flush()
self.currentSearchID = searchDB.id
session.commit()
except:
session.rollback()
raise
finally:
session.close()
pass
def close_spider(self, spider):
session = self.Session()
timeUTCNow = datetime.datetime.utcnow()
timestampNow = timeUTCNow.timestamp() * 1000.0
try:
q = session.query(SearchesDB)
q = q.filter(SearchesDB.id== self.currentSearchID)
record = q.one()
record.end_timestamp = timestampNow
session.commit()
except:
session.rollback()
raise
finally:
session.close()
pass
I use sqlite3 for my discord bot 😂
harsh pulsar
nothing wrong with sqlite3
im writing a bot and im using sqlite3
its easier to set up than any other database
its a single file for easy backups and portability
runs on all non-bizarre platforms
torn sphinx
THANKS ❤
FINALLY that I am using something that no one is saying that sucks 😄
WHAT DOES THIS MEAN? ;(
error - database is locked
sqlite3 is nice until it blocks
neat reef
@torn sphinx it means that the database it's locked
neat reef
Is there another program accessing the database?
ivory turtle
@pliant oxide that's great! If you run into further issues with it (for example with relational tables, lmk!)
pliant oxide
😊 ok
torn sphinx
No one? 😢
hello darnkess smile friend
How can I fix this NoneType' object is not subscriptable
neat reef
@torn sphinx the song says "hello darkness my old friend"
Also, showing the line where it happens the error would be helpful
torn sphinx
I did send,but I delted :d
member_exp = SQL.fetchone()
SQL.execute(f'select Member_ID from Experience where Server_Name="{server_name}" and Server_ID="{server_id}"')
member_dbID = SQL.fetchone()
if member_dbID[0] == None:
print("")
newexp = exp
SQL.execute('insert into Experience(Server_Name, Server_ID, Member_ID, Exp) values(?,?,?,?)', (server_name, server_id, author_id, newexp))
db.commit()
return
else:
member_exp = sorted(member_exp[0])
mexp = member_exp
there @neat reef
neat reef
Probably SQL.fetchone is returning None
neat reef
Well, you just check for that case
torn sphinx
CHECK WHAT? :d
neat reef
The return value
torn sphinx
I mean there is a ID
because I have it open in a database browser
Now I get this
member_exp = sorted(member_exp[0])
TypeError: 'int' object is not iterable```member_exp[0] is returning a integer
torn sphinx
I will try inting the member_exp[1:-1
neat reef
What error
neat reef
That's what the error says
neat reef
?
neat reef
members_exp[1:-1] returned an empty string
torn sphinx
0/0 how >d
harsh pulsar
have you tried printing it
proven wagon
I dont recoment saving things by name if they have a id, names can change at any time. @torn sphinx
torn sphinx
._.
OF FU
you mean server names? thanks!
it was just so I can know from where that is..
ivory turtle
In my opinion, yes.
storm hawk
so would "id" be primary key and auto incriment?
ivory turtle
yes
storm hawk
or the actual user id
ivory turtle
Reason being, that later you might want to store.. idk, some kind of guild-specific data, but users could be in both guilds. (think about the number of messages sent or whatever)
So unique ID's always
storm hawk
well. users will contain skills, level, hp, gold, and name
ivory turtle
Then well... what do users have/require in said system
okay so
Save skills for later
storm hawk
probably yeah
ivory turtle
(Personally I'd use a relational table for the skills too)
storm hawk
yeah.
ivory turtle
Yeah.. 😛
Or you could just give the player a 'class'
And then bind skills to that specific class.
Anyhow, all thoughts for the future
First get basic user/player stats in there
ivory turtle
right, MySQL huh
storm hawk
i'll just use double for now
ivory turtle
use SMALLINT
ivory turtle
I think MySQL has Int type too for columns tho
But not sure
Oh, if you want to use decimal then doubles iirc
storm hawk
ok
ivory turtle
Not sure what decimal type does anymore, lemme check
storm hawk
you can set a default value? i knew this i just never used it. i'm dumb. that lessens my sql query so much.
storm hawk
totally forgot
ivory turtle
For fields where you're not sure or don't want anything, a common default value is just NULL
Which I believe is used as the default default value anyway ;P
ivory turtle
Awesome
storm hawk
so next is items...?
ivory turtle
An item Table yes
Once again, PKey for index
Then idk, names, damage values, gold value for selling
etc.
ivory turtle
sec
storm hawk
k
ivory turtle
Okay, so what now 👀
storm hawk
i think the relations table
proven wagon
How do i do a lookup where one where one of the columns are equal to (set value)
One out of 4 colums may contain that value
storm hawk
pretty sure it's just SQL
ivory turtle
Oof. Been a while since I resorted to raw SQL instead of an orm
Okay so, in your software set up a 3rd table
add 2 columns
storm hawk
k
proven wagon
Im using postgresql
Can i do like 'WHERE colum1 or colum2 or colum3 or colum4 is (value)'
ivory turtle
You should be able to add foreign keys to said columns and direct them to the primary keys of the 1st and 2nd table @storm hawk , you'd have to look up how though since it's software specific
@proven wagon
SELECT * FROM table_name WHERE [condition]
It'll select any and all rows matching your condition
No need to specify the columns you want to check
But if you do
OR / AND are usable
Yes
SELECT * FROM table_name WHERE AGE >= 25 AND SALARY >= 65000;
SELECT * FROM table_name WHERE AGE >= 25 OR SALARY >= 65000;
Yes but what would said contition be
proven wagon
In my table, there will be one out of 4 colums that will equal my value
ivory turtle
okay
so
WHERE col1 == 1 OR col2 == 1 OR col3 == 1 OR col4 == 1;
?
I assume that would work
Let me know if it doesn't though
storm hawk
@ivory turtle so i got kinda caught up in something
but i'm back now
give me a sec. i'll show you what my tables look like
CREATE TABLE `users` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`user` bigint(20) NOT NULL,
`name` varchar(12) NOT NULL,
`xp` double NOT NULL DEFAULT '1',
`level` double NOT NULL DEFAULT '1',
`deaths` bigint(20) NOT NULL DEFAULT '0',
`wins` bigint(20) NOT NULL DEFAULT '0',
`losses` bigint(20) NOT NULL DEFAULT '0',
`spouse` bigint(20) NOT NULL DEFAULT '0',
`god` varchar(10) NOT NULL DEFAULT 'Hestia',
`class` varchar(15) NOT NULL,
`money` double NOT NULL DEFAULT '1000',
`guild` bigint(20) NOT NULL DEFAULT '0',
`hp` double NOT NULL DEFAULT '100',
`mana` bigint(20) NOT NULL,
PRIMARY KEY (`id`)
)
CREATE TABLE `items` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`owner` bigint(20) DEFAULT NULL,
`name` varchar(45) NOT NULL,
`value` bigint(20) NOT NULL DEFAULT '0',
`attack` bigint(20) NOT NULL DEFAULT '0',
`defense` bigint(20) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`)
)
there's the items
ivory turtle
uh hu
ivory turtle
So you made the 3rd table
ivory turtle
And did a quick google on your software tool and how it allows you to set foreign keys?
storm hawk
yep
storm hawk
mmhm
ivory turtle
Both will be foreign keys
One linking to the primary key of TB1
And the other linking to the primary key of TB2
That's all.
storm hawk
w
ivory turtle
Although, I'm speaking from experience while using an ORM
storm hawk
give me a sec
bruh
it literally doesn't show up
aflajkshfrjakishfinsfjhbksadjhugdfhgfesaiujf
ivory turtle
Hmm?
storm hawk
the columns are a type?
ivory turtle
What doesn't
storm hawk
is that fine??????/
storm hawk
does it matter what they are named?
ivory turtle
for sake of convenience I personally name them
FK_x_tablename_columnname
so
FK_1_users_id
FK_2_items_id
👀
But whatever floats your boat