databases

[frozen fossil]

@rich trout what is order

[rich trout]

used in ORDER BY?

[frozen fossil]

order isnt even a column

https://raizo.de/p/LiToa.png

[rich trout]

you wrote 'order in'

[frozen fossil]

uh

ig it should be id

actually no

i dont even know what that is

@rich trout what is order even supposed to be

[rich trout]

no idea

What were you getting the ids from?

maybe it ought to be id

[frozen fossil]

lmfao

so i need the tuple?

                cursor.execute("SELECT id FROM orders WHERE userid = %s", (userid,))
                cache = cursor.fetchall()
                ids = tuple(i[0] for i in cache) # useless TODO: USE

thats where i get my ids

[real timber]

I'm wondering about something relating to google cloud storage and bigquery, but there's SQL as well that I'm unsure of.

Basically what I have is a bunch of daily data in storage. I was thinking that it would be good to pull into BigQuery so that I could query through it, but I'm not actually sure how I would go about that.

All of the data is in flat file CSV's, I don't have any intention to build a relational database from it ( I don't have the time or the experience really ). But I would like to know any advice on how others would approach something like this

I've written something which will, for a given file, download it from each day and create a local csv that I can use for analysis, but this feels like the kind of thing that I should be doing from within BQ (and then downloading) rather than downloading a load then creating locally

[rich trout]

@frozen fossil the solution we were working with is called a subquery, where you do one query using the results of another. Your first query is the one you just posted-- SELECT id FROM orders.. WHERE. The second query is the one you are going to use that in, SELECt... FROM... WHERE ... IN (first query). Since mysql supports nesting queries, you can put the first query inside the second one like I showed to solve your issue

[frozen fossil]

alright so i can remove first query

mysql.connector.errors.InterfaceError: No result set to fetch from.

hmm yes

[real timber]

Is the question I asked suitable for this channel?

[pure cypress]

Yes I think it's fine

[full kite]

Good Morning room

[frozen fossil]

@rich trout https://raizo.de/p/3ENAl.png getting somewhere

[torn sphinx]

So I want to use the count function to assign a an incremented int to each record in my table that meets a certain condition

doing this doesn't work just returns 3 and the last record data

I also want to be able to later query for a record by case number

I was told this

the count result becomes a column, when you wanna update that case log just query for that specific case

Is this possible?

[rough hearth]

I'm working on a project with sqlachemy, and I want to have a number of entries for users of the program, and for each user, a variable number of entries about things they want the program to track

[copper echo]

So i have two tables - guild_settings1 and guild_settings2 and i want to move data from first to second but by guild_id column

how can i do this?

[rough hearth]

seems like everyone wants help with sql

[torn sphinx]

lol

[winter ivy]

Any Flask-SQLAlchemy experts in the house ?

I've got a nasty challenge for you...

[wanton sentinel]

go for it

[celest blaze]

maybe gal needs to highlight Alex

otherwise how will I get my entertainment

[rough hearth]

Trying again to see if anyone's available: I'm working on a sqlalchemy thing, and I need to have a table where the values in one of the columns is another table

[shadow scaffold]

Hi there ! I just finish to create my database in Flask-Sqlalchemy and I was wondering if someone could give me his feedback on my schema (cf screenshot). Thank you so much. Also if you have question related flask-sqlalchemy i'm not an expert but maybe I could help. 🙂

[broken cloud]

Hey guys
So I've been trying this query for SQl
cur.execute("SELECT * FROM GIVEAWAY ORDER BY RAND() LIMIT 1;")
on stackoverflow
For selecting a random record
It keeps returning
Command raised an exception: OperationalError: no such function: RAND tho

[minor ruin]

Seems clear...

RAND isn’t supported?

[frozen fossil]

how can i move a row to a other table?

meant with rollbacks

[fossil leaf]

ivs = 0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31
            hpiv = rand.choice(ivs)
            CCiv = rand.choice(ivs)
            CCDEFiv = rand.choice(ivs)
            FRiv = rand.choice(ivs)
            FRDEFiv = rand.choice(ivs)
            MGC = rand.choice(ivs)
            SPD = rand.choice(ivs)
            cuz_u_mean = Con[1] + 1
            amount_total = None
            sql = ("INSERT INTO mons(user_id, xp, Level, Name, HPiv, CCiv, CCDEFiv, FRiv, FRDEFiv, MGC, SPD, Total, num, item) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?)")
            val = (str(ctx.author.id), int(0), int(5), str(result[0]), int(hpiv), int(CCiv), int(CCDEFiv), int(FRiv), int(FRDEFiv), int(MGC), int(SPD), str(amount_total), str(cuz_u_mean), None)
            c.execute(sql, val)```

i get Command raised an exception: TypeError: can only concatenate str (not "int") to str

someone plz help

[frozen fossil]

try:
                cursor.execute("INSERT INTO purchases SELECT * FROM orders WHERE userid = %s", user.id)
                cnx.commit()
                cursor.execute("DELETE FROM orders WHERE userid = %s", user.id)
                cnx.commit()
            except Exception:
                cnx.rollback()
``` tried doing this but cant seem to get it to work

[frozen fossil]

sorry for the ping but could u help me out with moving a row to a other table @rich trout

[rich trout]

going to have to tell me what "not working" means. Row is not moving? There an error?

[frozen fossil]

@rich trout no error, just not moving

http://jelbrek.icu/i/e2zXX.png

[rich trout]

oh, right

You're not printing your error within your except block

So if there's a syntax error it would do this

try

except Exception as e:
    print(e)
    ...

[frozen fossil]

alright

1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '%s' at line 1

[fringe tiger]

user.id try with (user.id,)

[frozen fossil]

tried that already

[rich trout]

You've got two instances, and that error is pretty clean cut that the replacement is not occurring

Make sure not to forget the trailing ,

[frozen fossil]

um

could u explain it to me like

im 5?

oh wait i did it on one of the querys

nvm still the same

[fringe tiger]

iirc last time you had the same errror and turned out to be the missing , that we sent you multiple times 😛

[frozen fossil]

@fringe tiger http://jelbrek.icu/i/XoGn0B.png ;_;

[fringe tiger]

not this time I suppose 😛

[frozen fossil]

._.

[rich trout]

Try wrapping the select in ()

(select * from... )

[frozen fossil]

alright

@rich trout http://jelbrek.icu/i/dNIA.png

❤️

nearly done with the project

[pure cypress]

Not sure. Could you reproduce the issue with another spreadsheet?

Like, a blank spreadsheet and only write to line 1284

Well, it's a row not a line

[rich trout]

More likely related to the content, if the content of your line is greater than (I want to say 8192 characters?) excel will not load it right

[tidal apex]

Hi, I have a postgres / psycopg2 table that has a column of timestamptz values, but I need them as datetime objects in python. I tried writing a format string, but couldn't get it to work with the format that psycopg2 returns

[rich trout]

What's your attempt at a format string, and what's the format it returns?

[tidal apex]

the format it returns is mostly doable, except the timezone info which is / can just be +00, whilst pythons parse timezone requires +-HHMM

so it cant match without the minutes

[rich trout]

It should be returning them as datetime objects already, but just with a special timezone value

[tidal apex]

Well that's odd

[tidal apex]

Okay apparently, if you do SELECT (foo, bar) instead of SELECT foo, bar it returns the fields as strings

What a pain to debug

[rich trout]

well that's just unpleasant

[torn sphinx]

is there an easier way to write this

SELECT city, LENGTH(city) FROM station
order by length(city) desc, city asc
limit 1;
SELECT city, LENGTH(city) FROM station
order by length(city) asc, city asc
limit 1

[vital girder]

do you want to join the results?

[torn sphinx]

yeah

so you want me to use WITH and JOIN?

[torn sphinx]

anyways.. i'm done with that one

now.. something more complex

query to print the names of those customer(s) and the price of their order(s), who have the maximum price order within the first ten years of the first order

I dont get the second part

How do I select range of orders 10 years from first date

got it

dateadd and partitions

[rough hearth]

in sqlalchemy, how do you establish a relationship to another table?

I think the documentation I've been looking at is for having a relationship to a row in another table.

[broken cloud]

Hey guys

How do I make a column auto increment/

    cur.execute("""CREATE TABLE IF NOT EXISTS `GIVEAWAY_WINNER` (
    `date` DATETIME NOT NULL,
    `user_gw2_tag` VARCHAR( 42 ),
    `discord_uid` INT( 16 ),
    `discord_tag` VARCHAR( 40 ),
    `prize` VARCHAR ( 42 ),
    `row_id` INTEGER PRIMARY KEY AUTOINCREMENT
    );""")

Thats the table I created

Command raised an exception: OperationalError: table GIVEAWAY_WINNER has 6 columns but 5 values were supplied

Please help ❤️

I'm using SQLITE

[rich trout]

CREATE TABLE IF NOT EXISTS is your problem

presumably you already created it, except without the autoincrement attached to the column you were using

You'd need to either drop the table first to adjust it, or use an alter column command instead

[broken cloud]

No thats just to tell you the structure

it was only made once.

[rich trout]

hm.

Are you sure?

[broken cloud]

cur.execute("INSERT INTO `GIVEAWAY_WINNER` VALUES (?,?,?,?,?)", ("03-December-2019", "Shiv.5781", "210065940308688897", "Shiv#2443", "Eternity"))

Thats the query I executed

Assuming row_id should auto increment, I did not mention it

so keeps saying there's 6 fields and I'm only inputting 5

I dont mind dropping table again since its an empty table for now

[rich trout]

!e ```py
import sqlite3 as s
c = s.connect(":memory:").cursor()
c.execute("""CREATE TABLE IF NOT EXISTS GIVEAWAY_WINNER (
date DATETIME NOT NULL,
user_gw2_tag VARCHAR( 42 ),
discord_uid INT( 16 ),
discord_tag VARCHAR( 40 ),
prize VARCHAR ( 42 ),
row_id INTEGER PRIMARY KEY AUTOINCREMENT
);""")
c.execute("INSERT INTO GIVEAWAY_WINNER VALUES (?,?,?,?,?)", ("03-December-2019", "Shiv.5781", "210065940308688897", "Shiv#2443", "Eternity"))
c.execute("SELECT * FROM GIVEAWAY_WINNER")
print(c.fetchall())

[delicate field]

@rich trout :x: Your eval job has completed with return code 1.

001 | Traceback (most recent call last):
002 |   File "<string>", line 11, in <module>
003 | sqlite3.OperationalError: table GIVEAWAY_WINNER has 6 columns but 5 values were supplied

[rich trout]

...

Ok then

[broken cloud]

Why is it this way?

there's only 5 values

[rich trout]

I don't know, it's not supposed to be

[broken cloud]

I mean syntactically everything is right, right?

[rich trout]

Perhaps it's related to the types you're passing, but I'm not sure

!e ```py
import sqlite3 as s
import datetime
c = s.connect(":memory:").cursor()
c.execute("""CREATE TABLE IF NOT EXISTS GIVEAWAY_WINNER (
date DATETIME NOT NULL,
user_gw2_tag VARCHAR( 42 ),
discord_uid INT( 16 ),
discord_tag VARCHAR( 40 ),
prize VARCHAR ( 42 ),
row_id INTEGER PRIMARY KEY AUTOINCREMENT
);""")
c.execute("INSERT INTO GIVEAWAY_WINNER VALUES (?,?,?,?,?)", (datetime.datetime.now(), "Shiv.5781", 210065940308688897, "Shiv#2443", "Eternity"))
c.execute("SELECT * FROM GIVEAWAY_WINNER")
print(c.fetchall())

[delicate field]

@rich trout :x: Your eval job has completed with return code 1.

001 | Traceback (most recent call last):
002 |   File "<string>", line 12, in <module>
003 | sqlite3.OperationalError: table GIVEAWAY_WINNER has 6 columns but 5 values were supplied

[rich trout]

No, it's not. Well, that's interesting

[broken cloud]

i know lel

[rich trout]

oh, duh

[broken cloud]

What is it

[rich trout]

You need to have a parameter set to None for your autoincrement column

[broken cloud]

as in?

[rich trout]

!e ```py
import sqlite3 as s
import datetime
c = s.connect(":memory:").cursor()
c.execute("""CREATE TABLE IF NOT EXISTS GIVEAWAY_WINNER (
id INTEGER PRIMARY KEY,
date DATETIME NOT NULL,
user_gw2_tag VARCHAR( 42 ),
discord_uid INT( 16 ),
discord_tag VARCHAR( 40 ),
prize VARCHAR ( 42 )
);""")
c.execute("INSERT INTO GIVEAWAY_WINNER VALUES (?, ?,?,?,?,?)", (None, datetime.datetime.now(), "Shiv.5781", 210065940308688897, "Shiv#2443", "Eternity"))
c.execute("SELECT * FROM GIVEAWAY_WINNER")
print(c.fetchall())

[delicate field]

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

[(1, '2019-12-03 17:42:07.950055', 'Shiv.5781', 210065940308688897, 'Shiv#2443', 'Eternity')]

[broken cloud]

😮

lemme try that

yay it works

@rich trout thanks a ton ❤️

[gilded narwhal]

you can replace the first ? with null in the query then pass in 5 values

i.e. c.execute("INSERT INTO `GIVEAWAY_WINNER` VALUES (null, ?,?,?,?,?)", (datetime.datetime.now(), "Shiv.5781", 210065940308688897, "Shiv#2443", "Eternity"))

[spare umbra]

Anybody has an idea how i could store a python dictionary of my modmails in MySQL?
Looks like this:

modmails{
    1234(guildID):{
        1234(userID):(messageID,created_at),
        5678(userID):(messageID,created_at),
        9101(userID):(messageID,created_at)
    }
    5678:{
        31233:(messageID,created_at)
    }
}

How would i do that in MySQL? new table modmails and colums for guild id (Primary key) and then furthermore the userids and stuff or what?

[delicate field]

Sorry, but you may only use this command within #bot-commands.

[icy shore]

Anyone around? I have a Python/MySQL question

so the code

for row in records:
            print("Id: ", row[0])
            print("Name: ", row[1])
            print("Email: ", row[2])
            print("password: ", row[3])
            print("\n")

works but what if I want to do

row = records
print (" ID: ", row[0])

that fails.

[violet comet]

?

what do you want to achieve here?

you only want values from one row?

[icy shore]

yes my SQL Query only returns one row because it is doing a username match so I want to do an IF statement on the password row[2] and not use a for loop for 1 single row

[violet comet]

what database library are you using?

[icy shore]

from flaskext.mysql import MySQL

?

if I do row = records and do a print statement I get this:

DEBUG: Row: ((51, 'Dogbert', '[e-mail]', 'DogBert11'),)

but row[3] does not return the password

[violet comet]

records[0] is the first row of your result set

[icy shore]

so how do I get the 3rd element in that row?

[violet comet]

incidentally, which fetch method are you using for this query?

[icy shore]

fetchall

records = cursor.fetchall()

[violet comet]

you can either 1) access it with records[0][whatever column index] or 2) use fetchone() and access row[column index] directly

[icy shore]

so my query always only returns one row all records are unique

[violet comet]

yeah, sure, but if you use fetchall(), you are going to get a list of rows anyway

a list of rows containing only one row

[icy shore]

ah ok - makes sense. should I switch to fetchone since I always only have 1 ?

[violet comet]

that's for you to decide

[icy shore]

LOL

fair enough

[violet comet]

both solutions above give the same result

[icy shore]

thanks 🙂

ok thanks I will play with it some more. I am so use to C and how arrays work, Python messes me up

but again its been like 10 years since I coded 🙂

[fathom pewter]

How would I make a sort of profile creation system with a discord bot?

[torn sphinx]

lol

they're writing documentation like memes now..

[spare umbra]

Anybody has an idea how i could store a python dictionary of my modmails in MySQL?
Looks like this:

modmails{
    1234(guildID):{
        1234(userID):(messageID,created_at),
        5678(userID):(messageID,created_at),
        9101(userID):(messageID,created_at)
    }
    5678:{
        31233:(messageID,created_at)
    }
}

[torn sphinx]

Hey there, i'm new to databases and object oriented programmation in general, so i was wondering if this is a good setup for a data base and how to create a query method, hints ? xD

class Database:
	def __init__(self, db):
		self._connect = sqlite3.connect(db)
		self._cursor = self.connect.cursor()
	def commit(self):
		self.cursor.commit()
		
	def fetchone(self):
		return self.cursor.fetchone()
	
	def fetchall(self):
		return self.cursor.fetchall()
		```

[toxic cedar]

"Can't connect to MySQL server on 'www.remotemysql.com' (10061)")

[inner folio]

@torn sphinx I like to implement the dunder methods when using databases.

[torn sphinx]

Oh

How can u use them in this case ? @inner folio ?

I mean you can use the exit methode to close the database

But how would u use the others

[inner folio]

You could impletment next and len and iter these are good things for looping over databases or any list/dict type objects

[torn sphinx]

oh yeh

[inner folio]

like for instance once you have made you query and collected your results you return them into a instance variable and let next iterate over it. so a for loop could look like this 'for row in Database(db=db, query="select * from blah blah;'"):'

[torn sphinx]

Yeh

[inner folio]

for loop could look like this 'for row in Database(db=db, query="select * from blah blah;'"):'

whops

[torn sphinx]

I mean u can just use for loops directly

But it seems like a more interesting way

[mortal torrent]

How can I add a new row of data to an existing CSV file using pandas?

I'm going to get the user inputs for each of the rows, but I don't know how to append the row with these details

For example, I have the file:

[gleaming loom]

Using SQLAlchemy, how would I say, "Insert this row if it doesn't exist"

[full canyon]

yo

with aiomysql

if i do one connection and execution a second will it be bad for the computer

nvm

[polar isle]

Using psycopg2 why does this not update my database? It doesnt give any errors or anything

username = "Drew"
conn = psycopg2.connect(host="localhost", database="my_database", user="postgres", password="my_password")
cur = conn.cursor()
cur.execute("UPDATE online_users SET usernames = array_cat(usernames, %s)", (username,))
conn.commit()```But if I run the same code in a command terminal I get
`psycopg2.errors.InFailedSqlTransaction: current transaction is aborted, commands ignored until end of transaction block`
and I believe that means that its a bad SQL statement. I'm not sure where its 'bad' though
Any help is much appreciated

[sweet nebula]

The above image shows 3 tables in my database. I believe it to be in 2nd Normal form as all fields in each table depend on the primary key. However in AvailableSolvers Access is also dependant on ClassID and SolverID combined. However, the combination of ClassID and SolverID is unique for each ClassSolverID. ie they could be used as a combined key. Does this mean the database is or is not in 3rd normal form???

[torn sphinx]

Hi there im trying to do some stuff with databases (in a discord bot) and my database is suppose to count up of witch type of punishments you have gotten yet it dosent write aneything to my db aneyone know why? py cursor.execute(f"SELECT user_id, kicks FROM logs WHERE guild_id = '{ctx.guild.id}' and user_id = '{user.id}'") result1 = cursor.fetchone() if result1 == None: sql = ("INSERT INTO logs(guild_id, user_id, kicks) VALUES(?,?,?)") val = (ctx.author.guild.id, user.id, 1) cursor.execute(sql, val) db.commit() else: if result1[1] == None: sql = ("UPDATE logs SET kicks = ? WHERE guild_id = ? and user_id = ?") val = ("1", str(ctx.guild.id), str(user.id)) print("none") else: lvl = int(result1[1]) sql = ("UPDATE logs SET kicks = ? WHERE guild_id = ? and user_id = ?") val = (lvl + 1, str(ctx.guild.id), str(user.id)) cursor.execute(sql, val) db.commit() print("not none")

[candid hedge]

Did someone ping me here'

[plain radish]

@candid hedge yes @toxic cedar ghostpinged you.

[toxic cedar]

Yeah it was by accident

you sneeky guy wtf,

[candid hedge]

@plain radish thanks for checking! @toxic cedar np 🙂

[plain radish]

lol if you accidentally ping it's fine. just leave it up and say sorry or whatever to make it clear it was an accident

deleting it just makes it super confusing though lol

[candid hedge]

^^

[torn sphinx]

cant i give a role a color? await guild.create_role(name='--AFK--', colour=0x22ff22)

worng channel

[torn sphinx]

in psql: if i have a table with characters / units, and every character has multiple skills how can i query one character with all its skills at once? i know how to query both separately but im not sure if its possible in one query?

[ionic pecan]

are skills a separate table? an array?

[torn sphinx]

@ionic pecan it's a separate table. basically one character has multiple skills

[ionic pecan]

i'm assuming the skills table references the characters somehow?

in that case you can use a JOIN

https://www.postgresql.org/docs/current/tutorial-join.html

[torn sphinx]

yeah there's references. and yeah im aware, my concern is more like i want to return some character information plus their skills. i know this can be easily done with two separate queries, one to grab the character data and the other to grab the skills. not sure how a join without getting repeated data would be possible

[pearl tree]

in sqlalchemy, is it efficient to write tables that you know will only be linked to another table polymorphically? I am unsure how this would affect performance of a database using polymorphicism on classes.

[pearl tree]

did i ask a weird question 😐

[rich trout]

SQLAlchemy implements polymorphism via foreign keys, so as fast as that I would imagine. However, they do note in their documentation that when doing a polymorphic get request they do a bunch of left outer joins, which are less performant than simply having everything in one table. I don't expect it to be some sort of major performance penalty, but that does depend on your tables, layout, and queries I imagine

[pearl tree]

okay, thankyou, where in the documentation does it say that about inner joins btw?

[rich trout]

https://docs.sqlalchemy.org/en/13/orm/inheritance_loading.html#setting-with-polymorphic-at-mapper-configuration-time

LEFT OUTER JOIN, actually

[hasty hinge]

Should I store a link in a DB as a VARCHAR or TEXT?

[rich trout]

IMO, depends on what kind of link and your use case.

TEXT and BLOB is stored off the table with the table just having a pointer to the location of the actual storage.

VARCHAR is stored inline with the table. VARCHAR is faster when the size is reasonable, the tradeoff of which would be faster depends upon your data and your hardware, you'd want to benchmark a realworld scenario with your data.

Some urls with tracking junk can be very long, but usually a varchar suffices for reasonable usage

[hasty hinge]

I don't really know what is better, I'll just have discord image links, like this:
https://cdn.discordapp.com/attachments/639939405393428491/653059974322454541/ThisIsAnExampleLink.png

They have approximately between 80 and 150 characters

[rich trout]

Then a varchar will probably work fine for you

[hasty hinge]

Thanks! I'll use a varchar then

[chilly elbow]

If anyone here is using SQLAlchemy does anyone have any ideas on how to make a query being returned include its relational columns?

I have a table called mentors which has a one-to-many relation with a table called timeslots, I'm trying to make it so that when I query for a mentor in the table mentors the mentor that is returned includes the relational column timeslots

If that makes sense?

[chilly elbow]

nvm i solved this

[chilly elbow]

turns out to have the relationship be resolved as you get the parent object you must set the lazy attribute of the relationship to immediate

[torn sphinx]

The database, 'D:\OneDrive\Code\Python3\Discord bots\SakuraBot\sakura.db', does not exist and cannot be created because the containing directory, 'D:\OneDrive\Code\Python3\Discord bots\SakuraBot', is not writable. The application is unusable until you make it writable.

windows 10 updated and now phplite admins says this

and my database is doesn't work

[torn sphinx]

bros i feel like bombing microsoft

I was working on my bot

and then they forced me to update windows now shit broke

I can't code

[meager sable]

the folder is read only

make it read/write

[strong bane]

@torn sphinx get it off onedrive and put it in some normal folder

[torn sphinx]

it's not read only

I closed one drive, I don't think it's the reason but I'll try

@strong bane Thank you, that fixed it, F onedrive, it's so bad honestly

before the update I was having issues not being able to make an exception for the database, constantly being re-written meant one drive would not be able to sync and throwing errors

I should've just stick to github for backup

[hasty hinge]

Why is it better to use int numbers as ID instead a varchar like a username as ID?

[proven wagon]

If you dont want to let people have the same username having a username as id is ok .. @hasty hinge

[hasty hinge]

Ohh, that is my case, I am making a DB with unique usernames, so I guess it is fine, thanks a lot! @proven wagon

[proven wagon]

👍

[cyan shoal]

hi

How could make two classes with the same name, but only one is activated via an if statement

nvm actually

[tranquil quarry]

whats wrong with this ```py
def select_user(conn, user):
"""
Get user xp and lvl in a array
:param conn: the Connection object
:param user:
:return xp, lvl:
"""
cur = conn.cursor()
sql = """SELECT * FROM ranks
WHERE id = ?"""
cur.execute(sql, (user,))

rows = cur.fetchall()

xpandlvl = []

print('cur: ', cur)

for row in rows:
    xpandlvl.append(row)
    #print(row)

print(xpandlvl)
return xpandlvl``` its returning an empty array. and on ranks table theres id, xp and lvl like this https://gyazo.com/ebfa2287967622982b9806e54b6213c0 (using sqlite3)

[inner pecan]

Can anyone explain why I am seeing very slow fetchmany() performance with sqlite3 no matter what size value I use? its taking 3-5 minutes to fetchmany 9million rows in chunks of 1000-100,000

myQuery = "SELECT {} FROM '{}' A JOIN '{}' B USING(Symbol)".format(ColumnsToSelect,table1_name,table2_name)
c.execute(myQuery)
while c.fetchmany(100000) != []: # this will break when no more rows will be returned i.e end of table
    nr+=1
    print(nr)
    results = c.fetchmany(100000)
    #code to process results goes here but I commented it out to test the fetchmany speed```

executing myQuery in SQL takes 9 seconds, not the 300 or more its taking in python with fetchmany

9477056 rows returned in 9646ms from: SELECT A.*, B.* FROM 'cg9-ny4-l1_Table1_2019-12-08' A JOIN 'cg1-ch-l1_Table1_2019-12-08' B USING(Symbol);```

[pearl tree]

in SQL is it possible for a table to hold data when a specific parameter is reeached?

For example, I have two tables User and group

User by itself doesn't have any hierarchy unless it is part of a group, a user can also be in multiple groups.

When a group looks at a user it also can see what hierarchy that user is for that specific group.

When a user looks at its own hierarchy it will see all of its hierarchies for all of the groups it is in.

Is this possible or will I need to make a 3rd table somewhere

If I was to implement something like this in sqlalchemy
i would expect these results

>>> user = User()
>>> group1 = Group()
>>> group2 = Group()
>>> group1.user.append(user)
>>> group1.user.hierarchy = Hierarchy(level = "Master")
>>> group2.user.append(user)
>>> group2.user.hierarchy = Hierarchy(level = "Member")
>>> user.hierarchy
"group1: Master, group2: Member"
>>> group1.user.hierarchy
"Master"
>>> group2.user.hierarchy
"Member"

[torn sphinx]

I think I would make a third table since they can be in multiple groups

If it was one group then you could store the group_id in a column in the user table

but with multiple groups that'd get messy imo..

so user_groups table would have two columns user_id,group_id

[torn sphinx]

can someone help in aiomysql? in dms

[ionic pecan]

no, ask here instead

[raven garden]

how do i create user databases for a discord bot?

[balmy flame]

can someone help me with this error code: 1142 CREATE View Command denied in MySQL.

[torn sphinx]

quick question, how can I delete the first query ?

say I use select, returns a list of queries, I wanna delete q[0]

[rich trout]

@torn sphinx The easiest way to do that is to select the ID column, then DELETE FROM table WHERE id = ? and pass in your id that you just got

[torn sphinx]

thank you, that was my solution as well ^^

[rich trout]

Yes, you really should. Depending on your database setup, not closing the cursor could cause it to undo your changes, and not committing could cause your changes to lock up other changes, or be undone when you quit

[torn sphinx]

I did a select query, then got the ID of the first one, then did a delete query

[rich trout]

👍

[torn sphinx]

wdym closing the cursor ?

[rich trout]

cursor.close()

It's a way of telling the database that "I'm done with this", so it can free up connection resources

There's also a context manager expression for it:

from contextlib import closing
with closing(connection.cursor()) as cursor:
    cursor.execute("stuff")

[torn sphinx]

await self.client.cur.execute("CREATE TABLE if not exists strike(server BIGINT, member BIGINT, amount INT)")```

`Command raised an exception: ProgrammingError: (1064, "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 'member BIGINT, amount INT)' at line 1")`

??

anyone?

@rich trout sorry for tag but can u help?

[rich trout]

@torn sphinx SERVER is a reserved myql name, you'll need to wrap it in backticks to use it as a column name

``

[torn sphinx]

oh

@rich trout still same error i changed server to guild

[rich trout]

Try putting a space after strike? but other than that that line seems alright

[torn sphinx]

nope still

[rich trout]

Has the error message changed at all?

[torn sphinx]

nope

[rich trout]

Try ``'ing every column, but if that doesn't work I'm not sure what's wrong

[torn sphinx]

wdym?

[rich trout]

strike (`server` BIGINT, `member` BIGINT...

[torn sphinx]

ok

ugh same error

[rich trout]

Can't be, since we changed the input. It should be mirroring your backticks in the syntax error, no?

[torn sphinx]

not same error

[rich trout]

kk

[torn sphinx]

in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'member = 552008092821487627' at line 1")

i think its another line

[rich trout]

So the quoting was it, then

yeah, that's another line

probably have more columns to quote

[torn sphinx]

ye it created the table

btw what the quoting do?

[rich trout]

Makes sure mysql knows you're definitely referring to a column, instead of maybe something else

[torn sphinx]

oh

[oblique fulcrum]

So I’m tying to use MongoDB. I’m running a discord bot off a raspberry pi (if that helps at all). I’m getting this error:

Extension 'cogs.currency2' raised an error: ConfigurationError: query() got an unexpected keyword argument 'lifetime'

I have looked through the MongoDB troubleshooting guide and the examples to no avail.

My code:

from discord.ext import commands, tasks
import discord
import dbl
import pymongo
import dns
import json

class Currency2(commands.Cog):
    def __init__(self, bot):
        dbclient = pymongo.MongoClient("mongodb+srv://MongoDB0:[e-mail]/test?w=majority")
        db = dbclient.test
        
    @commands.command(name="boop")
    async def boop(self, ctx):
        ctx.send("bonk")
            
def setup(bot):
    bot.add_cog(Currency2(bot))

[rich trout]

Hopefully you removed the password from that connectionstring

[oblique fulcrum]

I forgot to remove but changed it afterwards realizing my mistake

As well as the dbl client password

[rich trout]

But that looks like some sort of versioning error in pymongo, not sure what's the deal there. Unless you're manually calling query() somewhere and that's not shown above

[oblique fulcrum]

Mk, thanks

[tranquil torrent]

kk

[tranquil torrent]

can we use 3 and statements in mysql?

like condition 1 AND condition 2 AND condition 3

........ AND condition n

[ionic pecan]

yes

[tranquil torrent]

with OR is it same?

Like condition 1 OR condition 2 OR condition 3
........ OR condition n

[ionic pecan]

yes

[tranquil torrent]

k thx

what if a statement is mixture of AND and OR

Like condition 1 OR condition 2 AND condition 3 OR condition 4
........ AND condition n

[ionic pecan]

yes

[tranquil torrent]

How would that work

?

[ionic pecan]

what have you tried?

[tranquil torrent]

Nope

I did not tried

[torn sphinx]

So say I have a list like this right [ { "id": 1000, "type": "BigBox", "name": "Mall of America", "address": "340 W Market", "address2": "", "city": "Bloomington", "state": "MN", "zip": "55425", "location": { "lat": 44.85466, "lon": -93.24565 }, "hours": "Mon: 10-9:30; Tue: 10-9:30; Wed: 10-9:30; Thurs: 10-9:30; Fri: 10-9:30; Sat: 10-9:30; Sun: 11-7", "services": [ "Geek Squad Services", "Best Buy Mobile", "Best Buy For Business" ] }, { "id": 1002, "type": "BigBox", "name": "Tempe Marketplace", "address": "1900 E Rio Salado Pkwy", "address2": "", "city": "Tempe", "state": "AZ", "zip": "85281", "location": { "lat": 33.430729, "lon": -111.89966 }, "hours": "Mon: 10-9; Tue: 10-9; Wed: 10-9; Thurs: 10-9; Fri: 10-10; Sat: 10-10; Sun: 10-8", "services": [ "Windows Store", "Geek Squad Services", "Best Buy Mobile", "Best Buy For Business" ]} ]
I want to get all data associated with the 'id' of 1002 what would be the best way to do that?

[ionic pecan]

what have you tried already?

[torn sphinx]

Nothing because I'm not sure where to start. I know how to get individual pieces of data that's about it

I know one way I could do it but it's not very efficient and I'm hoping there is just a simple way to do this that I don't know

[torn sphinx]

ping if anyone responds

[noble hill]

I have my User model
And I need to have the ability to login with twitter which will create a User entry into my user table and then will also add an entry in to my twitter table which has linked twitter accounts
If the user has already logged in with a different oauth method then it will just add an entry to twitter table
https://docs.sqlalchemy.org/en/13/orm/inheritance.html
this seemed promissing but I dont rlly know

I am using sqlalchemy btw

[rich trout]

While you could use inheritance and polymorphism, it will probably be simpler just to include an optional "sourced from oath" foreign key, that way they remain a regular user with additional twitter based details

[noble hill]

can you explain what foreign key is?

I really dont understand what they are

[rich trout]

Both under and over the hood are quite similar--after all, that essentially is what sqlalchemy's polymorphic inheritance does--but I think it would be simpler to implement and manage. It would use less "Magic"

Think of them as references

[noble hill]

okay

alrighty

I know what I will do then

[rich trout]

A "foreign key" is a spot in your table for the id of an item from another table, and the database make sure it's always pointing to a valid item

SQLAlchemy has some convenience methods (in the docs under "relations") that allow you to treat foreign keys as simple properties of python objects. Like, user.twitter_details.handle

[noble hill]

@rich trout do I have to point to the id or can I point to the entire entry?

like do I have to point to the id of the User entry or can I point to the entire user entry lol

[rich trout]

Generally you point at the ID, which provides you the entire entry

[noble hill]

how does it provide me the entire entry?

I can look up the rest, but that requires another query

[rich trout]

While it only stores the ID locally, when you request objects through a query (and have not enabled lazy-loading), sqlalchemy will insert additional details into the query to pull the entire other object as well

[noble hill]

I see

Is this correct? ```py
user_id = Column(Integer, ForeignKey('user.id'))

[rich trout]

Yep

You'd also want the relationship line--user = relationship("User")

[noble hill]

thanks

[noble hill]

@rich trout SQLAlchemy isnt creating all my tables wtf

[tranquil torrent]

Hello

In MySQL how multiple ORDER BY statements work

How can both the conditions satisfy

HELP

<@&267628507062992896> are people available ?

[spiral burrow]

Please do not randomly ping people

[tranquil torrent]

uh

ELA cab u tell me any good server for MySQL

help

Cuz this server is Pythoniss

@spiral burrow

[spiral burrow]

If I knew the answer, I would have answered. I don't know why you would ping after I just said not to

[tranquil torrent]

I am sorry i did it cuz u will see

[spiral burrow]

I can see it just fine without the ping

[tranquil torrent]

Why its bad to ping

it will be much easy to see ping message

Umm ELA i think i should ping again........ are u there?

okay....

[tranquil torrent]

ok dont help

[torn sphinx]

maybe you shouldnt ping people who are busy?

[rough folio]

Hey guys im still stuck on a issue. Anyone here a data base expertish ?
I cannot seem to get my button to delete from the database.
I can insert, cannot remove. Its a treeview list

https://github.com/nzsnapshot/Snaps_Rebuild__List - Here is the code

sqlite3.InterfaceError: Error binding parameter 0 - probably unsupported type. - This is the error I recieve

[limber stone]

I'm using asyngpg as my library for postgresql. I'm trying to use the LIKE operator but I can't seem to figure out how to use it in asyncpg without it erroring.

async def get_tag_by_alias(self, alias):
        async with self.bot.con.acquire() as con:
            try:
                alias = await con.fetchrow(f"""SELECT tag_text FROM tags WHERE aliases LIKE %$1%;""", alias)```

I get a synteax error near `%`

[toxic rune]

Have you tried doing LIKE $1 and making alias be %blablah%?

[shell ocean]

anyone can give me advice on how I should design my backend?

a big part of the app will basically be the ability to search for objects that match certain criteria

so two things I'm considering:

1. whether to query the database (probably MongoDB) for each search (so the filter is on the database side) or to keep a copy in memory and filter that directly

2. should my outward-facing APIs, and by extension my server, be sync or async? if I'm going with querying the database, it seems to make more sense to be async since I'm guessing blocking on database access will be slow...? but I don't have much experience in this

[limber stone]

@toxic rune I have not. But I've come up with a different way of doing this anyways. So I do not need to do this anymore

[torn sphinx]

Flask-sqlalchemy question. I'm trying to filter my query with request arguments, but it always returns an empty list. What am I missing?

@app.route('/msg/', methods=['GET'])
def show_msg():
    # Applying any filters passed with the URL query
    filters = {}
    if request.args.get('guild_id'):
        filters['guild_id'] = int(request.args.get('guild_id'))
    if request.args.get('channel_id'):
        filters['channel_id'] = int(request.args.get('channel_id'))
    if request.args.get('author_id'):
        filters['author_id'] = int(request.args.get('author_id'))
    print(filters)
    print(Message.query.filter_by(**filters).all())
    return jsonify(
        [to_json(i) for i in Message.query.filter_by(**filters).all()]
    )```

If no args passed, it returns all entries

Column names are correct

Even if i fit the criteria right in filter_by, it returns an empty list

[rich trout]

Then perhaps your data is not matching your query

the code you have there is correct

[torn sphinx]

Bigint in database, int in criteria

[rich trout]

but are there entries for your values?

[torn sphinx]

There are. Without filters it returns all of them

[torn sphinx]

Somehow the ids in the database are wrong. They show as correct when queried without filters and jsonified, but in the database they're stored as different numbers

I'll try to figure out why tomorrow

[toxic rune]

Taking into account that you can disable a command on one, multiple or all channels in your server, how would you put this on a database? I thought of different methods:

1. One row (guild_id, channel_id, command_name) per channel in that guild. But this would mean that if you add a new channel it wouldn't be automatically ignored.
2. One row per command per guild which has a column that defines whether that command is disabled globally (for the entire guild) and another column that holds a bigint array that defines which channels are disabled (in case it's not disabled globally.)

For 2)

id: 1
guild_id: 1234567890123456789
command_name: my_command
global_disable: False
disabled_channels: [1234567890123456789, 1345634565469]

[rich trout]

Don't be stingy with tables

One table for channel bans, one table with guild bans

[toxic rune]

I thought so, but wouldn't it be redundant to define it again? Especially since I also have custom cooldowns so I would have a few more columns in there. (I.e. in that example you'd add cooldown type, seconds and amount of times you can use the command before triggering cooldown (or rate/per/type in discord.py terms))

[rich trout]

That's what foreign keys are for

Say, command_cooldown, command_guild_bans, command_channel_bans, each "ban" would reference a command cooldown entry

[toxic rune]

There's no guarantee that I'd have a cooldown entry unless the guild owners decide to add a customized cooldown (which if they don't it uses the default which is on the bot side)

[rich trout]

Then make the foreign key nullable, and pass in null if there isn't one?

[toxic rune]

Hmm I see

[tranquil torrent]

I am having trouble in group by help e

Help

[torn sphinx]

!ask

[delicate field]

ask

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

• Don't ask to ask your question, just go ahead and tell us your problem.
• Try to solve the problem on your own first, we're not going to write code for you.
• Show us the code you've tried and any errors or unexpected results it's giving.
• Keep your patience while we're helping you.

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

[dreamy oracle]

though not mysql server,anybody knows how to use mysql;

[sturdy fable]

Hi everyone. I am rebuilding a discord bot that I made over a year ago. I have learned a lot since then and want to rewrite it with the things I have learned. One thing I am having issues with is the database.

I was originally using a sqlite db but I would really like to learn a network based db so I am picking postgres. The database will be updating every minute with quite a bit of data. Should I have a script that does all the writes to the database or should it be part of the discord bot? I currently have it as part of the bot but it seems like everyone separates it? Danny bot (Creator of the discord.py) doesn't seem to have his sql stuff in his bot so does the bot for this server. Are they hiding it from github or is it separate and why?

Please ping me so I know to look here. 🙂 Thank you again.

[rich trout]

For RoboDanny, the database code is under cogs/utils/db, and is in the form of a custom-made ORM

[errant knoll]

Right now I am using a json database for storing a load of words with their translation. However I need to be able to get the translated word very quickly after the original is entered, With about 390 words it is running quite slow. What would be a suitable database for this application?
Example of what I'm doing:

{
  "big": "grand",
  "the daughter": "la fille",
  "red": "rouge",
  "polite": "poli",
  "small": "petit",
  "no": "non",
  "I am": "je suis",
  "five": "cinq"
}

[rich trout]

sqlite

Or just not opening and saving the file each time you want a new word, and only on bot startup/shutdown

[errant knoll]

I already do that, I'll look at sqlite

[thorn jolt]

sqlite is the easiest

use peewee as ur orm

it's also the easiest

but for this solution the best thing would be elasticsearch

but it's a little bit complex

[errant knoll]

ok thank you

[austere garnet]

hey I am getting an error from MySql that says: 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 '?)' at line 1

this is my code: ```python
for i in lks:
print(i)
mycursor.execute("""INSERT INTO mylks (lktable) VALUES (?)""", i,)

not sure why this is giving me an error...

[rich trout]

You’re not triggering parameter replacement

Mysqlconnector uses %s

And expects data as a tuple, so “(i**,**)”

[austere garnet]

oooo ok

thank you

also is this a good way to assign values from a list into a single column?

[patent glen]

@austere garnet look at executemany

[austere garnet]

surething

ah! okay thanks

I have a question tho, how come these statements aren't simpler? Like why do I need to specify the number of inputs going in by %s or ?, why doesn't the library interpret them given a list and like a single placeholder?

[patent glen]

just the library design

though in part

i think it's because the underlying database wouldn't support it if you were doing the same statement multiple times with different size lists

plus people would be tempted to try to do "where x in ?", [some enormous list], and there is a limit to the total number of individual parameters

[wanton sentinel]

is it normal to find GoogleDriveDS under Application on a website
And I have the option to delete the database

[ionic pecan]

doesnt sound like it

[gaunt jay]

self.cur.execute("INSERT INTO `Server 1` VALUES (member[0],member[1],member[2],member[3],member[4],member[5],member[6],member[7],member[8])")```

discord.ext.commands.errors.CommandInvokeError: Command raised an exception: ProgrammingError: (1064, "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 '[0],member[1],member[2],member[3],member[4],member[5],member[6],member[7],member' at line 1")

e h...?

(member is a tuple btw)

[rich trout]

You don't access the members of the tuple inside the command

you insert a number of %s, each one where the next element of the tuple goes

(%s, %s, %s...)

[gaunt jay]

ohhhh cos sql doesn't use python syntax

ohh ok makes sense thanks

[gaunt jay]

discord.ext.commands.errors.CommandInvokeError: Command raised an exception: ProgrammingError: (1064, "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 '%d,%d,%s,%s,%d,%d,%d,%d,%d)' at line 1")

@rich trout 😦

self.cur.execute("INSERT INTO `Server 1` VALUES (%d,%d,%s,%s,%d,%d,%d,%d,%d)".format(member[0],member[1],member[2],member[3],int(member[4]),int(member[5]),member[6],member[7],member[8]))```

[rich trout]

It's completely separate from python's syntax

.execute("... VALUES (%s, %s, %s, %s)", member)

[gaunt jay]

ahhhh i just fully realised what it meant now

thanks

[wary panther]

I think I must have an issue with my SQL because the last thing my code prints is the print statement right before c.execute and then... just sits there.

SQL = '''INSERT INTO my_table(datetime, iv, i, ii, iii, list1, list2, list3, list4, list5, list6, list7, list8, list9, list10) VALUES(%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)'''
data = [datetime.now(), iv, i, ii, iii, llist[0], llist[1], llist[2], llist[3], llist[4], llist[5], llist[6], llist[7], llist[8], llist[9]]
c.execute(SQL, data)

I have fifteen items in my row and I count fifteen "%s"
is datetime.datetime() to the millisecond a problem?

[rich trout]

%s is supposed to handle that for you

[wary panther]

dang it then I'm still unsure. OK. Thanks!

[gaunt jay]

Traceback (most recent call last):
  File "somewhere", line 79, in wrapped
    ret = await coro(*args, **kwargs)
  File "C:/Users/karth/Documents/Discord Bot/Main Bot.py", line 182, in database
    datamod.add_member(i+1,member.id,member.name,str(member.avatar_url),0,1,random.randint(1000,10000),1,100)
  File "C:\Users\karth\Documents\Discord Bot\DiscordDB.py", line 26, in add_member
    self.cur.execute(SQL)
  File "C:\Users\karth\AppData\Local\Programs\Python\Python37-32\lib\site-packages\pymysql\cursors.py", line 170, in execute
    result = self._query(query)
  File "C:\Users\karth\AppData\Local\Programs\Python\Python37-32\lib\site-packages\pymysql\cursors.py", line 328, in _query
    conn.query(q)
  File "C:\Users\karth\AppData\Local\Programs\Python\Python37-32\lib\site-packages\pymysql\connections.py", line 517, in query
    self._affected_rows = self._read_query_result(unbuffered=unbuffered)
  File "C:\Users\karth\AppData\Local\Programs\Python\Python37-32\lib\site-packages\pymysql\connections.py", line 732, in _read_query_result
    result.read()
  File "C:\Users\karth\AppData\Local\Programs\Python\Python37-32\lib\site-packages\pymysql\connections.py", line 1075, in read
    first_packet = self.connection._read_packet()
  File "C:\Users\karth\AppData\Local\Programs\Python\Python37-32\lib\site-packages\pymysql\connections.py", line 684, in _read_packet
    packet.check_error()
  File "C:\Users\karth\AppData\Local\Programs\Python\Python37-32\lib\site-packages\pymysql\protocol.py", line 220, in check_error
    err.raise_mysql_exception(self._data)
  File "somewhere", line 109, in raise_mysql_exception
    raise errorclass(errno, errval)
pymysql.err.ProgrammingError: (1064, "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 '%d,%d,%s,%s,%d,%d,%d,%d,%d)' at line 1")

[toxic rune]

@rich trout Probably a bit late but regarding https://discordapp.com/channels/267624335836053506/342318764227821568/654430705102618635

How would you design the channel bans table? I thought of 4 columns: id, guild_id, channel_id, command_name Is that fine?

[gaunt jay]

datamod.add_member(i+1,member.id,member.name,str(member.avatar_url),0,1,random.randint(1000,10000),1,100)

        SQL = "INSERT INTO `Server 1` VALUES (%d,%d,%s,%s,%d,%d,%d,%d,%d)".format(member)
        self.cur.execute(SQL)```

please help

[rich trout]

Seems fine dimbreath

[gaunt jay]

i'm really not seeing where i'm going wrong

[toxic rune]

Don't use format. Use the args parameter of execute() instead.

I.e.

self.cur.execute('INSERT INTO blabla VALUES (%s)', (val1,))

Depends on what member is, according to the pymysql you can pass as args a tuple, list or a dict for named placeholders instead.

I'm curious how would I deal with if they want to disable all commands in a channel, wouldn't that end up being multiple INSERTs on the table?

[rich trout]

Yes, or you could make a special "all" value to check for too

[copper echo]

Hello ive got in to the problem, so i want to make something like subreddits with sqlalchemy and flask
i have 2 models User and Sub which contains subs and users column
but i dont know how to propely made it

class Sub(db.Model):
    id = db.Column(db.Integer(), primary_key=True)
    name = db.Column(db.String(80), unique=True)
    description = db.Column(db.String(2050))
    nsfw = db.Column(db.Boolean())
    users = db.relationship('User', backref='sub', lazy=True)


class User(db.Model):
    id = db.Column(db.Integer(), primary_key=True)
    username = db.Column(db.String(64), index=True, unique=True)
    email = db.Column(db.String(120), unique=True)
    password = db.Column(db.String(128))
    
    active = db.Column(db.Boolean())
    confirmed_at = db.Column(db.DateTime())
    account_created = db.Column(db.DateTime(), default=datetime.utcnow)
    subs = db.relationship('Sub', backref='user', lazy=True)

sqlalchemy.exc.NoForeignKeysError: Can't find any foreign key relationships between 'sub' and 'user'.
sqlalchemy.exc.NoForeignKeysError: Could not determine join condition between parent/child tables on relationship Sub.users - there are no foreign keys linking these tables. Ensure that referencing columns are associated with a ForeignKey or ForeignKeyConstraint, or specify a 'primaryjoin' expression.

[true sphinx]

Hey guys using postgres and could use some help. So I'm trying to count every distinct word in a column which I have here

SELECT word, count(*)
FROM ( 
  SELECT regexp_split_to_table(content, '\s') as word
  FROM datascience."Bix's Bastards"
) t
GROUP BY word

but now I'm trying to take it another step further and check another column and it only counts those words if the author column is x as well. Totally lost how to proceed

[rich trout]

@copper echo see here https://docs.sqlalchemy.org/en/13/orm/basic_relationships.html#many-to-many

[gaunt jay]

    def add_member(self,*member):
        self.cur.execute("INSERT INTO `Server 1` (`Index`, `User ID`, `Name`, `Avatar URL`, `Kicked`, `Banned`, `Credits`, `Level`, `XP Points`) VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s)",member)
        print("success")```

datamod = DiscordDB.Database()
    datamod.initialize()
    members = ctx.guild.members
    for i in range(len(members)):

        member = members[i]
        print(member.name)       
        datamod.add_member(i+1,member.id,member.name,str(member.avatar_url),0,1,random.randint(1000,10000),1,100)
    datamod.terminate()
    print("complete")```

so basically,

success
Nadeko
success
TypicalBot
success
MEE6
success
Yggdrasil
success
tortole 🎄
success
Aki
success
Pokécord
success
Gioraffe Joestar
success
posiedon72
success
Rals.ai
success
Akitrix
success```

this is the output

it outputs success, meaning the cursor's command is meant to have been succesful

however, firstly my database is actually not updated

secondly, it doesn't output the "complete" message

[grim iris]

has somebody used graphs database to recommend users based on keywords? The Keywords are extracted from a text and combined with the users

[austere garnet]

    await coro(*args, **kwargs)
  File "/Users/usr/Projects/discord_bots/bot/mbot.py", line 28, in on_ready
    await scp.mainf()
  File "/Users/usr/Projects/disord_bots/bot/scp.py", line 86, in mainf
    difference = [i for i in slks if i not in lks]
  File "/Users/usr/Projects/discord_bots/bot/scp.py", line 73, in sql_handling
    mycursor.execute("SELECT lks FROM slks")
  File "/Library/Frameworks/Python.framework/Versions/3.6/lib/python3.6/site-packages/mysql/connector/cursor.py", line 551, in execute
    self._handle_result(self._connection.cmd_query(stmt))
  File "/Library/Frameworks/Python.framework/Versions/3.6/lib/python3.6/site-packages/mysql/connector/connection.py", line 490, in cmd_query
    result = self._handle_result(self._send_cmd(ServerCmd.QUERY, query))
  File "/Library/Frameworks/Python.framework/Versions/3.6/lib/python3.6/site-packages/mysql/connector/connection.py", line 267, in _send_cmd
    return self._socket.recv()
  File "/Library/Frameworks/Python.framework/Versions/3.6/lib/python3.6/site-packages/mysql/connector/network.py", line 243, in recv_plain
    raise errors.InterfaceError(errno=2013)
mysql.connector.errors.InterfaceError: 2013: Lost connection to MySQL server during query

I have a script for my discord bot

and another for my database

and when I run my database script which calls upon the discord bot

I get this error

await scp.mainf() I think this is where the error is happening, but I'm not sure why

[proven wagon]

Any tips to improve query efficiency? have a table of 28k rows and its growing rapidly, takes 3.7 seconds to fetch everything using pgadmin atm

[toxic rune]

Indexes?

[proven wagon]

huh? 🙂

[grim iris]

what kind of data base is it?

[proven wagon]

PostgreSQL

the table is growing aproximately 1000 rows per day

[toxic rune]

3.7 seconds seems awfully lot for 28k rows only

[proven wagon]

🤷

[hot crest]

Is there an general recommendation on how large batches should I make with cursor execs before throwing the execs in with a commit? I am using psycopg2 to throw data to postgres database and I am trying to import about 80GB XML file in there parsed with lxml iterparse(approx 10.3m rows going to be inserted) but would need some guiding if theres some best practises for it when working with massive amounts of data to be inserted.

[dull shoal]

How bigs y'alls databases in GB

Want to know if 25GB is big or small I honestly have no idea

[jade dune]

guys quick question, how hard would it be to make a simple app which implements basic CRUD operations and connects to a MySQL database in python

im thinking of using tkinter to make it?

[wary panther]

I'm still having issues with my MySQL code, and I'm hoping someone has fresh eyes. The weird thing (to me) is that I used the exact same set up in python 3.7 in windows 10 and it worked just fine but now I'm using python3 in kali linux and it's not working. That shouldn't be an issue, right? If I didn't properly import a module, I would get an error saying the module can't be found, right? So how did this set up work the first couple of times and now I'm doing this in a different computer and I'm having an issue?

import mysql.connector

db = mysql.connector.connect(user='', password='', host='', port=3306, database='')
c = db.cursor()

SQL = ''' my sql query '''
data = mydata
c.execute(SQL, data)
db.commit()
db.close()

how did that work and now suddenly it doesn't work? ☹️ I have to be screwing something up here and I can't tell what the issue is.

I'll wait several seconds but my program just seems stuck at the c.execute(SQL, data) line. I even tried using a different module, hoping maybe I accidentally picked a module not compatible with python 3 and maybe if I specifically looked at the python/MySQL module options out there maybe that was the answer. Nah, instead of keyboard interrupt stopping me at

File "/usr/local/lib/python3.7/dist-packages/mysql/connector/cursor_cext.py", line 266, in execute
raw_as_string=self._raw_as_string)
File "/usr/local/lib/python3.7/dist-packages/mysql/connector/connection_cext.py", line 472, in cmd_query
raw_as_string=raw_as_string)

I get this other traceback instead

File "/usr/lib/python3/dist-packages/MySQLdb/cursors.py", line 247, in execute
res = self._query(query)
File "/usr/lib/python3/dist-packages/MySQLdb/cursors.py", line 411, in _query
rowcount = self._do_query(q)
File "/usr/lib/python3/dist-packages/MySQLdb/cursors.py", line 374, in _do_query
db.query(q)
File "/usr/lib/python3/dist-packages/MySQLdb/connections.py", line 292, in query
_mysql.connection.query(self, query)

but it's still not working in either case. The query isn't super complicated and the data is tiny so I think this should be a relatively fast part of the code, no?

[void otter]

@jade dune pretty easy since all the logic and modules are extremely easy to implement

[jade dune]

ok thanks

is there a better module to use for gui besides tkinter?

[wind zinc]

Anyone know a good library that connects Firebase and a Python code?

[void otter]

@jade dune pyqt and kivy are 👌

[minor ruin]

Doesn't Google publish FireBase module?

[wary panther]

Nothing obvious sticking out on my stuff?

[coarse patrol]

try creating a new empty table

there is no reason that shouldnt complete fast

helps rule out any issue with the command

Also, for your data, is it a single row you are adding?

or changing

[median ocean]

Is there a way to make a nested Django model field unique inside of its parent, an example, a duplicate song cannot exist in a playlist but the same song can exist in another playlist

nvm found it
unique_together

[swift berry]

Traceback (most recent call last):
  File "index.py", line 35, in <module>
    server = MotorClient('mongodb+srv://USER:<PASS>@cluster0-l6hmh.azure.mongodb.net/test?retryWrites=true&w=majority')
  File "C:\Users\Ken\AppData\Local\Programs\Python\Python36\lib\site-packages\motor\core.py", line 161, in __init__
    delegate = self.__delegate_class__(*args, **kwargs)
  File "C:\Users\Ken\AppData\Local\Programs\Python\Python36\lib\site-packages\pymongo\mongo_client.py", line 621, in __init__
    connect_timeout=timeout)
  File "C:\Users\Ken\AppData\Local\Programs\Python\Python36\lib\site-packages\pymongo\uri_parser.py", line 463, in parse_uri
    nodes = dns_resolver.get_hosts()
  File "C:\Users\Ken\AppData\Local\Programs\Python\Python36\lib\site-packages\pymongo\srv_resolver.py", line 102, in get_hosts
    _, nodes = self._get_srv_response_and_hosts(True)
  File "C:\Users\Ken\AppData\Local\Programs\Python\Python36\lib\site-packages\pymongo\srv_resolver.py", line 83, in _get_srv_response_and_hosts
    results = self._resolve_uri(encapsulate_errors)
  File "C:\Users\Ken\AppData\Local\Programs\Python\Python36\lib\site-packages\pymongo\srv_resolver.py", line 79, in _resolve_uri
    raise ConfigurationError(str(exc))
pymongo.errors.ConfigurationError: The DNS operation timed out after 20.000637531280518 seconds

i dont know why i cant connect to pymongo?

i tried using both motor and pymongo

[torn sphinx]

does anyone have experience with Rstudio and SQL?

I know this is a python discord

[void otter]

@swift berry you need to specify exact name and password

[swift berry]

@void otter i have already specified user and pass, i just took it out because i was sharing the link elsewhere

well i managed to fix it by using motor instead

[solid void]

anybody good with mongodb/mongoengine here? not that i like to use it, but i'm migrating from it to postgresql, and i have optimization problems due to that, i'm currently extracting a big list of ids from postgres in a query, and then fetching all documents in a collection whose ids are not in this list, with our current data, the list is 23k long, and the collection is 80k big, so that's almost 2 billion individual checks, so the request just dies of timeout. I think a bloom filter could help, as it would allow to very quickly exclude true negative, with only the positive to be double checked for false positive, but i don't see any indication that mongodb can do that.

(as we are migrating incrementally, and this collection will be the last one to go, as it's the most complex, i can't just migrate it)

[wary panther]

GUISE GUISE GUISE I FIXED IT. All by myself, to boot. What a wonderful feeling 🙂

[jade dune]

@wary panther nice bro what was the issue

[coarse patrol]

@wary panther yeah, I'm curious as well

[jade dune]

PLEASE RESPOND I CANT TAKE IT ANYMORE I HAVE TO KNOW

[coarse patrol]

[jade dune]

[snow plover]

Anyone here familiar with Pyodbc ? im wondering how secure code is, will any of the data in the script be unencrypted and easy to get a hold of?

[wary panther]

@jade dune @coarse patrol
so my SQL query was fine but my database connection definitely didn't survive any amount of my VPN changing, which happened. Also I left my other VPN on by accident so I messed the whole thing up. Once I tried to run my program directly and made sure to close and reconnect to the database on any VPN connection changes, I was golden.
Now I'm working through some weird random errors... and my workaround for not being able to talk to Russia seems like it won't work without some major alterations but if I can fix that and make sure this doesn't break for stupid reasons... I should be able to collect data over my vacation and not have to worry about anything until I go back to work. Fingers crossed!

Can't wait for vacation. I'm hitting diminishing returns on coffee and just need a chance to quit caffeine for a bit and get caught up on sleep and be hydrated for once. Coffee will do something again!!

[coarse patrol]

I've banged by head against the wall a few times because of VPN or firewall issues.

[chrome dagger]

hello guys

can i ask about mongodb question

i got a collection like so:
{ "_id" : ObjectId("5df5323bb1d987bc8f426a86"), "DateTime" : ISODate("2017-10-13T01:31:00Z"), "Power" : 26 } { "_id" : ObjectId("5df5323bb1d987bc8f426a87"), "DateTime" : ISODate("2017-10-13T01:32:00Z"), "Power" : 27 } { "_id" : ObjectId("5df5323bb1d987bc8f426a88"), "DateTime" : ISODate("2017-10-13T01:33:00Z"), "Power" : 137 } { "_id" : ObjectId("5df5323bb1d987bc8f426a89"), "DateTime" : ISODate("2017-10-13T01:19:00Z"), "Power" : 28 } { "_id" : ObjectId("5df5323bb1d987bc8f426a8a"), "DateTime" : ISODate("2017-10-13T01:35:00Z"), "Power" : 129 } { "_id" : ObjectId("5df5323bb1d987bc8f426a8b"), "DateTime" : ISODate("2017-10-13T01:36:00Z"), "Power" : 129 } { "_id" : ObjectId("5df5323bb1d987bc8f426a8c"), "DateTime" : ISODate("2017-10-13T01:37:00Z"), "Power" : 148 } { "_id" : ObjectId("5df5323bb1d987bc8f426a8d"), "DateTime" : ISODate("2017-10-13T01:38:00Z"), "Power" : 146 } { "_id" : ObjectId("5df5323bb1d987bc8f426a8e"), "DateTime" : ISODate("2017-10-13T01:39:00Z"), "Power" : 129 } { "_id" : ObjectId("5df5323bb1d987bc8f426a8f"), "DateTime" : ISODate("2017-10-13T01:40:00Z"), "Power" : 129 } { "_id" : ObjectId("5df5323bb1d987bc8f426a90"), "DateTime" : ISODate("2017-10-13T01:41:00Z"), "Power" : 130 } { "_id" : ObjectId("5df5323bb1d987bc8f426a96"), "DateTime" : ISODate("2017-10-13T01:47:00Z"), "Power" : 124 }
as u can see the date is seperated by a minute for each data
my goal is to make it seperated by an hour like so:

    { "_id" : ObjectId("5df5323bb1d987bc8f426a99"), "DateTime" : ISODate("2017-10-13T02:00:00Z"), "Power" : 26 }
    { "_id" : ObjectId("5df5323bb1d987bc8f426a99"), "DateTime" : ISODate("2017-10-13T03:00:00Z"), "Power" : 28 }```
How can I do this in mongo DB?
in this case I will have to calculate the  power value for each hour

[solid void]

so you want to average all the minutes of the hour into a new document that has the datetime of the hour and the averaged value?

i'm not strong with mongo, but i think aggregate functions should do that

[torn sphinx]

aneyone know why im getting this syntax error? py db = sqlite3.connect("main.db") cursor = db.cursor() cursor.execute(f"SELECT channel_id FROM join WHERE guild_id = '{guild.id}'") result = cursor.fetchone()

[serene slate]

hmmm don't use sqlie3 but in MySQL 'join' is a function or whatever you call it dunno if that would stop it working or not

[torn sphinx]

oh yea ill try renaming it

[patent glen]

if you have to have something called that, you can put table names in quotes...

it's also mostly harmless here because ids are constrained to be numeric, but doing f-strings like that is a bad habit to get into

so something like py cursor.execute('SELECT channel_id FROM "join" WHERE guild_id = ?', [str(guild.id)])

[median ocean]

Does AutoField have to be a primary_key?
I want to create a field that auto increments the version number every time the object is updated, not sure how should I go about that

Yup, I guess the default pk is already an autofield :/

Model foo can't have more than one auto-generated field.

[patent glen]

you could create a trigger, depending on the db engine

don't know how that interacts with whatever ORM you're using

[median ocean]

@patent glen I'm using Postgres on Django ORM
signals would work?

[rich trout]

You've seen https://code.djangoproject.com/ticket/8576, right?

[median ocean]

Just discovered that pk itself is an autofield lol

I thought Django just handled the incrementing

version = models.FloatField(validators=no_negative_value, default=1)```

I guess I'll just let the method that handles updating get the current value and increment it

[rich trout]

https://github.com/aaugustin/django-sequences may be of use

Not sure if that's the correct solution in your case--some databases provide specific autoincrement api's that this does not appear to use

[median ocean]

Thanks fam this is actually useful, I think I could wrap my value in a get_next method before sending it to the database, similar to what they're doing but without the need to add an extra package

[rich trout]

Since you're using postgres, it may be worth looking into https://code.djangoproject.com/ticket/27452 as well

And here, where they basically do what you're looking for, it's just not live in django yet https://github.com/django/django/pull/11568/commits/1dd2f964c2e3691e881cb1d03aaf40620ea426fa

[median ocean]

serial is actually the right keyword I was missing :D

I guess I can also add a custom migration until this is live

[gilded reef]

ANyone have experience with the neo4j package ?

[swift hamlet]

    warId = Column(Integer)
    attacker_ranking = Column(Integer)
    defender_ranking = Column(Integer)
    attackerRanking = relationship("CWLMemberRanking", backref=backref("attacks", passive_deletes=True))
    defenderRanking = relationship("CWLMemberRanking", backref=backref("defence", passive_deletes=True))

    __table_args__ = (ForeignKeyConstraint([warId, attacker_ranking],
                                           [CWLMemberRanking.warID, CWLMemberRanking.ranking],
                                           ondelete='CASCADE'),
                      ForeignKeyConstraint([warId, defender_ranking],
                                           [CWLMemberRanking.warID, CWLMemberRanking.ranking],
                                           ondelete='CASCADE'),
                      {})

My problem is I am trying to link 2 records from the same table, so need a single foreignkey constraint
You have to specify the field when creating the key ... and you can only have one foreignKey
Is it possible to use the same foreign key for two different relationships?

I'm using sqlalchemy

[rich trout]

I'm curious as to why you're using the table-construction syntax for a declarative class, when you could be using declarative foreign keys

Something a la

attacker_ranking = Column(ForiegnKey(Integer(), "CWLMemberRanking.id"))

relationship() also takes a foreign_keys argument, that you can use to specify which key to use

[swift hamlet]

There are 2 primary keys on the table I am linking to

@rich trout

For single primary keys I'm doing the following:

    war = Column(Integer, ForeignKey('cwlwarstats.id', ondelete='CASCADE'))
    warAttack = relationship("CWLWarStats", backref=backref("attacks", passive_deletes=True))

[rich trout]

I would think:~~

attacker_id = Column(Integer(), Foriegnkey("CWLMemberRanking.primary_key1"))
defender_id = Column(Integer(), Foriegnkey("CWLMemberRanking.primary_key2"))
ranking = relationship("CWLMemberRanking", foreign_keys=[attacker_id, defender_id], back_populates="members")
```~~

But I think it's not clear what you want here

One reference to a table with two primary keys, or two references to a table with one primary key?

You might look into naming your constraints

[swift hamlet]

2 references to a table with 2 primary keys

[rich trout]

You're going to need 4 properties then

[swift hamlet]

I have both attacks and defences stored in the same table

I want one link to attacks, and one to the defence

The warId is the same for both

do I need a second anyway?

I have 3 properties atm

[rich trout]

That should be good then

You need to specify which key applies to which relationship in the relationship

relationship(..., foreign_keys=[warId, attacker_ranking])

Otherwise which key applies to which relationship is ambiguous

[frozen fossil]

can somebody help me with mysql authentication

[swift hamlet]

Perfect

Thank you very much

🙂

@rich trout

That looks like it worked

[rich trout]

@frozen fossil Sure, what's gone wrong?

[frozen fossil]

in a round ill tell u in 10-30 min

[rich trout]

👍

[frozen fossil]

@rich trout ```py
config = {
'host': 'localhost',
'database': 'shoppy',
'user': 'shoppy',
'password': '1337',
'raise_on_warnings': True,
}

this is my config

problem is

i get this ProgrammingError: 1045 (28000): Access denied for user 'shoppy'@'localhost' (using password: YES)

and i have no clue how to fix that since i granted all privleges

[rich trout]

Who did you grant them to?

[frozen fossil]

shoppy

[rich trout]

@ localhost?

[frozen fossil]

indeed

[rich trout]

can you log in manually?

[frozen fossil]

yeah but it directly closes mysql

[rich trout]

"directly closes"?

[frozen fossil]

yeah it automatically types ^C

and closes

[rich trout]

That's... not right

How are you manually logging in?

Also, ^C doesn't close mysql's command line client AFAIR

[frozen fossil]

mysql -u shoppy -p 1337

[rich trout]

Try using a bare -p, and then entering the password manually

[frozen fossil]

yeah that works, weird

i think ill reinstall mysql so the users get reset maybe thatll help

[rich trout]

Maybe, it's quite strange what you're describing

The access denied is fairly normal-- if you've granted to @ 127.0.0.1, or something similar, that's an error you'd get

Sigh

[torn sphinx]

I guess it's free now?

[frozen fossil]

yeah i fixed it

by reinstalling idk what it was

[rich trout]

File corruption, sounds like it

Anyway, go on suncake?

[frozen fossil]

what does it return again if a select wasnt successfull?

[rich trout]

[]

Or, .fetchall() should return an empty list

[frozen fossil]

http://jelbrek.icu/i/M5ugzJTs.png is id reserved for python?

[rich trout]

Yes, but your issue there is you've missed a trailing comma on that tuple

[frozen fossil]

is there a way i can still show it as id?

[rich trout]

Nothing is stopping you from clobbering the id builtin other than respect for fellow developers sanity

[frozen fossil]

alright

[rich trout]

!e ```py
a = 1
print(a == (a))
print(a == (a,))

[delicate field]

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

001 | True
002 | False

[torn sphinx]

I want to track the voice chat activity in my discord. The data is basically a date, amount of minutes, channel ID and an array of user ids. Is it wise to save the arrays of ids into the database? I won't be able to foreign key the users inside the array, and I don't know if it's possible to pk/unique an array. I use pgsql

[pure cypress]

You could just structure it differently with multiple tables I think, it'll just be relatively more complex

You could create a message table and then associate a bunch of message records with a certain id and call that a "group" record

Alternatively, a table that has multiple users paired with one ID (one user per record) and then store that ID in the "message activity" table

I think you'd need a composite PK for that one

[torn sphinx]

That's voice activity. I just need the members present in a channel, each minute

[pure cypress]

Ok sorry, voice

[torn sphinx]

Same day/members/channel stack amount of minutes. Made the same thing work for reacts and messages, but it's different here

So I already use composite PKs, but I haven't really used array columns yet

[pure cypress]

This is what I had in mind. Much easier to draw it out than try to explain in words

[rich trout]

Every time you consider using an array with SQL, you should be using another table

In this example, you have many "voice activity"s with many users

So you should use three tables, probably

Users <-> UserActivity <-> Activity

Useractivity could store, for example, join and exit times

[torn sphinx]

I see now what you want to say

Alright, I'll see what I can do with it tomorrow, thanks for the tip

[charred marsh]

i'm using sqlalchemy with discord.py, would it be recommended to create a session for each message or is it preferred to use one global session? what are the implications of each option

[torn sphinx]

One session is what you need, creating new session for each message is a waste of resources and will probably cause errors

[charred marsh]

yeah thats what im doing at the moment

feels a bit strange that sqlalchemy gives you a session generator and then only expcets you to make 1 session?

[torn sphinx]

Hello, interesting people!
What postgresql host do you recommend?
I have been using elephantsql host and I heard that there are better ones, I need a free one, or any advice?

[harsh pelican]

what is the difference between those two datatypes. date date[]

date

[minor ruin]

What database?

[proven wagon]

@harsh pelican most cases date[] will be a list of dates while date is ONE date

[harsh pelican]

This is what i assumed. But i didnt find documentation about it. I guessed it was an array of dates

@minor ruin postgres

[snow island]

so i was experimenting and decided to implement a DB into my discord bot, i made something that i think should work but for some reason it doesnt and idk why. when it runs then nothing happens, i then tried the same thing in CLI and and i got an error- ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction. when i then killed the connection to my bot and done it again it worked fine

mydb = mysql.connector.connect(host = "localhost",
    user = "root",
    passwd = "something",
    database = "Cerberus",
    auth_plugin = "mysql_native_password")

async def on_guild_join(guild):
    cursor.execute("select * from suggestions where Server_ID = (" + str(guild.id) + ")")
    print(cursor)
    result = cursor.fetchall()
    if (len(result) == 0): 
        cursor.execute("insert into suggestions (Server_ID) values (" + str(guild.id) + ")")
        print(cursor)

[minor ruin]

Mother Russia, if this SQL library doesn’t support a sync, you could end up with weirdness

[versed copper]

Are there any orms for sqlite?

[pure cypress]

SQLAlchemy supports sqlite I believe

[versed copper]

Hm

I’ll look into it

[rich trout]

Most python orms support sqlite

[hidden jackal]

I have a big chunk of data for fxtrading on a csv file currently for and I'm thikning of putting em in a mongodb, would it be faster to backtest strategies from the data directly from the csv instead from the mongodb? it's faster on csv right? or nah, instead of querying the data from the db?

[torn sphinx]

hey so im trying to restore my really old code and this is gonna sound stupid

but i cant remember how to make a database

wait i think i got it

nope didnt get it

i tried py conn = await aiosqlite3.connect('level.db') cur = await conn.cursor() cur.execute('''CREATE TABLE levels (level real, xp real, userid real)''') conn.close() conn2 = await aiosqlite3.connect('bot.db') cur2 = await conn2.cursor() cur2.execute('''CREATE TABLE settings (lvlmsg text, guildid real)''') conn2.close()

to try and make the tables

wait hm

yeah so i tried that and its not making the tables

uhh

[carmine heart]

I think you need to await the execute calls as well. They are probably coroutines

The close call as well

[torn sphinx]

yeah i did that just now

and its still not working

[carmine heart]

Any errors?

What's your current code?

[torn sphinx]

yes

so i temporarily stole my ping command

@bot.command()
async def ping(ctx):
   conn = await aiosqlite3.connect('level.db')
   cur = await conn.cursor()
   await cur.execute('''CREATE TABLE levels (level real, xp real, userid real)''')
   await conn.close()
   conn2 = await aiosqlite3.connect('bot.db')
   cur2 = await conn2.cursor()
   await cur2.execute('''CREATE TABLE settings (lvlmsg text, guildid real)''')
   await conn2.close()
   '''
   checks bot latency
   '''

   # Get the latency of the bot
   latency = bot.latency * 1000
   embed = discord.Embed(title="Pong!", description=str(round(latency)) + " ms", color=color)
   await ctx.send(embed=embed)

i implanted it there and thats my code temp

[carmine heart]

When I look at the examples in the Usage section here: https://pypi.org/project/aiosqlite/

It uses the execute methods on the conn (they call it db), not the cursor

[torn sphinx]

oh?

okay

[carmine heart]

I'm not sure, though. I've never worked with aiosqlite myself. I'm just going by what I read there.

Oh

Maybe it'll work wit hcursor objects, but you do need to commit your changes

https://docs.python.org/3/library/sqlite3.html#sqlite3.Connection.commit

(You'll also see await db.commit() examples in that Usage section)

[torn sphinx]

ok so i commited and the table is there

but now

Traceback (most recent call last):
File "C:\Users\jack\AppData\Local\Programs\Python\Python38\lib\site-packages\discord\ext\commands\core.py", line 79, in wrapped
ret = await coro(*args, **kwargs)
File "C:\Users\jack\OneDrive\Documents\GitHub\vibecheckbot\cogs\leveling.py", line 117, in level
level, xp = result[1:]
TypeError: 'NoneType' object is not subscriptable

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

Traceback (most recent call last):
File "C:\Users\jack\AppData\Local\Programs\Python\Python38\lib\site-packages\discord\ext\commands\bot.py", line 863, in invoke
await ctx.command.invoke(ctx)
File "C:\Users\jack\AppData\Local\Programs\Python\Python38\lib\site-packages\discord\ext\commands\core.py", line 728, in invoke
await injected(*ctx.args, **ctx.kwargs)
File "C:\Users\jack\AppData\Local\Programs\Python\Python38\lib\site-packages\discord\ext\commands\core.py", line 88, in wrapped
raise CommandInvokeError(exc) from exc
discord.ext.commands.errors.CommandInvokeError: Command raised an exception: TypeError: 'NoneType' object is not subscriptable

when i try to get the level of someone it errors

this is the bit of my code thats erroring

@commands.command()
    async def level(self, ctx, member: discord.Member):
        conn = await aiosqlite3.connect('level.db')
        cur = await conn.cursor()
        await cur.execute('SELECT * FROM levels WHERE userid = ?', [member.id])
        result = await cur.fetchone()
        if result:
            level, xp = result[1:]```

bro this is so frustrating

[torn sphinx]

ok so

i got it

i shouldve had integer instead of level

real*

but now its double sending messages?

i think its not working right cause i added a thing to make it add a personsxp when they request level data

@commands.command()
    async def level(self, ctx, member: discord.Member):
        conn = await aiosqlite3.connect('level.db')
        cur = await conn.cursor()
        await cur.execute('SELECT * FROM levels WHERE userid = ?', [member.id])
        result = await cur.fetchone()
        if result:
            level, xp = result[1:]
            semb = discord.Embed(color=color)
            semb.set_author(name=f"{str(member)}'s Stats:", icon_url=member.avatar_url)
            semb.add_field(name="Level", value=str(xp))
            semb.add_field(name="XP", value=str(level))
            await ctx.send(embed=semb)
            await conn.close()
        else:
            if member.bot == True:
                await ctx.send("Can't get level stats | Error: User is a bot")
                await conn.close()
            else:
                await cur.execute('INSERT INTO levels VALUES (?,1,1)', [member.id]) 
                await conn.commit()
                await cur.execute('SELECT * FROM levels WHERE userid = ?', [member.id])
                result = await cur.fetchone()
                level, xp = result[1:]
                semb = discord.Embed(color=color)
                semb.set_author(name=f"{str(member)}'s Stats:", icon_url=member.avatar_url)
                semb.add_field(name="Level", value=str(level))
                semb.add_field(name="XP", value=str(xp))
                await ctx.send(embed=semb)
                await conn.close()```

i think the bit at the bottom messes it up

wait nope

its running twice

[torn sphinx]

ok so it was a process messages but now

i have it setup to level up every 25 x level

but it doesnt seem to be working

and im not sure why

[copper echo]

                INSERT INTO count (guild_id) VALUES ($1)
                ON CONFLICT (guild_id) 
                DO UPDATE SET messages = EXCLUDED.messages + 1 WHERE count.guild_id = $1

when i run this i get ```
there is no unique or exclusion constraint matching the ON CONFLICT specification

can you explain me this?

and tell me how to fix this

[torn sphinx]

i use aiomysql and discord.py why when i do insert or update i need to send message in discord to make data appear in db and if closed without typing any message it doesnt save

[gloomy wyvern]

guys, what should i do if i want to learn how to create a database and use it, after learning to program with python and to use html and css?

[upbeat lily]

Learn sql, there's a lot of good tutorials out there

Iirc w3schools has one

[gloomy wyvern]

is it a good choice?

sql

[upbeat lily]

Yes. The best choice

It's not a system in its own but common to many

Easy to get started with SQLite then you have options like postgresql for larger scale stuff

[gloomy wyvern]

nice, thank you very much

[gloomy wyvern]

guys, i am learning sql, but how can i test it on my computer? can i use vs-code or should i use other software?

[gloomy wyvern]

pls?

[fringe tiger]

easiest would be to use the built in sqlite, import it and you can start writting code right away as sqlite uses file as storage

and you can code in any editor/IDE you want

[gloomy wyvern]

do I have to download sqlite and import it as compiler on vs-code?

[fringe tiger]

no

you don't have to donwload it it's part of standard package

[gloomy wyvern]

ah ok, sorry standard package of what?

[fringe tiger]

https://docs.python.org/3.8/library/sqlite3.html

it comes with Python

[gloomy wyvern]

ah cool! thanks

[silent tapir]

Using aiomysql, after committing an UPDATE query and selecting the changed fields afterwards, they are not updated until I select them again. They are updated in the actual database but not in aiomysql. What could be the issue for this strange behaviour and how could I fix it?

Side note, this does not happen with Insert statements.

[cobalt cipher]

Your query is expecting 6 values, you are only passing in 5

!e ```py
a = (626824882734235670, 660577883449982977, 660587380041973806, 660579449263226897, 660578581511929866,)
print(len(a))

[delicate field]

@cobalt cipher :white_check_mark: Your eval job has completed with return code 0.

5

[cobalt cipher]

Even with that , at the end, it only has 5

[rough hearth]

I've been trying to perform a task with sqlalchemy to no avail for a while

Given two strings, I want to find a row in a table that has cells containing those two strings, and return an integer in that row

or store an integer.

[steady epoch]

Is there any free db hosting site which offer free hosting of discord bot also

[void otter]

Heroku

One free postgres db per repo open

[steady epoch]

@void otter does heroku can give private db

[void otter]

what do you mean private

yes you get your own postgres database

[rough hearth]

I think I've figured out the gist of my sqlalchemy situation. I've noticed that if I use the ORM, the session that an object came from has to be open for the life of that object, but this conflicts with how I'm handling scoping.

Should I make a class that implements context handling for using the objects from the ORM in other scopes, or is that sinful?

[celest zodiac]

You could also create a context manager

[pure scroll]

@rough hearth there is expire_on_commit setting of session that will allow you to work with these objects after you closed the session, however it won't let you do any queries using these objects.

[rough hearth]

@pure scroll, thanks!

[proper copper]

Anyone know if its possible to use django's get_or_create (https://docs.djangoproject.com/en/3.0/ref/models/querysets/#get-or-create) in a bulk manner? e.g. can i do it in one transaction with multiple entities or is this asking for concurrency issues?

[snow island]

await cur.execute(f"select Channel_ID from suggestions where Server_ID = {ctx.guild.id}")
    result = await cur.fetchall()

    if len(result) == 0:
        await cur.execute(f"select * from suggestions where Server_ID = {ctx.guild.id}")
        result = await cur.fetchall()

        if len(result) == 0:
            await cur.execute(f"insert into suggestions (Server_ID) values {ctx.guild.id}")

        await cur.execute(f"INSERT INTO suggestions (Channel_ID) values {channel.id} where Server_ID = '{ctx.guild.id}'")
        await ctx.send("Added both Server and channel")

    else:
        await cur.execute(f"UPDATE suggestions SET Channel_ID = {channel.id} where Server_ID = {ctx.guild.id}")
        await ctx.send("added channel")

I have the above code which seems to work fine but when i do:

await cur.execute(f"select Channel_ID from suggestions where Server_ID = {ctx.guild.id}")
channel = await cur.fetchall()
print(channel)

It prints ((645728131637837845,),) rather than just the int, anyone know why that may be?

[rich trout]

Fetchall() returns a list of rows

a row is a list of items

Well, s/list/tuple

So it's the list of results, containing a single row, with a single item that is your id

If there were multiple matches, with multiple returned columns it would make more sense

[snow island]

ah, got it, thanks a lot

[golden pier]

Boy do i wish i found this channel sooner…
I'm trying to refine a search for medical literature by MeSH terms and appearance of a text phrase in article's body. PubMed can do MeSH and Google Scholar can do body text, but neither can do both. Is there a search engine that combines these features? — No one else knew of one so I started scripting a workaround to just send pm results to gs…but…it seems incredulous that what I'm looking for doesn't already exist

Any of yall know?

[solemn plover]

anyone here uses firebase?

[torn sphinx]

how would i select a row from a table where it list all of the records without the <Record {row}= >

[river barn]

assuming you're talking about asyncpg

the record object represents a row

if you want a dict instead (though I see no reason why since you can just record["something"] just like you would in a dict and it also supports number based indexing), you can just dict() it

oh, if you're confused about the methods

fetch returns a list of records that match your query
fetchrow returns the first record that matches your query
fetchvalue returns a row of choice (based on index) of the first record that matches your query

[rugged sluice]

I have a really stupid question..i know i can compare two tables and check if there is a differenct, but i would like to know , counting way, how much hits i have when i am joining tables (or to check if there is a hit and analyse where). Do you have any idea how i can create such way...

[molten knoll]

It depends on the database but with some you can return the amount of rows with COUNT
example:

SELECT COUNT(*) FROM employees WHERE employees.age < 25

You can do the same after you joined the tables.

[rugged sluice]

i understand but how do you that with a join? and check how much joins i have? and what my rate? or i need really join every join manually?

[pure scroll]

what do you mean by that? it does not change much

select count(*) from table1 t1
join table2 t2 on t1.id = t2.id

[rugged sluice]

YEs, but you know its a id

but when you dont know where i can join

so i must search the colums to join

[molten knoll]

You should read up what happens when you join different tables and then check if your tables are join-compatible.

[rugged sluice]

oke, comparing 2 tables without any idea, is no option.. 🙂 like select count(*) from table1
join table2 t2 on t1 = t2

wit results like t2.ID vs t1. ID = 5000 , t2.chicken vs t1.chicken = 150, t2.amount vs t1.amount = 42

[pure scroll]

to compare table you need to know what you compare against

same applies to joins you just join on these params

if you want that to be one huge response instead of multiple database queries and in-memory joins use UNION and DISTINCT operations

[rugged sluice]

Hmm good idea

I am gonna try

[proper field]

I've been racking my brain for the past how long trying to develop a database in SQL and I have no idea what I'm doing. Does anyone have any visual programs or sites they'd recommend that allows me to dynamically edit a database?

[minor ruin]

What database?

Like Maria/SQLite or Postgres

[proper field]

SQLite.

[minor ruin]

MariaDB has HeidiSQL

[proper field]

I'm planning on using SQLite since it seems like the easiest for a small Discord bot.

Especially since I'm running it locally.

[minor ruin]

SQLite has DB Browser for SQLite

[proper field]

Oh word? I don't know if it dynamically updates though.

Let me go check.

[void otter]

@proper field
Sequeler

[broken cloud]

Can a table's FK and PK be same?

( I don't mean I'm referencing the FK to the PK ). I have X column, which refers to Y in another table.
I want X to be the FK, and PK for the current table.

[pure cypress]

If it will be unique then yes

Though I am suspicious of such a design choice

[proven wagon]

Hey, when force closing open connections to a postgresql servers on my desktop the server side connections dont seem to disappear, this only works when connecting from my laptop.
Connecting using asyncpg

[proven wagon]

I also just tried creating a table with a SERIAL id, but it doesent assign a id when posting

[torn sphinx]

hey guys

who can help with mysqli?

[dusky cape]

Concept question. If I wanted to encrypt a database. Do I

A) encrypt each input
Or
B) encrypt the entire .db file

The .db names are "encrypted" in that the user name connects to an encrypted file that contains the .db name type of thing.

However the .db file itself can be viewed with a SQL viewer program.

Or does this fall under security as a question.

[void otter]

encrypt each input

encrypting the file is gonna be a pain in the arse to interact with

[dusky cape]

I had a feeling that would be the case.

Wonder if I can create a program to transfer an old database to a new one with encryption and pick up with it from there.

I would imagine so if using the same method of encryption between the two programs.

[minor ruin]

Generally when you have to encrypt the database, you should be doing column level encryption

Some databases have native support for that

[dusky cape]

I will have to do some Sqlite3 research to figure out how best to encrypt. Unfortunately I didn't encrypt from the getgo as I wanted the ability to check my program during development.

[pallid osprey]

Alright guys quick question, here is my error:
TypeError: unsupported operand type(s) for +: 'NoneType' and 'int'
Here is the code:

     c = self.conn.cursor()
     c.execute("SELECT * FROM members WHERE memberID = ?", [ID])
     rows = c.fetchall()
  
     if len(rows) == 0:
      #create user and re-fetch
      self.create_user(ID)
      c.execute("SELECT * FROM members WHERE memberID = ?", [ID])
      rows = c.fetchall()

     user = rows[0]
     if user is None:
        return 
     c.execute("UPDATE members SET voiceTime = ? WHERE memberID = ?",[user[1] + 1, ID]) 
     self.conn.commit()```
Any ideas?

[pallid osprey]

Im scared now that no one has responded lol

[rich trout]

user[1] is none

I suspect that's the "voicetime" column, and if you don't set a default the default is probably null/none

[pallid osprey]

@rich trout thank you for the reply!

@rich trout Im struggling to fix this. Would you have a fix that I could use?

[rich trout]

check if user[1] is none, annd if it is dont do the addition just set it to 1, no?

[pallid osprey]

So remove the + 1

Now if I do that, it will not count time in voice chats incrementally

    def __init__(self):
        self.conn = sqlite3.connect('sqlitedb.db')

    def __del__(self):
        self.conn.close()

    def create_user(self, user_id):
        c = self.conn.cursor()
        c.execute("INSERT INTO members_data (discord_user_id) VALUES (?)", [user_id])
        self.conn.commit()

    def increment_voice_mins(self, user_id):
        c = self.conn.cursor()
        c.execute("SELECT * FROM members_data WHERE discord_user_id = ?", [user_id])
        rows = c.fetchall()

        if len(rows) == 0:
            #create user and re-fetch
            self.create_user(user_id)
            c.execute("SELECT * FROM members_data WHERE discord_user_id = ?", [user_id])
            rows = c.fetchall()

        user = rows[0]
        if user is None:
            return

        c.execute("UPDATE members_data SET voice_chat_mins = ? WHERE discord_user_id = ?",
                  [user[1] + 1, user_id])
        self.conn.commit()``` 
Any help with this snippet of code would be greatly appreciated. The issue is that everytime this is ran it spits a nonetype error that I cannot wrap my young dev brain around. Basically I want the bot to increment the time in voice chat everyminute and keep track of that in the memberID row. Basically this is to log the time of a user.

[hot verge]

i'm managing a database for a discord.py bot using tortoise-orm and i'm struggling to understand exactly what i should do in this situation: i have a table for users and am trying to create a table for marriages between users (so that i could find a single user's marriage and the data around that including the data about the other user). I've dug around a bit online but still struggling to understand exactly the type of relation(s(?)) i should set up and how to go about it.

[rich trout]

Personally I would set up two tables: One for users, and one for marriages. The marriages table would have two foreign keys to the users table, and whatever marriage data was involved. You could do a "composite key" between the two users or just a general ID, which depends on how you want the data normalized.

for example, marriage #50 would be between user#20, and user#10, and read something like this:
ID: 50, User1: 20, User2: 10, Date: 2020/01/01

[hot verge]

and say i had a user that i wanted to know whether was married and if so get the row, how would i do that? i don't know if they're user 1 or user 2.

[mossy lotus]

You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'NOT NULL

[rich trout]

You'd select with OR @hot verge

WHERE user1 = ID OR user2 = ID

[mossy lotus]

i just started learning SQL

[hot verge]

thought it would be something like that

thanks

[mossy lotus]

and im stuck there

[rich trout]

Well, step one is to try each not null individually to figure out which line is causing the issue

[mossy lotus]

i wrote it again

it seems the NOT NULL function is the issue

[rich trout]

Can you paste the code as text here?

[mossy lotus]

sure

CREATE TABLE members(
id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT ,
First_Name VARCHAR NOT NULL
)

thats my first ever try i wont be surprised if its a stupid error

[rich trout]

Your issue is actually fairly simple, and unrelated to your NOT NULL

VARCHAR requires a length to be specified, a la "VARCHAR(255)" for example

You can also specify PRIMARY KEY within the column definition, so this works just fine:

CREATE TABLE members(
    id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    First_Name VARCHAR(255) NOT NULL
)

I find http://sqlfiddle.com/ quite useful

[mossy lotus]

oh i thought its like python where it tells you actually wheres the error

thanks!

[rich trout]

It tries, but it's not nearly as accurate

[mossy lotus]

python is so easy compared to other languages man

soo easy

[mossy lotus]

i dont see any error

thats irritating

[rich trout]

SMALLINT(10)?

[mossy lotus]

is it not possible to do that?

[rich trout]

Doesn't look right

[mossy lotus]

ill try without it

didnt help

oh my god

its because of the comma in the end

[wanton void]

tldr: for db ingestion (mssql) should i build my own to control connection and transaction or using lib?

hi, I'm managing a staging database on SQL Server and as part of it, I have to ingest data (mostly dataframe) to the db. I find pandas's to_sql is super useful (simple and good perfomance too) but using that i have to give up my own control over connection and transaction,... which is not what I really want. What should I do, building my own dataframe to db function or bring the lib's code to my module (which is not recommended, I know) So basically, need some advice and recommendation. Thank you

[minor ruin]

Why do you care about connection and transaction?

[wanton void]

well I didnt af first but then thinking about it, having the ability to control that directly from our code is better right, in case any error and concurrency

[dusty helm]

hey guys, any good guide on pgsql? (complete guide) with examples and images and all the love and support a material could get? i'm stuck in "keys" in sql

[shell ocean]

anyone can give me some advice on which database I should choose for a certain application

[ionic pecan]

@dusty helm the postgresql documentation is really good, are you talking about primary / foreign keys?

@shell ocean you‘ll need to expand your use case a bit for us to help

generally postgresql is a wise choice

[shell ocean]

in particular, the data I want to store consists of multiple numeric fields (each record has the same number of fields). the bulk of operations will be filtering on any combination of these fields using numeric comparisons (equal to, more than, less than), as well as bitwise ones (and/or, specifically).
my original plan was MongoDB, but I was just wondering if there was a better fit for this also, apart from these numeric fields that will be filtered on, each record will contain string fields

@ionic pecan I suppose my main concern is how it'll perform when I want to filter on an arbitrary set of columns

[ionic pecan]

hmm, what you have sounds like a good fit for sql, since you have a fixed number of fields and querying based on multiple fields is definitely possible in SQL - performance depends on your dataset size, generally indexes help

i'm not too familar with mongodb to tell you how filtering on multiple columns is performed there, though

[mossy lotus]

I want to do something very simple and i cant find any good tutorials or videos that actually works for me

I made an html form, inside the form theres a lot of input like first name and last name, these inputs have their names with the name attribute, first name has name=“First_name”

And such

What i wanna do is get these input data and get them to be stored in a php file and then get the php file to connect to MySql database

[ionic pecan]

doesn't sound like something related to python

[dusty helm]

@ionic pecan yes, primary and foreign keys, my project is getting bigger than expected, i'm adding 10% or more everyday to the main idea, each new idea requires 3 to 5 new tables

and i'm complete newbie

all i have till now are some html sketches

but it's getting some shape

[ionic pecan]

what problem do you plan to solve with primary & foreign keys?

[dusty helm]

well, i'll give you my experience time and you can assume my problems... haha

i'm studying programming for like, 4 months

6 to 12 hours a day

1 day off

[ionic pecan]

that's great!

[dusty helm]

i know very little

about everything

[ionic pecan]

I mean like, do you want to learn about primary & foreign keys to solve a problem in your application, or simply learn about their purpose

[dusty helm]

i want to learn for my application and for future uses

i'll use it a lot

[mossy lotus]

I’m almost done

Just got an error that doesn’t seem to be major

[dusty helm]

since i learn how to use excel for doing basically everything, i always used databases, i created my tables and used lots of "procv's"

[ionic pecan]

this page contains information about general data constraints and uses that to explain primary & foreign keys along with their purpose: https://www.postgresql.org/docs/12/ddl-constraints.html

i would recommend reading through that

[mossy lotus]

Is there even anyone who can help here

[ionic pecan]

don't ask to ask

[dusty helm]

@ionic pecan thank you! i'll read

[mossy lotus]

Oh please show me the ask thingy

!ask

[delicate field]

ask

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

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

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

[mossy lotus]

I’ve been trying for 2 days and believe me I won’t ask anyone if I didn’t really need to

[ionic pecan]

missing quotes around the email

[mossy lotus]

explain pls

[ionic pecan]

you need to quote the [e-mail] field just like you quote the First_name and Last_name fields ('somefirstname' , 'somelastname')

same probably goes for United States

otherwise they won't be interpreted as text you want to insert into the table

[mossy lotus]

omg youre a fucking genius thank you!!!!!

you have no idea how much ive been stuck because of this stupid mistake

thanks man

[safe sail]

I have two tables:

How do I get the rows from guild_config where the columns config_key and command_name intersect?

So I want to return rows 3-6 from guild_config because they have a config_key which is a command_name

[safe sail]

select guild_id, config_key, config_value from guild_config, commands where guild_config.config_key = commands.command_name and guild_id = some_id; 

Is this ok?

[frozen fossil]

is there a way to create a mysql database using a query with a .sql file

[rugged glen]

read the file, execute it

[frozen fossil]

wdym by "executei t"

[patent glen]

@frozen fossil which mysql library are you using?

[frozen fossil]

i am using mysql connector

[patent glen]

see the answer to https://stackoverflow.com/questions/4408714/execute-sql-file-with-python-mysqldb that mentions mysql connector and multi=True (it has 0 points, but I think that's partly because the person asking the question was not using mysql connector)

though it looks like maybe the database has to already exist (empty?) to connect... maybe you can leave that out if the script contains create database and use statements though

[frozen fossil]

thanks i will look at that later after the dentist

[raw vigil]

UPDATE Books
SET
ISBN = (?),
Title = (?),
Author(s) = (?),
Pages = (?),
Number Available = (?),
Publisher = (?)
WHERE
ISBN = (?)

can anyone please tell me how to fix the syntax error in this query
its in Access SQL

edit: I fixed it, put the column names in square brackets

[torn sphinx]

Star expansion expression references column rental_id which is neither grouped nor aggregated at [1:8]

I dont get it

rental_id is unique.. so what is this telling me

the query I ran was:

SELECT * FROM bigquery-public-data.london_bicycles.cycle_hire GROUP BY start_station_name;

[torn sphinx]

I understand the create table part, but what's this within brackets? CREATE TABLE london2 (end_station_name VARCHAR(255), num INT);

^ this is in mysql..

[urban cradle]

sounds like a big query error

have you tried selecting specifics from it rather than wildcard?

or perhaps limiting it and seeing if it runs?

[urban cradle]

wait @torn sphinx , were you asking about the 2nd bit?

that's just defining the column and data type

[torn sphinx]

yeah just asked about the second part..