Inserting records into postgreSQL database in Python

Question

When I run this command in python,

command=("""INSERT INTO perms(role_id,resource_id,p_create,p_read,p_update,p_delete,p_execute) VALUES 
(%s,%s,%s,%s,%s,%s,%s)""")
cur.execute(command(roleArray,reasourceArrray,data[2],data[3],data[4],data[5],data[6]))

I get the following error

Traceback (most recent call last):
cur.execute(command(roleArray,reasourceArrray,data[2],data[3],data[4],data[5],data[6]))
psycopg2.ProgrammingError: can't adapt type 'numpy.int64'
bash: parse_git_branch: command not found

Additional info: RoleArray contains 792 records & looks like this [102, 102, 102, 102, 102, 102, 103, 103, 105, 105, 106, 106, 106, 106, 106, 106, 106,...etc]

reasourceArrray contains 792 records & looks like this [45, 46, 47, 48, 49, 50, 51, 52, 53, 54, 55, 56, 57, 58, 59, 60, 61,...etc]

data[2],data[3],data[4],data[5],data[6] all contain 792 records & are boolean fields.

Any feedback on how to resolve the error?

UPDATE ---- Thank you everyone for the feedback! In addition to the answer I upvoted check out this solution

for i, x, y in zip(RoleArray, resourceArray, range (0,len(data[2]))):
  data2Value=data[2][y] 
  data3Value=data[3][y] 
  data4Value=data[4][y] 
  data5Value=data[5][y] 
  data6Value=data[6][y] 

  cur.execute("INSERT INTO perms (role_id,resource_id,p_create,p_read,p_update,p_delete,p_execute) VALUES('"
  +(i)+"','"+(x)+"','"+(str(data2Value))+"','" 
  (str(data3Value))+"','"+(str(data4Value))+"','" 
  (str(data5Value))+"','"+(str(data6Value))+"')") 
  con.commit()

roganjosh · Accepted Answer · 2020-06-02T10:44:55.060

numpy integers are not the same as regular Python integers. You can do a simple check on that:

import numpy as np

arr = np.array([1, 2, 3])
print(type(arr[0]))

arr_1 = arr.tolist()
print(type(arr_1[0]))

The psycopg2 library (and I think most others, but I can't make an exhaustive list) won't accept the numpy integer type; you need to call numpy.tolist() to convert them back.

That said, there are other issues. The error you've given me in the comments shows that you have a pandas.DataFrame. There are several ways to approach this:

Insert the extra numpy arrays (RoleArray, resourceArray) into the df and then use df.to_sql`
Grab the values from the df as a 2D numpy array, insert the columns, and then do the upload

In the example below, I've chosen the latter for "reasons" i.e. no particular reason :)

Since you are inserting multiple rows, you would normally use executemany in SQL to avoid multiple transactions. However, it doesn't work as expected in psycopg2. Therefore, we're going to use execute_batch

Putting it all together:

import numpy as np
import pandas as pd

from psycopg2.extras import execute_batch

# Create a fake DataFrame so that we can do some slicing
df = pd.DataFrame()
for x in range(10):
    df[x] = np.random.randint(0, 100, 10)

# Create the additional arrays with values in different ranges
RoleArray = np.random.randint(100, 200, 10)
resourceArray = np.random.randint(200, 300, 10)

# Take a slice of the df to get the relevant columns, and get the numpy array
# using .values
data = df.iloc[:, 2:7].values

# Add the extra columns to the "front" of the array
data = np.hstack((np.array([RoleArray, resourceArray]).reshape(-1, 2), data))

# Convert into Python integer types
data = data.tolist()

# Now bulk up-load
command=("""
         INSERT INTO perms(role_id,
                           resource_id,
                           p_create,
                           p_read,
                           p_update,
                           p_delete,
                           p_execute) 
         VALUES (%s,%s,%s,%s,%s,%s,%s)
         """)

execute_batch(cur,
              command,
              data)        

# Don't forget a conn.commit() (or equivalent) here

When I checked type(data[2]) I got `` & when making the changes you suggested (Thank you!) I got this error `AttributeError: 'DataFrame' object has no attribute 'tolist'` — CatGirl19, Jun 01 '20 at 23:37
@CatGirl19 oh, then you need to clarify what `data` is. It looks like an array but this suggests it's a 2D DataFrame — roganjosh, Jun 01 '20 at 23:42
@CatGirl19 nm, I got it. You did include it. Give me a few mins — roganjosh, Jun 01 '20 at 23:45
@CatGirl19 I'm going to delete this for now. I think I'm too tired to properly match everything up into something properly pythonic. I've commented on the answer by Parfait, so that might be the best shot for fixing this in the short-term — roganjosh, Jun 02 '20 at 00:02
@CatGirl19 Please see my edit. I think I've fixed it now I've had a sleep :) — roganjosh, Jun 02 '20 at 10:45
Thank you! This solution works! I also solved it by doing the following. `for i, x, y in zip(RoleArray, resourceArray, range (0,len(data[2]))): data2Value=data[2][y] data3Value=data[3][y] data4Value=data[4][y] data5Value=data[5][y] data6Value=data[6][y] cur.execute("INSERT INTO perms (role_id,resource_id,p_create,p_read,p_update,p_delete,p_execute) VALUES ('"+(i)+"','"+(x)+"','"+(str(data2Value))+"','"+(str(data3Value))+"','"+(str(data4Value))+"','"+(str(data5Value))+"','"+(str(data6Value))+"')") con.commit()` — CatGirl19, Jun 02 '20 at 17:20
@CatGirl19 you're welcome. It's tough to read that code but it looks like you're building a query with string concatenation. Please don't do that; it's open to SQL Injection. Look up "Bobby Tables" to start, and then be horrified by how impactful and wide-spread the issue is :) — roganjosh, Jun 02 '20 at 18:37

score 0 · Answer 2 · answered Jun 01 '20 at 23:23

The problem comes from %s expecting a string and you are passing a numpy array. Instead what you should consider doing is iterating over the records and inserting them one by one with a for loop. You will probably need to cast the values in the two numpy arrays to string by using: str(value).

score 0 · Answer 3 · answered Jun 01 '20 at 23:55

Consider executemany and properly separate query and parameters in execute call. Also convert pandas Series to lists for parameterization:

command = """INSERT INTO perms (role_id,resource_id,p_create,p_read,
                                p_update,p_delete,p_execute) 
             VALUES (%s, %s, %s, %s, %s, %s, %s)
          """

cur.executemany(command, (roleArray, reasourceArrray,
                          data[2].tolist(), data[3].tolist(),
                          data[4].tolist(), data[5].tolist(),
                          data[6].tolist()))
myconn.commit()

Inserting records into postgreSQL database in Python

3 Answers3