Extracting Data from SQL Server and Publishing to PubSub

[Scott Mitchell]

Good Morning All,

I am sure this is a very simple issue to resolve as I am only new to both Google Cloud and Python, so please be gentle.

I am attempting to extract data from an SQL Server Database (straight forward enough so far) using pyodbc and publish the resulting data to a PubSub topic.  The ultimate goal is to then utilise Dataflow to populate a Big Query table.  I used Publisher.py from the Google Documentation as a basis for my code:

def publish_messages(project_id, topic_name):

   """Publishes multiple messages to a Pub/Sub topic."""

   # [START pubsub_quickstart_publisher]

   # [START pubsub_publish]

   from google.cloud import pubsub_v1

   

   # TODO project_id = "Your Google Cloud Project ID"

   # TODO topic_name = "Your Pub/Sub topic name"

    publisher = pubsub_v1.PublisherClient()

   # The `topic_path` method creates a fully qualified identifier

   # in the form `projects/{project_id}/topics/{topic_name}`

   topic_path = publisher.topic_path(project_id, topic_name)

    for n in range(1, 10):

       data = u'Message number {}'.format(n)

       # Data must be a bytestring

       data = data.encode('utf-8')

       # When you publish a message, the client returns a future.

       future = publisher.publish(topic_path, data=data)

       print(future.result())

    print('Published messages.')

   # [END pubsub_quickstart_publisher]

   # [END pubsub_publish]

I have tried several modifications to this but am not able to publish to my established topic, against which I have a single Subscriber to validate against.

The code in it's current form is:

import pyodbc

import os

import sys

os.environ["GOOGLE_APPLICATION_CREDENTIALS"] = "H:\\venv\\SQLBDtoBigQuery\\key.json"

sqlSelect = "SELECT * FROM EMP"

connectionString = f"DRIVER={{SQL Server}};server={server};database={database};uid={username};pwd={password}"

conn = pyodbc.connect(connectionString)

cursor = conn.cursor()

cursor = conn.cursor().execute(sqlSelect)

columns = [column[0] for column in cursor.description]

print(columns)

results = []

for row in cursor.fetchall():

    results.append(dict(zip(columns, row)))

def publish_messages(project_id, topic_name, inbound_data):

    """Publishes multiple messages to a Pub/Sub topic."""

    # [START pubsub_quickstart_publisher]

    # [START pubsub_publish]

    from google.cloud import pubsub_v1

    # TODO project_id = "Your Google Cloud Project ID"

    # TODO topic_name = "Your Pub/Sub topic name"

    publisher = pubsub_v1.PublisherClient()

    # The `topic_path` method creates a fully qualified identifier

    # in the form `projects/{project_id}/topics/{topic_name}`

    topic_path = publisher.topic_path(project_id, topic_name)

    for row in inbound_data:        

        # Data must be a bytestring

        str_data=row

        data = str_data.encode('utf-8')

        # When you publish a message, the client returns a future.

        future = publisher.publish(topic_path, attribute=data)

    print('Published messages.')

Any help would be greatly appreciated.

Warm Regards,

Scott

This email is sent by Auto & General Insurance Company Ltd, Auto & General Services Pty Ltd, Auto & General Holdings Pty Ltd or a related body corporate (Auto & General) and is for the intended addressee.
The views expressed in this email and attachments (email) reflect the views of the stated author but may not reflect views of Auto & General. This email is confidential and subject to copyright. 
It may be privileged. If you are not the intended addressee, confidentiality and privilege have not been waived and any use, interference with, or disclosure of this email is unauthorised. 
If you are not the intended addressee please immediately notify the sender and then delete the email. Auto & General does not warrant that this email is error or virus free.

[Jinjun (Cloud Platform Support)]

Hello,

Google Groups are for general discussion. For specific coding issues, please ask on Stackoverflow.com that is monitored by many developers.