I am using this code to connect to Bigquery and it creates a table. Still, I m getting this error.
Blockquoteimport os
import psycopg2
from google.cloud import bigquery
Establish connection to PostgreSQL
conn = psycopg2.connect(database=“Marketing”,
user=“postgres”,
password=“”,
host=“localhost”,
port=“5432”)
cur = conn.cursor()
Fetch data from PostgreSQL
cur.execute(‘’‘SELECT ORDER_NUM, ORDER_TYPE, CUST_NAME, PROD_NUMBER, PROD_NAME,
QUANTITY, PRICE, DISCOUNT, QUANTITY * PRICE * (1 - DISCOUNT) AS ORDER_TOTAL
FROM Sales’‘’)
rows = cur.fetchall()
Set the path to your service account key file
os.environ[‘GOOGLE_APPLICATION_CREDENTIALS’] = r’C:\Users\Anthony.DESKTOP-ES5HL78\Downloads\inner-orb-349717-213eaa6ac019.json’
Create a BigQuery client
client = bigquery.Client()
Specify your project ID and dataset ID
project_id = ‘inner-orb-349717’
dataset_id = ‘Anthony’ # Remove the project ID from dataset_id
Construct the BigQuery table schema
schema = [
bigquery.SchemaField(‘ORDER_NUM’, ‘INTEGER’, mode=‘REQUIRED’),
bigquery.SchemaField(‘ORDER_TYPE’, ‘STRING’, mode=‘NULLABLE’),
bigquery.SchemaField(‘CUST_NAME’, ‘STRING’, mode=‘NULLABLE’),
bigquery.SchemaField(‘PROD_NUMBER’, ‘STRING’, mode=‘NULLABLE’),
bigquery.SchemaField(‘PROD_NAME’, ‘STRING’, mode=‘NULLABLE’),
bigquery.SchemaField(‘QUANTITY’, ‘INTEGER’, mode=‘NULLABLE’),
bigquery.SchemaField(‘PRICE’, ‘FLOAT’, mode=‘NULLABLE’),
bigquery.SchemaField(‘DISCOUNT’, ‘FLOAT’, mode=‘NULLABLE’),
bigquery.SchemaField(‘ORDER_TOTAL’, ‘FLOAT’, mode=‘NULLABLE’),
]
Create the BigQuery table
table_ref = client.dataset(dataset_id, project=project_id).table(‘Sales40’) # Specify the project ID in the dataset reference
table = bigquery.Table(table_ref, schema=schema)
table = client.create_table(table)
Insert data into the BigQuery table
rows_to_insert =
for row in rows:
rows_to_insert.append(tuple(row))
Insert the data into the BigQuery table
job_config = bigquery.LoadJobConfig()
job_config.source_format = bigquery.SourceFormat.PYTHON
job_config.write_disposition = bigquery.WriteDisposition.WRITE_APPEND
job = client.load_table_from_pydata(rows_to_insert, table_ref, job_config=job_config)
job.result()
print(‘Data inserted into BigQuery table successfully.’)
Check for errors in the job
if job.errors:
print(‘Error occurred while inserting data into BigQuery table:’)
for error in job.errors:
print(error)
else:
print(‘No errors occurred during data insertion.’)
Close the PostgreSQL connection
conn.close()
Blockquote
Blockquote
ile “C:\Users\Anthony.DESKTOP-ES5HL78\Documents\Scrapy\projects\pp.py”, line 56, in
job_config.source_format = bigquery.SourceFormat.PYTHON
AttributeError: type object ‘SourceFormat’ has no attribute ‘PYTHON’
Blockquote