databases

[torn sphinx]

yes but how would i use sql like erm can i access data within a python file like i can with json
@finite chasm yes you can.

[proven arrow]

The main difference is SQL databases are relational, whereas NoSQL aren't.

Yes so where you can have tables and the data can be related

[torn sphinx]

yes

mysql and sqlite both use sql

imo, for a dbot you should just use a nosql db.

[brazen charm]

eek not really

Pretty much all the data your bot will use will be relational

and is very very suited to being relational

which SQL excels at

no

there are other NoSQL dbs

though only 1 viable Driver for python atm for async

[queen saffron]

If you guys wanted to make a table that looks like this, what would you use?

[brazen charm]

the NoSQL db drivers for python are really rather lacking for async stuff

[queen saffron]

im using a mongodb, flask, and datatables

[proven arrow]

@queen saffron looks like a data table

[torn sphinx]

@finite chasm just use a json, its super easy to use.

[queen saffron]

the python code that takes the queries that come from datatables isn't working properly. theres got to be a better way to do this 😦

[proven arrow]

Data tables is front end, generally your server would populate the table

just use a json, its super easy to use.
@torn sphinx Just because it's easy to use does not mean it's the appropriate solution here.

[queen saffron]

what would you use to populate datatables with data from mongodb

[proven arrow]

Your server should return that data to the front end

Then data tables will take care of the rest.

This question would be better suited in #web-development maybe?

[torn sphinx]

hi, I would like to save this as json to use it later as a mongodb document. How can I translate this grammar files to json? <symbol> = a mix of constants and <other_symbol>s
this is the file https://github.com/googleprojectzero/domato/blob/master/common.txt

[queen saffron]

uhhhh not too sure what you mean by that... how do you expect mongo to interpret the queries coming from datatables

i have python code that takes the server side requests coming from datatables and performs the filtering, searching, sorting against the mongodb

I can't just dump 10,000 entries against the clients browser and expect everything to be nice and fast

[tropic wigeon]

Sorry to interrupt, but I'm trying to combine two big data files, and I was using pandas data frames originally but they're really slow, plus take a ton of ram. Would SQLite be a good alternative?

[torn sphinx]

please some help.. I'm asking and no one even reply

[proven arrow]

I can't just dump 10,000 entries against the clients browser and expect everything to be nice and fast
Well yeah don't be doing that. Datatables has documentation on how server side processing should be done. You should have a read of this https://datatables.net/manual/server-side
Use the parameters it provides you to get your data from the database and pass it back accordingly. You may also want to check if python has a library for datatables that may assist you in this.

please some help.. I'm asking and no one even reply
@torn sphinx Your question in your current task is not database related. You may want to claim one of the help channels to get help with this issue of yours. See #❓｜how-to-get-help

[torn sphinx]

I asked in general and I got no help, one told me because json maybe database channel help

yes it's a task but I'm asking for guide not work done

[proven arrow]

I meant task as in the current problem you are trying to solve.

[queen saffron]

i may have figured out the problem, I formated the date that i insert each document, and i think its having a problem sorting by the date

the problem im having is the pagnating was returning the same data as the first page but if I sorted by a different column it didnt seem to happen

[torn sphinx]

I meant task as in the current problem you are trying to solve.
@proven arrow the problem is I've no experience with python and maybe I will invest a week to make something it can be done in 5minutes with some help

[proven arrow]

@torn sphinx Ok. In that case can you be more clear in your question as to exactly what your trying to achieve so someone can give you a better answer.

[torn sphinx]

the question is, how can I translate a grammar txt from that project grammar.py parses the files, to translate it to json. Problem is there is symbol = value or another symbol = value, and some symbols have multiple values, then I need something like "<symbol>": [['<another>'], ['x']], etc

I want to generate a big json file with all that grammar

later I will able to parse it or use it as document in mongodb

[proven arrow]

So as an example, you want it like this?

"<newline>" : [ "<cr><lf>"],
"<interestingint>" : [ "32768", "3737",...],
....

[zealous parcel]

Guys, I have the database connected once and then it doesn't work, why? "use mysql"

    raise errors.OperationalError("MySQL Connection not available.")
mysql.connector.errors.OperationalError: MySQL Connection not available.

[proven arrow]

As the error says it can't find a connection to the database

Could happen if there was a network issue or the connection to the database was closed

[torn sphinx]

So as an example, you want it like this?

"<newline>" : [ "<cr><lf>"],
"<interestingint>" : [ "32768", "3737",...],
....

@proven arrow right

[proven arrow]

Ok well I'm not on a computer but let me try typing something up from my phone.

[torn sphinx]

thank you

[proven arrow]

from collections import defaultdict
import json

with open('data.txt') as f:
    content = f.readlines()

data = defaultdict(list)

for x in content:
    if x != "\n":
         x = x.strip()
         tag = x.split()[0]
         value = x.split('= ', 1)[1]
         data[tag].append(value)

json_data = json.dumps(data, indent=4)
print (json_data)

Try that, although I've not tested it. However it assumes you have a file in the directory called data.txt, and the comments are removed from the file

[torn sphinx]

IndexError: list index out of range

[proven arrow]

Remove the comments at the top and try again

[torn sphinx]

I did it but same

traceback (most recent call last):
File "tojson.py", line 13, in <module>
value = x.split('= ', 1)[1]
IndexError: list index out of range

I've to remove all comments

[proven arrow]

Isn't that what I said?

[torn sphinx]

this should work, thank you again

[proven arrow]

Yes it should, you can see it here, #bot-commands message

[twilit isle]

    ids = db.session.query(MyModel.id).all()
    bulk_data_objects = []

    for x in my_records:
        existing_id = list(filter(lambda z: z[0] == x['id'], ids))
        if len(existing_id) > 0:
           continue

        r = MyModel(
            type=x['type'],
            text=x['text']
            )
        bulk_data_objects.append(r)

    db.session.bulk_save_objects(bulk_data_objects)
    db.session.flush()
    db.session.commit()
    print('Bulk updated to DB')```

I'm trying to add a bunch of query_results to my db, and want to check if they exits already beforehand

this is quite time consuming though

is the are way to 1). check faster if the id's already exist 2). add to db in bulk and just ignore the duplicated ones

highly appreciate your input

also if my general construction with .bulk_save_objects() makes sense

[proven arrow]

Which database are you using?

[twilit isle]

MySQL with SQLalchemy

in Flask

[proven arrow]

Oh I see, I thought you were making raw queries, which if you were you could use INSERT IGNORE, which would insert or ignore it if already exists. However since I don't use sqlalchemy, I'm not sure if it has a feature.

[twilit isle]

My first idea was to set id to unique in the Model

that way it will just throw an error when trying to add it to existing

but I guess that's a hack

[proven arrow]

Well that's kind of how the INSERT IGNORE works as well, except it doesn't throw an error but silently ignores it

Maybe sqlalchemy might complain about the error even if you did that?

[torn sphinx]

Yes it should, you can see it here, #bot-commands message
@proven arrow something is wrong with a big file, how can I debug what line is failing?

there is no comments

[twilit isle]

I will look into INSERT IGNORE

I also think my lambda function is not particularly efficient

existing_id = list(filter(lambda z: z[0] == x['id'], ids))

[proven arrow]

@torn sphinx add it in a try catch, and in the catch print the line or the values

[torn sphinx]

there are values with "" and : that fails

I did it with print() but not try: catch:

for example I've values that includes <symbol> = align="<othersymbol>"

[-1] seems to work

not, is not because I got duplicated values

"<newline>": [
"<cr><lf>",
"<cr><lf>",
"<cr><lf>",
"<cr><lf>",

[proven arrow]

If you want to ignore the index errors then put in try catch,

for x in content:
    try:
        # code here
    except IndexError:
        pass # or print(x) to get the line of error

You have the basic idea of how it works now and how it can be done. The code I sent originally works perfectly fine with that dataset. You should try to attempt to troubleshoot any errors yourself if you are able to.

[torn sphinx]

right, thanks

[torn sphinx]

@proven arrow any idea how can I add an extra square bracket to values? like "<symbol>" : [["a"],["b"],["c"]]

[proven arrow]

Append it as a list

data[tag].append([value])

[torn sphinx]

thanks!

[stark tundra]

I'm having a problem with an sql query, and peewee. Well, first I need to figure out correct sql query. I have a table with two columns, both ints. I want all values column A that shares two specific values in column B. so for example I have the pairs (1, 0), (1, 1), (1, 2), (2, 2), (2, 3) and the specific values are 1 and 2, I want the number 1 since that has both numbers in column B.

[robust robin]

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.image_file}', '{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"User('{self.title}', '{self.date_posted}')"

When I try to input data I get this, following a tutorial and cant find the problem
>>> user_1 = User(username='admin', email='[e-mail]', password='password')

sqlalchemy.exc.InvalidRequestError: One or more mappers failed to initialize - can't proceed with initialization of other mappers. Triggering mapper: 'mapped class User->user'. Original exception was: When initializing mapper mapped class User->user, expression 'Post' failed to locate a name ('Post'). If this is a class name, consider adding this relationship() to the <class 'main.User'> class after both dependent classes have been defined.

[warped frigate]

does anyone else have issues with psycopg2/postgres taking forever to execute_values with 5000+ values every so often? every few times that I run it with 5000 values, it will take over an hour to execute, but most times it takes only a few seconds

lowering the number of values helps to a point, but there's really no change in behavior when lowering values below 5k

using python 3.8.6, the latest stable psycopg2, postgresql 13, and compiling the code with cython 3.0a6 and running on ubuntu 20.04

[fiery cypress]

Does anyone know why my code is failing?

x = collection.aggregate(
        [
            {
                '$match': {'UserID': ctx.author.id}
             },
            {
                '$group':{
                    'DiscordID': '$UserID',
                    'Count': {'$sum': 1}
                  }
             }
        ]
    )
    print(x)

pymongo.errors.OperationFailure: The field 'DiscordID' must be an accumulator object,

please I've been stuck on this for 45 minutes 😭

[warped frigate]

@fiery cypress it looks like you're trying to use a string instead of an accumulator object for the DiscordID field

this post on stack overflow might help:
https://stackoverflow.com/a/54440809

[fiery cypress]

So i looked at that but I wasn't sure if it pertained to what I was trying to do. So I'm thinking like if I have 4 users [Max, Rye, Will, Max], with userIDs respectively [1, 2, 3, 4]

If Max with user 4 uses the command, if I try to do {$first : $userID} would it do 4, or 1?

If it returns 1, what acculumator object(?) would I have to do to match the userID to the author?

[torn sphinx]

i want a db manager like elephant sql that is free
what should i use
ik elephant has a free one but it isnt enough space
pls ping me

[cold slate]

hi

[dusky iris]

can someone whelp me why this code is showing wrong?

mydb = mysql.connector.connect(host="127.0.0.1", port=3306, user="root", passwd="pass", database="school")
cursor = mydb.cursor()
cursor.execute("CREATE TABLE student (SID NUM(3), Sname VARCHAR(255), Smarks NUM(3)")```

Traceback (most recent call last):
File "/Users/admin/Desktop/pyconnectsql.py", line 7, in <module>
cursor.execute("CREATE TABLE student (SID NUM(3), Sname VARCHAR(255), Smarks NUM(3))")
File "/Users/admin/anaconda3/lib/python3.7/site-packages/mysql/connector/cursor_cext.py", line 266, in execute
raw_as_string=self._raw_as_string)
File "/Users/admin/anaconda3/lib/python3.7/site-packages/mysql/connector/connection_cext.py", line 475, in cmd_query
sqlstate=exc.sqlstate)
mysql.connector.errors.ProgrammingError: 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'NUM(3), Sname VARCHAR(255), Smarks NUM(3))' at line 1

[modern mulch]

Missing parentheses

"CREATE TABLE student (SID NUM(3), Sname VARCHAR(255), Smarks NUM(3))"

Missing ) at the end

[dusky iris]

("CREATE TABLE student (SID NUM(3), Sname VARCHAR(255), Smarks NUM(3)")

you mean after the 3 or after the " ?

[modern mulch]

You have 5 ( but only 4 ) in there

"CREATE TABLE student (SID NUM(3), Sname VARCHAR(255), Smarks NUM(3))"

[dusky iris]

mydb = mysql.connector.connect(host="127.0.0.1", port=3306, user="root", passwd="pass", database="school")

cursor = mydb.cursor()

cursor.execute("CREATE TABLE student (SID NUM(3), Sname VARCHAR(255), Smarks NUM(3))")```

i type this and it still shows error

[modern mulch]

Ah I also don't think mysql has NUM type

There're INT and BIGINT and alike

[dusky iris]

ohhh

[modern mulch]

https://www.mysqltutorial.org/mysql-data-types.aspx/

[dusky iris]

😦

my bad

in noob

god its still not working

cursor.execute("CREATE TABLE student (SID int, Sname varchar(255), Smarks int")

[modern mulch]

Missing the ending ) again

[dusky iris]

oof

it worked

thanks a lot @modern mulch hope you didn't mind me being a noob

i just started with this python sql connectivity stuff

[modern mulch]

Not at all 😄

[maiden shoal]

dEteBEESa

[torn sphinx]

can somone help me set up a data base for my discord bot
i made one but i dont want to use local host
i want is hosted so when i host my bot it still works
anyone know hwo to do this or hep me
ping me pls

[maiden shoal]

.

DeteBESe

[proven arrow]

@torn sphinx You can still use localhost when you host it as well

If the database is running on the same host as your application then it's still considered as localhost

[torn sphinx]

@torn sphinx You can still use localhost when you host it as well
@proven arrow i dont want my computer running 24/7 tho

[proven arrow]

You don't run it on your computer

You run it on the server you host it on

[velvet coyote]

hey what would fetchone return in asyncpg

[proven arrow]

It has fetchrow instead, fetchone is not a method

[velvet coyote]

I See

[broken charm]

Hi

Where can i het help

😅

[wintry stream]

@broken charm #❓｜how-to-get-help

[torn sphinx]

Hello, i have 4 columns (id, user_id, col1, col2). ID is the only unique key here. For each user i want to get the average of all his col1 total, and col2 total. All are int columns

So below table for example:

id, user_id, col1, col2
1     10      2      2
2     10      5      5
3     5       7      4
4     5       3      2

Should give the following result:

user_id,  average
10,          7
5,           8

So basically, like this for the first user id 5 it is calculate like: ((7+3)+(4+2)) = 16 / 2 = 8

[shell ocean]

Hello, i have 4 columns (id, user_id, col1, col2). ID is the only unique key here. For each user i want to get the average of all his col1 total, and col2 total. All are int columns
@torn sphinx just to be clear this is SQL?

[torn sphinx]

yeah

[shell ocean]

uh

for user ID 10

isn't the average 7?

[torn sphinx]

oh sorry i meant 5 my mistake

[shell ocean]

hm

SELECT user_id, (SUM(col1) + SUM(col2)) / 2 FROM table GROUP BY user_id?

[torn sphinx]

that works, but the 2 can be more than 2

it should be how many columns this user has?

[shell ocean]

use AVG then

it should be how many columns this user has?
@torn sphinx but you said

each user has 2 columns?

do you mean rows?

[torn sphinx]

user can have many rows in the table

[shell ocean]

yes

but

the average is between

the two sums

so it's always

divided by 2

[torn sphinx]

so that would work what you sent?

[shell ocean]

try it and tell me

that's based on how you described the problem

[torn sphinx]

let me try, thanks

sorry i meant devided by the number of rows it has

so if user_id has 10 rows, then it would divide by 10

[shell ocean]

then

that's different

from what you originally wanted

so you actually want the sum of the averages of each column?

[torn sphinx]

so both columns should be added and then divided by the number of rows, for that user

[shell ocean]

AVG(col1) + AVG(col2)

next time

when you give an example

you should probably make it a bit bigger

so it's less ambiguous

anyway, that should work

[gaunt garden]

Hello everyone, I was wondering if anyone could point me in the direction of a possible db for a discord bot. Im currently running sqlite and it is blocking. I'm looking to upgrade. I have been looking into MongoDB. suggestions?

[proven arrow]

@gaunt garden sqlite is only blocking if you use the non async module for it. You can use aiosqlite which is the async version for it.

Also MongoDB and SQLite are two different databases, where sqlite is relational and uses SQL, and MongoDB is not relational/NoSQL. So switching like this would require all your DB code to change as well.

[gaunt garden]

is there a way that i can create just a database file to run for my application so like where i access sqlite but pass info to that file when i need to query, add or remove stuff? right now its physically in every code block and im hoping to condense my code a bit. sorry if thats a stupid or confusing question, ive seen it done, but dont really know how abouts to set it up. and i will look into aiosqlite. thanks!

[proven arrow]

You can make a single connection and access it whenever you need to, however that won't prevent your issue of blocking, since those I/O operations will still have to be done.

[gaunt garden]

true.

[torn sphinx]

what is the difference between HAVING and WHERE?

i am asking in databse channel so my question is related to that also

[gaunt garden]

@torn sphinx The GROUP BY clause groups a set of rows into a set of summary rows or groups. Then the HAVING clause filters groups based on a specified condition. Note that the HAVING clause is applied after GROUP BY clause, whereas the WHERE clause is applied before the GROUP BY clause.

From:
https://www.sqlitetutorial.net/sqlite-having/#:~:text=Introduction to SQLite HAVING clause&text=The GROUP BY clause groups,based on a specified condition.&text=Note that the HAVING clause,before the GROUP BY clause.

what a link xD

[torn sphinx]

Ok let me read it thanks

They both filter the result so can we not just have where for both?

[proven arrow]

It's to do with the order of operations (certain clauses are evaluated before others).
WHERE applies the filter to each row individually, and happens before any aggregation. Whereas, HAVING filters after the aggregation/grouping is done.

Let me send a illustration of the order in how they are evaluated, will make more sense.

If you look at that WHERE happens before the grouping, so it's not possible to use after the group by, hence why we have HAVING.

[torn sphinx]

ohhh right ok yes yes, makes sense

[modest arrow]

Hello developers ! I'm new on this Discord (forgive me for my english, I'm French '-').

I don't know if this channel is the right one for my subject.

Btw, I have a problem with Flask and its system of database. I followed this video : https://www.youtube.com/watch?v=4T5Gnrmzjak&t=927s

I'm trying to make a webService which returns you all availables hotels depending your booking request (Start Date, Number of Nights, Number of Rooms)

I made 2 database :
Bookings ( Contains every booking )
Hotels ( Contains every hotels )

My actual problem is that I can ask only one database per request. I don't realy understood how it works that's probably the main problem.

So when the client wants to know every available hotels with a (Start Date, n° nights and n° rooms) I need to get every hotels, stock them in a variable, and after that I get every booking to match the delta time of the requests and booking to know if the hotel is availabled during this period of time.

#hotel.py
#...
def get_hotels_db():
    db = getattr(g, "_database", None)
    if db is None:
        db = g._database = shelve.open("hotels.db")
    return db
#...

#booking.py
def get_booking_db():
    db = getattr(g, "_database", None)
    if db is None:
        db = g._database = shelve.open("booking.db")
    return db

class AskBooking(Resource):
    def get(self, start_date, nights, rooms):
        actual_date = datetime.date.today()
        array_start_date = start_date.split("_")
        start_date = datetime.date(int(array_start_date[2]), int(array_start_date[1]), int(array_start_date[0]))

        if(start_date < actual_date):
            return {'messages': 'Date not correct ', 'data':{}}, 404
        if(nights <= 0):
            return {'messages': 'Night can\'t be nul or negative', 'data':{}}, 404
        if(rooms <= 0 ):
            return {'messages': 'Rooms can\'t be nul or negative', 'data':{}}, 404

        shelf_hotels = hotel.get_hotels_db()
        keys = list(shelf_hotels.keys())

        available_hotels = []
        for key in keys:
            if(shelf_hotels[key]['rooms'] == rooms):
                available_hotels.append(shelf_hotels[key])

        print(available_hotels)
        shelf_booking = get_booking_db()
        keys = list(shelf_booking.keys())
        bookings = []
        for key in keys:
            print(shelf_booking[key])
            # if(shelf_booking[key]['hotel_identifier'] in available_hotels):
            #     pass
            # print("key :",key)
            # print("shelf_booking[key] :",shelf_booking[key]['name'])
            bookings.append(shelf_booking[key])

        return {'message': 'Success', 'data': bookings}, 200


#This line is in an other file (__init__.py)
api.add_resource(booking.AskBooking, '/ask_booking/<string:start_date>/<int:nights>/<int:rooms>')

Sorry for the big message ^^

[glass gorge]

how would i make mysql query inclusive? like if i have a date range 11-08-2020 through 11-09-2020, mysql doesn't include datapoints on 11-09-2020 it excludes them

[modest arrow]

So here in my example when I want to read get_booking_db() it returns me hotels database (I go running i'll be comeback in 1hour)

[glass gorge]

are both datasets on the same db @modest arrow

if not you'd need to define new connections

*not

[lofty peak]

If anyone needs help with PostgreSQL let me know

[proven arrow]

@glass gorge That is because your column probably has a time value to it as well. So when you do 2020-09-11 it just assumes until the starting of that day.
So as you did not specify the time, your query just assume the beginning of that day and so is being interpreted as everything between: 2020-11-08 00:00:00 and 2020-09-11 00:00:00. This is why it leaves out everything on the day 11/09

So you should change the last date to 2020-09-11 23:59:59

[glass gorge]

thx

i saw that stack overflow post as well xD

I should have deleted my question

thank you thank you

[proven arrow]

You should leave the question, in case someone in the future ever searches through ctrl f

[glass gorge]

🙂

different servers have different expectations

someone shamed me for not having self control on another server

[torn sphinx]

sqlite3.OperationalError: database is locked

someone can help me with this error

?

[modest arrow]

@glass gorge Ok thanks i'll try

[torn sphinx]

I want to start database integration on my Discord Bot, but i don't understand it at all, anyone got any good tutorials/tips or someone who could help me with it?

I want to start database integration on my Discord Bot, but i don't understand it at all, anyone got any good tutorials/tips or someone who could help me with it?
sqlite3

[hasty juniper]

@torn sphinx use async version

like aiosqlite

[toxic cedar]

i have my database hosted on my server and it has an assigned static ip

used cloudflare dns to point my db.domainname.com to that ip

but now i can't connect to the database with that host name

ping me if you have a solution. I just want to connect to my database via my subdomain address so i don't have to remember my server ip all the time

[wide hinge]

may I ask someone to give a version of the one below in analytic function?

This one is a self join if I'm not wrong but they said using self join is an anti-pattern and expensive and said it is better to use analytic function is instead.

SELECT w2.id 
FROM Weather AS w1 JOIN Weather AS w2 ON DATEDIFF(w1.recordDate, w2.recordDate) = -1
WHERE w2.Temperature > w1.Temperature

[toxic mist]

plz help

[fiery wedge]

hey so i'm new to this discord server, i'm not sure if this is the right place but long story short, I really need help with my python coding homework. I'm not goood at coding however I have to take it for my major and i was wondering if this is the right place to maybe get some help?

Im so in over my head with this class and I can't find any tutors around me

[torn sphinx]

when connecting to my DB that is hosted on a vps(digital ocean ) i get this error. py psycopg2.OperationalError: could not connect to server: Connection timed out (0x0000274C/10060) Is the server running on host "db-postgresql-****-*****-do-user-********-0.b.db.ondigitalocean.com" (1**.**.**9.***) and accepting TCP/IP connections on port 5432? how do i fix this?

please ping me

[quartz tusk]

Let's say I have a table where I have to reset the value of a row at a specific time? How do I do this in Postgres?

Do I constantly check the time every second in a python script and then run the query once the time matches or can I schedule it in PSQL or do I run a cron job that executes the query?

[proven arrow]

@quartz tusk Use a Cron job

@fiery wedge If you need guidance or assistance you can always ask in the relevent help channels.

@torn sphinx Is your database on the same server as the code you are trying to access it from?

[modern parcel]

hi can someone explain why it has to be replaced? and what does the prefixes 'b' and 'p' mean?

and whether if it's okay to use the first method.

this is for sql ^

[proven arrow]

@modern parcel They are both the same queries, and will give you the same result.
The b, and p are just aliases for the table name. So for example, Book b makes the letter b represent the name Book. So now instead of writing the Book each time, you can just reference it as b. You could call this whatever you like, as its just an alias (temporary name for that table just for that query). You can also have aliases for columns as well.

[hasty juniper]

con = psycopg2.connect(database='db', user='postgres', host='localhost', password='2628')
db = con.cursor()
print(db)
db.execute("""create table if not exists eventsData (
            key int primary key,
            channelName text,
            embedDict text,
            eventActive boolean
            )""")
msg = 1
db.execute("INSERT INTO eventsData (key) VALUES (?)", (msg,))
obj = db.execute("select from eventsData *")
print(obj)
con.close()
``` i just switched from sqlite3 to postgresql why is it throwing an error

psycopg2.errors.SyntaxError: LINE 1: INSERT INTO eventsData (key) VALUES (?)

[shell ocean]

@hasty juniper try %s instead of ?

[hasty juniper]

can you find out why?

and

where db data

[shell ocean]

why what

[hasty juniper]

why %s instead of ?

[torn sphinx]

I want to start database integration on my Discord Bot, but i don't understand it at all, anyone got any good tutorials/tips or someone who could help me with it? (sqlite3)

[hasty juniper]

use asyncio @torn sphinx

[torn sphinx]

as in aiosqlite?

[shell ocean]

why %s instead of ?
@hasty juniper because that’s the driver’s syntax

[hasty juniper]

yes

[eternal raptor]

Traceback (most recent call last):
File "C:\Users\sebik\Desktop\discordbot\NinjaManager\venv\lib\site-packages\discord\ext\tasks_init_.py", line 101, in _loop
await self.coro(*args, **kwargs)
File "C:\Users\sebik\Desktop\discordbot\NinjaManager\cogs\general.py", line 22, in reklama_jobs
query6 = await self.client.conn.fetchrow(sql8)
File "C:\Users\sebik\Desktop\discordbot\NinjaManager\venv\lib\site-packages\asyncpg\pool.py", line 567, in fetchrow
return await con.fetchrow(query, *args, timeout=timeout)
File "C:\Users\sebik\Desktop\discordbot\NinjaManager\venv\lib\site-packages\asyncpg\connection.py", line 477, in fetchrow
data = await self._execute(query, args, 1, timeout)
File "C:\Users\sebik\Desktop\discordbot\NinjaManager\venv\lib\site-packages\asyncpg\connection.py", line 1445, in _execute
result, _ = await self.__execute(
File "C:\Users\sebik\Desktop\discordbot\NinjaManager\venv\lib\site-packages\asyncpg\connection.py", line 1454, in __execute
return await self._do_execute(query, executor, timeout)
File "C:\Users\sebik\Desktop\discordbot\NinjaManager\venv\lib\site-packages\asyncpg\connection.py", line 1476, in _do_execute
result = await executor(stmt, None)
File "asyncpg/protocol/protocol.pyx", line 196, in bind_execute
asyncpg.exceptions.InsufficientPrivilegeError: permission denied for table queue

i don't want to give my bot superuser permissions to database

[torn sphinx]

use asyncio @torn sphinx
@hasty juniper You got any good tutorials on it? or any tips?

[eternal raptor]

i didn't set superuser

[hasty juniper]

no tutorials on it, only docs

[torn sphinx]

alright.

[hasty juniper]

@torn sphinx check https://github.com/omnilib/aiosqlite

[torn sphinx]

Yeah I'm currently on that haha

[torn sphinx]

@torn sphinx Is your database on the same server as the code you are trying to access it from?
@proven arrow yeah i got it a while ago thank you

[torn sphinx]

If I were to change an Embed Color using Sqlite3 DB, as what should I save the TABLE Type?
Integer, Text, Blob, Numeric or real? I assume Numeric, but i'm not 100% sure

[proven arrow]

@torn sphinx embed colour of what?

[torn sphinx]

Of an embed?

[proven arrow]

What's the data look like?

[torn sphinx]

As in the table? or?

[proven arrow]

The data you want to store

[torn sphinx]

Well i'm trying to store the value of a color basically, so let's say someone wants the embed color to be #7289da, then they use the command to set it as #7289da

and then the embed color will be that color that they chose

[proven arrow]

For a discord embed?

[torn sphinx]

yes

Sorry yeah, discord.py

I didn't include that before, mb

[proven arrow]

You can store it as an integer. However when you insert it you need to either insert the decimal value of the colour, or prepend the hex with 0x. This would be the easiest way since im pretty sure discord accepts these formats directly.

[torn sphinx]

And how would I do it in the code? because right now i'm doing:

                    color = discord.Color.value

                e = discord.Embed(
                    colour=int(result5[0]).format(color=color),

and i'm getting this as an error: https://gyazo.com/777113e6e435c0113ab7e692b15fb94c

And I already had a feeling that it wouldn't work

https://gyazo.com/03a68f8c50b0a7a8da1a9edb4413d473

this is how it's stored

[proven arrow]

Because your providing it in an invalid format

I said store as an integer

[torn sphinx]

The "color" is an integer in the DB

[proven arrow]

Integers dont have #

[torn sphinx]

ic

so it's just the 7289da

Without the #

[proven arrow]

with a 0x at the beginning

[torn sphinx]

ic

[proven arrow]

For example, if you had the table colours

CREATE TABLE IF NOT EXISTS colours (id integer PRIMARY KEY, colour integer);

Then you can insert it as:

INSERT INTO colours VALUES(1, 0x7289da);
INSERT INTO colours VALUES(2, 0xF1D44A);

It would store it as the decimal value, however discord should accept the decimal value as the colour

[torn sphinx]

im trying to make a custom prefix for my dbot with sqlite3
but i get the error TypeError: Iterable command_prefix or list returned from get_prefix must contain only strings, not function
the code is def get_prefix(client, message): c.execute('SELECT prefix FROM prefixes WHERE guild_id=?', (message.guild.id,)) pre = c.fetchone() return str(pre)

[proven arrow]

Thats not a database issue, but discord.py

[torn sphinx]

ok

with a 0x at the beginning
@proven arrow Even if I use the 0x at the beginning, it still gives me that same error.

[proven arrow]

Show the error

[torn sphinx]

https://gyazo.com/3caf34e31f0341e4d5c474d1a67de493

[proven arrow]

Whats the column type in the db?

[torn sphinx]

Integer

[proven arrow]

Then that should work, show the code how you get the value and set the colour

[torn sphinx]

Imo i think it's the code

1sec

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

this is the whole cog file

[proven arrow]

Theres 200 lines there, what am i looking at?

[torn sphinx]

Right, sorry uh, Line 48-53

that's where I get the value, but i'm sure I did it wrong

and if on how I update the color in the DB is line 177-195

I just noticed that's the wrong code, since it's without the colorvalue thingy

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

Correct code^

[proven arrow]

Try colour=result5[0]

[torn sphinx]

https://gyazo.com/c80eeeb0bbd7cc666126fa73f10f030a

[proven arrow]

After you get the colour add print(type(result5[0])) and see the output

[torn sphinx]

Where would I add that into it?

Since that stuff always gets me kinda messed up

[proven arrow]

After you get the result5

Add it to line 50

[torn sphinx]

https://gyazo.com/ca53b592fb2a3cf97d5e9b42a59dcbfa

[proven arrow]

Well you told me the column type is integer

Clearly its not

[torn sphinx]

well it is according to the db

[proven arrow]

Then that would say integer

[torn sphinx]

https://gyazo.com/c634e988abfc76859d0154a12c7bbd09

[proven arrow]

You can manually try converting to int. Try this: colour=int(result5[0], 16)

[torn sphinx]

That worked

Thank you very much

[proven arrow]

You need to restructure your code/queries. You are making 6 queries, to get the same info you can get in a single query.

[torn sphinx]

I'm just starting to learn this, so it's all pretty new, i'm sure it'll improve over time haha

[proven arrow]

And dont use f strings for queries. Its one of the cause of sql injections (you can read up on this)

[torn sphinx]

👍 I will, thank you

[hoary sonnet]

morning

[frosty barn]

Hey, so I have a table with lots of data points (userid, level, date). I want to get the last 3 datapoints of all users, is that possible with one query or do i need 1 for each user?

[tepid cradle]

@frosty barn you can do that with a window function. Assuming that you mean the latest 3 data points based on date, your query will look like this:

select * from (
  select *, rank() over (partition by userid order by date desc) as rank_val
  from table_name
) t1 where rank_val < 4

The sub-query uses a window function called rank to assign a rank ordered by date in descending order. Since you need it for each user, the table is partitioned by userid, so the ranking will restart every time the userid changes.
In the outer query, you then filter rows where the rank, denoted by the column name rank_val, is less than 4

t1 is just a name assigned to the table returned by the subquery. t1 and rank_val are like variable names, you can use whatever you want in place of those.

[lime echo]

I am using heroku pg:push to push my local database to Heroku.
I get the following error:

throw er; //unhandled error event```
What's the best way to fix this?

[frosty barn]

@tepid cradle Thanks a lot my man! <3 I will need to read a bit more about window functions but i think i understand

[tepid cradle]

@frosty barn yes, window functions seem a bit complicated initially. I remember struggling with them for quite a while before I could understand how to use them. But once you get a hang of them, it will open an entirely new world of advanced SQL queries.

[torn sphinx]

@tepid cradle sounds fun, any good tutorial for this?

or i should first learn basic sql. actually i know the basics but keep forgetting

i mean it doesnt stick

[tepid cradle]

@torn sphinx I don't know any tutorials for this, mostly learnt by using them whenever I needed.
Usually things don't stick unless you are using them on a regular basis. So if you are interested in SQL, a good way to learn is to design a project around an SQL database (PostgreSQL is a good option) and use raw SQL queries rather than an ORM.
You'll obviously need a reasonable understanding of basic SQL before you're able to fully leverage window functions.

[icy fable]

When creating a Python-SQLite program that utilizes a Database class to (1) connect, (2) make a cursor and (3) execute to an SQLite table, is it best for that Database class to create its Connection in the init function? (edited

[unique bone]

hello, i installed mongoDB and i wanna execute mongo commands in jupyter notebooks how to do this?

[lime echo]

Why is pg giving me this unrecognized data block type (0) while searching archieve

[eternal raptor]

        query1 = 'SELECT "owner_id" FROM ads WHERE "guild_id" = $1'
        wynik1 = await self.client.conn.fetchrow(query1, ctx.guild.id)
        wynikx1 = wynik1[0]

wynikx1 = wynik1[0]
NoneType object is not subscriptable

asyncpg

plz help me... 😦

[brazen charm]

you dont need to use ""

SEKECT owner_id FROM ads WHERE guild_id = $1

[lime echo]

@brazen charm bro can you help me with my thing?

I am trying to push my pg database pg:push to Heroku but it gives me the following error:
unrecognized data block type (0) while searching archieve

[dreamy heath]

anyone has good suggestions for local efficient database?

[hoary pagoda]

For a small project ?

https://sqlitebrowser.org/

[zenith spruce]

Hello Everyone
I'm unable to run SQL queries in Python

using SSMS

can anyone please help?

[torn sphinx]

    row = await conn.execute("SELECT server FROM lmessages WHERE value = $1", ctx.guild.id)``` why is this not working? it is supposed to look for  teh row but it isnt

[wet steeple]

I'm trying to list all customer names and SSN for customers who made no transactions in 2018.
I tried this:

SELECT customer.CustomerName, customer.CustomerSSN FROM customer INNER JOIN transaction ON customer.CustomerSSN = transaction.CustomerSSN WHERE YEAR(transaction.TransactionDate) <> '2018' ORDER BY customer.CustomerSSN

I'm still getting customers who made purchases in 2018, it just displays their transactions in other years, how do i fix this?

[paper flower]

!code

[delicate field]

Here's how to format Python code on Discord:

```py
print('Hello world!')
```

These are backticks, not quotes. Check this out if you can't find the backtick key.

[paper flower]

sql instead of py

@wet steeple

Also are you sure that YEAR function returns a string?

[wet steeple]

umm not sure?

[paper flower]

try 2018 then

instead of '2018'

[wet steeple]

its still the same

[paper flower]

What dialect are you using?

[wet steeple]

this is the transaction table

customers

[paper flower]

mysql?

[wet steeple]

yeah mysql

my result

[paper flower]

SELECT customer.CustomerName, customer.CustomerSSN
FROM customer
    INNER JOIN transaction ON customer.CustomerSSN = transaction.CustomerSSN
WHERE YEAR(transaction.TransactionDate) != 2018
ORDER BY customer.CustomerSSN

I didn't really change anything but maybe it will work?

[wet steeple]

its still showing all of them

i tried 2018 both string and as an integer

[paper flower]

btw

why there's duplicated data in your database?

[wet steeple]

where?

[paper flower]

Or customerSSN is your pk

because you usually use some kind of id as primary key

[wet steeple]

yeah customer ssn is pk

[paper flower]

can you try to select rows where year = 2020?

just to see if it works

[wet steeple]

like this?

[paper flower]

SELECT customer.CustomerName, customer.CustomerSSN
FROM customer
    INNER JOIN transaction ON customer.CustomerSSN = transaction.CustomerSSN
WHERE YEAR(transaction.TransactionDate) = 2020
ORDER BY customer.CustomerSSN

[paper flower]

mhm

try != '2019'?

[paper flower]

is it your expected output? 🤔

[wet steeple]

well it was 2018 but even for 2019 you can see that Duncan is still there who made transactions in 2019

[paper flower]

but he also made transactions in other years

[wet steeple]

yeah im trying to list all customers’ name and SSN for the customers who have made no transaction in the year 2018.

[paper flower]

mhm

let me think a bit then

Since i'm not very good with sql

[wet steeple]

ty so much for helping

[paper flower]

@wet steeple i think i figured it out

[wet steeple]

!

[paper flower]

select customer.* from customer
where 2019 != all (select year(transaction.TransactionDate) from transaction where customer.CustomerSSN = transaction.CustomerSSN)
group by customer.CustomerSSN;

I have no way to test it

Tell me what error you will get 🙂

[wet steeple]

its only showing the one person

[paper flower]

hm ,sec

what was the condition

select all persons that didn't make any transactions in some year?

say 2019

[wet steeple]

oh it was 2018

lemme change and see

oh it works!

[paper flower]

poggers

[paper flower]

You could make view out of it

[wet steeple]

whats view?

[paper flower]

are you familiar with functions?

In programming

[wet steeple]

kind of?

like def ... right?

[paper flower]

so basically you can create a function in sql and name it
get_cutomers_without_transactions_in_year(year_param int)

and call it like
select * from get_cutomers_without_transactions_in_year(2018)

[wet steeple]

ohh interesting

ty very much

[paper flower]

np

[glad sleet]

how would i make my database look like this:

items:

"id": id,
"name": name

this is what i have:

and this is the code im trying:

self.config.update_one({"id": ctx.author.id}, {"$push": {"items": {"id": id, "item": item}}})```

[torn sphinx]

in postgres is it possible to just append to the end of an array, or do I have to extract the array into a variable and then append the item (in python cause using python) ?? or is there an actual function?

[topaz glen]

hey guys, can somone help me with normal forms i have a table with 1 normal form can somone help me simplify it to 3rd normal form or 4 normal form
its seems very bulky right now

i thought about divding it by gender(but didnt seem right) or if they are over 18

[worthy pawn]

hey is there a way to set the name of a mysql database from a variable?

all the times i have tried to do it i get syntax error

[torn sphinx]

Are all dbs the same?

[shell ocean]

Are all dbs the same?
@torn sphinx in what way

[worthy pawn]

im using mysql @torn sphinx @shell ocean

and python

[torn sphinx]

@Cog.listener()
    async def on_guild_join(self, guild):
        if await self.bot.conn.execute(f'SELECT EXISTS(SELECT FROM config WHERE guild_id = {guild.id})') != True:
            print("was true")
            # set guild config            
            await self.bot.conn.execute('INSERT INTO config(guild_id, command_prefix) VALUES({guild.id}, \'..\')')

            # insert any existing roles into database
            for role in guild.roles:
                await self.bot.conn.execute('INSERT INTO roles(guild_id, role_id, category, assignable, earnable, locked) VALUES({guild.id}, {role}, NULL, false, false, true)')

asyncpg.exceptions.PostgresSyntaxError: syntax error at or near "{"
I have zero clue what is happening, also VSCode says that the variable 'roles' is not used, even though it is...

discordpy btw

postgres

[proven arrow]

You should use query parameters

[torn sphinx]

what do u mean, am i not?

[proven arrow]

No you are not

[torn sphinx]

the schemas are already set up

[proven arrow]

It's nothing to do with schema

[torn sphinx]

I dont understand, what do you mean then

wait i am not useinf f string

i just noticed

[proven arrow]

Yeah and don't use f strings either

[torn sphinx]

damn always the little things that get me

[proven arrow]

A parameterised query is a way of precompiling a query and send it to the database. Also known as prepared statements. So this way you send the query with placeholders for the values , and then the database knows exactly the query you are wanting. Thereafter it just needs you to provide the parameters that might fit into that placeholder.

("SELECT * FROM table_name WHERE value=$1, 1234)
This is an example of how it should be done. The $1 is just a placeholder for the value that should go there (in example above the value is 1234). The database will know what value goes there after compiling it, and so if someone was to inject some SQL then it would simply not work.

@torn sphinx

[torn sphinx]

oh, so with my code sql injection is possible?

[proven arrow]

If you are taking in user input then yeah or if someone was to change the data that you were inputting into the dB.

[torn sphinx]

I'm confused though, so I have the table schemas set, and they know which type of data goes in each column.

await bot.conn.execute('''
    CREATE TABLE IF NOT EXISTS roles(
        guild_id bigint,
        role_id bigint,
        category varchar(32),
        assignable boolean,
        earnable boolean,
        locked boolean
    )''')

here is one

[proven arrow]

They can get that information, if they want again through injection.

Let me give you example

user_input = "1234" # the input you expect
user_input = "1234; DROP TABLE students;" # the input you actually received. Note the extra DROP TABLE command. 
cursor.execute(f"SELECT * FROM students WHERE value={user_input}")

What do you think will happen there?

[torn sphinx]

holy shit, I see

[proven arrow]

Yeah it'll drop that entire table as well as it gets evaluated as SELECT * FROM students WHERE value=1234; DROP TABLE students;

[torn sphinx]

alright, i deff want to do what you were talking about earlier, I'll go look into it, it's called Parameterised Query?

[proven arrow]

Yeah

[torn sphinx]

cool, thanks a bunch!

    db = sqlite3.connect('settings.sqlite')
    cursor = db.cursor()
    cursor.execute('''
        CREATE TABLE IF NOT EXISTS settings(
        guild_id TEXT,
        prefix TEXT,
        )
        ''')```

looks like i have done a mistake

    cursor.execute('''
sqlite3.OperationalError: near ")": syntax error```

woops found the error

[pseudo cove]

@worthy pawn why are you dynamically creating dbs

[worthy pawn]

what?

[pseudo cove]

hey is there a way to set the name of a mysql database from a variable?
@worthy pawn

is there a reason why you cant just hardcode a name

[worthy pawn]

yeh i guess but is there any way to set it as a varibale

[proven arrow]

You can although its not recommended

Btw do you mean as a parameterised query or as a formatted string?

[queen saffron]

I'm pulling data from an API and putting it into a list, is there a way to query the list against my mongo database and return values that don't match?

for valids in db.find({"name": {"$in": mylist}}, {'registrar': 1}):
    print(valids)

How would I make this return the values that didn't match?

[quartz moon]

for valids in db.find({"name": {"$in": mylist}}, {'registrar': 1}):
    print(valids)

Hello sorry for the silly question but how can I paste my code in such a stylish way?

[queen saffron]

```python
code here
```

works with alot of languages

[quartz moon]

Thanks a lot!

[velvet coyote]

Hey what is this error

    print(dict(data_))
ValueError: dictionary update sequence element #0 has length 4; 2 is required

code :

    data_ = await conn.fetch(
        '''SELECT * FROM people WHERE user_id = 707472976454483988'''
    )
    print(dict(data_))

[eternal raptor]

discord.ext.commands.errors.CommandInvokeError: Command raised an exception: AttributeError: 'Pool' object has no attribute 'fetchall'

[proven arrow]

@eternal raptor what database and library?

[eternal raptor]

postgresql, library: asyncpg

[proven arrow]

Ok, well then it doesn't have a method called fetchall

It has fetch, fetchval, and fetchrow

[eternal raptor]

I know, but which command replace?

[proven arrow]

Use fetch() to get all the rows

[eternal raptor]

ok, thank you ❤️

I will try

[silk vortex]

  File "C:\Users\Internet\AppData\Local\Programs\Python\Python38\lib\site-packages\pymongo\topology.py", line 215, in _select_servers_loop
    raise ServerSelectionTimeoutError(
pymongo.errors.ServerSelectionTimeoutError: connection closed,connection closed,connection closed, Timeout: 30s, Topology Description: <TopologyDescription id: 5fac137b3e07ea5ec664c510, topology_type: ReplicaSetNoPrimary, servers: [<ServerDescription ('exo-database-shard-00-00.auixs.mongodb.net', 27017) server_type: Unknown, rtt: None, error=AutoReconnect('connection closed')>, <ServerDescription ('exo-database-shard-00-01.auixs.mongodb.net', 27017) server_type: Unknown, rtt: None, error=AutoReconnect('connection closed')>, <ServerDescription ('exo-database-shard-00-02.auixs.mongodb.net', 27017) server_type: Unknown, rtt: None, error=AutoReconnect('connection closed')>]>

post = {"_id": "0", "Servers": "0"}
collection.insert_one(post)
``` I'm trying to post to a mongodb collection but I get this error

[eternal raptor]

https://mystb.in/ExpendituresForestsHerbs.python

[little venture]

Hello everyone, can anyone recommend a book that teaches SQL using Python. Thanks in advance.

I know basic SQL commands and have base knowledge of SQL generally.

[latent bone]

mysql?

cuz then i can give you some resources

not books but resources nonetheless

[little venture]

I'd appreciate them. Kindly DM me.

[eternal raptor]

https://mystb.in/ExpendituresForestsHerbs.python
pls help

[eternal raptor]

Guys, Could you help me?

[dreamy heath]

if anyone knows mongo db
how do i check if a variable exists by id?
(i can probably find by loading all the variables but im not sure if thats efficient)

[proven arrow]

Guys, Could you help me?
@eternal raptor What is the value of wynik1?

[eternal raptor]

wait

[proven arrow]

index out of range means the value you are trying to index by is not there

[eternal raptor]

owner id

oh

[proven arrow]

For example, the below would give index out of range since there are only three possible indices:

x = [1,2,3]
print(x[10])

Do print(wynik1) before you index it and check its value.

[eternal raptor]

but earlier it worked :/

because when i use

query1

[proven arrow]

Then probably that record doesnt exist for that query

[eternal raptor]

to string ctx.author.send

bot send ('owner_id')

but i want to bot send owner_id

without ( ' ' )

so

what should i do?

[proven arrow]

Do print(wynik1) before you index it and check its value.

[torn sphinx]

Database

I like Database

[eternal raptor]

ooo

ok

@proven arrow Thank you so much <3, guy. I will check values this method.

[marble osprey]

If I'm using sqlite to print a certain part of a database such as the genre of movies
How do i insert an input into the WHERE bit, if that is where it even goes

[zinc maple]

select * from movies where genre = 'horror';

I think?

oh wait, u want to get all the movies and their genres from the db?

select name, genre from movies;
you right don't need a where if u want all

or do you want to just gent a list of all genres that exist in the db @marble osprey ?

select distinct genre from movies;

[marble osprey]

Basically i want to be able to input the genre and it return every movie of that genre

[zinc maple]

ah then my first one was actually right
select name from movies where genre = 'horror';

assuming each movie has just the single genre, if there are multiple it depends on how you store it and queries get a bit more complicated...

[marble osprey]

I'm having other problems that are preventing me from even trying it

I think i will give up for now :/

[proven arrow]

@marble osprey what issue are you having?

[marble osprey]

It was telling me that execute was not an attribute or something

[proven arrow]

Can you show the code and error?

[marble osprey]

I've already turned off the computer
Sorry

[silk vortex]

    count = f"{len(client.guilds)}"
    post = {"_id": "0", "Servers": "0"}
    results = collection.update_one({"_id": 0}, {"$set":{"servers":count}})
``` Does anyone know why this doesn't work?

it doesn't update the servers field in mongodb for some reason

[proven arrow]

@silk vortex Isnt the method updateOne? https://docs.mongodb.com/manual/reference/method/db.collection.updateOne/#db.collection.updateOne

[silk vortex]

I don't think so. I think that's for something else.

https://api.mongodb.com/python/current/api/pymongo/collection.html#pymongo.collection.Collection.update_one @proven arrow

[proven arrow]

I see, well i dont use mongodb so not too sure about that then.

[silk vortex]

ok

[zinc maple]

@marble osprey when u get back to it, at very least it doesnt sound like the issue is with the query then*

sry for the ping 😛

[marble osprey]

Well i haven't been able to test the query
Although the line it is erroring on is after it

[zinc maple]

maybe you're trying to execute on the connection instead of the connection's cursor

not sure tho, but the query should not be the fault at least, good luck

[proven arrow]

maybe you're trying to execute on the connection instead of the connection's cursor
@zinc maple That does not matter

[zinc maple]

yeah that's what I wasn't sure about

just saw in my own code that I was getting the cursor and then executing on that, but it does ring a bell that it shouldn't matter you're right

[proven arrow]

Well yeah that is how its done. Its just that with connection.execute it would get an intermediate cursor object for you, and then call cursor.execute for you as well.

[glad sleet]

so i have 2 of the same dictionarys in my array, how would i just delete one and not all

[fast whale]

I am trying to make an todo commands , this is how my function and command looks like

    async def make_todo(self,ctx,entry:str):
        values = f"{ctx.author.id}, {entry}, {int(datetime.now().timestamp())} , {ctx.message.jump_url}"
        await self.bot.db.execute(f"INSERT INTO todos(owner_id, todo_content, timestamp , jump_url) VALUES {values}")
    



    @commands.group(invoke_without_command=True)
    async def todo(self,ctx):
        await ctx.send("Something!")

    @todo.command(name="create")
    async def todo_create(self, ctx , * ,entry:str):
        await self.make_todo(ctx, entry)
        await ctx.send(f"{emote.check} | Your todo was successfully added to the list.")

this is the error I get

Ignoring exception in command todo create:
Traceback (most recent call last):
  File "C:\python38\lib\site-packages\discord\ext\commands\core.py", line 85, in wrapped
    ret = await coro(*args, **kwargs)
  File "c:\Users\Rohit\Desktop\discord bots\timepass\cogs\utility\todo.py", line 28, in todo_create
    await self.make_todo(ctx, entry)
  File "c:\Users\Rohit\Desktop\discord bots\timepass\cogs\utility\todo.py", line 17, in make_todo
    await self.bot.db.execute(f"INSERT INTO todos(owner_id, todo_content, timestamp , jump_url) VALUES {values}")
  File "C:\python38\lib\site-packages\asyncpg\pool.py", line 518, in execute
    return await con.execute(query, *args, timeout=timeout)
  File "C:\python38\lib\site-packages\asyncpg\connection.py", line 295, in execute
    return await self._protocol.query(query, timeout)
  File "asyncpg\protocol\protocol.pyx", line 316, in query
asyncpg.exceptions.PostgresSyntaxError: syntax error at or near "548163406537162782"

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

Traceback (most recent call last):
  File "C:\python38\lib\site-packages\discord\ext\commands\bot.py", line 903, in invoke
    await ctx.command.invoke(ctx)
  File "C:\python38\lib\site-packages\discord\ext\commands\core.py", line 1329, in invoke
    await ctx.invoked_subcommand.invoke(ctx)
  File "C:\python38\lib\site-packages\discord\ext\commands\core.py", line 859, in invoke
    await injected(*ctx.args, **ctx.kwargs)
  File "C:\python38\lib\site-packages\discord\ext\commands\core.py", line 94, in wrapped
    raise CommandInvokeError(exc) from exc
discord.ext.commands.errors.CommandInvokeError: Command raised an exception: PostgresSyntaxError: syntax error at or near "548163406537162782"

I think that's something with the database that's why i asked it here

this is how columns look like, when I create them

id column changes to integer and not null after I save

[hasty juniper]

await db.execute("INSERT INTO eventsData (key,eventActive) VALUES (%s,%s)", (msg.content,0))
await db.commit() 
``` why i have error

asyncpg.exceptions.PostgresSyntaxError

error near "%"

[proven arrow]

Use $1, $2 instead for placeholder

[hasty juniper]

now i have Command raised an exception: InterfaceError: the server expects 2 arguments for this query, 1 was passed
HINT: Check the query against the passed list of arguments @proven arrow

await db.execute("INSERT INTO eventsData (key,eventActive) VALUES ($1, $2)", (msg.content,0,))

[proven arrow]

@hasty juniper Remove the brackets around the parameters. So, await db.execute("query", msg.content, 0)

[hasty juniper]

but tuple

[proven arrow]

No

[torn sphinx]

i get this when i insert into mongodb something with the motor library, <Future pending cb=[run_on_executor.<locals>._call_check_cancel() at C:\Users\USER\AppData\Local\Programs\Python\Python36\lib\site-packages\motor\frameworks\asyncio\__init__.py:80]>, can anyone tell me what this is?

[lime echo]

How do I push to heroku without sslmode.
I do heroku pg:push db_name DATABASE_URL --app myapp but it usually fails when it comes to processing the data within the database. I think that's a security issue.
So, what's the best way to push without sslmode or any alternative to push my local database to heroku?

@proven arrow hey man, can you help me with this please?

[lime echo]

How do I deploy my local database to Elephantsql, I couldn't find any tutorial on it?

@dull scarab can you please help me with this?

[dull scarab]

You can't deploy it unless they have an import feature

You can write a script that reads all the entries of your local db, then connects to the postgres db and inserts them

but you need to create the tables first

[lime echo]

As far as I see, they don't.

Oh yeah that's a good idea.

Or I can just import a backup? let's me see.

[dull scarab]

then

1. create the tables you need, either manually or with a script
2. fetch all rows from your local db
3. connect to the online db, and insert each row in the same order

[lime echo]

I can just import a backup and that's it? what do you think, Floppy?

[dull scarab]

¯_(ツ)_/¯

Have't done much in this domain

i just knew of the host, and know that it works as a host

[clear elbow]

so if I wanted to host my own database that I could access, where would I start?
I already have somewhere to host, but I cannot find anywhere online to help me get started
If anyone can help me, please ping, as I would really like to get this started

If it is needed, the computer I will host at is Debian based

[torn sphinx]

@clear elbow you want to host so you can access from an application that is running on that server?

or you want something different?

[clear elbow]

I unfortunately am running an application elsewhere, so it will have to be accessiblge from elsewhere

it looks like I will have to set up MySQL on the server

[torn sphinx]

yes of course that is neccessity yes

but to allow other computer to connect, you have to allow the connection from the sql server setting file.

[clear elbow]

that makes sense

I am not finding a way to install it using the command line, as that is all I have access to

[torn sphinx]

ummm ok, let me see if i can find the command i used

you are on debian 10? @clear elbow

[clear elbow]

yes

[torn sphinx]

ok so make follow this guide, https://www.digitalocean.com/community/tutorials/how-to-install-the-latest-mysql-on-debian-10

has all command inside

[clear elbow]

I am currently downloading it

 mysql-apt-config pre-depends on lsb-release
  lsb-release is not installed.

dpkg: error processing archive mysql-apt-config_0.8.16-1_all.deb (--install):
 pre-dependency problem - not installing mysql-apt-config
Errors were encountered while processing```
This is the error I get when I run
`sudo dpkg -i mysql-apt-config*`

[clear elbow]

finally got it

[torn sphinx]

hey guys can anyone help me with a sql select statement

i got it working but my method needs 5 select statements which takes a long time and isnt efficient at all

here is my selects: ```
routes = db.run('SELECT Route, COUNT(Route) AS count FROM hits WHERE Domain = %s AND Timestamp BETWEEN %s AND %s GROUP BY Route', (domain, d1, d2))

browsers = db.run('SELECT Browser, COUNT(Browser) AS count FROM hits WHERE Domain = %s AND Timestamp BETWEEN %s AND %s GROUP BY Browser', (domain, d1, d2))

locations = db.run('SELECT Location, COUNT(Location) AS count FROM hits WHERE Domain = %s AND Timestamp BETWEEN %s AND %s GROUP BY Location', (domain, d1, d2))


screens = db.run('SELECT Device, COUNT(Device) AS count FROM hits WHERE Domain = %s AND Timestamp BETWEEN %s AND %s GROUP BY Device', (domain, d1, d2))


referrals = db.run('SELECT Referrer, COUNT(Referrer) AS count FROM hits WHERE Domain = %s AND Timestamp BETWEEN %s AND %s GROUP BY Referrer', (domain, d1, d2))```

this is the table: CREATE TABLE IF NOT EXISTS hits ( ID INTEGER PRIMARY KEY AUTO_INCREMENT, Domain TEXT NOT NULL, Route TEXT NOT NULL, Timestamp INTEGER NOT NULL, Browser TEXT, Location TEXT, Device TEXT, Referrer TEXT )

[clear elbow]

Use 3 bacticls are the beginning and end of code to make it look neater

Backtics**

[torn sphinx]

i need to select the count of location, device, referrer etc where the domain is == "whatever"

[clear elbow]

Code line 2```

[torn sphinx]

okay

that better?

[lime echo]

Heroku:

2020-11-12T14:19:47.776494+00:00 app[worker.1]:     Is the server running on host "localhost" (127.0.0.1) and accepting
2020-11-12T14:19:47.776494+00:00 app[worker.1]:     TCP/IP connections on port 5432?```

[torn sphinx]

    @commands.command(name="set-update")
    @commands.guild_only()
    @commands.is_owner()
    async def set_update(self, ctx, *, update=None):
        if update is None:
            db = await self.bot.db.fetch("SELECT * FROM botinfo")
            get_news = db["update"]
            return await ctx.send(f"Current Bot update is\n\n``{get_news}``")
        
        get_db = await self.bot.db.fetchrow("SELECT update FROM botinfo")
        if get_db is None:
            await self.bot.db.execute("INSERT INTO botinfo(update, time2) VALUES ($1, $2)", update, datetime.utcnow())
            return await ctx.send(f"{emote.check} | Successfully updated bot-update.")

        await self.bot.db.execute("UPDATE botinfo SET(update, time2) VALUES ($1, $2)", update, datetime.utcnow())
        return await ctx.send(f"{emote.check} | Successfully updated bot-update.")

discord.ext.commands.errors.CommandInvokeError: Command raised an exception: PostgresSyntaxError: syntax error at or near "VALUES"

this is my table

[proven arrow]

Your update syntax is wrong

See the correct syntax here, https://www.w3schools.com/sql/sql_update.asp

And you might want to add a WHERE clause, if you dont want it to update all the rows

[torn sphinx]

what do i put after WHERE?

[proven arrow]

Your filter for the rows to update

If not specified it will update all the rows

[torn sphinx]

i mean there will be only 4 column

[proven arrow]

And you might want to add a WHERE clause, if you dont want it to update all the rows
columns and rows are different

[lyric cobalt]

Ok, i'm running this query: SELECT id, name, description, author FROM ``todos``WHERE id=1; on this database schema: sql CREATE TABLE todos ( id INTEGER PRIMARY KEY, name TEXT NOT NULL, description TEXT NOT NULL, author TEXT NOT NULL )
But I just get all the results

wait nvm now it works

[wintry stream]

@shrewd geyser what error appears if you try to pip install asyncpg?

[shrewd geyser]

I forgot what it was so let me try and pip it again

[ember turret]

what is the problem?

[shrewd geyser]

!paste

[delicate field]

Pasting large amounts of code

If your code is too long to fit in a codeblock in discord, you can paste your code here:
https://paste.pydis.com/

After pasting your code, save it by clicking the floppy disk icon in the top right, or by typing ctrl + S. After doing that, the URL should change. Copy the URL and post it here so others can see it.

[shrewd geyser]

@wintry stream https://paste.pythondiscord.com/ekirudaruw.php

@ember turret Thats the error I get

[wintry stream]

ah i think you don't have a dependency installed

1 sec

wait docs says it doesnt

[shrewd geyser]

Doesn't what

[wintry stream]

doesn't need requirements

[shrewd geyser]

Ohh, then I have no idea why it won't work

[wintry stream]

Installation
asyncpg has no external dependencies and the recommended way to install it is to use pip:

$ pip install asyncpg

[shrewd geyser]

Should I down grade to 3.8?

[wintry stream]

hmmm

you can try @shrewd geyser

[shrewd geyser]

waiting for 3.9.0 to unistall....

[ember turret]

hello i just started using python so could anyone tell me what to learn first

[weak tinsel]

basics, and this is the wrong channel

[deep siren]

i have a problem

<html lang="de" dir="ltr">
  <head>
    <meta charset="utf-8">
    <title>Login</title>
    <link rel="stylesheet" href="style.css">
  </head>
  <body>
      <?php
      if(isset($_POST["submit"])){
        require("mysql.php");
        $stmt = $mysql->prepare("SELECT * FROM account where USERNAME = :user"); //UserName
        $stmt->bindParam(":user", $_POST["username"]);
        $stmt->execute();
        $count = $stmt->rowCount();
        if($count == 1){
          $row = $stmt->fetch();
          if(password_verify($_POST["password"], $row["PASSWORD"])){
            session_start();
            $_SESSION["uesername"] = $row["USERNAME"];
            header("Location: search.php");
          } else {
            echo "Login Error1";
          }

        } else {
          echo "Login Error";
        }
      }

       ?>
       <form action="index.php" method="post">
    <div class="login-box">
      <h1>Login</h1>
      <div class="textbox">
        <input type="username" placeholder="Username" name="username" required><br>
      </div>
        <div class="textbox">
        <input type="password" placeholder="Password" name="password" required><br>
      </div>
      <input class="btn" type="submit" name="submit" value="Log In">
    </div>
  </form>

  </body>
</html>```
 = index.php
```<?php
$host = "localhost";
$name = "database";
$user = "mysql-admin";
$passwort = "C0mmander admin";
try {
  $mysql = new PDO("mysql:host=$host;dbname=$name", $user, $passwort)
} catch (\PDOException $e) {
    echo "SQL Error: ".$e->getMessage();
}
?>```
 mysql.php
```<?php
session_start();
if(isset($_SESSION["username"])) {
  header("Location: index.php");
  exit;
}
?>

<!DOCTYPE html>...```
 and search.php
when i enter a correct username and password in index.php then i want to go to search.php . BUt i stay on the same site and the loginbox disapears

why?

[torn sphinx]

how can I create a database in python with oracle?

[torn sphinx]

hi

I'm trying to figure out how to use an Access file and upload its tables to a database where I can run a query

I need to automate this with minimal scripting

[sudden phoenix]

Hello everybody, I learn Flask-AppBuilder and I try to setup a "custom" database query in a decorator (@render) in a model.py. Someone knows how to call sqlalchemy on top of FAB ?

[gentle meadow]

anyone help with mongo?

```

[hasty juniper]

How can i put dict in postgres ?

[shell ocean]

How can i put dict in postgres ?
@hasty juniper there are JSON fields

but I would advise thinking about whether you can avoid doing so

[hasty juniper]

it try put in json but discord.ext.commands.errors.CommandInvokeError: Command raised an exception: DataError: invalid input for query argument $1: {'color': 4010, 'type': 'rich', 'descrip... (expected str, got dict)

oke

i fix that

Thx

[eternal raptor]

Hi, I have a problem: I know that there is no such data in the table and I would like Python to not throw an error anyway: 'NoneType' object is not subscriptable, or what is the result1 = query1 [0] it displays error 'index list out of a range 'because there is no data there, but I know ... I want it to display None instead. I have this condition: if result1 is None:
It won't do it for me because it crashes the first or second error, it depends which one prints, and I'd like it to just treat result1 or resultx1 as None instead
code snippet:

query1 = 'SELECT "owner_id" FROM oferty WHERE "guild_id" = $1'
        wynik1 = await self.client.conn.fetch(query1, ctx.guild.id)
        wynikx1 = wynik1[0]
        if ctx.message.author.id == ctx.guild.owner_id:
            if ctx.message.mention_everyone in ctx.message.content:
                if ctx.guild.owner_id in wynikx1:
                    sql01 = 'SELECT "owner_id" FROM blacklist WHERE "guild_id" = $1'
                    wynik01 = await self.client.conn.fetch(sql01, ctx.guild.id)
                    sql02 = 'SELECT "owner_id" FROM owners WHERE "guild_id" = $1'
                    wynik02 = await self.client.conn.fetch(sql02, ctx.guild.id)
                    sql03 = 'SELECT "owner_id" FROM oferty WHERE "guild_id = $1"'
                    wynik03 = await self.client.conn.fetch(sql03, ctx.guild.id)
                    wynik001 = wynik01[0]
                    wynik002 = wynik02[0]
                    wynik003 = wynik03[0]

Here I just gave a little more code so that there were more examples of indexes and I mean the third if, even if I put the result1 instead of resultx1, the effect is the same, but it will not stick to the index that it is out of range (as if this data were in the table, it would not stick to it, because now it is not there and points to Null (in python None)), it would stick to NoneType object is not subscriptable.
Do you have ideas about other queries or some changes in the code?
I'm using PostgreSQL, asyncpg (asyncio postgresql library for python), discord.py
second code snippet:
cant find at the moment but it was:

if result wynikx1 is None:
   # do ... code

It will crash that list index out of a range
if it was instead of resultx1 -> result1
that would give 'NoneType object is not subscriptable'

[lime echo]

I have my database on Elephantsql and I am trying to remotely connect to my bot.
I always get this error:
is the server accepting tcp/ip connections on port 5432 elephantsql
What's the best way to fix this?

[dreamy cedar]

Hii :)

Can anyone suggest any alternative to Popsql?

I am trying to install Popsql but, not working... And, also it's expensive..

[lime echo]

@dreamy cedar maybe sqlite or Postgres?

[dreamy cedar]

I mean, I am using MySQL... But, I need some IED for better interaction because using Terminal is not easy 😅😅

@dreamy cedar maybe sqlite or Postgres?
@lime echo I tried Popsql but, I feel i can't use it and have some issues 😞

[lime echo]

@dreamy cedar yeah try something else.
If the database is small, just use sqlite3.

[shell ocean]

like I think

what @dreamy cedar is asking

is what database explorer/GUI to use

not what RDBMS

[dreamy cedar]

not what RDBMS
@shell ocean right..

[lime echo]

@dreamy cedar ``WorkbenchorphpMyAdmin`.

[dreamy cedar]

Let me check it out.. Thank you :)

I will ping you back.. If I stuck somewhere

[lime echo]

you're welcome!

and OK!

[dreamy cedar]

Workbench is paid?

I think..

[lime echo]

It has a free plan as far as I know. @dreamy cedar
Do you have XAMPP? I think that I have a better solution for you.

If you have XAMPP just run your MySQL then open your browser and go to localhost/phpmyadmin. (that's it!)

[dreamy cedar]

Honestly speaking, i am just beginning to learn about DB... But, I will do as you say..

Never hear of XAMPP.. Let me Google it..

[lime echo]

Hah, it's OK!

[torn sphinx]

pgAdmin4 stucked in loading screen, anyone know the solution for it?

[lime echo]

@torn sphinx just shut it down then restart it.

[torn sphinx]

Tried

Not worked

@lime echo Stucked here

[lime echo]

Oh, I don't know how to fix it to be honest. @torn sphinx

Maybe just reinstall it or something.

[torn sphinx]

Yeah reinstalled and not worked lol, anyway thanks

[lime echo]

xD

you're welcome!

[torn sphinx]

I'm gonna grab a help channel

[weak tinsel]

how can i get logs from heroku postgre database

or is there a way to check logs on heroku?

[broken pumice]

hi, im using pyodbc to connect to ms sql server, is there any way to do this asynchronously

ping me if u have an answer

[proper totem]

hi im pretty new to databases i want to make a login system with username password and hwid how can i ask for them ??

def login():
  mycursor = mydb.cursor()
  print('Enter username: ')
  username = input()
  mycursor.execute('SELECT username FROM oasisacc WHERE username=?', (username,))
  checkUsername = mycursor.fetchall()
  if checkUsername != 0:
    print('Username does not exist')
  else:
    print('Logged In!')

thats where im at currently

[broken pumice]

i made one, using text channels

[proper totem]

wdym?

[broken pumice]

@proper totem what module are u using?

[proper totem]

mysql.connector

[broken pumice]

try giving a wrong username, and print the result

[proper totem]

i tried it doesnt even give me a answer its just an error

[broken pumice]

Oh

whats the error

?

[proper totem]

Not all parameters were used in the SQL statement

ok thanks anyways i fixed it

[eternal raptor]

Hi, I have a problem: I know that there is no such data in the table and I would like Python to not throw an error anyway: 'NoneType' object is not subscriptable, or what is the result1 = query1 [0] it displays error 'index list out of a range 'because there is no data there, but I know ... I want it to display None instead. I have this condition: if result1 is None:
It won't do it for me because it crashes the first or second error, it depends which one prints, and I'd like it to just treat result1 or resultx1 as None instead
code snippet:

query1 = 'SELECT "owner_id" FROM oferty WHERE "guild_id" = $1'
        wynik1 = await self.client.conn.fetch(query1, ctx.guild.id)
        wynikx1 = wynik1[0]
        if ctx.message.author.id == ctx.guild.owner_id:
            if ctx.message.mention_everyone in ctx.message.content:
                if ctx.guild.owner_id in wynikx1:
                    sql01 = 'SELECT "owner_id" FROM blacklist WHERE "guild_id" = $1'
                    wynik01 = await self.client.conn.fetch(sql01, ctx.guild.id)
                    sql02 = 'SELECT "owner_id" FROM owners WHERE "guild_id" = $1'
                    wynik02 = await self.client.conn.fetch(sql02, ctx.guild.id)
                    sql03 = 'SELECT "owner_id" FROM oferty WHERE "guild_id = $1"'
                    wynik03 = await self.client.conn.fetch(sql03, ctx.guild.id)
                    wynik001 = wynik01[0]
                    wynik002 = wynik02[0]
                    wynik003 = wynik03[0]

Here I just gave a little more code so that there were more examples of indexes and I mean the third if, even if I put the result1 instead of resultx1, the effect is the same, but it will not stick to the index that it is out of range (as if this data were in the table, it would not stick to it, because now it is not there and points to Null (in python None)), it would stick to NoneType object is not subscriptable.
Do you have ideas about other queries or some changes in the code?
I'm using PostgreSQL, asyncpg (asyncio postgresql library for python), discord.py
second code snippet:
cant find at the moment but it was:

if result wynikx1 is None:
   # do ... code

It will crash that list index out of a range
if it was instead of resultx1 -> result1
that would give 'NoneType object is not subscriptable'

[broken pumice]

hi, im using pyodbc to connect to ms sql server, is there any way to do this asynchronously

[upbeat marsh]

I'm getting very often this error => http://sqlalche.me/e/13/2j85 I tried the pool_pre_ping thing, pool_recycle, etc... nothing seems to work, any suggestion ?

[silk vortex]

result = await collection.update_one({'_id': 0}, {'$set': {'servers': count}}) I'm trying to use motor to update my server count on a database but it isn't working for some reason. I'm trying to update my database with my servercount but it isn't.

[shrewd geyser]

bank = await conn.fetchrow('SELECT bank FROM economy WHERE id=$1', ctx.author.id)

[finite ice]

the way i do it is like fetchrow("SELECT * FROM economy WHERE user_id = $1)

i set that to a var for example user

and i index it

[shrewd geyser]

The user_id part?

Or the way where you set what user_id should equal

[finite ice]

no like user = <urbotvar>.fetchrow("SELECT * FROM economy WHERE id=$1")

and i index user

for example

user['money']

[shrewd geyser]

Ohh

[finite ice]

you could try doing that

[shrewd geyser]

Okay

@finite ice So now it just prints balance instead of the acutal value

[finite ice]

what did you do

[shrewd geyser]

balance = await conn.fetchrow('SELECT * FROM economy WHERE id=$1', ctx.author.id)

[finite ice]

did you index it

[shrewd geyser]

Yes

Wait now I can't

It turns it into a str when I do

[finite ice]

wot

[shrewd geyser]

@finite ice VS Code is being weird

[finite ice]

wdym

[shrewd geyser]

I fixed it

[finite ice]

alr

[shrewd geyser]

@finite ice It worked thx

[finite ice]

np

[silk vortex]

#databases message Can someone help me please?

[earnest parcel]

Please explain what you mean by isn't working

[silk vortex]

it's not updating

it's just staying the same

I don't get an error either

@earnest parcel

dbclient = motor.motor_asyncio.AsyncIOMotorClient("mongodb+srv://[e-mail]/exo-database?retryWrites=true&w=majority")
db = dbclient['exo_database']
collection = db['Servers']

this is the connection code

[idle storm]

I have this code ```conn = sqlite3.connect("amounts.db")

c = conn.cursor()

# create a table
c.execute("""CREATE TABLE IF NOT EXIST amounts
                (id TEXT, amount REAL)""")
conn.commit()``` which is meant to create a table, but I'm getting an error: `sqlite3.OperationalError: near "EXIST": syntax error`. Anyone have a fix?

[proven arrow]

@idle storm Exist is plural, so should be EXISTS

[rugged holly]

anyone here know how the repl database works

[idle storm]

I saw, thank you!

Got another error tho

    c = conn.cursor()
    c.execute("CREATE TABLE IF NOT EXISTS amounts (userid TEXT, amount INT)""")``` I used this code to make a table. However, when I try to insert values to it, I get this error: ```c.execute("INSERT INTO amounts (userid, amount) VALUES (?, ?)",(id, 100))
sqlite3.OperationalError: table amounts has no column named userid``` Anyone got a fix? Ping me with ur answer please

I'm tryna make a table with 2 columns, userid and amount. I then want to put a given user id with the variable id and a int of 100 into said table for context.

Do you have any idea @proven arrow?

[shrewd geyser]

@finite ice You still online?

[torn sphinx]

someone come to my aid

how do i set up a log viewer for heroku?

[shrewd geyser]

Is this valid SQL ```py
await conn.execute('''UPDATE guilds(prefix) SET VALUE($2) WHERE id=$1''', ctx.guild.id, 'l!')

[noble root]

wtf is wrong with this, once it works, once it doesn't work (0 errors)

[silk vortex]

ALTER TABLE Users RENAME TO Test; I'm trying to do this but it says users relation is not found. The table name is Users so I'm not sure why this is happening.

[merry arch]

Question

I know i could look this up on youtube but id rather hear it from you guys whats a good module for local databases?

i wanna get the hang of it before i start using it for active ones over the web.

[dreamy cedar]

@lime echo could help me.. I am getting this error when I am trying to open
loaclhost/phpmyadmin/

@lime echo could help me.. I am getting this error when I am trying to open
loaclhost/phpmyadmin/
@dreamy cedar

Or anyone know fix this?

@hazy shore : could you please help me to fix this 😞

[weak tinsel]

I know i could look this up on youtube but id rather hear it from you guys whats a good module for local databases?
@merry arch i use mysql - mysql.connector module

you can use PostGRE SQL too

i dont think there is a lot of difference

[weak tinsel]

but how do i get error logs of postgre db in heroku? using asyncpg

[lime echo]

@lime echo could help me.. I am getting this error when I am trying to open
loaclhost/phpmyadmin/
@dreamy cedar sure you will get an error because you need to install XAMPP and launch it in order to use that.

[dreamy cedar]

Give me a moment.. Let me do that

@dreamy cedar sure you will get an error because you need to install XAMPP and launch it in order to use that.
@lime echo any official website?

@lime echo getting this error massage...

[lime echo]

@lime echo getting this error massage...
@dreamy cedar idk what's that lmao but it sound like u cant install it because of UAC

[dreamy cedar]

Ya.. What to do? 😞

[lime echo]

Try to solve it. Search on google or smth.

[dreamy cedar]

Does it look something like this?

[lime echo]

I cant help you a lot today since in not that free... I have a bunch of stuff to do.

[eternal raptor]

wtf is wrong with this, once it works, once it doesn't work (0 errors) :PEPE_hands: :PEPE_rope:
@noble root siema 🙂

[lime echo]

Did it work?

[eternal raptor]

Hi, I have a problem: I know that there is no such data in the table and I would like Python to not throw an error anyway: 'NoneType' object is not subscriptable, or what is the result1 = query1 [0] it displays error 'index list out of a range 'because there is no data there, but I know ... I want it to display None instead. I have this condition: if result1 is None:
It won't do it for me because it crashes the first or second error, it depends which one prints, and I'd like it to just treat result1 or resultx1 as None instead
code snippet:

[dreamy cedar]

It's okay :).. Yaa.. It showing me it's installing

[eternal raptor]

query1 = 'SELECT "owner_id" FROM oferty WHERE "guild_id" = $1'
        wynik1 = await self.client.conn.fetch(query1, ctx.guild.id)
        wynikx1 = wynik1[0]
        if ctx.message.author.id == ctx.guild.owner_id:
            if ctx.message.mention_everyone in ctx.message.content:
                if ctx.guild.owner_id in wynikx1:
                    sql01 = 'SELECT "owner_id" FROM blacklist WHERE "guild_id" = $1'
                    wynik01 = await self.client.conn.fetch(sql01, ctx.guild.id)
                    sql02 = 'SELECT "owner_id" FROM owners WHERE "guild_id" = $1'
                    wynik02 = await self.client.conn.fetch(sql02, ctx.guild.id)
                    sql03 = 'SELECT "owner_id" FROM oferty WHERE "guild_id = $1"'
                    wynik03 = await self.client.conn.fetch(sql03, ctx.guild.id)
                    wynik001 = wynik01[0]
                    wynik002 = wynik02[0]
                    wynik003 = wynik03[0]

[lime echo]

Perfect good job!

[eternal raptor]

Here I just gave a little more code so that there were more examples of indexes and I mean the third if, even if I put the result1 instead of resultx1, the effect is the same, but it will not stick to the index that it is out of range (as if this data were in the table, it would not stick to it, because now it is not there and points to Null (in python None)), it would stick to NoneType object is not subscriptable.
Do you have ideas about other queries or some changes in the code?
I'm using PostgreSQL, asyncpg (asyncio postgresql library for python), discord.py
second code snippet:
cant find at the moment but it was:

if result wynikx1 is None:
   # do ... code

It will crash that list index out of a range
if it was instead of resultx1 -> result1
that would give 'NoneType object is not subscriptable'

[dreamy cedar]

Please not spam.. You might going to get warning 😬

Perfect good job!
@lime echo any link to setup XAMPP?

[eternal raptor]

:/

[dreamy cedar]

I mean any good article..

:/
@eternal raptor don't worry someone will help you

[eternal raptor]

I have repaired this since 1 week

[lime echo]

I mean any good article..
@dreamy cedar google bro

[dreamy cedar]

😅 👌

[eternal raptor]

Sorry for spamminf, but I really dunno what could I do if repair this.

[proven arrow]

@eternal raptor I don't understand what your issue is?

if x is None: should work

[velvet coyote]

    data_ = await conn.fetch(
        '''SELECT user_id FROM users'''
    )
    print(data_)
    if 536875405819379733 in data_:
        print("Working as expected")
    else:
        print("NOPE!!!!")

So this gives [<Record user_id=302281963455250434>, <Record user_id=707472976454483988>, <Record user_id=575252669443211264>, <Record user_id=536875405819379733>]

as the output, and 536875405819379733 is there but why does it print NOPE!!???

[proven arrow]

Because its a list of Record objects. The integer is not directly in that list like that.

You can take a look at https://magicstack.github.io/asyncpg/current/api/index.html#record-objects to see the operations you can do on the Records.

[velvet coyote]

ohh ok thx a lot for that

that's for fetchrow though, and when I do fethcrow it only gives one value but there are 4

<Record user_id=302281963455250434>

[proven arrow]

Your query is asking for only 1 column, and fetchrow gives a single row, so that is the correct result your getting

[eternal raptor]

@eternal raptor I don't understand what your issue is?
@proven arrow but table hasn't data and bot send: x = wynik1[0]
if x is None:
IndexError: List index out of a range
if wynik1 is None:
'NoneType' object is not subscriptable
I know, that is None, but i want to bot execute this if is None: do code
But bot doesn't want execute this.

[proven arrow]

@eternal raptor Then why cant you do it like this?

if not wynik01:
  print("No value")
else:
  wynik001 = wynik01[0]

You can also do fetchval since you are only requesting a single column value, and this way you wouldnt have to subscript it to get the value.

[eternal raptor]

Hm

I will try this

Thank you, I told to you at 3PM ( at me it’s 12:20 AM)

[proven arrow]

So you want 5?

In the example you gave, you can do something like:

sorted_results = sorted(my_dict, key=lambda x: (my_dict[x]['level']), reverse=True)

The above sorts by level by level, but if 2 dicts have the same level, and so if you want to sort by the xp, you can do the below:

sorted_results = sorted(my_dict, key=lambda x: (my_dict[x]['level'], my_dict[x]['exp']), reverse=True)

sorted_results will contain the list of keys sorted. Then you can use these keys to access elements of that dict. Maybe there might be a better/more efficient way to do this in python, but then you should ask that in another channel, since your question is not entirely Database related.

Get the first element of that list by index

first = sorted_results[0]

[weak tinsel]

how to use add_log_listener in asyncpg module?

with dpy

do i define it as an event?

[eternal raptor]

Unhandled exception in internal background task 'reklama_jobs'.
Traceback (most recent call last):
File "C:\Users\sebik\Desktop\discordbot\NinjaManager\venv\lib\site-packages\discord\ext\tasks__init__.py", line 101, in _loop
await self.coro(*args, **kwargs)
File "C:\Users\sebik\Desktop\discordbot\NinjaManager\cogs\general.py", line 41, in reklama_jobs
await channel.send(f"Reklama właściciela: {query1} \n\n {query3}")
File "C:\Users\sebik\Desktop\discordbot\NinjaManager\venv\lib\site-packages\discord\abc.py", line 904, in send
data = await state.http.send_message(channel.id, content, tts=tts, embed=embed,
File "C:\Users\sebik\Desktop\discordbot\NinjaManager\venv\lib\site-packages\discord\http.py", line 241, in request
raise Forbidden(r, data)
discord.errors.Forbidden: 403 Forbidden (error code: 50013): Missing Permissions

Bot send a message only once and next... error

Guys, anyone know ?

[silk vortex]

insert = await connection.execute('insert into "public"."Join" (guild_id) values ($1)', guild_id)

values = await connection.execute('select guild_id from "public"."Join" where guild_id = $1', guild_id)
await ctx.send(values)  
```I have this code and it works but is there a way I can return the actual data instead of `Select 1`?

I'm using asyncpg to contact a postgresql database

[proven arrow]

@silk vortex You need to use fetch when you want to get some data from the database, instead of execute. You can use fetch to get all rows, fetchrow to get a single row, or fetchval to get a single value. See the method here in the documentation, and also have a read of the two functions under it. https://magicstack.github.io/asyncpg/current/api/index.html#asyncpg.connection.Connection.fetch

[silk vortex]

oh ok

Thanks!

@proven arrow I have a question. I'm trying to do an enable command for my on_member_join. So, would each column be a server ID or would I name one column as guild_id and 1 column as channel_id?

[proven arrow]

What is an enable command?

[silk vortex]

like so a server owner can enable the join message. Once they enable it, they can get the join message in their server through my bot

I'm doing this for my bot btw

[proven arrow]

Well you would need to know which channel to send the message to so you would need to have a column for the channel id as well then

id | guild_id | channel_id | message should be the bare minimum you should store. Im not sure what other data is required for that process/command.

[silk vortex]

oh

I don't have the id column

but I was thinking of having the guild_id replace the id column

[proven arrow]

Well that was just me implying thats a Primary Key. You can have the guild_id as the Primary Key column instead

[silk vortex]

yeah

I'm gonna have the guild_id as the primary key

[proven arrow]

That would work also

[silk vortex]

Thanks!

I was just confused how I should structure my table

[shrewd forge]

Does sqlite support order by similarity? I know postgres supports it but there's small differences so i'm not sure

[ancient bough]

is anyone here good at using google sheets as a database? if so

im trying to check for a certain string in a column and once i find the column i want to get the row is that possible?

[trim knoll]

await con.execute(f"CREATE TABLE {name}("
                              f"userid numeric NOT NULL,"
                              f"points numeric NOT NULL,"
                              f"coins integer,"
                              f"items text[],"
                              f"PRIMARY KEY (userid)"
                              f")"
                              f"WITH (autovacuum_enabled = True)")

this is driving me insane it keeps saying syntax error at create i dont understand :/

[sharp nest]

await con.execute(f"CREATE TABLE {name}("
                              f"userid numeric NOT NULL,"
                              f"points numeric NOT NULL,"
                              f"coins integer,"
                              f"items text[],"
                              f"PRIMARY KEY (userid)"
                              f")"
                              f"WITH (autovacuum_enabled = True)")

this is driving me insane it keeps saying syntax error at create i dont understand :/
@trim knoll
Dont use format strings, use the execute parameters (should be in the library docs) if I recall it handles sql escape characters.

[glad sleet]

is there a way i can set the min to 0 for an int in mongodb

to make sure it doesnt go negative

[earnest parcel]

Can't you check that directly in your python code?

[tepid cradle]

this is driving me insane it keeps saying syntax error at create i dont understand :/
@trim knoll
When you're not using any variables, why are using f strings? Why not just plain string?
And for the error, for multi-line string, you either need a backslash at the end of every line, or start the string with triple quotes.

[trim knoll]

@trim knoll
Dont use format strings, use the execute parameters (should be in the library docs) if I recall it handles sql escape characters.
@sharp nest execute params dont work with the table name

@trim knoll
When you're not using any variables, why are using f strings? Why not just plain string?
And for the error, for multi-line string, you either need a backslash at the end of every line, or start the string with triple quotes.
@tepid cradle i am using variable for the table name, and the backslashes are redundant the indentation takes care of it. my error is query related not db related. moreover i have done similar stuff before this is the first time i am facing this stupid error

await con.execute("""
                                    CREATE TABLE name(
                                    userid numeric NOT NULL,
                                    points numeric NOT NULL,
                                    coins integer,
                                    items text[],
                                    PRIMARY KEY (userid)
                                    )
                                    WITH (autovacuum_enabled = True)
                              """)

tried, still the same error

[proven arrow]

Syntax looks fine, what is the error?

And this is for postgres right?

[trim knoll]

yup

sytax error at or near create

[proven arrow]

Can you show code for that function you are running? Your error also seems to be showing another query in it

[torn sphinx]

Can I use sqlite3, but make it so it sends the data to a database on another device?

Like if it's a public "application" (so other people can download it to their pc), that needs to save information. Then it saves the information somewhere online, or on my pc.

[steady lava]

Hey everyone,
I want to store YouTube links with their title (mostly songs) associated with each user. For example : suppose a person named "A" has four songs in his playlist and the structure would looks like

{
    'A':
        {
          'crawling': <YouTube link>,
          'faint': <YouTube link>,
          'Nothing else matters': <YL>
         }
}

Here, I am not sure which method would be best to store and retrieve these links faster. I know about JSON obj but how will I create or store songs for Different users.. a nested dicitionary in json file or sql database ??

[proven arrow]

@torn sphinx sqlite isnt a network database, it cant communicate over a network

[torn sphinx]

alright

[proven arrow]

You could have clients communicate with a web service, which would communicate with sqlite, but you would have to consider if you really want to do that

@steady lava You can store it in a database. There are SQL and NoSQL ones, and even most SQL dbs allow you to store JSON data as a column type as well.

[steady lava]

@proven arrow thanks for replying ..
Actually I was thinking to create N number of json files for N number of users (if they want to save the playlist) is it okay to do so, and yes why SQL is more preferable than JSON ?

[proven arrow]

SQL will allow you to query the data. As an example, you could do something like "get me all the users who have song x in their playlist". Although this can still be done with your programming language, its not always as efficient/so easy to do especially if the data grows, whereas SQL is made to query such things. Also say you define you json structure to be like you do now, however in the future there is a need to maybe capture some extra data, then what do you then do with all of your current JSON objects, because updating these can be problematic, whereas with a DB its relatively straight forward. Also managing all these files for each user sounds really inefficient too, especially if your users will be rising. And have a read of this. #databases message Also you should just try googling it, because there will be plenty of more reasons to help you decide, and sometimes I find it difficult to get my message across with the language barrier. @steady lava

[torn sphinx]

Hi, so i make tables some time ago. One table called Products, and another OrderItems. Now the id in Products table is the PK, and was used to link the table to order items. This ID is also the product code of the internal Product items. Now there is a need to change the Product IDs for some products, but as you might imagine this will cause issue because will also change order IDs on the orderitems table?

[proven arrow]

Easiest way would be to add an extra column to store the product ID. So something like product_id, and then use this to store your internal product ID values.

That way you wont be changing the id field, and those relations would stay as they are. So changing the product id, from here onwards would be just like changing the description or some other value (i.e. it wont affect the other data).

Maybe if you have a front end, then just change it so from now on it shows the value of the new column as the product id

[trim knoll]

Can you show code for that function you are running? Your error also seems to be showing another query in it
@proven arrow dammit you were absolutely right, the error was somewhere else. It was in the parent command (i am using dpy lib) and having some code in it was messing up the create table stuff in sub command

i should read my tracebacks better lol, thank you

[torn sphinx]

Ahhh ok yes thats an easy fix, and help me understand better. Thanks so much i was worrying that someone at my work, will shout at me for this bad design i made 😅 . Thanks very much @proven arrow , i will have to just add this extra column.

[pulsar kestrel]

why i am getting connection refused

i try to connect my pi to database

[proven arrow]

Are you trying to connect on localhost?

If so then just use localhost as the host name

[pulsar kestrel]

@proven arrow

access denied

[proven arrow]

Then make sure that user exists, and has access to the database

[pulsar kestrel]

oh ok

[steady lava]

@proven arrow thank you very much for your answer. It makes sense now why sql is more preferable than using JSON, yeah JSON is just like any other dictionary object in python, only the difference is between dict objects in python store in memory at run time and then destroyed once the python process terminates, while we can save this dict. Object into a JSON file for later purpose.
I was planning to use sql but right now I don't have any experience with it.. so this might be a good chance to learn by implementing it. Also, Different JSON files for Different user is not so good idea hm.. it just increases memory, suppose having more than 5 users running the same CLI music player, and each have their own config files or playlist file. .

[wintry stream]

reading back a bit @trim knoll from your error one thing very clearly that i can see is that you are using fstrings for your queries

this is VERY dangerous and allows for SQL injection

https://www.youtube.com/watch?v=_jKylhJtPmI

always use SQL arguments, don't use fstrings/format

[pulsar kestrel]

@proven arrow i created new use and grant all permission, so it's working now

[torn sphinx]

uh do i put this here?
So i want to look for a item in inventory with this code for thing in users[str(user.id)]["inv"]: n = thing["item"] a = thing["amount"]
but i want it to only look for the item fishingrod. how can i do that?
right now it scans al the items but i want to make it so it only looks for fishing rod and the ammount of fishingrods a person has

{"728265216453771274": {"wallet": 12839.0, "bank": 0, "inv": [{"item": "apple", "amount": 1}, {"item": "fishingrod", "amount": 0}]}

im using json btw

[sharp nest]

@trim knoll fair, i saw f string and was like "wtf" .
consider using sqlalchemy, makes it all nice and easy. https://docs.sqlalchemy.org/en/14/core/metadata.html

[wintry stream]

asyncpg also supports sql arguments

very easily

[limber stone]

Is there a good method to updating a row where you may not know the data that changed? For example:
UUID | x | y | z | abc
The UUID wont change, but x, y, z, or even abc may change. However, you are just giving a block of data that contains x, y,z, and abc with no clue as to what changed.

I could just do on conflict do update set literally every single column but that just seems...inefficient?

[torn sphinx]

is mini database a Dictionary

?

[weak tinsel]

anyone who uses asyncpg can help me?

async def add_new_value_to_db(name):
    db_connection = await asyncpg.connect(host=host,
                                        password=password,
                                        user=user,
                                        port=port,
                                        dsn=dsn,
                                        database=database)
    try:
        await db_connection.execute("CREATE TABLE IF NOT EXISTS BOTGAMEDB(Name TEXT(30) PRIMARY KEY,POINTS INT(10) NOT NULL,DEFAULT POINTS = 500)")
    except:
        pass
    
    try:
        await db_connection.execute(f'INSERT INTO BOTGAMEDB VALUES("{name}",500)')
        await db_connection.commit()
    except:
        pass
    await db_connection.close()

i call this function from a discord.py command

[torn sphinx]

you mean

@bot.command()

[weak tinsel]

yes

[torn sphinx]

what is you'r problem

[weak tinsel]

it doesnt create the table

[torn sphinx]

you mean

Tuple?

[weak tinsel]

huh?

[torn sphinx]

tuple

[weak tinsel]

no it doesnt create the table in the database

[torn sphinx]

ah

[weak tinsel]

i suppose mysql and postgre have the same syntax

cuz i know mysql pretty well

[torn sphinx]

hmm

is it giveing error?

[weak tinsel]

uhh thing is

im hosting it on heroku

[torn sphinx]

Syntax Error?

[weak tinsel]

so i dont know how to get errors

[torn sphinx]

ah

stranger

[weak tinsel]

ok first of all

how to get error logs from asyncpg module?

[torn sphinx]

I am not use asyncpg module

[weak tinsel]

ahh ok

[torn sphinx]

How do you make a database? (So people can download your program and add to the database)

IF thats possibly

[weak tinsel]

what sort of database?

[torn sphinx]

oh

hm

idk

[weak tinsel]

SQL?

No SQL?

[torn sphinx]

for python

SQL I guess

[weak tinsel]

ahh ok

[torn sphinx]

not for a website though

[weak tinsel]

i use mysql.connector

and connect to mysql

[torn sphinx]

import mysqsl?

.connector

[weak tinsel]

import mysql.connector```

very simple to use

[torn sphinx]

okay

also

how do you make a discord bot with python

[weak tinsel]

but first you gotta learn mysql

[torn sphinx]

how do you make a discord bot with python
@torn sphinx easy

[weak tinsel]

how do you make a discord bot with python
@torn sphinx #discord-bots

[torn sphinx]

ok

[weak tinsel]

i would suggest learning postgre for dpy tho

[ashen lintel]

import mysql.connector

mydb = mysql.connector.connect(
  database = "str_fivem",
  host = "localhost",
  user = "root",
  password = ""
)

database = mydb.cursor()

def p(text):
  print(text)

def str_reg(banned):
  banned = input()

  if banned == "1":
    p("Succes")

    str_sql = ("UPDATE vrp_users SET banned = '0' WHERE id = '%s'")
    database.execute(str_sql, (banned))

    mydb.commit()
  else:
    p("failed")
    return banned```

UPDATE vrp_users SET banned = '0' WHERE id = '%s'```

[pseudo summit]

why redef print for less readability?

[ashen lintel]

it's not changing to 0

[pseudo summit]

@ashen lintel Your statement is adding quotes where not necessary. Since you already have a placeholder (should be %s not #s), you don't ALSO need ''

[ashen lintel]

@pseudo summit