dougdrums posted:

CarForumPoster posted:

Forming an LLC can be under $200 and may indemnify you from the worries. If they sue they’re suing an LLC with no money.

Zugzwang posted:

I did not know about cached_property. That looks awesome and I can’t wait to simplify some of my code with it.

mila kunis posted:

So since the OP is pretty old, what's considered the best tutorial/course for learning python for someone who's never programmed before these days?

qsvui posted:

I thought everyone moved onto Poetry now? Not that I know if it's any better...

duck monster posted:

PIP works!

duck monster posted:

Oh god. Why are people creating even more package managers. Are people REALLY pining for the toolchain hell that is javascript THAT much?

PIP works!

(Oh and I suppose theres Conda. The maths people seem to like that one for some reason)

QuarkJets posted:

Pip works well for pure python packages, conda works well if you also have non-python dependencies. The Intel MKL is a faster binding for numpy, sometimes much faster, and while there are instructions for compiling numpy from source with the mkl (which you of course installed separately) gently caress actually doing that, and quadruple-gently caress doing that in Windows lmao

duck monster posted:

Yeah I get that with Conda. All those hosed up nvidia and intel binary blobs that need to be rustled. Or f*#?king GDAL lol.

I just wish Pipenv was fit for purpose. I like the idea. Its just.... kind of broken

quote:

ProgrammingError: (pymysql.err.ProgrammingError) (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '), 0, 0, 0, NULL, (), NULL, NULL, 0, 0, 0, 0, 'tns:ID', 1, 'id', 0, 0, 0),(1, 0,' at line 1")

Seventh Arrow posted:

Apologies for the bunches of text incoming. This is my first foray into pandas' "to_sql" function.

I have a dataframe full of metadata pulled from salesforce. I'm trying to push this dataframe to mysql (my manager wants to use mysql because it's free, of course).

The dataframe looks ok when I inspect it. The to_sql connection string works ok. But I get the following error:

Of course the full error is big ol' text dump if you're morbidly curious, or think it will help: https://www.klgrth.io/paste/8795j

The python code is below. The salesforce credentials are from a randomly generated instance populated with fake data, so you're free to try the below code or pulling it into a dataframe or whatever:

code:

import pandas as pd
from simple_salesforce import Salesforce, SalesforceLogin, SFType
import sqlalchemy
import pymysql

engine = sqlalchemy.create_engine("mysql+pymysql://<username>:<password>@localhost/python_data_loader")


class SalesforceMetadata:
    def __init__(self, username, password, security_token=None, domain=None):
        self.username = username
        self.password = password
        self.security_token = security_token
        self.domain = domain 
        self.salesforce = None

    def connect_to_salesforce(self):
        try:
            self.session_id, self.instance = SalesforceLogin(username=self.username, password=self.password, security_token=self.security_token, domain=self.domain)
            self.salesforce = Salesforce(session_id=self.session_id, instance=self.instance)
            print(f'Connected to "{self.instance}"')
        except Exception as e:
            print(e)

    def salesforce_org_metadata_list(self):
        """
        return Salesforce org objects metafsys
        """
        if self.salesforce is None:
            print('Please connect to Salesforce first')
            return
        df = pd.DataFrame(self.salesforce.describe())
        df = df['sobjects'].apply(pd.Series)
        return df

    def object_metadata(self, object_api_name):
        if self.salesforce is None:
            print('Please connect to Salesforce first')
            return
        try:
            obj = SFType(object_api_name, session_id=self.session_id, sf_instance=self.instance)
            df = pd.DataFrame(obj.describe()['fields'])
            return df
        except Exception as e:
            print(e)
                
    def extract_picklist_values(self, object_metadata_df, field_api_name):
        picklist_values = []
        picklist_list_column = (object_metadata_df[object_metadata_df['name']==field_api_name]['picklistValues'].apply(pd.Series).T)
        if picklist_list_column.empty:
            print('Picklist is empty.')
            return
        for item in (object_metadata_df[object_metadata_df['name']==field_api_name]['picklistValues'].apply(pd.Series).T).iterrows():
            picklist_values.append(item[1].values[-1]['value'])
        return picklist_values

    
username = 'test-psh23q4xjkbt@example.com'
password = 'bfkuG5wf]snic'
domain = 'test'
security_token = None


# construct sf_metadata object
sf_metadata = SalesforceMetadata(username, password, security_token, domain)


# connect to Salesforce
sf_metadata.connect_to_salesforce()


# return organization metadata (this will return overall detail of every single Saleforce object in an org)
df_org_metadata = sf_metadata.salesforce_org_metadata_list()
df_org_metadata.to_csv('org metadata sample.csv', index=False)
# print(df_org_metadata)


# to return just an object's metadata as a dataframe
df_account = sf_metadata.object_metadata('account')
df_opportunity = sf_metadata.object_metadata('opportunity')


# push data to MySQL db
object_list = ['Account']
for object_api_name in object_list:
    df = sf_metadata.object_metadata(object_api_name)
    if df is None:
        print(f'Object {object_api_name} is not found.')
    else:
        df.to_sql(con=engine,name=object_api_name,if_exists='replace', index=True)

Thanks in advance for any help. I kinda-sorta suspect that somehow a funky character or spacing or something is being passed to mysql but I'm not exactly sure.

Also I tried other mysql modules (like mysqldb) to no avail.

CarForumPoster posted:

I didn’t read but iirc % text when you’re trying to do %s screws things up. Iirc a work around is to use a text() function. I’m posting from bed so can’t Google much more than that

Ihmemies posted:

I guess numpy is just drat slow. In Advent of Code 2022/08 I saved data to a numpy 2d array. When I iterated through the array, two python for loops were like twice as fast than using numpy.ndarray() iterator. I began to think if more numpy functions are so slow too.

I timed the parts of my program:
time to do slices: 4.49991ms
time for silver: 63.13467ms
time for gold: 51.50747ms
time total: 119.14206ms

For silver I was doing this around 40000 times:

code:

max_trees_around.append(numpy.max( lf_slice )) 

So I switched to

code:

max_trees_around.append(max(lf_slice.flatten().tolist())) 

time to do slices: 5.00917ms
time for silver: 39.77084ms
time for gold: 40.23886ms
time total: 85.01887ms
...

I even tried running the whole solution 100 times with timeit.
With python's max(): 9040.51970ms
With numpy.max(): 12312.64960ms

Edit: I got the same with numpy.amax(lf_slice), numpy.nanmax() was quite a bit slower.
Edit2: I guess numpy needs a lot more data than a 10k cell 2d array. Welp. At least it's so easy to access slices compared to python's 2d array with lists.

QuarkJets posted:

I don't really know the answer, but since you're just using a canned dataframe method I'd guess that there's some issue in your data. I'm not about to go digging through that huge one-line dump of it so good luck with that

You can help isolate the problem by just inserting one row at a time, and if it breaks on the very first row then try selecting a specific range of columns (say the first 10 of them).

DoctorTristan posted:

Also the ‘connectionless commits’ in sqlAlchemy (your df.to_sql(con=engine,…)) are deprecated, you should do this as

code:

with engine.connect() as conn:
    df.to_sql(con=conn,…)

Edit: from a very brief scan of the error message I’d venture that the issue is going to turn out to be that the source data contains some text in brackets that pandas is either parsing into a tuple or not quoting properly and is loving up the sql insert statements. The way to figure it out is to iteratively narrow down the problem row/column, as QuarkJets suggested above.

DELETE CASCADE posted:

numpy sucks, pandas sucks, gotta use it but it'll break so who tf knows

don't use mysql ever, replace with postgres, everything will work fine then

QuarkJets posted:

oh yeah and point out to your manager that postgres is also free

DoctorTristan posted:

Also as a very general rule I’d avoid using pandas for data pipeline work like this because the ‘helpful’ inferring of column data types will frequently gently caress you over in exactly this way. Generally the safer way is to upload the data as text into a staging table and then do type conversions on the database side.

(The best method is to use proper ELT tools, but from the sound of things that isn’t an option)

QuarkJets posted:

OP I think your observations here are the payoff from the big recommendations a few weeks ago: that you should carefully inspect the data so that you understand what it contains. After a few posters suggested that this is a data problem you were able to point out that there's a multiple-choice field that contains bracketed options, and you were able to deduce that maybe that's the real cause of the issue. I think that's probably right, since the error is complaining about parens in an SQL statement that pandas created from your data.

So what should you do about this, do you really need this column in the database? It sounds like these are options shown to a user in a UI maybe, that doesn't sound useful for a database but I have no idea whether you may actually need it. Drop that column if you can. Ideally you'd only query for the smallest-necessary number of columns, right now you have 57 columns and I'm guessing you don't need to put all of those in a database table but maybe I'm wrong.

DoctorTristan is right to point out that there are pitfalls in using pandas, if there's more info you can extract from the SalesForce AI then you may be able to start catching these issues before they occur - deviations in data types are especially troublesome (e.g. maybe there's a field that is normally an integer but sometimes returns a string or null or some other poo poo).

QuarkJets posted:

(...)
The loop control variable isn't hidden, it's part of the Sequence that was created when you invoked range(). Generators work this way too:

Python code:

def weird_number_generator():
    yield 0
    yield 10
    yield -40
    yield 50

values = weird_number_generator()
x = next(values)
print(x)  # 0
x += 4
print(x)  # 4
x = next(values)
print(x)  # 10
print(next(values))  # -40