well I guess the Datastax driver must be what people are using
#databases
timber lily
torn sphinx
sql makes me want to commit death
pure scroll
@timber lily like this one ? https://github.com/aio-libs/aiocassandra
ah you want to have it with tornado
ionic pecan
@torn sphinx it may seem daunting at first, but once you have the basics down it‘s a super powerful tool
torn sphinx
hello, on sqlite to retrieve stuff from a query I can do this: cursor.execute("SELECT ...").fetchall()
how can I archieve the same on psycopg2 without having to:
cursor.execute("SELECT ....")
var = cursor.fetchall()```@pure scroll Thanks, didn't see that one.
At this point I'm evaluating whether Python is really the best fit for async stuff, it seems like most of the async drivers/libraries are young one-person creations and aren't very actively developed
I prefer Python but when I compare the python async libraries to the equivalents in the Node world, the node packages have like 30,000 stars on github and lots of stable releases
pure scroll
you should remember the fact that node was async from the very begining while async for python got popular quite recently
timber lily
yeah for sure
torn sphinx
@ionic pecan Actually sql itself doesnt seem bad, it's just the python part of it. Im using the module mysql and typing it all in power shell. It's just hard to read and type everything.
_ _
I saw a video of someone using popsql but that cost money and isn't python. Any recommendations?
torn sphinx
What can I do to prevent inserting entries where 3 column values are the same with an existing entry in SQLite?
Is it as simple as UNIQUE (col_name1, col_name2, col_name3)?
random barn
yup
torn sphinx
Cool
ionic pecan
@torn sphinx do you mean database server or python library?
inner folio
I went cold turkey on SQL and went full steam on ElasticSearch. I'm still not full conftable yet but I'm starting to like it
chilly slate
is there a way to change the active directory on the Apache24 and mssql? When I use my webserver, in the logs, it says Login failed for user \\ serverName instead of \\ normally my username
torn sphinx
@ionic pecan python library
vernal viper
Hello, Im currently doing an ETL process and was stuck with this simple python problem. I tried posting on the python help sessions but everyone kept spamming it. basically i have a raw template which looks like this https://cdn.discordapp.com/attachments/303906576991780866/611070166125772800/unknown.png
Running a dataframe with values inside, I want to open up that template,(there will be future data init) and dump the dataframe inside so itll look like this:
However, when i run my code, i kept getting this output:
the table formatting stops at cell C2
is there anyway to push the data INTO the table formatting template(appending values)?
right now my current code is like this:
import pandas as pd
import xlsxwriter
import openpyxl
import os,sys
from openpyxl import load_workbook
d = {'this': [1, 2, 3], 'is': [4, 5, 6], 'test': [7, 8, 9]}
df = pd.DataFrame(data = d)
def write_data(self, file_descr):
"""
Use dataframe to_excel to write into file_descr (filename) - open first if file exists.
"""
if os.path.isfile(file_descr):
print(file_descr, 'exists')
# Solution to keep existing data
book = load_workbook(file_descr)
writer = pd.ExcelWriter(file_descr, engine='openpyxl')
writer.book = book
writer.sheets = dict((ws.title, ws) for ws in book.worksheets)
df.to_excel(writer, sheet_name='Sheet1', index=False, header= True,
float_format='%.2f')
# writer = pd.ExcelWriter(file_descr,engine = 'xlsxwriter')
# df.to_excel(writer,index=False,sheet_name = 'Sheet1')
# workbook = writer.book
writer.save()
else:
self.data_df.to_excel(file_descr, sheet_name='Sheet1', index=False,
float_format='%.2f')
file_descr = 'test.xlsx'
write_data(df,file_descr)
thanks for the help!
spare jewel
https://paste.pydis.com/sasihivuza.py
getting no table found error
i tried deleting dbsql
migrating again
still getting error
when trying to create super user
spare jewel
i m using https://github.com/justdjango/teach-me-django.git rep
jade fulcrum
hello does anyone know how to connect an excel file to a sql server
indigo mason
Why
indigo mason
Probably best just to use an IDE like DataGrip
jade fulcrum
@indigo mason cant i just use SQL server ODBC
[SqlServerODBC] (15) Failed to connect to the SQL Server instance: [HY000]: Connection broken
is the error I get
autumn jackal
If anybody is experience with GQL, I need help understanding why this won't work anymore
export const insertNewImageMutation = gql`
mutation insertNewImage(
$email: String!,
$member_id: Int!,
$image_url: String!
) {
insert_media_libraries(
objects: {
email: $email
image_url: $image_url,
member_id: $member_id
}
) {
returning {
email
image_url
member_id
}
}
}
`;```.. is that python?
@torn sphinx most database adapters are compliant with DBAPI 2, which means they work the same, from there on you just add abstractions
you could try SQLAlchemy but my general impression of the tools is that for simpler queries they add more complexity than plain sql
paper ridge
for tpl in tuplelist:
cur.execute('''INSERT OR REPLACE INTO GUV
(Jahr, Ertrag, Betriebskosten, Bruttobetriebsgewinn, Forschung_und_Entwicklung,
Vertriebs_und_Verwaltungskosten, Personalkosten, Abschreibung_und_Abgrenzungen,
Sonstige_Betriebskosten, Geschäftsaufwand, Betriebseinnahmen_vor_Zinsen_und_Steuern,
Neutrale_Erträge, Gewinn_vor_Einkommenssteuer, Steueraufwand,
Nettogewinn_für_weitere_Geschäftstätigkeit, Nettogewinn, Nettogewinn_verfügbar_für_Aktionäre,
Unverwässert, Verwässert)
VALUES ( ?, ?, ?, ?, ?, ?, ?, ? ,? ,? ,? ,? ,? ,? ,? , ?, ?, ?, ? )''', tpl)
This is an SQL command I wrote in Python today (indentation got messed up when copy and pasting)
I am iterating through a list of tuples and trying to insert the touple (tpl) into my SQlite database
does anyone have any idea why it doesn't work?
I don't even get an error message, it just doesn't do anything
pure cypress
you probably need to commit
connection.commit()
You can do the commit after the for loop ends
copper sphinx
What would be the best approach to a database ORM for a Discord bot as it needs to have easy way to change the database backend?
I really don't know, but async-sqlalchemy seems to be abandoded?
harsh pulsar
@copper sphinx its not an orm as such but maybe this helps https://www.encode.io/databases/ -- its a wrapper around asyncpg, aiomysql, and aiosqlite
copper sphinx
Well, MSSQL is not supported. :(
harsh pulsar
is there an async ODBC package?
that would get you cross-db support for any db that supports ODBC
copper sphinx
Well, there are ORM's being made on top of the SQLA Core, but unfortunately they are not production ready.
Ah, apperently there ain't any async ODBC packages which I could find.
harsh pulsar
thats a bummer
i guess theoretically you could do the sql work in another thread but thats pretty annoying
copper sphinx
Yeah, and that's a hacky workaround too.
craggy wolf
Hello
Anybody have ever use mssql on docker ?
I have a error while trying to use sqlalchemy to connect to it.
It's Flask py app, It's in another container also.
here is my code.
# sample 1
# params = urllib.parse.quote_plus(
# "DRIVER={/opt/microsoft/msodbcsql17/lib64/libmsodbcsql-17.4.so.1.1};SERVER=127.0.0.1;PORT=1433;DATABASE=moldb;UID=sa;PWD=molDb@test2019"
# )
# params = 'DRIVER={/opt/microsoft/msodbcsql17/lib64/libmsodbcsql-17.4.so.1.1};' \
# 'SERVER=localhost,1433;' \
# 'DATABASE=moldb;' \
# 'UID=sa;' \
# 'PWD=molDb@test2019;'
# params = urllib.parse.quote_plus(params)
# engine = create_engine('mssql+pyodbc:///?odbc_connect=%s' % params)
# sample 2
# engine = create_engine(
# 'mssql+pymssql://sa:molDb@test2019@127.0.0.1:1433/moldb',
# pool_pre_ping=True,
# echo=True)
# connection = engine.connect()
# result = connection.execute('exec sp_databases')
# print(result)
# sample 3
import pyodbc
pyodbc.connect('DRIVER={ODBC Driver 13 for SQL Server};SERVER=127.0.0.1,123;DATABASE=moldb;UID=sa;PWD=molDb@test2019')
all lead to same result.
(pymssql.OperationalError) (20009, b'DB-Lib error message 20009, severity 9:\nUnable to connect: Adaptive Server is unavailable or does not exist (127.0.0.1:1433)\nNet-Lib error during Connection refused (111)\n')
I do research in a lot of place and try many different way. it's still not work right now.
I just move from nodejs to python [Flask],
Can somebody would give me some help ?
void otter
@copper sphinx SQLAlchemy
craggy wolf
Waiting ^^
void otter
my man, use flask-sqlalchemy
craggy wolf
Ref from official sqlalchamy doc said the OpparationalError is come from odbc itself not sqlalchemy
So is it different Sqlalchemy and Flask-sqlalchemy ?
torn sphinx
What is the best data base server for Android app ?
I heard about firebase but its anlittle tricky
I need with username pass auth
void otter
@craggy wolf
Yes they are different but flask-sqlalchemy is easier to use and improves code readability
craggy wolf
@void otter now it's work after i try to connect with Sql server on window!!
@void otter Thx you for answering me ^^
void otter
Np
craggy wolf
@void otter now i think mssql on docker might have problem or maybe the setting in Flask all on docker.
inner pecan
for i in fields:
print("print i in fields : "+i)
c.execute("ALTER TABLE L1 Add COLUMN (?) VARCHAR(255)", (i,))
print("Column added")```
Hi, can anyone help me understand why this c.execute fails to do anything, my program gets frozen on the foor loop and only does the first item i n the listfields is a list of columns I want to add to table L1
['56', '57', '58', '59', '60', '61', '63', '64', '65', '66', '67', '68', '69', '70', '71', '72', '73', '74', '112', '113', '114', '115', '116', '119', '120', '516', '652', '653', '654', '655', '657', '658', '659', '1359', '259', '260', '293', '301', '398', '793', '1478', '1604', '1610']
print i in fields : 56
example of output with the list printed. It does the print statement I added for troubleshooting then gets stuck on the c.execute line
void otter
Did you indent it good
inner pecan
yeah it copy pasted weird but its indented fine
inner pecan
(i,)) in this you mean?
I thought I read it needs the comma or it reads it as a tuple and then I get an error, but let me try
I get the same thing if I remove the comma.
the table im trying to alter definitely exists too and im connected to the db
i might have found the issue.
paper ridge
Hi, can anyone help with this: I have a SQL database and I want to insert securityName into it, but it always deletes the previous entries when I do so
code:
cur.execute("INSERT INTO Unternehmen (securityName) VALUES ( ? )", (securityName, ))
cur.execute("SELECT id FROM Unternehmen WHERE securityName = ?", (securityName, ))
unternehmen_id = cur.fetchone()[0]
the table I want to insert it into looks like this: CREATE TABLE Unternehmen( id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE, securityName TEXT UNIQUE )
oh I know where I fucked up now
wooden tulip
how can i make discord my db
fringe tiger
use sqlite if you're a beginner
gleaming frost
@fringe tiger Does he mean use discord as a database?
That's a very funny idea.
Theoretically possible
fringe tiger
He probably means a db for his discord bot
subtle flax
will asyncpg transaction save me from the kind of error where data changes between database operations?
Or should I write sql procedure to avoid it?
example:
- I check if user exists
- I do operations with user if it exists
If user was deleted between 1st and 2nd operations, I would get awful bugs
pure scroll
bugs like?
subtle flax
It will cause errors that should not happen because I checked that user exists
data may be interpreted wrong if it was modified and wrong actions might be taken
pure scroll
writing sql stored procedures eventually means that you will have your business logic in both application and database layers.
Which always leads to opinionated discussions in your dev team. But if you are fine with that, you can do it.
Alternative is that you can lock the user record with the select ... lock in shared mode statement, this will guarantee that no other transaction can modify this record while yours is not closed
But to be honest I think the key problem is that other logic does heavily rely on that, on bigger systems you would usually end up with async operations and eventual consistency models, where problems like that are natural and you should rather try building idempotent operations
subtle flax
but does transaction lock data preventing this or no?
pure scroll
depends on the transaction isolation level
I never had to deal with it, so can't really say. I guess you would have to use select ... lock in shared mode
subtle flax
ok, thank you
frozen osprey
Hey. How to delete object in array in MongoDB?
My array looks like this
'cards': [{'name': 'wooden_shield', 'power': 0, 'health': 10, 'deck': False}, {'name': 'peasant', 'power': 5, 'health': 8, 'deck': False}, {'name': 'pickpocket', 'power': 3, 'health': 1, 'deck': False}]
I want to delete element which's name is peasant
I know how to match this
but idk how to delete this from array cards
thanks in advance
This is what I'v done.
for card in cards
if card['name'] == "peasant":
# delete element here
say
card['name'] == None
wdym?
void otter
do you want to delete the whole object?
frozen osprey
ummm.... I want to delete dict which's name is peasant so I want to delete this {'name': 'peasant', 'power': 5, 'health': 8, 'deck': False},
so this is gonna stay
'cards': [{'name': 'wooden_shield', 'power': 0, 'health': 10, 'deck': False}, {'name': 'pickpocket', 'power': 3, 'health': 1, 'deck': False}]
dawn pulsar
Hey everyone, recently some people found an exploit and were able to insert more entries then they should have been able to (Should be a max of 1 per hour) but they managed 11 in one second, my question is can I create a query that checks if anyone's id appears in the database more then once in any hour?
Table name plot_votes
And it has columns uuid, something_else andtime
(Time is a unix_timestamp in milliseconds, uuid is the person who did the request)
wind pelican
you could select entries for that id where time >= current time minus one hour and refuse to insert a new one if there are any results
if your program is multithreaded/concurrent you likely would want to do this in a single statement
how to do that depends a little on your db, but using where not and then a select subquery would likely do it
dawn pulsar
I've fixed it, I was more thinking retrospectively
wind pelican
you mean to go back and remove any that are less than an hour away from the previous one?
you could grab the list of entries and sort by date, then check the time between each entry, removing any whos date is too close to the previous one. but youll want to make sure to always compare to a valid entry.
eg if you remove a date, dont compare the next entry to that removed date. compare it to the previous good date
dawn pulsar
Yes, would I have to do that manually?
Also, it's the time between each entry for each person
wind pelican
you can write a script to do it, but no there isnt any built in function that can go back and do that automatically.
and yeah you would prob need to run this query for each user. that would make the code a lot simpler
how much data is there per user? if you can store in memory the results of a query with just the post id and date for every one of their posts you can do most of the checking in python then go back and remove all the posts you dont like
then run it again for each user
dawn pulsar
Yeah, it's just theres a few hundred entrys per day with a few thousands players
I removed it for one person, but I'm not 100% sure anyone else knew about the exploit
wind pelican
you could run a query to get all entries that are too close to the previous one
dawn pulsar
How would I do that?
wind pelican
then only worry about this if there are other results
dawn pulsar
But I also need to check the time between entries from the same person
wind pelican
so you would prob need to run the query multiple time, once for each user
which can easily be automated tbh
dawn pulsar
Hmm, but like 1k queries
wind pelican
yeah itll be a lot of queries lol
looks like you could use WINDOW, lag and lead to compare two rows in a query: https://stackoverflow.com/questions/7974866/how-to-compare-the-current-row-with-next-and-previous-row-in-postgresql
dawn pulsar
So there's no way to check all entries where the last entry with the same id was < 1 hour before it?
wind pelican
that would let you compare how close the dates are between rows
i think the hard part is going to be grouping them by id
you could prob write a query that runs multiple times or uses like a sub table or view it generates for each user
but that seems more complicated than just running the query multiple times
cause in the db you have multiple rows that are sequential by date, but have different ids
so you cant just compare previous row to next row unless you filter out all the other ids
in theory you could sort by user id then date
and have your comparison function take the two rows and check to make sure the id's are the same
that would protect you when the sorted data transitions between users
you could also sort by user id then date, and jsut compare the two rows. then in python filter out all results where the id of the two entries are different
if you are good at sql, i would prob try using that WINDOW stuff the guy in the stackoverflow did, and add in an additional check to make sure both rows have the same user id [and of course ORDER BY userid then date]
torn sphinx
@frozen osprey
if cards[name] == peasant:
del cards[name]
Use del to delete the dict entry
dull scarab
How would I go about searching in multiple collections for an id?
I have 2 different documents that consist of 2 different question types, and would like to search for a specific id in both of them without having to do a single query to all collections (Pymongo)
Or would it be better to just combine the 2 collections to a combined one named questions and filter by type or something similar?
echo turret
guys can some help me with inserting values into a db
particularly timestamp data using psycopg 2
and postgres
ping me
def outputer(self):
while True:
file_get = requests.get(self.url)
html_txt = file_get.text
html_soup = soup(html_txt, features="html.parser")
# html = soup.prettify(html_soup)
value_habitat = html_soup.select('#quote_price')
for btc_value in value_habitat:
sleep(1)
record_timestamp = datetime.datetime.now()
insert_command = "INSERT INTO test(BTC_price, timestamp) VALUES('" + btc_value + "','" + \
record_timestamp + "')"
self.conn.execute(insert_command)
everything is already imported
so how can I import the btc price and timestamp data
my sql skills are pretty rusty 🙂
dull scarab
Don't use string formatting / concatenation for building queries. You'll open yourself up to sql injections
Use prepared statements instead and placeholders
echo turret
yh but this is just a simple program, not like a website or something 🙂
dull scarab
Should still always practice it
echo turret
I'm getting this error
TypeError: can only concatenate str (not "Tag") to str
its the timestamp thing
line
30
dull scarab
btc_value is a tag in value_habitat
from what I can tell without seing the tracdback
Not a string, hence why you cant concatenate it with a string
Havent done much bs but it probably either has a text attribute you can use, or just call str on it
echo turret
like the thing is I need the store the value of the btc I've webscraped from the web and also timestamp data when I've done it
dull scarab
You can't store objects like that at least
dull scarab
Show the full traceback
echo turret
sure one sec
self.conn.execute(insert_command)
psycopg2.errors.InvalidTextRepresentation: invalid input syntax for type double precision: "<span data-currency-price="" data-usd="10355.5578063" id="quote_price">
<span class="h2 text-semi-bold details-panel-item--price__value" data-currency-value="">10355.56</span>
<span class="text-large" data-currency-code="">USD</span>
</span>"
LINE 1: INSERT INTO test(BTC_price, timestamp) VALUES('<span data-cu...
```is that the whole thing
echo turret
Traceback (most recent call last):
File "E:/projects/projects/pycharm/software/christian/Webscraping.py", line 40, in <module>
run.outputer()
File "E:/projects/projects/pycharm/software/christian/Webscraping.py", line 31, in outputer
self.conn.execute(insert_command)
psycopg2.errors.InvalidTextRepresentation: invalid input syntax for type double precision: "<span data-currency-price="" data-usd="10350.2082546" id="quote_price">
<span class="h2 text-semi-bold details-panel-item--price__value" data-currency-value="">10350.21</span>
<span class="text-large" data-currency-code="">USD</span>
</span>"
LINE 1: INSERT INTO test(BTC_price, timestamp) VALUES('<span data-cu...
heres the code
def outputer(self):
while True:
file_get = requests.get(self.url)
html_txt = file_get.text
html_soup = soup(html_txt, features="html.parser")
# html = soup.prettify(html_soup)
value_habitat = html_soup.select('#quote_price')
for btc_value in value_habitat:
sleep(1)
record_timestamp = str(datetime.datetime.now())
print(record_timestamp)
insert_command = "INSERT INTO test(BTC_price, timestamp) VALUES('" + str(btc_value) + "','" + \
record_timestamp + "')"
self.conn.execute(insert_command)
def create_table(self):
create_table = "CREATE TABLE test(id serial PRIMARY KEY, BTC_price FLOAT NOT NULL, timestamp VARCHAR(35))"
self.conn.execute(create_table)
run = comply()
run.create_table()
run.outputer()
If you just need the value you should probably just index the tag, get the text.
And since your BTC_price is a float.. not a string (in your table)
you need to convert that as well
echo turret
Traceback (most recent call last):
File "E:/projects/projects/pycharm/software/christian/Webscraping.py", line 40, in <module>
run.outputer()
File "E:/projects/projects/pycharm/software/christian/Webscraping.py", line 30, in outputer
record_timestamp + "')"
TypeError: can only concatenate str (not "Tag") to str
Should probably also optimize it by using execute_many
echo turret
can you show me how
dull scarab
print btc_value see what it is, work your way down to a single value
convert it to a float
tostring the datetime
insert
To use execute many you push a tuple of (btcvalue, timestamp) to a list, and when youre done pushing all the value you give execute many the query, (with prepared statements,) and your list.
I gotta get back to work, good luck
echo turret
thanks!
late schooner
hey so I've got this idea for a python program, I've made lots of things with python but not done anything with databases
I'm imagining a linked list data structure
and using tkinter for my GUI
so for saving this to be able to reload, do you just pickle the linked list object?
do you need to pickle every node?
or is this the wrong approach entirely?
I want a linked list because I want a given node to in theory be able to point to many 'relative' nodes
which can point back to it, if they are related of course
the user would define what nodes are related to what other nodes
though I don't need help with implementing that, I mainly am unsure about how to get it so the user can make a savefile and exit the program, and then reload the file later when they want
dull scarab
Sounds like you want a table for nodes, and a table for connections
dull scarab
Database table
long stream
@abg I think pickling would be an option. Other common formats for real simple data storage are JSON and YAML. There's Python libraries for both, and for plenty other options too. Even just a CSV file may work and get you up and running quickly.
Hey folks Ive got a SQLAlchemy question if anyone's willing to weigh in. I know that the ORM will generate SQL tables from table objects defined in Python if they don't exist, but what if I want to give a particular application read-only access? Do I accomplish that with user permissions in the DB itself, or is there a SQLAlchemy setting or something that says effectively "use the ORM and these table definitions, but do not try to change the table structure (schema?) no matter what"?
pure scroll
you would typically do that with user permissions
digital wharf
Im looking into using databases for an application (discord bot). However I am not sure about what I should use. I am going back and forward between postgres and mongodb, anyone got an recommendations?
upbeat lily
If you're goal is at least partly to build knowledge, I'd recommend postgres, because the experience with a sql database is a lot more generically useful than that of mongo
digital wharf
Yea, Im looking to build knowledge in a database area as well as getting something that will work for me
Im just looking at some things and wondering because sql/postgres seems to prefer splitting among loads of tables instead of just having them together as a big info group? Could just be what im looking at though
upbeat lily
Well, the splitting data amongst different tables is common to all sql databases
Generally once you get your head around it, that way of storing data makes life easier
digital wharf
yea thats the thing, I just dont have my head around storing data in different tables rather then just all together
upbeat lily
Well, it's a very good thing to learn. If what you're storing isnt to complex, you'll generally only end up with a few tables, but I havent had any sizable experience with mongo, so I can't offer a direct comparison
long stream
Is there any problem with leaving a DB session open indefinitely? I've got a program that needs to monitor a DB for changes and take action when something does change...seems like a waste of time to continually open and close the session when 99+% of the loop cycles will see no change in the DB at all.
(Postgres DB if that's relevant)
proven wagon
Is there a database that anyone really recomends for beginners? I've used json for ages now for my discord bot and i really think its time to move on to something "bigger"
Is there something wrong with still using json? Why are other databases than json "better", in which ways?
upbeat lily
SQLite is a great first step
proven wagon
yeah i think i poked around with that when looking at django tutorials
upbeat lily
Its SQL based meaning you use it the same way you use most databases, but filebased which means no pain-in-the-ass config set up issues
And the drivers are built right into Python making it very easy
proven wagon
What about my second and third questions?:
Is there something wrong with still using json? Why are other databases than json "better", in which ways?
upbeat lily
Well, a variety of reasons, but one simple one is that if you're opening and closing JSON files or writing to them too rapidly things can go wrong very easily
proven wagon
I noticed that, having about 4000 lines of json lost one time
upbeat lily
A better example is that its much easier to manage your data manually if its in a database - once you end up with even slightly complex schemas, its easy to lose control when you have a million related JSON files
or a JSON file with a million children
Also, as soon as you get your head around it a little bit, a database is almost as easy as using JSONs for everything
proven wagon
can i use a program to better view the .sqlite3 files? i still have the file i used when building a website
upbeat lily
Well, you can't view the contents. But you should be able to execute SQL on it - I know you can with PyCharm pro
so I assume there are free programs that let you do it
something like select * from sqlite_master would give you the tablenames, then you can select * from the relevant tables
indigo mason
There are loads of browsers for SQLite
proven wagon
found sqlitebrowser.org
dusty scroll
hey, i'm kinda looking into migrating some of my heavily accessed stuff to redis from mariadb, what kind of data would be better suited off in redis?
proven wagon
is there some sort of async version of sqlite i should use? (i dont know if i have any idea about what im talking about here)
indigo mason
There is
But
SQLite is fast enough you don't need to worry about it
If you were having scaling or blocking issues it's best just to swap to Postgres
proven wagon
at about 160 servers (discord bot) and growing right now
indigo mason
That's fine
cinder forge
can you insert images into postgres?
void otter
Yes but they take up loads of spacre
cinder forge
so how would i go about making a logger, where if something specific happens it'll print out a specific picture. I was thinking of making a table with the specific requirements along with the pictures
cinder forge
sounds good
pliant pendant
SQLSQLSQLSQLSL
What's most efficient way to do this
for tag in tags:
query = '''SELECT TagID from SFW_AnimeArt_tags WHERE TagName = %s''' % (tag)
cursor.execute(query)
cache.append(query)
cursive river
Hello
Do you guys know how I can trigger mysql function via python ?
i need to do something like this : SELECT bss_smooth_renewals(6)
but once i do :
cur = cnx.cursor(buffered=True, dictionary=True)
iterable = cur.execute(query['sql'], multi=True)
where the sql is the SELECT bss_smooth_renewals(6);changes in DB are not taking effect
why this is in some kind of read-only mode ?
when i run SELECT via mysql console, it works, function make necessary changes like expcted
this is kinda confusing.
once again, this is mysql function (not a procedure)
cursive river
that could be the reason
where DML:
Data Manipulation Language (DML) refers to the INSERT, UPDATE and DELETE statements
DML allows to add / modify / delete data itself. ```@cursive river are you using connection.commit()
cursive river
no
cursive river
oh,
pliant pendant
connection.commit()
cursor.close()
connection.close()
cursive river
let me check it
i just go strait to connection.close()
try:
start = timer()
cur = cnx.cursor(buffered=True, dictionary=True)
iterable = cur.execute(query['sql'], multi=True)
#iterable = cur.callproc('bss_smooth_renewals', [2, ])
#iterable = cur.callproc('bss_smooth_renewals', ('2',) )
result = []
for item in iterable:
try:
result += item.fetchall()
except mysql.connector.errors.InterfaceError:
pass
except:
elapsed = timer() - start
logging.exception(json.dumps({'action': 'running query', 'status': 'failed', "elapsed": elapsed, 'query': query['sql']}))
return "The SQL query (alias: {}) failed, please check the logs for more information".format(query['alias'])
raise
else:
elapsed = timer() - start
logging.info(json.dumps({'action': 'running query', 'status': 'success', "elapsed": elapsed, 'query': query['sql'], 'result': '{}'.format(result)}))
finally:
cnx.close()
return result
you have to commit before you close it
if you wanna write
cnx.commit() before cnx.close()
cursive river
OMG, @pliant pendant you are my hero!
1st run: "message": {"message": "renewals smoothed", "number": ["12803"]}
2nd run: "message": {"message": "renewals smoothed", "number": ["0"]}
i lost like 2 days
wtf!
thanks m8
🍻
pliant pendant
@cursive river my pleasure 
serene slate
I'm building a MySQL database and I know pretty much just the basics and I'm looking for a bit of help.
I'm scraping data and just dumping it into the database. I'm gonna make an API to handle requests from multiple sources querying the database.
When querying for a specific row I need to include 4 of the 15 columns to get a unique row
Currently the scrapes build an ID using a date, time, sport and event name so I have a column called 'id' that is just a string of those 4 values.
When a duplicate ID is detected, I just update existing values.
I'm looking for key/index advice
should I get rid of the string as an ID, replace it with integers. If so, how do I then uniquely identify rows and update them rather than creating a new row each time with an incremented ID
novel pewter
anyone familiar with pymongo?
if I do collectionname.delete_many() without any arguments, will it delete everything in the collection?
serene slate
import pymongo
myclient = pymongo.MongoClient("mongodb://localhost:27017/")
mydb = myclient["mydatabase"]
mycol = mydb["customers"]
x = mycol.delete_many({})
print(x.deleted_count, " documents deleted.") ```looks like that's how you delete all docments
novel pewter
thanks
long stream
@serene slate I'm still learning DB technologies as well but what you're describing is the concept of a "primary key", right? I think the simplest primary key is a single column - usually an integer, like you said, but I think it's possible for multiple columns to be combined such that they comprise a primary key as well. I think the DB schema can be defined in such a way that the DB itself treats that combination of columns as a primary key. Is that helpful or not really?
serene slate
I think I get what you're saying.
So have a primary key as just an int. Then if I want I can create key made of several columns?
I need to go test a query actually.
long stream
Either are valid approaches. You don't need to do both things - multiple columns can be defined as comprising a primary key together, meaning that no row can contain identical values for all columns as another row which already exists, and the DB itself will enforce that.
A single integer column as the primary key is the more common scenario though, I believe.
serene slate
Okay, my query didn't work I was using ON DUPLICATE wrong
using something like this:
...ON DUPLICATE KEY UPDATE...
Can tell it which key to use?
I'm off to google that qustion
long stream
Yeah we've quickly reached the end of my ability to confidently weigh in, haha. Good luck!
serene slate
Okay, problem solved. Make a primary key out of 3 columns seems to do the trick. I'm going to experiment with that for a bit. Thanks for the input people!
long stream
Sweet, glad it worked out
ionic pecan
cloud turtle
Help me :/
cloud turtle
@ionic pecan Help me :/
ionic pecan
don't ping random people assking for help
you need to use single quotes around named_tester, not `
ionic pecan
can you clarify what you mean with "straight"?
cloud turtle
Ahh, google translation.
He's always rewind.
ionic pecan
you mean it keeps running the loop?
cloud turtle
The loop does not continue, it rewinds.
When I pull it out of the loop, the program shuts itself down.
cloud turtle
Full Code: ```py
import pymysql.cursors
import pymysql
paran=int("1000")
ts=int(1)
BlaBla (Connection)
İşaretçimizi oluşturalım
baglanti = db.cursor()
kayit_soru=int(input("Sisteme kayitli isen [1], Değil isen [2], çıkmak için [9] "))
if kayit_soru == 9:
print("İyi günler dileriz!")
if kayit_soru == 2:
yeni_kadi=input("Yeni Kullanici Giris Adin: ")
yeni_ksifre=input("Yeni kullanici Giris Sifren: ")
print("Kayit Basarili!")
sonuc = "INSERT INTO Kayitlar (k_adi, k_sifre) VALUES (%s, %s)"
cursor=baglanti.execute(sonuc, (yeni_kadi, yeni_ksifre))
db.commit()
elif kayit_soru == 1:
named_tester=str(input("Name: "))
password_tester=str(input("Password: "))
baglanti.execute("SELECT * FROM Kayitlar WHERE k_adi = 'named_tester' AND k_sifre = 'password_tester'")
for row in baglanti.fetchall():
kadi = str(row["k_adi"])
ksifre = str(row["k_sifre"])
if ((named_tester==kadi) and (password_tester==ksifre)):
print("Login Successfull!")
ts=ts+1
else:
print("Login Failed!")
```
ionic pecan
that's missing the loop though?
cloud turtle
How is it missing?
ionic pecan
wasn't it part of your initial code?
cloud turtle
Let me pack up now
I've just changed that, and I'm throwing it back.
Full Code: ```py
import pymysql.cursors
import pymysql
paran=int("1000")
ts=int(1)
BlaBla (Connection)
İşaretçimizi oluşturalım
baglanti = db.cursor()
while ts==1:
kayit_soru=int(input("Sisteme kayitli isen [1], Değil isen [2], çıkmak için [9] "))
if kayit_soru == 9:
print("İyi günler dileriz!")
break
if kayit_soru == 2:
yeni_kadi=input("Yeni Kullanici Giris Adin: ")
yeni_ksifre=input("Yeni kullanici Giris Sifren: ")
print("Kayit Basarili!")
sonuc = "INSERT INTO Kayitlar (k_adi, k_sifre) VALUES (%s, %s)"
cursor=baglanti.execute(sonuc, (yeni_kadi, yeni_ksifre))
db.commit()
elif kayit_soru == 1:
named_tester=str(input("Name: "))
password_tester=str(input("Password: "))
baglanti.execute("SELECT * FROM Kayitlar WHERE k_adi = 'named_tester' AND k_sifre = 'password_tester'")
for row in baglanti.fetchall():
kadi = str(row["k_adi"])
ksifre = str(row["k_sifre"])
if ((named_tester==kadi) and (password_tester==ksifre)):
print("Login Successfull!")
ts=ts+1
else:
print("Login Failed!")
while ts==2:
soru=int(input("Kredi mi çekmek istiyorsun [1], Para mi yatirmak istiyorsun [2]"))
if soru==1:
kredi=int(input("Ne kadar kredi çekmek istiyorsun? "))
print("Bizden", kredi,"Kadar kredi çektin. Suanda", paran-kredi,"Paran var.")
elif soru==2:
para_yatir=int(input("Ne kadar yatirmak istiyorsun? :"))
print(para_yatir, "Kadar para hesabina yatti! Bankanda", paran+para_yatir,"Kadar para var!")
else:
print("Bay Bay!")
does loop 1 or loop 2 keep rewinding?
be careful with your screenshots, because from your previous codeblocks i'm guessing you don't want everybody to know your database
pymysql.connect(...
ionic pecan
i got it, no need to remake it
is there a user with that name and password in the database?
pliant pendant
@ionic pecan i think he means it rollbacks
ionic pecan
wait, now I see the issue, the quote suggestino from me at the start was wrong, sorry
you will want to add username and password to baglanti.execute with %s
cloud turtle
Not problem
ionic pecan
baglanti.execute("SELECT * FROM Kayitlar WHERE k_adi = %s AND k_sifre = %s", (named_tester, password_tester))
something like this, like you did above with INSERT
ionic pecan
sorry, i'm busy right now
cloud turtle
Is there anyone else
hollow vale
why doesnt this work import mysql.connector
import hashlib
mydb = mysql.connector.connect(
host="localhost",
user="root",
passwd="password",
database="testdb"
)
mycursor = mydb.cursor()
mycursor.execute("CREATE TABLE master_passwords (passwords VARCHAR(255)")
upbeat lily
In what way doesn't it work?
fringe tiger
@hollow vale commit?
pliant pendant
for folders in os.listdir(path):
for images in os.listdir(path+"\\"+folders):
#print(images)
#unkey = datastore_prep.uidGen(16)
#3 - get folder name image is in & find author by foldername
connection = datastore_prep.getConnection("test")
query = '''SELECT authorid FROM SFW_AnimeArt_author WHERE foldername = %s;'''
cursor = connection.cursor()
authorid = cursor.execute(query, (folders,))
cursor.close()
connection.close()
print(authorid)
print(folders,)
^^ It returns none ^^
this is what it doesn't find
i wanna sleep this one trough 
pliant pendant
REEEEEEEEEEEEEEEEEEEEEE
so it seems that the statement is NOT the problem
anyone aware of psycopg2 with WHERE statement
i solved it
cursor.fetchall()
apparently is a thing
cloud turtle
torn sphinx
is JSON easily corruptible when written to a lot?
upbeat lily
I believe so, yeah
torn sphinx
so, i wanted to use MongoDB for things like leveling system and economy systems
cloud turtle
upbeat lily
Mongo should be fine for that, although I think I'd recommend just learning how to use a sql database Square Rooted
copper echo
so i have one table with guild_id and prefix column, and second table with guild_id and many other columns, how can i move prefix from first table to second based on correct guild_id's?
regal sleet
Hi
what is wrong with this query? ```SELECT COUNT(*) FROM CONCAT('m',SESSIONID) WHERE ID IN (SELECT TWINS FROM STUDENTS)
full function:```CREATE FUNCTION TWIN_THERE(ID TEXT,SESSIONID TEXT)RETURNS TINYINT(20) BEGIN RETURN (SELECT COUNT(*) FROM CONCAT('m',SESSIONID) WHERE ID IN (SELECT TWINS FROM STUDENTS)) END
pastebin https://pastebin.com/8T8b7vEg
opaque dragon
is it possible to connect a pandas datatable to a landing page or web app?
copper echo
https://discordapp.com/channels/267624335836053506/342318764227821568/613395755734138883 if anyone can help me with this just ping me
thanksw
pure cypress
@copper echo Isn't that solved with a simple where clause?
pure cypress
with a subquery or a join
copper echo
SELECT * FROM prefixes RIGHT JOIN guild_settings WHERE prefixes.guild_id = guild_settings.guild_id
should do work?
prefixes is first table
and it has 2 columns guild_id and prefix
and i want to transfer prefix
pure cypress
You need to be using an UPDATE not a SELECT. You need to use an INNER JOIN not a RIGHT JOIN. You need to use ON instead of WHERE (i.e. INNER JOIN ... ON ... = ...)
copper echo
lemme try again
UPDATE guild_settings INNER JOIN prefixes ON prefixes.guild_id = guild_settings.guild_id
is that okey?
copper echo
set
but what is thing after =
UPDATE guild_settings SET prefix = ... INNER JOIN prefixes ON prefixes.guild_id = guild_settings.guild_id
pure cypress
It has similar syntax to the ON but you'd use different column names
copper echo
btw i have to create column in guild_settings called prefix
pure cypress
Then create the column beforehand
copper echo
it will be easy i know how to make it
why different column names?
UPDATE guild_settings SET prefix = ... INNER JOIN prefixes ON prefixes.guild_id = guild_settings.guild_id is that okey for now?
pure cypress
Because if you re-used prefixes.guild_id = guild_settings.guild_id for the SET you'd end up setting the guild_id but what you want to set is the prefix column.
copper echo
uhm can you explain it more?
UPDATE guild_settings SET prefixes.guild_id = guild_settings.guild_id for now i think i totally messed it up
pure cypress
Okay maybe let's break that down
prefixes.guild_id what this means is the guild_id column from the prefixes table
copper echo
yes i get that part
pure cypress
Using guild_id column in the SET is not correct
because you want to set the prefix column instead
potent cloud
Hi, sorry that im jumping in , just need a to ask a small question, is this the right way to use this?
sql_temp.execute(f"INSERT INTO users (user_id, submission_id) VALUES (?,?)", (user_id, comment_id))
exactly
pure cypress
so just change the column names in the set to the prefix column
pure cypress
right
pure cypress
Maybe
copper echo
maybe?
pure cypress
I think some RDBMS require different syntax for the join
some of them want a FROM
which one are you using?
copper echo
postgresql
pure cypress
Ugh okay maybe postgres is different still
It's confusing that they all do it slightly differently...
copper echo
@potent cloud you are using fstring so you can pass variables
so
what should i do
@potent cloud .. in correct format ofc
pure cypress
Okay so it's this in postegres https://stackoverflow.com/a/7869611/5717792
copper echo
UPDATE guild_settings as gs
SET prefix = pr.prefix
FROM prefixes AS pr
WHERE gs.guild_id = pr.guild_id
@copper echo I kinda meant if the SQL is fine
I removed the f string now
I was told something was wrong there but not what
So now i guess. i know it aint the SQL syntax
fringe tiger
don't use f string or format
copper echo
in asyncpg you can use $1 iirc
fringe tiger
you're gonna get injected
copper echo
i dont know what is syntax for vars in other libs
or
is that the same for every?
$1,2,3,4 ...
UPDATE guild_settings SET prefix = '/' WHERE prefix = null how can i update all None in table?
this is not working for me
yup im totally dumb and forget to set default while creating table xd, but i set it now
but
somehow None columns has not updated
i changed = null to IS NULL and it worked
@pure cypress thank you for help
:D
everything worked
pure cypress
np
gleaming frost
This question is about the official postgres docker image.
I want to be able to change my password using the env vars after it has been set. How can I do that?
void otter
if you try to insert null in not null column, what kind of error would i get
ionic pecan
@gleaming frost manually
hallow jetty
I've got this query:
def get_unknown_players(number: t.Optional[int] = None):
coll = get_collection()
pipeline = [
{'$unwind': '$player_index'},
{'$project': {'_id': '$player_index', 'region': '$platformId'}},
{'$lookup': {
'from': 'players',
'localField': '_id',
'foreignField': '_id',
'as': 'found'}},
{'$match': {'found': []}},
{'$group': {'_id': {'id': '$_id', 'region': '$region'}}},
{'$project': {'_id': '$_id.id', 'region': '$_id.region'}}]
if number is not None:
pipeline.append({'$limit': number})
return coll.games.aggregate(pipeline, allowDiskUse=True)
It executes fairly quickly until it hits the match stage.
It seems like it's making a full comparison, when I really just want to check if the document in the other collection exists.
Ideally I could just set found to 1 or 0 in a lookup pipeline or something, but I can't find the right way to say that.
pliant pendant
I need someone with good database planning to help me out
i know how to code databases but i need help to plan it out
harsh pulsar
@hallow jetty what database is that?
long stream
Is anyone familiar enough with SQLAlchemy and asyncio to know the broad implications of trying to add some simple async functionality to calls involving the SQLAlchemy ORM? For example, assume I have an engine and a session - is it worthwhile to use "await session.query(...)" instead of the traditional blocking call without using "await"? Is it pointless to do that, is it potentially harmful, is it useful? I'm new to both technologies and just want to make sure I'm not doing something foolish by making my DB-related function calls coroutines.
Might have just answered my own question actually...after all if session.query() itself is not a coroutine defined with async then adding await to it doesn't work. Anyway, if anyone has useful input for me even so, please let me know 🙂
hallow jetty
@long stream as long as you are using https://pypi.org/project/async-sqlalchemy/ it should be fine 🙂
long stream
@hallow jetty nice, I'll give it a look. Thanks!
pliant pendant
BEGIN
IF NOT EXISTS (SELECT folder_name
FROM SFW_AnimeArt_author
WHERE folder_name = %s)
BEGIN
INSERT INTO FROM SFW_AnimeArt_author (folder_name)
VALUES (%s)
END
END
my eyes are burning
what did i wrong??
harsh pulsar
sometimes discord strips the leading whitespace off the first line
im not sure why
or is that not your question...
pliant pendant
no it's saying error near IF
but i cant think of why
i mean there's absolutely nothing
telling me any syntax errors exist in it
for the %s
in psycopg2
cursor.executemany(query, [[v, v] for v in folders])
looks like you have an unmatched BEGIN with no END
also you're starting a transaction for each query, that could be quite slow
pliant pendant
there's two begins and two ends
pliant pendant
is there any good example otherwise
for if not exist
also tell me more about the each query thing
harsh pulsar
oh you could also use ON CONFLICT
harsh pulsar
INSERT INTO
SFW_AnimeArt_author
(folder_name)
VALUES
(%s)
ON CONFLICT
(folder_name)
DO NOTHING
like i said i havent used psql in a while but hopefully thats correct
the docs are a bit of a learning curve but they are very very good imo https://www.postgresql.org/docs/current/sql-insert.html
note that ON CONFLICT is a nonstandard postgres extension
harsh pulsar
(i think i was wrong about what you would "normally" do)
non-standard means it won't work with other database engines - mysql, ms sql server, etc
harsh pulsar
executemany is correct
harsh pulsar
but you dont need BEGIN, you should use a transaction with psycopg2's API http://initd.org/psycopg/docs/usage.html#transactions-control
pliant pendant
it complains about where not exists again
harsh pulsar
show your query?
pliant pendant
INSERT INTO SFW_AnimeArt_author (folder_name)
VALUES (%s)
WHERE NOT EXISTS (
SELECT * FROM SFW_AnimeArt_author
WHERE folder_name = %s )
ok
transaction example adapted from the docs:
query = '''
INSERT INTO SFW_AnimeArt_author(folder_name)
VALUES (%s)
ON CONFLICT (folder_name) DO NOTHING
'''
folder_names = ['a', 'b', ...]
with psycopg2.connect(DSN) as conn:
with conn.cursor() as cursor:
curs.executemany(query, ((f,) for f in folder_names))
something like that, anyway
using with on a connection starts a transaction, and commits only if there is no error when the with block is exited
pliant pendant
i see
my way of doing it doe
is to commit when all my progress is done
i'll need 3 executemanys
at most
cause it's alot of data to be sent
ill make one cursor object
that follows trough the entire code
until it's done
harsh pulsar
not sure what the value in keeping 1 cursor is
also you can use with multiple times on the same connection
pliant pendant
so i dont need to connect it all the times
yeah
i could transfer connection instead
true
but the cursor way works quite well
btw
how do i avoid the "no results to fetch" error
cause if all the stuff is inside after that transaction we made
psycopg2.ProgrammingError: no results to fetch
harsh pulsar
show the code giving you that error?
pliant pendant
def folmage_crunch(cursor, folders, images = None):
if images == None:
query = '''
INSERT INTO SFW_AnimeArt_author (folder_name)
VALUES (%s)
ON CONFLICT
(folder_name)
DO NOTHING
'''
else:
return "noep"
print(folders)
cursor.executemany(query, [[v] for v in folders])
result = cursor.fetchall()
#Folder doesn't exist in author database
return result```noep is temporary
harsh pulsar
you dont use fetch after an insert
pliant pendant
ah true
harsh pulsar
basically what you would do in your application is this
def main():
connection = psycopg2.connect('dbname=sfw_animeart user=gofven')
and you would pass that connection around to functions
when you want to use a transaction, write with connection: and everything inside the with will be in a transaction
pliant pendant
actually the connection is stored in another python function
so i dont need to open it all the time
so i can import it
harsh pulsar
thats fine. but you would reuse one connection for the whole application
(similarly its good to use with on cursors because it releases any resources associated with the cursor as soon as the block exits)
pliant pendant
one connection for whole application, yes i'm doing it
will it automatically close the connection
when the application is done?
like when i trun off the server?
harsh pulsar
yes but it's better to close the connection manually
pliant pendant
i realised an issue with the image one...
i mean its sorry
i confused u there
yeah i will try fix that later on
i am just thinking of ways to fix a thing i got here
cause it seems that i'll need to read before doing a write
also thanks for the help u gave me
but it's not the last time u'll see me, at all :)
actually i got a really difficult question
could i also remove in the same transaction
folders that doesn't exist
no actually i have to make it separate
molten kelp
i need save data in db, for exect time, exmpl: api give me every 1 min data, i must save this data& this data time
i dont know how it made
i try
cur.execute("INSERT INTO TEST (TIME, VALUES) VALUES (current_timestamp,4.22211)")
postgres or python dont wont show me this)))))
????
harsh pulsar
@molten kelp it sounds like you need query parameters
you are using psycopg2?
cur.execute("INSERT INTO TEST (TIME, VALUES) VALUES (current_timestamp, %s)", [4.22211])
scenic olive
Not sure if this is the right place ,but ill try :) - Hi.
I have two tables. players and discord_channels.
The players table holds the relevant:
battletag (unique player name), rank, games played, time played, etc
the discord_channels table holds:
battletag, nickname, discordChannelId
- a player can be added on multiplie discord channels with different nicknames - but relating to unique battletag in players table.
- a player can have multiplie battletags - but the same nickname (to avoid multiplie entries on a leaderboard for smurf accounts)
What im trying to do is get a leaderboard list based on these conditions
- a unique list pr discord channel id
- i want to sort the list by the MAX of any of the values damageRank, tankRank, supportRank
player1 (nickname: David) is ranked damage: 4300 tank: 4000, support: 3500
player2 (nickname: Joe) is ranked damage: 3800, tank: 4100, support: 4100
player3 (nickname: Joe) is ranked damage: 3800, tank: 4100, support: 4500
the list would look like this:
player3 - 4500 (support)
player1 - 4200 (damage)
** player2 (would not show since player3 is also called Joe but has a higher rank)
heres where im stuck: https://pastebin.com/QxFyCEBD -- any tips on what i can look into?
final lion
which bit are you stuck on @scenic olive ?
torn sphinx
so, how would i make a table in postgres using asyncpg that contains UserID | UserName | EconoBal and then whenever a user (not a bot) sends a message, they get added to the table, when the same users sends another message, their EconoBal gets incremented by 1, if there's a user already in the table and another user sends a message, the new user gets added to the table under the first user's entry
torn sphinx
await s_db_conn.execute('''
CREATE TABLE users(
UserID serial PRIMARY KEY,
UserName text,
EconoBal text
)''')```look into INSERT ... ON CONFLICT
INSERT INTO users (UserID, UserName, EconoBal)
VALUES (%s, %s, 1)
ON CONFLICT (UserId) DO
UPDATE SET EconoBal = EconoBal + 1
maybe try that @torn sphinx
torn sphinx
ok
torn sphinx
well, is my first code portion correct?
harsh pulsar
to create the table, yes
however the UserName thing is kinda funky
should be fine though
if that query works how i think it should work, for an existing user, the UserName value gets ignored
since it's only checking for conflicts on UserId
it might be
INSERT INTO users (UserID, UserName, EconoBal)
VALUES (%s, %s, 1)
ON CONFLICT (UserId) DO
UPDATE SET EconoBal = users.EconoBal + 1
import discord
from discord.ext import commands
class EconoCog(commands.Cog):
def __init__(self, bot):
self.bot = bot
async def snakebotdb():
s_db_conn = await asyncpg.connect('postgresql://postgres@whatever/whateverdb')
await s_db_conn.execute('''
CREATE TABLE users(
UserID serial PRIMARY KEY,
UserName text,
EconoBal text
)''')
@commands.Cog.listener()
async def on_message(message):```should the snakebotdb function go outside of the class?
harsh pulsar
hm
you dont want to re-create the table every time your bot starts up
you should probably have a separate "init" script
but otherwise you should probably create the connection in __init__
then access it with self.connection
torn sphinx
oh ok
class EconoCog(commands.Cog):
def __init__(self, bot):
self.bot = bot
async def snakebotdb():
self.s_db_conn = await asyncpg.connect('postgresql://postgres@localhost/snakebotdb')```not quite
oh hm
i see what you did
yeah, what you had the first time was fine
class EconoCog(commands.Cog):
def __init__(self, bot):
self.bot = bot
async def snakebotdb(self):
self.s_db_conn = await asyncpg.connect('postgresql://postgres@localhost/snakebotdb')
class EconoCog(commands.Cog):
def __init__(self, bot):
self.bot = bot
self.s_db_conn = None
async def get_db_conn(self):
if self.s_db_conn is None:
self.s_db_conn = await asyncpg.connect('postgresql://postgres@localhost/snakebotdb')
return self.s_db_conn
well, that throws an access to s_db_conn before definition
harsh pulsar
where do you see that?
torn sphinx
if self.s_db_conn is None:
ornate isle
it's set to None during __init__
torn sphinx
oh ok
harsh pulsar
class EconoCog(commands.Cog):
def __init__(self, bot):
self.bot = bot
self.s_db_conn = None
self._db_lock = asyncio.Lock()
async def get_db_conn(self):
async with self._db_lock:
if self.s_db_conn is None:
self.s_db_conn = await asyncpg.connect('postgresql://postgres@localhost/snakebotdb')
return self.s_db_conn
this should hopefully prevent two coroutines from trying to set the connection simultaneously
(totally untested, mind you)
torn sphinx
self._db_lock(): isn't callable
torn sphinx
oh ok
where do i put the py await s_db_conn.execute(''' CREATE TABLE users( UserID serial PRIMARY KEY, UserName text, EconoBal text )''') in ```py
class EconoCog(commands.Cog):
def init(self, bot):
self.bot = bot
self.s_db_conn = None
self._db_lock = asyncio.Lock()
async def get_db_conn(self):
with self._db_lock:
if self.s_db_conn is None:
self.s_db_conn = await asyncpg.connect('postgresql://postgres@localhost/snakebotdb')
return self.s_db_conn
@commands.Cog.listener()
async def on_message(self, message):
await self.s_db_conn.execute('''
INSERT INTO users (UserID, UserName, EconoBal)
VALUES (%s, %s, 1)
ON CONFLICT (UserId) DO
UPDATE SET EconoBal = users.EconoBal + 1
''')```
i would write that as a separate function actually
because you only need to call it once, ever (when you first create the database)
you dont want it to run every time the bot starts
maybe you can create the table IF NOT EXISTS
ornate isle
yeah, that's.... about as robust as you'll be able to get while just writing raw sql
harsh pulsar
quick question -- what happens if the user changes their username
do you change it in the database?
torn sphinx
that's a bit difficult to worry about
await s_db_conn.execute('''
CREATE TABLE users(
UserID serial PRIMARY KEY,
EconoBal text
)''')```oh ok
harsh pulsar
also why not use the discord user id as the primary key
torn sphinx
async def econoTable():
await s_db_conn.execute('''
CREATE TABLE users(
UserID serial user.id,
EconoBal number
)''')```so, i should replace serial user.id with bigint primary key?
azure chasm
Does SQLite3 grab by newest?
Like i am doing
fetch = await cur.execute("SELECT AID, Level FROM Bookmark WHERE ID=?",(ctx.author.id,))
Would fetch[0] be the newest item added to the DB?
torn sphinx
well, the code throws this error: Ignoring exception in on_message Traceback (most recent call last): File "C:\Users\Mika\AppData\Local\Programs\Python\Python37\lib\site-packages\discord\client.py", line 270, in _run_event await coro(*args, **kwargs) File "c:\Users\Mika\Documents\SnakeBot Alpha\cogs\economy.py", line 27, in on_message await self.s_db_conn.execute(''' AttributeError: 'NoneType' object has no attribute 'execute'
code: ```py
class EconoCog(commands.Cog):
def init(self, bot):
self.bot = bot
self.s_db_conn = None
self._db_lock = asyncio.Lock()
async def get_db_conn(self):
with self._db_lock:
if self.s_db_conn is None:
self.s_db_conn = await asyncpg.connect('postgresql://postgres@localhost/snakebotdb')
return self.s_db_conn
async def econoTable(self):
await self.s_db_conn.execute('''
CREATE TABLE users(
UserID bigint user.id,
EconoBal number
)''')
@commands.Cog.listener()
async def on_message(self, message):
await self.s_db_conn.execute('''
INSERT INTO users (UserID, EconoBal)
VALUES (%s, %s, 1)
ON CONFLICT (UserId) DO
UPDATE SET EconoBal = users.EconoBal + 1
''')```
the way i set it up, you have to use conn = await get_db_conn()
the point is that the database connection is created automatically the first time you try to use it
azure chasm
The reason for my question btw is i have to remove the newest item added to a user queue
harsh pulsar
i think this might be a bit complicated for your erxperience level
@azure chasm no, it would be the first row in the DB. which i think by default is the oldest but i dont know that you can rely on the insertion order
azure chasm
Oh i meant oldest*
harsh pulsar
i think the answer is no
if you need to track insertion time you should use a timestamp
torn sphinx
@harsh pulsar where would conn = await get_db_conn() go though?
dawn pulsar
I'm a complete database noob, but I've also spent many hours trying to get this working. I have a database online, and I apppear to have no errors, I can select * from a table and see [], which makes sense, but when I try an insert, I still get [] any ideas why?
It's a MySQL database and I'm using mysql-connector
harsh pulsar
@dawn pulsar the insert probably isn't being committed
dawn pulsar
There's no erros
harsh pulsar
call connection.commit() after cursor.execute('INSERT ...')
dawn pulsar
errors*
Ahhhh
It worked
❤
@harsh pulsar Is there anyway I could get someone's position on a leaderboard by count of rows?
So there's 1000 rows, and the column name has their name 15 times, they'd be second behind someone where count(name) == 16
I think I described that horribly, but basically rank users by count of a column, and then get their position, so it'd return, idk, 3
harsh pulsar
groupby?
SELECT * FROM (
SELECT user_id, count(*) as n
FROM my_table
GROUP BY user_id )
ORDER BY n DESC
something like that
dawn pulsar
But can I get someone's position without calculating everyone and then finding them in there?
harsh pulsar
hm... probably not
dawn pulsar
):
harsh pulsar
mysql probably has some kind of RANK function
hallow cloud
views or materialised views
calculate once, and if nothing changes simply the value again
i forgot if that's how they actually work, gotta check it out
welp, a view is not that
oh yea, a materialised view basically acts as a cache
so yea you can create a materialised view, which calculates this
and then each time
you want a specific rank
you simply fetch the already precalculated data
buoyant adder
i'm writing a very minimal cli thing that makes an expensive api call. the results change each day. i'd like to effectively memoise it outside of python.
ideas have been to populate a json file with <date>:<content> and attempt to read it from there before making the api call.
would rather not use a database for this - a bit overkill
any suggestions?
(i know i'm asking #databases for a solution that explicitly doesn't use a database, but no other channels fit better)
basically looking to memoise to disk. it's not the most elegant soln and not difficult to write, but wondered if there were any libraries that can help me
hallow cloud
memcached/redis
buoyant adder
i'm aware of what they are and how roughly they work, but i have not used them
to clarify, the 'content' i'm talking about is a small integer
so i'm looking for something very lightweight
hallow cloud
they're both lightweight
buoyant adder
hm fair
hallow cloud
it's practically a service
and it's just
key-value pair
basically like a dictionary in memory
buoyant adder
ehh you're right
i figured this, i was just hoping to come across something new for this
hallow cloud
a file does the job, doesn't have any overhead
buoyant adder
yep
hallow cloud
wouldn't really recommend anything else since it does the job
persistent data - either db or a file
buoyant adder
i suppose any option i have would just be a wrapper for one of those
dawn pulsar
@harsh pulsar The rank function seems simple but the example people use on SO are really confusing?
harsh pulsar
you might need to rank over a partition
dawn pulsar
Yeah, idk what that is
How do I do that
And what is that?
SELECT
id, RANK() OVER (ORDER BY id ) Ranking
from RankDemo;
I found that as well, does that look right???
I'm well out of my depth
harsh pulsar
SELECT user_id, n, rank() OVER ( ORDER BY n ) rank
FROM (
SELECT user_id, count(*) as n
FROM my_table
GROUP BY user_id )
maybe something like that
oh man mysql doesnt have a rank function
lul
you have to implement it yourself
https://stackoverflow.com/a/3333697
have fun
dawn pulsar
What does this do?
What does the @ and then := mean?
That reminds me of, I think it's haskell?
@harsh pulsar I'm scurred
harsh pulsar
@curRank is a variable
:= assigns to a variable
it's the equivalent of doing curRank = curRank + 1 while looping over rows in python
dawn pulsar
I'm still quite confused
I have lots of entries with colums
staff, name, duration andtime
I'd like to get the person with the most entries in the staff column
e.g.
Skezza, Jeff ...
Skezza, Sam
Salt, Steve
That'd show me as #1 and you as #2
hallow cloud
so doing a materialised view is a nono?
dawn pulsar
SELECT staff,
@curRank := @curRank + 1 AS rank
FROM sessions, (SELECT @curRank := 0) r
ORDER BY count(staff)
GROUP BY staff
How does this look? Am I remotely close?
@harsh pulsar
hallow cloud
how big is your database that you need to implement this effectively
dawn pulsar
It's not mine, and it's got 47k entries
hallow cloud
hmm fair enough
If you can optimize it why not hehe
thought it would be faster with just a regular query with a db this size
harsh pulsar
@hallow cloud mysql doesnt have RANK()
hallow cloud
i mean the workaround rank
dawn pulsar
@harsh pulsar How well did I translate the code? Am I in the right direction?
SELECT staff,
@curRank := @curRank + 1 AS rank
FROM sessions, (SELECT @curRank := 0) r
ORDER BY count(staff)
GROUP BY staff
wdym a regular query @hallow cloud
personally i would just do it in python
but this is educational
harsh pulsar
you want to do what exactly?
hallow cloud
Agreed, this is a task for the db
hallow cloud
rank is based on which atribute
dawn pulsar
e.g.
!rank <Skezza>
dawn pulsar
Right, A) You're a freaking legend
B) nvm
C) How are you so smart
harsh pulsar
A) been around for millions of years, reputation builds up
B) ;)
C) i'm not
also what an ugly hideous query
this is why we use postgres
dawn pulsar
you arreeee
harsh pulsar
heres the postgres version, lol http://www.sqlfiddle.com/#!15/9721d/1
harsh pulsar
yeah i would never use mysql in a project for that reason
there are probably good reasons to use it in a production setting but my sanity is not worth the complexity
dawn pulsar
It's not my DB lmao
harsh pulsar
yeah this is why ORMs are nice
torn sphinx
hey @harsh pulsar here too 🙂
I have a problem with SQLAlchemy, if someone could help me out in #help-falafel I'd appreciate it
lament trail
I'm using postgres and want to have an array that has a limited size
So that If I add an item and it exceeds the limit the oldest item is deleted from the array
What would be the best way to achieve this?
torn sphinx
@harsh pulsar if you can, you should pin that message for the postgres version of the SQL Fiddle thingy
hallow cloud
@lament trail lru
harsh pulsar
@lament trail probably need to use a trigger https://www.postgresql.org/docs/current/sql-createtrigger.html
zenith dragon
Hey, can someone help me with psycopg2 and using the SQL object to pass variables into a query? Below is a REPL example where it fails with the SQL object, but I can pass in a manually formatted, quote-escaped string to get the results I want.
>>> import psycopg2
>>> from psycopg2.sql import SQL
>>> conn = psycopg2.connect(dbname="mydb", host="db", user="admin")
>>> cur = conn.cursor()
>>> cur.execute(SQL("create table {} (id serial NOT NULL PRIMARY KEY, name VARCHAR(32))").format(psycopg2.sql.Identifier("testTable")))
>>> conn.commit()
>>> name = "John Doe"
>>> cur.execute(SQL("insert into {}({}) VALUES ({})").format(psycopg2.sql.Identifier('testTable'), psycopg2.sql.Identifier('name'), psycopg2.sql.Identifier(name)))
Traceback (most recent call last):
File "<stdin>", line 1, in <module>
psycopg2.errors.UndefinedColumn: column "John Doe" does not exist
LINE 1: insert into "testTable"("name") VALUES ("John Doe")
^
>>> cur.execute("insert into \"testTable\"(name) values('John Doe')")
@zenith dragon for values, use query placeholders
double quotes are ANSI style escaping for column and table names
harsh pulsar
no
import psycopg2
from psycopg2.sql import SQL
conn = psycopg2.connect(dbname="mydb", host="db", user="admin")
cur = conn.cursor()
table_identifier = psycopg2.sql.Identifier("testTable")
create_query = SQL("create table {} (id serial NOT NULL PRIMARY KEY, name VARCHAR(32))").format(table_identifier)
cur.execute(create_query)
name = "John Doe"
insert_query = SQL("insert into {} (name) values (%s)").format(table_identifier)
cur.execute(insert_query, [name])
something like that
inner pecan
hello could someone help me with forming an SQL query?
I have two tables with exact same schema with a primary key column Symbol . I want to join on Symbol (which I've done) but where the columns values are not equal to eachother. Example of my header + row after joining which cointains the data from both table, where there's slight differences
harsh pulsar
@inner pecan what condition do you have in mind, other than equality
inner pecan
HI salt rock lamp, what do you mean by condition? So for most rows they are identical across both tables but I want a table of the ones which have values which differ between the two, just if they're not equal
harsh pulsar
hmm
inner pecan
someone suggested I do a join on table1.symbol = table2.symbol and the fields where not equal but Im not sure how this is done.
Like JOIN on table1.symbol = table2.symbol, fieldintable1 != fieldsintable2?
not sure how that last bit is done
inner pecan
but theres 30 fields (columns) do I need to put each one in the query
harsh pulsar
what database engine ar eyou using
inner pecan
sqlite3
so join on table1.symbol = table2.sym, table1.field1 != table2.field1, table1.field2 != table2.field2, ...., table1.fieldn != table2.fieldn
harsh pulsar
SELECT *
FROM table1 JOIN table2 USING(symbol)
WHERE table1.field1 <> table2.field2
so need a WHERE clause for each field?
harsh pulsar
oh you actually want not-equal on all 30 fields?
what if some of the fields are equal, and some arent
inner pecan
so for each symbol (row) there are 30 or so fields (the amount of fields and field names differ actually depending on another part of my script)
i want to find where there's differences
the differences can be in any column other than Symbol
so any of the 30 columns
harsh pulsar
SELECT *
FROM a JOIN b USING(symbol)
WHERE
a.field1 <> a.field1 OR
a.field2 <> b.field2 OR
...
a.field30 <> b.field30
heres quick example
See for symbol AAC column 56 is different across
both tables
so Id like to make a new table
with
SYMBOL | FIELD | TABLE1VALUE|TABLE2VALUE
so for this itd be
AAC|56|0.5931|0.5636
does that make sense?
harsh pulsar
ok. normally you hear the advice "DONT USE F STRINGS FOR SQL"
this is the one and only time you are allowed to violate that rule
inner pecan
yes ive heard that
my previous code was using .format
and someone told me to get rid of it
that was for something else though
harsh pulsar
what you are NEVER supposed to do is, use f-strings or .format for inputting data
for column and table names, you dont have a choice
if youre using psycopg2 they have some facilities to escape table names
but otherwise you have to do it manually
harsh pulsar
no dont
inner pecan
ok
harsh pulsar
its for postgres
this only works because: 1) you have full control over the data, and 2) none of the column names need to be escaped/quoted
!e
comparison_colnames = ['56', '57', '58', '59', '60']
query = '''
SELECT a.symbol, {0}
FROM table1 as a JOIN table2 as b USING (symbol)
WHERE {1}
'''.format(
', '.join(comparison_colnames),
' OR '.join(f'a.{colname} <> b.{colname}' for colname in comparison_colnames)
)
print( query )
delicate fieldBOT
@harsh pulsar Your eval job has completed.
001 | SELECT a.symbol, 56, 57, 58, 59, 60
002 | FROM table1 as a JOIN table2 as b USING (symbol)
003 | WHERE a.56 <> b.56 OR a.57 <> b.57 OR a.58 <> b.58 OR a.59 <> b.59 OR a.60 <> b.60
oh i see
thats quite clever
thats very helpful too, because i need to do this for many tables where the column field names aren't always the same so generating the WHERE clause dynamically is good
inner pecan
ok i will try this, thanks a lot. it was surprisingly much harder than i realised to do what I want here and compare two tables by value, google brought up just when tables are missing rows that the other one has
not many questions on checking for value differences
inner pecan
pandas.io.sql.DatabaseError: Execution failed on sql '
SELECT a.symbol, 56, 57, 58, 59, 60, 61, 63, 64, 65, 66, 67, 68, 69, 70, 71, 72, 73, 74, 112, 113, 114, 115, 116, 119, 120, 516, 652, 653, 654, 655, 657, 658, 659, 1359, 259, 260, 293, 301, 398, 793, 1478, 1604, 1610
FROM 'cg1-ny4-l1_Table0_20190816' as a JOIN 'cg1-ny4-l2_Table0_20190816' as b USING (Symbol)
WHERE a.56 <> b.56 OR a.57 <> b.57 OR a.58 <> b.58 OR a.59 <> b.59 OR a.60 <> b.60 OR a.61 <> b.61 OR a.63 <> b.63 OR a.64 <> b.64 OR a.65 <> b.65 OR a.66 <> b.66 OR a.67 <> b.67 OR a.68 <> b.68 OR a.69 <> b.69 OR a.70 <> b.70 OR a.71 <> b.71 OR a.72 <> b.72 OR a.73 <> b.73 OR a.74 <> b.74 OR a.112 <> b.112 OR a.113 <> b.113 OR a.114 <> b.114 OR a.115 <> b.115 OR a.116 <> b.116 OR a.119 <> b.119 OR a.120 <> b.120 OR a.516 <> b.516 OR a.652 <> b.652 OR a.653 <> b.653 OR a.654 <> b.654 OR a.655 <> b.655 OR a.657 <> b.657 OR a.658 <> b.658 OR a.659 <> b.659 OR a.1359 <> b.1359 OR a.259 <> b.259 OR a.260 <> b.260 OR a.293 <> b.293 OR a.301 <> b.301 OR a.398 <> b.398 OR a.793 <> b.793 OR a.1478 <> b.1478 OR a.1604 <> b.1604 OR a.1610 <> b.1610
': near ".56": syntax error
Do you know what my error is here?
looks like something to do with my a placeholder in the statement, but i dont see how as its the correct table name
oops actually 1 mistake
nvm my typo wasn't the cause
@harsh pulsar
harsh pulsar
@inner pecan are those actually column names in a database
cause you need to quote them
inner pecan
Yeah the columns are numbers
(I will be renaming them to actual names instead of numbers at some point)
harsh pulsar
comparison_colnames = ['56', '57', '58', '59', '60']
query = '''
SELECT a.symbol, {0}
FROM table1 as a JOIN table2 as b USING (symbol)
WHERE {1}
'''.format(
', '.join(f'"{colname}"' for colname in comparison_colnames),
' OR '.join(f'a."{colname}" <> b."{colname}"' for colname in comparison_colnames)
)
print( query )
this is precisely why its a last resort
inner pecan
Thanks.
No more error
I'm not sure this does exactly as I want though
output gives the columns as values
Symbol 56 57 58 59 60 61 63 64 65 66 67 68 69 70 71 72 73 74 112 113 114 115 116 119 120 516 652 653 654 655 657 658 659 1359 259 260 293 301 398 793 1478 1604 1610
AAC. 56 57 58 59 60 61 63 64 65 66 67 68 69 70 71 72 73 74 112 113 114 115 116 119 120 516 652 653 654 655 657 658 659 1359 259 260 293 301 398 793 1478 1604 1610
AAU. 56 57 58 59 60 61 63 64 65 66 67 68 69 70 71 72 73 74 112 113 114 115 116 119 120 516 652 653 654 655 657 658 659 1359 259 260 293 301 398 793 1478 1604 1610
AB. 56 57 58 59 60 61 63 64 65 66 67 68 69 70 71 72 73 74 112 113 114 115 116 119 120 516 652 653 654 655 657 658 659 1359 259 260 293 301 398 793 1478 1604 1610
ABBV. 56 57 58 59 60 61 63 64 65 66 67 68 69 70 71 72 73 74 112 113 114 115 116 119 120 516 652 653 654 655 657 658 659 1359 259 260 293 301 398 793 1478 1604 1610```its giving me a list of the ones with differences though
but not what they are
ah wait..
harsh pulsar
@inner pecan usually you can get the column names off the cursor
So you can zip a row w/ the column names to get a dict
inner pecan
SELECT a.symbol, "56", "57", "58", "59", "60", "61", "63", "64", "65", "66", "67", "68", "69", "70", "71", "72", "73", "74", "112", "113", "114", "115", "116", "119", "120", "516", "652", "653", "654", "655", "657", "658", "659", "1359", "259", "260", "293", "301", "398", "793", "1478", "1604", "1610"
FROM 'cg1-ny4-l1_Table0_20190816' as a JOIN 'cg1-ny4-l2_Table0_20190816' as b USING (Symbol)
WHERE a."56" <> b."56" OR a."57" <> b."57" OR a."58" <> b."58" OR a."59" <> b."59" OR a."60" <> b."60" OR a."61" <> b."61" OR a."63" <> b."63" OR a."64" <> b."64" OR a."65" <> b."65" OR a."66" <> b."66" OR a."67" <> b."67" OR a."68" <> b."68" OR a."69" <> b."69" OR a."70" <> b."70" OR a."71" <> b."71" OR a."72" <> b."72" OR a."73" <> b."73" OR a."74" <> b."74" OR a."112" <> b."112" OR a."113" <> b."113" OR a."114" <> b."114" OR a."115" <> b."115" OR a."116" <> b."116" OR a."119" <> b."119" OR a."120" <> b."120" OR a."516" <> b."516" OR a."652" <> b."652" OR a."653" <> b."653" OR a."654" <> b."654" OR a."655" <> b."655" OR a."657" <> b."657" OR a."658" <> b."658" OR a."659" <> b."659" OR a."1359" <> b."1359" OR a."259" <> b."259" OR a."260" <> b."260" OR a."293" <> b."293" OR a."301" <> b."301" OR a."398" <> b."398" OR a."793" <> b."793" OR a."1478" <> b."1478" OR a."1604" <> b."1604" OR a."1610" <> b."1610"
should the column names be in quotes
as getting this error now:
': ambiguous column name: 56
SELECT a.symbol, "56", "57", "58", "59", "60", "61", "63", "64", "65", "66", "67", "68", "69", "70" -< should this be unquoted?
inner pecan
oh I see
harsh pulsar
Stick "a." in the format string for the select part
comparison_colnames = ['56', '57', '58', '59', '60']
query = '''
SELECT a.symbol, {0}
FROM table1 as a JOIN table2 as b USING (symbol)
WHERE {1}
'''.format(
', '.join(f'a."{colname}"' for colname in comparison_colnames),
' OR '.join(f'a."{colname}" <> b."{colname}"' for colname in comparison_colnames)
)
print( query )
ok
so this is close
i think i can (hopefully) work out the rest from here
this gives me the table1 values but not table2
but gives me the ones with differences
e.g
Symbol 56 57 58 59 60 61 63 64 65 66 67 68 69 70 71 72 73 74 112 113 114 115 116 119 120 516 652 653 654 655 657 658 659 1359 259 260 293 301 398 793 1478 1604 1610
AAC. 0.5931 N 0.6052 N 2019-08-15 0.6189 4 20:04:03 2019-08-15 N 1436268 4 2019-08-15 776103.965501 1 2019-08-14 0.67 0.81 0.81 0.66 -0.1401 -17.29 746.305300 0.67 0.6701 2019-08-14 2019-08-14 296378.383948 422163 2019-08-14 16:03:20.556 USD AAC 1 100 100 100
so i need to add the b.columns
in my statement
in the select from
smoky radish
hey i have a problem with flask_sqlalchemy, not sure if i should ask here or in #web-development so tell me if i should move this.
im trying to catch this error i get sqlalchemy.exc.IntegrityError: (sqlite3.IntegrityError) UNIQUE constraint failed but i can't seem to find the exception object.
sqlalchemy.exc.IntegrityError doesn't exist, db.exc.IntegrityError doesn't exist either, db is my name for the SQLAlchemy object by flask_sqlalchemy.
i also couldn't find a flask_sqlalchemy import for exceptions
inner pecan
i think ive got my issue sorted for now. thx for the help @harsh pulsar
smoky radish
addition to my problem: after deciding to temporarily catch all exceptions on the problematic sqlalchemy query, the same error shows up on a later query, although ive called flush() on the session
graceful nimbus
accountiden = input()
query = ("SELECT * FROM accounts WHERE accountid = %s")
cursor.execute(query, accountiden)
result = cursor.fetchall()
for i in result:
print(i)
Any clue why I'm getting this error? I think the query is correct and the row accountid excists
smoky radish
is accountid supposed to be a integer? if so make sure you pass a integer to cursor.execute
graceful nimbus
using mysql.connecter
And I also tried setting accountiden to an int
And gave me the same error
graceful nimbus
Oh, why is that?
graceful nimbus
Makes sense, didn't know that, it fixed it
Thanks
So even if I give 2 values, it still needs to end with ,
smoky radish
i suggest you read up on python Tuple datatype 🙂
smoky radish
reminder my problem is still open: https://discordapp.com/channels/267624335836053506/342318764227821568/614941554079170563
zenith dragon
Yanking relevant lines from some tests in SQLAlchemy...
exception = tsa.exc.IntegrityError("foo", {"x": "y"}, None)
Is there some free hosting for Postgresql? Easy like mongodb atlas?
void otter
I know heroku gives you free postgres database
harsh pulsar
@smoky radish at that point you might just want to go look in the source code
@smoky radish it also might inherit from a base exception class that you can catch
torn sphinx
This MongoDB deployment does not support retryable writes. Please add retryWrites=false to your connection string.
I get this when I am trying to run my bot and use my command.
It's hosted on Heroku
Ping me if someone responds
It looks like when I try to write stuff in the document it errors out
torn sphinx
It's a link
Not a connection string
self.client = mcl("mongodb://:@ds311538.mlab.com:11538/fuedal")
I took the credentials out because uh..
pure cypress
mongodb://:@ds311538.mlab.com:11538/fuedal?retryWrites=false
gleaming frost
Can anyone explain why asyncpg is so slow on windows, but really fast on Linux?
harsh pulsar
does aiopg have similar slowdown?
harsh pulsar
try aiopg
i doubt it's the library
more likely it's the event loop implementation + whatever low-level i/o stuff is happening
possibly likely also postgres itself, if you're running postgres on windows vs on linux
gleaming frost
Postgres might be the issue. Good point