yes
#databases
naive sandal
try it out
arctic acorn
ok
its giving an indent error
db = await aiosqlite.connect(botdev.sqlite)
await db.execute('REPLACE INTO DBFORJOINANDLEAVE (guild_id, welcome message) VALUES (?,?)', (ctx.guild.id, welcmsg))
await db.commit()
its giving a syntax error here
lemme send it
File "C:/Users/blufl/Desktop/discord.pyxys/botdev.py", line 10
db = await aiosqlite.connect(botdev.sqlite)
^
SyntaxError: 'await' outside function```@naive sandal
naive sandal
Hmm check out the documentation for the .connect method
arctic acorn
hm
lemme check it out
this db stuff is cofusing af lmao
conn = sqlite3.connect('example.db')
apparantly this is how its used for sqlite3
@naive sandal
naive sandal
What about aiosqlite though?
pure cypress
The error clearly states you cannot await outside a function
So... Move the code into an "async def"
arctic acorn
so what the code for C:\Users\blufl\PycharmProjects\untitled1\venv\Scripts\python.exe C:/Users/blufl/Desktop/discord.pyxys/botdev.py File "C:/Users/blufl/Desktop/discord.pyxys/botdev.py", line 10 db = await aiosqlite.connect(botdev.sqlite) ^ SyntaxError: 'await' outside function
?
wait no
i meant to ask
what shud i make the async def function
sorry in advance if these qns are veri basic
im very new at this
pure cypress
Do you not have any existing ones?
pure cypress
Async defs
arctic acorn
this is my first ever database
yes
i do
shud i put this under on_ready
let me try that
pure cypress
Maybe
You should know that on ready may be called multiple times
It can happen if the bot reconnects
So make sure that calling multiple times won't be a problem if you put it there
arctic acorn
yes
it can
async def on_ready():
db = await aiosqlite.connect(botdev.sqlite)
await db.execute('REPLACE INTO DBFORJOINANDLEAVE (guild_id, welcome message) VALUES (?,?)', (ctx.guild.id, welcmsg))
await db.commit()
print("The bot is ready to function.")```thats the code then
if i put it under on_ready
pure cypress
Where do you plan to get the ctx and welcome message
pure cypress
Those currently aren't defined
arctic acorn
thats what im making the db for
pure cypress
So your code won't work
arctic acorn
meaning?
pure cypress
You have undefined variables
arctic acorn
what is unddefined?
arctic acorn
oh god
naive sandal
You need to get those from the user
arctic acorn
i overlooked that
You need to get those from the user
@naive sandal eys
how do i get those from the user tho?
naive sandal
From a command
arctic acorn
ok
so something like
og
async def msg_leave(ctx, *, expression):```does something like that work
@naive sandal
arctic acorn
yes
naive sandal
Now execute the SQL queries to insert the data into the database and commit it
arctic acorn
ok let me see if i do it right
Traceback (most recent call last):
File "C:\Users\blufl\PycharmProjects\untitled1\venv\lib\site-packages\discord\client.py", line 312, in _run_event
await coro(*args, **kwargs)
File "C:/Users/blufl/Desktop/discord.pyxys/botdev.py", line 12, in on_ready
db = await aiosqlite.connect(botdev.sqlite)
NameError: name 'botdev' is not defined
uhm
im confused
even tho botdev.sqlite
is my file name
this is so confusing
i cant find anything to go off of online as well
shud i just like use normal sqlite3'
@hazy mango
lilac bane
how did you define botdev?
hazy mango
NameError: name 'botdev' is not defined
Error seems pretty self-explanatory to me 🤷♀️
naive sandal
It's supposed to be a string
arctic acorn
I'm not sure why thas the case
Wait
Fuck
My qoutes
I forgot they broke
Traceback (most recent call last):
File "C:\Users\blufl\PycharmProjects\untitled1\venv\lib\site-packages\discord\client.py", line 312, in _run_event
await coro(*args, **kwargs)
TypeError: on_ready() missing 1 required positional argument: 'ctx'```new error
ill send code rn
async def on_ready(ctx):
db = await aiosqlite.connect('botdev.sqlite')
await db.execute('REPLACE INTO DBFORJOINANDLEAVE (guild_id, welcome message) VALUES (?,?)', (ctx.guild.id, welcmsg))
await db.commit()
print("The bot is ready to function.")
on_ready is an event
arctic acorn
yes
arctic acorn
oh right
hazy mango
You can't just randomly add parameters to events
delicate fieldBOT
arctic acorn
k
wait
this will come in async def ()
right?
im just gonna give up on this for rn
naive sandal
@arctic acorn you need to understand how Object Oriented programming works before starting to write more complex pieces of code.
arctic acorn
yes
torn sphinx
@arctic acorn If you have the guild id, you can use bot.get_guild instead of ctx.guild and pass in the id. Also, instead of REPLACE INTO DBFORJOINANDLEAVE, you can use UPDATE DBFORJOINANDLEAVE SET guild_id = ? and welcome_message = ? Then just pass your tuple after that. You may also want to add WHERE in there if you have multiple records in your table.
I'm only experienced with regular sqlite3 but this seems to be an asyncio version of it so the code should still be the same besides the use of await. Please let me know if I'm wrong :)
arctic acorn
yes ur mostly right
and i just gonna switch to basic sqlite3
aio is just confusionm
and i cant find any good info on aio
so like
ye
left scaffold
hey, i have a d.py bot that i want to make a levelling system for however 1 idk whether to use sqlite or postgureSQL or mySQL and i have also never done dbs before so i want to learn howto. can anybody help me?
ping me pls
wide shuttle
yield from cursor.execute("SELECT * FROM t1 WHERE id=%s", (5,))
If anyone knows, is this query protected from injection, etc? I'm using aiomysql
brazen charm
@wide shuttle it should do
wide shuttle
Thanks
brazen charm
@torn sphinx i mean theyre two very different databases, one is a single file. The other is a server try database (Even tho use postgre over mysql)
proven arrow
@left scaffold You can use whichever you want, as they will all work. Most important thing here is to make sure you use an async library.
In terms of which is better? Postgres is good for large amount of transactions, or for large (and small) bots, and so will be good for future proofing if the bot grows. Although the other two should work fine for small bots.
The most important as I said is to use an async driver for the DB.
MySQL- aiomysql
SQLite3 - aiosqlite
Postgres - asyncpg
left scaffold
@left scaffold You can use whichever you want, as they will all work. Most important thing here is to make sure you use an async library.
In terms of which is better? Postgres is good for large amount of transactions, or for large (and small) bots, and so will be good for future proofing if the bot grows. Although the other two should work fine for small bots.
@proven arrow thanks I will use postgressql but are there any useful guides to help me?
proven arrow
Are you familiar with SQL?
left scaffold
nope
proven arrow
Oh well, here’s a good guide for learning sql. https://sqlbolt.com/
They all use kind of the same syntax, so once you familiarise yourself with that then you can go ahead with using Postgres. And then maybe look up some other tutorials on internet about installing Postgres and setting it up.
But here is the asyncpg documentation, once your done with that: https://magicstack.github.io/asyncpg/current/
torn sphinx
i prefer sqlite over any other cause its already preinstalled and it on python docs no need to go for another documentation
left scaffold
@proven arrow thanks
@torn sphinx yes i would use sqlite however my bot is growing fast so i need postgreges
torn sphinx
I just installed PgAdmin 4 and it's telling me; Installing a system tray plugin will prevent this window from being shown. Where can I install it (I'm on linux)
torn sphinx
@left scaffold no problem thats why they are many databse out there
left scaffold
Thanks I have found a good video to help me with postgres so I'll look thanks
left scaffold
Ah yes
minor zealot
hey guys i try to make a database sqlite3 in discord module but it shows me this err so help me pls if u can
solar gale
i think i want to move into backend dev. what do you guys think is a good choice of client-server RDMS to figure out how to set up first?
high bone
Is it a bad idea to dynamically add columns in a Postgresql database? Like, on demand? I'll often need to add new columns to already-existing tables and make completely new tables dynamically as the application needs more features, is it viable or should I pre-think a futureproof table for every aspect of the application?
torn sphinx
@rich trout thanks
shadow lion
Is MySQL workbench good to use with MariaDB?
toxic apex
I'm making a d.py bot and I'm looking for database. Has anyone used MongoDB for this? I got a recommendation to check it out. What do you all think? Please ping me in replies.
wanton storm
Hey guys I need help to solve this issue. Let's say I have a table that looks like this
John|Fixing|hammer|7/20/2020 11:00:00|7/20/2020 14:00:00
Mary|Fixing|screwD|7/20/2020 10:00:00|7/20/2020 15:00:00
Peter|Fixing|drill|7/20/2020 9:00:00|7/20/2020 12:00:00
John|cleaning|broom|7/20/2020 14:00:00|7/20/2020 17:00:00
Peter|cleaning|wipes|7/20/2020 12:00:00|7/20/2020 14:00:00
Mary|cleaning|duster|7/20/2020 15:00:00|7/20/2020 20:00:00``` and so on for a very large data set. I want to find out if there are clusters of tools in the data. I.e if there is a higher chance that someone who fixed with a hammer would clean with a broom and if someone who fixed with a drill would be more likely to clean with wipes later. The output of this would be groups of tools that are likely to be chosen on the same routing of activities. Like: ```Activities|Tool
fixing | drill
cleanning|wipes
cooking| pan ``` for each cluster of tools. Is something like this possible if so how? Thanks!What system are you working in? GROUP BY and HAVING @wanton storm
wanton storm
What system are you working in? GROUP BY and HAVING
@toxic apex The main data base is in SQL
toxic apex
Maybe COUNT too depending on what you're looking to track
wanton storm
Maybe COUNT too depending on what you're looking to track
@toxic apex I think count would go over the whole table and I want to see per activity what tool is used the next activity
reef crag
I think I have a simple one here for all of you experts. I have a massive major league baseball database, I want to know how to code/query the records of each team and over 60-game slices of results . MLB has a 60-game schedule. I can handle the sorting to see what each team's max percentage has been at any point in a saeason over 20 seasons. but how do I slice the data to show 60-game results? To help, use parameters: date, game number, margin. margin<0 is a Loss since the teams score dfewer runs than the popponent and margin>0 is a win.
reef crag
how long does it normally take to get any hewlp?
rich trout
Depends on the problem, how you ask, and how many people are around
but for you, you'll probably want to look into GROUP BY, and floor()ing row_number to make groups of appropriate size
This (https://stackoverflow.com/questions/7068235/group-by-every-n-records-in-t-sql) is for T-SQL, so the syntax for your database is probably different, but it shows the general idea
reef crag
ok great
so, if I use group by Python code and the parameter game number, how would you code it to be a rolling game-by game listing?
here is listing of one team and one season. so, when you see game number 60, the total number of wins and win percentage shown are what I want. but, how do I chop off 'append' game number one an dmake the range from game number 2 to 61? and then it rolls GN 3,62
really appreciated @rich trout
rich trout
group_by is SQL
reef crag
so range is 60-games and returns data between game numbers 1 and 60 and all the way to game 103 to 162
rich trout
For sqlite, something like
SELECT * FROM games GROUP_BY cast((row_number/60) as int)
will get you groups of games by 60.. maybe
there's probably an off-by-one in there
reef crag
so in Python I can use cast ?
rich trout
That's not python, its SQL
you run sql like this, usually
database = sqlite3.connect("mydb.db")
cur = database.cursor()
cur.execute("SELECT * FROM games")
print(cur.fetchall())
are you using something like pandas?
reef crag
yeh, but I have a Python DB unfortunately
torn sphinx
uhm... i got a problem i just cant solve...
i just wanted to do a command for example:
"!punch" and the bot should say "{user.name} u got punched by {message.author.name}" or something like that with an attached gif, but i couldnt figure out how to get the link from a json file, someone recommended me to use a database or just a txt, but i just cant figure out how to get a random gif out of one column from sqlite...
long story short: How can i get a link from one column in my table to send in a message?
harsh pulsar
are you storing the images as binary data in the database?
or are you storing the filenames as text?
@torn sphinx
harsh pulsar
looks like you need to download the image from that url
not sure if imgur has an API or they allow direct image downloads
if i remember right, the direct download URL is like i.imgur.com/a/<code>
torn sphinx
oh- i got them already, i uploaded them to imgur
torn sphinx
cuz someone said links would be easier
harsh pulsar
sure, if you're using discord
torn sphinx
ye
harsh pulsar
in that case just embed the image from the link
this sounds like a #discord-bots question now
torn sphinx
okay
harsh pulsar
the question being "how do i embed an image from an imgur link"
torn sphinx
thank chuu
lavish ferry
Can I use Oracle Cloud Database and use postgresql/pgAdmin4?
brazen charm
im probably gonna say no
looking at their site they offer NoSQL or MySQL versions tho they are very unclear about what it is
tho it could be its just Oracle SQL which is a diffrent system entirely
worthy pawn
Hi with MySQL how can u update a value In python code?
brazen charm
UPDATE <table_name> SET column=value, other_column=value2 WHERE constraint1=value
torn sphinx
Okay so, whenever I wanna use my sqlite3 database. I get the error "database is locked". How do I "unlock" it?
rich trout
check to make sure you don't have it open somewhere else
torn sphinx
Wait so
rich trout
if you're running a discord bot multiple copies open will do that, or having a db editor open while trying to use it
rich trout
hm
torn sphinx
So wait
I can't have it open in like DB Broswer for SQLite?
Because when I closed that, it worked.
rich trout
yes
torn sphinx
Alright, now it works.
Thank you.
Also, 1 more thing
How do I search for a specific data (through the python script) and print it out?
Like if I add an ID to every "person" that I add in there. How can I search for that specific ID through my script?
torn sphinx
I'm new brother, you gon' have to explain that.
rich trout
Well, the way to insert data into a database is with an INSERT query
torn sphinx
Yes.
rich trout
INSERT INTO tablename VALUES (data, data, data)
the way you get it out is with SELECt
SELECT (columnname, columnname) FROM tablename WHERE condition
condition can be id = 1, for example
rich trout
means "get all columns of rows that match"
torn sphinx
SELECT (id) FROM info1 (my table) WHERE id=VariableHere
?
Or is it easier just to do *?
rich trout
Well, that query won't give yo uanything you don't have
because select id will return the id, from the rows that match that id
rich trout
so, if there is a row with that it'll just return, say 1, if you asked for id = 1
torn sphinx
It will give me an error?
rich trout
it will return an empty result set
rich trout
basically, every time you do SELECT, it gets a list of matching rows
you can fetch them from the cursor you're using with sqlite with fetchall()
so,
rich trout
!e ```py
import sqlite3
db = sqlite3.connect(":memory:")
c = db.cursor()
c.execute("CREATE TABLE test_table (id INTEGER PRIMARY KEY, name TEXT)")
c.execute("INSERT INTO test_table VALUES (null, 'Bob')")
c.execute("SELECT name FROM test_table WHERE id = 1")
print(c.fetchall())
@rich trout :white_check_mark: Your eval job has completed with return code 0.
[('Bob',)]
If you provide an ID that matches nothing, it returns [], or an empty list
rich trout
if you request more columns, they show up in the list as longer tuples (eg: (1, 'Bob', '5 feet tall'))
torn sphinx
Over to question number 3
How do I use a variable to search for the ID?
Like an input variable?
rich trout
Yes, they're called parameters
you replace the variable with a ? in the execute statement
so, "SELECT name FROM test_table WHERE id = ?"
torn sphinx
Oh right, the same way I use insert.
rich trout
well, a tuple works too but that can have mistakes. Since (1) is just 1, and (1,) is (1,)
yes
rich trout
.fetchall() from the cursor
torn sphinx
Let me try it.
@rich trout 'str' object has no attribute 'fetchall'
Got that as an error
rich trout
What's your code look like
you can scroll up to see my example code too, which should help narrow down the issue too
torn sphinx
import sqlite3
conn = sqlite3.connect('C:/pythonstuff/database.db')
cur = conn.cursor()
a = input("First name: ")
b = input("Last name: ")
c = input("Age: ")
d = input("ID: ")
e = input("E-Mail: ")
cur.execute("""CREATE TABLE IF NOT EXISTS info1 (
first_name DATATYPE,
last_name DATATYPE,
age DATATYPE,
id DATATYPE,
email DATATYPE
)""")
info2 = [a, b, c, d, e]
cur.execute("INSERT INTO info1 (first_name, last_name, age, id, email) values (?, ?, ?, ?, ?)",
(info2))
id = input("ID: ")
cur.execute("SELECT id from info1 WHERE id = ?", [id])
print(c.fetchall())
conn.commit()
That's everything
Oh wait
Let me edit it
There it is
rich trout
c vs cur on that line
torn sphinx
Yea.
Now I found another thing
It prints the id back
I want it to print everything about the "person" that owns the id.
First name, last name, age, id & mail.
rich trout
Well, you're SELECT statement is only requesting id
if you want everything, ask for everything with *
torn sphinx
One last thing
This is the output: [(123, 123, 123, 1, '123@123.se'), ('okay', '', 'okay', 1, 2)]
Which is right
But I want it to output like this:
First name: 123 Last name: 123 Age: 123 ID: 1 E-Mail: 123@123.se
Or something in that style
I gtg
Just uhhh, if you can spend that time. DM me it.
Thanks for the help, have a good night.
rich trout
No worries
@torn sphinx you'll want to format it like anything else in python. But I'll give you some examples for pretty unpacking and formatting:
for row in cursor: # No need for fetchall if you're looping over it
id, name, age = row # unpack each item of the tuple into a variable
print(f"ID: {id: >3}, {name: >10} {age: >3} years old")
the {: > 10} syntax is called a "format specifier", and is extra, so you could just do f"Age: {age}" and it would replace {age} with the actual value of the age variable. In this case, the blank space before the > means "pad with space", > means "align the text right", and the number is the amount of columns to use
tired granite
I'm about to start learning how Python interacts with a database. I don't have a particular project in mind, MySQL and SQLAlchemy appear to be the most used/best documented options I've seen.
Any recommendations on which to use? Any drawbacks to one vs the other? Any general advice? I'm all ears.
brazen charm
@tired granite start with sqlite
It's inbuilt with python and is a single file rather than a entire server
harsh pulsar
@tired granite i recommend postgres personally, i think its a little more user friendly and has much better documentation
also imo it's better to learn with plain sql code, no orm like sqlalchemy
and you would typically use a python library called psycopg2 to communicate between python and the postgresql server
gloomy pike
in sql, for columns of type bit , do you have to insert 0 or 1 , or can you insert booleans?
like can i do
await db.execute(f"Update Logging set {column} = ? where GuildID = ?", (True, ctx.guild.id))``` where {column} is a column of type bitGood question, try it in a test database
rancid forge
could anyone help me set up sqlite?
when i download sqlite i cant find the .exe to import into my code
barren fiber
What is the best way to check if a column is already present in a database using SQLAlchemy? (nvm, decided to just check if .rowcount is greater than 0)
rich trout
You don't need to download sqlite for python
it comes with it
@barren fiber you can also use sqlalchemy's META object to inspect all the existing tables
With metadata.tables being autopopulated with a reflection load
strong fable
def tweet_storage_procedure(twitter_id, tweet_id, tweet_creation_time, tag_count):
cursor = mydb.cursor()
cursor.execute("SELECT tweet_creation_time FROM tweet_data WHERE tweet_id = " + tweet_id)
result = cursor.fetchall()
print(type(twitter_id))
print(type(tweet_id))
print(type(tweet_creation_time))
print(type(tag_count))
if len(result) == 0:
cursor = mydb.cursor()
cursor.execute("INSERT INTO tweet_data VALUES(" + tweet_id + "," + tweet_creation_time + "," + twitter_id + "," + str(tag_count) + ")")
mydb.commit()
print("Inserted!")
return
print('Duplicate Id! Not inserted')
this is my code
I am facing issues after the if statement
it isnt printing inserted
nor throwing any invalid syntax errors
the result fetch'd is 0
i tested that. and it goes into the if statement too
probably something wrong with the cursor.execute, but I am not sure.
rich trout
👍
brazen charm
@gloomy pike Depends what SQL it is, postgre will accept a range of values for bools
like yes-no, true-false, 1-0 etc....
toxic apex
I'm making a d.py bot and I'm looking for database. Has anyone used MongoDB for this? I got a recommendation to check it out. What do you all think? Please ping me in replies.
sudden basalt
hey i just read that postgresql has problems running on windows, is that true, did anyone experience something like that. I'm asking coz i'm about to set up my django/postgrsql project
hallow cloud
not really, a few windows specific things you have to setup, but that applies for every operating system
brazen charm
@sudden basalt Literally never had a issue running postgre on windows
@toxic apex I use mongo but its async driver is pretty bad overall
i'd say something like asyncpg and postgre would be better if you plan on it scaling
toxic apex
How does the async driver cause problems?
brazen charm
its a really weird system that was built for Python 2 even tho it could of switched to python3
so everything it does is awkard to setup and use
and slow
toxic apex
I'm not sure I understand the async side of it. Can it run concurrent queries, like find()s?
brazen charm
it has to be async to not block the event loop and stop the bot running
otherwise it blocks and stops the bot
theyre not really 'concurrent' in mongo's case
but they just dont block the event loop
toxic apex
I am able to run queries now and it has a pretty quick response time with a small dataset. I'm only going to see the issues with more users?
brazen charm
pymongo will have an effect even at small stuff
but it wont me noticable
at bigger and high rate of queries tho it will slow your bot right down
toxic apex
Ok, thanks for the advice!
rich plume
hey i just read that postgresql has problems running on windows, is that true, did anyone experience something like that. I'm asking coz i'm about to set up my django/postgrsql project
@sudden basalt I had the problem installing on Windows Home
finite lynx
Could someone help me understand, how to recognize n+1?
tepid cradle
could you elaborate a bit, @finite lynx
finite lynx
Like, I have heard that when querying bigger datasets with relations there is often the n+1 problem, but I don't quite fully understand how it works. @tepid cradle
reef crag
looking for help using python code to take 60-row slices of a database
stable violet
add a LIMIT 60 to get just the first 60 instances of your query
hasty juniper
if cur.execute(f"SELECT UserID FROM users WHERE UserID = {member.id}").fetchone() is None:
cur.execute('INSERT INTO users () VALUES (?, ?, ?, ?)', (member, member.id, 0, 0)
```help plsit must work
i think
summer bridge
just watched the sqlite3 tutorial and i got a question
how do i assign(if possible) multiple items to same person
for example if there is like
discord id, and after that ill put like stuff that discord id has
example id one has thing 1 and thing 2
but id two has only thing 1
how would that work
hallow cloud
you model it with a relation (all relation databases have these)
each id has several "things"
you model it with a one-to-many relation
lucid needle
i dont know how to use sqlite3 well xd
harsh pulsar
@hasty juniper don't use an f-string for the member id, use a ? placeholder like in your insert query on the 2nd line
harsh pulsar
also @hasty juniper if you set a primary key or unique constraint on UserID then you can just do insert into or ignore
finite lynx
Could someone provide some documenation on effective database queries?
runic pilot
here's a good guide https://use-the-index-luke.com/
finite lynx
Aleight thank you, will look into it
hallow mango
finite lynx
Btw, would learning writing in pure SQL be also a good practice for a deeper understanding of queries and things like that?@runic pilot
runic pilot
yes, that would help
torn sphinx
anyone know what to do when your query result keeps pausing?
because it's too big
in oracle sql
keeps crashing lol
proven arrow
@somber isle Really depends on your usage and the kind of application. But postgres probably has the best async driver which is asyncpg
somber isle
im using a discord bot so it will have async calls to the database @proven arrow can postgres be used on a cloud based vm like heroku?
because the whole cloud vm is accessed through a terminal
unless its web based like mongodb which would be great too
proven arrow
Well postgres/asynpg are actually recommended for bot dev because of how good the async driver is for it. Heroku does support postgres by the looks of it since their website shows this, but I have never used heroku. But just remember heroku is not made for bots, and is not recommended, although you may get away with it if the bot is small. There are many reasons why and here are some: https://discordapp.com/channels/267624335836053506/343944376055103488/727927331791962192
somber isle
would you recommend any other cloud based vms for running a bot that is free or really cheap?
proven arrow
Google Cloud has a free tier actually as well, which is good for a free service, and since its a full VM you can kind of use any DB on it and have more control. But to use this you will need a bank card just to verify your account, but after that it is free as long as you stay within the limits.
Personally I use galaxy gate, which is cheap and pretty good.
somber isle
i have been using gcp but one time I tried connecting to my vm, it would take minutes and then time out and stopped working idk if i did something wrong lol
proven arrow
Ah yes I used to have a similar problem as well, and my SSH keys would stop working too. I have no idea why but a simple restart from their web console seemed to fix the issue temporarily. And yes https://galaxygate.net/
somber isle
so for a bot i would just want a virtual server? @proven arrow
proven arrow
somber isle
ty ty
one more question, do you think 1 cpu and 1gb ram would be enough for a bot on multiple discord guilds @proven arrow
proven arrow
Yes thats plenty, more than enough.
proven arrow
Yes
somber isle
ty
torn sphinx
does anyone know how i can speed this up?
i have a list of ids where i need certain values and the cases for each id is different
SELECT count(case when x), count(case when y), count(case when z)
FROM table inner join table2 inner join table3
WHERE id = ['insert id here']
would creating a temporary table of all the joins then referring to that temporary table speed things up?
clear reef
Here I want to display all the warnings that are in the database in the same embed. This only shows the most previous one.
modest matrix
because you're only adding the field one time
you need to actually loop through the list of warnings
void frost
How can I store an offset-aware datetime.datetime object in a sqlite3 database and keep the offset-awareness when retrieving it?
clear reef
because you're only adding the field one time
you need to actually loop through the list of warnings
@modest matrix how though
modest matrix
loop through records, and add a field for each element
just a simple for loop
wanton storm
Hey guys I need help to solve this issue. Let's say I have a table that looks like this
John|Fixing|hammer|7/20/2020 11:00:00|7/20/2020 14:00:00
Mary|Fixing|screwD|7/20/2020 10:00:00|7/20/2020 15:00:00
Peter|Fixing|drill|7/20/2020 9:00:00|7/20/2020 12:00:00
John|cleaning|broom|7/20/2020 14:00:00|7/20/2020 17:00:00
Peter|cleaning|wipes|7/20/2020 12:00:00|7/20/2020 14:00:00
Mary|cleaning|duster|7/20/2020 15:00:00|7/20/2020 20:00:00```
and so on for a very large data set. I want to find out if there are clusters of tools in the data. I.e if there is a higher chance that someone who fixed with a hammer would clean with a broom and if someone who fixed with a drill would be more likely to clean with wipes later. I massaged the data a bit and got a list that has each trasnfer of tools in the routing of activities but I am not sure how to proceed from here. Would a pie chart showcase this data? maybe a network graph for each name and establish common routings that way? What I have right now looks something like this
```Transfer| Counts
(drill, wipes)| 2170
(wipes, pan) |1955```
any help is appreciatedcan you run sqlite on VM and how?
modest matrix
yes, it shouldn't really be any different than from a normal computer?
somber isle
how would you set it up?
the VM is all operated from a cmd
i only know how to install sqllite through a normal os
modest matrix
it comes with python tho, doesnt it
why would you install something
somber isle
i thought you needed to install the software or what not to actually store the db
i guess you dont?
modest matrix
nope
somber isle
ahh thank god
modest matrix
it's a module that comes with python
somber isle
so i just need to create a folder where all my databases will be stored
sqlite3 right?
somber isle
i need to use this though https://aiosqlite.omnilib.dev/en/latest/api.html#cursors
ruby magnet
Sorry for the noob question here but I’m learning python. I have 2 lists, 1 with strings (names of people), 1 with int (their game scores). How do I keep the values associated in finding the winner’s name and score?
somber isle
if you entered the data in correctly they should have the same position @ruby magnet
i.e score = scores[pos] and name = names[pos]
ruby magnet
Ok, I can find the max score in the int list but how do I know which index to tell the string list to print out?
somber isle
however, i recommend using a dict @ruby magnet
modest matrix
ah for a bot yes
somber isle
you will need to know the pos that the high score is in @ruby magnet
do you have an example of the lists?
i can show you
modest matrix
i mean it's just pip, so it's like, not hard
somber isle
yeah im making a discord bot and i need a db to use with it @modest matrix
ruby magnet
Sure @somber isle , one sec
modest matrix
it has install instructions too if you really need it
https://aiosqlite.omnilib.dev/en/latest/index.html#install
it's just pip 
nothing to be afraid of, same thing on windows
somber isle
i see, I just didnt know if it would create databases without needing an sql program @modest matrix
modest matrix
(aio)sqlite is the "sql program" so to speak
ruby magnet
while golfer_input.lower() != 'n':
golfer_name = input('Please enter golfer's name: ')
golfer_score = input('Please enter golfer's score: ')
golferNames.append(golfer_name)
scores.append(golfer_score)
golfersCount += 1
golfer_input = input("Do you have more golfers to enter? (y/n) ")
modest matrix
since it's just a file
ruby magnet
using that loop to generate the list
somber isle
tip: you cannot use the same quote marks without using a \ @ruby magnet
see what happens
now
ruby magnet
yeah, it has the , not sure why it didn't show
somber isle
it interepts the string ending there
ruby magnet
\
somber isle
ill show how to do the thing youre asking
ruby magnet
much much appreciated
somber isle
golferNames =[]
scores = []
golfersCount = 0
golfer_input = ''
while golfer_input.lower() != 'n':
golfer_name = input('Please enter golfer\'s name: ')
golfer_score = input('Please enter golfer\'s score: ')
golferNames.append(golfer_name)
scores.append(golfer_score)
golfersCount += 1
golfer_input = input("Do you have more golfers to enter? (y/n) ")
print(golferNames)
print(scores)```Please enter golfer's score: 0
Do you have more golfers to enter? (y/n) y
Please enter golfer's name: b
Please enter golfer's score: 1
Do you have more golfers to enter? (y/n) y
Please enter golfer's name: c
Please enter golfer's score: 3
Do you have more golfers to enter? (y/n) y
Please enter golfer's name: d
Please enter golfer's score: 4
Do you have more golfers to enter? (y/n) y
Please enter golfer's name: e
Please enter golfer's score: 5
Do you have more golfers to enter? (y/n) n
['a', 'b', 'c', 'd', 'e']
['0', '1', '3', '4', '5']```there is the console @ruby magnet
i used letters and numbers to represent names and scores
notice how the highest score is 5 which is position 5 of the list?
so you would go into the name list and get pos 5
burnt yew
what is the most efficient (space efficient too) way of storing a lot of data about objects and their attributes?
ruby magnet
right, I've been able to get this far. I'm trying to automate it so I don't have to see the lists to know manually which is highest. i.e print('The winner is: ',_______ (plus their score))
hopefully that makes sense
I figure I can use the max function with the score list but I don't know how to tell the names list which pos to print to go along with it
somber isle
im working on that part rn
names = ['a','b','c','d','e', 'f']
scores = [69, 72, 54, 33, 93, 84]
highScore = scores[0]
position = 0
for item in scores:
if item > highScore:
highScore = item
else:
position = position + 1
print(highScore)
print(position)``` @ruby magnetnow to get the name just do: python nameOfHighScorer = names[position]
ruby magnet
oh,4
somber isle
it just goes through each item in the list and compares it the last
ruby magnet
that's a cool way to approach it! I didn't think about running in a loop like that
thank you!
somber isle
np!
i do recommend using something like a dictionary though! unless this is a problem you were given where you had to use lists
ruby magnet
I haven't studied up on dictionary any yet. Sounds like a good one to look into next!
gritty pine
Hey is there a way with mysql connector to 'refresh' a connection to remove uncommited changes before adding new ones to the queue with .execute? My use case is i pass a single connection around to a bunch of functions but want to make sure that the commit within each function is only commiting changes made within that function (without having to generate fresh connections)
Ah maybe reset_connection...
runic pilot
make a transaction for each function and have each function commit, but reuse the connection to make transactions
gritty pine
The problem is i want to not have to rely on previous functions hving committed their changes, does making a new transaction flush the old one?
wraith relic
hi
pale jackal
Hello guys, I don't know if this is the right spot to ask my question, but can anybode explain me what "Patent use" means here?
delicate bluff
pale jackal
nvm. found an answer
long lantern
Hi, how can I check if a MySQL row exists in python? Basically I want to check if it exists and if it doesn't, create it and if it doesn't, update it.
proven arrow
@long lantern You can fetch the row and then check if it’s empty.
#execute query
query = cursor.execute(....)
#fetch row
row = cursor.fetchone()
# check if it is empty and print error
if not row:
#row not found so create new row
else:
#row exists so update
thanks m8 had someone help me in another channel, but if that doesn't work I'll try that
proven arrow
Ah ok 
merry mirage
Hey guys. In mongodb aggregation, say I have a price field.
How can I aggregate this to create another field that if the price is higher than $100, the new field would set to "expensive", otherwise "cheap"?
torn sphinx
I am working on MongoDB and I have set my IP address in it.
Now, if I am hosting a project on a hosting service, can it access my database or I have to add the hosting service IP as well?
warped glen
https://mystb.in/OaklandWatchConcern.python
Can anyone tell me if there's anything wrong in this or how I can condense/correct my code
Anyone can help me?
clear reef
using discord.py
Now I recently got a warning system where it saves warnings using SQLite
but now it doesnt save it locally. If you check the warnings on a different server, it shows the same warnings as the user has on other servers.
How can I make it server local and not server global?
it might be that i dont save guild ID's
pulsar stag
yes you will need to save guilds
and have a fk in your warning table that points to that guild
clear reef
yes you will need to save guilds
@pulsar stag"guild_id" INTEGER NOT NULLworks?
pulsar stag
i mean i guess? tho ideally that would be a fk to the guilds table
pulsar stag
idk whatever that screenshot is from
i just write my sql by hand
easier that way
clear reef
do you get this though
hybrid sonnet
is 256mb ram enough for a one server bot that just does simple commands and accesses a small db
hybrid sonnet
cubes.host
hybrid sonnet
my plan is to eventually upgrade to a pi but i'm tired of leaving my pc running the .py all the time lol
pulsar stag
ya that should be good
hybrid sonnet
yeah but honestly 5euro per year is more than fair I would say
pulsar stag
im at 1 gig on my ec2 instance and i dont come close to needing all that
hybrid sonnet
I saw that cubes.host said it came with database capability as well
clear reef
uhm so did i make a forgein key or not
clear reef
i mean
i think i already did make one
it does say "foreign key"
and that it refrences "warns" my table
pulsar stag
i meant a fk for your guild
lusty grail
Anyone has a clue why would psycopg2 insert only strings from the whole query
Dropping all numbers
Silently
While column names are correct, all placeholders are '%s' and values are actually integers and floats, not strings
Columns also have proper types
lusty grail
Yea
pulsar stag
im so used to sqlite rn lol
clear reef
i meant a fk for your guild
@pulsar stag ?
lusty grail
'anyway that shit makes no sense
Psycopg2 converts all numbers to NULL in query
Silently
clear reef
you have a guilds table
@pulsar stag ohhh do i need to make a table with the warns table?
pulsar stag
this is an example from my shitty small discord bot
CREATE TABLE IF NOT EXISTS Guilds (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL
);
CREATE TABLE IF NOT EXISTS Users (
id INTEGER PRIMARY KEY,
fk_guildId INTEGER NOT NULL,
name TEXT NOT NULL,
isBanned BOOLEAN DEFAULT false,
misc TEXT,
FOREIGN KEY(fk_guildId)
REFERENCES Guilds (id)
);
CREATE TABLE IF NOT EXISTS Users_Guilds (
fk_guildId INTEGER NOT NULL,
fk_userId INTEGER NOT NULL,
FOREIGN KEY(fk_userId)
REFERENCES Users (id),
FOREIGN KEY(fk_guildId)
REFERENCES Guilds (id)
);
CREATE TABLE IF NOT EXISTS Channels (
id INTEGER PRIMARY KEY,
fk_guildId INTEGER NOT NULL,
name TEXT NOT NULL,
isDeleted BOOLEAN DEFAULT false,
misc TEXT,
FOREIGN KEY(fk_guildId)
REFERENCES Guilds (id)
);```hmmm
ok so i made a table named Guilds
i made "id" and "name" just like you
id being primary key
then
i went to warns table
created a fiel called "fk_guildId"
integer, not null
and then set it as a fk
to the "Guilds" table
and then "id" within "Guilds"
yes?
pulsar stag
dont not null the primary key
clear reef
ym infraction id is primary in warns
o
ok
the foreign key isnt supposed to be NN
now i have this
pulsar stag
Did you set it as an fk to guilds
pulsar stag
Ok
Ya so now when you insert a warning you set the fk as the guild id
And can query from that
pulsar stag
I mean that’s a massive topic lol
pulsar stag
But select _ from _ where _=_
You need to also specify the guild id in the where
Where fk_guildid = whatever guild id
pulsar stag
I mean you didn’t add it to your sql statemnt lol
clear reef
i dont know what to do simply
havent written anything about the guild id yet
because i am confused
clear reef
oh?
pulsar stag
clear reef
wahahaha 
ohhh
wait
aaa
i have no idea what im supposed to do
the examples doesnt tell me much about having ids and checking if this user has a warning in this server
atomic jay
I get this error when I try to install mysqlcenter anyone knows why?
reef crag
what r your preferred Database managmnet tools? SQL, Apache Drill, T-SQL, Python DN tools, and others?
I have a 6 millions row PGA Tour database in Access and wondeirng if there is a better way to set this up and mitigate into a new management system?
clear reef
@pulsar stag (probably shouldnt ping) but i have now made the "Guilds" table get information. No idea if i was supposed to do that but i did
oops
wait
lets make the guild id and name go there
not user
dumb me
rocky jewel
Good morning guys, does anyone here have experience connecting to an MS SQL database using pyodbc?
languid steppe
harsh pulsar
!ask @clear reef @rocky jewel @languid steppe
delicate fieldBOT
harsh pulsar
handy that it applies to all 3 of you at once 😉
clear reef
handy that it applies to all 3 of you at once 😉
@harsh pulsar I mean, if you look further up...
harsh pulsar
@clear reef even in the context of the above its not clear what you're asking for
reef crag
@harsh pulsar TY for the reply... how about Apache Drill?
harsh pulsar
i looked into it years ago. could be useful if you have a lot of heterogeneous data sources
apache spark has similar functionality that i use somewhat regularly
opaque pumice
hi anyone know how to use variables in a sqlite query?
cosmic smelt
@opaque pumice like user input?
cosmic smelt
1 sec
opaque pumice
kk
cosmic smelt
think I did something similar recently lol
@opaque pumice here's an example, sorry if it's not what you're looking for
opaque pumice
anything would be helpful really
cosmic smelt
SUM(
CASE WHEN dates.dates BETWEEN '%s' AND '%s'
AND employee_area = 'afd.50'
AND employee_shift = 'nat'
AND wkday_num IN ('1','2','3','4')
THEN employees.nat
ELSE 0 END) AS 'afd.50_nat'
FROM dates, employees
""" %(date_from, date_to,
date_from, date_to,
date_from, date_to,
date_from, date_to,
date_from, date_to
)
so '%s' are replaced with variables
opaque pumice
are u using mysql?
cosmic smelt
and you end your sql string with %(var1, var2)
opaque pumice
kk ill try that
cosmic smelt
this is querying an access database so I think it's microsoft sql database dialect
sqlite3
opaque pumice
sqlite3 ok
harsh pulsar
@cosmic smelt do not use string interpolation
for databases
that is bad advice
you should be using query parameters
according to the specific python databse library you are using to connect
harsh pulsar
field3 = ?
cosmic smelt
you just want a user to insert a value when prompted right
harsh pulsar
sqlite3 uses ? as placeholders, not %s
opaque pumice
oh
harsh pulsar
also write (arg,) or [arg] not (arg)
vital belfry
I have a database at Heroku and need to move it to my ubuntu server, any idea how? (postgres 12)
opaque pumice
ok
cosmic smelt
oh yeah my bad, my example is in pandasql, not sqlite3
opaque pumice
ohhh
ok when i looked it up %s was for mysql
thats why i was confused
kk now it worked
thanks @harsh pulsar and @cosmic smelt
cosmic smelt
lol sorry I couldn't help better
opaque pumice
np
cosmic smelt
@opaque pumice you dont happen to write a lot of sql in python do you?
cosmic smelt
gotcha :}
vital belfry
When I create a postgres server where can I see the host I can connect to using my bot?
Never hosted it myself
cosmic smelt
@vital belfry do you have pgadmin4 installed?
vital belfry
yup
cosmic smelt
in the browser window to the left, right click postgresql 12 and click properties
go to connection
vital belfry
Well yea but I'm installing it on a Ubuntu Server and need to get the host name from the command line
so I can then connect to it in pgadmin
cosmic smelt
ohhh, my bad. I am unfamiliar with linux
cosmic smelt
stay strong buddy
vital belfry
Never did this before so I'm trying to get it setup
harsh pulsar
then again, the process of breaking everything 100 times is educational in and of itself
vital belfry
got a clean VM, lets go
if I get this working it will be epic
It made the user postgres but idk the password to it
seems like it wont work
I installed it but cant get the hostname
south burrow
Would a question semi-related to SQLAlchemy fit here?
(i.e. I think it's more a generic Python question, but related to DBs and SQLAlchemy)
vital belfry
Yea this is a database channel so ig its ok
You may ask questions or talk about databases in this channel.
wooden basin
How can I do with sqlite3 to extract a data and use it in a variable?
@tasks.loop(minutes=1)
async def check_reminds(self):
time = datetime.datetime.now()
db = sqlite3.connect('reminders.sqlite')
cursor = db.cursor()
cursor.execute(f'''
SELECT *,
CASE WHEN time IS '{time}'
THEN 'Yes'
ELSE 'No'
END AS timequery
FROM main
''')
harsh pulsar
you need to use .fetchall() on the cursor after executing to get the data
don't. use. f-strings. for. sql.
wooden basin
@harsh pulsar i want to get the data of the time column to make a condition
like if date == time.db:
pass
rich trout
to me, it appears that you actually want to get a list of the reminders that should be handled?
You should also be aware that time IS time will be very finicky, because its too precise. You may skip over items
I would suggest selecting reminders that have not been marked ACTIONED, and have a time less than the current time
Which you can do without a case statement
wooden basin
what I want is that when the time put in the database is equal to the current one, send the message @rich trout
rich trout
Yes, so you should check for a list of "upcoming" reminders, and send them at the appropriate time
like, say, all the ones that will be showing up in the next minute
south burrow
Given a JSON file;
{
"key1": {
"desc": "Long description 1",
"type": "Integer",
"nullable": false
},
"key2": {
"desc": "Long description 2",
"type": "String",
"nullable": true
}
}
... and code;
with open("example.json", 'r') as f:
j = json.load(f)
i = FooClass()
for attr, value in foo:
for key in j:
if re.match(j[key]['desc'], attr):
setattr(i, key, value)
This is used to dynamically create attributes for the FooClass, so that only attributes defined in the JSON are used. If there are more key/value-pairs in foo, than what's defined in the JSON, they are not added as attributes.
The second objective is that the attr-value in foo can change every now and then. By using this method, we only need to update the mapping in the JSON file, and the key-attribute in i stays the same (key1/key2).
Now, I'm now trying to change the FooClass into an SQLAlchemy class. However, I was hoping to avoid having to define the attributes manually;
class FooClass(Base):
key1 = Column(Integer, nullable=False)
key2 = Column(String, nullable=True)
Is there a way, by using the loaded JSON-file in j, that I can dynamically create those tables?
rich trout
Yes, see table->class loading
wooden wraith
how do I make a connection to python with MySQL
rich trout
You'd want to create a Table(), then use that to construct a class. I've done so somewhere, so give me a moment
south burrow
I see. Looks like you can use __table__ for that? Maybe that's what you used?
class MyClass(Base):
__table__ = Table('my_table', Base.metadata,
Column('id', Integer, primary_key=True),
Column('name', String(50))
)
The question then is how one would approach creating said Table() dynamically...
rich trout
Well, dynamically, given you know the types
If you want to modify an existing table, alembic is an addon to sqlalchemy that supports that I believe
For example, this is valid:
for type_ in data_types:
columns = [
Column(name, TYPE_MAP[type_name], **get_options(type_options)) for name, type_name, type_options in type['type_data']
]
t = Table(type_['name'], Base.metadata, *columns)
class Datatype(Base):
__table__ = t
Ahh, I see. That makes sense. You have example code for TYPE_MAP as well?
rich trout
Basically just goes between what you've got for type: "Integer" and sqlalchemy's "Integer", "String"
Might need to be a function if you want it more complex
south burrow
Ye. Is there a feasible way to take a string, and make it into the imported SQLAlchemy methods? (Integer(), String(), Boolean, etc).
rich trout
You'll probably have to write a function to do it tbh
Something where you check if it's just basic (IE: Integer -> Integer) and just go into a dictionary
or if it's like String and has options that should have defaults/options provided
south burrow
Yeah, I see. Thanks for the input!
hidden nacelle
Is this where pygsheets intellectuals lurk
Or does google sheets not count as a database
opaque pumice
o
i use gspread lol
would an sqlite3 database still work if i hosted the bot on a vps
harsh pulsar
sure
i do that
sqlite is just a special file format + a library to interact with that file format
opaque pumice
ok got it
im still figuring out hosting on vps
so if i host it on a vps it would still use files on my local machine right
south burrow
@rich trout (or any other), any suggestions on how to expand on the above SQLAlchemy-question if I want to use versioned objects? (i.e. the versioned_rows_w_versionid.py example included with SQLAlchemy). It would involve class Datatype(Versioned, Base) from the above example (which will not work, as you cannot define additional Column() outside the __table__ = t assignment).
rich trout
I don't see why it would be any different
Mm ah, I see
Mixing inheritance with __table__
I don't know
I suspect you could reimplement the inheriting behavior, but that doesn't seem like the right decision.
Looking into it, there is likely a way to construct a class dynamically and then trigger sqlalchemy mapping and base class attachment, but I've never done or even considered doing that before
south burrow
I think I figured out a workaround.
rich trout
You should be able to subclass DeclarativeMeta, passing it into declarative_base(metaclass=MyClass)
and manually call what it does on setattr and getattr when you want to set up mapping, probably
its in sqlalchemy.ext.declarative.api
do share?
south burrow
You have this check in the original example;
if not isinstance(instance, Versioned):
continue
... but I don't foresee many corner cases where that makes much sense, unless I'm missing something? The only benefit is that if you have multiple SQLAlchemy classes, and want versioning on all of them, you don't need to duplicate the code (as all versioning info is put into the Versioned class).
So you can just change that check to match for the specific SQLAlchemy class name, and move the class Versioned logic into the SQLAlchemy class.
Sure, you get duplicated code if you need to do multiple SQLAlchemy classes.
To workaround the before_flush hook, you can also just create a dummy class Versioned that you assign all SQLAlchemy classes.
class Versioned(object):
pass
# Realestate object
class Example(Versioned, Base):
__table__ = t
# optional - add a calculated is_current_version column
@classmethod
def __declare_last__(cls):
…
def new_version(self, session):
…
I think that also results in the versioning data being included in each table rather than in a parent table with a join....
but that might be desired
south burrow
And then you just create the rest as from the example.
That is correct.
That was specifically from the temporal rows examples.
The "use separate history table" is even more tricky to get working with the use of __table__
I've tested the suggested workaround above, and I haven't found any cases where it's wrong.
Haven't tested with a huge dataset and/or stress-tested, but seems OK for now.
the new_version() method usually has to be different from each object, so having everything within the SQLAlchemy class seems even more logical
opaque pumice
anyone know why this is returning a syntax error on db.commit
rich trout
what you've sent appears fine
if you've replaced your field names with field2, beware that a reserved name like set or user will trigger a syntax error
opaque pumice
oh
ive fixed that error
but now i have a question
why is this not committing
when i print the changes show but its not committing to the database
rich trout
I am not sure why you have a double commit
but I suspect it is committing (or not running)
if you run the code and inspect connection.total_changes, what does it tell you?
opaque pumice
how do i inspect connection.total_changes
nvm got it
connection.totalchanges prints 1
languid flame
Guys
torn sphinx
is it worth it to make sth that makes a class stub based on columns or is there already sth that does that
lusty grail
Hey guys. Anyone ever faced issue with psycopg2 that in execute it would only ever accept numbers converted to strings? And if we pass number it gets NULLed silently
rancid forge
how do i open the db browser once downloaded??
inland wraith
how to import mongodb
fluid tusk
pymongo
is this the right way to connect to psql using url postgres://username:pass@host:port/db
tepid smelt
Can anyone help me with connecting to my SQLite Database from python
@torn sphinx i do this
https://www.a2hosting.com/kb/developer-corner/sqlite/connecting-to-sqlite-using-python
from sqlite3 import connect
delicate fieldBOT
tepid smelt
Okay thanks
@torn sphinx no need to sign up my it was just an example
olive delta
Hello
I wanna update each row's column and set to a value where column1=column2
The update statement looks like this?
update table set col3='HELLO' where col1=col2
?
harsh pulsar
yeah but you have to include the table name too
also it might vary depending on what database you're using
misty zenith
i have an excel file i wish to turn into a db table in sqlite, does anyone know how? i can't figure it out using the web
harsh pulsar
@misty zenith 1) load it with pandas, 2) save to sqlite with pandas 🙂
import sqlite3
import pandas as pd
db_conn = sqlite3.connect('output.db')
customers_jan2020 = pd.read_excel('my-workbook.xlsx', sheet='Customers Jan 2020', index_col='Customer ID')
data.to_sql('customers', db_conn, if_exists='append')
refer to the docs for various usage options
https://pandas.pydata.org/docs/reference/api/pandas.read_excel.html
https://pandas.pydata.org/docs/reference/api/pandas.DataFrame.to_sql.html
and if you need to read multiple sheets from the same workbook efficiently, use pd.ExcelFile which isn't documented for some reason but can be used something like this:
with pd.ExcelFile('my-workbook.xlsx') as ef:
data = {sheet: ef.parse(sheet) for sheet in f.sheet_names}
the above code would read every sheet into a dict
misty zenith
thank you a bunch @harsh pulsar
tepid cradle
@misty zenith add method='multi' parameter to your data.to_sql command, otherwise it can be quite slow, if your dataset is large
data.to_sql('customers', db_conn, if_exists='append', method='multi')
harsh pulsar
yep good tip
w/ postgres once i even wrote a custom method= that temporarily dumped to CSV and then COPYed the data.. idk why i didnt just do that manually but it seemed appealing at the time
tepid cradle
All a part of the learning process, I guess
I used to use R to insert data into Db. Since R is not as program-thinking oriented as Python, they implement these things in the most optimised manner by default. So the data which took 25 seconds to get inserted from R, took over 10 minutes using Python. It had me baffled for almost a month before I figured that there's a method='multi' parameter which replicates R's timings
harsh pulsar
yep R is funny like that
all the hard stuff is done in C
whereas in python that's true maybe only 2/3 of the time 😛
python defaults also tend to be more "general" wheras R libraries tend to focus on more specific use cases
tepid cradle
yeah. After learning Python, R feels like halfway between a spreadsheet program and a programming language
It was so damn easy to learn compared to Python
remote willow
That's encouraging, I know excel and Python and am about to try to pick up R
tepid cradle
The best thing about R though is that operations are vectorised be default. Because of that, Numpy feels more familiar to me than Python's built-in lists
remote willow
Oh that's nice. Step one of a lot of the stuff I do is to throw it in a numpy array or pandas df
tepid cradle
that said, going from Python to R will definitely be confusing
Python
A = [1, 2]
B = [3, 4]
A + B
>>> [1, 2, 3, 4]
R
A = c(1, 2)
B = c(3, 4)
A + B
>>> (4, 6)
Ooh. It's more math oriented than...I dunno.. "item oriented"
harsh pulsar
but numpy is more like how R behaves
A = np.array([1, 2])
B = np.array([3, 4])
A + B
this is the same as R and Matlab
numpy is heavily based on the latter
@tepid cradle this is why i recommend people start with Python and then R, whereas i learned R first
well actually i learned Stata then SAS then finally R
mercifully i forgot everything i knew about the first 2
i think i even used Minitab once..
tepid cradle
🤣
I was going to go for SAS course. Then I found this coursera course on R, back in the days it used to be free.
I looked at the options ₹50,000 for SAS, ₹0 for R
R seemed more appealing
harsh pulsar
good choice
tepid cradle
never regretted
remote willow
Is SAS still in heavy use? There's a lot of jobs asking for it, but I've never run into it
harsh pulsar
all the money i save on proprietary software i just donate to FOSS software foundations instead, feels good
SAS is still used in certain industries yes
plenty of legacy systems still using it
i would be very alarmed if a new organization decided to use it though
torn sphinx
i tried sas once
i think the only thing its good at is generating code for ppl who dont want to code
tepid cradle
The good thing about learning R was that it's very general purpose. So in my previous organisation, when I could do most of the things in Excel, I still used R once in a while because it was wayyyy faster for large datasets compared to Excel
Couldn't have done that if I'd learnt SAS
torn sphinx
i think sas is more tailored to ppl who dont want to write code
remote willow
That's been my perception of SAS, it seems like something one could get pigeonholed in
torn sphinx
and just want to see a result
harsh pulsar
yep exactly @remote willow although that's also true for any non-free (as in freedom) software platform
at least R is adjacent to programming and the skills are somewhat transferrable
advanced R programming will take you into both C and Lisp territory
tepid cradle
The bad part of R is that if you have similar expectations of other programming languages, you're going to be disappointed
My initial days with Python were really frustrating
especially the errors
R's error messages are concise one-liners.
Python's 100-line traceback instantly sent me into a fit of rage whenever it dumped that wall of text on me (very often)
It took a while to learn to sift through that and understand the actual error. I still don't like it all that much
remote willow
Heh, yeah. I've always assumed that CVS-receipt-level traceback wall was useful to someone, just not to me.
tepid cradle
Feels a bit unnecessary
harsh pulsar
going back to R errors after python tracebacks is actually annoying
and the worst part about R for me is the unqualified imports
that, or "looping is dog slow"
tepid cradle
and the worst part about R for me is the unqualified imports
@harsh pulsar oh yes, this. Nightmare. One library's functions getting masked by another library can be so confusing if you are using two similar libraries, of which there are a lot in R
harsh pulsar
yes
at least the scoping/masking rules are consistent
and its worse in libraries like dplyr that assume they are imported
at least years ago, certain things would break if you tried to write dplyr::mutate() instead of library(dplyr); mutate()
tepid cradle
Haven't used that syntax much
I loved lubridate though. I replicated its dmy, mdy, ymd functions in Python for my personal use. It was a fun learning exercise as well, regarding datetime
torn sphinx
Is there more efficient way to do this?
@commands.Cog.listener()
async def on_raw_bulk_message_delete(self, payload):
for message_id in payload.message_ids:
if message_id in self.bot.giveaway_messages_list:
async with self.bot.pgdb.acquire() as conn:
async with conn.transaction():
await conn.execute("UPDATE giveaways SET status='Cancelled' WHERE message=$1", message_id)
It is for delete giveaway from database when bulk delete messages. giveaway_messages_list is integer list of all giveaway message IDs
tepid cradle
queries with IN can be difficult to perform, but you definitely can
which db are you using?
torn sphinx
who me?
tepid cradle
yup
torn sphinx
asynpg postgres
it works, but i dont know if is efficient
or if there is exist better way to do so
tepid cradle
Using IN would obviously be far more efficient
torn sphinx
Actaully new problem
Is there way to change this,
await conn.execute("UPDATE giveaways SET status='Cancelled' WHERE message=$1", message_id)
So is only set to Cancelled if the status value is running
I dont want to set to cancel if status value is ended
tepid cradle
just add and status = 'running' at the end of your query
torn sphinx
Oh yes lol how stupid of me 😅
Using
INwould obviously be far more efficient
@tepid cradle And how would i do this? you have example or link?
tepid cradle
give me a minute
@torn sphinx
query = "UPDATE giveaways SET status='Cancelled' WHERE message in ({})"
messages = [1,2,3]
query = query.format(', '.join(['%s']*len(messages)))
# the previous line gives you "UPDATE giveaways SET status='Cancelled' WHERE message in (%s, %s, %s)"
cur.execute(query, messages)
This should be much faster if you have a lot of messages
torn sphinx
Oh i see yes thanks friend 🙂
tepid cradle
let me know if you try it and it works @torn sphinx
I have tried this with psycopg2 only, not with asyncpg
harsh pulsar
@torn sphinx your on_raw_bulk_message_delete code looks fine
oh i see you wanted to expedite the looping
torn sphinx
Does it?
I thought like how i did this was bad:
for message_id in payload.message_ids:
if message_id in self.bot.giveaway_messages_list:
that's fine, how big is giveaway_messages_list?
you should acquire the connection outside the loop btw
torn sphinx
Because if user deletes bulk 100 messages then it loops 100 times for example.
And giveaway list is only of running giveaway. For now is only few items because still small, but maybe in future is bigger
harsh pulsar
and yes gkoru's suggestion is good
@commands.Cog.listener()
async def on_raw_bulk_message_delete(self, payload):
message_ids = list(set(payload.message_ids) & set(self.bot.giveaway_messages_list))
query_template = "UPDATE giveaways SET status='Cancelled' WHERE message in ({})"
query = query_template.format(', '.join(['%s'] * len(message_ids)))
async with self.bot.pgdb.acquire() as conn:
async with conn.transaction():
await conn.execute(query, message_ids)
how about something like this?
you can of course write
message_ids = [m for m in payload.message_ids if m in self.bot.giveaway_messages_list]
depending on what you prefer
torn sphinx
Yes the second way of writing makes more meaning to me
@harsh pulsar I get error asyncpg.exceptions.PostgresSyntaxError: syntax error at or near "%"
harsh pulsar
can you print(query) after generating it?
torn sphinx
Ignoring exception in on_raw_bulk_message_delete
Traceback (most recent call last):
File "D:\Programs\Python\Python37\lib\site-packages\discord\client.py", line 312, in _run_event
await coro(*args, **kwargs)
File "D:\Documents\bot\cogs\BotEvents.py", line 181, in on_raw_bulk_message_delete
await conn.execute(query, message_ids)
File "D:\Programs\Python\Python37\lib\site-packages\asyncpg\connection.py", line 274, in execute
_, status, _ = await self._execute(query, args, 0, timeout, True)
File "D:\Programs\Python\Python37\lib\site-packages\asyncpg\connection.py", line 1403, in _execute
query, args, limit, timeout, return_status=return_status)
File "D:\Programs\Python\Python37\lib\site-packages\asyncpg\connection.py", line 1411, in __execute
return await self._do_execute(query, executor, timeout)
File "D:\Programs\Python\Python37\lib\site-packages\asyncpg\connection.py", line 1423, in _do_execute
stmt = await self._get_statement(query, None)
File "D:\Programs\Python\Python37\lib\site-packages\asyncpg\connection.py", line 328, in _get_statement
statement = await self._protocol.prepare(stmt_name, query, timeout)
File "asyncpg\protocol\protocol.pyx", line 163, in prepare
asyncpg.exceptions.PostgresSyntaxError: syntax error at or near "%"
one of us probably made a mistake
brazen charm
asyncpg uses the $n place holders
harsh pulsar
ah
brazen charm
like standard postgre
harsh pulsar
that would do it
harsh pulsar
its a nice feature imo if you need to re-use a parameter
query = query_template.format(', '.join(f'${m}' for m in range(len(message_ids))))
Now i get new error. asyncpg.exceptions.UndefinedParameterError: there is no parameter $0
Query print is UPDATE giveaways SET status='Cancelled' WHERE message in ($0, $1)
harsh pulsar
evidently
torn sphinx
More errors 😬
asyncpg.exceptions._base.InterfaceError: the server expects 4 arguments for this query, 1 was passed
HINT: Check the query against the passed list of arguments.
UPDATE giveaways SET status='Cancelled' WHERE message in ($1, $2, $3, $4)
brazen charm
you dont give async pg a tuple btw