databases

[dull scarab]

Afaik

Asyncpg is compatible with an async environment

[brave grotto]

it's in the name yeah

I thought psycopg2 would be too

[dull scarab]

Well does it require await anywhere?

[brave grotto]

nope

[dull scarab]

Then it doesn't have coroutines

[brave grotto]

you speak the truth

[dull scarab]

And is synchronous

[brave grotto]

maybe it's the issue here then

My whole bot is async

[dull scarab]

Id try it

[brave grotto]

alright

i'll try this

updating the pipenv

How do I connect to a DB using a sslcert with asyncpg ?

How do I give the ssl keys and certs with this ?

[brave grotto]

@dull scarab

Having this error

pg_hba.conf rejects connection for host "94.126.119.20", user "koinbot", database "koinbot_data", SSL off

I have this now

[brave grotto]

halp me 😦

[ionic pecan]

post your pg_hba.conf

[brave grotto]

I don't have one ...

my database is a google cloud one

[rancid bronze]

cur.execute("SELECT sum(salary) from salaries where teamID is '{0}' and yearID is '{1}'".format(team, year))

so I'm running this query for a list of years -- how do I do that as a single query?

[pure cypress]

probably using group by

[rancid bronze]

how would that work?

[pure cypress]

where are your years coming from?

[rancid bronze]

I'm super bad with sql sorry

passed data

[pure cypress]

is it in another table?

[rancid bronze]

no--a provided list

[pure cypress]

well then you have to just loop the list in python and run the query on each I think

I thought you had it in a table

actually maybe you still can

[rancid bronze]

damn yea just looping is slow

[pure cypress]

something like where yearID in(all the years) group by yearID?

Fair warning I am rusty with SQL. I tend to figure stuff out by testing so it's hard to help when I can't test the sql

[rancid bronze]

what would all the years look like?

[pure cypress]

comma delimited list of years

where yearID in (2001, 2003, 2003)

[rancid bronze]

cur.execute("SELECT teamID, yearID, sum(salary) from salaries where yearID in (2012,2013,2014) group by yearID".format(team, year))

no dice...

[pure cypress]

give me a moment then

[rancid bronze]

thanks so much

[pure cypress]

Well here is a basic example that works https://www.db-fiddle.com/f/9BpLHbYF5iMKTbsSspkqdc/0

idk what your actual schema is like though

it doesnt quite make sense for me that there'd be multiple rows for the same teamid and year though

I'm guessing you have individual people and also store their team and year there?

in which case it sounds like it isnt normalised

[rancid bronze]

doesn't seem to work 😦

what's normalized?

even this returns a single null: SELECT sum(salary) from salaries where teamID is '{0}' and yearID in(2004, 2005)

[pure cypress]

what is your schema like then?

[rancid bronze]

honestly it was an import. the DB is super small--maybe I can just share it here?

yeah cool

import sqlite3
from sqlite3 import Error

def create_connection(db_file):
    try:
        conn = sqlite3.connect(db_file)
        return conn
    except Error as e:
        print(e)

    return None


def get_annual_salary(connection, team, year):
    cur = connection.cursor()
    cur.execute("SELECT sum(salary), teamID, yearID from salaries where teamID is '{0}' and yearID in(2004, 2005)".format(team, year))
    # cur.execute("SELECT sum(salary) from salaries where teamID is '{0}' and yearID is '{1}'".format(team, year))
    rows = cur.fetchall()
    print(rows)



def main():
    database = "path to db"

    # create a database connection
    conn = create_connection(database)
    get_annual_salary(conn, 'WAS', 2005)
    # get_teams_list(conn)
    # get_team_record(conn, 'COL', 2008)

if __name__ == '__main__':
    main()

here's what I'm working with so you can really emulate it lol

[pure cypress]

ah it's cause the year column is a string not an integer

this works sql SELECT teamID, yearID, sum(salary) from salaries where teamID in ('ATL', 'BAL') and yearID in ('2004', '2005') group by teamID, yearID;

[rancid bronze]

oh awesome

thanks

super helpful!

😃

[dull scarab]

@rancid bronze also try to avoid using string formatting for your queries. Its vulnerable to sql injections and could be a nightmare

Use prepared statements instead where you replace the values you need with placeholders, i think its ? In sql and pass your variables in order to execute

Sqlite*

[torn sphinx]

You can use any placeholder despite ? in the documentation

[sick nacelle]

Hey guys, kinda new here. I was wondering if someone could possibly mentor me or guide me on how to become a Data Engineer

The boot camp I've found in my city are a bit on the expensive side also I'm working full time so it's hard to invest that time into a boot camp.

[dull scarab]

There really isn't any program for mentoring or 1 on 1 tutoring offered here. You're welcome to ask questions and discuss the language though. You might be looking for #data-science-and-ml as well. Maybe a few there can guide you towards resources to learning what you need. @sick nacelle

[sick nacelle]

@dull scarab Ahh okay. Well I'm in a bit of a predicament. I was just wondering if someone could give me some advice on what I should do in terms of career goals as well.

[dull scarab]

I'm not in the field myself so I can't help you out there. But for general data-science questions you can ask in #data-science-and-ml , and if you need career / education advice try #career-advice

[sick nacelle]

Got it thanks!

[analog forge]

Hey I don’t know where else this would go but can anyone how you find the api to a website? Where are they usually?

[rancid root]

any suggestions for a key-value store that allows wildcard searches across tuple keys?

e.g.
query (1 * *), get

(1,1,1)
(1,2,3)
(1,0,213) etc

[smoky radish]

hey there,

using sqlite, is there any way to increase a value by one while inserting, when another row ould have the same value?

im trying to save querys i get from users, and how common they are.

so far i have a table like this CREATE TABLE querys(id INTEGER PRIMARY KEY, query TEXT, amount INTEGER)
and i would like to increase amount when the query value is the same.

how would i do this?

[west lark]

Hey, is it possible to make SQLAlchemy interpret None as 0 for One-to-Many relationship?

I'm trying to order a table of Products.
I want to be able to order by the amount of Offers a Product have. I can do it like:

session.query(Product).join(Product.offers).group_by(Product.id).order_by(desc(func.count()))

But this way the query only returns Products that have one or more offers. I want to be able to query by ascending order including Products that has None offers.

Any way to do this?

[plain oxide]

guys see i have this pdf

now i wanna convert this to excel sheets for ease of use

how to do in p

py

[dull scarab]

https://github.com/pdftables/python-pdftables-api Seems to be a good guess. I have not used it though

Accompanied by a blog post about it https://pdftables.com/blog/pdf-to-excel-with-python

[plain oxide]

see those files

how to read

[opaque saddle]

Hello guys,
I have run into an unicode problem with postgres and python3. Anyone has any experience with that?

UnicodeEncodeError: 'ascii' codec can't encode character '\xdc' in position 448: ordinal not in range(128), while every encoding setting seems to be alright

nevermind, after 2 hours I'm just realizing that my system has not all necessary locales installed

[rain cobalt]

@plain oxide strange man

[obsidian leaf]

how do i set a password for a database using sqlite3

ive seen in other languages its been conn.SetPassword("password") but doesnt seem to be it in python

[glossy ermine]

why are you trying to set a password on sqlite 🤔

@obsidian leaf

[obsidian leaf]

oof i have learnt

[torn sphinx]

@obsidian leaf sqlcipher

[glossy ermine]

what

[indigo mason]

@cerulean harbor

Don't use MySQL

It's the bad SQL

Use Postgres

[cerulean harbor]

No

lol

[tropic zealot]

Is it considered a criminal offense to use RethinkDB over Postgres?

[cerulean harbor]

I want to use RethinkDB

https://github.com/rethinkdb/rethinkdb-python

[ionic pecan]

you should probably read about the pros and cons of these databases

[indigo mason]

@cerulean harbor There's a reason Postgres is industry standard

Also you were trying to use MySQL

But you say Postgres is bad?

[cerulean harbor]

no

[pure scroll]

good luck adding column on mysql table with millions of records

[turbid crypt]

still learning the fundamentals i guess. could use a little guidance. http://pasteall.org/1363532/python

[brave grotto]

Guys. I wanna do an Update query

and update values in a db

using vars

is the syntax this way

Update table SET (column1, column2) = (var1, var2);

because Pycharm says there is an error

[dull scarab]

What db

UPDATE table
SET column1 = ?, columns2 = ?
WHERE some_condition``` and pass the variables in order to execute

is the general format, but the placeholder ? may vary based on db

So e.g

cursor.execute(
    """
    UPDATE users
    SET money = ?, transactions = ?
    WHERE id = ?
    """, new_money, len(transactions), user.id
)```

[brave grotto]

the problem is that there is no WHERE

because I want to update the whole table

[dull scarab]

The where just specifies what to update

if you exclude where it will update the entire table

[brave grotto]

alrighty

[dull scarab]

What are you trying to update?

[brave grotto]

A whole table

that is used as a cache

22 columns and 1500+ rows

[dull scarab]

So its writing cache to db

You may wanna use executemany then iirc

[brave grotto]

executemany ? what is this ?

[dull scarab]

it does many queries with a list of variables

[brave grotto]

i'm a good guy newb with DB stuff and python

[dull scarab]

so you'd setup a list of entries to update and pass the list with a query and it handles it for you

[brave grotto]

how

can you show me an example ?

[dull scarab]

Lemme find the thing, I don't remember the exact syntax

[brave grotto]

sure 😃

[dull scarab]

what db?

sqlite, postgres, mysql etc?

[brave grotto]

postgres

[dull scarab]

uh, which lib?

[brave grotto]

psycopg2

I tried Asyncpg

but couldn't establish a connection

because i'm using ssl certs

and couldn't find a doc with an ssl example working

[dull scarab]

So it expects a query and a list of tuples for parameters for the query

cursor.executemany("QUERY", [(1,2), (3,2)] )```

[brave grotto]

This is how I do currently

[dull scarab]

So using the example query from earlier..

parameters = [
    (55,  10, 245245), # money, transactions, id
    (100, 23, 242342)
]

cursor.executemany(
    """
    UPDATE users
    SET money = ?, transactions = ?
    WHERE id = ?
    """, parameters
)```

You don't have to loop through your entries and execute, just format them as to somthing like thispy params = [ (params1), (params2), ]

[brave grotto]

yeah

But the json containing everything is fucking huge

[dull scarab]

then execute many

you can keep that format, but you gotta pack it into a list of tuples

then pass that list with a query at the end when the loop is done running

[brave grotto]

I see

I have this error with the first solution you provided @dull scarab

And with the executemany

this means I have to do 1500+ params tuples

[dull scarab]

It's that or 1500 queries, execute many is probably more optimized for that many

i dont think postgres has ? placeholders?

it uses $1, $2, $3 ... iirc

[brave grotto]

maybe I need to use %s

like in the insert into query

[ashen zenith]

Yo, can anyone here help me out with some basic SQL?

Database theory I mean

Functional Dependencies like
A->BC

[brave grotto]

is this correct ?

[pure cypress]

I don't recognise the DBMS you're using but it looks like your missing the as ... part of the with

[brave grotto]

DBMS ?

I setup the connection and it's working

but the output of this part of code is

I just want the number of rows returned

[fringe plover]

Hello, what's the easiest way to connect a mySQL db to a HTML form?

[brave grotto]

php

[fringe plover]

Okay, I never used php, would you mind to tell me if what I'm coding is wrong or not?

[brave grotto]

I can't help you

I'm a total noob

[fringe plover]

oh

okay ^^

[timber stratus]

@fringe plover you would use a framework, like Flask or Django with Python. In the framework you make things called models. The HTML form will make an instance of that model and then it will be stored in db.

[fringe plover]

Oh okay. Is it easier to use than php ?

[broken linden]

PHP is a programming language specifically designed for web development while what import error said are frameworks for web development for the python programming language

So if you don't want to learn a new (horrible) language yes

[fringe plover]

Okay for now I just made smth that sends the data to my email and answer myself to all the emails. But I'll look into that, thanks

[foggy shore]

PHP is awful, as it a combination of lot of languages

u'll find things coming from C, perl and java

[broken linden]

Most languages are combinations of other languages

[torn sphinx]

Is anyone familar with mysql.connector?

I got many connections run with it. But know I have to work with SQLExpress by Microssoft. So Hostname looks something like SERVERNAME\SQLEXPRESS

I kn ow I have the right credential and the server is running because other clients have active connections. Butwith mysql.connector I always get the error:

mysql.connector.errors.DatabaseError: 2003 (HY000): Can't connect to MySQL server on 'SERVERNAME\SQLEXPRESS' (10061)

[glossy ermine]

using a mysql connector to connect to an mssql server is only going to cause you issues.

[topaz wharf]

Hi! I've a question about how to approach translations of terms on a database. I read there are different options. I chose the one that consists of adding translation_id in every table with terms to be translated (product_id, product_name, translation_id), one "translation" table (translation_id, language_id, term), and a table for language specs (language_id, language_code, language_name). However, how can approach fields headers translation? (I'm returning them on API requests). Thx!

[primal idol]

Hi ! In SQLITE, when inserting around 19000 rows, it takes quite some time to do it. are there any tips to make it faster ? Should I look for indexes ? Or it could come also from my latpop HD ? thanks.

[pure scroll]

HDD speed matters there

[primal idol]

ok thank you

[glossy ermine]

@primal idol Inserting that many rows should not take long, can you post the code of how you are inserting please

[primal idol]

In fact, I was wrong. I have a file which is a list of 75000 dictionnaries. And so far, I was extracting each dictionnary and doing an INSERT in sqlite3/python3.7

I am thinking about replacing by executemany but it seems it is accepting only tuple as parameters (need to check more)

[dull scarab]

building up a list of parameters and using executemany is probably a bit faster

it just takes the parameters you give normaly, but in a list```py

normal

execute("query", (param1, param2))

execute many

executemany("query", [ (param1, param2), (param1, params2) ] )```

[primal idol]

I will try. May I ask what is the difference in the background ?

opening just one connection instead of open/closing one

for each insert

?

[dull scarab]

Mostly that each insert has quite a bit of overhead which you have to go through for every execution when doing it one by one

while executemany setups this overhead for all of the queries

as far as i know, at least

[primal idol]

ok thank you. I just need to replace my list by tuple.

[glossy ermine]

@dull scarab @primal idol Its because in sqlite with python, you have transactions (transactions are a thing other than python just to be clear, just the way the standard sqlite3 lib handles them)

Each insert will have a new transaction, which will slow things down a lot

Also 75000 inserts shouldn't take long

[rocky wharf]

Hi everyone! working on some views for a postgres db. Essentially, got this intermediate table with the columns patient_id, disease, mortality, patient_id is integer pk, disease is a string name of the disease, and mortality is a string that can either be ALIVE or EXPIRED. I am grouping by disease names. I want to get mortality percentages for the different diseases. I am able to get a count for all admissions, however I am not sure how to get a count of the rows where the mortality = EXPIRED.

my query looks like something along the lines of

select
 disease,
count(patient_id) as n_adm,
<<count(patient_id where mortality = EXPIRED) >> 
from blah
group by disease;

I know the second count statement in the pointy brackets is incorrect; I'm just trying to illustrate what I'm trying to do

Does this make sense? Am I being dumb? I would appreciate any advice! Thanks in advance!

[dry patio]

sqlcmd = '''CREATE TABLE IF NOT EXUSTS {}(var REAL, var2 TEXT, var3 REAL, var4 REAL)'''.format(self.support_channel_count)
sqlisrtcmd = '''INSERT INTO {}({}, {}, {}, {})'''.format(self.random, authorid, name, age, score)

con = sqlite3.connect('botdata\\databases\\userinfo.db')
c = con.cursor()
c.execute(sqlcmd)
c.execute(sqlisrtcmd)

Ignoring exception in command tnew
Traceback (most recent call last):
File "C:\Users\ahdba\AppData\Local\Programs\Python\Python36\lib\site-packages\discord\ext\commands\core.py", line 50, in wrapped
ret = yield from coro(args, **kwargs)
File "c:\Users\ahdba\Desktop\Availium Solutions Bot\Commands\TicketSystem\tnew.py", line 198, in tnew
c.execute(sqlcmd)
sqlite3.OperationalError: near "EXUSTS": syntax error

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

Traceback (most recent call last):
File "C:\Users\ahdba\AppData\Local\Programs\Python\Python36\lib\site-packages\discord\ext\commands\bot.py", line 846, in process_commands
yield from command.invoke(ctx)
File "C:\Users\ahdba\AppData\Local\Programs\Python\Python36\lib\site-packages\discord\ext\commands\core.py", line 374, in invoke
yield from injected(ctx.args, **ctx.kwargs)
File "C:\Users\ahdba\AppData\Local\Programs\Python\Python36\lib\site-packages\discord\ext\commands\core.py", line 54, in wrapped
raise CommandInvokeError(e) from e
discord.ext.commands.errors.CommandInvokeError: Command raised an exception: OperationalError: near "EXUSTS": syntax error

Help please 😅

[nova hawk]

can you print the queries before executing

@dry patio CREATE TABLE IF NOT EXUSTS

EXUSTS -> EXISTS

[dry patio]

oh crap lol

Now im getting this error

Ignoring exception in command tnew
Traceback (most recent call last):
  File "C:\Users\ahdba\AppData\Local\Programs\Python\Python36\lib\site-packages\discord\ext\commands\core.py", line 50, in wrapped
    ret = yield from coro(*args, **kwargs)
  File "c:\Users\ahdba\Desktop\Availium Solutions Bot\Commands\TicketSystem\tnew.py", line 199, in tnew
    c.execute(sqlisrtcmd)
sqlite3.OperationalError: near "235973302898655232": syntax error

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

Traceback (most recent call last):
  File "C:\Users\ahdba\AppData\Local\Programs\Python\Python36\lib\site-packages\discord\ext\commands\bot.py", line 846, in process_commands
    yield from command.invoke(ctx)
  File "C:\Users\ahdba\AppData\Local\Programs\Python\Python36\lib\site-packages\discord\ext\commands\core.py", line 374, in invoke
    yield from injected(*ctx.args, **ctx.kwargs)
  File "C:\Users\ahdba\AppData\Local\Programs\Python\Python36\lib\site-packages\discord\ext\commands\core.py", line 54, in wrapped
    raise CommandInvokeError(e) from e
discord.ext.commands.errors.CommandInvokeError: Command raised an exception: OperationalError: near "235973302898655232": syntax error```

[wind pelican]

to start, you shouldnt be using format strings with sql.
do something like:

db.execute( 'SELECT * FROM user WHERE username = ?', (username,)  ).fetchone()

thats how you get sql injections

next is self.random a random number? if so you are doing INSERT INTO 235973302898655232

which i didnt think would be a syntax error but it shouldnt be a table name

[dry patio]

random is letters the number is for authorid

[wind pelican]

i think your insert syntax is wrong then:

'INSERT INTO user (username, password, locked) VALUES (?,?,?)'

also its super super important that you stop using format strings for your sql

you magically avoid a ton of security issues and bugs if you switch to statements like these instead

[primal idol]

@dull scarab Thanks for the advices yesterday. In term of performance, it is day and night execute versus executemany

[dull scarab]

👍 Nice to hear

[glossy ermine]

i explained why...

[final mason]

Is there a way to pass a string into something like this

    crsr.execute(sql_command, firstbc)

When I have firstbc = box1, crsr.execute fails saying that sqlite3.ProgrammingError: Incorrect number of bindings supplied. The current statement uses 1, and there are 4 supplied.

Which makes me think it's taking each character in firstbc as a statement

[ionic pecan]

you need to pass it as a tuple

crsr.execute(sql_command, (firstbc,))

[final mason]

When I pass it as a tuple it doesn't work

[ionic pecan]

what about it "doesn't work"? do you get an error? does it launch a nuclear missile?

[final mason]

Oh, well it's working now...

Yesterday it just wasn't reporting anything back, which made me think it wasn't getting inputted properly

[final mason]

How can I search if an entry exists in the table based on a variable?

Right now I have sql_command = "SELECT itemName1 from inventory WHERE itemName1 =?"

But it could be in itemName1, itemName2, itemName3, or itemName4

[wind pelican]

you can use OR in your WHERE statement

eg WHERE itemName1 = ? OR itemName2 = ? etc

I have not figured out how to pass a single value to multiple ? 's though without passing it in multiple times yet

[final mason]

Yeah that's kinda what I have now

Right now I just run this for itemNameX

    crsr.execute(sql_command,(firstbc,))
    if crsr.fetchone():
        sql_command = """SELECT itemLocation from inventory WHERE itemName1 = ?;"""
        crsr.execute(sql_command,(firstbc,))
        location = crsr.fetchone()[0]
        print(location)

[wind pelican]

http://www.sqlitetutorial.net/sqlite-case/ might do what you want.

SELECT itemLocation 
    CASE
        WHEN itemName1 = ? then itemName1
        WHEN itemName2 = ? then itemName2
    END
FROM inventory 
WHERE
    ? in (itemName1, itemName2)

something similar to that

[primal idol]

an standard SQL INSERT statement with dynamic values lik

INSERT OR REPLACE INTO table values (?, ?,?,?), data

How should I handle when I have around 75000 tuples in the field data and the length of my tuples is not always the same.
In the above case, it is 4. but could be 9 or 1.

[marsh ferry]

@primal idol You can insert lots of data in a single insert.

INSERT INTO table (col1, col2, col3) VALUES (...,...,...), (...,...,...) ....

But ensure that every tuple is the same.

Either make them the same

Or run an insert for each tuple and specify the columns

But I'm not sure how you want to do an OR REPLACE

UPSERTs are notoriously tricky

[primal idol]

That was my issue, length of tuples are different. And i cannot run a single insert for this amount of lines

Replace is not inportant for now. Just an insert with executemany.

[marsh ferry]

Perhaps fix the tuples with missing columns by replacing them with tuples with NULL in those columns

[primal idol]

That is what i was thinking but i was hoping for something different 😃

A trick i didn t know

[marsh ferry]

If the tuples are the same it is still not an executemany, just a single statement. A single insert can create multiple rows

P.S I'm new to Discord - Did you delete my messge?

It turned red.

Then disappeared

Weird

What is your input data stored as?

List of Dict or List of Lists or List of Tuples?

[primal idol]

List of dict with nested dict inside

[marsh ferry]

Regardless... Something like

for i in range(len(data)):
   row = data[i]
   new_row = fill_in_the_blanks(row)
   data[i] = new_row

[primal idol]

I ll try ! Thanks

[marsh ferry]

And then add an enhancement - for example let the fill_in_the_blanks() function return None if the row is correct.

So no update required if the row is already correct...

Using None as an indicator that the row does not need to be updated....

for i in range(len(data)):
   row = data[i]
   new_row = fill_in_the_blanks(row)
   if new_row is not None:
       data[i] = new_row

[primal idol]

I did it a bit differentlt but takes for help @marsh ferry

[patent glen]

@marsh ferry red means it failed to send, you get a limited time to pick retry from the ⋮ menu, then it goes away

[barren wave]

Could I get someone to test something I wrote?

[glossy ermine]

@barren wave no lol

[torn sphinx]

Is this chat for SQL Databases?

[jade pilot]

anyone here good with json?

[primal idol]

Yes @torn sphinx

[jade pilot]

heck

where can i talk about json

[rustic yarrow]

Json is probably web development

[pure scroll]

I would say it depends on the context, you can have JSON in SQL DBs

like postgres supports JSON and JSONB field types, and those two require additional knowledge of built-in functions in order to query for it.

or you can use nosql database which is a sort of a json most of the times.

[patent glen]

It's also not like this channel is exclusive to sql... stuff like tinydb and mongodb are probably on topic for in here

[torn sphinx]

Hey, I have a list stored as a string in my database, but how can I make it an actual list when using it in Python? Is there a way to transform the string into a list?

So, the list is basically like this now: "[513470169419481110, 513470171159986191]"

[pure scroll]

yeah you could use json module of python and do json.loads( "[513470169419481110, 513470171159986191]")

[torn sphinx]

Thx!

'[513470171159986191] WHERE guild_id = 510853998337589259' at line 1")```

query = f"UPDATE moderation_config SET staff_roles = {staff_roles} WHERE guild_id = {ctx.guild.id}"```

Someone any idea why I'm getting this error? staff_roles is an list by the way. Don't know if it has something to do with that.

[broken linden]

because SQL is not a friend of lists afaik?

at least not lists like you want them

and as SQL doesnt know what a [ means it throws a syntax error

@torn sphinx

[patent glen]

@torn sphinx don't use f-strings for sql queries. ```py
query = 'UPDATE moderation_config SET staff_roles = ? WHERE guild_id = ?'
cursor.execute(query, (staff_roles, ctx.guild.id))

or possibly

cursor.execute(query, (json.dumps(staff_roles), ctx.guild.id))

[torn sphinx]

👍 thx

[forest pike]

any cool kids who can help me with mongodb by any chance?

or should i be going to a #help channel?

basically, i've got a collection called 'users'

and under every one of the documents, there's an array called 'bets'

and this is an array of documents

so, like, embedded documents

and say there's a user, let's call them 'linky'

i want to find a specific bet from the user 'linky' with a specific _id

how does one do that?

okay solved it by doing stuff, if anyone actually reads this and cares just ping me but you probably figured this all out already k bye

[obsidian leaf]

c.execute("SELECT * FROM admins,teachers,students WHERE username LIKE :username",{"username":(str(self.username)+"%")})```
gives me an error 
```py
 sqlite3.OperationalError: ambiguous column name: username```
any ideas on what it means by this?

[cunning ginkgo]

**:**username

The :

[obsidian leaf]

whats wrong with it?

[cunning ginkgo]

Try without it

Either the colon shouldn't be there, or it can't find someone like username

[slate spire]

i believe dbapi requries you to have a : for named parameters

try a different param name maybe?

[dry patio]

How can i create a table based on a variable?

Table named using a variable*

[marsh ferry]

One way is By combining str.format() and %s .

So:

table_name = 'users'
values = [20]
sql = 'SELECT * FROM {table} WHERE age = %s;'
cur.exec(sql.format(table=table_name), values)

Note that in the above the cur.exec() AUGHT TO do the escaping on the values. This will not happen automatically with whatever is in table_name so that needs to be from a trusted source, otherwise you open yourself up to all kinds of sleepless nights.

I say AUGHT because you must check that your db package implements that correctly.

ping @dry patio

[ionic pecan]

.format is horrible, dont do that

the issue sqlite is having that the username field could refer to any of the three tables

you will need to do something like

admins.username LIKE :username OR teachers.username LIKE ...

[patent glen]

what is from admins,teachers,students

is it a join? a union? (EDIT: It's a join, but it looks like you want a union)

@obsidian leaf what exactly are you trying to do?

[obsidian leaf]

fixed it by doing this but thanks anyway 😃 py c.execute("""SELECT admins.username FROM admins,teachers,students WHERE admins.username = teachers.username AND admins.username = students.username LIKE :username""", {"username":(str(self.username)+"%")})
what im trying to do is generate a username ie. firstinitial + lastname and if thats already taken then add a 1 on the end of it - then a 2 if thats taken

[patent glen]

ok but i mean more broadly

why do you have three tables

[obsidian leaf]

making a school database

[patent glen]

ok but

why three tables

why not one table for all users, and then a column to say if that user is an admin, a teacher or a student

[obsidian leaf]

cus student stores some other things like year group - behaviour points, achievement points

[patent glen]

you should have a base user table and then a student additional data table

[obsidian leaf]

i see where your coming from

and i think your right

[patent glen]

also that query you fixed it with doesn't look right

[obsidian leaf]

yea i thought so xd

made it late last night and i made a note to check it next time i go back to it

so how would you say i should add year group, points etc. on?

[patent glen]

probably a students table with the same primary key as the users table

[obsidian leaf]

ok yea

[patent glen]

and then any student will have a row in both tables and you can just select * from users inner join students on users.id = students.id

whether id is a numeric id or a username etc

that way users.username can be a primary key or a unique constraint

to stop you from accidentally creating two users with the same name

[obsidian leaf]

thank you 😃 ill make the changes soon

[bold garden]

So i want to create a table of people and cars, i created a table that map the car to the owners but i also want to have a record of the purchase order (so i can sort it by obtain order)

i am using sql_alchemy btw

[wind pelican]

you would prob want three or four tables then. One for people, one for cars, one for ownership, since a car can have more than one owner such as primary driver and occassional driver or if someone cosigns a lease etc, then a purchase order table

ownership could be car_id, person_id, type_enum where type_enum in some way tells you how they own it like if they are the primary owner or cosigned etc.

in theory the purchase order table could also be the ownership table

looks like you have the first three but youll have to do something similar for this last table. I would expect a PO typically to be a po_id and a a 'list' of items on the po. so it may also need an intermediate table to relate it to cars and also one for people

if you want one owner per car and one po per car then you dont really need the intermediate tables

[cerulean widget]

Hi there, I am having a problem with using sort_values() with a height-related feature. It is a dtypes == 'object' and I want to keep it a \d-\d{1,2} format. I can't seem to have it sort in an ascending order. What steps can I take to solve this issue? Thanks in advanced.

[urban cradle]

anyone here a bit smart with .MDF and .LDF files?

specifically the garbage fire that is MSSQL

[agile dawn]

Hello guys - Would anyone be available to help at all?

[ionic pecan]

!t 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.

[agile dawn]

Thanks 😃

I have a discord bot I have created which is hosted on my dedicated server - the dedicated server does not have mysql on it (doesnt need it)

The bot is made in python and connects to a remote mysql database. I am randomly getting an error which is shown next

I have asked the hosting server providers the database is hosted on to check logs/firewalls etc.. and they are advising they cant see anything.

I am struggling to find the problem and where to fix it. Not sure if its my dedicated server thats dropping the connection or if its the remote mysql server kicking it out

[jagged shore]

Random bypasser here without a direct solution to your problem but wondering if it can be designed around -- if the connection is inconsistent can you just write a try/except and a loop until it gets the data you want?

[agile dawn]

what I normally have to do is kill the robot and start it back up again

but its such a tiny script - not sure if the script is maybe not keeping the connection alive

[jagged shore]

so how often does it happen?

[agile dawn]

randomly

but really often as in every hour at least

[jagged shore]

That's not bad. Just write code to handle the exception when it happens? I mean, if it was some huge enterprise system that's not really a good answer, but if this is just a small app I wouldn't worry about making sure the connection never ever fails?

[agile dawn]

the host of the mysql server has just advised the wait_timeout is set to 120

is that possibly the issue

[jagged shore]

Seconds? As in two minutes? Hm

So if you don't make any interactions

within 2 minutes

it drops

[agile dawn]

yep seconds

[jagged shore]

yeah, that makes sense

[agile dawn]

not sure lol

what ill do is

[jagged shore]

So you need to send some keepalive query?

[agile dawn]

ill run the bot

and give it 2 mins

ive sent a query through it - just waiting for 2 mins then ill try another command to see if it kicks it out

[jagged shore]

seems solid

[agile dawn]

yep lost connection after 120seconds

hmmmmm

[jagged shore]

So, like

idk there's a lot of ways to keep a connection alive but

maybe async/await a 110 second sleep?

[agile dawn]

would the best way be sending a ping every 60 seconds

[jagged shore]

then run a dummy query as a keepalive?

[agile dawn]

or would a firewall pick that up

could do that

[jagged shore]

hm idk if you can, like, forge a keepalive by pinging, think you need to actually interact with that somehow. There might be a keepalive setting in the actual connection object?

if you could just set that...

I'd google it -- python mysql keepalive

[patent glen]

just close the connection after you're done with it and reconnect

use a connection pooling library, it should take care of that without you churning too many real connections

[agile dawn]

I wouldnt have a clue how to add that if I'm honest - a friend online built most of it lol

but I can take this feedback to him 😃

[patent glen]

are you using aiomysql?

look at the docs at https://github.com/aio-libs/aiomysql

[agile dawn]

just mysql I think

[patent glen]

there are about half a dozen regular mysql libraries for python, which one are you using

oh, mysql.connector

look at https://dev.mysql.com/doc/connector-python/en/connector-python-connection-pooling.html

[agile dawn]

Thanks @patent glen

[near anchor]

list_as_string = str("'1', '2', '3'")
cursor.execute("SELECT * FROM [dbtable] WHERE [id_] in (?)", list_as_string)

Anyone got any idea why this wont give any results?

using pyodbc on msql

nvm, turned the whole query into an fstring and made it work

[marsh ferry]

You should let the DB connector format the arguments.

What DBMS uses [tablename] ???

[patent glen]

@gloomy zephyr EDIT sorry wrong @, I don't even know why your name comes up when I type @merc
the correct way to do that is something like py thelist = ['1', '2', '3'] question_marks = ','.join('?'*len(list)) # ?,?,? query = f"SELECT * FROM [dbtable] WHERE [id_] in ({question_marks})" cursor.execute(query, thelist) # or (..., *thelist) if you have other arguments

@near anchor

[marsh ferry]

I trongly disagree @patent glen. You should let the connector format the arguments.

See the section on parameters here:

https://github.com/mkleehammer/pyodbc/wiki/Getting-started

[gloomy zephyr]

?

[marsh ferry]

It ensures correct Escape Sequences, etc.

Encoding and what not

[patent glen]

@marsh ferry note that what I used an f-string for was not the arguments themselves, it was a list of question marks.

There's no other way to do that

most databases do not allow you to pass in a list or sequence object as a single parameter - i'm not aware of any that do

[marsh ferry]

Really? with Psycopg2 formats lists as PG SQL "array"s I assumed they all do that.

[patent glen]

sqlite3 doesn't even allow you to put a single parameter after in at all, it's a syntax error

[marsh ferry]

Yes but I think = ANY(%s) is more ANSI-SQL compliant

[patent glen]

and if you try to pass a list or tuple into a query that's not a syntax error, you get sqlite3.InterfaceError: Error binding parameter 0 - probably unsupported type.

[marsh ferry]

I've only worked much with PG so I may be wrong.

[patent glen]

sqlite doesn't support ANY, I don't know if it's ANSI or not, but it's still got the same problem - it expects a query or a list, and you can't bind an array-as-single-paramter for a list in most connectors

it's been my experience on oracle and ms sql as well, though that wasn't in python

[marsh ferry]

This kind of thing is coming up more and more in my work now so time for me to go educate myself a bit more.

[patent glen]

anyway, pyodbc doesn't, see https://github.com/mkleehammer/pyodbc/blob/d8e5537c04dae715b72c364fbffe2b2eb4810e2d/src/params.cpp#L37 for the supported types

It's cool that postgresql can, but that's a very uncommon feature in my experience

[dry patio]

http://prntscr.com/llbdzy

im trying to do something like that but i get this error

discord.ext.commands.errors.CommandInvokeError: Command raised an exception: OperationalError: no such column: user.id

nvm got it

[ionic pecan]

what did you end up with?

[solid pine]

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

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

reading the python docs on sqlite3, why is the first one insecure? it doesnt go into much detail as to why

[slender meadow]

I'm guessing that the second one protects against SQL injection.

[solid pine]

lol ah sql injections, another thing i'll have to read about

[dry patio]

So the way I do embed rn, is create a new one when ever i want to send an embed, I want to create a function that i call when i want to send embeds but I can't think of a way of doing that.

[vestal apex]

is this a #databases question?

[lunar cave]

Hi guys, I am new to the server, I need help with a problem with sqlite

[pure cypress]

Hello, welcome.

!t 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.

[lunar cave]

code

I don't get any error in my code but it fails to update the table.

I can insert a row without any problem

[pure cypress]

maybe where isn't matching anything?

[lunar cave]

I use a print before and I am getting the title right. I am going to double check

[harsh pulsar]

@lunar cave are you just overwriting the same data?

[lunar cave]

the row is null.

[harsh pulsar]

what database is this

its possible autocommit is disabled

[pure cypress]

It's sqlite.

It should be getting committed since the connection is being used as a context manager.

[lunar cave]

I am using the regular sqlite lib

import sqlite3

I've been able to create and work with the table, except the update

[harsh pulsar]

@pure cypress yeah im not too familiar w/ the dbapi ins and outs

ive been burned by that before

specifically in sqlite iirc

[lunar cave]

I found the problem. thank you.

since my I had overwritten the value of my title in the middle of my code.

the sqlite was not able to find an avaliable title because I stored a different title a few lines before. I need to be more careful assigning the variables.

thanks mark for mentioning the where in one of your solutions

[pure cypress]

You're welcome.

[vital yarrow]

Hello.
I am rather new and have taken on a small project to help me understand what I am learning and to keep my motivation up for learning.

I have gotten a medium sized json response and used json.loads to turn it into a python list.
Could someone help me with how to insert the values from this rather large list into a DB? I dont think I am googling right because all my results take me to stack overflow where most of the questions I either dont understand yet or they arent what I am looking for.

[mild sierra]

@vital yarrow you can try https://docs.ponyorm.com/firststeps.html one off easiest ORM's

[vital yarrow]

Thanks IbeeX, I will read up on that now.

[dull scarab]

@solid pine In short it means that a user could insert their own sql strings into yours if you concatenate the strings like py execute("SELECT * FROM users WHERE name=" + name_var +";")
or

execute("SELECT * FROM users WHERE name={};".format(name_var))```
So if `name_var` is an non sanitized variable that user could input something like 
`x or 1 = 1` (I believe would work, without testing it)

This would make the query 
```py
execute("SELECT * FROM users WHERE name=x or 1=1;")```
You can probably see why this is an issue.

[solid pine]

ahah that plus the funny comic makes sense now

thanks

[patent glen]

the -- shown in that comic makes the rest of the line a comment @solid pine

in case you were wondering

[torn sphinx]

Is anyone of you experienced with SQL-Express connections? I worked with mysql-connector and it realy works fine to connect to an mysql-server. But ms sqlexpress seemsto be a bit different to connect with.

[torn sphinx]

ok I have it. FYI

If you use SQLEXPRESS, you have to use pyodbc. MySQL-Connector doesnt work with it.

[tight crow]

can someone help me with this question

i really do not understand it

😦

[pure cypress]

You need to find the number of orders per customer. One order = one invoice. Invoices has a foreign key on CustomerId.

You can do a query on Customers with a join on Invoices using CustomerId

Since you need the total, you need to use the sum() aggregate function.

[slender meadow]

This is MySQL. Why does this work?

create table role (
    roleCode varchar(255) not null primary key,
    displayName varchar(255) not null
);
insert into role (roleCode, displayName) values  ('admin', 'Administrator'); -- okay
insert into role (displayName) values ('ShouldNotWork'); -- but it does work

[hasty hinge]

I am trying to do a python telegram bot connected to a sql database, what module should I use to connect with the sql database?

I am using this module https://github.com/python-telegram-bot/python-telegram-bot

[broken linden]

what type of sql db

there are several

@hasty hinge

[hasty hinge]

I am using a MySQL database @broken linden

[broken linden]

then use either MySQLdb or sqlalchemy if you want an ORM

[ionic pecan]

i think pymysql is more active https://github.com/PyMySQL/PyMySQL

[rose basin]

okay so I'm trying to figure out how to get an SQL query written using SQLAlchemy so that I don't have to rely on chunks of raw SQL

SELECT A.*, MAX(ledger.created_at)
      FROM accounts A 
      JOIN accounts_ledger ledger 
      ON A.id = ledger.account_id 
      WHERE ledger.successful
      GROUP BY A.id;```

[glossy ermine]

@ionic pecan if you want an ORM pymysql isn't an ORM

[ionic pecan]

and where did i imply that?

[glossy ermine]

i think pymysql is more active

You are comparing

So that is where the implication came from

[ionic pecan]

compared to mysqldb

[glossy ermine]

I see, miscommunication then

[wide jungle]

Hello, i'm using sqlite and i'm trying to update a column (which is currently null) with the count of the number of duplicates found in another column.
hope the formatting below works

Update GrimWares
set
ItemQuantity = (Count(ItemName) from GrimWares);

i used Py formatting even though it's SQLlite ;o

i get the error: near "from": syntax error:

[light granite]

I wonder... What is the simpliest way to work with MySQL? I mean is there some sort of the most basic connector or something.

[torn sphinx]

Hey folks, I have a table named groups with the following columns:

groupname, gpassword, gid

I would like to add a column called members

but it needs to contain multiple users

im using sqlite3

[torn sphinx]

https://stackoverflow.com/questions/29014030/sql-multiple-rows-records-into-one-row-with-3-columns

found an answer - just going to create additional columns member1, member2, member3

[rancid root]

recommend me a high perf timeseries store

[glossy ermine]

@wide jungle You'd need to use a full subquery

@rancid root InfluxDB

[wide jungle]

@glossy ermine do i need to add just the select amd the group by into the query? i have no where clause requirements.

[glossy ermine]

Count(ItemName) from GrimWares isn't a full query

Need the select

@wide jungle

[wide jungle]

@glossy ermine ya i agree was asking if its just the select or I'll need the group by in the query aswell right?

[foggy shore]

Hi guys ! I was asking myself
how would u handle Ctrl + Z & Ctrl + Y ?
storing all modifies in a database as a diff format ?
a size-limited database where u delete first index and add an index when it goes to limit ?
also is it possible to store an array / dictionnary in database ?
'cause i want to make the diff more efficient by putting lines as indexes

[rancid root]

https://redux.js.org/recipes/implementingundohistory not database specific but covers the most common state management patterns to implement undo/redo

[foggy shore]

cool

thx

[hearty iron]

$ sudo service mongodb status                                                              
● mongodb.service - An object/document-oriented database                                                            
   Loaded: loaded (/lib/systemd/system/mongodb.service; enabled; vendor preset: enabled)                            
   Active: failed (Result: exit-code) since Mon 2018-11-26 13:05:02 UTC; 3s ago                                     
     Docs: man:mongod(1)                                                                                            
  Process: 1556 ExecStart=/usr/bin/mongod --config /etc/mongodb.conf (code=exited, status=100)                      
 Main PID: 1556 (code=exited, status=100)                                                                           
                                                                                                                    
 systemd[1]: Started An object/document-oriented database.                          
 systemd[1]: mongodb.service: Main process exited, code=exited, status=100/n/a      
 systemd[1]: mongodb.service: Unit entered failed state.                            
 systemd[1]: mongodb.service: Failed with result 'exit-code'.```

I was trying to run mongodb but it isn't starting

It used to run and function properly btw

@me please 🙏

[oblique knoll]

can someone help me in #help-croissant

[faint pilot]

what could I use to get me goin?

/easier

[faint pilot]

hmm

[split owl]

@faint pilot ive heard that SQLite is pretty good for beginners

[faint pilot]

mk

ty

[split owl]

np

[faint pilot]

is it on pypi?

@split owl

[harsh pulsar]

sqlite is a standalone database engine

its great. its portable, high quality software, performant enough to use it at work, no need to mess around w/ setting up servers and authentication, nice extensions like jsonb

oh and python ships w/ a sqlite3 package

[faint pilot]

hmm just lookin for something that ik how to put together with digital ocean

[harsh pulsar]

sqlite is probably easiest

its only weak point is concurrency. if you are expecting more than a few users writing data at the same time you'll have to be careful

whereas mysql and postgres are much more robust in that regard

[torn sphinx]

the error codes help with debugging

https://stackoverflow.com/questions/45691032/mongod-service-start-exits-with-code-100

@hearty iron

[clever lava]

Having trouble trying to figure out the best way to expose a list of posts to the client for pagination purposes

Is it a bad idea to send all posts to them as opposed to sending x at a time?

[quiet ermine]

how much lighter is SQLite vs Postgres?

[patent glen]

sqlite doesn't require a separate server process, it's managed from within the program connecting to the database

[quiet ermine]

👍

i shall use it for my small project then

(got postgres installed already but sqllite would probably be easier to distribute to my server)

[wide jungle]

Hey following on from a question yesterday:
SQL Lite 3 question

Update GrimWares
set ItemQuantity = 
    (Select iq.ItemQuantity
    from GrimWares as it
    JOIN (Select ItemName, Count(ItemName) as 'ItemQuantity' from GrimWares Group By ItemName) 
    as iq ON it.ItemName = iq.ItemName)

if i do this sub-query on it's own, each row has a different number for the ItemQuantity, if i run this query as part of the update and set it returns '2' for each row for the ItemQuantity column?

[lofty quest]

I have a discord bot and I've got my rudimentary system working atm so that I can recall a specific case (kick, ban, etc) by a number using a massive dict. I would like to move this into a database of sorts. Wondering if I should use JSON or SQL?
If SQL would be better, does anyone have a good guide to follow because I don't fully understand how to use it?

[dull scarab]

Depends on the size and activity of your bot

If it's for personal use on a server or 2 a json would work.

You could use sqlite as well for a small amount of servers / activity, and postgres for larger bots as it's probably among the best alternatives for higher traffic

I haven't take this myself, but code academy usually have good courses https://www.codecademy.com/learn/learn-sql

[hasty hinge]

Hi, I am using PyMySQL for my telegram bot, it works fine, but after some hours I am getting a pymysql.err.InterfaceError: (0, '') error in every sql query, I don't know what to do to fix that.

[harsh pulsar]

hell no dont use json for random access imo

theres no reason not to use sqlite

sql isnt hard to learn and sqlite is pretty much the simplest sql experience possible nowadays

[lofty quest]

I am using TinyDB to create a JSON Database holding basically an action log for my discord bot. It looks like, python {'_default': {}, GUILD_ID: {INDEX : {USER_ID: {CASE NUMBER : REPORT (string)}}}} I was wondering if I could remove the INDEX parameter so my dict would be formatted GUILD_ID > USER_ID > CASE NUMBER > REPORT but I am very new to JSONs and databases so I do not know how to accomplish this or if it is even possible because I have searched for ~10 mins and nada

And I am not using SQLite because the application for this bot will be very slim - currently just mastering d.py

[lofty quest]

from what I can tell its built in '1', '2', etc

[civic marten]

Question, I have joined 3 tables, showing stores, the products they sell and their prices, how do I list all the products of stores that have any products between 10 and 50 dollars? Not the products that are between 10 and 50, but if a store has any between 10 and 50, list them all

[hearty iron]

@lofty quest you can have it like this

{
  "_default": {},
  "Guilds": {
    GUILD_ID: {
      "Users":{
        USER_ID:{
          "Cases":{
            CASE_NUMBER: REPORT
          }
        }
      }
    }
  }
}```

and then can access REPORT as `data['Guilds'][GUILD_ID]['Users'][USER_ID]['Cases'][CASE_NUMBER]`

It will make sense to you after you save a lot of data and have to get stuff from each thing and something similar.
Like getting all the reports from this will be easier!
You can get it easily by using nested for loops

[faint pilot]

                    check1 = c.execute("SELECT * FROM verifiedpeople WHERE id=:id", {'id': int(after.id)})
                    check2 = c.execute("SELECT * FROM verifiedpeople WHERE id=:id AND verified=:verified", {'id': int(after.id), 'verified': 'No'})
                    if int(after.id) in check2.fetchall():
                        if "No" in check2.fetchall():
                            c.execute("""UPDATE verifiedpeople SET verified = :verified
                                        WHERE id = :id""",
                                        {'id': int(after.id), 'verified': "Yes"})
                            print(c.fetchone())
                            print("Updated")
                            conn.commit()
                            conn.close()
                            print(c.fetchone())
                            return
                        else:
                            print("No updating")
                            return
                    if len(check1.fetchall()) == 0:
                        c.execute("INSERT INTO verifiedpeople VALUES (:name, :id, :verified)", {'name': str(after.display_name), 'id': int(after.id), 'verified': 'Yes'})
                        print(c.fetchone())
                        print("Added")
                        conn.commit()
                        conn.close()
                        return
                    else:
                        print("Already there")
                        return

it always prints "Added" when the thing is already there and it doesnt print "No updating"

using sqlite3
there something I'm doing wrong?

[ionic pecan]

the first condition will never be true

.fetchall returns a list of tuples

if anything, you need to check if int(after.id) in check2.fetchone(), and then i'd also recommend just fetching the ID

[pulsar timber]

how could I increase/decrease a value in SQLite?

decided to ask here since it is the databases channel

[gilded narwhal]

i have a database of files and folders:

            ['drop table if exists files'],
            ['drop table if exists folders'],

            ['create table files ('
                'id integer primary key,'
                'path text,'
                'parent integer,'
                'constraint unique_path unique (path),'
                'foreign key (parent) references folders(id)'
             ')'],

            ['create table folders ('
                'id integer primary key,'
                'path text,'
                'parent integer,'
                'constraint unique_path unique (path),'
                'foreign key (parent) references folders(id)'
             ')']
        ]

is there some way to set up some kind of rule that will automatically set the parent of a file/folder?

(parent being the folder that a file/folder is inside)

@pulsar timber https://stackoverflow.com/a/744290

[pulsar timber]

thank you! @gilded narwhal

[gilded narwhal]

👍

[pulsar timber]

what should I define UPDATE as?

[wind pelican]

@gilded narwhal i am not quite sure what you are asking, but if you dont want people to be able to put files in without setting the parent you can enforce NOT NULL, like parent INTEGER NOT NULL

[gilded narwhal]

@wind pelican rather than calculating and setting the parent of each file/folder manually i'd like the db to do it automatically for me when i insert them

[wind pelican]

so you want to pass it a full path for example?

eg "/home/person/folder/file.txt"

[gilded narwhal]

@pulsar timber what do you mean by defining UPDATE?

yeah

[wind pelican]

hmmm

[gilded narwhal]

so basically i'd just add a path to the db and there'd be some rule thatd do like: 1. check if it startswith any of the paths in the folders table 2. set the index to the one that found, if it's found

[wind pelican]

this https://stackoverflow.com/questions/3727543/sql-query-to-get-full-hierarchy-path goes in to some useful sql commands you could use

[gilded narwhal]

i'm not trying to get the full path

[pulsar timber]

it shows "unresolved reference "UPDATE"

[gilded narwhal]

i want the db to set the parent id for me

[wind pelican]

right but you will have to use a similar construct

just in reverse

its not a trivial query, i dont know how off the top of my head but hopefully that will give you some hints

[gilded narwhal]

ah, i see. thanks, i'll take a look

[wind pelican]

https://academy.vertabelo.com/blog/do-it-in-sql-recursive-tree-traversal/ this looks like its going the direction yoiu want.

but you might want to split the path yourself in to an array or something easier for the db to understand

[gilded narwhal]

i dont really need to split it since all the paths are in full anyway

including the folder paths

it just needs to be a startswitch check on the path

[wind pelican]

oh

i thought you had a recursive structure

like a row for each step

but it sounds like you store the whole path from the root in each node

[gilded narwhal]

nah, just wanna make it easier to step through folders as needed later on without doing a lot of text searching

[wind pelican]

so you just need to split the input somehow, like on the last '/'

then search the folder paths for the first half

and use the second half as the name

[gilded narwhal]

@pulsar timber that's quite strange. would you mind posting your whole query?

[wind pelican]

though personally id just make this two queries, get the right folder, insert the file row, commit

[pulsar timber]

sure

import sqlite3
from inventory import Inventory

conn = sqlite3.connect('inventory.db')

c = conn.cursor()

def insert_inv(inv):
    with conn:
        c.execute("INSERT INTO inventory VALUES (:credits, :hitpoints, :attack, :maxhealth)", {'credits': inv.credits, 'hitpoints': inv.hitpoints, 'attack': inv.attack, 'maxhealth': inv.maxhealth})


def update_credits(inv, credits):
    pass


def remove_inv(inv):
    pass


inv_1 = Inventory(50, 20, 25, 20)
inv_2 = Inventory(50, 20, 25, 20)


# c.excecute("INSERT INTO inventories VALUES (':credits', ':hitpoints', ':attack', ':maxhealth')", (inv_1.credits, inv_1.hitpoints, inv_1.attack, inv_1.maxhealth))

# conn.commit()

# c.excecute("INSERT INTO inventories VALUES (':credits', ':hitpoints', ':attack', ':maxhealth')", (inv_1.credits, inv_1.hitpoints, inv_1.attack, inv_1.maxhealth))

# conn.commit()

insert_inv(inv_1)
insert_inv(inv_2)

UPDATE Inventory SET credits = credits + 50


c.execute("SELECT * FROM inventory WHERE credits=?", (50,))
print(c.fetchall())

conn.commit()

[gilded narwhal]

im working with roughly ~100k files here so i thought maybe there was some way to let the db handle it automatically without extra calculation on my end

but yeah i should just time the effect of an extra query and see if it's significant

Pep, there's no UPDATE in this code

[dull scarab]

There is

But not in a string

Just.. there at the end

[gilded narwhal]

oh wow. completely glossed over that

[dull scarab]

You have to actually execute it as a query

[gilded narwhal]

tfw syntax highlighting is actually unhelpful 😛

[pulsar timber]

ok

anyways thanks!

[gilded narwhal]

no worries

wait no

it was @dull scarab who solved it. why am i taking credit 😅

[pulsar timber]

never mind I misspelled execute lol

[pulsar timber]

import sqlite3
from inventory import Inventory
import discord

conn = sqlite3.connect('inventory.db')

c = conn.cursor()

def insert_inv(inv):
    with conn:
        c.execute("INSERT INTO inventory VALUES (:credits, :hitpoints, :attack, :maxhealth)", {'credits': inv.credits, 'hitpoints': inv.hitpoints, 'attack': inv.attack, 'maxhealth': inv.maxhealth})


def update_credits(inv, credits):
    pass


def remove_inv(inv):
    pass


inv_1 = Inventory(50, 20, 25, 20)
inv_2 = Inventory(50, 20, 25, 20)


# c.excecute("INSERT INTO inventories VALUES (':credits', ':hitpoints', ':attack', ':maxhealth')", (inv_1.credits, inv_1.hitpoints, inv_1.attack, inv_1.maxhealth))

# conn.commit()

# c.excecute("INSERT INTO inventories VALUES (':credits', ':hitpoints', ':attack', ':maxhealth')", (inv_1.credits, inv_1.hitpoints, inv_1.attack, inv_1.maxhealth))

# conn.commit()

insert_inv(inv_1)
insert_inv(inv_2)

c.execute("UPDATE Inventory SET credits = credits + 1")
conn.commit()

c.execute("SELECT * FROM inventory WHERE credits=?", (54,))
print(c.fetchall())

conn.commit()

right now if I enter in c.execute("SELECT * FROM inventory WHERE credits=?", (54,)), it shows the values correctly

but when I enter `c.execute("SELECT * FROM inventory WHERE credits=?", (56,)),' it also shows the values correctly

there aren't supposed to be more than 2 inventories

[wind pelican]

would you show us your inventory code?

make sure you are setting credits correctly

[gleaming frost]

Does anyone know what is causing this? (Rethinkdb)

async def tuple_convert():
    """Converts all coords to tuples"""

    all_docs = await r.table(USER_TABLE).run(bot_conn)
    while (await all_docs.fetch_next()):
        doc = await all_docs.next()
        print(tuple(doc["cords"]))
        doc["coords"] = tuple(doc["cords"])
        await r.table(USER_TABLE).replace(doc).run(bot_conn)

sharpbot_1_242ea091cb4c | (523, 185)
sharpbot_1_242ea091cb4c | (660, 530)
sharpbot_1_242ea091cb4c | (676, 435)

And yet.......

https://mcadesigns.co.uk/i/uw97.png

[sullen fox]

Hi,
I want to use MongoDB in my application : you can set a search with parameters and then scrape tweets. I need to store the search parameters and the tweets. Then, I'll only iterate in these tweets. So, the read performance is the most important factor.
I'm new to MongoDB and I drew this scheme :
https://altab.fr/snaps/chrome_2018-12-01_23-20-12.png
The problem of this scheme is that a document can't exceed 16MB (hardcoded) (so ~1k8 tweets). I can store only usefull fields and work around this limit but I prefer not.
I would like to have your valuable advice before starting to implement MongoDB

[ionic pecan]

well the relational way to do this would be referencing the "index" table on the individual "tweets" entries with a foreign key

not sure if mongodb, being a modern, webscale database, supports such thing.

[sullen fox]

@ionic pecan I think we can

if I understood well, you would merge the 2 collections?

I was thinking about having 1 document = 1 tweet in the tweets collection to bypass the document size limit

but I don't know if it's good for the performances

[twin spoke]

Heh, was that a reference to the "mongoDB is webscale" youtube video?

[median snow]

Hello, can someone help me understand why this attempt at an UPSERT only updates the first value (price) and does not update the other two (value and cost)?

import time
conn = sqlite3.connect("simple.db")
c = conn.cursor()
day = time.strftime("%j")
c.execute("CREATE TABLE IF NOT EXISTS prices(day INTEGER PRIMARY KEY, price int, value int, cost int)")
price, value, cost = input("write price value and cost separated by a space\n>").split()
print(price,value,cost)
c.execute("INSERT INTO prices(day,price) VALUES(?,?) ON CONFLICT(day) DO UPDATE SET price=excluded.price WHERE excluded.price < prices.price",(day,price))
c.execute("INSERT INTO prices(day,value) VALUES(?,?) ON CONFLICT(day) DO UPDATE SET value=excluded.value WHERE excluded.value < prices.value OR prices.value = 'None'",(day,value))
c.execute("INSERT INTO prices(day,cost) VALUES(?,?) ON CONFLICT(day) DO UPDATE SET cost=excluded.cost WHERE excluded.cost < prices.cost or prices.cost = 'None'",(day,cost))
for line in c.execute("SELECT * FROM prices"):
    print(line)
conn.commit()
conn.close()```

The desired outcome would be a single row in the database for every day of input containing the lowest of each 3 of the values found (during the day)

[median snow]

apparently the problem with the other 2 values is that their value gets initiated to None and the WHERE clause does not validate

if anyone runs in the same problem it finally worked when I replaced the prices.value/cost = 'None' part of the statement with prices.value/cost IS NULL

[torn sphinx]

```py
code here
```

[mighty crystal]

ok I have a problem. I recently wanted to start developing my bot again so I copied it to my PC from my Raspberry Pi, copied the database itself from a MySQL database and installed MySQL on my PC

but now I have a problem...

MySQL 8.0 (my PC version) won't accept this type of queries

https://gyazo.com/6e06202fe1f50527b83d56f37ce83bc2

any idea why or how could I fix this?

SELECT rank FROM chat_leveling_system_backup WORKS
SELECT level FROM chat_leveling_system_backup DOESN'T

https://gyazo.com/e469eca61b951d34dbfdc54fac832ed4

this is the table structure

[glossy ermine]

Notice the double '' at the end

[mighty crystal]

That's the ' the error added in. Notice it has one at the start of the error quote as well. Also the error is before that

I added a single one before and after the needed string

[mighty crystal]

SOLVED

I uninstalled MySQL 8.0 and installed MariaDB and now it works

🕺

thanks to @plain radish ❤

[tranquil trellis]

Hi, I have some knowledge of relational database design but I'm having a hard time wrapping my head around NoSQL database design. Anyone know some useful tutorials for NoSQL design?

[torn sphinx]

@tranquil trellis NoSQL can be imagined as a collection of JSON documents, these do not have to follow a certain Schema

[glossy ermine]

Thats massively over generic

NoSQL can be more than json documents

Take redis for example

[versed coyote]

Or Cassandra

It's a general term for non-SQL databases, through often used to refer to ORMs like MongoDB

[glossy ermine]

ORM?

[tranquil trellis]

I understand that but what I dont understand for example is when I should put stuff in different tables. Because from my limited experience, I have understood that NoSQL databases don't like combining stuff from multiple tables like relational databases.

[lofty quest]

Quick question regarding SQLite... The blob datatype doesn't really have that much documentation on it in the docs, but can I store a dict as a BLOB value?

[pure scroll]

blob is binary, you can store there whatever you want, but for dictionaries it's probably better to use text so you would be able to easily read it as a human being

[lofty quest]

okay

If I have a file called run.py and inside a function that looks like: ```python
from bot import database

def get_guild():
guild_id = input( "Enter Your Guild ID: " )
database.bootup(guild_id)

get_guild()Then in my dir `folder>bot folder> database.py` I have another function:python
def bootup(guild_id: int):
path = Path('my path')
connection = sqlite3.connect(path)
db = connection.cursor()

db.execute('''CREATE TABLE f'{guild_id}'(member TEXT PRIMARY KEY, cases BLOB)''')
db.commit()

its currently throwing: File "C:\Users\clapp\Desktop\My Bot\bot\database.py", line 10, in bootup db.execute('''CREATE TABLE f'{guild}'(member TEXT PRIMARY KEY, cases BLOB)''') sqlite3.OperationalError: near "'{guild}'": syntax error
All of my integer values are going to be converted into strings, hence the TEXT

[pure scroll]

Why do you want to have separate table per guild?

you could just have a guild name as a row of your table, or as a separate table and foreign id constraint to it

the second problem is that you should never use any user input unescaped in a query

becaue that makes it possible to use SQL injections

[lofty quest]

1. The db will house info as such guild --> user --> cases I figure the easiest way of accomplishing this is to create a table with a name equivalent to the Guilds ID.

2. the user input is a string of numbers provided to them, so all they have to do is copy+paste.

The general question I was asking however, was if it is possible to make a table with a name that depends on an argument.

[pure scroll]

the code that you posted should work

ah wait

[lofty quest]

its throwing that error

[pure scroll]

f'{guild} -> f'{guild_id}

[lofty quest]

no end quotes?

[pure scroll]

because your function does not know what guild is

no

it's just guild -> guild_id

since you don't have a variable guild in the scope of your function

[lofty quest]

thats a typo on my end

its throwing a syntax error

[pure scroll]

then the next question would be if you are running on python 3.6

3.6+

because f-strings is a python 3.6 feature

[lofty quest]

3.7.1

[pure scroll]

weird, try to use the good-old format instead
db.execute('CREATE TABLE {table_name} (member TEXT PRIMARY KEY, cases BLOB)'.format(table_name=guild_id)

but still I think it's worth to have data not in the separate tables, but in one table. In the end what is the gain of having it split into different tables? and how are you going to make migrations later on?

[lofty quest]

File "C:\Users\clapp\Desktop\My Bot\bot\database.py", line 10, in bootup
db.execute('CREATE TABLE {table_name} (member TEXT PRIMARY KEY, cases BLOB)'.format(table_name=guild))
sqlite3.OperationalError: near "3215467": syntax error

I will try to merge into one table and see how it goes, for now though, this syntax error is extremely annoying

[pure scroll]

you table names cannot start with digit

what you can have instead is something like guild_1231231 as a table name

[lofty quest]

makes sense

fixed

[pure scroll]

but you definitely should think of redoing it, since using any user input unescaped in a query is really NOGO

[lofty quest]

i am in the process of redoing it. still really new to databases so trying to learn as much as possible and as fast as possible

[slate shadow]

i hope i can get some help here:

         c.execute("SELECT rowid , * FROM sugs WHERE sugid = {}".format(reaction.message.id))
        row = c.fetchone()```
it prints as if there were nothing in the row but there is!
`"240483083508252672"    "-=The Promidius=-"    "03/12/18"    "test 6"    "519232169260875787"    "suggested"` thats the content of the row
i guess i'm missing something obvious anyone can help me?

[dull scarab]

Dont use format for queries, use prepared statements

Also why are you selecting a specific column, then everything.

[slate shadow]

so it also shows the row id

[dull scarab]

Is the id you use and store the same type?

Int/string

And are you fetching the id of the author, or the message

Theres a lot left out, what is reaction, what db model is it, what does the table look like

[slate shadow]

yeah theyr the same type

        c.execute("""CREATE TABLE sugs (
                    userID integer, 
                    userNam text,
                    sugPost text,
                    sugdesc text,
                    sugid integer,
                    sugStatus text
                    )""")```

thats the table

reaction.message.id is 519232169260875787

[dull scarab]

Use BIGINT if it's ids

as integer reaches 2,147,483,647
and that id is far bigger.

[slate shadow]

i chaged it, still same problem

[dull scarab]

And what database are you using?

[slate shadow]

sqlite3

[dull scarab]

and if you just do SELECT rowid , * FROM sugs

you get a response?

[slate shadow]

yeah

that kind of works

[dull scarab]

Kind of?

[slate shadow]

yeah it does now select the info from the 1st row

problem must be then WHERE sugid = {}".format(int(reaction.message.id))

[dull scarab]

Again, don't use format

[slate shadow]

should i use ? ?

[dull scarab]

c.execute("SELECT rowid , * FROM sugs WHERE sugid = ?", (reaction.message.id,))```

yes

Don't forget to pass the id as a tuple

But as to why it's not working.. are you sure the entry is there?

and that the id is correct

Like if you got the id manually, and hardcoded it. Does that work

[slate shadow]

the id from the message is the same as the one gets stored.its also the same as reaction.message.id

and wont find that row

[dull scarab]

Is it querying the same db you're viewing?

[slate shadow]

yes

[dull scarab]

Did you recreate the table after changing the integer to bigint

[slate shadow]

yes

[dull scarab]

If you tried what I suggested, started with a clean slate, new table and all that and it still doesn't work then I have no idea

I'm afraid

[slate shadow]

ok

thank you so much

[trim chasm]

hey there

i'm using the psycopg2 module and a postgresql database

and try to insert something with: python cursor.execute("INSERT INTO list (year, rating, imdbid, title, country, cast, genres, directors, writers, decade, bechdelid) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s);" , (year, rating, imdbid, title, country, cast, genres, directors, writers, decade, bechdelid))

But I get bash Traceback (most recent call last): File "<stdin>", line 1, in <module> psycopg2.ProgrammingError: syntax error at or near "cast" LINE 1: ... INTO list (year, rating, imdbid, title, country, cast, genr...

all my variables are strings

cast is something like "tom, jerry, berna, isolde"

I thought with this format, execute would sanitize my strings.

like this: http://initd.org/psycopg/docs/usage.html#query-parameters

[glossy ermine]

@trim chasm Cast is a keyword

To fix you can either change the name, or wrap it in quotes

[granite rock]

Hey

I need some help

[torn sphinx]

I'm surprised that when calling .execute() with parameters the driver can't identify that if it isn't a tuple that it's.. one parameter. It seems a bit ugly to have (parentId,)

uh i need help

so this is my code

def data():
   for item in b:
      c.execute("""INSERT INTO DATA (ITEMS) VALUES(?) """, item)
   connection.commit()
data()

i get

sqlite3.ProgrammingError: Incorrect number of bindings supplied. The current statement uses 1, and there are 3 supplied.

b = ["Data1", "Data2", "Data3"]

so how do i fix this

c.execute("INSERT INTO DATA (ITEMS) VALUES(%s)", (item,)) or something

i tried that

or wait

let me try again

I think it's because it tries to use item as a tuple

@torn sphinx c.execute("INSERT INTO DATA (ITEMS) VALUES(%s)",(item,))
sqlite3.OperationalError: near "%": syntax error

where am i suppose to put the "%" at the end

uh

wait now i get

    c.execute("INSERT INTO DATA (ITEMS) VALUES(%s)"%(item,))
sqlite3.OperationalError: no such column: Data1

It's unsafe to use string formatting in an execution, you should use prepared statements. This seems like an echo chamber because somebody else was doing the exact same thing if you scroll up in the channel a bit. 😄

I should legally change my last name to "'); DROP ALL DATABASES; COMMIT;" to find every online account system using string formatting on SQL queries.

[vernal ocean]

Anyone here have any experience with inserting and selecting ' into a Sql database?

I've got it inserting fine, but can't seem to select it, either ways I've tried it syntax errors or it's looking for the escape character as part of the string

[pure cypress]

Which RDBMS are you using and how are you performing queries?

[vernal ocean]

MariaDB and SqlAlchemy

[pure cypress]

What have you tried to escape the quote? \' and ''?

[vernal ocean]

Yeah so when I insert it I did \\' but not ' '

\' didn't work

[pure cypress]

is the quote parameterised or part of the "raw SQL"?

Oh, well I suppose it's wrong to assume you're doing raw sql queries as it is an ORM after all

[vernal ocean]

Sorry I'm not sure what you mean by that, still fairly new to using Sql with Python

[pure cypress]

Can you show the code for the query?

[vernal ocean]

SELECT server FROM `tags` WHERE server={0} AND tag={1}

And then I'm passing the values into {0} and {1}

[pure cypress]

and those values have the quote?

[vernal ocean]

Wait, yeah sorry

It's a user entered value, so for example aa'aa

[slate spire]

uh, are you using .format...?

[vernal ocean]

Yes

Want me to post that?

[slate spire]

...sure...

[vernal ocean]

check_sql = check_sql.format(ctx.message.server.id, self.escape(txt))

Escape() is what adds the required '' for the query

[slate spire]

what are you using for the db, you should not be using .format for this, especially for a discord bot

[vernal ocean]

Really? Why is that

[pure cypress]

If you use a query with parameter bindings or whatever they're called, sqlalchemy will likely take care of escaping the quote for you

[vernal ocean]

Well when I was working on the part that was inserting it wasn't so I had to escape it myself, just to be clear escape() is used to add the single quotes around {1} like '{1}'

[slate spire]

because you're opening yourself to sql injection

especially since this looks like a tag system

[vernal ocean]

🤔

So %s would be better?

[pure cypress]

especially because that value comes from user imput

No, use this https://docs.sqlalchemy.org/en/latest/core/sqlelement.html#sqlalchemy.sql.expression.text

[slate spire]

i'm curious why you're using sql queries since you said you were using sqlalchemy earlier

[pure cypress]

For ORMs, raw SQL queries are typically used only for more complex queries.

[vernal ocean]

Oh okay, what's the alternative?

[pure cypress]

If you use SQL for everything, it kinda defeats the purpose of an ORM.

Well, do you even need an ORM?

If you do, then here is how you use it in SQLAlchemy https://docs.sqlalchemy.org/en/latest/orm/tutorial.html

If not, use something like aiomysql instead and keep performing queries using SQL

[vernal ocean]

I'm just editing an existing bot, so I'm not sure what the original authors intentions were

I'll take a look at that expression link you posted once I get home though, thanks

[pure cypress]

If the existing queries were already being done with .format() then the authors of the bot were following bad practices

[vernal ocean]

I kind of figured, it's a mix between %s and .format

[pure cypress]

If they are already using SQLAlchemy then fair enough to just keep using it since it may be a lot of work to migrate over to another library depending on the bot's scale.

Furthermore, it'd also be easier for you, as a beginner, to just convert the queries to use sqlalchemy.sql.expression.text since the SQL will be the same for the most part.

Where as the latter method would require relatively more learning of SQLAlchemy on your part

[vernal ocean]

Yeah that's what I was thinking

Thanks 👌

[pure cypress]

Regarding your original quote issue, like I said, I think SQLAlchemy will take care of escaping it for you if you use a parameterised query. Don't have a way to test that though.

[vernal ocean]

I will play around with it some more tonight

[torn sphinx]

I know you aren't using psycopg, @vernal ocean , but this section explains the problem with query parameters in SQL: http://initd.org/psycopg/docs/usage.html#the-problem-with-the-query-parameters

"Never, never, NEVER use Python string concatenation (+) or string parameters interpolation (%) to pass variables to a SQL query string. Not even at gunpoint." I like the "Not even at gunpoint." part

[vernal ocean]

Haha I'll give it a read

[lofty quest]

I have a table in my SQLite database initializaed with ```python
conn = sqlite3.connect(path)
db = conn.cursor()

db.execute( '''CREATE TABLE IF NOT EXISTS "Content Creators" (guildID INTEGER PRIMARY KEY, memberID INTEGER) ''' )
db.commit()
``` And I need to retrieve a list of all the member IDs for a certain guild. How do I do that? A lot of the docs and examples use data[key][key] but I don't know where they are getting the data from

[pure cypress]

Make a select query in SQL

[lofty quest]

that would look like ```python
db.execute('''SELECT memberID from "Content Creators" ''')

I am very new to SQL

[pure cypress]

Use the where clause

select x from y where z = some_value

[lofty quest]

okay, and this will output a list of memberIDs?

[pure cypress]

Yes

[lofty quest]

thank you

@pure cypress just one = or == with the WHERE clause

[pure cypress]

one

[vital yarrow]

Hello, I am pulling data from the online game Eve using their swagger interface. I can get my code to pull the data and print it no worries, but trying to insert it into a database is proving to be an issue.
This is my code https://pastebin.com/RePWh
I have limiited the pull to just 2 pages at the moment until I know it is going to insert properly.
This is the error I am getting

  File "test_script.py", line 48, in <module>
    sql="insert into forge_orders (duration,is_buy_order,issued,location_id,min_volume,order_id,price,range,system_id,type_id,volume_remain,volume_total) VALUES (%d,%d,%d,%s,%d,%d,%s,%f,%s,%d,%d,%d,%d)" %json_response
TypeError: not enough arguments for format string``` 

Unfortunately I do not understand enough yet to know if what I have googled is giving me the right info. 
I have tried `json_response = json.loads(list(raw_response.raw))` ` json_response = json.loads(raw_response.raw)` on line 41, as well as adjusting the insert statement as much as I know how, but cant seem to get anything to work.

[torn sphinx]

Hmm, I sense a C background. In most SQL implementations I've used on Python %s is used for all value placeholders. Also, % is string interpolation which doesn't escape any fields since it's a String method unrelated to SQL at all. I suggest instead of "% json_response" to do ", (json_response["duration"], json_reponse["buyorder"], ... ) )

The INSERT expects the values to be in a tuple, not a dictionary. The values should be 1 dimensional, and dictionaries are not.

[slate spire]

i'd also advise against using 'x' % y for your sql query

[vital yarrow]

Ah ok, I have no background at all, just trying to cobble something together from bits and pieces I can find.
So are you suggesting something like ``` sql="insert into forge_orders (json_response['duration'],json_response['is_buy_order'],json_response['issued'],json_response['location_id'],json_response['min_volume'],json_response['order_id'],json_response['price'],json_response['range'],json_response['system_id'],json_response['type_id'],json_response['volume_remain'],json_response['volume_total'])"

or does the json_response variable need to be outside of the string, so use the double quotes " instead of singles ' ?

[slate spire]

erm no

nothing like that

if you're gonna do it by tuple you'd need to do it one by one, alternatively you can just used named parameters and pass the whole json_response to it (assuming there's nothing else in the response)

[vital yarrow]

im unsure of what you mean sorry.
This is the json response I am getting
[{"duration":90,"is_buy_order":false,"issued":"2018-11-07T03:55:30Z","location_id":60001801,"min_volume":1,"order_id":5233442817,"price":8000.0,"range":"region","system_id":30000162,"type_id":251,"volume_remain":4994,"volume_total":5000} There are 1000 of these returned per page.
Do I need to dreate a new insert query for each column is what you are saying? or a new insert for each set?

[torn sphinx]

Right, that's a dictionary

Like a Map or a Hash

SQL values should be a list of values

That result is unordered

Usually you can use an "insertmany" sort of method to insert many rows, but you'll still need to convert that into a list. There are various ways to do this, maybe by mapping it.

[vital yarrow]

Ok, thanks for the tips. Now I have some idea of what I should be trying to do. 😃

[near anchor]

Spent 5 hours on finding this bug: "table X has 9 columns but 8 values were supplied"

[buoyant breach]

A somewhat dumb question. What would one use in async environment to access a PostgreSQL database instead of Django ORM?

Is there even an ORM that is compatible with async environment?

Or the whole idea of ORM and async somewhat contradict each other?

[zealous briar]

?_Hi guys, for example primary key such as user_id, should it be in varchar or INT

[buoyant breach]

It depends on what do you intend to put there and how do you intend to use it.

Generally having INT AUTOINCREMENT as a primary key is a good idea.

[dapper crescent]

best way to load CSV Into a dB?

It’s for a flaskapp?

[ionic pecan]

needs more information

"a db" could be anything from a cassandra cluster to sqlite

[dapper crescent]

So SQLite

I know there’s stuff like PYpi I think that do this sort of thing?

Was looking for mayb a better explanation in python on how it is done

do i just create a new db model defining the columns etc

?

[glossy ermine]

You can import a CSV file into SQLite if thats what you want

[dapper crescent]

yeah thats sounds bout right

then afterwards connecting to that db to do diff requests

[glossy ermine]

Yea you can do that

Don't need python to import really, can just use the command line

[dapper crescent]

and will that create the db with the corresponding column headers e.g. csv with car details

so brand, engine size, price etc

and whats the diff between using postrgres and mysql?

[glossy ermine]

that changed quickly

[dapper crescent]

lol

cause i know u can create a model

n connect to that model depending on ur db choice i.e. postgres and mysql

then do some shennagins there but im jus all over the place haha

[glossy ermine]

what do you mean by model

[dapper crescent]

in flask you can do something like db.model

n interact with that i.e opening a connection then doing some sql on it

sqlalchemy*

[glossy ermine]

oh you mean sqlalchemy

[dapper crescent]

yeah

https://docs.sqlalchemy.org/en/latest/core/connections.html

so create a model give it corresponding table column names matching the csv

then open that csv n load that in using sqlalchemy?

then afterwards simply just opening a connection to it when i need to get some data from it

im new to all this so jus trying ro wrap my head around it

[glossy ermine]

You don't need sqlalchemy to import the csv

If you want to use sqlalchemy afterwards to interact with the data, thats your choice

[dapper crescent]

yeah

so to import the csv its simply a cmd command?

[glossy ermine]

Yea

[dapper crescent]

what is the cmd sorry

il try it now

is it something like load data 'filename' etc? :S

and what database should i choose to use?

[glossy ermine]

So you type sqlite in commandline

Then type .mode csv

[dapper crescent]

ok

then point to the csv location?

[glossy ermine]

then .import csv/file/location table

[dapper crescent]

and will that put the table with the column headings matching csv headers?

i wanna upload all the data inside the csv to the table

[glossy ermine]

yes

[dapper crescent]

even if i create a blank table?

eg of dataset

[glossy ermine]

you don't need to create a table

[dapper crescent]

o when i run that cmd it'll do it itself?

[glossy ermine]

if it has the headers yes

[dapper crescent]

o cool sweet

also when it comes to choosing what database to use what would be the best?

'optimal'

or does it even matter

[glossy ermine]

it massively depends on what you plan to do

[dapper crescent]

eg?

like between a relational and non relational db

sql vs nosql?

i feel like nosql would be better due to not being bound to structure limitations? :S

maybe ive gone off topic lol il jus google that kinda stuff

thanks @glossy ermine

[glossy ermine]

Na its fine

9 times out of 10 you won't want nosql

[dapper crescent]

how come?

[glossy ermine]

because 9 times out of 10 your data is relational

[dapper crescent]

relational as in a column matching a row value etC?

i have no exp with databases at all so relational n non relational i dont really know much

other then running sql

[glossy ermine]

As in if your data is mumbled garbage and has no links then nosql is a perfect fit

But lets take a forum website for example, you have users, and those users have posts

This fits perfectly into a relational DB

[dapper crescent]

oooooooo

nice example

so for a car database company

i guess relational DB would work

best

[glossy ermine]

Yep :)

[dapper crescent]

so why do people like to use nosql stuff

[glossy ermine]

buzzwords

[dapper crescent]

like when would i use a nosql db

what scenario

[glossy ermine]

it depends on the nosql db chosen

nosql is a massive umbrella term really

[dapper crescent]

so i user postgres at my old work place