CODEWITHSUNDEEP
pythonsql
CatGirl19
CatGirl19

Reputation: 209

Inserting records into postgreSQL database in Python

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()

Upvotes: 0

Views: 1646

Answers (3)

roganjosh
roganjosh

Reputation: 13175

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:

  1. Insert the extra numpy arrays (RoleArray, resourceArray) into the df and then use df.to_sql`
  2. 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

Upvotes: 1

Parfait
Parfait

Reputation: 107567

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()

Upvotes: 0

bhristov
bhristov

Reputation: 3187

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).

Upvotes: 0

Related Questions