#databases | Python | Page 102

torn sphinx Jul 28, 2020, 8:38 PM

#

thank you so much guys!

harsh pulsar Jul 28, 2020, 8:40 PM

#

great

#

if anyone else is curious about this behavior, here's an example: https://www.db-fiddle.com/f/hPGgbBQ5BRciqj8i1P71mg/0

DB Fiddle - SQL Database Playground

An online SQL database playground for testing, debugging and sharing SQL snippets.

muted ingot Jul 28, 2020, 10:58 PM

#

@rich trout the bot finally works

#

i figured out how to append in json

#

so everything is nicely organized too

#

i also made it so the bot overrides existing queries

#

which is nice so no duplicates

craggy pawn Jul 29, 2020, 2:55 AM

#

are there any resources that show how I should format queries from a discord bot?

#

basically escape all the dumb shit people will inevitably try to send to my db

muted ingot Jul 29, 2020, 2:56 AM

#

what do you mean?

#

also #discord-bots?

#

@craggy pawn

#

actually eh i guess it's sorta topical

craggy pawn Jul 29, 2020, 2:57 AM

#

I am making a discord bot for wow guilds that allows people to query a db I built and receive info about items, etc

#

I just would like to make it so people can't try and give me the ol bobby tables treatment

tepid cradle Jul 29, 2020, 3:06 AM

#

If you use parameterised queries, that should be good. But you need to be more specific with your questions, like an example or two would be helpful

fallow elm Jul 29, 2020, 3:16 AM

#

@craggy pawn depending on the driver you use there should be a way to pass parameters separately from the query itself.

for example NEVER do

c.execute("INSERT INTO foo VALUES ({})".format(something_from_user))

this is how you get SQL injection because now they can control your query. if you end the parameters seperately you don't have to worry about escaping. this is driver dependent but for example it might look like

c.execute("INSERT INTO foo VALUES (?)", something_from_user)

#

the difference is the drive is sending the query and the parameters separately to the database rather than you trying to munge it into a single query string

craggy pawn Jul 29, 2020, 3:22 AM

#

99% of the queries would be some variation of

SELECT * FROM loot WHERE itemtype = 'cloth';

fallow elm Jul 29, 2020, 3:25 AM

#

if you're just selecting from preselected stuff you don't need to worry about sql injection

#

it'll just depend on what the interface looks like

#

like if the UI is !loot cloth then you need to make sure not to use a format string

craggy pawn Jul 29, 2020, 3:44 AM

#

the format is !loot [itemtype] [stat1]* [stat2]*

#

= optional

craggy pawn Jul 29, 2020, 5:47 AM

#

I have it set up currently to read the table into a dataframe at restart every night since its a fairly small table and then my !loot command is just one big ugly block of dataframe manipulation

#

it works, but its an eyesore and I feel like it could be cleaned up by querying the db directly

hallow umbra Jul 29, 2020, 6:17 AM

#

-Background info: flask_mysqldb

~i am able to send data to database successfully just not able to get data out to the Chart.js

Anyone know how to call data from mysql database to <script>(javascript)?

#

can @noble ice name when u reply to me

tepid cradle Jul 29, 2020, 6:22 AM

#

@craggy pawn, another thing you can do is to create a separate credentials with only read permission, then use that for your bot.

fallow elm Jul 29, 2020, 6:22 AM

#

@hallow umbra you can do two things:

when rendering your page page your data into your template in a way that javascript understands. there's a tojson filter you can use: https://flask.palletsprojects.com/en/0.12.x/templating/#standard-filters
expose the data on an api endpoint and query that from javascript via ajax/fetch request

craggy pawn Jul 29, 2020, 6:23 AM

#

thats an idea

#

Eventually, I want to use it for forming groups and grouping data so I while that would work in the meantime, I would have to change it if I ever get around to doing that

torn sphinx Jul 29, 2020, 6:25 AM

#

Hi

craggy pawn Jul 29, 2020, 6:25 AM

#

unless I can use 2 connections, but I feel like at that point, I should just figure out how to safely use a read/write account

torn sphinx Jul 29, 2020, 6:26 AM

#

I use sqlite3 to save my time, but when I tell the bot to return it back, it is a tuple. then I print it out here is what comes out
('2020-07-29 14:14:49.530130',)
How to convert this into datetime?

tepid cradle Jul 29, 2020, 6:27 AM

#

I think you can also have credentials with insert/update permissions, but not delete/drop permissions. Still better than full access.
And with this, deletions can be handled using an active flag which you set to false. Then everywhere you query, you only take active records

unless I can use 2 connections, but I feel like at that point, I should just figure out how to safely use a read/write account
@craggy pawn

#

I use sqlite3 to save my time, but when I tell the bot to return it back, it is a tuple. then I print it out here is what comes out
('2020-07-29 14:14:49.530130',)
How to convert this into datetime?
@torn sphinx
Db queries always return tuples. It's already a datetime object most likely. You just have to access it with subsetting, like result[0]

torn sphinx Jul 29, 2020, 6:29 AM

#

ok thx

hallow umbra Jul 29, 2020, 6:29 AM

#

@fallow elm <script>
let myChart = document.getElementById('myChart').getContext('2d');

// Global Options
Chart.defaults.global.defaultFontFamily = 'Lato';
Chart.defaults.global.defaultFontSize = 18;
Chart.defaults.global.defaultFontColor = '#777';

let massPopChart = new Chart(myChart, {
  type:'pie', // bar, horizontalBar, pie, line, doughnut, radar, polarArea
  data:{
    labels:['Wrong login credentials', 'URL traversal', '500 error'],
    datasets:[{
      label:'Population',
      data:[
        20,
        30,
        50

torn sphinx Jul 29, 2020, 6:29 AM

#

yea it works thx @tepid cradle

hallow umbra Jul 29, 2020, 6:30 AM

#

i want to send something into data:

#

i have </script> end tag

craggy pawn Jul 29, 2020, 6:31 AM

#

it looks like its as simple as ```
grant select, insert, update on loot to mydiscordbot;

tepid cradle Jul 29, 2020, 6:31 AM

#

@hallow umbra use codeblocks to post code.
And to pass data from API to Javascript, you have to convert it to JSON.
Convert the DB output to a Python dictionary, then use json.dumps() to convert it to JSON

torn sphinx Jul 29, 2020, 6:32 AM

#

date = datetime.datetime(int(timeresult[0]))
print(date)

It throws an error
ValueError: invalid literal for int() with base 10: '2020-07-29 14:13:52.881769'
But without the int(), it will say that it requires a int type object...

tepid cradle Jul 29, 2020, 6:33 AM

#

it looks like its as simple as ```
grant select, insert, update on loot to mydiscordbot;

@craggy pawn yup.
In production environments, it's always a good idea to not use credentials with delete/drop permissions. Extra layers of safety never hurt.
And setting active = false instead of delete also helps maintain an audit trail and easily handle unwanted or accidental deletions

#

date = datetime.datetime(int(timeresult[0]))
print(date)
It throws an error
ValueError: invalid literal for int() with base 10: '2020-07-29 14:13:52.881769'
But without the int(), it will say that it requires a int type object...
@torn sphinx
Are you sure it's not already returning a datetime object? The DB library should handle that, you don't need to convert at all

torn sphinx Jul 29, 2020, 6:36 AM

#

oh ok

#

let me try again

#

I tried this code

timeresult = cursor.fetchone()
date = timeresult[0]
datetimedifference = datetime.datetime.now() - date
print(datetimedifference.total_seconds())

Error:

TypeError: unsupported operand type(s) for -: 'datetime.datetime' and 'str'

#

But when I convert it to int, it will say that invlaid literal..

tepid cradle Jul 29, 2020, 6:40 AM

#

Your datetime seems to be stored as string in your db. You can handle that with datetime.datetime.strptime().
But the ideal thing to do would be to correct the data type in our Db

craggy pawn Jul 29, 2020, 6:40 AM

#

sounds like your date column in your db isnt formatted as a datetime

#

so if I am generating a query based off user input, how would I handle optional variables in non-standard order in multiple columns?

#

would I need to go through and identify which column each arg belongs to and then generate a query with ```
where columnname = arg or columnname is not null;

tepid cradle Jul 29, 2020, 6:54 AM

#

Unfortunately you can only parameterize values and not column names. So you have two approaches:

Have multiple queries pre-written and use the relevant one based on inputs
Have a base query, validate the column names provided against a list of columns (which you can fetch from the information_schema), then use string formatting to create your query, then pass the values as parameters

#

Validating column names will prevent arbitrary string from being passed as a column name. You only pass a string if you know it's a column name.

craggy pawn Jul 29, 2020, 7:07 AM

#

their requests would be limited to 3/4 columns, I am just unsure in which order they will be given

#

if that changes anything

torn sphinx Jul 29, 2020, 7:07 AM

#

ok thx @tepid cradle

craggy pawn Jul 29, 2020, 7:09 AM

#

if you could imagine this being the table:```
item stat1 stat2 boss

egg crit haste dave
sandal haste mastery jim
stick vers mastery bob

#

and

!loot haste
!loot crit dave
!loot dave
!loot mastery jim haste

#

would all be valid requests

tepid cradle Jul 29, 2020, 7:22 AM

#

Ok. This will need a strategy crafted specifically to handle this kind of query. You can either just do an or in each column based on the number of parameters provided, or if the values are all text, a better strategy will be to use FTS (full text search) and create an FTS Document which is combination of all three columns.
I'm talking in Postgresql specific terms here as that's what I am experienced with

#

I've handled this before using FTS. I had mutual funds listed with their categories. So something like

ABSL Frontline    Large cap
DSP equity           Multi cap
Mirae emerging  Mid cap
Principal emerging   Mid cap

I wanted the user to be able to search both based on category as well as name from a single search box. So I created an FTS doc with values from both columns. Works like a charm

#

You can even do it on the fly, like
cur.execute("select * from table where concat(stat1, stat2, boss) ilike %s", (f"{param1}%%{param2}",))

#

Actually, no, that will not make it order agnostic

#

But I'll leave it here, just for ideas

craggy pawn Jul 29, 2020, 7:37 AM

#

I'm tempted to just force standard order but I feel like that is generally not user friendly

tepid cradle Jul 29, 2020, 7:42 AM

#

Nah. Look up full text search, most DBs have some support for it. Best if you're using postgresql, very easy to implement

mild vector Jul 29, 2020, 8:43 AM

#

Hey !
How can I count the number of people who have the same value in a column with asyncpg please?

tepid cradle Jul 29, 2020, 9:57 AM

#

Do you write queries differently on asyncpg?

hallow umbra Jul 29, 2020, 12:47 PM

#

using mysql workbench innodb, how do i display rows in terms of current day with interval of 7days

📎 unknown.png

tepid cradle Jul 29, 2020, 1:23 PM

#

between current_date and current_date - interval 7 day

granite arch Jul 29, 2020, 1:32 PM

#

Does anyone have any experience with postgres_fdw?

I've got the server mapping and foreign tables up and running but was just wondering if there is a nice way of checking the status (i.e. accessible or not) of that FDW server connection.

At query time there'll be an error raised if the connection fails, so one way would be to make some minimal generic query to the server or a specific foreign table (e.g. SELECT 1 FROM foreign_table (there might be a nicer query to use here for this purpose)) and catch that error. Does anyone have any ideas, or improvements on this concept?

Thanks 🙂

harsh pulsar Jul 29, 2020, 1:52 PM

#

i have used it @granite arch but only up to the point where "it works and i dont want to touch it anymore"

#

select 1 from foreign_table seems reasonable 🤷‍♂️

granite arch Jul 29, 2020, 1:53 PM

#

Haha, fair enough 😆
Do you reckon that's simplest/most efficient query I can get away with?

harsh pulsar Jul 29, 2020, 2:10 PM

#

no idea honestly

#

maybe do something silly like deliberately take down the foreign table database server

#

then try that query

#

see if it fails as expected

granite arch Jul 29, 2020, 2:12 PM

#

I'll give it a go. Thanks for your input @harsh pulsar 🙂

vital edge Jul 29, 2020, 2:44 PM

#

Hello im trying stuff with sql (mariadb)
Can we replace "DESC" and "1" by variables in ORDER BY (%s) DESC LIMIT 1 ?

I tried to put (%s) instead of them and doing the usual stuff but it doesn't seem to work, is it supposed to ?

cobalt owl Jul 29, 2020, 2:44 PM

#

Hi all, I'm trying to connect to an Access database through PYODBC and am getting the following error:

InterfaceError: ('IM002', '[IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified (0) (SQLDriverConnect)')

#

I've done some googling and found if Access and your Python bit's dont match (32 bit vs 64 bit) that could be an issue so I reinstalled Anaconda and Python for 32bit to match my Access and am getting the same error, any ideas?

#

import pyodbc

connStr = (
    r"DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};"
    r"DBQ=C:\Users\pathtomyaccessreport.accdb;"
    )
cnxn = pyodbc.connect(connStr) ```

granite arch Jul 29, 2020, 3:36 PM

#

Does anyone have any experience with postgres_fdw?

I've got the server mapping and foreign tables up and running but was just wondering if there is a nice way of checking the status (i.e. accessible or not) of that FDW server connection.

At query time there'll be an error raised if the connection fails, so one way would be to make some minimal generic query to the server or a specific foreign table (e.g. SELECT 1 FROM foreign_table (there might be a nicer query to use here for this purpose)) and catch that error. Does anyone have any ideas, or improvements on this concept?

Thanks 🙂
@harsh pulsar Well, it works. I'm not sure if it will eventually slow down as more data is added to the table being queried (select 1... returns a value for each row I think) (it's got to be a lot of data to make any real difference, but worth considering 🙂 ). I wonder if there is a constant time query that can achieve the same thing.

tepid cradle Jul 29, 2020, 4:08 PM

#

Hello im trying stuff with sql (mariadb)
Can we replace "DESC" and "1" by variables in ORDER BY (%s) DESC LIMIT 1 ?

I tried to put (%s) instead of them and doing the usual stuff but it doesn't seem to work, is it supposed to ?
@vital edge

You can, but you'll have to pass the values through string formatting, not through query parameters.
cur.execute("select * from table where col1 = %s order by {} limit {}".format(col2, 10), ("foo",))

Like this

grim lotus Jul 29, 2020, 4:14 PM

#

What's the best db for a big application which will take images amd lots of other data

tepid cradle Jul 29, 2020, 4:19 PM

#

Not a good idea to store images in Db, regardless of the Db. Just use file storage for images and store the pointers in the Db.
If you're going for free/open source, my suggestion would be to go for PostgreSQL Db. It's robust, commonly available with cloud providers, actively developed and maintained, and comes with lots and lots of features.

harsh pulsar Jul 29, 2020, 4:22 PM

#

@granite arch you can do select 1 from foreign_table limit 1 maybe

#

one would hope that a query planner can optimize that

#

postgres is great

#

you could store images in it as binary blobs if you really neeed to

#

but +1 use filenames instead

granite arch Jul 29, 2020, 4:23 PM

#

Ah, great plan I'll try that. Thanks again!

torn sphinx Jul 29, 2020, 4:37 PM

#

How would I store a list in an sqlite3 database?

#

Would I have it as a string?

vital edge Jul 29, 2020, 5:16 PM

#

Q_Botte = "SELECT botteID FROM Interm_Botte WHERE championID = (SELECT id FROM Champion WHERE nom = '{}') AND laneID = (SELECT id FROM Lane WHERE nom = '{}') ORDER BY '{}' {} LIMIT {}" 
Q_Botte_Full = Q_Botte.format(champ,lane,'winrate','ASC','3')```
Ok so I made this thanks to you help @tepid cradle  but it works strangely :(
When I change "ASC" for "DESC" or "winrate" by "pickrate" it doesn't affect the results by any means wheareas it's supposed to (I checked the database)

#

For a bit of context, this is the complete table for the championID and the laneID I used as test

(columns are : ID, pickrate, winrate, championID, laneID, botteID)

(27, 10.2, 0.07, 1, 4, 7)
(22, 46.02, 7.45, 1, 4, 2)
(26, 46.57, 0.14, 1, 4, 6)
(23, 49.97, 5.61, 1, 4, 3)
(21, 50.31, 75.59, 1, 4, 1)
(25, 50.69, 0.41, 1, 4, 5)
(24, 53.94, 1.32, 1, 4, 4)```

tepid cradle Jul 29, 2020, 5:18 PM

#

why do you have quotes around the order by string? It's not supposed to be a string for your database

#

Also, don't use string concatenation for your where clause

vital edge Jul 29, 2020, 5:19 PM

#

ok now it works

#

just removed the quotes

tepid cradle Jul 29, 2020, 5:21 PM

#

Q_Botte = """SELECT botteID 
              FROM Interm_Botte 
              WHERE championID = (SELECT id FROM Champion WHERE nom = %s) 
                  AND laneID = (SELECT id FROM Lane WHERE nom = %s) 
              ORDER BY {} {} LIMIT {}
          """
Q_Botte_Full = Q_Botte.format('winrate', 'ASC', 3)
cursor.execute(Q_bottle_full, ('champ', 'lane'))

#

This would be the ideal query

brazen charm Jul 29, 2020, 5:21 PM

#

other than that nice injection attack vulnerability

tepid cradle Jul 29, 2020, 5:22 PM

#

that's of course there, but column names can't be parameterized. So they'll have to take precautions against that

#

It's easier in case of column names though. They can be validated before being passed to the query

gentle silo Jul 29, 2020, 5:24 PM

#

How is there an injection vulnerability, the string formatting is hardcoded

tepid cradle Jul 29, 2020, 5:25 PM

#

If it's hardcoded then why do you need string formatting? Just put the values in the query itself

#

unless you wan to keep it dynamic for your code but not accept values for the same from the front-end

fluid wind Jul 29, 2020, 5:30 PM

#

@torn sphinx I know you've already got you answer, but here is another version

from datetime import datetime as tm
timestamp, = ('2020-07-29 14:14:49.530130',)
ts = tm.strptime(timestamp, '%Y-%m-%d %H:%M:%S.%f')```

cobalt owl Jul 29, 2020, 5:32 PM

#

Hi all, I'm trying to connect to an Access database through PYODBC and am getting the following error:

InterfaceError: ('IM002', '[IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified (0) (SQLDriverConnect)')
Can anyone assist on this by chance? Any other troubleshooting besides the bits not matching?

vital edge Jul 29, 2020, 5:34 PM

#

@tepid cradle I am really thankfull for any advice that can make me progress so thanks a lot already
But I don't get why it is important to use those 2 ways instead of only one ?*

grim lotus Jul 29, 2020, 5:43 PM

#

Which and how should i build my Database its for a app that tracks your meals

#

So people will be uploading thier meals

#

So how should i structure my app

#

it will be having name user-name email + daily meals for every day

#

How would i make that db ?

fluid wind Jul 29, 2020, 5:48 PM

#

@cobalt owl I had the same error until I've installed "Microsoft Access Database Engine 2010 Redistributable" - https://www.microsoft.com/en-us/download/confirmation.aspx?id=13255. Obviously you have to match 32 or 64bit across Python, PYODBC, and Access Engine. After that your code worked pretty much as it is.

import pyodbc
db_file = r"/Code/Python/sandbox/AccessDb.accdb"
con_str = "Driver={Microsoft Access Driver (*.mdb, *.accdb)};Dbq=" + db_file
con = pyodbc.connect(con_str)```

cobalt owl Jul 29, 2020, 5:49 PM

#

@fluid wind Thanks, I'll give that a shot!

#

Dang, can't install without admin privledges ha

#

Aw well, looks like I'll have to manually import from Access to do my bidding

fluid wind Jul 29, 2020, 5:56 PM

#

@cobalt owl If you can export table as CSV file, you're all set

tepid cradle Jul 29, 2020, 5:56 PM

#

@tepid cradle I am really thankfull for any advice that can make me progress so thanks a lot already
But I don't get why it is important to use those 2 ways instead of only one ?*
@vital edge Usually if you're building an application with a where clause, it indicates that at some point you'll allow the users of the app to pass values to those where clauses.
So suppose you have something like "select * from products where product_name = %s"
you expect the user to pass something like, say, samsung. So you'll query "select * from products where product_name = 'samsung'"
But the user is malicious and they pass samsung; drop table products;
now your query becomes "select * from products where product_name = 'samsung'; drop table products;" and your database deletes the table.
This is called SQL Injection attack, where people malicious people can inject SQL statements into your query. If you are going to be dealing with databases, I would recommend reading a little bit more about it.
To prevent against this, most Db libraries provide a way to pass parameters separately. This provides a measure of protection as the parameters are then prepares by the library, including detection of malicious code.

grim lotus Jul 29, 2020, 5:57 PM

#

Does using $ prevents sql injection?

cobalt owl Jul 29, 2020, 5:57 PM

#

@cobalt owl If you can export table as CSV file, you're all set
@fluid wind I don't think that should be an issue, I will poke around, still relatively new to this. I really appreciate your help!

fluid wind Jul 29, 2020, 5:58 PM

#

@grim lotus bind variables do. Some db drivers provide extra functionality to sanitize SQL as well.

grim lotus Jul 29, 2020, 5:58 PM

#

Ohh i see

#

I have another question above GWsetmyxPeepoCry

tepid cradle Jul 29, 2020, 6:02 PM

#

ask.
But your previous question is quite vague. If you want to structure your db, you'll have to decide what fields you're going to store

granite arch Jul 29, 2020, 6:02 PM

#

@harsh pulsar That seems much faster. Great suggestion, thank you 🙂

harsh pulsar Jul 29, 2020, 6:03 PM

#

@torn sphinx if you enable the json extension you can store it as json

tepid cradle Jul 29, 2020, 6:03 PM

#

I'd recommend this website to try and build your Db diagram first. https://dbdiagram.io/home
@grim lotus

A Free Database Designer for Developers and Analysts

Quick and simple free tool to help you draw your database relationship diagrams and flow quickly using just keyboard

grim lotus Jul 29, 2020, 6:03 PM

#

Oh thankss

#

But that's what i m asking

#

How should i structure it

#

Whats recommended to do

#

📎 Screenshot_2020-07-29-23-35-35-55.jpg

#

Is it ALLOWED to make it like so ?

#

having a order id inside a table and having sub-info of it ?

knotty pond Jul 29, 2020, 6:11 PM

#

does aiosqlite close the db when there isn't activity for a while, because my stuff will be running fine but after a while i get a value error that's like:
ValueError: no active connection

tepid cradle Jul 29, 2020, 6:19 PM

#

@grim lotus, usually you structure databases in such a way that information of one type stays in one place and you try to avoid too many repetitions

torn sphinx Jul 29, 2020, 6:19 PM

#

@torn sphinx if you enable the json extension you can store it as json
@harsh pulsar as json in the sqlite3 db?

harsh pulsar Jul 29, 2020, 6:19 PM

#

yes

torn sphinx Jul 29, 2020, 6:19 PM

#

alright

#

like it stores json data in an sqlite 3 db?

strong kiln Jul 29, 2020, 6:22 PM

#

Sqlalchemy question: can you join two tables from different binds?

harsh pulsar Jul 29, 2020, 6:24 PM

#

!e ```python
import json
import sqlite3

db = sqlite3.connect(':memory:')

with db:
db.execute('create table hello (name text, metadata json)')
db.execute('insert into hello values (?, ?)', ('salt rock', json.dumps({'a': 32, 'b': [-1, -2]})))

results = db.execute("select name, json_extract(metadata, '$.b') from hello").fetchall()
for name, metadata in results:
print(name, json.loads(metadata), sep=' | ')

delicate fieldBOT Jul 29, 2020, 6:24 PM

#

@harsh pulsar :white_check_mark: Your eval job has completed with return code 0.

salt rock | [-1, -2]

harsh pulsar Jul 29, 2020, 6:24 PM

#

@torn sphinx ^

#

the json data is passed back and forth to python as a string

torn sphinx Jul 29, 2020, 6:24 PM

#

oh alright

harsh pulsar Jul 29, 2020, 6:24 PM

#

but in the sql query itself you can do json manipulation

#

itd be nice if the db library automatically converted json columns for you but this works well enough for my purposes

torn sphinx Jul 29, 2020, 6:25 PM

#

alright

tepid cradle Jul 29, 2020, 6:45 PM

#

does aiosqlite close the db when there isn't activity for a while, because my stuff will be running fine but after a while i get a value error that's like:
ValueError: no active connection
@knotty pond
The db closes the connection.
The library is not "running" all the time as such, so it can't close the connection

knotty pond Jul 29, 2020, 6:46 PM

#

ok, thanks, but is there any way around this?

#

i mean theoretically it wouldn't be a problem since when i put the database on the live server it won't have that issue, but in the rare event that that does happen?

grim lotus Jul 29, 2020, 6:49 PM

#

@grim lotus, usually you structure databases in such a way that information of one type stays in one place and you try to avoid too many repetitions
@tepid cradle yes i know i have worked with databases to be exact asyncpg + postresql

#

But I can't get the point howwl i store such info which will be incoming every few hours with something new

tepid cradle Jul 29, 2020, 6:52 PM

#

You have one user table, and another meals table with a foreign key relationship with the user table through the user id column

grim lotus Jul 29, 2020, 6:53 PM

#

How does that foreign key relationship works GWslippyPeepoL

#

I didn't knew about that , can this be achieved in psql?

fluid wind Jul 29, 2020, 6:54 PM

#

@grim lotus it's a big topic, your best bet is googling and reading few articles.

grim lotus Jul 29, 2020, 6:54 PM

#

Well can you assist what to exactly search for ? In order to relate to that topic much

tepid cradle Jul 29, 2020, 6:56 PM

#

Search for foreign key relationship in databases

cobalt owl Jul 29, 2020, 9:09 PM

#

What is the best way to manipulate data out of Access within Python? I apparently can't export CSVs from Access queries or tables which is odd

#

and unfortunately I can't get PYODCB working so I'm at wits end hahah

thorn comet Jul 29, 2020, 9:34 PM

#

Whats the best way to start database coding?

harsh pulsar Jul 29, 2020, 9:41 PM

#

learn sql, start playing around in sqlite

pliant pendant Jul 29, 2020, 10:50 PM

#

is it worth using default

#

in places i would otherwise replace NULL with default values in code

fluid wind Jul 29, 2020, 11:11 PM

#

NULLs are special, they indicated absence of value . The closest Python equivalent is None.

pliant pendant Jul 29, 2020, 11:15 PM

#

indeed, but is it worth leaving it NULL for memory saving

#

or is it a bad habit

fluid wind Jul 29, 2020, 11:22 PM

#

@cobalt owl interesting, I was able to, but steps are not entirely obvious.

#

@pliant pendant if default values work for you, or you can guarantee that data you're getting never have NULLs, sure, you can go without those. In practice, unless you control the data, NULLs are here to stay.

pliant pendant Jul 29, 2020, 11:33 PM

#

NULL is handy, i would use them for e.g. checking premium time limit, where users who never ordered it has a NULL value

#

but default is what i see useful for e.g. permissions

#

where i have defined default values which i would put anyways trough code

harsh pulsar Jul 29, 2020, 11:35 PM

#

go with what makes sense for your problem first

#

if and only if you encounter specific performance requirements, then worry about memory

fluid wind Jul 29, 2020, 11:37 PM

#

Well, permissions example is different. You're controlling it, it's defined as NOT NULL, and you set default value as 'N' for example. But then we get into specifics of RDBMS engines.

torn sphinx Jul 30, 2020, 1:12 AM

#

@torn sphinx I know you've already got you answer, but here is another version
from datetime import datetime as tm
timestamp, = ('2020-07-29 14:14:49.530130',)
ts = tm.strptime(timestamp, '%Y-%m-%d %H:%M:%S.%f')```

@fluid wind thx bro but I got an error
ValueError: time data '2020-07-30 09:11:40.847388' does not match format '%y-%m-%d %H:%M:%S.%f'

#

Or maybe any one can hep me please

fluid wind Jul 30, 2020, 1:42 AM

#

%Y is capital

tepid cradle Jul 30, 2020, 1:46 AM

#

Refer https://strftime.org/
It's important to be able to figure out these small things on your own. If you keep waiting for someone to answer for every little problem, coding will take hella lots of time.

Python strftime reference

A quick reference for Python's strftime formatting directives.

carmine knot Jul 30, 2020, 1:50 AM

#

hey guys is there where I might ask a questions about using csv_reader questions?

fluid wind Jul 30, 2020, 1:51 AM

#

standard csv module?

carmine knot Jul 30, 2020, 1:52 AM

#

yes im a beginner and I am having a hard time understand why my code appears to be jumping past or missing some lines

#

is there a special way to copy and paste my code or will it specially format it when I paste it in?

torn sphinx Jul 30, 2020, 1:54 AM

#

what are databases?

fluid wind Jul 30, 2020, 1:55 AM

#

@carmine knot same way as with github markdown

#

https://docs.github.com/en/github/writing-on-github/creating-and-highlighting-code-blocks

Creating and highlighting code blocks - GitHub Docs

Share samples of code with fenced code blocks and enabling syntax highlighting.

carmine knot Jul 30, 2020, 1:57 AM

#

with open('addresses.csv', 'r') as csv_file:
csv_reader = csv.reader(csv_file)
address = input("Enter Address:")
for row in csv_reader:
address = input("Enter Address:")
for field in row:
if field == address:
print(row[1])
pixel = row[0]
pixel = int(pixel)
print (pixel)

fluid wind Jul 30, 2020, 2:00 AM

#

try with ```python
with open('addresses.csv', newline='') as csv_file:

newline parameter is important

carmine knot Jul 30, 2020, 2:00 AM

#

what does the newline do?

fluid wind Jul 30, 2020, 2:02 AM

#

https://docs.python.org/3/library/csv.html?highlight=csv reader#csv.reader

carmine knot Jul 30, 2020, 2:02 AM

#

I just attempting to read the file and compare my input and see if my input shows up anywhere inside the csv file and once it finds a comparison it will print the first row which is just a indexing number (ex. 1,2,3,4,5)

ancient abyss Jul 30, 2020, 2:04 AM

#

cursor.execute("""CREATE TABLE IF NOT EXISTS level_system(

                name TEXT,
                user_id BIGINT,
                guild_id BIGINT,
                level INT
)""")
        conn.commit()

I can't use ANY command

#

psycopg2.errors.InFailedSqlTransaction: current transaction is aborted, commands ignored until end of transaction block

#

WHY??? I HAVE IT COMMITED!

carmine knot Jul 30, 2020, 2:04 AM

#

O wow im a idiot I just noticed I have the (address=input) twice when i only needs to call that before attempting to search

harsh osprey Jul 30, 2020, 2:09 AM

#

Hey, anyone use gino ORM?

#

I'm getting some odd af behaviour

#

I encountered it ages ago and Can't remember my fix

#

db = gino.Gino()
async def init():
    await db.set_bind(sql.PATH)
    print(type(db) == type(None))
    await asyncio.sleep(5)
    await db.create_all()
asyncio.get_event_loop().run_until_complete(init())

this is my code

#

C:\Users\samue\AppData\Local\Programs\Python\Python38-32\python.exe "F:/code/python webservers/addhq.xyz/main.py"
False
C:\Users\samue\AppData\Local\Programs\Python\Python38-32\lib\site-packages\sqlalchemy\sql\schema.py:4546: RuntimeWarning: coroutine 'GinoEngine._run_visitor' was never awaited
  bind._run_visitor(
RuntimeWarning: Enable tracemalloc to get the object allocation traceback
Traceback (most recent call last):
  File "F:/code/python webservers/addhq.xyz/main.py", line 23, in <module>
    asyncio.get_event_loop().run_until_complete(init())
  File "C:\Users\samue\AppData\Local\Programs\Python\Python38-32\lib\asyncio\base_events.py", line 616, in run_until_complete
    return future.result()
  File "F:/code/python webservers/addhq.xyz/main.py", line 22, in init
    await db.create_all(db.bind, checkfirst=True)
TypeError: object NoneType can't be used in 'await' expression

Process finished with exit code 1

This is the output

#

As you can see, db is clearly not None

#

it even says false

#

so I'm confused where this mystery await None is coming from

#

I uhh

#

I think I have lost a few braincells

#

its db.gino.create

#

not

#

facepalm

fallow elm Jul 30, 2020, 2:34 AM

#

ducky

torn sphinx Jul 30, 2020, 2:53 AM

#

@fluid wind Here is my code:
datetimedifference = datetime.datetime.now().strftime('%y-%m-%d %H:%M:%S.%f') - datetime.datetime.strptime(timestamp, '%y-%m-%d %H:%M:%S.%f')

And the same error occurred

#

ValueError: time data '2020-07-30 09:31:22.263898' does not match format '%y-%m-%d %H:%M:%S.%f'

#

How to fix that

fluid wind Jul 30, 2020, 2:55 AM

#

copy/paste format string from my answer

torn sphinx Jul 30, 2020, 2:56 AM

#

ok

#

TypeError: strptime() argument 1 must be str, not datetime.datetime```

fluid wind Jul 30, 2020, 3:01 AM

#

you keep using %y instead of %Y to parse year in a format YYYY

torn sphinx Jul 30, 2020, 3:06 AM

#

oh okay

fluid wind Jul 30, 2020, 3:07 AM

#

If it helps

>>> from datetime import datetime as tm
>>> fmt = '%Y-%m-%d %H:%M:%S.%f'
>>> now = tm.now().strftime(fmt)
>>> print(f"{now=}")
now='2020-07-29 23:05:16.419737'
>>> ts = tm.strptime(now, fmt)
>>> print(f"{ts=}")
ts=datetime.datetime(2020, 7, 29, 23, 5, 16, 419737)

haughty perch Jul 30, 2020, 3:07 AM

#

why am i getting this error?

Ignoring exception in on_command_completion
Traceback (most recent call last):
  File "C:\Users\ethan\AppData\Local\Programs\Python\Python38\lib\site-packages\discord\client.py", line 312, in _run_event
    await coro(*args, **kwargs)
  File "C:\Users\ethan\Desktop\Discord Bots\AuxBot-Python\Utilities\Purge Logs.py", line 35, in on_command_completion
    channel = get(ctx.guild.text_channels, id=self.guilds[str(ctx.guild.id)][1])
KeyError: '665811923471958018'

here is my code that logs when my purge command is completed: [https://pastebin.com/eeqGSPfP]
here is my code that sets a log channel it's in line 21 to 37: [https://pastebin.com/QtjVhpE9]
and here is my json file after i do the .logchannel command to set the channel: [https://pastebin.com/J0S09XEH]

all of this is in conjunction with discord.py i tried asking for help in their server and they said that it is not a dsicord.py issue i hope that this fits under this category/topic because it does involve json files if not sorry.

torn sphinx Jul 30, 2020, 3:08 AM

#

guild id must be a int @haughty perch

#

not str

haughty perch Jul 30, 2020, 3:08 AM

#

how do i set that?

torn sphinx Jul 30, 2020, 3:09 AM

#

channel = get(ctx.guild.text_channels, id=self.guilds[int(ctx.guild.id)][1])

haughty perch Jul 30, 2020, 3:10 AM

#

thank you so much i appreciate

torn sphinx Jul 30, 2020, 3:10 AM

#

You are welcome

#

@fluid wind Bro thx for your help a lot but
datetimedifference = datetime.datetime.now().strftime(fmt) - datetime.datetime.strptime(ts, fmt)

#

TypeError: strptime() argument 1 must be str, not datetime.datetime

haughty perch Jul 30, 2020, 3:18 AM

#

@torn sphinx i am still receiving the same error even after i applied your changes

torn sphinx Jul 30, 2020, 3:18 AM

#

May I see your code?

#

and the error

haughty perch Jul 30, 2020, 3:19 AM

#

it is in the paste bins in my past message

#

so is the error

torn sphinx Jul 30, 2020, 3:19 AM

#

also please ask at #discord-bots

haughty perch Jul 30, 2020, 3:19 AM

#

ok

fluid wind Jul 30, 2020, 3:33 AM

#

@torn sphinx can't tell you without seeing all the relevant code.
My guess you want to find a difference between two timestamps, if so, this is how you do it

from datetime import datetime as dt
from time import sleep

timestamp = dt.now()
sleep(5)
timedifference = dt.now() - timestamp
print(f"{timedifference.seconds=}")

Important thing, both arguments need to be either datetime or date.

torn sphinx Jul 30, 2020, 3:34 AM

#

Ok thx bro

warm vortex Jul 30, 2020, 3:50 AM

#

any noticeable difference between mongoDB Azure and mongoDB AWS? i'd read that AWS can be noticeably slower on medium loads

#

but not sure if that's true

haughty perch Jul 30, 2020, 5:04 AM

#

how do i update a json file after it has been changed without having to restart the bot

haughty perch Jul 30, 2020, 5:20 AM

#

?

tepid cradle Jul 30, 2020, 5:37 AM

#

Seems more like #discord-bots question than a database question

stoic geode Jul 30, 2020, 10:31 AM

#

hii

#

pyodbc question:
is there any way to get the data from cursor.execute() or at least keep the cursor opened until we loop through its rows of

tepid cradle Jul 30, 2020, 10:35 AM

#

I think you'd need to do cursor.fetchall()

stoic geode Jul 30, 2020, 10:37 AM

#

just tried that and still nothing

tepid cradle Jul 30, 2020, 10:37 AM

#

something like

cursor.execute("select * from ....")
rows = cursor.fetchall()
for row in rows:
    print(row)

stoic geode Jul 30, 2020, 10:37 AM

#

oww, thanks

torn sphinx Jul 30, 2020, 11:21 AM

#

sqlite3.IntegrityError: UNIQUE constraint failed: member_logs.user_id

#

I get this error

#

On my discord bot

#

This is my code

#

@bot.event
async def on_member_join(member : discord.Member):
    guild = member.guild
    channel = guild.get_channel(661770071328161802)
    welcome = guild.get_channel(664977282531852289)
    random_welcome = [f"Eyyyy! {member} just got here! How are ya doing today? Welcome to HydraJam Network! Wanna hear a joke? Alright, alright.\n**What's the best thing about Switzerland?** \n*'I don't know, but the flag is a big plus.'*\nHAHAHAAHHAHAHAHAH!\nFunny, right? Anywho, welcome to HydraJam Network!",
    f"Sup {member}. This is HydraJam Network, welcome. If you need anything, our staff are here to help. Wanna hear a joke? I got one, listen here.\n**What do you call a factory that sells good products?**\n*'A satisfactory.'*\nHAHAHAHHAHAHAH! How much did you laugh? Lol. Anywho, head over to [#661593585149542429](/guild/267624335836053506/channel/661593585149542429/) and make yourself feel like home. Again, contact a moderator if you need anything. Good day!"]
    embed=discord.Embed(
    color=0xea1216,
    description=f"**Username:** {member.mention}\n**ID:** {member.id}\n**Bot:** {member.bot}\n\n**Action:** Joined"
    )
    embed.set_footer(text="Welcome to HydraJam!")
    embed.set_author(url=member.avatar_url, name=f"{member}")
    embed.set_thumbnail(url=member.avatar_url)
    await channel.send(embed=embed)
    async with aiosqlite.connect("C:/HydraJamNetwork/database/hydra_info.db") as db:
        #await db.execute("CREATE TABLE member_logs (username str, user_id integer unique)")
        await db.execute("INSERT INTO member_logs (username, user_id) values (?, ?)",
                        (str(member), int(member.id)))
        await db.commit()
    await welcome.send(random.choice(random_welcome))

#

I use aiosqlite btw

#

Gotta rool

#

roll*

#

Peace out

burnt turret Jul 30, 2020, 1:27 PM

#

maybe you're testing it out yourself and as you already exist in the database, it sees that the member_id already exists -> but that column has a unique constraint
(just speculating though)

minor zealot Jul 30, 2020, 4:15 PM

#

i want to convert my database like that

#

ID NAME AGE ADDRESS SALARY

1 Paul 32 California 20000.0
2 Allen 25 Texas 15000.0
3 Teddy 23 Norway 20000.0
4 Mark 25 Rich-Mond 65000.0
5 David 27 Texas 85000.0
6 Kim 22 South-Hall 45000.0
7 James 24 Houston 10000.0

#

like i want to see it like that

#

if there is a way to do it please help me

fallow elm Jul 30, 2020, 4:22 PM

#

i'm not quite sure what you're asking

minor zealot Jul 30, 2020, 4:24 PM

#

i'm not quite sure what you're asking
@fallow elm the database its like a table i just want to show this table

fallow elm Jul 30, 2020, 4:26 PM

#

SELECT * FROM sometable?

#

i didn't mean that snarkily, i'm just not really sure what you're asking.

#

are you just asking how to query the table and see the data or do you know how to query and looking for it in a certain format or something else?

toxic tulip Jul 30, 2020, 5:30 PM

#

Is it good practice to use returning along with insert ?

#

Or does it just add some unnecessary data transfer

fluid wind Jul 30, 2020, 5:36 PM

#

It depends. Say, in Oracle, you can use a sequence to generate unique number, then pass it to insert statement. In this case, you don't need 'returning'. Or you can use sequence.nextval as part of your insert statement, and use 'returning' to get PK value back.

toxic tulip Jul 30, 2020, 5:42 PM

#

Got it, thank you chief

fallow elm Jul 30, 2020, 5:51 PM

#

i mean it depends on your needs. if you don't the the result, don't return it

manic geyser Jul 30, 2020, 6:09 PM

#

anyone know a way to connect to a sqlite db aand make statements without writting a python script to do it?

fallow elm Jul 30, 2020, 6:16 PM

#

sqliite command lne tool?

torn sphinx Jul 30, 2020, 6:22 PM

#

Is there a way to get a whole column of sqlite3 as a python list? Like all the entrys that 1 column has, getting it as list in python to iterate through

fallow elm Jul 30, 2020, 6:23 PM

#

items = [row[0] for row in rows]

manic geyser Jul 30, 2020, 6:25 PM

#

i'll try that, thanks gary

cosmic gulch Jul 30, 2020, 6:45 PM

#

quick question. Im using bcrypt lib and this is how im storing the password hash but when I check the password to see if valid in /login. I get encoding error

#

📎 unknown.png

#

📎 unknown.png

#

^ TypeError: Unicode-objects must be encoded before checking

fallow elm Jul 30, 2020, 6:47 PM

#

does user.password need to be encoded too?

cosmic gulch Jul 30, 2020, 6:50 PM

#

i tried that

#

that didnt work either

#

lol

#

when i encode user.password

#

i get invalid salt

fallow elm Jul 30, 2020, 6:58 PM

#

can you print(repr(user.password), type(user.password)). i'm thinking you have something funny going on there

#

i'm betting it's unicode but when you encode it it's corrupting it

misty zenith Jul 30, 2020, 7:08 PM

#

can anyone tell me what i'm doing wrong in this LIKE query:

song = cur.execute("SELECT Name FROM tracks WHERE Name LIKE ?", (x,)).fetchall()

fallow elm Jul 30, 2020, 7:09 PM

#

what's not working about it and what is x

misty zenith Jul 30, 2020, 7:09 PM

#

it's supposed to give all possible outcomes when x comes through the db more than once say the song is called 'walk on water', if x were to be 'walk' the query should give back the full title

#

does that make sense? i hate databases

fallow elm Jul 30, 2020, 7:11 PM

#

what is the contents of x

#

you need 'walk%'

misty zenith Jul 30, 2020, 7:11 PM

#

partially the name of the song

fallow elm Jul 30, 2020, 7:11 PM

#

if you want to do a prefix lookup

#

'%walk%' if you want to do a substring search

#

basically % is the wild card

misty zenith Jul 30, 2020, 7:12 PM

#

lemme check

#

holy crap

#

you genieus

#

thank you!!

fallow elm Jul 30, 2020, 7:14 PM

#

thumbsupio

stable violet Jul 31, 2020, 1:03 AM

#

Not sure what has changed or if I accidentally stepped on something, but restarted postgresql/PGAdmin 4, and in the past it showed a single application running on restart which was the postgres DB session. Now I've got autovacuum, WALWriter, Checkpointer, BGWriterHibernate, etc., and I'm constantly at a 1 transactions per second with spikes, even when the single application connecting to the DB isn't running. I've been googling and understand what some of these are, but can anyone explain why I would be at a constant 1 transactions even with the application off and why there would be spikes up to 3 transactions per second fairly frequently?

slender bolt Jul 31, 2020, 1:04 AM

#

How can i store a list of items in a column using sqlite3? I can't figure out which data type i need to use because there isnt a direct one for lists

stable violet Jul 31, 2020, 1:05 AM

#

https://stackoverflow.com/questions/3005231/how-to-store-array-in-one-column-in-sqlite3 looks like either store as a string and regex/split them when you need the data back or create a separate table

fluid wind Jul 31, 2020, 1:06 AM

#

@slender bolt you can concatenate list and store it as a text

#

or have an extra table that stores list items

#

depending on a list size

slender bolt Jul 31, 2020, 1:08 AM

#

Oh i see, how would dictionaries be stored as well? since there isnt a type for that. would it be the same way

fluid wind Jul 31, 2020, 1:08 AM

#

you can "json" dictionaries and store those as text

#

unless you need to search by dictionary elements in db

stable violet Jul 31, 2020, 1:08 AM

#

this is sounding like it could use its own table IMO

fluid wind Jul 31, 2020, 1:09 AM

#

if json structure is fix, could be a separate table as well

#

fixed

slender bolt Jul 31, 2020, 1:10 AM

#

right okay i see okay thanks

queen saffron Jul 31, 2020, 3:03 AM

#

i'm writing a python script that queries an api, pulls out what I want and publishes it to a discord webhook. After getting that all working ,I just realized I need to be storing this data so I can filter out data that has already been sent. Anyone have any suggestions on what would be the most efficent way of doing that?

#

def parseapi():
    apiresponse = queryapi()
    data = []

    for i in apiresponse:
        image = (imageEndpoint + i['chart'] + ".png")
        message = (i['ellipsis'])
        author = (i['author'])
        article_id = (i['article_id'])
        created_on = (i['created_on'])
        alerttype = (i['type'])
        starnum = (i['stars'])
        actionable = (i['actionable'])
        hottopic = (i['hot_topic'])
        stock_tag = (i['ellipsis']).split(" ")[0]
        industry = "Null"
        bullsnbears = "Null"
        for y in i['tags']:
            if y['type'] == "industry":
                industry = y['tag_name'].split("-")[1]
            if y['tag'] == "bullish":
                bullsnbears = "bullish"
            if y['tag'] == "bearish":
                bullsnbears = "bearish"
        
    
        sendtodiscord(message, image, author, article_id, created_on, actionable, alerttype, starnum, industry, hottopic, bullsnbears, stock_tag)
        sleep(5)

#

basically all im doing right now ... lol

tepid cradle Jul 31, 2020, 3:08 AM

#

I don't see the need to unpack the dictionary into individual variables when you can simply directly send the values from the dictionary itself

#

At least the ones where you're not making any transformation

vale lodge Jul 31, 2020, 3:10 AM

#

How long do you have possible repeats for? Is it just for the next message? ie., is it true to say that the next message may be the same as the previous but no messages before that one?

#

What I'm getting at @queen saffron is that unless you have strict requirements about not sending repeat messages from a long time ago, or on restarts or the ability to run multiple instances of this code. I'd try to do it in memory before adding a DB complexity.

queen saffron Jul 31, 2020, 3:14 AM

#

@tepid cradle hmm yeah I see what you mean

#

How long do you have possible repeats for? Is it just for the next message? ie., is it true to say that the next message may be the same as the previous but no messages before that one?
@vale lodge It depends what api endpoint I query, different endpoints might have results going back 2 weeks and I don't care to keep sending the same bunch of messages to the webhook, plus I have to figure out how to sort by the date it was published

#

like right now, the last message it sends from the endpoint is 2 weeks old, with the first one being 2 days

#

i was just starting to look on github for an example

#

i have 2 elasticsearch databases running on servers, ill just send it to one of them

#

never done that myself before though, only edited and setup projects I find on github lol

vale lodge Jul 31, 2020, 3:18 AM

#

My first instinct for these types of query patterns would be Mongo, Elasticsearch is a lot of indexing overhead that won't be used. But if you have it available it'll certainly do the job also.

queen saffron Jul 31, 2020, 3:19 AM

#

hmm ... okay mongo it is!

#

Would just create a unique index on a key, then just publish all the results from the api and let the duplicates get rejected?

vale lodge Jul 31, 2020, 3:29 AM

#

Sure, one way to do it is to split reading the API and sending discord messages into two processes. Just create records with a flag sent_to_discord set to False and then the sender can just grab all records to be sent and mark them as such.

#

The other way is to keep it as is. Once you've parsed the API, check for duplicates in the DB and drop them. Then send what's left and store it to the DB

warm vortex Jul 31, 2020, 4:33 AM

#

Hey, my mongoDB is overwriting my object in my object rather than creating a new object inside of the initial object is anyone here familiar enough to help?

#

this is the code

collection.update(
{"_id": message.author.id}, 
  {"$set": 
    {"stocks": 
      {stock: 
        {
        "amount": int(accountStockCheck + amount),
        "date of last purchase": f"{str(date.today())}",
        "price of last purchase": buying
        }
      }
    }  
  }
)

vale lodge Jul 31, 2020, 4:39 AM

#

I'm only marginally familiar with Mongo. But maybe if you're just trying to update a single document use update_one https://pymongo.readthedocs.io/en/stable/api/pymongo/collection.html?highlight=update#pymongo.collection.Collection.update_one

#

It seems like update() has a few different behaviors and that may be causing your problem

#

Not to mention it's deprecated

warm vortex Jul 31, 2020, 4:40 AM

#

initially i was using update_one : (

#

but it did the same thing

#

i must mention that "stock" is a variable linked to a string in my overarching code. so for example, it could be aapl or msft.

#

not sure if that might cause issues

granite nest Jul 31, 2020, 8:41 AM

#

is there a way to insert a value and auto_increment the option_id field when poll_id's are equal or do i have to count manually everytime I insert an option.

Solution :

all_options = 
SELECT * FROM options
WHERE poll_id = ?

INSERT INTO options (poll_id, len(all_options)+1, text) 
VALUES(?, option_id , ?)

Table :

📎 unknown.png

tepid cradle Jul 31, 2020, 9:23 AM

#

You need to create poll_id as an auto increment /serial data type in your Db. Then it will be populated automatically when you insert any value.
Which Db is this?

#

Different databases have different field names and slightly different semantics. So it's always a good idea to mention what you're using

rustic imp Jul 31, 2020, 9:48 AM

#

How do we backup databases in MySQL

vapid ruin Jul 31, 2020, 9:49 AM

#

i wanted to start learning how to use python with DB ... do you normally make the DB in mysql workshop then connect it with python or people tend to create it from python right away ?

fallow elm Jul 31, 2020, 9:53 AM

#

@rustic imp there's a command called mysqldump that comes with mysql you can look into for backups

rustic imp Jul 31, 2020, 9:55 AM

#

@fallow elm I know syntax I learned that from W3schools.com but it's not working

fallow elm Jul 31, 2020, 9:55 AM

#

@vapid ruin both are pretty common. if you're using a library for migrations and such you can defined your models in python and manage schema changes through python. if you're just writing raw sql i usually see people just manage schema outside of python. neither is really right or wrong

#

@rustic imp not working doesn't tell much. is there an error?

tepid cradle Jul 31, 2020, 10:00 AM

#

@vapid ruin It depends on the use case. If you are doing it just for learning purpose, you can probably use sqlite and create the Db from inside the python code itself. However, if you are setting up a project which requires a proper data store, you might want to set up a database separately with a reasonably planned schema and then connect to it through your python code.

vapid ruin Jul 31, 2020, 10:04 AM

#

@tepid cradle isee ,, but i don't really know much about creating DB on mysql . and i want to learn how to make a proper DB connect it with python is it a lot of learning to learn how to make it separately from python ?

tepid cradle Jul 31, 2020, 10:07 AM

#

It's actually not very difficult, the basic stuff. I would recommend getting started with MariaDB, it's an implementation of MySQL. It is available for all Linux, Windows, and Mac. Just download and get started. You'll find many tutorials for the inital setup.

#

From python, you can use MySQL Connector to connect with MariaDB

#

what OS do you have?

vapid ruin Jul 31, 2020, 10:08 AM

#

@tepid cradle windows 10

#

is MariaDB an alternative DB program than mysql ?

#

cause i have never heard about MariaDB .. so was wondering if there is a diff between them and which one do you think better to go for ..

#

i don't really want to dive crazy deep inside it ... i just finished python basics so i am looking to understand more modules .. do some very small projects to feel more confident in what i have as right now idon't feel like i can create much

tepid cradle Jul 31, 2020, 10:13 AM

#

Yeah, MariaDB is an open source database, made by the original developers of MySQL

#

It's very stable and highly compatible with MySQL drivers

#

It's easier to set up than MySQL, I feel. Mainly because you need to signup on Oracle website just to download MySQL Db

quartz storm Jul 31, 2020, 10:16 AM

#

im trying to follow a video tutorial on sqlite but i am using google colab to write my code. does anyone know how to get the database file to display the tables i have created?

vapid ruin Jul 31, 2020, 10:19 AM

#

@tepid cradle so basically both are so similar MariaDB and mysql but Maria is more easier to set up but aside from that both do the same thing right

tepid cradle Jul 31, 2020, 10:33 AM

#

Yes, they're same

#

try to set it up. You can alsways ask here if you get stuck at some point

quartz storm Jul 31, 2020, 11:18 AM

#

does anyone know how to visualize sqlite in google colab?

nocturne mica Jul 31, 2020, 11:40 AM

#

why VGG16 and VGG 19 show poor performance on Facial Emotion Recognition?

granite nest Jul 31, 2020, 12:35 PM

#

You need to create poll_id as an auto increment /serial data type in your Db. Then it will be populated automatically when you insert any value.
Which Db is this?
@tepid cradle sqlite3. however, I want to increment "option_id' not "poll_id" as in the querries I gave.
(poll_id, option_id) is unique btw
but option_id should be incremented when inserting a value which has the same poll_id as another poll
each poll has different options and I want to be able to add an option to an existing poll

fluid wind Jul 31, 2020, 12:42 PM

#

@granite nest what if you need to reorder options? 🙂

granite nest Jul 31, 2020, 12:42 PM

#

(i don't)

#

but i'd love to have a way to do it x) same with deleting options

fluid wind Jul 31, 2020, 12:43 PM

#

add position column to options, make poll_id+position unique, set autoincrement on option_id

#

you'll still have to calculate position manually though

granite nest Jul 31, 2020, 12:45 PM

#

I've had a hard time getting poll_id to be unique already :/
my naïve approach was this poll_id = max(self.polls.keys() or [-1]) + 1

fluid wind Jul 31, 2020, 12:45 PM

#

if you set it to autoincrement in db, you don't have to do it

crystal inlet Jul 31, 2020, 1:41 PM

#

Is there a way to do joins using sqlalchemy that doesn't return a list of tuples or but rather a single sqlalchemy object?

#

for instance SELECT * FROM person INNER JOIN address ON person.id=address.id LIMIT 200 returns a nice data set from both tables. The equivalent sqalchemy I came up with was data = session.query(person, address).join(address, address.id == person.id).limit(200).all() Which works but I have then reference data[#].Person.name & data[#].Address.zip

fluid wind Jul 31, 2020, 2:01 PM

#

@crystal inlet I'm not familiar with SQLAlchemy much, but it's ORM, so you're getting objects back. You can probably try execute() method to run SQL query directly.

minor zealot Jul 31, 2020, 2:07 PM

#

i need some help here as u can see i have a normal sqlite table but whene i try to fetch my data it just return the userid to me so how can i do here

📎 data.PNG

#

📎 false.PNG

crystal inlet Jul 31, 2020, 2:11 PM

#

@fluid wind well that's a super easy option I hadn't considered.

#

@minor zealot where's the actual SQL query?

#

one nice thing about sqlalchemy orm's is they have a __dict__ function that returns a dict of the data which is really easy to work with afterwards. And as far as I can tell there isn't a great way to merge these dicts without a buch of manual processing if I'm getting more than one object back.

fluid wind Jul 31, 2020, 2:21 PM

#

@crystal inlet what happens if you do {**dict1, **dict2} with data[#].Person and data[#].Address?

crystal inlet Jul 31, 2020, 2:25 PM

#

oh you're a genius

fluid wind Jul 31, 2020, 2:25 PM

#

@crystal inlet e.g. python class Person: pass class Address: pass p, a = Person(), Address() p.name = 'Bob' a.zip = '12345' m = {**p.__dict__, **a.__dict__} print(m) {'name': 'Bob', 'zip': '12345'}

crystal inlet Jul 31, 2020, 2:25 PM

#

ya

#

{**d.Person.__dict__, **d.Address.__dict__}

delicate fieldBOT Jul 31, 2020, 2:26 PM

#

:incoming_envelope: :ok_hand: applied mute to @torn sphinx until 2020-07-31 14:36 (9 minutes and 59 seconds) (reason: duplicates rule: sent 4 duplicated messages in 10s).

crystal inlet Jul 31, 2020, 2:26 PM

#

did exactly what I wanted

#

What does the **imply?

fluid wind Jul 31, 2020, 2:26 PM

#

dictionary unpacking

crystal inlet Jul 31, 2020, 2:28 PM

#

even more impressive is it's not merging duplicates. This is good to know

unborn sentinel Jul 31, 2020, 2:28 PM

#

!unmute 614032158897733642

delicate fieldBOT Jul 31, 2020, 2:28 PM

#

:incoming_envelope: :ok_hand: pardoned infraction mute for @torn sphinx.

fluid wind Jul 31, 2020, 2:30 PM

#

@crystal inlet Yes, second dictionary in this merge expression will overwrite values of the same keys in the first one.

crystal inlet Jul 31, 2020, 2:32 PM

#

is it a newish operation for dicts?

fluid wind Jul 31, 2020, 2:34 PM

#

Python 3.5, I think, so recent. dict.update works as well.

crystal inlet Jul 31, 2020, 2:37 PM

#

Cool. I don't write code very often I'm missing a lot of new things that make life easier.

#

Thanks for explaining

fluid wind Jul 31, 2020, 2:37 PM

#

YW

sinful condor Jul 31, 2020, 6:41 PM

#

I am trying to see what the highest numbers are in a json file and rank them how would I do that

fallow elm Jul 31, 2020, 6:41 PM

#

what is the format of the json?

sinful condor Jul 31, 2020, 6:43 PM

#

one sec ill copy and paste some of it because I dont know

#

{"449756487510654988/721149838573699154": {"xp": 203, "level": 5, "warnings": 0}, "469228384321536011/732567342525186111": {"xp": 30, "level": 3, "warnings": 1}, "603611557162647552/721149838573699154": {"xp": 5, "level": 1, "warnings": 0}, "663117661139632148/721149838573699154": {"xp": 88, "level": 4, "warnings": 0}, "449756487510654988/732567342525186111": {"xp": 53, "level": 3, "warnings": 0}, "530818947227058186/732567342525186111": {"xp": 0, "level": 1, "warnings": 0}, "599632471038099496/732567342525186111": {"xp": 1, "level": 1, "warnings": 0}, "482154322084233217/732567342525186111": {"xp": 0, "level": 1, "warnings": 0}}

#

but all in one line

meager wyvern Jul 31, 2020, 7:11 PM

#

Hello everyone, I need some assistance and reassurance that I executed a histogram chart correctly age_distribution = sns.distplot(df_ea['Age'], bins = 20) in which case it outputted this:

📎 unknown.png

harsh pulsar Jul 31, 2020, 7:11 PM

#

@meager wyvern did you mean to post this in #data-science-and-ml?

meager wyvern Jul 31, 2020, 7:12 PM

#

HECK

#

my apologies

harsh pulsar Jul 31, 2020, 7:12 PM

#

hah no problem

#

dont want your question to go unanswered

meager wyvern Jul 31, 2020, 7:12 PM

#

Yeah I don't want that happening

fallow elm Jul 31, 2020, 7:12 PM

#

@sinful condor and what do you want to sort by from that json file?

sinful condor Jul 31, 2020, 7:13 PM

#

like see what the highest 10 numbers are

fallow elm Jul 31, 2020, 7:14 PM

#

which field

sinful condor Jul 31, 2020, 7:14 PM

#

when "xp" is the category

#

or field

fallow elm Jul 31, 2020, 7:17 PM

#

so you can just load the json into a dict and then do something like this to sort it

>>> pprint.pprint(sorted(data.items(), key=lambda (key, value): value["xp"], reverse=True))
[(u'449756487510654988/721149838573699154',
  {u'level': 5, u'warnings': 0, u'xp': 203}),
 (u'663117661139632148/721149838573699154',
  {u'level': 4, u'warnings': 0, u'xp': 88}),
 (u'449756487510654988/732567342525186111',
  {u'level': 3, u'warnings': 0, u'xp': 53}),
 (u'469228384321536011/732567342525186111',
  {u'level': 3, u'warnings': 1, u'xp': 30}),
 (u'603611557162647552/721149838573699154',
  {u'level': 1, u'warnings': 0, u'xp': 5}),
 (u'599632471038099496/732567342525186111',
  {u'level': 1, u'warnings': 0, u'xp': 1}),
 (u'482154322084233217/732567342525186111',
  {u'level': 1, u'warnings': 0, u'xp': 0}),
 (u'530818947227058186/732567342525186111',
  {u'level': 1, u'warnings': 0, u'xp': 0})]

sinful condor Jul 31, 2020, 7:18 PM

#

but thats not what the json file always looks like its a level system

#

for a discord bot

fallow elm Jul 31, 2020, 7:18 PM

#

if you don't have a consistent data format i don't know what to say

#

i'm showing you a technique for sorting by value in a dictionary

#

that can be applied generally

#

if you're asking how to sort arbitrarily formatted data then i honestly have no idea but maybe i'm misunderstanding

stable violet Jul 31, 2020, 7:49 PM

#

When doing a simple SELECT transaction in postgresql, is it better to perform a commit or a rollback? I'm neither committing new information nor wanting to roll-back as no new data was added/ the query was successful, but I know I need to close the transaction so I'm not sure which I should be using and am having trouble finding an answer in google

harsh pulsar Jul 31, 2020, 8:23 PM

#

don't use a transaction @stable violet

stable violet Jul 31, 2020, 8:30 PM

#

Unless I’m misunderstanding, aren’t all SELECTS with a cursor object count as a transaction? Going by this link https://pynative.com/python-postgresql-select-data-from-table/

torn sphinx Jul 31, 2020, 8:56 PM

#

how do I import CSV into MongoDB

#

I literally know nothing

#

All I have is a CSV file that I don't want to manually upload

harsh pulsar Jul 31, 2020, 9:00 PM

#

@stable violet maybe at some low level? but that example doesn't even mention transactions

#

also this is bad style imo

#

i guess it makes sense in order to close the connection properly

#

this is bad

except (Exception, psycopg2.Error) as error :

it should just be

except psycopg2.Error as error :

stable violet Jul 31, 2020, 9:05 PM

#

Sorry about that, had to work from mobile for a minute, this link https://www.postgresql.org/message-id/4071D840.3040205@opencloud.com discusses that any query starts a transaction, so if autocommit is off then a commit() or rollback() is required to close that transaction. I was able to see this when working as well, I could see in PGAdmin4 after applying commits in my sql functions within my application, I was no longer having rollbacks where I previously was (I assume this was because autocommit was handling it for me) @harsh pulsar

torn sphinx Jul 31, 2020, 9:11 PM

#

how do I import CSV into MongoDB
@torn sphinx

stable violet Jul 31, 2020, 9:13 PM

#

https://kb.objectrocket.com/mongo-db/how-to-import-a-csv-into-mongodb-327 this link might help you

ObjectRocket

Data Pilot

How to Import a CSV into MongoDB | ObjectRocket

This is a step-by-step tutorial on how to import your data from a csv file into MongoDB.

torn sphinx Jul 31, 2020, 9:14 PM

#

uh

#

do I need to install the Mongo client

stable violet Jul 31, 2020, 9:14 PM

#

if you don't have access to a mongodb shell, it looks like you would need that, yes

#

I personally haven't worked with mongodb, I'm just googling your questions honestly

torn sphinx Jul 31, 2020, 9:16 PM

#

lol

#

I have my MongoDB running on my discord bot

#

so it syncs up and bans people who appear on it

gaunt pasture Jul 31, 2020, 9:32 PM

#

so all of the data points in my SQLite3 database are either "y" or "n" (except for one) and i want to check if ANY have "n". how would i do that?

#

so, basically, if any column = n, n = True

crystal aspen Jul 31, 2020, 9:51 PM

#

i have created a table in mysql how to retrieve primary key

torn sphinx Jul 31, 2020, 10:14 PM

#

hey i am trying to work with mysql right now and i want to subtract -1 from an int in a table. does someone know how to do that?

#

    async with aiosqlite.connect("C:/HydraJamNetwork/database/hydra_info.db") as db:
        #await db.execute("CREATE TABLE member_logs (username str, user_id integer unique)")
        await db.execute("INSERT INTO member_logs (username, user_id) values (?, ?)",
                        (str(member), int(member.id)))
        await db.commit()

Can anyone see any problems here? I can't remember the error and I have to login to my ALT account to test the cmd, anywho. If anyone has a good eye, can you see any problem?

#

Ping me if you have an answer.

#

Thanks.

opaque pumice Jul 31, 2020, 10:40 PM

#

anyone know why this doesnt work

📎 unknown.png

#

im trying to insert a column with a particular name

fluid wind Jul 31, 2020, 10:44 PM

#

you can't use binds for column names

opaque pumice Jul 31, 2020, 10:45 PM

#

@fluid wind how else would i make a column with author id

fluid wind Jul 31, 2020, 10:46 PM

#

Hmmm, alter table t1 add column author_id text?

opaque pumice Jul 31, 2020, 10:46 PM

#

@fluid wind how would i make the name of the column that user's id

somber hatch Aug 1, 2020, 1:15 AM

#

how can i make a "post" in flask-PyMongo the only thing that worked is:

@app.route('/post', methods=['POST'])
def add_star():
    users = mongo.db.Users
    users.insert({'uuid': "uuid", 'banned': False})

but how can i make the uuid not post uuid, but something else, that ive entered not in the code, im really bad at explaining but yeah

tepid cradle Aug 1, 2020, 2:23 AM

#

how would i make the name of the column that user's id
@opaque pumice why do you want a colum for each user id? That's poor design.
You create an entry for every user id in a user table and store related info on a separate table using foreign key relationship

#

hey i am trying to work with mysql right now and i want to subtract -1 from an int in a table. does someone know how to do that?
@torn sphinx update table set col1 = col1-1

fluid wind Aug 1, 2020, 2:32 AM

#

don't forget WHERE 🙂

#

is there esoteric-databases?

north ridge Aug 1, 2020, 2:47 AM

#

A JSON file is kinda like a database to me tbh

#

Is there any tips or a good website I can go to so I can learn how to read and write a JSON file?

#

||Mainly write actually||

torpid crater Aug 1, 2020, 3:29 AM

#

I think what @somber hatch means is that he wants to generate a new UUID for his mongo object. If that's true, then he doesn't need to include "uuid" because when he does the insert it will automatically add a uuid called _id, which is already a randomly generated thing called ObjectId

gaunt pasture Aug 1, 2020, 4:34 AM

#

my table was just working, but now it's all empty and isn't being written to at all

#

i'm really frustrated because it happened out of nowhere, i didn't change anything

#

📎 unknown.png

restive stone Aug 1, 2020, 7:19 AM

#

Error

  File "Breeze.py", line 37, in get_prefix
    prefix = client.cur.execute(f"SELECT prefix FROM prefix WHERE guild = {message.guild.id}").fetchall()
AttributeError: 'NoneType' object has no attribute 'id'

Code

async def get_prefix(client, message):
  client.cur.execute("CREATE TABLE IF NOT EXISTS prefix(guild INT, prefix TEXT DEFAULT '-')")
  prefix = client.cur.execute(f"SELECT prefix FROM prefix WHERE guild = {message.guild.id}").fetchall()
  if prefix == []:
    return "-"
  return prefix[0]

discord.py - SQLite3

fallow elm Aug 1, 2020, 7:23 AM

#

it's telling you that message.guild is None

craggy pawn Aug 1, 2020, 8:07 AM

#

any idea why its taking forever for simple commands to execute on an empty table?

#

Like, I am trying to drop an empty table and it acts like its starting to do it, and then just sits there

#

nvm.. I had an unclosed connection in a python script that was running

grim lotus Aug 1, 2020, 1:13 PM

#

Can i insert multiple values into 1 column ?

#

1. | 1234,4567,7890 |

Something like this so later i can column[index] to call thier values

upbeat lily Aug 1, 2020, 1:19 PM

#

Depends on the db. But you probably shouldn't

grim lotus Aug 1, 2020, 1:19 PM

#

Why

#

I m using psql

upbeat lily Aug 1, 2020, 1:21 PM

#

It breaks 1nf

#

First normal form

harsh pulsar Aug 1, 2020, 1:22 PM

#

Postgres supports array valued columns

#

There are a handful of use cases for it, but i agree, it's the kind of thing where if you have to ask you probably don't need it

grim lotus Aug 1, 2020, 1:23 PM

#

Is it not recommended to do ?

sinful condor Aug 1, 2020, 1:34 PM

#

I figured out how to find the highest thing in the xp column in my json file but is there a way to tell it also to only get the ones with a certain thing in it

#

like"whatever the user id is/whatever the guild id is"and figure only take the ones with that guild id

steel patrol Aug 1, 2020, 1:40 PM

#

pymongo/mongo db question, best way to clear/delete all array elements?

tepid cradle Aug 1, 2020, 1:45 PM

#

Is it not recommended to do ?
@grim lotus No. If you're using a relational database, try to format your tables in appropriate format. If you're having to store array as a value, that's a good indication that this particular info should go into a separate table with a foreign key relationship to this table. Unless you know there are just a handful of values, in which case you can just split them into multiple columns.
for instance, you are storing people's info where they can put up to three phone numbers. Just use three columns for phone numbers. But they can also add n number movies to their watchlist. Create a separate table for movie watchlist with user_id being the foreign key in movie watchlist table.

Arrays will make it very difficult, if not impossible, to perform join queries and summarise data. You should avoid it as far as possible.

grim lotus Aug 1, 2020, 1:48 PM

#

That's actually a big brain talk I'll take around 5 minutes to understand it , thanks a lot buds

grim lotus Aug 1, 2020, 2:20 PM

#

Will amount of tables + amount of column will decrease the Database speed

#

@grim lotus No. If you're using a relational database, try to format your tables in appropriate format. If you're having to store array as a value, that's a good indication that this particular info should go into a separate table with a foreign key relationship to this table. Unless you know there are just a handful of values, in which case you can just split them into multiple columns.
for instance, you are storing people's info where they can put up to three phone numbers. Just use three columns for phone numbers. But they can also add n number movies to their watchlist. Create a separate table for movie watchlist with user_id being the foreign key in movie watchlist table.

Arrays will make it very difficult, if not impossible, to perform join queries and summarise data. You should avoid it as far as possible.
@tepid cradle
Suppose i have a option to add as much as movies , so where I'll add / insert into what column for infinite data that will vary from user to user , some user may have 1 movie but another may have 6 , so what will i be doing in that case as you told not to use arrays

tepid cradle Aug 1, 2020, 2:34 PM

#

Will amount of tables + amount of column will decrease the Database speed
Of course it will. And so will arrays, probably more so. It's the Db admin's job to structure the data for peak performance, using partitioning, indexing, etc. But I'd say, unless you're dealing with data upwards of 100 GB, or serving an application used by thousands of users, you don't have to worry about speed yet.

so where I'll add / insert into what column for infinite data
You don't insert columns, you add rows. Here's an example:

users:
| user_name | email             |
| owl       | owl@discord.com   |
| gkrou     | gkrou@discord.com | 

watchlist:
| user_name | movie                    |
| gkrou     | Harry Potter             |
| gkrou     | Lord of the rings        |
| gkrou     | Star wars                |

Then when you want all the movies in my watchlist, you query using:

SELECT * FROM watchlist WHERE user_name = 'gkrou'

or perform a join query using:

select * from users
    inner join watchlist on users.user_name = watchlist.user_name

The second query will give you all the movies in the watchlist of all users

haughty perch Aug 1, 2020, 2:40 PM

#

Does anyone know why i am getting this error

Ignoring exception in on_message
Traceback (most recent call last):
  File "C:\Users\ethan\AppData\Local\Programs\Python\Python38\lib\site-packages\discord\client.py", line 312, in _run_event
    await coro(*args, **kwargs)
  File "C:\Users\ethan\Desktop\Discord Bots\AuxBot-Python\Utilities\Leveling.py", line 29, in on_message
    user = await self.client.pg_con.fetch('SELECT * FROM users WHERE user_id = $1 AND guild_id = $2', (author_id, guild_id,))
  File "C:\Users\ethan\AppData\Local\Programs\Python\Python38\lib\site-packages\asyncpg\pool.py", line 542, in fetch
    return await con.fetch(query, *args, timeout=timeout)
  File "C:\Users\ethan\AppData\Local\Programs\Python\Python38\lib\site-packages\asyncpg\connection.py", line 420, in fetch
    return await self._execute(query, args, 0, timeout)
  File "C:\Users\ethan\AppData\Local\Programs\Python\Python38\lib\site-packages\asyncpg\connection.py", line 1402, in _execute
    result, _ = await self.__execute(
  File "C:\Users\ethan\AppData\Local\Programs\Python\Python38\lib\site-packages\asyncpg\connection.py", line 1411, in __execute
    return await self._do_execute(query, executor, timeout)
  File "C:\Users\ethan\AppData\Local\Programs\Python\Python38\lib\site-packages\asyncpg\connection.py", line 1433, in _do_execute
    result = await executor(stmt, None)
  File "asyncpg\protocol\protocol.pyx", line 178, in bind_execute
  File "asyncpg\protocol\prepared_stmt.pyx", line 120, in asyncpg.protocol.protocol.PreparedStatementState._encode_bind_msg
asyncpg.exceptions._base.InterfaceError: the server expects 2 arguments for this query, 1 was passed
HINT:  Check the query against the passed list of arguments.

it's working with pgadmin4 using postgreSQL here is my code: [https://pastebin.com/aF99TWsY]

📎 unknown.png

brazen charm Aug 1, 2020, 2:46 PM

#

@haughty perch you dont do (arg1, arg2) with async pg

#

its just (QUERY, arg1, arg2, arg3, etc...)

grim lotus Aug 1, 2020, 2:47 PM

#

@tepid cradle u are awesome, is there a estimate at how much rows or amount of data you can see a decrease

haughty perch Aug 1, 2020, 2:55 PM

#

@brazen charm where about do i changed that in my code?

brazen charm Aug 1, 2020, 2:55 PM

#

i mean you have (QUERY, (arg1, arg2)) rn so change that lol

haughty perch Aug 1, 2020, 3:01 PM

#

what so like this await self.client.pg_con.execute('INSERT INTO users (users, guild_id, lvl, xp) VALUES (QUERY, ($1, $2 , 1, 0))', author_id, guild_id) i'm new to databases so idk what I'm doing lol

#

@brazen charm ^^^

brazen charm Aug 1, 2020, 3:03 PM

#

yeah

haughty perch Aug 1, 2020, 3:05 PM

#

well that just gave me the same error

brazen charm Aug 1, 2020, 3:20 PM

#

oh you've done this (QUERY, ($1, $2 , 1, 0)) which is wrong

#

i didnt notice that at first

burnt turret Aug 1, 2020, 3:43 PM

#

hi, how does one decide what database to use? I know how to use mongodb and mysql, and i always tend to use mongodb because im a bit partial to it, but when would it be preferred to use SQL over NoSQL databases?

somber hatch Aug 1, 2020, 3:46 PM

#

how can i change something from true to false in mongodb(with python ofc)

harsh pulsar Aug 1, 2020, 4:23 PM

#

@burnt turret use sql for tabular data

#

Usually its best to just use what you are familiar with

#

But unless you have complicated schemaless nested document data i dont see a good reason to use mongo over mysql personally

#

I just use postgres and sqlite for everything

burnt turret Aug 1, 2020, 4:26 PM

#

oh okay. thanks!

#

is the syntax for postgres very different from mysql? i havent really checked it out

brazen charm Aug 1, 2020, 4:29 PM

#

not really

#

Postgre is just generally better

#

it just does more, faster and safer

polar pelican Aug 1, 2020, 4:34 PM

#

can somebody tell me why ```
user = User.query.filter_by(username=username)
user.is_moderator = True
error = False
try:
db.session.commit()
db.session.close()
except Exception as e:
traceback.print_exc()
db.session.rollback()
error = True

#

does not update my database properly?

#

after successfully committing and closing the session and trying to navigate to a page that I have set up for moderators, locked by "if current_user.is_moderator:", current_user.is_moderator fails.

#

i am using flask-sqlalchemy with flask-login.

tepid cradle Aug 1, 2020, 5:16 PM

#

is the syntax for postgres very different from mysql? i havent really checked it out
@burnt turret Any database which uses SQL follows SQL standards. So the basics are all the same. There can be minor differences, like Postgres uses select * from table limit 10 where MS SQL uses select top 10 * from table, but largely it will be same.
The differences will be seen only when you start working with advanced stuff.

Regarding your original question, I mostly recommend SQL databases because of the analytical and summarisation capabilities they provide. The SQL standards itself is an advantage as by learning one, you're practically learning all SQL databases. Not so for NoSQL databases. NoSQL is good for data storage and retrieval. But if you need any kind of analytical capabilities, you should use SQL database.

tepid cradle Aug 1, 2020, 5:39 PM

#

delete the contents - update table_name set timing = null
delete the column - alter table table_name drop column timing

burnt turret Aug 1, 2020, 5:41 PM

#

thank you @tepid cradle !

grim lotus Aug 1, 2020, 6:00 PM

#

if i create a child table with foreign key

Will the collumn which is the foreign key column be updated automatically with null values to other columns

#

owl      | wol@gamaol.com
hyena.   | kek@gmail.dotcom```
If this is my parent table with the foreign key `username` in other next table

username | oder_id ```

Whenever I'll be inserting new rows to parent table with username and email will my child table also have a username column filled automatically ? And if so them what will be the value of order_id if not defined

tepid cradle Aug 1, 2020, 6:12 PM

#

No, it won't get filled automatically. Foreign key relationship is a constraint.
If col2 of table_b has a foreign key relationship with col1 of table_a, what this means is that you will not be able to enter any value in col2 of table_b which is not present in col1 of table_a.
It doesn't mean that the data will be auto-populated.

grim lotus Aug 1, 2020, 6:14 PM

#

Okay this means if i try to add username in table_number 2(child table ) which is not present in table _number 1(parent table) it won't add ?

tepid cradle Aug 1, 2020, 6:16 PM

#

correct. It will throw an error

grim lotus Aug 1, 2020, 6:18 PM

#

so foreign key will only provide as a barrier from adding invalid-value ,. Its just like managing 2 seperate tables ?

tepid cradle Aug 1, 2020, 6:19 PM

#

Yes. It's for maintaining data integrity.

#

The deletions can be cascaded though. So if you delete a user from the parent table, you can choose to delete all related records from the child table automatically

grim lotus Aug 1, 2020, 6:21 PM

#

Ohhh i seee so if i have a registration system
(Sign up with username)
i have to manually add the data and update them with 2 sql queries to each table(the child table and parent table)

tepid cradle Aug 1, 2020, 6:24 PM

#

At the time of sign up, you should only ask for essential info which goes into the user table. Anything which goes into a secondary table should Ideally be post sign-up

grim lotus Aug 1, 2020, 6:24 PM

#

Ohhhh i seee

#

And if i wanna store images ? Should i consider making A BYTEA or use something else ?

tepid cradle Aug 1, 2020, 6:26 PM

#

It might seem tedious now, but starts to make sense as you use it more and more.

#

I generally do not recommend storing images in a database at all. Use a file storage and store the pointer in the database.
I don't know what BYTEA is, but if you want to, you can store them as base64 strings

grim lotus Aug 1, 2020, 6:29 PM

#

you mean store the images somewhere else + and store the index of them in db to call them later accordingly?

#

BYTEA is a column type for storing bytes value

tepid cradle Aug 1, 2020, 6:30 PM

#

you mean store the images somewhere else + and store the index of them in db to call them later accordingly?
@grim lotus yes

#

BYTEA is a column type for storing bytes value
@grim lotus ok

grim lotus Aug 1, 2020, 6:30 PM

#

Thanks bud appreciate it GWcmeisterPeepoLove

#

GWcmeisterPeepoLove

tepid cradle Aug 1, 2020, 6:31 PM

#

👍

lime patio Aug 1, 2020, 10:17 PM

#

Someone can help me with format sql output in python?

ancient abyss Aug 1, 2020, 11:59 PM

#

        cursor.execute("""CREATE TABLE IF NOT EXISTS level_system(
                name TEXT,
                user_id BIGINT,
                guild_id BIGINT,
                level INT,
                xp INT
)""")
        conn.commit()

Is there something wrong here? I can't use commands without 'closing the transaction' or something

#

Ping me if your reply

#

It has to do with the 'xp'

#

Before it, it worked fine

tight moat Aug 2, 2020, 2:45 AM

#

Hello, can someone tell me how to get the average of occurrence of an event on a daily basis? I have this PostgreSQL table:

| user_id | date       |
|---------|------------|
| 71      | 2019-01-02 |
| 71      | 2019-01-02 |
| 71      | 2019-01-03 |
| 71      | 2019-01-04 |
| 71      | 2019-01-04 |
| 71      | 2019-01-05 |
| 71      | 2019-01-05 |
| 71      | 2019-01-06 |

And I want that to look like:

| user_id | avg_per_day  |
|---------|--------------|
| 71      | 2            |

haughty perch Aug 2, 2020, 3:08 AM

#

@brazen charm Referring back to this message [https://discordapp.com/channels/267624335836053506/342318764227821568/739140570043121765] what is the correct way to code that line?

Discord

Discord - A New Way to Chat with Friends & Communities

Discord is the easiest way to communicate over voice, video, and text. Chat, hang out, and stay close with your friends and communities.

tepid cradle Aug 2, 2020, 3:19 AM

#

Hello, can someone tell me how to get the average of occurrence of an event on a daily basis? I have this PostgreSQL table:
@tight moat It's actually more of a mathematics question, you need to think how to solve the problem, the query is not very complicated. Try this:

select user_id, count(date)/count(distinct date) as avg_per_day
    from table_name
    group by user_id

opaque pumice Aug 2, 2020, 4:17 AM

#

how do u make sqlite postgre?

fallow elm Aug 2, 2020, 5:25 AM

#

sqlite and postgres are different technologies

tepid cradle Aug 2, 2020, 5:44 AM

#

You can't make one database into another database, they are different pieces of software.
You can transfer the tables and data from one to the other. If your database is small, It's best to keep it simple. Create a database in Postgres (only database, not the tables), then use Pandas to read from sqlite and write to Postgres. Use this script:

from sqlalchemy import create_engine
import pandas as pd
sqlite_engine = create_engine("sqlite:///db.sqlite")
pg_engine = create_engine("postgresql://username:password@host:port/db")
with sqlite_engine.connect() as conn:
    res = conn.execute("select name from sqlite_master where type = 'table'") #reads table names
    tables = res.fetchall()
for table in [i[0] for i in tables]:
    df = pd.read_sql_table(table, sqlite_engine)
    df.to_sql(table, pg_engine, index=False, method='multi')

Keep in mind, this is only suitable for small databases and only if you're not well versed enough to recreate the tables. You can also use "select sql from sqlite_master where type = 'table'" to get the create table SQL query directly and recreate the tables in postgres using those.

sick dragon Aug 2, 2020, 8:44 AM

#

when selecting, should i commit, rollback or nothing?

rich trout Aug 2, 2020, 9:08 AM

#

nothing

#

!e ```py
import sqlite3
con = sqlite3.connect(":memory:")
c = con.cursor()
c.execute("CREATE TABLE people (id INT, name TEXT, address TEXT)")
c.execute("INSERT INTO people VALUES (?, ?, ?)", (1, "Bast", "unspecified"))
c.execute("INSERT INTO people VALUES (?, ?, ?)", (2, "Fors", "1111 nowhere lane"))
con.commit()
for i in range(15):
c.execute("SELECT * FROM people")
c.fetchall()

delicate fieldBOT Aug 2, 2020, 9:10 AM

#

@rich trout :warning: Your eval job has completed with return code 0.

[No output]

rich trout Aug 2, 2020, 9:13 AM

#

commit() indicates the database should ensure your data persists (IE: if there's an error after here, it shouldn't affect this). rollback() indicates there was an error up to/before here, and it should undo what it's working on and recover. SELECT doesn't change anything, so there's no need to persist anything.. because nothing's changed.. or rollback anything, for the same reason

vapid ruin Aug 2, 2020, 9:25 AM

#

why am i getting an error ? i still haven't done anything yet

📎 programming.PNG

#

📎 programming2.PNG

rich trout Aug 2, 2020, 9:28 AM

#

that's an error thrown on .connect(). This has some potential solutions for you: https://stackoverflow.com/questions/50557234/authentication-plugin-caching-sha2-password-is-not-supported

Stack Overflow

Authentication plugin 'caching_sha2_password' is not supported

I am trying to connect to a MySQL server with python connector. I created a new user lcherukuri with the authentication plugin mysql_native_password.

But I got the error
mysql.connector.errors.

#

You've probably installed the wrong mysql access library

vapid ruin Aug 2, 2020, 9:30 AM

#

iam sorry .. what do you mean installed wrong acess library cause i went thorugh mysql website and downloaded it and did same as videos do next next finish etc

#

@rich trout can you tell me what to do please ... like do i have to uninstall mysql and re do it again ?

rich trout Aug 2, 2020, 9:33 AM

#

Try passing auth_plugin='mysql_native_password' into your connect method first

vapid ruin Aug 2, 2020, 9:35 AM

#

hmmm still not working sir

📎 programming3.PNG

rich trout Aug 2, 2020, 9:36 AM

#

if you wouldn't mind running pip freeze in the terminal/console and telling me what the output is?

vapid ruin Aug 2, 2020, 9:38 AM

#

📎 programming4.PNG

#

this is what i got sir

rich trout Aug 2, 2020, 9:39 AM

#

Ah, there it is

#

You're looking for mysql-connector-python, not mysql-connector. Probably.

#

you can switch via pip uninstall mysql-connector and pip install mysql-connector-python

vapid ruin Aug 2, 2020, 9:40 AM

#

ohh really ...

#

i will try appreciate it sir

#

question ... what is the difference exactly ? iam simply trying to connect mysql with python i don't really know much tbh

sick dragon Aug 2, 2020, 9:41 AM

#

thanks @rich trout!

rich trout Aug 2, 2020, 9:42 AM

#

mysql-connector is the old (mysql v4.1) version of mysql-connector-python, which does the ".execute() to actually sending the commands to the server" part of the code

vapid ruin Aug 2, 2020, 9:43 AM

#

i did as you said by uninstalling the old version and installing the new one but still

📎 programming5.PNG

#

am i doing the import wrong ?

#

like does it have another way of importing it or using it

#

📎 programming6.PNG

rich trout Aug 2, 2020, 9:44 AM

#

That should be right

#

I think

vapid ruin Aug 2, 2020, 9:45 AM

#

this is the pip freeze picture is it good ?

rich trout Aug 2, 2020, 9:45 AM

#

yes

vapid ruin Aug 2, 2020, 9:45 AM

#

hmmm idk why i am still getting the error

rich trout Aug 2, 2020, 9:45 AM

#

You shouldn't need the auth_plugin line anymore

vapid ruin Aug 2, 2020, 9:45 AM

#

tried removing it but still same issue

rich trout Aug 2, 2020, 9:45 AM

#

can you send another picture?

vapid ruin Aug 2, 2020, 9:45 AM

#

yes sir

#

of the error ?

rich trout Aug 2, 2020, 9:46 AM

#

yes

vapid ruin Aug 2, 2020, 9:46 AM

#

📎 programming7.PNG

fallow elm Aug 2, 2020, 9:46 AM

#

that's a different error

#

it doesn't like your password

vapid ruin Aug 2, 2020, 9:46 AM

#

oih

rich trout Aug 2, 2020, 9:46 AM

#

Yes, that's progress!

vapid ruin Aug 2, 2020, 9:46 AM

#

wait lemme see

#

OMG it worked 😄 !!!!!

rich trout Aug 2, 2020, 9:47 AM

#

And better than most progress, it's also forwards

vapid ruin Aug 2, 2020, 9:47 AM

#

i don't know how to thank you man you're a legend

rich trout Aug 2, 2020, 9:47 AM

#

fireworks2

#

I'm just a volunteer, no need

#

glad to help

vapid ruin Aug 2, 2020, 9:47 AM

#

@rich trout you're a legend man

#

iam still new i hope one day become like you

fallow elm Aug 2, 2020, 9:48 AM

#

we were all where you are at some point. you're on your way

vapid ruin Aug 2, 2020, 9:49 AM

#

@fallow elm thank you for your help as well ... yeah i hope i can get better

ancient abyss Aug 2, 2020, 1:28 PM

#

https://codeshare.io/5wgX8D

#

When I take the bot offline, the xp resets to 5

#

I get it's because it's a global variable, but how would I use my database (postgresql) to stop that from happening?

proven arrow Aug 2, 2020, 1:33 PM

#

What is the purpose of the global XP value? What does it do?

ancient abyss Aug 2, 2020, 1:34 PM

#

It increments as users talk. If it wasn't global, and it was inside the on_message() function, it wouldn't increment

proven arrow Aug 2, 2020, 1:34 PM

#

But isnt the XP supposed to be per user, and different for each user?

ancient abyss Aug 2, 2020, 1:34 PM

#

It is

proven arrow Aug 2, 2020, 1:35 PM

#

So how are you controlling this with just a single global xp value?

ancient abyss Aug 2, 2020, 1:35 PM

#

📎 unknown.png

#

Example

proven arrow Aug 2, 2020, 1:37 PM

#

Well if you store the XP per user inside the DB, then you dont need a global value at all. You can just retreive the value that is stored inside the DB for that user, and then increment that.

ancient abyss Aug 2, 2020, 1:37 PM

#

Ok, I'll probably try that then. Not sure why that didn't cross my mind

proven arrow Aug 2, 2020, 1:38 PM

#

Also why are you making multiple queries to get the user_id and the level?

ancient abyss Aug 2, 2020, 1:38 PM

#

I'm not?

proven arrow Aug 2, 2020, 1:39 PM

#

You can just have a single query and get both the values, and you can add to this by also retreiving the XP as well.

#

Well this is what you have currently:

        cursor.execute('SELECT user_id FROM level_system WHERE user_id = %s AND guild_id = %s',
                       (user_id, guild_id))
        result = cursor.fetchone()

        cursor.execute('SELECT level FROM level_system WHERE user_id = %s AND guild_id = %s',
                       (user_id, guild_id))
        result2 = cursor.fetchone()

ancient abyss Aug 2, 2020, 1:39 PM

#

Ah

#

One is for level, one is for user id

proven arrow Aug 2, 2020, 1:39 PM

#

Yeah but you can get all this info with a single query

ancient abyss Aug 2, 2020, 1:39 PM

#

So, I have those for, if result is None, insert

#

I thought you could, but anytime I try, it never works

proven arrow Aug 2, 2020, 1:41 PM

#

cursor.execute('SELECT user_id, level, xp FROM level_system WHERE user_id = %s AND guild_id = %s',
                       (user_id, guild_id))
        result = cursor.fetchone()

This would work.

ancient abyss Aug 2, 2020, 1:41 PM

#

Huh, alright

proven arrow Aug 2, 2020, 1:44 PM

#

And last piece of advice. It doesn't look like you are using an async library for your DB. So making these requests inside your on_message for every single message will break your bot pretty quick because of blocking. Consider using an async lib, asyncpg is very good for postgres.

ancient abyss Aug 2, 2020, 1:44 PM

#

Ughh, everyone says that! I can not use asyncpg, it's too different and will not work for me

#

It will not work

hot geyser Aug 2, 2020, 1:44 PM

#

Anyone know anywhere to get up to data quality data sets for sport leagues that would be ideal for a predictive model to guess over under for sports books

ancient abyss Aug 2, 2020, 1:44 PM

#

I've tried

proven arrow Aug 2, 2020, 1:48 PM

#

Well people are saying this for a reason, because it will break your bot, especially inside on_message of your bot. Some people will also say not to use DB requests for things like levelling inside on_message and instead use like a cache to save resources and unnecessary work, even with an async lib, so let alone the non-async.

#

But if you need assistance with setting it up or using it then you can always ask for help. It shouldn't take too long getting used to, and its kind of the same syntax, just a little different in the sense you have to await things.

ancient abyss Aug 2, 2020, 1:49 PM

#

Ok. I guess i'll try it once more. I'll be right back, I'm gonna import asyncpg and show what happens

#

Ha! Here's a perfect example!

#

📎 unknown.png

#

Do i need to make just one giant function for it?

proven arrow Aug 2, 2020, 1:52 PM

#

Well that is basic async syntax, and not related to the DB actually. It means whenever you do await it must be inside an async function

ancient abyss Aug 2, 2020, 1:52 PM

#

Ok, so I need to make a giant one and put all the code under it?

proven arrow Aug 2, 2020, 1:52 PM

#

Well ideally how you would want it is, make a single connection and then use this connection throughout your program/code.

#

Since you are using discord bot, you may want to assign the connection to your bot instance. Then you can easily access the connection in other parts of your code if you were to use things like cogs etc.

ancient abyss Aug 2, 2020, 1:54 PM

#

Assign it? What do you mean?

#

Now when I run it, nothing happens

#

it just stops

proven arrow Aug 2, 2020, 1:55 PM

#

# This below is your bot instance. It is stored inside the variable called "bot"
bot = commands.Bot(command_prefix=...)

# This function will setup your bot connection, and assign it to the variable bot.
async def setup_bot(bot):
    await bot.wait_until_ready()

    # Connect to db
    bot.my_db = await asyncpg.create_pool(user=db_username, password=db_pwd, database=db_name, host=db_host)

bot.loop.create_task(setup_bot(bot))

ancient abyss Aug 2, 2020, 1:57 PM

#

I have mine to client, not bot

proven arrow Aug 2, 2020, 1:57 PM

#

Well then just change it, I dont know what your code looks like.

ancient abyss Aug 2, 2020, 1:57 PM

#

Would the param for the function be client?

proven arrow Aug 2, 2020, 1:58 PM

#

The function parameter is local, and you can name it as you like. But you can just replace wherever it says bot with client if that makes it easier for you.

ancient abyss Aug 2, 2020, 2:01 PM

#

unresolved reference bot

#

📎 unknown.png

proven arrow Aug 2, 2020, 2:02 PM

#

replace it with client

ancient abyss Aug 2, 2020, 2:02 PM

#

Tried. I'll show what happens then

#

📎 unknown.png

proven arrow Aug 2, 2020, 2:03 PM

#

Yeah thats fine, its just your IDE it doesnt know.

ancient abyss Aug 2, 2020, 2:04 PM

#

Oh

proven arrow Aug 2, 2020, 2:05 PM

#

Also note above I have used connection pool. This makes it more efficient when you make the requests. It keeps multiple connections alive with the DB. So whenever you need to use the DB, instead of creating a new connection which takes resources, you can just get one from the pool.

ancient abyss Aug 2, 2020, 2:05 PM

#

So, it runs now, woo, but what is the asnycpg version of the psycopg2 cursor?

#

And that makes sense

proven arrow Aug 2, 2020, 2:05 PM

#

https://magicstack.github.io/asyncpg/current/index.html
The docs have good examples

ancient abyss Aug 2, 2020, 2:05 PM

#

Ok

#

conn not defined

📎 unknown.png

#

But conn is defined

📎 unknown.png

proven arrow Aug 2, 2020, 2:08 PM

#

https://discordapp.com/channels/267624335836053506/342318764227821568/739481525908799518 Read this again, and the comments I added

#

Your connection should be stored in the bot instance.

ancient abyss Aug 2, 2020, 2:10 PM

#

is it though..

#

client = commands.Bot(command_prefix=[".", "</>"])
client.remove_command('help')


async def setup_bot(client):
    await client.wait_until_ready()
    conn = await asyncpg.connect(database=DB_NAME, user=DB_USER,
                                 password=DB_PASS, host=DB_HOST, port=DB_PORT)

    client.my_db = await asyncpg.create_pool(user=DB_USER, password=DB_PASS, database=DB_NAME, host=DB_HOST)

proven arrow Aug 2, 2020, 2:10 PM

#

Why do you have 2 connections?

ancient abyss Aug 2, 2020, 2:10 PM

#

oh

#

didn't see that

#

Removed it, still have the error

proven arrow Aug 2, 2020, 2:11 PM

#

Well now the error is because you are missing the basic OOP concepts.

#

Are you aware of Object Oriented Programming?

ancient abyss Aug 2, 2020, 2:12 PM

#

I think so

#

Like, classes?

proven arrow Aug 2, 2020, 2:12 PM

#

That just one part of it.

ancient abyss Aug 2, 2020, 2:13 PM

#

Well, what am I missing?

stark vessel Aug 2, 2020, 2:13 PM

#

like inheritance

#

function overriding

proven arrow Aug 2, 2020, 2:14 PM

#

Your connection/pools to the database is stored inside client.my_db. You connect once and you never need to connect again now. You just need to call this connection variable whenever you want to interact with the DB

ancient abyss Aug 2, 2020, 2:14 PM

#

Oh

#

What do you mean by call it? I know what you mean, but where?

#

Where would I put it there for example?

📎 unknown.png

proven arrow Aug 2, 2020, 2:17 PM

#

Is that inside a cog or inside the main file where you make the DB?

ancient abyss Aug 2, 2020, 2:17 PM

#

Main

proven arrow Aug 2, 2020, 2:20 PM

#

    # Take a connection from the pool which is stored in the variable "client.my_db"
    async with client.my_db.acquire() as connection:
        # Open a transaction.
        async with connection.transaction():
            # Run the query
            await connection.execute("YOUR QUERY")

Note how this uses await, so it needs to be inside an async function

ancient abyss Aug 2, 2020, 2:20 PM

#

wow

proven arrow Aug 2, 2020, 2:21 PM

#

Its not always necessary to use a transaction but in this example I did so you can use the same example later on with your other queries.

ancient abyss Aug 2, 2020, 2:22 PM

#

That is way too much

#

Something like that?

📎 unknown.png

#

I'm honestly on the verge of dropping this and saying never mind to it

#

Yeah I appreciate the time and help, but this ain't it

#

This is too advanced for me, call me a moron, whatever

proven arrow Aug 2, 2020, 2:31 PM

#

Uh ok, but its not super advanced. You just need to take some time to try to understand what's going on. Trying to replace an lib when you are unaware of certain concepts can be daunting, but I suggest you to play around with it in a new project, instead of trying to fit it directly into your current code, so you get the idea of how it works. But you will have to eventually come back to an async lib for the DB, even if you disregard it now. The library you use for your bot is based around async so its kind of a must.

gaunt meadow Aug 2, 2020, 2:37 PM

#

how to make a time out of aiosqlite

tight moat Aug 2, 2020, 2:52 PM

#

@tepid cradle didn't had the time to thank you yesterday, I had a huge headache lol, thanks!

tepid cradle Aug 2, 2020, 2:54 PM

#

@tepid cradle didn't had the time to thank you yesterday, I had a huge headache lol, thanks!
@tight moat Don't even know which answer you're thanking me for, but you're welcome 😅

#

Oh. I wrote that in the morning, i.e., today morning for me. Lol..
Did it work? I hadn't tested it because I didn't have any dataset like that and was too lazy to create and test.

tight moat Aug 2, 2020, 3:09 PM

#

Hell yeah it worked hahaha don't worry, it's what I'm looking for 😂

worthy lark Aug 2, 2020, 3:19 PM

#

Hello. What is the most popular MySQL library for Python?

tepid cradle Aug 2, 2020, 3:21 PM

#

Hello. What is the most popular MySQL library for Python?
@worthy lark https://pypi.org/project/mysql-connector-python/

PyPI

mysql-connector-python

MySQL driver written in Python

worthy lark Aug 2, 2020, 3:23 PM

#

@tepid cradle Thanks!

hasty juniper Aug 2, 2020, 3:28 PM

#

async with aiosqlite.connect('data/db/Mute.sql') as db:
        await db.execute("""CREATE TABLE IF NOT EXISTS users (UserID BIGINT, User TEXT DEFAULT 'YES')""")
        await db.commit()

```what the ????????????????```cmd
    )""")
    ^
SyntaxError: invalid syntax

#

i fix that

#

Nwm

#

db

mild vector Aug 2, 2020, 5:20 PM

#

Hey !
I want to create an Inventory system with asyncpg and I don't know what is the best way to do that

#

CREATE TABLE IF NOT EXISTS profiles(
                    member_id text PRIMARY KEY,
                    banner text,
                    inventory (???)
                );

#

What type should I use ?

nimble pendant Aug 2, 2020, 5:22 PM

#

pls help on #algos-and-data-structs

tepid cradle Aug 2, 2020, 5:31 PM

#

What type should I use ?
@mild vector Depends on what kind of values it will contain. Can you give an example of that?

mild vector Aug 2, 2020, 5:33 PM

#

There is a table for banners that contains the banner_id, banner_url, banner_name
Same for titles (title_id, title_name, title_desc)
And in the profiles table in the user inventory there is a banners category and a titles category with the ids of the banners and titles the user has

#

Sorry if I don't explain well I don't speak english very well

#

@tepid cradle

tepid cradle Aug 2, 2020, 5:35 PM

#

I still don't know what value the inventory column will hold

mild vector Aug 2, 2020, 5:35 PM

#

ints

#

But like JSON

#

inventory : {"banners": {1, 2, 3, ...}, "titles": {1, 2, 3, ...}}

tepid cradle Aug 2, 2020, 5:36 PM

#

what type of db is it?

mild vector Aug 2, 2020, 5:36 PM

#

PostgreSQL

tepid cradle Aug 2, 2020, 5:43 PM

#

PostgreSQL supports JSON. See this: https://www.postgresqltutorial.com/postgresql-json/
But I'd advise against using it this way. The point of relational database is lost by storing JSON in them. Try splitting it into multiple columns and rows, basically make it tabular

PostgreSQL JSON Tutorial

We show you how to work with PostgreSQL JSON data and introduce you to some important PostgreSQL JSON operators and functions for handling JSON data.

mild vector Aug 2, 2020, 5:44 PM

#

Okay

#

Thanks

hasty juniper Aug 2, 2020, 6:47 PM

#

@bot.event
async def on_ready():
    channel = bot.get_channel(739094007195500606)
    await channel.send(f'''Now Bot Online, latency: {round(bot.latency * 1000)}ms''')

    bot.load_extension(f"Moderation.mode")
    bot.load_extension(f"data.database_load")

    async with aiosqlite.connect('data/db/Mute.sql') as db:
        await db.execute("""CREATE TABLE IF NOT EXISTS users(UserID BIGINT, User TEXT DEFAULT 'YES')""")
        await db.commit()

    for guild in bot.guilds:
        for member in guild.members:
            async with aiosqlite.connect('data/db/Mute.sql') as db:
                cur = await db.cursor()
                await cur.execute(f"SELECT UserID FROM users WHERE UserID = {member.id}")
                res = await cur.fetchone()
                if res is "None":
                      await cur.execute('INSERT INTO users (UserID) VALUES (?)', (member.id))
                      await cur.commit()






@bot.command()
async def testDB(ctx):
    async with aiosqlite.connect('data/db/Mute.sql') as db:
        cur = await db.cursor()
        await cur.execute(f"""SELECT UserID FROM users WHERE UserID = {ctx.author.id}""").fetchone()
        res = await cur.fetchone()
        await ctx.send(res)
``` ```cmd
E:\ПРоэкты ПАЙТОН\HACKsCC+\main.py:47: RuntimeWarning: coroutine 'Cursor.execute' was never awaited
  await cur.execute(f"""SELECT UserID FROM users WHERE UserID = {ctx.author.id}""").fetchone()
  File "E:\ПРоэкты ПАЙТОН\HACKsCC+\main.py", line 47, in testDB
    await cur.execute(f"""SELECT UserID FROM users WHERE UserID = {ctx.author.id}""").fetchone()
AttributeError: 'coroutine' object has no attribute 'fetchone'

gentle silo Aug 2, 2020, 7:13 PM

#

calling a coroutine just returns a coroutine object, it doesn't return the result of that coroutine

#

you have to await that returned coroutine to get the result

hasty juniper Aug 2, 2020, 7:22 PM

#

@gentle silo

📎 unknown.png

#

i do that but

#

noring exception in command testDB:
Traceback (most recent call last):
  File "C:\Users\NotToxic89\AppData\Local\Programs\Python\Python37\lib\site-packages\discord\ext\commands\core.py", line 83, in wrapped
    ret = await coro(*args, **kwargs)
  File "E:\ПРоэкты ПАЙТОН\HACKsCC+\main.py", line 49, in testDB
    await ctx.send(res)
  File "C:\Users\NotToxic89\AppData\Local\Programs\Python\Python37\lib\site-packages\discord\abc.py", line 870, in send
    data = await state.http.send_message(channel.id, content, tts=tts, embed=embed, nonce=nonce)
  File "C:\Users\NotToxic89\AppData\Local\Programs\Python\Python37\lib\site-packages\discord\http.py", line 225, in request
    raise HTTPException(r, data)
discord.errors.HTTPException: 400 Bad Request (error code: 50006): Cannot send an empty message

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "C:\Users\NotToxic89\AppData\Local\Programs\Python\Python37\lib\site-packages\discord\ext\commands\bot.py", line 892, in invoke
    await ctx.command.invoke(ctx)
  File "C:\Users\NotToxic89\AppData\Local\Programs\Python\Python37\lib\site-packages\discord\ext\commands\core.py", line 797, in invoke
    await injected(*ctx.args, **ctx.kwargs)
  File "C:\Users\NotToxic89\AppData\Local\Programs\Python\Python37\lib\site-packages\discord\ext\commands\core.py", line 92, in wrapped
    raise CommandInvokeError(exc) from exc
discord.ext.commands.errors.CommandInvokeError: Command raised an exception: HTTPException: 400 Bad Request (error code: 50006): Cannot send an empty message

gentle silo Aug 2, 2020, 7:23 PM

#

So what is that error telling you?

hasty juniper Aug 2, 2020, 7:23 PM

#

empty

gentle silo Aug 2, 2020, 7:23 PM

#

what is empty

hasty juniper Aug 2, 2020, 7:24 PM

#

@bot.command()
async def testDB(ctx):
    async with aiosqlite.connect('data/db/Mute.sql') as db:
        cur = await db.cursor()
        await cur.execute(f"""SELECT UserID FROM users WHERE UserID = {ctx.author.id}""")
        res = await cur.fetchone()
        await ctx.send(res)

gentle silo Aug 2, 2020, 7:24 PM

#

?

hasty juniper Aug 2, 2020, 7:24 PM

#

i need to output value from db

#

SELECT UserID FROM users WHERE UserID = {ctx.author.id}

#

@gentle silo

#

how

fallow elm Aug 2, 2020, 7:30 PM

#

please please please don't use interpolation or string formatting to build your sql query. do

await cur.execute(f"""SELECT UserID FROM users WHERE UserID = ?""", (ctx.author.id,))

#

the aiosqlite docs aren't great because they don't explain this but the standard lib docs explain it https://docs.python.org/3/library/sqlite3.html

#

Usually your SQL operations will need to use values from Python variables. You shouldn’t assemble your query using Python’s string operations because doing so is insecure; it makes your program vulnerable to an SQL injection attack (see https://xkcd.com/327/ for humorous example of what can go wrong).

Instead, use the DB-API’s parameter substitution. Put ? as a placeholder wherever you want to use a value, and then provide a tuple of values as the second argument to the cursor’s execute() method. (Other database modules may use a different placeholder, such as %s or :1.) For example:

# Never do this -- insecure!
symbol = 'RHAT'
c.execute("SELECT * FROM stocks WHERE symbol = '%s'" % symbol)

# Do this instead
t = ('RHAT',)
c.execute('SELECT * FROM stocks WHERE symbol=?', t)
print(c.fetchone())

# Larger example that inserts many records at a time
purchases = [('2006-03-28', 'BUY', 'IBM', 1000, 45.00),
             ('2006-04-05', 'BUY', 'MSFT', 1000, 72.00),
             ('2006-04-06', 'SELL', 'IBM', 500, 53.00),
            ]
c.executemany('INSERT INTO stocks VALUES (?,?,?,?,?)', purchases)

bold geode Aug 2, 2020, 9:33 PM

#

Reminds me I need to try sql injection

lunar tapir Aug 3, 2020, 12:19 AM

#

so i have this level system and when i do the ,level command nothing happens
no error nothing
heres the code https://paste.pythondiscord.com/ociwaxiwuv.py

#

ping me with reply

haughty perch Aug 3, 2020, 3:42 AM

#

Referring back to this message [https://discordapp.com/channels/267624335836053506/342318764227821568/739140570043121765] what is the correct way to code that line?

Discord

Discord - A New Way to Chat with Friends & Communities

Discord is the easiest way to communicate over voice, video, and text. Chat, hang out, and stay close with your friends and communities.

#

??????????

rich trout Aug 3, 2020, 4:19 AM

#

not sure why you put in QUERY

#

Just ($1, $2, 1, 0) should work I'd think

haughty perch Aug 3, 2020, 5:58 AM

#

I still get the same error?

Ignoring exception in on_message
Traceback (most recent call last):
  File "C:\Users\ethan\AppData\Local\Programs\Python\Python38\lib\site-packages\discord\client.py", line 312, in _run_event
    await coro(*args, **kwargs)
  File "C:\Users\ethan\Desktop\Discord Bots\AuxBot-Python\Utilities\Leveling.py", line 29, in on_message
    user = await self.client.pg_con.fetch('SELECT * FROM users WHERE user_id = $1 AND guild_id = $2', (author_id, guild_id,))
  File "C:\Users\ethan\AppData\Local\Programs\Python\Python38\lib\site-packages\asyncpg\pool.py", line 542, in fetch
    return await con.fetch(query, *args, timeout=timeout)
  File "C:\Users\ethan\AppData\Local\Programs\Python\Python38\lib\site-packages\asyncpg\connection.py", line 420, in fetch
    return await self._execute(query, args, 0, timeout)
  File "C:\Users\ethan\AppData\Local\Programs\Python\Python38\lib\site-packages\asyncpg\connection.py", line 1402, in _execute
    result, _ = await self.__execute(
  File "C:\Users\ethan\AppData\Local\Programs\Python\Python38\lib\site-packages\asyncpg\connection.py", line 1411, in __execute
    return await self._do_execute(query, executor, timeout)
  File "C:\Users\ethan\AppData\Local\Programs\Python\Python38\lib\site-packages\asyncpg\connection.py", line 1433, in _do_execute
    result = await executor(stmt, None)
  File "asyncpg\protocol\protocol.pyx", line 178, in bind_execute
  File "asyncpg\protocol\prepared_stmt.pyx", line 120, in asyncpg.protocol.protocol.PreparedStatementState._encode_bind_msg
asyncpg.exceptions._base.InterfaceError: the server expects 2 arguments for this query, 1 was passed
HINT:  Check the query against the passed list of arguments.

@rich trout

rich trout Aug 3, 2020, 5:58 AM

#

('SELECT * FROM users WHERE user_id = $1 AND guild_id = $2', (author_id, guild_id,))

#

dont wrap the aprameters in their own tyuple for asyncpg

#

execute(SQL, param1, param2)

haughty perch Aug 3, 2020, 5:59 AM

#

could you fix the line idk how to

#

@rich trout ^

tepid cradle Aug 3, 2020, 7:09 AM

#

Try it on your own @haughty perch , Bast has told you what's wrong.
Try running the queries independently using sample values in terminal/Jupyter Notebook so that you can quickly iterate and check whether it's working or not.

haughty perch Aug 3, 2020, 7:11 AM

#

bruh just want some fukin help because i have no idea what I'm doing when it comes to databases

tepid cradle Aug 3, 2020, 7:17 AM

#

And I'm assuming you want to get past the stage where you have no idea to a stage where you know what you're doing.
To get to that stage, you need to learn to figure out the problem. Us writing your code will not teach you.
Bast pointed out the error, you just need to read his message, compare it to your code and correct the error

#

Actually, it's such a common thing that it's there as a tag

#

!ask

delicate fieldBOT Aug 3, 2020, 7:23 AM

#

Asking good questions will yield a much higher chance of a quick response:

• Don't ask to ask your question, just go ahead and tell us your problem.
• Don't ask if anyone is knowledgeable in some area, filtering serves no purpose.
• Try to solve the problem on your own first, we're not going to write code for you.
• Show us the code you've tried and any errors or unexpected results it's giving.
• Be patient while we're helping you.

You can find a much more detailed explanation on our website.

tepid cradle Aug 3, 2020, 7:23 AM

#

See, point 3

rich trout Aug 3, 2020, 7:23 AM

#

!ban 327745755789918208 no, we won't write your code for you, and no, that was not an appropriate thing to say here

delicate fieldBOT Aug 3, 2020, 7:23 AM

#

failmail :ok_hand: applied ban to @haughty perch permanently.

tepid cradle Aug 3, 2020, 7:32 AM

#

Lol.. Some people just make it difficult to help them. If looking at other people's code helps learn then that is already available as examples in the documentation.

near cradle Aug 3, 2020, 7:35 AM

#

sigh.

tepid cradle Aug 3, 2020, 7:35 AM

#

I wish the guy spent the same amount of energy on trying to fix his code 🤦

fallow elm Aug 3, 2020, 7:36 AM

#

Poor lemon having to be pinged with that blobsad

#

Very aggro nasty language

olive scaffold Aug 3, 2020, 8:46 AM

#

can someone recommend me a good free DB? I mean its gonna get super big or anything, anything that can do the job?

proven arrow Aug 3, 2020, 8:48 AM

#

Well all databases are free. What’s your use case?

olive scaffold Aug 3, 2020, 8:48 AM

#

Im actually planning on using it for a discord bot to store some info on how many messages people have sent and stuff. Currently im using a JSON file, and i wanna get started using a "proper" DB

#

its for personal use

#

on my friends server

#

so its not gonna get big or anything

#

how long have you been typing for?

#

lmao jk take your time

proven arrow Aug 3, 2020, 8:51 AM

#

Ok then sqlite will get you the job done for a single server and takes minimal setup. The most important thing to consider when making bots in terms of DB is to make sure you use an async driver for it.

#

aiosqlite is the async driver for sqlite

olive scaffold Aug 3, 2020, 8:51 AM

#

hm

#

are there anything you know of to help me setup? Like a youtube vid or the docs or something?

#

and is it like JSON?

#

i mean does it convert arrays into lists and does all that magic?

#

and dictionaries to.. dicts

#

ok i found a video

#

k 👌

tepid cradle Aug 3, 2020, 8:55 AM

#

Well all databases are free. What’s your use case?
@proven arrow well, I wouldn't say that, I mean MS SQL and Oracle are also a thing.
But yeah, PostgreSQL is a pretty good production class Db for large applications

spare zenith Aug 3, 2020, 8:55 AM

#

Hey peoples!

#

I just migrated my data from sqlite to postgres

#

I use Django to access the data in the database

#

however, if i take a look at the datetime objects now stored in the database, these all return a non timezone aware object

#

how can i fix that?

tepid cradle Aug 3, 2020, 8:59 AM

#

Postgresql has datetime with timezone and datetime without timezone data types. You probably chose the wrong one while creating the Db tables

olive scaffold Aug 3, 2020, 9:05 AM

#

wait sqlite doesn't support bools?

#

._.

spare zenith Aug 3, 2020, 9:07 AM

#

Postgresql has datetime with timezone and datetime without timezone data types. You probably chose the wrong one while creating the Db tables
Well i just used sequel (ruby) to transfer the data, that seemed the easiest way to do so

#

How would i alter those columns to make those things timezone aware?

tepid cradle Aug 3, 2020, 9:14 AM

#

How would i alter those columns to make those things timezone aware?
@spare zenith I generally connect to my db with DBeaver (community edition) to perform maintenence tasks. It has a lot of functions in the GUI itself.
But otherwise you'll need to use ALTER TABLE query, don't know the full query off the top of my head. You can look it up, just search for change data type of column query pgsql.
You'll have to be careful about the existing data though

spare zenith Aug 3, 2020, 9:16 AM

#

thank you 🙂

spare zenith Aug 3, 2020, 9:42 AM

#

hey @tepid cradle i was completely unfamiliar with Dbeaver. Thank you a ton for this, what an amazing tool!

tepid cradle Aug 3, 2020, 9:54 AM

#

It is. It might not be very beginner friendly, but it has a ton of features.
If you end up using it often, make sure you explore its templates feature, allows you to save aliases for frequently used queries, saves a lot of time.

dense narwhal Aug 3, 2020, 10:14 AM

#

        db = sqlite3.connect('main.sqlite')
        cursor = db.cursor()
        cursor.execute("SELECT user_id FROM levels ORDER BY lvl DESC LIMIT 10")
        result = cursor.fetchall()
        user1 = str(result[0])
        user2 = str(result[1])
        user3 = str(result[2])
        user4 = str(result[3])
        user5 = str(result[4])
        user6 = str(result[5])
        user7 = str(result[6])
        user8 = str(result[7])
        user9 = str(result[8])
        user10 = str(result[9])
        await ctx.send(f"<@{int(user1)}>\n<@{int(user2)}>\n<@{int(user3)}>\n<@{int(user4)}>\n<@{int(user5)}>\n<@{int(user6)}>\n<@{int(user7)}>\n<@{int(user8)}>\n<@{int(user9)}>\n<@{int(user10)}>")
        cursor.close()
        db.close()

#

what the worng here?

#

??

marsh tinsel Aug 3, 2020, 10:23 AM

#

creds = \
    {
        "token": "Token_Here",
        "postgre": {
            "url": "postgres://"
        }
    }

#

how can i fill the url

tepid cradle Aug 3, 2020, 10:28 AM

#

what the worng here?
@dense narwhal fetchall returns a list of tuples. So result[0] will be a tuple. You're using str function on a tuple. Can't say what it will return, but probably not what you want. Try result[0][0].

Or better still, run the query on an interactive terminal /Jupyter Notebook and see what result it returns.

dense narwhal Aug 3, 2020, 10:30 AM

#

what i need to edit in this code?

#

@tepid cradle

tepid cradle Aug 3, 2020, 10:38 AM

#

Already suggested in first paragraph

marsh tinsel Aug 3, 2020, 11:03 AM

#

whats a good postgres host

tepid cradle Aug 3, 2020, 11:18 AM

#

In terms of cost or uptime or scalability? Or free host?

marsh tinsel Aug 3, 2020, 11:19 AM

#

Uptime

brazen charm Aug 3, 2020, 11:19 AM

#

A vps

marsh tinsel Aug 3, 2020, 11:20 AM

#

Well how do setup it on an vps then

tepid cradle Aug 3, 2020, 11:21 AM

#

Well, most vps will provide Ubuntu. Instructions for setting up PostgreSQL Db on Ubuntu are easily available online. And it's rather straightforward, accept for the username part.

marsh tinsel Aug 3, 2020, 11:22 AM

#

Desbian

#

should be kinda same

rich trout Aug 3, 2020, 11:23 AM

#

As someone who's worked with both, yep

#

basically the same

rigid otter Aug 3, 2020, 11:32 AM

#

So I intend to move from Mongo to Postgres

#

I was thinking if it was worth thonk

#

also why does postgres installation on Windows gotta be so fat

brazen charm Aug 3, 2020, 11:40 AM

#

because it also has the interface aswell PgAdmin

mild vector Aug 3, 2020, 12:43 PM

#

Hey,
I wanted to know what was the way to store with PostgreSQL a list of integers.
I know that when creating the table you have to write :

CREATE TABLE IF NOT EXISTS myTable(
    mylist integer[]
);

But then how I can add, delete or edit values in it.

mild vector Aug 3, 2020, 1:02 PM

#

Help please :3

tepid cradle Aug 3, 2020, 1:25 PM

#

Why do you want to store a list of integers in a relational database?

#

I was thinking if it was worth :thonk:
@rigid otter totally worth it. SQL is so much easier and provides so many more features as compared to a NoSQL database

rigid otter Aug 3, 2020, 1:29 PM

#

easier 😓

#

fucking confusing the datatypes

tepid cradle Aug 3, 2020, 1:32 PM

#

Lol. It's worth it because then you're at least assured that your code won't break due to datatype conflicts. On a NoSQL, you insert some values as string, then someone else inserts some values for the same key as int, and lo, your code is no longer working till you fix this issue.
No such confusion on SQL Db, the second person will either get an error, or the values will be cast into the correct data type, depending on how it's being inserted

#

Also, join, count, group by, partition, subqueries, ... yay! None of this is possible, or at least trivially achieved on a NoSQL Db

scarlet atlas Aug 3, 2020, 2:03 PM

#

When you make an instance of AsyncIOMotorClient in the motor module, do you need to await it? The docs don't show so.

marsh tinsel Aug 3, 2020, 2:32 PM

#

How to setup password tho?

marsh tinsel Aug 3, 2020, 2:48 PM

#

got i

ripe helm Aug 3, 2020, 6:36 PM

#

Why does this: ```py
c.execute(f'SELECT {value} FROM members WHERE id = ?', (userid,))

give me an integer like i expected but this: ```py
c.execute(f'SELECT ? FROM members WHERE id = ?', (value, userid))

literally just give me the column name?

for example i have a money column, the first query gives me the integer stored, the second query just gives me the word 'money'

fluid wind Aug 3, 2020, 6:37 PM

#

cannot use bind for column name

ripe helm Aug 3, 2020, 6:37 PM

#

oh ok

delicate fieldBOT Aug 3, 2020, 6:44 PM

#

:x: According to my records, this user already has a mute infraction. See infraction #10298.

#

:x: According to my records, this user already has a mute infraction. See infraction #10298.

#

:incoming_envelope: :ok_hand: applied mute to @torn sphinx until 2020-08-03 18:54 (9 minutes and 59 seconds) (reason: duplicates rule: sent 4 duplicated messages in 10s).

vestal robin Aug 3, 2020, 6:46 PM

#

Hopefully this is the right channel,
But what would be faster/easier

Having a differnet .json file for each persons information I wanted to store.

or having something like I don't know what they are called but look like a json in a json and if it was something like that (Sorry for not knowing the name) how would I read the information for each individual user?

dreamy wren Aug 3, 2020, 7:06 PM

#

how to connect sql databases to python ???

brazen charm Aug 3, 2020, 7:18 PM

#

@vestal robin Fasters -> Dont us JSON
Easier -> Dont use JSON

#

@dreamy wren which sql database

dreamy wren Aug 3, 2020, 7:20 PM

#

my sql

#

like how can we integrate the databases in my sql to python

brazen charm Aug 3, 2020, 7:22 PM

#

@dreamy wren https://www.w3schools.com/python/python_mysql_getstarted.asp

vestal robin Aug 3, 2020, 7:26 PM

#

So it would be better to learn something else?
If so what?

brazen charm Aug 3, 2020, 7:37 PM

#

You should use a proper database rather than JSON for that

vestal robin Aug 3, 2020, 7:38 PM

#

Well the purpose is for a discord bot that in easier terms is like a currency bot.
Should I still learn one and if so which one and how?

brazen charm Aug 3, 2020, 7:41 PM

#

You should 100% use a database not json

vestal robin Aug 3, 2020, 7:46 PM

#

Which one? I've never actually dealt with databases

brazen charm Aug 3, 2020, 7:49 PM

#

Start with Sqlite as its inbuilt with python

#

https://www.sqlitetutorial.net/sqlite-python/

SQLite Tutorial

admin

SQLite Python

This section guides you how to work with the SQLite database using Python sqlite3 module.

barren bronze Aug 3, 2020, 8:22 PM

#

Does anyone here have any place they can reccomend where one can learn about intermediate to advanced database management, design, use and security? I know the basics of SQL and have made a fully functioning proof of concept youtube clone around a RDBMS. But i really want to get more intermediate with better solutions of storing for example comments, accounts etc. Id really like some guidance on reccomended ways to go around designing efficient and secure databases. As I feel really incompetent when desiging and using databases and hammer on it till it works. So, does anyone know of good books, websites, youtube videos, documentation that can help with learning something like that?

subtle orbit Aug 3, 2020, 9:05 PM

#

^ I would also like to be notified if anyone has an answer for this.

solar gale Aug 3, 2020, 10:46 PM

#

at what point would it be a good idea to migrate from sqlite to an actual client-server db?

brazen charm Aug 3, 2020, 10:50 PM

#

depends on your scale or what youre doing

vital edge Aug 3, 2020, 10:50 PM

#

Hello I have a question
What would be the easiest way to export and import datas from a file like xls/csv ?

solar gale Aug 3, 2020, 10:53 PM

#

@brazen charm for now, im just going to share my bot with some friends' servers, but at some point, i'll toss it on top.gg

brazen charm Aug 3, 2020, 10:54 PM

#

I know bots that run SQLite in multiple thousand servers

#

it really just depends how often youre read writing and if you need a connection pool etc...

solar gale Aug 3, 2020, 10:54 PM

#

fair enough

brazen charm Aug 3, 2020, 10:54 PM

#

Its not a massive deal to absolutely switch from the start

solar gale Aug 3, 2020, 10:55 PM

#

i wouldn't do it right now anyways lol

vital edge Aug 3, 2020, 10:55 PM

#

Nobody import/export datas in mysql ? 🥺

#

Is there some kind of tool to do it manually ? Like an app or something

fluid wind Aug 4, 2020, 12:10 AM

#

@vital edge mysqldump - https://linuxize.com/post/how-to-back-up-and-restore-mysql-databases-with-mysqldump/

How to Back Up and Restore MySQL Databases with Mysqldump

This tutorial explains how to backup and restore MySQL or MariaDB databases from the command line using the mysqldump utility. The backup files created by the mysqldump utility are basically a set of SQL statements that can be used to recreate the original database.

vital edge Aug 4, 2020, 12:11 AM

#

the dump worked pretty good

#

but when I tried to import it didn't import anything

fluid wind Aug 4, 2020, 12:13 AM

#

see the article, it has examples

tepid cradle Aug 4, 2020, 5:07 AM

#

but when I tried to import it didn't import anything
@vital edge how were you importing?
As in, were the tables already created or you were importing in an empty database?

autumn token Aug 4, 2020, 9:34 AM

#

"INSERT INTO levels VALUES(" + str(member.id) + ", 0, 0"
any idea what im doing wrong? i just cant see where im going wrong

tepid cradle Aug 4, 2020, 9:52 AM

#

Use f-string for string concatenation
Don't use string concatenation for SQL Queries
While asking questions about Dbs, always mention the Db and library you're using

#

Also, if something is not working, it always helps if you tell what error your getting.
You have given practically no information in your question

vital edge Aug 4, 2020, 10:08 AM

#

@tepid cradle empty data base

edgy field Aug 4, 2020, 12:08 PM

#

Hello everyone,
I just have one requirement to be done for My project , wanted to know the feasibility in python.
Migrate certain tables form one DB(oracle) to another DB (Postgres) , there are some changes in the columns (noting major)... all happing in production ENV

#

Can this be achieved easily in Python

fluid wind Aug 4, 2020, 12:28 PM

#

@edgy field Google first "migrating from Oracle to Postgress", there are few articles that highlight the differences between two database, and tools. It will help you decide if you want to use Python for your task.

kindred coral Aug 4, 2020, 12:30 PM

#

how do you write to a mysql database based on user input in python?

fluid wind Aug 4, 2020, 12:33 PM

#

@kindred coral you might want to go through this tutorial (or any other you can find) https://www.mysqltutorial.org/python-mysql/

MySQL Tutorial

Founder

Python MySQL Tutorial

This Python MySQL tutorial section shows you how to use MySQL connector/Python to access MySQL databases.

marsh tinsel Aug 4, 2020, 12:37 PM

#

How could i get the ping of my postgres server in python

#

in async or smth

harsh pulsar Aug 4, 2020, 12:39 PM

#

you can use subprocess to invoke the pg_isready command line tool https://www.postgresql.org/docs/current/app-pg-isready.html

#

then measure the elapsed time thereof

#

however there will be a lot of overhead

marsh tinsel Aug 4, 2020, 12:44 PM

#

is it possible to do something like this with asyncpg?

harsh pulsar Aug 4, 2020, 12:45 PM

#

that im not sure of. check their docs

marsh tinsel Aug 4, 2020, 12:47 PM

#

whats an pid

#

Hmm i could just ping my server

edgy field Aug 4, 2020, 12:50 PM

#

@fluid wind okay thank you I’ll do that and then ask more specific doubts

torn sphinx Aug 4, 2020, 2:35 PM

#

I started coding a discord bot that would be essentially like a Hunter Simulator with which you'd hunt animals in different forests/places and then later sell them or keep them if they are rare. With coins you'd unlock places where you could hunt and find rarer and more valuable animals and you could also buy better hunting gear. I have even more ideas, but the problem is that I don't know sql lang nor how to work with sqlite, therefore, I'm looking for a coding partner that could do the database part for the bot. (I'm coding in Python using discord.py library)

tepid cradle Aug 4, 2020, 2:51 PM

#

You could try using SQLAlchemy ORM if you don't want to learn SQL. It will allow you to interact with the Db using python code.

torn sphinx Aug 4, 2020, 2:58 PM

#

I would want someone to help me out with that and be my partner

torn sphinx Aug 4, 2020, 3:56 PM

#

I built a Python app that connects to a Teradata database successfully. However, after compiling the app with Pyinstaller establishing the db connection fails. Any ideas of how to resolve this? It would be much appreciated

fluid wind Aug 4, 2020, 4:13 PM

#

@torn sphinx the error message would be helpful. My wild guess that some of the libraries are not included.

torn sphinx Aug 4, 2020, 4:18 PM

#

@fluid wind Good point. I had it currently in a try-block so I dont get one. I will try to get the error message. But this is the line of code that before compile works (ignore indentation).

import teradatasql
con = teradatasql.connect(host='serveraddress',
user='myuser',
password='mypass'
)

fluid wind Aug 4, 2020, 4:28 PM

#

@torn sphinx also this document might be helpful https://pyinstaller.readthedocs.io/en/stable/when-things-go-wrong.html

torn sphinx Aug 4, 2020, 4:37 PM

#

@fluid wind Nice, I will be sure to read

Here is the error i get

📎 unknown.png

verbal wraith Aug 4, 2020, 4:45 PM

#

TinyDB is also another good small DB to use.

#

I've used it for some Discord bots before.

#

https://tinydb.readthedocs.io/en/stable/

tepid cradle Aug 4, 2020, 5:18 PM

#

@torn sphinx In a try/except, never create an all encompassing except statement. First thing is to try to handle specific errors so that your application fails gracefully rather than with a long traceback with unexpected behaviour. Something like

try:
    #do something
except ValueError:
    #do something
except AttributeError:
    #do womething

And if not that, at the very least, capture the error message and include it in the output:

try:
    #do something
except Exception as e:
    #do something
    print(repr(e))

torn sphinx Aug 4, 2020, 7:24 PM

#

Hey guys, so I am learning sqlalchemy and am trying to add it to my flask app (I am following a tutorial) but I get this error:

C:\Python\Python382\lib\site-packages\flask_sqlalchemy\__init__.py:812: UserWarning: Neither SQLALCHEMY_DATABASE_URI nor SQLALCHEMY_BINDS is set. Defaulting SQLALCHEMY_DATABASE_URI to "sqlite:///:memory:".
  warnings.warn(
C:\Python\Python382\lib\site-packages\flask_sqlalchemy\__init__.py:833: FSADeprecationWarning: SQLALCHEMY_TRACK_MODIFICATIONS adds significant overhead and will be disabled by default in the future.  Set it to True or False to suppress this warning.
  warnings.warn(FSADeprecationWarning(```

#

however

#

from datetime import datetime
from flask import Flask, render_template, url_for, flash, redirect  #Importing everything we need.
from flask_sqlalchemy import SQLAlchemy
from forms import RegistrationForm, LoginForm
app = Flask(__name__)  #creating an app variable, __name__ is the name of the module.  
app.config['SECRET_KEY'] = 'faa98298428ea3f7b3eee0b89c22cae3'
app.config['SQLAlCHEMY_DATABASE_URI'] = 'sqlite:///site.db'
db = SQLAlchemy(app)

class User(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String(20), unique=True, nullable=False)
    email = db.Column(db.String(120), unique=True, nullable=False)
    image_file = db.Column(db.String(20), nullable=False, default='default.jpg')
    password = db.Column(db.String(60), nullable=False)
    posts = db.relationship('Post', backref='author', lazy=True)

    def __repr__(self):
        return f"User('{self.username}', '{self.email}', '{self.image_file}')"



class Post(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    title = db.Column(db.String(100), nullable=False)
    date_posted = db.Column(db.DateTime, nullable=False, default=datetime.utcnow)
    content = db.Column(db.Text, nullable=False)
    user_id = db.Column(db.Integer, db.ForeignKey('user.id'), nullable=False)

    def __repr__(self):
        return f"Post('{self.title}', '{self.date_posted}')"```

#

IT says I don't have SQLALCHEMY_DATABASE_URI bind set

#

however in my code I do

#

I was supposed to do from flaskblog import db

#

flaskblog is the name of my main py file btw

#

db.create_all()

#

but I get that error

void bane Aug 4, 2020, 7:56 PM

#

casing. you have a lowercase L in your config above

#

SQLAlCHEMY... 5th letter

torn sphinx Aug 4, 2020, 7:57 PM

#

oh thanks man

torn sphinx Aug 4, 2020, 8:15 PM

#

I need someone who knows how to work with dbs for a discord bot, I'm coding the bot just need someone to help with dbs and be my coding partner for that matter, DM me if you could

#

I would but I am still learning it

wintry canopy Aug 4, 2020, 9:29 PM

#

      members_dict = json.load(f)
with open('alt_dicts.json') as f:
      alt_dict = json.load(f)```I have made sure the file is in the same directory but the code gets stuck here with no errors. please help

deep hill Aug 4, 2020, 9:42 PM

#

hello. I have an issue with my script. I have successfully connected to a mysql database but my script doesn't like this line

await cursor.execute(f"INSERT INTO users (steamID, playerID, playerName, online) VALUES ({steam_id}, {player_id}, {player_name}, 'Yes')")

Does anyone know why?

#

the table exists on the sql database with those column headers

harsh pulsar Aug 4, 2020, 9:50 PM

#

don't use f-strings to insert data into sql queries @deep hill

#

what database library are you using?

deep hill Aug 4, 2020, 9:51 PM

#

@harsh pulsar its aiomysql

#

I tried it this way too...

#

2 sec

harsh pulsar Aug 4, 2020, 9:52 PM

#

await cursor.execute("INSERT INTO users (steamID, playerID, playerName, online) VALUES (%s, %s, %s, 'Yes')", (steam_id, player_id, player_name))

deep hill Aug 4, 2020, 9:52 PM

#

query = "INSERT INTO users (steamID, playerID, playerName, online) VALUES (%s, %s, %s, %s)"
await cursor.execute(query, (steam_id, player_id, player_name, 'Yes'))

#

ah right

#

i'll try your method

harsh pulsar Aug 4, 2020, 9:54 PM

#

either/or, your method should work too

deep hill Aug 4, 2020, 9:54 PM

#

It still doesn't seem to like it

harsh pulsar Aug 4, 2020, 9:54 PM

#

what is the error

#

also show us the schema for the users table

deep hill Aug 4, 2020, 9:56 PM

#

So there is no actual error, it just stops working at that line and starts again from the beginning. If i comment that line out, the rest of the script runs as it should

#

so i think its a formatting issue?

#

📎 unknown.png

#

thats the table structure...

#

sorry quite new to this so if there is a proper way to display scheme i don't know it...

harsh pulsar Aug 4, 2020, 9:58 PM

#

what do you mean "stops working at that line"

#

sounds like your script has more problems

#

show your whole code

delicate fieldBOT Aug 4, 2020, 9:59 PM

#

Hey @deep hill!

Uh-oh! It looks like your message got zapped by our spam filter. We currently don't allow .txt attachments, so here are some tips to help you travel safely:

• If you attempted to send a message longer than 2000 characters, try shortening your message to fit within the character limit or use a pasting service (see below)

• If you tried to show someone your code, you can use codeblocks
(run !code-blocks in #bot-commands for more information) or use a pasting service like:

https://paste.pythondiscord.com

deep hill Aug 4, 2020, 9:59 PM

#

oh 2 sec

#

https://paste.pythondiscord.com/ducaxalimu.py

#

so the file it is processing has around 6 different lines inside it. if i remove the problem line, it prints/cycles through each line as expected

#

as soon as i try to insert it into sql, it prints first line only

#

then starts from the beginning

harsh pulsar Aug 4, 2020, 10:02 PM

#

theres no reason your script should start from the beginning

#

so it's more likely that something else is happening

#

your bare except on line 56 is the problem

#

if there's an error you just ignore the error

#

you should at least print the error so you can see what happened

#

but for real you probably shouldn't just catch errors and discard them

deep hill Aug 4, 2020, 10:05 PM

#

ah ok

#

🙂

#

'_ContextManager' object has no attribute 'execute'

#

i do not know what that means

harsh pulsar Aug 4, 2020, 10:08 PM

#

first of all do not share cursors across queries

#

create one cursor per query

#

second, they want you to write async with db.cursor() as cursor: await cursor.execute(..)

#

they give examples in the docs

deep hill Aug 4, 2020, 10:10 PM

#

excellent thanks

#

it doesnt quite make sense to me, as i pulled that code from a test script i wrote a few days ago

#

it worked in that script but not this one

#

adding the async line made it work

harsh pulsar Aug 4, 2020, 10:22 PM

#

@deep hill read the docs carefully. they're there to help you

deep hill Aug 4, 2020, 10:38 PM

#

ty

lucid needle Aug 5, 2020, 12:51 AM

#

Can i have database in raspberry pi?

fluid wind Aug 5, 2020, 1:15 AM

#

yes, pick a db you need/want, google for tutorial

fallow elm Aug 5, 2020, 1:17 AM

#

raspberry pi is just a computer, probably running linux

#

so yea, you can run a database on it

fluid wind Aug 5, 2020, 1:21 AM

#

Especially on Raspberry Pi 4 with 4 or 8 gb of RAM. Can even use a Docker.

tepid cradle Aug 5, 2020, 2:04 AM

#

Can i have database in raspberry pi?
@lucid needle I have two databases running on my Pi, one MySQL for use with kodi, and another PostgreSQL for use with my projects. I also have multiple scripts, pulling data from web APIs and downloaded csv files and putting them in my Db, running every day.
So yeah, you can have a fairly functional Db, as long as you're not planning to host it for public access.

ionic marsh Aug 5, 2020, 4:22 AM

#

i'm trying to handle a sqlite error

async with aiosqlite.connect(cls._DATABASE) as db:

            async with db.execute(query) as cursor:
                async for row in cursor:
                    print(row)

im intentionally inputting an invalid query, thus getting the traceback: sqlite3.OperationalError: near "FROMm": syntax error. put no matter where I put a try/except, it still shows the traceback. How do I handle errors using async with? Using the normal sqlite library works fine, but i wanted to try the aiosqlite

fallow elm Aug 5, 2020, 4:29 AM

#

what did you try?

ionic marsh Aug 5, 2020, 4:38 AM

#

i've tried wrapping the async with with a try/except, and i've tried using a try/except on the function call

#

none of it works. its always showing the traceback

#

PepeHands