#databases | Python | Page 75

torn sphinx Oct 19, 2019, 9:47 AM

#

This code means a lot to me..now it is fucked by a stupid database...why does it not tell me what is it's problem? ;-;

#

https://media.discordapp.net/attachments/342318764227821568/634784408259526669/unknown.png?width=513&height=41

#

sqlite3..if you did not see that from the code in the link

#

Everything worked just fine until I got a error saying the same database is locked FROM ANOTHER COG......I will give that cog code,too

#

https://paste.pydis.com/fojiwozuga.py

#

I always did the databases on a timeout(1 second) and I even tryed deleteing and recreating the database serversettings.db

crystal sapphire Oct 19, 2019, 10:03 AM

#

A database cannot be accessed by 2 threads at once

#

You need to use something that can handle that for you

#

Also disconnecting/reconnecting from the database each time a command is executed isn't a great idea if you need good performances

#

@torn sphinx

#

I had that problem once, but now I use sqlachemy with a session factory and it's working just fine 👌

torn sphinx Oct 19, 2019, 10:17 AM

#

🤔

#

bruh

#

so if I run a command that is connecting to the database#1 and THEN on_message happens and connects to the database #1, that equals ERROR, database locked?

#

Did I understand you correctly?

#

But But I tried just typing a message and same error happened..good thing I make frequent back_ups of my code

#

@crystal sapphire

crystal sapphire Oct 19, 2019, 10:19 AM

#

Yeah this is the idea

#

Since discord.py is an async library, there is a probability that 2 parts of your code can access the database at the same time, causing the error

torn sphinx Oct 19, 2019, 10:20 AM

#

-_-

#

Well that sucks

crystal sapphire Oct 19, 2019, 10:20 AM

#

¯_(ツ)_/¯

alpine heath Oct 19, 2019, 10:20 AM

#

Hey guys it would mean a lot if someone who knew what they were doing would please help me

#

https://www.reddit.com/r/learnpython/comments/dk1i7q/need_help_asap_small_issue_with_the_code/?utm_source=share&utm_medium=ios_app&utm_name=iossmf

Need help ASAP. Small issue with the code!

Hey people of r/learnpython I'm working on a small school project and I'm getting this annoying error. It occurs when I try to update my Database....

#

heres my post

torn sphinx Oct 19, 2019, 10:21 AM

#

@crystal sapphire I never heard/worked with sqlalchemy,any tips?

alpine heath Oct 19, 2019, 10:21 AM

#

please feel free to comment ;//

crystal sapphire Oct 19, 2019, 10:22 AM

#

I don't have anything special, if you need some help, here is my repo https://github.com/Renondedju/ISARTBot/tree/rework-2.0
Just keep in mind that this is a WIP so things might no work as it should here and there

#

but it should get you started

#

@alpine heath query = "UPDATE inventory SET name=?, type=?, vendor=?, stock=?. price=?, sellingprice=? WHERE id=?" should be query = "UPDATE inventory SET name=?, type=?, vendor=?, stock=?, price=?, sellingprice=? WHERE id=?" instead

#

you used a . instead of a , somwhere

#

Read the error tho, everything is written

#

sqlite3.OperationalError: near ".": syntax error

torn sphinx Oct 19, 2019, 10:24 AM

#

Bruh

#

I will go trough 5 databases until I found the perfect one

#

database types*

#

This is a nightmare

crystal sapphire Oct 19, 2019, 10:25 AM

#

@torn sphinx here what the discord.py guys recommends:

https://www.postgresql.org/ - Widely used, open source database with good data integrity and feature set. The typical recommendation for bots.
https://www.mysql.com/ - The de facto SQL standard in software development. Usually not recommended here due to the decreased feature set in comparison to PostgreSQL, but strongly time-tested.
https://www.sqlite.org/index.html - File-based DB, no authentication. Lack of networking means query-by-query time is relatively fast, but tends to chug past a certain amount of load.

torn sphinx Oct 19, 2019, 10:26 AM

#

Back to REWRITING MY WHOLE DATABASE CODE ;-;

#

Thanks @crystal sapphire ..At least I am on the best way(to Hell) 😂

crystal sapphire Oct 19, 2019, 10:27 AM

#

SafetyJim salute

torn sphinx Oct 19, 2019, 10:31 AM

#

When you drink because of problem...and while you are drunk,the problem gets massivly bigger :'d

#

Over 3000 lines of code

undone roost Oct 19, 2019, 5:06 PM

#

in one to many relationship one contains foreign key and many contains the relationship code?

tawny sail Oct 19, 2019, 5:59 PM

#

what do u mean by relationship code?

#

its easier to under stand this with an example, Lets say you have 2 tables User and another called post. A user can have many posts but a post can have one author, i.e a User.
So you will define a foregin key relationship on the User table from the post table

real merlin Oct 19, 2019, 7:58 PM

#

Hey ! I have a problem, I can't find how to edit an already existing data in a json file, can someone please help me ?

torn sphinx Oct 19, 2019, 8:57 PM

#

how do you go about designing a complex SQL database? do you use some kind of software to help the process?
I've never built as big a database as I'll have to in my current project and I don't even know where to start

proven wagon Oct 19, 2019, 10:55 PM

#

Hey, im experiencing some issues about hosting a postgresql database on my new raspberry pi 4 4gb, the setup is fine and i can interact with the database locally from the pi. When trying to connect through pgAdmin on a different computer is where the errors come. The spesific error im having trouble with is this SSL error:

📎 unknown.png

#

If there is more information i can provide to get help please let me know

#

Im going to sleep now so dont expect a answer right away :/

mystic surge Oct 20, 2019, 1:28 AM

#

Which of these would be fastest

Update users set username =null where userid =2525

Replace?

Drop users where users =2525

rain wagon Oct 20, 2019, 2:09 AM

#

@torn sphinx I use https://dbdiagram.io/home. First, group your data into tables. Just how you think it is practical. Even if it is duplicate. Then, remove the duplicates by deciding which it's main table is and insert references into the other tables it has to be in. When I have done all that, then I go and use DBeaver to write a SQL file for the db and table creation and insert some example values to get started programming.

A Free Database Designer for Developers and Analysts

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

#

@proven wagon Have you allowed access for your computer in /etc/postgresql/%version%/main/pg_hba.conf ? E.g. something like host all all IP/Subnet md5?

#

@mystic surge One query is always faster than 2

undone roost Oct 20, 2019, 3:29 AM

#

@tawny sail class Theatre(db.Model):

""" Model for storing Theatre related details """
__tablename__ = "theatre"
__table_args__ = {'extend_existing': True}

id = Column(Integer, primary_key=True, autoincrement=True)
name = Column(String(50), nullable=False)
public_id = Column(String(100), unique=True)
description=Column(String(255))
base_price=Column(Integer,nullable=False,default=200)

audi_id = Column(Integer, ForeignKey('audi.id'))

Audi(db.Model):

""" Model for storing auditorium related details """
__tablename__ = "audi"
__table_args__ = {'extend_existing': True}

id=Column(Integer, primary_key=True,autoincrement=True)
audi_name= Column(String(20),nullable=False)
rows = Column(Integer, nullable=False)
columns = Column(Integer, nullable=False)
theatre = relationship('Theatre', backref='audi',lazy='dynamic')

#

dont mind the syntax

#

one theatre has many audi. So is it correct way to define it

#

?

tawny sail Oct 20, 2019, 3:30 AM

#

u can wrap it in python markdown highlighting

undone roost Oct 20, 2019, 3:31 AM

#

how

tawny sail Oct 20, 2019, 3:31 AM

#

!syntax

undone roost Oct 20, 2019, 3:32 AM

#

lol

tawny sail Oct 20, 2019, 3:32 AM

#

its like this

<code here>

#

📎 unknown.png

undone roost Oct 20, 2019, 3:33 AM

#

!syntax

#

how does that opens

tawny sail Oct 20, 2019, 3:33 AM

#

so one theater has many audi, so u define a foreign key in the audi table which links to the theater table

undone roost Oct 20, 2019, 3:34 AM

#

foreign key will be in audi table ? and not in theatre table?

tawny sail Oct 20, 2019, 3:34 AM

#

nop

#

i mean not in theater table and yes in audi table

undone roost Oct 20, 2019, 3:35 AM

#

okay foreign key in many's table and what about relationship?

#

theatre = relationship('Theatre', backref='audi',lazy='dynamic')

#

this line....will be changed to audi ?

#

and has to be put in theatre table?

tawny sail Oct 20, 2019, 3:36 AM

#

put the foreign key code in the audi table

#

is that sql alchemy?

undone roost Oct 20, 2019, 3:36 AM

#

yes

tawny sail Oct 20, 2019, 3:37 AM

#

oh im not sure how you do it with sqlalchemy but you get the concept right?

undone roost Oct 20, 2019, 3:37 AM

#

i am confused only about that

tawny sail Oct 20, 2019, 3:38 AM

#

in the audi table, define a foregin key on the theater table

undone roost Oct 20, 2019, 3:38 AM

#

on where to put foreign key and where to put relationship

#

wait what

#

class Theatre(db.Model):

""" Model for storing Theatre related details """
__tablename__ = "theatre"
id = Column(Integer, primary_key=True, autoincrement=True)
name = Column(String(50), nullable=False
audi_id = Column(Integer, ForeignKey('audi.id'))

#

this is right?

tawny sail Oct 20, 2019, 3:40 AM

#

theater VARCHAR(100) REFERENCES Theater(id)
)```

#

you put the code for the foreign key in the audi table

#

hope u can convert that to sql alchemy

undone roost Oct 20, 2019, 3:41 AM

#

okay

#

i got that

tawny sail Oct 20, 2019, 3:41 AM

#

class Audi(db.Model):
    theater = Column(String(100), ForeignKey(table.column)```
something like this i belive

undone roost Oct 20, 2019, 3:42 AM

#

where to put relationship?

tawny sail Oct 20, 2019, 3:42 AM

#

what relationship?

undone roost Oct 20, 2019, 3:42 AM

#

need to define relationship also na

#

theatre = relationship('Theatre', backref='audi',lazy='dynamic')

#

can change this to audi

tawny sail Oct 20, 2019, 3:43 AM

#

i guess, idk sql alchemy

undone roost Oct 20, 2019, 3:43 AM

#

ohh np

#

thanks a lot

tawny sail Oct 20, 2019, 3:44 AM

#

yw

gilded stag Oct 20, 2019, 4:53 AM

#

In an online booking system, how do we make sure if someone is in the process of booking a reservation, we don't allow another user to try to book it?

rain wagon Oct 20, 2019, 4:57 AM

#

Temporary tables are dropped when the connection that created it is dropped

#

So, set a table with a boolean and an id of the reservation as a temp table and check for it before allowing booking

gilded stag Oct 20, 2019, 4:57 AM

#

I see

rain wagon Oct 20, 2019, 4:57 AM

#

make sure the connection stays active until the session of the user ends

gilded stag Oct 20, 2019, 4:58 AM

#

so a 2nd table, query this table before querying the main booking table

rain wagon Oct 20, 2019, 4:58 AM

#

emphasis on temporary tables

gilded stag Oct 20, 2019, 4:58 AM

#

how come temporary table?

rain wagon Oct 20, 2019, 4:58 AM

#

Temporary tables are dropped when the connection that created it is dropped

#

and make sure there is a fallback timer

#

otherwise I can block your business by leaving my browser open

#

I'd allow 5-15 minutes until the reservation is cancelled, depending on how much there is to fill out etc

gilded stag Oct 20, 2019, 5:00 AM

#

I am confused as to why we are creating a temporary table. I would understand if its maybe a temporary row in a table

rain wagon Oct 20, 2019, 5:00 AM

#

because there is no such thing as a temporary row

#

There are temporary tables though, which you can create through sql within a select statement

gilded stag Oct 20, 2019, 5:01 AM

#

oh

rain wagon Oct 20, 2019, 5:01 AM

#

http://www.sqlservertutorial.net/sql-server-basics/sql-server-temporary-tables/

SQL Server Tutorial

admin

An Introduction to SQL Server Temporary Tables By Pracical Examples

This tutorial covers SQL Server Temporary Tables and how to manipulate the them effectively including creating and removing operations.

gilded stag Oct 20, 2019, 5:02 AM

#

@rain wagon thanks!

undone roost Oct 20, 2019, 7:29 AM

#

@gilded stag what are you working on?

#

i will have to deal with the same problem later on

proven wagon Oct 20, 2019, 11:54 AM

#

@rain wagon Yes, and also changed some the listen_addresses

#

Also tried turning SSL off, changing the port etc and always get the same error

#

I have a feeling its not the postgresql configs that are wrong

torn sphinx Oct 20, 2019, 12:10 PM

#

@rain wagon thank you for the suggestion

undone roost Oct 20, 2019, 1:41 PM

#

Seat.query.with_entities(Seat.id).filter_by(audi_id=(Showing.query.with_entities(Showing.audi_id).filter_by(id=data['showing_id']))).first()

#

will this get me a list of id in seat table?

#

i am getting error : none type object is not iterable....it means it is returning nothing right?

#

solved 😅

woeful ice Oct 20, 2019, 3:30 PM

#

Hi,
Is there a way to keep data somewhere in database and let many tables point to that data. Like if i have some text and i want to distribute that data in multiple location can i make the data once and then let the location point to that data

undone roost Oct 20, 2019, 3:32 PM

#

@rich trout will this query give me a list of IDs in table Seat


seat_id=Seat.query.with_entities(Seat.id).filter_by(audi_id=(Showing.query.with_entities(Showing.audi_id).filter_by(id=data['showing_id']).first())).all()

rich trout Oct 20, 2019, 3:34 PM

#

if it works, yes

undone roost Oct 20, 2019, 3:44 PM

#

but when i run a for loop over it i get only one entry

#

def save_a_reservation(data):
    rsrv=Reservation.query.filter_by(showing_id=data['showing_id']).first()
    if not rsrv:
        seat_id=Seat.query.with_entities(Seat.id).filter_by(audi_id=(Showing.query.with_entities(Showing.audi_id).filter_by(id=data['showing_id']).first())).all()
        seat_id=[id for id in seat_id]
        for i in seat_id:
            new_rsrv=Reservation(
                seat_id=i,
                showing_id=data['showing_id']
                )
            save_changes(new_rsrv)
            response_object = {
                'status': 'success',
                'message': 'Reservation detail successfully saved ',
            }
            return response_object, 201
        else:
            response_object = {
                'status': 'fail',
                'message': 'Reservation already exists.',
            }
            return response_object, 409

#

which is first id of seat table

#

i did list comprehension if in case i was getting a tuple instead of a list

undone roost Oct 20, 2019, 4:21 PM

#

there was some error in code .....corrected that and it worked

proven wagon Oct 20, 2019, 4:41 PM

#

Error when connecting to the database hosted on my raspberry pi.


SSL error: wrong version number
expected authentication request from server, but received S```
I have:
 - Changed listen_address to `'*'`
 - Turned SSL off
 - appended `host   all   all   my_ip   md5`
 - Changed port to same as pi is hosted off
 - Tried to re-install (twice)

Please help 🤷

ionic pecan Oct 20, 2019, 4:52 PM

#

whats your client library

#

and version of that

proven wagon Oct 20, 2019, 5:35 PM

#

How can i check? 🤔 Only got my Pi this week @ionic pecan e: sorry for late ping 🤷

ionic pecan Oct 20, 2019, 6:10 PM

#

no worries, friend, i don't mind it at all

#

for checking server version, you can either ask postgres or ask your package manager

#

$ sudo -u postgres psql -c 'SELECT version();'
[sudo] password for myself: 
                                                     version                                                      
------------------------------------------------------------------------------------------------------------------
 PostgreSQL 11.5 (Debian 11.5-3.pgdg100+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 8.3.0-6) 8.3.0, 64-bit
(1 row)

#

or

#

dpkg --list | fgrep postgresql-
ii  postgresql-10                     10.10-1.pgdg100+1            amd64        object-relational SQL database, version 10 server
ii  postgresql-11                     11.5-3.pgdg100+1             amd64        object-relational SQL database, version 11 server

but as you see that may display other versions as well, so the top one is more reliable, as it uses the default one (based on postgres' port)

#

for client library - how are you connecting to postgres from your python app?

#

chances are you're using psycopg2, right?

proven wagon Oct 20, 2019, 6:39 PM

#

No im connecting using pgAdmin

#

For some reason that query doesent work for me

#

I have 11.5

#

@ionic pecan

ionic pecan Oct 20, 2019, 7:11 PM

#

wait, so that error is from pgadmin?

proven wagon Oct 20, 2019, 7:31 PM

#

I dont think sop

#

I have always been able to connect to localhost and databases hosted online with pgAdmin

proven wagon Oct 20, 2019, 8:00 PM

#

oh yeah nvm yes, the error is coming when connecting in pgAdmin @ionic pecan didnt really see what you meant

proven wagon Oct 20, 2019, 8:43 PM

#

📎 unknown.png

stable pilot Oct 21, 2019, 4:12 AM

#

hey guys

#

can anyone please help me

#

i have this code

#

            await ctx.send('player has won')
                    async with conn.cursor() as mycursor:
                        userint = ctx.author.id
                        check2 = "SELECT discordCoin FROM discordcoin WHERE discordID = %s"
                        await mycursor.execute(check2, (userint,))
                        myresult2 = await mycursor.fetchone()
                        addCoin = (myresult2[0] + (coinAmount + coinAmount))
                        sql = "UPDATE discordcoin SET discordCoin = %s WHERE discordID = %s"
                        val = (addCoin, ctx.author.id)
                    async with get_conn() as conn:
                        async with conn.cursor() as cur:
                            await cur.execute(sql, val)
                        await conn.commit()
                    conn.close()


                else:
                    conn.close()
                    await ctx.send('NPC has won')```

#

just wondering why it aintworking >

pure cypress Oct 21, 2019, 5:03 AM

#

Why are you getting a new connection when you already have one?

#

or a new cursor for that matter

vocal moon Oct 21, 2019, 7:12 AM

#

@bot.command()
#@commands.has_any_role("[JA] Junior Agent", "[BA] Border Agent", "[BC] Border Commander", "[BI] Border Inspector", "[DD] Department CO/XO", "[DBC] Deputy Border Chief", "[BC] Border Chief", "[BA] Board of Advisors", "[DBD] Deputy Commissioner", "[C] Commissioner")
async def createFile(ctx):
    
    member = ctx.author
    role = member.roles
    
    await ctx.send('`Enter your ROBLOX name.`')
    def check(m):
        return m.author == ctx.author
    robloxIgn = await bot.wait_for('message', check=check)
    
    await ctx.send("`Enter your callsign.`")
    callsign = await bot.wait_for('message', check=check)
    
    hs = discord.utils.get(ctx.author.roles, id='634073747195822090') #630370651537932308
    ofo = discord.utils.get(ctx.author.roles, id='634129126655000586') #630370583959306287

    if hs in role:
        hs_var = {HS}
        c.execute("INSERT INTO this (roblox, call, depname) VALUES (?, ?, ?)", (robloxIgn.content, callsign.content, hs_var))
        conn.commit()
    elif ofo in role:
        ofo_var = {OFO}
        c.execute("INSERT INTO this (roblox, call, depname) VALUES (?, ?, ?)", (robloxIgn.content, callsign.content, ofo_var))
        conn.commit()
    else:
        await ctx.send('ok')
    await ctx.send(f"`Successfully created a user file for {robloxIgn.content}`")

This code does not output anything onto the SQL, not sure whats wrong nor does it output any errors.

rich trout Oct 21, 2019, 7:46 AM

#

@vocal moon hs and ofo are always None, because discord.py's ids are integers, not strings. There will be no role with id "634073747195822090", but there may be one with id 634073747195822090

vocal moon Oct 21, 2019, 8:24 AM

#

O

#

Oh

#

I’m stupid

#

Thanks

remote hatch Oct 21, 2019, 12:13 PM

#

with pgadmin 4 how do you make a sub table from a column?

#

i have a column guild id and i want to have a set of preferences for the guild

void otter Oct 21, 2019, 12:27 PM

#

you can make a table and connect it

remote hatch Oct 21, 2019, 12:29 PM

#

how do you do that?

#

@void otter can you explain a bit more?

void otter Oct 21, 2019, 12:38 PM

#

well, with foreign key

#

relationship, can be 1 config for guild(1-1)

remote hatch Oct 21, 2019, 12:39 PM

#

so do you use foreign tables?

#

can you send some documentation or something?

void otter Oct 21, 2019, 12:40 PM

#

foreign keys to connect tables

#

take a look at table relationships, fundamental for database management and creation

torn sphinx Oct 21, 2019, 1:10 PM

#

Postgresql
Is it bad if I directly assign a value when selecting things from the database? Security wise

#

I know in sqlite3 we do SOMETHING = ?

buoyant breach Oct 21, 2019, 1:15 PM

#

http://initd.org/psycopg/docs/usage.html

>>> cur.execute("INSERT INTO numbers VALUES (%s, %s)" % (10, 20)) # WRONG
>>> cur.execute("INSERT INTO numbers VALUES (%s, %s)", (10, 20))  # correct

#

http://initd.org/psycopg/docs/usage.html#passing-parameters-to-sql-queries

#

That is if you use psycopg. Other libs may have other approaches.

torn sphinx Oct 21, 2019, 1:26 PM

#

Thank you very much!

torn sphinx Oct 21, 2019, 1:49 PM

#

Do you know how I autoincrement in postgresql?

#

xd

buoyant breach Oct 21, 2019, 1:54 PM

#

There is a statement for that in table schema query.

#

I think in postgres there is a datatype called SERIAL.

#

https://www.tutorialspoint.com/postgresql/postgresql_using_autoincrement.htm

PostgreSQL - AUTO INCREMENT - Tutorialspoint

PostgreSQL - AUTO INCREMENT - PostgreSQL has the data types smallserial, serial and bigserial; these are not true types, but merely a notational convenience for creating unique identifier co

torn sphinx Oct 21, 2019, 1:56 PM

#

You are such a nice person!! Thanks

buoyant breach Oct 21, 2019, 1:58 PM

#

You can make your own sequence if you like, but generally serial should be enough.

torn sphinx Oct 21, 2019, 2:00 PM

#

Cool

#

I get too many connections on the role??

#

the port is too busy or what?

#

nevermind I just had to refresh my database browser -_-

proven wagon Oct 21, 2019, 4:07 PM

#

Error when connecting to the database hosted on my raspberry pi.

I am using pgAdmin to connect.


SSL error: wrong version number
expected authentication request from server, but received S```
I have:
 - postgresql version 11.5 on my pi
 - Changed listen_address to `'*'`
 - Tried turning SSL off
 - appended `host   all   all   my_ip   md5`
 - Changed port to same as pi is hosted off
 - Tried to re-install (twice)

Please help 🤷

proven wagon Oct 21, 2019, 7:55 PM

#

For anyone looking back at this. I solved it.
did everything again, but with sudo apt-get update && sudo apt-get upgrade -y before installing postgresql

torn sphinx Oct 21, 2019, 9:24 PM

#

😂 glad you fixed it

torn sphinx Oct 22, 2019, 4:12 AM

#

I store datetime.datetime.utcnow() on my database on a column that's TIMESTAMP WITH TIME ZONE,
when i fetch this value from the database (i get it as utc, not sure if i can change this behavior with asyncpg)
i try to compare it again with datetime.datetime.utcnow() and it complains that i can't deal with offset-naive and offset-aware datetimes?

pure scroll Oct 22, 2019, 7:39 AM

#

datetime.utcnow does not return any timezone offset

#

while the result from a database will have explicit UTC offset

tropic crescent Oct 22, 2019, 9:48 AM

#

Hi guys, I am trying to set up a python DB using python DB-API I am having issues inserting values into my DB using parameters when I want to insert integers and not just strings.

#

I tried to do it this way - but it makes sense that I get an error since I have designed my tables columns to be integers other than "Name".

📎 screenshot.png

#

What is the correct paramstyle for inserting integers I already tried %int

#

?

torn sphinx Oct 22, 2019, 10:25 AM

#

should I use multiple databases rather than different tables only for POSTGRESQL ? I was using sqlite3 before,but I got databases locked..thus why I am switching

torn sphinx Oct 22, 2019, 11:04 AM

#

@pure scroll So it is wise to do: datetime.datetime.now(datetime.timezone.utc) instead of utcnow()? I want to handle everything in UTC and convert as it's needed (if it is)

Also if I want to compare the UTC time I have on my database on that table (from that datetime.datetime.now()) I should compare it with something like CURRENT_TIMESTAMP AT TIME ZONE 'UTC'?

pure scroll Oct 22, 2019, 11:04 AM

#

depends on the database, but most of the times databases are able to handle time offsets

#

e.g if you compare UTC vs GMT database will know how to convert one to another

#

datetime.datetime.now(datetime.timezone.utc) yeah that will work

torn sphinx Oct 22, 2019, 11:06 AM

#

Well all I know is that what I will be inserting into the table is utc all the time

pure scroll Oct 22, 2019, 11:06 AM

#

yes, but you can query for it using any timezone

#

db will convert your query timestamp to UTC

torn sphinx Oct 22, 2019, 11:06 AM

#

It's stored as UTC right?

#

(All the time, regardless of time zone)

pure scroll Oct 22, 2019, 11:07 AM

#

depends on your configuration you can make it store in any other timezone

#

but it's recommended to do it in UTC

torn sphinx Oct 22, 2019, 11:07 AM

#

hmm i never saw any kind of setting to change how it' stored (other than setting current time zone)

pure scroll Oct 22, 2019, 11:08 AM

#

which database you have?

torn sphinx Oct 22, 2019, 11:08 AM

#

PSQL

pure scroll Oct 22, 2019, 11:09 AM

#

yeah, it stores in UTC and only utc

torn sphinx Oct 22, 2019, 11:09 AM

#

Yeah I was just checking that

#

I'm curious if the only way to change what time zone it returns is by doing select current_timestamp at time zone 'anything other than UTC';

#

or if it can be i.e. set up by asyncpg

pure scroll Oct 22, 2019, 11:11 AM

#

I think it's up to you, I personally prefer to have as less logic on DB layer as possible, so I'd do that in my python code

#

but there is nothing wrong with doing that on DB and most likely DB will make it faster

torn sphinx Oct 22, 2019, 11:13 AM

#

oh mostly curiosity as i'll handle everything in utc and dont need to show any other time zone

#

just want to be sure nothing messes up

#

https://discordapp.com/channels/267624335836053506/342318764227821568/636148237446676481

pure scroll Oct 22, 2019, 11:16 AM

#

multiple databases to solve what problem?

#

Postgres is able to hold lots of concurrent requests and connections to it (depends on your server and queries of course but can go up to thousands)

torn sphinx Oct 22, 2019, 11:19 AM

#

COOL

#

I am asking because I am scared the database gets locked or something like sqlite3 did 😂

#

So to summarize:

create table testing (timedata timezone with timestamp); 
# then insert with
await db.execute('insert into time(timedata) values($1)', datetime.datetime.now(datetime.timezone.utc))

is fine?

pure scroll Oct 22, 2019, 11:24 AM

#

yeah, should work

torn sphinx Oct 22, 2019, 11:51 AM

#

I'm not really sure if this is correct behaviour or i'm doing something wrong but if i do:

now = datetime.datetime.now(datetime.timezone.utc)
jst = now.astimezone(pytz.timezone('Japan'))
await db.execute('INSERT INTO testing(timedata) VALUES($1)', jst)
result = await db.fetchval('SELECT timedata AT TIME ZONE \'Japan\' FROM testing')
print(result == jst) # This gives False

# Japan Time: 2019-10-22 20:50:55.351206+09:00
# Japan Time (Database): 2019-10-22 20:50:55.351206

I'm not sure what's causing this

torn sphinx Oct 22, 2019, 12:14 PM

#

But if I return it with no timezone (I assume it should be returning as UTC since the value is 2019-10-22 12:13:27.724365+00:00 the comparison works fine

#

select timedata AT TIME ZONE 'Japan' from testing;
          timedata
----------------------------
 2019-10-22 21:13:27.724365
(1 row)

tawny plank Oct 22, 2019, 3:38 PM

#

Hey! I would need to interact with a MySQL DB in one of my python project but I don't know which module to use for that. Should I just use the MySQL Connector for python or would you rather advise others? Which? Why?

rough onyx Oct 22, 2019, 7:17 PM

#

@tawny plank look up sqlalchemy

zealous echo Oct 22, 2019, 8:36 PM

#

Im learning sql. Any good certs to prove to employers I know what Im doing?

tawny plank Oct 22, 2019, 9:01 PM

#

@rough onyx ok thanks. Why would you recommend it over the others though? What are your experiences with DB modules for python?

rough onyx Oct 22, 2019, 9:08 PM

#

@tawny plank sqlalchemy is extremely popular by itself and you will find lots of plugins to make it interact with libraries like flask, graphene, etc...

#

also it will be a bit more "abstract" than something like mysql-connector-python, so it will probably lead to quicker development and code that's easier to port for other rdbms

tawny plank Oct 22, 2019, 9:11 PM

#

How is it more abstract? (not very experienced with DBs)

rough onyx Oct 22, 2019, 9:12 PM

#

basically mysql-connector-python is about sending raw mysql queries

tawny plank Oct 22, 2019, 9:12 PM

#

ok

rough onyx Oct 22, 2019, 9:12 PM

#

sqlalchemy let's you use the db with python function

#

like

#

s = select([table.c.col1]).where(table.c.col2==5)

tawny plank Oct 22, 2019, 9:13 PM

#

So it create sother models? Like makes a table a dict or something of the kind?

#

ok I see

rough onyx Oct 22, 2019, 9:13 PM

#

kinda yeah

tawny plank Oct 22, 2019, 9:13 PM

#

ok

rough onyx Oct 22, 2019, 9:13 PM

#

more like turns everything to python object / classes

#

gtg

tawny plank Oct 22, 2019, 9:13 PM

#

ok

#

what is gtg?

#

also is it still possible to send raw SQL querries?

low stone Oct 23, 2019, 3:28 AM

#

vibe check

#

okay i guess no vibes here

elfin geyser Oct 23, 2019, 7:27 AM

#

            columns = [seller,buyer,item,price]
            c.execute("""INSERT INTO auctions (seller,buyer,item,price)
              values(?,?,?,?)"""  % (columns))```

#

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

#

            c.execute("""INSERT INTO auctions (seller,buyer,item,price)
              VALUES(?,?,?,?)"""  % (seller,buyer,item,price))```
`TypeError: not all arguments converted during string formatting`

#

what am i missing?

molten knoll Oct 23, 2019, 7:34 AM

#

How many columns does the table auctions have? If there are missing columns do they have default values?

elfin geyser Oct 23, 2019, 7:35 AM

#

c.execute("""CREATE TABLE auctions (seller,buyer,item,price)""")

twilit swan Oct 23, 2019, 7:36 AM

#

!e

 """INSERT INTO auctions (seller,buyer,item,price) VALUES (%d, %s, %s, %s)""" % (1, "hello", "world", 2)

delicate fieldBOT Oct 23, 2019, 7:36 AM

#

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

twilit swan Oct 23, 2019, 7:37 AM

#

@elfin geyser you need to use the old printf-style formatting placeholders if you're using % for string formatting

#

>>> """INSERT INTO auctions (seller,buyer,item,price) VALUES (%d, %s, %s, %s)""" % (1, "hello", "world", 2)
'INSERT INTO auctions (seller,buyer,item,price) VALUES (1, hello, world, 2)'

#

try this

pure scroll Oct 23, 2019, 7:38 AM

#

never try this with database queries

twilit swan Oct 23, 2019, 7:38 AM

#

also true ^

pure scroll Oct 23, 2019, 7:38 AM

#

just never use string formatting as this is a subject to sql injection

molten knoll Oct 23, 2019, 7:39 AM

#

c.execute("INSERT INTO auctions (seller,buyer,item,price) VALUES(?,?,?,?)" , (seller,buyer,item,price))

I think the proper syntax is a , not %.

pure scroll Oct 23, 2019, 7:39 AM

#

but @twilit swan was half way there, you most likely need to use %s instead of ?, even though we have a database API standart in python, none of db drivers do really care about it

molten knoll Oct 23, 2019, 7:40 AM

#

using ? is the recommended way of executing sql statements I believe.

pure scroll Oct 23, 2019, 7:40 AM

#

yeah, this is a standard but again, many libraries don't care about it

elfin geyser Oct 23, 2019, 7:50 AM

#

@twilit swan when i try to run your example:

    c.execute("""INSERT INTO auctions (seller,buyer,item,price) VALUES (%d, %s, %s, %s)""" % (1, "hello", "world", 2))
sqlite3.OperationalError: no such column: hello```

twilit swan Oct 23, 2019, 7:52 AM

#

try % (1, "'hello'", "'world'", 2)

#

" 'hello' "

#

@elfin geyser it thinks hello is a column name because i didn't put it in single quotes

elfin geyser Oct 23, 2019, 7:53 AM

#

hmm

#

so how should i do that with variable?

twilit swan Oct 23, 2019, 7:55 AM

#

did it work? you mean you want to store the " 'hello' " into a variable?

elfin geyser Oct 23, 2019, 7:56 AM

#

yes

#

a - "hello" c.execute("""INSERT INTO auctions (seller,buyer,item,price) VALUES (%d, %s, %s, %s)""" % (1, 'a', "'world'", 2))

molten knoll Oct 23, 2019, 7:58 AM

#

a also needs to be put into quotation marks else it looks for a column called a instead of the string a.

#

https://discordapp.com/channels/267624335836053506/342318764227821568/636468701972922368
did this not work?

#

with the ? you don't need to worry about putting the right quotes. I also don't know if single quotes are accepted.

elfin geyser Oct 23, 2019, 8:00 AM

#

with ? it's perfect 🙂

#

thank you very much!

twilit swan Oct 23, 2019, 8:01 AM

#

yeah, ? is the better way compared to manually constructing queries and worrying about escaping

molten knoll Oct 23, 2019, 8:08 AM

#

This is why you should stick to ? when possible:

c.execute("""INSERT INTO auctions (seller,buyer,item,price) VALUES (%s, %s, %s, %s)""" % ('"sql"','"injection"','"careful"','"lmao"); DROP TABLE auctions;--'))

it inserts a new row into auctions and deletes the table auctions. With ? the last value would be the string "lmao"); DROP TABLE auctions;. Without ? it would evaluate the SQL statement DROP TABLE auctions

#

📎 exploits_of_a_mom.png

#

-- is a comment. With that it will ignore the closing bracket ) that would normally follow.

proven wagon Oct 23, 2019, 9:01 AM

#

What are the main differenced between asyncpg.pool.Pool and asyncpg.Connection

pure scroll Oct 23, 2019, 9:32 AM

#

a pool is a set of connections

#

Because establishing connection with a database server takes time it makes sense too keep it alive.
So Pool is abstraction of set of connections, whenever you take a connection from it, it will start a transaction and give one available connection to you.
Whenever you close it, it will actually return it back to pool and also would execute rollback statement so that connection is not dirty, and has no pending transaction running on it.

proven wagon Oct 23, 2019, 10:06 AM

#

Thanks 🙂

ivory turtle Oct 23, 2019, 2:22 PM

#

Nvm, fixed

tawny plank Oct 23, 2019, 3:59 PM

#

Hey!

#

I must be completly blind but I've been looking at how to install a DBAPI for sqlalchemy

#

I wonder, which is the recommended one?

torn sphinx Oct 23, 2019, 5:08 PM

#

@pure scroll reading what you said about, since a pool is a set of connections,
whats the difference between doing:

mypool.execute('myquery')

vs

async with mypool.acquire() as test:
    test.execute('myquery')

what is the purpose of the second and when should i use it?

pure scroll Oct 23, 2019, 5:58 PM

#

in the first example pool will take the first available connection and execute statement on it.
so e.g if you do

mypool.execute('query1')
mypool.execute('query2')
``` its not guaranteed that both sttatement will happen within the same db connection

#

while in the second example yoou explicitly aquire a connection and pool will never give it to any other thread/coroutine until you release it back @torn sphinx

torn sphinx Oct 23, 2019, 6:47 PM

#

@pure scroll oh, as i thought makes sense. what "real life" use cases are a good example of when needing to acquire()?

pure scroll Oct 23, 2019, 6:58 PM

#

Can't come up with any apart from a transaction, as long as you don't need a transaction, you can stick to executing things on pool directly

plush dune Oct 24, 2019, 6:27 AM

#

hey, I've tried asking this in web-dev channel... How do I make each name return it's own database... right now, when I print my database in html, it returns every database ...

crisp tundra Oct 24, 2019, 4:01 PM

#

I need some help with choosing a data type for postgres sql

#

I basically have some json that's in a format

#

the string appears multiple times, like tags from an album

#

and some cells may have more than one of these items

#

Basically Im planning to make arrays of hstores

#

is there any better data type I should use?

#

the key can be dense_ranked and made into a one to many relationship

undone roost Oct 24, 2019, 5:16 PM

#

how to make changes into and existing record in SQLAlchemy?

frigid tartan Oct 24, 2019, 5:27 PM

#

is it generally bad practice to access data (csv's, sql tables) in a classes constructor? that is to say, I pass a name to the constructor and then have the constructor find the relevant data in a file to set it's attributes

vocal moon Oct 24, 2019, 9:51 PM

#

import discord
from discord.ext import commands
import sqlite3



class RPG(commands.Cog):

    def __init__(self, bot):
        self.bot = bot

    global conn
    global c
    conn = sqlite3.connect('main.db')
    c = conn.cursor()

    def create_table():
        c.execute('CREATE TABLE IF NOT EXISTS info(name BLOB, ammount BLOB)')
    create_table()

    @commands.command()
    async def create(self, ctx):
        value = 100.00
        value = float(value)
        author = ctx.message.author
        c.execute("INSERT INTO this (name, ammount) VALUES (?, ?)", (author, value))
        conn.commit()
        await ctx.send(f"`Created a file for {author}`")
    
def setup(bot):
    bot.add_cog(RPG(bot))

I have this code, the main error is in the line where I insert the two variables, I am getting no errors and no output

vocal moon Oct 24, 2019, 10:09 PM

#

anyone?

proud iron Oct 24, 2019, 10:09 PM

#

@vocal moon
I just started Python 2 weeks ago, so I'm not rlly sure, but maybe...

The output of ctx.message.author is type abc.User. So it's not just a simple string, not sure if your database can store that datatype? You can try changing it to ctx.message.author.id (Data type integer).

vocal moon Oct 24, 2019, 10:09 PM

#

O

#

hi lemme read

proud iron Oct 24, 2019, 10:10 PM

#

:p

vocal moon Oct 24, 2019, 10:10 PM

#

oh yeh im storing it as the wrong thing

#

thanks

proud iron Oct 24, 2019, 10:11 PM

#

Np ;)

vocal moon Oct 24, 2019, 10:11 PM

#

whats the thing for a number again

#

value?

#

in sql

proud iron Oct 24, 2019, 10:11 PM

#

Idk dude, I'm not using your kind of database.

vocal moon Oct 24, 2019, 10:12 PM

#

o thanks ill google it also can i dm you real quick

proud iron Oct 24, 2019, 10:12 PM

#

Ofc you can dm me

vocal moon Oct 24, 2019, 10:12 PM

#

thanks

candid night Oct 25, 2019, 5:58 AM

#

https://mystb.in/upixewowin.bash

Mystbin

A fast intuitive paste service.

rancid root Oct 25, 2019, 7:13 AM

#

📎 unknown.png

#

can someone explain how this is possible in sql server and what dumb mistake I am making?

#

the only difference between the left and right query is removing the degenerate groupby fields. The output is the same. But the left query runs in 8s while the right in 45s...

buoyant breach Oct 25, 2019, 7:24 AM

#

What exactly is the issue?

rancid root Oct 25, 2019, 7:25 AM

#

the fact that removing a bunch of degenerate groupby makes the thing run 5x slower

buoyant breach Oct 25, 2019, 7:25 AM

#

Execution time difference? Grouping may take quite a while depending on the nature and distribution of the data.

#

Oh, so it's the other way around.

#

Lol. I see.

rancid root Oct 25, 2019, 7:26 AM

#

slower with groupby removed
and key point is the groupby are degenerate, they have a cardinality of 1, they match the where clause predicates

buoyant breach Oct 25, 2019, 7:26 AM

#

Then I would guess there are different optimizations and essentially different low level queries are being executed.

#

You may want to do EXPLAIN or whatever the equivalent of your server for it is and inspect the output.

rancid root Oct 25, 2019, 7:28 AM

#

yeah i'm currently locked out from showplan/explain so just left to guess at the random magic sql server must be doing under the hood to act like this

buoyant breach Oct 25, 2019, 7:29 AM

#

But yeah, the behavior is puzzling to say the least.

#

I would be interested to know the specifics too if you ever get to it. =]

rancid root Oct 25, 2019, 7:29 AM

#

only guess is the groupby stack might be aligned to the clustered index order and the reduced stack is not, so it seeks more..? Idk

rich trout Oct 25, 2019, 7:33 AM

#

I suspect it's difficult for the database to compare A_Location_ID

#

That, or Type_ID is indexed and A_Location_ID is not, which would lead to the differing query speeds

#

The one on the left, then, would involve grouping first by type_Id, then by whatever else it has, but the other would involve grouping over a parameter that may be much more awkward to section over

#

Especially if it treats the index as a precomputed binary tree, at which point the grouping is already essentially computed at the beginning of the query itself

rancid root Oct 25, 2019, 7:37 AM

#

hrm

rich trout Oct 25, 2019, 7:37 AM

#

...I read your query again

#

If group_by occurs before the WHERE statement, this makes sense

#

that does not seem like an optimized query plan though

rancid root Oct 25, 2019, 7:39 AM

#

pretty sure WHERE is the first thing that runs

#

https://jvns.ca/blog/2019/10/03/sql-queries-don-t-start-with-select/

SQL queries don't start with SELECT - Julia Evans

SQL queries don't start with SELECT

rich trout Oct 25, 2019, 7:41 AM

#

I found a good explanation of your exact circumstances

#

due to bad luck and a hedging optimizer, it does indeed seem that some of your filters are being pushed behind the join

#

https://www.sql.kiwi/2012/03/fun-with-aggregates.html

Fun with Scalar and Vector Aggregates

A SQL Server technical blog from New Zealand by Paul White (SQL Kiwi)

#

I would expect only adding type_id to the group_by would return you to the fast speed

#

Or, as the article suggests, in some cases () might work too

rancid root Oct 25, 2019, 7:48 AM

#

i'll give that a whirl and ping you back if it works

rich trout Oct 25, 2019, 7:48 AM

#

👍

#

It's all fancy words tacked onto "conjecture" until it works!

rancid root Oct 25, 2019, 7:51 AM

#

nope

#

and it seems like removing any of the groupby stages from the original stack causes a perf hit (not sure if linear)

rich trout Oct 25, 2019, 7:52 AM

#

hm.

#

A portion of the performance is lost for each one?

#

If it's splitting the where clause, that might happen

#

Each group_by could force the where to be on the other side of the join

rancid root Oct 25, 2019, 7:53 AM

#

looks like its constant

#

original stack = 8s
anything else = 45s

rich trout Oct 25, 2019, 7:53 AM

#

odd

#

I have no idea then

rancid root Oct 25, 2019, 7:54 AM

#

thanks for helping out, im gonna give up for now and wait for them to give me showplan so at least this isn't blind guessing

rich trout Oct 25, 2019, 7:55 AM

#

yeah

#

good luck

tropic crescent Oct 25, 2019, 10:22 AM

#

Hi guys, I am getting a syntax error that I can not fix.

#

I am typing this statement: """CREATE OR REPLACE TABLE item (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(128) NOT NULL,  type VARCHAR(128) NOT NULL,  amount INT NOT NULL,  cost INT NOT NULL);

#

I get this error:
mysql.connector.errors.ProgrammingError: 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 'type VARCHAR(128) NOT NULL,  amount INT NOT NULL,  cost INT NOT NULL)' at line 1

#

I also tried to change "type" to a different name in case it was a reserved word

stable badger Oct 25, 2019, 11:06 AM

#

!sucribe

vocal moon Oct 25, 2019, 11:59 AM

#

import sqlite3

conn = sqlite3.connect('users.db')
c = conn.cursor()

def create_table():
    c.execute("CREATE TABLE IF NOT EXISTS this(id BLOB,  value BLOB)")
create_table()

@bot.command()
async def create(ctx):
    member = ctx.author.id
    value = 100.00
    c.execute("INSERT INTO this (id, value) VALUES (?, ?, ?)", (member, value))
    conn.commit()
    await ctx.send(f"`Created a file for {ctx.author}`")

It does not seem to be working or outputting any errors

rich trout Oct 25, 2019, 12:03 PM

#

INSERT INTO this (id, value) VALUES (?, ?, ?)

is wrong, the number of ?'s should match the number of inputs

#

You should be getting an error

torn sphinx Oct 25, 2019, 12:05 PM

#

Do take note to sanitize the inputs before inserting them into the SQL query.

rancid root Oct 25, 2019, 12:10 PM

#

@rich trout so turns out at least some facet of it is caching of some sort. :/

rich trout Oct 25, 2019, 12:10 PM

#

oh that sounds peachy

rancid root Oct 25, 2019, 12:10 PM

#

every time the query string is changed, it takes 45s to run, then 8s on the subsequent run. The groupby stack was a red herring

#

but that makes the actual perf problem even more fun to diagnose...

rich trout Oct 25, 2019, 12:11 PM

#

I had to deal with an http cache that ignored cache-control: no cache

#

man was that fun debugging cached authentication

#

mumble

#

You are grouping by A_Location_ID, correct?

#

Do you have permission to create an index?

rancid root Oct 25, 2019, 12:12 PM

#

location id, geometry

rich trout Oct 25, 2019, 12:12 PM

#

I also spot a distict(stringvalue), how big is stringvalue?

rancid root Oct 25, 2019, 12:12 PM

#

yeah I think I can do pretty much anything except showplan, because our itsec guys are super smart and don't need to have their hand held for each individual permission on each db

#

it's a varchar 100

#

we'll be reducing it to a numeric hash

rich trout Oct 25, 2019, 12:13 PM

#

That could be the kicker

#

how many rows, ish

rancid root Oct 25, 2019, 12:13 PM

#

it walks 10m, the total table is about 40m

#

i'm shocked it takes 8 seconds to do so little work

rich trout Oct 25, 2019, 12:13 PM

#

that would do it

rancid root Oct 25, 2019, 12:13 PM

#

we must still be doing something really wrong

rich trout Oct 25, 2019, 12:13 PM

#

It's not doing little work

rancid root Oct 25, 2019, 12:14 PM

#

I typically use something like clickhouse for these kinds of workloads and it'd do this in 8 milliseconds

rich trout Oct 25, 2019, 12:14 PM

#

its comparing 10 million rows of 100 length strings to each other

rancid root Oct 25, 2019, 12:14 PM

#

why would it compare them to each other

#

it's an o(n) operation no?

rich trout Oct 25, 2019, 12:14 PM

#

distinct()

#

It's O(nm) where M is the number of distinct strings

rancid root Oct 25, 2019, 12:14 PM

#

see value
check against seen values
is it new? add, incr count
move on

rich trout Oct 25, 2019, 12:14 PM

#

or, I suppose, O(n) hashes

rancid root Oct 25, 2019, 12:14 PM

#

yeah

rich trout Oct 25, 2019, 12:15 PM

#

but 100 characters has a very large number of possibilities

rancid root Oct 25, 2019, 12:15 PM

#

i want to try another agg that's not count d to verify this, not sure what i can use though

#

sum/max/avg/etc are all precalculated in column stores

#

so not a fair comparison

rich trout Oct 25, 2019, 12:16 PM

#

I'd just try Count(stringvalue)

rancid root Oct 25, 2019, 12:16 PM

#

precalc'd

rich trout Oct 25, 2019, 12:16 PM

#

Is it though? Since it's per group?

rancid root Oct 25, 2019, 12:16 PM

#

yes because it can then sum the precalc group counts without actually evaluating them per row

#

count d is unique here in having to walk every row

rich trout Oct 25, 2019, 12:17 PM

#

why not do an == comparison

rancid root Oct 25, 2019, 12:18 PM

#

I just tried count anyway. Same 8 seconds as count d actually

#

so that's not the bottleneck

#

i mean maybe 8 sec is just how long sql server takes to traverse 10m rows, i don't know what to expect from it

#

but something like clickhouse would do this closer to 8-80ms

rich trout Oct 25, 2019, 12:20 PM

#

Well, since it pulled 8 seconds I think we can deduce that it is the hashing of uniques that's triggering the 5x time increase

rancid root Oct 25, 2019, 12:20 PM

#

let me verify that..

#

good point

#

yes, the caching behaviour and 5x slowdown on first run seems to be gone. That's one down

rich trout Oct 25, 2019, 12:21 PM

#

Good, at least we know where that's coming from

rancid root Oct 25, 2019, 12:21 PM

#

I guess it retains the hashmap of seen items between runs and dumps it if the query changes

rich trout Oct 25, 2019, 12:23 PM

#

Try a run with just counts(), no actual rows returned

#

That ought to give us a view on how fast it iterates and filters through it all

rancid root Oct 25, 2019, 12:33 PM

#

ok so

#

1: the caching behaviour is back, so I guess it's just an unpredictable force for now

#

2:

FROM 
(
SELECT 
      count(*) as Num_Records


  FROM dbo.FACT_AssetAttributes_Col
  where Attr_URN = 'urn:asset:ci_name'
  and TypeId = 5
  and Aggregation = 'COUNTD' 
  and DataFormat = 'numeric' 
  and DisplayName = 'CI Name' 
  and GroupDisplayName = 'Asset'
  group by 
        Geography
        ,A_Location_ID
) t```
slow time with groupbys, instant without

rich trout Oct 25, 2019, 12:38 PM

#

Is Geography a string?

rancid root Oct 25, 2019, 12:38 PM

#

it is

#

will also be hashed to integer later

rich trout Oct 25, 2019, 12:38 PM

#

try group_by type_id

rancid root Oct 25, 2019, 12:39 PM

#

you win

rich trout Oct 25, 2019, 12:39 PM

#

Instant again?

rancid root Oct 25, 2019, 12:39 PM

#

anything that's not a string in the groupbys is instant

rich trout Oct 25, 2019, 12:39 PM

#

Yep

rancid root Oct 25, 2019, 12:39 PM

#

⛅

#

i get it

#

thanks for taking the time to help with this

rich trout Oct 25, 2019, 12:40 PM

#

So the slowdown is occurring because it builds a massive list of unique strings and has to filter on them

#

In both cases

rancid root Oct 25, 2019, 12:40 PM

#

i've been spoilt by analytics stores that intern strings as a matter of fact

rich trout Oct 25, 2019, 12:40 PM

#

good to know, note to self: all that extra work you did turning string flags into foreignkeys was a very good idea

#

I don't know, but I hope mysql does that, but your strings are probably too long

rancid root Oct 25, 2019, 12:42 PM

#

they're pretty long but low cardinality. But yeah this all makes sense now 😄

rich trout Oct 25, 2019, 12:42 PM

#

fireworks2

rancid root Oct 25, 2019, 12:42 PM

#

thanks again

rich trout Oct 25, 2019, 12:42 PM

#

May I point you towards an alternate solution instead of integer hashing?

rancid root Oct 25, 2019, 12:42 PM

#

interning?

rich trout Oct 25, 2019, 12:43 PM

#

foreign keys

rancid root Oct 25, 2019, 12:43 PM

#

how's that work

rich trout Oct 25, 2019, 12:43 PM

#

I've noticed you have more than a few string flags involved

rancid root Oct 25, 2019, 12:43 PM

#

yes

rich trout Oct 25, 2019, 12:43 PM

#

Generally, you'd create a table for each column of flag

#

so, you'd have an Aggregations table

#

and it would have an id, and the aggregation label, and anything else specific to it

#

then in this table you would simply have the integer referencing that aggregation

#

it does end up making queries a little bit more complicated, but as you can see it is very efficient

rancid root Oct 25, 2019, 12:44 PM

#

so interning with extra steps?

rich trout Oct 25, 2019, 12:44 PM

#

yes

#

It also enforces accuracy: you can't have invalid flags, etc

rancid root Oct 25, 2019, 12:44 PM

#

i was thinking of just chucking in an additional column for each string flag with an integer version of it, no need for hashing, just inrc an int or something for each uniq

rich trout Oct 25, 2019, 12:45 PM

#

It's basically that except db side

rancid root Oct 25, 2019, 12:45 PM

#

yeah fair point

#

does adding the joins to recover the string not cost much?

rich trout Oct 25, 2019, 12:45 PM

#

It should be basically free

rancid root Oct 25, 2019, 12:45 PM

#

(again I mostly live on data-science and work with very different tooling)

rich trout Oct 25, 2019, 12:45 PM

#

It only needs to be done once, and if you do a JOIN WHERE then it should filter first

rancid root Oct 25, 2019, 12:46 PM

#

well, I have time. So might as well try both and see if there's a perf cost

rich trout Oct 25, 2019, 12:46 PM

#

regardless, even if you don't want to add that to the query, you can simply use the ID's as your integer interning and load them manually into your application at the beginning

rancid root Oct 25, 2019, 12:46 PM

#

yeah

rich trout Oct 25, 2019, 12:47 PM

#

👍

#

good luck!

#

sorry, join on

#

ugh

#

SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate
FROM Orders
INNER JOIN Customers ON Orders.CustomerID=Customers.CustomerID;

#

is an example

#

That should filter prior to joining

rancid root Oct 25, 2019, 12:55 PM

#

noted

#

i think as long as an index exists it'll be o(1) slower than storing the intern'd/key value explicitly alongside the string

#

1 being very small

rancid root Oct 25, 2019, 1:11 PM

#

sigh

#

can't alter, can't create tables

rich trout Oct 25, 2019, 1:12 PM

#

F

rancid root Oct 25, 2019, 1:12 PM

#

F to respect our itsec team that's going to get an ass whooping on monday

weary cloak Oct 25, 2019, 1:14 PM

#

hiii! is there any way to store list of strings in a sqlite3 db

rancid root Oct 25, 2019, 1:15 PM

#

list of strings per row, or just list of strings in general?

weary cloak Oct 25, 2019, 1:15 PM

#

per row

rancid root Oct 25, 2019, 1:16 PM

#

apparently not, SO recommends storing a long string with a separator per item and deserialising from that

weary cloak Oct 25, 2019, 1:17 PM

#

ah

#

like a |

rancid root Oct 25, 2019, 1:17 PM

#

i.e. my_list='a|b|c|d'
and have the client split it

#

yeah

weary cloak Oct 25, 2019, 1:17 PM

#

right on, thanks

vocal moon Oct 25, 2019, 4:21 PM

#

@bot.command()
async def flip(ctx, choice, bet: int):
    
    c.execute(f"SELECT * FROM this WHERE id = {ctx.author.id}")
    data = c.fetchall()
    
    value = data[0][1]
    
    choice = choice.upper()
    
    x = random.choice(["HEADS", "TAILS"])
    
    if choice == x:
        y = value+bet
        c.execute("UPDATE this SET value = {} WHERE id = {}".format(y, ctx.author.id))
        conn.commit()
        await ctx.send("yess boi u won")
    else:
        await ctx.send(x)
        await ctx.send("`(test)`")

#

Not updating and it's not presenting any errors

vocal moon Oct 25, 2019, 4:39 PM

#

¬

rain ember Oct 25, 2019, 5:05 PM

#

Could it be the curser isn't set properly or something?

vocal moon Oct 25, 2019, 5:05 PM

#

no it is lemme check

#

conn = sqlite3.connect('users.db')
c = conn.cursor()

rain ember Oct 25, 2019, 5:05 PM

#

📎 unknown.png

#

i used postgres

vocal moon Oct 25, 2019, 5:06 PM

#

Im not using postgress

#

o

rain ember Oct 25, 2019, 5:06 PM

#

I think it should still be the same tho, right ?

vocal moon Oct 25, 2019, 5:06 PM

#

yeh thats inserting

#

im updating

#

I already have the values there

rain ember Oct 25, 2019, 5:07 PM

#

Yeah

#

Can you look into the database using the gui and see if the values are updating or what's going on?

#

It should have some record of changes

#

That's all I can really offer 😦

vocal moon Oct 25, 2019, 5:09 PM

#

nope nothings really updating 😐

#

📎 unknown.png

#

wait

rain ember Oct 25, 2019, 5:09 PM

#

are you using sql alchemy?

vocal moon Oct 25, 2019, 5:09 PM

#

could it be because there is one row im not inserting into?

#

sqlite

#

already done that

#

wait i think i found my issue

rain ember Oct 25, 2019, 5:11 PM

#

I didn't see it sorry

#

what's the error

vocal moon Oct 25, 2019, 5:11 PM

#

nvm

rain ember Oct 25, 2019, 5:12 PM

#

you have c.execute and conn.commit?

#

wouldn't that be c.commit?

vocal moon Oct 25, 2019, 5:12 PM

#

yeh

#

no

#

wait

#

lemme try

#

nop

#

nope*

#

I FOUND THE ISSUE

#

i had an on_error event that i did not even know about and it was throwing the errors away

rain ember Oct 25, 2019, 5:17 PM

#

??

#

Wait

#

what was causing the on-error event?

#

How was it being handled that you couldn't see it?

#

what was the issue ?

vocal moon Oct 25, 2019, 5:18 PM

#

None Type + int

rain ember Oct 25, 2019, 5:18 PM

#

lol

vocal moon Oct 25, 2019, 5:18 PM

#

lmao

lofty summit Oct 26, 2019, 4:54 PM

#

Hey, how to use a cursor in a multi-file project without open and close it for each operation?

late palm Oct 26, 2019, 4:57 PM

#

????

#

You wanna use the sql cursor multiple times????

lofty summit Oct 26, 2019, 4:59 PM

#

yes, why?

late palm Oct 26, 2019, 4:59 PM

#

In one db

lofty summit Oct 26, 2019, 5:00 PM

#

yes

late palm Oct 26, 2019, 5:00 PM

#

Or separate

lofty summit Oct 26, 2019, 5:00 PM

#

same db

late palm Oct 26, 2019, 5:00 PM

#

Just rerun you sql code to the table that you want

#

And it should work with out re-declaring

#

Unfortunately

#

You do need to reopen or redo the cursor i think
Just gimme a sec to check my notes

#

cur.execute(sql, params)

#

That

#

Will be needed to be runned again

#

Otherwise it should work

lofty summit Oct 26, 2019, 5:04 PM

#

Since I'm pretty new with db I'll try to explain clearly what I've in mind:
Let's say I've 2 file

-db.py
-other.py

In my db.py I define all my functions related to my database, and then I import these functions in other.py to use them. Question is: How to avoid opening and closing the database in each function of db.py? Or is it ok to do so?

rain wagon Oct 26, 2019, 5:05 PM

#

Use a getter function that checks if the object already exists

#

look at setattr and getattr as well

#

especially if using Flask or Django

late palm Oct 26, 2019, 5:06 PM

#

It is also fine to open it each time

rain wagon Oct 26, 2019, 5:06 PM

#

but you also don't need to close the db if you still need it

#

commit() should be enough and your data is safe

lofty summit Oct 26, 2019, 5:06 PM

#

I don't use any framework, but I'll look into getter and setattr

late palm Oct 26, 2019, 5:06 PM

#

Okay so then your using sqlite3???

lofty summit Oct 26, 2019, 5:07 PM

#

postgres

#

and psycopg2 module

rain wagon Oct 26, 2019, 5:07 PM

#

is it a web app or a normal one?

#

I am using that as well

lofty summit Oct 26, 2019, 5:07 PM

#

discord bot with a db

late palm Oct 26, 2019, 5:08 PM

#

Okay

lofty summit Oct 26, 2019, 5:09 PM

#

I don't really understand how to have a cursor that can be used from different files ,that's why I thought I needed to open and close it each time.

#

@rain wagon You said it was possible not to close it, how? each function is separated from the others

rain wagon Oct 26, 2019, 5:24 PM

#

I've assumed you are making a normal program or web app

lofty summit Oct 26, 2019, 5:27 PM

#

Ok, thank you both anyway!

mystic ivy Oct 26, 2019, 5:30 PM

#

Hey all, I'm having trouble understanding how to structure my database such that users can select their interests from a pre-defined list as well as add their own custom interests

rain wagon Oct 26, 2019, 5:32 PM

#

Well, you are making a table with some predefined interests and then have a form where user can enter their own

#

also add the ability to remove the predefined stuff

#

so, when a user registers, prefill the table and let them manage it

mystic ivy Oct 26, 2019, 5:33 PM

#

how does that look as far as the database? Like I wouldn't make a column in my interests table for every interest would I?

#

And this would be a many-to-many relationship between users and interests?

rain wagon Oct 26, 2019, 5:35 PM

#

there are multiple ways to approach this: One way is to have a table where you resolve the n:m relationship, insert the foreign keys for the predefined interests and the user id as another foreign keys. It get's tricky when users make their own interests, because you need to save them somewhere as well

#

so I'd say have 2 different tables, one with predefined, one with user defined and resolve it in a third table

mystic ivy Oct 26, 2019, 5:35 PM

#

hm okay. If I try to mock this schema up would you take a look?

rain wagon Oct 26, 2019, 5:40 PM

#

hold on, need to think again 😛

#

https://dbdiagram.io/d/5da934dd02e6e93440f25ae6

A Free Database Designer for Developers and Analysts

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

#

@mystic ivy

#

I think this is kind of a solution

#

Add all interests to ONE table, reference the creator, then resolve the chosen interests in a new table. All predefined interests get id 1, so make sure to never delete ID 1

#

This way you can also offer those interests to other users as they type

mystic ivy Oct 26, 2019, 5:47 PM

#

Thanks!! So when I say predefined I don't mean pre-selected, I mean when they're looking at this page there are checkboxes for preset options but if they're option isn't there, they can type this in

#

that might simplify things

#

also confused how all predefined interests get id 1, wouldn't every interest have its own ID?

#

So I'm guessing I would just have checkboxes, then pull the values from the checkboxes that are selected, then commit them to t_interests with the current_user.id?

rain wagon Oct 26, 2019, 5:56 PM

#

I meant creator_id

#

sorry

mystic ivy Oct 26, 2019, 5:58 PM

#

Ah right so the resolve table is used to capture those interests that weren't created by the current user? So I would query the resolve table instead. But if I go the route I mentioned above, I could eliminate the resolve table right?

rain wagon Oct 26, 2019, 6:06 PM

#

When you have a n:m relationship, you need a resolve table

mystic ivy Oct 26, 2019, 6:07 PM

#

Oh right. But I could get rid of the created_by_id field?

rain wagon Oct 26, 2019, 6:14 PM

#

I wouldn't.

#

So you can delete stuff created by a specific user

#

and considering DSGVO, you may have to

#

sorry, GDPR in English

mystic ivy Oct 26, 2019, 6:15 PM

#

Okay, thanks so much for all your help 🙂

supple fox Oct 26, 2019, 7:20 PM

#

I have table of polls.
When i insert the polls into the table i do it in newest to oldest.
Which makes it so that my serial id of the newest becomes 1.
This causes trouble if i keep inserting polls to the table since than it will go 3 -> 2 -> 1 -> 46 -> 47... when i output the polls to users.
Is there a way to reset the serial id in the opposite order or a custom order? (i got a column for poll creation i can sort by)

rough onyx Oct 26, 2019, 8:20 PM

#

there's most likely a way to reset a serial id (on postgres it's possible if that's what you're using)

#

but I wouldn't do that

#

if it's just an output issue, I think a simple for loop will allow you to reindex things easily

inner pecan Oct 26, 2019, 9:24 PM

#

I have table1, I want to add a colum to it from table2 with value depending on some condition. I currently have done this but I did it by creating a table3 which is made from JOIN of table1 and table2. What is a better way of just updating table1 so I don't need to have table3 ?

#

def Test():
    c.execute("DROP TABLE IF EXISTS TEST")
    Query = "CREATE TABLE if not exists TEST AS SELECT a.Symbol, a.FieldName, a.Value1, a.Value2, b.Value as Permission FROM FieldDifferences a JOIN '{}' as b USING(Symbol) WHERE b.FieldName = 'P'".format(tablename1)
    c.execute(Query)```

#

so now I want to have table3 (TEST) replace table1 (FieldDifferences)

#

which does not seem like good practice for this

inner pecan Oct 26, 2019, 9:52 PM

#

nvm i did this by doing a select of a select

viscid vault Oct 27, 2019, 12:56 AM

#

Hi all - does anyone know if there's a way to either a) cascade deletion down to foreign keys when deleting their related rows or b) refuse to delete if a conflict arises? I'm running into an issue where running raw SQL against my postgres DB doesn't seem to be obeying the foreign key constraints I've put in place.

The query I'm using is

WITH updated AS ( INSERT INTO screener_ticker SELECT * FROM screener_ticker_temp ON CONFLICT (ticker) DO UPDATE SET "table" = EXCLUDED."table", permaticker = EXCLUDED.permaticker, ticker = EXCLUDED.ticker, name = EXCLUDED.name, [...other fields] RETURNING id ) DELETE FROM screener_ticker CASCADE WHERE id NOT IN ( SELECT id FROM updated );

and I have a related table screener_incomestatement which has a FK back to screener_ticker.id but for some reason this is deleting the ticker but not the related income statement.

viscid vault Oct 27, 2019, 1:24 AM

#

I think I found the problem. I think my FK constraints got deleted somehow as I was messing around here

toxic rune Oct 27, 2019, 2:18 AM

#

If I have a string that is repeated multiple times in a table is it better to have a lookup table and use a constant (integer) to reference to the string in the lookup table?

rich trout Oct 27, 2019, 2:43 AM

#

yes

elfin geyser Oct 27, 2019, 3:16 AM

#

any free database cloud ?

old scarab Oct 27, 2019, 4:38 AM

#

hello, I am new to learning sql in class and I've tried googling this but haven't really found what I'm looking for

I'm wondering if SUM has to be by itself (not nested in anything) or if I can use it in the following way:
SELECT (SUM(col1)+SUM(col2)) col3

(also same question for COUNT)

#

(please tag if answering)

lofty summit Oct 27, 2019, 7:54 AM

#

Quick question, what's the interest of disabling auto commit in a DB? (Postgres)

rich trout Oct 27, 2019, 7:56 AM

#

autocommit causes every statement to "commit" if it finishes successfully

#

With autocommit off, you need to issue a COMMIT or ROLLBACK command to finalize your instructions

lofty summit Oct 27, 2019, 8:10 AM

#

I know what it does, ty, but I am more questioning the goal of this practice

rich trout Oct 27, 2019, 8:13 AM

#

Here's an example use:

#

rows = cursor.execute("Massive expensive query")
if not validate(rows):
    cursor.execute("ROLLBACK")
    raise Error()

more_rows = cursor.execute("Modify half of the data we need to")
if not validate(more_rows):
    cursor.execute("ROLLBACK")
    raise Error()
param1, param2 = learning_model_operate(rows, more_rows)
finish = cursor.execute("Finish modifications, %, %", param1, param2)
cursor.execute("COMMIT")

#

Essentially, it lets you "pause" a query while you perform external operations, without losing your state or leaving the database "halfway" between valid states

#

depending on your isolation level, this "pause" could be visible or not

#

this transaction would preserve atomicity depending on your isolation level, whereas with autocommit on you may overwrite new data with results from old data

lofty summit Oct 27, 2019, 8:18 AM

#

Hmm right, but why checking the data after executing the query and not before?

#

I mean, checking if the query is right before sending it to the db

rich trout Oct 27, 2019, 8:19 AM

#

You could, but with autocommit off you can interface with things outside the database to ensure the data is correct, such as a REST api

#

You can do the same by prepending your queries with "BEGIN TRANSACTION"

lofty summit Oct 27, 2019, 8:20 AM

#

Oh ok, like constrains in the table

#

thank you!

wind zinc Oct 27, 2019, 10:20 AM

#

@elfin geyser if you're looking for a free database you won't really find a good stable and reliable one, other than Firebase (made by google), it uses NoSQL tho

rich trout Oct 27, 2019, 10:25 AM

#

Do postgres and sqlite not count as stable and reliable?

#

Mariadb, etc

ionic pecan Oct 27, 2019, 12:21 PM

#

postgres definitely is stable and reliable lol

gilded narwhal Oct 27, 2019, 3:28 PM

#

@ionic salmon afaik INTEGER PRIMARY KEY should reuse deleted ids

#

but maybe that's only when the id count reaches the max

#

https://stackoverflow.com/a/10727585 yep

remote harness Oct 27, 2019, 3:30 PM

#

i'm not a sqlite user, but do you have AUTOINCREMENT turned on?

https://www.sqlite.org/autoinc.html

If the AUTOINCREMENT keyword appears after INTEGER PRIMARY KEY, that changes the automatic ROWID assignment algorithm to prevent the reuse of ROWIDs over the lifetime of the database. In other words, the purpose of AUTOINCREMENT is to prevent the reuse of ROWIDs from previously deleted rows.

#

@ionic salmon

viscid vault Oct 27, 2019, 3:50 PM

#

anyone familiar w/ postgres cursor objects and how to execute more advanced statements safely w/ them? particularly w/ parameterized SQL .... for example how can I safely do something like this in a cursor.execute()?

CREATE TABLE "table_name" ("id" serial NOT NULL PRIMARY KEY, "field1" varchar(255) NULL, "field2" varchar(255) NULL, [... other fields]);

#

I'm reading that escaping the quotes is "doing it wrong" so I'm trying to understand how to form this string properly

rain wagon Oct 27, 2019, 3:54 PM

#

Why are you quoting the table names at all?

#

it is not necessary

viscid vault Oct 27, 2019, 3:55 PM

#

yeah I'm seeing that right now actually 🙂

#

removing the quotes as we speak lol

#

what about if I have a column called "table"?

#

might I need to quote that?

rain wagon Oct 27, 2019, 3:56 PM

#

don't use reserved names

viscid vault Oct 27, 2019, 3:56 PM

#

it's a requirement to have this field called table. changing it isn't currently an option

rain wagon Oct 27, 2019, 3:56 PM

#

table is a sql keyword

viscid vault Oct 27, 2019, 3:57 PM

#

I understand that. I agree w/ you on not using reserved names, just saying in this situation I don't have a choice

#

I need to make the schema match the data going into it which has a column called "table" referencing which table the data comes from at the source

rain wagon Oct 27, 2019, 3:58 PM

#

then call it from_table

#

If it is homework, then your teacher needs a lesson

#

if it's work, then tell your boss it does not work this way

viscid vault Oct 27, 2019, 3:59 PM

#

It's not homework, it's a professional codebase w/ 200k lines of code and I am my own boss, and this is beyond my control.

#

I don't really wanna argue about the semantics of it though - I agree w/ you, but I'm very aware of the need to stay away from reserved keywords and I know for a fact this will not be a problem in this case that's all

#

anyway you answered my original question so it's all good!

#

thanks

#

as an aside though I do agree w/ you...the issue is that the API I get the data from calls the field "table" (which I agree is dumb and bad practice) but I don't want to deal w/ translating fields that are named differently on my end just for this one column.

rich trout Oct 27, 2019, 4:26 PM

#

If you know there are going to be conflicts or are potentially going to be conflicts with reserved names, I would suggest having an easy-add easy-remove shared prefix/postfix applied to every table and column name

#

something like project_

#

so you can have project_table

#

If you're using SQLite, here's a guide on string quoting:

#

https://www.sqlite.org/lang_keywords.html

#

The standard quoting for identifiers is the double quote

viscid vault Oct 27, 2019, 4:29 PM

#

thanks - I'm using Postgres and you're right that's not a bad idea but it's really not a big deal in my situation 🙂

#

just quoting that one field works fine and won't cause any problems. This is a nightly management operation I'm writing anyway so I don't need to worry about SQL injection here

#

I'd be more inclined to avoid escaping and stuff if it was user facing

rich trout Oct 27, 2019, 4:31 PM

#

Postgres has some interesting string escaping features sqlite doesn't have, too, but I think "" will work fine for your needs

viscid vault Oct 27, 2019, 4:32 PM

#

yea that worked fine.

#

at the moment the issue I'm struggling w/ is how to handle duplicate IDs that are inserted in a temp table

#

for example in my temp table:

----------------------------------------------------------------------------
 (1,A,"Agilent Technologies Inc")
 (2,AA,"Alcoa Corp")
 (3,AAAB,"Admiralty Bancorp Inc")
 (4,AAAGY,"Altana Aktiengesellschaft")
 (5,AAAP,"Advanced Accelerator Applications SA")
 (6,AABC,"Access Anytime Bancorp Inc")
 (7,AAC,"AAC Holdings Inc")
 (8,AAC1,"Arcadia Financial Ltd")
 (9,AACB,"Alliance Atlantis Communications Inc")
 (10,AACC,"Asset Acceptance Capital Corp")
 (11,AACE,"Ace Cash Express Inc")
 (12,AACG,"ATA Inc") <---------------------------------note this guy
 (13,AACPF,"Australia Acquisition Corp")
 (14,AAI,"Airtran Holdings Inc")
 (15,AAI1,"Aqua Alliance Inc")
 (16,AAII,"Aaipharma Inc")
 (17,AAIIQ,"Alabama Aircraft Industries Inc")

#

and in the table I want to "merge" with this one:

----------------------------------------------------------------------------
 (1,A,"Agilent Technologies Inc Updated")
 (3,AAAB,"Admiralty Bancorp Inc")
 (4,AAAGY,"Altana Aktiengesellschaft")
 (5,AAAP,"Advanced Accelerator Applications SA")
 (6,AABC,"Access Anytime Bancorp Inc")
 (7,AAC,"AAC Holdings Inc")
 (8,AAC1,"Arcadia Financial Ltd")
 (9,AACB,"Alliance Atlantis Communications Inc")
 (10,AACC,"Asset Acceptance Capital Corp")
 (11,AACE,"Ace Cash Express Inc")
 (12,AACPF,"Australia Acquisition Corp") <-------causes an ID conflict on INSERT INTO
 (13,AAI,"Airtran Holdings Inc")
 (14,AAI1,"Aqua Alliance Inc")
 (15,AAII,"Aaipharma Inc")

#

is it possible to have multiple ON CONFLICT solutions?

rich trout Oct 27, 2019, 4:38 PM

#

The question is why

viscid vault Oct 27, 2019, 4:38 PM

#

Well the reason is this query throws an integrity error cause id 12 exists already in the current table:

CREATE TABLE deleted_tickers_temp AS (
    WITH updated AS (
        INSERT INTO screener_ticker SELECT * FROM screener_ticker_temp ON CONFLICT (ticker) DO UPDATE SET 
        ticker = EXCLUDED.ticker,
        name = EXCLUDED.name,
        [...] RETURNING id
    ), deleted_tickers as (
        SELECT id FROM screener_ticker WHERE id NOT IN (
            SELECT id FROM updated
        )
    ) select * from deleted_tickers
);

#

cause I create the temp tickers table from a CSV which has its own id sequence

rich trout Oct 27, 2019, 4:39 PM

#

Is there a reason not to discard one of the two id sequences?

viscid vault Oct 27, 2019, 4:40 PM

#

yes, the IDs are FK'd from another table

#

so I don't want to lose the original sequence

#

I delete the tickers that aren't in the new temp table, which cascades down to their related objects, but for new ones I probably want to just create them at the end of the table

#

this is what I've got so far which works up to the point of that ID conflict (these are just SQL statements imported from another file called ops to keep the code reasonable but it should give you an idea of the process:

with connection.cursor() as cursor:
    cursor.execute(ops.DROP_EXISTING_TEMP_TICKERS_TABLE)
    cursor.execute(ops.CREATE_TEMP_TICKERS_TABLE)
    cursor.execute(ops.COPY_CSV_TO_TEMP_TICKERS_TABLE.format(filename))
    cursor.execute(ops.DROP_EXISTING_DELETED_TICKERS_TABLE)
    cursor.execute(ops.INSERT_UPDATE_AND_CREATE_DELETED_TICKERS_TABLE)

rich trout Oct 27, 2019, 4:42 PM

#

I think it would help to split this into two steps

viscid vault Oct 27, 2019, 4:43 PM

#

ok - how so??

#

alternatively, I wonder if instead of selecting * from the temp table I select everything except ID will that let the real one handle the auto-increment?

rich trout Oct 27, 2019, 4:45 PM

#

I'm finding it difficult to untangle the logic and references in that statement, which is why I suggested separating it

viscid vault Oct 27, 2019, 4:45 PM

#

which statement?

rich trout Oct 27, 2019, 4:45 PM

#

The big create table statement

viscid vault Oct 27, 2019, 4:45 PM

#

ok I can try to simplify it ...

rich trout Oct 27, 2019, 4:46 PM

#

you can have multiple conflict resolvers

#

If you want to go that route

viscid vault Oct 27, 2019, 4:46 PM

#

working from the inside out, the WITH updated AS part just takes everything from the screener_ticker_temp table and inserts it into screener_ticker (the current/existing table) but if it hits a conflict on the ticker column, rather than insert, it updates that row

#

it saves that result as a CTE called "updated"

rich trout Oct 27, 2019, 4:47 PM

#

The syntax is ON CONFLICT (column) DO THING

#

What I would do is create two temporary tables

viscid vault Oct 27, 2019, 4:48 PM

#

right, so the ON CONFLICT (ticker) looks for any tickers that exist in both tables and rather than insert a duplicate just update the existing row

rich trout Oct 27, 2019, 4:48 PM

#

one for insert, one for update, and on conflict insert it into another table

#

You're missing an ON CONFLICT (id), then, I think

viscid vault Oct 27, 2019, 4:49 PM

#

unless, since I don't really care about the ID I just don't select it to insert

rich trout Oct 27, 2019, 4:50 PM

#

You'd still need an ON CONFLICT (id) DO NOTHING, or w.e

viscid vault Oct 27, 2019, 4:50 PM

#

not if I'm not inserting that column though?

rich trout Oct 27, 2019, 4:50 PM

#

true

#

but you wouldn't get an id conflict if you weren't inserting that column?

#

From what I can tell, it looks like you've got a conflict handler for the ticker column, but not the ID column

viscid vault Oct 27, 2019, 4:51 PM

#

yeah you're right - that's why I was wondering if there's a way to have multiple

#

cause if there's an ID conflict I don't want to update, I want to insert, lol

#

but generate a new ID 😛

#

hmm, it doesn't seem to like me skipping the ID column either.

#

django.db.utils.ProgrammingError: column "id" is of type integer but expression is of type record
LINE 3:   INSERT INTO screener_ticker SELECT ("table",
                                             ^
HINT:  You will need to rewrite or cast the expression.

#

I think writing the on conflict handler for ID is trivial, the question is just what do I actually want to do on that conflict 🙂

rich trout Oct 27, 2019, 4:56 PM

#

Good luck!

viscid vault Oct 27, 2019, 4:59 PM

#

thanks haha

rich trout Oct 27, 2019, 5:00 PM

#

Perhaps try splitting it out a little bit more. If you manually separate your sequences out into "conflicts on id" and "conflicts on ticker", then it could be more cleanly handled

viscid vault Oct 27, 2019, 5:00 PM

#

yeah that's what I'm trying to figure out

rich trout Oct 27, 2019, 5:00 PM

#

Something like SELECT id WHERE table1id = table2id

#

Where you filter proactively instead of reactively

viscid vault Oct 27, 2019, 5:02 PM

#

I think basically what I want to do is

select everything from temp table
insert it into existing table
2a) if there's a conflict on 'ticker', update the row
2b) if there's a conflict on 'id', toss the temp ID and insert a new record in the existing table after the last current ID

#

but I don't know how to do that 😛

#

might have to just do nothing and then make a second pass

#

or insert into another temp table

rich trout Oct 27, 2019, 5:05 PM

#

Why "if there's a conflict"

#

Why "insert, and if there's a conflict" and not "if there is a conflict, update"

#

You can SELECT conflicts easily, so you could explicitly handle each case individually

#

without dealing with the conflict machinery

viscid vault Oct 27, 2019, 5:06 PM

#

because the goal is to insert new tickers as well as update existing ones, and delete ones that have been removed

#

hence try to insert, if there's a conflict, update

#

but that doesn't work when it tries to insert a ticker w/ an ID that already exists

#

and if I just add an on conflict (id) do update set .... then it will overwrite the existing ID w/ the new ticker's info

#

which effectively changes the PK

rich trout Oct 27, 2019, 5:08 PM

#

Here:

#

CREATE deleted_tickers_temp AS ( SELECT * FROM screener_ticker_temp );
INSERT INTO deleted_tickers_temp ( SELECT * from screener_ticker ON CONFLICT (ticker, id) DO NOTHING);
INSERT INTO deleted_tickers_temp ( SELECT all but id FROM screener_ticker WHERE ticker not in (SELECt ticker from deleted_tickers_temp))
UPDATE deleted_tickers_temp ...from.. (SELECT * from screener_ticker WHERE ticker in ( SELECT ticker from deleted_tickers_temp))

#

You can use better syntax, this was just to get the idea out

#

you can use UPDATE FROM on the conflicting tickers, and the insert into is self-explanatory

#

Or you could chain them

viscid vault Oct 27, 2019, 5:17 PM

#

I don't think this does what I need though ... particularly this:

INSERT INTO deleted_tickers_temp ( SELECT * from screener_ticker ON CONFLICT (ticker, id) DO NOTHING);

rich trout Oct 27, 2019, 5:17 PM

#

That step inserts all the non conflicting rows

#

The next step inserts new rows that have conflicting ids but not tickers

viscid vault Oct 27, 2019, 5:18 PM

#

but there will be like 99% conflicting rows

rich trout Oct 27, 2019, 5:18 PM

#

the final step updates rows with conflicting tickers

#

Even better to handle them explicitly then

viscid vault Oct 27, 2019, 5:19 PM

#

hmmm, I'm trying to visualize how this works.

rich trout Oct 27, 2019, 5:20 PM

#

Divide the job into three different instructions

#

if (row is good) -> row
if (row should be updated) -> update row
if (row should be adjusted and added) -> insert row with new id

viscid vault Oct 27, 2019, 5:22 PM

#

so here's the problem with it...

#

I just did the first step and it creates a table which is basically a dupe of screener_tickers_temp (ok fine, no worries) ... but this is what's in it ...

#

----------------------------------------------------------------------------
 (1,A,"Agilent Technologies Inc")
 (2,AA,"Alcoa Corp")
 (3,AAAB,"Admiralty Bancorp Inc")
 (4,AAAGY,"Altana Aktiengesellschaft")
 (5,AAAP,"Advanced Accelerator Applications SA")
 (6,AABC,"Access Anytime Bancorp Inc")
 (7,AAC,"AAC Holdings Inc")
 (8,AAC1,"Arcadia Financial Ltd")
 (9,AACB,"Alliance Atlantis Communications Inc")
 (10,AACC,"Asset Acceptance Capital Corp")
 (11,AACE,"Ace Cash Express Inc")
 (12,AACG,"ATA Inc")
 (13,AACPF,"Australia Acquisition Corp")
 (14,AAI,"Airtran Holdings Inc")
 (15,AAI1,"Aqua Alliance Inc")

#

note in particular id 13 (AACPF)

#

now I do: INSERT INTO deleted_tickers_temp ( SELECT * from screener_ticker ON CONFLICT (ticker, id) DO NOTHING);

rich trout Oct 27, 2019, 5:24 PM

#

That's not quite right

#

it was an example, so there's bits of syntax missing

#

that on-conflict is in the wrong ()

viscid vault Oct 27, 2019, 5:25 PM

#

that's not the problem though - regardless of the syntax when that query works, it will pull from this table:

----------------------------------------------------------------------------
 (1,A,"Agilent Technologies Inc Updated")
 (3,AAAB,"Admiralty Bancorp Inc")
 (4,AAAGY,"Altana Aktiengesellschaft")
 (5,AAAP,"Advanced Accelerator Applications SA")
 (6,AABC,"Access Anytime Bancorp Inc")
 (7,AAC,"AAC Holdings Inc")
 (8,AAC1,"Arcadia Financial Ltd")
 (9,AACB,"Alliance Atlantis Communications Inc")
 (10,AACC,"Asset Acceptance Capital Corp")
 (11,AACE,"Ace Cash Express Inc")
 (12,AACPF,"Australia Acquisition Corp")
 (13,AAI,"Airtran Holdings Inc")
 (14,AAI1,"Aqua Alliance Inc")
 (15,AAII,"Aaipharma Inc")

#

and when it gets to id 12 (AACPF) it will do nothing right?

rich trout Oct 27, 2019, 5:25 PM

#

yep

viscid vault Oct 27, 2019, 5:26 PM

#

that's no bueno

rich trout Oct 27, 2019, 5:26 PM

#

Why?

viscid vault Oct 27, 2019, 5:26 PM

#

because then I have the ticker AACPF w/ an id of 13 and it's supposed to be 12

#

it got shifted in the temp tickers table cause there was another ticker inserted above it

rich trout Oct 27, 2019, 5:27 PM

#

But the tickers still conflict, no?

viscid vault Oct 27, 2019, 5:27 PM

#

yea ... so what does that mean?

rich trout Oct 27, 2019, 5:28 PM

#

Ok

#

You select in from your first table

#

that defines your initial, old data

#

you select in from your second table

#

if the id's are duplicate, don't insert, if the tickers dupe, don't insert

viscid vault Oct 27, 2019, 5:29 PM

#

hmm, ok, I think you've got the tables backwards

rich trout Oct 27, 2019, 5:29 PM

#

probably

viscid vault Oct 27, 2019, 5:30 PM

#

screener_ticker is the one that will have the correct existing IDs

#

temp is the one that will have new stuff

rich trout Oct 27, 2019, 5:30 PM

#

then create your temp from your valid base data

viscid vault Oct 27, 2019, 5:31 PM

#

doing that now. So now I have basically a dupe of the current (valid) data, and I need to take everything from the temp table and stuff it in, handling the various conflicts.

#

lemme go back to your query again 😄

rich trout Oct 27, 2019, 5:31 PM

#

👍

viscid vault Oct 27, 2019, 5:35 PM

#

ugh, apparently you can't use multiple columns in the on conflict clause

rich trout Oct 27, 2019, 5:37 PM

#

Meh, just make it a where

#

INSERT INTO deleted_tickers_temp ( SELECT * from new_data_table WHERE id not in (SELECT id from deleted_tickers_temp) AND ticker not in (select ticker from deleted_Tickers_Temp));

viscid vault Oct 27, 2019, 5:38 PM

#

doesn't that search the whole table repeatedly?

rich trout Oct 27, 2019, 5:39 PM

#

depends on the optimizer

#

also, try just a plain ON CONFLICT DO NOTHING

#

you may not have to specify a constraint

viscid vault Oct 27, 2019, 5:41 PM

#

welp ... that ain't right lol

#

lfa_pg_test=# drop table deleted_tickers_temp;
DROP TABLE
lfa_pg_test=# CREATE TABLE deleted_tickers_temp AS ( SELECT * FROM screener_ticker );
SELECT 14376
lfa_pg_test=# INSERT INTO deleted_tickers_temp SELECT * from screener_ticker_temp ON CONFLICT DO NOTHING;
INSERT 0 14390
lfa_pg_test=# select count(*) from deleted_tickers_temp ;
 count 
-------
 28766
(1 row)

lfa_pg_test=#

rich trout Oct 27, 2019, 5:42 PM

#

What's wrong about it?

viscid vault Oct 27, 2019, 5:43 PM

#

it created 14,000+ duplicates 😄

rich trout Oct 27, 2019, 5:44 PM

#

yikes

#

Where'd the constraints go?

#

oh

#

Your query doesn't have ()

#

it's possible the on conflict is applying to the inner query

viscid vault Oct 27, 2019, 5:45 PM

#

looks like CREATE TABLE AS didn't copy the constraints

#

I think I know how to resolve that

rich trout Oct 27, 2019, 5:46 PM

#

I need to be going, but good luck!

viscid vault Oct 27, 2019, 5:47 PM

#

no prob - thanks for your help!

viscid vault Oct 27, 2019, 7:46 PM

#

oye this query will be the death of me.

pseudo geode Oct 28, 2019, 12:23 AM

#

I was wondering if anyone can help me with how to search or lookup in SQLite3 with python

rancid root Oct 28, 2019, 4:54 AM

#

@rich trout that 45s query down to 700ms or so after removing all strings, cheers

rich trout Oct 28, 2019, 4:58 AM

#

👍

#

Thats much more appropriate timing :P

rancid root Oct 28, 2019, 6:00 AM

#

still slow asf for 10m rows but yeah i guess

foggy cedar Oct 28, 2019, 10:22 AM

#

I've posted a question in help-7 that i think i maybe should have posted here, but any help is appreciated 😄

toxic rune Oct 28, 2019, 4:11 PM

#

Is it a bad idea to have two different type of databases in one same project? I.e. Postgres and mongo.

Reason: My project right now uses postgres for it's data. But I have a specific type of data which comes from another source that i'm formatting that has been a huge pain until today to organize in something like postgres, a lot of tables, references, etc which breaks my brain.

glad spear Oct 28, 2019, 6:17 PM

#

can you use a jsonb column for that data?

toxic rune Oct 28, 2019, 6:19 PM

#

I can, but I'm not sure if it's a good idea. As I need to query specific values of that data

rich trout Oct 29, 2019, 6:45 AM

#

It's not a great idea to mix them, because it significantly increases your operational complexity and causes you to have to manage two different datastores separately. But if it's going to be an extreme pain to manage in a RDBMS then perhaps a secondary "document store" could solve your problem. Code that works is better than pretty code that does not.

#

But I would suggest taking a second look or opinion on how hard it is to organize the data into postgres, as there could be some easy savings overlooked

compact warren Oct 29, 2019, 2:17 PM

#

please what is the equivalent for decimal(8,2) in SqlAlchemy?
I'm declaring models for tables
Would

db.Numeric(8,2)

suffice?

toxic rune Oct 29, 2019, 4:46 PM

#

@rich trout Well the data in both databases don't really interact with each other at all, they're separated

#

It's just a lot of data that sounds painful to keep updated, lot of tables too

polar badge Oct 29, 2019, 5:56 PM

#

Anyone able to help me get mongodb working. I am having issues connecting to my cluster from python. this is the rror.
pymongo.errors.ServerSelectionTimeoutError: SSL handshake failed: cluster0-shard-00-01-ltipa.gcp.mongodb.net:27017: [WinError 10054] An existing connection was forcibly closed by the remote host,SSL handshake failed: cluster0-shard-00-02-ltipa.gcp.mongodb.net:27017: [WinError 10054] An existing connection was forcibly closed by the remote host,SSL handshake failed: cluster0-shard-00-00-ltipa.gcp.mongodb.net:27017: _ssl.c:1039: The handshake operation timed out

polar badge Oct 29, 2019, 9:29 PM

#

figured it out, guess my work is blocking port 27017f or outbound.

inner pecan Oct 29, 2019, 11:42 PM

#

hi can someone please explain this sql query to me

 Select A., B. From A Full Join B on A.key = B.key Where B.Key is null or A.key is null```

#

specifically, the last bit. I don't understand what checking if A.key or B.key is null does?

#

For context, A and B have identical table schema but values for the primary key may or may not be different between them

#

this is meant to find differences?

polar badge Oct 29, 2019, 11:48 PM

#

If memory serves me right, you may have two columns where the data is null, and it could happen a lot. so you wouldn't want it to match one very one, as null is not very unique.

inner pecan Oct 29, 2019, 11:49 PM

#

shouldn't it be IS NOT NULL then ?

#

to not match if its null?

inner pecan Oct 30, 2019, 12:21 AM

#

how can I refer to my tables composite primary key? it's made of two fields

#

so how do I join on it?

#

e.g join on table1.primarykey = table2.primarykey ?

#

ON Table1.Key1 = Table2.Key1 AND Table1.Key2 = Table2.Key2

#

would this work? by .Key1/.Key2 is just that the column?

inner pecan Oct 30, 2019, 1:07 AM

#

Query1 = "SELECT Symbol, Fieldname, a.Value as Value1, b.Value as Value2  FROM '{}' as a JOIN '{}' as b USING(Symbol,FieldName) WHERE a.Value != b.Value".format(tablename1,tablename2)```
Can someone explain to me why this has the same query speed whether I make Symbol,FieldName my primary composite key in the table or not?

#

I have two Symbol,FieldName,Value tables where Symbol+FieldName is unique (primary key)

#

and i want to find the different values for Value

#

how can I make this query faster, I thought it would be if I define these as my composite key in table?

faint prairie Oct 30, 2019, 1:20 AM

#

optimizing querys depends on a lot of things, like what other data is stored in the table, the order of the columns, the amount of data etc.

#

But I do see something that should be addressed

#

using .format() to format SQL queries puts you at rist of SQL injection attack unless you're doing aggressive input validation yourself

#

You should use a library that can do parametrized queries with prepared statements

#

The pymysql library has a simple api for this, here's an example: https://stackoverflow.com/questions/775296/mysql-parameterized-queries

Stack Overflow

MySQL parameterized queries

I am having a hard time using the MySQLdb module to insert information into my database. I need to insert 6 variables into the table.

cursor.execute ("""
INSERT INTO Songs (SongName, SongAr...

#

Almost all other databases and client libraries support similar things if you're not using mysql

inner pecan Oct 30, 2019, 1:23 AM

#

is that when you use (?,?,?) tuples for example?

#

im using sqlite

faint prairie Oct 30, 2019, 1:23 AM

#

Yes

inner pecan Oct 30, 2019, 1:24 AM

#

ok ive used that before on other statements, ill make sure im consistent

faint prairie Oct 30, 2019, 1:24 AM

#

If your application doesnt accept input from users there's no danger, but it's good practice anyway

inner pecan Oct 30, 2019, 1:24 AM

#

im just trying to work out whether SQL is what I want for my project

#

and im not sure if its too slow or if my queries are just bad

#

since im new to it

faint prairie Oct 30, 2019, 1:25 AM

#

a major slowdown with SQLite has to do with the order of columns

#

if you have a column that is type BLOB in the middle of the table and the objects are very large it will hurt performance as it has to scan over those object every time

inner pecan Oct 30, 2019, 1:26 AM

#

my columns are all VARCHAR(255) at the moment

faint prairie Oct 30, 2019, 1:26 AM

#

How many columns?

inner pecan Oct 30, 2019, 1:27 AM

#

5, 4 of which are unique and can make a composite key

faint prairie Oct 30, 2019, 1:27 AM

#

(BLOB was just an example btw, any large field will impact performance)

inner pecan Oct 30, 2019, 1:27 AM

#

but i have a lot of rows

#

max use case is 60,000,000 rows

faint prairie Oct 30, 2019, 1:27 AM

#

I would just recommend using an AUTOINCREMENT as PK anyway

inner pecan Oct 30, 2019, 1:27 AM

#

im using a Attribute Entity Value schema someone recommended me

#

or maybe its entity attribute value

#

well

#

its basically

#

I have two tables, both of which should be identical but in reality aren't due to various issues

#

they're all for stock market symbols btw

#

each stock market symbol (lke AAPL, MSFT)

#

has fields

#

so

#

AAPL 52WeekHigh 34.5

#

AAPL 52WeekLow 23.4

#

MSFT Close 30.3

#

so I have these in tabe1, but I also have them in table2 but the values might be slightly different

#

I want to get the differences all in one table

#

like

#

AAPL 52WeekHigh 34.5 56.4

#

so
symbol field value1 value2

#

make sense?

#

and symbol field is a primary key

#

its unique

faint prairie Oct 30, 2019, 1:30 AM

#

Sure so you want to join on the identical PKs and detect discrepancies?

inner pecan Oct 30, 2019, 1:30 AM

#

yes
but

#

my question is

#

I was stupid and didn't define my primary KEy in my table schema

#

and I was using this query: python Query1 = "SELECT Symbol, Fieldname, a.Value as Value1, b.Value as Value2 FROM '{}' as a JOIN '{}' as b USING(Symbol,FieldName) WHERE a.Value != b.Value".format(tablename1,tablename2)

#

and I changed it so I defined my primary key as a composite of Symbol,FieldName

#

but there was no difference in speed

#

so is that query already using the primary key to index ?

#

and its not needed?

#

but yes, what I want to do is join on the identical PK's and detect discrepancies

#

thats exactly it

#

now I'm wondering if for an example table with potentially 120,000,000 rows this is way too slow and not feasible?

#

because that's a potential max use case i may run into

#

and i have to build my app based on that just in case

faint prairie Oct 30, 2019, 1:33 AM

#

I mean 60mil rows is a bit big for SQLite, are you considering putting the data into postres/mysql for the large table?

inner pecan Oct 30, 2019, 1:33 AM

#

because in theory it should just iterate through table1 then use index lookup on the PK to see if the same or not

#

I am not, I wanted to use SQLite so its running locally and not on a port/etc on my box

#

when ive found the differences, I will be making some stats on them e.g how many are different, what are the main differences

#

and sending this over the network to a remote MySQL database

#

the SQLITE DB is meant to be temporary - it will get deleted after I generate stats off of the differences

#

I was using dictionaries before this but I ran out of memory when I got to the large datasets

#

so now im trying sqlite

#

as its disk based

faint prairie Oct 30, 2019, 1:35 AM

#

I'm not sure then, if it doesn't take too long (less than days) and its a one time ETL it might be "good enough"

inner pecan Oct 30, 2019, 1:36 AM

#

it needs to be run once a day every day on a large number of servers 😛

#

(which are also doing other things)

#

i guess I will just download my max use case dataset and try it on there and see how long it takes..

faint prairie Oct 30, 2019, 1:37 AM

#

Hmmm, then I might look at something more productionalized, but I'm not sure how to optimize that query as is

inner pecan Oct 30, 2019, 1:38 AM

#

yeah its a bit above my pay grade to be honest but if no one else does it it wont get done

#

its that kind of situation

faint prairie Oct 30, 2019, 1:38 AM

#

¯_(ツ)_/¯

lament hornet Oct 30, 2019, 5:37 PM

#

i've got a discord bot that currently uses sqlite with an ORM (peewee) but i want to migrate to postgres and do everything with queries rather than an ORM.
can anyone recommend a sort of best practices guide for this? i have no prior experience with postgres and my other DB experience is with mssql using exclusively sprocs.

related question, looking at some examples of other bots using dbs like these, they seem to have a lot of boiler plate stuff like:

class DateSQL(SQLType):
    python = datetime.date

    def to_sql(self):
        return 'DATE'

defined for all types. is this a requirement?

viscid vault Oct 30, 2019, 8:50 PM

#

anyone know if it's possible to capture conflicts when doing an INSERT INTO blah_table ... ON CONFLICT DO UPDATE SET blah=EXCLUDED.blah?

#

I want to know which rows were updated vs which ones weren't touched.

#

actually, on that note, I think my query here is just updating all rows since the conflict isn't actually on the row data but rather on whether it's trying to insert a dupe ... in other words, most times when there's a conflict on the constraint in question, the row won't change so I want to DO NOTHING, however if something has changed I want to update it.

#

Here's my query (Postgres):

CREATE TABLE updated_income_statements AS (
    WITH updated AS (
        INSERT INTO screener_incomestatement (
            ...
        ) SELECT 
            ...
        FROM screener_incomestatement_temp ON CONFLICT ON CONSTRAINT unique_income_statement DO UPDATE SET 
            col1 = EXCLUDED.col1,
            col2 = EXCLUDED.col2,
            ...
            RETURNING ticker_id, dimension_id
    ) SELECT * FROM updated
)

viscid vault Oct 30, 2019, 10:46 PM

#

figured this out by adding a WHERE clause ... now I am going the other way, trying to figure out how to also log which ones were inserted lol

inner pecan Oct 31, 2019, 12:18 AM

#

anyone know why a 3.8 GB text file inserted into a sqlite3 table is making my DB more than 40 GB ??

#

is that normal?

full jungle Oct 31, 2019, 6:32 AM

#

What exactly are you using to store it? Also, I would consider simply storing the file path to it in the DB that's on an FTP server, rather than the file itself. But if that's not an option you should be able to store it as a BLOB.

lofty summit Oct 31, 2019, 6:46 PM

#

I need to store datas (json/python dictionaries) temporarily (datas that are used in combination with my Postgres db), I don't want to add a column/table to store them because after a short period of time I'll delete them. Is storing them in a local file (using pickle ?) is okay or a bad idea?

buoyant breach Oct 31, 2019, 6:55 PM

#

Depends on the load. It may be slow if you want to store a lot at once.

#

Also pickle has it's own issues. It may make sense to use something like raw json.

lofty summit Oct 31, 2019, 6:56 PM

#

the datas would be a couple of ids

buoyant breach Oct 31, 2019, 6:58 PM

#

Then it should be fine.

lofty summit Oct 31, 2019, 6:59 PM

#

ok ty

ornate isle Oct 31, 2019, 7:44 PM

#

just use json if the data is easily serializable.

#

that said, there are also things like dataset that lets you literally write to an actual sql db (sqlite or postgres) with the ease of using a dict, or tinydb that uses a flat json file as a "database" but has the ability to query it like you might a true db, etc

#

those options are probably way overkill for what you are doing currently but nonetheless they are fantastic tools to be aware of for down the road

inner pecan Nov 1, 2019, 4:07 AM

#

if I have two tables with identical schema and have 30 columns, there's one primary key. What would bet he most efficient way of finding all column values where the primary key differs between to two tables?

#

e.g primary key BOB in table 1 might have the 10th column with value 10, but in table 2 its 20

#

I guess I can do a join on primary key where a.column1 != b.column1 or a.column2 != b.column2 .... a.column30 != b.column30

#

but that seems a bit silly?

sweet nebula Nov 1, 2019, 11:03 AM

#

would anyone be willing to help me design a small database from scratch? i know whats going to be stored in it etc. however i need a design write up. So something that shows how i developed the tables from no normal form up to 3rd normal form and an ERD

#

also wont be online for a couple of hours so if you are willing could you private message me thanks 🙂

fringe tiger Nov 1, 2019, 12:35 PM

#

I'm using debian 10 and trying to set up mysql (mariadb) on it wjth Python aiomysql. So I can connect to it in CLI with
sudo mysql -u root -h localhost -p (empty password for now just want to connect to it)

#

but when trying to connect with python I get "cant connect to database" alltho I'm using the same data as in sudo mysql -u root -h localhost -p aka user root host localhost port 3306 passeword empty

#

ping for reply

urban cradle Nov 1, 2019, 1:18 PM

#

don't specify host @fringe tiger

#

sudo mysql -u root -p should be enough

fringe tiger Nov 1, 2019, 1:19 PM

#

No I mean that works

#

But connecting with py script doesn't

urban cradle Nov 1, 2019, 1:19 PM

#

OOOH

#

my bad

#

how are you connecting into it with python?

fringe tiger Nov 1, 2019, 1:20 PM

#

Aka I know the code is working because I used it on win. But it wont conmect on linhx vps

#

Sec

urban cradle Nov 1, 2019, 1:22 PM

#

if your code is connecting into a remote host you'll obviously need to specify that

#

plus you'll need to GRANT ALL ON *.* (or whatever database/table) TO 'user'@'clientip'

fringe tiger Nov 1, 2019, 1:23 PM

#

no no it's all on local

urban cradle Nov 1, 2019, 1:24 PM

#

could be the hostname of your VPS isn't coming through as you'd expect

#

if you cat /etc/hostname on your VPS what do you get?

#

your python could be trying to access it as 'user'@'hostname'

#

rather than being viewed as local host or any

fringe tiger Nov 1, 2019, 1:25 PM

#

so my config

{
  "host": "127.0.0.1",
  "port": 3306,
  "user": "root",
  "password": "",
  "database_name": "spectro"
}

I tried "localhost" too no bueno.
The database "spectro" as created trough CLI sudo mysql -u root -h localhost -p and then using mysql queries.

My connect command in python looks like:

async def _get_pool(self):
        host = self.database_config.get_key("host")
        port = self.database_config.get_key("port")
        user = self.database_config.get_key("user")
        password = self.database_config.get_key("password")
        password = password if password else None
        database_name = self.database_config.get_key("database_name")
        return await aiomysql.create_pool(host=host, port=port, user=user, password=password,
                                          db=database_name, autocommit=True)

And I run the script with sudo python3 bot.py

#

Id on't have that much epx with setting databases in linux, first one in fact

#

I can't test that command as it's not my VPS (it's a client for which I'm creating the script but the database won't connect)

urban cradle Nov 1, 2019, 1:27 PM

#

SELECT user,host FROM mysql.user;

fringe tiger Nov 1, 2019, 1:27 PM

#

It google cloud btw

urban cradle Nov 1, 2019, 1:27 PM

#

and see what the host is set to

fringe tiger Nov 1, 2019, 1:27 PM

#

ah I'll take note of that

#

thanks

urban cradle Nov 1, 2019, 1:27 PM

#

if you set the host to 'user'@'%' then that user can log in from any IP

fringe tiger Nov 1, 2019, 1:28 PM

#

I though local is always enabled

urban cradle Nov 1, 2019, 1:28 PM

#

create a new user for your app to use rather than root too

#

yeah but it could be specified as "localhost" and your code is trying to connect in as "<hostname of server>"

fringe tiger Nov 1, 2019, 1:28 PM

#

yes I planned that but I was wondering why I can't connect with root

#

oki

#

oke thanks 👍

urban cradle Nov 1, 2019, 1:29 PM

#

np

#

if you need any more help give me a poke

sweet nebula Nov 1, 2019, 1:52 PM

#

ok im making a database. which stores usernames (unique key), whether they are an admin, whether its their first time logging on, and what parts of a programme they have access too. with this i notice their is a many-to-many relationship that needs resolving with a link table as many users can have access to many different programmes. On top of this different users may have access to different parts e.g. USER1 has access to part A and B whereas USER2 just has access to part A. So how would i make the link in an Entity relationship diagram

#

📎 unknown.png

#

would something like this work?

#

ignore the lists in the Users table

craggy jackal Nov 1, 2019, 4:52 PM

#

So you might want a couple of tables

#

A solver is really just a solver name right?

#

@sweet nebula

#

What is a "solver" what attribution is for solvers only?

#

Is it just a name that is the PK for what solver is?